2

I am using SQLite-net for accessing an SQLite database file in a WinRT app. I don't have any problems reading from the database using the ExecuteQuery (I actually use the modified version from https://github.com/praeclarum/sqlite-net/issues/82 as I don't use table mappings and want the results as dictionary which calls ExecuteDeferredQuery underneath).

When I try to insert records into my database using ExecuteNonQuery, I am getting an exception with the message "CannotOpen".

Well, just a few lines above, I can read from the database successfully. I double checked the file permissions to the sqlite database file and gave everyone on the computer full control to the file to avoid any file permission issues, but the result is the same, still getting "CannotOpen".

I just tried to do a select statement with ExecuteNonQuery (just to see if it works"), I still get an exception, this time saying "Row" as the exception message.
I tried to execute my insert with ExecuteQuery to see what happens, no exception is thrown, everything seems OK, but no rows are inserted into my database.

Well, that may be explainable as ExecuteQuery is designed for querying, not inserting, but what could be the reason for ExecuteNonQuery throwing exceptions?

Here is my code (removed actual table names and parameters with generic ones for privacy):

SQLiteCommand cmd = conn.CreateCommand("insert into mytable(myfields...) values (?,?,?,?,?,?,?)", my parameters...);
cmd.ExecuteNonQuery(); //throws SQLiteException

However this code doesn't throw exception:

SQLiteCommand cmd = conn.CreateCommand("select * from mytable where some condition...", some parameters...);
var result = cmd.ExecuteToDictionary(); //renamed ExecuteQuery method from https://github.com/praeclarum/sqlite-net/issues/82

UPDATE: I've further tracked the issue down to something even simpler (and weird). This code is the very first call to SQLite framework after initialization of the connection. This very code, when executed, throws an exception in the fourth line:

 SQLiteCommand cmd = conn.CreateCommand("select * from mytable");
 cmd.ExecuteNonQuery();
 cmd = conn.CreateCommand("select * from mytable"); //yes, the same simple query as above
 cmd.ExecuteNonQuery();//getting error

UPDATE 2: If I call ExecuteToDictionary instead of ExecuteNonQuery, it works.

UPDATE 3: If I try a direct query (from the conn object such as conn.Execute("query...")) before all these calls it fails. If it's an insert query, I get CannotOpen error, if it's a select query, I get a Row error.

Why am I getting an exception on the second call to ExecuteNonQuery?

Why am I getting a different error message "Row" when I try SELECT with ExecuteNonQuery? And lastly, why are these exceptions so user-unfriendly?

Can Poyrazoğlu
  • 33,241
  • 48
  • 191
  • 389
  • I formatted your question so it is easier to digest... – rene Jun 22 '13 at 09:50
  • @rene thanks. I've realized I've asked a little bit too much in one place :) – Can Poyrazoğlu Jun 22 '13 at 10:06
  • It might be helpful if you provide a code sample that reproduces the error(s). – chue x Jun 22 '13 at 13:29
  • @chuex added sample code. – Can Poyrazoğlu Jun 22 '13 at 18:09
  • @canpoyrazoğlu - Are you doing anything with the connection object prior to your code samples above? Can I assume that the above are the first commands you are creating with the connection? – chue x Jun 22 '13 at 18:49
  • yes, exactly. my connection is actually a singleton created when first needed, and always used thereafter. no modifications are ever made. – Can Poyrazoğlu Jun 22 '13 at 18:55
  • @canpoyrazoğlu - Sorry, given the above, I cannot reproduce the errors. The only thing I would be cautious about is using a singleton connection. If you are doing windows-runtime development, you'll likely be putting db calls onto the threadpool, which may be problematic. From the [SQLite Site](http://www.sqlite.org/threadsafe.html): *Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.* – chue x Jun 22 '13 at 22:14
  • @canpoyrazoğlu - One more thing: Are you using the latest SQLite-Net version? – chue x Jun 22 '13 at 22:20
  • @chuex yes I'm on the latest version downloaded it just 4 days ago. see my updated question, there is nothing about threads. – Can Poyrazoğlu Jun 23 '13 at 08:09
  • @canpoyrazoğlu - I am not sure whether the code in your first update is valid. You are calling the function `ExecuteNonQuery` using a `SELECT` statement. It is intended only for `INSERT, UPDATE, DELETE`. Also regarding threads - you may not be using them now, but you will likely have to do so if you want your UI to be responsive. – chue x Jun 23 '13 at 15:27
  • @chuex i tried all combinations, it fails. i've tried it with insert too, it fails. regarding threads, i'll be careful not to access the database from different threads, all the code is currently in the UI thread anyway. – Can Poyrazoğlu Jun 23 '13 at 18:43

2 Answers2

3

Found out the answer. The SQLite file was in a directory that didn't have write access (the file DID have all the access in file properties, but I think it's a WinRT security model issue as the file was outside the sandbox of WinRT's storage folders. I could read the file, but not write. Thanks to SQLite-net's extremely helpful exception messages such as "Row" and "CannotOpen", without giving any real details about the problem, it took me days to realize that it was a file access issue rather than an SQLite configuration/query issue.

If anyone has any similar problems in the future, first, check that the SQLite database is in the storage directory of the WinRT app.

Can Poyrazoğlu
  • 33,241
  • 48
  • 191
  • 389
0

try to close and open the connection object before executing any other operations

Eslam Gamal
  • 74
  • 5