Using SQLite: Create, Open and Close a Database File

In this lesson you will learn how to create, open and close a SQLite database file; but first, run the application and you will see output shown in the first image below. Click the + button and you will see output shown in the second image below. Clicking the Add view’s back button, will return you to the Master view. If the Master view table view control had data in its rows, you will be able to access the Detail view by tapping a row or delete a row. The third image shows what the Detail scene look like in the simulator window. Of course its controls will show information about the item the user selected from the Master View’s Table View control.

Now, here is a snap shot of the Project Navigator panel. We will add code in the AppDelegate class to declare and implement methods that’ll interact with the application’s database file. The Item class is for creating item objects, which we will add in the database file. Remaining class files (MasterViewController, DetailViewController, AddViewController) are connected to the storyboard scenes and we’ll add code in them to make their scenes functional.

Since we’ll be storing the SqliteDatabase application’s data in a SQLite database file, we need to do the follow:

1. Database enable the AppDelegate class
2. Get the full path to the SQLite database file
3. Open the database file
4. Close the database file
5. Modify the didFinishLaunchingWithOptions: method

Task 1: Database enable the AppDelegate class

The first thing you have to do is configure the AppDelegate class to use the SQLite engine; in other words, you’ll have to database enable the AppDelegate class. Here’s how to do it.

Do steps shown in these images to add the SQLite engine in the project.

After performing steps shown in above images, the SQLite engine will appear in locations indicated in the arrow in this image.

Click the AppDelegate.h file and modify its code to look like this:

Now that you’ve database enable the AppDelegate class, you can use the SQLite engine’s constants and functions in the Xcode project.

Task 2: Get the full path to the database file

Since you’ll be creating a SQLite database file in the application sandbox’s Documents folder, you need to get the full path to that folder, then append the database file name to it.

Here is the method to handle this task. Add it in the AppDelegate.m file. Be sure to declare the method in the AppDelegate.h file as well.

- (NSString *)docPath
{
  NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  NSString *docsFolder = [paths objectAtIndex:0];
  return [docsFolder stringByAppendingPathComponent:@"itemsOwned.sqlite"];
}

Task 3: Create and open a SQLite a database file

Now that we have the full path to the database file, we have to do this:

1. Check to see if the database file already exists in the application sandbox’s Documents folder; if it doesn’t, then create a database file in the application sandbox’s Documents folder, then close.
2. If the database file already exists in the sandbox’s Documents folder, then just open it.

Here is the code to add in the AppDelegate.m file. Don’t forget to declare the method in the AppDelegate.h file.

- (void) openDatabase
{
  // Get full path to the database file
  NSString *dbFile = [self docPath];

  // Check to see if the database file exists in the Documents folder
  BOOL dbExist = [[NSFileManager defaultManager] fileExistsAtPath:dbFile];

  if(!dbExist) {
    // It doesn't, so create and open the vintageDatabase file in the Documents folder
    int result = sqlite3_open([dbFile UTF8String], &dbHandle);

    if (result != SQLITE_OK) {
      // Unable to open the database file, so close it
      [self closeDatabase];

      // Display an error message
      NSLog(@"openDatabase Error:\n%s",sqlite3_errmsg(dbHandle));
    }
  }
  sqlite3_open([dbFile UTF8String], &dbHandle);
}

There are three points I want to make about the openDatabase method’s code.

1. Statements within the if(!dbExist) control structure is executed the very first time you run the application, because the sqlite database file doesn’t exists in the sandbox’s Documents folder yet.
2. On subsequent run of the application, statements within the if(!dbExist) control structure isn’t executed.
3. The final statement in the method is executed every time you run the application, since it is outside the the if() control structures.

Task 4: Close the database file

When you are done using the SQLite database file, you’ll have to close it by using the sqlite3_close() function. I want you to declare and implement this method in the AppDelegate class. Don’t forget to declare the method in the AppDelegate.h file.

- (void)closeDatabase
{
  // Close the SQLite db file
  int result = sqlite3_close(dbHandle);

  if (result != SQLITE_OK) {
    // Unable to close the db file, so display an error message
    NSLog(@"closeDatabase Error:\n'%s'", sqlite3_errmsg(dbHandle));
  }
}

Task 5: Modify the didFinishLaunchingWithOptions: method

Now, it is time to use the openDatabase and closeDatabase methods in the didFinishLaunchingWithOptions: method like this:

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
  [self openDatabase];
  [self closeDatabase];

  // Override point for customization after application launch.
  return YES;
}

Run The Application

Once you’ve entered above code in the didFinishLaunchingWithOptions: method, run the application. You should see output shown in Figure A below. Use Finder to verify that the database file was created in the application sandbox’s Documents folder. The full path to the Documents folder on my Mac is this:

/Users/merlyn/Library/Application Support/iPhone Simulator/7.0.3/D8EFB9F-7…/Documents

Figure B shows what you’ll see in the Documents folder.

Figure A	Figure B

That’s all for this week. Next week, you’ll learn how add table in the database file you created in the sandbox’s Documents folder.