SQLite Tutorial Part 2

Credits:

Most of the codes are from Techotopia and I refer this tutorial from their SQLite tutorial 
For more information and detailed discussion, refer to their site. :D

This will be a continuation of our first SQLite Tutorial. We will only edit our previous project so I suggest you go over with SQLite Tutorial Part1 first.

• Delete the SQLite Tutorial app in your device / simulator since we are going to change the database.
• Delete the derived data of SQLiteTutorial on your Organizer (upper-right corner of Xcode IDE).
  

We will be covering the following topics:
1. Having multiple columns in our table named "SAMPLETABLE".
2. Getting all data in SAMPLETABLE (with multiple columns)
3. Search and return data from our SAMPLETABLE.

1. Let's edit our ViewController.xib such that we can enter information of a person.
Change the "keyboard type" of the textfield "Year" so that the user can only input numbers.

2. Create IBOutlet properties for these textfields (and be sure to remove the old IBOutlet we created before for it might cause our program to crash especially that it's no longer connected to any textfields in our view). Synthesize these properties on the Implementation Section.
//Interface Section

@property (strong, nonatomic) IBOutlet UITextField *firstNameTextField;

@property (strong, nonatomic) IBOutlet UITextField *lastNameTextField;

@property (strong, nonatomic) IBOutlet UITextField *courseTextField;

@property (strong, nonatomic) IBOutlet UITextField *yearTextField;

//Implementation Section

@synthesize firstNameTextField;

@synthesize lastNameTextField;

@synthesize courseTextField;

@synthesize yearTextField;

3. Connect these outlets to our objects in ViewController.xib.

Remember to set our File's Owner as the delegate of our new textfields (for dismissing the keyboard).

Right-click textField and point the delegate to File's Owner.

4. Remember the prepareDatabase() method we had before in ViewController.m? We'll change the part in our sql_stmt (SQL Statement).

Instead of:

"CREATE TABLE IF NOT EXISTS SAMPLETABLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, MESSAGE TEXT)";

We'll have:

"CREATE TABLE IF NOT EXISTS SAMPLETABLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName TEXT, LastName TEXT, Course TEXT, Year INT)";

5. We'll also edit the insert_stmt of our addTextToDatabase() method in ViewController.m. It should now look like this:

NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO SAMPLETABLE (FirstName, LastName, Course, Year) VALUES (\"%@\", \"%@\", \"%@\", \"%@\")",  self.firstNameTextField.text, self.lastNameTextField.text, self.courseTextField.text, self.yearTextField.text];

Having multiple columns in our table named "SAMPLETABLE" - DONE!

6. In our getTextFromDB method in ListViewController.m, we'll just add a few lines inside our while loop. This loop while (sqlite3_step(statement) == SQLITE_ROW)  will continue to go to the next row until there's no more row to go down with.

while (sqlite3_step(statement) == SQLITE_ROW) {

//NSString *personID = [[NSString alloc] 
                                   initWithUTF8String: (const char *) sqlite3_column_text(statement, 0)];

NSString * firstName = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)]; 

NSString * lastName = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)]; 

NSString * course = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)]; 

NSString * year = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)]; 

                 [list addObject:[NSString stringWithFormat:@"%@ %@ %@-%@", firstName, lastName, course, year]];

            }

Again, the 0, 1, 2, 3, and 4 refers to the column number in our query results. So, IF our query statement is SELECT FirstName, LastName FROM SAMPLETABLE we should expect 2 columns in our query results, right? So to get the first name and last name of the result:

NSString * firstName = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)]; 

NSString * lastName = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)]; 

You may also choose to edit your tableView's cellForRowAtIndexPath method according to your preference.

Getting all data in SAMPLETABLE (with multiple columns)  - DONE!

7. Create a new UIViewController class and import this new class to our AppDelegate. Allocate and initialize the new class and add it in tabbarController's array of viewControllers.

You must now have these lines in your appDelegate's didFinishLaunchingWithOptions method:

SearchViewController *searchVC = [[SearchViewController alloc] initWithNibName:@"SearchViewController" bundle:nil];

 tabController. viewController = [[NSArray  alloc] initWithObjects:self.viewController, listVC, searchVC, nil];

8. Add textfields to our SearchViewController.xib file so the user may choose to search by name, course, or year. Remember also to set the delegates of the textfields to the File's Owner (to dismiss the keyboard).

9. Add <UITextFieldDelegate> to SearchViewController's interface section. And the textFieldShouldReturn method to SearchViewController class.

- (BOOL)textFieldShouldReturn:(UITextField *)textField {

    [textField resignFirstResponder];

    return NO;

}

10. Import sqlite3.h to SearchViewController class. Copy databasePath and myDatabase variables we had from ViewController and ListViewController classes. Create another NSMutableArray for the searchResults.

• #import <sqlite3.h>

• @property (strong, nonatomic) NSString *databasePath;
• @property (nonatomic) sqlite3 *myDatabase;
• @property (strong, nonatomic) NSMutableArray *searchResult;

• @synthesize databasePath;
• @synthesize myDatabase;
• @synthesize searchResult;

11. Let's create a findStudent method (IBAction). I basically copied the getTextFromDB() method from ListViewController but we will tweak it a little in the SQL Query. Connect this IBAction to the Search Button in our SearchViewController.xib.

- (IBAction) findStudent :(id)sender {

    NSString *docsDir;

    NSArray *dirPaths;

    

    // Get the documents directory

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = dirPaths[0];

    

    // Build the path to the database file

    databasePath = [[NSString alloc]

                    initWithString: [docsDir stringByAppendingPathComponent:

                                     @"sampleDatabase.db"]];

    

    const char *dbpath = [databasePath UTF8String];

    sqlite3_stmt    *statement;

    

    if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK)

    {

        NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM SAMPLETABLE WHERE %@", [self whereClause]]; //whereClause method in Step 12

        

        const char *query_stmt = [querySQL UTF8String];

        

        if (sqlite3_prepare_v2(myDatabase, query_stmt, -1, &statement, NULL) == SQLITE_OK) {

            [searchResult removeAllObjects];

            while (sqlite3_step(statement) == SQLITE_ROW) {

                //NSString *personID = [[NSString alloc] initWithUTF8String: (const char *) sqlite3_column_text(statement, 0)];

NSString * firstName = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)]; 

NSString * lastName = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)]; 

NSString * course = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)]; 

NSString * year = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)]; 

                

                 [searchResult addObject:[NSString stringWithFormat:@"%@ %@ %@-%@", firstName, lastName, course, year]];

            }

           sqlite3_finalize(statement);

        }

        sqlite3_close(myDatabase);

    }
    

    //Result/s of the query will be shown in our Console Area.

    for (int i = 0; i < [searchResult count]; i++) {

        NSLog(@"Result: %@", [searchResult objectAtIndex:i]);

    }

}

12. I created another method that will append and finalize the where clause of our SQL query.

- (NSString *) whereClause {

    NSString *where = @"";

    BOOL withWhereClause = NO;

    

    if (self.firstNameTextField.text.length > 0) {

        where = [where stringByAppendingString:[NSString stringWithFormat:@"FirstName=\"%@\"", self.firstNameTextField.text]];

        withWhereClause = YES;

    }

    if (self.lastNameTextField.text.length > 0) {

        if (withWhereClause) {

            where = [where stringByAppendingString:@" and "];

        }

        where = [where stringByAppendingString:[NSString stringWithFormat:@"LastName=\"%@\"", self.lastNameTextField.text]];

        withWhereClause = YES;

    }

    if (self.courseTextField.text.length > 0) {

        if (withWhereClause) {

            where = [where stringByAppendingString:@" and "];

        }

        where = [where stringByAppendingString:[NSString stringWithFormat:@"Course=\"%@\"", self.courseTextField.text]];

        withWhereClause = YES;

    }

    if (self.yearTextField.text.length > 0) {

        if (withWhereClause) {

            where = [where stringByAppendingString:@" and "];

        }

        where = [where stringByAppendingString:[NSString stringWithFormat:@"Year=\"%@\"", self. yearTextField.text]];

    }

    //NSLog(@"where clause: %@", where);

    return where;

}

Search and return data from our SAMPLETABLE - DONE!

Hit Run!

Download Sample Project here.