Yannis P. Yannis P. - 1 year ago 53
iOS Question

ios populating sqlite database error, final entries are not written

UPDATED QUESTION, check the end of the post

I am making an iPhone app and I want when the user first lunches the app, for the app to create a new database and populate it with the data of a plist file.

Here is my view did load code:

//get database path
NSString *symptomDatabasePath = [self symptomsDatabasePath];

//check if database exists and initialize if it doesn't
if(![[NSFileManager defaultManager] fileExistsAtPath:symptomDatabasePath])
[self createAndPopulateDatabase];

My symptomDatabasePath function:

//get the path of the symptoms database
- (NSString *) symptomsDatabasePath
NSArray *pathsArray = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [pathsArray objectAtIndex:0];
return [documentsDirectory stringByAppendingPathComponent:@"symptoms.sqlite"];

And finally my createANdPopulateDatabase function:

- (void) createAndPopulateDatabase
//get query to insert symptoms into database
NSArray *pathsArray = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [pathsArray objectAtIndex:0];
NSString *databaseDataFile = [documentsDirectory stringByAppendingPathComponent:@"symptomsDatabaseList.plist"];
NSString *symptomData = [NSString stringWithContentsOfFile:databaseDataFile encoding:NSUTF8StringEncoding error:NULL];

sqlite3 *database;
if (sqlite3_open([[self symptomsDatabasePath] UTF8String], &database)!=SQLITE_OK)
NSAssert(0, @"Failed to open database");

NSString *createTableQuery = @"CREATE TABLE IF NOT EXISTS tbl_symptoms (symptomCode varchar(15) NOT NULL PRIMARY KEY,title varchar(255) NOT NULL,shortTitle varchar(255) NOT NULL,inclusions varchar(255) NOT NULL,exclusions varchar(255) NOT NULL,symptomCategory varchar(255) NOT NULL);";

char *errorMsg;
//create new table
if(sqlite3_exec(database, [createTableQuery UTF8String], NULL, NULL, &errorMsg)!=SQLITE_OK)
NSAssert(0, @"Error creating table: %s", errorMsg);

NSString *insertQueryString = @"INSERT INTO tbl_symptoms (symptomCode, title, shortTitle, inclusions, exclusions, symptomCategory) VALUES ";
//array containing the seperate symptom data
NSArray *symptomArray = [symptomData componentsSeparatedByString:@"),"];
sqlite3_stmt *statement;
//nsmutable array with all queries
NSMutableArray *queryArray = [[NSMutableArray alloc] init];
//loop through the aray with symptoms build queries and store them in an array
for(int i=0; i<[symptomArray count]; i++)
//create query string with insert command
NSMutableString *insertString = [[NSMutableString alloc] initWithString:insertQueryString];
//append the specific symptoms data
[insertString appendString:[symptomArray objectAtIndex:i]];
[insertString appendString:@");"];
[queryArray insertObject:insertString atIndex:i];

//insert data into database

for(int i=0; i<[queryArray count]; i++)
const char *insertChar = [[queryArray objectAtIndex:i] UTF8String];

if(sqlite3_prepare_v2(database, insertChar, -1, &statement, nil)!=SQLITE_OK)
NSAssert(0, @"Error filling table: %s", errorMsg);
if (sqlite3_step(statement)!=SQLITE_DONE)
NSAssert(0, @"Error inserting data into database: %s", errorMsg);
//finalize changes

My code is a bit messy because i have been trying various solutions. Anyway I have the data I want to input written like this in my phlist file:

('Z27', 'Fear of a social problem', 'Fear of a social problem', 'concern about/fear of having a social problem', 'if the patient has a social problem, code the problem ', 'Social problems’),
('Z28', 'Limited function/disability (Z)', 'Limited function/disability (Z)', '', '', 'Social problems’),
('Z29', 'Social problem NOS', 'Social problem NOS', 'environmental problems; malingering', '', 'Social problems’

there are 320 such entries in my file, these are the last 3 because they are the ones I am having trouble with.

The app copies all the other 317 entries, but not these 3. And I know it is missing a ) at the end, but that's because my code appends it on every loop.

I get a SIGABRT error and the following error code:

"Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Error filling table: (null)'"

Can anyone help me? I probably have done something wrong with sqlite3, though I don't understand why it only doesn't copy the last 3 entries.

Also earlier it wouldn't copy only the last entry, but after I copy pasted it around to see if it was a problem with the entry itself, it started not copying the last 3 :S.

Any help will be greatly appreciated


So it turns out I was getting the errors cause I was using NSAssert. Now I am using NSLogs to log my errors, and the app launches correctly, but I still get the error when copying the final 3 entries.

According to my logs moth the sqlite3_prepare_v2 and the sqlite3_step functions fail, but ONLY for the final 3 entries :S

Rob Rob
Answer Source

The problem is that you have smart quotes. For example, I see you have

..., 'Social problems’), ...

Note, you're starting that with a standard apostrophe, but finishing it with a closing single quote. If you look very carefully, they look different.

Replace those smart quotes with standard apostrophe and that should fix the immediate problem.

I'd suggest you log the SQL and carefully examine it. Also, when the sqlite3_prepare_v2 fails, you are not logging the appropriate error message. You can do something like:

if (sqlite3_prepare_v2(database, insertChar, -1, &statement, nil)!=SQLITE_OK) {
    NSLog(@"prepare failure: %s", sqlite3_errmsg(database));
    NSAssert(0, @"Error filling table");