mysql Chapter Four (C API)

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:

#include "mysql.h"

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

$ cc -I/usr/include/mysql -L/usr/lib/mysql myapp.c -o myapp -lm -lmysqlclient

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:

int mysql_affected_rows(MYSQL *mysql)

DESCRIPTION:

Retrieves the number of rows affected by the last UPDATE, DELETE or INSERT.

RETURN VALUE:

An integer >0 indicating the number of changed/retrieved rows.
Zero if no records matched the WHERE clause in an UPDATE or DELETE.
-1 if the query returned an error, for example and attempt was made to add a duplicate primary key during an INSERT.

EXAMPLE:

mysql_query( &mysql, "INSERT INTO gl_transact (acctnbr, amount)
VALUES (12345, 651.30)" );

if ( mysql_affected_rows( &mysql ) < 0 )
   fprintf( stderr, "Attempted to add duplicate primary key\n" );

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:

void mysql_close(MYSQL *mysql);

DESCRIPTION:

Closes a previously opened connection.

mysql_close must be called after completing all operations performed via the MySQL connection. If not done, the thread created by mysql_connect will linger until the built-in timeout is reached. On a busy server this can quickly eat a lot of memory, although it should take very little CPU time.

If a connection is closed before a running query has completed, the query will continue until it attempts to return any part of the result set to the client. It will then die upon noticing that the connection is no longer active.

The default timeout is 30 seconds for an active query and 8 hours for an open connection.

RETURN VALUE:

None.

mysql_connect

SYNOPSIS:

MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)

DESCRIPTION:

Attempts to establish a connection to a MySQL database engine running on host. The value of host may be either a hostname or an IP address. The user parameter contains the user's MySQL login ID, and the passwd parameter contains the password for user. NOTE: Do not attempt to encrypt passwd before calling mysql_connect. Encryption is handled automatically by the client API.

If host is NULL, 'localhost' is assumed.
if user is NULL, 'current user' is assumed. Under Windows ODBC, the current user must be explicitly specified. Under Unix the current login ID is assumed.
If password is NULL then only records in the user table without a password entry will be checked for a match. This allows the db-admin to setup the MySQL privilege system in such a way that a user gets different privileges depending on whether they have specified a password or not.

mysql_connect must complete successfully before any action is allowed to be performed on a database.

You may optionally specify the first argument of mysql_connect to be (MYSQL*) 0 This will force the C API to automatically alloc memory for the connection structure and free it on close. The downside of this approach is that you can't retrieve an error message from mysql_connect when you use this option.

RETURN VALUE:

mysql if the connection was successful.
NULL if unsuccessful.

EXAMPLE:

MYSQL *mysql;

if((mysql=malloc(sizeof(MYSQL)))!=NULL)
{
if (!(mysql_connect(mysql,"pr_server","jqpublic","mypasswd")))
{
free( mysql );
exit( 1 );
}
}
free( mysql );

mysql_create_db

SYNOPSIS:

int mysql_create_db(MYSQL *mysql, const char *db);

DESCRIPTION:

Creates the database named in db on the machine pointed to by mysql. The MySQL connection must have been made with a user ID that has create privileges. (Refer to chapter 5 for more details on user privileges.)

RETURN VALUE:

Zero if the database was successfully created.
Non-zero if an error occurred. The error message may be retrieved by calling mysql_error.

mysql_data_seek

SYNOPSIS:

void mysql_data_seek(MYSQL_RES *res, uint offset);

DESCRIPTION:

Seeks to an arbitrary row in a query result set. May not be used in conjunction with mysql_use_result

RETURN VALUE:

None

mysql_drop_db

SYNOPSIS:

int mysql_drop_db(MYSQL *mysql, const char *db);

DESCRIPTION:

Drop the database named in db from the machine pointed to by mysql. The connection must have been made with a user ID that has drop privileges for the specified database. (Refer to chapter 6 for more details on user privileges.)

RETURN VALUE:

Zero if the database was successfully dropped.
Non-zero if an error occurred. The error message may be retrieved by calling mysql_error.

mysql_eof

SYNOPSIS:

int mysql_eof(MYSQL_RES *)

DESCRIPTION:

mysql_fetch_row

mysql_error

SYNOPSIS:

char *mysql_error(MYSQL *mysql)

DESCRIPTION:

mysql

An empty string will be returned if no error occurred.

mysql_fetch_field

SYNOPSIS:

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *handle);

DESCRIPTION:

Find out what type a table field is.

mysql_fetch_lengths

SYNOPSIS:

unsigned int *mysql_fetch_lengths(MYSQL_RES *mysql)

DESCRIPTION:

Returns the length of all columns in a query result set. If you plan on retrieving data that contains a \0 you must use this function to get the actual length of the field value.

mysql_fetch_row

SYNOPSIS:

MYSQL_ROW mysql_fetch_row(MYSQL_RES *mysql);

DESCRIPTION:

Fetch the 'next' row in the query result. Will return a NULL pointer when all rows have been retrieved.

mysql_field_seek

SYNOPSIS:

void mysql_field_seek(MYSQL_RES *result, int field)

DESCRIPTION:

field

mysql_num_fields(MYSQL_RES*)-1

mysql_free_result

SYNOPSIS:

void mysql_free_result(MYSQL_RES *result);

DESCRIPTION:

Free memory used to store a query result. Should be called whenever you have finished using the results of a mysql_store_result() call

mysql_get_client_info

SYNOPSIS:

char *mysql_get_client_info(void);

DESCRIPTION:

This function simply returns a string containing version information for the client library currently in use.

mysql_get_host_info

SYNOPSIS:

char *mysql_get_host_info(MYSQL *mysql);

DESCRIPTION:

Returns name of host (same as the host argument to mysql_connect).

mysql_get_proto_info

SYNOPSIS:

int mysql_get_proto_info(MYSQL *mysql);

DESCRIPTION:

Get protocol version used by connection. MySQL implements dynamic protocols based on client capabilities. In version 3.20.X this doesn't really do anything, but in future versions it will for example allow one client to connect using the current protocol, while another connects using encryption and compression.

mysql_get_server_info

SYNOPSIS:

char *mysql_get_server_info(MYSQL *mysql);

DESCRIPTION:

Returns the version number of the server.

mysql_insert_id

SYNOPSIS:

int mysql_insert_id(MYSQL *mysql)

DESCRIPTION:

Returns ID generated for AUTO_INCREMENT field on result variable 'res'.

mysql_list_dbs

SYNOPSIS:

MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild);

DESCRIPTION:

Provided to ease porting of mSQL applications.

Similar to doing 'SHOW databases [ LIKE wild-card ]' as a query.

mysql_list_fields

SYNOPSIS:

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild);

DESCRIPTION:

Provided to ease porting of mSQL applications.

Similar to doing 'SHOW fields [FROM table] [FROM database] [LIKE wild-card]' in a query.

mysql_list_processes

SYNOPSIS:

MYSQL_RES *mysql_list_processes(MYSQL *mysql);

DESCRIPTION:

Get a list of the thread currently running on the MySQL database engine. You must have process privileges.

mysql_list_tables

SYNOPSIS:

MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild);

DESCRIPTION:

Provided to ease porting of mSQL applications.

Similar to doing 'SHOW tables [FROM database]' as a query.

mysql_num_fields

SYNOPSIS:

int mysql_num_fields(MYSQL_RES *result);

DESCRIPTION:

This macro returns the number of columns (fields) in a query result.

EXAMPLE:

MYSQL mysql;
MYSQL_RES *result;
int fields;

if ( mysql_query( &mysql, "SELECT * FROM emp_master" ) == 0 )
{
	result = mysql_store_result( &mysql );
	if ( result != NULL )
	{
		fields = mysql_num_fields( result );
		printf( "Retrieved %u fields\n", fields );
	}
	else
		printf( "Query failed\n" );
} 
else
	abort();

mysql_num_rows

SYNOPSIS:

int mysql_num_rows(MYSQL_RES *result);

DESCRIPTION:

This macro returns the number of rows returned by the last mysql_list_dbs, mysql_list_fields, mysql_list_processes, mysql_list_tables or mysql_store_result.

EXAMPLE:

MYSQL mysql;
MYSQL_RES *result;
int rows;

if ( mysql_query( &mysql, "SELECT * FROM emp_master" ) == 0 )
{
	result = mysql_store_result( &mysql );
	if ( result != NULL )
	{
		rows = mysql_num_rows( result );
		printf( "Retrieved %u rows\n", rows );
	}
	else
		printf( "Query failed\n" );
} 
else
	abort();

mysql_query

SYNOPSIS:

int mysql_query(MYSQL *mysql, const char *query);

DESCRIPTION:

Executes the SQL query pointed to by query on the database pointed to by mysql. This function will return zero if the query executed properly. A non-zero result indicates an error. A call to mysql_error will retrieve the error message.

Calling mysql_num_rows will give you the number of rows returned by the query.

If you have an AUTO_INCREMENT field in the table being updated and you are doing an INSERT, you can find the newly assigned field value by checking mysql_insert_id.

mysql_real_query

SYNOPSIS:

int mysql_real_query(MYSQL *mysql, const char *query, uint length);

DESCRIPTION:

This function is called by mysql_query after it does a strlen function call to calculate the length of the query string. It could be used if your program allocates a fixed buffer for the query string.

You'll have to use this function if you have data with un-escaped \0 values.

mysql_reload

SYNOPSIS:

int mysql_reload(MYSQL *mysql);

DESCRIPTION:

Have the MySQL database engine reload the user permissions table, flush all caches and close all open tables not in use. This should be done before running isamchk on any table.

Requires user to have reload privileges.

mysql_select_db

SYNOPSIS:

int mysql_select_db(MYSQL *mysql, const char *db);

DESCRIPTION:

Attempt to connect to the database pointed to by db, on the machine pointed to by mysql. The MySQL database engine on the server will use the login and password contained in mysql to authenticate the connection.

A successful call to mysql_connect is necessary before mysql_select_db can be used.

In general mysql_select_db must be called successfully before attempting to query a database. The exceptions are queries such as the following.

SHOW DATABASES like 'A%';

SELECT 1+1; # SELECT without using tables.

mysql_shutdown

SYNOPSIS:

int mysql_shutdown(MYSQL *mysql);

DESCRIPTION:

Shut down a MySQL database engine. User must have shutdown privileges.

mysql_stat

SYNOPSIS:

char *mysql_stat(MYSQL *mysql);

DESCRIPTION:

Returns the info similar to 'mysqladmin version' as a character string. This includes uptime in seconds, running threads, questions, reloads and open tables information. This is essentially the same as the mysqladmin programs stat option.

mysql_store_result

SYNOPSIS:

MYSQL_RES *mysql_store_result(MYSQL *mysql);

DESCRIPTION:

Reads the result to the client. You must use this or mysql_use_result() to get the result from the server. You must always use mysql_store_result() or mysql_use_result() after you have executed a successful query.

mysql_store_result() returns NULL on error or if the statement didn't return any data. You can check for errors with:

if (!(result=mysql_store_result(&mysql))
 && mysql_num_fields(&mysql))
  fputs(mysql_error(&mysql),stderr);

A call to mysql_free_result() must be made when you're done to free memory.

mysql_use_result

SYNOPSIS:

MYSQL_RES *mysql_use_result(MYSQL *mysql);

DESCRIPTION:

The same as mysql_store_result(), except that the result is fetched dynamically from the server for each 'mysql_fetch_row()'. This should not be used on interactive applications since it ties up the server. This helps to hold down the memory usage on the client side.