Stuck on same results after some random select query

Question

Weird stuff. I'm querying a small SQLite database. I want to get random result from it. It works quite well, but after about 10-15 queries, I get stuck on the same results. I must obviously be missing something, though I can't figure out what. Here's some code showing you the method I'm calling over and over :

 -(NSString*)setBtnImage:(UIButton*) btn{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"database.sqlite"];
    NSString *result;
    NSString *resultTemp;
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK){
        const char *sql = "SELECT * FROM main ORDER BY RANDOM() LIMIT 1;";
        sqlite3_stmt *searchStatement;
        if(sqlite3_prepare_v2(database, sql, -1, &searchStatement, NULL) == SQLITE_OK){
            while (sqlite3_step(searchStatement) == SQLITE_ROW) {
                NSString *mot = [NSString stringWithUTF8String:(char *)sqlite3_column_text(searchStatement, 1)];
                NSString *nom_fichier = [NSString stringWithUTF8String:(char *)sqlite3_column_text(searchStatement, 3)];
                resultTemp = [mot stringByAppendingString:@" "];
                result = [resultTemp stringByAppendingString:nom_fichier];
                UIImage *image = [UIImage imageNamed:nom_fichier];
                [btn setImage:image forState:UIControlStateNormal];
                [btn setTitle:mot forState:UIControlStateNormal];
            }
        }
        sqlite3_finalize(searchStatement);
    }
    return result;
}

So, you can see that I'm getting a filename from the database in order to know which file to apply to a button. And that's it. It works quite well until I'm stuck on the same results, forever. No error messages, though.

I'm using Xcode 4.6

Any help appreciated.

Thank you, folks.

You mean the results keep repeating or you get the same single result everytime after a while? — DrummerB, May 16 '13 at 19:47
Some tips: Keep the database open until you need to close it (like when it goes into the background) and specify the names of the columns in your `SELECT` so you can see what you selecting without referring to the schema. — trojanfoe, May 16 '13 at 19:51
try http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql — mCube, May 16 '13 at 19:52
I cannot reproduce this behaviour. Must be something else wrong. How many rows are in the table? I have chosen a file with 250 rows and they come random all the way and it does not play any role if I close and open the database or not. — hol, May 16 '13 at 20:52

score 0 · Answer 1 · answered May 16 '13 at 21:26

Try to add:

sqlite3_close(database);

after

sqlite3_finalize(searchStatement);

After playing around with it for a while I got a strange SQLITE_PROTOCOL error when reopening the database again after a while. The error means the database is locked. It turned out that this happens if the database is not closed after use. The way you have written your routine you never close the database but reopen it each time. As there is also not much error handling it might only looked like that you are getting the same result again. Because if an error happens you just do not set the new button image. Though I must say that I had to open and not close the database very often not just 10-15 times. I had a loop reading random numbers 25 times and I did this when I went in background and when I came in foreground. So it happened after about 250 times.

However what you must do is to add some error logging so you can easily see what is going on. So put some else branches in your code and NSLog it.

My modified test version looks like this:

-(NSString*)setBtnImage
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"database2.sqlite"];
    NSString *result;
    // NSString *resultTemp;
    sqlite3 *database;
    int rc = sqlite3_open([path UTF8String], &database) ;
    if (rc == SQLITE_OK){
        const char *sql = "SELECT id FROM log ORDER BY RANDOM() LIMIT 1;";
        sqlite3_stmt *searchStatement;
        int rc = sqlite3_prepare_v2(database, sql, -1, &searchStatement, NULL) ;
        if(rc == SQLITE_OK){
            if (sqlite3_step(searchStatement) == SQLITE_ROW) {
                int r=sqlite3_column_int(searchStatement, 0);
                result = [NSString stringWithFormat:@"%i",r];
            } else {
                result = @"nothing";
            }
            sqlite3_finalize(searchStatement);
        } else {
            NSLog(@"rc=%i",rc);
        }
        sqlite3_close(database);
    } else {
        NSLog(@"could not open rc=%i",rc);
    }

    return result;
}

Stuck on same results after some random select query

1 Answers1