[REQ] SQL tutorial

Replies

• ryantcb Posts: 328Registered Users

  January 19

  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);
  
  }
  }
  

  Flag

  • Spam
  • Abuse
  • Troll

  0 • Off Topic Insightful Disagree Dislike Like Awesome