- Home /
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 here (rename to .cs).
// 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
the usual way to cancel a sql is indeed to open a transaction and then rollback it so go ahead - don't bother too much about the "full memory" error right now, I guess depends on your test code that is a bit messy - I suggest you to remake it with just a clean single repeating query with rollback and see if the problem still happens.
I got rid of the code—turns out the way it worked is verboten anyway.
Rolling back a command (or undoing it) is the easy part! $$anonymous$$y goal is interrupting the command—that is, stopping its execution while it’s executing.
In this sense "Cancel" is the wrong word, so I'll update the question if I can to be more accurate.
to help you dig into further I think it would be good you send us more details about your environment: Unity release, which sqlite plugin libraries, version etc you're using and also your test code as well could help a lot.
I'll add back the latest test code. As you mention, it is a bit messy, though refactoring doesn't help much.
Running in Unity 4.6.1f1 on OS X 10.8.5, using $$anonymous$$ono.Data.SqliteClient, setup detailed here.
a while ago I downloaded a nifty asset store package named Sqliter and now I remember something in the comments talking about async usage with Unity, that I 'fetched' again and post here:
/// - It is a flat file database. You can change the settings to make it run completely in memory, which will make it even /// faster; however, you cannot have separate threads interact with it -ever-, so if you plan on using SQLite for any sort /// of multiplayer game and want different Unity instances to interact/read data... they absolutely cannot.
I'm not entirely sure if this is what you're trying to achieve but anyway what I'm seeing in your code looks indeed fiddling with multithreading, something Unity doesn't like much, for what I know. This is how much I can help you with this, sorry.
Answer by Victory Dan Greene · Dec 30, 2014 at 08:51 PM
It sure seems as though this can’t be done—for now, at any rate. Thanks for your help anyway!
Your answer
Follow this Question
Related Questions
Animatior Glitch when interrupted 0 Answers
Sqlite db error build iphone4 0 Answers
Clearing cached sqlite 1 Answer
Database (SQLite) Setup for Unity 0 Answers
Spatialite under Unity 0 Answers