i got a sqlite database but something is wrong with my statement this is my method to open the database and to retrieve some data i need:
- (void)createEditableCopyOfDatabaseIfNeeded
{
// First, test for existence.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"monsterDB.DB"];
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success)
{
// TODO: auch aktuelle version ? -> server check
return;
}
// TODO: The writable database does not exist, so copy the default from server to the appropriate location.
}
- (sqlite3 *)getDBConnection
{
[self createEditableCopyOfDatabaseIfNeeded];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"monsterDB.DB"];
// Open the database. The database was prepared outside the application.
sqlite3 *newDBConnection;
if (sqlite3_open([path UTF8String], &newDBConnection) == SQLITE_OK)
{
NSLog(@"Database Successfully Opened :)");
}
else
{
NSLog(@"Error in opening database :(");
}
return newDBConnection;
}
- (DEMonster *)getEventByType:(NSString *)type
{
DEMonster *result = nil;
sqlite3 *connection = [self getDBConnection];
NSString *textString = [NSString stringWithFormat:@"SELECT * FROM monsters WHERE type = '%@' ORDER BY RANDOM() LIMIT 1;", @"city"]; // TODO: Hier type nehmen für testzwecke nur city
const char *text = [textString UTF8String];
sqlite3_stmt *select_statement;
if (sqlite3_prepare_v2(connection, text, -1, &select_statement, NULL) != SQLITE_OK)
{
// error
NSLog(@"Error");
sqlite3_close(connection);
return result;
}
if (sqlite3_step(select_statement) == SQLITE_ROW)
{
result = [[DEMonster alloc] init];
result.name = [[NSString alloc] initWithUTF8String:(char const *)sqlite3_column_text(select_statement, 1)];
result.attacks = [[NSMutableArray alloc] initWithObjects:[[NSString alloc] initWithUTF8String:(char const *)sqlite3_column_text(select_statement, 2)], [[NSString alloc] initWithUTF8String:(char const *)sqlite3_column_text(select_statement, 3)], nil];
result.defenses = [[NSMutableArray alloc] initWithObjects:[[NSString alloc] initWithUTF8String:(char const *)sqlite3_column_text(select_statement, 4)], [[NSString alloc] initWithUTF8String:(char const *)sqlite3_column_text(select_statement, 5)], nil];
int length = sqlite3_column_bytes(select_statement, 6);
NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(select_statement, 6) length:length];
result.monsterIcon = [UIImage imageWithData:imageData];
result.attackValue = sqlite3_column_double(select_statement, 12);
result.defenseValue = sqlite3_column_double(select_statement, 13);
result.attackValues = [[NSMutableArray alloc] initWithObjects:[NSNumber numberWithDouble:sqlite3_column_double(select_statement, 8)], [NSNumber numberWithDouble:sqlite3_column_double(select_statement, 9)], nil];
result.defenseValues = [[NSMutableArray alloc] initWithObjects:[NSNumber numberWithDouble:sqlite3_column_double(select_statement, 10)], [NSNumber numberWithDouble:sqlite3_column_double(select_statement, 11)], nil];
}
sqlite3_finalize(select_statement);
sqlite3_close(connection);
return (result);
}
Basically the problem is in the "getEventByType" method. Somehow i always get the "Error" NSLog, because my statement does not return "SQLITE_OK".
The output looks like this:
2014-04-15 22:24:15.805 MonsterSafari[3701:60b] Database Successfully Opened :)
2014-04-15 22:24:15.806 MonsterSafari[3701:60b] Error
As you can see the database looks to be opened successfully, but somehow the statement looks to be wrong. When i run the exact same statement in my sqlbrowser (some app i use to browse the sqlite database) the query works just fine. Any idea what could possibly be wrong ?
Thanks in advance
stringWithFormatto build a query. Bad things will happen. Do it properly by binding values to the query.sqlite3_openwould create blank database. So (a) uninstall and reinstall app (to get rid of any blank database); (b) usesqlite3_open_v2which will not create blank database; and (c) double check your database creation routine.