sqlite db in iphone app error: no such table

Question

I have a problem when pushing data to my sqlite database in iphone application. here's how I create database:

-(IBAction)createButtonPressed:(id)sender
{
    NSLog(@"createButtonPressed: called");
    status.text=[status.text stringByAppendingString:@"create button pressed.\n"];
    NSString *docsDir;
    NSArray *dirPaths;

    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = [dirPaths objectAtIndex:0];

    // Build the path to the database file
    self.databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"database.sqlite"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        status.text=[status.text stringByAppendingString:@"creating database... "];
        status.text=[status.text stringByAppendingFormat:@"path: %@\n\n", databasePath];

    const char *dbpath = [databasePath UTF8String];

        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            status.text=[status.text stringByAppendingString:@"database opened successfully\n"];

            char *errMsg;
            const char *sql_stmt;
            NSString *sqlStatement=[[NSString alloc] init];

            /*
             CREATING JOB TABLE
             */
            sqlStatement=@"";
            [sqlStatement stringByAppendingString:@"CREATE TABLE IF NOT EXISTS JOB "];
            [sqlStatement stringByAppendingString:@"(ID INTEGER PRIMARY KEY AUTOINCREMENT, "];
            [sqlStatement stringByAppendingString:@"NUMBER TEXT, "];
            [sqlStatement stringByAppendingString:@"DATE_LOADING INTEGER, "];
            [sqlStatement stringByAppendingString:@"DATE_DEPARTURE INTEGER, "];
            [sqlStatement stringByAppendingString:@"DATE_UNLOADING INTEGER, "];
            [sqlStatement stringByAppendingString:@"ORIGIN INTEGER, "];
            [sqlStatement stringByAppendingString:@"DESTINATION INTEGER, "];
            [sqlStatement stringByAppendingString:@"SUPPLIER INTEGER, "];
            [sqlStatement stringByAppendingString:@"RAILROAD INTEGER, "];
            [sqlStatement stringByAppendingString:@"FOREMAN INTEGER, "];
            [sqlStatement stringByAppendingString:@"WEATHER INTEGER, "];
            [sqlStatement stringByAppendingString:@"COMMENT TEXT, "];
            [sqlStatement stringByAppendingString:@"MODIFY_STATUS INTEGER)"];

            sql_stmt = [sqlStatement UTF8String];

            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                status.text=[status.text stringByAppendingString:@"failed to create JOB table\n"];
            }
            else
            {
                status.text=[status.text stringByAppendingString:@"JOB table created\n"];
            };


        status.text=[status.text stringByAppendingString:@"closing database...\n"];
        sqlite3_close(database);

    }
    else
    {
        status.text=[status.text stringByAppendingString:@"failed to open/create database\n"];
    };
}
else
{
    status.text=[status.text stringByAppendingString:@"database exists "];
    status.text=[status.text stringByAppendingFormat:@"path: %@\n\n", databasePath];
};

[status scrollRangeToVisible:NSMakeRange([status.text length], 0)];

}

everything is with creating is OK. BUT! when I try to push data to my table I get an error: "no such table JOB" pushing data code:

NSLog(@"populateButtonPressed: called");
status.text=[status.text stringByAppendingString:@"populate button pressed.\n"];

NSFileManager *filemgr = [NSFileManager defaultManager];

if ([filemgr fileExistsAtPath: databasePath ] == YES)
{
    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        status.text=[status.text stringByAppendingString:@"database opened successfully\n"];

        char *errMsg;
        const char *sql_stmt;
        NSString *sqlStatement=[[NSString alloc] init];

        /*
         POPULATING JOB TABLE
         */
        sqlStatement=@"INSERT INTO JOB (NUMBER, DATE_LOADING, DATE_DEPARTURE, DATE_UNLOADING, ORIGIN, DESTINATION, SUPPLIER, RAILROAD, FOREMAN, WEATHER, COMMENT, MODIFY_STATUS) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        sql_stmt = [sqlStatement UTF8String];
        sqlite3_stmt *stmt;
        sqlite3_prepare_v2(database, sql_stmt, -1, &stmt, nil);
        sqlite3_bind_text(stmt, 1, "number", -1, SQLITE_STATIC); //number
        sqlite3_bind_int(stmt, 2, 100); //date_loading
        sqlite3_bind_int(stmt, 3, 101); //date_departure
        sqlite3_bind_int(stmt, 4, 102); //date_unloading
        sqlite3_bind_int(stmt, 5, 103); //origin
        sqlite3_bind_int(stmt, 6, 104); //destination
        sqlite3_bind_int(stmt, 7, 105); //supplier
        sqlite3_bind_int(stmt, 8, 106); //railroad
        sqlite3_bind_int(stmt, 9, 107); //foreman
        sqlite3_bind_int(stmt, 10, 108); //weather
        sqlite3_bind_text(stmt, 11, "commentstring", -1, SQLITE_STATIC); //comment
        sqlite3_bind_int(stmt, 12, 1);

        if (sqlite3_step(stmt)!=SQLITE_DONE)
        {
            status.text=[status.text stringByAppendingString:@"WARNING!!! Could not step (execute) stmt\n"];
            status.text=[status.text stringByAppendingFormat:@"error: %@\n", [NSString stringWithUTF8String:sqlite3_errmsg(database)]];
        }
        else
        {
            status.text=[status.text stringByAppendingString:@"it seems like stmt step (execute) was successfull"];
        };

        if(sqlite3_finalize(stmt)==SQLITE_DONE)
        {
            status.text=[status.text stringByAppendingString:@"finalizing stmt was successfull\n"];
        }
        else
        {
            status.text=[status.text stringByAppendingString:@"WARNING!!! failed finalizing stmt\n"];
        };


        status.text=[status.text stringByAppendingString:@"closing database...\n"];
        sqlite3_close(database);

    }
    else
    {
        status.text=[status.text stringByAppendingString:@"failed to open database\n"];
    };
}
else
{
    status.text=[status.text stringByAppendingString:@"database does not exist. you need to create it.\n"];
};

[status scrollRangeToVisible:NSMakeRange([status.text length], 0)];

the database exists at path and opens successfully. iOS5 ipad application, uses ARC and libsqlite3.dylib, I'm running my application in simulator.

sqlite3_prepare_v2(database, sql_stmt, -1, &stmt, nil); just after this row I get an error: "no such table JOB" — Oleg
– Oleg, Commented Mar 23, 2012 at 13:53

Praveen-K · Accepted Answer · 2012-03-23 13:59:15Z

1

is your db path is correct? why do not you check in your terminal first?

> sqlite3 <your_db_path>

> show tables;

answered Mar 23, 2012 at 13:59

Praveen-K

3,4012 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Oleg Over a year ago

lifeinlagsworkstation:Documents lifeinlags$ sqlite3 database.sqlite SQLite version 3.6.12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> show tables    ...>

Oleg Over a year ago

file database.sqlite exists there. But it seems like no tables where created. Why? what's wrong with my code? (you can see creating tables code on top of my question)

Praveen-K Over a year ago

I doubt it. well i will just suggest you to spare 2 mins on bit.ly/iUPld0. i do not know because i hate to use plain sqlite3 cause of lots of pain where there is many sql wrapper class around. bit.ly/GFOS1Q

Oleg Over a year ago

what wrapper do you recommend?

Collectives™ on Stack Overflow

sqlite db in iphone app error: no such table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related