SQLite in iPhone

Question

I have an app which calls web services and persists the results to an sqlite db, the idea being that when the device is offline, these persisted results are retrieved. This all works ok, but if I rebuild the project, the database seems to be wiped and I can't get any data out which I previously put in. Can anyone explain what is going on here please? I am using the 4.3 simulator and Xcode 4.

I am also using the FMDatabase library...

-(NSMutableArray*)retrieveDataSourceOfType:(NSString*)type{
 FMDatabase *db = [self getDB];
 NSMutableArray *items = [[NSMutableArray alloc] initWithObjects:nil];
 if([type isEqualToString:@"AlertItem"]){
 FMResultSet *results = [db executeQuery:@"SELECT * FROM AlertItem"];
 while([results next]){
 AlertItem *tmp = [[AlertItem alloc] init];
 tmp.Description = [results stringForColumn:@"Description"];
 tmp.NumItems = [results intForColumn:@"NumItems"];
 tmp.ModuleGroup = [results stringForColumn:@"ModuleGroup"];
 tmp.StaffID = [results intForColumn:@"StaffID"];
 tmp.Status = [results intForColumn:@"Status"];
 [items addObject:tmp];
 }
 }
 [db close];
 [db release];
 return items;
}
-(FMDatabase*)getDB{
NSString *path = [[[NSBundle mainBundle] resourcePath] 
stringByAppendingString:@"/XXXX.sqlite"];
 NSLog(@"%@", path);
 NSFileManager *fm = [NSFileManager defaultManager];
 if([fm fileExistsAtPath:path]){
 FMDatabase *db = [[FMDatabase databaseWithPath:path]retain];
 NSLog(@"%@", path);
 if(![db open]){
 [db release];
 return nil;
 }
 else{
 return db;
 }
 }
}

Thanks.

• deanWombourne: show us some code; we're not psychic!
• Serhii Mamontov: This won't work on device, because you don't have rights to modify signed bundle. Move your database in Documents for example.
• shaw2thefloor: Sorry, which Documents? Is this some iPhone directory?
• Serhii Mamontov: Yes, please check documents or wait till I'll get to work and post one row of code. But I think kind people's will do that for me.
• Serhii Mamontov: Example below. It shows how to get documents folder.

Answer 1

I think this depends where you put your database file. If it is a resource I think it will be overwitten every time you deploy your project. You should copy/create your database in the application cache folder.

Here is how to get the path:

 NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES); 
 NSString *cacheDirectory = [paths objectAtIndex:0]; 

Here are other directories you could use.

I would try NSFileManager for file copying. I'm not sure about these I only use sqlite for autocomplete, no insert/update.

Try fileExistsAtPath to see fi the file exists and if not you should copy over the empty one from your resources using copyItemAtPath

• shaw2thefloor: Ah that sounds like it might work. How do I copy the file to the application cache in Xcode?

Answer 2

If you test only on simulator, maybe you make sometimes "Clean All Targets". If this is not that case, than pleas show how you open database connection? Maybe you open temporary database and it will be removed as soon as connection is closed.