Unable to update database

Question

I'm having trouble updating my database. I believe it's due to it possibly being read only, however I am unsure. I created my database in my console, and the code below adds the database to the project folder rather than referencing it externally. Here is the code to transfer the file to the project folder

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
 {

  databaseName = @"databasenew.sql";


/* copy over to phone code */

   NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,          NSUserDomainMask,YES) objectAtIndex:0]; 
   NSString *sqlFile =[documentDirectory stringByAppendingPathComponent:@"databasenew.sql"];

if (![[NSFileManager defaultManager] fileExistsAtPath:sqlFile]) {
    NSString *bundleSql = [[NSBundle mainBundle] pathForResource:@"databasenew" ofType:@"sql"];


    NSError *error = nil;
    [[NSFileManager defaultManager] copyItemAtPath:bundleSql toPath:sqlFile error:&error];

    if (error) {
        NSLog(@"Could not copy json file: %@", error);
    }
    else{
        NSLog(@"yea");
    }
}
else{

    NSLog(@"transfer complete");
}
NSString *bundleSql = [[NSBundle mainBundle] pathForResource:@"databasenew" ofType:@"sql"];


databasePath = bundleSql;


self.window.rootViewController = self.viewController;
[self.window makeKeyAndVisible];
return YES;

}

Here is the code where I try to update

-(void)parser:(NSXMLParser *)parser didStartElement:(NSString *)elementName namespaceURI:(NSString *) namespaceURI qualifiedName:(NSString *)qName
     attributes: (NSDictionary *)attributeDict
    {

if ([elementName isEqualToString:@"Message"])
{
    NSLog(soapResults);  
    NSString *convertString = soapResults;
    check = [convertString intValue];
    NSLog(@"user is");
    NSLog(user);
    if(check > 0){

        databaseName = @"databasenew.sql";
        NSString *bundleSql = [[NSBundle mainBundle] pathForResource:@"databasenew" ofType:@"sql"];
        databasePath = bundleSql;

        sqlite3 *database;

        if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
            const char *sqlStatement = "UPDATE people set user = 'munch'";
            sqlite3_stmt *compiledStatement;
            if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
                NSLog(@"success");
                NSLog(soapResults);
                BOOL success = sqlite3_step(compiledStatement);
                if (success != SQLITE_DONE) {
                    BOOL myBool=YES; 
                    NSLog([NSString stringWithFormat:@"My Bool value : %d",success]); 

                } 
            }else{
                NSLog(@"the fail ispreparing something");

                NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
            }


            sqlite3_finalize(compiledStatement);

        }else{
            NSLog(@"fail");
        }

        sqlite3_close(database);
        NSString *string = [NSString stringWithFormat:@"%d", check];
        NSLog(string);
        NSLog(@"lolololol"); 
        check = 0;
        [self Bootup];

    }else{
             UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Problem!" message:@"Incorrect Username or Password"
                                                           delegate:self cancelButtonTitle:@"Ok" otherButtonTitles:@"Yes", nil];
        [alert show];
        [alert release];
    }
  //  gotoContent = checkConfirm;
}



if( [elementName isEqualToString:@"ValidateUserResponse"])
{
    if(!soapResults)
    {
        soapResults = [[NSMutableString alloc] init];
        NSLog(soapResults);
        NSLog(@"above is soap validate user");
    }

    recordResults = TRUE;
}

}

I've done my NDlog debugging and it says "success" as it should if the code for updating is successful, however the database itself does not update. What is the reason for this?

Answer 1

-(BOOL) someUpdateFunction { BOOL updatedSuccessfully = NO;

// Open the database.
if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
    NSString *updateQuery = [[NSString alloc] initWithFormat:@"UPDATE people SET user = 'terror' WHERE user = 'greg'"];
    const char *query = [updateQuery UTF8String];
    [updateQuery release];

    sqlite3_stmt *statement; 
    int res = (sqlite3_prepare_v2( database, query, -1, &statement, NULL));

    if(res == SQLITE_OK) {
        int result = sqlite3_step(statement);
        if(result == SQLITE_DONE) {
            updatedSuccessfully = YES;
            sqlite3_finalize(statement);
        }else {
            NSLog(@"Failed with error code: %d", result);
        }
    }else {
        NSLog(@"Failed to prepare the update query with error code: %d", res);
    }
}else {
    NSLog(@"Failed to open the database");
}

sqlite3_close(database);
//NSLog(updatedSuccessfully);
//NSLog(@"The value of the bool is %@\n", updatedSuccessfully);
return updatedSuccessfully;

}

I was supplied this in the chat room. Thanks lads.

Remember to copy the DB over to the device else. You can find the DB path with this code:

- (NSString *) dataFilePath:(NSString *) dbName { 
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); 
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:dbName];
return dbPath;

}

-(BOOL)buildtheDB{ BOOL updatedSuccessfully = NO;

NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES) objectAtIndex:0]; 
NSString *sqlFile =[documentDirectory stringByAppendingPathComponent:kDatabaseFileName];

if ([[NSFileManager defaultManager] fileExistsAtPath:sqlFile] == NO) {
    NSString *bundleSql = [[NSBundle mainBundle] pathForResource:@"database" ofType:@"sqlite"];

    NSError *error = nil;

    BOOL databaseCopied = [[NSFileManager defaultManager] copyItemAtPath:bundleSql toPath:sqlFile error:&error];
    if (databaseCopied == NO) {
        if (error) {
            NSLog(@"Could not copy database file with error: %@", [error localizedDescription]);
        }
    }else {
        NSLog(@"Database copied successfully");
    }
}
else{
    NSLog(@"Database already copied");
}

return updatedSuccessfully;

}

Answer 2

You are forgeting some code for update statement

//the binding part is missing in your code , you need to write after Prepare statement.

sqlite3_bind_text(compiledStatement, 1, [string UTF8String], -1, SQLITE_TRANSIENT);

if(SQLITE_DONE != sqlite3_step(compiledStatement))
NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(database));

sqlite3_reset(compiledStatement);

Via this site

This will resolve your error.