Chapter 22. APIs and Libraries
Prev |  | Next

## Chapter 22. APIs and Libraries

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, because it was developed by the MySQL team, and is the basis for most of the other APIs. This chapter also covers the `libmysqld` library (the embedded server), as well as some programs that are useful for application developers.

## 22.1. libmysqld, the Embedded MySQL Server Library

### 22.1.1. Overview of the Embedded MySQL Server Library

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

The embedded server library is based on the client/server version of MySQL, which is written in C/C++. Consequently, the embedded server also is written in C/C++. There is no embedded server available in other languages.

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:

Function | When to Call
`mysql_server_init()` | Should be called before any other MySQL function is called, preferably early in the `main()` function.
`mysql_server_end()` | Should be called before your program exits.
`mysql_thread_init()` | Should be called in each thread you create that accesses MySQL.
`mysql_thread_end()` | Should be called before calling `pthread_exit()`

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

The `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 22.2.12.1, “`mysql_server_init()`”.

One difference between the embedded server and the standalone server is that for the embedded server, authentication for connections is disabled by default. To use authentication for the embedded server, specify the `--with-embedded-privilege-control` option when you invoke configure to configure your MySQL distribution.

### 22.1.2. Compiling Programs with `libmysqld`

To get a `libmysqld` library you should configure MySQL with the `--with-embedded-server` option. See Section 2.9.2, “Typical configure Options”.

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.

To compile a C program to include the necessary files to embed the MySQL server library into a compiled version of a program, use the GNU C compiler (`gcc`). The compiler will need to know where to find various files and need instructions on how to compile the program. The following example shows how a program could be compiled from the command line:

    gcc mysql_test.c -o mysql_test -lz \
    `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`

Immediately following the `gcc` command is the name of the uncompiled C program file. After it, the `-o` option is given to indicate that the file name that follows is the name that the compiler is to give to the output file, the compiled program. The next line of code tells the compiler to obtain the location of the include files and libraries and other settings for the system on which it's compiled. Because of a problem with mysql_config, the option `-lz` (for compression) is added here. The mysql_config piece is contained in backticks, not single quotes.

### 22.1.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 current operating systems support big files.)

- You cannot set this up as a master or a slave (no replication).

- Very large result sets may be unusable on low memory systems.

- You cannot connect to an embedded server from an outside process with sockets or TCP/IP. However, you can connect to an intermediate application, which in turn can connect to an embedded server on the behalf of a remote client or outside process.

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

### 22.1.4. Options with the Embedded Server

Any options that may be given with the mysqld server daemon, may be used with an embedded server library. Server options may be given in an array as an argument to the `mysql_server_init()`, which initializes the server. They also may be given in an option file like `my.cnf`. To specify an option file for a C program, use the `--defaults-file` option as one of the elements of the second argument of the `mysql_server_init()` function. See Section 22.2.12.1, “`mysql_server_init()`”, for more information on the `mysql_server_init()` function.

Using option files can make it easier to switch between a client/server application and one where MySQL is embedded. Put common options under the `[server]` group. These are read by both MySQL versions. Client/server-specific options should go under the `[mysqld]` section. Put options specific to the embedded MySQL server library in the `[embedded]` section. Options specific to applications go under section labeled `[ApplicationName_SERVER]`. See Section 4.3.2, “Using Option Files”.

### 22.1.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 optimization to do.

- Errors are written to `stderr`. We will add an option to specify a filename for these.

- We have to change `InnoDB` not to be so verbose when using the embedded version. If your database does not contain `InnoDB` tables, to suppress related messages you can add the `--skip-innodb` option to the options file under the group `[libmysqd_server]`, or when initializing the server with mysql_server_init().

### 22.1.6. Embedded Server Examples

These two example programs should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes are needed, mostly with file paths. These examples are designed to give enough details for you to understand the problem, without the clutter that is a necessary part of a real application. The first example is very straightforward. The second example is a little more advanced with some error checking. The first is followed by a command-line entry for compiling the program. The second is followed by a GNUmake file that may be used for compiling instead.

Example 1

`test1_libmysqld.c`

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

    MYSQL *mysql;
    MYSQL_RES *results;
    MYSQL_ROW record;

    static char *server_options[] = { "mysql_test", "--defaults-file=my.cnf" };
    int num_elements = sizeof(server_options)/ sizeof(char *);

    static char *server_groups[] = { "libmysqld_server", "libmysqld_client" };

    int main(void)
    {
       mysql_server_init(num_elements, server_options, server_groups);
       mysql = mysql_init(NULL);
       mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
       mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

       mysql_real_connect(mysql, NULL,NULL,NULL, "database1", 0,NULL,0);

       mysql_query(mysql, "SELECT column1, column2 FROM table1");

       results = mysql_store_result(mysql);

       while((record = mysql_fetch_row(results))) {
          printf("%s - %s \n", record[0], record[1]);
       }

       mysql_free_result(results);
       mysql_close(mysql);
       mysql_server_end();

       return 0;
    }

Here is the command line for compiling the above program:

    gcc test1_libmysqld.c -o test1_libmysqld -lz \
     `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`

Example 2

To try out the example, create an `test2_libmysqld` directory at the same level as the MySQL source directory. Save the `test2_libmysqld.c` source and the `GNUmakefile` in the directory, and run GNU `make` from inside the `test2_libmysqld` directory.

`test2_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[] = {
      "test2_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
       * initializes the server using groups = {
       *   "server", "embedded", NULL
       *  }.
       *
       * In your $HOME/.my.cnf file, you probably want to put:

    [test2_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 does not accept the
       * client's options, and vice versa.
       */
      mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test2_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);
        mysql_free_result(res);
      }
      else
        (void)printf("Affected rows: %lld\n", mysql_affected_rows(db));

      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-5.0/include
    #lib      := $(HOME)/mysql-5.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

### 22.1.7. Licensing the Embedded Server

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 http://www.mysql.com/company/legal/licensing/.

## 22.2. 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 and Connector/NET) 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 Chapter 8, Client and Utility Programs, for a list of these variables.

The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16KB) is automatically increased up to the maximum size (the maximum is 16MB). 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 statements 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 16MB of data, you must have a communication buffer limit of at least 16MB (in both server and client). The client's default maximum is 16MB, but the default maximum in the server is 1MB. You can increase this by changing the value of the `max_allowed_packet` parameter when the server is started. See Section 7.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 22.2.15, “How to Make a Threaded Client”. For creating a standalone application which includes the "server" and "client" in the same program (and does not communicate with an external MySQL server), see Section 22.1, “libmysqld, the Embedded MySQL Server Library”.

### 22.2.1. C API Data types

- `MYSQL`

  This structure represents a handle to one database connection. It is used for almost all MySQL functions. You should not try to make a copy of a `MYSQL` structure. There is no guarantee that such a copy will be usable.

- `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` does 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. If the field was given an alias with an `AS` clause, the value of `name` is the alias.

- `char * org_name`

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

- `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. If the table was given an alias with an `AS` clause, the value of `table` is the alias.

- `char * org_table`

  The name of the table, as a null-terminated string. Aliases are ignored.

- `char * db`

  The name of the database that the field comes from, as a null-terminated string. If the field is a calculated field, `db` is an empty string.

- `char * catalog`

  The catalog name. This value is always `"def"`.

- `char * def`

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

- `unsigned long length`

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

- `unsigned long 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 name_length`

  The length of `name`.

- `unsigned int org_name_length`

  The length of `org_name`.

- `unsigned int table_length`

  The length of `table`.

- `unsigned int org_table_length`

  The length of `org_table`.

- `unsigned int db_length`

  The length of `db`.

- `unsigned int catalog_length`

  The length of `catalog`.

- `unsigned int def_length`

  The length of `def`.

- `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 `MYSQL_TYPE_BLOB`, `MYSQL_TYPE_ENUM`, `MYSQL_TYPE_SET`, or `MYSQL_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.

- `unsigned int charsetnr`

  The character set number for the field.

- `enum enum_field_types type`

  The type of the field. The `type` value may be one of the `MYSQL_TYPE_` symbols shown in the following table.

  Type Value | Type Description
  `MYSQL_TYPE_TINY` | `TINYINT` field
  `MYSQL_TYPE_SHORT` | `SMALLINT` field
  `MYSQL_TYPE_LONG` | `INTEGER` field
  `MYSQL_TYPE_INT24` | `MEDIUMINT` field
  `MYSQL_TYPE_LONGLONG` | `BIGINT` field
  `MYSQL_TYPE_DECIMAL` | `DECIMAL` or `NUMERIC` field
  `MYSQL_TYPE_NEWDECIMAL` | Precision math `DECIMAL` or `NUMERIC` field (MySQL 5.0.3 and up)
  `MYSQL_TYPE_FLOAT` | `FLOAT` field
  `MYSQL_TYPE_DOUBLE` | `DOUBLE` or `REAL` field
  `MYSQL_TYPE_BIT` | `BIT` field (MySQL 5.0.3 and up)
  `MYSQL_TYPE_TIMESTAMP` | `TIMESTAMP` field
  `MYSQL_TYPE_DATE` | `DATE` field
  `MYSQL_TYPE_TIME` | `TIME` field
  `MYSQL_TYPE_DATETIME` | `DATETIME` field
  `MYSQL_TYPE_YEAR` | `YEAR` field
  `MYSQL_TYPE_STRING` | `CHAR` or `BINARY` field
  `MYSQL_TYPE_VAR_STRING` | `VARCHAR` or `VARBINARY` field
  `MYSQL_TYPE_BLOB` | `BLOB` or `TEXT` field (use `max_length` to determine the maximum length)
  `MYSQL_TYPE_SET` | `SET` field
  `MYSQL_TYPE_ENUM` | `ENUM` field
  `MYSQL_TYPE_GEOMETRY` | Spatial field
  `MYSQL_TYPE_NULL` | `NULL`-type field
  `MYSQL_TYPE_CHAR` | Deprecated; use `MYSQL_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 evaluates to TRUE if the field is numeric:

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

  To distinguish between binary and non-binary data for string data types, check whether the `charsetnr` value is 63. If so, the character set is `binary`, which indicates binary rather than non-binary data. This is how to distinguish between `BINARY` and `CHAR`, `VARBINARY` and `VARCHAR`, and `BLOB` and `TEXT`.

### 22.2.2. C API Function Overview

The functions available in the C API are summarized here and described in greater detail in a later section. See Section 22.2.3, “C API Function Descriptions”.

Function | Description
mysql_affected_rows() | Returns the number of rows changed/deleted/inserted by the last `UPDATE`, `DELETE`, or `INSERT` query.
mysql_autocommit() | Toggles autocommit mode on/off.
mysql_change_user() | Changes user and database on an open connection.
mysql_close() | Closes a server connection.
mysql_commit() | Commits the transaction.
mysql_connect() | Connects to a MySQL server. This function is deprecated; use `mysql_real_connect()` instead.
mysql_create_db() | Creates a database. This function is deprecated; use the SQL statement `CREATE DATABASE` instead.
mysql_data_seek() | Seeks to an arbitrary row number in a query result set.
mysql_debug() | Does a `DBUG_PUSH` with the given string.
mysql_drop_db() | Drops a database. This function is deprecated; use the SQL statement `DROP DATABASE` instead.
mysql_dump_debug_info() | Makes the server write debug information to the log.
mysql_eof() | Determines whether the last row of a result set has been read. This function is deprecated; `mysql_errno()` or `mysql_error()` may be used instead.
mysql_errno() | Returns the error number for the most recently invoked MySQL function.
mysql_error() | Returns the error message for the most recently invoked MySQL function.
mysql_escape_string() | Escapes special characters in a string for use in an SQL statement.
mysql_fetch_field() | Returns the type of the next table field.
mysql_fetch_field_direct() | Returns the type of a table field, given a field number.
mysql_fetch_fields() | Returns an array of all field structures.
mysql_fetch_lengths() | Returns the lengths of all columns in the current row.
mysql_fetch_row() | Fetches the next row from the result set.
mysql_field_seek() | Puts the column cursor on a specified column.
mysql_field_count() | Returns the number of result columns for the most recent statement.
mysql_field_tell() | Returns the position of the field cursor used for the last `mysql_fetch_field()`.
mysql_free_result() | Frees memory used by a result set.
mysql_get_client_info() | Returns client version information as a string.
mysql_get_client_version() | Returns client version information as an integer.
mysql_get_host_info() | Returns a string describing the connection.
mysql_get_server_version() | Returns version number of server as an integer.
mysql_get_proto_info() | Returns the protocol version used by the connection.
mysql_get_server_info() | Returns the server version number.
mysql_info() | Returns information about the most recently executed query.
mysql_init() | Gets or initializes a `MYSQL` structure.
mysql_insert_id() | Returns the ID generated for an `AUTO_INCREMENT` column by the previous query.
mysql_kill() | Kills a given thread.
mysql_library_end() | Finalize MySQL C API library.
mysql_library_init() | Initialize MySQL C API library.
mysql_list_dbs() | Returns database names matching a simple regular expression.
mysql_list_fields() | Returns field names matching a simple regular expression.
mysql_list_processes() | Returns a list of the current server threads.
mysql_list_tables() | Returns table names matching a simple regular expression.
mysql_more_results() | Checks whether any more results exist.
mysql_next_result() | Returns/initiates the next result in multiple-statement executions.
mysql_num_fields() | Returns the number of columns in a result set.
mysql_num_rows() | Returns the number of rows in a result set.
mysql_options() | Sets connect options for `mysql_connect()`.
mysql_ping() | Checks whether the connection to the server is working, reconnecting as necessary.
mysql_query() | Executes an SQL query specified as a null-terminated string.
mysql_real_connect() | Connects to a MySQL server.
mysql_real_escape_string() | Escapes special characters in a string for use in an SQL statement, taking into account the current character set of the connection.
mysql_real_query() | Executes an SQL query specified as a counted string.
mysql_refresh() | Flush or reset tables and caches.
mysql_reload() | Tells the server to reload the grant tables.
mysql_rollback() | Rolls back the transaction.
mysql_row_seek() | Seeks to a row offset in a result set, using value returned from `mysql_row_tell()`.
mysql_row_tell() | Returns the row cursor position.
mysql_select_db() | Selects a database.
mysql_server_end() | Finalize embedded server library.
mysql_server_init() | Initialize embedded server library.
mysql_set_local_infile_default() | Set the `LOAD DATA LOCAL INFILE` handler callbacks to their default values.
mysql_set_local_infile_handler() | Install application-specific `LOAD DATA LOCAL INFILE` handler callbacks.
mysql_set_server_option() | Sets an option for the connection (like `multi-statements`).
mysql_sqlstate() | Returns the SQLSTATE error code for the last error.
mysql_shutdown() | Shuts down the database server.
mysql_stat() | Returns the server status as a string.
mysql_store_result() | Retrieves a complete result set to the client.
mysql_thread_id() | Returns the current thread ID.
mysql_thread_safe() | Returns 1 if the clients are compiled as thread-safe.
mysql_use_result() | Initiates a row-by-row result set retrieval.
mysql_warning_count() | Returns the warning count for the previous SQL statement.

Application programs should use this general outline for interacting with MySQL:

1. Initialize the MySQL library by calling `mysql_library_init()`. The library can be either the `mysqlclient` C client library or the `mysqld` embedded server library, depending on whether the application was linked with the `-libmysqlclient` or `-libmysqld` flag.

2. Initialize a connection handler by calling `mysql_init()` and connect to the server by calling `mysql_real_connect()`.

3. Issue SQL statements and process their results. (The following discussion provides more information about how to do this.)

4. Close the connection to the MySQL server by calling `mysql_close()`.

5. End use of the MySQL library by calling `mysql_library_end()`.

The purpose of calling `mysql_library_init()` and `mysql_library_end()` is to provide proper initialization and finalization of the MySQL library. For applications that are linked with the client library, they provide improved memory management. If you don't call `mysql_library_end()`, a block of memory remains allocated. (This does not increase the amount of memory used by the application, but some memory leak detectors will complain about it.) For applications that are linked with the embedded server, these calls start and stop the server.

`mysql_library_init()` and `mysql_library_end()` are available as of MySQL 5.0.3. These actually are `#define` symbols that make them equivalent to `mysql_server_init()` and `mysql_server_end()`, but the names more clearly indicate that they should be called when beginning and ending use of a MySQL library no matter whether the application uses the `mysqlclient` or `mysqld` library. For older versions of MySQL, you can call `mysql_server_init()` and `mysql_server_end()` instead.

If you like, the call to `mysql_library_init()` may be omitted, because `mysql_init()` will invoke it automatically as necessary.

To connect to the server, call `mysql_init()` to initialize a connection handler, then call `mysql_real_connect()` with that handler (along with other information such as the hostname, username, and password). Upon connection, `mysql_real_connect()` sets the `reconnect` flag (part of the `MYSQL` structure) to a value of `1` in versions of the API older than 5.0.3, or `0` in newer versions. A value of `1` for this flag indicates that if a statement cannot be performed because of a lost connection, to try reconnecting to the server before giving up. As of MySQL 5.0.13, you can use the `MYSQL_OPT_RECONNECT` option to `mysql_options()` to control reconnection behavior. When you are done with the connection, call `mysql_close()` to terminate it.

While a connection is active, the client may send SQL statements 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 initializes 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 previously 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 statements (retrieving rows only as necessary) without knowing whether the statement 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 statement 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 statement returned no data (indicating that it was an `INSERT`, `UPDATE`, `DELETE`, and so forth), and was not expected to return rows. If `mysql_field_count()` is non-zero, the statement should have returned rows, but didn't. This indicates that the statement 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, and so forth). 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.

### 22.2.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()`.

#### 22.2.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 were 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()`. Because `mysql_affected_rows()` returns an unsigned value, you can check for -1 by comparing the return value to `(my_ulonglong)-1` (or to `(my_ulonglong)~0`, which is equivalent).

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 you specify the flag `CLIENT_FOUND_ROWS` when connecting to mysqld, `mysql_affected_rows()` returns the number of rows matched by the `WHERE` statement for `UPDATE` statements. Otherwise, it returns the number of rows actually changed.

Note that when you use a `REPLACE` command, `mysql_affected_rows()` returns 2 if the new row replaced an old row, because in this case, one row was inserted after the duplicate was deleted.

If you use `INSERT ... ON DUPLICATE KEY UPDATE` to insert a row, `mysql_affected_rows()` returns 1 if the row is inserted as a new row and 2 if an existing row is updated.

#### 22.2.3.2. `mysql_autocommit()`

`my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)`

Description

Sets autocommit mode on if `mode` is 1, off if `mode` is 0.

Return Values

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

Errors

None.

#### 22.2.3.3. `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.

`mysql_change_user()` fails if the connected user cannot be authenticated or 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.

This command always performs a `ROLLBACK` of any active transactions, closes all temporary tables, unlocks all locked tables and resets the state as if one had done a new connect. This happens 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));
    }

#### 22.2.3.4. `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.

#### 22.2.3.5. `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.

#### 22.2.3.6. `mysql_commit()`

`my_bool mysql_commit(MYSQL *mysql)`

Description

Commits the current transaction.

As of MySQL 5.0.3, the action of this function is subject to the value of the `completion_type` system variable. In particular, if the value of `completion_type` is 2, the server performs a release after terminating a transaction and closes the client connection. The client program should call `mysql_close()` to close the connection from the client side.

Return Values

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

Errors

None.

#### 22.2.3.7. `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()`.

#### 22.2.3.8. `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));
    }

#### 22.2.3.9. `mysql_data_seek()`

`void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)`

Description

Seeks to an arbitrary row in a query result set. The `offset` value is a row number and should be in the range from `0` to `mysql_num_rows(result)-1`.

This function requires that the result set structure contains the entire result of the query, so `mysql_data_seek()` may be used only in conjunction with `mysql_store_result()`, not with `mysql_use_result()`.

Return Values

None.

Errors

None.

#### 22.2.3.10. `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”, and 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");

#### 22.2.3.11. `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));

#### 22.2.3.12. `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.

#### 22.2.3.13. `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()` always returns 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 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));
    }

#### 22.2.3.14. `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`. Errors also are listed at Appendix B, Error Codes and Messages.

Note that some functions like `mysql_fetch_row()` don't set `mysql_errno()` if they succeed.

A rule of thumb is that all functions that have to ask the server for information reset `mysql_errno()` if they succeed.

Return Values

An error code value for the last `mysql_`xxx`()` call, if it failed. zero means no error occurred.

Errors

None.

#### 22.2.3.15. `mysql_error()`

`const char *mysql_error(MYSQL *mysql)`

Description

For the connection specified by `mysql`, `mysql_error()` returns a null-terminated string containing the error message for the most recently invoked API function that failed. If a function didn't fail, the return value of `mysql_error()` may be the previous error or an empty string to indicate no error.

A rule of thumb is that all functions that have to ask the server for information reset `mysql_error()` if they succeed.

For functions that reset `mysql_errno()`, 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 5.11.2, “Setting the Error Message Language”.

Return Values

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

Errors

None.

#### 22.2.3.16. `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 character set.

#### 22.2.3.17. `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 (8KB) if you call `mysql_fetch_field()` to ask for the length of a `BLOB` field. (The 8KB 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);
    }

#### 22.2.3.18. `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);
    }

#### 22.2.3.19. `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);
    }

#### 22.2.3.20. `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 optimization, 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]);
        }
    }

#### 22.2.3.21. `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

Note that error is not reset between calls to `mysql_fetch_row()`

- `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");
    }

#### 22.2.3.22. `mysql_field_count()`

`unsigned int mysql_field_count(MYSQL *mysql)`

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 22.2.13.1, “Why `mysql_store_result()` Sometimes Returns `NULL` After `mysql_query()` Returns Success”.

Return Values

An unsigned integer representing the number of columns 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`.

#### 22.2.3.23. `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()` retrieves 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.

#### 22.2.3.24. `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.

#### 22.2.3.25. `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()`, and so forth. When you are done with a result set, you must free the memory it uses by calling `mysql_free_result()`.

Do not attempt to access a result set after freeing it.

Return Values

None.

Errors

None.

#### 22.2.3.26. `mysql_get_character_set_info()`

`void mysql_get_character_set_info(MYSQL *mysql, MY_CHARSET_INFO *cs)`

Description

This function provides information about the default client character set. The default character set may be changed with the `mysql_set_character_set()` function.

This function was added in MySQL 5.0.10.

Example

    if (!mysql_set_character_set(&mysql, "utf8"))
    {
        MY_CHARSET_INFO cs;
        mysql_get_character_set_info(&mysql, &cs);
        printf("character set information:\n");
        printf("character set name: %s\n", cs.name);
        printf("collation name: %s\n", cs.csname);
        printf("comment: %s\n", cs.comment);
        printf("directory: %s\n", cs.dir);
        printf("multi byte character min. length: %d\n", cs.mbminlen);
        printf("multi byte character max. length: %d\n", cs.mbmaxlen);
    }

#### 22.2.3.27. `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.

#### 22.2.3.28. `mysql_get_client_version()`

`unsigned long mysql_get_client_version(void)`

Description

Returns an integer that represents the client library version. The value has the format `XYYZZ` where `X` is the major version, `YY` is the release level, and `ZZ` is the version number within the release level. For example, a value of `40102` represents a client library version of `4.1.2`.

Return Values

An integer that represents the MySQL client library version.

Errors

None.

#### 22.2.3.29. `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 hostname.

Return Values

A character string representing the server hostname and the connection type.

Errors

None.

#### 22.2.3.30. `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.

#### 22.2.3.31. `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.

#### 22.2.3.32. `mysql_get_server_version()`

`unsigned long mysql_get_server_version(MYSQL *mysql)`

Description

Returns the version number of the server as an integer.

Return Values

A number that represents the MySQL server version in this format:

    major_version*10000 + minor_version *100 + sub_version

For example, 5.0.12 is returned as 50012.

This function is useful in client programs for quickly determining whether some version-specific server capability exists.

Errors

None.

#### 22.2.3.33. `mysql_hex_string()`

`unsigned long mysql_hex_string(char *to, const char *from, unsigned long length)`

Description

This function is used to create a legal SQL string that you can use in an SQL statement. See Section 9.1.1, “Strings”.

The string in `from` is encoded to hexadecimal format, with each character encoded as two hexadecimal digits. The result is placed in `to` and a terminating null byte is appended.

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. When `mysql_hex_string()` returns, the contents of `to` is a null-terminated string. The return value is the length of the encoded string, not including the terminating null character.

The return value can be placed into an SQL statement using either `0x`value`` or `X'`value`'` format. However, the return value does not include the `0x` or `X'...'`. The caller must supply whichever of those is desired.

Example

    char query[1000],*end;

    end = strmov(query,"INSERT INTO test_table values(");
    end = strmov(end,"0x");
    end += mysql_hex_string(end,"What's this",11);
    end = strmov(end,",0x");
    end += mysql_hex_string(end,"binary data: \0\r\n",16);
    *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.

#### 22.2.3.34. `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 contains 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 `INSERT ... VALUES` only for the multiple-row form of the statement (that is, only if multiple value lists are specified).

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.

#### 22.2.3.35. `mysql_init()`

`MYSQL *mysql_init(MYSQL *mysql)`

Description

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

Return Values

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

Errors

In case of insufficient memory, `NULL` is returned.

#### 22.2.3.36. `mysql_insert_id()`

`my_ulonglong mysql_insert_id(MYSQL *mysql)`

Description

Returns the value generated for an `AUTO_INCREMENT` column by the previous `INSERT` or `UPDATE` statement. Use this function after you have performed an `INSERT` statement into a table that contains an `AUTO_INCREMENT` field.

More precisely, `mysql_insert_id()` is updated under these conditions:

- `INSERT` statements that store a value into an `AUTO_INCREMENT` column. This is true whether the value is automatically generated by storing the special values `NULL` or `0` into the column, or is an explicit non-special value.

- In the case of a multiple-row `INSERT` statement, `mysql_insert_id()` returns the first automatically generated `AUTO_INCREMENT` value; if no such value is generated, it returns the last last explicit value inserted into the `AUTO_INCREMENT` column.

- `INSERT` statements that generate an `AUTO_INCREMENT` value by inserting `LAST_INSERT_ID(`expr`)` into any column.

- `INSERT` statements that generate an `AUTO_INCREMENT` value by updating any column to `LAST_INSERT_ID(`expr`)`.

- The value of `mysql_insert_id()` is not affected by statements such as `SELECT` that return a result set.

- If the previous statement returned an error, the value of `mysql_insert_id()` is undefined.

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

The value of `mysql_insert_id()` is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

See Section 12.9.3, “Information 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 statements because the value of that function is maintained in the server. Another difference is that `LAST_INSERT_ID()` is not updated if you set an `AUTO_INCREMENT` column to a specific non-special value.

The reason for the difference between `LAST_INSERT_ID()` and `mysql_insert_id()` is that `LAST_INSERT_ID()` is made easy to use in scripts while `mysql_insert_id()` tries to provide a little more exact information of what happens to the `AUTO_INCREMENT` column.

Return Values

Described in the preceding discussion.

Errors

None.

#### 22.2.3.37. `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.

#### 22.2.3.38. `mysql_library_end()`

`void mysql_library_end(void)`

Description

This is a synonym for the `mysql_server_end()` function. It was added in MySQL 5.0.3.

See Section 22.2.2, “C API Function Overview”, for usage information.

#### 22.2.3.39. `mysql_library_init()`

`int mysql_library_init(int argc, char **argv, char **groups)`

Description

This is a synonym for the `mysql_server_init()` function. It was added in MySQL 5.0.3. See Section 22.2.12.1, “`mysql_server_init()`”.

See Section 22.2.2, “C API Function Overview” for usage information.

#### 22.2.3.40. `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.

#### 22.2.3.41. `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.

#### 22.2.3.42. `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.

#### 22.2.3.43. `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.

#### 22.2.3.44. `mysql_more_results()`

`my_bool mysql_more_results(MYSQL *mysql)`

Description

Returns true if more results exist from the currently executed query, and the application must call `mysql_next_result()` to fetch the results.

Return Values

`TRUE` (1) if more results exist. `FALSE` (0) if no more results exist.

In most cases, you can call `mysql_next_result()` instead to test whether more results exist and initiate retrieval if so.

See Section 22.2.9, “C API Handling of Multiple Query Execution”, and Section 22.2.3.45, “`mysql_next_result()`”.

Errors

None.

#### 22.2.3.45. `mysql_next_result()`

`int mysql_next_result(MYSQL *mysql)`

Description

If more query results exist, `mysql_next_result()` reads the next query results and returns the status back to application.

You must call `mysql_free_result()` for the preceding query if it returned a result set.

After calling `mysql_next_result()` the state of the connection is as if you had called `mysql_real_query()` or `mysql_query()` for the next query. This means that you can call `mysql_store_result()`, `mysql_warning_count()`, `mysql_affected_rows()`, and so forth.

If `mysql_next_result()` returns an error, no other statements are executed and there are no more results to fetch.

See Section 22.2.9, “C API Handling of Multiple Query Execution”.

Return Values

Return Value | Description
0 | Successful and there are more results
-1 | Successful and there are no more results
>0 | An error occurred

Errors

- `CR_COMMANDS_OUT_OF_SYNC`

  Commands were executed in an improper order. For example if you didn't call `mysql_use_result()` for a previous result set.

- `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.

#### 22.2.3.46. `mysql_num_fields()`

`unsigned int mysql_num_fields(MYSQL_RES *result)`

To pass a `MYSQL*` argument instead, use `unsigned int mysql_field_count(MYSQL *mysql)`.

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 the query was a `SELECT` (or `SELECT`-like) statement. The example shown here illustrates how this may be done.

See Section 22.2.13.1, “Why `mysql_store_result()` Sometimes Returns `NULL` After `mysql_query()` Returns Success”.

Return Values

An unsigned integer representing the number of columns 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 whether `mysql_field_count(&mysql)` is = 0. This happens only if something went wrong.

#### 22.2.3.47. `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()` does 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.

#### 22.2.3.48. `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 behavior 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 option values:

Option | Argument Type | Function
`MYSQL_INIT_COMMAND` | `char *` | Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting.
`MYSQL_OPT_COMPRESS` | Not used | Use the compressed client/server protocol.
`MYSQL_OPT_CONNECT_TIMEOUT` | `unsigned int *` | Connect timeout in seconds.
`MYSQL_OPT_GUESS_CONNECTION` | Not used | For an application linked against `libmysqld`, this allows the library to guess whether to use the embedded server or a remote server. “Guess” means that if the hostname is set and is not `localhost`, it uses a remote server. This behavior is the default. `MYSQL_OPT_USE_EMBEDDED_CONNECTION` and `MYSQL_OPT_USE_REMOTE_CONNECTION` can be used to override it. This option is ignored for applications linked against `libmysqlclient`.
`MYSQL_OPT_LOCAL_INFILE` | optional pointer to uint | If no pointer is given or if pointer points to an `unsigned int != 0` the command `LOAD LOCAL INFILE` is enabled.
`MYSQL_OPT_NAMED_PIPE` | Not used | Use named pipes to connect to a MySQL server on NT.
`MYSQL_OPT_PROTOCOL` | `unsigned int *` | Type of protocol to use. Should be one of the enum values of `mysql_protocol_type` defined in `mysql.h`.
`MYSQL_OPT_READ_TIMEOUT` | `unsigned int *` | Timeout for reads from server (works currently only on Windows on TCP/IP connections).
`MYSQL_OPT_RECONNECT` | `my_bool *` | Enable or disable automatic reconnection to the server if the connection is found to have been lost. Reconnect has been off by default since MySQL 5.0.3; this option is new in 5.0.13 and provides a way to set reconnection behavior explicitly.
`MYSQL_OPT_SET_CLIENT_IP` | `char *` | For an application linked against linked against `libmysqld` (with `libmysqld` compiled with authentication support), this means that the user is considered to have connected from the specified IP address (specified as a string) for authentication purposes. This option is ignored for applications linked against `libmysqlclient`.
`MYSQL_OPT_USE_EMBEDDED_CONNECTION` | Not used | For an application linked against `libmysqld`, this forces the use of the embedded server for the connection. This option is ignored for applications linked against `libmysqlclient`.
`MYSQL_OPT_USE_REMOTE_CONNECTION` | Not used | For an application linked against `libmysqld`, this forces the use of a remote server for the connection. This option is ignored for applications linked against `libmysqlclient`.
`MYSQL_OPT_USE_RESULT` | Not used | This option is unused.
`MYSQL_OPT_WRITE_TIMEOUT` | `unsigned int *` | Timeout for writes to server (works currently only on Windows on TCP/IP connections).
`MYSQL_READ_DEFAULT_FILE` | `char *` | Read options from the named option file instead of from `my.cnf`.
`MYSQL_READ_DEFAULT_GROUP` | `char *` | Read options from the named group from `my.cnf` or the file specified with `MYSQL_READ_DEFAULT_FILE`.
`MYSQL_REPORT_DATA_TRUNCATION` | `my_bool *` | Enable or disable reporting of data truncation errors for prepared statements via `MYSQL_BIND.error`. (Default: enabled) Added in 5.0.3.
`MYSQL_SECURE_AUTH` | `my_bool*` | Whether to connect to a server that does not support the password hashing used in MySQL 4.1.1 and later.
`MYSQL_SET_CHARSET_DIR` | `char*` | The pathname to the directory that contains character set definition files.
`MYSQL_SET_CHARSET_NAME` | `char*` | The name of the character set to use as the default character set.
`MYSQL_SHARED_MEMORY_BASE_NAME` | `char*` | Named of shared-memory object for communication to server. Should be same as the option `--shared-memory-base-name` used for the mysqld server you want to connect to.

Note that the `client` group 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:

Option | Description
`connect-timeout` | Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.
`compress` | Use the compressed client/server protocol.
`database` | Connect to this database if no database was specified in the connect command.
`debug` | Debug options.
`disable-local-infile` | Disable use of `LOAD DATA LOCAL`.
`host` | Default hostname.
`init-command` | Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting.
`interactive-timeout` | Same as specifying `CLIENT_INTERACTIVE` to `mysql_real_connect()`. See Section 22.2.3.51, “`mysql_real_connect()`”.
`local-infile[=(0|1)]` | If no argument or argument != 0 then enable use of `LOAD DATA LOCAL`.
`max_allowed_packet` | Max size of packet client can read from server.
`multi-results` | Allow multiple result sets from multiple-statement executions or stored procedures.
`multi-statements` | Allow the client to send multiple statements in a single string (separated by ‘`;`’).
`password` | Default password.
`pipe` | Use named pipes to connect to a MySQL server on NT.
`protocol={TCP|SOCKET|PIPE|MEMORY}` | The protocol to use when connecting to the server.
`port` | Default port number.
`return-found-rows` | Tell `mysql_info()` to return found rows instead of updated rows when using `UPDATE`.
`shared-memory-base-name=`name`` | Shared-memory name to use to connect to server (default is "MYSQL").
`socket` | Default socket file.
`user` | Default user.

Note that `timeout` has been replaced by `connect-timeout`, but `timeout` is still supported in MySQL 5.0.22 for backward compatibility.

For more information about option files, see Section 4.3.2, “Using 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));
    }

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

#### 22.2.3.49. `mysql_ping()`

`int mysql_ping(MYSQL *mysql)`

Description

Checks whether the connection to the server is working. If the connection 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 connection to the server is alive. Non-zero if an error occurred. A non-zero return does not indicate whether the MySQL server itself is down; the connection might be broken for other reasons such as network problems.

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.

#### 22.2.3.50. `mysql_query()`

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

Description

Executes the SQL query pointed to by the null-terminated string `query`. Normally, the string must consist of a single SQL statement and you should not add a terminating semicolon (‘`;`’) or `\g` to the statement. If multiple-statement execution has been enabled, the string can contain several statements separated by semicolons. See Section 22.2.9, “C API Handling of Multiple Query Execution”.

`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 whether the query should return a result set, you can use `mysql_field_count()` to check for this. See Section 22.2.3.22, “`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.

#### 22.2.3.51. `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 other API functions that require a valid `MYSQL` connection handle structure.

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 initialize the `MYSQL` structure. You can change a lot of connect options with the `mysql_options()` call. See Section 22.2.3.48, “`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` or the empty string `""`, the current user is assumed. Under Unix, this is the current login name. Under Windows ODBC, the current username must be specified explicitly. See Section 23.1.9.2, “Configuring a MyODBC DSN on Windows”.

- 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 are 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 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 sets the default database to this value.

- If `port` is not 0, the value is 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 to enable certain features:

  Flag Name | Flag Description
  `CLIENT_COMPRESS` | Use compression protocol.
  `CLIENT_FOUND_ROWS` | Return the number of found (matched) rows, not the number of changed 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. The client's session `wait_timeout` variable is set to the value of the session `interactive_timeout` variable.
  `CLIENT_LOCAL_FILES` | Enable `LOAD DATA LOCAL` handling.
  `CLIENT_MULTI_STATEMENTS` | Tell the server that the client may send multiple statements in a single string (separated by ‘`;`’). If this flag is not set, multiple-statement execution is disabled.
  `CLIENT_MULTI_RESULTS` | Tell the server that the client can handle multiple result sets from multiple-statement executions or stored procedures. This is automatically set if `CLIENT_MULTI_STATEMENTS` is set.
  `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). This option should not be set by application programs; it is set internally in the client library. Instead, use `mysql_ssl_set()` before calling `mysql_real_connect()`.

For some parameters, it is possible to have the value taken from an option file rather than from an explicit value in the `mysql_real_connect()` call. To do this, call `mysql_options()` with the `MYSQL_READ_DEFAULT_FILE` or `MYSQL_READ_DEFAULT_GROUP` option before calling `mysql_real_connect()`. Then, in the `mysql_real_connect()` call, specify the “no-value” value for each parameter to be read from an option file:

- For `host`, specify a value of `NULL` or the empty string (`""`).

- For `user`, specify a value of `NULL` or the empty string.

- For `passwd`, specify a value of `NULL`. (For the password, a value of the empty string in the `mysql_real_connect()` call cannot be overridden in an option file, because the empty string indicates explicitly that the MySQL account must have an empty password.)

- For `db`, specify a value of `NULL` or the empty string.

- For `port`, specify a value of 0.

- For `unix_socket`, specify a value of `NULL`.

If no value is found in an option file for a parameter, its default value is used as indicated in the descriptions given earlier in this section.

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 than `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 reads the `[client]` and `[your_prog_name]` sections in the `my.cnf` file which ensures that your program works, 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` in versions of the API older than 5.0.3, or `0` in newer versions. A value of `1` for this flag indicates that if a statement cannot be performed because of a lost connection, to try reconnecting to the server before giving up. As of MySQL 5.0.13, you can use the `MYSQL_OPT_RECONNECT` option to `mysql_options()` to control reconnection behavior.

#### 22.2.3.52. `mysql_real_escape_string()`

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

Note that `mysql` must be a valid, open connection. This is needed because the escaping depends on the character set in use by the server.

Description

This function is used to create a legal SQL string that you can use in an SQL statement. See Section 9.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 9.1, “Literal Values”). (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` is a null-terminated string. The return value is the length of the encoded string, not including the terminating null character.

If you need to change the character set of the connection, you should use the `mysql_set_character_set()` function rather than executing a `SET NAMES` (or `SET CHARACTER SET`) statement. `mysql_set_character_set()` works like `SET NAMES` but also affects the character set used by `mysql_real_escape_string()`, which `SET NAMES` does not.

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.

#### 22.2.3.53. `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. Normally, the string must consist of a single SQL statement and you should not add a terminating semicolon (‘`;`’) or `\g` to the statement. If multiple-statement execution has been enabled, the string can contain several statements separated by semicolons. See Section 22.2.9, “C API Handling of Multiple Query Execution”.

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 whether the query should return a result set, you can use `mysql_field_count()` to check for this. See Section 22.2.3.22, “`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.

#### 22.2.3.54. `mysql_refresh()`

`int mysql_refresh(MYSQL *mysql, unsigned int options)`

Description

This functions flushes tables or caches, or resets replication server information. The connected user must have the `RELOAD` privilege.

The `options` argument is a bit mask composed from any combination of the following values. Multiple values can be OR'ed together to perform multiple operations with a single call.

- `REFRESH_GRANT`

  Refresh the grant tables, like `FLUSH PRIVILEGES`.

- `REFRESH_LOG`

  Flush the logs, like `FLUSH LOGS`.

- `REFRESH_TABLES`

  Flush the table cache, like `FLUSH TABLES`.

- `REFRESH_HOSTS`

  Flush the host cache, like `FLUSH HOSTS`.

- `REFRESH_STATUS`

  Reset status variables, like `FLUSH STATUS`.

- `REFRESH_THREADS`

  Flush the thread cache.

- `REFRESH_SLAVE`

  On a slave replication server, reset the master server information and restart the slave, like `RESET SLAVE`.

- `REFRESH_MASTER`

  On a master replication server, remove the binary log files listed in the binary log index and truncate the index file, like `RESET MASTER`.

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.

#### 22.2.3.55. `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.

#### 22.2.3.56. `mysql_rollback()`

`my_bool mysql_rollback(MYSQL *mysql)`

Description

Rolls back the current transaction.

As of MySQL 5.0.3, the action of this function is subject to the value of the `completion_type` system variable. In particular, if the value of `completion_type` is 2, the server performs a release after terminating a transaction and closes the client connection. The client program should call `mysql_close()` to close the connection from the client side.

Return Values

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

Errors

None.

#### 22.2.3.57. `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. The `offset` value is a row offset that should be a value returned from `mysql_row_tell()` or from `mysql_row_seek()`. This value is not a row number; if you want to seek to a row within a result set by number, use `mysql_data_seek()` instead.

This function requires that the result set structure contains the entire result of the query, so `mysql_row_seek()` may be used only in conjunction with `mysql_store_result()`, not with `mysql_use_result()`.

Return Values

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

Errors

None.

#### 22.2.3.58. `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.

#### 22.2.3.59. `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.

#### 22.2.3.60. `mysql_set_character_set()`

`int mysql_set_character_set(MYSQL *mysql, char *csname)`

Description

This function is used to set the default character set for the current connection. The string `csname` specifies a valid character set name. The connection collation becomes the default collation of the character set. This function works like the `SET NAMES` statement, but also sets the value of `mysql->charset`, and thus affects the character set used by `mysql_real_escape_string()`

This function was added in MySQL 5.0.7.

Return Values

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

Example

    MYSQL mysql;

    mysql_init(&mysql);
    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));
    }

    if (!mysql_set_charset_name(&mysql, "utf8"))
    {
        printf("New client character set: %s\n", mysql_character_set_name(&mysql));
    }

#### 22.2.3.61. `mysql_set_local_infile_default()`

    void
    mysql_set_local_infile_default(MYSQL *mysql);

Description

Sets the `LOAD LOCAL DATA INFILE` handler callback functions to the defaults used internally by the C client library. The library calls this function automatically if `mysql_set_local_infile_handler()` has not been called or does not supply valid functions for each of its callbacks.

The `mysql_set_local_infile_default()` function was added in MySQL 4.1.2.

Return Values

None.

Errors

None.

#### 22.2.3.62. `mysql_set_local_infile_handler()`

    void
    mysql_set_local_infile_handler(MYSQL *mysql,
                   int (*local_infile_init)(void **, const char *, void *),
                   int (*local_infile_read)(void *, char *, unsigned int),
                   void (*local_infile_end)(void *),
                   int (*local_infile_error)(void *, char*, unsigned int),
                   void *userdata);

Description

This function installs callbacks to be used during the execution of `LOAD DATA LOCAL INFILE` statements. It enables application programs to exert control over local (client-side) datafile reading. The arguments are the connection handler, a set of pointers to callback functions, and a pointer to a data area that the callbacks can use to share information.

To use `mysql_set_local_infile_handler()`, you must write the following callback functions:

    int
    local_infile_init(void **ptr, const char *filename, void *userdata);

The initialization function. This is called once to do any setup necessary, open the datafile, allocate data structures, and so forth. The first `void**` argument is a pointer to a pointer. You can set the pointer (that is, `*ptr`) to a value that will be passed to each of the other callbacks (as a `void*`). The callbacks can use this pointed-to value to maintain state information. The `userdata` argument is the same value that is passed to `mysql_set_local_infile_handler()`.

The initialization function should return zero for success, non-zero for an error.

    int
    local_infile_read(void *ptr, char *buf, unsigned int buf_len);

The data-reading function. This is called repeatedly to read the data file. `buf` points to the buffer where the read data should be stored, and `buf_len` is the maximum number of bytes that the callback can read and store in the buffer. (It can read fewer bytes, but should not read more.)

The return value is the number of bytes read, or zero when no more data could be read (this indicates EOF). Return a value less than zero if an error occurs.

    void
    local_infile_end(void *ptr)

The termination function. This is called once after `local_infile_read()` has returned zero (EOF) or an error. This function should deallocate any memory allocated by `local_infile_init()` and perform any other cleanup necessary. It is invoked even if the initalization function returns an error.

    int
    local_infile_error(void *ptr, char *error_msg, unsigned int error_msg_len);

The error-handling function. This is called to get a textual error message to return to the user in case any of your other functions returns an error. `error_msg` points to the buffer into which the message should be written, and `error_msg_len` is the length of the buffer. The message should be written as a null-terminated string, so the message can be at most `error_msg_len`–1 bytes long.

The return value is the error number.

Typically, the other callbacks store the error message in the data structure pointed to by `ptr`, so that `local_infile_error()` can copy the message from there into `error_msg`.

After calling `mysql_set_local_infile_handler()` in your C code and passing pointers to your callback functions, you can then issue a `LOAD DATA LOCAL INFILE` statement (for example, by using `mysql_query()`). The client library automatically invokes your callbacks. The filename specified in `LOAD DATA LOCAL INFILE` will be passed as the second parameter to the `local_infile_init()` callback.

The `mysql_set_local_infile_handler()` function was added in MySQL 4.1.2.

Return Values

None.

Errors

None.

#### 22.2.3.63. `mysql_set_server_option()`

`int mysql_set_server_option(MYSQL *mysql, enum enum_mysql_set_option option)`

Description

Enables or disables an option for the connection. `option` can have one of the following values:

MYSQL_OPTION_MULTI_STATEMENTS_ON | Enable multi statement support.
MYSQL_OPTION_MULTI_STATEMENTS_OFF | Disable multi statement support.

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.

- `ER_UNKNOWN_COM_ERROR`

  The server didn't support `mysql_set_server_option()` (which is the case that the server is older than 4.1.1) or the server didn't support the option one tried to set.

#### 22.2.3.64. `mysql_shutdown()`

`int mysql_shutdown(MYSQL *mysql, enum enum_shutdown_level shutdown_level)`

Description

Asks the database server to shut down. The connected user must have `SHUTDOWN` privileges. The `shutdown_level` argument was added in MySQL 5.0.1. MySQL 5.0 servers support only one type of shutdown; `shutdown_level` must be equal to `SHUTDOWN_DEFAULT`. Additional shutdown levels are planned to make it possible to choose the desired level. Dynamically linked executables which have been compiled with older versions of the `libmysqlclient` headers and call `mysql_shutdown()` need to be used with the old `libmysqlclient` dynamic library.

The shutdown process is described in Section 5.2.6, “The MySQL Server Shutdown Process”.

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.

#### 22.2.3.65. `mysql_sqlstate()`

`const char *mysql_sqlstate(MYSQL *mysql)`

Description

Returns a null-terminated string containing the SQLSTATE error code for the last error. The error code consists of five characters. `'00000'` means “no error.” The values are specified by ANSI SQL and ODBC. For a list of possible values, see Appendix B, Error Codes and Messages.

Note that not all MySQL errors are mapped to SQLSTATE error codes. The value `'HY000'` (general error) is used for unmapped errors.

Return Values

A null-terminated character string containing the SQLSTATE error code.

See Also

See Section 22.2.3.14, “`mysql_errno()`”, Section 22.2.3.15, “`mysql_error()`”, and Section 22.2.7.26, “`mysql_stmt_sqlstate()`”.

#### 22.2.3.66. `mysql_ssl_set()`

`int mysql_ssl_set(MYSQL *mysql, const char *key, const char *cert, const char *ca, const char *capath, const char *cipher)`

Description

`mysql_ssl_set()` is used for establishing secure connections using SSL. It must be called before `mysql_real_connect()`.

`mysql_ssl_set()` does nothing unless OpenSSL support is enabled in the client library.

`mysql` is the connection handler returned from `mysql_init()`. The other parameters are specified as follows:

- `key` is the pathname to the key file.

- `cert` is the pathname to the certificate file.

- `ca` is the pathname to the certificate authority file.

- `capath` is the pathname to a directory that contains trusted SSL CA certificates in pem format.

- `cipher` is a list of allowable ciphers to use for SSL encryption.

Any unused SSL parameters may be given as `NULL`.

Return Values

This function always returns `0`. If SSL setup is incorrect, `mysql_real_connect()` returns an error when you attempt to connect.

#### 22.2.3.67. `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.

#### 22.2.3.68. `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`, `CHECK TABLE`, and so forth).

You don't have to call `mysql_store_result()` or `mysql_use_result()` for other queries, but it does not do any harm or cause any notable performance degradation 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 on).

If you want to know whether the query should return a result set, you can use `mysql_field_count()` to check for this. See Section 22.2.3.22, “`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 whether an error occurred by checking if `mysql_error()` returns a non-empty string, if `mysql_errno()` returns non-zero, or if `mysql_field_count()` returns zero.

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 22.2.13.1, “Why `mysql_store_result()` Sometimes Returns `NULL` After `mysql_query()` Returns Success”.

Return Values

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

Errors

`mysql_store_result()` resets `mysql_error()` and `mysql_errno()` if it succeeds.

- `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.

#### 22.2.3.69. `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 changes. 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.

#### 22.2.3.70. `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 allocates memory only 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 ties 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 are returned as part of the result set for your next query. The C API gives 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 `mysql_use_result()` has finished. (However, after you have fetched all the rows, `mysql_num_rows()` accurately returns the number of rows fetched.)

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

When using the `libmysqld` embedded server, the memory benefits are essentially lost because memory usage incrementally increases with each row retrieved until `mysql_free_result()` is called.

Return Values

A `MYSQL_RES` result structure. `NULL` if an error occurred.

Errors

`mysql_use_result()` resets `mysql_error()` and `mysql_errno()` if it succeeds.

- `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.

#### 22.2.3.71. `mysql_warning_count()`

`unsigned int mysql_warning_count(MYSQL *mysql)`

Description

Returns the number of warnings generated during execution of the previous SQL statement.

Return Values

The warning count.

Errors

None.

### 22.2.4. C API Prepared Statements

The MySQL client/server protocol provides for the use of prepared statements. This capability uses the `MYSQL_STMT` statement handler data structure returned by the `mysql_stmt_init()` initialization function. Prepared execution is an efficient way to execute a statement more than once. The statement is first parsed to prepare it for execution. Then it is executed one or more times at a later time, using the statement handle returned by the initialization function.

Prepared execution is faster than direct execution for statements executed more than once, primarily because the query is parsed only once. In the case of direct execution, the query is parsed every time it is executed. Prepared execution also can provide a reduction of network traffic because for each execution of the prepared statement, it is necessary only to send the data for the parameters.

Prepared statements might not provide a performance increase in some situations. For best results, test your application both with prepared and non-prepared statements and choose whichever yields best performance.

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

The following statements can be used as prepared statements: `CREATE TABLE`, `DELETE`, `DO`, `INSERT`, `REPLACE`, `SELECT`, `SET`, `UPDATE`, and most `SHOW` statements. Other statements are not supported in MySQL 5.0.

### 22.2.5. C API Prepared Statement Data types

Prepared statements mainly use the `MYSQL_STMT` and `MYSQL_BIND` data structures. A third structure, `MYSQL_TIME`, is used to transfer temporal data.

- `MYSQL_STMT`

  This structure represents a prepared statement. A statement is created by calling `mysql_stmt_init()`, which returns a statement handle (that is, a pointer to a `MYSQL_STMT`). The handle is used for all subsequent statement-related functions until you close it with `mysql_stmt_close()`.

  The `MYSQL_STMT` structure has no members that are for application use. Also, you should not try to make a copy of a `MYSQL_STMT` structure. There is no guarantee that such a copy will be usable.

  Multiple statement handles can be associated with a single connection. The limit on the number of handles depends on the available system resources.

- `MYSQL_BIND`

  This structure is used both for statement input (data values sent to the server) and output (result values returned from the server). For input, it is used with `mysql_stmt_bind_param()` to bind parameter data values to buffers for use by `mysql_stmt_execute()`. For output, it is used with `mysql_stmt_bind_result()` to bind result set buffers for use in fetching rows with `mysql_stmt_fetch()`.

  To use a `MYSQL_BIND` structure, you should zero its contents to initialize it, and then set its members appropriately. For example, to declare and initialize an array of three `MYSQL_BIND` structures, use this code:

      MYSQL_BIND    bind[3];
      memset(bind, 0, sizeof(bind));

  The `MYSQL_BIND` structure contains the following members for use by application programs. Each is used both for input and for output, although sometimes for different purposes depending on the direction of data transfer.

  - `enum enum_field_types buffer_type`

    The type of the buffer. The allowable `buffer_type` values are listed later in this section. For input, `buffer_type` indicates what type of value you are binding to a statement parameter. For output, it indicates what type of value you expect to receive in a result buffer.

  - `void *buffer`

    For input, this is a pointer to the buffer in which a statement parameter's data value is stored. For output, it is a pointer to the buffer in which to return a result set column value. For numeric data types, `buffer` should point to a variable of the proper C type. (If you are associating the variable with a column that has the `UNSIGNED` attribute, the variable should be an `unsigned` C type. Indicate whether the variable is signed or unsigned by using the `is_unsigned` member, described later in this list.) For date and time data types, `buffer` should point to a `MYSQL_TIME` structure. For character and binary string data types, `buffer` should point to a character buffer.

  - `unsigned long buffer_length`

    The actual size of `*buffer` in bytes. This indicates the maximum amount of data that can be stored in the buffer. For character and binary C data, the `buffer_length` value specifies the length of `*buffer` when used with `mysql_stmt_bind_param()`, or the maximum number of data bytes that can be fetched into the buffer when used with `mysql_stmt_bind_result()`.

  - `unsigned long *length`

    A pointer to an `unsigned long` variable that indicates the actual number of bytes of data stored in `*buffer`. `length` is used for character or binary C data. For input parameter data binding, `length` points to an `unsigned long` variable that indicates the length of the parameter value stored in `*buffer`; this is used by `mysql_stmt_execute()`. For output value binding, the return value of `mysql_stmt_fetch()` determines the interpretation of the length. If `mysql_stmt_fetch()` returns 0, `*length` indicates the actual length of the parameter value. If `mysql_stmt_fetch()` returns `MYSQL_DATA_TRUNCATED`, `*length` indicates the non-truncated length of the parameter value. In this case, the minimum of `*length` and `buffer_length` indicates the actual length of the value.

    `length` is ignored for numeric and temporal data types because the length of the data value is determined by the `buffer_type` value.

  - `my_bool *is_null`

    This member points to a `my_bool` variable that is true if a value is `NULL`, false if it is not `NULL`. For input, set `*is_null` to true to indicate that you are passing a `NULL` value as a statement parameter. For output, this value is set to true after you fetch a row if the result set column value returned from the statement is `NULL`.

    `is_null` is a pointer to a boolean rather than a boolean scalar so that it can be used in the following way:

    - If your data values are always `NULL`, use `MYSQL_TYPE_NULL` to bind the column.

    - If your data values are always `NOT NULL`, set `is_null = (my_bool*) 0`.

    - In all other cases, you should set `is_null` to the address of a `my_bool` variable and change that variable's value appropriately between executions to indicate whether data values are `NULL` or `NOT NULL`.

  - `my_bool is_unsigned`

    This member is used for integer types. (These correspond to the `MYSQL_TYPE_TINY`, `MYSQL_TYPE_SHORT`, `MYSQL_TYPE_LONG`, and `MYSQL_TYPE_LONGLONG` type codes.) `is_unsigned` should be set to true for unsigned types and false for signed types.

  - `my_bool *error`

    For output, set this member to point to a `my_bool` variable to have truncation information for the parameter stored there after a row fetching operation. (Truncation reporting is enabled by default, but can be controlled by calling `mysql_options()` with the `MYSQL_REPORT_DATA_TRUNCATION` option.) When truncation reporting is enabled, `mysql_stmt_fetch()` returns `MYSQL_DATA_TRUNCATED` and `*error` is true in the `MYSQL_BIND` structures for parameters in which truncation occurred. Truncation indicates loss of sign or significant digits, or that a string was too long to fit in a column. The `error` member was added in MySQL 5.0.3.

- `MYSQL_TIME`

  This structure is used to send and receive `DATE`, `TIME`, `DATETIME`, and `TIMESTAMP` data directly to and from the server. This is done by setting the `buffer_type` member of a `MYSQL_BIND` structure to one of the temporal types, and setting the `buffer` member to point to a `MYSQL_TIME` structure.

  The `MYSQL_TIME` structure contains the following members:

  - `unsigned int year`

    The year.

  - `unsigned int month`

    The month of the year.

  - `unsigned int day`

    The day of the month.

  - `unsigned int hour`

    The hour of the day.

  - `unsigned int minute`

    The minute of the hour.

  - `unsigned int second`

    The second of the minute.

  - `my_bool neg`

    A boolean flag to indicate whether the time is negative.

  - `unsigned long second_part`

    The fractional part of the second. This member currently is unused.

  Only those parts of a `MYSQL_TIME` structure that apply to a given type of temporal value are used: The `year`, `month`, and `day` elements are used for `DATE`, `DATETIME`, and `TIMESTAMP` values. The `hour`, `minute`, and `second` elements are used for `TIME`, `DATETIME`, and `TIMESTAMP` values. See Section 22.2.10, “C API Handling of Date and Time Values”.

The following table shows the allowable values that may be specified in the `buffer_type` member of `MYSQL_BIND` structures. The table also shows those SQL types that correspond most closely to each `buffer_type` value, and, for numeric and temporal types, the corresponding recommended C type.

The types are “recommended” because implicit type conversion may be performed in both directions. The `buffer_type` value controls the conversion that will be performed. For example, to fetch a SQL `MEDIUMINT` column value, you can specify a `buffer_type` value of `MYSQL_TYPE_LONG` and use a C variable of type `int` as the destination buffer. If you fetch a numeric column with a value of 255 into a `char[4]` character array, specify a `buffer_type` value of `MYSQL_TYPE_STRING` and the resulting value in the array will be a 4-byte string containing `'255\0'`.

To distinguish between binary and non-binary data for string data types, check whether the `charsetnr` value of the result set metadata is 63. If so, the character set is `binary`, which indicates binary rather than non-binary data. This is how to distinguish between `BINARY` and `CHAR`, `VARBINARY` and `VARCHAR`, and `BLOB` and `TEXT`.

`buffer_type` Value | SQL Type | Recommended C Type
`MYSQL_TYPE_BIT` | `BIT` | `unsigned long long int`
`MYSQL_TYPE_TINY` | `TINYINT` | `unsigned char`
`MYSQL_TYPE_SHORT` | `SMALLINT` | `short int`
`MYSQL_TYPE_LONG` | `INT` | `int`
`MYSQL_TYPE_LONGLONG` | `BIGINT` | `long long int`
`MYSQL_TYPE_FLOAT` | `FLOAT` | `float`
`MYSQL_TYPE_DOUBLE` | `DOUBLE` | `double`
`MYSQL_TYPE_NEWDECIMAL` | `DECIMAL` | `char[]`
`MYSQL_TYPE_TIME` | `TIME` | `MYSQL_TIME`
`MYSQL_TYPE_DATE` | `DATE` | `MYSQL_TIME`
`MYSQL_TYPE_DATETIME` | `DATETIME` | `MYSQL_TIME`
`MYSQL_TYPE_TIMESTAMP` | `TIMESTAMP` | `MYSQL_TIME`
`MYSQL_TYPE_STRING` | `CHAR/BINARY` | `char[]`
`MYSQL_TYPE_VAR_STRING` | `VARCHAR/VARBINARY` | `char[]`
`MYSQL_TYPE_TINY_BLOB` | `TINYBLOB/TINYTEXT` | `char[]`
`MYSQL_TYPE_BLOB` | `BLOB/TEXT` | `char[]`
`MYSQL_TYPE_MEDIUM_BLOB` | `MEDIUMBLOB/MEDIUMTEXT` | `char[]`
`MYSQL_TYPE_LONG_BLOB` | `LONGBLOB/LONGTEXT` | `char[]`

### 22.2.6. C API Prepared Statement Function Overview

The functions available for prepared statement processing are summarized here and described in greater detail in a later section. See Section 22.2.7, “C API Prepared Statement Function Descriptions”.

Function | Description
mysql_stmt_affected_rows() | Returns the number of rows changes, deleted, or inserted by prepared `UPDATE`, `DELETE`, or `INSERT` statement.
mysql_stmt_attr_get() | Get value of an attribute for a prepared statement.
mysql_stmt_attr_set() | Sets an attribute for a prepared statement.
mysql_stmt_bind_param() | Associates application data buffers with the parameter markers in a prepared SQL statement.
mysql_stmt_bind_result() | Associates application data buffers with columns in the result set.
mysql_stmt_close() | Frees memory used by prepared statement.
mysql_stmt_data_seek() | Seeks to an arbitrary row number in a statement result set.
mysql_stmt_errno() | Returns the error number for the last statement execution.
mysql_stmt_error() | Returns the error message for the last statement execution.
mysql_stmt_execute() | Executes the prepared statement.
mysql_stmt_fetch() | Fetches the next row of data from the result set and returns data for all bound columns.
mysql_stmt_fetch_column() | Fetch data for one column of the current row of the result set.
mysql_stmt_field_count() | Returns the number of result columns for the most recent statement.
mysql_stmt_free_result() | Free the resources allocated to the statement handle.
mysql_stmt_init() | Allocates memory for `MYSQL_STMT` structure and initializes it.
mysql_stmt_insert_id() | Returns the ID generated for an `AUTO_INCREMENT` column by prepared statement.
mysql_stmt_num_rows() | Returns total rows from the statement buffered result set.
mysql_stmt_param_count() | Returns the number of parameters in a prepared SQL statement.
mysql_stmt_param_metadata() | (Return parameter metadata in the form of a result set.) Currently, this function does nothing.
mysql_stmt_prepare() | Prepares an SQL string for execution.
mysql_stmt_reset() | Reset the statement buffers in the server.
mysql_stmt_result_metadata() | Returns prepared statement metadata in the form of a result set.
mysql_stmt_row_seek() | Seeks to a row offset in a statement result set, using value returned from `mysql_stmt_row_tell()`.
mysql_stmt_row_tell() | Returns the statement row cursor position.
mysql_stmt_send_long_data() | Sends long data in chunks to server.
mysql_stmt_sqlstate() | Returns the SQLSTATE error code for the last statement execution.
mysql_stmt_store_result() | Retrieves the complete result set to the client.

Call `mysql_stmt_init()` to create a statement handle, then `mysql_stmt_prepare` to prepare it, `mysql_stmt_bind_param()` to supply the parameter data, and `mysql_stmt_execute()` to execute the statement. You can repeat the `mysql_stmt_execute()` by changing parameter values in the respective buffers supplied through `mysql_stmt_bind_param()`.

If the statement is a `SELECT` or any other statement that produces a result set, `mysql_stmt_prepare()` also returns the result set metadata information in the form of a `MYSQL_RES` result set through `mysql_stmt_result_metadata()`.

You can supply the result buffers using `mysql_stmt_bind_result()`, so that the `mysql_stmt_fetch()` automatically returns data to these buffers. This is row-by-row fetching.

You can also send the text or binary data in chunks to server using `mysql_stmt_send_long_data()`. See Section 22.2.7.25, “`mysql_stmt_send_long_data()`”.

When statement execution has been completed, the statement handle must be closed using `mysql_stmt_close()` so that all resources associated with it can be freed.

If you obtained a `SELECT` statement's result set metadata by calling `mysql_stmt_result_metadata()`, you should also free the metadata using `mysql_free_result()`.

Execution Steps

To prepare and execute a statement, an application follows these steps:

1. Create a prepared statement handle with `msyql_stmt_init()`. To prepare the statement on the server, call `mysql_stmt_prepare()` and pass it a string containing the SQL statement.

2. If the statement produces a result set, call `mysql_stmt_result_metadata()` to obtain the result set metadata. This metadata is itself in the form of result set, albeit a separate one from the one that contains the rows returned by the query. The metadata result set indicates how many columns are in the result and contains information about each column.

3. Set the values of any parameters using `mysql_stmt_bind_param()`. All parameters must be set. Otherwise, statement execution returns an error or produces unexpected results.

4. Call `mysql_stmt_execute()` to execute the statement.

5. If the statement produces a result set, bind the data buffers to use for retrieving the row values by calling `mysql_stmt_bind_result()`.

6. Fetch the data into the buffers row by row by calling `mysql_stmt_fetch()` repeatedly until no more rows are found.

7. Repeat steps 3 through 6 as necessary, by changing the parameter values and re-executing the statement.

When `mysql_stmt_prepare()` is called, the MySQL client/server protocol performs these actions:

- The server parses the statement and sends the okay status back to the client by assigning a statement ID. It also sends total number of parameters, a column count, and its metadata if it is a result set oriented statement. All syntax and semantics of the statement are checked by the server during this call.

- The client uses this statement ID for the further operations, so that the server can identify the statement from among its pool of statements.

When `mysql_stmt_execute()` is called, the MySQL client/server protocol performs these actions:

- The client uses the statement handle and sends the parameter data to the server.

- The server identifies the statement using the ID provided by the client, replaces the parameter markers with the newly supplied data, and executes the statement. If the statement produces a result set, the server sends the data back to the client. Otherwise, it sends an okay status and total number of rows changed, deleted, or inserted.

When `mysql_stmt_fetch()` is called, the MySQL client/server protocol performs these actions:

- The client reads the data from the packet row by row and places it into the application data buffers by doing the necessary conversions. If the application buffer type is same as that of the field type returned from the server, the conversions are straightforward.

If an error occurs, you can get the statement error code, error message, and SQLSTATE value using `mysql_stmt_errno()`, `mysql_stmt_error()`, and `mysql_stmt_sqlstate()`, respectively.

Prepared Statement Logging

For prepared statements that are executed with the `mysql_stmt_prepare()` and `mysql_stmt_execute()` C API functions, the server writes `Prepare` and `Execute` lines to the general query log so that you can tell when statements are prepared and executed.

Suppose that you prepare and execute a statement as follows:

1. Call `mysql_stmt_prepare()` to prepare the statement string `"SELECT ?"`.

2. Call `mysql_stmt_bind_param()` to bind the value `3` to the parameter in the prepared statement.

3. Call `mysql_stmt_execute()` to execute the prepared statement.

As a result of the preceding calls, the server writes the following lines to the general query log:

    Prepare  [1] SELECT ?
    Execute  [1] SELECT 3

Each `Prepare` and `Execute` line in the log is tagged with a `[`N`]` statement identifier so that you can keep track of which prepared statement is being logged. `N` is a positive integer. If there are multiple prepared statements active simultaneously for the client, `N` may be greater than 1. Each `Execute` lines shows a prepared statement after substitution of data values for `?` parameters.

Version notes: `Prepare` lines are displayed without `[`N`]` before MySQL 4.1.10. `Execute` lines are not displayed at all before MySQL 4.1.10.

### 22.2.7. C API Prepared Statement Function Descriptions

To prepare and execute queries, use the functions described in detail in the following sections.

Note that all functions operating with a `MYSQL_STMT` structure begin with the prefix `mysql_stmt_`.

To create a `MYSQL_STMT` handle, use the `mysql_stmt_init()` function.

#### 22.2.7.1. `mysql_stmt_affected_rows()`

`my_ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt)`

Description

Returns the total number of rows changed, deleted, or inserted by the last executed statement. May be called immediately after `mysql_stmt_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 were 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_stmt_store_result()`. Because `mysql_stmt_affected_rows()` returns an unsigned value, you can check for -1 by comparing the return value to `(my_ulonglong)-1` (or to `(my_ulonglong)~0`, which is equivalent).

See Section 22.2.3.1, “`mysql_affected_rows()`”, for additional information on the return value.

Errors

None.

Example

For the usage of `mysql_stmt_affected_rows()`, refer to the Example from Section 22.2.7.10, “`mysql_stmt_execute()`”.

#### 22.2.7.2. `mysql_stmt_attr_get()`

`int mysql_stmt_attr_get(MYSQL_STMT *stmt, enum enum_stmt_attr_type option, void *arg)`

Description

Can be used to get the current value for a statement attribute.

The `option` argument is the option that you want to get; the `arg` should point to a variable that should contain the option value. If the option is an integer, then `arg` should point to the value of the integer.

See Section 22.2.7.3, “`mysql_stmt_attr_set()`”, for a list of options and option types.

Note: In MySQL 5.0, `mysql_stmt_attr_get()` uses `unsigned int *`, not `my_bool *`, for `STMT_ATTR_UPDATE_MAX_LENGTH`. This is corrected in MySQL 5.1.7.

Return Values

`0` if okay. Non-zero if `option` is unknown.

Errors

None.

#### 22.2.7.3. `mysql_stmt_attr_set()`

`int mysql_stmt_attr_set(MYSQL_STMT *stmt, enum enum_stmt_attr_type option, const void *arg)`

Description

Can be used to affect behavior for a prepared statement. This function may be called multiple times to set several options.

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 `option` values:

Option | Argument Type | Function
`STMT_ATTR_UPDATE_MAX_LENGTH` | `my_bool *` | If set to 1: Update metadata `MYSQL_FIELD->max_length` in `mysql_stmt_store_result()`.
`STMT_ATTR_CURSOR_TYPE` | `unsigned long *` | Type of cursor to open for statement when `mysql_stmt_execute()` is invoked. `*arg` can be `CURSOR_TYPE_NO_CURSOR` (the default) or `CURSOR_TYPE_READ_ONLY`.
`STMT_ATTR_PREFETCH_ROWS` | `unsigned long *` | Number of rows to fetch from server at a time when using a cursor. `*arg` can be in the range from 1 to the maximum value of `unsigned long`. The default is 1.

Note: In MySQL 5.0, `mysql_stmt_attr_get()` uses `unsigned int *`, not `my_bool *`, for `STMT_ATTR_UPDATE_MAX_LENGTH`. This is corrected in MySQL 5.1.7.

If you use the `STMT_ATTR_CURSOR_TYPE` option with `CURSOR_TYPE_READ_ONLY`, a cursor is opened for the statement when you invoke `mysql_stmt_execute()`. If there is already an open cursor from a previous `mysql_stmt_execute()` call, it closes the cursor before opening a new one. `mysql_stmt_reset()` also closes any open cursor before preparing the statement for re-execution. `mysql_stmt_free_result()` closes any open cursor.

If you open a cursor for a prepared statement, `mysql_stmt_store_result()` is unnecessary, because that function causes the result set to be buffered on the client side.

The `STMT_ATTR_CURSOR_TYPE` option was added in MySQL 5.0.2. The `STMT_ATTR_PREFETCH_ROWS` option was added in MySQL 5.0.6.

Return Values

`0` if okay. Non-zero if `option` is unknown.

Errors

None.

Example

The following example opens a cursor for a prepared statement and sets the number of rows to fetch at a time to 5:

    MYSQL_STMT *stmt;
    int rc;
    unsigned long type;
    unsigned long prefetch_rows = 5;

    stmt = mysql_stmt_init(mysql);
    type = (unsigned long) CURSOR_TYPE_READ_ONLY;
    rc = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*) &type);
    /* ... check return value ... */
    rc = mysql_stmt_attr_set(stmt, STMT_ATTR_PREFETCH_ROWS,
                             (void*) &prefetch_rows);
    /* ... check return value ... */

#### 22.2.7.4. `mysql_stmt_bind_param()`

`my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)`

Description

`mysql_stmt_bind_param()` is used to bind data for the parameter markers in the SQL statement that was passed to `mysql_stmt_prepare()`. It uses `MYSQL_BIND` structures to supply the data. `bind` is the address of an array of `MYSQL_BIND` structures. The client library expects the array to contain an element for each ‘`?`’ parameter marker that is present in the query.

Suppose that you prepare the following statement:

    INSERT INTO mytbl VALUES(?,?,?)

When you bind the parameters, the array of `MYSQL_BIND` structures must contain three elements, and can be declared like this:

    MYSQL_BIND bind[3];

The members of each `MYSQL_BIND` element that should be set are described in Section 22.2.5, “C API Prepared Statement Data types”.

Return Values

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

Errors

- `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` value is illegal or is not one of the supported types.

- `CR_OUT_OF_MEMORY`

  Out of memory.

- `CR_UNKNOWN_ERROR`

  An unknown error occurred.

Example

For the usage of `mysql_stmt_bind_param()`, refer to the Example from Section 22.2.7.10, “`mysql_stmt_execute()`”.

#### 22.2.7.5. `mysql_stmt_bind_result()`

`my_bool mysql_stmt_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind)`

Description

`mysql_stmt_bind_result()` is used to associate (bind) columns in the result set to data buffers and length buffers. When `mysql_stmt_fetch()` is called to fetch data, the MySQL client/server protocol places the data for the bound columns into the specified buffers.

All columns must be bound to buffers prior to calling `mysql_stmt_fetch()`. `bind` is the address of an array of `MYSQL_BIND` structures. The client library expects the array to contain an element for each column of the result set. If you do not bind columns to `MYSQL_BIND` structures, `mysql_stmt_fetch()` simply ignores the data fetch. The buffers should be large enough to hold the data values, because the protocol doesn't return data values in chunks.

A column can be bound or rebound at any time, even after a result set has been partially retrieved. The new binding takes effect the next time `mysql_stmt_fetch()` is called. Suppose that an application binds the columns in a result set and calls `mysql_stmt_fetch()`. The client/server protocol returns data in the bound buffers. Then suppose that the application binds the columns to a different set of buffers. The protocol does not place data into the newly bound buffers until the next call to `mysql_stmt_fetch()` occurs.

To bind a column, an application calls `mysql_stmt_bind_result()` and passes the type, address, and the address of the length buffer. The members of each `MYSQL_BIND` element that should be set are described in Section 22.2.5, “C API Prepared Statement Data types”.

Return Values

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

Errors

- `CR_UNSUPPORTED_PARAM_TYPE`

  The conversion is not supported. Possibly the `buffer_type` value is illegal or is not one of the supported types.

- `CR_OUT_OF_MEMORY`

  Out of memory.

- `CR_UNKNOWN_ERROR`

  An unknown error occurred.

Example

For the usage of `mysql_stmt_bind_result()`, refer to the Example from Section 22.2.7.11, “`mysql_stmt_fetch()`”.

#### 22.2.7.6. `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 statement has pending or unread results, this function cancels them so that the next query can be executed.

Return Values

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

Errors

- `CR_SERVER_GONE_ERROR`

  The MySQL server has gone away.

- `CR_UNKNOWN_ERROR`

  An unknown error occurred.

Example

For the usage of `mysql_stmt_close()`, refer to the Example from Section 22.2.7.10, “`mysql_stmt_execute()`”.

#### 22.2.7.7. `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. The `offset` value is a row number and should be in the range from `0` to `mysql_stmt_num_rows(stmt)-1`.

This function requires that the statement result set structure contains the entire result of the last executed query, so `mysql_stmt_data_seek()` may be used only in conjunction with `mysql_stmt_store_result()`.

Return Values

None.

Errors

None.

#### 22.2.7.8. `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 occurred. Client error message numbers are listed in the MySQL `errmsg.h` header file. Server error message numbers are listed in `mysqld_error.h`. Errors also are listed at Appendix B, Error Codes and Messages.

Return Values

An error code value. Zero if no error occurred.

Errors

None.

#### 22.2.7.9. `mysql_stmt_error()`

`const char *mysql_stmt_error(MYSQL_STMT *stmt)`

Description

For the statement specified by `stmt`, `mysql_stmt_error()` returns a null-terminated string containing the error message for the most recently invoked statement 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_stmt_errno(stmt))
    {
      // an error occurred
    }

    if (mysql_stmt_error(stmt)[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.

Return Values

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

Errors

None.

#### 22.2.7.10. `mysql_stmt_execute()`

`int mysql_stmt_execute(MYSQL_STMT *stmt)`

Description

`mysql_stmt_execute()` executes the prepared query associated with the statement handle. The currently bound parameter marker values are sent to server during this call, and the server replaces the markers with this newly supplied data.

If the statement is an `UPDATE`, `DELETE`, or `INSERT`, the total number of changed, deleted, or inserted rows can be found by calling `mysql_stmt_affected_rows()`. If this is a statement such as `SELECT` that generates a result set, you must call `mysql_stmt_fetch()` to fetch the data prior to calling any other functions that result in query processing. For more information on how to fetch the results, refer to Section 22.2.7.11, “`mysql_stmt_fetch()`”.

For statements that generate a result set, you can request that `mysql_stmt_execute()` open a cursor for the statement by calling `mysql_stmt_attr_set()` before executing the statement. If you execute a statement multiple times, `mysql_stmt_execute()` closes any open cursor before opening a new one.

Return Values

Zero if execution was successful. Non-zero 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.

Example

The following example demonstrates how to create and populate a table using `mysql_stmt_init()`, `mysql_stmt_prepare()`, `mysql_stmt_param_count()`, `mysql_stmt_bind_param()`, `mysql_stmt_execute()`, and `mysql_stmt_affected_rows()`. The `mysql` variable is assumed to be a valid connection handle.

    #define STRING_SIZE 50

    #define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"
    #define CREATE_SAMPLE_TABLE "CREATE TABLE test_table(col1 INT,\
                                                     col2 VARCHAR(40),\
                                                     col3 SMALLINT,\
                                                     col4 TIMESTAMP)"
    #define INSERT_SAMPLE "INSERT INTO test_table(col1,col2,col3) VALUES(?,?,?)"

    MYSQL_STMT    *stmt;
    MYSQL_BIND    bind[3];
    my_ulonglong  affected_rows;
    int           param_count;
    short         small_data;
    int           int_data;
    char          str_data[STRING_SIZE];
    unsigned long str_length;
    my_bool       is_null;

    if (mysql_query(mysql, DROP_SAMPLE_TABLE))
    {
      fprintf(stderr, " DROP TABLE failed\n");
      fprintf(stderr, " %s\n", mysql_error(mysql));
      exit(0);
    }

    if (mysql_query(mysql, CREATE_SAMPLE_TABLE))
    {
      fprintf(stderr, " CREATE TABLE failed\n");
      fprintf(stderr, " %s\n", mysql_error(mysql));
      exit(0);
    }

    /* Prepare an INSERT query with 3 parameters */
    /* (the TIMESTAMP column is not named; the server */
    /*  sets it to the current date and time) */
    stmt = mysql_stmt_init(mysql);
    if (!stmt)
    {
      fprintf(stderr, " mysql_stmt_init(), out of memory\n");
      exit(0);
    }
    if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE)))
    {
      fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }
    fprintf(stdout, " prepare, INSERT successful\n");

    /* Get the parameter count from the statement */
    param_count= mysql_stmt_param_count(stmt);
    fprintf(stdout, " total parameters in INSERT: %d\n", param_count);

    if (param_count != 3) /* validate parameter count */
    {
      fprintf(stderr, " invalid parameter count returned by MySQL\n");
      exit(0);
    }

    /* Bind the data for all 3 parameters */

    memset(bind, 0, sizeof(bind));

    /* INTEGER PARAM */
    /* This is a number type, so there is no need to specify buffer_length */
    bind[0].buffer_type= MYSQL_TYPE_LONG;
    bind[0].buffer= (char *)&int_data;
    bind[0].is_null= 0;
    bind[0].length= 0;

    /* STRING PARAM */
    bind[1].buffer_type= MYSQL_TYPE_STRING;
    bind[1].buffer= (char *)str_data;
    bind[1].buffer_length= STRING_SIZE;
    bind[1].is_null= 0;
    bind[1].length= &str_length;

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

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

    /* Specify the data values for the first row */
    int_data= 10;             /* integer */
    strncpy(str_data, "MySQL", STRING_SIZE); /* string  */
    str_length= strlen(str_data);

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

    /* Execute the INSERT statement - 1*/
    if (mysql_stmt_execute(stmt))
    {
      fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }

    /* Get the total number of affected rows */
    affected_rows= mysql_stmt_affected_rows(stmt);
    fprintf(stdout, " total affected rows(insert 1): %lu\n",
                    (unsigned long) affected_rows);

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

    /* Specify data values for second row, then re-execute the statement */
    int_data= 1000;
    strncpy(str_data, "The most popular Open Source database", STRING_SIZE);
    str_length= strlen(str_data);
    small_data= 1000;         /* smallint */
    is_null= 0;               /* reset */

    /* Execute the INSERT statement - 2*/
    if (mysql_stmt_execute(stmt))
    {
      fprintf(stderr, " mysql_stmt_execute, 2 failed\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }

    /* Get the total rows affected */
    affected_rows= mysql_stmt_affected_rows(stmt);
    fprintf(stdout, " total affected rows(insert 2): %lu\n",
                    (unsigned long) affected_rows);

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

    /* Close the statement */
    if (mysql_stmt_close(stmt))
    {
      fprintf(stderr, " failed while closing the statement\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }

Note: For complete examples on the use of prepared statement functions, refer to the file `tests/mysql_client_test.c`. This file can be obtained from a MySQL source distribution or from the BitKeeper source repository.

#### 22.2.7.11. `mysql_stmt_fetch()`

`int mysql_stmt_fetch(MYSQL_STMT *stmt)`

Description

`mysql_stmt_fetch()` returns the next row in the result set. It can be called only while the result set exists; that is, after a call to `mysql_stmt_execute()` that creates a result set or after `mysql_stmt_store_result()`, which is called after `mysql_stmt_execute()` to buffer the entire result set.

`mysql_stmt_fetch()` returns row data using the buffers bound by `mysql_stmt_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.

All columns must be bound by the application before calling `mysql_stmt_fetch()`.

If a fetched data value is a `NULL` value, the `*is_null` value of the corresponding `MYSQL_BIND` structure contains TRUE (1). Otherwise, the data and its length are returned in the `*buffer` and `*length` elements based on the buffer type specified by the application. Each numeric and temporal type has a fixed length, as listed in the following table. The length of the string types depends on the length of the actual data value, as indicated by `data_length`.

Type | Length
`MYSQL_TYPE_TINY` | 1
`MYSQL_TYPE_SHORT` | 2
`MYSQL_TYPE_LONG` | 4
`MYSQL_TYPE_LONGLONG` | 8
`MYSQL_TYPE_FLOAT` | 4
`MYSQL_TYPE_DOUBLE` | 8
`MYSQL_TYPE_TIME` | `sizeof(MYSQL_TIME)`
`MYSQL_TYPE_DATE` | `sizeof(MYSQL_TIME)`
`MYSQL_TYPE_DATETIME` | `sizeof(MYSQL_TIME)`
`MYSQL_TYPE_STRING` | `data length`
`MYSQL_TYPE_BLOB` | `data_length`

Return Values

Return Value | Description
0 | Successful, the data has been fetched to application data buffers.
1 | Error occurred. Error code and message can be obtained by calling `mysql_stmt_errno()` and `mysql_stmt_error()`.
`MYSQL_NO_DATA` | No more rows/data exists
`MYSQL_DATA_TRUNCATED` | Data truncation occurred

`MYSQL_DATA_TRUNCATED` is returned when truncation reporting is enabled. (Reporting is enabled by default, but can be controlled with `mysql_options()`.) To determine which parameters were truncated when this value is returned, check the `error` members of the `MYSQL_BIND` parameter structures.

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`

  The buffer type is `MYSQL_TYPE_DATE`, `MYSQL_TYPE_TIME`, `MYSQL_TYPE_DATETIME`, or `MYSQL_TYPE_TIMESTAMP`, but the data type is not `DATE`, `TIME`, `DATETIME`, or `TIMESTAMP`.

- All other unsupported conversion errors are returned from `mysql_stmt_bind_result()`.

Example

The following example demonstrates how to fetch data from a table using `mysql_stmt_result_metadata()`, `mysql_stmt_bind_result()`, and `mysql_stmt_fetch()`. (This example expects to retrieve the two rows inserted by the example shown in Section 22.2.7.10, “`mysql_stmt_execute()`”.) The `mysql` variable is assumed to be a valid connection handle.

    #define STRING_SIZE 50

    #define SELECT_SAMPLE "SELECT col1, col2, col3, col4 FROM test_table"

    MYSQL_STMT    *stmt;
    MYSQL_BIND    bind[4];
    MYSQL_RES     *prepare_meta_result;
    MYSQL_TIME    ts;
    unsigned long length[4];
    int           param_count, column_count, row_count;
    short         small_data;
    int           int_data;
    char          str_data[STRING_SIZE];
    my_bool       is_null[4];
    my_bool       error[4];

    /* Prepare a SELECT query to fetch data from test_table */
    stmt = mysql_stmt_init(mysql);
    if (!stmt)
    {
      fprintf(stderr, " mysql_stmt_init(), out of memory\n");
      exit(0);
    }
    if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
    {
      fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }
    fprintf(stdout, " prepare, SELECT successful\n");

    /* Get the parameter count from the statement */
    param_count= mysql_stmt_param_count(stmt);
    fprintf(stdout, " total parameters in SELECT: %d\n", param_count);

    if (param_count != 0) /* validate parameter count */
    {
      fprintf(stderr, " invalid parameter count returned by MySQL\n");
      exit(0);
    }

    /* Fetch result set meta information */
    prepare_meta_result = mysql_stmt_result_metadata(stmt);
    if (!prepare_meta_result)
    {
      fprintf(stderr,
             " mysql_stmt_result_metadata(), returned no meta information\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }

    /* Get total columns in the query */
    column_count= mysql_num_fields(prepare_meta_result);
    fprintf(stdout, " total columns in SELECT statement: %d\n", column_count);

    if (column_count != 4) /* validate column count */
    {
      fprintf(stderr, " invalid column count returned by MySQL\n");
      exit(0);
    }

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

    /* Bind the result buffers for all 4 columns before fetching them */

    memset(bind, 0, sizeof(bind));

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

    /* STRING COLUMN */
    bind[1].buffer_type= MYSQL_TYPE_STRING;
    bind[1].buffer= (char *)str_data;
    bind[1].buffer_length= STRING_SIZE;
    bind[1].is_null= &is_null[1];
    bind[1].length= &length[1];
    bind[1].error= &error[1];

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

    /* TIMESTAMP COLUMN */
    bind[3].buffer_type= MYSQL_TYPE_TIMESTAMP;
    bind[3].buffer= (char *)&ts;
    bind[3].is_null= &is_null[3];
    bind[3].length= &length[3];
    bind[3].error= &error[3];

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

    /* Now buffer all results to client */
    if (mysql_stmt_store_result(stmt))
    {
      fprintf(stderr, " mysql_stmt_store_result() failed\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }

    /* Fetch all rows */
    row_count= 0;
    fprintf(stdout, "Fetching results ...\n");
    while (!mysql_stmt_fetch(stmt))
    {
      row_count++;
      fprintf(stdout, "  row %d\n", row_count);

      /* column 1 */
      fprintf(stdout, "   column1 (integer)  : ");
      if (is_null[0])
        fprintf(stdout, " NULL\n");
      else
        fprintf(stdout, " %d(%ld)\n", int_data, length[0]);

      /* column 2 */
      fprintf(stdout, "   column2 (string)   : ");
      if (is_null[1])
        fprintf(stdout, " NULL\n");
      else
        fprintf(stdout, " %s(%ld)\n", str_data, length[1]);

      /* column 3 */
      fprintf(stdout, "   column3 (smallint) : ");
      if (is_null[2])
        fprintf(stdout, " NULL\n");
      else
        fprintf(stdout, " %d(%ld)\n", small_data, length[2]);

      /* column 4 */
      fprintf(stdout, "   column4 (timestamp): ");
      if (is_null[3])
        fprintf(stdout, " NULL\n");
      else
        fprintf(stdout, " %04d-%02d-%02d %02d:%02d:%02d (%ld)\n",
                         ts.year, ts.month, ts.day,
                         ts.hour, ts.minute, ts.second,
                         length[3]);
      fprintf(stdout, "\n");
    }

    /* Validate rows fetched */
    fprintf(stdout, " total rows fetched: %d\n", row_count);
    if (row_count != 2)
    {
      fprintf(stderr, " MySQL failed to return all rows\n");
      exit(0);
    }

    /* Free the prepared result metadata */
    mysql_free_result(prepare_meta_result);

    /* Close the statement */
    if (mysql_stmt_close(stmt))
    {
      fprintf(stderr, " failed while closing the statement\n");
      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
      exit(0);
    }

#### 22.2.7.12. `mysql_stmt_fetch_column()`

`int mysql_stmt_fetch_column(MYSQL_STMT *stmt, MYSQL_BIND *bind, unsigned int column, unsigned long offset)`

Description

Fetch one column from the current result set row. `bind` provides the buffer where data should be placed. It should be set up the same way as for `mysql_stmt_bind_result()`. `column` indicates which column to fetch. The first column is numbered 0. `offset` is the offset within the data value at which to begin retrieving data. This can be used for fetching the data value in pieces. The beginning of the value is offset 0.

Return Values

Zero if the value was fetched successfully. Non-zero if an error occurred.

Errors

- `CR_INVALID_PARAMETER_NO`

  Invalid column number.

- `CR_NO_DATA`

  The end of the result set has already been reached.

#### 22.2.7.13. `mysql_stmt_field_count()`

`unsigned int mysql_stmt_field_count(MYSQL_STMT *stmt)`

Description

Returns the number of columns for the most recent statement for the statement handler. This value is zero for statements such as `INSERT` or `DELETE` that do not produce result sets.

`mysql_stmt_field_count()` can be called after you have prepared a statement by invoking `mysql_stmt_prepare()`.

Return Values

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

Errors

None.

#### 22.2.7.14. `mysql_stmt_free_result()`

`my_bool mysql_stmt_free_result(MYSQL_STMT *stmt)`

Description

Releases memory associated with the result set produced by execution of the prepared statement. If there is a cursor open for the statement, `mysql_stmt_free_result()` closes it.

Return Values

Zero if the result set was freed successfully. Non-zero if an error occurred.

Errors

#### 22.2.7.15. `mysql_stmt_init()`

`MYSQL_STMT *mysql_stmt_init(MYSQL *mysql)`

Description

Create a `MYSQL_STMT` handle. The handle should be freed with `mysql_stmt_close(MYSQL_STMT *)`.

Return values

A pointer to a `MYSQL_STMT` structure in case of success. `NULL` if out of memory.

Errors

- `CR_OUT_OF_MEMORY`

  Out of memory.

#### 22.2.7.16. `mysql_stmt_insert_id()`

`my_ulonglong mysql_stmt_insert_id(MYSQL_STMT *stmt)`

Description

Returns the value generated for an `AUTO_INCREMENT` column by the prepared `INSERT` or `UPDATE` statement. Use this function after you have executed a prepared `INSERT` statement on a table which contains an `AUTO_INCREMENT` field.

See Section 22.2.3.36, “`mysql_insert_id()`”, for more information.

Return Values

Value for `AUTO_INCREMENT` column which was automatically generated or explicitly set during execution of prepared statement, or value generated by `LAST_INSERT_ID(`expr`)` function. Return value is undefined if statement does not set `AUTO_INCREMENT` value.

Errors

None.

#### 22.2.7.17. `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.

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.

#### 22.2.7.18. `mysql_stmt_param_count()`

`unsigned long mysql_stmt_param_count(MYSQL_STMT *stmt)`

Description

Returns the number of parameter markers present in the prepared statement.

Return Values

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

Errors

None.

Example

For the usage of `mysql_stmt_param_count()`, refer to the Example from Section 22.2.7.10, “`mysql_stmt_execute()`”.

#### 22.2.7.19. `mysql_stmt_param_metadata()`

`MYSQL_RES *mysql_stmt_param_metadata(MYSQL_STMT *stmt)`

This function currently does nothing.

Description

Return Values

Errors

#### 22.2.7.20. `mysql_stmt_prepare()`

`int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length)`

Description

Given the statement handle returned by `mysql_stmt_init()`, prepares the SQL statement pointed to by the string `query` and returns a status value. The string length should be given by the `length` argument. The string 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 by embedding question mark (‘`?`’) characters into the SQL string at the appropriate positions.

The markers are legal only in certain places in SQL statements. For example, they are allowed in the `VALUES()` list of an `INSERT` statement (to specify column values for a row), or in a comparison with a column in a `WHERE` clause to specify a comparison value. However, they are not allowed for identifiers (such as table or column names), or to specify both operands of a binary operator such as the `=` equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

The parameter markers must be bound to application variables using `mysql_stmt_bind_param()` before executing the statement.

Return Values

Zero if the statement was prepared successfully. Non-zero 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.

If the prepare operation was unsuccessful (that is, `mysql_stmt_prepare()` returns non-zero), the error message can be obtained by calling `mysql_stmt_error()`.

Example

For the usage of `mysql_stmt_prepare()`, refer to the Example from Section 22.2.7.10, “`mysql_stmt_execute()`”.

#### 22.2.7.21. `mysql_stmt_reset()`

`my_bool mysql_stmt_reset(MYSQL_STMT *stmt)`

Description

Reset the prepared statement on the client and server to state after prepare. This is mainly used to reset data sent with `mysql_stmt_send_long_data()`. Any open cursor for the statement is closed.

To re-prepare the statement with another query, use `mysql_stmt_prepare()`.

Return Values

Zero if the statement was reset 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.

#### 22.2.7.22. `mysql_stmt_result_metadata()`

`MYSQL_RES *mysql_stmt_result_metadata(MYSQL_STMT *stmt)`

Description

If a statement passed to `mysql_stmt_prepare()` is one that produces a result set, `mysql_stmt_result_metadata()` returns the result set metadata in the form of a pointer to a `MYSQL_RES` structure that can be used to process the meta information such as total number of fields and individual field information. This result set pointer can be passed as an argument to any of the field-based API functions that process result set metadata, such as:

- `mysql_num_fields()`

- `mysql_fetch_field()`

- `mysql_fetch_field_direct()`

- `mysql_fetch_fields()`

- `mysql_field_count()`

- `mysql_field_seek()`

- `mysql_field_tell()`

- `mysql_free_result()`

The result set structure should be freed when you are done with it, which you can do by passing it to `mysql_free_result()`. This is similar to the way you free a result set obtained from a call to `mysql_store_result()`.

The result set returned by `mysql_stmt_result_metadata()` contains only metadata. It does not contain any row results. The rows are obtained by using the statement handle with `mysql_stmt_fetch()`.

Return Values

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

Errors

- `CR_OUT_OF_MEMORY`

  Out of memory.

- `CR_UNKNOWN_ERROR`

  An unknown error occurred.

Example

For the usage of `mysql_stmt_result_metadata()`, refer to the Example from Section 22.2.7.11, “`mysql_stmt_fetch()`”.

#### 22.2.7.23. `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. The `offset` value is a row offset that should be a value returned from `mysql_stmt_row_tell()` or from `mysql_stmt_row_seek()`. This value is not a row number; if you want to seek to a row within a result set by number, use `mysql_stmt_data_seek()` instead.

This function requires that the result set structure contains the entire result of the query, so `mysql_stmt_row_seek()` may be used only in conjunction with `mysql_stmt_store_result()`.

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.

#### 22.2.7.24. `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_stmt_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.

#### 22.2.7.25. `mysql_stmt_send_long_data()`

`my_bool mysql_stmt_send_long_data(MYSQL_STMT *stmt, unsigned int parameter_number, const char *data, unsigned long length)`

Description

Allows an application to send parameter data to the server in pieces (or “chunks”). This function can be called multiple times to send the parts of a character or binary data value for a column, which must be one of the `TEXT` or `BLOB` data types.

`parameter_number` indicates which parameter to associate the data with. Parameters are numbered beginning with 0. `data` is a pointer to a buffer containing data to be sent, and `length` indicates the number of bytes in the buffer.

Note: The next `mysql_stmt_execute()` call ignores the bind buffer for all parameters that have been used with `mysql_stmt_send_long_data()` since last `mysql_stmt_execute()` or `mysql_stmt_reset()`.

If you want to reset/forget the sent data, you can do it with `mysql_stmt_reset()`. See Section 22.2.7.21, “`mysql_stmt_reset()`”.

Return Values

Zero if the data is sent successfully to server. 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_OUT_OF_MEMORY`

  Out of memory.

- `CR_UNKNOWN_ERROR`

  An unknown error occurred.

Example

The following example demonstrates how to send the data for a `TEXT` column in chunks. It inserts the data value `'MySQL - The most popular Open Source database'` into the `text_column` column. The `mysql` variable is assumed to be a valid connection handle.

    #define INSERT_QUERY "INSERT INTO test_long_data(text_column) VALUES(?)"

    MYSQL_BIND bind[1];
    long       length;

    smtt = mysql_stmt_init(mysql);
    if (!stmt)
    {
      fprintf(stderr, " mysql_stmt_init(), out of memory\n");
      exit(0);
    }
    if (mysql_stmt_prepare(stmt, INSERT_QUERY, strlen(INSERT_QUERY)))
    {
      fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed");
      fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
      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_stmt_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_stmt_send_long_data(stmt,0,"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_stmt_send_long_data(stmt,0," - 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_stmt_execute(stmt))
    {
      fprintf(stderr, "\n mysql_stmt_execute failed");
      fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
      exit(0);
    }

#### 22.2.7.26. `mysql_stmt_sqlstate()`

`const char *mysql_stmt_sqlstate(MYSQL_STMT *stmt)`

Description

For the statement specified by `stmt`, `mysql_stmt_sqlstate()` returns a null-terminated string containing the SQLSTATE error code for the most recently invoked prepared statement API function that can succeed or fail. The error code consists of five characters. `"00000"` means “no error.” The values are specified by ANSI SQL and ODBC. For a list of possible values, see Appendix B, Error Codes and Messages.

Note that not all MySQL errors are yet mapped to SQLSTATE codes. The value `"HY000"` (general error) is used for unmapped errors.

Return Values

A null-terminated character string containing the SQLSTATE error code.

#### 22.2.7.27. `mysql_stmt_store_result()`

`int mysql_stmt_store_result(MYSQL_STMT *stmt)`

Description

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

It is unnecessary to call `mysql_stmt_store_result()` for other statements, but if you do, it does not harm or cause any notable performance problem. You can detect whether the statement produced a result set by checking if `mysql_stmt_result_metadata()` returns `NULL`. For more information, refer to Section 22.2.7.22, “`mysql_stmt_result_metadata()`”.

Note: MySQL doesn't by default calculate `MYSQL_FIELD->max_length` for all columns in `mysql_stmt_store_result()` because calculating this would slow down `mysql_stmt_store_result()` considerably and most applications doesn't need `max_length`. If you want `max_length` to be updated, you can call `mysql_stmt_attr_set(MYSQL_STMT, STMT_ATTR_UPDATE_MAX_LENGTH, &flag)` to enable this. See Section 22.2.7.3, “`mysql_stmt_attr_set()`”.

Return Values

Zero if the results are buffered successfully. Non-zero 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.

### 22.2.8. C API Prepared statement problems

Here follows a list of the currently known problems with prepared statements:

- `TIME`, `TIMESTAMP`, and `DATETIME` do not support parts of seconds (for example from `DATE_FORMAT()`.

- When converting an integer to string, `ZEROFILL` is honored with prepared statements in some cases where the MySQL server doesn't print the leading zeros. (For example, with `MIN(number-with-zerofill)`).

- When converting a floating point number to a string in the client, the rightmost digits of the converted value may differ slightly from those of the original value.

- Prepared statements do not use the Query Cache, even in cases where a query does not contain any placeholders. See Section 5.14.1, “How the Query Cache Operates”.

- Prepared statements do not support multi-statements (that is, multiple statements within a single string separated by ‘`;`’ characters). This also means that prepared statements cannot invoke stored procedures that return result sets, because prepared statements do not support multiple result sets.

### 22.2.9. C API Handling of Multiple Query Execution

MySQL 5.0 supports the execution of multiple statements specified in a single query string. To use this capability with a given connection, you must specify the `CLIENT_MULTI_STATEMENTS` option in the `flags` parameter to `mysql_real_connect()` when opening the connection. You can also set this for an existing connection by calling `mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)`.

By default, `mysql_query()` and `mysql_real_query()` return 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_STATEMENTS */
    mysql_real_connect(..., CLIENT_MULTI_STATEMENTS);

    /* 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");
    do
    {
      /* Process all results */
      ...
      printf("total affected rows: %lld", mysql_affected_rows(mysql));
      ...
      if (!(result= mysql_store_result(mysql)))
      {
         printf(stderr, "Got fatal error processing query\n");
         exit(1);
      }
      process_result_set(result); /* client function */
      mysql_free_result(result);
    } while (!mysql_next_result(mysql));

The multiple-statement capability can be used with `mysql_query()` or `mysql_real_query()`. It cannot be used with the prepared statement interface. Prepared statement handles are defined to work only with strings that contain a single statement.

### 22.2.10. C API Handling of Date and Time Values

The binary protocol allows you to send and receive date and time values (`DATE`, `TIME`, `DATETIME`, and `TIMESTAMP`), using the `MYSQL_TIME` structure. The members of this structure are described in Section 22.2.5, “C API Prepared Statement Data types”.

To send temporal data values, create a prepared statement using `mysql_stmt_prepare()`. Then, before calling `mysql_stmt_execute()` to execute the statement, use the following procedure to set up each temporal parameter:

1. In the `MYSQL_BIND` structure associated with the data value, set the `buffer_type` member to the type that indicates what kind of temporal value you're sending. For `DATE`, `TIME`, `DATETIME`, or `TIMESTAMP` values, set `buffer_type` to `MYSQL_TYPE_DATE`, `MYSQL_TYPE_TIME`, `MYSQL_TYPE_DATETIME`, or `MYSQL_TYPE_TIMESTAMP`, respectively.

2. Set the `buffer` member of the `MYSQL_BIND` structure to the address of the `MYSQL_TIME` structure in which you pass the temporal value.

3. Fill in the members of the `MYSQL_TIME` structure that are appropriate for the type of temporal value to be passed.

Use `mysql_stmt_bind_param()` to bind the parameter data to the statement. Then you can call `mysql_stmt_execute()`.

To retrieve temporal values, the procedure is similar, except that you set the `buffer_type` member to the type of value you expect to receive, and the `buffer` member to the address of a `MYSQL_TIME` structure into which the returned value should be placed. Use `mysql_bind_results()` to bind the buffers to the statement after calling `mysql_stmt_execute()` and before fetching the results.

Here is a simple example that inserts `DATE`, `TIME`, and `TIMESTAMP` data. The `mysql` variable is assumed to be a valid connection handle.

      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_stmt_init(mysql);
      if (!stmt)
      {
        fprintf(stderr, " mysql_stmt_init(), out of memory\n");
        exit(0);
      }
      if (mysql_stmt_prepare(mysql, query, strlen(query)))
      {
        fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed");
        fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
        exit(0);
      }

      /* set up 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_stmt_bind_param(stmt, bind);

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

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

      mysql_stmt_execute(stmt);
      ..

### 22.2.11. C API Threaded Function Descriptions

You need to use the following functions when you want to create a threaded client. See Section 22.2.15, “How to Make a Threaded Client”.

#### 22.2.11.1. `my_init()`

`void my_init(void)`

Description

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

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

Return Values

None.

#### 22.2.11.2. `mysql_thread_init()`

`my_bool mysql_thread_init(void)`

Description

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

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

Return Values

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

#### 22.2.11.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.

#### 22.2.11.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 if the client is thread-safe, 0 otherwise.

### 22.2.12. C API Embedded Server Function Descriptions

If you want to allow your application to be linked against the embedded MySQL server library, you must use the `mysql_server_init()` and `mysql_server_end()` functions. See Section 22.1, “libmysqld, the Embedded MySQL Server Library”.

However, to provide improved memory management, even programs that are linked with `-lmysqlclient` rather than `-lmysqld` should include calls to begin and end use of the library. As of MySQL 5.0.3, the `mysql_library_init()` and `mysql_library_end()` functions can be used to do this. These actually are `#define` symbols that make them equivalent to `mysql_server_init()` and `mysql_server_end()`, but the names more clearly indicate that they should be called when beginning and ending use of a MySQL C API library no matter whether the application uses `libmysqlclient` or `libmysqld`. For more information, see Section 22.2.2, “C API Function Overview”.

#### 22.2.12.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 the server and initializes any subsystems (`mysys`, `InnoDB`, and so forth) that the server uses. If this function is not called, the next call to `mysql_init()` executes `mysql_server_init()`. 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.

If you want to connect to an external server without starting the embedded server, you have to specify a negative value for `argc`.

The `NULL`-terminated list of strings in `groups` selects which groups in the option files are active. See Section 4.3.2, “Using Option Files”. For convenience, `groups` may be `NULL`, in which case the `[server]` and `[embedded]` groups are 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) {
      if (mysql_server_init(sizeof(server_args) / sizeof(char *),
                            server_args, server_groups))
        exit(1);

      /* Use any MySQL API functions here */

      mysql_server_end();

      return EXIT_SUCCESS;
    }

Return Values

0 if okay, 1 if an error occurred.

#### 22.2.12.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.

### 22.2.13. Common questions and problems when using the C API

#### 22.2.13.1. Why `mysql_store_result()` Sometimes Returns `NULL` After `mysql_query()` Returns Success

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()`.

#### 22.2.13.2. What Results You Can 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`.

  For a fast re-create, use `TRUNCATE TABLE`.

- `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 22.2.3.36, “`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.

#### 22.2.13.3. How to Get the Unique ID for the Last Inserted Row

If you insert a record into a table that contains an `AUTO_INCREMENT` column, you can obtain the value stored into that column by calling the `mysql_insert_id()` function.

You can check from your C applications whether a value was stored in an `AUTO_INCREMENT` column by executing the following code (which assumes that you've checked that the statement succeeded). It determines whether the query was an `INSERT` with an `AUTO_INCREMENT` index:

    if ((result = mysql_store_result(&mysql)) == 0 &&
        mysql_field_count(&mysql) == 0 &&
        mysql_insert_id(&mysql) != 0)
    {
        used_id = mysql_insert_id(&mysql);
    }

For more information, see Section 22.2.3.36, “`mysql_insert_id()`”.

When a new `AUTO_INCREMENT` value has been generated, you can also obtain it by executing a `SELECT LAST_INSERT_ID()` statement with `mysql_query()` and retrieving the value from the result set returned by the statement.

For `LAST_INSERT_ID()`, the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even 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

Note that `mysql_insert_id()` returns the value stored into an `AUTO_INCREMENT` column, whether that value is automatically generated by storing `NULL` or `0` or was specified as an explicit value. `LAST_INSERT_ID()` returns only automatically generated `AUTO_INCREMENT` values. If you store an explicit value other than `NULL` or `0`, it does not affect the value returned by `LAST_INSERT_ID()`.

#### 22.2.13.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.

### 22.2.14. 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` options in 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` in the link command.

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

To make it simpler to compile MySQL programs on Unix, we have provided the mysql_config script for you. See Section 22.9.2, “mysql_config — Get Compile Options for Compiling Clients”.

You can use it to compile a MySQL client as follows:

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

The `sh -c` is needed to get the shell not to treat the output from mysql_config as one word.

### 22.2.15. 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.

To avoid aborting the program when a connection terminates, MySQL blocks `SIGPIPE` on the first call to `mysql_server_init`(), `mysql_init()`, or `mysql_connect()`. If you want to use your own `SIGPIPE` handler, you should first call `mysql_server_init()` and then install your handler.

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 22.2, “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 22.2.11, “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 creates a thread-safe client library `libmysqlclient_r`. (Assuming that 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 creates 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 does not have the necessary thread-specific variables in place and you are likely to end up with a core dump sooner or later.

To 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 frees 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.

## 22.3. MySQL PHP API

PHP is a server-side, HTML-embedded scripting language that may be used to create dynamic Web pages. It is available for most operating systems and Web servers, and can access most common databases, including MySQL. PHP may be run as a separate program or compiled as a module for use with the Apache Web server.

PHP actually provides two different MySQL API extensions:

- `mysql`: Available for PHP versions 4 and 5, this extension is intended for use with MySQL versions prior to MySQL 4.1. This extension does not support the improved authentication protocol used in MySQL 5.0, nor does it support prepared statements or multiple statements. If you wish to use this extension with MySQL 5.0, you will likely want to configure the MySQL server to use the --old-passwords option (see Section A.2.3, “`Client does not support authentication protocol`”). This extension is documented on the PHP Web site at http://php.net/mysql.

- `mysqli` - Stands for “MySQL, Improved”; this extension is available only in PHP 5. It is intended for use with MySQL 4.1.1 and later. This extension fully supports the authentication protocol used in MySQL 5.0, as well as the Prepared Statements and Multiple Statements APIs. In addition, this extension provides an advanced, object-oriented programming interface. You can read the documentation for the `mysqli` extension at http://php.net/mysqli. A helpful article can be found at http://www.zend.com/php5/articles/php5-mysqli.php.

If you're experiencing problems with enabling both the `mysql` and the `mysqli` extension when building PHP on Linux yourself, see Section 22.3.2, “Enabling Both `mysql` and `mysqli` in PHP”.

The PHP distribution and documentation are available from the PHP Web site. MySQL provides the `mysql` and `mysqli` extensions for the Windows operating system for MySQL versions as of 5.0.18 on http://dev.mysql.com/downloads/connector/php/. You can find information why you should preferably use the extensions provided by MySQL on that page.

### 22.3.1. Common Problems with MySQL and PHP

- `Error: Maximum Execution Time Exceeded`: This is a PHP limit; go into the `php.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 16MB instead of 8MB.

- `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 process 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`.

- `Error: Client does not support authentication protocol`: This is most often encountered when trying to use the older `mysql` extension with MySQL 4.1.1 and later. Possible solutions are: downgrade to MySQL 4.0; switch to PHP 5 and the newer `mysqli` extension; or configure the MySQL server with `--old-passwords`. (See Section A.2.3, “`Client does not support authentication protocol`”, for more information.)

Those with PHP4 legacy code can make use of a compatibility layer for the old and new MySQL libraries, such as this one: http://www.coggeshall.org/oss/mysql2i.

### 22.3.2. Enabling Both `mysql` and `mysqli` in PHP

If you're experiencing problems with enabling both the `mysql` and the `mysqli` extension when building PHP on Linux yourself, you should try the following procedure.

1. Configure PHP like this:

       ./configure --with-mysqli=/usr/bin/mysql_config --with-mysql=/usr

2. Edit the `Makefile` and search for a line that starts with `EXTRA_LIBS`. It might look like this (all on one line):

       EXTRA_LIBS = -lcrypt -lcrypt -lmysqlclient -lz -lresolv -lm -ldl -lnsl
       -lxml2 -lz -lm -lxml2 -lz -lm -lmysqlclient -lz -lcrypt -lnsl -lm
       -lxml2 -lz -lm -lcrypt -lxml2 -lz -lm -lcrypt

   Remove all duplicates, so that the line looks like this (all on one line):

       EXTRA_LIBS = -lcrypt -lcrypt -lmysqlclient -lz -lresolv -lm -ldl -lnsl
       -lxml2

3. Build and install PHP:

       make
       make install

## 22.4. MySQL Perl API

The Perl `DBI` module provides a generic interface for database access. You can write a DBI script that works with many different database engines without change. To use DBI, you must install the `DBI` module, as well as a DataBase Driver (DBD) module for each type of server you want to access. For MySQL, this driver is the `DBD::mysql` module.

Perl DBI is the recommended Perl interface. It replaces an older interface called `mysqlperl`, which should be considered obsolete.

Installation instructions for Perl DBI support are given in Section 2.14, “Perl Installation Notes”.

DBI information is available at the command line, online, or in printed form:

- Once you have the `DBI` and `DBD::mysql` modules installed, you can get information about them at the command line with the `perldoc` command:

      shell> perldoc DBI
      shell> perldoc DBI::FAQ
      shell> perldoc DBD::mysql

  You can also use `pod2man`, `pod2html`, and so forth to translate this information into other formats.

- For online information about Perl DBI, visit the DBI Web site, http://dbi.perl.org/. That site hosts a general DBI mailing list. MySQL AB hosts a list specifically about `DBD::mysql`; see Section 1.7.1, “MySQL Mailing Lists”.

- For printed information, the official DBI book is Programming the Perl DBI (Alligator Descartes and Tim Bunce, O'Reilly & Associates, 2000). Information about the book is available at the DBI Web site, http://dbi.perl.org/.

  For information that focuses specifically on using DBI with MySQL, see MySQL and Perl for the Web (Paul DuBois, New Riders, 2001). This book's Web site is http://www.kitebird.com/mysql-perl/.

## 22.5. MySQL C++ API

`MySQL++` is a MySQL API for C++. Warren Young has taken over this project. More information can be found at http://www.mysql.com/products/mysql++/.

### 22.5.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 run into problems if you try to use the default `libmysql.dll` libraries (that were compiled using VC++) with Borland C++. To avoid this problem, only call `mysql_init()` with `NULL` as an argument, not a pre-allocated `MYSQL` structure.

## 22.6. 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/.

## 22.7. 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/.

## 22.8. 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.

## 22.9. MySQL Program Development Utilities

This section describes some utilities that you may find useful when developing MySQL programs.

- `msql2mysql`

  A shell script that converts `mSQL` programs to MySQL. It doesn't handle every case, but it gives a good start when converting.

- `mysql_config`

  A shell script that produces the option values needed when compiling MySQL programs.

### 22.9.1. msql2mysql — Convert mSQL Programs for Use with MySQL

Initially, the MySQL C API was developed to be very similar to that for the mSQL database system. Because of this, mSQL programs often can be converted relatively easily for use with MySQL by changing the names of the C API functions.

The msql2mysql utility performs the conversion of mSQL C API function calls to their MySQL equivalents. msql2mysql converts the input file in place, so make a copy of the original before converting it. For example, use msql2mysql like this:

    shell> cp client-prog.c client-prog.c.orig
    shell> msql2mysql client-prog.c
    client-prog.c converted

Then examine `client-prog.c` and make any post-conversion revisions that may be necessary.

msql2mysql uses the replace utility to make the function name substitutions. See Section 8.17, “replace — A String-Replacement Utility”.

### 22.9.2. mysql_config — Get Compile Options for Compiling Clients

mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL.

mysql_config supports the following options:

- `--cflags`

  Compiler flags to find include files and critical compiler flags and defines used when compiling the `libmysqlclient` library.

- `--include`

  Compiler options to find MySQL include files. (Note that normally you would use `--cflags` instead of this option.)

- `--libmysqld-libs`, `--embedded`

  Libraries and options required to link with the MySQL embedded server.

- `--libs`

  Libraries and options required to link with the MySQL client library.

- `--libs_r`

  Libraries and options required to link with the thread-safe MySQL client library.

- `--port`

  The default TCP/IP port number, defined when configuring MySQL.

- `--socket`

  The default Unix socket file, defined when configuring MySQL.

- `--version`

  Version number for the MySQL distribution.

If you invoke mysql_config with no options, it displays a list of all options that it supports, and their values:

    shell> mysql_config
    Usage: /usr/local/mysql/bin/mysql_config [options]
    Options:
      --cflags         [-I/usr/local/mysql/include/mysql -mcpu=pentiumpro]
      --include        [-I/usr/local/mysql/include/mysql]
      --libs           [-L/usr/local/mysql/lib/mysql -lmysqlclient -lz
                        -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
      --libs_r         [-L/usr/local/mysql/lib/mysql -lmysqlclient_r
                        -lpthread -lz -lcrypt -lnsl -lm -lpthread]
      --socket         [/tmp/mysql.sock]
      --port           [3306]
      --version        [4.0.16]
      --libmysqld-libs [-L/usr/local/mysql/lib/mysql -lmysqld -lpthread -lz
                        -lcrypt -lnsl -lm -lpthread -lrt]

You can use mysql_config within a command line to include the value that it displays for a particular option. For example, to compile a MySQL client program, use mysql_config as follows:

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

When you use mysql_config this way, be sure to invoke it within backtick (‘```’) characters. That tells the shell to execute it and substitute its output into the surrounding command.

Prev |  | Next
Chapter 21. Precision Math | Home | Chapter 23. Connectors