cannot create database table and insert in iOS sqlite3

Question

I am new in iOS development. I am trying to do a simple todo list app. I am using xocde 8 and objective-C language . I tried several tutorial but could not create the database table. Here is my code.

-(void)createOrOpenDB{
    printf("createOrOpenDB: into this function \n");

    NSArray *docsDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *dirPaths = [docsDir objectAtIndex:0];

    dbPathString = [[NSString alloc] initWithString: [dirPaths stringByAppendingPathComponent:@"person.db"]];

    NSFileManager *fileManager = [NSFileManager defaultManager];

    char *err;
    printf("createOrOpenDB: into this function before if statement \n");
    if(![fileManager fileExistsAtPath:dbPathString]){
        const char *dbPath = [dbPathString UTF8String];
            printf("createOrOpenDB: into this functions 1st if statement \n");
        //create db here
        if(sqlite3_open(dbPath, &personDB) ==SQLITE_OK){
            const char *sql_stnt = "CREATE TABLE IF NOT EXISTS PERSONS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, AGE INTEGER)";
            if(sqlite3_exec(personDB, sql_stnt, NULL, NULL, &err) !=SQLITE_OK){
                printf("Failed to create table\n");
            }
            sqlite3_close(personDB);
            printf("createOrOpenDB: database table created\n");
        }
    }
}

when i press the add button , its not giving me any error. but its not adding any data. NSLog in not working in xcode 8 . so i did printf instead . and my code breaks before the if statement . can anybody tell me what i am doing wrong?

This creates table if the database doesn't exist. But what if the database does exist, but the table is not there (e.g. perhaps you had an earlier rendition that didn't have this `create table` logic)? I'd look in the documents folder and see if there's a database there or not. If there is, I'd delete it and try again. — Rob, Oct 12 '16 at 07:15
thats right. my table was already created. but the problem is , when i press the add button , its not adding anything in the database. — EA Rashel, Oct 12 '16 at 07:47

KAR · Accepted Answer · 2016-10-12T07:54:07.707

do like this,

+(DBManager*)getSharedInstance{
    if (!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        [sharedInstance createDB];
    }
    return sharedInstance;
}

-(BOOL)createDB{
    NSString *docsDir;
    NSArray *dirPaths;
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains
    (NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString:
                    [docsDir stringByAppendingPathComponent: @"student.db"]];
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == YES)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "create table if not exists studentsDetail (regno integer primary key, name text, department text, year text)";
            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg)
                != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create table");
            }
            else
            {
                NSLog(@"Table Created Successfully");
            }
            sqlite3_close(database);
            return  isSuccess;
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
        sqlite3_finalize(statement);
    }
    return isSuccess;
   // NSLog(@"database %@", isSuccess);
}

For saving the data in this table,

- (BOOL) saveData:(NSString*)registerNumber name:(NSString*)name
       department:(NSString*)department year:(NSString*)year;
{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *insertSQL = [NSString stringWithFormat:@"insert into studentsDetail (regno,name, department, year) values (\"%ld\",\"%@\", \"%@\", \"%@\")",(long)[registerNumber integerValue], name, department, year];
                                const char *insert_stmt = [insertSQL UTF8String];
                                sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
                                if (sqlite3_step(statement) == SQLITE_DONE)
                                {
                                    return YES;
                                }
                                else {
                                    return NO;
                                }
                                sqlite3_reset(statement);
                                }
                                return NO;
                                }

It will create table every time when table does not exist.

Hope t will help you.

I think this is backwards. The purpose of this routine is to create the database and table if it doesn't exist. If there is no database there, this won't create it... — Rob, Oct 12 '16 at 07:16
Also, `sqlite3_finalize` doesn't belong here. You only do that if you've prepared a statement, which you haven't here... — Rob, Oct 12 '16 at 07:19
thanks for the reply. but my database table is already created. but when i press the add button, its not adding the info in the database table. — EA Rashel, Oct 12 '16 at 07:46
You've copied your answer from [here](http://stackoverflow.com/a/22653366/3588973) without attribution. — Droppy, Oct 12 '16 at 08:21
@Droppy bro check that code and mine. I have implemented this code from one tutorial in my project so its not copied code. Search on web and you will find this code in one of the tutorial available on web. This code in my project. And the purpose is to solve the problem — KAR, Oct 12 '16 at 08:24
Why don't you add a link in your answer? The link will likely provide more information than your answer does. — Droppy, Oct 12 '16 at 08:25
@Droppy at this time I am not remember the tutorial so I have added code from my project. — KAR, Oct 12 '16 at 08:26

cannot create database table and insert in iOS sqlite3

1 Answers1