Forum

Hello,

  I posted on this subject yesterday but am unable to find it so I am assuming that it failed to post for some reason.  Hmm… I wonder if the update to an sqlite db failed… naw, not likely.  My apologies if this is a duplicate.  Anyway I am new to objective-c, iphone development and sqlite but not to relation database and sql so what I'm running into should have a simple answer and I must admit to being somewhat embarassed by having to post it but after struggling with it for 2 days and not getting anywhere I decided to swallow my pride and ask for help.  Anyway on to the description:

  I have an iphone app which utilizes an sqlite db table for keeping user profile informaton.  Since this is an iphone app it only needs a single row for the only user.  I have an option for the user to update the password for which I issue the following simple update command:

update UsersProfile set password='newpassword' where id=1'

My database table schema is very simple:

• id                      INTEGER PK
• loginRequired     VARCHAR(3)
• allowEmailPw     VARCHAR(3)
• lastLogin            VARCHAR(24)
• password           VARCHAR(32)
• emailAddr          VARCHAR(60)

The update appears to work just fine with SQLITE_DONE being returned by the step call, however, the database field is not chaged.  I am able to change the very same query to a select and it works fine.  I created this database file using the Firefox sqlite manager.  I am able to update the database with no problems from this interface and see the results from my programatic select query which proves that I am referencing the very same database file in both cases.  I checked the file protections on the database file and it is -rwxrwxrwx.  Just in case I set the same protection mask on the containing directory.   I have created a test project and added the following code to the appDidFinishLaunching section of the app delegate file to test this with the simplest case:

        sqlite3        *dbHandle;
        NSString *dbName = [[[NSBundle mainBundle] resourcePath]
            stringByAppendingPathComponent:@”PlayersNotebook1.sqlite”];
           
        NSString        *nsUpdateStr =  @”update UsersProfile set password='newpass' where id=1;”;
        NSString        *nsSelectStr    = @”select password from UsersProfile where id=1;”;

        static              sqlite3_stmt *init_stmt_update = nil;       
           
            sqlite3_open (
                [dbName UTF8String],
                &dbHandle);
                       
        if (sqlite3_prepare_v2 (
                dbHandle,   
                [nsUpdateStr UTF8String],
    //            [nsSelectStr UTF8String],   
                -1 ,
                &init_stmt_update,
                NULL
            ) != SQLITE_OK) {
           
                NSAssert1(
                    0,
                    @”Error: failed to prepare “
                    @”statement with message '%s'.”,
                    sqlite3_errmsg(dbHandle));

            }
   
    if (
        SQLITE_DONE   
    //    SQLITE_ROW
            != sqlite3_step
            (init_stmt_update))
       
        NSAssert1(0, @”Error while updating. '%s'”,
        sqlite3_errmsg(dbHandle));

// Uncomment this section when doing select and it will show the results in console

/*            NSString *test_out =
                [NSString stringWithUTF8String:(char *)
                    sqlite3_column_text (init_stmt_update,
                    0) ];     */
                   
                NSLog (test_out);

        sqlite3_reset(init_stmt_update);
        if (dbHandle) sqlite3_close(dbHandle);
        sqlite3_finalize(init_stmt_update);

While writing this up I did think of one further thing to try which is creating the db programatically instead of creating it with the Firefox add-on.  Perhaps there is some inconsistency between the versions of the file created.  I will do this now and post the results here.

  If anyone has any ideas, suggestions, comments etc they would be most appreciated.

Cheers,

-Tom

Well I finally solved the mystery for the most part.  It appears that the file that was getting updated was buried in the sandbox and that it was set for read-only access.  A clean targets did remove the offending file.  My updates using sqlite manager to the database were being reflected by my programmatic queries because the file was being reloaded each time the app ran.  I now recreated the database and table if it is not found in the documents directory as well as use the NSFileManager to set the permissions on the file.  Oh well, it's all uphill from here.

-Tom