manual_MySQL_APIs.html

Document Actions

MySQL Reference Manual for version 5.0.3-alpha - 21 MySQL APIs Go to the first, previous, next, last section, table of contents.

21 MySQL APIs

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

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

21.1.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.13 The replace String-Replacement Utility.

21.1.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 and version 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:

CFG=/usr/local/mysql/bin/mysql_config
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 subsitute its output into the surrounding command.

21.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) use the mysqlclient library to communicate with the MySQL server. This means that, for example, you can take advantage of many of the same environment variables that are used by other client programs, because they are referenced from the library. See section 8 MySQL 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 queries and communication packets.

The communication buffer must be large enough to contain a single SQL statement (for client-to-server traffic) and one row of returned data (for server-to-client traffic). Each thread's communication buffer is dynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOB values that contain up to 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 21.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 21.2.16 libmysqld, the Embedded MySQL Server Library.

21.2.1 C API Data types

MYSQL

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

MYSQL_RES

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

MYSQL_ROW

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

MYSQL_FIELD

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

MYSQL_FIELD_OFFSET

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

my_ulonglong

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

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

The MYSQL_FIELD structure contains the members listed here:

char * name

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

char * table

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

char * def

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

enum enum_field_types type

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

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

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

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

unsigned int length

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

unsigned int max_length

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

unsigned int flags

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

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

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

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

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

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

unsigned int decimals

The number of decimals for numeric fields.

21.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 21.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_change_user()	Changes user and database on an open connection.
mysql_charset_name()	Returns the name of the default character set for the connection.
mysql_close()	Closes a server connection.
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 (new in 4.1).
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_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_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 charset of the connection.
mysql_real_query()	Executes an SQL query specified as a counted string.
mysql_reload()	Tells the server to reload the grant tables.
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_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.
mysql_commit()	Commits the transaction.
mysql_rollback()	Rolls back the transaction.
mysql_autocommit()	Toggles autocommit mode on/off.
mysql_more_results()	Checks whether any more results exist.
mysql_next_result()	Returns/initiates the next result in multiple-statement executions.

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 strictly older than 5.0.3, of 0 in newer versions. A value of 1 for this flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up. When you are done with the connection, call mysql_close() to terminate it.

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

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

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

There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once by calling mysql_store_result(). This function acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval by calling mysql_use_result(). This function 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 already been fetched from the server. With mysql_use_result(), mysql_fetch_row() actually retrieves the row from the server. Information about the size of the data in each row is available by calling mysql_fetch_lengths().

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

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

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

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

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

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

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

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

21.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() will return the number of rows matched by the WHERE statement for UPDATE statements.

Note that when you use a REPLACE command, mysql_affected_rows() returns 2 if the new row replaced an old row. This is 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.

21.2.3.2 `mysql_change_user()`

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

Description

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

This function was introduced in MySQL 3.23.3.

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.

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

Return Values

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

Errors

The same that you can get from mysql_real_connect().

CR_COMMANDS_OUT_OF_SYNC: Commands were executed in an improper order.
CR_SERVER_GONE_ERROR: The MySQL server has gone away.
CR_SERVER_LOST: The connection to the server was lost during the query.
CR_UNKNOWN_ERROR: An unknown error occurred.
ER_UNKNOWN_COM_ERROR: The MySQL server doesn't implement this command (probably an old server).
ER_ACCESS_DENIED_ERROR: The user or password was wrong.
ER_BAD_DB_ERROR: The database didn't exist.
ER_DBACCESS_DENIED_ERROR: The user did not have access rights to the database.
ER_WRONG_DB_NAME: The database name was too long.

Example

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

Errors

Same as for mysql_real_connect().

21.2.3.6 `mysql_create_db()`

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

Description

Creates the database named by the db parameter.

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

Return Values

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

Errors

CR_COMMANDS_OUT_OF_SYNC: Commands were executed in an improper order.
CR_SERVER_GONE_ERROR: The MySQL server has gone away.
CR_SERVER_LOST: The connection to the server was lost during the query.
CR_UNKNOWN_ERROR: An unknown error occurred.

Example

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

21.2.3.7 `mysql_data_seek()`

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)

Description

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

21.2.3.9 `mysql_drop_db()`

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

Description

Drops the database named by the db parameter.

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

Return Values

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

Errors

CR_COMMANDS_OUT_OF_SYNC: Commands were executed in an improper order.
CR_SERVER_GONE_ERROR: The MySQL server has gone away.
CR_SERVER_LOST: The connection to the server was lost during the query.
CR_UNKNOWN_ERROR: An unknown error occurred.

Example

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

21.2.3.10 `mysql_dump_debug_info()`

int mysql_dump_debug_info(MYSQL *mysql)

Description

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

Return Values

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

Errors

CR_COMMANDS_OUT_OF_SYNC: Commands were executed in an improper order.
CR_SERVER_GONE_ERROR: The MySQL server has gone away.
CR_SERVER_LOST: The connection to the server was lost during the query.
CR_UNKNOWN_ERROR: An unknown error occurred.

21.2.3.11 `mysql_eof()`

my_bool mysql_eof(MYSQL_RES *result)

Description

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

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

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

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

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

Return Values

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

Errors

None.

Example

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

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

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

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

21.2.3.12 `mysql_errno()`

unsigned int mysql_errno(MYSQL *mysql)

Description

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

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

21.2.3.13 `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 will 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.8.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.

21.2.3.14 `mysql_escape_string()`

You should use mysql_real_escape_string() instead!

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

21.2.3.15 `mysql_fetch_field()`

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)

Description

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

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

If you've called mysql_query() to perform a SELECT on a table but have not called mysql_store_result(), MySQL returns the default blob length (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);
}

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]);
    }
}

21.2.3.19 `mysql_fetch_row()`

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Description

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

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

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

Return Values

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

Errors

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

21.2.3.20 `mysql_field_count()`

unsigned int mysql_field_count(MYSQL *mysql)

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

Description

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

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

See section 21.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.

21.2.3.21 `mysql_field_seek()`

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)

Description

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

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

Return Values

The previous value of the field cursor.

Errors

None.

21.2.3.22 `mysql_field_tell()`

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)

Description

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

Return Values

The current offset of the field cursor.

Errors

None.

21.2.3.23 `mysql_free_result()`

void mysql_free_result(MYSQL_RES *result)

Description

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

21.2.3.29 `mysql_get_server_version()`

unsigned long mysql_get_server_version(MYSQL *mysql)

Description

Returns the version number of the server as an integer.

This function was added in MySQL 4.1.0.

Return Values

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

major_version*10000 + minor_version *100 + sub_version

For example, 4.1.2 is returned as 40102.

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

Errors

None.

21.2.3.30 `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 a 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 will be 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 0xvalue or X'value' format. However, the return value does not include the 0x or X'...'. The caller must supply whichever of those is desired.

mysql_hex_string() was added in MySQL 4.0.23 and 4.1.8.

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

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

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

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

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

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

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

21.2.3.39 `mysql_num_fields()`

unsigned int mysql_num_fields(MYSQL_RES *result)

Or:

unsigned int mysql_num_fields(MYSQL *mysql)

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

Description

Returns the number of columns in a result set.

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

See section 21.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 will happen only if something went wrong.

21.2.3.40 `mysql_num_rows()`

my_ulonglong mysql_num_rows(MYSQL_RES *result)

Description

Returns the number of rows in the result set.

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

Return Values

The number of rows in the result set.

Errors

None.

21.2.3.41 `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_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'. New in 4.1.0.
`MYSQL_OPT_READ_TIMEOUT`	`unsigned int *`	Timeout for reads from server (works currently only on Windows on TCP/IP connections). New in 4.1.1.
`MYSQL_OPT_WRITE_TIMEOUT`	`unsigned int *`	Timeout for writes to server (works currently only on Windows on TCP/IP connections). New in 4.1.1.
`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: disabled) New in 5.0.3.
`MYSQL_SECURE_AUTH`	`my_bool*`	Whether to connect to a server that does not support the new 4.1.1 password hashing. New in 4.1.1.
`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's to connect to. New in 4.1.0.

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 21.2.3.44 `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. New in 4.1.1.
`multi-statements`	Allow the client to send multiple statements in a single string (separated by `;'). New in 4.1.9.
`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 server (New in 4.1)
`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"). New in MySQL 4.1.
`socket`	Default socket file.
`user`	Default user.

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

For more information about option files, see section 4.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.

21.2.3.42 `mysql_ping()`

int mysql_ping(MYSQL *mysql)

Description

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

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

Return Values

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

Errors

CR_COMMANDS_OUT_OF_SYNC: Commands were executed in an improper order.
CR_SERVER_GONE_ERROR: The MySQL server has gone away.
CR_UNKNOWN_ERROR: An unknown error occurred.

21.2.3.43 `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 21.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 21.2.3.20 mysql_field_count().

Return Values

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

Errors

CR_COMMANDS_OUT_OF_SYNC: Commands were executed in an improper order.
CR_SERVER_GONE_ERROR: The MySQL server has gone away.
CR_SERVER_LOST: The connection to the server was lost during the query.
CR_UNKNOWN_ERROR: An unknown error occurred.

21.2.3.44 `mysql_real_connect()`

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

Description

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

The parameters are specified as follows:

The first parameter should be the address of an existing MYSQL structure. Before calling mysql_real_connect() you must call mysql_init() to initialize the MYSQL structure. You can change a lot of connect options with the mysql_options() call. See section 21.2.3.41 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 22.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 will be checked for a match. This allows the database administrator to set up the MySQL privilege system in such a way that users get different privileges depending on whether or not they have specified a password. Note: Do not attempt to encrypt the password before calling mysql_real_connect(); password encryption is handled automatically by the client API.
db is the database name. If db is not NULL, the connection will set the default database to this value.
If port is not 0, the value will be used as the port number for the TCP/IP connection. Note that the host parameter determines the type of the connection.
If unix_socket is not NULL, the string specifies the socket or named pipe that should be used. Note that the host parameter determines the type of the connection.

The value of client_flag is usually 0, but can be set to a combination of the following flags in very special circumstances:

Flag Name	Flag Nescription
`CLIENT_COMPRESS`	Use compression protocol.
`CLIENT_FOUND_ROWS`	Return the number of found (matched) rows, not the number of affected rows.
`CLIENT_IGNORE_SPACE`	Allow spaces after function names. Makes all functions names reserved words.
`CLIENT_INTERACTIVE`	Allow `interactive_timeout` seconds (instead of `wait_timeout` seconds) of inactivity before closing the connection. The client's session `wait_timeout` variable will be 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. New in 4.1.
`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. New in 4.1.
`CLIENT_NO_SCHEMA`	Don't allow the `db_name.tbl_name.col_name` syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs.
`CLIENT_ODBC`	The client is an ODBC client. This changes `mysqld` to be more ODBC-friendly.
`CLIENT_SSL`	Use SSL (encrypted protocol). This option should not be set by application programs; it is set internally in the client library.

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 will read the [client] and [your_prog_name] sections in the `my.cnf' file which will ensure that your program will work, even if someone has set up MySQL in some non-standard way.

Note that upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1 in versions of the API strictly older than 5.0.3, of 0 in newer versions. A value of 1 for this flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up.

21.2.3.45 `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 a 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 will be a null-terminated string. The return value is the length of the encoded string, not including the terminating null character.

Example

char query[1000],*end;

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

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

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

Return Values

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

Errors

None.

21.2.3.46 `mysql_real_query()`

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

Sections

Personal tools

- Services

- support

- Firm

manual_MySQL_APIs.html

Document Actions

Description

Return Values

Errors

Example

Description

Return Values

Errors

Example

Description

Return Values

Errors

Description

Return Values

Errors

Description

Return Values

Errors

Description

Return Values

Errors

Example

Description

Return Values

Errors

Description

Return Values

Errors

Example

Description

Return Values

Errors

Example

Description

Return Values

Errors

Description

Return Values

Errors

Example

Description

Return Values

Errors

Description

Return Values

Errors

Description

Return Values

Errors

Example

Description

Return Values

Errors

Example

Description

Return Values

Errors

Example

Description

Return Values

Errors

Example

Description

Return Values

Errors

Example

Description

Return Values

Errors

Example

Description

Return Values

Errors

Description