CODEWITHSUNDEEP
iphoneiossqlite
user1587552
user1587552

Reputation: 13

iOS sqlite insert problems

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 :

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.

Upvotes: 1

Views: 803

Answers (3)

Owen Hartnett
Owen Hartnett

Reputation: 5935

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.

Upvotes: 0

NDY
NDY

Reputation: 3557

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];

Upvotes: 0

trojanfoe
trojanfoe

Reputation: 122458

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).

Upvotes: 1

Related Questions