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: 664Registered 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: 664Registered 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.

    <br />#import <sqlite3.h><br />@interface ViewController : UIViewController<br />{<br />//this is how we will reference our database.<br />sqlite3 *ourDataBase;<br />}
    Now in the implementation file get a reference to or create the databases location
    //create the path to the database<br />-(NSString*)filePath {<br />    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);<br />    return [[paths objectAtIndex:0]stringByAppendingPathComponent:@"ourDatabaseFile.db"];<br />}<br /><br />//open the database converting the filepath to a C string with UTF8String method<br />//close database if not SQLITE_OK<br />-(void)openDatabase{<br /><br />    if (sqlite3_open([[self filePath]UTF8String], &ourDatabase)!=SQLITE_OK) {<br />        sqlite3_close(ourDataBase);<br />    }else{<br />        NSLog(@"Database opened");<br />    }<br />}<br /><br /><br />//create the table to use inside database we will make a very simple database to store people and their favourite food<br />-(void)createTableNamed:(NSString*)tableName<br />             sectionOne:(NSString*)SectionOne<br />              SectionTwo:(NSString*)SectionOne<br />             <br />{<br />   //create a char error pointer <br />    char *err;<br /><br />    //create the 'create table' SQL statement<br />    NSString *SQL_createTable=[NSString stringWithFormat:@" CREATE TABLE IF NOT EXISTS '%@'('%@' TEXT PRIMARY KEY NOT NULL,'%@' FLOAT)",tableName,SectionOne,SectionOne];<br />    <br /><br />    if (sqlite3_exec(ourDataBase, [SQL_createTable UTF8String], NULL, NULL, &err)!=SQLITE_OK) {<br />//if table not created show the error and close database<br />        NSLog(@"%s",err);<br />        sqlite3_close(ourDataBase);<br />    }else{<br />        NSLog(@"created table or used existing");<br />    }<br />       <br />}<br /><br />//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<br /><br />-(void)viewDidLoad {<br />    [self openDatabase];<br />    [self createTableNamed:@"Our Table" SectionOne:@"Name" SectionTwo:@"Favourite Food"];<br />}<br /><br />//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<br />//NameTextfield, FavFoodTextfield and addToTableButton which actions the call to addToTable<br /><br />-(void)addToTable{<br /><br />//take the contents of the text fields and save into NSStrings<br />  NSString *name = [NameTextfield text];<br />  NSString *favFood = [FavFoodTextfield text];<br /> <br />//create the insert data SQL statement <br /><br />NSString *insertIntoTable = [NSString stringWithFormat:@"INSERT INTO 'Our Table' VALUES ('%@',%@)",name,favFood];<br /><br /> char *err;<br /><br />//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<br />    int SQL_RESULT = sqlite3_exec(ourDataBase, [insertIntoTable UTF8String], NULL, NULL, &err);<br /><br />switch (SQL_RESULT) {<br />        case 0:{<br />            NSLog(@"added OK");<br />           <br />        }<br />            break;<br /><br /><br />}<br /><br />//Now we need to retrieve data from our database lets assume we have another button retrieveFromDatabase which calls function retrieveData<br />-(void)retrieveData{<br /> if (sqlite3_open([[self filePath]UTF8String], &ourDataBase)==SQLITE_OK)  {<br />      <br />    <br />    NSString *SQL_select =[NSString stringWithFormat:@"SELECT 'Favourite Food'  FROM 'Our Table' WHERE name = '%@'",[NameTextfield text]];<br /><br />    sqlite3_stmt *statement;<br />    <br />    if (sqlite3_prepare_v2(ourDataBase, [SQL_select UTF8String], -1, &statement, nil)==SQLITE_OK) {<br />        while (sqlite3_step(statement)==SQLITE_ROW) {<br />//            //get the fav food and create NSString from char pointer<br />            char *favFood =(char*) sqlite3_column_text(statement, 0);<br />            NSString *favFoodString=[NSString stringWithUTF8String:favFood];<br />            <br />      //put that result into the textfield.<br />     <br />        }<br />    }<br />    else{<br />        sqlite3_close(ourDataBase); <br />       <br />    }<br />    sqlite3_finalize(statement);<br /><br />}<br />}<br />
Sign In or Register to comment.