| 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. For brevity, where these parameters are not mentioned in the descriptions of functions in which they occur, you can 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. mysql_change_user() was introduced in MySQL 3.23.3. 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 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 NULLmysql_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 can happen if mysql_init() cannot allocate a new handler. If mysql_init() allocates the handler, mysql_close() deallocates it automatically when you close the connection. mysql_init() was introduced in MySQL 3.22.1. 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 can call mysql_options() multiple times if you want to set several options. (If you call mysql_options() multiple times to set a given option, mysql_real_connect() uses the most recently specified value for that option.) 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 or non-zero if the option value is unknown. mysql_options() itself was introduced in MySQL 3.22.1, but some of the allowable option values were introduced later, as indicated in the option descriptions. The following options are available: MYSQL_INIT_COMMAND Specifies a query to execute after connecting to the server. arg points 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_INIT_COMMAND was introduced in MySQL 3.22.10. 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 call mysql_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_LOCAL_INFILE Enables or disables the use of LOAD DATA LOCAL. arg is a pointer to an unsigned int that should be non-zero to enable this capability or zero to disable it. This option will be ineffective if the server has been configured to always disallow LOAD DATA LOCAL. MYSQL_OPT_LOCAL_INFILE was introduced in MySQL 3.23.49. (From MySQL 3.22.15 to 3.23.48, LOAD DATA LOCAL is always enabled, and prior to 3.22.15, it is unavailable.) MYSQL_OPT_NAMED_PIPE Specifies that the connection to the server should use a named pipe. arg is NULL. This option is for Windows clients only and only for connections to Windows NT-based servers. MYSQL_OPT_NAMED_PIPE was introduced in MySQL 3.22.5. 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 points 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_FILE was introduced in MySQL 3.22.10. MYSQL_READ_DEFAULT_GROUP Specifies an option file group in which to look for option values. arg points 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_READ_DEFAULT_GROUP was introduced in MySQL 3.22.10. MYSQL_SET_CHARSET_DIR Specifies the pathname of the directory where character set files are located. arg points 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_DIR was introduced in MySQL 3.23.14. MYSQL_SET_CHARSET_NAME Indicates the name of the default character set to use. arg points to a null-terminated string containing the character set name. MYSQL_SET_CHARSET_NAME was introduced in MySQL 3.23.14. Certain options can also be specified by means of an option file in either the [client] group or in a group that you specify with the MYSQL_READ_DEFAULT_GROUP option. The list of options that pertain to client programs is given in the "Option Files" section of Appendix E, "MySQL Program Reference." For Windows pathnames 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 SQL_BIG_TABLES=1 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 SQL_BIG_TABLES=1"); 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; returns non-zero if an error occurred. mysql_ping() was introduced in MySQL 3.22.1. 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 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 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. host_name indicates the name of the MySQL server host. Table F.4 shows how the client attempts to connect for various host_name values for UNIX and Windows clients. 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 F.4. Client Connection Types by Server Hostname Type. | Hostname Value | UNIX Connection Type | Windows Connection Type | | 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 connection to the local host | TCP/IP connection to the local host | | 127.0.0.1 | TCP/IP connection to the named host | TCP/IP connection to the named 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 only be able to connect 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 name 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 name 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 can be 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 queries, the server should return the number of rows matched rather than the number of rows changed. Use of this option can 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 ignore all spaces between the function name and the argument list, which also has the side effect of making all function names reserved words. This option was introduced in MySQL 3.22.7. 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. This option was introduced in MySQL 3.23.28. CLIENT_NO_SCHEMA Disallows db_name.tbl_name.col_name syntax. If you specify this option, the server allows only references of the forms tbl_name.col_name, tbl_name, or col_name in queries. CLIENT_ODBC Identifies the client as an ODBC client. This option was introduced in MySQL 3.22.4. 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 auto-commit mode. This option was introduced in MySQL 3.23.17. 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 The mysql_real_connect() db_name parameter was added in 3.22.0. The use of mysql_init() to initialize the MYSQL connection handler argument began in 3.22.1. int 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 can be passed as NULL. mysql_ssl_set() was introduced in MySQL 4.0.0. It requires some additional MySQL configuration ahead of time. (See Chapter 12 for information on using secure connections.) 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 zero if no error occurred or non-zero otherwise. The possible error codes are listed in the errmsg.h and mysqld_error.h MySQL header files. 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 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: char *err = mysql_error (conn); if (err[0] == '\0') /* empty string? */ printf ("Everything is okay\n"); else printf ("Something is wrong!\n"); Query Construction and Execution Routines The functions in this section allow you to send queries to the server. mysql_real_escape_string() helps you construct queries by escaping characters that need special treatment. Each query 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. int mysql_query (MYSQL *conn, const char *query_str); 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 because mysql_query() will interpret the first one as the end of the query. If your query does contain binary data, use mysql_real_query() instead. mysql_real_query() is slightly faster than mysql_query(). mysql_query() returns zero for success or 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. unsigned long mysql_real_escape_string (MYSQL *conn, char *to_str, const char *from_str, unsigned long from_len); Encodes a string that can contain special characters so that it can be used in an SQL statement, taking into account the current character set when performing encoding. Table F.5 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 F.5. mysql_real_escape_string() Character Encodings | Special Character | Encoding | | NUL (ASCII 0) | \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 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 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 output is: 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. mysql_real_escape_string() was introduced in MySQL 3.23.14 as a replacement for mysql_escape_string(), which does not take into account the current character set and is now deprecated. To write code that will work with any version of MySQL, include the following fragment in any source file that uses mysql_real_escape_string(): #if !defined(MYSQL_VERSION_ID) || (MYSQL_VERSION_ID<32314) #define mysql_real_escape_string(conn,to_str,from_str,len) \ mysql_escape_string(to_str,from_str,len) #endif This maps mysql_real_escape_string() to mysql_escape_string() when the source is compiled under older versions of MySQL. int mysql_real_query (MYSQL *conn, const char *query_str, unsigned long length); Given a query specified as a counted string, mysql_real_query() sends the query to the server to be executed. The string can contain binary data (including null bytes). The query text is given by query_str, and the length is indicated by length. mysql_real_query() returns zero for success, and it returns 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. Prior to MySQL 4, the length argument is an unsigned int. 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 F.6 compares the two functions. Table F.6. 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 a 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(), and mysql_row_seek(), 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 are retrieved. | 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() can 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 as that function on 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 query has been issued, or if the query 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 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. 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 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 by passing CLIENT_FOUND_ROWS in the flags parameter to mysql_real_connect(). mysql_real_connect() returns a my_ulonglong value; see the note about printing values of this type in the "Scalar Data Types" section earlier in this appendix. 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. Prior to MySQL 3.23.7, the row_num argument was of type unsigned int. 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 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 can 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); } 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 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")); } Prior to MySQL 3.22.5, 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 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 yourself for 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 can 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 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 zero 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 the result set\n"); } 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\n", (unsigned long) mysql_affected_rows (conn)); } } else /* a result set was returned */ { /* ... process rows here, then free result set ... */ 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 source 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 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(). 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(). As of MySQL 3.22.3, mysql_free_result() automatically fetches and discards any unfetched rows for result sets generated by calling mysql_use_result(). 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. (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 query that you expect to generate a new value. If you issue another query before calling mysql_insert_id(), its value will be reset. Note that this behavior 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 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 "Scalar Data Types" section earlier in this appendix. 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 either way, but invoking it with a connection handler argument now is deprecated. You should always retrieve a column count with mysql_field_count() instead; see the description of that function for an example that 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 */ } mysql_num_rows() returns a my_ulonglong value; see the note about printing values of this type in the "Scalar Data Types" section earlier in this appendix. 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 can 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 F.6. 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. As of MySQL 3.22.3, 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. (Prior to MySQL 3.22.3, you should fetch the rows yourself until none are left and then call mysql_free_result()., If you fail to do so before issuing another query, an "out of sync" error will occur.) See the comparison of mysql_store_result() and mysql_use_result() in Table F.6. mysql_store_result() and mysql_use_result() are both 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. 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". mysql_character_set_name() was introduced in MySQL 3.23.21. const char * mysql_get_client_info (void); Returns a null-terminated string describing the client library version, for example, "3.23.51". 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", or ". via named pipe". 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, "4.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. 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 the following: 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 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 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 mysql_kill(). 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. Administrative Routines The functions in this section allow you to control aspects of server operation. 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 or 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. mysql_thread_end() was introduced in MySQL 4.0.0. 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. mysql_thread_init() was introduced in MySQL 4.0.0. 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. mysql_thread_safe() was introduced in MySQL 3.23.14 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 Thee following 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 PROCESS privilege to do this prior to MySQL 4.0.2 and the SUPER privilege from 4.0.2 on. 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 alternative preferred ways to do the same thing. Most of these functions can be replaced by issuing an equivalent query with mysql_query(). For example, mysql_create_db("db_name") can be replaced with the following call: mysql_query (conn, "CREATE DATABASE db_name") 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. As FLUSH has been extended to perform a wider variety of flush operations, mysql_refresh() became deprecated, too. 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. (But you should consider upgrading. In general, it is safe to upgrade to newer MySQL client libraries because they will work with both old and new servers.) A few functions, such as mysql_connect(), mysql_eof(), and mysql_escape_string() are deprecated because they have been replaced by other functions that do more or that provide more information. To plan for the future, it's best to avoid all functions listed in this section because some or all of them may disappear eventually. MYSQL * mysql_connect (MYSQL *conn, const char *host_name, const char *user_name, const 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, const char *db_name); Creates a database with the name given by db_name. This can be done now by issuing a CREATE DATABASE statement with mysql_query(). mysql_create_db() returns zero for success or non-zero for failure. This function is deprecated as of MySQL 3.21.15. int mysql_drop_db (MYSQL *conn, const char *db_name); Drops the database named by db_name. This can be done now by issuing a DROP DATABASE statement with mysql_query(). mysql_drop_db() returns zero for success or 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() can 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() can be used now to achieve the same effect, although 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. unsigned long mysql_escape_string (char *to_str, const char *from_str, unsigned long from_len); Encodes a string that can contain special characters so that it can be used in an SQL statement. mysql_escape_string() is like mysql_real_escape_string() except that it does not take a conn argument indicating the current connection and does not take into account the current character set when performing encoding. See the description of mysql_real_escape_string() for more information. As of MySQL 3.23.14, mysql_escape_string() is deprecated and mysql_real_escape_string() should be used in preference to it. Prior to MySQL 3.23.6, the from_len argument is an unsigned int. int mysql_kill (MYSQL *conn, unsigned long thread_id); Kills the server thread identified by thread_id and returns zero for success or non-zero for failure. You can always kill your own threads. To kill other threads, you must have PROCESS privilege prior to MySQL 4.0.2, and the SUPER privilege from 4.0.2 on. The effect of this function can be achieved by issuing a KILL statement with mysql_query(). mysql_kill() is deprecated as of MySQL 3.22.9. MYSQL_RES * mysql_list_dbs (MYSQL *conn, const 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 pattern indicated by wild (which can contain the '%' and '_' wildcard pattern characters) 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() can be obtained by executing a SHOW DATABASES statement 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, const char *tbl_name, const 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 pattern indicated by wild (which can contain the '%' and '_' wildcard pattern characters) or all columns if wild is NULL. You are responsible for calling mysql_free() to free the result set. The list produced by mysql_list_fields() can be obtained by executing a SHOW COLUMNS statement 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 currently 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() can 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 pattern indicated by wild (which can contain the '%' and '_' wildcard pattern characters) 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() can be obtained by executing a SHOW TABLES statement with mysql_query() and then processing the result set. This function is deprecated as of MySQL 3.22.0. 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 or non-zero for failure. The options value should be composed from 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. This option was introduced in MySQL 3.23.19. REFRESH_SLAVE Tells a replication slave server to forget its position in the master logs. This is equivalent to issuing a RESET SLAVE statement. This option was introduced in MySQL 3.23.19. REFRESH_STATUS Reinitializes the status variables to zero. This is equivalent to issuing a FLUSH STATUS statement. This option was introduced in MySQL 3.22.11. REFRESH_TABLES Closes all open tables. This is equivalent to issuing a FLUSH TABLES statement. REFRESH_THREADS Flushes the thread cache. This option was introduced in MySQL 3.23.16. 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 mysql_refresh() is deprecated because you can perform the same actions by issuing FLUSH or RESET statements. See the descriptions of those statements in Appendix D, "SQL Syntax Reference." 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, non-zero for failure. This function is deprecated as of MySQL 3.21.9. |
