The C Language API is the native programming interface for MySQL. In fact, virtually all the other APIs that are available use the C API. For example, the PHP interface, which you will learn about in Lesson 24, "Using MySQL with PHP," is written in C using this API. It contains many functions that map directly to a corresponding function in the underlying C library.
Using the C API
The client library libmysqlclient.so contains the functions used to communicate with MySQL from a C program, and it is installed automatically if you compile MySQL from source. Your program must be linked using this library at compile time. Using the GCC compiler, use the following command to compile myprog.c:
$ gcc o myprog myprog.c -lmysqlclient
Your programs must include the header file mysql.h, which is also installed automatically when building from source. It installs to a subdirectory off your include pathusually /usr/include/mysql/mysql.hso your program must contain this statement at the beginning.
Finding the Compiler Options
The mysql_config utility shipped with MySQL enables you to find the correct compiler options to use when compiling a client program. Three possible switches can be passed to this utility to return the compiler flags, including the path and library options, as shown in the following examples. The responses shown might differ on your system.
$ mysql_config --cflags -I/usr/include/mysql -mcpu=i486 -fno-strength-reduce $ mysql_config --include -I/usr/include/mysql $ mysql_config --libs -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv
A failsafe way to pass all the correct compiler options to GCC is to use a shell escape, like this:
$ gcc o myprog myprog.c 'mysql_config --cflags --include -- libs'
Connecting to MySQL
The first MySQL API function you need to learn is how to connect to a MySQL database. This is actually done in two stages.
Firstly, you must initialize a new instance of a data structure that will become your connection reference for all future function calls using that database. The object must be declared as type MYSQLnote the unusual capitalization hereand you initialize it for use by the API by passing it to the mysql_init() function.
Next, you call the mysql_real_connect() function to initiate a connection to a MySQL database. Look at the example in Listing 23.1 first; we'll look at each value that can be passed as an argument in turn.
Listing 23.1. Establishes a Connection to a MySQL Server
Of course, this program does not really do anything beyond establishing a connection to the database and giving an error if the connection failed. For now, let this just serve as an example of how to create a database connection using the C API.
The return value from mysql_real_connect() is a MYSQL type objectin fact, the same value as the first argumentif the connection is successful, or NULL if it failed for any reason. We examine some more detailed error-reporting functions later in this chapter.
The mysql_real_connect() function takes eight arguments, with the function prototype shown here:
mysql_real_connect(&mysql, "host", "user", "password", "dbname", port, unix_socket, client_flag);
These are the arguments:
Executing a Query
After you have established a connection to a MySQL server, you can issue a query to the database using the mysql_query() function. The first argument to this function is a connection reference. The second should be a null-terminated string that contains a single SQL query.
If the query you want to execute contains binary data, use mysql_real_query() instead. This function takes three arguments, with the third being the length of the query string. Using this function, any instances of the \0 character in your data are not treated as the null terminator in the string.
The return value from mysql_query() or mysql_real_query() is NULL to indicate success. If MySQL encounters an error, the function returns one of the values shown in Table 23.1.
If the return value is not NULL, you can call the mysql_errno() and mysql_error() messages to display the MySQL error number and message text, respectively. They each take a single argument, which is the connection handle.
Using the Result of a Query
If the query you pass to mysql_query() or mysql_real_query() is a SELECT statement, it returns data that you want to fetch into your program. The API functions that work with fetched data require a result resource argument, which is a MYSQL_RES type of object.
To create a result handle, call the mysql_use_result() function. It takes a single argumenta database connection handleand returns a result object that corresponds to the most recently executed query on that connection.
The return value from mysql_use_result() is either NULL to indicate success or one of the error values from Table 23.1. When a result handle has been successfully assigned, you can continue to execute other queries using the same database connection.
The most powerful way to fetch data from a result handle is with the mysql_fetch_row() function. The first time you call mysql_fetch_row(), the first row from the data set is returned and each subsequent call returns the next row until no data remains to be fetched.
The data is returned into a MYSQL_ROW type structure, which contains one element for each column returned by the query. The elements are numbered from zero, in the order they appear in the SELECT statement.
For example, suppose that your program executes the query SELECT code, price FROM products ORDER BY price and then uses mysql_fetch_row() to return the first row into a structure named result. The value contained in result will be the code for the first product returnedwith this query, the least expensive product in the tableand result will hold its price value.
The mysql_num_fields() function tells you the number of columns the query returns. Using this in a loop, you can fetch a value from each column. The example in Listing 23.2 uses this technique, along with the other functions you have learned so far in this chapter, to produce a complete query example using the C API.
Listing 23.2. Performing a Query and Displaying the Results
The output of this program is a tabulated list of columns, as follows:
MUSGRP Musicians of America PRESINC Presidents Incorporated SCICORP Science Corporation
Using Other SQL Commands
You can issue other SQL commands than SELECT using mysql_query() or mysql_real_query(), but they will not return a result. If you have the appropriate permissions, you can issue UPDATE, INSERT, and DELETE statements on the database, and you could even perform a Data Definition Language statementthe most common use for this is a CREATE TEMPORARY TABLE statement in your application.
The function mysql_affected_rows() can be used to determine the effect of an INSERT, UPDATE, or DELETE statement. It returns the number of rows affected by the SQL statement.
For INSERT and DELETE statements, this is a straight count of the number of rows added or removed by the operation. When the statement performed is an UPDATE, the return value from mysql_affected_rows() is the number of rows in which a value was changed from its previous value; this could be less than the total number of rows matched in the WHERE clause.
To keep your program efficient, make sure you free up any resources that are no longer required.
Use mysql_free_result() to deallocate the resources associated with a result handle. After you have freed the result resource, you should not attempt to access it again in the program. This function takes a single argument, which is a MYSQL_RES type of object.
When you are done with the database connection altogether, use mysql_close() with a database handle as its argument to close the connection and free up the resource.