If you have been following my tutorials, you know that we have been working primarily with UITableViews. This is mostly because SO many applications can be developed using this simple control. This final UITableView tutorial will be taking all of the skills learned from previous tutorials, putting them all together, and adding SQLite to create a prioritized To-Do list. I will also be showing you how to add multiple columns to your table cells and we will be exploring some of the other controls that the iPhone has to offer. What good would the tutorials be if we didn’t use them to create something useful.
I will move a little faster in this tutorial while still explaining the new stuff in detail. I will assume that you have completed the fruits tutorial and it’s prerequisites.
This tutorial will be a multipart series as it will be a little longer than my previous ones. In this first tutorial, you will learn:
- Create a NavigationBased Application
- Create a Database
- Add the Database to Your Project
- Add the SQLite3 Framework
- Create a Todo Class Object
- Initialize the Database
So let’s get started…
Open up X-Code and Select File->New Project… Select Navigation-Based Application and click Choose…
Name your project todo. Now let’s create the todo database that we will be using. Open up the Terminal application on your Mac. This is located in Applications > Utilities.
If you have installed XCode, you should have mysqlite3 already on your computer. To check this, type:
sqlite3 into the Terminal and sqlite3 should start. Type .quit to exit. If sqlite3 is not installed, install all of the XTools from your Mac Installation Disk.
Now that the terminal is open let’s create the database. This is done with the command:
sqlite3 todo.sqlite
SQLite3 will now start and load the todo.sqlite database. By default the database is empty and contains no tables. If you need a refresher on the basics of SQL databases Google It. Since our application is fairly simple, we only need to create one table. We will create a table called todo by typing the following statement:
CREATE TABLE todo(pk INTEGER PRIMARY KEY, text VARCHAR(25), priority INTEGER, complete BOOLEAN);
One thing to note here is the pk field. It is the primary key of the table. This adds functionality such that every time a row is added to the database, it auto-increments this field. This will be a unique identifier to identify each row. All of the other fields should be fairly self explanitory.
Now that our table has been created, let’s add some data. We will eventually be adding todo items within our app, but for now we will add some defaults. Type the following commands below.
INSERT INTO todo(text,priority,complete) VALUES('Take out the trash',3,0);
INSERT INTO todo(text,priority,complete) VALUES('Do Computer Science homework',1,0);
INSERT INTO todo(text,priority,complete) VALUES('Learn Objective C',1,0);
INSERT INTO todo(text,priority,complete) VALUES('DIGG this tutorial',2,0);
You can add as many todo items as you would like. For this tutorial, make sure you enter a priority between 1 and 3 (You’ll see why later). Now our database has been created and populated let’s exit out of SQLite3. Do this by typing .quit. Your terminal window should look something like this.
Now go back to XCode. Do a Control-Click (right click) on the folder named Resources. Click Add -> Existing Files… and browse to your todo.sqlite file and click Add. It will then prompt you with a screen like this.
Make sure you check the box that says Copy items into destination group’s folder (if needed). You should now see the todo.sqlite file inside of the resource folder.
Now that we have added the database, we need to load the Objective C libraries so we can use it. Do a control-click (right click) on the Frameworks folder. Click Add -> Existing Frameworks. Now this part is a little strange. It has been my experience that these libraries are not in the same place on all machines. So in the search bar type in libsqlite3. The file we are looking for is called libsqlite3.0.dylib. This may pull up multiple files as OSX has it’s own versions of this file. Just click on the largest of the files that show up and click Add. As you can see, mine is about 1.7 MB.
Now it should add the framework and your directory will look something like this:
We need to create an object to hold our todo information. We will eventually be making an array of these objects to populate a UITableView. Go ahead and click File -> New File… Select NSObject Subclass and click Next.
Name this object todo.m and check the box that says Also create “Todo.h” and click Finish.
Open up todo.h and add the following code.
We see some new things here…First, there is a variable of type sqlite3 called database. This will be a reference to the applications database and will allow the todo object to communicate with it. Make sure you add a #import<sqlite3.h> in your imports.
Next, we see a primary key. Notice that in the property declaration it has the keywords assign and readonly. This tells the compiler that this variable, once assiged, can not be changed again. This is good since each todo will be uniquely identified by this variable.
Also, I have declared a method called initWithPrimaryKey. This will be the contstructor for this object. It takes an integer to assign as the primary key and an sqlite3 object to use as the database reference.
Let’s implement this method…Open up todo.m and add the following code.
There are quite a few new things that need to be explained here. I will just go through it line by line.
static sqlite3_stmt *init_statement = nil
This will hold our initialize statement when retrieving todo data from the database. This statement is static, meaning it is independent of any instance. In other words, there will be only one of them no matter how many todo objects we create. This statement will get compiled and allow us to do some neat things. I’ll explain more in a bit.
The next lines makes sure that the super class (NSObject) initilizes properly before we initilize a todo object. We then set the local primary key and database objects to the parameters passed to the initWithPrimaryKey method.
Now some interesting stuff happens. The next lines checks if our init_statment is null. This will happen only once per launch of the application. If it is null, we create a new string containing an SQL statement. If you are familiar with SQL at all, this should look pretty familiar with one exception. What is a question mark doing in there? Well, I will tell you. After the SQL statement gets compiled, we can bind a value to it that will eventually replace the question mark. So this allows us to have 1 generic SQL statement, but bind different values to it to retrieve different results. So the next line, you guessed it, prepares the statement and stores it in our init_statement. The if statement just checks to see if this finished correctly and prints an error if there was a problem.
Moving on… The line sqlite3_bind_int simply replaces that question mark with the primary key of the current todo object, so what we end up with is statements like this:
SELECT text FROM todo WHERE pk = 1;
SELECT text FROM todo WHERE pk = 2;
SELECT text FROM todo WHERE pk = 3;
SELECT text FROM todo WHERE pk = n;
After that, the sqlite3_step(init_statement) method is called. This method executes the SQL statement on the database. It is contained inside of an if statement to make sure it executed properly. Now we can finally access the todo data. We see this line:
self.text = [NSString stringWithUTF8String:(char*) sqlite3_column_text(init_statement,0)];
Wow, that’s a mouthful… Let’s analyze it. The sqlite3_column_text method tells SQL that we want to retrieve a string object from the database. It has 2 parameters. The first, is just a reference to the SQL statement that was used. The second is the column number that we wish to get text from. So in this case, we only have one column (SELECT text FROM…) so there is only 1 index and that’s the 0th index. Next, the (char *) is just a cast to a string (might not be needed, but good practice). And finally, we build an NSString object with the data returned so that we can assign self.text to it.
This is quite a bit to explain in just text. If I have lost you, feel free to ask me questions in the comments.
We are done with the todo object for now…
Go ahead and open up todoAppDelegate.h and add the following code.
This should look a little familiar with the exception of a few lines. Notice that I have created an NSMutableArray of todo objects. This will be (like the fruit example) an array to hold our todo items. We will eventually use this array to populate a UITableView. The only new lines here are the import of sqlite3.h and the sqlite3 *database line. Now let’s open up todoAppDelegate.m and add some code.
One new thing we see here is a private interface. We declared it here because it’s specific to this object so it does not need to be declared in the .h file. The 2 functions we will implement are createEditableCopyOfDatabaseIfNeeded and initializeDatabase. Much of the code for these has already been written for us inside of Apple’s SQLBooks tutorial. I will going through this code and explaining it the best that I can. Add the following code.
What this method is essentially doing is copying the database from your project folder to the documents folder on your iPhone. This will only happen once as it first checks if the database already exists in the documents folder. I’m not going to go through this line by line as it is fairly self explanitory. Apple does a great job of naming functions and variables so that we can understand what is going on. If I get enough requests in the comments, I’ll do a line-by-line writup of this function.
The next function we will implement is initializeDatabase. Add the following code:
That’s a lot of text! Don’t worry it’s mostly comments. Let’s analyze this code…Some of it is very similar to the fruits example.
The first line creates and initializes a NSMutableArray. We then go on to set this array to our object’s todos array and release the temporary object.
The next 3 lines locate the database we created inside of the documents folder. Following that, the sqlite3_open line open’s the database so we can access its data. If the database opens correctly, we then proceed to retrieve todo items. The first line:
const char *sql = "SELECT pk FROM todo";
is an SQL statement that we will use to get all of the primary keys from the database. We then prepare the statement (as we did inside the todo.m file) only this time there is no “?” in the statement. That is because there is not condition for retrieving the primary keys. We are simply saying “give me all of the primary keys in the database”.
Now we see a while loop that is stepping through the SQL results. Every time we call sqlite3_step, the next result gets retrieved. The line:
int primaryKey = sqlite3_column_int(statement,0);
retrieves the primary key from each result. This is very similar to retrieving the text in the todo.m class only we use the sqlite3_column_int method instead of the sqlite3_column_text method. This is done for obvious reasons.
After we have the primary key, we create a new Todo object and call the initWithPrimaryKey constructor that we created. The primary key gets passed as well as a reference to the database. This allows the Todo object to essentially “look itself up” in the database. Finally, we add the newly created Todo object to our array of todos.
The last statement sqlite3_finalize clears the statement from memory and does some other cleanup.
The last part of this tutorial is calling these functions to create and initialize the database. So add the following code to applicationDidFinishLaunching:
We are simply calling these functions. You can now click Build and Go but your application won’t display anything! You might be quite frustrated that you completed this portion of the tutorial and have yet to see anything. Well, stay tuned! I will have the next portion of this tutorial up soon.
For you ambitious programmers you could move on. If you notice, at this point we are in a similar situation as the fruit tutorial. We have an Array of objects that will eventually populate a UITableView.
This tutorial will be a 4 part series and I will show you how to use a few more controls. We will be adding, editing, and deleting todo items. If you have any questions, please leave them in the comments. Also, if you get lost you can download the sample code here
Happy iCoding!
167 Comments
when you publish part 4… i am waiting for that….
@Varun,
Have you tried downloading the sample code? Post some of your errors so that I can help you troubleshoot it. Also, one major source of error is not importing the sqlite library into your project. Did you complete that step.
@Prayag
Soon…It’s almost done. Will be out sometime next week.
Kinda redundant to add my thanks Brandon but want you to know new people are glomming on.
Your efforts are greatly appreciated.
Learned a lot about databases in XCode. Thanks for the excellent examples, but when I compile and run I get no errors but it quits unexpectedly and crashes. I’m guessing I forgot to release something?
Thanks.
Update:
The console error message was that :
‘Assertion failure in -[todoAppDelegate initializeDatabase]
Terminating due to uncaught exception. Failed to open
database with message ‘not an error’.
Weird.
Ooops! My bad. Missing last couple lines of code!
Fixed now.
Thanks.
Hey Brandon, I am extremely thankful for your 4 part SQL Lite database driven application. My background is that I use to be a dynamic database driven application development developer (5 years ago). I mainly used Microsoft’s Active Server Pages with SQL Server and Oracle Databases. I feel very confident with your SQL language and calls. However, though I typed every line of code that you laid out in your tutorial . . . AND . . . have printed out EVERY PAGE of the Apple iPhone & C related documentation from the iPhone Developers Program, I do not understand how to create an application on my own. I would have NO IDEA how (and why) you do what you do, when you do it. I am VERY lost. PLEASE lead me to an “iPhone Applications for Dummy’s” website or book or something . . . EVEN BETTER . . . do you know of a training facility that I could attend in Southern California? Because as it stands now. I am just mimicking your code (and appreciating you for it) and my light is not coming on as to how I would have been able to have done what you do on my own. As you can see, I am anxious to learn. PLEASE HELP ME, Brandon. Thanks.
@Chip
Have you read any of my Hello World Tutorials. I have a few more on the site…They should provide a little more insight for you as the SQL tutorials are a little more advanced. Also, in these tutorials, I have assumed you completed my previous tutorials.
I don’t know of any training areas. I’m not even sure if they exist as Apple is pretty lame when it comes to their NDA. Even me having this site violates it.
Read through the earlier tutorials on my site and feel free to post any questions either in the comments section or in the forums.
thank for reading…
Good job Brandon, nicely presented… would’nt be possible without this tutorial
I’m just getting into iphone dev as a c# coder, and it’s a pretty different experience. I’ve had a heck of a time finding some solid concrete examples to start with that are between hello world, and complicated…
This looks like the first set of tutorials that hit just the right spot! I’ll be using this first series to start working on. Kudos, and thanks so much!
@redth,
no problem! I would not suggest starting here though. I have some easier tutorials that will get you started on understanding Interface Builder.
Look in the sidebar of the site. Under the “tutorials” heading. They are listed in the order I write them.
Hello Brandon,
I have 3 Apps published on the AppStore, but I love your tutorials !
They are very basic apps, oriented towards aviation needs, and I am learning a lot from you !
Please keep it up !
One question though, already asked by Phil on Aug 19th (but I haven’t seen a response) :
Can we build the database with a GUI ?
The one I have in mind has about 200 entries, and it would take me forever in Terminal !
Thanks
@Deamb
You do not have to do each sql statement line by line
1) enter into the sqlite3 command prompt
sqlite3
2)Copy and paste the text below and press enter
CREATE TABLE test(pk INTEGER PRIMARY KEY, text VARCHAR(25), priority INTEGER, complete BOOLEAN);
INSERT INTO test(text,priority,complete) VALUES(‘Take out the trash’,3,0);
INSERT INTO test(text,priority,complete) VALUES(‘Do Computer Science homework’,1,0);
INSERT INTO test(text,priority,complete) VALUES(‘Learn Objective C’,1,0);
INSERT INTO test(text,priority,complete) VALUES(‘DIGG this tutorial’,2,0);
This should allow you to easily recreate databases.
Hello Brandon,,
i have changed mine data base file but my application give old reference . and does not leave old reference. mine application could not fine updated database even if i removed old database reference and also deleted it. so can u plz help me for that . what should i do ? i also removed build filed. then also i could not use new db.
i am too much confusing and project deadline is near . so plz as soon as give solution for that.
Thanks Regards
sunny.
@ Backwardselvis :
Thanks for the tip, but I still have to write all 200 entries into something like text editor before I paste it into terminal !
Anyway, I guess I’ll do it !
@Shani,
If I understand your question correctly, you simply need to uninstall you application from the simulator. This is done by clicking and holding your mouse down on the “home” screen in the simulator. The app icons will “wiggle” and you can click the “X” in the corner. It will prompt you to remove.
After doing this, add your new database to your project and the reference will now match up.
@Deamb
Have you tried Googleing an SQLite GUI. I haven’t seen one. I think in the comments of another post someone suggests a web based one. I’ll post if I find one.
No brandon, no luck so far, except 250$ ones, I haven’t found anything…
I’ll look for a web-based one though
Thanks again
Brandon:
I’ll add my kudos to the others. Thanks very much for your effort in writing these tutorials. I would not be able to get started as an iPhone developer without you. You Rock!!!
OK, an actual question:
About
@interface todoAppDelegate (private)
The syntax here looks like a category named “private”. Is that true?
Not quite… What this line is say is, we are declaring a private interface for this class. It’s sort of like declaring a road map for this class.
Thanks for reading…
Hey guys,
Thanks for this tutorial Brandon!
For those of you who want to test the code after completing this tutorial, just add this code to RootViewController.m:
First:
#import “todo.h”
..and then..
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
todoAppDelegate *appDelegate = (todoAppDelegate *) [[UIApplication sharedApplication]delegate];
return appDelegate.todos.count;
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
static NSString *MyIdentifier = @”MyIdentifier”;
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:MyIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:MyIdentifier] autorelease];
}
todoAppDelegate *appDelegate = (todoAppDelegate *) [[UIApplication sharedApplication]delegate];
todo *t = (todo *)[appDelegate.todos objectAtIndex:indexPath.row];
[cell setText:[NSString stringWithFormat:@"%@ - #%d", t.text, t.primaryKey]];
// Set up the cell
return cell;
}
Maybe you want to add this to your tutorial Brandon, shows some progress
Thanks anyway!
@Neo
Thanks for posting this. The reason that I didn’t add that code to this tutorial is I show how this is done is part 2 of the todo series. I should probably make that more clear by linking to the next tutorial at the bottom.
Anyway, great job man!
Brandon: Yes you’re right, I know that there are 4 parts. But I just did it for myself – to check if the sql database works -before starting with part 2!
Thanks!
Brandon,
not sure what’s going on but when I try to launch the App I get a “Unable to read symbols for “/System/Library/Frameworks/UIKit.framework/UIKit” (file not found).” I know this is not where the UIKit framework is located and I can’t figure out how to change it. Your help is much appreciated.
Just wanted to say Thanks
As a tip for noobs like me, i have found that hitting the Build and Go button frequently through the tut helps in finding errors/typos earlier which makes them easier to fix. Maybe a suggestion for Brandon to put a B&G instruction after each section to suggest that the app should build at that point with no errors. Believe me, I do this a lot now and it helps me a lot!!!
Anyway, gotta go… I’ve got the next part to follow
See you in Prt2 comments!!
Hey, great tutorial, however I seem to be having trouble with something.
I’ve followed your instructions quite closely, but cannot seem to make changes to the database in a programmatic way. That is to say that, I have a dehydrate routine, but the changes it makes do not seem to persist, the next time I load the program, the previous values are still present… this is also true when I directly edit the todo.sqlite db from the terminal… so far as the project is concerned everything should be working…
Any ideas ?
.joe
You should’ve at least mentioned in one sentenced that your example is borrowed from the Apple examples … but good comments, though!
@Stefan,
What do you mean man? Did you miss the line that states
“Much of the code for these has already been written for us inside of Apple’s SQLBooks tutorial”
implying that some of this code came from Apple’s examples. What else would you like me to add?
Hello Bradon..
it’s nice tutorial and too much help in to create new application.
one problem i have …
i have changed the name of project then i got error in sqlite3_prepare_v2 statement. it gives error in sqlite_error.
so can please give any solution for this ? what should do now ?
Thanks Regards,
Shani
Just wondering where the NSAssert1 will output its errors. I am not seeing it print anything to the Xcode console.
How come in the initializeDatabase method you don’t initialize the self.todos directly with the alloc/init methods and instead you create a temp object?
thank you.
this tutorial is very helpful to me
how can i open .sqlite file,
if I use NON Navigation Based Application Project with link to SQLITE i have error of building aapp. Why?????? Just try to add link and build empty project. Please HELP!!!
It’ll be awesome if you showed us how to do this with some Tabs. Also, I’m new and I’ve been following along your tutorial. I’m trying to add another text box called text1 and I added it to the sql table. But everytime I run the app… it crashes and says there’s no column called text1. Any help would be appreciated.
Hello. I think you are eactly thinking like Sukrat. I really loved the post.
Cheers, this is an amazing tutorial, great work.
Good luck with the child too!!
What a waste of time! This is just an incomplete rehash of the apple sqlbooks tutorial.
Thanks a lot for the tutorial!
One question, how should I do if I would like to bind more parameters? For instance:
const char *sql = “SELECT text FROM todo WHERE pk=? AND px=?”;
Thanks, chr
Ops, I presume the second parameter specifies the index
Maybe this is simpler than I think, but how do we use multiple tables? how do I tell the app which one i’m trying to get at?
In the project it has something like this: const char *sql = “SELECT pk FROM thetable”;
If i want to initialize another table and be able to query it independently, do I do something like this:
const char *sql1 = “SELECT pk FROM thetable2″; ?
Thanks for any input (BTW your tutorial explained alot).
I’m getting “error: ‘database’ undeclared (first use in this function)” compile-time for two lines:
in todoAppDelegate.m:
if(sqlite3_open([path UTF8String], &database) == SQLITE_OK)
and in Todo.m:
NSAssert1(0, @”Failed to prepare statement with message ‘%s’.”,sqlite_errmsg(databse));
I’m curious about my arguments not working correctly in the second case as I also had trouble with the initWithName method arguments in the fruit example. That aside, it seems the first instance (passed as reference) should definitely work. Please let me know if there’s a place I may have missed the declaration.
Thanks!
Ben
I’m getting a “_TERMINATING_DUE_TO_UNCAUGHT_EXCEPTION__” error and am unsure how to use the debugger – no matter how early in the program I set my break point, I get that error!
Could I get a link to a debugger-how to, or some advice in general? Thanks!
While my answer to this question is probably in this list of tutorials, I have yet to find it.
I have an app setup and working. It consists of 3 tab’s. Two tabs display a tableview with a list of names from two plist files. These lists are in alphabetical order with an index. The third view simply shows my logo and details about the app.
I would like to take the first view, a table view, and have it display details about the name that the user click on in the table. I want to keep my list grouped in ABC order and keep the index on the side. My details for each name are going to consists of many lines of text, and maybe a picture icon.
I am having trouble finding a place to store this detail data and how to call it in when a user click on the related name in my first tableview.
The second tableview is just a tableview, with no details.
I have been stuck on this for days. Any help would be great! Thanks for all the great advice in these tutorials!!
Hey i am very big fan of iCodeBlog.. i learned so much from here !!
now i have some confusion regarding ,
“create a new folder in iPhone” while running my application;
so i can store user modify data !!
since i dont know the directory structure of iPhone ..
so please help me regarding this..
i request other reader as well …
Hey Man Bardon… Thanks man…
I was working on this simple app where in the user sets in the to-do list then the app helps him to remember things by just giving him simple Alarm type thing… How should i work that out…
Please help me out with this Dude…
Great tutorial. Thanks for doing this. One quick (or not) question. How would you modify this to accommodate a database with more than 1 table? In the app I’m working on I have two tables and each table has a corresponding object.
Thanks,
Andy
the comments here are having a laugh – i’ve added your blog to my netvibes account, keep up the good work
Hi, i have some simple problem. i am using the sqlite to save data.In My application when i Insert the some record into database table then record inserting properly, but next time when i am re-inserting the record after closing the application. my database file not taking changes. it showing previous record again and again. i not getting any Query error at Console, Console saying that I am inserting the record with row id. but my database file is not getting the Changes.
What am i missing something. or when i close my application suddenly my database file not committed with new record. Did i have to code at the application terminate Point?
Thanks in Advance.
Thanks for an excellent tutorial.
I’m trying to figure out how to get the data from all columns in one row, where primarykey is equal to variable “i” and put the data in a nsmutablearray, one column per array entry.
Here’s an examle
contacts table would contain:
pk(INTEGER), surname (VARCHAR), lastname (VARCHAR), email (VARCHAR).
sql: “SELECT * FROM contacts WHERE pk = ?”
and ? would be replaced by variable “i”
Any pointers in the right direction would be much appreciated
thanks
G
15 Trackbacks