I have an app which displays a ListView from SQLite database content. When I click on one of the items in the ListView, I post a message to my JNI thread and try to do a DELETE command on a table thats in the same database but its a completely different table to what has been access by the ListView.
Unfortunately the sqlite3_finalize() call fails with an error code of 1 (SQL_ERROR).
I suspect this is because the database has been opened (or is still open?) by the SimpleCursorAdapter. Would this be the case?
The DELETE statement itself works perfectly when I try it in Firefox's SQLiteManager (where I test all my SQLite statements on a copy of the database). UPDATE commands seem to work OK as do any SELECT commands - its just the DELETE that isn't working.
Do I somehow need to manually close the SimpleCursorAdapter's cursor when it has finished displaying the rows? I'm using a LoaderManager to maintain the data content.
Here's the JNI execution code:
BOOL ExecuteSQL( char *pszSQL )
{
sqlite3_stmt *pStmt;
int rc;
rc = sqlite3_prepare_v2( g_MainDB,
pszSQL,
-1,
&pStmt,
0 );
int nFailStep = 0;
if( rc == SQLITE_OK )
rc = sqlite3_step( pStmt );
else
nFailStep = 1;
if( rc == SQLITE_OK )
rc = sqlite3_finalize( pStmt );
else
nFailStep = 2;
if( ( rc == SQLITE_OK ) || ( rc == SQLITE_DONE ) )
return TRUE;
else
nFailStep = 3;
LogDebugf( "ExecuteSQL: rc: %d (%s) nFailStep: %d\n", rc, sqlite3_errstr( rc ), nFailStep );
return FALSE;
}
It fails with:
ExecuteSQL: rc: 1 (SQL logic error or missing database) nFailStep: 3
The entire nFailStep
logic is horribly wrong; the value is unlikely to be correct.
Furthermore, that code does not call sqlite3_finalize
if sqlite3_step
is successful.
Additionally, you'd get a better error message with sqlite3_errmsg
:
BOOL ExecuteSQL(const char *pszSQL)
{
sqlite3_stmt *stmt;
int rc;
rc = sqlite3_prepare_v2(g_MainDB, pszSQL, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
LogDebugf("ExecuteSQL: prepare failed: %s\n", sqlite3_errmsg(g_MainDB));
return FALSE;
}
rc = sqlite3_step(stmt);
if (rc != SQLITE_ROW && rc != SQLITE_DONE) {
LogDebugf("ExecuteSQL: step failed: %s\n", sqlite3_errmsg(g_MainDB));
sqlite3_finalize(stmt);
return FALSE;
}
sqlite3_finalize(stmt);
return TRUE;
}