(iOS) Basic SQLite

Read me

USE THIS:
https://github.com/ccgus/fmdb

A Cocoa / Objective-C wrapper around SQLite

---------------------------------------------
OR:

1. Add libsqlite3.dylib to Frameworks

2. Commonly used functions: (Full List: http://www.sqlite.org/c3ref/funclist.html)

sqlite3_open() - Opens specified database file. If the database file does not already exist, it is created.

sqlite3_close() – Closes a previously opened database file.

sqlite3_prepare_v2() – Prepares a SQL statement ready for execution.

sqlite3_step() – Executes a SQL statement previously prepared by the sqlite3_prepare_v2() function.

sqlite3_column_<type>() – Returns a data field from the results of a SQL retrieval operation where <type> is replaced by the data type of the data to be extracted (text, blob, bytes, int, int16 etc).

sqlite3_finalize() - Deletes a previously prepared SQL statement from memory.

sqlite3_exec() – Combines the functionality of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call.

sqlite.mm

#import "/usr/include/sqlite3.h"

//Declare a pointer to sqlite database structure
sqlite3 *contactDB;

//Databse path has tobe iin UTF-8 format, so  convert NSString to UTF-8
NSString *databasePath = [self dataFilePath];
const char *dbpath = [databasePath UTF8String];

//Open or create the sqlite database file using the functions:
//int sqlite3_open(const char *filename, sqlite3 **database);
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
  //Database opened successfully
} 
else 
{
	//Failed to open database
}


//Create a database table
const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";

if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) == SQLITE_OK)
{
       // SQL statement execution succeeded
}


//SQL statements are prepared and stored in a structure of type sqlite3_stmt using the sqlite3_prepare_v2() function.
sqlite3_stmt *statement;

//Executing SQL statements
NSString *querySQL = @"SELECT address, phone FROM contacts”;

const char *query_stmt = [querySQL UTF8String];

if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
  //Statement prepared successfully
} else {
	//Statement preparation failed
}


//Ectracting data from a database table
sqlite3_stmt *statement;

NSString *querySQL = @"SELECT address, phone FROM contacts”;

const char *query_stmt = [querySQL UTF8String];

sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL);

//The statement subsequently needs to be executed, step by step (row by row)
//If a row matching the selection criteria is found, the sqlite3_step() function returns SQLITE_ROW
while (sqlite3_step(statement) == SQLITE_ROW)
{
       //Get values
       //Using sqlite3_column_<type>() - Where <type> is replaced by the type of data being extracted
       NSString *addressField = [[NSString alloc] initWithUTF8String:
                (const char *) sqlite3_column_text(statement, 0)];

       NSString *phoneField = [[NSString alloc] initWithUTF8String:
                (const char *) sqlite3_column_text(statement, 1)];

  // Code to do something with extracted data here


//Close the database
sqlite3_close(contactDB);
  [addressField release];
	[phoneField release];
}
sqlite3_finalize(statement);

-----------------------------------------

//Create a filetothe Documents folder
-(NSString *)dataFilePath
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    return [documentsDirectory stringByAppendingPathComponent:@"file.anyextention"];
}

sqlite3_column types

sqlite3_column_blob()
sqlite3_column_bytes()
sqlite3_column_bytes16()
sqlite3_column_count()
sqlite3_column_double()
sqlite3_column_int()
sqlite3_column_int64()
sqlite3_column_text()
sqlite3_column_text16()
sqlite3_column_type()
sqlite3_column_value()