C API (Managing and Using MySQL, 2nd Edition)

12.1. API Overview

The following list shows the function calls of the MySQL C API. Chapter 19 lists each of these methods with detailed prototype information, return values, and descriptions.

mysql_affected_rows( )
mysql_close( )
mysql_connect( )
mysql_create_db( )
mysql_data_seek( )
mysql_drop_db( )
mysql_eof( )
mysql_error( )
mysql_fetch_field( )
mysql_fetch_lengths( )
mysql_fetch_row( )
mysql_field_count( )
mysql_field_seek( )
mysql_free_result( )
mysql_get_client_info( )
mysql_get_host_info( )
mysql_get_proto_into( )
mysql_get_server_info( )
mysql_init( )
mysql_insert_id( )
mysql_list_dbs( )
mysql_list_fields( )
mysql_list_processes( )
mysql_list_tables( )
mysql_num_fields( )
mysql_num_rows( )
mysql_query( )
mysql_real_query( )
mysql_reload( )
mysql_select_db( )
mysql_shutdown( )
mysql_stat( )
mysql_store_result( )
mysql_use_result( )

You may notice that many of the function names do not seem directly related to accessing database data. In many cases, MySQL actually only provides an API interface into database administration functions. By reading the function names, you might have gathered that any database application you write might look something like this:

Connect
Select DB
Query
Fetch row
Fetch field
Close

Example 12-1 shows a simple select statement that retrieves data from a MySQL database using the MySQL C API.

Example 12-1. A program that selects all data in a test database and displays the data

#include <stdio.h>
#include <mysql.h>
  
int main(char **args) {
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL *connection, mysql;
int state;
  
/* connect to the MySQL database at localhost */
mysql_init(&mysql);
connection = mysql_real_connect(&mysql,"localhost", "orausr", "orapw", 
                                    "oradb", 0, 0, 0);
/* check for a connection error */
if (connection == NULL) {
/* print the error message */
printf(mysql_error(&mysql));
return 1;
}
state = mysql_query(connection,
"SELECT test_id, test_val FROM test");
if (state != 0) {
printf(mysql_error(connection));
return 1;
}
/* must call mysql_store_result( ) before you can issue 
      any other query calls */
    result = mysql_store_result(connection);
    printf("Rows: %d\n", mysql_num_rows(result));
    /* process each row in the result set */
    while ( ( row = mysql_fetch_row(result)) != NULL ) {
        printf("id: %s, val: %s\n",
            (row[0] ? row[0] : "NULL"),
            (row[1] ? row[1] : "NULL"));
 }
 /* free the result set */
 mysql_free_result(result);
 /* close the connection */
 mysql_close(connection);
 printf("Done.\n");
}

Of the included header files, both mysql.h and stdio.h should be obvious to you. The mysql.h header contains the prototypes and variables required for MySQL, and stdio.h the prototype for printf( ). On FreeBSD, you can compile this program for MySQL 3.23 with the GNU C compiler using the command line:

gcc -L/usr/local/lib/mysql -I/usr/local/include/mysql -o select select.c\
      -lmysqlclient

On Mac OS X, compiling MySQL 4.0 requires linking to zlib:

cc -L/usr/local/lib/mysql -I/usr/local/include/mysql -o select select.c\
     -lmysqlclient -lz

You should of course substitute the directory where you have MySQL installed for /usr/local/lib/mysql and /usr/local/include/mysql in the preceding code.

The main( ) function follows the steps we outlined earlier: it connects to the server, selects a database, issues a query, processes the result sets, and cleans up the resources it used. We will cover each of these steps in detail as the chapter progresses. For now, read the code and get a feel for what it does.

12.1.1. The Connection

An application should call mysql_init( ) before performing any other operation. This method initializes a database handler used by many of the functions—including the connection and error handling functions. In the above example, we created a handler in the declaration:

MYSQL *connection, mysql;

The pointer to the handler, connection, will represent our actual connection once it is made; the allocated handler, mysql, represents a null connection until we actually make the database connection. Our first step is to initialize this handler through the mysql_init( ) function:

mysql_init(&mysql);

This function takes a reference to an allocated null handler. The MySQL API requires this hocus-pocus with a null handler to support operations such as error handling that occur outside the context of a physical database connection. The first function needing this handler is the actual connection API: mysql_real_connect( ).

TIP: At first glance over the API list, you may be tempted to use the mysql_connect( ) function. The odd name of the mysql_real_connect( ) function exists because it is a replacement for the long-deprecated mysql_connect( ) function. The old mysql_connect( ) provided compatibility for mSQL applications; you should never use it in modern applications.

The mysql_real_connect( ) function takes several arguments:

null handler: The connection handler allocated and subsequently initialized through mysql_init( ).
host: The name of the machine on which the MySQL server is running.
user: The user ID of the MySQL user to connect under.
password: The password that identifies the user you are connecting under.
database: The name of the database on the MySQL server to connect to.
port: The port number MySQL is listening to. If you specify 0, it will connect to MySQL on MySQL's default port number.
unix socket: A pointer to the Unix socket or null. Under Windows, you should be certain to pass in NULL and not a null string—i.e., use (char *)NULL and not (char *)"".
client flag: A number including a set of flags for the connection. You will generally pass in 0 here.

Upon success, the mysql_real_connect( ) function returns a pointer to an actual MySQL connection. To verify success, your application should check for a null value:

if( connection == NULL ) {
    /* An error! */
}

If you run into an error during a connection, it becomes clear why you needed that null handler we created in mysql_init( ). It provides you with access to the error:

printf("%s\n", mysql_error(&mysql));

We will go into more detail on error handling later in the chapter.

12.1.2. Queries and Results

Now that you have a physical connection to the database, you can interact with MySQL. The above example used the mysql_query( ) function to get all of the rows from the test table in our sample database:

state = mysql_query(connection, "SELECT test_id, test_val FROM test");

This function returns nonzero on error. Once you send a query, you should therefore check the return code to make sure the query executed properly.

if(state != 0 ) {
    /* Error! */
}

If the return code is 0, you can access any results through the mysql_store_result( ) function:

result = mysql_store_result(connection);

This function returns a pointer to the result set generated by the previous query executed against the specified connection. If that query did not generate results, or if you encounter an error getting to the results, this function returns null. The earlier example does not look for these states—we will go into them in more detail when we cover error handling later in the chapter.

The results given to you by the mysql_store_result( ) function are now under your control. They will exist in memory until you explicitly free them through the mysql_free_result( ) function. In this case, you should step through each row of the results and print the row's values:

while( (row = mysql_fetch_row(result)) != NULL ) {
    printf("id: %s, val: %s\n", (row[0] ? row[0] : "NULL"),
           (row[1] ? row[1] : "NULL"));
}

Even though the test_id column in our database is a numeric column, we still treat it as a null-terminated string in the results. We have to do this since the MYSQL_RES typedef is, in fact, nothing more than an array of null-terminated strings—regardless of their underlying MySQL type. We will perform some more complex result set handling that includes binary data later in this chapter.

Once you are done with the results, you must tell MySQL to free the memory they use:

mysql_free_result(result);

12.1.3. Closing the Connection

The final step of any database application is to free the database resources it uses. In MySQL, you free your database resources through the mysql_close( ) function:

mysql_close(connection);

If you attempt to use that connection at any point after closing it, you will encounter an error.

Chapter 12. C API

Contents:

12.1. API Overview

Example 12-1. A program that selects all data in a test database and displays the data

12.1.1. The Connection

12.1.2. Queries and Results

12.1.3. Closing the Connection