CODEWITHSUNDEEP
objective-csqliteinsert
Grymjack
Grymjack

Reputation: 539

Sqlite insert statement will not put a record in the table

Everything in this function seems to work as expected, just no record actually shows up in the database table. If I enter the sql directly through the Firefox sqlite manager plugin, the record appears just fine. Probably something frustratingly stupid, any ideas?

- (IBAction)buttonInsertRecord:(id)sender
{
    // initializing db file access
    NSFileManager *fileMgr = [NSFileManager defaultManager];
    NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"KJV.sqlite"];

    // testing to see if database was found
    BOOL success = [fileMgr fileExistsAtPath:dbPath];
    if(!success)
    {
        // database not found
        labelStatus.text = @"database not found";
    }
    else
    {
        // database not found
        labelStatus.text = @"database found";
    }

    sqlite3_stmt *statement;
    sqlite3 *dbPointer;

    if (sqlite3_open(dbPath.UTF8String, &dbPointer) == SQLITE_OK)
    {
        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO users (user_name, user_id) VALUES ('testuser', null);"];

        const char *insert_stmt = [insertSQL UTF8String];

        sqlite3_prepare_v2(dbPointer, insert_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            labelStatus.text = @"added";
        }
        else
        {
            labelStatus.text = @"failed";
        }

        sqlite3_finalize(statement);
        sqlite3_close(dbPointer);
    }
}

Upvotes: 1

Views: 870

Answers (1)

janusfidel
janusfidel

Reputation: 8106

    //your database is not writable because its in the bundle.
    NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"KJV.sqlite"];

if you want a readable/writable database, you can do this:

    - (void) createEditableCopyOfDatabaseIfNeeded 
    {   
        BOOL success;
        NSFileManager *fileManager = [NSFileManager defaultManager];
        NSError *error;
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documentsDirectory = [paths objectAtIndex:0];
        NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"databasename.sqlite"];
        NSLog(@"writableDBPath == %@",writableDBPath);
        if ([fileManager fileExistsAtPath:writableDBPath])
        {
            NSLog(@"Database already exist");
        }
        else 
        {
            NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"databasename.sqlite"];
            success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
            if (!success) 
                NSLog(@"Failed to create writable database file with message '%@'.", [error localizedDescription]);
        }
    }

and heres how you add your data

- (void) addToDatabase
{
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documentsDirectory = [paths objectAtIndex:0];
        NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:@"databasename.sqlite"];

        if(sqlite3_open([databasePath UTF8String], &mDatabase_update) == SQLITE_OK)
        {

            NSString *sqlQuery = [NSString stringWithFormat: @"Insert into table 
             .
             .
             .
             .

Upvotes: 4

Related Questions