Using C to execute SQL statements in the MySQL database (3)

Retrieving data

Now start writing the first program that retrieves data from a database. We will select the contents of all rows older than 5. Unfortunately we don't yet know how to handle this data, so all we can do is loop through it. This is select1.c:

#include
#include
#include "mysql.h"
MYSQL my_connection;
Mysql_res *res_ptr;
Mysql_row Sqlrow;
int main (int argc, char *argv[]) {
int res;
Mysql_init (&my_connection);
if (Mysql_real_connect (&my_connection, "localhost", "Rick"),
"Bar", "Rick", 0, NULL, 0)) {
printf ("Connection success\n");
res = mysql_query (&my_connection, "Select Childno, fname,
Age from children WHERE age > 5 ");
if (res) {
printf ("Select Error:%s\n", Mysql_error (&my_connection));
} else {
Res_ptr = Mysql_store_result (&my_connection);
if (res_ptr) {
printf ("Retrieved%luows\n", (Unsignedlong) mysql_num_rows (res_ptr));
while ((Sqlrow = Mysql_fetch_row (res_ptr))) {
printf ("fetched data...\n");
}
if (Mysql_errno (&my_connection)) {
fprintf (stderr, "retrive error:s\n", Mysql_error (&my_connection));
}
}
Mysql_free_result (RES_PTR);
}
Mysql_close (&my_connection);
} else {
fprintf (stderr, "Connection failed\n");
if (Mysql_errno (&my_connection)) {
fprintf (stderr, "Connection error%d:%s\n",
Mysql_errno (&my_connection), Mysql_error (&my_connection));
}
}
return exit_success;
}


Retrieving the result set and looping through important parts of the retrieved data has been highlighted.

Retrieve one row of data at a time

To retrieve the data row by line, instead of getting all the data immediately and storing it in the client, you can replace the Mysql_store_result call with Mysql_use_result:
Mysql_res *mysql_use_result (MySQL *connection);


This function also obtains a connection object and returns the result binding pointer, or returns NULL when an error occurs. Similar to Mysql_store_result, it returns a pointer to a result set object; The key difference is that when you return, you actually don't retrieve any data to the result set, just initialize the result set to be ready to retrieve the data.

Resources

You can refer to the English version of this article at the Developerworks Global site.

This article is taken from the 5th chapter of the professional Linux book published by Wrox Press Ltd.

About the author

Rick Stones is a system designer who works for the IT department of a large pan-European pharmaceutical distribution and distribution company. Since 1985, he has been using various forms of UNIX and found Linux with an early. CD-ROM release. He writes software for UNIX, Linux, and other platforms in a variety of languages, as well as installing and managing several Linux servers. In a very limited spare time, he tried to improve his piano playing skills.