MySQL Chapter Four (C API)

Documentation Version: 0.95
MySQL Version: 3.20.29

Overview

This chapter documents the C Application Programming Interface (API) supplied with the MySQL database system. The API supports a rich assortment of functions that allow complete access to the MySQL database engine from a client program, regardless of whether it is running locally or on a remote system.

Preparation

You will need to include the mysql.h header file at the top of your C program:

You will also need to link in the math (for encryption) and mysqlclient libraries:

The include files are typically located in /usr/include/mysql, and the client libraries can be found in /usr/lib/mysql.

If you have a memory leak in your client you can compile with the --with-debug=yes option. This will cause the client code to use the 'safe_malloc' package in the MySQL client library. You would call TERMINATE(stdout) or my_end(1) in your client application before exiting to get a list of all memory leaks. Check the file mysys/safemalloc.c in the source distribution for further details.

Following is a sample MySQL client program that would simply perform a SELECT and then display all returned rows on standard output. While not all API functions are included, it should give you an idea of the typical client program layout. 

#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"

MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;

void exiterr(int exitcode)
{
    fprintf( stderr, "%s\n", mysql_error(&mysql) );
    exit( exitcode );
}

int main()
{
    uint i = 0;

    if (!(mysql_connect(&mysql,"host","username","password"))) 
        exiterr(1);

    if (mysql_select_db(&mysql,"payroll"))
        exiterr(2);

    if (mysql_query(&mysql,"SELECT name,rate FROM emp_master"))
        exiterr(3);

    if (!(res = mysql_store_result(&mysql)))
        exiterr(4);

    while((row = mysql_fetch_row(res))) {
        for (i=0 ; i < mysql_num_fields(res); i++) 
            printf("%s\n",row[i]);
    }

    if (!mysql_eof(res))
        exiterr(5);

    mysql_free_result(res);
    mysql_close(&mysql);
}


Client Functions

The MySQL API uses a MYSQL data structure (defined in mysql.h) to establish a connection with the database engine. You may set up multiple connections from a single client program, however, each connection must be assigned to its own separate MYSQL structure.

After a successful query, if data is to be returned to the client, the result set must be transferred via either the mysql_store_result or mysql_use_result functions. Both of these functions store the result set in a MYSQL_RES structure. The difference is that mysql_store_result reads the entire result set into memory on the client, where mysql_use_result instructs the client to retrieve a row dynamically from the server with each call to mysql_fetch_row. Keep in mind, however, that mysql_use_result ties up server resources and thus should not be used for interactive applications where user actions are often unpredictable and could result in extended delays. Note also that you may have only one connection open that uses mysql_user_result, and it must be the most recently created one. An additional consideration is the fact that by default the mysqld process will close the connection after thirty seconds of idle time.

Data retrieved from the result set with mysql_fetch_row is placed into a MYSQL_ROW structure, which is simply an array of pointers to the beginning of each field.

mysql_affected_rows

SYNOPSIS:

DESCRIPTION:

RETURN VALUE:

EXAMPLE:

MySQL is optimized for the 'delete all records in a table' case. A side effect of this optimization is that MySQL will return zero for the number of rows affected in this situation. Doing a 'select count(*) from the_table' before deleting all records will give you a value for the number of rows affected, though this value may change between the SELECT and and DELETE since MySQL 3.20.X does not support table locking. This is fixed in version 3.21.X

mysql_close

SYNOPSIS:

DESCRIPTION:

RETURN VALUE:

mysql_connect

SYNOPSIS:

DESCRIPTION:

RETURN VALUE:

EXAMPLE:

mysql_create_db

SYNOPSIS:

DESCRIPTION:

RETURN VALUE:

mysql_data_seek

SYNOPSIS:

DESCRIPTION:

RETURN VALUE:

mysql_drop_db

SYNOPSIS:

DESCRIPTION:

RETURN VALUE:

mysql_eof