iPhone SDK Articles
IMPORTANT:
The following tutorial has been deprecated and should no longer be used for any purposes. To learn more on how to use SQLite click here In this tutorial I will talk about how to access data using the SQL Lite database. In my last iPhone SDK tutorial, we learned how to create a table view where the data comes from an array. In this tutorial, we will access the data from the database and display it using a table view.
In this tutorial I will talk about how to access data using the SQL Lite database. In my last iPhone SDK tutorial, we learned how to create a table view where the data comes from an array. In this tutorial, we will access the data from the database and display it using a table view.
This is how the application will look like, I know it is not much but we will learn many things and I wanted to keep this tutorial a little short.
In this tutorial, you will learn how to
- Create a database.
- Package an existing database with the application.
- Copy the database
- Execute SQL commands
- Close the database.
Let us start by creating a SQL Lite database with the following schema. To Learn more about SQL Lite visit www.sqlite.org.
Open up the command line interface and create a database by typing sqlite3 SQLNavigation.sql. The database file will be created in the PWD (Present Working Directory). Now we can execute SQL statements at the command prompt. In the database schema above we have two tables, parent table denoted by CoffeeHouse and a child table Coffee. Coffee table contains general information about the coffee that the coffee house sells.
Create a “Navigation-Based Application” and name it “SQLNavigation”. To add this database in our project, click on Project -> Add to Project and select your database file and add it to the Resources folder. We have successfully added the database to the project.
The first we have to check for when the application finishes loading is, whether the database is present in the user’s iPhone or not. If it is not then we have to copy it.
In applicationDidFinishLaunching method, before we make the window visible , we have to check if the database exists or not and then get the information from the database and display it in the table view. We only copy the database once, when the application is first launched. This happens in createDatabaseIfNeeded method.
We first get an object of type FileManager which we will use to access the file system. Then we will search for the user’s document directory in a given domain. The third parameter is a “YES” because we want the complete path to the directory, which means if a ~ is found in the string it will replace it with the complete document path. As the function returns an array, we will get the first string in the array. We then create a complete path to the database file and check if the database exists or not. If the database does not exists then we copy the database from the resource bundle to the document directory.
To execute sql commands, first add the libsqlite3.0.dylib file to your project. This file is located under /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.0.sdk/usr/lib/. We also need to import the following header file sqlite3.h in the application delegate header file.
Now we will get a list of all the coffee shops and display it on the first screen. The user will be able to select a coffee shop and see all the coffees that a shop has. The user further can select a coffee and see its details. Let us first get a list of all the coffee shops and siaply it in our table view. We will get the data as soon as we have finished calling createDatabaseIfNeeded method.
We first get the path to where the database is located and open it using sqlite3_open method. This method returns 0 (or SQLITE_OK) if it is successful in opening the database. The connection object here is the variable database and like any other language we also have a command object of type sqlite3_stmt. sqlite3_prepare_v2 is a method by which we initialize the sql statement. After the method is executed the compiled statement is stored in the selectStatement variable. Just like we have some kind of an execute method, we will call sqlite3_step method to execute the sql statement and get a forward-read only reader. sqlite3_step returns SQLITE_ROW if there are no errors. It is then we get the data from the columns using either sqlite3_column_int or sqlite3_column_text methods.
We also have a class created called Coffee to hold the information we get from the database. We also have an array created in the header file of the delegate to keep track of the coffee objects. The coffee class contains two variables (for now) coffeeHouseID and coffeeHouseName, it also contains a method called initWithCoffeeHouseData which returns itself. This method is used by the application delegate to create an object of type coffee, later this object is added to the array.
In the implementation file of the Root View Controller, we need to tell the table view how many rows it should expect. We do this by getting a reference to the application delegate and then returning the count of the array.
We then get the same refernce again in cellForRowAtIndexPath to write the coffee house name to the text of the cell. We get a coffee object from the array and use the coffeeHouseName property.
Application delegate receives an applicationWillTerminate message when the applicaton is closing. To release the resources appropriately, we should close the database connection here. We call the sqlite3_close messase to close the database and release its resources.
This concludes the part 1 of using SQL Lite databases in the iPhone applications. Read Part II of the tutorial here.
You can download the source code by clicking here and please let me know what you thought about this tutorial.
If you enjoyed this article, please consider sharing it!
>Error code 1 usually means missing database file. Can you check your code to see if this is the function being used to get the database, I know I had an error which I fixed.
stringByAppendingPathComponent
Happy Programming,
iPhone SDK Articles
>Thank for your excelent tutorial,
but I have a question.
I did sqlite -init SQLNavigation.sql and following your instruction in terminal.
and it looked working well. but I have deleted the ‘SQLNavigation.sql’ file and re-make the *.sql file, then I can’t find my sql file anywhere. ( I tried to search the file using spotlite program in my labtop, but I couldn’t.)Is there anyone who can help me?
>int returnValue = sqlite3_prepare_v2(database, sql, -1, &selectStatement, NULL);
i am getting returnValue as 1;
I have followed all steps still getting this error
Will you please tell me what i am missing .. code is same as yours
sindhu tiwari
>You really haven’t shown how you created the coffee class and what you added and how you created that coffee class. I think you need to be more detailed and step by step from scratch to finish.
I don’t want to have to download your source code and try to modify it.. you should be clearer in what you explain so that I can follow what you’re doing.
This tutorial doesn’t help me understand what to do properly.
>Does anybody know how to make the list sort alphabetically. I have been trying to figure this out
>Ugh sorry scratch last comment, you can do it this way:
bash> sqlite3 -init schema SQLNavigation.sql
>I actually had to:
sqlite3 -init SQLNavigation.sql
(adding the -init)
Also, the line containing the filename must be removed first.
>This is an awesome tutorial. I have been looking for a tutorial like this for the longest time. I actually found this site on http://www.iphonedevver.com which is an iPhone SDK tutorial search site.
>Firefox SQLManage allows you to create and populate tables via GUI. Try it out!
>I got lost at Copy the database section. Where do I find “applicationDidFinishLaunching”
Pls bear with me this is all very new. Assume I know nothing. even though I completed the hello world dutorial.
>Hi Jai,
I really appreciate your work on these tutorials. I also appreciate that you include the source code. I have one suggestion in that sometimes you aren’t specific on which source code you are adding the code to. I search your example code to find which file to add to, but if you include the header info in your image of the code it could clear it up for some other people. Thanks again,
>The article helped me a lot, thanks for putting it up.
tommy asked:
“Is there a easier/faster way to add more rows to the database without using the sqlite3 terminal interface?”
You can also use the .import command. I had thousands of rows of data from another source to load, and this is how I did it. Worked well.
One thing I avoided in order to make it easier to import was that I didn’t define an “id” in my table. It is unnecessary in any case, sqlite automatically does this, calling it “rowid”, see:
http://www.sqlite.org/autoinc.html
>Hi Jai thanks for you reply about the terminal.
Just in case your wondering you can use a firefox plugin called SQLite Manager to easily drop tables or add new data to your database. I think you still need to create the initial database in the terminal though.
Thanks again for all the good work
>Is there any way to get this code other than typing it in from screenshots?
>Hi Tommy,
I think we have to use the terminal, to add some initial data in the database.
>Thanks alot your website is great – it has really helped me get started with developing iphone apps.
I was just wondering how you edit/add a new row to an sql file without using the terminal. The MAC operating system identifies the .sql file as an “exec” and if I open the .sql file up in a text editor there are all these random symbols (such as ##Å). This also happens with the SQLite book sample code from apple.
Is there a easier/faster way to add more rows to the database without using the sqlite3 terminal interface?
Thanks for you help and thanks again for the great articles.
>Nice job. Thanks for the effort to put this together. It gave me a nice jumpstart.
>Hey man, just found your tutorials, They are exactly what I needed to break the barrier to entry (I’m a linux, win32, xbox 360, ps3, wii developer and the programming paradigm of cocoa is quite a bit different then what I’m use to)
Thanks a ton for taking the effort/time to do this