Strange SQLite behavior

Question

Hi,

I am inserting data into a table and sqlite3_step returns SQLITE_DONE. But sometimes there is no new data in the table! Even if I do the check immediately after the data was added.

Here is my code:

sqlite3_stmt *addTranslationStmt2 = NULL;

if(sqlite3_prepare_v2( database , "INSERT INTO translations(lang1_wordid, lang2_wordid) VALUES(?, ?)" , -1, & addTranslationStmt2, NULL) != SQLITE_OK)
{
  NSLog(@"statement prepare error");
  addTranslationStmt2 = NULL;
  return -1;
}

sqlite3_bind_int(addTranslationStmt2, 1, word_id);
sqlite3_bind_int(addTranslationStmt2, 2, translation_id);

if(sqlite3_step(addTranslationStmt2) != SQLITE_DONE)
{
  NSLog(@"addTranslationStmt2 error: '%s'", sqlite3_errmsg(database));
  sqlite3_reset(addTranslationStmt2);
  return -1;
}

sqlite3_reset(addTranslationStmt2);

if (![self wordHasTranslation: word_id translation_id: translation_id])
  NSLog(@"And after all translation was not added even though sqlite3_step returns SQLITE_DONE!");

So I am wondering why does the last mentioned log function is being called. This means that after adding data there is no data in table. Sometimes it adds and sometimes doesn't.

Where may the problem be? Thank you in advance.

UPDATE: I found out that this happens only when a new database is created. After an application is restarted it works in a right way. But if a new database is created, the strange behavior comes back.

Answer 1

Sqlite does operate by default in auto-commit mode, but we have no way of knowing if some previous interaction or setting may have reset that; if you add an explicit COMMIT after the INSERT, does that help?

Edit: probably not, give further info from the comments, but just in case you do need to commit in other circumstances, the simplest way is:

char* errmsg;
int result = sqlite3_exec(database, "COMMIT", NULL, NULL, &errmsg);

with the usual result codes &c (you'll need to sqlite3_free(errmsg) after you've used the error message it points to, if any).