Using SQLite: Add Records in The Database

on May 28, 2014 |

Last week you created a table called possession in the application’s database file. Today you will learn how to do the following tasks:

Add records in the database file
Fetch all records from the database file
Display the database records in the Master view’s Table View control

The Item Class

Now, before we get started in performing above tasks, you’ll have to create an Item class in the SqliteDatabase project. Take a look at this diagram it shows how we’ll use the it to add records in the SQLite database file.

Figure 1

Now that you know how we’ll use the Item class, use images shown in Figure 3 to create the Item class in the project. Make it a subclass of the NSObject class.

Figure 2

Next, add this code in the Item.h file.

#import <Foundation/Foundation.h>

@interface Item : NSObject

// The class properties
@property (nonatomic, retain) NSString *itemName;
@property (nonatomic, retain) NSString *itemDescription;
@property (nonatomic, retain) UIImage *itemImage;

@end

The AppDelegate Class Code

Now, I want you add this import statement in the AppDelegate.h file:

#import “Item.h”

Declare and implement these methods in the AppDelegate.m file. The first method add a record in the database’s possessions table. The second method fetch all records from the possessions table, add them in a mutable array, then return it to the caller of the method.

- (void) createRecord:(Item *)formData;
{
  // Set up the INSERT string
  const char *sqlStatement = "INSERT INTO possessions (name, description, photo) VALUES (?, ?, ?)";
  sqlite3_stmt *compiledStatement;

  // Prepare the sqlStatement
  int result = sqlite3_prepare_v2(dbHandle, sqlStatement, -1, &compiledStatement, NULL);

  // Bind the sqlStatement parameters to a value
  sqlite3_bind_text(compiledStatement, 1, [formData.itemName UTF8String], -1, NULL);
  sqlite3_bind_text(compiledStatement, 2, [formData.itemDescription UTF8String], -1, NULL);
  NSData *imageData = UIImagePNGRepresentation(formData.itemImage);
  sqlite3_bind_blob(compiledStatement, 3, [imageData bytes], [imageData length], NULL);

  if(result != SQLITE_OK) {
    NSLog(@"INSERT ERROR:\n%s", sqlite3_errmsg(dbHandle));
  }

  // Execute the prepared statement (Insert a record in the database file)
  result = sqlite3_step(compiledStatement);

  // Finalize the prepared statement
  sqlite3_finalize(compiledStatement);
}

The AddViewController Class Code

Since the app user will be using the Add View screen (see Figure 4) to add records in the database file, you’ll have to add code in its header and implementation file. Start by clicking the AddViewController.h file and modify its code to what’s shown in Figure 5.


Figure 3: The Add View screen	Figure 4: The AddViewController.h file

What you’ve done is imported two class header files: Item and AppDelegate. You conformed the AddViewController class to the UIImagePickerControlIerDelegate class and the UINavigationControllerDelegate class so methods of the UIImagePickerControllerDelegate class will work.
Now, add this code in the AddViewController.m file’s viewDidLoad method.

- (void)viewDidLoad
{
  [super viewDidLoad];
  // Initialize the dbItem object
  self.dbItem = [[Item alloc] init];

  // A pointer variable to the AppDelegate class so we can access its properties and methods
  self.appDelegate = [[UIApplication sharedApplication] delegate];
}

When the user tap the Add View Controller navigation bar Save button, we want the app to perform main tasks listed in the image below. The flowchart is another way of representing the tasks.

Figure 5

Here is the source code to add in the saveButtonTapped: method.

- (IBAction)saveButtonTapped:(UIBarButtonItem *)sender {
  // Trim itemName field
  NSString *cleanString = [self.itemName.text stringByTrimmingCharactersInSet:
  [NSCharacterSet whitespaceAndNewlineCharacterSet]];

  if (cleanString.length == 0) {
    // Display an Alert View
    UIAlertView *alert = [[UIAlertView alloc]
    initWithTitle:@"Required Data Missing"
    message:@"Item Name"
    delegate: nil
    cancelButtonTitle: @"OK"
    otherButtonTitles: nil];
    [alert show];
  } else {
    // Open the database
    [self.appDelegate openDatabase];

    // Query the database
    if ([self.appDelegate checkForDuplicateEntry:self.itemName.text]) {
      // Display an Alert View
      UIAlertView *alert = [[UIAlertView alloc]
      initWithTitle:@"Save Failed"
      message:@"The item Name already exists in the database"
      delegate: nil
      cancelButtonTitle: @"OK"
      otherButtonTitles: nil];
      [alert show];
    } else {
      // Add a record in the database
      [self.appDelegate createRecord:self.dbItem];

       // Close the database
       [self.appDelegate closeDatabase];

      // Perform a segue which return the user back to the Master view
      [self performSegueWithIdentifier:@"gotoMasterView" sender:self];
    }
  }
}

Here is the code for the checkForDuplicateEntry: method. Add it in the AppDelegate.m file. Don’t forget to declare the method in the AppDelegate.h file.

- (BOOL) checkForDuplicateEntry:(NSString *)itemName
{
  const char *sql = "SELECT name FROM possessions WHERE name = ?";
  sqlite3_stmt *compiledStatement;
  BOOL recordFound = NO;

  //1. Prepare the sql statement
  int result = sqlite3_prepare_v2(dbHandle, sql, -1, &compiledStatement, NULL);

  // 2. Bind the sqlStatement parameters to a value
  sqlite3_bind_text(compiledStatement, 1, [itemName UTF8String], -1, NULL);

  if(result != SQLITE_OK) {
    NSLog(@"checkForDuplicateEntry Error:\n%s", sqlite3_errmsg(dbHandle));
  }

  //3. Execute the prepared statement
  if (sqlite3_step(compiledStatement) == SQLITE_ROW) {
    recordFound = YES;
  }

  //4. Finalize the prepared statement
  sqlite3_finalize(compiledStatement);

  return recordFound;
}

When the user click the Choose Image button, we want the app to launch the simulator’s Photos application so the user can select a photo, which the app load in the Image View control. Here is the source code to add in the button’s chooseImageButtonTapped: method.

- (IBAction)chooseImageButtonTapped {
  // Declare and initialize a ImagePicker controller object
  UIImagePickerController *imagePicker = [[UIImagePickerController alloc] init];

  // Set the imagePicker delegate so we can use its delegate methods
  imagePicker.delegate = self;

  // Set the imagePicker object to access the Photos app's Photo Library
  imagePicker.sourceType = UIImagePickerControllerSourceTypePhotoLibrary;

  // Launch the Photos app
  [self presentViewController:imagePicker animated:YES completion:nil];
}

Add these methods of the UIImagePickerController class below the chooseImageButtonTapped method.

- (void)imagePickerController:(UIImagePickerController *)picker didFinishPickingMediaWithInfo:(NSDictionary *)info
 {
 // Declare an image object, set it to the image the user picked
 UIImage *chosenImage = [info valueForKey:UIImagePickerControllerOriginalImage];

// Set the view's ImageView control's Image property with the image the user picked
 self.imageView.image = chosenImage;

// The user finished picking an image, so dismiss the imagePicker
 [self dismissViewControllerAnimated:YES completion:nil];
 }

- (void)imagePickerControllerDidCancel:(UIImagePickerController *)picker
 {
 // The user tapped the imagePicker's Cancel button, so dismiss the imagePicker
 [self dismissViewControllerAnimated:YES completion:Nil];
 }

That’s it you’ve entered necessary code to make the Add View work.

Run The Application

I want you to run the application. Go to the Add view screen enter information shown in Figure 6, in the view’s Text Field controls. Click the Choose Image button to select an image from the Simulator’s Photos app-see Figure 7. When you’re done, click the navigation bar’s Save button. The app will add record in the database file, then redirect you to the Master View screen-see Figure 8.