Modifying SQLite

SQLite Binding, Inserting, Updating, and Deleting

SQL allows limiting data to only the data needed via the where clause. For instance, the following statement only selects records whose age column is greater than 30. select * from mytable where age > 30 When placing SQL statements like this into a SQLite statement, you can parameterize the where clause’s value. For instance, to parameterize age’s value, write the following code. select * from mytable where age > ? You then bind your program’s value to the SQL statement’s parameter. Binding You bind one of your program’s values to a SQL statement’s parameter using a bind method (Listing 16-12). Different data types have different bind methods. Listing 16-12 SQLite bind methods (from the SQLite online documentation) int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n); NOTE Bindings start with 1 rather than 0. For instance, the following code snippet shows a SQL statement and its subsequent binding. const char * select_statement = ‖Select * from photos where name = ?‖; sqlite3_bind_text(select_statement, 1, [photo.name UTF8String], -1, SQLITE_TRANSIENT); The first argument is a pointer to the prepared statement. The second argument is the SQL statement’s parameter number. The third argument is the value that should be bound to the SQL statement’s parameter. The fourth argument is the number of bytes in the value—if negative, the length is automatically determined from the C string

Insert, Update, and Delete

There is little difference between the steps for inserting, updating, or deleting records using the SQLite C library. The primary difference is you only call the sqlite3_step method once. Usually, you use insert, update, or delete with binding. For instance, insert into customers (name, age, company, location) values (?, ?, ?,?); or update customers set location = ? where company = ?; or delete customers where company = ?; In the following task, you insert, update, and delete a record.

Inserting, Updating, and Deleting Records

Making a Database Writable

1. Open the MYDBProject project in Xcode.
2. Add a class method named moveDatabase to Remember, a class method uses a plus rather than a minus.
3. Implement the method in m as in Listing 16-13.
4. Modify the getAllPhotos method in PhotosDAO to obtain the records from the documents
5. Open m and add a call to the moveDatabase method to the first line of viewDidLoad (Listing 16-14).

Listing 16-13 The moveDatabase and getAllPhotos methods + (void) moveDatabase { NSFileManager *fileManager = [NSFileManager defaultManager]; NSString *theDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"myDatabase.sqlite"]; NSError *error; BOOL success;   NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString * docsDir = [paths objectAtIndex:0]; NSString * newPath = [docsDir stringByAppendingPathComponent:@"chapter18.sqlite"]; [fileManager removeItemAtPath:newPath error: &error]; success = [fileManager copyItemAtPath:theDBPath toPath:newPath error: &error]; if (!success) { NSLog(@"Failed to copy database...error handling here %@.", [error localizedDescription]); } } - (NSMutableArray *) getAllPhotos { NSMutableArray * photosArray = [[NSMutableArray alloc] init]; @try { NSFileManager *fileManager = [NSFileManager defaultManager]; NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString * docsDir = [paths objectAtIndex:0]; NSString * theDBPath = [docsDir stringByAppendingPathComponent: @"myDatabase.sqlite"]; BOOL success = [fileManager fileExistsAtPath:theDBPath]; if (!success) { NSLog(@"Failed to find database file '%@'."); } if (!(sqlite3_open([theDBPath UTF8String], &database) == SQLITE_OK)) { NSLog(@"An error opening database, normally handle error here."); } const char *sql = "SELECT id,name,photo FROM photos"; sqlite3_stmt *statement; if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) { NSLog(@"Error, failed to prepare statement, normally handle error here."); } while (sqlite3_step(statement) == SQLITE_ROW) { PhotoDAO * aPhoto = [[PhotoDAO alloc] init]; aPhoto.photoID = sqlite3_column_int(statement, 0); aPhoto.name = [NSString stringWithUTF8String:(char *) sqlite3_column_text(statement, 1)]; const char * rawData = sqlite3_column_blob(statement, 2); int rawDataLength = sqlite3_column_bytes(statement, 2); NSData *data = [NSData dataWithBytes:rawData length: rawDataLength]; aPhoto.photo = [[UIImage alloc] initWithData:data]; [photosArray addObject:aPhoto]; } if(sqlite3_finalize(statement) != SQLITE_OK) { NSLog(@"Failed to finalize data statement, normally error handling here."); }   if (sqlite3_close(database) != SQLITE_OK) { NSLog(@"Failed to close database, normally error handling here."); } } @catch (NSException *e) { NSLog(@"An exception occurred: %@", [e reason]); return nil; } return photosArray; } Listing 16-14 The viewDidLoad method - (void)viewDidLoad { [PhotosDAO moveDatabase]; PhotosDAO * myPhotos = [[PhotosDAO alloc] init]; self.photos = [myPhotos getAllPhotos]; [self.theImageView setImage:((PhotoDAO *)[self.photos objectAtIndex:0]).photo]; [self.theLabel setText:((PhotoDAO *)[self.photos objectAtIndex:0]) .name]; [myPhotos release]; [super viewDidLoad]; } The first thing you do is make the database writeable. The Resources folder is read-only. Saving changes requires the database to be writable, so you copied the database to your documents directory. You also modified the getAllPhotos method so it obtained the database from the application’s document directory rather than the resources directory. Inserting Records  

1. Add png to the project’s Resources group. You can find this photo in the book’s Resources folder.
2. Add a new method to PhotosDAO called Implement the method (Listing 16- 15).
3. Create a new IBAction in MyDBProjectViewController called addKidPhoto (Listing 16- 16).
4. Build and save the
5. Open xib and add a new Bar Button item to the toolbar. Change the bar button’s title to Add.
6. Connect the addKidPhoto action to the Add \
7. Save and exit Interface
8. Click Build And Go to run the application in the iPhone Simulator (Figure 16-7).

  Listing 16-15 The addPhoto method - (void) addPhoto : (PhotoDAO *) photo { const char * sql = "insert into photos (name, photo) values (?, ?)"; sqlite3_stmt *insert_statement = nil; NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES);                                

Figure 16-7 Running the application on my iPhone

  NSString * docsDir = [paths objectAtIndex:0]; NSString * thePath = [docsDir stringByAppendingPathComponent: @"myDatabase.sqlite"]; sqlite3_open([thePath UTF8String], &database); sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL); sqlite3_bind_text(insert_statement, 1, [photo.name UTF8String], -1, SQLITE_TRANSIENT); NSData * binData = UIImagePNGRepresentation(photo.photo); sqlite3_bind_blob(insert_statement, 2, [binData bytes], [binData length], SQLITE_TRANSIENT); sqlite3_step(insert_statement); sqlite3_finalize(insert_statement); sqlite3_close(database); } Listing 16-16 The addKidPhoto IBAction - (IBAction) addKidPhoto: (id) sender { static BOOL wasAdded; if(!wasAdded) { PhotosDAO * myPhotos = [[PhotosDAO alloc] init]; PhotoDAO * aPhoto = [[PhotoDAO alloc] init]; NSString * imgPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"kids.png"]; aPhoto.name = @"Kids Both"; aPhoto.photo = [[UIImage alloc] initWithContentsOfFile:imgPath]; [myPhotos addPhoto:aPhoto];     [self.photos release]; self.photos = [myPhotos getAllPhotos]; [myPhotos release]; wasAdded = YES; } } The addPhoto method (Listing 16-15) allows new photos to be inserted. As it is a simple example, the method merely gets the photo from your resources group. The addPhoto method first creates a SQL string with parameters. The method then replaces the question marks by binding them to the appropriate value. For instance, the name column is text, so addPhoto binds it to a C string. The UIImage is binary, so it is bound to a blob. After binding, addPhoto then inserts the record by calling the sqlite3_step method. This method is called only once, as no data is returned from the insert statement. Notice, for brevity, an examination of the return code is omitted, as is other error handling from Listing 16-6 forward. Updating Records

1. Return to the Xcode project.
2. Drag png from the book’s Resources folder to the Resources group in Xcode.
3. Add a new NSInteger called currentID to m. Change the changeImage method to update this new variable with the current photo’s id from the database (Listing 16-17).
4. Add a new method called changeAPhotoImage to PhotosDAO (Listing 6-18).
5. Add a new IBAction called changePhotosImage to MyDBProjectViewController (Listing 16-19). Save and build the
6. Open xib in Interface Builder and add another bar button to the toolbar. Change the button’s title to Change.
7. Connect the changePhotosImage action to the Change
8. Save and exit Interface Click Build And Go to run the application in the iPhone Simulator (Figure 16-8).

Figure 16-8 Changing the image

  Listing 16-17 The currentID variable, and modified changeImage NSInteger currentID = 0; - (IBAction) changeImage: (id) sender { static NSInteger currentElement = 0; if(++currentElement == [self.photos count]) currentElement = 0; PhotoDAO * aPhoto = (PhotoDAO *) [self.photos objectAtIndex: currentElement]; currentID = aPhoto.photoID; [self.theLabel setText:aPhoto.name]; [self.theImageView setImage:aPhoto.photo]; } Listing 16-18 The changeAPhotoImage method

• (void) changeAPhotoImage: (UIImage *) image theID: (NSInteger) photoID { const char * sql = "update photos set photo = ? where id = ?"; sqlite3_stmt *update_statement = nil;

NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString * docsDir = [paths objectAtIndex:0]; NSString * thePath = [docsDir stringByAppendingPathComponent:@"chapter18.sqlite"]; sqlite3_open([thePath UTF8String], &database); sqlite3_prepare_v2(database, sql, -1, &update_statement, NULL); NSData * binData = UIImagePNGRepresentation(image); sqlite3_bind_blob(update_statement, 1, [binData bytes], [binData length], SQLITE_TRANSIENT); sqlite3_bind_int(update_statement, 2, photoID); sqlite3_step(update_statement); sqlite3_finalize(update_statement); sqlite3_close(database); } Listing 16-19 The changePhotosImage method -(IBAction) changePhotosImage: (id) sender { PhotosDAO * myPhotos = [[PhotosDAO alloc] init]; NSString * imgPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"woman.png"]; [myPhotos changeAPhotoImage:[[UIImage alloc] initWithContentsOfFile: imgPath] theID: currentID]; [self.photos release]; self.photos = [myPhotos getAllPhotos]; [myPhotos release]; } Updating a record is as straightforward as inserting it. The changeAPhotoImage first creates a SQL string with parameters. It then binds a file’s binary data to photo and an integer to id. After binding, it then calls the step function once, finalizes the statement, and closes the database. Notice that updating requires the record’s id, as SQLite uses the id to update the correct record. To accommodate this requirement, you added a currentID variable and changed the changeImage method to set the currentID with the currently selected photo record. Deleting Records

1. Quit the application and return to Xcode.
2. Add a new method called deletePhoto to PhotosDAO (Listing 16-20).
3. Create a new IBAction called deletePhoto to MyDBProjectViewController (Listing 16- 21).
4. Move the static NSInteger currentElement from the changeImage method in Listing 16- 11 to just below the currentID variable (Listing 16-22). Remove the static
5. Save and build.
6. Open MyDBProjectViewController.xib in Interface Builder.

       Figure 16-9 The MyDBProjectViewController view’s canvas  

1. Add another button to the toolbar and change its title to Delete.
2. Resize the image and move the label to above the toolbar, as you are running out of space on the toolbar (Figure 16-9).
3. Connect the button to the deletePhoto
4. Click Build And Go and try deleting a photo.

Listing 16-20 The deletePhoto method in PhotosDAO(void) deletePhoto: (NSInteger) photoID { const char * sql = "delete from photos where id = ?"; sqlite3_stmt *delete_statement = nil; NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString * docsDir = [paths objectAtIndex:0]; NSString * thePath = [docsDir stringByAppendingPathComponent: @"chapter18.sqlite"]; sqlite3_open([thePath UTF8String], &database); sqlite3_prepare_v2(database, sql, -1, &delete_statement, NULL); sqlite3_bind_int(delete_statement, 1, photoID);   sqlite3_step(delete_statement); sqlite3_finalize(delete_statement); sqlite3_close(database); } Listing 16-21 The deletePhoto IBAction in MyDBProjectViewController - (IBAction) deletePhoto : (id) sender { PhotosDAO * myPhotos = [[PhotosDAO alloc] init]; [myPhotos deletePhoto:currentID]; [self.photos release]; self.photos = [myPhotos getAllPhotos]; currentElement = 0; [myPhotos release]; }   Listing 16-22 Placing currentElement at the class’s top so it shared in the class @implementation MyDBProjectViewController @synthesize photos; @synthesize theImageView; @synthesize theLabel; NSInteger currentID = 0; NSInteger currentElement = 0; The delete statement follows the same pattern as insert and update. The only real difference is the SQL string. const char * sql = "delete from photos where id = ?";