PDA

View Full Version : problem with SQLite DB, insert, update DB




goldenlife
Apr 6, 2011, 05:07 AM
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
Apr 22, 2012, 02:26 PM
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
Apr 22, 2012, 05:23 PM
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.