Skip to content

iPhone SDK – SQLite part 3

August 4, 2010

Because I haven’t had the time to create another video, I am going to write the 3rd part of my iPhone SQLite series on this blog. If you aren’t already familiar with SQLite, please view my beginner iPhone/SQLite tutorials at http://youtube.com/ConnorAlves.

One thing to keep in mind is that when your SQLite database file is within the standard Application directory, any data that was changed will be completely disregarded once the user quits the application. To allow your database to be persisted each time the app is opened or closed, you will need to copy your database file from the App directory to the Documents directory DURING RUNTIME. Within your AppDelegate file’s ApplicationDidFinishLaunching method, place this code:


NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
NSString *databasePath = [documentsDir stringByAppendingPathComponent:@"tutorial.sql"];
NSFileManager *fileManager = [NSFileManager defaultManager];

if ([fileManager fileExistsAtPath:databasePath] == NO) {
NSString *resourcePathtoDB = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"tutorial.sql"];
[fileManager copyItemAtPath:resourcePathtoDB toPath:databasePath error:nil];
[resourcePathtoDB release];
}

Basically all that this code does is copy the SQLite database file from the Application Resources directory to the Documents directory, only if it does not already exist.

Now that we have the file copied, we can properly INSERT and/or UPDATE records in the database. Without further blabbing, you can open your database file simply by assembling a string path to the file in the Documents directory, and then sending that string path to the Sqlite class:


NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
NSString *databasePath = [documentsDir stringByAppendingPathComponent:@"tutorial.sql"];
Sqlite *database = [[Sqlite alloc] initWithFile:databasePath];

The last thing to do is to actually send INSERT and UPDATE query strings to the database like so:


[database executeNonQuery:@"INSERT INTO names (name) VALUES ('Connor');"];

Incase you didn’t understand how this query was structured; you can see that ‘names’ is the identifier of the table we are trying to insert into, and ‘name’ within the parentheses is the column we want to write some data to. Lastly, we say ‘VALUES’ indicating that we are specifying the corresponding value(s) for the field(s) specified. In this case, the value is ‘Connor’.

The very last thing to talk about is the UPDATE command. When updating rows or tables, it is very important to ensure that your query has other parameters, such as ‘WHERE’, unless you want to update the whole table for whatever reason. The reason why I say this is that if you were to send a command like “UPDATE names SET name=’Connor’;”, all of the ‘name’ fields in the table will be changed to ‘Connor’, which is not what we want. Here’s a decent example:


[database executeNonQuery:@"UPDATE names SET name='Fred' WHERE id=1;"];

The code above would be completely satisfactory, because we know that at all times this query would only be updating at most 1 row.

That’s all I have to say, thank you for visiting my blog, and don’t forget to follow me on Twitter and Youtube!

Advertisements
9 Comments
  1. D Barrett permalink

    Thank you SOOOOOOOOOOOOOOOOOOOOOOOOOOOO much! You da man!

    • Sorry for the wait, but it’s finally here 😀

      • D Barrett permalink

        Much appreciated!

        So to clarify, say I want to insert something into a database with a button action, do I put this bit of code in the viewDidLoad method of a view controller?

        NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documentsDir = [documentPaths objectAtIndex:0];
        NSString *databasePath = [documentsDir stringByAppendingPathComponent:@”tutorial.sql”];
        Sqlite *database = [[Sqlite alloc] initWithFile:databasePath];

        And the INSERT command in the button action method?
        …or do I put everything in the button action method?

        Thanks again.

      • You are partially correct, however, you’ll need to initiate the database variable as an instance variable within the .h file so that it can be accessed inside of any of your other methods (for example, an IBAction for a button). Then, within the viewDidLoad method the only thing you would change is:


        Sqlite *database = .........;


        To:


        database = ...........;

  2. D Barrett permalink

    Outstanding. Thx!

  3. george permalink

    Hi,
    Thanks for tutorials. I have a question.

    [super viewDidLoad];

    NSString *myDB = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@”crmdatabase.sql”];
    database = [[Sqlite alloc] init];
    [database open:myDB];
    [database executeNonQuery:@”INSERT INTO names (pin,username,password) VALUES (‘qwe’,’asd’,’zx’);”];
    NSArray *results = [database executeQuery:@”SELECT * from names;”];
    for(NSDictionary *row in results){
    pin.text = [row valueForKey:@”pin”];
    username.text = [row valueForKey:@”username”];
    password.text = [row valueForKey:@”password”];

    }

    here if I use like that, it looks like it is saving to table and I can see them on pin.txt etc. But when I run it from console select * from names, it doesn’t show up the insert into command. It is still the old one. Can you please write me a solution with it? It looks like it is like writing to table on cache but in real db it is not writing.

  4. george permalink

    and I created this in delegate.m file :

    -(void)applicationDidFinishLaunching:(UIApplication *)application
    {

    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    NSString *databasePath = [documentsDir stringByAppendingPathComponent:@”crmdatabase.sql”];
    NSFileManager *fileManager = [NSFileManager defaultManager];

    if ([fileManager fileExistsAtPath:databasePath] == NO) {
    NSString *resourcePathtoDB = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@”crmdatabase.sql”];
    [fileManager copyItemAtPath:resourcePathtoDB toPath:databasePath error:nil];
    [resourcePathtoDB release];
    }
    }

  5. john permalink

    This statement caused *me* to have a crash only the first time the app was run…
    [resourcePathtoDB release]

    It could be something else, but for me, commenting out this line fixed it!

  6. praveen permalink

    i have an error with ==Sqlite *database = [[Sqlite alloc] initWithFile:databasePath];== that Unknown type name ,,wen i replace ‘Sqlite’ with sqlite 3….and alloc and int,,,then xcode give an error that reciever type sqlite3 is not an objective c class ,,,and it can’t initWith file databasepath….how to solve this issue,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: