0

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

9
  • 3
    So, what does sqlite3_errmsg return?? Commented Apr 15, 2014 at 20:39
  • 2
    BTW - DO NOT use stringWithFormat to build a query. Bad things will happen. Do it properly by binding values to the query. Commented Apr 15, 2014 at 21:03
  • It says "no such table monsters" but its definetly there. Commented Apr 15, 2014 at 22:00
  • 1
    possible duplicate of iOS Sqlite3 SQLITE_ERROR Commented Apr 15, 2014 at 22:17
  • 1
    A common mistake is to have an error in the database creation logic (even if you had a problem in a previous version) and the sqlite3_open would create blank database. So (a) uninstall and reinstall app (to get rid of any blank database); (b) use sqlite3_open_v2 which will not create blank database; and (c) double check your database creation routine. Commented Apr 15, 2014 at 22:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.