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: 663Registered 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?

Replies

  • ryantcbryantcb Posts: 663Registered 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.

    sqliteDemo.h

    #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
    sqliteDemo.m
    //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
    -(void)openDatabase{

    if (sqlite3_open([[self filePath]UTF8String], &ourDatabase)!=SQLITE_OK) {
    sqlite3_close(ourDataBase);
    }else{
    NSLog(@"Database opened");
    }
    }


    //create the table to use inside database we will make a very simple database to store people and their favourite food
    -(void)createTableNamed:(NSString*)tableName
    sectionOne:(NSString*)SectionOne
    SectionTwo:(NSString*)SectionOne

    {
    //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(@"%s",err);
    sqlite3_close(ourDataBase);
    }else{
    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

    -(void)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");

    }
    break;


    }

    //Now we need to retrieve data from our database lets assume we have another button retrieveFromDatabase which calls function retrieveData
    -(void)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.

    }
    }
    else{
    sqlite3_close(ourDataBase);

    }
    sqlite3_finalize(statement);

    }
    }
Sign In or Register to comment.