Using SQLite: Update The Database Records

Last week you learned how to fetch records from the database’s possession table and display them in the Master View’s Table View control. Today you’ll learn how to update a record in the database’s possessions table via the Detail view.

The SQL UPDATE Statement

This statement update a single record or multiple records in a SQLite database table. The UPDATE statement returns no result and has this general syntax:

Which records are updated is determined by the condition in the WHERE clause. The condition is tested against each record of the table. The most common condition use the equality condition to check a unique column, such as a PRIMARY KEY column.

Example 1

Say you created a table called employees in the application’s database file and inserted these records in it.

Now, you want to change the last record’s department value from “Human Resource” to “Marketing”. To do so you’d use the sqlite3_execute() function to prepare, step, and finalize this SQL statement:

Above query will update a single record that satisfies the condition in the WHERE clause. If there are multiple records (or rows) that satisfy the condition, all of them will be modified. If no WHERE clause is specified, all values in the “department” column will be modified. In other words; the “department” column values for all six records will be replace with the text, Marketing.

When you write an UPDATE query, make sure you set up the WHERE condition correctly to avoid lose of vital data.

Update a Record in The possessions Table

Now that you’ve been introduced to SQL’s UPDATE statement, you are ready to use it in the SqliteDatabase application to enable the app user to update one or more records in the itemsOwned’s possessions table. Assume the table currently have these records.

Say you wanted to change the third record’s item name and description, you’d have to first declare and implement this method in the AppDelegate class, which update the record in the database belonging to the oldItemName passed to the method as a parameter.

- (void)updatePossession:(NSString *)oldItemName field2:(Item *)formData;
{
  const char *sql = "UPDATE possessions SET name=?, description=?, photo=? WHERE name=?";
  sqlite3_stmt *compiledStatement;

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

  const char *newName = [formData.itemName UTF8String];
  const char *newDescription = [formData.itemDescription UTF8String];
  const char *dbName = [oldItemName UTF8String];

  //2. Bind the method's parameters
  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);
  sqlite3_bind_text(compiledStatement, 4, [formData.itemName UTF8String], -1, NULL);

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

  //3. Execute the sql statement
  result = sqlite3_step(compiledStatement);
  if (result != SQLITE_DONE) {
    NSLog(@"updatePossession method error:\n%s", sqlite3_errmsg(dbHandle));
  }

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

The second thing you’ll have to do is, declare this property in the DetailViewController.h file:

@property (strong, nonatomic) AppDelegate *appDelegate;

Next, add highlighted code in the DetailViewController.m file’s viewDidLoad method.

- (void)viewDidLoad
{
  [super viewDidLoad];
  self.appDelegate = [[UIApplication sharedApplication] delegate];

  // Populate the view's controls
  self.itemName.text = self.dbItem.itemName;
  self.itemDescription.text = self.dbItem.itemDescription;
  self.imageView.image = self.dbItem.itemImage;

  self.oldItemName = self.dbItem.itemName;
}

Now, add this code in the DetailViewController.m file’s updateButtonTapped: method.

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

  // Open the database
  [self.appDelegate openDatabase];

  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 {
    // 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 {
     // Reset the dbItem object's properties
     self.dbItem.itemName = self.itemName.text;
     self.dbItem.itemDescription = self.itemDescription.text;
     self.dbItem.itemImage = self.imageView.image;

      // Update a record in the database
      [self.appDelegate updatePossession:self.oldItemName field2:self.dbItem];

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

Here is the code to add 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];
}

Finally, here’s 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];
}

Run The Application

When you are done, run the application, click the view’s table row to load the items detail in the Detail View, modify the view’s Item Name and Item Description Text Field controls, then click the navigation bar’s Update button. The app will update the record in the database file, then display the Master View in the simulator’s window. You know the item was updated because the new item name is shown in the Master View’s Table View control.

This conclude this week’s workshop. Next week, you’ll learn how to delete a record from the application’s database file and the Master View’s Table View control.