CannotOpen on second UPDATE statement in async transaction about sqlite-net HOT 6 OPEN

For me it worked using:

tran.ExecuteAsync("UPDATE Settlement SET Completed = 1 WHERE DamagedObjectId = ?", settlement.DamagedObjectId);

from sqlite-net.

I also get this issue on Metro using sqlite3.dll (3.7.13)

from sqlite-net.

For those hitting this can you try one of the options below and see if it helps?

Adding this code right after opening the database produces the expected
result:

verifySQLResult(SQLite3.Execute(_db, "PRAGMA temp_store = memory;", 0, 0, 0));

Alternatively, the following code could be used (also right after opening the
database):

var TempPath = Windows.Storage.ApplicationData.Current.TemporaryFolder.Path;
verifySQLResult(SQLite3.Execute(_db, string.Format(
"PRAGMA temp_store_directory = '{0}';", TempPath), 0, 0, 0));

from sqlite-net.

As already said, try using executeAsync() instead of execute().

Tim Heuer [email protected] schrieb:

For those hitting this can you try one of the options below and see if it helps?

Adding this code right after opening the database produces the expected
result:

verifySQLResult(SQLite3.Execute(_db, "PRAGMA temp_store = memory;", 0, 0, 0));

Alternatively, the following code could be used (also right after opening the
database):

var TempPath = Windows.Storage.ApplicationData.Current.TemporaryFolder.Path;
verifySQLResult(SQLite3.Execute(_db, string.Format(
"PRAGMA temp_store_directory = '{0}';", TempPath), 0, 0, 0));

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/78#issuecomment-7985751.

from sqlite-net.

Kornelis - I'm just trying to determine/help with perhaps a more fundamental issue with WinRT app usage and SQLite (and specifically those using this lib as well). If the temp_store works it will help continue to isolate (and perhaps change something we need in SQLite to expose an export for this option)

from sqlite-net.

Tim, you are right. If I add either

Execute("PRAGMA temp_store = memory;");

or

var TempPath = Windows.Storage.ApplicationData.Current.TemporaryFolder.Path; 
Execute(String.Format("PRAGMA temp_store_directory = '{0}';", TempPath));

to the SQLiteConnection constructor right after the call to SQLite3.Open I get the expected behavior - the issue doesn't occur any more.

Kornelis, while your workaround avoids this issue, it's generally not a good idea to call SQLiteAsyncConnection methods inside RunInTransactionAsync. As I described in issue #73 this can result in a deadlock when the call ends up running in a different thread. It happened to me in application code. That's way I marked SQLiteAsyncConnection.RunInTransactionAsync(Action<SQLiteAsyncConnection>) as obsolete and added the override accepting Action<SQLiteConnection> instead.

from sqlite-net.