Executing SQL Statements In MySQL Databases Using C Retrieving the data

Executing SQL statements in MySQL databases using C

By Neil Matthew & Richard Stones - 2004-02-06 Page: 1 2 3 4 5

Retrieving the data

We are now in a position to write our first program that retrieves data from the database. We're going to select the contents of all rows for which age is greater than 5. Unfortunately we don't know how to process this data yet, so all we can do it loop round retrieving it. This is select1.c :


      #include <stdlib.h>
      #include <stdio.h>
      #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 %lu rows\n", (unsigned long)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;
       }

The important section, where we retrieve a result set and loop through the retrieved data, is highlighted.

Retrieving the data one row at a time

To retrieve the data row by row, as we require it, rather than fetching it all at once and storing it in the client, we can replace the mysql_store_result call with mysql_use_result :

MYSQL_RES *mysql_use_result(MYSQL *connection);

This function also takes a connection object and returns a result set pointer, or NULL on error. Like mysql_store_result , this returns a pointer to a result set object; the crucial difference though, is that it hasn't actually retrieved any data into the result set when it returns, just initialized the result set ready to receive data.

Resources

This article is extracted from Chapter 5 of Professional Linux from Wrox Press Ltd

View Executing SQL statements in MySQL databases using C Discussion

Page: 1 2 3 4 5 Next Page: Excerpt from Professional Linux Programming

First published by IBM developerWorks