iPhone - SQLite Database Read

Question

I'm trying to do a basic application.
My files list:

DatabaseClass.h and .m,
IDRGameAppDelegate.h and .m,
scoredb.sqlite,
ScoreViewController.h and .m, 
MainWindow.xib // Main Window
ScoreWindow.xib //Show Score Window

1.) my database is ;

CREATE TABLE "game" ("id" INTEGER PRIMARY KEY ,"name" VARCHAR(32),"score" VARCHAR(20));
INSERT INTO "game" VALUES(1,'interclock','1.234');
INSERT INTO "game" VALUES(2,'dui','345');
INSERT INTO "game" VALUES(3,'reflex','987');

2-) i created DatabaseClass.h and DatabaseClass.m

DatabaseClass.h is:

#import <Foundation/Foundation.h>
@interface DatabaseClass : NSObject {

    NSString *name;
    NSString *score;
}
@property (nonatomic, retain) NSString *name;
@property (nonatomic, retain) NSString *score;
-(id)initWithName:(NSString *)n score:(NSString *)s;
@end

DatabaseClass.m is;

#import "DatabaseClass.h"
@implementation DatabaseClass
@synthesize name, score;
-(id)initWithName:(NSString *)n score:(NSString *)s{
    self.name = n;
    self.score = s;
    return self;
}
@end

my IDRGameAppDelegate.h is;

#import <UIKit/UIKit.h> 
#import <sqlite3.h> // Import the SQLite database framework

@class IDRGameViewController , ScoreViewController  ;


@interface IDRGameAppDelegate : NSObject <UIApplicationDelegate> {

    IBOutlet UIWindow *window;
    IBOutlet IDRGameViewController *viewController;
    IBOutlet ScoreViewController *scoreViewController;


    // Database variables
    NSString *databaseName;
    NSString *databasePath;

    // Array to store the animal objects
    NSMutableArray *scores; 
}

- (void)flipToScore;
- (void)scoreToMainBack;

-(void) checkAndCreateDatabase;
-(void) readScoreFromDatabase;

@property (nonatomic, retain)  UIWindow *window;
@property (nonatomic, retain)  IDRGameViewController *viewController;
@property (nonatomic, retain)  ScoreViewController *scoreViewController;
@property (nonatomic, retain) NSMutableArray *scores;

@end

IDRGameAppDelegate.m is;

#import "IDRGameAppDelegate.h"
#import "IDRGameViewController.h"
#import "ScoreViewController.h"
#import "DatabaseClass.h"


@implementation IDRGameAppDelegate

@synthesize window;
@synthesize viewController;
@synthesize scoreViewController;

@synthesize scores;



- (void)applicationDidFinishLaunching:(UIApplication *)application {

    // Setup some globals
    databaseName = @"scoredb.sqlite";

    // Get the path to the documents directory and append the databaseName
    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

    // Execute the "checkAndCreateDatabase" function
    [self checkAndCreateDatabase];

    // Query the database for all animal records and construct the "animals" array
    [self readScoreFromDatabase];

    // Override point for customization after app launch    
    [window addSubview:viewController.view];
    [window makeKeyAndVisible];
}

- (void)dealloc {
    [scores release];
    [viewController release];
    [window release];
    [super dealloc];
}


-(void) checkAndCreateDatabase{
    // Check if the SQL database has already been saved to the users phone, if not then copy it over
    BOOL success;

    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];

    // Check if the database has already been created in the users filesystem
    success = [fileManager fileExistsAtPath:databasePath];

    // If the database already exists then return without doing anything
    if(success) return;

    // If not then proceed to copy the database from the application to the users filesystem

    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

    [fileManager release];
}


-(void) readScoreFromDatabase {
    // Setup the database object
    sqlite3 *database;

    // Init the animals Array
    scores = [[NSMutableArray alloc] init];

    // Open the database from the users filessytem
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
        // Setup the SQL Statement and compile it for faster access
        const char *sqlStatement = "select * from game";
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {



            // Loop through the results and add them to the feeds array
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                // Read the data from the result row

                NSString *aName =[NSString stringWithCString:(char *)sqlite3_column_text(compiledStatement, 2)];
                NSString *aScore =[NSString stringWithCString:(char *)sqlite3_column_text(compiledStatement, 3)];

                //NSString *aName = [NSString stringWithString:(NSString *)sqlite3_column_text(compiledStatement, 2)];
                //NSString *aScore = [NSString stringWithString:(NSString *)sqlite3_column_text(compiledStatement, 3)];



                // Create a new animal object with the data from the database
                DatabaseClass *dbOBJ = [[DatabaseClass alloc] initWithName:aName score:aScore];

                // Add the animal object to the animals Array
                [scores addObject:dbOBJ];

                [dbOBJ release];
            }
        }
        // Release the compiled statement from memory
        sqlite3_finalize(compiledStatement);

    }
    sqlite3_close(database);

}


- (void)flipToScore {


    ScoreViewController *aSecondView = [[ScoreViewController alloc] initWithNibName:@"ScoreWindow" bundle:nil];
    [self setScoreViewController:aSecondView];
    [aSecondView release];

    [UIView beginAnimations:nil context:NULL];
    [UIView setAnimationDuration:2.0];
    [UIView setAnimationTransition:UIViewAnimationTransitionFlipFromRight forView:window cache:YES];
    [viewController.view removeFromSuperview];
    [self.window addSubview:[scoreViewController view]];
    [UIView commitAnimations];

}

- (void)scoreToMainBack {



    [UIView beginAnimations:nil context:NULL];
    [UIView setAnimationDuration:2.0];
    [UIView setAnimationTransition:UIViewAnimationTransitionFlipFromRight forView:window cache:NO];


    [scoreViewController.view removeFromSuperview];
    [self.window addSubview:[viewController view]];

    [UIView commitAnimations];


    [scoreViewController release];
    scoreViewController = nil;

}
@end

ScoreViewController.h is;

#import <UIKit/UIKit.h>

@interface ScoreViewController : UIViewController {

    IBOutlet UILabel *gameNameLabel;
    IBOutlet UILabel *gameScoreLabel;


}
@property (nonatomic, retain) IBOutlet UILabel *gameNameLabel;
@property (nonatomic, retain) IBOutlet UILabel *gameScoreLabel;

-(IBAction)refreshClick:(id)sender;

@end

and finally ScoreViewController.m is ;

#import "ScoreViewController.h"
#import "DatabaseClass.h"
#import "IDRGameAppDelegate.h"

@implementation ScoreViewController

@synthesize gameNameLabel,gameScoreLabel;


/*
// The designated initializer. Override to perform setup that is required before the view is loaded.
- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil {
    if (self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil]) {
        // Custom initialization
    }
    return self;
}
*/

/*
// Implement loadView to create a view hierarchy programmatically, without using a nib.
- (void)loadView {
}
*/

/*
// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad {
    [super viewDidLoad];
}
*/

/*
// Override to allow orientations other than the default portrait orientation.
- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation {
    // Return YES for supported orientations
    return (interfaceOrientation == UIInterfaceOrientationPortrait);
}
*/

-(IBAction)refreshClick:(id)sender {

    // Navigation logic -- create and push a new view controller
    IDRGameAppDelegate *appDelegate = (IDRGameAppDelegate *)[[UIApplication sharedApplication] delegate];
    DatabaseClass *dbOBJ = (DatabaseClass *)[appDelegate.scores objectAtIndex:0];



    gameNameLabel.text = [dbOBJ name];
    gameScoreLabel.text = [dbOBJ score];


}




- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning]; // Releases the view if it doesn't have a superview
    // Release anything that's not essential, such as cached data
}


- (void)dealloc {
    [super dealloc];
}


@end

Problem is: when I click to refreshButton program is locking and breaking, but I can't see an error.

Program is not working I didn't read and show database records. What is the problem?

Answer 1

Why did you decide to stick directly with SQLite on the iPhone? After iPhone OS 3.0 is available to all iPhone users, i would recommend to make use of this database abstraction. It is quite easy to use and it prevents you to code a bunch of database access code.

On Mike Swans blog is a useful introduction to core data, which may act as a starting point for your development.

As part of the iPhone reference documentation Apple published a Core Data Tutorial for the iPhone OS which covers the iPhone specific features.

I've used it in an application on my own and found it really time saving compared with the SQLite database code i had before.