Using SQLite: Fetch and Display The Database Records

Last week you learned how to add records in the Database. Today you will you will learn how to fetch records from the database and display them in the Master view’s Table View control.

Fetch Records From The Database

I want you to declare and implement this method in the AppDelegate class. It job is to fetch all records from the database’s possessions table, add them in a mutable array, then return the array to the caller of the method.

- (NSMutableArray *) retrievePossessions
{
  // This array is for holding records fetched from the database's possessions table
  NSMutableArray *dbRecords = [[NSMutableArray alloc] init];
  NSData *dbImage = [[NSData alloc] init];

  const char *sql = "SELECT name, description, photo FROM possessions";
  sqlite3_stmt *compiledStatement;

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

  if(result != SQLITE_OK) {
    NSLog(@"retrievePossessions Error:\n%s", sqlite3_errmsg(dbHandle));
  } else {
    //2. Execute the prepared statement
    while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
      // Convert the record's columns to Objective-C objects before assigning them to object variables
      //NSNumber *itemId = [NSNumber numberWithInt:sqlite3_column_int(compiledStatement, 0)];
      NSString *itemName = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(compiledStatement, 1)];
      NSString *itemDescription = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(compiledStatement, 2)];

      const char *binaryData = sqlite3_column_blob(compiledStatement, 3);
      int dataLength = sqlite3_column_bytes(compiledStatement, 3);
      dbImage = [NSData dataWithBytes:binaryData length:dataLength];

      // Instantiate an object from the Item class
      Item *record = [[Item alloc] init];

      // Initialize the object's properties
      record.itemName = itemName;
      record.itemDescription = itemDescription;
      record.itemImage = [UIImage imageWithData:dbImage];

      // Add the object in the mutable array
      [dbRecords addObject:record];
    }

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

  return dbRecords;
}

Display The Database Records

Now, I want you to modify the MasterViewController.h file’s code to what’s shown below.

#import <UIKit/UIKit.h>
#import "Item.h"
#import "AppDelegate.h"

@interface MasterViewController : UITableViewController

@property (strong, nonatomic) NSMutableArray *itemsOwned;
@property (strong, nonatomic) AppDelegate *appDelegate;

@end

Next, add this import statement in the MasterViewController.m file

#import "DetailViewController.h"

Add code shown below in the MasterViewController.m file method.

- (void)viewDidLoad
{
  [super viewDidLoad];
  // A pointer variable to the AppDelegate class so we can access its properties and methods
  self.appDelegate = [[UIApplication sharedApplication] delegate];

  // Fetch all records from the database file and place them in the mutable array
  [self.appDelegate openDatabase];
  self.itemsOwned = [self.appDelegate retrievePossessions];
  [self.appDelegate closeDatabase];
}

Add source code shown below in the Table View’s methods.

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView
{
  return 1;
}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
  return self.itemsOwned.count;
}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
  UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:@"Cell"];

  // Instantiate and initialize an Item object
  Item *row = [[Item alloc] init];
  row = self.itemsOwned[indexPath.row];

  // Add the object's itemName property in the table row and return
  cell.textLabel.text = row.itemName;
  return cell;
}

Test The App

Take the app for a spine and you will see the Item’s name in the Master View control.

Pass The Item to The Detail View

What you need to do now is add this code in the MasterViewController.m files’s prepareForSegue method. Code in the if() statement block basically pass the Item Name the user select from the Master view’s Table View control, to the DetailViewController’s dbItem property.

- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender
{
  NSIndexPath *indexPath = [self.tableView indexPathForSelectedRow];
  if ([segue.identifier isEqualToString:@"showDetail"]) {
    DetailViewController *destination = segue.destinationViewController;
    destination.dbItem = self.itemsOwned[indexPath.row];
  }
}

In the storyboard file, I’ve already set up the segue for the Table View cell object and given it a name, showDetail.

The Detail View Code

What you need to do now is add code in the DetailViewController class to populate the view’s controls and enable the user to select an photo from the simulator Photos app’s Photo Library. Start by modifying the DetailViewController.h file to this:

Figure 1

Now, add this code in the DetailViewcController.m file’s viewDidLoad method.

- (void)viewDidLoad
{
  [super viewDidLoad];
  // Populate the view's controls
  self.itemName.text = self.dbItem.itemName;
  self.itemDescription.text = self.dbItem.itemDescription;
 self.imageView.image = self.dbItem.itemImage;
}

Next, add this code in the chooseImageButtonTapped method.

- (IBAction)chooseImageButtonTapped {
  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];
}

Here is the code to implement two methods of the UIImagePickerControllerDelegate class.

- (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];
}

Test The DetailViewController Code

Now, run the program, click a row of the Table View control-see Figure 2, the detail view will load in the simulator’s window and it will look like Figure 3 below. Go ahead click the Choose Image button to select and a different photo for the item.

Figure 2	Figure 3

What you need to do now is add code Detail view’s Update button’s method to update the record in the database for the item shown in the view’s controls. You’ll learn how to do that next week.