C API Functions
The client library functions may be classified into the following categories:
Connection management routines to establish and terminate connections to the Server
Status and error-reporting routines to get error codes and messages
Query construction and execution routines to construct queries and send them to the server
Result set processing routines to handle results from queries that return data
Information routines that provide information about the client, server, protocol version, and the current connection
Administration routines for controlling server operation
Debugging routines to generate debugging information
Deprecated routines that now are considered obsolete
Client library functions within each of these categories are described in detail in the following sections, listed alphabetically within category. Certain parameters appear recurrently in the function descriptions and have a standard meaning:
conn is a pointer to the MYSQL connection handler for a server connection.
res_set is a pointer to a MYSQL_RES result set structure.
field is a pointer to a MYSQL_FIELD column information structure.
row is a MYSQL_ROW data row from a result set.
For brevity, where these parameters are not mentioned in the descriptions of functions in which they occur, you may assume the meanings just given.
Unless otherwise indicated, you may assume any given function is present in the client library at least as far back as MySQL 3.21.0.
Connection Management Routines
These functions allow you to establish and terminate connections to a server, to set options affecting the way connection establishment occurs, and to re-establish connections that have timed out.
my_bool
mysql_change_user (MYSQL *conn, char *user_name, char *password, char *db_name);Changes the user and the default database on the connection specified by conn. The database becomes the default for table references that do not include a database specifier. If db_name is NULL, no default database is selected.
mysql_change_user() returns true if the user is allowed to connect to the server and, if a database was specified, has permission to access the database. Otherwise, the function fails and the current user and database remain unchanged.
void
mysql_close (MYSQL *conn),)Closes the connection specified by conn. Call this routine when you are done with a server session. If the connection handler was allocated automatically by mysql_init(), mysql_close() de-allocates it.
Do not call mysql_close() if the attempt to open a connection failed.
MYSQL *
mysql_init (MYSQL *conn);Initializes a connection handler and returns a pointer to it. If conn points to an existing MYSQL structure, mysql_init() initializes that handler and returns its address:
MYSQL conn_struct, *conn; conn = mysql_init (&conn_struct);
If conn is NULL, mysql_init() allocates a new handler, initializes it, and returns its address:
MYSQL *conn; conn = mysql_init (NULL);
If mysql_init() fails, it returns NULL. This may happen if mysql_init() cannot allocate a new handler.
If mysql_init() allocates the handler, mysql_close() de-allocates it automatically when you close the connection.
int
mysql_options (MYSQL *conn, enum mysql_option option, char *arg);This function allows you to tailor connection behavior more precisely than is possible with mysql_real_connect() alone. Call it after mysql_init() and before mysql_real_connect(). You may call mysql_options() multiple times if you want to set several options.
The option argument specifies which connection option you want to set. Additional information needed to set the option, if any, is specified by the arg argument. (Note that arg is a pointer.) arg is NULL if no additional information is needed.
The following options are available:
MYSQL_INIT_COMMAND
Specifies a query to execute after connecting to the server. arg is a null-terminated string containing the query. The query is executed after reconnecting as well (for example, if you call mysql_ping()). Any result set returned by the query is discarded.
MYSQL_OPT_COMPRESS
Specifies that the connection should use the compressed client/server protocol if the server supports it. arg is NULL.
It is also possible to specify compression when you callmysql_real_connect().
MYSQL_OPT_CONNECT_TIMEOUT
Specifies the connection timeout, in seconds. arg is a pointer to an unsigned int containing the timeout value.
MYSQL_OPT_NAMED_PIPE
Specifies that the connection to the server should use a named pipe. arg is NULL. This option is for Windows 95/98/NT clients only and only for connections to Windows NT servers.
MYSQL_READ_DEFAULT_FILE
Specifies an option file to read for connection parameters. Options are read from the [client] group in the file.
MYSQL_READ_DEFAULT_GROUP
Specifies a group to read from the option file that you name with MYSQL_READ_DEFAULT_FILE. The named group is read in addition to the [client] group. If no option file is named, the client library looks for the standard option files and reads them.
If you call mysql_options() multiple times to set a given option, mysql_real_connect() uses the most recently specified value for that option.
mysql_options() returns zero for success and non-zero if the option value is unknown.
The following options may be used in an option file (in either the [client] group or a group that you specify with the MYSQL_READ_DEFAULT_GROUPoption):
compress database=db_name debug host=host_name init-command=query password=your_password pipe port=port_num return-found-rows socket=socket_name timeout=seconds user=user_name
The mysql_options() calls in the following example set connection options so that mysql_real_connect() connects with a named pipe using the compressed protocol and a timeout of 10 seconds. It reads C:my.cnf.special for information from the [client] and [mygroup] groups. When the connection is established, a SET SQL_BIG_TABLES statement is executed.
MYSQL *conn; unsigned int timeout; if ((conn = mysql_init (NULL)) == NULL) … deal with error … timeout = 10; mysql_options (conn, MYSQL_OPT_CONNECT_TIMEOUT, (char *) &timeout); mysql_options (conn, MYSQL_OPT_COMPRESS, NULL); mysql_options (conn, MYSQL_OPT_NAMED_PIPE, NULL); mysql_options (conn, MYSQL_READ_DEFAULT_FILE, "C:my.cnf.special"); mysql_options (conn, MYSQL_READ_DEFAULT_GROUP, "mygroup"); mysql_options (conn, MYSQL_INIT_COMMAND, "SET SQL_BIG_TABLES=1"); if (mysql_real_connect (conn, …) == NULL) … deal with error …mysql_options() was introduced in MySQL 3.22.1. MYSQL_INIT_COMMAND, MYSQL_READ_DEFAULT_FILE, and MYSQL_READ_DEFAULT_GROUP were introduced in MySQL 3.22.10.
int
mysql_ping (MYSQL *conn);Checks whether the connection indicated by conn is still up. If not, mysql_ping() reconnects using the same parameters that were used initially to make the connection. Thus, you should not call mysql_ping() without first successfully having called mysql_real_connect().
mysql_ping() was introduced in MySQL 3.22.1.
MYSQL *
mysql_real_connect (MYSQL *conn, char *host_name, char *user_name, char *password, char *db_name, unsigned int port_num, char *socket_name, unsigned int flags);Connects to a server and returns a pointer to the connection handler. conn should be a pointer to an existing connection handler that was initialized by mysql_init().The address of the handler is the return value for a successful connection. NULL is returned if an error occurs.
If the connection attempt fails, you can pass the conn handler value to mysql_errno() and mysql_error() to obtain error information. However, you should not pass the conn value to any other client library routines that assume the handler reflects a successful connection.
host_name is the server to connect to. Table F.4 shows how the client attempts to connect for various host_name values for UNIX and Windows clients. (If you connect using a UNIX socket or named pipe, the socket_name parameter specifies the socket or pipe name.)
user_name is your MySQL username. If this is NULL, the client library sends a default name. Under UNIX, the default is your login name. Under Windows, the default is your name as specified in the USER environment variable if that variable is set and "ODBC" otherwise.
password is your password. If this is NULL, you will only be able to connect if the user grant table contains an entry matching your username and the host from which you are connecting and that entry has a blank password.
db_name is the database to use. If this is NULL, no initial database is selected.
port_num is the port number to use for TCP/IP connections. If this is 0, the default port number is used.
socket_name is the socket name to use for connections to the local host. If this is NULL, the default socket name is used.
The port number and socket name are used according to the value of host_name. If you are connecting to the local host, mysql_real_connect() attempts a connection using a UNIX domain socket (under UNIX) or a named pipe (under Windows). Otherwise, it connects using TCP/IP.
flags can be 0 to specify no options or one or more of the values shown in Table F.5. The options affect the operation of the server.
| Flag Value | Effect on Server Operation |
|---|---|
| CLIENT_FOUND_ROWS | For UPDATE queries, return the number of rows matched rather than the number of rows changed. |
| CLIENT_NO_SCHEMA | Don't allow db_name.tbl_name.col_name syntax. |
| CLIENT_COMPRESS | Use compressed communications protocol, if the server supports it. |
| CLIENT_ODBC | Treat the client as an ODBC client. |
If you specify CLIENT_NO_SCHEMA, the server allows only references of the forms tbl_name.col_name, tbl_name, or col_name in queries.
The flag values are bit values, so you can combine them using '|' or '+', for example, CLIENT_COMPRESS | CLIENT_ODBC or CLIENT_COMPRESS+CLIENT_ODBC.
mysql_real_connect() was introduced in MySQL 3.21.10. The db_name parameter was added in 3.22.0. The use of mysql_init() to initialize MYSQL arguments began in 3.22.1.
Status and Error-Reporting Routines
The functions in this section allow you to determine and report the causes of errors.
unsigned int
mysql_errno (MYSQL *conn);Returns an error code for the most recently invoked client library routine that returned a status. The error code is 0 if no error occurred and non-zero otherwise. The possible error codes are listed in the MySQL header files errmsg.h and mysqld_error.h.
if (mysql_errno (conn) == 0) printf ("Everything is okay "); else printf ("Something is wrong! ");mysql_errno() was introduced in MySQL 3.21.7.
char *
mysql_error (MYSQL *conn);Returns a null-terminated string containing an error message for the most recently invoked client library routine that returned a status. The return value is the empty string if no error occurred (this is the zero-length string "" not a NULL pointer). Although normally you call mysql_error() after you already know an error occurred, the return value itself can be used to detect the occurrence of an error:
if (mysql_error (conn)[0] == ' ') /* empty string? */ printf ("Everything is okay "); else printf ("Something is wrong! ");mysql_error() was introduced in MySQL 3.21.7 (possibly a little later).
Query Construction and Execution Routines
The functions in this section allow you to send queries to the server.
mysql_escape_string() helps you construct queries by escaping characters that need special treatment.
unsigned int
mysql_escape_string (char *to_str, char *from_str, unsigned int from_len);Encodes a string that may contain special characters so that it can be used in an SQL statement. Table F.6 lists the characters that are considered special and their encodings.
Table F.6. mysql_escape_string() Character Encodings Special Character Encoding NUL (ASCII 0) (backslash-'0', not backslash-null) Backslash \ (backslash-backslash) Single quote ' (backslash-single quote) Double quote " (backslash-double quote) Newline (backslash-'n') Carriage return (backslash-'r') Ctrl-Z (backslash-'Z') The buffer to be encoded is specified as a counted string.from_str points to that buffer, and from_len indicates the number of bytes in it. mysql_escape_string() writes the result into the buffer pointed to by to_str and adds a null byte. to_str must point to an existing buffer that is at least (from_len*2)+1 bytes long. (In the worst case scenario, every character in from_str might need to be encoded as a two-character sequence, and you also need room for the terminating null.)
mysql_escape_string() returns the length of the encoded string, not counting the terminating null byte.
The resulting encoded string contains no internal nulls but is null-terminated, so you can use it with functions such as strlen() or strcat().
When you write literal strings in your program, take care not to confuse the lexical escape conventions of the C programming language with the encoding done by mysql_escape_string(). Consider the following example, and the output produced by it:
to_len = mysql_escape_string (to_str, " '" 32", 7); printf ("to_len = %d, to_str = %s ", to_len, to_str);The output is:
to_len = 14, to_str = '"
The printed value of to_str looks very much like the string specified as the second argument of the mysql_escape_string() call, but is in fact quite different.
int
mysql_query (MYSQL *conn, char *query_string);Given a query specified as a null-terminated string, mysql_query() sends the query to the server to be executed. The string should not contain binary data; in particular, it should not contain null bytes, which mysql_query() interprets as the end of the query. If your query does contain binary data, use mysql_real_query() instead.
The query must consist of a single SQL statement, and should not end with a semicolon (';') or 'g'. (';' and 'g' are conventions of the mysql client program, not of the client library.)
mysql_query() returns zero for success and non-zero for failure. A successful query is one that the server accepts as legal and executes without error. Success does not imply anything about the number of rows affected or returned.
int
mysql_real_query (MYSQL *conn, char *query_string, unsigned int length);Given a query specified as a counted string, mysql_real_query() sends the query to the server to be executed. The string may contain binary data (including null bytes). The query text is given by query_string, and the length is indicated by length.
The query must consist of a single SQL statement, and should not end with a semicolon (';') or 'g'. (';' and 'g' are conventions of the mysql client program, not of the client library.)
mysql_real_query() returns zero for success and non-zero for failure. A successful query is one that the server accepts as legal and executes without error. Success does not imply anything about the number of rows affected or returned.
int
mysql_select_db (MYSQL *conn, char *db_name);Selects the database named by db_name as the current database, which becomes the default for table references that contain no explicit database specifier. You must have permission to access the database; otherwise, mysql_select_db() fails.
mysql_select_db() returns zero for success, non-zero for failure.
Result Set Processing Routines
When a query produces a result set, the functions in this section allow you to retrieve the set and access its contents. The mysql_store_result() and mysql_use_result() functions create the result set and one or the other must be called before using any other functions in this section. Table F.7 compares the two functions.
my_ulonglong
mysql_affected_rows (MYSQL *conn);Returns the number of rows changed by the most recent DELETE, INSERT, REPLACE, or UPDATE query. For such queries, mysql_affected_rows() may be called immediately after a successful call to mysql_query(). You can also call this function after issuing a statement that returns rows. In this case, the function acts the same way as mysql_num_rows() and is subject to the same constraints on which the value is meaningful, as well as the additional constraint that mysql_affected_rows() is never meaningful when you use mysql_use_result().
mysql_affected_rows() returns zero if no query has been issued or if the query was one that returns rows but no rows were selected. A return value greater than zero indicates the number of rows changed (for DELETE, INSERT, REPLACE, UPDATE) or returned (for queries that return rows). A return value of -1 indicates either an error, or that you (erroneously) called mysql_affected_rows()after issuing a query that returns rows but before actually retrieving the result set. Because mysql_affected_rows() returns an unsigned value, you should perform the comparison by casting its result to a signed value:
if ((long) mysql_affected_rows (conn) == -1) fprintf (stderr, "Error! ");If you have specified that the client should return the number of rows matched for UPDATE queries, mysql_affected_rows() returns that value rather than the number of rows actually modified. (MySQL does not update a row if the columns to be modified are the same as the new values.) This behavior can be selected either by specifying return-found-rows in an option file or by passing CLIENT_FOUND_ROWS in the flags parameter to mysql_real_connect().
See the note about printing values of type my_ulonglong in the section "Scalar Data Types."
void
mysql_data_seek (MYSQL_RES *res_set, unsigned int offset);Seeks to a particular row of the result set. The value of offset may range from 0 to mysql_num_rows(res_set)-1. The results are unpredictable if offset is out of range.
mysql_data_seek() requires that the entire result set has been retrieved, so you can use it only if the result set was created by mysql_store_result(), not by mysql_use_result().
MYSQL_FIELD *
mysql_fetch_field (MYSQL_RES *res_set);Returns a structure containing information (metadata) about a column in the result set. After you successfully execute a query that returns rows, the first call to mysql_fetch_field() returns information about the first column. Subsequent calls return information about successive columns following the first, and NULL when no more columns are left.
You can also call mysql_field_tell() to determine the current column position, or mysql_field_seek() to select a particular column to be returned by the next call to mysql_fetch_field().
The following example seeks the first MYSQL_FIELD, then fetches successive column information structures:
MYSQL_FIELD *
mysql_fetch_fields (MYSQL_RES res_set);Returns an array of all column information structures for the result set. You access them as follows:
MYSQL_FIELD *field; unsigned int i; field = mysql_fetch_fields (res_set); for (i = 0; i < mysql_num_fields (res_set); i++) { printf ("column %u: name = %s max_length = %lu ", i, field[i].name, field[i].max_length); }Compare this to the example shown for mysql_fetch_field(). Note that although both functions return values of the same type, those values are accessed using slightly different syntax for each function.
MYSQL_FIELD *
mysql_fetch_field_direct (MYSQL_RES *res_set, unsigned int field_num);Given a column index, returns the information structure for that column. The value of field_num may range from 0 to mysql_num_fields()-1. The results are unpredictable if field_num is out of range.
The following example accesses MYSQL_FIELD structures directly:
MYSQL_FIELD *field; unsigned int i; for (i = 0; i < mysql_num_fields (res_set); i++) { field = mysql_fetch_field_direct (res_set, i); printf ("column %u: name = %s max_length = %lu ", i, field->name, field->max_length); }mysql_fetch_field_direct() does not work properly prior to MySQL 3.23.
unsigned long *
mysql_fetch_lengths (MYSQL_RES *res_set);Returns a pointer to an array of unsigned long values representing the lengths of the columns in the current row of the result set. You must call mysql_fetch_lengths() each time you call mysql_fetch_row() or your lengths will be out of sync with your data values.
The length for NULL values is zero, but a zero length does not by itself indicatea NULL data value. Because an empty string also has a length of zero, you must check whether or not the data value is a NULL pointer to distinguish between the two cases.
The following example displays lengths and values for the current row, printing "NULL" if the value is NULL:
unsigned long *length; length = mysql_fetch_lengths (res_set); for (i = 0; i < mysql_num_fields (res_set); i++) { printf ("length is %lu, value is %s ", length[i], (row[i] != NULL ? row[i] : "NULL")); }mysql_fetch_lengths() appeared in MySQL 3.20.5. Prior to MySQL 3.22.7, the return type of mysql_fetch_lengths() was unsigned int.
MYSQL_ROW
mysql_fetch_row (MYSQL_RES *res_set);Returns a pointer to the next row of the result set, represented as an array of strings (except that NULL column values are represented as NULL pointers). The i-th value in the row is the i-th member of the value array.
Values for all data types, even numeric types, are returned as strings. If you want to perform a numeric calculation with a value, you must convert it yourself—for example, with atoi() or atof().
mysql_fetch_row() returns NULL when there are no more rows in the data set. (If you use mysql_use_result() to initiate a row-by-row result set retrieval, mysql_fetch_row() also returns NULL if a communications error occurs.)
Data values are null-terminated, but you should not treat values that can contain binary data as null-terminated strings. Treat them as counted strings instead. (To determine column value lengths, call mysql_fetch_lengths().)
The following code shows how to loop through a row of data values and determine whether or not each value is NULL:
MYSQL_ROW row; unsigned int i; while ((row = mysql_fetch_row (res_set)) != NULL) { for (i = 0; i < mysql_num_fields (res_set); i++) { printf ("column %u: value is %s ", i, (row[i] == NULL ? "NULL" : "not NULL")); } }To determine the types of the column values, use the column metadata stored in the MYSQL_FIELD column information structures, obtained by calling mysql_fetch_field(), mysql_fetch_fields(), or mysql_fetch_field_direct().
unsigned int
mysql_field_count (MYSQL *conn);Returns the number of columns for the most recent query on the given connection. This function is usually used when mysql_store_result() or mysql_use_result() return NULL. mysql_field_count() tells you whether or not a result set should have been returned. A return value of 0 indicates no result set and no error. A non-zero value indicates that columns were expected and that, because none were returned, an error occurred.
The following example illustrates how to use mysql_field_count() for error-detection purposes:
res_set = mysql_store_result (conn);
if (res_set == NULL) /* no result set was returned */
{
/*
*does the lack of a result set mean that an error
*occurred or that no result set should be expected?
*/
if (mysql_field_count (conn) > 0)
{
/*
* a result set was expected, but mysql_store_result()
* did not return one; this means an error occurred
*/
printf ("Problem processing result set
");
}
else
{
/*
* a result set was not expected; query returned no data
* (it was not a SELECT, SHOW, DESCRIBE, or EXPLAIN),
* so just report number of rows affected by query
*/
printf ("%lu rows affected
",
(unsigned long) mysql_affected_rows (conn));
}
}
else /* a result set was returned */
{
/* … process rows here … */
mysql_free_result (res_set);
}
mysql_field_count() was introduced in MySQL 3.22.24. Prior to that version, mysql_num_fields() was used for the same purpose. To write code that will work with any version of MySQL, include the following fragment in any file that uses mysql_field_count():
#if !defined(MYSQL_VERSION_ID) || MYSQL_VERSION_ID<32224 #define mysql_field_count mysql_num_fields #endif
This maps mysql_field_count() to mysql_num_fields() when the source is compiled under older versions of MySQL.
MYSQL_FIELD_OFFSET
mysql_field_seek (MYSQL_RES *res_set, MYSQL_FIELD_OFFSET offset);Seeks to the column information structure specified by offset. The next call to mysql_fetch_field() will return the information structure for the given column. offset is not a column index; it is a MYSQL_FIELD_OFFSET value obtained from an earlier call to mysql_field_tell() or to mysql_field_seek().
To reset to the first column, pass an offset value of 0.
MYSQL_FIELD_OFFSET
mysql_field_tell (MYSQL_RES *res_set);Returns the current column information structure offset. This value may be passed to mysql_field_seek().
void
mysql_free_result (MYSQL_RES *res_set);De-allocates the memory used by the result set. You must call mysql_free_result() for each result set you work with. Typically, result sets are generated by calling mysql_store_result() or mysql_use_result(). However, some client library functions generate result sets implicitly, and you are responsible for freeing those sets, too. These functions are mysql_list_dbs(), mysql_list_fields(), mysql_list_processes(), and mysql_list_tables().
char *
mysql_info (MYSQL *conn);Returns a string containing information about the effect of the most recently executed query of the following types. The string format is given immediately following each query:
ALTER TABLE … Records: 0 Duplicates: 0 Warnings: 0 INSERT INTO … SELECT … Records: 0 Duplicates: 0 Warnings: 0 INSERT INTO … VALUES (…),(…),… Records: 0 Duplicates: 0 Warnings: 0 LOAD DATA … Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 UPDATE … Rows matched: 0 Changed: 0 Warnings: 0The numbers will vary according to the particular query you've executed, of course.
For statements not shown in the preceding list, mysql_info() returns NULL. mysql_info() returns non-NULL for INSERT INTO … VALUES only if the statement contains more than one value list.
The string returned by mysql_info() is in the language used by the server, so you can't necessarily count on being able to parse it by looking for certain words.
my_ulonglong
mysql_insert_id (MYSQL conn);Returns the AUTO_INCREMENT value generated by the most recently executed query on the given connection. Returns zero if no query has been executed or if the previous query did not generate an AUTO_INCREMENT value. This means you should call mysql_insert_id() immediately after a query that you expect to generate a new value. If any other query intervenes between that query and the point at which you want to use the value, the value of mysql_insert_id() will be reset by the intervening query.
A zero return value is distinct from any valid AUTO_INCREMENT value because such values start at 1. (Exception: If you create an AUTO_INCREMENT column and then insert a literal negative number, the sequence will begin with that number and may eventually reach 0 as a valid member of the sequence. It's assumed in this case that you know what you're doing, particularly because mysql_insert_id() returns an unsigned number and you'll need to play tricks with the return value.)
Note that the behavior of mysql_insert_id() differs from that of the SQL function LAST_INSERT_ID(). mysql_insert_id() is maintained in the client and is set for each query. The value of LAST_INSERT_ID() is maintained in the server and persists from query to query.
The values returned by mysql_insert_id() are connection specific and are not affected by AUTO_INCREMENT activity on other connections.
See the note about printing values of type my_ulonglong in the section "Scalar Data Types."
unsigned int
mysql_num_fields (MYSQL_RES *res_set);Returns the number of columns in the result set. mysql_num_rows() is often used to iterate through the columns of the current row of the set, as illustrated by the following example:
MYSQL_ROW row; unsigned int i; while ((row = mysql_fetch_row (res_set)) != NULL) { for (i = 0; i < mysql_num_fields (res_set); i++) { /* do something with row[i] here … */ } }Prior to MySQL 3.22.24, mysql_num_fields() was also used to perform the function now performed by mysql_field_count()—that is, to test whether or not a NULL return from mysql_store_result() or mysql_use_result() indicates an error. This is why in older source code you will sometimes see mysql_num_fields() being called with a pointer to a connection handler rather than with a pointer to a result set. mysql_num_fields() used to be callable both ways. Use of mysql_num_fields() with a connection handler is now deprecated. You should write programs using mysql_field_count() instead; the description for that function shows how to use it even for older versions of MySQL.
my_ulonglong
mysql_num_rows (MYSQL_RES *res_set);Returns the number of rows in the result set. If you generate the result set with mysql_store_result(), you can call mysql_num_rows() anytime thereafter:
if ((res_set = mysql_store_result (conn)) == NULL) { /* mysql_num_rows() can be called now */ }If you generate the result set with mysql_use_result(), mysql_num_rows() doesn't return the correct value until you have fetched all the rows:
if ((res_set = mysql_use_result (conn)) == NULL) { /* mysql_num_rows() cannot be called yet */ while ((row = mysql_fetch_row (res_set)) != NULL) { /* mysql_num_rows() still cannot be called */ } /* mysql_num_rows() can be called now */See the note about printing values of type my_ulonglong in the section "Scalar Data Types."
MYSQL_ROW_OFFSET
mysql_row_seek (MYSQL_RES *res_set, MYSQL_ROW_OFFSET offset);Seeks to a particular row of the result set. mysql_row_seek() is similar to mysql_data_seek(), but the offset value is not a row number. offset must be a value obtained from a call to mysql_row_tell() or mysql_row_seek().
mysql_row_seek() returns the previous row offset.
mysql_row_seek() requires that the entire result set has been retrieved, so you can use it only if the result set was created by mysql_store_result(), not by mysql_use_result().
MYSQL_ROW_OFFSET
mysql_row_tell (MYSQL_RES *res_set);Returns an offset representing the current row position in the result set. This is not a row number; the value may be passed only to mysql_row_seek(), not to mysql_data_seek().
mysql_row_tell() requires that the entie result set has been retrieved, so you can use it only if the result set was created by mysql_store_result(), not by mysql_use_result().
MYSQL_RES *
mysql_store_result (MYSQL *conn);Following a successful query, returns the result set and stores it in the client. Returns NULL if the query returns no data or an error occurred. When mysql_store_result() returns NULL, call mysql_field_count() or one of the error-reporting functions to determine whether a result set was not expected or whether an error occurred.
When you are done with the result set, pass it to mysql_free_result() to de-allocate it.
See the comparison of mysql_store_result() and mysql_use_result() in Table F.7.
MYSQL_RES *
mysql_use_result (MYSQL *conn);Following a successful query, initiates a result set retrieval but does not retrieve any data rows itself. You must call mysql_fetch_row() to fetch the rows one by one. Returns NULL if the query returns no data or an error occurred. When mysql_use_result() returns NULL, call mysql_field_count() or one of the error-reporting functions to determine whether a result set was not expected or whether an error occurred.
When you are done with the result set, pass it to mysql_free_result() to de-allocate it.
See the comparison of mysql_store_result() and mysql_use_result() inTable F.7.
mysql_store_result() and mysql_use_result() both are used to retrieve result sets, but they affect the way you can use other result set–handling functions.
Information Routines
These functions provide information about the client, server, protocol version, and the current connection.
char *
mysql_get_client_info (void);Returns a null-terminated string describing the client library version—for example, "3.22.25".
char *
mysql_get_host_info (MYSQL *conn);Returns a null-terminated string describing the current connection—for example, "Localhost via UNIX socket" or "your.host.com via TCP/IP".
unsigned int
mysql_get_proto_info (MYSQL *conn);Returns a number indicating the client/server protocol version used for the current connection.
char *
mysql_get_server_info (MYSQL *conn);Returns a null-terminated string describing the server version—for example, "3.22.25-log". The suffixes you may see following the server version number are -log (logging is on), -debug (the server is running debug mode), or -demo (the server is running in demo mode).
char *
mysql_stat (MYSQL *conn);Returns a null-terminated string containing server status information or NULL if an error occurred. The format of the string is subject to change. Currently it looks something like this:
Uptime: 864034 Threads: 1 Questions: 32736 Slow queries: 50 Opens: 1428 Flush tables: 1 Open tables: 61
These values may be interpreted as follows:
Uptime is the number of seconds the server has been up.
Threads is the number of threads currently running in the server.
Questions is the number of queries the server has executed.
Slow queries is the number of queries that take longer than the server's long_query_time parameter.
Opens is the number of tables the server has opened.
Flush tables is the number of times that FLUSH, REFRESH, and RELOAD commands have been executed.
Open tables is the number of tables the server currently has open.
The information returned by mysql_stat() is the same as that reported by the mysqladmin status command. (Where do you think mysqladmin gets the information?)
unsigned long
mysql_thread_id (MYSQL *conn);Returns the thread number that the server associates with the current connection. You can use this number as an identifier for mysql_kill().
It's not a good idea to execute mysql_thread_id() until just before you need the value. If you get the value and store it, intending to use it later, the value may be incorrect. This can happen if your connection goes down and then is re-established (for example, with mysql_ping()) because the server will assign a new thread identifier.
Administrative Routines
The functions in this section allow you to control aspects of server operation.
int
mysql_kill (MYSQL *conn, unsigned long thread_id);Kill the server thread identified by thread_id.
If you have the PROCESS privilege, you can kill any thread. Otherwise, you can kill only your own threads.
mysql_kill() returns zero for success and non-zero for failure.
int
mysql_refresh (MYSQL *conn, unsigned int options);This function is similar in effect to the SQL FLUSH statement, except that you can tell the server to flush several kinds of things at once. The options value should be one or more of the values shown in Table F.8.
Table F.8. mysql_refresh() Options Option Value Action Taken by the Server REFRESH_GRANT Reload grant table contents REFRESH_LOG Begin new general and update logs (whichever ones are currently open) REFRESH_TABLES Close all open tables REFRESH_HOSTS Flush the host cache REFRESH_STATUS Reset status variables to zero The options in Table F.8 are bit values, so you can combine them using '|' or '+'—for example, REFRESH_LOG|REFRESH_TABLES or REFRESH_LOG+REFRESH_TABLES.
For more information on flush operations, see the description of the FLUSH statement in Appendix D, "SQL Syntax Reference."
mysql_refresh() returns zero for success and non-zero for failure.
int
mysql_shutdown (MYSQL *conn);Instructs the server to shut down. You must have the SHUTDOWN privilege to do this.
mysql_shutdown() returns zero for success, non-zero for failure.
Debugging Routines
These functions allow you to generate debugging information on either the client or server end of the connection. This requires MySQL to be compiled to support debugging. (Use the --with-debug option when you configure the MySQL distribution.)
void
mysql_debug (char *debug_string);Performs a DBUG_PUSH operation using the string debug_string. The format of the string is described in the MySQL Reference Manual.
To use mysql_debug(), the client library must be compiled with debugging support.
int
mysql_dump_debug_info (MYSQL *conn);Instructs the server to write debugging information to the log. You must have the PROCESS privilege to do this.
mysql_dump_debug_info() returns zero for success, non-zero for failure.
Deprecated Routines
The MySQL client library includes a number of functions that now are deprecated because there are preferred ways to do the same thing. Most of these functions can be replaced by passing an equivalent query to mysql_query(). For example, mysql_create_db("db_name") can be replaced with this call:
mysql_query (conn, "CREATE DATABASE db_name")
A few functions, such as mysql_connect() and mysql_eof(), are deprecated because they have been replaced by functions that do more or that provide more information.
Over time, more functions have become deprecated as MySQL understands more SQL statements. For example, when the SQL FLUSH PRIVILEGES statement was added, mysql_reload() became deprecated. The following descriptions indicate the version of MySQL at which each function became deprecated and the preferred way to perform each function now. If your client library is older than the MySQL version listed, you must still use the deprecated function, of course.
If you want to plan for the future, you should avoid all functions listed in this section. Some or all of them will disappear in MySQL 4.0.
MYSQL *
mysql_connect (MYSQL *conn, char *host_name, char *user_name, char *password);This is the predecessor of mysql_real_connect(). It is, in fact, now implemented as a call to mysql_real_connect().
This function is deprecated as of MySQL 3.22.0.
int
mysql_create_db (MYSQL *conn, char *db_name);Creates a database with the name given by db_name. This can be done now by issuing a CREATE DATABASE command with mysql_query().
mysql_create_db() returns zero for success and non-zero for failure.
This function is deprecated as of MySQL 3.21.15.
int
mysql_drop_db (MYSQL *conn, char *db_name);Drops the database named by db_name. This can be done now by issuing a DROP DATABASE command with mysql_query().
mysql_drop_db() returns zero for success and non-zero for failure.
This function is deprecated as of MySQL 3.21.15.
my_bool
mysql_eof (MYSQL_RES *res_set);Returns non-zero if the end of a result set has been reached and zero if an error occurred. mysql_eof() is used when you use the combination of mysql_use_result() to initiate a result set retrieval and mysql_fetch_row() to fetch the data rows one at a time. With mysql_use_result(), a NULL return from mysql_fetch_row() may mean either that the end of the set has been reached or that an error occurred. mysql_eof() distinguishes between the two outcomes.
mysql_errno() and mysql_error() may be used now to achieve the same effect, though actually they return more information. (They indicate the reason for any error that may have occurred, rather than simply whether or not it did occur.)
This function is deprecated as of MySQL 3.21.17.
MYSQL_RES *
mysql_list_dbs (MYSQL *conn, char *wild);Returns a result set listing database names on the server or NULL if an error occurred. The list contains all databases matching the SQL regular expression indicated by wild (which may contain the wildcard characters '%' and '_') or all databases if wild is NULL. You are responsible for calling mysql_free() to free the result set.
The list produced by mysql_list_dbs() may be obtained by executing a SHOW DATABASES command with mysql_query() and then processing the result set.
This function is deprecated as of MySQL 3.22.0.
MYSQL_RES *
mysql_list_fields (MYSQL *conn, char *tbl_name, char *wild);Returns a result set listing column names in the given table or NULL if an error occurred. The list contains all column names matching the SQL regular expression indicated by wild (which may contain the wildcard characters '%' and '_') or all ccolumns if wild is NULL. You are responsible for calling mysql_free() to free the result set.
The list produced by mysql_list_fields() may be obtained by executing a SHOW COLUMNS command with mysql_query() and then processing the result set.
This function is deprecated as of MySQL 3.22.0.
MYSQL_RES *
mysql_list_processes (MYSQL *conn);Returns a result set containing a list of the processes running in the server or NULL if an error occurred. If you have the PROCESS privilege, the list contains all server processes. If you do not, the list contains only your own processes. You are responsible for calling mysql_free() to free the result set.
The list produced by mysql_list_fields() may be obtained by executing a SHOW PROCESSLIST query with mysql_query() and then processing the result set.
This function is deprecated as of MySQL 3.22.0.
MYSQL_RES *
mysql_list_tables (MYSQL *conn, char *wild);Returns a result set listing tables in the current database or NULL if an error occurred. The list contains all table names matching the SQL regular expression indicated by wild (which may contain the wildcard characters '%' and '_') or all tables if wild is NULL. You are responsible for calling mysql_free() to free the result set.
The list produced by mysql_list_tables() may be obtained by executing a SHOW TABLES command with mysql_query() and then processing the result set.
This function is deprecated as of MySQL 3.22.0.
int
mysql_reload (MYSQL *conn);Instructs the server to reload the grant tables. This can be done now by issuing a FLUSH PRIVILEGES query with mysql_query(). You must have the RELOAD privilege to use mysql_reload().
mysql_reload() returns zero for success and non-zero for failure.