how to update sqlite3 database in iphone

Question

i want to update my sqlite database but i cannot find the way to do it,following is the code:

 const char *sqlStatement = "UPDATE frame SET fileExist = '1' WHERE name='$variable'";

if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
 NSLog(@"successupdate");
}

from the above code i want my table update where the name is equal to $variable name;how to achieve this?

Answer 1

You're nearly there.

const char *sqlStatement = "UPDATE frame SET fileExist = '1' WHERE name=?";

sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL);

sqlite3_bind_text(sqlStatement, 1, variable, -1, SQLITE_TRANSIENT);

int success = sqlite3_step(sqlStatement);

sqlite3_reset(sqlStatement);

Note that preparing the SQL statement only tells SQLite what the statement looks like. It's sqlite3_bind_text that applies the variable to the SQL statement and the sqlite3_step line that actually runs it.

Answer 2

I am using these lines of code to insert data. But yet it is not workable for me. I used the same technique you told in this post. But acquiring same result means nothing. I am trying to explain you in code as well that where i am facing difficulties.

- (void)dataInsertion {
    NSString *query;
    NSString *querySelect;
    NSString *queryInsert;
    NSArray *asdasd;
    NSArray *asd;
    Sqlite *sqliteDatabase =[[Sqlite alloc] init];

    /***************************Database Enterance is working here for temporary base*************************/

/******After Applying these lines of code, i am able to get the result in console not in my database. But as soon as i restart my application, I have lost the data in console as well.******/
    myDB=[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Test.sqlite"];
    Sqlite *sqliteDatabase =[[Sqlite alloc] init];
    [sqliteDatabase open:myDB];

    queryInsert=[NSString stringWithFormat:@"INSERT INTO Test VALUES('value1','Value2','Value3');"];    
    NSLog(@"Query is , %@",queryInsert);
    [sqliteDatabase executeNonQuery:queryInsert];

    query=[NSString stringWithFormat:@"Select * FROM Test"];
    NSLog(@"Query is , %@",query);
    asdasd=[sqliteDatabase executeQuery:query];
    NSLog(@"Array contains %@",asdasd);
    [asdasd release];
    /*****************************************************/

    /***************Other Way*************/
/**************By Applying these line, My Application get crashed on lines  [sqliteDatabase executeNonQuery:queryInsert];*******/
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"SohaibDb.sqlite"];

    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    BOOL success;
    success = [fileManager fileExistsAtPath:path];
    if (success)
    {
        NSLog(@"Writable database already exists.");
        /***** My control is coming here****/       
    }
    else
    {   
    /******** Control is not coming here*********/
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Test.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:path error:&error];
        if (!success) 
        {
            NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
        }       
    }

    /******* Here it is giving me warning that Argument2 is from incompatible pointer type*****/
    if (sqlite3_open([path UTF8String], &sqliteDatabase) == SQLITE_OK)
    {
        /***** My control is coming here ******/
        queryInsert=[NSString stringWithFormat:@"INSERT INTO Test VALUES('value1','Value2','Value3');"];    
        NSLog(@"Query is , %@",queryInsert);
        [sqliteDatabase executeNonQuery:queryInsert];  //On this line Application is Crashing without giving an error

        query=[NSString stringWithFormat:@"Select * FROM Test"];
        NSLog(@"Query is , %@",query);
        asdasd=[sqliteDatabase executeQuery:query];
NSLog(@"Array contains %@",asdasd);
        [asdasd release];       
    }
    else
    {
          /********** Control is not coming here ********/
        NSLog(@"Database is not Opening.");
    }
    /*******************************************/

    [sqliteDatabase commit];
    [sqliteDatabase close]; 
}

Any little help will be appreciated greatly in this regards.

Answer 3

FMDB is a nice wrapper for SQLite statements. It takes care of most of the prepare/bind/step/reset drudgery. I highly recommend it for any project that needs a DB, but doesn't want to use Core Data.