The C Language API

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.

Client Programs

The client utilities shipped with MySQL, such as mysql and mysqladmin, are all written using the C API. You can examine the source code for these if you want to see how they work.

Using the C API

The client library 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.

 #include <mysql/mysql.h> 

Binary Installation

When installing MySQL from a binary package or RPM, the distribution is split into separate components for the server, the client, and development. The packages containing the shared libraries and header files have names that begin with MySQL-shared- and then contain the version number and platform name.

For example, the current version of the RPM package for Red Hat Enterprise is named MySQL-devel-standard-5.0.18-0.rhel3.i386.rpm.

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

 #include <stdio.h> #include <mysql/mysql.h> main() { MYSQL mysql; mysql_init(&mysql); if (!mysql_real_connect(&mysql, "localhost",                    "username", "password", "mysql10",                    0, NULL, 0)) {   fprintf(stderr, "Unable to connect to MySQL server\n"); } } 

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:

  1. mysqlThe MYSQL type object that you initialized previously with mysql_init().

  2. hostThe hostname of the MySQL server. This can be an IP address, domain name or hostname, or localhost for servers running on the same host as the client program.

  3. userThe username for database authentication.

  4. passwdThe password that corresponds to the username passed in argument 3.

  5. dbnameThe database name to connect to.

  6. portThe port number for TCP/IP connections. For localhost connections or to use the default port, use 0 here.

  7. unix_socketThe name of the socket or named pipe for local connections. For TCP/IP connections or to use the default socket, use NULL here.

  8. client_flagThis argument can be used to enable certain features of the client library that are not covered in this book. Use 0 here unless you have a specific need.

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.

Terminating Queries

The terminating semicolon is not required when you pass a query to mysql_query() or mysql_real_query(), but if you include it, it will not cause an error.

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.

Table 23.1. Error Codes Returned by mysql_query() or mysql_real_query()




Commands were executed in an improper order.


The MySQL server has gone away.


The connection to the server was lost during the query.


An unknown error occurred.

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[0] will be the code for the first product returnedwith this query, the least expensive product in the tableand result[1] 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.

Counting Rows Returned

Similar to mysql num fields(), you can use mysql_num_rows() to find the total number of rows a query returns.

Listing 23.2. Performing a Query and Displaying the Results

 #include <stdio.h> #include <mysql/mysql.h> main() { MYSQL mysql; MYSQL_RES *result; MYSQL_ROW row; int numrows, numcols, c; mysql_init(&mysql); /* Establish a database connection */ if (!mysql_real_connect(&mysql, "localhost",                    "username", "password", "mysql10",                    0, NULL, 0)) {   fprintf(stderr, "Error connecting: Error %d: %s\n",                      mysql_errno(&mysql), mysql_error(&mysql)); } /* Execute a query */ char query[] = "SELECT * FROM customers ORDER BY name"; if (mysql_query(&mysql, query)) {   fprintf(stderr, "Error executing query: Error %d: %s\n",                      mysql_errno(&mysql), mysql_error(&mysql)); } /* Assign the result handle */ result = mysql_use_result(&mysql); if (!result) {   fprintf(stderr, "Error using result: Error %d: %s\n",                      mysql_errno(&mysql), mysql_error(&mysql)); } /* Find the number of columns in the result */ numcols = mysql_num_fields(result); /* Loop through the result set to display it */ while (row = mysql_fetch_row(result)) {   for(c=0; c<numcols; c++) {     printf("%s\t", row[c]);   }   printf("\n"); } } 

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.

Using a Result

Attempting to call mysql_use_result() after executing a SQL statement that is not a SELECT is nonsense and produces an error.

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.

Tidying Up

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.

Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: