getting error while trying to create sqlite databa

Question

Hi in my Application I'm trying to create a local database to store values with primary key auto increment value but its giving error like.

2014-06-19 14:34:28.499 brt[2363:80b] *** Assertion failure in -[listviewpoliticalViewController createTable:withField1:withField2:withField3:withField4:], /Users/madhavadudipalli/Desktop/ ios projects/brt/brt/listviewpoliticalViewController.m:37

2014-06-19 14:34:28.501 brt[2363:80b] *** Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Could not create table'

I'm getting error like above lines please tell me how to resolve this issue.

My database create code in .h file.

    -(NSString *) filePath;
    -(void)openDB;

    -(void) createTable: (NSString *) tableName
        withField1:(NSString *) field1
        withField2:(NSString *) field2
        withField3:(NSString *) field3
        withField4:(NSString *) field4;

Database code in .m file.

     -(void) createTable: (NSString *) tableName
         withField1:(NSString *) field1
         withField2:(NSString *) field2
         withField3:(NSString *) field3
        withField4:(NSString *) field4;
   {
     char *err;
     NSString *sql =[NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ( Id integer PRIMARY KEY,'%@' TEXT, '%@' TEXT, '%@' TEXT);",tableName,field1,field2,field3];
   if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
       sqlite3_close(db);
       NSAssert(0, @"Could not create table");
      }else{
       NSLog(@"Table Created");
      }

    }
    -(NSString *) filePath{
       NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
     return [[paths objectAtIndex:0] stringByAppendingPathComponent:@"bp.sqlite"];
   }
   -(void)openDB{
    if (sqlite3_open([[self filePath] UTF8String], &db)!=SQLITE_OK) {
       sqlite3_close(db);
       NSAssert(0, @"Database failed to open");

    }else{
      NSLog(@"database opened");
    }
  }

I have used the above code to create the database in sqlite please tell me where I'm doing wrong in the above code how to resolve this issue I have been struck here for long time please help me out.

Thanks.

Answer 1

You have some errors in formatting the SQL statement. You shouldn't use '%@'. just use %@

Here is a Working Example:

@interface SQLiteManager ()

@property (strong, nonatomic) NSString *databasePath;
@property (nonatomic) sqlite3 *myDataBase;

@end

@implementation SQLiteManager

- (void)createDatabase
{
    // get the path to our data base
    NSArray *directories = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *doctumentsDirectory = [directories lastObject];
    self.databasePath = [[NSString alloc] initWithString:[doctumentsDirectory stringByAppendingPathComponent:@"/yourDataBaseName.db"]];

    NSFileManager *fileManager = [NSFileManager defaultManager];

    // create DB if it does not already exists
    if (![fileManager fileExistsAtPath:self.databasePath]) {

        const char *dbPath = [self.databasePath UTF8String];

        if (sqlite3_open(dbPath, &_myDataBase) == SQLITE_OK) {

            char *errorMsg;
            const char *sql_statement = "CREATE TABLE IF NOT EXISTS SOME_TABLE_NAME (ID INTEGER PRIMARY KEY AUTOINCREMENT, FIRST_NAME TEXT, LAST_NAME TEXT)";

            if (sqlite3_exec(_myDataBase, sql_statement, NULL, NULL, &errorMsg) != SQLITE_OK) {

                [self errorCreatingTable:[NSString stringWithFormat:@"failed creating table. ERROR:%s", errorMsg]];
            }

            sqlite3_close(_myDataBase);

        } else {

            [self errorCreatingTable:@"failed openning / creating table"];
        }
    }
}

- (void)errorCreatingTable:(NSString *)errorMsg
{
    NSLog(@"%@", errorMsg);
}