iOS sqlite insert problems

Question

Trying to solve this xcode problem since 2 weeks and didn't find any solutions on the web...

So, I developed an iphone apps with a tableview generated from a sqlite database (this part is ok). And I have a button that it insert an new line in my tableview (this part is ok) but I can't insert data in the sqlite database.

My function is (I did it from some tutorials):

- (void) insertMyObjectDataIntoDatabase: (MyObject*)myObj {
NSLog(@"Start Insertion");
//init DB
NSString *databaseName = @"bd_MyObjects.sqlite";
NSArray *documentsPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir =  [documentsPaths lastObject];//[documentsPaths objectAtIndex:0];
NSString *databasePath = [documentsDir stringByAppendingPathComponent:databaseName]; //NSLog(@"databasePath = %@.", databasePath);
BOOL success;
NSFileManager *FileManager = [NSFileManager defaultManager];
NSError *error;
success = [FileManager fileExistsAtPath:databasePath];
if (!success) {
    NSLog(@"ERROR dans l'existence du fichier DB.");
} else {
    //NSString *databasePathFromApp = [[NSBundle mainBundle] pathForResource:@"bd_MyObjects" ofType:@"sqlite"];
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath ] stringByAppendingPathComponent:databaseName ];
    NSLog(@"databasePathFromApp = '%@'.", databasePathFromApp);
    //success = [FileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:&error];
    success = [FileManager fileExistsAtPath:databasePath];
    if (!success) {
        NSLog(@"ERROR - Failed to create writable database file with message - ErrorMsg='%@'.", [error localizedDescription]);
        NSLog(@"ERROR - copyItemAtPath: \n%@\n toPath:\n%@", databasePathFromApp, databasePath);
    } else {
        NSLog(@"OK - Database created - ErrorMsg='%@'.", [error localizedDescription]);

        //[FileManager release];
        //to setup database object
        sqlite3 *database;
        //int statusSqlOpening = sqlite3_open([databasePath UTF8String], &database);
        int statusSqlOpening = sqlite3_open_v2([databasePath UTF8String], &database, SQLITE_OPEN_READWRITE, nil);
        NSLog(@"statusSqlOpening='%d' - '%s'.", statusSqlOpening, sqlite3_errmsg(database));
        if (statusSqlOpening != SQLITE_OK) {
            NSLog(@"ERROR opening the database: statusSqlOpening='%d' - ErrorMsg='%s'.", statusSqlOpening, sqlite3_errmsg(database));
        } else {
            NSLog(@"OK - database opened: statusSqlOpening='%d' - ErrorMsg='%s'.", statusSqlOpening, sqlite3_errmsg(database));
            static sqlite3_stmt *compiledStatement;
            NSString *statement = [[NSString alloc] initWithFormat:@"INSERT INTO tb_MyObjects (DateOfMyObject, Quantity1, Quantity2) VALUES ('%@', %d, %d)", myObj.DateOfMyObject, myObj.Quantity1, myObj.Quantity2,];
            const char *sqlstatement = [statement UTF8String];
            NSLog(@"reqString = %s",sqlstatement);
            int statusSqlExecution = sqlite3_prepare_v2(database, sqlstatement, -1, &compiledStatement, NULL);
            //int statusSqlExecution = sqlite3_exec(database, sqlstatement, NULL, NULL, NULL);
            if (statusSqlExecution != SQLITE_OK) {
                //NSLog(@"Error when executing the request: statusSqlOpening='%d' - statusSqlExecution='%d' - statusSqlStep='%d' - ErrorMsg='%s'.", statusSqlOpening, statusSqlExecution, statusSqlStep, sqlite3_errmsg(database));
                NSLog(@"ERROR when executing the request: statusSqlOpening='%d' - statusSqlExecution='%d' - ErrorMsg='%s'.", statusSqlOpening, statusSqlExecution, sqlite3_errmsg(database));
            } else {
                NSLog(@"OK - request executed: statusSqlOpening='%d' - statusSqlExecution='%d' - ErrorMsg='%s'.", statusSqlOpening, statusSqlExecution, sqlite3_errmsg(database));
                //sqlite3_step(compliedstatement) executes the statement...
                int statusSqlStep = sqlite3_step(compiledStatement);
                if (statusSqlStep != SQLITE_DONE) {
                    NSLog(@"ERROR by inserting: statusSqlStep='%d' - ErrorMsg='%s'", statusSqlStep, sqlite3_errmsg(database));
                } else {
                    NSLog(@"OK - Data successfully inserted: statusSqlStep='%d' - ErrorMsg='%s'.", statusSqlStep, sqlite3_errmsg(database));
                }
            }
            sqlite3_finalize(compiledStatement);
        }
        //Closing DB
        sqlite3_close(database);
    }
}
}

When I run this apps on the iphone simulator and click on my button, I have these logs:

2012-08-09 14:40:09.980 apps[4850:c07] Start Insertion
2012-08-09 14:40:09.981 myApps[4850:c07] databasePathFromApp = '/Users/adictic/Library/Application Support/iPhone Simulator/5.1/Applications/FE129301-39F9-4883-8714-125A6BF16FC3/myApps.app/bd_MyObjects.sqlite'.
2012-08-09 14:40:09.981 myApps[4950:c07] databasePath = '/Users/adictic/Library/Application Support/iPhone Simulator/5.1/Applications/FE129301-39F9-4883-8714-125A6BF16FC3/Documents/bd_MyObjects.sqlite'.
2012-08-09 14:40:09.981 myApps[4850:c07] OK - Database created - ErrorMsg='(null)'.
2012-08-09 14:40:09.982 myApps[4850:c07] statusSqlOpening='0' - 'not an error'.
2012-08-09 14:40:09.982 myApps[4850:c07] OK - database opened: statusSqlOpening='0' - ErrorMsg='not an error'.
2012-08-09 14:40:09.982 myApps[4850:c07] reqString = INSERT INTO tb_MyObjects (DateOfMyObject, Quantity1, Quantity2) VALUES ('2012-08-09 12:40:09 +0000', 150, 150)
2012-08-09 14:40:09.983 myApps[4850:c07] ERROR when executing the request: statusSqlOpening='0' - statusSqlExecution='1' - ErrorMsg='no such table: tb_MyObjects'.

So, I understand from these logs that :

my database is correctly created
my database is correctly opened
my request is correctly writen (I run it in sqlite manager and data is correctly inserted)
but the request is not executed, the table doesn't seem to exist?! -

I try to change the rights on the sqlite file (chmod 777 /Users/adictic/Library/Application Support/iPhone Simulator/5.1/Applications/FE129301-39F9-4883-8714-125A6BF16FC3/myApps.app/bd_MyObjects.sqlite) but it didn't have any effects.

I develop on Xcode 4.4.

Thank in advance for your helps.

trojanfoe · Accepted Answer · 2012-08-09 13:40:51Z

1

You can't insert into a database that is stored within your app bundle as the bundle is signed and you are effectively changing it.

You need to either:

1) Create an empty database file and schema using the sqlite command line tool, store that in the app bundle and then copy that into the documents folder on first use.

2) Create the empty database file and schema programmatic-ally within the documents folder on first use (this would be my preferred approach).

answered Aug 9, 2012 at 13:40

trojanfoe

123k23 gold badges219 silver badges249 bronze badges

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

2 Comments

user1587552 Over a year ago

Ok I understood: you can't write in a database that is not create by the project. So I have to revise all my design because my tableview was populate by an prefilled database...

trojanfoe Over a year ago

@user1587552 Yes - you are better off creating the schema programmatically as that will allow you to 'upgrade' the schema in future if it needs to change. For that purpose you should create a table where you can record the current schema version for this purpose.

NDY · Accepted Answer · 2012-08-09 13:44:55Z

Do something like that in a method, perhaps initDatabase or something: (the reason is the answer of trojanfoe, I just want to add some code snippets)

NSString *databaseName = @"bd_MyObjects.sqlite";

// Get the path to the documents directory and append the databaseName
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
NSString *databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

// Check if the SQL database has already been saved to the users phone, if not then copy it over
BOOL success;

// Create a FileManager object, we will use this to check the status
// of the database and to copy it over if required
NSFileManager *fileManager = [NSFileManager defaultManager];

// Check if the database has already been created in the users filesystem
success = [fileManager fileExistsAtPath:databasePath];

// If the database already exists then return without doing anything
if(success) return;

// If not then proceed to copy the database from the application to the users filesystem

// Get the path to the database in the application package
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

// Copy the database from the package to the users filesystem
[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

do not forget to accept trojanfoe's answer if you think it is correct and vote it up

Owen Hartnett · Accepted Answer · 2012-08-09 13:52:24Z

0

Your error message is telling you that whatever database you opened doesn't have the table tb_MyObjects in it. You've commented out the line that actually copies the database from your bundle to the directory. Note that calling open on a nonexistent database will create a new empty one (without the table tb_MyObjects in it). If you've done this once, then you're playing with an empty table, because you commented out the code to copy a fresh one.

answered Aug 9, 2012 at 13:52

Owen Hartnett

5,9352 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

iOS sqlite insert problems

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related