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?

Replies

  • 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.

    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.