C API Functions
Client library functions for the C API are described in detail in the following sections, grouped by category and listed alphabetically within category. Certain parameter names recur throughout the function descriptions and have the following conventional meanings:
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. row_num is a row number within a result set, from 0 to one less than the number of rows. col_num is a column number within a row of a result set, from 0 to one less than the number of columns. stmt is a handler for a prepared statement.
For brevity, where these parameters are not mentioned in the descriptions of functions in which they occur, you may assume the meanings just given.
Connection Management Routines
These functions allow you to establish and terminate connections to a server, to set options affecting the way connection establishment occurs, to re-establish connections that have timed out, and to change the current username.
A typical sequence involves calling mysql_init() to initialize a connection handler, mysql_real_connect() to establish the connection, and mysql_close() to terminate the connection when you are done with it. If it's necessary to indicate special options or set up an encrypted SSL connection, call mysql_options() or mysql_ssl_set() after mysql_init() and before mysql_real_connect().
my_bool
mysql_change_user (MYSQL *conn,
const char *user_name,
const char *password,
const char *db_name);
Changes the user and the default database for 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. If is faster to use this function to change the current user than to close the connection and open it again with different parameters. It can also be used to implement persistent connections for programs that serve different users during the course of their execution.
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. It is unnecessary to call mysql_close() if the attempt to open a connection fails. However, you might want to do so if mysql_init() allocated the handler, so that it can be disposed of.
MYSQL *
mysql_init (MYSQL *conn);
Initializes a connection handler and returns a pointer to it. If the parameter points to an existing MYSQL handler structure, mysql_init() initializes it and returns its address:
MYSQL conn_struct, *conn;
conn = mysql_init (&conn_struct);
If the parameter is NULL, mysql_init() allocates a new handler, initializes it, and returns its address:
MYSQL *conn;
conn = mysql_init (NULL);
The second approach is preferable over the first; letting the client library allocate and initialize the handler itself avoids problems that may arise with shared libraries if you upgrade MySQL to a newer version that uses a different internal organization for the MYSQL structure. 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,
const 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. (If you call mysql_options() multiple times to set a given option, the most recent value applies.) 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 always interpreted as a pointer.) You can pass an arg value of NULL for options that require no additional information. mysql_options() returns zero for success and non-zero if the option value is unknown. The following options are available: MYSQL_INIT_COMMAND Specifies a query to execute after connecting to the server. arg should point to a null-terminated string containing the query. The query will be 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 client and server both support it. arg should be NULL. It is also possible to specify compression when you call mysql_real_connect(). MYSQL_OPT_CONNECT_TIMEOUT Specifies the connection timeout, in seconds. arg should be a pointer to an unsigned int containing the timeout value. MYSQL_OPT_LOCAL_INFILE Enables or disables the use of LOAD DATA LOCAL. arg should be NULL to disable this capability, a pointer to an unsigned int that should be zero or non-zero to disable or enable this capability. This option will be ineffective if the server has been configured to always disallow LOAD DATA LOCAL. MYSQL_OPT_NAMED_PIPE Specifies that the connection to the server should use a named pipe. arg should be NULL. This option is for Windows clients only, and only for connections to Windows NT-based servers. MYSQL_OPT_PROTOCOL Specifies the protocol to use for connecting to the server. arg should point to an unsigned int value containing the protocol code. The allowable codes are MYSQL_PROTOCOL_MEMORY (shared memory), MYSQL_PROTOCOL_PIPE (Windows named pipe), MYSQL_PROTOCOL_SOCKET (Unix socket file), and MYSQL_PROTOCOL_TCP (TCP/IP). MYSQL_OPT_READ_TIMEOUT The timeout for reading from the server, in seconds. This option applies only to TCP/IP connections on Windows. arg should be a pointer to an unsigned int containing the timeout value. MYSQL_OPT_READ_TIMEOUT was introduced in MySQL 4.1.1. MYSQL_OPT_WRITE_TIMEOUT The timeout for writing to the server, in seconds. This option applies only to TCP/IP connections on Windows. arg should be a pointer to an unsigned int containing the timeout value. MYSQL_OPT_WRITE_TIMEOUT was introduced in MySQL 4.1.1. MYSQL_READ_DEFAULT_FILE Specifies an option file to read for connection parameters, rather than the usual option files that are searched by default if option files are read. arg should point to a null-terminated string containing the filename. Options will be read from the [client] group in the file. If you use MYSQL_READ_DEFAULT_GROUP to specify a group name, options from that group will be read from option files, too. MYSQL_READ_DEFAULT_GROUP Specifies an option file group in which to look for option values. arg should point to a null-terminated string containing the group name. (Specify the group name without the surrounding '[' and ']' characters.) The named group will be read in addition to the [client] group. If you also name a particular option file with MYSQL_READ_DEFAULT_FILE, options are read from that file only. Otherwise, the client library looks for the options in the standard option files. Note that if you specify neither MYSQL_READ_DEFAULT_FILE nor MYSQL_READ_DEFAULT_GROUP, no option files are read. MYSQL_REPORT_DATA_TRUNCATION Controls whether to report data truncation errors via the MYSQL_BIND.error member when the binary protocol for prepared statements is used. arg should be a pointer to a my_bool variable that is zero or non-zero to disable or enable truncation checking. MYSQL_REPORT_DATA_TRUNCATION was introduced in MySQL 5.0.3. MYSQL_SECURE_AUTH Controls whether to require secure authentication. arg should be a pointer to a my_bool variable that is zero or non-zero to allow or disallow connecting to a server that does not support the more secure password hashing implemented in MySQL 4.1.1. MYSQL_SECURE_AUTH was introduced in MySQL 4.1.1. MYSQL_SET_CHARSET_DIR Specifies the pathname of the directory where character set files are located. arg should point to a null-terminated string containing the directory pathname. The directory is on the client host; this option is used when the client needs to access character sets that aren't compiled into the client library but for which definition files are available. MYSQL_SET_CHARSET_NAME Indicates the name of the default character set to use. arg should point to a null-terminated string containing the character set name. MYSQL_SHARED_MEMORY_BASE_NAME Indicates the shared memory name to use for shared-memory connections. arg should point to a null-terminated string containing the name.
If you use the MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP options with mysql_options() to cause mysql_real_connect() to read option files, it recognizes the following options:
character-sets-dir=charset_directory_path
compress
connect-timeout=seconds
database=db_name
debug
default-character-set=charset_name
disable-local-infile
host=host_name
init-command=query
interactive-timeout=seconds
local-infile
max-allowed-packet=size
multi-results
multi-statements
password=your_pass
pipe
port=port_num
protocol=protocol_type
return-found-rows
secure-auth
shared-memory-base-name=name
socket=socket_name
ssl-ca=ssl_certificate_authority_file
ssl-capath=ssl_certificate_authority_directory_path
ssl-cert=ssl_certificate_file
ssl-cipher=str
ssl-key=ssl_key_file
timeout=seconds
user=user_name
Instances of the host, user, password, database, port or socket options found in option files are overridden if the corresponding argument to mysql_real_connect() is non-NULL. The multi-results option is equivalent to passing CLIENT_MULTI_RESULTS in the flags argument to mysql_real_connect(). The multi-statements option is equivalent to passing CLIENT_MULTI_STATEMENTS and CLIENT_MULTI_RESULTS in the flags argument to mysql_real_connect(). timeout is recognized but obsolete; use connect-timeout instead. multi-results and secure-auth are recognized as of MySQL 4.1.1. multi-statements is recognized as of MySQL 4.1.9 (it was originally named multi-queries in 4.1.1, which continues to be recognized for backward compatibility). For Windows pathnames that are specified with the MYSQL_READ_DEFAULT_FILE or MYSQL_SET_CHARSET_DIR options, '\' characters can be given either as '/' or as '\\', although '/' is preferable. The mysql_options() calls in the following example have the effect of setting connection options so that mysql_real_connect() reads C:\my.cnf.extra for information from the [client] and [mygroup] groups, connects using a named pipe and a timeout of 10 seconds, and executes a SET NAMES 'utf8' statement after the connection has been established. Communication over the connection will use the compressed protocol.
MYSQL *conn;
unsigned int timeout;
if ((conn = mysql_init (NULL)) == NULL)
... deal with error ...
mysql_options (conn, MYSQL_READ_DEFAULT_FILE, "C:/my.cnf.extra");
mysql_options (conn, MYSQL_READ_DEFAULT_GROUP, "mygroup");
mysql_options (conn, MYSQL_OPT_NAMED_PIPE, NULL);
timeout = 10;
mysql_options (conn, MYSQL_OPT_CONNECT_TIMEOUT,
(char *) &timeout);
mysql_options (conn, MYSQL_INIT_COMMAND, "SET NAMES 'utf8'");
mysql_options (conn, MYSQL_OPT_COMPRESS, NULL);
if (mysql_real_connect (conn, ...) == NULL)
... deal with error ...
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(). Returns zero if the connection was up or was successfully re-established, non-zero if an error occurred.
MYSQL *
mysql_real_connect (MYSQL *conn,
const char *host_name,
const char *user_name,
const char *password,
const char *db_name,
unsigned int port_num,
const char *socket_name,
unsigned long flags);
Connects to a server and returns a pointer to the connection handler. conn should be a pointer to an existing connection handler that has been initialized by mysql_init(). The return value is the address of the handler for a successful connection, or NULL if an error occurred. 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 a connection has been established successfully. The remaining arguments indicate how to connect to the server. For arguments specified as NULL or zero, the value can be supplied by options found in an option file that mysql_real_connect() reads. (The client can cause mysql_real_connect() to read option files by calling mysql_options() with the MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP options.) host_name indicates the name of the MySQL server host. Table G.5 shows the connection protocol that the client uses for various host_name values for Unix and Windows clients. The table applies unless you have called mysql_options() with the MYSQL_OPT_PROTOCOL option to specify the protocol explicitly. Note that the name "localhost" is special for Unix systems. It indicates that you want to connect using a Unix socket rather than a TCP/IP connection. To connect to a server running on the local host using TCP/IP, pass "127.0.0.1" (a string containing the IP address of the local host's loopback interface) for the host_name value, rather than passing the string "localhost". Table G.5. Client Connection Protocol by Server Hostname TypeHostname Value | Unix Connection Protocol | Windows Connection Protocol |
|---|
hostname | TCP/IP connection to the named host | TCP/IP connection to the named host | IP number | TCP/IP connection to the named host | TCP/IP connection to the named host | localhost | Unix socket file connection to the local host | Shared-memory connection (if available) to the local host, otherwise a TCP/IP connection | 127.0.0.1 | TCP/IP connection to the local host | TCP/IP connection to the local host | . (period) | Does not apply | Named-pipe connection to the local host | NULL | Unix socket connection to the local host | TCP/IP connection to the local host; on Windows NT-based systems, a named-pipe connection is attempted first before falling back to TCP/IP |
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 be able to connect only if the password is blank in the user grant table entry that matches your username and the host from which you are connecting. 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 Unix socket filename to use for connections to "localhost" under Unix, or the pipe name for named-pipe connections under Windows. If this is NULL, the default socket or pipe name is used. The port number and socket filename are used according to the value of host_name. Under Unix, mysql_real_connect() connects using a Unix domain socket if you connect to "localhost". Under Windows, mysql_real_connect() connects using a named pipe if you connect to ".". Otherwise, the function connects using TCP/IP. The flags value can be one or more of the values shown in the following list, or 0 to specify no options. These options affect the operation of the server. CLIENT_COMPRESS Specifies that the connection should use the compressed client/server protocol if the server supports it. CLIENT_FOUND_ROWS Specifies that for UPDATE statements, the server should return the number of rows matched rather than the number of rows changed. Use of this option may hinder the MySQL optimizer and make updates slower. CLIENT_IGNORE_SPACE Normally, function names must be followed immediately by the parenthesis that begins the argument list, with no intervening spaces. This option tells the server to all spaces between the function name and the argument list, which also has the side effect of making all function names reserved words. CLIENT_INTERACTIVE Identifies the client as an interactive client. This tells the server that it can close the connection after a number of seconds of client inactivity equal to the server's interactive_timeout variable value. Normally, the value of the wait_timeout variable is used. CLIENT_MULTI_RESULTS Enable multiple result sets to be fetched with the mysql_more_results() and mysql_next_result() functions. CLIENT_MULTI_RESULTS was introduced in MySQL 4.1.1. Note:
In MySQL 5.0 and up, you must specify this option if the program uses a CALL statement to invoke stored procedures that return a result. Otherwise, an error will occur. CLIENT_MULTI_STATEMENTS Enable multiple-statement execution. When this capability is turned on, you can send multiple statements to the server in a single string. This option also enables CLIENT_MULTI_RESULTS to allow multiple result sets to be fetched. CLIENT_MULTI_STATEMENTS was introduced in MySQL 4.1.1. CLIENT_NO_SCHEMA Disallows db_name.tbl_name.col_name syntax. If you specify this option, the server allows references only of the forms tbl_name.col_name, tbl_name, or col_name in statements. CLIENT_ODBC Identifies the client as an ODBC client. CLIENT_SSL This is used internally for SSL connections; client programs should not use it. CLIENT_TRANSACTIONS Indicates to the server that the client knows about transactions. That is, the client understands that the server will have rolled back an active transaction if the connection drops during the transaction when not running in autocommit mode.
The flag values are bit values, so you can combine them in additive fashion using either the | or the + operator. For example, the following expressions are equivalent:
CLIENT_COMPRESS | CLIENT_ODBC
CLIENT_COMPRESS + CLIENT_ODBC
my_bool
mysql_ssl_set (MYSQL *conn,
const char *key,
const char *cert,
const char *ca,
const char *capath,
const char *cipher);
This function is used for setting up a secure connection over SSL to the MySQL server. If OpenSSL support is not compiled into the client library, mysql_ssl_set() does nothing. Otherwise it sets up the information required to establish an encrypted connection when you call mysql_real_connect(). (In other words, to set up a secure connection, call mysql_ssl_set() first and then mysql_real_connect().) mysql_ssl_set() always returns 0; any SSL setup errors will result in an error at the time you call mysql_real_connect(). key is the path to the key file. cert is the path to the certificate file. ca is the path to the certificate authority file. capath is the path to a directory of trusted certificates, to be used for certificate verification. cipher is a string listing the cipher or ciphers to use. Any parameter that is unused may be passed as NULL. mysql_ssl_set() requires some additional MySQL configuration ahead of time. (See Chapter 12, "MySQL and Security," for information on using secure connections.)
Error-Reporting Routines
The functions in this section allow you to determine and report the causes of errors. The possible error codes and messages are listed in the errmsg.h, mysqld_error.h, and sql_state.h MySQL header files.
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 zero if no error occurred and non-zero otherwise.
if (mysql_errno (conn) == 0)
printf ("Everything is okay\n");
else
printf ("Something is wrong!\n");
const char *
mysql_error (MYSQL *conn);
Returns a null-terminated string that contains 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:
const char *err = mysql_error (conn);
if (err[0] == '\0') /* empty string? */
printf ("Everything is okay\n");
else
printf ("Something is wrong!\n");
const char *
mysql_sqlstate (MYSQL *conn);
Returns a null-terminated string that contains an SQLSTATE error code for the most recently invoked client library routine that returned a status. This code is a five-character string. SQLSTATE values are taken from the ANSI SQL and ODBC standards. A value of "00000" means "no error." A value of "HY000" means "general error." This value is used for those MySQL errors that have not yet been assigned more-specific SQLSTATE codes.
if (strcmp (mysql_sqlstate (conn), "00000") == 0)
printf ("Everything is okay\n");
else
printf ("Something is wrong!\n");
mysql_sqlstate() was introduced in MySQL 4.1.1.
Statement Construction and Execution Routines
The functions in this section allow you to send SQL statements to the server. mysql_hex_string() and mysql_real_escape_string() help you construct statements by encoding characters that need special treatment. Unless you have enabled multiple-statement execution as described later in "Multiple Result Set Routines," each string sent to the server for execution must consist of a single SQL statement, and should not end with a semicolon character (';') or a \g sequence. ';' and \g are conventions of the mysql client program, not of the C client library.
unsigned long
mysql_hex_string (char *to_str,
const char *from_str,
unsigned long from_len);
Encodes a string that may contain special characters so that it can be used in an SQL statement. The buffer to be encoded is specified as a counted string. from_str points to the buffer, and from_len indicates the number of bytes in it. mysql_hex_string() encodes every character in the buffer using two hexadecimal digits, writes the encoded result into the buffer pointed to by to_str, and adds a terminating null byte. to_str must point to an existing buffer that is at least (from_len*2)+1 bytes long. mysql_hex_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(). Note that the result value is not by itself legal as a hexadecimal constant in an SQL statement. To construct a legal constant, you should either add "0x" at the beginning, or add "X'" at the beginning and "'" at the end. Example:
to_len = mysql_hex_string (to_str, "\0\\\'\"\n\r\032", 7);
printf ("to_len = %d, to_str = %s\n", to_len, to_str);
The example produces the following output:
to_len = 14, to_str = 005C27220A0D1A
int
mysql_query (MYSQL *conn, const char *stmt_str);
Given an SQL statement specified as a null-terminated string, mysql_query() sends the statement to the server to be executed. The string should not contain binary data; in particular, it should not contain null bytes, because mysql_query() will interpret the first one as the end of the statement. If your statement does contain binary data, use mysql_real_query() instead. mysql_real_query() is slightly faster than mysql_query(). mysql_query() returns zero for success, non-zero for failure. A successful statement 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.
unsigned long
mysql_real_escape_string (MYSQL *conn,
char *to_str,
const char *from_str,
unsigned long from_len);
Encodes a string that may contain special characters so that it can be used in an SQL statement, taking into account the current character set when performing encoding. Table G.6 lists the characters that are considered special and how they are encoded. (Note that the list does not include the SQL pattern characters, '%' and '_'.) Table G.6. mysql_real_escape_string() Character EncodingsSpecial Character | Encoding |
|---|
NUL (zero-valued byte) | \0 (backslash-zero) | Backslash | \\(backslash-backslash) | Single quote | \' (backslash-single quote) | Double quote | \" (backslash-double quote) | Newline | \n (backslash-'n') | Carriage return | \r (backslash-'r') | Ctrl-Z | \Z (backslash-'Z') |
The only characters that MySQL itself requires to be escaped within a string are the backslash and the quote character that surrounds the string (either ''' or '"'). mysql_real_escape_string() escapes the others to produce strings that are easier to read and to process in log files. The buffer to be encoded is specified as a counted string. from_str points to the buffer, and from_len indicates the number of bytes in it. mysql_real_escape_string() writes the encoded result into the buffer pointed to by to_str and adds a terminating 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 byte.) mysql_real_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_real_escape_string(). Consider the following example source code, and the output produced by it:
to_len = mysql_real_escape_string (conn, to_str, "\0\\\'\"\n\r\032", 7);
printf ("to_len = %d, to_str = %s\n", to_len, to_str);
The example produces the following output:
to_len = 14, to_str = \0\\\'\"\n\r\Z
The printed value of to_str in the output looks very much like the string specified as the third argument of the mysql_real_escape_string() call in the original source code, but is in fact quite different.
int
mysql_real_query (MYSQL *conn,
const char *stmt_str,
unsigned long length);
Given an SQL statement specified as a counted string, mysql_real_query() sends the statement to the server to be executed. The statement text is given by stmt_str, and its length is indicated by length. The string may contain binary data (including null bytes). mysql_real_query() returns zero for success, non-zero for failure. A successful statement 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, const 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. If you do not have permission to access the database, mysql_select_db() fails. mysql_select_db() is most useful for changing databases within the course of a connection. Normally you will specify the initial database to use when you call mysql_real_connect(), which is faster than calling mysql_select_db() after connecting. 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 G.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 statement. For such statements, mysql_affected_rows() may be called immediately after a successful call to mysql_query() or mysql_real_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 as that function when the value is meaningful, as well as the additional constraint that if you use mysql_use_result() to generate the result set, mysql_affected_rows() is never meaningful. mysql_affected_rows() returns zero if no statement has been issued, the statement was an UPDATE that changed no rows, or the statement was of a type that can return rows but selects none. A return value greater than zero indicates the number of rows changed (for DELETE, INSERT, REPLACE, UPDATE) or returned (for statements that return rows). A return value of -1 indicates either an error, or that you (erroneously) called mysql_affected_rows() after issuing a statement that returns rows but before actually retrieving the result set. However, because mysql_affected_rows() returns an unsigned value, you can detect a negative return value only by casting the result to a signed value before performing the comparison:
if ((long) mysql_affected_rows (conn) == -1)
fprintf (stderr, "Error!\n");
If you have specified that the client should return the number of rows matched for UPDATE statements, 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 by passing CLIENT_FOUND_ROWS in the flags argument to mysql_real_connect(). mysql_real_connect() returns a my_ulonglong value; see the note about printing values of this type in the section "Scalar Data Types."
void
mysql_data_seek (MYSQL_RES *res_set, my_ulonglong row_num);
Seeks to a particular row of the result set. The value of row_num can range from 0 to mysql_num_rows(res_set)1. The results are unpredictable if row_num 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_data_seek() differs from mysql_row_seek(), which takes a row offset value as returned by mysql_row_tell() rather than a row number.
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 statement 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, or NULL when no more columns are left. Related functions are 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 to the first MYSQL_FIELD, and then fetches successive column information structures:
MYSQL_FIELD *field;
unsigned int i;
mysql_field_seek (res_set, 0);
for (i = 0; i < mysql_num_fields (res_set); i++)
{
field = mysql_fetch_field (res_set);
printf ("column %u: name = %s max_length = %lu\n",
i, field->name, field->max_length);
}
MYSQL_FIELD *
mysql_fetch_fields (MYSQL_RES *res_set);
Returns an array of all column information structures for the result set. These may be accessed 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\n",
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_fetch_field() returns a pointer to a single field structure; mysql_fetch_fields() returns a pointer to an array of field structures.
MYSQL_FIELD *
mysql_fetch_field_direct (MYSQL_RES *res_set, unsigned int col_num);
Given a column index, returns the information structure for that column. The value of col_num can range from 0 to mysql_num_fields(res_set)1. The results are unpredictable if col_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\n",
i, field->name, field->max_length);
}
unsigned long *
mysql_fetch_lengths (MYSQL_RES *res_set);
Returns a pointer to an array of unsigned long values representing the lengths of the column values 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 indicate a NULL data value. An empty string also has a length of zero, so you must check whether 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 the word "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\n",
length[i], (row[i] != NULL ? row[i] : "NULL"));
}
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 of i range from 0 to mysql_num_fields(res_set)1. 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 yourselffor example, with atoi(), atof(), or sscanf(). 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 occurred.) 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 do this, you will need the column value lengths, which may be obtained by calling mysql_fetch_lengths(). The following code shows how to loop through a row of data values and determine whether 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\n",
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 statement on the given connection. This function is normally used when mysql_store_result() or mysql_use_result() return NULL. mysql_field_count() tells you whether a result set should have been returned. A return value of zero indicates no result set and no error. (This happens for INSERT and UPDATE statements, for example.) 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) /* a result set was returned */
{
/* ... process rows here, and then free result set ... */
mysql_free_result (res_set);
}
else /* no result set was returned */
{
/*
* does the lack of a result set mean that the statement didn't
* return one, or that it should have but an error occurred?
*/
if (mysql_field_count (conn) == 0)
{
/*
* statement generated no result set (it was not a SELECT,
* SHOW, DESCRIBE, etc.); just report rows-affected value.
*/
printf ("%lu rows affected\n",
(unsigned long) mysql_affected_rows (conn));
}
else /* an error occurred */
{
printf ("Problem processing the result set\n");
}
}
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 that column. offset is not a column index; it is a MYSQL_FIELD_OFFSET value obtained from an earlier call to mysql_field_tell() or from mysql_field_seek(). To reset to the first column, the offset value should be zero.
MYSQL_FIELD_OFFSET
mysql_field_tell (MYSQL_RES *res_set);
Returns the current column information structure offset. This value can 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(). For result sets generated by calling mysql_use_result(), mysql_free_result() automatically fetches and discards any unfetched rows.
my_ulonglong
mysql_insert_id (MYSQL conn);
Returns the value stored into an AUTO_INCREMENT column by the most recently executed statement on the given connection. This applies to an automatically generated AUTO_INCREMENT value or a literal value stored in the column. (This differs from the LAST_INSERT_ID() SQL function, which returns only automatically generated values.) mysql_insert_id() returns zero if no statement has been executed or if the previous statement did not involve an AUTO_INCREMENT column. (A zero return value is distinct from any valid AUTO_INCREMENT value because such values are positive.) You should call mysql_insert_id() immediately after issuing the statement that involves an AUTO_INCREMENT column. If you issue another statement before calling mysql_insert_id(), its value will be reset. Note that this behavior differs from that of the LAST_INSERT_ID() SQL function. mysql_insert_id() is maintained in the client and is set for each statement. The value of LAST_INSERT_ID() is maintained in the server and persists from statement to statement, until you generate another AUTO_INCREMENT value. The values returned by mysql_insert_id() are connection-specific and are not affected by AUTO_INCREMENT activity on other connections. mysql_insert_id() returns a my_ulonglong value; see the note about printing values of this type 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_fields() 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 ... */
}
}
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 */
}
mysql_num_rows() returns a my_ulonglong value; see the note about printing values of this type 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 is a MYSQL_ROW_OFFSET value that must be obtained from a call to mysql_row_tell() or mysql_row_seek(), or zero to seek to the first row. 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 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_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 G.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. That is all that is necessary to finish query processing, because mysql_free_result() automatically retrieves and discards any unfetched rows before releasing the result set. See the comparison of mysql_store_result() and mysql_use_result() in Table G.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.
Table G.7. Comparison of mysql_store_result() and mysql_use_result()mysql_store_result() | mysql_use_result() |
|---|
All rows in the result set are fetched by mysql_store_result() itself. | mysql_use_result() initializes the result set, but defers row retrieval to mysql_fetch_row(). | Uses more memory; all rows are stored in the client. | Uses less memory; one row is stored at a time. | Slower due to overhead involved in allocating memory for the entire result set. | Faster because memory need be allocated only for the current row. | A NULL return from mysql_fetch_row() indicates the end of the result set, not an error. | A NULL return from mysql_fetch_row() indicates the end of the result set or an error, because communications failure can disrupt retrieval of the current record. | mysql_num_rows() can be called anytime after mysql_store_result() has been called. | mysql_num_rows() returns a correct row count only after all rows have been fetched. | mysql_affected_rows() is a synonym for mysql_num_rows(). | mysql_affected_rows() cannot be used. | Random access to result set rows is possible with mysql_data_seek(), mysql_row_seek(), and mysql_row_tell(). | No random access into result set; rows must be processed in order as returned by the server. mysql_data_seek(), mysql_row_seek(), and mysql_row_tell() should not be used. | Tables are read-locked for no longer than necessary to fetch the data rows. | Tables can stay read-locked if the client pauses in mid-retrieval, locking out other clients attempting to modify the tables. | The max_length member of result set MYSQL_FIELD structures is set to the longest value actually present in the result set for the columns in the set. | max_length is not set to any meaningful value, because it cannot be known until all rows have been retrieved. |
Information Routines
These functions provide information about the client, server, protocol version, and the current connection. The values returned by most of these are retrieved from the server at connect time and stored within the client library.
const char *
mysql_character_set_name (MYSQL *conn);
Returns the name of the default character set for the given connection; for example, "latin1".
const char *
mysql_get_client_info (void);
Returns a null-terminated string describing the client library version; for example, "4.1.5".
const char *
mysql_get_host_info (MYSQL *conn);
Returns a null-terminated string describing the given connection, such as "Localhost via Unix socket", "cobra.snake.net via TCP/IP", ". via named pipe", or "Shared memory".
unsigned int
mysql_get_proto_info (MYSQL *conn);
Returns a number indicating the client/server protocol version used for the given connection.
const char *
mysql_get_server_info (MYSQL *conn);
Returns a null-terminated string describing the server version; for example, "5.0.2-alpha-log". The value consists of a version number, possibly followed by one or more suffixes. The suffix values are listed in the description of the VERSION() function in Appendix C, "Operator and Function Reference."
const 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: 0
The numbers will vary according to the particular query you've executed, of course. mysql_info() returns non-NULL for INSERT INTO … VALUES only if the statement contains more than one value list. For statements not shown in the preceding list, mysql_info() always returns NULL. 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.
const 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: 1189474 Threads: 4 Questions: 331869 Slow queries: 50 Opens: 1424
Flush tables: 1 Open tables: 64 Queries per second avg: 0.279
These values may be interpreted as follows: Uptime is the number of seconds the server has been running. Threads is the number of threads currently running in the server. Questions is the number of statements the server has executed. Slow queries is the number of statements that took longer to process than the time indicated by the server's long_query_time variable. Opens is the number of tables the server has opened. Flush tables is the number of FLUSH, REFRESH, and RELOAD statements that have been executed. Open tables is the number of tables the server currently has open. Queries per second is the ratio of Questions to Uptime.
Not coincidentally, the information returned by the mysql_stat() function is the same as that reported by the mysqladmin status command. (mysqladmin itself invokes this function to get 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 the KILL statement. Do not invoke mysql_thread_id() until just before you need the value. If you retrieve the value and store it, the value may be incorrect when you use it later. This can happen if your connection goes down and then is re-established (for example, with mysql_ping()), because the server will assign the new connection a different thread identifier.
unsigned int
mysql_warning_count (MYSQL *conn);
Returns the number of warnings generated by the most recent statement that generates such messages.
Transaction Control Routines
The functions in this section provide control over transaction processing.
my_bool
mysql_autocommit (MYSQL *conn, my_bool mode);
Enable autocommit mode for the current connection if mode is true (non-zero), disables autocommit mode otherwise. Returns zero for success and non-zero otherwise.
my_bool
mysql_commit (MYSQL *conn);
Commits the current transaction. Returns zero for success and non-zero otherwise.
my_bool
mysql_rollback (MYSQL *conn);
Rolls back the current transaction. Returns zero for success and non-zero otherwise.
Multiple Result Set Routines
The routines in this section are used when the multiple-statement execution capability is enabled. To use this capability, specify the CLIENT_MULTI_STATEMENTS flag when you open the connection with mysql_real_connect(). You can also enable multiple-statement execution for an already-open connection using the mysql_set_server_option() function.
To send the statements to the server to be executed, use mysql_real_query() or mysql_query(). The statements should be sent as a single string separated by semicolons.
An example that shows how to use these routines is given in "Using Multiple-Statement Execution," in Chapter 6.
my_bool
mysql_more_results (MYSQL *conn);
Returns non-zero if more query results exist to be read and zero otherwise. To begin processing the next result, you must call mysql_next_result().
int
mysql_next_result (MYSQL *conn);
Initiates processing for the next result if any exists. After calling this function, you can process the result as you normally would for single-statement execution. mysql_next_result() returns 0 if there were more results available, 1 if there were not, and a value greater than zero if an error occurred.
Prepared Statement Routines
The routines in this section implement the binary client/server protocol that provides support for the prepared statement API. They are divided into the following sections:
Error-reporting routines to get error codes and messages Statement 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
Currently, only the following statements can be prepared: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most variations of SHOW.
The binary protocol underwent quite a bit of revision during the earlier releases of MySQL 4.1. In particular, many functions were renamed from their pre-4.1.2 names. For best results, you should try to use a recent version of MySQL.
Prepared Statement Error-Reporting Routines
The functions in this section allow you to determine and report the causes of prepared statement errors. The possible error codes and messages are listed in the errmsg.h, mysqld_error.h, and sql_state.h MySQL header files.
unsigned int
mysql_stmt_errno (MYSQL_STMT *stmt);
Returns an error code for the most recently invoked prepared statement routine that returned a status. The error code is zero if no error occurred and non-zero otherwise.
if (mysql_stmt_errno (stmt) == 0)
printf ("Everything is okay\n");
else
printf ("Something is wrong!\n");
const char *
mysql_stmt_error (MYSQL_STMT *stmt);
Returns a null-terminated string that contains an error message for the most recently invoked prepared statement 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_stmt_error() after you already know an error occurred, the return value itself can be used to detect the occurrence of an error:
const char *err = mysql_stmt_error (stmt);
if (err[0] == '\0') /* empty string? */
printf ("Everything is okay\n");
else
printf ("Something is wrong!\n");
const char *
mysql_stmt_sqlstate (MYSQL_STMT *stmt);
Returns a null-terminated string that contains an SQLSTATE error code for the most recently invoked prepared statement routine that returned a status. This code is a five-character string. SQLSTATE values are taken from the ANSI SQL and ODBC standards. A value of "00000" means "no error." A value of "HY000" means "general error." This value is used for those MySQL errors that have not yet been assigned more-specific SQLSTATE codes.
if (strcmp (mysql_stmt_sqlstate (stmt), "00000") == 0)
printf ("Everything is okay\n");
else
printf ("Something is wrong!\n");
mysql_stmt_sqlstate() was introduced in MySQL 4.1.1.
Prepared Statement Construction and Execution Routines
The functions in this section allow you to send prepared SQL statements to the server. Each string must consist of a single SQL statement, and should not end with a semicolon character (';') or a \g sequence. ';' and \g are conventions of the mysql client program, not of the C client library.
An example program that demonstrates how to use many of these functions is shown in Chapter 6.
my_bool
mysql_stmt_bind_param (MYSQL_STMT *stmt, MYSQL_BIND *bind_array);b
Given a prepared statement handler, stmt, the mysql_stmt_bind_param() function provides a set of data values to be bound to the '?' placeholders in the statement. bind_array is the address of an array of MYSQL_BIND structures. There must be one structure in the array for each placeholder in the prepared statement. mysql_stmt_bind_param() returns zero if the bind operation was successful and non-zero otherwise. mysql_stmt_bind_param() was introduced in MySQL 4.1.2.
my_bool
mysql_stmt_close (MYSQL_STMT *stmt);
Closes the prepared statement handler and deallocates any resources associated with it. This includes canceling any results that might be pending for the handler. mysql_stmt_close() returns zero for success and non-zero otherwise. After closing a statement handler, do not attempt to use it for further operations. If the server still has prepared statements that are associated with a given client connection when the connection closes, it discards those statements.
MYSQL_STMT *
mysql_stmt_init (MYSQL *conn);
Allocates and initializes a MYSQL_STMT handler. Returns a pointer to the handler, or NULL if the handler could not be allocated. You should release the handler with mysql_stmt_close() when you are done with it. mysql_stmt_init() was introduced in MySQL 4.1.2.
int
mysql_stmt_execute (MYSQL_STMT *stmt);
Executes the prepared statement associated with the given statement handler. Returns zero if the statement was executed successfully and non-zero otherwise. Before executing the statement, you must bind data values to it by calling mysql_stmt_bind_param() if the statement contains any '?' placeholders. After a successful execution, determine the result of the statement according to whether it returns a result set. For statements that return no result set, call mysql_stmt_affected_rows() to determine the number of rows inserted, deleted, or updated. For statements that return a result set, metadata becomes available and can be retrieved with mysql_stmt_result_metadata(). To fetch the results, use mysql_stmt_bind_result() to bind result buffers to columns, mysql_stmt_fetch() to retrieve rows, and mysql_stmt_free_result() to free the result set. mysql_stmt_execute() was introduced in MySQL 4.1.2.
int
mysql_stmt_prepare (MYSQL_STMT *stmt,
const char *stmt_str,
unsigned long length);
Given an SQL statement specified as a counted string, mysql_stmt_prepare() sends the statement to the server to be prepared for later execution and associates the statement handler, stmt, with the prepared statement. The statement text is given by stmt_str, and its length is indicated by length. The string may contain binary data (including null bytes). mysql_stmt_prepare() returns zero for success and non-zero for failure. The statement can contain '?' characters as parameter markers to indicate where data values should be bound to the statement when it is executed later. mysql_stmt_prepare() was introduced in MySQL 4.1.2.
my_bool
mysql_stmt_reset (MYSQL_STMT *stmt);
Reset the prepared statement handler to the state that it has after calling mysql_stmt_prepare(). mysql_stmt_reset() was introduced in MySQL 4.1.1.
MYSQL_RES *
mysql_stmt_result_metadata (MYSQL_STMT *stmt);
After a successful call to mysql_stmt_execute(), mysql_stmt_result_metadata() returns metadata about the columns that result from the statement if it is one that returns a result set. The return value is a pointer to a MYSQL_RES structure. The result set structure is similar to that for a non-prepared statement that you obtain after invoking mysql_store_result(), except that it does not contain any data. You can obtain information about the columns by passing the structure pointer to functions that take a MYSQL_RES argument such as mysql_fetch_field(), mysql_fetch_fields(), and mysql_num_fields(). When you are done with the structure, pass it to mysql_free_result() to dispose of it. If the prepared statement is not one that returns a result set, mysql_stmt_result_metadata() returns NULL to indicate that no metadata information is available. mysql_stmt_result_metadata() was introduced in MySQL 4.1.2.
my_bool
mysql_stmt_send_long_data (MYSQL_STMT *stmt,
unsigned int param_num,
const char *data,
unsigned long length);
This function can be used to send long BLOB or TEXT values a piece at a time. param_num indicates which parameter the call applies to. It can range from 0 to mysql_stmt_param_count(stmt)1. data is a pointer to the buffer containing the data to send, and length indicates how many bytes to send. mysql_stmt_send_long_data() was introduced in MySQL 4.1.2.
Prepared Statement Result Set Processing Routines
When executing a prepared statement produces a result set, the functions in this section allow you to retrieve the set and access its contents.
An example program that demonstrates how to use many of these functions is shown in Chapter 6.
my_ulonglong
mysql_stmt_affected_rows (MYSQL_STMT *stmt);
This function is the prepared statement equivalent of mysql_affected_rows(), except that you call it after invoking mysql_stmt_execute(). For statements that return no result set, mysql_stmt_affected_rows(), returns the number of rows inserted, deleted, or updated by executing the statement. For statements that return a result set, this function acts like mysql_num_rows(). mysql_stmt_affected_rows() returns a my_ulonglong value; see the note about printing values of this type in the section "Scalar Data Types."
my_bool
mysql_stmt_attr_get (MYSQL_STMT *stmt,
enum enum_stmt_attr_type attr_type,
void *attr);
Gets a prepared statement handler attribute. See the description of mysql_stmt_attr_set() for a description of the allowable attr_type attribute values. attr is a pointer to a variable into which the attribute value should be written.
my_bool attr;
if (mysql_stmt_attr_get (stmt, STMT_ATTR_UPDATE_MAX_LENGTH, &attr) == 0)
printf ("Attribute gotten successfully\n");
else
printf ("Could not get attribute\n");
mysql_stmt_attr_get() returns zero if the attribute was obtained successfully, non-zero if the attribute type is unknown. mysql_stmt_attr_get() was introduced in MySQL 4.1.2.
my_bool
mysql_stmt_attr_set (MYSQL_STMT *stmt,
enum enum_stmt_attr_type attr_type,
const void *attr);
Sets a prepared statement handler attribute. attr_type indicates which attribute to set, and attr is a pointer to a variable that contains the value of the attribute. Currently the only allowable attr_type value is STMT_ATTR_UPDATE_MAX_LENGTH, which controls whether mysql_stmt_store_result() calculates the max_length metadata value for result set columns. To disable or enable this attribute, declare a variable of type my_bool, set it to zero or non-zero, and pass its address to mysql_stmt_attr_set():
my_bool attr = 1;
if (mysql_stmt_attr_set (stmt, STMT_ATTR_UPDATE_MAX_LENGTH, &attr) == 0)
printf ("Attribute set successfully\n");
else
printf ("Could not set attribute\n");
mysql_stmt_attr_set() returns zero if the attribute was set successfully, non-zero if the attribute type is unknown. mysql_stmt_attr_set() was introduced in MySQL 4.1.2.
my_bool
mysql_stmt_bind_result (MYSQL_STMT *stmt, MYSQL_BIND *bind_array);
Given a prepared statement handler, stmt, the mysql_stmt_bind_result() function provides an array of MYSQL_BIND structures to be used for fetching result set rows. bind_array is the address of an array of MYSQL_BIND structures. There must be one structure in the array for each column in the result set. Each time you call mysql_stmt_fetch() to retrieve a result set row, the column values are returned in the MYSQL_BIND structures. mysql_stmt_bind_result() returns zero if the bind operation was successful and non-zero otherwise. You must bind the structures to the result set columns before retrieving rows, and the buffers pointed to by the structures must be large enough to store the retrieved values. It is allowable to call mysql_stmt_bind_result() while retrieving a result set to bind columns to different MYSQL_STMT structures. The most recent bindings are those used by mysql_stmt_fetch(). mysql_stmt_bind_result() was introduced in MySQL 4.1.2.
void
mysql_stmt_data_seek (MYSQL_STMT *stmt, my_ulonglong row_num);
Seeks to a particular row of the result set. The value of row_num can range from 0 to mysql_stmt_num_rows(stmt)1. The results are unpredictable if row_num is out of range. mysql_stmt_data_seek() requires that the entire result set has been retrieved into client memory, so you can use it only if you have called mysql_stmt_store_result() after executing the statement. mysql_stmt_data_seek() differs from mysql_stmt_row_seek(), which takes a row offset value as returned by mysql_stmt_row_tell() rather than a row number. mysql_stmt_data_seek() was introduced in MySQL 4.1.1.
unsigned int
mysql_stmt_field_count (MYSQL_STMT *stmt);
This function can be called after invoking mysql_stmt_prepare() with the statement handler. It returns the number of columns in the result set that will be generated when you execute the statement. If the statement will not produce a result set (for example, if it is an INSERT or UPDATE), mysql_stmt_field_count() returns zero. mysql_stmt_field_count() was introduced in MySQL 4.1.3.
int
mysql_stmt_fetch (MYSQL_STMT *stmt);
After a successful call to mysql_stmt_execute() to execute a prepared statement that returns a result set, optionally followed by a call to mysql_stmt_store_result() to retrieve the result set into client memory, call mysql_stmt_fetch() to retrieve rows of the result. The buffers into which you want to fetch result columns first must be bound to MYSQL_BIND structures by calling mysql_stmt_bind_result(). mysql_stmt_fetch() returns zero if a row was fetched successfully, MYSQL_NO_DATA if there are no more rows to fetch, and 1 if an error occurred. After a successful fetch, the column values are available in the MYSQL_BIND structures bound to the result. mysql_stmt_fetch() was introduced in MySQL 4.1.2.
int
mysql_stmt_fetch_column (MYSQL_STMT *stmt,
MYSQL_BIND *bind,
unsigned int col_num,
unsigned long offset);
This function fetches data for a single column from the current result set row. Returns zero for success and non-zero if an error occurred. bind is a MYSQL_BIND structure that should be set up to indicate the kind of value to retrieve, the buffer into which to retrieve it, and the length (amount) of the data to retrieve. col_num indicates which column to fetch. Its value can range from 0 to mysql_stmt_field_count(stmt)1. offset indicates the offset into the column value at which value retrieval should begin; 0 indicates the start of the value. mysql_stmt_fetch_column() was introduced in MySQL 4.1.2.
my_bool
mysql_stmt_free_result (MYSQL_STMT *stmt);
De-allocates the memory used by the result set associated with the given statement handler. Returns zero for success and non-zero otherwise. Any unfetched rows are discarded. You must call mysql_stmt_free_result() for each result set generated by the handler. mysql_stmt_free_result() was introduced in MySQL 4.1.1.
my_ulonglong
mysql_stmt_insert_id (MYSQL_STMT *stmt);
This function is the prepared-statement equivalent of mysql_insert_id(). It is used after you call mysql_stmt_execute() to execute a statement that generates an AUTO_INCREMENT value. mysql_stmt_insert_id() returns a my_ulonglong value; see the note about printing values of this type in the section "Scalar Data Types." mysql_stmt_insert_id() was introduced in MySQL 4.1.2.
my_ulonglong
mysql_stmt_num_rows (MYSQL_STMT *stmt);
Returns the number of rows in the result set, if you have fetched the result into client memory by calling mysql_stmt_store_result(). If you have not called mysql_stmt_store_result(), mysql_stmt_num_rows() returns zero. mysql_stmt_num_rows() returns a my_ulonglong value; see the note about printing values of this type in the section "Scalar Data Types." mysql_stmt_num_rows() was introduced in MySQL 4.1.1.
int
mysql_stmt_store_result (MYSQL_STMT *stmt);
Retrieves the result set produced by executing a prepared statement. This causes the result to be buffered in client memory, so that calls to mysql_stmt_fetch() return rows from the buffered result. Calling mysql_stmt_store_result() also allows you to use mysql_stmt_data_seek(), mysql_stmt_row_seek(), or mysql_stmt_row_tell(). These functions operate by positioning the row cursor of a result set buffered in client memory. For performance reasons, the max_length value in the result set metadata for each column is not calculated by default. If you want this value to be calculated when you call mysql_stmt_store_result(), use the mysql_stmt_set_attr() function to set the statement handler's STMT_ATTR_UPDATE_MAX_LENGTH_FLAG. You can fetch rows of the result set by calling mysql_stmt_fetch() without calling mysql_stmt_store_result() first. In this case, rows are retrieved from the server one by one. Calling mysql_stmt_store_result() after executing a statement that produces no result set has no effect.
unsigned long
mysql_stmt_param_count (MYSQL_STMT *stmt);
After a successful call to mysql_stmt_prepare() to prepare a statement, mysql_stmt_param_count() returns the number of parameters in the statement (indicated by '?' placeholders). The return value is zero if there are no placeholders. mysql_stmt_param_count() was introduced in MySQL 4.1.2.
MYSQL_ROW_OFFSET
mysql_stmt_row_seek (MYSQL_STMT *stmt, MYSQL_ROW_OFFSET offset);
Seeks to a particular row of the result set. mysql_stmt_row_seek() is similar to mysql_stmt_data_seek(), but the offset value is not a row number. offset is a MYSQL_ROW_OFFSET value that must be obtained from a call to mysql_stmt_row_tell() or mysql_stmt_row_seek(), or zero to seek to the first row. mysql_stmt_row_seek() returns the previous row offset. mysql_stmt_row_seek() requires that the entire result set has been retrieved into client memory, so you can use it only if you have called mysql_stmt_store_result() after executing the statement. mysql_stmt_row_seek() was introduced in MySQL 4.1.1.
MYSQL_ROW_OFFSET
mysql_stmt_row_tell (MYSQL_STMT *stmt);
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_stmt_row_seek(), not to mysql_stmt_data_seek(). mysql_stmt_row_tell() requires that the entire result set has been retrieved into client memory, so you can use it only if you have called mysql_stmt_store_result() after executing the statement. mysql_stmt_row_tell() was introduced in MySQL 4.1.1.
Administrative Routines
The functions in this section allow you to control aspects of server operation.
int
mysql_refresh (MYSQL *conn, unsigned int options);
This function is similar in effect to the SQL FLUSH and RESET statements, except that you can tell the server to flush several kinds of things at once. mysql_refresh() returns zero for success, non-zero for failure. The options value should be composed of one or more of the values shown in the following list. You must have the RELOAD privilege to perform these operations. REFRESH_GRANT Reloads the grant table contents. This is equivalent to issuing a FLUSH PRIVILEGES statement. REFRESH_HOSTS Flushes the host cache. This is equivalent to issuing a FLUSH HOSTS statement. REFRESH_LOG Flushes the log files by closing and reopening them. This applies to whatever logs the server has open, and is equivalent to issuing a FLUSH LOGS statement. REFRESH_MASTER Tells a replication master server to delete the binary logs listed in the binary log index file and to truncate the index. This is equivalent to issuing a RESET MASTER statement. REFRESH_SLAVE Tells a replication slave server to forget its position in the master logs. This is equivalent to issuing a RESET SLAVE statement. REFRESH_STATUS Reinitializes the status variables to zero. This is equivalent to issuing a FLUSH STATUS statement. REFRESH_TABLES Closes all open tables. This is equivalent to issuing a FLUSH TABLES statement. REFRESH_THREADS Flushes the thread cache. There is no equivalent SQL statement for this operation.
The option flags are bit values, so you can combine them in additive fashion using either the | or the + operator. For example, the following expressions are equivalent:
REFRESH_LOG | REFRESH_TABLES
REFRESH_LOG + REFRESH_TABLES
int
mysql_set_server_option (MYSQL *conn,
enum enum_mysql_set_option option);
Sets a server option and returns zero if the option was set successfully or non-zero otherwise. Currently this is used only to enable or disable the multi-statement execution capability. Option Value | Meaning |
|---|
MYSQL_OPTION_MULTI_STATEMENTS_OFF | Turn multiple-statement execution off | MYSQL_OPTION_MULTI_STATEMENTS_ON | Turn multiple-statement execution on |
Note:
Enabling multiple-statement execution with MYSQL_OPTION_MULTI_STATEMENTS_ON does not also enable multiple result sets the way that the CLIENT_MULTI_STATEMENTS option to mysql_real_connect() also enables CLIENT_MULTI_RESULTS. mysql_set_server_option() was introduced in MySQL 4.1.1.
int
mysql_shutdown (MYSQL *conn, enum mysql_enum_shutdown_level level);
Instructs the server to shut down. You must have the SHUTDOWN privilege to do this. The second argument is present only as of MySQL 4.1.3. Its value should be SHUTDOWN_DEFAULT; other shutdown levels will be implemented eventually. mysql_shutdown() returns zero for success, non-zero for failure.
Threaded Client Routines
The routines in this section are used for writing multi-threaded clients.
void
mysql_thread_end (void);
Frees any thread-specific variables initialized by mysql_thread_init(). To avoid memory leaks, you should call this function explicitly to terminate any threads that you create.
my_bool
mysql_thread_init (void);
Initializes thread-specific variables. This function should be called for any thread you create that will call MySQL functions. In addition, you should call mysql_thread_end() before terminating the thread.
unsigned int
mysql_thread_safe (void);
Returns 1 if the client library is thread-safe, 0 otherwise. The value of this function reflects whether MySQL was configured with the --enable-thread-safe-client option.
Embedded Server Communication Routines
This section describes routines that are used to communicate with libmysqld, the embedded MySQL server. To write a program that uses the embedded server, you should specify -lmysqld rather than -lmysqlclient when you link your program to produce an executable image. To find out the particular library flags that are appropriate for linking in the embedded server on your system, use the following command:
% mysql_config --libmysqld-libs
The regular client library, -lmysqlclient, contains dummy versions of these routines that do nothing. This means that if you call these routines within your program, it is possible to produce a standalone client or one that uses the embedded server from the same source code, depending on which library you select at link time.
void
mysql_server_end (void);
Shuts down the embedded server. You should call this function after you're done using the server.
int
mysql_server_init (int argc, char **argv, char **groups);
Initializes the embedded server. This function must be called before calling any other mysql_xxx() functions. The argc and argv arguments are like the standard arguments passed to main() in C programs: argc is the argument count; if there are none, argc should be zero. Otherwise, argc should be the number of arguments passed to the server. argv is a vector of null-terminated strings containing the arguments. Note that argv[0] will be ignored. The groups argument is a vector of null-terminated strings indicating which option file groups the embedded server should read. The final element of the vector should be NULL. If group itself is NULL, the server reads the [server] and [embedded] option file groups by default. Group names in the groups vector should be given without the surrounding '[' and ']' characters.
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, or --with-debug=full for more information. The latter option enables safemalloc, a library that performs extensive memory allocation checking.)
void
mysql_debug (const char *debug_str);
Performs a DBUG_PUSH operation using the string debug_str. 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 SUPER privilege to do this. mysql_dump_debug_info() returns zero for success, non-zero for failure.
 |
