Fetching rows in a MySQL database table using MySQL C API and C++

Question

I'm confused when trying to fetch table rows in mysql using C++ with MySQL C API.

I can do it easily in PHP, just because C++ is a strongly-typed language so that we also need to take care of the dirty process..

This is how I done it in PHP

$data = array();
$i = 0;
$query = mysql_query("SELECT * FROM `my_table`");
while($fetch = mysql_fetch_array($query))
{
  $data[$i] = $fetch['columntobefetched'];
  $i++;
}

But how to do the same in C++ with MySQL API?

Here's my code so far....with a confusing dead end...x__x

   MYSQL *sqlhnd = mysql_init(NULL);
    mysql_real_connect(sqlhnd, "server", "user", "pass", "database", port, NULL, 0);

    mysql_query(sqlhnd, "SELECT * FROM `my_table`");
    MYSQL_RES *confres = mysql_store_result(sqlhnd);
    int totalrows = mysql_num_rows(confres);
    int numfields = mysql_num_fields(confres);
    MYSQL_FIELD *mfield;

    while((row = mysql_fetch_row(confres)))
    {
        for(i = 0; i < numfields; i++)
        {
            while(mfield = mysql_fetch_field(confres))
            {
                mfield->//??? I'm dead
            }
        }
    }

Basically I wanted to get a value from a field in the database table and store it to a variable..

Any kind of help would be appreciated :)

Thanks

Avi · Accepted Answer · 2009-07-28 14:59:05Z

15

In the MySQL C API, mysql_fetch_row returns a MYSQL_ROW object, which is essentially an array of values in the current row.

So, your code should be something like:

mysql_query(sqlhnd, "SELECT * FROM `my_table`");
MYSQL_RES *confres = mysql_store_result(sqlhnd);
int totalrows = mysql_num_rows(confres);
int numfields = mysql_num_fields(confres);
MYSQL_FIELD *mfield;

while((row = mysql_fetch_row(confres)))
{
    for(i = 0; i < numfields; i++)
    {
        char *val = row[i];
        // do something with val...
    }
}

Better yet, don't do a "SELECT * FROM mytable" in a program. It would be much better to name the fields you expect, so that you can be sure of the order of the fields returned.

answered Jul 28, 2009 at 14:59

Avi

20.2k4 gold badges60 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cp.engr Over a year ago

What does the abbreviation "conf" stand for in your variable names?

cp.engr Over a year ago

This will leak memory. "You must also call mysql_free_result() after you are done with the result set." dev.mysql.com/doc/refman/5.7/en/mysql-store-result.html See also dev.mysql.com/doc/refman/5.7/en/c-api-function-overview.html

alecail · Accepted Answer · 2012-05-27 20:07:59Z

4

If you are using C++ why not using MySQL++? This is some sample code:

mysqlpp::Connection dbconnection;
dbconnection.connect("database", "server", "user", "pass");

mysqlpp::Query prepared_query = dbconnection.query( "SELECT * FROM `my_table`" );

mysqlpp::StoreQueryResult r = prepared_query.store();

int field = r.field_num("columntobefetched");

for(mysqlpp::StoreQueryResult::iterator i = r.begin(); i!=r.end();i++)
{
      std::cout << i->at(field) << std::endl;
      std::cout << (*i)["columntobefetched"] << std::endl; // this will be slower
}

edited May 27, 2012 at 20:07

alecail

4,0524 gold badges35 silver badges52 bronze badges

answered Jul 28, 2009 at 15:05

Arpegius

5,91741 silver badges56 bronze badges

2 Comments

VolkerK Over a year ago

Also (by default) the c++ connector code throws exceptions if something fails. Since your example C code doesn't have any error handling at all, you might be interested in that feature.

Warren Young Over a year ago

Nit: you need double quotes around "columntobefetched"

Collectives™ on Stack Overflow

Fetching rows in a MySQL database table using MySQL C API and C++

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related