14.1. Using C with MySQL

This section presents the basic tasks you need to perform use the C API.

14.1.1. Connecting to MySQL

When writing a C program to interact with MySQL, first make a connection to MySQL. To do this easily, you need to include a couple of C header files: stdio.h for basic C functions and variables, and mysql.h for special MySQL functions and definitions. These two files come with C and MySQL, respectively; you shouldn't have to download them from the Web if both were installed properly.

   #include <stdio.h>    #include "/usr/include/mysql/mysql.h"    int main(int argc, char *argv[  ])    {            MYSQL *mysql;            MYSQL_RES *result;            MYSQL_ROW row;

Because of the < and > symbols surrounding stdio.h, C is instructed to look for it in the default location for C header files (e.g., /usr/include), or in the user's path. Because mysql.h may not be in the default locations, with the aide of double quotes, the absolute path is given. An alternative here would have been <mysql/mysql.h>, because the header file is in a subdirectory of the default directory.

Within the standard main function just shown, the connection to MySQL is established. The first line sets up the definition for MySQL, with mysql, which will be referenced by mysql later. The second line defines and names a results set based on the definitions for MYSQL_RES in mysql.h. The results are to be stored in the result array, which will be an array of rows from MySQL. The third line of main uses the definition for MYSQL_ROW to establish the row variable, which will be used later to contain an array of columns from MySQL.

Having set up the header files and initial variables, set up an object in memory for interacting with the MySQL server using mysql_init( ):

if(mysql_init(mysql) =  = NULL)    {       fprintf(stderr, "Cannot initialize MySQL");       exit( );    }

The if statement here is testing whether a MySQL object can be initialized. If the initialization fails, a message is printed and the program ends. The mysql_init( ) function initializes the MySQL object using the MYSQL structure declared at the beginning of the main function called by convention, mysql. If C is successful in initializing the object, it will go on to attempt to establish a connection to MySQL:

   if(!mysql_real_connect(mysql, "localhost",       "user", "password", "db1", 0, NULL, 0))    {       fprintf(stderr, "%d: %s \n",          mysql_errno(mysql), mysql_error(mysql));       exit(EXIT FAILURE);    }

The elements of the mysql_real_connect( ) function here are fairly obvious: first the MySQL object is referenced; next the hostname or IP address; then the username and password; and finally the database to use. The three remaining items are the port number, the Unix socket filename, and a client flag, if any. Passing zeros and NULL tells the function to use the defaults for these. If the program cannot connect, it is to print the error message generated by the server to the standard error stream, along with the MySQL error number (hence the %d format instruction for displaying digits or a number) and finally a string (%s) containing the MySQL error message and then a line feed or a newline (\n). The actual values to plug into the format follow, separated by commas.

The program so far only makes a connection to MySQL. Now let's look at how you can add code to the program to run an SQL statement with the C API.

14.1.2. Querying MySQL

If the MySQL connection portion of the program is successful, the program can query the MySQL server with a query function such as mysql_query( ):

   if(mysql_query(mysql, "SELECT col1, col2 FROM table1"))    {       fprintf(stderr, "%d:  %s\n",       mysql_errno(mysql), mysql_error(mysql));    }    else    {       result = mysql_store_result(mysql);       while(row = mysql_fetch_row(result))         { printf("\%d - \%s \n", row[0], row[1]]); }       mysql_free_result(result);    }       mysql_close(mysql);       exit(EXIT SUCCESS);    }

Incidentally, this excerpt is using mysql_query( ), but you could use the mysql_real_query( ) function instead. The main difference between the two is that mysql_real_query() allows the retrieval of binary data, which may not be necessary, but it's safer to use. mysql_query( ) returns 0 if it's not successful, so if the preceding SQL statement does not succeed in selecting data from MySQL, an error message will be printed. However, if the query is successful, it will return a 0 and the else statement to be executed. In the else statement block, the first line captures the results of the query and stores them in memory with the use of the mysql_store_result( ) function. Later, the memory will be freed when mysql_free_result( ) is issued with the variable name result in the parentheses.

Before letting go of the data, though, you must loop through each row of it and display results from each row for the user. Do this with a while statement and the mysql_fetch_row() function. This function retrieves one row of the results at a time and in this particular program, it stores each row in the row variable. Then the printf statement prints to the screen the value of each field in the format shown. Notice that each field is extracted by typical array syntax (i.e., array [n]). The formatting instructions for printf are in double quotes, the same method as with fprintf in the if statement earlier. Once C has gone through each row of the results, it will stop and then free up the buffer of the data, concluding the else statement. This brief program ends with a mysql_close( ) call to finish the MySQL session and to disconnect from MySQL. The final closing curly brace ends the main function.

To compile the program with the GNU C Compiler (gcc), you can enter something such as the following from the command line:

gcc -o mysql_c_prog mysql_c_prog.c \    -L/usr/include/mysql -L/usr/lib/mysql -lmysqlclient

Notice that the paths to the MySQL header file and the MySQL data directory are given as well, and the name of the client library, mysqlclient, is also given. These paths may be different on your system. When the compiler attempts to compile the program (mysql_c_prog.c here), it will check for syntax errors in the code. If it finds any, it will fail to compile and will display error messages. If its successful, the resulting compiled program (mysql_c_prog) may be executed.