problem with SQLite DB, insert, update DB

Discussion in 'iOS Programming' started by goldenlife, Apr 6, 2011.

  1. goldenlife, Apr 6, 2011
    Last edited by a moderator: Apr 6, 2011

    macrumors newbie

    Joined:
    Oct 2, 2010
    #1
    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:

    Code:
    -(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,
     
  2. macrumors newbie

    Joined:
    Apr 22, 2012
    #2
    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.
     
  3. macrumors 68030

    PhoneyDeveloper

    Joined:
    Sep 2, 2008
    #3
    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.
     

Share This Page