Solution for sqlite3_prepare : Problem with prepare statement

I followed a tutorial of SQL Lite located at : http://klanguedoc.hubpages.com/hub/Tutorial-on-Creating-an-IOS-5-SQLite-Database-Application-IOS-5-SQLite#

Titled : Tutorial on Creating an iOS SQLite Database Application.

It is truly a great tutorial on getting started for Sql Lite.

I followed the tutorial but i was stuck into a problem of sqlite3_prepare not working as expected.  It was not returning ok.  Here is the offending line of code

if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)

{

NSLog(@”Problem with prepare statement”);

}

 

It was always printing > Problem with prepare statement.

After searching a lot on various forums and answer sites like stackoverflow i came up with a solution that works for me.  Change the file name from First to your filename

-(NSMutableArray *) getMyWines

{

NSMutableArray *wineArray = [[NSMutableArray alloc] init];

@try {

NSFileManager *fileMgr = [NSFileManager defaultManager];

NSString *dbPath = [[NSBundle mainBundle]pathForResource: @”First” ofType:@”sqlite”];

BOOL success = [fileMgr fileExistsAtPath:dbPath];

if(!success)

{

NSLog(@”Cannot locate database file ‘%@’.”, dbPath);

}

if(!(sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK))

{

NSLog(@”An error has occured.”);

}

const char *sql = “SELECT id, Wine, Rating, Photo FROM  WineTbl”;

sqlite3_stmt *sqlStatement;

if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)

{

NSLog(@”Problem with prepare statement”);

}

if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)

{

NSLog(@”Problem with prepare statement”);

}

while (sqlite3_step(sqlStatement) == SQLITE_ROW) {

WineList *myWine = [[WineList alloc] init];

myWine.wineId = sqlite3_column_int(sqlStatement, 0);

myWine.wine = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement, 1)];

myWine.rating = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement, 2)];

const char *raw = sqlite3_column_blob(sqlStatement, 3);

int rawLen = sqlite3_column_bytes(sqlStatement, 3);

NSData *data = [NSData dataWithBytes:raw length:rawLen];

myWine.photo = [[UIImage alloc] initWithData:data];

[wineArray addObject:myWine];

}

}

@catch (NSException *exception) {

NSLog(@”An exception occured:%@”, [exception reason]);

}

@finally {

return wineArray;

}

 

Let me know in case if it doesn’t work for you.

Advertisements

2 comments

  1. i’m having same problem 😦
    here’s my code:

    -(NSMutableArray *) getChampionDatabase
    {
    NSMutableArray *championsArray = [[NSMutableArray alloc] init];

    @try {

    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSString *databasePath = [[NSBundle mainBundle]pathForResource: @”Champions Database” ofType:@”sqlite”];
    BOOL success = [fileManager fileExistsAtPath:databasePath];

    if (!success)
    {
    NSLog(@”cannot connect to Database! at filepath %@”,databasePath);
    }
    else
    {
    NSLog (@”SUCCESS!!”);
    }
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {

    const char *sql = “SELECT CHAMPION_ID,CHAMPION_NAME,CHAMPION_IMG FROM CHAMPIONS”;
    sqlite3_stmt *sqlStatement;

    if(sqlite3_prepare_v2(database, sql, -1, &sqlStatement, NULL) == SQLITE_OK)
    {
    while (sqlite3_step(sqlStatement)==SQLITE_ROW) {
    championList *ChampionList = [[championList alloc]init];
    ChampionList.championId = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,0)];
    ChampionList.championName = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement,1)];
    ChampionList.championImage = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStatement, 2)];
    [championsArray addObject:ChampionList];
    }
    }
    else
    {
    NSLog(@”problem with database prepare”);
    }
    }
    else
    {
    NSLog(@”problem with database openning”);
    }
    }
    @catch (NSException *exception)
    {
    NSLog(@”An exception occured: %@”, [exception reason]);
    }
    @finally
    {
    return championsArray;
    }
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s