CODEWITHSUNDEEP
iosobjective-csqlite
user3245831
user3245831

Reputation: 13

How to retrieve value from SQLite database

I'm trying to get data from my DB but I have some problem.

Here is my code:

NSString *action=[[NSString alloc]init];
NSString *queryStatement = [NSString stringWithFormat:@"SELECT ACTIONNAME FROM ACTIONS WHERE ACTIONSYMBOL = '%@'", symbol];

// Prepare the query for execution
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
    // Create a new address from the found row
    while (sqlite3_step(statement) == SQLITE_ROW) {
        action = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)]; // fails on this line
    }
    sqlite3_finalize(statement);

    return action;
}

(the parameter symbol come from outside)

When I run this, it fails at the line with the call to stringWithUTF8String with sqlite3_column_text results.

Upvotes: 1

Views: 1050

Answers (2)

Rob
Rob

Reputation: 437542

In your call to sqlite3_column_text, you're using the index 1, but it takes a zero-based index. Use 0 instead of 1. See the SQLite sqlite_column_XXX documentation, which says:

The leftmost column of the result set has the index 0.

By the way, since stringWithUTF8String throws an exception if you pass it a NULL value, it's often safer to check the result if sqlite3_column_text is not NULL before proceeding, and handle the error gracefully otherwise. Also, you might want to check for sqlite3_step and sqlite3_prepare_v2 errors, like so:

NSString *queryStatement = [NSString stringWithFormat:@"SELECT ACTIONNAME FROM ACTIONS WHERE ACTIONSYMBOL = '%@'", symbol];  // note, it can be dangerous to use `stringWithFormat` to build SQL; better to use `?` placeholders in your SQL and then use `sqlite3_bind_text` to bind the `symbol` value with the `?` placeholder

if (sqlite3_prepare_v2(database, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
    int rc;
    while ((rc = sqlite3_step(statement)) == SQLITE_ROW) {
        const unsigned char *value = sqlite3_column_text(statement, 0);  // use zero

        if (value) {
            NSString *action = [NSString stringWithUTF8String:(const char *)value];

            // now do whatever you want with `action`, e.g. add it to an array or what
        } else {
            // handle the error (or NULL value) gracefully here
        }

        // make sure to check for errors in `sqlite3_step`

        if (rc != SQLITE_DONE)
        {
            NSLog(@"%s: sqlite3_step failed: %s", __FUNCTION__, sqlite3_errmsg(database));
        }
    }
}
else
{
    NSLog(@"%s: sqlite3_prepare_v2 failed: %s", __FUNCTION__, sqlite3_errmsg(database));
}

Incidentally, as the above illustrates, to correctly perform all of the error checking is a little cumbersome. This is where FMDB can be useful, simplifying the above to (where db is an FMDatabase object that has been opened):

FMResultSet *rs = [db executeQuery:@"SELECT ACTIONNAME FROM ACTIONS WHERE ACTIONSYMBOL = ?", symbol];
if (!rs) {
    NSLog(@"%s: executeQuery failed: %@", __FUNCTION__, [db lastErrorMessage]);
    return;
}

while ([rs next]) {
    NSString *action = [rs stringForColumnIndex:0];

    // do whatever you want with `action` here
}

[rs close];

And if you use ? placeholders (rather than using stringWithFormat to build your SQL, which is dangerous) the benefits of using FMDB are even more compelling.

Upvotes: 0

trojanfoe
trojanfoe

Reputation: 122391

You probably want to collect the results in an NSMutableArray:

NSMutableArray *action = [[NSMutableArray alloc] init];
...

while (sqlite3_step(statement) == SQLITE_ROW) {
     [action addObject:[NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 0)]];
}

...

You can then see what was collected later:

for (NSString *s in action) {
    NSLog(@"%@", s);
}

EDIT As pointed out in @Rob's answer, the first column is 0, not 1.

Upvotes: 2

Related Questions