Why do I get a SQLITE_MISUSE : Out of Memory error?

Question

I am writing an iOS application that directly accesses SQLite. I have done this sort of thing many times on Android, so I'm struggling to see where my error lies - however my inserts are returning the SQLITE_MISUSE error (code 21), with the message "out of Memory". Below are the steps I have taken to lead me to this insert.

First, the table creation:

NSString *sql = @"CREATE TABLE IF NOT EXISTS UsersTable (lastName TEXT,id TEXT PRIMARY KEY NOT NULL,picture BLOB,firstName TEXT,age TEXT,email TEXT,sex TEXT,height TEXT,weight TEXT)";

//create the database if it does not yet exist
NSFileManager *filemgr = [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath: path ] == NO)
{
    const char *dbpath = [path UTF8String];

    //This was if (sqlite3_open(dbpath, &store) == SQLITE_OK) , but it has not made a difference.
    if (sqlite3_open_v2(dbpath, &store, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)
    {
        char *errMsg = NULL;
        const char *sql_stmt = [sql UTF8String];

        if (sqlite3_exec(store, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
        {
            NSLog(@"Failed to create table: %s", errMsg);
        }
        if (errMsg)
            sqlite3_free(errMsg);
    }
    else
    {
        NSLog(@"Failed to open/create database");
    }
}

Next, the insert (currently using the email address for the user ID):

INSERT INTO UsersTable (id,lastName,firstName,email) VALUES ("[email protected]","Smith","John","[email protected]")

I am using one selector for all database interactions, so the above text is passed here:

-(int)execSQL:(NSString *)statement
{
    NSLog(@"%@",statement);

    const char *insert_stmt = [statement UTF8String];
    sqlite3_stmt *stmnt;

    sqlite3_prepare_v2(store, insert_stmt, -1, &stmnt, NULL);
    int result = sqlite3_step(stmnt);

    sqlite3_finalize(stmnt);

    if (result != SQLITE_OK)
    {
        NSLog(@"Error: %s", sqlite3_errmsg(store));//This prints "Error: out of memory"
    }
    return result;
}

What am I doing wrong?

Solution

Your open routine is only creating/opening the database if the database doesn't exist. Your database probably already exists and thus your routine isn't even opening it.

Bottom line, if you try calling SQLite functions without opening the database, you will get the SQLITE_MISUSE return code (which indicates that the SQLite functions were not called in the right order) and the sqlite3_errmsg will return the cryptic "out of memory" error.

---

A couple of other, unrelated observations:

1. You really should check the return code of sqlite3_prepare as well:

   - (int)execSQL:(NSString *)statement
   {
       int result;
   
       NSLog(@"%@",statement);
   
       const char *insert_stmt = [statement UTF8String];
       sqlite3_stmt *stmnt;
   
       if ((result = sqlite3_prepare_v2(store, insert_stmt, -1, &stmnt, NULL)) != SQLITE_OK)
       {
           NSLog(@"%s: prepare failure '%s' (%d)", __FUNCTION__, sqlite3_errmsg(store), result);
           return result;
       }
   
       if ((result = sqlite3_step(stmnt)) != SQLITE_DONE)
       {
           NSLog(@"%s: step failure: '%s' (%d)", __FUNCTION__, sqlite3_errmsg(store), result);
       }
   
       sqlite3_finalize(stmnt);
   
       return result;
   }
   

   In my experience, many common development problems are related to the SQL itself, something that is identified by checking the return code of the sqlite3_prepare_v2 statement.

2. You really should not be building your SQL statement in a NSString. You open yourself to SQL injection attacks or, considering the more benign situation, just a SQL errors if someone's name has a quotation mark in it, e.g. The "Destroyer". You should be using ? placeholders and then use sqlite3_bind_xxx functions to bind the values. Something like:

   - (int)insertIdentifier:(NSString *)identifier
                  lastName:(NSString *)lastName
                 firstName:(NSString *)firstName
                     email:(NSString *)email
   {
       int result;
   
       const char *insert_stmt = "INSERT INTO UsersTable (id, lastName, firstName, email) VALUES (?, ?, ?, ?);";
       sqlite3_stmt *stmnt;
   
       if ((result = sqlite3_prepare_v2(store, insert_stmt, -1, &stmnt, NULL)) != SQLITE_OK)
       {
           NSLog(@"%s: prepare failure '%s' (%d)", __FUNCTION__, sqlite3_errmsg(store), result);
           return result;
       }
   
       if ((result = sqlite3_bind_text(stmnt, 1, [identifier UTF8String], -1, NULL)) != SQLITE_OK)
       {
           NSLog(@"%s: bind #1 failure '%s' (%d)", __FUNCTION__, sqlite3_errmsg(store), result);
           sqlite3_finalize(stmnt);
           return result;
       }
   
       if ((result = sqlite3_bind_text(stmnt, 2, [lastName UTF8String], -1, NULL)) != SQLITE_OK)
       {
           NSLog(@"%s: bind #2 failure '%s' (%d)", __FUNCTION__, sqlite3_errmsg(store), result);
           sqlite3_finalize(stmnt);
           return result;
       }
   
       if ((result = sqlite3_bind_text(stmnt, 3, [firstName UTF8String], -1, NULL)) != SQLITE_OK)
       {
           NSLog(@"%s: bind #3 failure '%s' (%d)", __FUNCTION__, sqlite3_errmsg(store), result);
           sqlite3_finalize(stmnt);
           return result;
       }
   
       if ((result = sqlite3_bind_text(stmnt, 4, [email UTF8String], -1, NULL)) != SQLITE_OK)
       {
           NSLog(@"%s: bind #4 failure '%s' (%d)", __FUNCTION__, sqlite3_errmsg(store), result);
           sqlite3_finalize(stmnt);
           return result;
       }
   
       if ((result = sqlite3_step(stmnt)) != SQLITE_DONE)
       {
           NSLog(@"%s: step failure: '%s'", __FUNCTION__, sqlite3_errmsg(store));
       }
   
       sqlite3_finalize(stmnt);
   
       return result;
   }
   

   You can then call this like so:

   [self insertIdentifier:@"[email protected]"
                 lastName:@"Smith"
                firstName:@"John"
                    email:@"[email protected]"];
   

3. As you can see, as you start writing code where you're appropriately checking each and every return value, binding each variable, etc., your SQLite code gets hairy awfully quickly. I'd suggest you contemplate looking at FMDB. It's a nice, thin wrapper around the SQLite functions, which greatly simplifies the exercise of writing SQLite code in Objective-C.