iOS Sqlite3 SQLITE_ERROR

Question

I have a function in an iOS app which reads in some data from a database. The function is extremely simple:

-(void) readCategories {
    sqlite3 *database;

    if([[NSFileManager defaultManager] fileExistsAtPath:databasePath]){
        if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
            const char *sqlStatement = "SELECT * FROM Categories ORDER BY name COLLATE NOCASE ASC";
            sqlite3_stmt *compiledStatement;
            int errorCode = sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL);

            if(errorCode == SQLITE_OK) {
                while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                    [categories addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)]];
                }
            } else {
                 NSLog(@"Error reading categories. Code: %d, message: '%s'", errorCode,sqlite3_errmsg(database));
            }
            sqlite3_finalize(compiledStatement);
            sqlite3_close(database);
        } else {
            NSLog(@"Error opening DB");
        }
    } else {
        NSLog(@"DB does not exist.");
    }
}

The problem is that the errorCode is always SQLITE_ERROR which according to the documentation is: "SQL error or missing database". The message given is: 'no such table: Categories'

Now, if I look at the database file on my computer, the table is clearly there. I can also run exactly the same query on it and it works correctly.

Does anyone have any ideas on what is going wrong?

Thanks.

are you sure that the file at databasePath is a correct database file? — Omar Abdelhafith
– Omar Abdelhafith, Commented Jul 3, 2012 at 12:30
you should use sqlite3_errmsg (sqlite.org/c3ref/errcode.html) to see what's wrong. SQLITE_ERROR can also mean, that your SQL is wrong. — Stefan
– Stefan, Commented Jul 3, 2012 at 12:33
plese print error code NSLog(@"error = %d",sqlite3_errcode(database)); before sqlite3_finalize(compiledStatement); — Deepesh
– Deepesh, Commented Jul 3, 2012 at 13:09
use this line (sqlite3_prepare_v2(database, [sqlStatement cStringUsingEncoding:NSUTF8StringEncoding], -1, &compiledStatement, NULL) — Deepesh
– Deepesh, Commented Jul 3, 2012 at 13:22

Rob · Accepted Answer · 2014-04-15 22:16:38Z

3

If you ever ran this code prior to adding the [[NSFileManager defaultManager] fileExistsAtPath:databasePath] check, the standard sqlite3_open will have created a blank database for you if it didn't find one. So,

Reset your Simulator via "Reset Content and Settings..." on the Simulator's menu. This will clear out any extraneous database files that may have been created in the past. (If you're doing this on a device, remove the app and then reinstall it.)
The sqlite3_open command will create blank database if it's not found. To be safe, in the future, use sqlite3_open_v2 instead, which will never create a blank database:
```
if (sqlite3_open_v2([databasePath UTF8String], &database, SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK) {
    // handle opening error here
}
```
By using sqlite3_open_v2, it will ensure that a database will never be created during the open process.
If you're still having problems, navigate to your simulator's directory on your machine, namely "~/Library/Application Support/iPhone Simulator". (You might have to unhide your Library folder by typing the following command in the terminal window:
```
chflags nohidden ~/Library
```
Try examining the database there (rather than the version in your Xcode project) and see if the database has all of the tables you expected it to.
If after doing all of this, you're not seeing your database being copied to your simulator/device, make sure you've set your target's Build Phases settings so that the database is include via "Copy Bundle Resources".
As others have suggested, whenever you get SQLITE_ERROR, always check out the error details via something like NSLog(@"%s SQLITE_ERROR '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(database), sqlite3_errcode(database));.

edited Apr 15, 2014 at 22:16

answered Jul 3, 2012 at 14:08

Rob

441k74 gold badges851 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dale Myers Over a year ago

1) I am using a device and can only use a device due to hardware constraints. However, I have removed everything as this was indeed happening. 2) Done. 3) As I said, using hardware, so this won't work unfortunately. 4) Yeah this is all correct on my project. 5) Yup, using that now.

Rob Over a year ago

@Velox if the database wasn't being copied over, it's undoubtedly point #4, which will make sure it's part of the bundle. You may also, then, want to have the app copy this file to the Documents folder so that any user updates won't get blown away by future app upgrades.

Dale Myers · Accepted Answer · 2012-07-03 15:09:10Z

0

So the bug was that it wasn't copying over the database file. I still have no idea why it wasn't. I fortunately have the luxury of being able to create the database file programatically. Doing that solved the problem. Not sure why the file wouldn't copy however.

answered Jul 3, 2012 at 15:09

Dale Myers

2,8214 gold badges29 silver badges50 bronze badges

Collectives™ on Stack Overflow

iOS Sqlite3 SQLITE_ERROR

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related