Inserting a data into SQLite (using FMDB) doesn't work on device?

Question

I am a newbie here. I am not able to insert any data into my SQLite database when running on my device. I am using the below piece of code to connect SQLite. It works perfect in my simulator but doesn't work on my iPad device. Can anyone help please? Thank you.

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0]; 
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"DADatabase1.sqlite"];
NSLog(@"%@", writableDBPath);

FMDatabase* db = [FMDatabase databaseWithPath:writableDBPath];

/*if ([fileManager fileExistsAtPath:writableDBPath] == NO) {
    NSString *resourcePath = [[NSBundle mainBundle] pathForResource:@"DADatabase1.sqlite" ofType:@"sqlite"];
    [fileManager copyItemAtPath:resourcePath toPath:writableDBPath error:nil];
}*/
[db open];
[db traceExecution];
[db beginTransaction];
BOOL success = [db executeUpdate:[NSString stringWithFormat:@"insert into PatientDetails (patAge) values (?)",patAge.text]];
if(!success)
{
    NSLog(@"Query not success!");
}
else {
    NSLog(@"Query success!");
}
//NSString *query = [NSString stringWithFormat:];
[db commit];
[db close];

you also have to replace ? by %@ because ? is for prepared Statements — jussi, Aug 06 '12 at 07:48
Okay but it does work perfectly on the simulator. So, do you think this causes the problem? Thanks — GenieWanted, Aug 06 '12 at 08:34

score 2 · Accepted Answer · edited Nov 28 '12 at 08:12

2

Did you put your Database with your Tables in your Projects resources folder? You have to copy your database file to your Apps Document folder.

+ (BOOL)initDB {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"sqlite.db"]; 

NSFileManager *fileManager = [NSFileManager defaultManager];

if (![fileManager fileExistsAtPath: path])
{
    NSString *bundle =  [[ NSBundle mainBundle] pathForResource:@"sqlite" ofType:@"db"];
    [fileManager copyItemAtPath:bundle toPath:path error:nil];
    return YES;
}
    return NO;
}

edited Nov 28 '12 at 08:12

Nam Vu

5,669
7
58
90

answered Aug 06 '12 at 07:38

jussi

2,166
1
22
37

100 2012-08-06 13:10:31.806 Pro_sample[317:707] Anesthesia, Local 2012-08-06 13:10:36.751 Pro_sample[317:707] Count of Sample Array: 1 2012-08-06 13:10:36.758 Pro_sample[317:707] /var/mobile/Applications/453590BF-DCA9-4768-BDBA-75E1087E6CE8/Documents/DADatabase1.sqlite 2012-08-06 13:10:36.765 Pro_sample[317:707] Query not success! – GenieWanted Aug 06 '12 at 07:45
I added the database file manually into the Documents folder. Do I have to do anything programmatically? – GenieWanted Aug 06 '12 at 07:45
Yes you have to do it programmatically with my Code, because Files from your Simulator Document Folder will no be copied to your Device – jussi Aug 06 '12 at 07:47
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"DADatabase1.sqlite"]; NSFileManager *fileManager = [NSFileManager defaultManager]; NSLog(@"%@", writableDBPath); – GenieWanted Aug 06 '12 at 09:59
if (![fileManager fileExistsAtPath:writableDBPath]) { NSString *bundle = [[ NSBundle mainBundle] pathForResource:@"DADatabase1" ofType:@"sqlite"]; NSLog(@"%@", bundle); [fileManager copyItemAtPath:bundle toPath:writableDBPath error:nil]; } – GenieWanted Aug 06 '12 at 10:00
I have added the above code in my project and set a breakpoint at the if statement. It doesn't go inside the if statement. So, that means the database file is inside the documents folder. Correct? Console NSLog shows:
/var/mobile/Applications/453590BF-DCA9-4768-BDBA-75E1087E6CE8/Documents/DADatabase1.sqlite
– GenieWanted Aug 06 '12 at 10:01
Same behavior on an iOS Device? – jussi Aug 06 '12 at 10:04
Well, it doesn't care about the if statement. No matter whether it is on device or simulator. When running on simulator, this is what NSLog shows:/Users/raghuvardhan/Library/Application Support/iPhone Simulator/5.1/Applications/91B32953-7C6A-4C0C-8099-C19BCAC71991/Documents/DADatabase1.sqlite 2012-08-06 15:57:40.947 Pro_sample[1755:f803] Query success! – GenieWanted Aug 06 '12 at 10:29
When running on iPad device, this is what I get: /var/mobile/Applications/453590BF-DCA9-4768-BDBA-75E1087E6CE8/Documents/DADatabase1.sqlite 2012-08-06 15:59:54.449 Pro_sample[763:707] Query not success! – GenieWanted Aug 06 '12 at 10:30
Seems strange! Hard time here with this.. :( I really appreciate your help. Thanks BTW – GenieWanted Aug 06 '12 at 10:30
NSLog(@"%@",[db lastErrorMessage]); Whats the Error Message? – jussi Aug 06 '12 at 10:33
2012-08-06 16:05:35.218 Pro_sample[811:707] Database Path: /var/mobile/Applications/453590BF-DCA9-4768-BDBA-75E1087E6CE8/Documents/DADatabase1.sqlite 2012-08-06 16:05:35.222 Pro_sample[811:707] **bold** Error: out of memory – GenieWanted Aug 06 '12 at 10:35
lastErrorMessage says "Out of memory".. Puzzling?? – GenieWanted Aug 06 '12 at 10:38
Remove: [db open]; [db traceExecution]; [db beginTransaction]; [db commit]; Add: db = [FMDatabase databaseWithPath:path]; db.traceExecution = YES; db.logsErrors = YES; [db open]; – jussi Aug 06 '12 at 10:40
Did so... Here is what Log screen shows: 2012-08-06 16:14:42.298 Pro_sample[849:707] executeUpdate: insert into PatientDetails (patAge) values ('78') 2012-08-06 16:14:42.302 Pro_sample[849:707] DB Error: 1 "no such table: PatientDetails" 2012-08-06 16:14:42.307 Pro_sample[849:707] DB Query: insert into PatientDetails (patAge) values ('78') 2012-08-06 16:14:42.311 Pro_sample[849:707] DB Path: /var/mobile/Applications/453590BF-DCA9-4768-BDBA-75E1087E6CE8/Documents/DADatabase1.sqlite – GenieWanted Aug 06 '12 at 10:45
1

Worked perfectly after copying the DB file into Documents folder. Thank you so much for your help. – GenieWanted Aug 07 '12 at 10:24

score 1 · Answer 2 · answered Aug 06 '12 at 07:35

1

The sample code provided doesn't have a CREATE TABLE statement. The INSERT has nowhere to insert to. Did you create the table manually (using the sqlite3 command line tool) while you were developing in the simulator?

answered Aug 06 '12 at 07:35

Chris

3,445
3
22
28

I in-fact created the table using SQLite Manager (Firefox add-on). It just works fine in the simulator. I can't get why it doesn't do anything when on device. Am I missing anything in terms of database file path specification? – GenieWanted Aug 06 '12 at 07:43
I'm not familiar with the Firefox plug ing, but presumably there were some steps in which you specified that a PatientDetails table should exist and which columns it should have? Looking at your code, the database is expected to be in the Documents folder, which is fine and correct. But that folder and its contents aren't (to my understanding) copied across to the real device. Your code needs to create the table for itself. To avoid the need to check for first run, you can just use `CREATE TABLE IF NOT EXISTS ...` and the right thing will happen. – Chris Aug 06 '12 at 07:49

score 0 · Answer 3 · answered Aug 06 '12 at 07:41

0

NOTE that:

File names in simulator is not case sensitive, but in device is case sensitive. Make sure your file name is exactly same.

answered Aug 06 '12 at 07:41

Ria

10,237
3
33
60

Inserting a data into SQLite (using FMDB) doesn't work on device?

3 Answers3

Linked