Advertise here

Advertise here

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

[REQ] SQL tutorial

ryantcbryantcb Posts: 667Registered Users @ @ @
edited June 2012 in Tutorial Requests
I am looking to learn how to integrate iOS with MySQL and or SQLite. I find many codes online but none that explain the syntax. Anyone know of a good book? I know how to CRUD a database but not via the iPhone any guides please that take SQL and run it through ios?


  • ryantcbryantcb Posts: 667Registered Users @ @ @
    Old post but not answered so thought I would come back in with the answer.

    First include the library libsqlite3.dylib and import the header into the .h and add a reference to a sqlite3 database.

    #import <sqlite3.h>
    @interface ViewController : UIViewController
    //this is how we will reference our database.
    sqlite3 *ourDataBase;
    Now in the implementation file get a reference to or create the databases location
    //create the path to the database
    -(NSString*)filePath {
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        return [[paths objectAtIndex:0]stringByAppendingPathComponent:@"ourDatabaseFile.db"];
    //open the database converting the filepath to a C string with UTF8String method
    //close database if not SQLITE_OK
        if (sqlite3_open([[self filePath]UTF8String], &ourDatabase)!=SQLITE_OK) {
            NSLog(@"Database opened");
    //create the table to use inside database we will make a very simple database to store people and their favourite food
       //create a char error pointer 
        char *err;
        //create the 'create table' SQL statement
        NSString *SQL_createTable=[NSString stringWithFormat:@" CREATE TABLE IF NOT EXISTS '%@'('%@' TEXT PRIMARY KEY NOT NULL,'%@' FLOAT)",tableName,SectionOne,SectionOne];
        if (sqlite3_exec(ourDataBase, [SQL_createTable UTF8String], NULL, NULL, &err)!=SQLITE_OK) {
    //if table not created show the error and close database
            NSLog(@"created table or used existing");
    //Now we have two methods one opens the database and the other creates the table so we best call them otherwise nothing will happen. we will use ViewDidLoad
    -(void)viewDidLoad {
        [self openDatabase];
        [self createTableNamed:@"Our Table" SectionOne:@"Name" SectionTwo:@"Favourite Food"];
    //We need to put stuff into our table now. Not going to cover the UI in this so lets just assume we have 2 text fields and a button
    //NameTextfield, FavFoodTextfield and addToTableButton which actions the call to addToTable
    //take the contents of the text fields and save into NSStrings
      NSString *name = [NameTextfield text];
      NSString *favFood = [FavFoodTextfield text];
    //create the insert data SQL statement 
    NSString *insertIntoTable = [NSString stringWithFormat:@"INSERT INTO 'Our Table' VALUES ('%@',%@)",name,favFood];
     char *err;
    //this will return an int that can be tested for using a switch statement we will just test that it worked with a result of 0, use others if you need to such a 19 which means name is not unique and you need to use INSERT OR REPLACE
        int SQL_RESULT = sqlite3_exec(ourDataBase, [insertIntoTable UTF8String], NULL, NULL, &err);
    switch (SQL_RESULT) {
            case 0:{
                NSLog(@"added OK");
    //Now we need to retrieve data from our database lets assume we have another button retrieveFromDatabase which calls function retrieveData
     if (sqlite3_open([[self filePath]UTF8String], &ourDataBase)==SQLITE_OK)  {
        NSString *SQL_select =[NSString stringWithFormat:@"SELECT 'Favourite Food'  FROM 'Our Table' WHERE name = '%@'",[NameTextfield text]];
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(ourDataBase, [SQL_select UTF8String], -1, &statement, nil)==SQLITE_OK) {
            while (sqlite3_step(statement)==SQLITE_ROW) {
    //            //get the fav food and create NSString from char pointer
                char *favFood =(char*) sqlite3_column_text(statement, 0);
                NSString *favFoodString=[NSString stringWithUTF8String:favFood];
          //put that result into the textfield.
Sign In or Register to comment.