MySQL C API: How to work with row from mysql_fetch_row()?

Question

This is some example code from my app:

int page_id;
string page_name;

enum COLUMNS {
    PAGE_ID,
    PAGE_NAME
};

if (mysql_query(conn, "SELECT page_id, page_name FROM pages")) {
    exit_mysql_error(conn);
}

MYSQL_RES *res = mysql_use_result(conn);

while (MYSQL_ROW row = mysql_fetch_row(res)) {
    page_id = *(int *)res[PAGE_ID];
    page_name = res[PAGE_NAME];
    // do some stuff where I need the page_id and page_name int/string
}

So imho this isn't the right way to obtain the integer value from the result (yet it works) but I couldn't find good example on how to do it. Also I'm quite new to programming in C++, so any feedback on the code sample is welcome.

Answer 1

page_id = atoi(row[0]);
page_name = row[1]; // not really sure

Answer 2

You can find here a complete example on how to use the Mysql API in C/C++:

Writing into mysql database from a single board computer using c

To get your fields:

while ((row = mysql_fetch_row (query_result))) 
{
    /* Each field in represented by the string row[i] */
    /* If the field you want to extract is an int, so you need to convert it */
    page_id = atoi(row[i]);
    page_name = row[2];

    last_filed = row[number_of_fields_in_your_table - 1];
}

You can also construct a buffer with the result of your query and parse it with a separator

while ((row = mysql_fetch_row (query_result))) 
{
        memset(buffer, '\0', sizeof(buffer));

        for (i = 0; i < num_fields - 1; i++)
        {
            strncat(buffer, row[i], strlen(row[i]) + 1);
            strncat(buffer, ";", 2);            
        }
        /* After this your buffer will contain 
         * buffer = "page_id;page_name;...;" 
         * and you can retreive all fields with snprintf for example
         */
         snprintf(buffer, sizeof(buffer), "%d;%s;...", page_id, page_name,...);
}

Answer 3

You could use a stringstream

 std::istringstream strStream(res[PAGE_ID]);
 strStream >> page_id;

you would need to #include<sstream> for this. Here I am assuming that your PAGE_ID value is correctly set to the column index to get the right column. I assumed from the question that your only worry was the casting from string to int* and that your column indices are already correct