iOS problem with SQLite DB, insert, update DB

[goldenlife]

Hi All,

I have created DB, and all related functions in Delegate.m file,
Now my problem is when I call functions in AppDidLaunch function then it works perfectly, I can Insert and Update my DB.
But when I try to call this function from another view, I cant update my DB, while debugging it works perfectly, it takes correct DB Path , but can not get updated.

Any idea?
My functions are like this:

-(void) CreateDatabase 
{
	
	BOOL success;
	NSFileManager *fileManager = [NSFileManager defaultManager];
	
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDirectory = [paths objectAtIndex:0];
	NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sql"];
	success = [fileManager fileExistsAtPath:writableDBPath];
	
	if(!success)
	{
		[fileManager createFileAtPath:writableDBPath contents:nil  attributes:nil];
	}
	
	
	if(sqlite3_open([writableDBPath UTF8String],&db)==SQLITE_OK)
	{		
				
		const char* sql1="CREATE TABLE IF NOT EXISTS userInfo('UserName'CHAR(50) PRIMARY KEY,'FirstName' CHAR(56),'LastName' CHAR(56),'Email' CHAR(100),'Password' CHAR(56),'P_Start_Date'char(50),'HMD_cycle'char(10),'HMD_Plast'char(10),'HMD_Pstart'char(10),'P_days'char(10),'O_days'char(10))";
		retVal=sqlite3_exec(db,sql1,NULL,NULL,&str2);
		
		sqlite3_close(db);
		[self Set_Database];
		
		
	}
}

-(void)Set_Database 
{	
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDirectory = [paths objectAtIndex:0];
	NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sql"];
	
	if(sqlite3_open([path UTF8String],&db)==SQLITE_OK)
	{
		
		const char *sql2;
		sql2 =[[NSString stringWithFormat:@"INSERT INTO userInfo VALUES('hello_user','rajni','pathak','rajni@gmail.com','rajni123','4/30/11','25','5','2','4','5');"]cStringUsingEncoding:NSUTF8StringEncoding];
		retVal=sqlite3_exec(db,sql2,NULL,NULL,&str2);
		
		sqlite3_close(db);
	}
}

-(BOOL) updateDB
{
	sqlite3_stmt *update_statement;
	
	NSString *sqlStr = [NSString stringWithFormat:@"UPDATE userInfo SET EMail= 'rags123new@gmail.com' WHERE UserName ='hello_user'"];
	
	const char *sql = [sqlStr UTF8String];

	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDirectory = [paths objectAtIndex:0];
	NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sql"];
	
	if(sqlite3_open([writableDBPath UTF8String],&db)==SQLITE_OK)
	{		
		
	if (sqlite3_prepare_v2(db, sql, -1, &update_statement, NULL) != SQLITE_OK) {
		UIAlertView *alert = [[UIAlertView alloc] initWithTitle:NSLocalizedString(@"DatabaseNotAvailable", @"") message:[NSString stringWithUTF8String:sqlite3_errmsg(db)]
													   delegate:self cancelButtonTitle:@"OK" otherButtonTitles: nil];
		[alert show];	
		[alert release];
		return NO;
	}
	
	int success = sqlite3_step(update_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(db));
		return NO;
	}
	
	sqlite3_finalize(update_statement);
	}
	return YES;
}

from other view I can only retrieve values from database, but when I tried to insert value or update , then my DB can't get updated. How can I solve this issue? pls anybody help

thanks,

 

[giorgioviko]

I have the same issue

Hi!

it's long since you posted this problem and I'm having the same problem that you. I don't know why my SQLite database is not updating with the data i'm inserting. The INSERT query is well formed, I've tried to execute it directly on a SQLite DB client and it works.

Could someone help me?

Thanks in advance.

Jorge.

 

[PhoneyDeveloper]

The usual problem is that the path to the db isn't correct or you're trying to modify a db that's inside the app bundle.