MySQLs C API

only for RuBoard - do not distribute or recompile

This section outlines the creation of a very simple C application for accessing the small database created in the preceding whirlwind tour section.

Dont Try This at Work, Kids

The program in Listing 1.3 makes a very good, tight, small, simple program. It does not make good Software Engineering. There are many things wrong with it, such as the following:

Main() is entirely too long.
The SHOW statements could be abstracted to a single function, with the only parameter being the string defining it.
It does not contain enough comments.
The database it is hitting is unsecured.
The database name and host name are hardcoded in the routine.

And on and on. My point is, Listing 1.3 is an example for this section of this book. It is not representative of how good C code”and good code in general”is written.

Listing 1.3 A Simple C Program to Demonstrate MySQLs C Interface

 #include <stdio.h>  #include <mysql.h>  #define def_host_name   "localhost"  #define def_user_name   NULL  #define def_password    NULL  #define def_db_name     "test_db"  MYSQL *cnx_init;  MYSQL *cnx_db;  MYSQL_RES *result_set;  MYSQL_ROW row;  unsigned int ctr;  /* Function Prototypes */  void show_result_set (MYSQL_RES *in_result_set);  int main (int argc, char *argv[])  {       printf("starting\n");        cnx_init = mysql_init (NULL);        if (cnx_init == NULL)                        { printf("failure in mysql_init\n");                        printf("Exit code 1\n");                        exit(1);                      }          cnx_db = mysql_real_connect (cnx_init,                              def_host_name,                              def_user_name,                              def_password,                              def_db_name,                              0,                              NULL,                              0) ;      if (cnx_db == NULL) {                    printf("failure in mysql_real_connect\n");                     printf("Exit code 2\n");                     printf("Error %u -- %s\n",                       mysql_errno (cnx_init),                       mysql_error (cnx_init));                      exit(2);      }       printf("Databases\n");       printf("=========\n");       if (mysql_query (cnx_init, "SHOW DATABASES") != 0)               printf("Failure in show databases\n");         else            {           result_set = mysql_store_result (cnx_init);            if (result_set == NULL)               printf("failure in mysql_store_result                           for SHOW DATABASES\n");            else                 {                    show_result_set (result_set);                 }         }       printf("Tables\n");       printf("======\n");       if (mysql_query (cnx_init, "SHOW TABLES") != 0)                     printf("Failure in show tables\n");         else            {           result_set = mysql_store_result (cnx_init);            if (result_set == NULL)                       printf("failure in mysql_store_result                                    for SHOW TABLES\n");           else                {                           show_result_set (result_set);                }        }      printf("Rows\n");      printf("====\n");      if (mysql_query(cnx_init, "Select * from tbl_books_1") != 0)  printf("Failure in show tables\n");        else           {          result_set = mysql_store_result (cnx_init);           if (result_set == NULL)              printf("failure in mysql_store_result                              for Select statement\n");           else                {                           show_result_set (result_set);                }        }        printf("Action Query\n");        printf("============\n");        if (mysql_query (cnx_init, "Update tbl_books_1                                        set year = 1940                                        where name='Ayn Rand'") != 0)                    {  printf ("failure in mysql_query,                                    Update statement.\n");                       printf ("Exit code 4\n");                       printf("Error %u -- %s\n",                                mysql_errno (cnx_init),                                mysql_error (cnx_init));                       exit (4);                    }        else        {printf ("statement succeeded: %lu row(s) affected\n",              (unsigned long) mysql_affected_rows(cnx_init));        }        mysql_close(cnx_init);        printf("terminating\n");        exit(0);  }    void show_result_set (MYSQL_RES *in_result_set)  {      while ((row = mysql_fetch_row (in_result_set)) != NULL)        {for (ctr=0;ctr < mysql_num_fields (in_result_set); ctr++)              {if (ctr > 0)                 fputc ('\t', stdout);                 printf ("%s",row[ctr]!=NULL?row[ctr]:"Null-val");              }              fputc ('\n', stdout);        }        if (mysql_errno (cnx_init) != 0)        {  printf ("failure in mysql_fetch_row\n");           printf ("Exit code 3\n");           printf("Error %u -- %s\n",                    mysql_errno (cnx_init),                    mysql_error (cnx_init));           exit (3);        }        mysql_free_result (in_result_set);  }

Compiling and Linking

In Linux, the best compiler to use is gcc. It ships with virtually every distribution, it is an excellent product, and nearly every system has one.

In this case, the compile and link task consists of two steps: first compile, and then link.

I suggest you put them in a file, such as chap1.build. Then go to the command line and send this command:

 % ./chap1.build

The contents of chap1.build would then be:

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

The first line compiles the source code (in this case, ch1.c ), and the third line does the linking. The “I in the first line is for the include files”in this case, the location of mysql.h , because it is not in the default search path . In the second line, the “L specifies the path or paths to link the library files”in this case, searching for the file libmysql client.a . Notice that for the preceding syntax, the leading lib and the trailing .a are not needed; the -l flag automatically assumes them. The small l tells which library to link, and “lz indicates to link zlib also. The backslash character ( \ ) is the command-line continuation character.

Note:

The “lz option is necessary if you get a message similar to the following:

 [root@delfin client1]# make  gcc -o client1 client1.o -L/usr/lib/mysql -lmysqlclient -lm  /usr/lib/mysql/libmysqlclient.a(my_compress.o): In function 'my_uncompress':  my_compress.o(.text+0x97): undefined reference to 'uncompress'  /usr/lib/mysql/libmysqlclient.a(my_compress.o): In function 'my_compress_alloc':  my_compress.o(.text+0x12b): undefined reference to 'compress'  collect2: ld returned 1 exit status  make: *** [client1] Error 1

The compress and uncompress functions are in zlib. Also note that you might get an error message stating something about the floor() function. If that happens, you need to link in the math library using “lm . If you are on Solaris, you might need other libraries as well. The mysql mailing list, mailing list archive, and Web site are a great source of information.

Makefiles

This section offers a quick introduction to makefiles as they are used in Linux C application development. Makefiles do what the second line of the gcc file script (just shown) does, except better.

A makefile is a set of commands similar to those you just saw, except that it is more customizable and rebuilds the application by recompiling only those files that have changed since the last compile. Quite simply, although a makefile is not necessary, it is better software engineering.

The following code is the makefile for the small program in the previous code:

 CC = gcc  INCLUDES = -I/usr/include/mysql  LIBS = -L/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -lz  PROG = ch1  all:: $(PROG)  .c.o:  $(CC) -c $(INCLUDES) $<  $(PROG): $(PROG).o  $(CC) -o $@ $(PROG).o $(LIBS)  clean::  rm -f $(PROG).o $(PROG)

This file should be put into a file called Makefile.in (this is one of the filenames make looks for by default). To execute the makefile, type make at the command line. Then cd to the directory where Makefile.in is located, and issue this command:

 %make

By default, make finds a file called Makefile . make is not an easy program to use, but for complicated programs that take a long time to compile, it might come in handy.

By way of quick explanation, the first four lines define variables (for lack of a better term ). You can see them used throughout the file. After the space, the next three lines compile the source and then the object files. Finally, the program removes ( rm ) the intermediate files.

only for RuBoard - do not distribute or recompile