iOS Sqlite3 tutorial

iPhone iOS embedded sqlite3 , first thing you should do to use sqlite3 API is to add it to your project , so in your project select the main root of project from project navigator and go to “Build Phases” then expand “Link Binary With Libraries” then click ( + ) button to add new framework and libraries and choose “libesqlite3.dylibe” ; its not yet , now select class “.h or .m” that you will write sqlite3 code on it and ( #import <sqlite3.h> ) , now you can begin your code
This sample “Example with source code” covered :

• create sqlite3 database
• create sqlite3 table if not exists
• open sqlite3 database
• add new row to sqlite3 database
• select and edit sqlite3 db row
• delete row from sqlite3 database

note that all sqlite3 commands that returns data we use it by function ” sqlite3_prepare_v2 “  and the returned data stores in “sqlite3_stmt”, and commands that doesn’t returns data we use it by function ” sqlite3_exec ” .

Create and open sqlite3 database :
To open or create sqlite3 data base , use “sqlite3_open” -> open exists database or create it if not exists.

sqlite3* myDB ;

    //Get Temporary Directory

    NSString* dbPath = NSTemporaryDirectory();

    //Append DataBase name to the path

    dbPath = [dbPath stringByAppendingPathComponent:@"myDB.sql3"];

    int result = sqlite3_open([dbPath UTF8String], &myDB);

    if (SQLITE_OK == result) {

        NSLog(@"myDB opend");

    }else {

        NSLog(@"myDB opening error");

        return;

    }

Object ” sqlite3* myDB” will be the database in our project !!
Sqlite3 database path “temporary\myDB.sql3″ or you can change it as you want ; note that “sqlite3_open” function is old C language and doesn’t understand objective-c string like “NSString dbPath” so you can convert NSString to char* -C string- using [NSString UTFString].
Function “sqlite3_open” returns integer as result , if result = SQLITE_OK means no errors else means something wrong !!
Create sqlite3 table if not exists :
To execute SQL Query use “sqlite3_exec” , so to create new table you only want to know the sql command , you can use any program to create new database and then use it in Xcode , or you can copy sql commands and past it in char* -C String- and use it.

 //write table query in C String ...

    const char* namesTableQuery = "CREATE TABLE IF NOT EXISTS namesTable ( id INTEGER PRIMARY KEY, firstName varchar(35), lastName varchar(35), phoneNum varchar(13),info TEXT)";

    char * errInfo ;

    result = sqlite3_exec(myDB, namesTableQuery, nil, nil, &errInfo);

    if (SQLITE_OK == result) {

        NSLog(@"names Table Created :)");

    }else {

        //NSString* err = [[NSString alloc]initWithUTF8String:errInfo];

        NSLog(@"error in creating table :(");

        return;

    }

As you see , “sqlite3_exec” function to execute create table command , note that “char* errInfo” to store error details if the result doesn’t equals SQLITE_OK .
Add or INSERT row to sqlite3 database table :
Simple you will use “sqlite3_exec” to execute INSERT sql command .

NSString* insertQuery = @"INSERT INTO namesTable (firstName, lastName, phoneNum, info) VALUES

                                                     ('tom', 'cat', '123456789', 'white and nice cat...')"

    char* errInfo;

    int result = sqlite3_exec(myDB, [insertQuery UTF8String], nil, nil, &errInfo);

    if (SQLITE_OK == result) {

        NSLog(@"Row Added :)");

    }else {

        //NSString* err = [[NSString alloc]initWithUTF8String:errInfo];

        NSLog(@"error in adding :(");

        return;

    }

SELECT row from sqlite3 database table
As you know sql SELECT command returns some rows as result , so you will use “sqlite3_prepare_v2″ to execute sql command and “sqlite3_stmt” to handle the result —> don’t forget to finalize sqlite3_stmt using “sqlite3_finalize” !!!

//sql SELECT command

    NSString* selectQuery = @"SELECT id, firstName, lastName, phoneNum, info FROM namesTable ORDER BY id";

    sqlite3_stmt* sqlStatement;

    int result = sqlite3_prepare_v2(myDB, [selectQuery UTF8String], -1, &sqlStatement, nil);

    if (SQLITE_OK == result) {

        while (sqlite3_step(sqlStatement)==SQLITE_ROW) {

            int nameid = (int)sqlite3_column_int(sqlStatement, 0);

            char* fName = (char*)sqlite3_column_text(sqlStatement, 1);

            char* lName = (char*)sqlite3_column_text(sqlStatement, 2);

            char* phome = (char*)sqlite3_column_text(sqlStatement, 3);

            char* info = (char*)sqlite3_column_text(sqlStatement, 4);

            NSLog(@"id= %i\tFirstName= %@\tSecondName= %@\tPhoneNum= %@\tInfo= %@",nameid,fName,lName,phome,info);

        }

        sqlite3_finalize(sqlStatement);

        NSLog(@"Select OK :)");

    }else {

        NSLog(@"Error in Select :(");

    }

DELETE row from sqlite3 database table :

All you want to know the sql DELETE command and execute it using “sqlite3_exec”

//delete

NSString* delete = @"DELETE FROM namesTable WHERE id = 1";

    char* error;

    int result = sqlite3_exec(myDB, [delete UTF8String], nil, nil, &error);

    if (SQLITE_OK == result) {

        [notesLabel setText:@"Row Deleted ..."];

    }else {

        [notesLabel setText:@"error in deleting!!"];

    }