1

I have an SQLite-based app that works fine on Windows, Android and Java. I am trying to port it to iOS: the device is running iOS 9.3.1.

Everything else works apart from the blob write, which fails on the sqlite_step call with an "unknown error". The code is based on this SE post.

This is a stripped-down version of the low-level SQLite interface: the query comes from the next level up.

NSLog (@"Preparing query {%s}", query);
rc = sqlite3_prepare_v2 (hdb, query, -1, &stmt, &tail);
if (rc == SQLITE_OK) {
   NSLog (@"Binding blob: size= %d", (int)[blob length]);
   rc = sqlite3_bind_blob (stmt, 1, [blob bytes], (int) [blob length], SQLITE_STATIC);
   if (rc == SQLITE_OK) {
      rc = sqlite3_step (stmt);
      if (rc != SQLITE_OK)
        [self sql_error: @"sqlDo" line: __LINE__];
   }
   if (sqlite3_finalize (stmt) != SQLITE_OK)
     [self sql_error: @"sqlEndQuery" line: __LINE__];

 }

This is the NSLog output:

Preparing query {INSERT OR REPLACE INTO xxx VALUES (18356, '', ?)}
Binding blob: size= 5388
SQL error at line 157 in sqlDo: query:{INSERT OR REPLACE INTO xxx VALUES (18356, '', ?)} message:{unknown error}
Community
  • 1
  • 1
JavaLatte
  • 378
  • 5
  • 18
  • Show the actual query instead of the "xxx". – rmaddy Apr 07 '16 at 16:20
  • @rmaddy: That is the actual query, apart from replacing the table name by xxx for reasons of confidentiality. – JavaLatte Apr 07 '16 at 16:45
  • Do you list three column names after the table name? `... INTO FOO (Col1, Col2, Col3) VALUES ...`. – rmaddy Apr 07 '16 at 16:46
  • I am writing all of the columns, so the column names are optional. here is the structure of the table: CREATE TABLE [xxx] ( [field1] INTEGER PRIMARY KEY, [field2] TEXT, [field3] BLOB); – JavaLatte Apr 07 '16 at 16:55
  • @Rob, it will still break, but in a subtler way. I'd rather have it crash and burn while I'm testing than risk shipping a production system that's writing records with fields missing. – JavaLatte Apr 07 '16 at 17:03
  • I'll respectfully disagree (because if you remove one field and add another or change the order of the fields, the SQL will insert data incorrectly without errors). I'd rather see explicit column names in the `INSERT` statement and any new fields should have constraints that capture other issues (e.g. non-null, default values, etc.). But to each his own. – Rob Apr 07 '16 at 19:39

1 Answers1

1

After performing a sqlite3_step of an INSERT statement, the result is SQLITE_DONE, not SQLITE_OK.

So, you want:

rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE)
    [self sql_error: @"sqlDo" line: __LINE__];
Rob
  • 415,655
  • 72
  • 787
  • 1,044