SQlite查询 - 如何检索多个列数据？ [英] SQlite query - How do I retrieve multiple column data?

问题描述

我在使用xcode和cocos2dx从SQlite数据库获取多个值时在网上找到一个工作示例很困难。这里是我的SQL查询：

  char sql_query [100] 
 sprintf（sql_query，SELECT * FROM SQList WHERE ColumnD BETWEEN％d AND％d ORDER BY RANDOM（）LIMIT 1，MinColumnD，MaxColumnD）; 
  

查询它自己似乎工作，主要的问题是如何获取我收集的值从'select *'到另一个int或char参数，以便我可以使用它？

我发现一些例子使用回调结构或提到关于使用sqlite3_prepare_v2

我无法找到任何方法的示例，请帮助！ >解决方案

使用 sqlite3_exec 时，必须转换字符串中的所有值，并且必须使用回调的 void * 指针或一些全局变量来返回数据：

  struct MyData {
 string一个; 
 int B，C; 
}; 
 
 int exec_callback（void * ptr，int argc，char * argv []，char * names []）
 {
 vector< MyData> * list = reinterpret_cast< vector< MyData> *（ptr）; 
 MyData d; 
 d.A = argv [0]？ argv [0]：; 
 d.B = atoi（argv [1]）; 
 d.C = atoi（argv [2]）; 
 list-> push_back（d）; 
 return 0; 
} 
 
 void query_with_exec（）
 {
 vector< MyData>列表; 
 
 char * errmsg = NULL; 
 sqlite3_exec（db，SELECT a，b，c FROM SQList / * WHERE ... * /，
 exec_callback，& list，& errmsg）; 
 if（errmsg）{
 printf（error：％s！\\\
，errmsg）; 
 return; 
} 
 
 //使用列表... 
} 
  

$ b b

当使用 sqlite3_prepare * 时，必须在循环中调用 sqlite3_step ，直到它不返回 SQLITE_ROW （当您只需要一个记录时，只能调用一次）：

  void query_with_step（）
 {
 vector< MyData>列表; 
 sqlite3_stmt * stmt; 
 
 int rc = sqlite3_prepare_v2（db，SELECT a，b，c FROM SQList / * WHERE ... * /，
 -1，& stmt，NULL） 
 if（rc！= SQLITE_OK）{
 printf（error：％s！\\\
，sqlite3_errmsg（db））; 
 return; 
} 
 
 for（;;）{
 rc = sqlite3_step（stmt）; 
 if（rc == SQLITE_DONE）
 break; 
 if（rc！= SQLITE_ROW）{
 printf（error：％s！\\\
，sqlite3_errmsg（db））; 
 break; 
} 
 
 MyData d; 
 const char * text =（const char *）sqlite3_column_text（stmt，0）; 
 d.A =文本？ text：; $ b $ d d.B = sqlite3_column_int（stmt，1）; 
 d.C = sqlite3_column_int（stmt，2）; 
 list.push_back（d）; 
} 
 
 sqlite3_finalize（stmt）; 
 
 //使用列表... 
} 
  

I'm having great difficulty to find a working example on the net regarding getting multiple values from a SQlite DB using xcode and cocos2dx. Here is the sql query I have:

char sql_query[100];
sprintf(sql_query, "SELECT * FROM SQList WHERE ColumnD BETWEEN %d AND %d ORDER BY RANDOM() LIMIT 1", MinColumnD, MaxColumnD);

The query it self seems to work, the main problem is how do I get the values that I collect from 'select *' into another int or char parameter so that I can use it?

Some example I found referred to using a callback to a struct or mentioned about using sqlite3_prepare_v2 and the step method.

I'm unable to find an example for either methods though, please help!

解决方案

When using sqlite3_exec, you have to convert all values from strings, and you have to use the callback's void * pointer or some global variable to return data:

struct MyData {
    string A;
    int B, C;
};

int exec_callback(void *ptr, int argc, char *argv[], char *names[])
{
    vector<MyData> *list = reinterpret_cast<vector<MyData> *>(ptr);
    MyData d;
    d.A = argv[0] ? argv[0] : "";
    d.B = atoi(argv[1]);
    d.C = atoi(argv[2]);
    list->push_back(d);
    return 0;
}

void query_with_exec()
{
    vector<MyData> list;

    char *errmsg = NULL;
    sqlite3_exec(db, "SELECT a, b, c FROM SQList /* WHERE ... */",
                 exec_callback, &list, &errmsg);
    if (errmsg) {
        printf("error: %s!\n", errmsg);
        return;
    }

    // use list ...
}

When using sqlite3_prepare*, you have to call sqlite3_step in a loop until it does not return SQLITE_ROW anymore (when you expect only one record, you can call it only once):

void query_with_step()
{
    vector<MyData> list;
    sqlite3_stmt *stmt;

    int rc = sqlite3_prepare_v2(db, "SELECT a, b, c FROM SQList /* WHERE ... */",
                                -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        printf("error: %s!\n", sqlite3_errmsg(db));
        return;
    }

    for (;;) {
        rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE)
            break;
        if (rc != SQLITE_ROW) {
            printf("error: %s!\n", sqlite3_errmsg(db));
            break;
        }

        MyData d;
        const char *text = (const char *)sqlite3_column_text(stmt, 0);
        d.A = text ? text : "";
        d.B = sqlite3_column_int(stmt, 1);
        d.C = sqlite3_column_int(stmt, 2);
        list.push_back(d);
    }

    sqlite3_finalize(stmt);

    // use list ...    
}

这篇关于SQlite查询 - 如何检索多个列数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！