Posts Tagged ‘SQLite Database’
Intro to SQLite for iOS developers
The SQLite engine is a lightweight library optimized for managing relational databases in embedded environments. It is supported on the iOS platform. Described below is a set of basic SQLite queries that will come in handy if you need /choose to use the C-style SQLite APIs to directly manipulate your database tables. It is intended for folks who are noobs to the database world. Direct manipulation using SQLite APIs is an alternative to using the high-level Object Oriented Core Data wrapper framework.
Note: The sample code snippets are intended for reference purposes. You should include appropriate error handling and any relevant synchronization support depending on the needs of your application. If you are interested in the details of the routines, please refer to the sqlite3.h header file.
Adding SQLite Support
SQLite libraries is available as part of the iOS SDK. Include the libsqlite3.0.dylib to your project using the usual procedure, and add
#import <sqlite3.h>
Note: The following code snippets assume an ivar named “dbHandle” corresponding to the database Handle defined in your header file as follows-
sqlite3* dbHandle;
Opening the connection to the database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
NSString* const dbFileName = @"AddressBook.sqlite"; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString* dbFile = [documentsDirectory stringByAppendingPathComponent:dbFileName]; sqlite3_config(SQLITE_CONFIG_SERIALIZED); int result = sqlite3_open([dbFile UTF8String], &dbHandle); if (result != SQLITE_OK) { NSLog(@"Failure in connecting to the database with result %d",result); } else { NSLog(@ "Succesfully opened connection to DB") ; } |
Creating a table
The following code snippet creates a table named “Contacts” in the “AddressBook.sqlite” database. Every row in the table corresponds to a “Contact” and has the following columns-
|
FirstName |
VARCHAR (non-null) |
|
LastName |
VARCHAR (null) |
|
|
VARCHAR (null) |
|
Telephone |
VARCHAR (null) |
|
1 2 3 4 5 6 7 8 9 |
char* error; const char *sqlQuery = "CREATE TABLE Contacts (FirstName TEXT NOT NULL, LastName TEXT, Email TEXT, Telephone TEXT,PRIMARY KEY (FirstName))"; int result = sqlite3_exec(dbHandle, sqlQuery, NULL, NULL, &error); if (result != SQLITE_OK) { NSLog(@ "Failure in creating the table with result %d",result) ; } else { NSLog(@ "Succesfully created table ") ; } |
Common queries on a table
Query execution follows the following basic sequence –
- “Prepare” the SQL query for executing using the sqlite3_prepare_v2() call. A SQL query can be comprised of multiple SQL statements, That said, the “preparation” routines only compile the first SQL statement in the query so I typically specify only one statement within a query.
- Optionally,“Bind” SQL query arguments with values using the sqlite3_bind_*() commands. Binding would be required only if there are arguments specified in the SQL query statement.
- “Evaluate” the compiled SQL query (that was output from the sqlite3_prepare_v2() command ) using the sqlite3_step() command.
- Optionally, “Retrieve” the results of the SQL query evaluation using the sqlite3_column_*() commands.You can access the value of every column of the resulting row using these routines. The leftmost column begins at index 0.
- “Reset” the compiled SQL query so it can be evaluated again. You can execute the query again using the sqlite3_bind() and sqlite3_step() commands
- “Destroy” the query object once you are done with the query using the sqlite3_finalize() command
Fetch total number of entries in the table
The code snippet returns the number of rows in the Contacts table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
int count = 0; int retVal; const char *sqlQuery = "SELECT COUNT(*) FROM Contacts"; sqlite3_stmt *query = nil; if ((retVal =sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { int queryResult = sqlite3_step(query); if (queryResult == SQLITE_ROW) { count = sqlite3_column_int(query, 0); NSLog(@"Succesfully selected row count %d ",count) ; } else { NSLog(@"Failed to selected row count ") ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@"Failure in preparing SELECT statement with result %d",retVal) ; } |
Add a row entry to the table
The code snippet adds a new Contact entry to the Contacts table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
NSString* contactFirstName = @"Foo"; NSString* contactLastName = @"Bar"; NSString* contactEmail = @"foo@bar.com"; NSString* contactTel = @"555-1234"; const char *sqlQuery = "INSERT into Contacts (FirstName, LastName, Email, Telephone) Values(?, ?, ?, ?)"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { // The contactFirstName is a primary key. So cannot be NULL. Do the relevant checks for null values before you get here sqlite3_bind_text(query, 1, [contactFirstName UTF8String], -1, SQLITE_TRANSIENT); if (contactLastName) { sqlite3_bind_text(query, 2, [contactLastName UTF8String], -1, SQLITE_TRANSIENT); } else { sqlite3_bind_null(query, 2); } if (contactEmail != nil) { sqlite3_bind_text(query, 3, [contactEmail UTF8String], -1, SQLITE_TRANSIENT); } else { sqlite3_bind_null(query, 3); } if (contactTel != nil) { sqlite3_bind_text(query, 4, [contactTel UTF8String], -1, SQLITE_TRANSIENT); } else { sqlite3_bind_null(query, 4); } int result = sqlite3_step(query); if (result != SQLITE_DONE) { NSLog(@ "Failure in adding row to table. Result is %d",result) ; } else { NSLog(@ "Succesfully added row ") ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Error in preparing INSERT statement %d",retVal); } |
Select a row entry from the table
The code snippet selects an entry in the Contacts table with FirstName = “Foo” . It also retrieves the rowId corresponding to the entry.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
NSString* searchName = @"Foo"; NSInteger rowId; NSString* lastName; NSString* email; NSString* tel; const char *sqlQuery = "SELECT rowid, FirstName, LastName, Email, Telephone FROM Contacts WHERE FirstName == ?"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { sqlite3_bind_text(query, 1, [searchName UTF8String], -1, SQLITE_TRANSIENT ); int result; if( (result = sqlite3_step(query)) == SQLITE_ROW) { rowId = sqlite3_column_int(query, 0); lastName = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(query, 2)]; email = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(query, 3)]; tel = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(query, 4)]; NSLog(@"Retrieved the row with Id %d for FirstName %@ :LastName :%@, Email:%@, Telephone:%@", rowId,searchName,lastName,email,tel); } else { NSLog(@ "Failure in select row from table. Result is %d",result) ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Failure in preparing SELECT statement with result %d",retVal) } |
Update a row entry in the table
The code snippet updates the Telephone number associated with an entry in the Contacts table with FirstName = “Foo” to “555-7777”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
NSString* searchStr = @"Foo"; NSString* updateStr = @"555-7777"; const char *sqlQuery = "UPDATE Contacts SET Telephone = ? WHERE FirstName == ?"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { sqlite3_bind_text(query, 1, [updateStr UTF8String], -1, SQLITE_TRANSIENT ); sqlite3_bind_text(query, 2, [searchStr UTF8String], -1, SQLITE_TRANSIENT ); int result; if ((result =sqlite3_step(query)) != SQLITE_DONE) { NSLog(@"Failed to execute the query %d",result); } else { NSLog(@"Succesfully updated row !"); } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Error in preparing UPDATE statement %d",retVal); } |
Delete a row entry from the table
The code snippet deletes an entry in the Contacts table with FirstName = “Foo” .
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
NSString* searchStr = @"Foo"; const char *sqlQuery = "DELETE FROM Contacts WHERE FirstName == ?"; sqlite3_stmt *query = nil; int retVal; if ((retVal = sqlite3_prepare_v2(dbHandle, sqlQuery, -1, &query, NULL)) == SQLITE_OK) { sqlite3_bind_text(query, 1, [searchStr UTF8String], -1, SQLITE_TRANSIENT ); int result; if ((result = sqlite3_step(query)) != SQLITE_DONE) { NSLog(@ "Failure in delete row from table. Result is %d",result) ; } else { NSLog(@ "Succesfully deleted row ") ; } sqlite3_reset(query); sqlite3_finalize(query); } else { NSLog(@ "Failure in preparing DELETE statement with result %d",retVal) ; } |
Closing connection to the database
The following code snippet closes the connection to the database. All resources must have been “finalized” before you invoke this routine.
|
1 2 3 4 5 6 7 |
int result = sqlite3_close(dbHandle); if (result != SQLITE_OK){ NSLog(@"Failure in closing connection to database. Result %d",result); } else { NSLog(@ "Successfully closed DB connection ") ; } |
Testing
You can use the freely available plugin “SQLite Manager” (https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/) for Mozilla Firefox to examine/verify the contents of your sqlite database.