cannot add data to local DB sqlite IOS

Question

Here is my code:

For creating the DB, in my ViewDidLoad:

 NSString *docsDir; NSArray *dirPaths; dirPaths=NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES); docsDir = [dirPaths objectAtIndex:0]; databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"contacts.db"]]; NSFileManager *filemgr=[NSFileManager defaultManager]; if ([filemgr fileExistsAtPath:databasePath]==NO) { NSLog(@"Creating DB"); const char *dbpath=[databasePath UTF8String]; if (sqlite3_open(dbpath, &contactDB)==SQLITE_OK) { char *error_msg; const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, URL TEXT, EMAIL TEXT, CODE TEXT, FULL TEXT)"; if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &error_msg) != SQLITE_OK) { NSLog(@"Failed to create table"); } sqlite3_close(contactDB); } else { NSLog(@"Failed to open/create database"); } } else { NSLog(@"DB exists"); }

I can see the output message "Creating DB"

Then where I have to add my data there I do have:

sqlite3_stmt *statement; const char *dbpath = [databasePath UTF8String]; if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) { NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO CONTACTS (url, email, code, full) VALUES (\"%@\", \"%@\", \"%@\", \"%@\")", url_2, mail, keycode,full_2 ]; const char *insert_stmt = [insertSQL UTF8String]; sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL); if (sqlite3_step(statement) == SQLITE_DONE) { NSLog(@"Contact added"); } else { NSLog(@"Failed to add contact"); } sqlite3_finalize(statement); sqlite3_close(contactDB); } else { NSLog(@"PROBLEM - CONTACT NOT ADDED"); }

and I see the "PROBLEM - CONTACT NOT ADDED" message.

Any help?

Rob · Accepted Answer · 2016-04-25 19:01:03Z

The problem is that you're opening your database in the NSDocumentationDirectory, not the NSDocumentDirectory.

A couple of unrelated observations:

If sqlite3_open() fails, you should look at the numeric return code which will help you diagnose the source of the problem. You can look up the values in the sqlite3.h header file.
An unrelated observation to your code sample: You should check your sqlite3_prepare_v2() return code as SQL errors are generally identified there, not at sqlite3_step(). If it returned something other than SQLITE_OK, then immediately call sqlite3_errmsg() to identify precisely why it failed.
You should not use stringWithFormat to add values for INSERT SQL statement. Instead, use ? placeholders and then use sqlite3_bind_XXX() functions to bind values to those SQL values. As it is, if any of your inserted values contained a quotation mark, your SQL would fail (and would be susceptible to SQL injection attacks).

Learning Programming · Accepted Answer · 2014-03-18 11:23:14Z

you made a minor mistake as shown by @Rob...

Instead of

dirPaths=NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES);

you should use

dirPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

Following is the corrected code,

NSString *docsDir; NSArray *dirPaths; dirPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); docsDir = [dirPaths objectAtIndex:0]; databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"contacts.db"]]; NSFileManager *filemgr=[NSFileManager defaultManager]; if ([filemgr fileExistsAtPath:databasePath]==NO) { NSLog(@"Creating Database..."); const char *dbpath=[databasePath UTF8String]; if (sqlite3_open(dbpath, &contactDB)==SQLITE_OK) { char *error_msg; const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, URL TEXT, EMAIL TEXT, CODE TEXT, FULL TEXT)"; if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &error_msg) != SQLITE_OK) { NSLog(@"Failed to create table"); } sqlite3_close(contactDB); } else { NSLog(@"Failed to open/create database"); } } else { NSLog(@"DB exists"); }

You can insert data like this,

sqlite3_stmt *statement; const char *dbpath = [databasePath UTF8String]; if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) { NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO CONTACTS (url, email, code, full) VALUES (\"%@\", \"%@\", \"%@\", \"%@\")", url_2, mail, keycode,full_2 ]; const char *insert_stmt = [insertSQL UTF8String]; sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL); if (sqlite3_step(statement) == SQLITE_DONE) { NSLog(@"Contact added"); } else { NSLog(@"Failed to add contact"); } sqlite3_finalize(statement); sqlite3_close(contactDB); } else { NSLog(@"PROBLEM - CONTACT NOT ADDED"); }

Thank You...!!!

Collectives™ on Stack Overflow

cannot add data to local DB sqlite IOS

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related