Tuesday, March 3, 2009

iPhone Development: SQLite3 - populating table from database

In this post I will be discussing how to create and use database on iPhone. The application will finally look like this:



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.

44 comments:

  1. Hi! Thanks for posting this tutorial.
    Just one thing: Blue font on black background is not very comfortable to read.

    ReplyDelete
  2. Hello,
    M trying to find another way putting code. This color comes by default when I copy code from xcode. Any idea how we can out it other way? Putting it in image doesn allow you 2 copy code from here and also u have 2 open it in different window.
    Suggestions will be highly appreciated.
    Thanks

    ReplyDelete
  3. change your blogspot CSS to give code tags the bgcolor of #FFF and the font color of #000.

    ReplyDelete
  4. hi
    thanks for a really good tutorial.

    I have one question though,
    I'd like to get just one row, where an integer variable (i) matches the user column from the database and put that row into an array, for further use within UILabels or UITextFields.
    (sql would look similar to "SELECT * FROM name WHERE id = ?" and then bind "?" to i somehow )
    I've built it in a view-based project, not navigation-based, but I end up with (null) value in the array.
    Any help or example script would be really appreciated.

    Georg

    ReplyDelete
  5. Hello Georg, sorry for the late reply.
    As such this shouldn't happen but I have always found problems while binding non-text values to statement.

    Can you try doing like this:
    char *sql = [[NSString stringWithFormat:@"Select * from name WHERE id = %d ",i] cString];

    Could you please provide me the create table command u used to create the table and den the code that you have written? That will make it easier fr me 2 figure out.
    Regards

    ReplyDelete
  6. I did above code...and i got warning ,DatabaseTest may not respond to -createEditableCopyOfDatabseIfNeeded..Please tell me wat is this warning

    ReplyDelete
  7. I did the above steps.I got a warning DatabaseTestAppDelegate may not respond to createEditableCopyOfDatabaseIfNeeded.Let me know if I have been missing anything?

    ReplyDelete
  8. Well you can ignore this warning and run your application, it will work.

    This warning came because the compiler couldnot find the definition of this method in the header file. If you want to remove the warnings, go into the application delegate header file and write the following code just before @end
    -(void)createEditableCopyOfDatabaseIfNeeded;
    please make sure that there is a semicolon at the end f line(see above)

    let me know if it helped.

    ReplyDelete
  9. Hi I am currently creating an app and would like to add a search screen that would search the db based on keywords, and return the results. Does anyone know how to do this or seen a good example on how to do this?

    As I need to get the app completed I am also open to the possibility of consulting $$$ Thanks
    Lee

    ReplyDelete
  10. @whizzconsult:
    u cn follow the tutorial here: http://ved-dimensions.blogspot.com/2009/02/iphone-development-adding-search-bar-in.html

    shall u need any further assistance, u can contact me at ved.redjack@gmail.com

    ReplyDelete
  11. impossible to read with this shitty colors.

    ReplyDelete
  12. thank you for posting code as images.
    too difficult to put it as text?

    ReplyDelete
  13. @Anonymous: if you are looking for short cuts, this might not be the right place.
    still you are always welcome to NOT-visit again ;)

    ReplyDelete
  14. This is someone else. I want to say thanks! I haven't tried it yet, but look on the bright side. We at least get to work on our typing skills! I just appreciate someone making something available like these things. I'm new to programming period. So, I can't even open the db right yet. For any exec stm I get exc_bad_access. You don't have to try to help, I too new and a lost cause at this point..

    ReplyDelete
  15. Hi - and thank you for the tutorial...this is especially tough to figure out for us .Net guys! :)

    I'm getting stuck on the sqlite3_prepare_v2 statement - it's falling into the error code and I cannot figure out why. Error is: Assertion failure in .....rootViewController.m

    Any ideas?
    My email is mark@palmbeachsoftware.com and I appreciate any help. Thanks.

    ReplyDelete
  16. Hi Mark, can you paste the query you are writing...looks like statement is not getting prepared properly due to some reasons. Have you verified if you have initialized the database already?
    Regards

    ReplyDelete
  17. can u put a downloadable sourcecode link

    ReplyDelete
  18. Hi, m sorry I cant put entire source code here..coz I dont have it on my new machine.

    Besides it was written for 2.2 and will have to be changed for it to work with 3.0

    Will try anyways..

    ReplyDelete
  19. Hi ved...
    I am trying to understand the sqlite3 code for iphone and I don't understand why this code and section is required. Can you please explain? Why do you have to do this?

    "We have done nothing but created an editable copy of database into local document. "

    ReplyDelete
  20. Hello Kjf,
    a tableView (and sections and all) is there coz I wanted to display the results retrieved from db (I think apart frm nothing we are also doing that ;))

    Thas just the way I write here...but is it that interrupting to see 3 tiny extra delegate methods?

    ReplyDelete
  21. I don't see you closing the database anywhere. Isn't that going to cause memory leaks?

    ReplyDelete
  22. you can edit getDatabaseConnection method to return the same connection (or from a group of opened connections like pooling) when app closes connections are released. You can try closing connections also..bt reopening a new connection over and over again is costly so avoid it.
    btw memory leak is a different thing though this is connection leak :P

    ReplyDelete
  23. Awesome tutorial thank you! :D

    ReplyDelete
  24. Thanks for sharing the post. To know more about iphone Application Development, Iphone Application Development Outsourcing India, Google Phone Experts, Software Development Outsourcing, Iphone Software Development go to http://www.cyfuture.com

    ------------------
    Iphone Application Development India

    ReplyDelete
  25. hye... i'm already have a database for my application.. but how to get the update of the database.. when i change the table.. nothing happen when i run the program... im using mysql manager...

    ReplyDelete
  26. could you provide more details on how you are trying to update your database?

    ReplyDelete
  27. Thanks for sharing this tutorial.
    The build & run went fine but on my Simulator it just reverted to the icon.

    Any idea why?

    Thanks

    ReplyDelete
  28. @MJB: you mean to say it crashed immediately??

    I wrote this code nearly an year back. Try copying the code in new project and it should work.
    Also if you can try running it with 2.2 sdk. It works fine on my older version of xcode.

    ReplyDelete
  29. It just flashed on the screen and then showed the icon.

    I then downloaded the latest xcode 3.2.2 and ran it again and it all works fine.

    So all is fine with the latest SDK.

    Thanks again for the response.

    ReplyDelete
  30. The app only flash and show the icon...

    It is that the SDK version too?


    Rafael

    ReplyDelete
  31. I wrote it for 2.1, try with 2.1 and then change the sdk. Let me know if it doesn work

    ReplyDelete
  32. Hi,

    I can't thank you enough! I've been trying to do this for hours! Do you know where I can find information on adding to the db during runtime?
    Thanks again,
    Doug

    ReplyDelete
  33. Hi Doug,

    Why do you want to add a DB at runtime? I am not sure you can do that.

    You can always create new tables at runtime. so you can put the database while developing and add new tables at runtime as required.

    Lemme know if you find any other way,
    Thanks

    ReplyDelete
  34. Nice post. I have been searching for articles about valves and actuators and your post really helps. Thanks a lot for posting this. iphone

    ReplyDelete
  35. i crate the database and open it in the iphone app but i am not able to create table and insert data and retrieve it please help me on on this

    Thanks

    ReplyDelete
  36. Hi Manju, can u please past some piece of code which u think is not working?

    from the command line you can create a table with regular syntax and then copy the same .sqlite3 file into your project.

    ReplyDelete
  37. Thanks for you replay

    below code is write it; but i create database in the sqlite3 and also create table in that data base and also i insert data in to that table i can see the inserting data in sqlite manager but i code to display the data in the iphone,using UITable view formate please help on that


    -(void)DVDone:(id)sender{
    sqlite3 *newDBconnection;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"BTDB.db"];
    // Open the database. The database was prepared outside the application.

    if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {
    NSLog(@"connection successfully");
    //char *errMsg;
    //NSString *sql;
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS routetracking ( id TEXT PRIMARY KEY, name TEXT);"];
    NSLog(sql);
    if(sqlite3_exec (newDBconnection, [sql UTF8String], NULL, NULL, NULL)!= SQLITE_OK)
    {
    sqlite3_close(newDBconnection);
    NSAssert(0,@"Table failed to create.");
    }
    else {

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO routetracking VALUES ('002','kansas2');"];

    if(sqlite3_exec (newDBconnection, [sql UTF8String], NULL, NULL, NULL)!= SQLITE_OK)
    {
    sqlite3_close(newDBconnection);
    NSLog(@"data is not inserted");
    }
    else{


    }
    }
    }else {

    NSLog(@"Error in opening database :");

    }

    }


    Thanks And Regards
    Manju

    ReplyDelete
  38. sorry for the late reply..

    If you had previously added the same db then try resetting iPhone simulator and re-install.

    Please find out the point where it fails:
    check following:
    db is opened
    statement is prepared

    if statement prepare is failing check your table schema and find out if your query is consistent with it.

    if you are not able to open db itself, try using the code given in this post..

    lemme know if you are still not able to make it work...ll post some piece of specific code.

    ReplyDelete
  39. and post any exception that you are getting..

    ReplyDelete
  40. Debugger stopped.
    Program exited with status value:0.
    [Session started at 2010-08-06 14:52:03 -0500.]
    GNU gdb 6.3.50-20050815 (Apple version gdb-1469) (Wed May 5 04:36:56 UTC 2010)
    Copyright 2004 Free Software Foundation, Inc.
    GDB is free software, covered by the GNU General Public License, and you are
    welcome to change it and/or distribute copies of it under certain conditions.
    Type "show copying" to see the conditions.
    There is absolutely no warranty for GDB. Type "show warranty" for details.
    This GDB was configured as "x86_64-apple-darwin".sharedlibrary apply-load-rules all
    Attaching to process 9092.
    Pending breakpoint 1 - ""DateView.m":122" resolved
    2010-08-06 14:52:07.485 mwindowsnew[9092:207] Unknown class Datetableview in Interface Builder file.
    2010-08-06 14:52:13.456 mwindowsnew[9092:207] connection successfully
    Current language: auto; currently objective-c
    2010-08-06 14:59:47.100 mwindowsnew[9092:207] '012' - 'kansas2'
    (gdb) continue
    Program received signal: “EXC_BAD_ACCESS”.
    (gdb)
    this the exciption i got it


    please me

    thank you

    ReplyDelete
  41. put breakpoints and find where it happens

    ReplyDelete
  42. Thanks for putting this together. Ran into a problem in RootViewController.m with a "'statement' undeclared" error in the following method...

    -(void)initializeTableData {
    sqlite3 *db = [DatabaseTestAppDelegate getNewDBConnection];
    sqlite3_stmt *statement = nil;
    const char *sql = "select * from user";
    if(sqlite3_prepare_v2(db, sql, -1, &statment, NULL)!=SQLITE_OK)
    NSAssert1(0,@"Error preparing statement",sqlite3_errmsg(db));
    else {
    while (sqlite3_step(statement) == SQLITE_ROW)
    [tableData addObject:[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement, 1)]];
    }
    //release the resources
    sqlite3_finalize(statement);
    }

    ReplyDelete
  43. We are one of the leading web design company in chennai,our services are web design,web hosting,seo ,business blogging,internet marketing,seo consultants in chennai http://www.spiderindia.com

    ReplyDelete
  44. The rapid proliferation of smartphones and social media redefined the very meaning of "internet marketing." blackberry apps for business
    iphone application development

    ReplyDelete

Leave your suggestions