Read-write example of SQLite in IOS

iOS
#1

Hi all,

I am a looking for advice of good tutorial or code example of SQLite in IOS to read and write in the database. Some people is talking about Core-data in iphone community, but I always prefer cross-platform solutions and SQlite looks android likes too.

I know Roxlu had made ofxSQLite, but it seems not working in IOS. I had tried with no success.

if I am getting working it, I will post the code.

Thanks.

#2

I managed to get write and read a SQlite, but mysteriously data is only storing temporally. I am using a nice wrapper library FMDB ( https://github.com/ccgus/fmdb ). I need to manage to store the data when app is close and open again with all data in. I think, I am doing something wrong about it. I imagine is something related with open the database file: “Datalist.sqlite”.

  
 NSArray *searchPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);  // Get the path to the database file  
    NSString *documentPath = [searchPaths objectAtIndex:0];  
    NSString *databasePath = [documentPath stringByAppendingPathComponent:@"Datalist.sqlite"];  
    NSLog(@"d/b path: /%@", databasePath);  
	  
    char * errmsg = nil;     
	  
    NSFileManager *fileManager = [NSFileManager defaultManager];  
    [fileManager removeItemAtPath:databasePath error:NULL];  //  <------------  delete d/b  TESTING ONLY!   
	  
    BOOL fileExists = [fileManager fileExistsAtPath:databasePath];  
	  
	FMDatabase *db = [FMDatabase databaseWithPath:databasePath];  
	if (![db open]) {  
		[db release];  
		NSLog(@"DB Error not open");  
	}  
	if ([db hadError]) {  
		NSLog(@"DB Error %d: %@", [db lastErrorCode], [db lastErrorMessage]);  
	}  
	  
	[db executeUpdate:@"CREATE TABLE Datalist(pk INTEGER PRIMARY KEY, name VARCHAR(25), age INTEGER);"];  
	[db executeUpdate:@"INSERT INTO Datalist(name,age) VALUES('varvara',26);"];  
	[db executeUpdate:@"INSERT INTO Datalist(name,age) VALUES('mar',29);"];  
	FMResultSet *rs = [db executeQuery:@"select * from Datalist;"];  
	while ([rs next]) {  
		NSLog(@"%@", [rs stringForColumn:@"name"]);  
	}  
	[rs close];  
	[db close];

Here code example (nothing funcy): http://mcanet.info/lab/2011/sqlite-OFiphone.zip

#3

I managed to fixed and get write and read in SQlite successfully. I am using the nice wrapper library FMDB ( https://github.com/ccgus/fmdb ).

  
  
FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];  
	if (![db open]) {  
		[db release];  
		NSLog(@"DB Error not open");  
	}  
	if ([db hadError]) {  
		NSLog(@"DB Error %d: %@", [db lastErrorCode], [db lastErrorMessage]);  
	}  
	if (![db tableExists:@"Datalist"]){  
		[db executeUpdate:@"CREATE TABLE Datalist(pk INTEGER PRIMARY KEY, name VARCHAR(25), age INTEGER);"];  
	}  
	[db beginTransaction];  
	[db executeUpdate:@"INSERT INTO Datalist(name,age) VALUES('varvara',26);"];  
	[db executeUpdate:@"INSERT INTO Datalist(name,age) VALUES('mar',29);"];  
	FMResultSet *rs = [db executeQuery:@"select * from Datalist;"];  
	while ([rs next]) {  
		NSLog(@"%@", [rs stringForColumn:@"name"]);  
	}  
	[db commit];  
	[rs close];  
	[db close];
#4

Strangely is working in simulator and not working when is test in the device.

#5

Maybe this is the source of yoru problems: You can’t write into the application-bundle on the device, you’ll have to write into your sandbox-documents folder.

Here’s a snippet to get the path to the documents-folder:

  
  
NSString* doc_folder = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

HTH,
Stephan

#6

Hi Stephan,

Thanks for suggestion. But still I didn’t make it work. The code not found the database. I add in project 'todo.sqlite" file and I am wondering what need more to do.

  
  
NSFileManager *fileManager = [NSFileManager defaultManager];  
    NSError *error;  
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);  
    NSString *documentsDirectory = [paths objectAtIndex:0];  
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"todo.sqlite"];  
    success = [fileManager fileExistsAtPath:writableDBPath];  
      
    // The writable database does not exist, so copy the default to the appropriate location.  
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"todo.sqlite"];  
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];  
    if (!success) {  
        NSLog(@"Failed to create writable database file with message '%@'.", [error localizedDescription]);  
    }
#7

I had the same issue last night. The solution is so simple just put ofxiOSGetDocumentsDirectory() mehod before your database path. You have to copy your database file in to bin/data folder. Then everything works smoothly;

sqlite = new  ofxSQLite (ofxiOSGetDocumentsDirectory()+"buzdolabi_renk.db");