We have implemented a custom SQLiteFunction in C# (using 1.0.116) that can take some time.

Now we want to be able to cancel it, but I found now way. Is there a way get an event when Connection.Cancel() is invoked?

Thank you

I'm afraid that's not possible, but I'm no expert.

The only API in SQLite to cancel a running statement is to interrupt it.

But that's likely implemented via regularly checking for an interrupted flag when running the VDBE code of that query, in between those VDBE instructions. But calling a custom (scalar) function is likely a single instruction, and it's running outside SQLite's control, which is waiting for it to return. In other words, an interrupt point is possible between instructions, but not inside a single VDBE instruction.

Unless there was an SQLite API such as sqlite3_was_interrupted() that user-code implementing a (long-running) custom function could call / poll explicitly, and return early if set. I'm not aware of such an API. Thus I don't think what you are asking is possible.

Sometimes threads can be interrupted, but typically not cleanly, and that wouldn't an SQLite mechanism. Signals also can also interrupt things, but often only temporarily, and again that's not an SQLite mechanism.

There should to be an sqlite3_is_interrupted(sqlite3_context*) function that heavy weight user functions/modules can call to check whether the application has called sqlite3_interrupt().

Something like sqlite3_is_interrupted is something that I wanted too, some time ago; I think it is useful for some things (including any functions/virtual-tables that access the internet, or that make a lot of slow calculations). However, it has not been added yet.

The sqlite3_is_interrupted() interface is now available on the latest trunk check-in. Please try it out and report back any problems you find.

Thanks! Here is the commit, for reference.

Can you please explain how Joe? Is this .NET specific? Or can a long-running C custom SQL function be interrupted too?

To do the same thing with the C API an API function (one that has access to SQLite3 internals) would need to accept the sqlite_context*, find the connection, and return the interrupt flag associated with the underlying connection.

Not complicated, but it would have to be an internal function (API).

Thank you! I will check it out when it is available! :-)