Unable to connect SQLite Database in iOS

Question

I am completely new to SQLite and iOS. I am following a basic tutorial on how to use SQLite in iOS:

http://www.switchonthecode.com/tutorials/using-sqlite-on-the-iphone#comment-11617

In the above link, they have specified the database as:

sqlite3 *database;
int result = sqlite3_open("/myExampleDatabase.db", &database);

but when I use the above code with replacing my database name, I get an error as specified in the subsequent alertview.

My question here is, do I have to add the database file into my resource folder? If not, do I have to have my database file somewhere that is accessible to iOS?

• GenieWanted: Also, it would be great if someone could actually provide me with some good tutorial links on connecting SQLite with iOS? Thanks again.
• Rob: If you've created you DB in advance, you need to make sure it's included in your bundle, you'll probably also want to copy it from the bundle to your Documents folder (i.e. see if it's already there, if not, copy it from your bundle to Documents, etc.). Your sqlite3_open call then has to refer to the fully specified path for the resulting file. For a few other tips for new SQLite users, see stackoverflow.com/questions/11605072/…
• GenieWanted: Thank you Robert! I've added the DB in my bundle and did the same to my Documents folder but then still I am not able to figure that out. Perhaps, I am missing something on my side. Will dig further.. Thanks again..

Answer 1

I suggest using FMDB wrapper for SQLite: https://github.com/ccgus/fmdb

• Rob: +1 I second that. FMDB really gets you out of the details of sqlite calls. It won't solve the problem of finding the database, but overall it's boon, taking all of the complexity out of making sqlite calls yourself.

Answer 2

If you want to open a sqlite database, you might want to:

1. Make sure you're including your database in your bundle.

2. Programmatically copy the database from your bundle to your documents (esp important if user will be modifying the database; if you're only reading, you could go ahead an just open the version in the bundle).

3. If you're running this in your simulator, you can go ahead and inspect the bundle and Documents folders if things don't go right, just to make sure everything is where it should be. You simulator's folder is something like "~/Library/Application Support/iPhone Simulator/5.1/Applications/" (replace the 5.1 with whatever version of your simulator you are using). You might have to unhide your Library folder, if you haven't already, by running the chflags nohidden ~/Library in a Terminal command-line window.

So, the code for getting the path of the database (and copying it to the Documents if it's not there yet), might look like:

NSString *databaseName = kDatabaseName; // obviously, replace this with your database filename, e.g., @"myExampleDatabase.db"
NSString *documentsFolder = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *databaseFullDocumentPath = [documentsFolder stringByAppendingPathComponent:databaseName];
NSString *databaseFullBundlePath = [[NSBundle mainBundle] pathForResource:databaseName ofType:@""];
NSFileManager *fileManager = [NSFileManager defaultManager];
if (![fileManager fileExistsAtPath:databaseFullDocumentPath])
{
 NSAssert([fileManager fileExistsAtPath:databaseFullBundlePath], @"Database not found in bundle");
 NSError *error;
 if (![fileManager copyItemAtPath:databaseFullBundlePath toPath:databaseFullDocumentPath error:&error])
 NSLog(@"Unable to copy database from '%@' to '%@': error = %@", databaseFullBundlePath, databaseFullDocumentPath, error);
}

Then, if you're doing your own sqlite calls, it would be something like:

sqlite3 *database;
if (sqlite3_open_v2([databaseFullDocumentPath UTF8String], &database, SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK)
{
 // do whatever you want to do
}

Or, alternatively, if you're using FMDB, it would be something like:

FMDatabase *db = [[FMDatabase alloc] initWithPath:databaseFullDocumentPath];
NSAssert(db, @"Unable to open create FMDatabase");
BOOL success = [db open];
NSAssert(success, @"Unable to open database");
if (success)
{
 // do whatever you want to do
}

• GenieWanted: Well, I finally made it using FMDB! It was straightforward indeed.. Thank you all! +1

Answer 3

I fully support the previous answer in most cases, however:

Are you sure you have to use sqlite3 instead of Core Data?

There are several discussion where you can get information when to use a database wrapper (like fmdb) and when to use Core Data. (Speaking personally, I love to use fmdb, but it always results in more code, complexity and most of the time a worse performance)

• Core Data vs SQLite 3
• Use CoreData or SQLite on iPhone?
• Core Data vs Sqlite and performance
• Core Data vs SQLite 3
• is it worth using core data for a simple sqlite app on the iphone with one table and no relationships or complicated subtable/views?
• Core Data vs. SQLite for SQL experienced developers

Some links to get started with Core Data:

• Core Data Programming Guide (Apple)
• Core Data Tutorial for iOS (Apple)

• Rob: Good question. If doing iOS only and not doing complicated SQL, then Core Data is a good technology, with some real up-side. But if using FMDB for your sqlite stuff, it's incredibly simple and easy.