VJVJ VJVJ - 6 days ago 5
iOS Question

Data are not inserting into sqliteDB iOS

I have created a table(REPORTDATA) in existing database. I am trying to insert the values in to table. But it is not inserted. I am using the following code.

dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = dirPaths[0];
databasePath = [docsDir stringByAppendingPathComponent:@"Album.db"];
const char *dbpath = [databasePath UTF8String];
NSString *insertSQL;
if (sqlite3_open(dbpath, & albumDB) == SQLITE_OK)
{
int rowCount = [self GetArticlesCount];
rowCount += 1;
NSString *tempcount = [NSString stringWithFormat:@"%d", rowCount];
insertSQL = [NSString stringWithFormat: @"INSERT INTO REPORTDATA (Num, Json) VALUES ('%@','%@')", tempcount, tempcount];
char *errmsg=nil;
if(sqlite3_exec(albumDB, [insertSQL UTF8String], NULL, NULL, &errmsg)==SQLITE_OK)
{
}
else
{
NSLog(@"Error Message is =%s",errmsg);
}
}
sqlite3_close(albumDB);


Get number of rows in a table:

- (int) GetArticlesCount
{
int count = 0;
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = dirPaths[0];
databasePath = [docsDir stringByAppendingPathComponent:@"Album.db"];
if (sqlite3_open([self.databasePath UTF8String], &albumDB) == SQLITE_OK)
{
const char* sqlStatement = "SELECT COUNT(*) FROM REPORTDATA";
sqlite3_stmt *statement;
if( sqlite3_prepare_v2(albumDB, sqlStatement, -1, &statement, NULL) == SQLITE_OK )
{
//Loop through all the returned rows (should be just one)
while( sqlite3_step(statement) == SQLITE_ROW )
{
count = sqlite3_column_int(statement, 0);
}
}
else
{
NSLog( @"Failed from sqlite3_prepare_v2. Error is: %s", sqlite3_errmsg(albumDB) );
}
sqlite3_finalize(statement);
sqlite3_close(albumDB);
}
return count;
}


I am getting

Error Message is =(null).

Rob Rob
Answer

I'd suggest examining the actual return value of sqlite3_exec:

int rc;
char *errmsg = NULL;

if ((rc = sqlite3_exec(albumDB, [insertSQL UTF8String], NULL, NULL, &errmsg)) == SQLITE_OK) {
    NSLog(@"Insert succeeded");
} else {
    NSLog(@"Insert failed: %s (%ld)", errmsg, (long)rc);
    if (errmsg) sqlite3_free(errmsg);
}

You report that it returned 21, which is SQLITE_MISUSE. This is typical if you called the API functions in the wrong order (e.g. performing some SQL after the database was closed).

The GetArticlesCount method is reopening a database (which is already open), replacing the albumDB variable with a new sqlite3 * pointer. Then, GetArticlesCount is closing the database, and when you return to the first method, the albumDB pointer is now referencing a closed database handle. Thus subsequent SQL calls will generate SQLITE_MISUSE.

To avoid this problem, I would advise against having each function that performs SQL from opening and closing the database. Open the database once and then have all subsequent SQLite calls use that one sqlite3 * pointer.

Comments