Iphone SQlite - Populate Table from Text Field

Question

Hi,

I am trying to prepopulate my SQlite Table from a Text File - alltough it is compiling fine, no rows will be inserted:

NSLog(@"Insert Table for English");


    char *errorMsg;

    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath ] stringByAppendingPathComponent:@"english.sql"];

    NSLog(@"DefaultPath: %@", defaultDBPath);

    NSString *sql = [[[NSString alloc] initWithContentsOfFile:defaultDBPath  
                                           encoding:NSUTF8StringEncoding 
                                              error:NULL] autorelease];

    if (sqlite3_exec(database,[sql UTF8String],NULL,NULL, &errorMsg) != SQLITE_OK) {
        NSAssert1(0, @"Error loading update file: %s", errorMsg);
    }

    NSLog(@"I should have written something");

This is how my text file looks like:

BEGIN TRANSACTION;
INSERT INTO PARTNER(branche) VALUES('Choose music for the Ceremony');
COMMIT;

I create the Database here:

-(void)createDatabase {

NSUserDefaults *userSETTINGS = [NSUserDefaults standardUserDefaults];

NSLog(@"HUHU - schau ma mal: Database = %d", [userSETTINGS integerForKey:@"Database"]);


if ([userSETTINGS integerForKey:@"Database"] == 0) {


    if (sqlite3_open([[self dataFilePath] UTF8String], &database) != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"Failed to open database");
    }

    char *errorMsg;
    //sqlite3_stmt *statement;
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS PARTNER (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, branche TEXT, company TEXT, name TEXT, phone TEXT, email TEXT, price INT, notes TEXT, done TEXT);";
    if (sqlite3_exec(database, [createSQL UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert1(0, @"Error createing table: %s", errorMsg);
    }

    NSUserDefaults *userSETTINGS = [NSUserDefaults standardUserDefaults];
    [userSETTINGS setInteger:1 forKey:@"Database"];

    NSLog(@"HUHU - schau ma mal: Database = %d", [userSETTINGS integerForKey:@"Database"]);

[self fillDatabase];

} else {

    NSLog(@"Database wurde schon angelegt");
    [self readDatabase];

}

}

The Log File tells my, that defaultDBPath is:

DefaultPath: /Users/sl/Library/Application Support/iPhone Simulator/User/Applications/0BE2EDC6-F070-43BE-9666-310257B495B9/WeddingPlanner.app/english.sql

What am I missing? The DB is correct, the Path is OK but nothing will be written...

Thanks for your answers, help, advice... :-)

BR,

Stefan

Answer 1

The correct syntax for committing transaction is

COMMIT TRANSACTION

Answer 2

Hi Kenny, thanks again for your continous help...

I have changed as follows:

if (sqlite3_open_v2([[self dataFilePath] UTF8String], &database, SQLITE_OPEN_READWRITE, nil) != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"Failed to open database");
    }

    NSLog(@"Insert Table for English");


    char *errorMsg;

    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath ] stringByAppendingPathComponent:@"e.sqlite"];

    NSLog(@"DefaultPath: %@", defaultDBPath);

    NSString *sql = [[[NSString alloc] initWithContentsOfFile:defaultDBPath  
                                           encoding:NSUTF8StringEncoding 
                                              error:NULL] autorelease];

    if (sqlite3_exec(database,[sql UTF8String],NULL,NULL, &errorMsg) != SQLITE_OK) {
        NSAssert1(0, @"Error loading update file: %s", errorMsg);
    }

    NSLog(@"Hier hätte ich schreiben sollen");

My Log looks like:

2010-02-21 20:37:41.666 WeddingPlanner[61189:207] Nil on start 2010-02-21 20:37:41.668 WeddingPlanner[61189:207] HUHU - schau ma mal: Database = 1 2010-02-21 20:37:41.669 WeddingPlanner[61189:207] Database wurde schon angelegt 2010-02-21 20:37:41.670 WeddingPlanner[61189:207] Abfrage: SELECT SUM(price) FROM PARTNER 2010-02-21 20:37:41.671 WeddingPlanner[61189:207] Nil on start 2010-02-21 20:37:41.672 WeddingPlanner[61189:207] Die gesetzte Sprache: (null) 2010-02-21 20:37:41.682 WeddingPlanner[61189:207] Abfrage: SELECT SUM(price) FROM PARTNER 2010-02-21 20:37:41.683 WeddingPlanner[61189:207] Nil on start 2010-02-21 20:37:41.685 WeddingPlanner[61189:207] HUHU - schau ma mal: (null) 2010-02-21 20:37:41.685 WeddingPlanner[61189:207] calculateDateDifference 2010-02-21 20:37:41.691 WeddingPlanner[61189:207] Das Datum DAY = -3338 wait_fences: failed to receive reply: 10004003 2010-02-21 20:37:56.597 WeddingPlanner[61189:207] English 2010-02-21 20:37:56.599 WeddingPlanner[61189:207] Die gespeicherte Sprache: EN 2010-02-21 20:37:56.600 WeddingPlanner[61189:207] Insert Table for English 2010-02-21 20:37:56.602 WeddingPlanner[61189:207] DefaultPath: /Users/sl/Library/Application Support/iPhone Simulator/User/Applications/D79C5BB0-B4E1-4AD7-8B9B-E4493D82F64D/WeddingPlanner.app/e.sqlite

So everthing looks fine - DB will be created, DB can be opened (because there is no failure issue), I am able to insert (view GUI) data into the table "partner", but I am not able to insert data via file...

BR,

Stefan