In this post I will be discussing how to create and use database on iPhone. The application will finally look like this:
Please refer my previous posts
- Simple Hello World Application
- Navigating from one view to another
- Creating a UITableView programmatically
- Customizing Header View of table
So to start, create a new project, chose the template “Navigation Based” and name it as “DatabaseTest”. We will be using SQLite3 database and for that we will need the libsqlite framework. Right click on frameworks->Add Existing. In the search bar type “libsql” and it will show you some 4 frameworks with same name. You have to choose “libsqlite3.0.dylib” whose size is 1.7MB.
Now we will create a database that we will be importing into our project later. Open the terminal and navigate into your project directory. Execute ls to verify that you are in correct folder. You should see the following screen on your terminal.
Now to create database in your project, type the following command:
sqlite3 data.sqlite
This will create a database with name data.sqlite. Now create a table with name “user” in your database. Execute the following command: sqlite> create table user (id varchar(10), name varchar(100));
Now we insert some dummy data into the table. Execute following commands:
sqlite> insert into user values(‘001′,’John Mclain’);
sqlite> insert into user values(‘002′, ‘Joey Triviani’);
sqlite> insert into user values(‘003′, ‘Spider Man’);
Check to see everything went fine.
Now we will import the database into our project. Right click on Resources->Add->Existing File and select data.sqlite.
This will add data.sqlite file into Resources folder.
Now we are ready to write code. Open DatabaseTestAppDelegate.h and import sqlite3.h
Open DataBaseTestAppDelegate.m file and add the following code:
- (void)createEditableCopyOfDatabaseIfNeeded {
NSLog(@”Creating editable copy of database”);
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@”data.sqlite”];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
NSAssert1(0, @”Failed to create writable database file with message ‘%@’.”, [error localizedDescription]);
}
}
We have done nothing but created an editable copy of database into local document. Now add the following code in the same file:
+(sqlite3 *) getNewDBConnection{
sqlite3 *newDBconnection;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {
NSLog(@”Database Successfully Opened 
”);
} else {
NSLog(@”Error in opening database 
“);
}
return newDBconnection;
}
Here we have created a class method which will be used by other classes to obtain the instance of database. We will see how this will be used in a short while. Mean while in the applicationDidFinishLaunching method add the following code:
- (void)applicationDidFinishLaunching:(UIApplication *)application {
// Configure and show the window
[self createEditableCopyOfDatabaseIfNeeded];
[window addSubview:[navigationController view]];
[window makeKeyAndVisible];
}
So, we have initialized the database. Now we have to use it. For this open RootViewController.h file and add the following code:
Now open RootViewController.m file and add following code:
Here we have used the initialize database method to obtain a reference to the database. Using that we created a statement and executed it. Always remember to finalize the statement to avoid unexpected database errors. Now in the viewDidLoad method we will be calling this method. Add the following code:
So we have read the data from database and also initialized tableData array. Time to display same into the table. Add the following code into the tableView delegate methods:
There you go! build and run the project and you should get the screen we saw in first image.
I have not covered very basic details, if you still need some I would suggest you to read my previous posts.
In next post I will be showing you how you can edit the table we created and also add new records to it and persist them into database.
[...] Using SQLite3 database [...]
[...] Using SQLite3 database So to start create a new ViewBased Project and name it as MySearchScreenApp. You can start with any template you want but you can find in my previous posts why I always start with the most basic template. The Search screen that you can see in the above images are implemented in MySearchScreenAppViewController class. So open the header file MySearchScreenAppViewController.h and add the following code: [...]
[...] adding code to same DatabaseTest project which I created earlier. You can find DatabaseTest here
[...] Using SQLite3 database [...]
Every example I have seen about SQLite in an App assumes you want to edit the database. Copying the source table represents upgrade issues if you want to supply updates, as the copy will only load the very first time. An update will be ignored, because the app already has the database. For instance, if you are supplying a dictionary, where the user just looks up details. What I can’t figure out is how to use the database supplied as is, read only. Any ideas?
I don know if i exactly understood the point here. But regarding upgrades I did come across same problem. Finally I had to change the name of database file in the next update..that worked fine.
I usually create a “ConfigureOnFirstLanuch” method in appDelegate from where in I update the database tables/data as required in the next version. This method is called every time a new version is launched for the first time.
Thanks ABC,
Changing the name is not a bad idea, because if you added a sequential number to the end, at least you would know what version you were at in any version.
The “ConfigureOnFirstLaunch” method sound interesting, you wouldn’t mind sharing that would you?
There are n number of ways to implement configureOnFirstLaunch method.
1) You can use Settings.bundle for that. You utilize an important property of preferences, which returns a boolean false for a preference that is not available in the pList file.
Suppose you write this code in applicationDidFinishLaunching:
if([[NSUserDefaults standardUserDefaults] boolForKey:@”version1.0Configured”])
{
Configure other things
}
else{
[self configureOnfirstLaunch];
[[NSUserDefaults standardUserDefaults] setBool:YES forKey:@”version1.0Configured”];
}
Since there was no such key “version1.0Configured” when the application was launched first time, boolForKey returns NO. You call configureOnFirstLaunch and do things that you want there and finally you set the version1.0Configured to YES. So from next launch onwards this method will not be called.
When you are updating the application simply modify the above code for version1.xConfigured key.
I jus hope I was clear enough
Just to mention, I changed the name of database in one of my application updates on App Store and I realized that some people crashed the application after getting an update. The problem could be solved only after reInstalling the application. I am yet not sure that was because of database conflicts or because I added some new entries in Settings.bundle which might not have been reflected in the update coz Settings.bundle already existed.
[...] iPhone Development: Using SQLite3 database iPhone Tutorial: Native Address Book like Screen Mar 29 [...]
hello, thanks for the tutorial. I’m new to iphone dev. I’d like to use an online database, basically the same I use in a website…
SQLite3 database is local, isn’it?
So how to connect distant database?
Thanks
I think d proper way 2 access remote data is through web services over http. I am unaware if iPhone sdk provides any drivers supporting remote database connection.
Can um tell me how i retrieve only images from database in a single table to my iphone application which contain 3 pages and i want to retrieve it different pages.
well, I was always scared of storing images in database, bt I will give it a try and let you know.
regards
I found this topic useful as I am having similar problems. I have done development based on an sqlite3 db and have subsequently changed the data in the db but can’t get the application to reference it. Even after removing the reference to the db in my Resources directory in my project it is still running, so I don’t know where it is picking up the information from.
In any case if I create the next db with a different name, what happens to the old one in the application considering it still works even without the reference in the Resources folder? While I may remove the references to it in the code, is it still there somehow and will it slow down the users who have downloaded the application? I’m worried that multiple databases over time will cause problems, but not sure if that would happen in the first place.
Any thoughts?
Good point! Some1 has to verify this.
hello, thanks for the tutorial. I’m relatively new to iphone dev.I want to use the existing database in my application with some new tables,how can i do this?
Thanks in advanced
Hello. Your tutorials really helps but I was trying out the SQLite3 tutorial and I can’t display the records. I just shows a blank screen with the title and the side selector. I have followed your instructions in the tutorial.
Hi,
I am trying to use a pre-populate sqlite DB in my iPhone app. I add it to my xcode project by doing “add existing file” after right-clicking on the resources folder but my db is then emptied and the structured modified: my field names start with a Z, some new tables appeared, etc…
Do you know how to keep the db unchanged?
thanks
Guillaume
hey thanks for the tutorial..
im very new to iphone dev. i tried to build your codes..
i gone thru it 5 times and corrected all syntax till it matches yours…but im getting 44 errors and 8 warning. are you able to send me a copy of the xcode
hey thanks for the tutorial..
im very new to iphone dev. i tried to build your codes..
i gone thru it 5 times and corrected all syntax till it matches yours…but im getting 44 errors and 8 warning. are you able to send me a copy of the xcodeproj file
Hi, thanks for such a great tutorial. I have worked through it step by step and have reached the point where I am working on “didSelectRowAtIndexPath”. My question is how can I now go forward and use the content of “Country *country = (Country *)[[objectsForCharacters objectForKey:[arrayOfCharacters objectAtIndex:indexPath.section]] objectAtIndex:indexPath.row];”: Country *country = (Country *) is apart of my script to display the detail view of the record selected. Any help would be great.