Can you interrupt a SQLite command? How?

I want to interrupt the execution of an IDbCommand. Does anyone know a way to force the command to cancel/abort/stop/cease and desist?

What I’m looking for is something along the lines of the core function sqlite3_interrupt() AKA “Interrupt A Long-Running Query”—but it doesn’t seem Mono’s SQLite library is designed for this. Is there a way to get SQLite working asynchronously like this?

Or, maybe, is there a working example anywhere of launching a SQLite command (in Unity) as an asynchronous process, then interrupting it? Google has come up empty.

Big thanks for any help you can provide!


History…

Using the command’s Cancel() method does nothing. Closing the command’s connection also does nothing.

Using BEGIN TRANSACTION and then calling a second command to ROLLBACK during execution throws out-of-memory errors and occasionally hangs the program.

It seems launching a SqliteCommand object asynchronously and then calling back to it like this is a no-no; Mono’s .chm docs specify:

You May NOT call methods or properties or otherwise reference any SQLite provider classes that belong to another thread.”


Code…

This is the Start() event of a single MonoBehaviour attached to the main camera in an otherwise empty scene. Full script [37873-sqlitetest.txt|37873].

// run once at startup
	public void Start() {
		Init() ;             // initialize database if it doesn't exist
		BeginFetch() ;       // launch a command 
		Thread.Sleep( 20 ) ; // wait a bit

		// interrupt with IDbTransaction.Rollback()
		// (throws "InvalidOperationException: Connection must be valid and open to commit transaction")
		//InterruptA() ;

		// interrupt by calling command's .Cancel() method and closing its connection
		// (doesn't do anything at all)
		//InterruptB() ;

		// interrupt by executing second command using commandText "ROLLBACK;"
		// (works, BUT sometimes crashes the program...)
		InterruptC() ;

	}

	// Sample output:

	// all Interrupt functions commented:
	//   Fetch() begins at 0 ms
	//   FetchCallback() begins at 194 ms
	//   Query completed in 194 ms: klaatu barada nikto

	// only InterruptA uncommented
	//   Fetch() begins at 1 ms
	//   InterruptA() begins at 20 ms
	//   InvalidOperationException: Connection must be valid and open to commit transaction
	//   FetchCallback() begins at 322 ms
	//   Query completed in 322 ms: klaatu barada nikto

	// only InterruptB uncommented
	//   Fetch() begins at 0 ms
	//   InterruptB() begins at 21 ms
	//   FetchCallback() begins at 275 ms
	//   Query completed in 275 ms: klaatu barada nikto

	// only InterruptC uncommented
	//   Fetch() begins at 0 ms
	//   InterruptC() begins at 21 ms
	//   Fetch interrupted: Mono.Data.SqliteClient.SqliteExecutionException: SQL logic error or missing database
	//   FetchCallback() begins at 29 ms
	//   Query completed in 30 ms: NULL

It sure seems as though this can’t be done—for now, at any rate. Thanks for your help anyway!