How to Display data from SQlite into Table views to iPhone app

Question

I'm working on an iPhone project in Xcode 4.3 with SQlite3, the connection between the SQlite and Xcode is done, now I want to display my data into a table views (three views) and its read only! so I have the main table view, select raw --> take to 2nd view and load other data from the DB select raw --> take to the details view to display long text and image!

Any help appreciated.

AppDelegate.h

#import "AppDelegate.h"

#import "MasterViewController.h"

@implementation AppDelegate

@synthesize window = _window;
@synthesize navigationController = _navigationController;

- (void)dealloc
{
    [_window release];
    [_navigationController release];
    [super dealloc];
}

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    self.window = [[[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]] autorelease];
    // Override point for customization after application launch.


    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,    NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];

    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"cities.sqlite"];

    NSFileManager *fileManager = [NSFileManager defaultManager];

    BOOL success = [fileManager fileExistsAtPath:dbPath];

    if (success) {

        NSLog(@"we have the database");

    } else {

        NSLog(@"we have no database");

        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"cities.sqlite"];


        BOOL moved = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:nil];

        if (moved) {
            NSLog(@"database copied");
        }

    }


    MasterViewController *masterViewController = [[[MasterViewController alloc] initWithNibName:@"MasterViewController" bundle:nil] autorelease];
    self.navigationController = [[[UINavigationController alloc] initWithRootViewController:masterViewController] autorelease];
    self.window.rootViewController = self.navigationController;
    [self.window makeKeyAndVisible];
    return YES;
}

MasterViewController.h

#import <UIKit/UIKit.h>
#import <sqlite3.h> 


@class DetailViewController;

@interface MasterViewController : UITableViewController {
    NSMutableArray *cities;
}

@property (strong, nonatomic) DetailViewController *detailViewController;

@end

MasterViewController.m

- (void)viewDidLoad
{
    [super viewDidLoad];

    students = [[NSMutableArray alloc] init];
    countries = [[NSMutableArray alloc] init];

    // Do any additional setup after loading the view, typically from a nib.
    self.navigationItem.leftBarButtonItem = self.editButtonItem;

    UIBarButtonItem *addButton = [[[UIBarButtonItem alloc] initWithBarButtonSystemItem:UIBarButtonSystemItemAdd target:self action:@selector(insertNewObject:)] autorelease];
    self.navigationItem.rightBarButtonItem = addButton;


    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"cities.sqlite"];


    sqlite3 *database;

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

        const char *sqlStatement = "select * from cities_info";

        sqlite3_stmt *compileStatement;

        if (sqlite3_prepare_v2(database, sqlStatement, -1, &compileStatement, NULL) == SQLITE_OK) {


            while (sqlite3_step(compileStatement) == SQLITE_ROW) {

                NSLog(@"one record");

                NSString *cityName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compileStatement, 1)];

                [cities addObject:cityName];

            }

            NSLog(@"cities: %@",cities);  

        }


    } else {


        NSLog(@"error in database");

    }
}

Blockquote

Answer 1

I suggest a light wrapper over SQLite - see https://github.com/JohnGoodstadt/EasySQLite

This will allow:

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    return _personTable.rows.count;
}

AND

    - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    ...

     NSArray* row= _personTable.rows[indexPath.row];
     cell.textLabel.text = row[[_personTable colIndex:@"lastname"]];
     ...

Set this up by using an iVar representing a SQL Table:

self.personTable = [_db  ExecuteQuery:@"SELECT firstname , lastname , age , salary FROM person"];

And a DB Connection iVar passing in your SQL file name:

self.db = [DBController sharedDatabaseController:@"DataTable.sqlite"];