Thursday, 4 October 2012

Database implementation using SQLite in iOS Applications

I have worked on Core Data and SQLite both, but i personally feel that using SQLite is a good choice.
Working on Database directly using SQLite is better than working on Core Data which actually works as a wrapper above SQLite.
We can easily fire complex sql queries on Database using SQLite to store, update or fetch data; which is a pathetic job using Core Data. 

Below is a simple demo app which will help you understand how to use SQLite to create database, store, update, fetch and delete records from database.

For using SQLite database in iPhone, iPad App first you need to link libsqlite3.0.dylib to your build target. 
To do this select 'Project' at top left navigation panel -> 'under Targets' select your build target -> then click on 'Build Phases' tab -> then in 'Link Binary With Libraries' click on '+' button and add libsqlite3.0.dylib.

 There are number of C functions contained within the libsqlite3.dylib library which can be used for sqlite implementation.

1. sqlite3_open() - This function is used to opens database. In case database does not exist, it creates one.
2. sqlite3_prepare_v2() – This function is used to prepares a SQL statement for execution.
3. sqlite3_step() – This function is used to executes a SQL statement previously prepared by the sqlite3_prepare_v2() function.
4. sqlite3_column_<type>() – This function is used to returns a data field from the results of a SQL retrieval operation. Where <type> is the type of the data to be extracted (int, text, blob, bytes, etc).
5. sqlite3_finalize() - This function is used delete a previously prepared SQL statement.
6. sqlite3_exec() – This function is used combine the functionality of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call.
7. sqlite3_close() – This function is used to close a database file previously opened.

Below is the code snippet for using sqlite implementation:

In header file:

In implementation file:

In xib file:

Here is the sample code: SQLITE_CODE


  1. Create your SQL statements using [NSString stringWithFormat] makes the app vulnerable to SQL Injection attacks. Use bind variables instead.

  2. Nice blogs.
    Videocon and stay in touch with your Contacts, Friends, and Loved ones.
    Seamless Roaming across India. National Roaming is Pre-Activated on Videocon Prepaid Connection with Zero Monthly Rentals.
    Tariff while Roaming anywhere in India.