IOS Programming - Insert,update,delete,select in SQLite

The SQLite database offers all the “standard” SQL constructs, including Select, Insert, Update and Delete. SQLite is an excellent solution for data persistence on IOS 5 devices: iPad, iPhone, iPod Touch. This tutorial demonstrates how to perform an insert, update and delete. For a detailed tutorial on creating, configuring a SQLite database for IOS 5 device and add it to your project and includes step by step code and instructions on how to select records from the database, read my tutorial: Tutorial on Creating an IOS 5 SQLite Database Application | IOS 5 | SQLite.

SQLite’s code in written in C, not Objective-C. C and Objective-C can be combined in the same Objective-C implementation files, or header files for that matter. For the SQLite parts of the code, you use C; for the IOS parts, you use Objective-C.

Overview of SQLite Operations


Like any other database, SQLite support where clauses when writing SQL statements for insert, updating, deleting and of course, selecting. You can also parametrized the value of the WHERE clause like you would do with other languages like Java and C#, or PHP.

Here is a snippet of SQL in SQLite with a parametrized WHERE clause. The “?” is replaced at runtime by the real value from a variable, field, method, etc. like any other program or database SQL statements.

Select col1, col2 from database where col1 = ?

What is different is how you pass the value to the statement. You need to bind the values to the statement. There are binding methods for each data type. The general syntax is:

int sqlite3_bind_datatype(sqlite3_stmt*, int, datatype);

Binding


Here is a list of binding methods for each supported data type.
  • Text : int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*), void(*));
  • Text16 : int sqlite3_bind_text16(sqlite3_stmt*, int, const char*, int n, void(*), void(*));
  • Null : int sqlite3_bind_text(sqlite3_stmt*, int);
  • Blob : int sqlite3_bind_blob(sqlite3_stmt*, int, const char*, int n, void(*), void(*));
  • Int : int sqlite3_bind_int(sqlite3_stmt*, int, int);
  • Int64 : int sqlite3_bind_int64(sqlite3_stmt*, int, int);
  • Double : Int64 : int sqlite3_bind_double(sqlite3_stmt*, int, double);
  • Value : int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value);
  • Zeroblob : int sqlite3_bind_blob(sqlite3_stmt*, int, int n);

Setting Up the CRUD Operations


Before getting into the actual operations of inserting, updating or deleting, we need to create an Objectice-C class to handle the operations and to do some housekeeping. Start by creating an Objective-C class, CrudOps.

In the header file define the following variables:
...
NSInteger dataId;
NSString *coltext;
NSInteger colint;
double coldbl;
NSFileManager *fileMgr;
NSString *homeDir;
NSString *title;


@property (nonatomic,retain) NSString *title;
@property (nonatomic,retain) NSString *coltext;
@property (nonatomic,retain) NSString *homeDir;
@property (nonatomic, assign) NSInteger dataId;
@property (nonatomic,assign) NSInteger colint;
@property (nonatomic, assign) double coldbl;
@property (nonatomic,retain) NSFileManager *fileMgr;

dataId, coltext, colint, coldbl represent the same columns as in the database. The fileMgr variable is to get the current path of the database in the Resource folder, homeDir is the Documents folder where we will copy the database because the Resource folder is always read-only. Finally the title variable is for an UIAlertView to display error messages.

Next configure the following methods for the crud operations:

-(void)CopyDbToDocumentsFolder;
-(NSString *) GetDocumentDirectory;
-(void)InsertRecords:(NSMutableString *)txt :(int) integer :(double) dbl;
-(void)UpdateRecords:(NSString *)txt :(NSMutableString *) utxt;
-(void)DeleteRecords:(NSString *)txt;

The CopyDbToDocumentFolder method will handle the copying of the database from the Resource folder to the Documents folder. Set the fileMgr object to the defaultManager. On the next line, define a dbPath NSString for the current database path. The copydbpath NSString is the variable for the target path to the Documents directory where we will copy the database.

Next we will use the removeItemAtPath method to delete the database from the Resource location to copy the database to the new location use the copyItemAtPath. If the copy method is unable to copy an alert is displayed to the user. Naturally this isn’t the best code, but is kept simplistic to demonstrate the operation. This is the code for the method:

...
-(void)CopyDbToDocumentsFolder{
NSError *err=nil;

fileMgr = [NSFileManager defaultManager];

NSString *dbpath = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:@"cruddb.sqlite"];

NSString *copydbpath = [self.GetDocumentDirectory
stringByAppendingPathComponent:@"cruddb.sqlite"];

[fileMgr removeItemAtPath:copydbpath error:&err];

if(![fileMgr copyItemAtPath:dbpath toPath:copydbpath error:&err])
{
UIAlertView *tellErr = [[UIAlertView alloc] initWithTitle:title message:@"Unable to copy
database." delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
[tellErr show];

}

}
....

The GetDocumentDirectory will get the path of the Documents directory. It will create the directory if it doesn’t exist. The fileMgr object uses the NSHomeDirectory() method to get the home directory of the app.
…
-(NSString *)GetDocumentDirectory{
fileMgr = [NSFileManager defaultManager];
homeDir = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];

return homeDir;
}
….

Inserting Records


Performing an insert is quite straightforward. Again start by setting the fileMgr to the defaultManager. Declare variables for the sqlite3 statement and the database. Next declare a const, sql, for the SQL query string. Afterwards, open the database and pass in the sql3_statement object: stmt and the query string. One important point I need to make is about indexing the parameter statement. Notice the second parameter in each “bind” method, the first one has a 1, the second a 2 and the third a 3. This is to establish the order of the parameter statements. I know this might be obvious to most while not so obvious for others.

Bind the input values using the sql3_bind_text, sql3_bind_int and sql3_bind_double. Apply the query with the sqlite3_step method and finally call the the sqlite3_finalize and sqlite3_close methods respectively.

These are the basic steps to doing CRUD (create, update and delete) operations as you will notice when we look at the other methods.

…
-(void)InsertRecords:(NSMutableString *) txt :(int) integer :(double) dbl{
fileMgr = [NSFileManager defaultManager];
sqlite3_stmt *stmt=nil;
sqlite3 *cruddb;...


//insert
const char *sql = "INSERT INTO data(coltext, colint, coldouble) VALUES(?,?,?)";

//Open db
NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
sqlite3_open([cruddatabase UTF8String], &cruddb);
sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(stmt, 2, integer);
sqlite3_bind_double(stmt, 3, dbl);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_close(cruddb); 
}
...

Updating Records


Updating records is almost a repeat of inserting records. The only change is syntax of the query string, which is a very standard SQL query string for updating records.
…
-(void)UpdateRecords:(NSString *)txt :(NSMutableString *)utxt{

fileMgr = [NSFileManager defaultManager];
sqlite3_stmt *stmt=nil;
sqlite3 *cruddb;


//insert
const char *sql = "Update data set coltext=? where coltext=?";

//Open db
NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
sqlite3_open([cruddatabase UTF8String], &cruddb);
sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [utxt UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_close(cruddb); 

}
….

Deleting Records


Again this is a repeat the first two; the only difference being the query string again.

…
-(void)DeleteRecords:(NSString *)txt{
fileMgr = [NSFileManager defaultManager];
sqlite3_stmt *stmt=nil;
sqlite3 *cruddb;

//insert
const char *sql = "Delete from data where coltext=?";

//Open db
NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
sqlite3_open([cruddatabase UTF8String], &cruddb);
sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_close(cruddb); 

}
….

UI Implementation


Now that we have our CRUD class and methods to allow us to perform CRUD operations, we will need to implement this functionality in our ViewController Scene (Figure 1). For the purposes of this tutorial, I created three fields and added an UISegementedControl to allow a user to choose the type of CRUD operation to do. By default the UISegmentedControl has two segements and it is set to Momentary. For our purposes, I will need to add an additional segment and change the type to: Bar. To make these changes, select the UISegmentedControl and open the Attributes Inspector and change the type to Bar and add an extra segment in the number of segements field. Finally select each segment in the combobox and rename them to Insert, Update and Delete.
Figure 1: App Layout and Output
Figure 1: App Layout and Output
Once the TextFields are setup, create a delegate for each field (Ctrl+left mouse button) to the ViewController object.

Next we will move to the kcbViewController header file to setup the IBOIutlets and IBActions and variables. Basically I created an UISegmentedControl variable, seg and I also created IBOutlets for each field on my ViewController and also one for the UISegmentedControl. I also created an IBAction for my UISegmentedControl on the ViewController. Take care when creating it to choose the UISegmentedControl as the Type and also change Action to IBAction, Click “Connect” create the connection.

Note: If you need to how to create connections and delegates, please read my other tutorials on performing these tasks. You will also find step by step instructions on how to make the keyboard close after you click on the return button. The links to the other tutorials are at the end of this tutorial.

In the kcbViewController header file
...
@interface kcbViewController : UIViewController
{
UISegmentedControl *seg;
}


@property (weak, nonatomic) IBOutlet UITextField *stringFld;
@property (weak, nonatomic) IBOutlet UITextField *intFld;
@property (weak, nonatomic) IBOutlet UITextField *doubleFld;
@property (weak, nonatomic) IBOutlet UISegmentedControl *seg;

- (IBAction)segButton:(id)sender;

In the implementation file, kcbViewController.m, I setup my setter or accessor methods using the @synthesize keyword. Finally I create a CrudOps object, called dbCrud and initialize it. Then I convert the value from textfield, self.stringFld.text to a NSMutableString so that it can be changed and also to match the input parameter of CRUD method sin my class.The rest of the code is pretty easy, I define a switch to detect which button is clicked on the UISegmentedControl using the selectedSegmentIndex and executing the corresponding method in the dbCrud object of the CrudOp class.

@synthesize stringFld;
@synthesize intFld;
@synthesize doubleFld;


- (IBAction)segButton:(id)sender {
CrudOp *dbCrud = [[CrudOp alloc] init];
NSMutableString *fldTxt = [NSMutableString stringWithString:self.stringFld.text];

switch(self.seg.selectedSegmentIndex)
{
case 0:
[dbCrud InsertRecords:fldTxt :[self.intFld.text intValue] :[self.doubleFld.text doubleValue]];
break;
case 1:
[dbCrud UpdateRecords:self.stringFld.text :fldTxt];

break;

case 2:
[dbCrud DeleteRecords:self.stringFld.text];
break;
}
}

Source Code

As usual here is the complete source code for the CrudOp class and ViewController class.

CrudOp.h

?
//
//  CrudOp.h
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface CrudOp : NSObject{
    NSInteger dataId;
    NSString *coltext;
    NSInteger colint;
    double coldbl;
    sqlite3 *db;
    NSFileManager *fileMgr;
    NSString *homeDir;
    NSString *title;
  
     
}
@property (nonatomic,retain) NSString *title;
@property (nonatomic,retain) NSString *coltext;
@property (nonatomic,retain) NSString *homeDir;
@property (nonatomic, assign) NSInteger dataId;
@property (nonatomic,assign) NSInteger colint;
@property (nonatomic, assign) double coldbl;
@property (nonatomic,retain) NSFileManager *fileMgr;
-(void)CopyDbToDocumentsFolder;
-(NSString *) GetDocumentDirectory;
-(void)InsertRecords:(NSMutableString *)txt :(int) integer :(double) dbl;
-(void)UpdateRecords:(NSString *)txt :(NSMutableString *) utxt;
-(void)DeleteRecords:(NSString *)txt;
@end

CrudOp.m

?
//
//  CrudOp.m
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//
#import "CrudOp.h"
@implementation CrudOp
@synthesize  coldbl;
@synthesize colint;
@synthesize coltext;
@synthesize dataId;
@synthesize fileMgr;
@synthesize homeDir;
@synthesize title;
-(NSString *)GetDocumentDirectory{
    fileMgr = [NSFileManager defaultManager];
    homeDir = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];
     
    return homeDir;
}
-(void)CopyDbToDocumentsFolder{
    NSError *err=nil;
    
    fileMgr = [NSFileManager defaultManager];
    
    NSString *dbpath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"cruddb.sqlite"];
     
    NSString *copydbpath = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
     
    [fileMgr removeItemAtPath:copydbpath error:&err];
    if(![fileMgr copyItemAtPath:dbpath toPath:copydbpath error:&err])
    {
        UIAlertView *tellErr = [[UIAlertView alloc] initWithTitle:title message:@"Unable to copy database." delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
        [tellErr show];
    }
                
}
-(void)InsertRecords:(NSMutableString *) txt :(int) integer :(double) dbl{
    fileMgr = [NSFileManager defaultManager];
    sqlite3_stmt *stmt=nil;
    sqlite3 *cruddb;
     
     
    //insert
    const char *sql = "Insert into data(coltext, colint, coldouble) ?,?,?";
     
    //Open db
    NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
    sqlite3_open([cruddatabase UTF8String], &cruddb);
    sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 2, integer);
    sqlite3_bind_double(stmt, 3, dbl);
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(cruddb);   
}
             
-(void)UpdateRecords:(NSString *)txt :(NSMutableString *)utxt{
     
    fileMgr = [NSFileManager defaultManager];
    sqlite3_stmt *stmt=nil;
    sqlite3 *cruddb;
     
     
    //insert
    const char *sql = "Update data set coltext=? where coltext=?";
     
    //Open db
    NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
    sqlite3_open([cruddatabase UTF8String], &cruddb);
    sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [utxt UTF8String], -1, SQLITE_TRANSIENT);
   
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(cruddb); 
     
}
-(void)DeleteRecords:(NSString *)txt{
     fileMgr = [NSFileManager defaultManager];
    sqlite3_stmt *stmt=nil;
    sqlite3 *cruddb;
     
    //insert
    const char *sql = "Delete from data where coltext=?";
     
    //Open db
    NSString *cruddatabase = [self.GetDocumentDirectory stringByAppendingPathComponent:@"cruddb.sqlite"];
    sqlite3_open([cruddatabase UTF8String], &cruddb);
    sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [txt UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(cruddb); 
     
}
@end

kcbViewController.h

?
//
//  kcbViewController.h
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//
#import <UIKit/UIKit.h>
@interface kcbViewController : UIViewController
{
    UISegmentedControl *seg;
}
@property (weak, nonatomic) IBOutlet UITextField *stringFld;
@property (weak, nonatomic) IBOutlet UITextField *intFld;
@property (weak, nonatomic) IBOutlet UITextField *doubleFld;
@property (weak, nonatomic) IBOutlet UISegmentedControl *seg;
- (IBAction)segButton:(id)sender;
@end

kcbViewController.m

?
//
//  kcbViewController.m
//  crud
//
//  Created by Kevin Languedoc on 11/29/11.
//  Copyright (c) 2011 kCodebook. All rights reserved.
//
#import "kcbViewController.h"
#import "CrudOp.h"
@implementation kcbViewController
@synthesize stringFld;
@synthesize intFld;
@synthesize doubleFld;
-(void)setSeg:(UISegmentedControl *)seg{
     
}
- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Release any cached data, images, etc that aren't in use.
}
#pragma mark - View lifecycle
- (void)viewDidLoad
{
    CrudOp *dbCrud = [[CrudOp alloc] init];
    [dbCrud CopyDbToDocumentsFolder];
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
}
- (void)viewDidUnload
{
    [self setStringFld:nil];
    [self setIntFld:nil];
    [self setDoubleFld:nil];
    [super viewDidUnload];
    // Release any retained subviews of the main view.
    // e.g. self.myOutlet = nil;
}
- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:animated];
}
- (void)viewDidAppear:(BOOL)animated
{
    [super viewDidAppear:animated];
}
- (void)viewWillDisappear:(BOOL)animated
{
    [super viewWillDisappear:animated];
}
- (void)viewDidDisappear:(BOOL)animated
{
    [super viewDidDisappear:animated];
}
- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation
{
    // Return YES for supported orientations
    return (interfaceOrientation != UIInterfaceOrientationPortraitUpsideDown);
}
- (IBAction)segButton:(id)sender {
    CrudOp *dbCrud = [[CrudOp alloc] init];
    NSMutableString *fldTxt = [NSMutableString stringWithString:self.stringFld.text];
     
    switch(self.seg.selectedSegmentIndex)
    {
        case 0:
            [dbCrud InsertRecords:fldTxt :[self.intFld.text intValue] :[self.doubleFld.text doubleValue]];
            break;
        case 1:
            [dbCrud UpdateRecords:self.stringFld.text :fldTxt];
            break;
             
        case 2:
            [dbCrud DeleteRecords:self.stringFld.text];
            break;
    }
}
@end

In Summary


Performing SQL operations using SQLite is very easy, once you know how to do it. SQLite offers a very handy data persistent store and is very easy to use. I hope you enjoyed this tutorial and found it useful.

Here are the links to my other IOS 5 tutorials that explain how to make connections, delegates. How to make the keyboard disappear after use.

Nhận xét

Đăng nhận xét

Bài đăng phổ biến