Problem With sqlite3 update Command

[ravindra1989]

Can Any one tell me what is wrong with this code?

+ (BOOL)updateEntryTable
{
	sqlite3 *database;
	
	sqlite3_stmt *update_statement;
	
	
	NSString *stmt1 = [NSString stringWithFormat:@"UPDATE entryTab SET ebody='done very well' WHERE eid='1'"];
	
	const char *sql = [stmt1 UTF8String];
	
	 if (sqlite3_open([[Utiliy databaseLocation] UTF8String], &database) == SQLITE_OK) 
	 {
	     if (sqlite3_prepare_v2(database, sql, -1, &update_statement, NULL) != SQLITE_OK) 
	     {
	          NSLog(@"Failed!!!!No databAse");
	          return NO;
	      }
	
		 int success = sqlite3_step(update_statement);
		 if (success == SQLITE_ERROR) 
		 {
			 NSLog(@"Error: failed to insert into the database with message %@", sqlite3_errmsg(database));
			 return NO;
		 }
	 }
	sqlite3_finalize(update_statement);
	sqlite3_close(database);
	return YES;
}

The insertion operation is successfully exicuted....While Updating It is exe uted with no error but Update operation is not done in the real database.

Thanks in advance.....

[DevMike]

Hi,

eID is your PrimeKey ?
Try your update statement without ' ' .

Cheers,
DevMike

Good sqllite tutorial:
SQLite Tutorial ? Updating data | iPhone SDK Articles

[dustbyter]

I'd take this approach to determining why it fails.

1. Take the SQL code as it is and run it in your database directly, does it update correctly? Also, if your PK is an integer, then you don't require the single quotes around it.

2. If the update query works correctly in DB; ensure the database you are reviewing is the correct database file that the app is using.

I didn't see any obvious issues in your code that you provided.

[ravindra1989]

Thank you DevMike.... eID is not a primary key and is of type String. I tried without single quote, but its not working..

[ravindra1989]

Thank You for kind response....
I Already tried those approaches. Same Query Is working Fine directly....But through code it is not possible...
Insertion is perfectly executed..

[ravindra1989]

Please Can Anyone help me to resolve this.......?....

[SundialSoft]

Originally Posted by ravindra1989

Can Any one tell me what is wrong with this code?

+ (BOOL)updateEntryTable
{
	sqlite3 *database;
	
	sqlite3_stmt *update_statement;
	
	
	NSString *stmt1 = [NSString stringWithFormat:@"UPDATE entryTab SET ebody='done very well' WHERE eid='1'"];
	
	const char *sql = [stmt1 UTF8String];
	
	 if (sqlite3_open([[Utiliy databaseLocation] UTF8String], &database) == SQLITE_OK) 
	 {
	     if (sqlite3_prepare_v2(database, sql, -1, &update_statement, NULL) != SQLITE_OK) 
	     {
	          NSLog(@"Failed!!!!No databAse");
	          return NO;
	      }
	
		 int success = sqlite3_step(update_statement);
		 if (success == SQLITE_ERROR) 
		 {
			 NSLog(@"Error: failed to insert into the database with message %@", sqlite3_errmsg(database));
			 return NO;
		 }
	 }
	sqlite3_finalize(update_statement);
	sqlite3_close(database);
	return YES;
}

The insertion operation is successfully exicuted....While Updating It is exe uted with no error but Update operation is not done in the real database.

Thanks in advance.....

I can't spot what's wrong. I made up my update statement based on the existing add statement from a demo I downloaded. Mine does not look exactly like yours. I have some sqlite3_bind_.... commands, here's my update function for what it's worth, hope it helps:-

//my attempt at update routine
- (void) updatesqldb {
	
	if(updateStmt == nil) {
		const char *sql = "update weddingBudget Set budDesc = ?, budBudgetAmt = ?,            budActualAmt = ?, budNote = ? Where STKEY = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &updateStmt, NULL) != SQLITE_OK)
			NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
	}
	
	sqlite3_bind_text(updateStmt, 1, [budDesc UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_double(updateStmt, 2, [budBudgetAmt doubleValue]);
	sqlite3_bind_double(updateStmt, 3, [budActualAmt doubleValue]);
    sqlite3_bind_text(updateStmt, 4, [budNote UTF8String],-1,SQLITE_TRANSIENT);
	
    int returnValue = -1;

    
    sqlite3_bind_int(updateStmt, 5, STKEY);
    
    if(returnValue != SQLITE_OK)
 //       NSLog(@"Not OK!!!");
    
    if(SQLITE_DONE != sqlite3_step(updateStmt))
        NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(database));
    
    sqlite3_reset(updateStmt);

this does work

[ravindra1989]

Thank you.....
My code is successfully executed.
The Error is not with this code.Before updating i am checking for record exist or not. that time i am not closing the database. So that the updating is not executed....