iPhone, sqlite3, how do I pass values into a select statement?

Question

This is the code I'm using to select some records from my database. I'm binding two dates into my sql, however, when I get to sqlite3_step I get SQLITE_DONE where I should be getting SQLITE_ROW. It looks like its processing the bindings rather than querying the data.

What am I doing wrong ?

NSString *startDateRangeString = @"2000-05-01";
NSString *endDateRangeString = @"2011-05-01";

sqlite3 *database;
int result = sqlite3_open("mydb.db", &database);
if(result != SQLITE_OK)
{
    NSLog(@"Could not open db.");
}

const char *sql = "select pid from tmp where due >= '%@' and due < '%@' order by due, pid;";
sqlite3_stmt *statementTMP;

int error_code = sqlite3_prepare_v2(database, sql, -1, &statementTMP, NULL);
if(error_code == SQLITE_OK) {

    sqlite3_bind_text(statementTMP, 1, [startDateRangeString UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statementTMP, 2, [endDateRangeString UTF8String], -1, SQLITE_TRANSIENT);

    int step_error_code = sqlite3_step(statementTMP);

    while(sqlite3_step(statementTMP) == SQLITE_ROW) // I get 101 aka SQLITE_DONE
    {
        NSLog(@"Found!!");
    }
}
sqlite3_finalize(statementTMP);
sqlite3_close(database);

Answer 1

I think your SQL is bad. Instead of %@ you should use ? for the positional parameters.

I strongly suggest using a wrapper to simplify life. FMDB is a great one at http://github.com/ccgus/fmdb.

Answer 2

Hi jules.

This is by design.

sqlite3_step returns SQLITE_ROW for each row in the resultset, and SQLITE_DONE to indicate there are no more rows. So if a resultset contains N rows, then N calls would return SQLITE_ROW and (N+1)st returns SQLITE_DONE. An empty resultset is not in any way special, it follows the same logic with N=0 (so the very first call returns SQLITE_DONE). This allows client code to handle all resultsets uniformly.

Hope that helps.

Let me know if you need anymore help.

PK

Answer 3

    char *statementTMP = "select pid from tmp where due >= '?1' and due < '?2' order by due, pid";
....
    sqlite3_bind_text(statementTMP, 1, [startDateRangeString UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statementTMP, 2, [endDateRangeString UTF8String], -1, SQLITE_TRANSIENT);

Answer 4

If I read correctly.. You are not executing the statement.. you are just preparing.. you have to do both..

this part just prepare the query

if (sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) != SQLITE_OK) 
{
            NSLog(@"SQL Warning: failed to prepare statement 
with message '%s'.", sqlite3_errmsg(database));
}

This part actually executes the query

if(sqlite3_exec(database, sql, nil, &stmt, &errmsg) == SQLITE_OK)
{
        NSLog(@"it means that you query executed correctly"); 
        if(sqlite3_step(stmt) == SQLITE_ROW)
        {
             NSLog(@"Found!!");
        }
}else 
{
        NSLog(@"SQL Warning: '%s'.", sqlite3_errmsg(database));
}

=)