9 MySQL APIs

This chapter describes the APIs available for MySQL, where to get them, and how to use them. The C API is the most extensively covered, as it was developed by the MySQL team, and is the basis for most of the other APIs.

9.1 MySQL C API

The C API code is distributed with MySQL. It is included in the mysqlclient library and allows C programs to access a database.

Many of the clients in the MySQL source distribution are written in C. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. You can find these in the clients directory in the MySQL source distribution.

Most of the other client APIs (all except Connector/J) use the mysqlclient library to communicate with the MySQL server. This means that, for example, you can take advantage of many of the same environment variables that are used by other client programs, because they are referenced from the library. See section 4.8 MySQL Client-Side Scripts and Utilities, for a list of these variables.

The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16K bytes) is automatically increased up to the maximum size (the maximum is 16M). Because buffer sizes are increased only as demand warrants, simply increasing the default maximum limit does not in itself cause more resources to be used. This size check is mostly a check for erroneous queries and communication packets.

The communication buffer must be large enough to contain a single SQL statement (for client-to-server traffic) and one row of returned data (for server-to-client traffic). Each thread's communication buffer is dynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOB values that contain up to 16M of data, you must have a communication buffer limit of at least 16M (in both server and client). The client's default maximum is 16M, but the default maximum in the server is 1M. You can increase this by changing the value of the max_allowed_packet parameter when the server is started. See section 5.5.2 Tuning Server Parameters.

The MySQL server shrinks each communication buffer to net_buffer_length bytes after each query. For clients, the size of the buffer associated with a connection is not decreased until the connection is closed, at which time client memory is reclaimed.

For programming with threads, see section 9.1.14 How to Make a Threaded Client. For creating a stand-alone application which includes the "server" and "client" in the same program (and does not communicate with an external MySQL server), see section 9.1.15 libmysqld, the Embedded MySQL Server Library.

9.1.1 C API Datatypes

MYSQL

This structure represents a handle to one database connection. It is used for almost all MySQL functions.

MYSQL_RES

This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from a query is called the result set in the remainder of this section.

MYSQL_ROW

This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling mysql_fetch_row().

MYSQL_FIELD

This structure contains information about a field, such as the field's name, type, and size. Its members are described in more detail here. You may obtain the MYSQL_FIELD structures for each field by calling mysql_fetch_field() repeatedly. Field values are not part of this structure; they are contained in a MYSQL_ROW structure.

MYSQL_FIELD_OFFSET

This is a type-safe representation of an offset into a MySQL field list. (Used by mysql_field_seek().) Offsets are field numbers within a row, beginning at zero.

my_ulonglong

The type used for the number of rows and for mysql_affected_rows(), mysql_num_rows(), and mysql_insert_id(). This type provides a range of 0 to 1.84e19. On some systems, attempting to print a value of type my_ulonglong will not work. To print such a value, convert it to unsigned long and use a %lu print format. Example:

printf ("Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));

The MYSQL_FIELD structure contains the members listed here:

char * name

The name of the field, as a null-terminated string.

char * table

The name of the table containing this field, if it isn't a calculated field. For calculated fields, the table value is an empty string.

char * def

The default value of this field, as a null-terminated string. This is set only if you use mysql_list_fields().

enum enum_field_types type

The type of the field. The type value may be one of the following:

Type value	Type description
FIELD_TYPE_TINY	TINYINT field
FIELD_TYPE_SHORT	SMALLINT field
FIELD_TYPE_LONG	INTEGER field
FIELD_TYPE_INT24	MEDIUMINT field
FIELD_TYPE_LONGLONG	BIGINT field
FIELD_TYPE_DECIMAL	DECIMAL or NUMERIC field
FIELD_TYPE_FLOAT	FLOAT field
FIELD_TYPE_DOUBLE	DOUBLE or REAL field
FIELD_TYPE_TIMESTAMP	TIMESTAMP field
FIELD_TYPE_DATE	DATE field
FIELD_TYPE_TIME	TIME field
FIELD_TYPE_DATETIME	DATETIME field
FIELD_TYPE_YEAR	YEAR field
FIELD_TYPE_STRING	String (CHAR or VARCHAR) field
FIELD_TYPE_BLOB	BLOB or TEXT field (use max_length to determine the maximum length)
FIELD_TYPE_SET	SET field
FIELD_TYPE_ENUM	ENUM field
FIELD_TYPE_NULL	NULL-type field
FIELD_TYPE_CHAR	Deprecated; use FIELD_TYPE_TINY instead

You can use the IS_NUM() macro to test whether a field has a numeric type. Pass the type value to IS_NUM() and it will evaluate to TRUE if the field is numeric:

if (IS_NUM(field->type))
    printf("Field is numeric\n");

unsigned int length

The width of the field, as specified in the table definition.

unsigned int max_length

The maximum width of the field for the result set (the length of the longest field value for the rows actually in the result set). If you use mysql_store_result() or mysql_list_fields(), this contains the maximum length for the field. If you use mysql_use_result(), the value of this variable is zero.

unsigned int flags

Different bit-flags for the field. The flags value may have zero or more of the following bits set:

Flag value	Flag description
NOT_NULL_FLAG	Field can't be NULL
PRI_KEY_FLAG	Field is part of a primary key
UNIQUE_KEY_FLAG	Field is part of a unique key
MULTIPLE_KEY_FLAG	Field is part of a non-unique key
UNSIGNED_FLAG	Field has the UNSIGNED attribute
ZEROFILL_FLAG	Field has the ZEROFILL attribute
BINARY_FLAG	Field has the BINARY attribute
AUTO_INCREMENT_FLAG	Field has the AUTO_INCREMENT attribute
ENUM_FLAG	Field is an ENUM (deprecated)
SET_FLAG	Field is a SET (deprecated)
BLOB_FLAG	Field is a BLOB or TEXT (deprecated)
TIMESTAMP_FLAG	Field is a TIMESTAMP (deprecated)

Use of the BLOB_FLAG, ENUM_FLAG, SET_FLAG, and TIMESTAMP_FLAG flags is deprecated because they indicate the type of a field rather than an attribute of its type. It is preferable to test field->type against FIELD_TYPE_BLOB, FIELD_TYPE_ENUM, FIELD_TYPE_SET, or FIELD_TYPE_TIMESTAMP instead. The following example illustrates a typical use of the flags value:

if (field->flags & NOT_NULL_FLAG)
    printf("Field can't be null\n");

You may use the following convenience macros to determine the boolean status of the flags value:

Flag status	Description
IS_NOT_NULL(flags)	True if this field is defined as NOT NULL
IS_PRI_KEY(flags)	True if this field is a primary key
IS_BLOB(flags)	True if this field is a BLOB or TEXT (deprecated; test field->type instead)

unsigned int decimals

The number of decimals for numeric fields.

9.1.2 C API Function Overview

The functions available in the C API are listed here and are described in greater detail in a later section. See section 9.1.3 C API Function Descriptions.

To connect to the server, call mysql_init() to initialise a connection handler, then call mysql_real_connect() with that handler (along with other information such as the hostname, user name, and password). Upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up. When you are done with the connection, call mysql_close() to terminate it.

While a connection is active, the client may send SQL queries to the server using mysql_query() or mysql_real_query(). The difference between the two is that mysql_query() expects the query to be specified as a null-terminated string whereas mysql_real_query() expects a counted string. If the string contains binary data (which may include null bytes), you must use mysql_real_query().

For each non-SELECT query (for example, INSERT, UPDATE, DELETE), you can find out how many rows were changed (affected) by calling mysql_affected_rows().

For SELECT queries, you retrieve the selected rows as a result set. (Note that some statements are SELECT-like in that they return rows. These include SHOW, DESCRIBE, and EXPLAIN. They should be treated the same way as SELECT statements.)

There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once by calling mysql_store_result(). This function acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval by calling mysql_use_result(). This function initialises the retrieval, but does not actually get any rows from the server.

In both cases, you access rows by calling mysql_fetch_row(). With mysql_store_result(), mysql_fetch_row() accesses rows that have already been fetched from the server. With mysql_use_result(), mysql_fetch_row() actually retrieves the row from the server. Information about the size of the data in each row is available by calling mysql_fetch_lengths().

After you are done with a result set, call mysql_free_result() to free the memory used for it.

The two retrieval mechanisms are complementary. Client programs should choose the approach that is most appropriate for their requirements. In practice, clients tend to use mysql_store_result() more commonly.

An advantage of mysql_store_result() is that because the rows have all been fetched to the client, you not only can access rows sequentially, you can move back and forth in the result set using mysql_data_seek() or mysql_row_seek() to change the current row position within the result set. You can also find out how many rows there are by calling mysql_num_rows(). On the other hand, the memory requirements for mysql_store_result() may be very high for large result sets and you are more likely to encounter out-of-memory conditions.

An advantage of mysql_use_result() is that the client requires less memory for the result set because it maintains only one row at a time (and because there is less allocation overhead, mysql_use_result() can be faster). Disadvantages are that you must process each row quickly to avoid tying up the server, you don't have random access to rows within the result set (you can only access rows sequentially), and you don't know how many rows are in the result set until you have retrieved them all. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.

The API makes it possible for clients to respond appropriately to queries (retrieving rows only as necessary) without knowing whether or not the query is a SELECT. You can do this by calling mysql_store_result() after each mysql_query() (or mysql_real_query()). If the result set call succeeds, the query was a SELECT and you can read the rows. If the result set call fails, call mysql_field_count() to determine whether a result was actually to be expected. If mysql_field_count() returns zero, the query returned no data (indicating that it was an INSERT, UPDATE, DELETE, etc.), and was not expected to return rows. If mysql_field_count() is non-zero, the query should have returned rows, but didn't. This indicates that the query was a SELECT that failed. See the description for mysql_field_count() for an example of how this can be done.

Both mysql_store_result() and mysql_use_result() allow you to obtain information about the fields that make up the result set (the number of fields, their names and types, etc.). You can access field information sequentially within the row by calling mysql_fetch_field() repeatedly, or by field number within the row by calling mysql_fetch_field_direct(). The current field cursor position may be changed by calling mysql_field_seek(). Setting the field cursor affects subsequent calls to mysql_fetch_field(). You can also get information for fields all at once by calling mysql_fetch_fields().

For detecting and reporting errors, MySQL provides access to error information by means of the mysql_errno() and mysql_error() functions. These return the error code or error message for the most recently invoked function that can succeed or fail, allowing you to determine when an error occurred and what it was.

9.1.3 C API Function Descriptions

In the descriptions here, a parameter or return value of NULL means NULL in the sense of the C programming language, not a MySQL NULL value.

Functions that return a value generally return a pointer or an integer. Unless specified otherwise, functions returning a pointer return a non-NULL value to indicate success or a NULL value to indicate an error, and functions returning an integer return zero to indicate success or non-zero to indicate an error. Note that ``non-zero'' means just that. Unless the function description says otherwise, do not test against a value other than zero:

if (result)                   /* correct */
    ... error ...

if (result < 0)               /* incorrect */
    ... error ...

if (result == -1)             /* incorrect */
    ... error ...

When a function returns an error, the Errors subsection of the function description lists the possible types of errors. You can find out which of these occurred by calling mysql_errno(). A string representation of the error may be obtained by calling mysql_error().

9.1.3.1 mysql_affected_rows()

my_ulonglong mysql_affected_rows(MYSQL *mysql)

Description

Returns the number of rows changed by the last UPDATE, deleted by the last DELETE or inserted by the last INSERT statement. May be called immediately after mysql_query() for UPDATE, DELETE, or INSERT statements. For SELECT statements, mysql_affected_rows() works like mysql_num_rows().

Return Values

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query, mysql_affected_rows() was called prior to calling mysql_store_result().

Errors

None.

Example

mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10");
printf("%ld products updated",(long) mysql_affected_rows(&mysql));

If one specifies the flag CLIENT_FOUND_ROWS when connecting to mysqld, mysql_affected_rows() will return the number of rows matched by the WHERE statement for UPDATE statements.

Note that when one uses a REPLACE command, mysql_affected_rows() will return 2 if the new row replaced and old row. This is because in this case one row was inserted after the duplicate was deleted.

9.1.3.2 mysql_change_user()

my_bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)

Description

Changes the user and causes the database specified by db to become the default (current) database on the connection specified by mysql. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.

This function was introduced in MySQL Version 3.23.3.

mysql_change_user() fails unless the connected user can be authenticated or if he doesn't have permission to use the database. In this case the user and database are not changed

The db parameter may be set to NULL if you don't want to have a default database.

Starting from MySQL 4.0.6 this command will always ROLLBACK any active transactions, close all temporary tables, unlock all locked tables and reset the state as if one had done a new connect. This will happen even if the user didn't change.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

The same that you can get from mysql_real_connect().

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

ER_UNKNOWN_COM_ERROR

The MySQL server doesn't implement this command (probably an old server)

ER_ACCESS_DENIED_ERROR

The user or password was wrong.

ER_BAD_DB_ERROR

The database didn't exist.

ER_DBACCESS_DENIED_ERROR

The user did not have access rights to the database.

ER_WRONG_DB_NAME

The database name was too long.

Example

if (mysql_change_user(&mysql, "user", "password", "new_database"))
{
   fprintf(stderr, "Failed to change user.  Error: %s\n",
           mysql_error(&mysql));
}

9.1.3.3 mysql_character_set_name()

const char *mysql_character_set_name(MYSQL *mysql)

Description

Returns the default character set for the current connection.

Return Values

The default character set

Errors

None.

9.1.3.4 mysql_close()

void mysql_close(MYSQL *mysql)

Description

Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init() or mysql_connect().

Return Values

None.

Errors

None.

9.1.3.5 mysql_connect()

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

Description

This function is deprecated. It is preferable to use mysql_real_connect() instead.

mysql_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_connect() must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info().

The meanings of the parameters are the same as for the corresponding parameters for mysql_real_connect() with the difference that the connection parameter may be NULL. In this case the C API allocates memory for the connection structure automatically and frees it when you call mysql_close(). The disadvantage of this approach is that you can't retrieve an error message if the connection fails. (To get error information from mysql_errno() or mysql_error(), you must provide a valid MYSQL pointer.)

Return Values

Same as for mysql_real_connect().

Errors

Same as for mysql_real_connect().

9.1.3.6 mysql_create_db()

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

Description

Creates the database named by the db parameter.

This function is deprecated. It is preferable to use mysql_query() to issue an SQL CREATE DATABASE statement instead.

Return Values

Zero if the database was created successfully. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

Example

if(mysql_create_db(&mysql, "my_database"))
{
   fprintf(stderr, "Failed to create new database.  Error: %s\n",
           mysql_error(&mysql));
}

9.1.3.7 mysql_data_seek()

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)

Description

Seeks to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_data_seek() may be used in conjunction only with mysql_store_result(), not with mysql_use_result().

The offset should be a value in the range from 0 to mysql_num_rows(result)-1.

Return Values

None.

Errors

None.

9.1.3.8 mysql_debug()

void mysql_debug(const char *debug)

Description

Does a DBUG_PUSH with the given string. mysql_debug() uses the Fred Fish debug library. To use this function, you must compile the client library to support debugging. See section E.1 Debugging a MySQL server. See section E.2 Debugging a MySQL client.

Return Values

None.

Errors

None.

Example

The call shown here causes the client library to generate a trace file in `/tmp/client.trace' on the client machine:

mysql_debug("d:t:O,/tmp/client.trace");

9.1.3.9 mysql_drop_db()

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

Description

Drops the database named by the db parameter.

This function is deprecated. It is preferable to use mysql_query() to issue an SQL DROP DATABASE statement instead.

Return Values

Zero if the database was dropped successfully. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

Example

if(mysql_drop_db(&mysql, "my_database"))
  fprintf(stderr, "Failed to drop the database: Error: %s\n",
          mysql_error(&mysql));

9.1.3.10 mysql_dump_debug_info()

int mysql_dump_debug_info(MYSQL *mysql)

Description

Instructs the server to write some debug information to the log. For this to work, the connected user must have the SUPER privilege.

Return Values

Zero if the command was successful. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.11 mysql_eof()

my_bool mysql_eof(MYSQL_RES *result)

Description

This function is deprecated. mysql_errno() or mysql_error() may be used instead.

mysql_eof() determines whether the last row of a result set has been read.

If you acquire a result set from a successful call to mysql_store_result(), the client receives the entire set in one operation. In this case, a NULL return from mysql_fetch_row() always means the end of the result set has been reached and it is unnecessary to call mysql_eof(). When used with mysql_store_result(), mysql_eof() will always return true.

On the other hand, if you use mysql_use_result() to initiate a result set retrieval, the rows of the set are obtained from the server one by one as you call mysql_fetch_row() repeatedly. Because an error may occur on the connection during this process, a NULL return value from mysql_fetch_row() does not necessarily mean the end of the result set was reached normally. In this case, you can use mysql_eof() to determine what happened. mysql_eof() returns a non-zero value if the end of the result set was reached and zero if an error occurred.

Historically, mysql_eof() predates the standard MySQL error functions mysql_errno() and mysql_error(). Because those error functions provide the same information, their use is preferred over mysql_eof(), which is now deprecated. (In fact, they provide more information, because mysql_eof() returns only a boolean value whereas the error functions indicate a reason for the error when one occurs.)

Return Values

Zero if no error occurred. Non-zero if the end of the result set has been reached.

Errors

None.

Example

The following example shows how you might use mysql_eof():

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(!mysql_eof(result))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}

However, you can achieve the same effect with the standard MySQL error functions:

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(mysql_errno(&mysql))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}

9.1.3.12 mysql_errno()

unsigned int mysql_errno(MYSQL *mysql)

Description

For the connection specified by mysql, mysql_errno() returns the error code for the most recently invoked API function that can succeed or fail. A return value of zero means that no error occurred. Client error message numbers are listed in the MySQL `errmsg.h' header file. Server error message numbers are listed in `mysqld_error.h'. In the MySQL source distribution you can find a complete list of error messages and error numbers in the file `Docs/mysqld_error.txt'.

Return Values

An error code value. Zero if no error occurred.

Errors

None.

9.1.3.13 mysql_error()

char *mysql_error(MYSQL *mysql)

Description

For the connection specified by mysql, mysql_error() returns the error message for the most recently invoked API function that can succeed or fail. An empty string ("") is returned if no error occurred. This means the following two tests are equivalent:

if(mysql_errno(&mysql))
{
    // an error occurred
}

if(mysql_error(&mysql)[0] != '\0')
{
    // an error occurred
}

The language of the client error messages may be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages. See section 4.6.2 Non-English Error Messages.

Return Values

A character string that describes the error. An empty string if no error occurred.

Errors

None.

9.1.3.14 mysql_escape_string()

You should use mysql_real_escape_string() instead!

This function is identical to mysql_real_escape_string() except that mysql_real_escape_string() takes a connection handler as its first argument and escapes the string according to the current character set. mysql_escape_string() does not take a connection argument and does not respect the current charset setting.

9.1.3.15 mysql_fetch_field()

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)

Description

Returns the definition of one column of a result set as a MYSQL_FIELD structure. Call this function repeatedly to retrieve information about all columns in the result set. mysql_fetch_field() returns NULL when no more fields are left.

mysql_fetch_field() is reset to return information about the first field each time you execute a new SELECT query. The field returned by mysql_fetch_field() is also affected by calls to mysql_field_seek().

If you've called mysql_query() to perform a SELECT on a table but have not called mysql_store_result(), MySQL returns the default blob length (8K bytes) if you call mysql_fetch_field() to ask for the length of a BLOB field. (The 8K size is chosen because MySQL doesn't know the maximum length for the BLOB. This should be made configurable sometime.) Once you've retrieved the result set, field->max_length contains the length of the largest value for this column in the specific query.

Return Values

The MYSQL_FIELD structure for the current column. NULL if no columns are left.

Errors

None.

Example

MYSQL_FIELD *field;

while((field = mysql_fetch_field(result)))
{
    printf("field name %s\n", field->name);
}

9.1.3.16 mysql_fetch_fields()

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)

Description

Returns an array of all MYSQL_FIELD structures for a result set. Each structure provides the field definition for one column of the result set.

Return Values

An array of MYSQL_FIELD structures for all columns of a result set.

Errors

None.

Example

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *fields;

num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++)
{
   printf("Field %u is %s\n", i, fields[i].name);
}

9.1.3.17 mysql_fetch_field_direct()

MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int fieldnr)

Description

Given a field number fieldnr for a column within a result set, returns that column's field definition as a MYSQL_FIELD structure. You may use this function to retrieve the definition for an arbitrary column. The value of fieldnr should be in the range from 0 to mysql_num_fields(result)-1.

Return Values

The MYSQL_FIELD structure for the specified column.

Errors

None.

Example

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *field;

num_fields = mysql_num_fields(result);
for(i = 0; i < num_fields; i++)
{
    field = mysql_fetch_field_direct(result, i);
    printf("Field %u is %s\n", i, field->name);
}

9.1.3.18 mysql_fetch_lengths()

unsigned long *mysql_fetch_lengths(MYSQL_RES *result)

Description

Returns the lengths of the columns of the current row within a result set. If you plan to copy field values, this length information is also useful for optimisation, because you can avoid calling strlen(). In addition, if the result set contains binary data, you must use this function to determine the size of the data, because strlen() returns incorrect results for any field containing null characters.

The length for empty columns and for columns containing NULL values is zero. To see how to distinguish these two cases, see the description for mysql_fetch_row().

Return Values

An array of unsigned long integers representing the size of each column (not including any terminating null characters). NULL if an error occurred.

Errors

mysql_fetch_lengths() is valid only for the current row of the result set. It returns NULL if you call it before calling mysql_fetch_row() or after retrieving all rows in the result.

Example

MYSQL_ROW row;
unsigned long *lengths;
unsigned int num_fields;
unsigned int i;

row = mysql_fetch_row(result);
if (row)
{
    num_fields = mysql_num_fields(result);
    lengths = mysql_fetch_lengths(result);
    for(i = 0; i < num_fields; i++)
    {
         printf("Column %u is %lu bytes in length.\n", i, lengths[i]);
    }
}

9.1.3.19 mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Description

Retrieves the next row of a result set. When used after mysql_store_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve. When used after mysql_use_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve or if an error occurred.

The number of values in the row is given by mysql_num_fields(result). If row holds the return value from a call to mysql_fetch_row(), pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1]. NULL values in the row are indicated by NULL pointers.

The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths(). Empty fields and fields containing NULL both have length 0; you can distinguish these by checking the pointer for the field value. If the pointer is NULL, the field is NULL; otherwise, the field is empty.

Return Values

A MYSQL_ROW structure for the next row. NULL if there are no more rows to retrieve or if an error occurred.

Errors

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

Example

MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;

num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
   unsigned long *lengths;
   lengths = mysql_fetch_lengths(result);
   for(i = 0; i < num_fields; i++)
   {
       printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");
   }
   printf("\n");
}

9.1.3.20 mysql_field_count()

unsigned int mysql_field_count(MYSQL *mysql)

If you are using a version of MySQL earlier than Version 3.22.24, you should use unsigned int mysql_num_fields(MYSQL *mysql) instead.

Description

Returns the number of columns for the most recent query on the connection.

The normal use of this function is when mysql_store_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a non-empty result. This allows the client program to take proper action without knowing whether the query was a SELECT (or SELECT-like) statement. The example shown here illustrates how this may be done.

See section 9.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?.

Return Values

An unsigned integer representing the number of fields in a result set.

Errors

None.

Example

MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;

if (mysql_query(&mysql,query_string))
{
    // error
}
else // query succeeded, process any data returned by it
{
    result = mysql_store_result(&mysql);
    if (result)  // there are rows
    {
        num_fields = mysql_num_fields(result);
        // retrieve rows, then call mysql_free_result(result)
    }
    else  // mysql_store_result() returned nothing; should it have?
    {
        if(mysql_field_count(&mysql) == 0)
        {
            // query does not return data
            // (it was not a SELECT)
            num_rows = mysql_affected_rows(&mysql);
        }
        else // mysql_store_result() should have returned data
        {
            fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
        }
    }
}

An alternative is to replace the mysql_field_count(&mysql) call with mysql_errno(&mysql). In this case, you are checking directly for an error from mysql_store_result() rather than inferring from the value of mysql_field_count() whether the statement was a SELECT.

9.1.3.21 mysql_field_seek()

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)

Description

Sets the field cursor to the given offset. The next call to mysql_fetch_field() will retrieve the field definition of the column associated with that offset.

To seek to the beginning of a row, pass an offset value of zero.

Return Values

The previous value of the field cursor.

Errors

None.

9.1.3.22 mysql_field_tell()

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)

Description

Returns the position of the field cursor used for the last mysql_fetch_field(). This value can be used as an argument to mysql_field_seek().

Return Values

The current offset of the field cursor.

Errors

None.

9.1.3.23 mysql_free_result()

void mysql_free_result(MYSQL_RES *result)

Description

Frees the memory allocated for a result set by mysql_store_result(), mysql_use_result(), mysql_list_dbs(), etc. When you are done with a result set, you must free the memory it uses by calling mysql_free_result().

Return Values

None.

Errors

None.

9.1.3.24 mysql_get_client_info()

char *mysql_get_client_info(void)

Description

Returns a string that represents the client library version.

Return Values

A character string that represents the MySQL client library version.

Errors

None.

9.1.3.25 mysql_get_server_version()

unsigned long mysql_get_server_version(MYSQL *mysql)

Description

Returns version number of server as an integer (new in 4.1).

Return Values

A number that represents the MySQL server version in format:

main_version*10000 + minor_version *100 + sub_version

For example, 4.1.0 is returned as 40100.

This is useful to quickly determine the version of the server in a client program to know if some capability exits.

Errors

None.

9.1.3.26 mysql_get_host_info()

char *mysql_get_host_info(MYSQL *mysql)

Description

Returns a string describing the type of connection in use, including the server host name.

Return Values

A character string representing the server host name and the connection type.

Errors

None.

9.1.3.27 mysql_get_proto_info()

unsigned int mysql_get_proto_info(MYSQL *mysql)

Description

Returns the protocol version used by current connection.

Return Values

An unsigned integer representing the protocol version used by the current connection.

Errors

None.

9.1.3.28 mysql_get_server_info()

char *mysql_get_server_info(MYSQL *mysql)

Description

Returns a string that represents the server version number.

Return Values

A character string that represents the server version number.

Errors

None.

9.1.3.29 mysql_info()

char *mysql_info(MYSQL *mysql)

Description

Retrieves a string providing information about the most recently executed query, but only for the statements listed here. For other statements, mysql_info() returns NULL. The format of the string varies depending on the type of query, as described here. The numbers are illustrative only; the string will contain values appropriate for the query.

INSERT INTO ... SELECT ...

String format: Records: 100 Duplicates: 0 Warnings: 0

INSERT INTO ... VALUES (...),(...),(...)...

String format: Records: 3 Duplicates: 0 Warnings: 0

LOAD DATA INFILE ...

String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

ALTER TABLE

String format: Records: 3 Duplicates: 0 Warnings: 0

UPDATE

String format: Rows matched: 40 Changed: 40 Warnings: 0

Note that mysql_info() returns a non-NULL value for the INSERT ... VALUES statement only if multiple value lists are specified in the statement.

Return Values

A character string representing additional information about the most recently executed query. NULL if no information is available for the query.

Errors

None.

9.1.3.30 mysql_init()

MYSQL *mysql_init(MYSQL *mysql)

Description

Allocates or initialises a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function allocates, initialises, and returns a new object. Otherwise, the object is initialised and the address of the object is returned. If mysql_init() allocates a new object, it will be freed when mysql_close() is called to close the connection.

Return Values

An initialised MYSQL* handle. NULL if there was insufficient memory to allocate a new object.

Errors

In case of insufficient memory, NULL is returned.

9.1.3.31 mysql_insert_id()

my_ulonglong mysql_insert_id(MYSQL *mysql)

Description

Returns the ID generated for an AUTO_INCREMENT column by the previous query. Use this function after you have performed an INSERT query into a table that contains an AUTO_INCREMENT field.

Note that mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value.

mysql_insert_id() is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column value to LAST_INSERT_ID(expr). See section 6.3.6.2 Miscellaneous Functions.

Also note that the value of the SQL LAST_INSERT_ID() function always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries because the value of that function is maintained in the server.

Return Values

The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Errors

None.

9.1.3.32 mysql_kill()

int mysql_kill(MYSQL *mysql, unsigned long pid)

Description

Asks the server to kill the thread specified by pid.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.33 mysql_list_dbs()

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

Description

Returns a result set consisting of database names on the server that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters `%' or `_', or may be a NULL pointer to match all databases. Calling mysql_list_dbs() is similar to executing the query SHOW databases [LIKE wild].

You must free the result set with mysql_free_result().

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.34 mysql_list_fields()

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

Description

Returns a result set consisting of field names in the given table that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters `%' or `_', or may be a NULL pointer to match all fields. Calling mysql_list_fields() is similar to executing the query SHOW COLUMNS FROM tbl_name [LIKE wild].

Note that it's recommended that you use SHOW COLUMNS FROM tbl_name instead of mysql_list_fields().

You must free the result set with mysql_free_result().

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.35 mysql_list_processes()

MYSQL_RES *mysql_list_processes(MYSQL *mysql)

Description

Returns a result set describing the current server threads. This is the same kind of information as that reported by mysqladmin processlist or a SHOW PROCESSLIST query.

You must free the result set with mysql_free_result().

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.36 mysql_list_tables()

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

Description

Returns a result set consisting of table names in the current database that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters `%' or `_', or may be a NULL pointer to match all tables. Calling mysql_list_tables() is similar to executing the query SHOW tables [LIKE wild].

You must free the result set with mysql_free_result().

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.37 mysql_num_fields()

unsigned int mysql_num_fields(MYSQL_RES *result)

or

unsigned int mysql_num_fields(MYSQL *mysql)

The second form doesn't work on MySQL Version 3.22.24 or newer. To pass a MYSQL* argument, you must use unsigned int mysql_field_count(MYSQL *mysql) instead.

Description

Returns the number of columns in a result set.

Note that you can get the number of columns either from a pointer to a result set or to a connection handle. You would use the connection handle if mysql_store_result() or mysql_use_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a non-empty result. This allows the client program to take proper action without knowing whether or not the query was a SELECT (or SELECT-like) statement. The example shown here illustrates how this may be done.

See section 9.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?.

Return Values

An unsigned integer representing the number of fields in a result set.

Errors

None.

Example

MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;

if (mysql_query(&mysql,query_string))
{
    // error
}
else // query succeeded, process any data returned by it
{
    result = mysql_store_result(&mysql);
    if (result)  // there are rows
    {
        num_fields = mysql_num_fields(result);
        // retrieve rows, then call mysql_free_result(result)
    }
    else  // mysql_store_result() returned nothing; should it have?
    {
        if (mysql_errno(&mysql))
        {
           fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
        }
        else if (mysql_field_count(&mysql) == 0)
        {
            // query does not return data
            // (it was not a SELECT)
            num_rows = mysql_affected_rows(&mysql);
        }
    }
}

An alternative (if you know that your query should have returned a result set) is to replace the mysql_errno(&mysql) call with a check if mysql_field_count(&mysql) is = 0. This will only happen if something went wrong.

9.1.3.38 mysql_num_rows()

my_ulonglong mysql_num_rows(MYSQL_RES *result)

Description

Returns the number of rows in the result set.

The use of mysql_num_rows() depends on whether you use mysql_store_result() or mysql_use_result() to return the result set. If you use mysql_store_result(), mysql_num_rows() may be called immediately. If you use mysql_use_result(), mysql_num_rows() will not return the correct value until all the rows in the result set have been retrieved.

Return Values

The number of rows in the result set.

Errors

None.

9.1.3.39 mysql_options()

int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg)

Description

Can be used to set extra connect options and affect behaviour for a connection. This function may be called multiple times to set several options.

mysql_options() should be called after mysql_init() and before mysql_connect() or mysql_real_connect().

The option argument is the option that you want to set; the arg argument is the value for the option. If the option is an integer, then arg should point to the value of the integer.

Possible options values:

Note that the group client is always read if you use MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP.

The specified group in the option file may contain the following options:

Note that timeout has been replaced by connect-timeout, but timeout will still work for a while.

For more information about option files, see section 4.1.2 `my.cnf' Option Files.

Return Values

Zero for success. Non-zero if you used an unknown option.

Example

MYSQL mysql;

mysql_init(&mysql);
mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
    fprintf(stderr, "Failed to connect to database: Error: %s\n",
          mysql_error(&mysql));
}

The above requests the client to use the compressed client/server protocol and read the additional options from the odbc section in the `my.cnf' file.

9.1.3.40 mysql_ping()

int mysql_ping(MYSQL *mysql)

Description

Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.

This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.

Return Values

Zero if the server is alive. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.41 mysql_query()

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

Description

Executes the SQL query pointed to by the null-terminated string query. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g to the statement.

mysql_query() cannot be used for queries that contain binary data; you should use mysql_real_query() instead. (Binary data may contain the `\0' character, which mysql_query() interprets as the end of the query string.)

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this. See section 9.1.3.20 mysql_field_count().

Return Values

Zero if the query was successful. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.42 mysql_real_connect()

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag)

Description

mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_real_connect() must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info().

The parameters are specified as follows:

• The first parameter should be the address of an existing MYSQL structure. Before calling mysql_real_connect() you must call mysql_init() to initialise the MYSQL structure. You can change a lot of connect options with the mysql_options() call. See section 9.1.3.39 mysql_options().
• The value of host may be either a hostname or an IP address. If host is NULL or the string "localhost", a connection to the local host is assumed. If the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.
• The user parameter contains the user's MySQL login ID. If user is NULL, the current user is assumed. Under Unix, this is the current login name. Under Windows ODBC, the current user name must be specified explicitly. See section 9.2.2 How to Fill in the Various Fields in the ODBC Administrator Program.
• The passwd parameter contains the password for user. If passwd is NULL, only entries in the user table for the user that have a blank (empty) password field will be checked for a match. This allows the database administrator to set up the MySQL privilege system in such a way that users get different privileges depending on whether or not they have specified a password. Note: Do not attempt to encrypt the password before calling mysql_real_connect(); password encryption is handled automatically by the client API.
• db is the database name. If db is not NULL, the connection will set the default database to this value.
• If port is not 0, the value will be used as the port number for the TCP/IP connection. Note that the host parameter determines the type of the connection.
• If unix_socket is not NULL, the string specifies the socket or named pipe that should be used. Note that the host parameter determines the type of the connection.
• The value of client_flag is usually 0, but can be set to a combination of the following flags in very special circumstances:

  Flag name	Flag description
  CLIENT_COMPRESS	Use compression protocol.
  CLIENT_FOUND_ROWS	Return the number of found (matched) rows, not the number of affected rows.
  CLIENT_IGNORE_SPACE	Allow spaces after function names. Makes all functions names reserved words.
  CLIENT_INTERACTIVE	Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection.
  CLIENT_LOCAL_FILES	Enable LOAD DATA LOCAL handling.
  CLIENT_MULTI_QUERIES	Tell the server that the client may send multi-row-queries (separated with `;'). If this flag is not set, multi-row-queries are disabled. New in 4.1.
  CLIENT_MULTI_RESULTS	Tell the server that the client can handle multiple-result sets from multi-queries or stored procedures. This is automatically set if CLIENT_MULTI_QUERIES is set. New in 4.1.
  CLIENT_NO_SCHEMA	Don't allow the db_name.tbl_name.col_name syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs.
  CLIENT_ODBC	The client is an ODBC client. This changes mysqld to be more ODBC-friendly.
  CLIENT_SSL	Use SSL (encrypted protocol).

Return Values

A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter.

Errors

CR_CONN_HOST_ERROR

Failed to connect to the MySQL server.

CR_CONNECTION_ERROR

Failed to connect to the local MySQL server.

CR_IPSOCK_ERROR

Failed to create an IP socket.

CR_OUT_OF_MEMORY

Out of memory.

CR_SOCKET_CREATE_ERROR

Failed to create a Unix socket.

CR_UNKNOWN_HOST

Failed to find the IP address for the hostname.

CR_VERSION_ERROR

A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version. This can happen if you use a very old client library to connect to a new server that wasn't started with the --old-protocol option.

CR_NAMEDPIPEOPEN_ERROR

Failed to create a named pipe on Windows.

CR_NAMEDPIPEWAIT_ERROR

Failed to wait for a named pipe on Windows.

CR_NAMEDPIPESETSTATE_ERROR

Failed to get a pipe handler on Windows.

CR_SERVER_LOST

If connect_timeout > 0 and it took longer then connect_timeout seconds to connect to the server or if the server died while executing the init-command.

Example

MYSQL mysql;

mysql_init(&mysql);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
    fprintf(stderr, "Failed to connect to database: Error: %s\n",
          mysql_error(&mysql));
}

By using mysql_options() the MySQL library will read the [client] and [your_prog_name] sections in the `my.cnf' file which will ensure that your program will work, even if someone has set up MySQL in some non-standard way.

Note that upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up.

9.1.3.43 mysql_real_escape_string()

unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length)

Description

This function is used to create a legal SQL string that you can use in a SQL statement. See section 6.1.1.1 Strings.

The string in from is encoded to an escaped SQL string, taking into account the current character set of the connection. The result is placed in to and a terminating null byte is appended. Characters encoded are NUL (ASCII 0), `\n', `\r', `\', `'', `"', and Control-Z (see section 6.1.1 Literals: How to Write Strings and Numbers). (Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. This function quotes the other characters to make them easier to read in log files.)

The string pointed to by from must be length bytes long. You must allocate the to buffer to be at least length*2+1 bytes long. (In the worst case, each character may need to be encoded as using two bytes, and you need room for the terminating null byte.) When mysql_real_escape_string() returns, the contents of to will be a null-terminated string. The return value is the length of the encoded string, not including the terminating null character.

Example

char query[1000],*end;

end = strmov(query,"INSERT INTO test_table values(");
*end++ = '\'';
end += mysql_real_escape_string(&mysql, end,"What's this",11);
*end++ = '\'';
*end++ = ',';
*end++ = '\'';
end += mysql_real_escape_string(&mysql, end,"binary data: \0\r\n",16);
*end++ = '\'';
*end++ = ')';

if (mysql_real_query(&mysql,query,(unsigned int) (end - query)))
{
   fprintf(stderr, "Failed to insert row, Error: %s\n",
           mysql_error(&mysql));
}

The strmov() function used in the example is included in the mysqlclient library and works like strcpy() but returns a pointer to the terminating null of the first parameter.

Return Values

The length of the value placed into to, not including the terminating null character.

Errors

None.

9.1.3.44 mysql_real_query()

int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)

Description

Executes the SQL query pointed to by query, which should be a string length bytes long. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g to the statement.

You must use mysql_real_query() rather than mysql_query() for queries that contain binary data, because binary data may contain the `\0' character. In addition, mysql_real_query() is faster than mysql_query() because it does not call strlen() on the query string.

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this. See section 9.1.3.20 mysql_field_count().

Return Values

Zero if the query was successful. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.45 mysql_reload()

int mysql_reload(MYSQL *mysql)

Description

Asks the MySQL server to reload the grant tables. The connected user must have the RELOAD privilege.

This function is deprecated. It is preferable to use mysql_query() to issue an SQL FLUSH PRIVILEGES statement instead.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.46 mysql_row_seek()

MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset)

Description

Sets the row cursor to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_row_seek() may be used in conjunction only with mysql_store_result(), not with mysql_use_result().

The offset should be a value returned from a call to mysql_row_tell() or to mysql_row_seek(). This value is not simply a row number; if you want to seek to a row within a result set using a row number, use mysql_data_seek() instead.

Return Values

The previous value of the row cursor. This value may be passed to a subsequent call to mysql_row_seek().

Errors

None.

9.1.3.47 mysql_row_tell()

MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)

Description

Returns the current position of the row cursor for the last mysql_fetch_row(). This value can be used as an argument to mysql_row_seek().

You should use mysql_row_tell() only after mysql_store_result(), not after mysql_use_result().

Return Values

The current offset of the row cursor.

Errors

None.

9.1.3.48 mysql_select_db()

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

Description

Causes the database specified by db to become the default (current) database on the connection specified by mysql. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.

mysql_select_db() fails unless the connected user can be authenticated as having permission to use the database.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.49 mysql_sqlstate()

const char *mysql_sqlstate(MYSQL *mysql)

Description

Returns the SQLSTATE error code for the last error.

Note that not all MySQL errors are yet mapped to SQLSTATE's. For not mapped errors we return "HY000" (General error).

This function was added to MySQL 4.1.1.

Return Values

SQLSTATE is a 6 digit character string that is specified by ANSI SQL and ODBC.

See also

See section 9.1.3.12 mysql_errno(). See section 9.1.3.13 mysql_error(). See section 9.1.7.18 mysql_stmt_sqlstate().

9.1.3.50 mysql_shutdown()

int mysql_shutdown(MYSQL *mysql)

Description

Asks the database server to shut down. The connected user must have SHUTDOWN privileges.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.51 mysql_stat()

char *mysql_stat(MYSQL *mysql)

Description

Returns a character string containing information similar to that provided by the mysqladmin status command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.

Return Values

A character string describing the server status. NULL if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.52 mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql)

Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).

You don't have to call mysql_store_result() or mysql_use_result() for other queries, but it will not do any harm or cause any notable performance if you call mysql_store_result() in all cases. You can detect if the query didn't have a result set by checking if mysql_store_result() returns 0 (more about this later one).

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this. See section 9.1.3.20 mysql_field_count().

mysql_store_result() reads the entire result of a query to the client, allocates a MYSQL_RES structure, and places the result into this structure.

mysql_store_result() returns a null pointer if the query didn't return a result set (if the query was, for example, an INSERT statement).

mysql_store_result() also returns a null pointer if reading of the result set failed. You can check if you got an error by checking if mysql_error() doesn't return a null pointer, if mysql_errno() returns <> 0, or if mysql_field_count() returns <> 0.

An empty result set is returned if there are no rows returned. (An empty result set differs from a null pointer as a return value.)

Once you have called mysql_store_result() and got a result back that isn't a null pointer, you may call mysql_num_rows() to find out how many rows are in the result set.

You can call mysql_fetch_row() to fetch rows from the result set, or mysql_row_seek() and mysql_row_tell() to obtain or set the current row position within the result set.

You must call mysql_free_result() once you are done with the result set.

See section 9.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?.

Return Values

A MYSQL_RES result structure with the results. NULL if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.53 mysql_thread_id()

unsigned long mysql_thread_id(MYSQL *mysql)

Description

Returns the thread ID of the current connection. This value can be used as an argument to mysql_kill() to kill the thread.

If the connection is lost and you reconnect with mysql_ping(), the thread ID will change. This means you should not get the thread ID and store it for later. You should get it when you need it.

Return Values

The thread ID of the current connection.

Errors

None.

9.1.3.54 mysql_use_result()

MYSQL_RES *mysql_use_result(MYSQL *mysql)

Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client will only allocate memory for the current row and a communication buffer that may grow up to max_allowed_packet bytes.

On the other hand, you shouldn't use mysql_use_result() if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows will be returned as part of the result set for your next query. The C API will give the error Commands out of sync; you can't run this command now if you forget to do this!

You may not use mysql_data_seek(), mysql_row_seek(), mysql_row_tell(), mysql_num_rows(), or mysql_affected_rows() with a result returned from mysql_use_result(), nor may you issue other queries until the mysql_use_result() has finished. (However, after you have fetched all the rows, mysql_num_rows() will accurately return the number of rows fetched.)

You must call mysql_free_result() once you are done with the result set.

Return Values

A MYSQL_RES result structure. NULL if an error occurred.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.3.55 mysql_commit()

my_bool mysql_commit(MYSQL *mysql)

Description

Commits the current transaction. Available from MySQL 4.1

Return Values

Zero if successful. Non-zero if an error occured.

Errors

None

9.1.3.56 mysql_rollback()

my_bool mysql_rollback(MYSQL *mysql)

Description

Rollbacks the current transaction. Available from MySQL 4.1

Return Values

Zero if successful. Non-zero if an error occured.

Errors

None.

9.1.3.57 mysql_autocommit()

my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)

Description

Sets autocommit mode on/off. If the mode is 1, set autocommit mode to on; in case of 0, set to off. Available from MySQL 4.1

Return Values

Zero if successful. Non-zero if an error occured

Errors

None.

9.1.3.58 mysql_more_results()

my_bool mysql_more_results(MYSQL *mysql)

Description

Returns true if more results exists from the currently executed query, and the application must call mysql_next_result() to fetch the results. Available from MySQL 4.1

Return Values

TRUE if more results exists. FALSE if no more results exists.

Errors

None.

9.1.3.59 mysql_next_result()

int mysql_next_result(MYSQL *mysql)

Description

If more query results exists, then mysql_next_result() reads the next query results and returns the status back to application. Available from MySQL 4.1

Return Values

Zero if successful. Non-zero if an error occured

Errors

None.

9.1.4 C API Prepared Statements

From MySQL 4.1, you can also make use of prepared statements using the statement handler MYSQL_STMT, which supports multiple query execution with input and output binding.

Prepared execution is an efficient way to execute a statement more than once. The statement is first parsed (prepared). Then it is executed one or more times at a later time, using the statement handle returned by the prepare function.

Another advantage of prepared statements is that it uses a binary protocol which makes the data tranfer between client and server more efficient.

Prepared execution is faster than direct execution for statements executed more than once, primarly because the query is parsed only once; In the case of direct execution, the query is parsed every time. Prepared execution also can provide a reduction of network traffic because during the execute call, it only sends the data for the parameters.

9.1.5 C API Prepared Statements DataTypes

Prepared statements mainly uses the following two MYSQL_STMT and MYSQL_BIND structures:

MYSQL_STMT

This structure represents a statement handle to prepared statements.It is used for all statement related functions. The statement is initialised when the query is prepared using mysql_prepare(). One connection can have 'n' statement handles, and the limit depends up on the system resources.

MYSQL_BIND

This structure is used in order to bind parameter buffers(mysql_bind_param()) inorder to the parameters data to mysql_execute() call; as well as to bind row buffers(mysql_bind_result()) to fetch the result set data using mysql_fetch(). The MYSQL_BIND structure contains the members listed here:

enum enum_field_types buffer_type [input]

The type of the buffer. The type value must be one of the following:

• MYSQL_TYPE_TINY
• MYSQL_TYPE_SHORT
• MYSQL_TYPE_LONG
• MYSQL_TYPE_LONGLONG
• MYSQL_TYPE_FLOAT
• MYSQL_TYPE_DOUBLE
• MYSQL_TYPE_TIME
• MYSQL_TYPE_DATE
• MYSQL_TYPE_DATETIME
• MYSQL_TYPE_TIMESTAMP
• MYSQL_TYPE_STRING
• MYSQL_TYPE_VAR_STRING
• MYSQL_TYPE_TINY_BLOB
• MYSQL_TYPE_MEDIUM_BLOB
• MYSQL_TYPE_LONG_BLOB
• MYSQL_TYPE_BLOB

void *buffer [input/output]

A pointer to a buffer for the parameters data in case if it is used to supply parameters data or pointer to a buffer in which to return the data when the structure is used for result set bind.

unsigned long buffer_length [input]

Length of the *buffer in bytes. For character and binary C data, the buffer_length specifies the length of the *buffer to be used as a parameter data in case if it is used with mysql_bind_param() or to return that many bytes when fetching results when this is used with mysql_bind_result().

long *length [input/output]

Pointer to the buffer for the parameter's length. When the structure is used as a input parameter data binding, then this argument points to a buffer that, when mysql_execute() is called, contains the length of the parameter value stored in *buffer. This is ignored except for character or binary C data. If the length is a null pointer, then the protocol assumes that all character and binary data are null terminated. When this structure is used in output binding, then mysql_fetch() return the the length of the data that is returned.

bool *is_null [input/output]

Indicates if the parameter data is NULL or fetched data is NULL.

MYSQL_TIME

This structure is used to send and receive DATE, TIME and TIMESTAMP data directly to/from server. The MYSQL_TIME structure contains the members listed here:

Member	Type	Description
year	unsigned int	Year.
month	unsigned int	Month of the year.
day	unsigned int	Day of the month.
hour	unsigned int	Hour of the day(TIME).
minute	unsigned int	Minute of the hour.
second	unsigned int	Second of the minute.
neg	my_bool	A boolean flag to indicate if the time is negative.
second_part	unsigned long	Fraction part of the second(not yet used)

9.1.6 C API Prepared Statements Function Overview

The functions available in the prepared statements are listed here and are described in greater detail in the later section. See section 9.1.7 C API Prepared Statement Function Descriptions.

Call mysql_prepare() to prepare and initialise the statement handle, then call mysql_bind_param() to supply the parameters data, and then call mysql_execute() to execute the query. You can repeat the mysql_execute() by changing parameter values from the respective buffer supplied through mysql_bind_param().

In case if the query is a SELECT statement or any other query which results in a resultset, then mysql_prepare() will also return the result set meta data information in the form of MYSQL_RES result set through mysql_prepare_result().

You can supply the result buffers using mysql_bind_result(), so that the mysql_fetch() will automatically returns data to this buffers. This is row by row fetching.

You can also send the text or binary data in chunks to server using mysql_send_long_data(), by specifying the option is_long_data=1 or length=MYSQL_LONG_DATA or -2 in the MYSQL_BIND structure supplied with mysql_bind_param().

Once the statement execution is over, it must be freed using mysql_stmt_close so that it frees all the alloced resources for the statement handle.

Execution Steps:

To prepare and execute a statement, the application:

• Calls mysql_prepare() and passes it a string containing the SQL statement. On a successful prepare, mysql_prepare returns the valid statement handle back to the application
• If the query results in a resultset, then mysql_prepare_result returns the result set meta info..
• Sets the values of any parameters using mysql_bind_param. All parameters must be set; else it will return an error or produce un-expected results
• Calls mysql_execute() to execute the statement.
• Repeat steps 2 and 3 as necessary, by changing the parameter values and re-executing the statement.
• Bind the data buffers to return the row values, if it is a result set query; using mysql_bind_result().
• Fetch the data to buffers row by row by calling mysql_fetch() repetedely until no more rows found.
• When mysql_prepare() is called, in the MySQL client/server protocol:
  • Server parses the query and sends the ok status back to client by assinging a statement id. It also sends total number of parameters, columns count and its meta information if it is a result set oriented query. All syntax and symantecs of the query is checked during this call by the server.
  • Client uses this statement id for the further executions, so that server identifies the statement back from the pool of statements. Now, client allocates a statement handle with this id and returns back to application.
• When mysql_execute() is called, in the MySQL client/server protocol:
  • Client uses the statement handle and sends the parameters data to server.
  • Server identifies the statement using the id provided by the client, and replaces the parameter markers with the newly supplied data and executes the query. If it results in a result set, then sends the data back to client, else sends an OK status with total number of rows changes/deleted/inserted.
• When mysql_fetch() is called, in the MySQL client/server protocol:
  • Client reads the data from the packet row by row and places it to application data buffers by doing the necessary conversions. If the application buffer type is same as that of field type, then the conversions are straight forward.

You can get the statement error code and message using mysql_stmt_errno() and mysql_stmt_error() respectively.

9.1.7 C API Prepared Statement Function Descriptions

You need to use the following functions when you want to prepare and execute the queries.

9.1.7.1 mysql_prepare()

MYSQL_STMT * mysql_prepare(MYSQL *mysql, const char *query, unsigned long length)

Description

Prepares the SQL query pointed to by the null-terminated string 'query'. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;`) or \g to the statement.

The application can include one or more parameter markers in the SQL statement. To include a parameter marker, the appication embeds a question mark (?) into the SQL string at the appropriate position.

The markers are legal only in certain places in SQL statements. For example, they are not allowed in the select list(the list of columns to be returned by a SELECT statement), nor are they allowed as both operands of a binary operator such as the equal sign (=), because it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Languange(DML) statements, and not in Data Defination Language(DDL) statements.

The parameter markers are then bound to application variables using mysql_bind_param().

Return Values

MYSQL_STMT if the prepare was successful. NULL if an error occured.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order

CR_OUT_OF_MEMORY

Out of memory

CR_SERVER_GONE_ERROR

The MySQL server has gone away

CR_SERVER_LOST

The connection to the server was lost during the query

CR_UNKNOWN_ERROR

An unkown error occured

If the prepare is not successful, that is, when mysql_prepare() returned a NULL statement, errors can be obtained by calling mysql_error().

Example

For the usage of mysql_prepare() refer to the Example from section 9.1.7.5 mysql_execute().

9.1.7.2 mysql_param_count()

unsigned int mysql_param_count(MYSQL_STMT *stmt)

Description

Returns the number of parameter markers present from the prepared query.

Return Values

An unsigned integer representing the number of parameters in a statement.

Errors

None

Example

For the usage of mysql_param_count() refer to the Example from section 9.1.7.5 mysql_execute().

9.1.7.3 mysql_prepare_result()

MYSQL_RES *mysql_prepare_result(MYSQL_STMT *stmt)

Description

If the mysql_prepare() resulted in a result set query, then mysql_prepare_result() returns the result set meta data in the form of MYSQL_RES structure; which can further be used to process the meta information such as total number of fields and individual field information. This resulted result set can be passed as an argument to any of the field based APIs in order to process the result set meta data information such as:

• mysql_num_fields()
• mysql_fetch_field()
• mysql_fetch_field_direct()
• mysql_fetch_fields()
• mysql_field_count()
• mysql_field_seek()
• mysql_field_tell() and
• mysql_free_result()

Return Values

A MYSQL_RES result structure. NULL if no meta information exists from the prepared query.

Errors

CR_OUT_OF_MEMORY

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

Example

For the usage of mysql_prepare_result() refer to the Example from section 9.1.7.13 mysql_fetch()

9.1.7.4 mysql_bind_param()

int mysql_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)

Description

mysql_bind_param is used to bind data for the parameter markers in the SQL statement from mysql_prepare. It uses the structure MYSQL_BIND to supply the data. The supported buffer types are:

• MYSQL_TYPE_TINY
• MYSQL_TYPE_SHORT
• MYSQL_TYPE_LONG
• MYSQL_TYPE_LONGLONG
• MYSQL_TYPE_FLOAT
• MYSQL_TYPE_DOUBLE
• MYSQL_TYPE_TIME
• MYSQL_TYPE_DATE
• MYSQL_TYPE_DATETIME
• MYSQL_TYPE_TIMESTAMP
• MYSQL_TYPE_STRING
• MYSQL_TYPE_VAR_STRING
• MYSQL_TYPE_TINY_BLOB
• MYSQL_TYPE_MEDIUM_BLOB
• MYSQL_TYPE_LONG_BLOB

Return Values

Zero if the bind was successful. Non-zero if an error occured.

Errors

CR_NO_PREPARE_STMT

No prepared statement exists

CR_NO_PARAMETERS_EXISTS

No parameters exists to bind

CR_INVALID_BUFFER_USE

Indicates if the bind is to supply the long data in chunks and if the buffer type is non string or binary

CR_UNSUPPORTED_PARAM_TYPE

The conversion is not supported, possibly the buffer_type is illegal or its not from the above list of supported types.

CR_OUT_OF_MEMOR

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

Example

For the usage of mysql_bind_param() refer to the Example from section 9.1.7.5 mysql_execute().

9.1.7.5 mysql_execute()

int mysql_execute(MYSQL_STMT *stmt.

Description

mysql_execute() executes the prepared query associated with the statement handle. The parameter marker values will be sent to server during this call, so that server replaces markers with this newly supplied data.

If the statement is UPDATE,DELETE,or INSERT, the total number of changed/deletd/inserted values can be found by calling mysql_stmt_affected_rows. If this is a result set query, then one must call mysql_fetch() to fetch the data prior to calling any other calls which results in query processing. For more information on how to fetch the statement binary data, refer to section 9.1.7.13 mysql_fetch().

Return Values

mysql_execute() returns the following return values:

Errors

CR_NO_PREPARE_QUERY

No query prepared prior to execution

CR_ALL_PARAMS_NOT_BOUND

Not all parameters data is supplied

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

Example

The following example explains the uasage of mysql_prepare, mysql_param_count, mysql_bind_param, mysql_execute and mysql_stmt_affected_rows().

MYSQL_BIND   bind[3];
MYSQL_STMT   *stmt;
MYSQL        *mysql;
my_ulonglong affected_rows;
long         length;
unsigned int param_count;
int          int_data;
short        small_data;
char         str_data[50], query[255];
my_bool      is_null;

  /* Set autocommit mode to true */
  mysql_autocommit(mysql, 1);
  
  if (mysql_query(mysql,"DROP TABLE IF EXISTS test_table"))
  {
    fprintf(stderr, "\n drop table failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  if (mysql_query(mysql,"CREATE TABLE test_table(col1 INT, col2 varchar(50), \
                                                 col3 smallint,\
                                                 col4 timestamp(14))"))
  {
    fprintf(stderr, "\n create table failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  
  /* Prepare a insert query with 3 parameters */
  strmov(query, "INSERT INTO test_table(col1,col2,col3) values(?,?,?)");
  if(!(stmt = mysql_prepare(mysql, query, strlen(query))))
  {
    fprintf(stderr, "\n prepare, insert failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  fprintf(stdout, "\n prepare, insert successful");

  /* Get the parameter count from the statement */
  param_count= mysql_param_count(stmt);

  fprintf(stdout, "\n total parameters in insert: %d", param_count);
  if (param_count != 3) /* validate parameter count */
  {
    fprintf(stderr, "\n invalid parameter count returned by MySQL");
    exit(0);
  }

  /* Bind the data for the parameters */

  /* INTEGER PART */
  bind[0].buffer_type= MYSQL_TYPE_LONG;
  bind[0].buffer= (char *)&int_data;
  bind[0].is_null= 0;
  bind[0].length= 0;

  /* STRING PART */
  bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
  bind[1].buffer= (char *)str_data;
  bind[1].buffer_length= sizeof(str_data);
  bind[1].is_null= 0;
  bind[1].length= 0;  

  /* SMALLINT PART */
  bind[2].buffer_type= MYSQL_TYPE_SHORT;
  bind[2].buffer= (char *)&small_data;
  bind[2].is_null= &is_null;
  bind[2].length= 0;
  is_null= 0;

  /* Bind the buffers */
  if (mysql_bind_param(stmt, bind))
  {
    fprintf(stderr, "\n param bind failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

  /* Specify the data */
  int_data= 10;             /* integer */
  strcpy(str_data,"MySQL"); /* string  */

  /* INSERT SMALLINT data as NULL */
  is_null= 1;

  /* Execute the insert statement - 1*/
  if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n execute 1 failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    fprintf(stderr, "\n send a bug report to bugs@lists.mysql.com, by asking why this is not working ?");
    exit(0);
  }
    
  /* Get the total rows affected */   
  affected_rows= mysql_stmt_affected_rows(stmt);

  fprintf(stdout, "\n total affected rows: %lld", affected_rows);
  if (affected_rows != 1) /* validate affected rows */
  {
    fprintf(stderr, "\n invalid affected rows by MySQL");
    exit(0);
  }

  /* Re-execute the insert, by changing the values */
  int_data= 1000;             
  strcpy(str_data,"The most popular open source database"); 
  small_data= 1000;         /* smallint */
  is_null= 0;               /* reset NULL */
 
  /* Execute the insert statement - 2*/
  if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n execute 2 failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }
    
  /* Get the total rows affected */   
  affected_rows= mysql_stmt_affected_rows(stmt);

  fprintf(stdout, "\n total affected rows: %lld", affected_rows);
  if (affected_rows != 1) /* validate affected rows */
  {
    fprintf(stderr, "\n invalid affected rows by MySQL");
    exit(0);
  }

  /* Close the statement */
  if (mysql_stmt_close(stmt))
  {
    fprintf(stderr, "\n failed while closing the statement");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }
  
  /* DROP THE TABLE */
  if (mysql_query(mysql,"DROP TABLE test_table"))
  {
    fprintf(stderr, "\n drop table failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  fprintf(stdout, "Success, MySQL prepared statements are working!!!");

Note: For complete example(s) on the usage of prepared statement APIs, refer to tests/client_test.c which comes as a part of source distribution or from the bitkeeper branch.

9.1.7.6 mysql_stmt_affected_rows()

ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt)

Description

Returns total number of rows changed by the last execute statement. May be called immediatlely after mysql_execute() for UPDATE,DELETE,or INSERT statements.For SELECT statements, mysql_stmt_affected rows works like mysql_num_rows().

Return Values

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query, mysql_stmt_affected_rows() was called prior to calling mysql_fetch().

Errors

None.

Example

For the usage of mysql_stmt_affected_rows() refer to the Example from section 9.1.7.5 mysql_execute().

9.1.7.7 mysql_bind_result()

my_bool mysql_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind)

Description

mysql_bind_result() is used to associate, or bind, columns in the resultset to data buffers and length buffers. When mysql_fetch() is called to fetch data, the MySQL client protocol returns the data for the bound columns in the specified buffers.

Note that all columns must be bound prior to calling mysql_fetch() in case of fetching the data to buffers; else mysql_fetch() simply ignores the data fetch; also the buffers should be sufficient enough to hold the data as the protocol doesn't return the data in chunks.

A column can be bound or rebound at any time, even after data has been fetched from the result set. The new binding takes effect the next time mysql_fetch() is called. For example, suppose an application binds the columns in a result set and calls mysql_fetch(). The mysql protocol returns data in the bound buffers. Now suppose the application binds the columns to a different set of buffers, then the protocol does not place the data for the just fetched row in the newly bound buffers. Instead, it does when the next mysql_fetch() is called.

To bind a column, an application calls mysql_bind_result() and passes the type, address, and the address of the length buffer. The supported buffer types are:

• MYSQL_TYPE_TINY
• MYSQL_TYPE_SHORT
• MYSQL_TYPE_LONG
• MYSQL_TYPE_LONGLONG
• MYSQL_TYPE_FLOAT
• MYSQL_TYPE_DOUBLE
• MYSQL_TYPE_TIME
• MYSQL_TYPE_DATE
• MYSQL_TYPE_DATETIME
• MYSQL_TYPE_TIMESTAMP
• MYSQL_TYPE_STRING
• MYSQL_TYPE_VAR_STRING
• MYSQL_TYPE_BLOB
• MYSQL_TYPE_TINY_BLOB
• MYSQL_TYPE_MEDIUM_BLOB
• MYSQL_TYPE_LONG_BLOB

Return Values

Zero if the bind was successful. Non-zero if an error occured.

Errors

CR_NO_PREPARE_STMT

No prepared statement exists

CR_UNSUPPORTED_PARAM_TYPE

The conversion is not supported, possibly the buffer_type is illegal or its not from the list of supported types.

CR_OUT_OF_MEMOR

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

Example

For the usage of mysql_bind_result() refer to the Example from section 9.1.7.13 mysql_fetch()

9.1.7.8 mysql_stmt_store_result()

int mysql_stmt_store_result(MYSQL_STMT *stmt)

Description

You must call mysql_stmt_store_result() for every query that successfully retrieves data(SELECT,SHOW,DESCRIBE,EXPLAIN), and only if you want to buffer the complete result set by the client, so that the subsequent mysql_fetch() call returns buffered data.

You don't have to call mysql_stmt_store_result() for other queries, but it will not harm or cause any notable performance in all cases.You can detect if the query didn't have a result set by checking if mysql_prepare_result() returns 0. For more information refer to section 9.1.7.3 mysql_prepare_result().

Return Values

Zero if the results are buffered successfully or Non Zero in case of an error.

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

9.1.7.9 mysql_stmt_data_seek()

void mysql_stmt_data_seek(MYSQL_STMT *stmt, my_ulonglong offset)

Description

Seeks to an arbitrary row in a statement result set. This requires that the statement result set structure contains the entire result of the last executed query, so mysql_stmt_data_seek() may be used in conjunction only with mysql_stmt_store_result().

The offset should be a value in the range from 0 to mysql_stmt_num_rows(stmt)-1.

Return Values

None.

Errors

None.

9.1.7.10 mysql_stmt_row_seek()

MYSQL_ROW_OFFSET mysql_stmt_row_seek(MYSQL_STMT *stmt, MYSQL_ROW_OFFSET offset)

Description

Sets the row cursor to an arbitrary row in a statement result set. This requires that the result set structure contains the entire result of the query, so mysql_stmt_row_seek() may be used in conjunction only with mysql_stmt_store_result().

The offset should be a value returned from a call to mysql_stmt_row_tell() or to mysql_stmt_row_seek(). This value is not simply a row number; if you want to seek to a row within a result set using a row number, use mysql_stmt_data_seek() instead.

Return Values

The previous value of the row cursor. This value may be passed to a subsequent call to mysql_stmt_row_seek().

Errors

None.

9.1.7.11 mysql_stmt_row_tell()

MYSQL_ROW_OFFSET mysql_stmt_row_tell(MYSQL_STMT *stmt)

Description

Returns the current position of the row cursor for the last mysql_fetch(). This value can be used as an argument to mysql_stmt_row_seek().

You should use mysql_stmt_row_tell() only after mysql_stmt_store_result().

Return Values

The current offset of the row cursor.

Errors

None.

9.1.7.12 mysql_stmt_num_rows()

my_ulonglong mysql_stmt_num_rows(MYSQL_STMT *stmt)

Description

Returns the number of rows in the result set.

The use of mysql_stmt_num_rows() depends on whether you used mysql_stmt_store_result() to buffer the entire result set in the statement handle or not.

If you use mysql_stmt_store_result(), mysql_stmt_num_rows() may be called immediately.

Return Values

The number of rows in the result set.

Errors

None.

9.1.7.13 mysql_fetch()

int mysql_fetch(MYSQL_STMT *stmt)

Description

mysql_fetch() returns the next rowset in the result set. It can be called only while the result set exists, that is, after a call to mysql_execute() that creates a result set or after mysql_stmt_store_result(), which is called after mysql_execute() to buffer the entire result set.

If row buffers are bound using mysql_bind_result(), it returns the data in those buffers for all the columns in the current row set and the lengths are returned to the length pointer.

Note that, all columns must be bound by the application.

If the data fetched is a NULL data, then the is_null value from MYSQL_BIND contains TRUE, 1, else the data and its length is returned to *buffer and *length variables based on the buffer type specified by the application. All numeric, float and double types have the fixed length(in bytes) as listed below:

where *data_length is nothing but the 'Actual length of the data'.

Return Values

Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

CR_UNSUPPORTED_PARAM_TYPE

If the buffer type is MYSQL_TYPE_DATE,DATETIME,TIME,or TIMESTAMP; and if the data type is not DATE, TIME, DATETIME or TIMESTAMP.

All other unsupported conversion errors are returned from mysql_bind_result().

Example

The following example explains the usage of mysql_prepare_result, mysql_bind_result(), and mysql_fetch()

  
MYSQL_STMT *stmt;  
MYSQL_BIND bind[2];
MYSQL_RES  *result;
int        int_data;
long       int_length, str_length;
char       str_data[50];
my_bool    is_null[2];

  query= "SELECT col1, col2 FROM test_table WHERE col1= 10)");
  if (!(stmt= mysql_prepare(mysql, query, strlen(query)))
  {
    fprintf(stderr, "\n prepare failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }

  /* Get the fields meta information */
  if (!(result= mysql_prepare_result(stmt)))
  {
    fprintf(stderr, "\n prepare_result failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

  fprintf(stdout, "Total fields: %ld", mysql_num_fields(result));

  if (mysql_num_fields(result) != 2)
  {
    fprintf(stderr, "\n prepare returned invalid field count");
    exit(0);
  }

  /* Execute the SELECT query */
  if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n execute failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));  
    exit(0);
  }

  /* Bind the result data buffers */
  bind[0].buffer_type= MYSQL_TYPE_LONG;
  bind[0].buffer= (char *)&int_data; 
  bind[0].is_null= &is_null[0];
  bind[0].length= &int_length;

  bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
  bind[1].buffer= (void *)str_data;
  bind[1].buffer_length= 20;
  bind[1].is_null= &is_null[1];
  bind[1].length= &str_length;

  if (mysql_bind_result(stmt, bind))
  {
    fprintf(stderr, "\n bind_result failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }  

  /* Now fetch data to buffers */
  if (mysql_fetch(stmt))
  {
    fprintf(stderr, "\n fetch failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }  
  
  if (is_null[0])
    fprintf(stdout, "\n Col1 data is NULL");
  else
    fprintf(stdout, "\n Col1: %d, length: %ld", int_data, int_length);
   
   if (is_null[1])
     fprintf(stdout, "\n Col2 data is NULL");
   else
    fprintf(stdout, "\n Col2: %s, length: %ld", str_data, str_length);

   
  /* call mysql_fetch again */
  if (mysql_fetch(stmt) |= MYSQL_NO_DATA)
  {
    fprintf(stderr, "\n fetch return more than one row);
    exit(0);
  }  

  /* Free the prepare result meta information */
  mysql_free_result(result);

  /* Free the statement handle */
  if (mysql_stmt_free(stmt))
  {
    fprintf(stderr, "\n failed to free the statement handle);
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }  
 

9.1.7.14 mysql_send_long_data()

int mysql_send_long_data(MYSQL_STMT *stmt, unsigned int parameter_number, const char *data, ulong length)

Description

Allows an application to send the data in pieces or chunks to server. This function can be used to send character or binary data values in parts to a column(it must be a text or blob) with a character or binary data type.

The data is a pointer to buffer containing the actual data for the parameter represendted by parameter_number. The length indicates the amount of data to be sent in bytes.

Return Values

Zero if the data is sent successfully to server. Non-zero if an error occured.

Errors

CR_INVALID_PARAMETER_NO

Invalid parameter number

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away

CR_OUT_OF_MEMOR

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

Example

The following example explains how to send the data in chunks to text column:

  
MYSQL_BIND bind[1];
long       length;

  query= "INSERT INTO test_long_data(text_column) VALUES(?)");
  if (!mysql_prepare(mysql, query, strlen(query))
  {
    fprintf(stderr, "\n prepare failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
   memset(bind, 0, sizeof(bind));
   bind[0].buffer_type= MYSQL_TYPE_STRING;
   bind[0].length= &length;
   bind[0].is_null= 0;

  /* Bind the buffers */
  if (mysql_bind_param(stmt, bind))
  {
    fprintf(stderr, "\n param bind failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   /* Supply data in chunks to server */
   if (!mysql_send_long_data(stmt,1,"MySQL",5))
  {
    fprintf(stderr, "\n send_long_data failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   /* Supply the next piece of data */
   if (mysql_send_long_data(stmt,1," - The most popular open source database",40))
  {
    fprintf(stderr, "\n send_long_data failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   /* Now, execute the query */
   if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n mysql_execute failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   This inserts the data, "MySQL - The most popular open source database" 
   to the field 'text_column'.  

9.1.7.15 mysql_stmt_close()

my_bool mysql_stmt_close(MYSQL_STMT *)

Description

Closes the prepared statement. mysql_stmt_close() also deallocates the statement handle pointed to by stmt.

If the current query results are pending or un-read; this cancels the query results; so that next call can be executed.

Return Values

Zero if the statement was freed successfully. Non-zero if an error occured.

Errors

CR_SERVER_GONE_ERROR

The MySQL server has gone away

CR_UNKNOWN_ERROR

An unkown error occured

Example

For the usage of mysql_stmt_close() refer to the Example from section 9.1.7.5 mysql_execute().

9.1.7.16 mysql_stmt_errno()

unsigned int mysql_stmt_errno(MYSQL_STMT *stmt)

Description

For the statement specified by stmt, mysql_stmt_errno() returns the error code for the most recently invoked statement API function that can succeed or fail. A return value of zero means that no error occured. Client error message numbers are listed in the MySQL errmsg.h header file. Server error message numbers are listed in mysqld_error.h. In the MySQL source distribution you can find a complete list of error messages and error numbers in the file Docs/mysqld_error.txt

Return Values

An error code value. Zero if no error occured.

Errors

None

9.1.7.17 mysql_stmt_error()

char *mysql_stmt_error(MYSQL_STMT *stmt)

Description

For the statement specified by stmt, mysql_stmt_error() returns the error message for the most recently invoked statement API that can succeed or fail. An empty string ("") is returned if no error occured. This means the following two sets are equivalent:

if (mysql_stmt_errno(stmt))
{
  // an error occured
}

if (mysql_stmt_error(stmt))
{
  // an error occured
}

The language of the client error messages many be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages.

Return Values

A character string that describes the error. An empry string if no error occured.

Errors

None

9.1.7.18 mysql_stmt_sqlstate()

const char *mysql_stmt_sqlstate(MYSQL_STMT *stmt)

Description

Works like the corresponding mysql_sqlstate function for prepared statements. See section 9.1.3.49 mysql_sqlstate().

Returns the SQLSTATE error code for the last error for the prepared statement.

This function was added to MySQL 4.1.1.

See also

See section 9.1.3.12 mysql_errno(). See section 9.1.3.13 mysql_error(). See section 9.1.3.49 mysql_sqlstate().

9.1.8 C API Handling multiple query executions

From version 4.1, MySQL supports the multi query execution in a single command. In order to do this, you must set the client flag CLIENT_MULTI_QUERIES option when opening the connection.

By default mysql_query() or mysql_real_query() returns only the first query status and the subsequent queries status can be processed using mysql_more_results() and mysql_next_result().

  /* Connect to server with option CLIENT_MULTI_QUERIES */
  mysql_real_connect(..., CLIENT_MULTI_QUERIES);

  /* Now execute multiple queries */
  mysql_query(mysql,"DROP TABLE IF EXISTS test_table;\
                     CREATE TABLE test_table(id INT);\
                     INSERT INTO test_table VALUES(10);\
                     UPDATE test_table SET id=20 WHERE id=10;\
                     SELECT * FROM test_table;\
                     DROP TABLE test_table";
  while (mysql_more_results(mysql))
  {
    /* Process all results */
    mysql_next_result(mysql);
    ...
    printf("total affected rows: %lld", mysql_affected_rows(mysql));
    ...
    if ((result= mysql_store_result(mysql))
    {
      /* Returned a result set, process it */
    }
  }

9.1.9 C API Handling DATE, TIME and TIMESTAMP

Using the new binary protocol from MySQL 4.1 and above, one can send and receive the DATE, TIME and TIMESTAMP data using the MYSQL_TIME structure.

MYSQL_TIME structure consites of the following members:

• year
• month
• day
• hour
• minute
• second
• second_part

In order to send the data, one must use the prepared statements through mysql_prepare() and mysql_execute(); and must bind the parameter using type as MYSQL_TYPE_DATE inorder to process date value, MYSQL_TYPE_TIME for time and MYSQL_TYPE_DATETIME or MYSQL_TYPE_TIMESTAMP for datetime/timestamp using mysql_bind_param() when sending and mysql_bind_results() while receiving the data.

Here is a simple example; which inserts the DATE, TIME and TIMESTAMP data.

MYSQL_TIME  ts;
MYSQL_BIND  bind[3];
MYSQL_STMT  *stmt;
  
  strmov(query, "INSERT INTO test_table(date_field, time_field,
                                        timestamp_field) VALUES(?,?,?");

  stmt= mysql_prepare(mysql, query, strlen(query))); 

  /* setup input buffers for all 3 parameters */
  bind[0].buffer_type= MYSQL_TYPE_DATE;
  bind[0].buffer= (char *)&ts;  
  bind[0].is_null= 0;
  bind[0].length= 0;
  ..
  bind[1]= bind[2]= bind[0];
  ..

  mysql_bind_param(stmt, bind);

  /* supply the data to be sent is the ts structure */
  ts.year= 2002;
  ts.month= 02;
  ts.day= 03;

  ts.hour= 10;
  ts.minute= 45;
  ts.second= 20;

  mysql_execute(stmt);
  .. 

9.1.10 C API Threaded Function Descriptions

You need to use the following functions when you want to create a threaded client. See section 9.1.14 How to Make a Threaded Client.

9.1.10.1 my_init()

void my_init(void)

Description

This function needs to be called once in the program before calling any MySQL function. This initialises some global variables that MySQL needs. If you are using a thread-safe client library, this will also call mysql_thread_init() for this thread.

This is automatically called by mysql_init(), mysql_server_init() and mysql_connect().

Return Values

None.

9.1.10.2 mysql_thread_init()

my_bool mysql_thread_init(void)

Description

This function needs to be called for each created thread to initialise thread-specific variables.

This is automatically called by my_init() and mysql_connect().

Return Values

None.

9.1.10.3 mysql_thread_end()

void mysql_thread_end(void)

Description

This function needs to be called before calling pthread_exit() to free memory allocated by mysql_thread_init().

Note that this function is not invoked automatically by the client library. It must be called explicitly to avoid a memory leak.

Return Values

None.

9.1.10.4 mysql_thread_safe()

unsigned int mysql_thread_safe(void)

Description

This function indicates whether the client is compiled as thread-safe.

Return Values

1 is the client is thread-safe, 0 otherwise.

9.1.11 C API Embedded Server Function Descriptions

You must use the following functions if you want to allow your application to be linked against the embedded MySQL server library. See section 9.1.15 libmysqld, the Embedded MySQL Server Library.

If the program is linked with -lmysqlclient instead of -lmysqld, these functions do nothing. This makes it possible to choose between using the embedded MySQL server and a stand-alone server without modifying any code.

9.1.11.1 mysql_server_init()

int mysql_server_init(int argc, char **argv, char **groups)

Description

This function must be called once in the program using the embedded server before calling any other MySQL function. It starts up the server and initialises any subsystems (mysys, InnoDB, etc.) that the server uses. If this function is not called, the program will crash. If you are using the DBUG package that comes with MySQL, you should call this after you have called MY_INIT().

The argc and argv arguments are analogous to the arguments to main(). The first element of argv is ignored (it typically contains the program name). For convenience, argc may be 0 (zero) if there are no command-line arguments for the server. mysql_server_init() makes a copy of the arguments so it's safe to destroy argv or groups after the call.

The NULL-terminated list of strings in groups selects which groups in the option files will be active. See section 4.1.2 `my.cnf' Option Files. For convenience, groups may be NULL, in which case the [server] and [emedded] groups will be active.

Example

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

static char *server_args[] = {
  "this_program",       /* this string is not used */
  "--datadir=.",
  "--key_buffer_size=32M"
};
static char *server_groups[] = {
  "embedded",
  "server",
  "this_program_SERVER",
  (char *)NULL
};

int main(void) {
  mysql_server_init(sizeof(server_args) / sizeof(char *),
                    server_args, server_groups);

  /* Use any MySQL API functions here */

  mysql_server_end();

  return EXIT_SUCCESS;
}

Return Values

0 if okay, 1 if an error occurred.

9.1.11.2 mysql_server_end()

void mysql_server_end(void)

Description

This function must be called once in the program after all other MySQL functions. It shuts down the embedded server.

Return Values

None.

9.1.12 Common questions and problems when using the C API

9.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?

It is possible for mysql_store_result() to return NULL following a successful call to mysql_query(). When this happens, it means one of the following conditions occurred:

• There was a malloc() failure (for example, if the result set was too large).
• The data couldn't be read (an error occurred on the connection).
• The query returned no data (for example, it was an INSERT, UPDATE, or DELETE).

You can always check whether the statement should have produced a non-empty result by calling mysql_field_count(). If mysql_field_count() returns zero, the result is empty and the last query was a statement that does not return values (for example, an INSERT or a DELETE). If mysql_field_count() returns a non-zero value, the statement should have produced a non-empty result. See the description of the mysql_field_count() function for an example.

You can test for an error by calling mysql_error() or mysql_errno().

9.1.12.2 What Results Can I Get From a Query?

In addition to the result set returned by a query, you can also get the following information:

• mysql_affected_rows() returns the number of rows affected by the last query when doing an INSERT, UPDATE, or DELETE. An exception is that if DELETE is used without a WHERE clause, the table is re-created empty, which is much faster! In this case, mysql_affected_rows() returns zero for the number of records affected.
• mysql_num_rows() returns the number of rows in a result set. With mysql_store_result(), mysql_num_rows() may be called as soon as mysql_store_result() returns. With mysql_use_result(), mysql_num_rows() may be called only after you have fetched all the rows with mysql_fetch_row().
• mysql_insert_id() returns the ID generated by the last query that inserted a row into a table with an AUTO_INCREMENT index. See section 9.1.3.31 mysql_insert_id().
• Some queries (LOAD DATA INFILE ..., INSERT INTO ... SELECT ..., UPDATE) return additional information. The result is returned by mysql_info(). See the description for mysql_info() for the format of the string that it returns. mysql_info() returns a NULL pointer if there is no additional information.

9.1.12.3 How Can I Get the Unique ID for the Last Inserted Row?

If you insert a record in a table containing a column that has the AUTO_INCREMENT attribute, you can get the most recently generated ID by calling the mysql_insert_id() function.

You can also retrieve the ID by using the LAST_INSERT_ID() function in a query string that you pass to mysql_query().

You can check if an AUTO_INCREMENT index is used by executing the following code. This also checks if the query was an INSERT with an AUTO_INCREMENT index:

if (mysql_error(&mysql)[0] == 0 &&
    mysql_num_fields(result) == 0 &&
    mysql_insert_id(&mysql) != 0)
{
    used_id = mysql_insert_id(&mysql);
}

The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).

If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');              # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

9.1.12.4 Problems Linking with the C API

When linking with the C API, the following errors may occur on some systems:

gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl

Undefined        first referenced
 symbol          in file
floor            /usr/local/lib/mysql/libmysqlclient.a(password.o)
ld: fatal: Symbol referencing errors. No output written to client

If this happens on your system, you must include the math library by adding -lm to the end of the compile/link line.

9.1.13 Building Client Programs

If you compile MySQL clients that you've written yourself or that you obtain from a third-party, they must be linked using the -lmysqlclient -lz option on the link command. You may also need to specify a -L option to tell the linker where to find the library. For example, if the library is installed in `/usr/local/mysql/lib', use -L/usr/local/mysql/lib -lmysqlclient -lz on the link command.

For clients that use MySQL header files, you may need to specify a -I option when you compile them (for example, -I/usr/local/mysql/include), so the compiler can find the header files.

To make the above simpler on Unix we have provied the mysql_config script for you. See section 4.8.9 mysql_config, Get compile options for compiling clients.

You can use this to compile a MySQL client by as follows:

CFG=/usr/local/mysql/bin/mysql_config
sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"

The sh -c is need to get the shell to not threat the output from mysql_config as one word.

9.1.14 How to Make a Threaded Client

The client library is almost thread-safe. The biggest problem is that the subroutines in `net.c' that read from sockets are not interrupt safe. This was done with the thought that you might want to have your own alarm that can break a long read to a server. If you install interrupt handlers for the SIGPIPE interrupt, the socket handling should be thread-safe.

In the older binaries we distribute on our web site (http://www.mysql.com/), the client libraries are not normally compiled with the thread-safe option (the Windows binaries are by default compiled to be thread-safe). Newer binary distributions should have both a normal and a thread-safe client library.

To get a threaded client where you can interrupt the client from other threads and set timeouts when talking with the MySQL server, you should use the -lmysys, -lmystrings, and -ldbug libraries and the net_serv.o code that the server uses.

If you don't need interrupts or timeouts, you can just compile a thread-safe client library (mysqlclient_r) and use this. See section 9.1 MySQL C API. In this case you don't have to worry about the net_serv.o object file or the other MySQL libraries.

When using a threaded client and you want to use timeouts and interrupts, you can make great use of the routines in the `thr_alarm.c' file. If you are using routines from the mysys library, the only thing you must remember is to call my_init() first! See section 9.1.10 C API Threaded Function Descriptions.

All functions except mysql_real_connect() are by default thread-safe. The following notes describe how to compile a thread-safe client library and use it in a thread-safe manner. (The notes below for mysql_real_connect() actually apply to mysql_connect() as well, but because mysql_connect() is deprecated, you should be using mysql_real_connect() anyway.)

To make mysql_real_connect() thread-safe, you must recompile the client library with this command:

shell> ./configure --enable-thread-safe-client

This will create a thread-safe client library libmysqlclient_r. (Assuming your OS has a thread-safe gethostbyname_r() function.) This library is thread-safe per connection. You can let two threads share the same connection with the following caveats:

• Two threads can't send a query to the MySQL server at the same time on the same connection. In particular, you have to ensure that between a mysql_query() and mysql_store_result() no other thread is using the same connection.
• Many threads can access different result sets that are retrieved with mysql_store_result().
• If you use mysql_use_result, you have to ensure that no other thread is using the same connection until the result set is closed. However, it really is best for threaded clients that share the same connection to use mysql_store_result().
• If you want to use multiple threads on the same connection, you must have a mutex lock around your mysql_query() and mysql_store_result() call combination. Once mysql_store_result() is ready, the lock can be released and other threads may query the same connection.
• If you program with POSIX threads, you can use pthread_mutex_lock() and pthread_mutex_unlock() to establish and release a mutex lock.

You need to know the following if you have a thread that is calling MySQL functions which did not create the connection to the MySQL database:

When you call mysql_init() or mysql_connect(), MySQL will create a thread-specific variable for the thread that is used by the debug library (among other things).

If you call a MySQL function, before the thread has called mysql_init() or mysql_connect(), the thread will not have the necessary thread-specific variables in place and you are likely to end up with a core dump sooner or later.

The get things to work smoothly you have to do the following:

1. Call my_init() at the start of your program if it calls any other MySQL function before calling mysql_real_connect().
2. Call mysql_thread_init() in the thread handler before calling any MySQL function.
3. In the thread, call mysql_thread_end() before calling pthread_exit(). This will free the memory used by MySQL thread-specific variables.

You may get some errors because of undefined symbols when linking your client with libmysqlclient_r. In most cases this is because you haven't included the thread libraries on the link/compile line.

9.1.15 libmysqld, the Embedded MySQL Server Library

9.1.15.1 Overview of the Embedded MySQL Server Library

The embedded MySQL server library makes it possible to run a full-featured MySQL server inside the client application. The main benefits are increased speed and more simple management for embedded applications.

The API is identical for the embedded MySQL version and the client/server version. To change an old threaded application to use the embedded library, you normally only have to add calls to the following functions:

Then you must link your code with `libmysqld.a' instead of `libmysqlclient.a'.

The above mysql_server_xxx functions are also included in `libmysqlclient.a' to allow you to change between the embedded and the client/server version by just linking your application with the right library. See section 9.1.11.1 mysql_server_init().

9.1.15.2 Compiling Programs with libmysqld

To get a libmysqld library you should configure MySQL with the --with-embedded-server option.

When you link your program with libmysqld, you must also include the system-specific pthread libraries and some libraries that the MySQL server uses. You can get the full list of libraries by executing mysql_config --libmysqld-libs.

The correct flags for compiling and linking a threaded program must be used, even if you do not directly call any thread functions in your code.

9.1.15.3 Restrictions when using the Embedded MySQL Server

The embedded server has the following limitations:

• No support for ISAM tables. (This is mainly done to make the library smaller)
• No user defined functions (UDFs).
• No stack trace on core dump.
• No internal RAID support. (This is not normally needed as most OS has nowadays support for big files).
• You can set this up as a server or a master (no replication).
• You can't connect to the embedded server from an outside process with sockets or TCP/IP.

Some of these limitations can be changed by editing the `mysql_embed.h' include file and recompiling MySQL.

9.1.15.4 Using Option Files with the Embedded Server

The following is the recommended way to use option files to make it easy to switch between a client/server application and one where MySQL is embedded. See section 4.1.2 `my.cnf' Option Files.

• Put common options in the [server] section. These will be read by both MySQL versions.
• Put client/server-specific options in the [mysqld] section.
• Put embedded MySQL-specific options in the [embedded] section.
• Put application-specific options in a [ApplicationName_SERVER] section.

9.1.15.5 Things left to do in Embedded Server (TODO)

• We are going to provide options to leave out some parts of MySQL to make the library smaller.
• There is still a lot of speed optimisation to do.
• Errors are written to stderr. We will add an option to specify a filename for these.
• We have to change InnoDB to not be so verbose when using in the embedded version.

9.1.15.6 A Simple Embedded Server Example

This example program and makefile should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes will be needed. This example is designed to give enough details to understand the problem, without the clutter that is a necessary part of a real application.

To try out the example, create an `test_libmysqld' directory at the same level as the mysql-4.0 source directory. Save the `test_libmysqld.c' source and the `GNUmakefile' in the directory, and run GNU `make' from inside the `test_libmysqld' directory.

`test_libmysqld.c'

/*
 * A simple example client, using the embedded MySQL server library
 */

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

MYSQL *db_connect(const char *dbname);
void db_disconnect(MYSQL *db);
void db_do_query(MYSQL *db, const char *query);

const char *server_groups[] = {
  "test_libmysqld_SERVER", "embedded", "server", NULL
};

int
main(int argc, char **argv)
{
  MYSQL *one, *two;

  /* mysql_server_init() must be called before any other mysql
   * functions.
   *
   * You can use mysql_server_init(0, NULL, NULL), and it will
   * initialise the server using groups = {
   *   "server", "embedded", NULL
   *  }.
   *
   * In your $HOME/.my.cnf file, you probably want to put:

[test_libmysqld_SERVER]
language = /path/to/source/of/mysql/sql/share/english

   * You could, of course, modify argc and argv before passing
   * them to this function.  Or you could create new ones in any
   * way you like.  But all of the arguments in argv (except for
   * argv[0], which is the program name) should be valid options
   * for the MySQL server.
   *
   * If you link this client against the normal mysqlclient
   * library, this function is just a stub that does nothing.
   */
  mysql_server_init(argc, argv, (char **)server_groups);

  one = db_connect("test");
  two = db_connect(NULL);

  db_do_query(one, "SHOW TABLE STATUS");
  db_do_query(two, "SHOW DATABASES");

  mysql_close(two);
  mysql_close(one);

  /* This must be called after all other mysql functions */
  mysql_server_end();

  exit(EXIT_SUCCESS);
}

static void
die(MYSQL *db, char *fmt, ...)
{
  va_list ap;
  va_start(ap, fmt);
  vfprintf(stderr, fmt, ap);
  va_end(ap);
  (void)putc('\n', stderr);
  if (db)
    db_disconnect(db);
  exit(EXIT_FAILURE);
}

MYSQL *
db_connect(const char *dbname)
{
  MYSQL *db = mysql_init(NULL);
  if (!db)
    die(db, "mysql_init failed: no memory");
  /*
   * Notice that the client and server use separate group names.
   * This is critical, because the server will not accept the
   * client's options, and vice versa.
   */
  mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test_libmysqld_CLIENT");
  if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0))
    die(db, "mysql_real_connect failed: %s", mysql_error(db));

  return db;
}

void
db_disconnect(MYSQL *db)
{
  mysql_close(db);
}

void
db_do_query(MYSQL *db, const char *query)
{
  if (mysql_query(db, query) != 0)
    goto err;

  if (mysql_field_count(db) > 0)
  {
    MYSQL_RES   *res;
    MYSQL_ROW    row, end_row;
    int num_fields;

    if (!(res = mysql_store_result(db)))
      goto err;
    num_fields = mysql_num_fields(res);
    while ((row = mysql_fetch_row(res)))
    {
      (void)fputs(">> ", stdout);
      for (end_row = row + num_fields; row < end_row; ++row)
        (void)printf("%s\t", row ? (char*)*row : "NULL");
      (void)fputc('\n', stdout);
    }
    (void)fputc('\n', stdout);
  }
  else
    (void)printf("Affected rows: %lld\n", mysql_affected_rows(db));

  mysql_free_result(res);

  return;

err:
  die(db, "db_do_query failed: %s [%s]", mysql_error(db), query);
}

`GNUmakefile'

# This assumes the MySQL software is installed in /usr/local/mysql
inc      := /usr/local/mysql/include/mysql
lib      := /usr/local/mysql/lib

# If you have not installed the MySQL software yet, try this instead
#inc      := $(HOME)/mysql-4.0/include
#lib      := $(HOME)/mysql-4.0/libmysqld

CC       := gcc
CPPFLAGS := -I$(inc) -D_THREAD_SAFE -D_REENTRANT
CFLAGS   := -g -W -Wall
LDFLAGS  := -static
# You can change -lmysqld to -lmysqlclient to use the
# client/server library
LDLIBS    = -L$(lib) -lmysqld -lz -lm -lcrypt

ifneq (,$(shell grep FreeBSD /COPYRIGHT 2>/dev/null))
# FreeBSD
LDFLAGS += -pthread
else
# Assume Linux
LDLIBS += -lpthread
endif

# This works for simple one-file test programs
sources := $(wildcard *.c)
objects := $(patsubst %c,%o,$(sources))
targets := $(basename $(sources))

all: $(targets)

clean:
        rm -f $(targets) $(objects) *.core

9.1.15.7 Licensing the Embedded Server

The MySQL source code is covered by the GNU GPL license (see section H GNU General Public License). One result of this is that any program which includes, by linking with libmysqld, the MySQL source code must be released as free software (under a license compatible with the GPL).

We encourage everyone to promote free software by releasing code under the GPL or a compatible license. For those who are not able to do this, another option is to purchase a commercial license for the MySQL code from MySQL AB. For details, please see section 1.4.3 MySQL Licenses.

9.2 MySQL ODBC Support

MySQL provides support for ODBC by means of the MyODBC program. This chapter will teach you how to install MyODBC, and how to use it. Here, you will also find a list of common programs that are known to work with MyODBC.

9.2.1 How To Install MyODBC

MyODBC 2.50 is a 32-bit ODBC 2.50 specification level 0 (with level 1 and level 2 features) driver for connecting an ODBC-aware application to MySQL. MyODBC works on Windows 9x/Me/NT/2000/XP and most Unix platforms. MyODBC 3.51 is an enhanced version with ODBC 3.5x specification level 1 (complete core API + level 2 features).

MyODBC is Open Source, and you can find the newest version at http://www.mysql.com/downloads/api-myodbc.html. Please note that the 2.50.x versions are LGPL licensed, whereas the 3.51.x versions are GPL licensed.

If you have problem with MyODBC and your program also works with OLEDB, you should try the OLEDB driver.

Normally you only need to install MyODBC on Windows machines. You only need MyODBC for Unix if you have a program like ColdFusion that is running on the Unix machine and uses ODBC to connect to the databases.

If you want to install MyODBC on a Unix box, you will also need an ODBC manager. MyODBC is known to work with most of the Unix ODBC managers.

To install MyODBC on Windows, you should download the appropriate MyODBC `.zip' file, unpack it with WinZIP or some similar program, and execute the `SETUP.EXE' file.

On Windows/NT/XP you may get the following error when trying to install MyODBC:

An error occurred while copying C:\WINDOWS\SYSTEM\MFC30.DLL. Restart
Windows and try installing again (before running any applications which
use ODBC)

The problem in this case is that some other program is using ODBC and because of how Windows is designed, you may not in this case be able to install a new ODBC drivers with Microsoft's ODBC setup program. In most cases you can continue by just pressing Ignore to copy the rest of the MyODBC files and the final installation should still work. If this doesn't work, the solution is to reboot your computer in ``safe mode`` (Choose this by pressing F8 just before your machine starts Windows during rebooting), install MyODBC, and reboot to normal mode.

• To make a connection to a Unix box from a Windows box, with an ODBC application (one that doesn't support MySQL natively), you must first install MyODBC on the Windows machine.
• The user and Windows machine must have the access privileges to the MySQL server on the Unix machine. This is set up with the GRANT command. See section 4.3.1 GRANT and REVOKE Syntax.
• You must create an ODBC DSN entry as follows:
  • Open the Control Panel on the Windows machine.
  • Double-click the ODBC Data Sources 32-bit icon.
  • Click the tab User DSN.
  • Click the button Add.
  • Select MySQL in the screen Create New Data Source and click the Finish button.
  • The MySQL Driver default configuration screen is shown. See section 9.2.2 How to Fill in the Various Fields in the ODBC Administrator Program.
• Now start your application and select the ODBC driver with the DSN you specified in the ODBC administrator.

Notice that there are other configuration options on the screen of MySQL (trace, don't prompt on connect, etc) that you can try if you run into problems.

9.2.2 How to Fill in the Various Fields in the ODBC Administrator Program

There are three possibilities for specifying the server name on Windows95:

• Use the IP address of the server.
• Add a file `\windows\lmhosts' with the following information:

  ip hostname
  

  For example:

  194.216.84.21 my_hostname
  

• Configure the PC to use DNS.

Example of how to fill in the ODBC setup:

Windows DSN name:   test
Description:        This is my test database
MySql Database:     test
Server:             194.216.84.21
User:               monty
Password:           my_password
Port:

The value for the Windows DSN name field is any name that is unique in your Windows ODBC setup.

You don't have to specify values for the Server, User, Password, or Port fields in the ODBC setup screen. However, if you do, the values will be used as the defaults later when you attempt to make a connection. You have the option of changing the values at that time.

If the port number is not given, the default port (3306) is used.

If you specify the option Read options from C:\my.cnf, the groups client and odbc will be read from the `C:\my.cnf' file. You can use all options that are usable by mysql_options(). See section 9.1.3.39 mysql_options().

9.2.3 Connect parameters for MyODBC

One can specify the following parameters for MyODBC on the [Servername] section of an `ODBC.INI' file or through the InConnectionString argument in the SQLDriverConnect() call.

The option argument is used to tell MyODBC that the client isn't 100% ODBC compliant. On Windows, one normally sets the option flag by toggling the different options on the connection screen but one can also set this in the option argument. The following options are listed in the same order as they appear in the MyODBC connect screen:

If you want to have many options, you should add the above flags! For example setting option to 12 (4+8) gives you debugging without package limits!

The default `MYODBC.DLL' is compiled for optimal performance. If you want to debug MyODBC (for example to enable tracing), you should instead use `MYODBCD.DLL'. To install this file, copy `MYODBCD.DLL' over the installed `MYODBC.DLL' file.

9.2.4 How to Report Problems with MyODBC

MyODBC has been tested with Access, Admndemo.exe, C++-Builder, Borland Builder 4, Centura Team Developer (formerly Gupta SQL/Windows), ColdFusion (on Solaris and NT with svc pack 5), Crystal Reports, DataJunction, Delphi, ERwin, Excel, iHTML, FileMaker Pro, FoxPro, Notes 4.5/4.6, SBSS, Perl DBD-ODBC, Paradox, Powerbuilder, Powerdesigner 32 bit, VC++, and Visual Basic.

If you know of any other applications that work with MyODBC, please send mail to myodbc@lists.mysql.com about this!

With some programs you may get an error like: Another user has modifies the record that you have modified. In most cases this can be solved by doing one of the following things:

• Add a primary key for the table if there isn't one already.
• Add a timestamp column if there isn't one already.
• Only use double float fields. Some programs may fail when they compare single floats.

If the above doesn't help, you should do a MyODBC trace file and try to figure out why things go wrong.

9.2.5 Programs Known to Work with MyODBC

Most programs should work with MyODBC, but for each of those listed here, we have tested it ourselves or received confirmation from some user that it works:

Program

Comment

Access

To make Access work:

• If you are using Access 2000, you should get and install the newest (version 2.6 or above) Microsoft MDAC (Microsoft Data Access Components) from http://www.microsoft.com/data/. This will fix the following bug in Access: when you export data to MySQL, the table and column names aren't specified. Another way to around this bug is to upgrade to MyODBC Version 2.50.33 and MySQL Version 3.23.x, which together provide a workaround for this bug! You should also get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5) which can be found here http://support.microsoft.com/support/kb/articles/Q 239/1/14.ASP. This will fix some cases where columns are marked as #deleted# in Access. Note that if you are using MySQL Version 3.22, you must to apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and above to go around this problem.
• For all Access versions, you should enable the MyODBC option flag Return matching rows. For Access 2.0, you should additionally enable Simulate ODBC 1.0.
• You should have a timestamp in all tables you want to be able to update. For maximum portability TIMESTAMP(14) or simple TIMESTAMP is recommended instead of other TIMESTAMP(X) variations.
• You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
• Only use DOUBLE float fields. Access fails when comparing with single floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you can't find or update rows.
• If you are linking a table through MyODBC, which has BIGINT as one of the column, then the results will be displayed as #DELETED. The work around solution is:
  • Have one more dummy column with TIMESTAMP as the data type, preferably TIMESTAMP(14).
  • Check the 'Change BIGINT columns to INT' in connection options dialog in ODBC DSN Administrator
  • Delete the table link from access and re-create it.
  It still displays the previous records as #DELETED#, but newly added/updated records will be displayed properly.
• If you still get the error Another user has changed your data after adding a TIMESTAMP column, the following trick may help you: Don't use table data sheet view. Create instead a form with the fields you want, and use that form data sheet view. You should set the DefaultValue property for the TIMESTAMP column to NOW(). It may be a good idea to hide the TIMESTAMP column from view so your users are not confused.
• In some cases, Access may generate illegal SQL queries that MySQL can't understand. You can fix this by selecting "Query|SQLSpecific|Pass-Through" from the Access menu.
• Access on NT will report BLOB columns as OLE OBJECTS. If you want to have MEMO columns instead, you should change the column to TEXT with ALTER TABLE.
• Access can't always handle DATE columns properly. If you have a problem with these, change the columns to DATETIME.
• If you have in Access a column defined as BYTE, Access will try to export this as TINYINT instead of TINYINT UNSIGNED. This will give you problems if you have values > 127 in the column!

ADO

When you are coding with the ADO API and MyODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer will return for the RecordCount Property a result of -1. To have the right value, you need to set this property to adUseClient, like is showing in the VB code here:

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCount

myrs.Close
myconn.Close

Another workaround is to use a SELECT COUNT(*) statement for a similar query to get the correct row count.

Active server pages (ASP)

You should use the option flag Return matching rows.

BDE applications

To get these to work, you should set the option flags Don't optimize column widths and Return matching rows.

Borland Builder 4

When you start a query you can use the property Active or use the method Open. Note that Active will start by automatically issuing a SELECT * FROM ... query that may not be a good thing if your tables are big!

ColdFusion (On Unix)

The following information is taken from the ColdFusion documentation: Use the following information to configure ColdFusion Server for Linux to use the unixODBC driver with MyODBC for MySQL data sources. Allaire has verified that MyODBC Version 2.50.26 works with MySQL Version 3.22.27 and ColdFusion for Linux. (Any newer version should also work.) You can download MyODBC at http://www.mysql.com/downloads/api-myodbc.html ColdFusion Version 4.5.1 allows you to us the ColdFusion Administrator to add the MySQL data source. However, the driver is not included with ColdFusion Version 4.5.1. Before the MySQL driver will appear in the ODBC datasources drop-down list, you must build and copy the MyODBC driver to `/opt/coldfusion/lib/libmyodbc.so'. The Contrib directory contains the program `mydsn-xxx.zip' which allows you to build and remove the DSN registry file for the MyODBC driver on Coldfusion applications.

DataJunction

You have to change it to output VARCHAR rather than ENUM, as it exports the latter in a manner that causes MySQL grief.

Excel

Works. A few tips:

• If you have problems with dates, try to select them as strings using the CONCAT() function. For example:

  select CONCAT(rise_time), CONCAT(set_time)
      from sunrise_sunset;
  

  Values retrieved as strings this way should be correctly recognised as time values by Excel97. The purpose of CONCAT() in this example is to fool ODBC into thinking the column is of ``string type''. Without the CONCAT(), ODBC knows the column is of time type, and Excel does not understand that. Note that this is a bug in Excel, because it automatically converts a string to a time. This would be great if the source was a text file, but is plain stupid when the source is an ODBC connection that reports exact types for each column.

Word

To retrieve data from MySQL to Word/Excel documents, you need to use the MyODBC driver and the Add-in Microsoft Query help. For example, create a db with a table containing 2 columns of text:

• Insert rows using the mysql client command-line tool.
• Create a DSN file using the ODBC manager, for example, `my' for the db above.
• Open the Word application.
• Create a blank new documentation.
• Using the tool bar called Database, press the button insert database.
• Press the button Get Data.
• At the right hand of the screen Get Data, press the button Ms Query.
• In the Ms Query create a New Data Source using the DSN file my.
• Select the new query.
• Select the columns that you want.
• Make a filter if you want.
• Make a Sort if you want.
• Select Return Data to Microsoft Word.
• Click Finish.
• Click Insert data and select the records.
• Click OK and you see the rows in your Word document.

odbcadmin

Test program for ODBC.

Delphi

You must use BDE Version 3.2 or newer. Set the Don't optimize column width option field when connecting to MySQL. Also, here is some potentially useful Delphi code that sets up both an ODBC entry and a BDE entry for MyODBC (the BDE entry requires a BDE Alias Editor that is free at a Delphi Super Page near you. (Thanks to Bryan Brunton bryan@flesherfab.com for this):

fReg:= TRegistry.Create;
  fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True);
  fReg.WriteString('Database', 'Documents');
  fReg.WriteString('Description', ' ');
  fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll');
  fReg.WriteString('Flag', '1');
  fReg.WriteString('Password', '');
  fReg.WriteString('Port', ' ');
  fReg.WriteString('Server', 'xmark');
  fReg.WriteString('User', 'winuser');
  fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True);
  fReg.WriteString('DocumentsFab', 'MySQL');
  fReg.CloseKey;
  fReg.Free;

  Memo1.Lines.Add('DATABASE NAME=');
  Memo1.Lines.Add('USER NAME=');
  Memo1.Lines.Add('ODBC DSN=DocumentsFab');
  Memo1.Lines.Add('OPEN MODE=READ/WRITE');
  Memo1.Lines.Add('BATCH COUNT=200');
  Memo1.Lines.Add('LANGDRIVER=');
  Memo1.Lines.Add('MAX ROWS=-1');
  Memo1.Lines.Add('SCHEMA CACHE DIR=');
  Memo1.Lines.Add('SCHEMA CACHE SIZE=8');
  Memo1.Lines.Add('SCHEMA CACHE TIME=-1');
  Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');
  Memo1.Lines.Add('SQLQRYMODE=');
  Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');
  Memo1.Lines.Add('ENABLE BCD=FALSE');
  Memo1.Lines.Add('ROWSET SIZE=20');
  Memo1.Lines.Add('BLOBS TO CACHE=64');
  Memo1.Lines.Add('BLOB SIZE=32');

  AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);

C++ Builder

Tested with BDE Version 3.0. The only known problem is that when the table schema changes, query fields are not updated. BDE, however, does not seem to recognise primary keys, only the index PRIMARY, though this has not been a problem.

Vision

You should use the option flag Return matching rows.

Visual Basic

To be able to update a table, you must define a primary key for the table. Visual Basic with ADO can't handle big integers. This means that some queries like SHOW PROCESSLIST will not work properly. The fix is to set the option OPTION=16384 in the ODBC connect string or to set the Change BIGINT columns to INT option in the MyODBC connect screen. You may also want to set the Return matching rows option.

VisualInterDev

If you get the error [Microsoft][ODBC Driver Manager] Driver does not support this parameter the reason may be that you have a BIGINT in your result. Try setting the Change BIGINT columns to INT option in the MyODBC connect screen.

Visual Objects

You should use the option flag Don't optimize column widths.

9.2.6 How to Get the Value of an AUTO_INCREMENT Column in ODBC

A common problem is how to get the value of an automatically generated ID from an INSERT. With ODBC, you can do something like this (assuming that auto is an AUTO_INCREMENT field):

INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();

Or, if you are just going to insert the ID into another table, you can do this:

INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');

See section 9.1.12.3 How Can I Get the Unique ID for the Last Inserted Row?.

For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row:

SELECT * FROM tbl_name WHERE auto IS NULL;

9.2.7 Reporting Problems with MyODBC

If you encounter difficulties with MyODBC, you should start by making a log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a MyODBC log.

To get a MyODBC log, you need to do the following:

1. Ensure that you are using `myodbcd.dll' and not `myodbc.dll'. The easiest way to do this is to get `myodbcd.dll' from the MyODBC distribution and copy it over the `myodbc.dll', which is probably in your `C:\windows\system32' or `C:\winnt\system32' directory. Note that you probably want to restore the old myodbc.dll file when you have finished testing, as this is a lot faster than `myodbcd.dll'.
2. Tag the `Trace MyODBC' option flag in the MyODBC connect/configure screen. The log will be written to file `C:\myodbc.log'. If the trace option is not remembered when you are going back to the above screen, it means that you are not using the myodbcd.dll driver (see the item above).
3. Start your application and try to get it to fail.

Check the MyODBC trace file, to find out what could be wrong. You should be able to find out the issued queries by searching after the string >mysql_real_query in the `myodbc.log' file.

You should also try duplicating the queries in the mysql monitor or admndemo to find out if the error is MyODBC or MySQL.

If you find out something is wrong, please only send the relevant rows (max 40 rows) to myodbc@lists.mysql.com. Please never send the whole MyODBC or ODBC log file!

If you are unable to find out what's wrong, the last option is to make an archive (tar or zip) that contains a MyODBC trace file, the ODBC log file, and a README file that explains the problem. You can send this to ftp://support.mysql.com/pub/mysql/secret/. Only we at MySQL AB will have access to the files you upload, and we will be very discrete with the data!

If you can create a program that also shows this problem, please upload this too!

If the program works with some other SQL server, you should make an ODBC log file where you do exactly the same thing in the other SQL server.

Remember that the more information you can supply to us, the more likely it is that we can fix the problem!

9.3 MySQL Java Connectivity (JDBC)

There are 2 supported JDBC drivers for MySQL:

• MySQL Connector/J from MySQL AB, implemented in 100% native Java. This product was formerly known as the mm.mysql driver. You can download MySQL Connector/J from http://www.mysql.com/products/connector-j/.
• The Resin JDBC driver, which can be found at http://www.caucho.com/projects/jdbc-mysql/index.xtp.

For documentation, consult any JDBC documentation, plus each driver's own documentation for MySQL-specific features.

9.4 MySQL PHP API

PHP is a server-side, HTML-embedded scripting language that may be used to create dynamic web pages. It contains support for accessing several databases, including MySQL. PHP may be run as a separate program or compiled as a module for use with the Apache web server.

The distribution and documentation are available at the PHP web site (http://www.php.net/).

9.4.1 Common Problems with MySQL and PHP

• Error: "Maximum Execution Time Exceeded" This is a PHP limit; go into the `php3.ini' file and set the maximum execution time up from 30 seconds to something higher, as needed. It is also not a bad idea to double the ram allowed per script to 16 MB instead of 8 MB.
• Error: "Fatal error: Call to unsupported or undefined function mysql_connect() in .." This means that your PHP version isn't compiled with MySQL support. You can either compile a dynamic MySQL module and load it into PHP or recompile PHP with built-in MySQL support. This is described in detail in the PHP manual.
• Error: "undefined reference to `uncompress'" This means that the client library is compiled with support for a compressed client/server protocol. The fix is to add -lz last when linking with -lmysqlclient.

9.5 MySQL Perl API

This section documents the Perl DBI interface. The former interface was called mysqlperl. DBI/DBD now is the recommended Perl interface, so mysqlperl is obsolete and is not documented here.

9.5.1 DBI with DBD::mysql

DBI is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql.

For more information on the Perl5 DBI, please visit the DBI web page and read the documentation:

http://dbi.perl.org/

For more information on Object Oriented Programming (OOP) as defined in Perl5, see the Perl OOP page:

http://language.perl.com/info/documentation.html

Note that if you want to use transactions with Perl, you need to have DBD-mysql version 1.2216 or newer. Version 2.1022 or newer is recommended.

Installation instructions for MySQL Perl support are given in section 2.7 Perl Installation Comments.

If you have the MySQL module installed, you can find information about specific MySQL functionality with one of the following command

shell> perldoc DBD/mysql
shell> perldoc mysql

9.5.2 The DBI Interface

Portable DBI Methods

MySQL-specific Methods

The Perl methods are described in more detail in the following sections. Variables used for method return values have these meanings:

$dbh

Database handle

$sth

Statement handle

$rc

Return code (often a status)

$rv

Return value (often a row count)

Portable DBI Methods

connect($data_source, $username, $password)

Use the connect method to make a database connection to the data source. The $data_source value should begin with DBI:driver_name:. Example uses of connect with the DBD::mysql driver:

$dbh = DBI->connect("DBI:mysql:$database", $user, $password);
$dbh = DBI->connect("DBI:mysql:$database:$hostname",
                    $user, $password);
$dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",
                    $user, $password);

If the user name and/or password are undefined, DBI uses the values of the DBI_USER and DBI_PASS environment variables, respectively. If you don't specify a hostname, it defaults to 'localhost'. If you don't specify a port number, it defaults to the default MySQL port (3306). As of Msql-Mysql-modules Version 1.2009, the $data_source value allows certain modifiers:

mysql_read_default_file=file_name

Read `file_name' as an option file. For information on option files, see section 4.1.2 `my.cnf' Option Files.

mysql_read_default_group=group_name

The default group when reading an option file is normally the [client] group. By specifying the mysql_read_default_group option, the default group becomes the [group_name] group.

mysql_compression=1

Use compressed communication between the client and server (MySQL Version 3.22.3 or later).

mysql_socket=/path/to/socket

Specify the pathname of the Unix socket that is used to connect to the server (MySQL Version 3.21.15 or later).

Multiple modifiers may be given; each must be preceded by a semicolon. For example, if you want to avoid hardcoding the user name and password into a DBI script, you can take them from the user's `~/.my.cnf' option file instead by writing your connect call like this:

$dbh = DBI->connect("DBI:mysql:$database"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
                $user, $password);

This call will read options defined for the [client] group in the option file. If you wanted to do the same thing but use options specified for the [perl] group as well, you could use this:

$dbh = DBI->connect("DBI:mysql:$database"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
                . ";mysql_read_default_group=perl",
                $user, $password);

disconnect

The disconnect method disconnects the database handle from the database. This is typically called right before you exit from the program. Example:

$rc = $dbh->disconnect;

prepare($statement)

Prepares an SQL statement for execution by the database engine and returns a statement handle ($sth), which you can use to invoke the execute method. Typically you handle SELECT statements (and SELECT-like statements such as SHOW, DESCRIBE, and EXPLAIN) by means of prepare and execute. Example:

$sth = $dbh->prepare($statement)
    or die "Can't prepare $statement: $dbh->errstr\n";

If you want to read big results to your client you can tell Perl to use mysql_use_result() with:

my $sth = $dbh->prepare($statement { "mysql_use_result" => 1});

execute

The execute method executes a prepared statement. For non-SELECT statements, execute returns the number of rows affected. If no rows are affected, execute returns "0E0", which Perl treats as zero but regards as true. If an error occurs, execute returns undef. For SELECT statements, execute only starts the SQL query in the database; you need to use one of the fetch_* methods described here to retrieve the data. Example:

$rv = $sth->execute
          or die "can't execute the query: $sth->errstr;

do($statement)

The do method prepares and executes an SQL statement and returns the number of rows affected. If no rows are affected, do returns "0E0", which Perl treats as zero but regards as true. This method is generally used for non-SELECT statements that cannot be prepared in advance (due to driver limitations) or that do not need to be executed more than once (inserts, deletes, etc.). Example:

$rv = $dbh->do($statement)
        or die "Can't execute $statement: $dbh- >errstr\n";

Generally the 'do' statement is much faster (and is preferable) than prepare/execute for statements that don't contain parameters.

quote($string)

The quote method is used to "escape" any special characters contained in the string and to add the required outer quotation marks. Example:

$sql = $dbh->quote($string)

fetchrow_array

This method fetches the next row of data and returns it as an array of field values. Example:

while(@row = $sth->fetchrow_array) {
        print qw($row[0]\t$row[1]\t$row[2]\n);
}

fetchrow_arrayref

This method fetches the next row of data and returns it as a reference to an array of field values. Example:

while($row_ref = $sth->fetchrow_arrayref) {
        print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n);
}

fetchrow_hashref

This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example:

while($hash_ref = $sth->fetchrow_hashref) {
        print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\
                $hash_ref->{title}\n);
}

fetchall_arrayref

This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of references to arrays for each row. You access or print the data by using a nested loop. Example:

my $table = $sth->fetchall_arrayref
                or die "$sth->errstr\n";
my($i, $j);
for $i ( 0 .. $#{$table} ) {
        for $j ( 0 .. $#{$table->[$i]} ) {
                print "$table->[$i][$j]\t";
        }
        print "\n";
}

finish

Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources associated with it. Example:

$rc = $sth->finish;

rows

Returns the number of rows changed (updated, deleted, etc.) by the last command. This is usually used after a non-SELECT execute statement. Example:

$rv = $sth->rows;

NULLABLE

Returns a reference to an array of values that indicate whether columns may contain NULL values. The possible values for each array element are 0 or the empty string if the column cannot be NULL, 1 if it can, and 2 if the column's NULL status is unknown. Example:

$null_possible = $sth->{NULLABLE};

NUM_OF_FIELDS

This attribute indicates the number of fields returned by a SELECT or SHOW FIELDS statement. You may use this for checking whether a statement returned a result: A zero value indicates a non-SELECT statement like INSERT, DELETE, or UPDATE. Example:

$nr_of_fields = $sth->{NUM_OF_FIELDS};

data_sources($driver_name)

This method returns an array containing names of databases available to the MySQL server on the host 'localhost'. Example:

@dbs = DBI->data_sources("mysql");

ChopBlanks

This attribute determines whether the fetchrow_* methods will chop leading and trailing blanks from the returned values. Example:

$sth->{'ChopBlanks'} =1;

trace($trace_level)

trace($trace_level, $trace_filename)

The trace method enables or disables tracing. When invoked as a DBI class method, it affects tracing for all handles. When invoked as a database or statement handle method, it affects tracing for the given handle (and any future children of the handle). Setting $trace_level to 2 provides detailed trace information. Setting $trace_level to 0 disables tracing. Trace output goes to the standard error output by default. If $trace_filename is specified, the file is opened in append mode and output for all traced handles is written to that file. Example:

DBI->trace(2);                # trace everything
DBI->trace(2,"/tmp/dbi.out"); # trace everything to
                              # /tmp/dbi.out
$dth->trace(2);               # trace this database handle
$sth->trace(2);               # trace this statement handle

You can also enable DBI tracing by setting the DBI_TRACE environment variable. Setting it to a numeric value is equivalent to calling DBI->(value). Setting it to a pathname is equivalent to calling DBI->(2,value).

MySQL-specific Methods

The methods shown here are MySQL-specific and not part of the DBI standard. Several of them are now deprecated: is_blob, is_key, is_num, is_pri_key, is_not_null, length, max_length, and table. Where DBI-standard alternatives exist, they are noted here:

insertid

If you use the AUTO_INCREMENT feature of MySQL, the new auto-incremented values will be stored here. Example:

$new_id = $sth->{insertid};

As an alternative, you can use $dbh->{'mysql_insertid'}.

is_blob

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a BLOB. Example:

$keys = $sth->{is_blob};

is_key

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a key. Example:

$keys = $sth->{is_key};

is_num

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column contains numeric values. Example:

$nums = $sth->{is_num};

is_pri_key

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a primary key. Example:

$pri_keys = $sth->{is_pri_key};

is_not_null

Returns a reference to an array of boolean values; for each element of the array, a value of FALSE indicates that this column may contain NULL values. Example:

$not_nulls = $sth->{is_not_null};

is_not_null is deprecated; it is preferable to use the NULLABLE attribute (described above), because that is a DBI standard.

length

max_length

Each of these methods returns a reference to an array of column sizes. The length array indicates the maximum possible sizes that each column may be (as declared in the table description). The max_length array indicates the maximum sizes actually present in the result table. Example:

$lengths = $sth->{length};
$max_lengths = $sth->{max_length};

NAME

Returns a reference to an array of column names. Example:

$names = $sth->{NAME};

table

Returns a reference to an array of table names. Example:

$tables = $sth->{table};

type

Returns a reference to an array of column types. Example:

$types = $sth->{type};

9.5.3 More DBI/DBD Information

You can use the perldoc command to get more information about DBI.

perldoc DBI
perldoc DBI::FAQ
perldoc DBD::mysql

You can also use the pod2man, pod2html, etc., tools to translate to other formats.

You can find the latest DBI information at the DBI web page: http://dbi.perl.org/.

9.6 MySQL C++ API

MySQL Connector/C++ (or MySQL++) is the official MySQL API for C++. More information can be found at http://www.mysql.com/products/mysql++/.

9.6.1 Borland C++

You can compile the MySQL Windows source with Borland C++ 5.02. (The Windows source includes only projects for Microsoft VC++, for Borland C++ you have to do the project files yourself.)

One known problem with Borland C++ is that it uses a different structure alignment than VC++. This means that you will run into problems if you try to use the default libmysql.dll libraries (that was compiled with VC++) with Borland C++. You can do one of the following to avoid this problem.

• You can use the static MySQL libraries for Borland C++ that you can find on http://www.mysql.com/downloads/os-win32.html.
• Only call mysql_init() with NULL as an argument, not a pre-allocated MYSQL struct.

9.7 MySQL Python API

MySQLdb provides MySQL support for Python, compliant with the Python DB API version 2.0. It can be found at http://sourceforge.net/projects/mysql-python/.

9.8 MySQL Tcl API

MySQLtcl is a simple API for accessing a MySQL database server from the Tcl programming language. It can be found at http://www.xdobry.de/mysqltcl/.

9.9 MySQL Eiffel Wrapper

Eiffel MySQL is an interface to the MySQL database server using the Eiffel programming language, written by Michael Ravits. It can be found at http://efsa.sourceforge.net/archive/ravits/mysql.htm.

9.10 Error Returns

Following are error codes that may appear when you call MySQL from any host language.

The Name and Error Code columns correspond to definitions in the MySQL source code file: `include/mysqld_error.h'

The SQLSTATE column corresponds to definitions in the MySQL source code file: `include/sql_state.h'

The SQLSTATE error code will only appear if you use MySQL version 4.1. SQLSTATE codes were added for compatibility with X/Open / ANSI / ODBC behaviour.

A suggested text for each error code can be found in the error-message file: `share/english/errmsg.sys'

Because updates are frequent, it is possible that the above sources will contain additional error codes.

Go to the first, previous, next, last section, table of contents.