iOS SQLite Tutorial for Beginners

ios sqlite tutorial

SQLite Database Engine is a software library that provides a Relational Database Management System similar to MySQL or Oracle. It is ‘Server-Less’, ‘Self-Contained’ with ‘Zero Configuration’ and uses ‘Transactional SQL’. These features make it a perfect candidate for mobile devices. In IOS SQLite is easily embeded with the application, as it is simply a file format. It replaces the ‘plist’ implementation for storing and handling the complex data in IOS. This tutorial focuses on the IOS SQLite CRUD operations.

For interesting iOS tutorials, see this course at Udemy.com.

How to use SQLite in iOS?

The ‘libsqlite3.dylib’ library in framework section is required to be added in an application, which is a C++ wrapper that provides the API to the SQLite commands. To use the SQLite commands, it is mandatory to import the ‘sqlite3’ header file.

IOS SQLite Create Database and Table

Creating SQLite Database is similar to creating a text file. In iOS, a path of document directory is used to save the database file in application because only that folder is accessible by iOS application for user data.

To execute any query in SQLite database, first a database is opened for querying, for this purpose a ‘sqlite3_open’ method is used with the path of a database and ‘sqlite3’ object as arguments. On successful execution, it returns the constant ‘SQLITE_OK’ equals to zero.

Finally, to create a table in database an iOS SQLite API method ‘sqlite3_exec’ is used with open sqlite3 object, a SQL query of create table, callback function, first argument to callback function and error message container as arguments. On successful execution, it returns ‘SQLITE_OK’ or in case of failed execution, SQL error and missing database a ‘SQLITE_ERROR’ equal to ‘1’.

Following code snippet illustrates the use of described API method to create an ‘EMPLOYEE’ table in ‘StaffManagement.db’, a SQLite database.

 

Code snippet:

#import <sqlite3.h>

@interface DataBaseManager : NSObject

{

NSString *databasePath;

}

+(DataBaseManager*)getInstance;

-(BOOL)createDB;

@end

 

static DataBaseManager *sharedInstance = nil;

static sqlite3 *database = nil;

static sqlite3_stmt *statement = nil;


@implementation DataBaseManager

+(DataBaseManager*)getInstance{

if (!sharedInstance) {

sharedInstance = [[super allocWithZone:NULL]init];

[sharedInstance createDB];

}

return sharedInstance;

}

-(BOOL)createDB{

NSString *docsDir;

NSArray *dirPaths;

// Get the documents directory path

dirPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES);

docsDir = dirPaths[0];

// Build the path to the database file

databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent: @"StaffManagement.db"]];

BOOL isSuccess = YES;

NSFileManager *filemgr = [NSFileManager defaultManager];

if ([filemgr fileExistsAtPath: databasePath ] == NO)

{

const char *dbpath = [databasePath UTF8String];

//Open the database connection

if (sqlite3_open(dbpath, &database) == SQLITE_OK)

{

char *errMsg;

const char *sql_stmt =

"create table if not exists EMPLOYEE (employeeID integer primary key NOT NULL, employeeName text NOT NULL, employeeImage blob, employeePhoneNumber text NOT NULL);";

//Execute the query

if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg)

!= SQLITE_OK)

{

isSuccess = NO;

}

sqlite3_close(database);

return isSuccess;

}

else {

isSuccess = NO;

}

}

return isSuccess;

}

@end

In the above code snippet, a singleton ‘DataBaseManager’ class subclass of ‘NSObject’ is used. On initialization, it creates a ‘StaffManagement.db’ and ‘EMPLOYEE’ table in ‘createDB’ instance method which returns true on success and false if any failure occurs.

Interested in learning more about iOS programming? Take a course at Udemy.com.

IOS SQLite Insert, Update and Delete

To insert and update data, an opened database is required and then an IOS SQLite API method ‘sqlite3_prepare_v2’ is used with opened SQLite object, SQL insert query or update query, length of query string in bytes and SQLite statement ‘sqlite3_stmt’ handle as arguments. On successful execution, ‘SQLITE_OK’ is returned and the SQLite statement points to the compiled prepared statement that is executed later using API method ‘sqlite3_step’ with statement pointer as an argument and returns ‘SQLITE_DONE’ (has value 101 shows ‘sqlite3_step’ is done executing) on success.

Following code snippet demonstrates the insertion of new employee data in ‘EMPLOYEE’ table using IOS SQLite API methods.

Code Snippet (add method in ‘DataBaseManager’):

-(BOOL) saveEmployee:(NSString*)employeeID        
employeeName:(NSString*)employeeName       
employeeImage:(NSData*)employeeImage 
employeePhoneNumber:(NSString*)employeePhoneNumber{

const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &database) == SQLITE_OK)

{        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO EMPLOYEE (employeeID, employeeName, employeeImage, employeePhoneNumber) values (\"%d\",\"%@\",?, \"%@\")", [employeeID integerValue], employeeName, employeePhoneNumber];                const char *insert_stmt = [insertSQL UTF8String];        if (sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL)!= SQLITE_OK)

return NO;

else        {

//Add a statement parameter to a query.

sqlite3_bind_blob(statement, 1, [employeeImage bytes], [employeeImage length], SQLITE_TRANSIENT);

if (sqlite3_step(statement) == SQLITE_DONE)

{                sqlite3_reset(statement);                return YES;            }

else

{                sqlite3_reset(statement);                return NO;            }

}

sqlite3_close(database);    }    return NO;}

 

In the above code snippet, a ‘sqlite3_prepare_v2’ is called with insertion query then an image data of employee is bind to a parameter using ‘sqlite3_bind_blob’ IOS SQLite API method. This method takes a pointer to a statement, index of the parameter in SQL query (leftmost is 1), a value to bind to a parameter and SQLite destructor type. To execute the statement with a ‘sqlite3_step’ method is called. Finally, ‘sqlite3_reset’ API method with prepared-statement as an argument is called to reset a statement back to its initial state. Same example can be used for the update and delete employee record by changing the only query string. To activate an option of ‘ON DELETE CASCADE’ before the deletion requires a call to ‘sqlite3_exec’ method. Following code snippet is used to enable that functionality.

Code snippet:

sqlite3_exec (database, "PRAGMA foreign_keys = ON;", 0, 0, 0);

sqlite3_step (statement);

sqlite3_reset (statement);

IOS SQLite Select

Selecting data from SQLite table also require an opened database and prepared statement with a select query. Then ‘sqlite3_step’ method is called to execute that statement. ‘SQLITE_ROW’ constant is returned if  row is returned successfully from table. The ‘sqlite3_column_count’ method returns the number of columns in current row. Finally a value is retrieve from ‘sqlite_column_x’ method where ‘x’ represents a type of column data returned, which is identified by using ‘sqlite3_column_type’ method. Following code snippet is used to retrieve all the data from EMPLOYEE table.

Code snippet (add method in ‘DataBaseManager’):

- (NSArray*) selectAllEmployee{

const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &database) == SQLITE_OK)

{

NSString *querySQL = [NSString stringWithFormat:@"select * from EMPLOYEE"];

const char *query_stmt = [querySQL UTF8String];

NSMutableArray *resultArray = [[NSMutableArray alloc]init];

if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) != SQLITE_OK){}

else

{

if(sqlite3_step(statement) == SQLITE_ROW)

{

do

{

NSMutableArray *row = [NSMutableArray array];

for (int i=0; i<sqlite3_column_count(statement); i++)

{

//Check the column type of retured data.

int colType = sqlite3_column_type(statement, i);

id  value;

if (colType == SQLITE_TEXT) {

const unsigned char *col = sqlite3_column_text(statement, i);

value = [NSString stringWithFormat:@"%s", col];

} else if (colType == SQLITE_INTEGER) {

int col = sqlite3_column_int(statement, i);

value = [NSNumber numberWithInt:col];

} else if (colType == SQLITE_FLOAT) {

double col = sqlite3_column_double(statement, i);

value = [NSNumber numberWithDouble:col];

} else if (colType == SQLITE_BLOB) {

value=[NSData dataWithBytes:sqlite3_column_blob(statement, i) length:length];

}

else if (colType == SQLITE_NULL) {

value = [NSNull null];

}

else {

NSLog(@"[SQLITE] UNKNOWN DATATYPE");

}

[row addObject:value];

}

[resultArray addObject:row];

}   while (sqlite3_step(statement) == SQLITE_ROW);

sqlite3_reset(statement);

sqlite3_close(database);

return resultArray;

}

else{

sqlite3_close(database);

return nil;

}

}

sqlite3_close(database);

return nil;

}

return nil;

}

In the above method of ‘DataBaseManager’ class, when a ‘sqlite3_step’ returns a ‘SQLITE_ROW’ constan, a specific method is called to retrieve the content of a column based on the type of column (‘SQLITE_TEXT, ‘SQLITE_BLOB’, ‘SQLITE_NULL’, etc.) returned by ‘sqlite3_column_type’ method. Finally, this retrieved data is saved in ‘NSMutableArray’ and is returned to a caller class.

 

IOS SQLite Xcode Project:

Employee’s controller.h:

 

Example1

 

Controller.m (insert button example):

example2

Resultant main view:

example3

Output:

example4

To learn more about the essentials of iOS, check out a course at Udemy.com.