Databases and CGI Programs in C++
Using the MySQL C API

Reference

The MySQL AB Company offers the following online reference to employing C programs to interface with MySQL databases:

• MySQL C API

Introduction

To access a database through a C or C++ CGI program, we employ an Application Program Interface (API).  Using MySQL to illustrate database manipulation, we employ the MySQL C API in this module. The "MySQL API Reference" contains a list in this API of types and functions along with the types of the arguments and return values.  

MySQL has APIs for languages other than C.  For example, Mysql++ is a C++ API for MySQL.  Documentation on the MySQL site includes a tutorial.

Initialization

Once the request for information comes into the CGI program, the program must query the database.  The library mysqlclient provides various types, variables, and functions to aid the programmer in such access.  We use this library by including header files mysql.h and sys/time.h, which mysql.h requires, as follows:

#include <sys/time.h>
#include <mysql.h>

MYSQL is a structure type for the connection to the database server.   he structure has a number of fields, such as names of the user and server.  TIn the segment below, we declare pChemConnect_ptr to be a pointer to the MYSQL structure that is to represent a connection to the PChem computer with a desired chemical database. MYSQL_RES is a structure type for information concerning the results of a select or show query.  Below, we declare queryResult_ptr to be a pointer to a structure of this type.  The result of a query may involve many rows.  We declare row to be of type MYSQL_ROW, the array type of data for one row of a select query.  As we see shortly, because of the parameter and return value types in various MySQL API functions, code is more straightforward for pChemConnect_ptr and queryResult_ptr to be pointer variables, while row is an array.  The three declarations follow:

MYSQL     *pChemConnect_ptr;
MYSQL_RES *queryResult_ptr;
MYSQL_ROW  row;

We initialize the MYSQL structure to which pChemConnect_ptr points with a call to the MySQL API function mysql_init, as follows:

 mysql_init(pChemConnect_ptr);

Connection

After initialization, we establish a connection to the server containing a MySQL database by calling the API function mysql_real_connect, which has the following parameters:

�        Pointer to an initialized MYSQL structure

�        Name or IP address (character string) of the MySQL database server; empty string ("") for local machine

�        Username (character string) for connection; empty string ("") for client's UNIX login name

�        Password (character string) to authenticate user; empty string ("") to authenticate users with no passwords only

�        Initial database; empty string ("") for none

�        Port for server connection; 0 for default

�        UNIX socket for connection; empty string ("") for default

�        Set of particular flags; 0 for none

The statement below establishes a connection to a MySQL database server with the following arguments:

�        pChemConnect_ptr - pointer to the initialized MYSQL structure

�        pchem.wofford.edu - server

�        shifletab - username

�        coeL3prM - password

�        "" - no initial database

�        0 - default port

�        "" - default socket

�        0 - no flags

 mysql_real_connect(pChemConnect_ptr,
"pchem.wofford.edu", "shifletab",
                     "coeL3prM", "", 0, "", 0);

With a successful connection, the function's return value is a pointer to the MySQL structure.  However, if the connection was unsuccessful, mysql_real_connect returns NULL.  After calling mysql_real_connect, we should verify that the connection was successful and if not, display an error message and abort execution.  With a parameter of a MYSQL, such as pChemConnect_ptr, pointer, the MySQL API function mysql_error displays an error message. The following segment attempts to make a connection, tests for no connection and in such a case, displays an error message and aborts execution with exit:

if(mysql_real_connect (pChemConnect_ptr, "pchem.wofford.edu","pchem.wofford.edu",
       "shifletab", "coeL3prH", "", 0, "", 0)p == NULL)
{

   cout << mysql_error(pChemConnect_ptr);

   exit(1);

}

If we have not opened a database while connecting to the server with mysql_real_connect, we attempt to do so, such as the chemical database chemDB, with a call to the API function mysql_select_db.  If successful, the function returns zero; while if unsuccessful it returns a nonzero number.  In this case, we should display an error message, close the connection with mysql_close, and abort execution.  A segment to accomplish these tasks follows:

if (mysql_select_db(pChemConnect_ptr, "chemDB") != 0)

{

cout << mysql_error(pChemConnect_ptr); mysql_close(pChemConnect_ptr);

exit(1);

}

Query

Suppose we want to query the chemical database chemDB as in Example 1 of the module "Accessing Databases with SQL": Obtain the experiment number (ExpNumber) and mass (SolventMass) from all benzene tuples (SolventName = 'Benzene) in the relation ChemExperiment where SolventMass is greater than 100.  To do so, we call the API function mysql_query with arguments of the MYSQL pointer pChemConnect_ptr and the SQL query statement, written as a string constant with no terminating semicolon as part of the string.  For readability, we type the query statement on several lines; we delimit each line of the query with quotation marks and have at least one blank separating the text at the end of one string and the beginning of the next so that the concatenated string does not have words running together.  Similar to mysql_select_db, mysql_query returns 0 if the query is successful and non-zero if unsuccessful.  Because of the length of the statement, for readability we store this return value in a variable, here state, and then test if an error occurred by comparing state with 0, as follows:

int state = mysql_query(pChemConnect_ptr,

          "select ExpNumber, SolventMass "

           "from ChemExperiment "

           "where  SolventName = �Benzene� and

           "SolventMass > 100");

 

if(state != 0)

{

   cout << mysql_error(pChemConnect_ptr);

   exit(1);

}

If any of the data is binary, we must use mysql_real_query instead of mysql_query.  With this function, we have a third argument of the length of a row of data.  Thus, if ExpNumber and SolventMass have a combined length of eight (8) bytes, we could employ the following command:

int state = mysql_real_query(pChemConnect_ptr,

          "select ExpNumber, SolventMass "

           "from ChemExperiment "

          "where  SolventName = �Benzene� and "

          "SolventMass > 100", 8 );

 

Having performed the query, we use the MySQL API function mysql_store_result to store the set of tuples of the result set in a MYSQL_RES structure.  The function returns a pointer to this structure, which we store in a MYSQL_RES * variable, such as queryResult_ptr, with the following statement:

 queryResult_ptr = mysql_store_result(pChemConnect_ptr);

If we wish to determine the number of rows (tuples) that the database returns as the result of the query, we employ the API function mysql_num_rows with this query result pointer as an argument, as follows:

int numOfRows =  mysql_num_rows(queryResult_ptr);

A call to the MySQL API function mysql_fetch_row with the pointer to the query result (queryResult_ptr) as the argument returns the first or next row of the result as a character string array of attribute (field) values.  When all rows have been fetched, NULL is returned.  Each tuple meeting the requested query criteria above has two attribute values, ExpNumber and SolventMass so that the row array has two elements.  In the code below, we repeatedly fetch a row, storing the returned value in the MYSQL_ROW variable row, until mysql_fetch_row returns NULL.  In the loop, we display the two attribute values in row[0] and row[1].  The loop follows:

 while((row = mysql_fetch_row(queryResult_ptr)) != NULL)

   cout << "ExpNumber: "     << row[0]

        << ", SolventMass: " << row[1] << endl;

The CGI program can do far more than just displaying the results.  For example, the program could obtain the floating point number to the value of row[1] with atof(row[1]) and perform additional calculations before displaying the results.  Often, such computations easier and more flexible in a program than in database queries.

Completion

When we are through with the results of a query, we should release the space that the result set consumes with the API function mysql_free_result and an argument of the query result pointer, such as follows:

 mysql_free_result(queryResult_ptr);

As we do with database access errors, when accessing the database server is complete, we release the structure containing connection information by calling mysql_close, such as follows:

 mysql_close(pChemConnect_ptr);

Quick Review Question

Quick Review Question 4. a. Select the MySQL API function to release the space that a query result set consumes. MYSQL MYSQL* mysql_close mysql_free mysql_free_result mysql_num mysql_num_rows mysql_query mysql_real_ask mysql_real_query MYSQL_RES MYSQL_RES* MYSQL_ROW MYSQL_ROW* mysql_result mysql_store mysql_store_result b. Select this function's argument Connection structure Pointer to connection structure Pointer to query result structure Query result structure c. Select the MySQL API function to end the connection to the server. MYSQL MYSQL* mysql_close mysql_free mysql_free_result mysql_num mysql_num_rows mysql_query mysql_real_ask mysql_real_query MYSQL_RES MYSQL_RES* MYSQL_ROW MYSQL_ROW* mysql_result mysql_store mysql_store_result d. Select the type of the argument for mysql_close. MYSQL MYSQL* mysql_close mysql_free mysql_free_result mysql_num mysql_num_rows mysql_query mysql_real_ask mysql_real_query MYSQL_RES MYSQL_RES* MYSQL_ROW MYSQL_ROW* mysql_result mysql_store mysql_store_result e. Select this argument of mysql_close. Connection structure Pointer to connection structure Pointer to query result structure Query result structure

Compilation

To compile the access a C++ program, say MainAccess.cc, with the GNU compiler gcc, we employ the following command line:

 gcc -L/usr/local/mysql/lib -I/usr/local/mysql/include \
-o MainAccess MainAccess.cc -lmysql -lnsl -lsocket

 The directory after -L, here usr/local/mysql/lib, tells the system the location of the mysql library; and the directory after -I, here usr/local/mysql/include, gives the location of the include, or header, files.  These locations should be adjusted to reflect directories on a particular database server.

Exercises

1.      Consider a database called anthropology with a discovery relation having the following schema:

         (ItemID: int, DiscoveryDate: date, longitude: float, latitude: float,

         condition: { 'EXCELLENT', 'VERYGOOD', 'GOOD', 'POOR', 'VERYPOOR'},

         carbon14: float)

         Write segments of C++ code using the MySQL API to do each of the following tasks:

a.      Use the database, which is on the server anthro.wofford.edu.  Make up a user name and password.  Have appropriate error-handling segments.

b.      Declare the variables.  Output for an attribute of type date is a string of the form YYYY-MM-DD.

c.      View the ItemIDs and descriptions of all items in poor condition.

d.      Count the number of items

e.      Display the ItemID and approximate age of each item in the database.  Performing the computation in C++, not the select statement string, evaluate the approximate age of the item as  

2.      For the psychology database of Quick Review Question 4 of the module "Accessing Databases with SQL," write a CGI program segment to display each event code (EventNumber) and the number of times that event occurs.

3.      a.      The section on "Joining" from the module "Accessing Databases with SQL" describes three tables (ChemKnown, ChemSolvent, and ChemExperiment) of a chemical database.  We can compute an estimate of the molecular weight of a solute, as follows, where the masses are in grams:

Kf is a column in table ChemSolvent, and the other columns are in ChemExperiment.  Write a CGI program segment to display the experiment number (ExpNumber) and the computed molecular weight for each solvent. Perform the computation in C++, not a select statement string.

         b.      Write a CGI program segment to return the estimated molecular weight for the experiment number 37. Perform the computation in C++, not a select statement string.

Exercises 4-22 relate to the object-oriented development of a MySQL API interface. 

Development is based on material from MySQL & mSQL by R. J. Yarger, G. Reese, and T. King, O�Reilly Publ., Sebastopol, CA, 1999, pp. 203-215.

4.      Write a header file for a class Access to access a MySQL database as illustrated in the class diagram of Figure 1.  The private data, connect_ptr, is to point to the connection structure and is of type MYSQL *.  Descriptions and prototypes of the public methods follow:

�        Access - Constructor with the eight parameters of mysql_real_connect to establish connection with MySQL server and open the database.

     Access(char *, char *, char *,
          char *, unsigned int, char *, unsigned int );

�        ~ Access - Destructor

     ~Access(void)

�        close - Close the server connection (and make connect_ptr NULL).

      void close(void);

�        query - Send a query to the database (character string of type char *) and return a pointer to the result (type Result *)

   Result *query(char *);

�        query - Polymorphic version of the previous method to send a query (character string of type char *) with its length (type unsigned int) to the database and return a pointer to the result (type result *)

      Result *query(char *, unsigned int);

 Figure 1.   Class diagram for a class Access

 5.      Write code to define the close member function the Access class, which Exercise 4 describes.  Before calling mysql_close, make sure connect_ptr points to a memory location (i.e., is not NULL)

6.      Write code to define the destructor for the Access class, which Exercise 4 describes.

7.      Write code to define the constructor for the Access class, which Exercise 4 describes.  Initialize connect_ptr and perform the connections to the server and the database.  Process any error with a throw of mysql_error.

8.      Write code to define the one-parameter query member function for the Access class, which Exercise 4 describes.  First, verify that connect_ptr points to a memory location (i.e., is not NULL). Process any error with a throw of mysql_error.  Query with mysql_query.  The constructor for the Result class has a parameter of type MYSQL_RES * (see Exercise 10).  Thus, have the final statement be the following:

 return new result(mysql_store_result(connect_ptr));

 9.      Write code to define the two-parameter query method of Exercise 4 that also has an unsigned int parameter for the length of the result.

10.    Write a header file for a class Result for processing a query result from a MySQL database as illustrated in the class diagram of Figure 2.  The private data is as follows:

�        rowCount - Number of rows (tuples) in query result; type int

�        queryResult_ptr - Pointer to the query result structure; type MYSQL *. 

�        currentRow_ptr - Pointer to current row of query result; type row *

         Descriptions and prototypes of the public methods follow:

�        Result - Constructor with one parameter of a pointer to a query result (type MYSQL_RES *); assigns parameter to  queryResult_ptr and -1 to rowCount.

          Result(MYSQL_RES *);

�        ~ Result - Destructor.

      ~Result(void)

�        close - Free the space pointed to by queryResult_ptr and make pointer NULL.

      void close(void);

�        getCurrentRow - Return currentRow_ptr.

      Row *getCurrentRow(void);

�        getRowCount - Obtain and return the value of rowCount.

      int getRowCount(void); 

�        next - Fetch next row and point currentRow_ptr to the row, returning boolean value to indicate success or failure.

      bool next(void);

 Figure 2.   Class diagram for a class result

 11.    Write code to define the close member function for the Result class, which Exercise 10 describes.  Before calling mysql_free_result, make sure queryResult_ptr points to a memory location (i.e., is not NULL).

12.    Write code to define the destructor for the Result class, which Exercise 10 describes.

13.    Write code to define the constructor for the Result class, which Exercise 10 describes.

14.    Write code to define the getCurrentRow method member function for the Result class, which Exercise 10 describes.  Throw an exception if queryResult_ptr is NULL.

16.    Write code to define the next member function for the Result class, which Exercise 10 describes. Throw an exception message if queryResult_ptr is NULL.  Fetch the next row.  If unsuccessful, make currentRow_ptr NULL; otherwise, point currentRow_ptr to a new Row; that is, call new for the constructor of the Row class with arguments of queryResult_ptr and the fetched row.

17.    Write a header file for a Row class for handling a query request row of a MySQL database as illustrated in the class diagram of Figure 3.  The private data is as follows:

�        queryResult_ptr - Pointer to the query result structure; type MYSQL *. 

�        fields - Row (array of fields) of query result; type MYSQL_ROW

         Descriptions and prototypes of the public methods follow:

�        Row - Constructor with parameters of a pointer to a query result (type MYSQL_RES *) and of a row (type MYSQL_ROW); assigns former parameter to  queryResult_ptr and latter to fields.

      Row(MYSQL_RES *, MYSQL_ROW);

�        ~ Row - Destructor

      ~Row(void);

�        close - Make queryResult_ptr and fields NULL.

      void close(void);

�        getField - Return field (type char *) indicated by integer parameter with numbering starting at 1, not 0 as in index.

      char *getField(int);

�        getFieldCount - Obtain and return the number of fields.

      int getFieldCount(void);

 Figure 3.   Class diagram for a class Row

18.    Write code to define the close method for the Row class, which Exercise 17 describes.  If fields is already NULL, throw an exception.

19.    Write code to define the destructor for the Row class, which Exercise 17 describes.

20.    Write code to define the constructor for the Row class, which Exercise 17 describes.

21.    Write code to define the method getFieldCount for the Row class, which Exercise 17 describes.  Throw an exception if fields is NULL.  The mysql function mysql_num_fields with a pointer to a Result object as parameter, in this case queryResult_ptr, returns the number of fields (columns).

22.    Write code to define the method getField for the Row class, which Exercise 17 describes.  Throw an exception if fields is NULL or the argument is out of range.

Projects

See Exercises 3-22  for an object-oriented development of the MySQL C API interface, Access, Result, and Row.  Each project contains a file of SQL commands to generate table(s) and load data into the table(s).  Save this file in a directory containing your database.  Enter (use) the database into which you wish to create the table(s) and insert the data.  We can execute the sequence of commands in the file with a command having the following format:

    mysql -u username -p < MySQLfile

1.      Develop a C++ program using the MySQL API to perform the tasks of Accessing Atomic Spectra Database Assignment" from Project 1 of the module "Accessing Databases with SQL."  The assignment uses a database of Atomic Spectra. (The structure and data are derived by Dr. Orlando Karam from the NIST Atomic Spectra Database )

2.      Develop a C++ program using the MySQL C API to perform the tasks of "Accessing Superfund Database Assignment" from Project 2 of the module "Accessing Databases with SQL."  The assignment uses a database on Superfund sites in South Carolina (SC). (The structure and data are derived by Dr. Orlando Karam from the from the EPA's Superfund (CERCLIS) Database.)

3.      Develop a C++ program using the MySQL C API to perform the tasks of "Accessing Star Database Assignment" from Project 3 of the module "Accessing Databases with SQL."  The assignment uses a database of star data.  (The structure is derived by Dr. Orlando Karam from star.dat by Dan Welch (see Project 5 of "Introduction to Databases").)

4.      Continue Project 4 from "CGI Programs and Web Forms" by expanding your C++ CGI program to access a MySQL database and return the desired data to the user on a web page. Click here to download statements to create a database of Atomic Spectra.  (The structure of the table was derived and the data was taken by Dr. Orlando Karam from the NIST Atomic Spectra Database .)

6.      Continue Project 6 from "CGI Programs and Forms" by expanding your C++ CGI program to access a MySQL database and return the desired data to the user on a web page.  Click here to download a star database.  (The structure was derived by Dr. Orlando Karam from star.dat by Dr. Dan Welch (see Project 5 of "Introduction to Databases").)

Copyright � 2002, Dr. Angela B. Shiflet
All rights reserved