No comments

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>  to the relevant header files.
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

The following code snippet creates a database file named “AddressBook.sqlite” within the Documents folder of the application. If the database exists, it opens a connection to the database.

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)
Email	VARCHAR (null)
Telephone	VARCHAR (null)

 

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

Add a row entry to the table

The code snippet adds a new Contact entry to the Contacts table

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.

 

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”

Delete a row entry from the table

The code snippet deletes an entry in the Contacts table with FirstName = “Foo” .

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.

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.