Table of Contents for

MySQL in a Nutshell, 2nd Edition

When writing a C program to interact with MySQL, you first need to prepare variables that will store data necessary for a MySQL connection and query results, and then you need to establish a connection to MySQL. To do this easily, you need to include a couple of C header files (as shown in the code example): 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 stdio.h is surrounded by < and > symbols, 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, the absolute path is given with the aid of double quotes. An alternative here would be <mysql/mysql.h> because the header file is in a subdirectory of the default directory.

Within the standard main function just shown, variables needed for the connection to MySQL are prepared. The first line creates a pointer to the MYSQL structure stored in the mysql variable. The next line defines and names a results set based on the definitions for MYSQL_RES in mysql.h. The results are 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 included the header files and set the initial variables, we can now 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");
      return 1;
   }

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 mysql by convention. 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));
      return 1;
   }

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.

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);
      return 0;
   }

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 is safer to use. mysql_query() returns zero if it’s successful and nonzero 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, the else statement will be executed because the if statement will have received a value of 0 from mysql_query(). 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 given.

Before letting go of the data, though, we must loop through each row of the results set and display the results from each row for the user. We’ll 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 example program, 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 enclosed within double quotes, the same method we used with the fprintf in the if statement earlier in this section. Once C has gone through each row of the results, it will stop processing 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 like the following from the command line:

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

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 (here, mysql_c_prog.c), it will check for syntax errors in the code. If it finds any, it will fail to compile and will display error messages. If it’s successful, the resulting compiled program (mysql_c_prog) may be executed.