In iOS SQLite Tutorial, I have covered how to use SQLite Database in iOS / Objective-C. You can read more about SQLite here. http://en.wikipedia.org/wiki/SQLite
Below are the topics covered in this article.
1). iOS SQLite API
2). SQLite Error Codes
3). Create SQLite Database Table
4). SQLite Select, Insert, Delete Examples
In order to use SQLite Database in iOS / Objective-C, you need to add sqlite3.h header to your source and add libsqlite3.dylib to libraries.
1) iOS SQLite API
Below is the list of most useful SQLite API.
a) sqlite3_open_v2 is to open a new database connection.
int sqlite3_open_v2( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb, /* OUT: SQLite db handle */ int flags, /* Flags */ const char *zVfs /* Name of VFS module to use */ );
b) sqlite3_close() is to close the database connections.
int sqlite3_close(sqlite3 *);
c) sqlite3_prepare_v2 is used to compile a SQL query.
int sqlite3_prepare_v2( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nByte, /* Maximum length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ );
d) sqlite3_step() is used to evaluate the statement prepared by sqlite3_prepare_v2. If return data is SQLITE_ROW, then each time a new row of data is ready for processing.The values may be accessed using the column access functions. sqlite3_step() is called again to retrieve the next row of data.
int sqlite3_step(sqlite3_stmt*);
Below functions are used to extract column values from a row.
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
e) sqlite3_finalize() is used to destroy a prepared statement.
int sqlite3_finalize(sqlite3_stmt *pStmt);
f) sqlite3_reset() is used to reset prepared statement object to initial state.
int sqlite3_reset(sqlite3_stmt *pStmt);
g) sqlite3_exec() is wrapper function of sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize() that allows an application to run multiple statements of SQL without having to use a lot of code.
Function usage for INSERT/UPDATE/DELETE operations
sqlite3_open_v2() sqlite3_prepare_v2(); sqlite3_step() sqlite3_finalize(); sqlite3_close()
OR Simply we can use this way
sqlite3_open_v2(); sqlite3_exec(); sqlite3_close();
Function usage for SELECT operation
sqlite3_open_v2();
sqlite3_prepare_v2();
while(sqlite3_step(stmt) == SQLITE_ROW)
{
}
sqlite3_finalize();
sqlite3_close();
2. SQLite Error Codes
Below is the list of SQLite Error codes.
#define SQLITE_OK 0 /* Successful result */ /* beginning-of-error-codes */ #define SQLITE_ERROR 1 /* SQL error or missing database */ #define SQLITE_INTERNAL 2 /* Internal logic error in SQLite */ #define SQLITE_PERM 3 /* Access permission denied */ #define SQLITE_ABORT 4 /* Callback routine requested an abort */ #define SQLITE_BUSY 5 /* The database file is locked */ #define SQLITE_LOCKED 6 /* A table in the database is locked */ #define SQLITE_NOMEM 7 /* A malloc() failed */ #define SQLITE_READONLY 8 /* Attempt to write a readonly database */ #define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite3_interrupt()*/ #define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ #define SQLITE_CORRUPT 11 /* The database disk image is malformed */ #define SQLITE_NOTFOUND 12 /* Unknown opcode in sqlite3_file_control() */ #define SQLITE_FULL 13 /* Insertion failed because database is full */ #define SQLITE_CANTOPEN 14 /* Unable to open the database file */ #define SQLITE_PROTOCOL 15 /* Database lock protocol error */ #define SQLITE_EMPTY 16 /* Database is empty */ #define SQLITE_SCHEMA 17 /* The database schema changed */ #define SQLITE_TOOBIG 18 /* String or BLOB exceeds size limit */ #define SQLITE_CONSTRAINT 19 /* Abort due to constraint violation */ #define SQLITE_MISMATCH 20 /* Data type mismatch */ #define SQLITE_MISUSE 21 /* Library used incorrectly */ #define SQLITE_NOLFS 22 /* Uses OS features not supported on host */ #define SQLITE_AUTH 23 /* Authorization denied */ #define SQLITE_FORMAT 24 /* Auxiliary database format error */ #define SQLITE_RANGE 25 /* 2nd parameter to sqlite3_bind out of range */ #define SQLITE_NOTADB 26 /* File opened that is not a database file */ #define SQLITE_ROW 100 /* sqlite3_step() has another row ready */ #define SQLITE_DONE 101 /* sqlite3_step() has finished executing */
3) Create SQLite Database Table
Below is the simple table schema which is used in all the provided code samples.
CREATE TABLE IF NOT EXISTS Students ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, AGE INTEGER, MARKS INTEGER )
You can use the below code to create SQLite table. SQLITE_OPEN_CREATE flag is used to create database file if it does not exists.
-(int) createTable:(NSString*) filePath
{
sqlite3* db = NULL;
int rc=0;
rc = sqlite3_open_v2([filePath cStringUsingEncoding:NSUTF8StringEncoding], &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (SQLITE_OK != rc)
{
sqlite3_close(db);
NSLog(@"Failed to open db connection");
}
else
{
char * query ="CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, marks INTEGER )";
char * errMsg;
rc = sqlite3_exec(db, query,NULL,NULL,&errMsg);
if(SQLITE_OK != rc)
{
NSLog(@"Failed to create table rc:%d, msg=%s",rc,errMsg);
}
sqlite3_close(db);
}
return rc;
}
Use the below code to get Database file path in Documents directory
-(NSString *) getDbFilePath
{
NSString * docsPath= NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES)[0];
return [docsPath stringByAppendingPathComponent:@"studets.db"];
}
4) SQLite Insert,Delete,Select Examples
4.1)SQLite INSERT Example
You can use the below code to insert a record in SQLite table.
-(int) insert:(NSString *)filePath withName:(NSString *)name age:(NSInteger)age marks:(NSInteger)marks
{
sqlite3* db = NULL;
int rc=0;
rc = sqlite3_open_v2([filePath cStringUsingEncoding:NSUTF8StringEncoding], &db, SQLITE_OPEN_READWRITE , NULL);
if (SQLITE_OK != rc)
{
sqlite3_close(db);
NSLog(@"Failed to open db connection");
}
else
{
NSString * query = [NSString
stringWithFormat:@"INSERT INTO students (name,age,marks) VALUES (\"%@\",%ld,%ld)",name,(long)age,(long)marks];
char * errMsg;
rc = sqlite3_exec(db, [query UTF8String] ,NULL,NULL,&errMsg);
if(SQLITE_OK != rc)
{
NSLog(@"Failed to insert record rc:%d, msg=%s",rc,errMsg);
}
sqlite3_close(db);
}
return rc;
}
4.2) SQLite DELETE Example
You can use the below code for deleting a record from table.
-(int) delete:(NSString*) filePath withName:(NSString*) name
{
sqlite3* db = NULL;
int rc=0;
rc = sqlite3_open_v2([filePath cStringUsingEncoding:NSUTF8StringEncoding], &db, SQLITE_OPEN_READWRITE , NULL);
if (SQLITE_OK != rc)
{
sqlite3_close(db);
NSLog(@"Failed to open db connection");
}
else
{
NSString * query = [NSString
stringWithFormat:@"DELETE FROM students where name=\"%@\"",name];
char * errMsg;
rc = sqlite3_exec(db, [query UTF8String] ,NULL,NULL,&errMsg);
if(SQLITE_OK != rc)
{
NSLog(@"Failed to delete record rc:%d, msg=%s",rc,errMsg);
}
sqlite3_close(db);
}
return rc;
}
4.3) SQLite SELECT Example
-(NSArray *) getRecords:(NSString*) filePath where:(NSString *)whereStmt
{
NSMutableArray * students =[[NSMutableArray alloc] init];
sqlite3* db = NULL;
sqlite3_stmt* stmt =NULL;
int rc=0;
rc = sqlite3_open_v2([filePath UTF8String], &db, SQLITE_OPEN_READONLY , NULL);
if (SQLITE_OK != rc)
{
sqlite3_close(db);
NSLog(@"Failed to open db connection");
}
else
{
NSString * query = @"SELECT * from students";
if(whereStmt)
{
query = [query stringByAppendingFormat:@" WHERE %@",whereStmt];
}
rc =sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, NULL);
if(rc == SQLITE_OK)
{
while (sqlite3_step(stmt) == SQLITE_ROW) //get each row in loop
{
NSString * name =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSInteger age = sqlite3_column_int(stmt, 2);
NSInteger marks = sqlite3_column_int(stmt, 3);
NSDictionary *student =[NSDictionary dictionaryWithObjectsAndKeys:name,@"name",
[NSNumber numberWithInteger:age],@"age",[NSNumber numberWithInteger:marks], @"marks",nil];
[students addObject:student];
NSLog(@"name: %@, age=%ld , marks =%ld",name,(long)age,(long)marks);
}
NSLog(@"Done");
sqlite3_finalize(stmt);
}
else
{
NSLog(@"Failed to prepare statement with rc:%d",rc);
}
sqlite3_close(db);
}
return students;
}
