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.

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.

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

  Part I: General MySQL Use Chapter 1. Getting Started with MySQL and SQL How MySQL Can Help You A Sample Database Basic Database Terminology A MySQL Tutorial Tips for Interacting with mysql Where to Now? Chapter 2. Working with Data in MySQL MySQL Data Types MySQL Column Types Working with Sequences Character Set Support Choosing Column Types Expression Evaluation and Type Conversion Chapter 3. MySQL SQL Syntax and Use MySQL Naming Rules Selecting, Creating, Dropping, and Altering Databases Creating, Dropping, Indexing, and Altering Tables Getting Information about Databases and Tables Retrieving Records from Multiple Tables Multiple-Table Deletes and Updates Performing Transactions Foreign Keys and Referential Integrity Using FULLTEXT Searches Writing Comments Features That MySQL Does Not Support Chapter 4. Query Optimization Using Indexing The MySQL Query Optimizer Column Type Choices and Query Efficiency Loading Data Efficiently Scheduling and Locking Issues Optimization for Administrators Part II: Using MySQL Programming Interfaces Chapter 5. Introduction to MySQL Programming Why Write Your Own MySQL Programs? APIs Available for MySQL Choosing an API Chapter 6. The MySQL C API General Procedure for Building Client Programs Client 1?Connecting to the Server Client 2?Adding Error Checking Client 3?Getting Connection Parameters at Runtime Processing Queries Client 4?An Interactive Query Program Writing Clients That Include SSL Support Using the Embedded Server Library Miscellaneous Topics Chapter 7. The Perl DBI API Perl Script Characteristics Perl DBI Overview Putting DBI to Work Using DBI in Web Applications Chapter 8. The PHP API PHP Overview Putting PHP to Work Part III: MySQL Administration Chapter 9. Introduction to MySQL Administration Overview of Administrative Duties General Administration Security Database Repair and Maintenance Chapter 10. The MySQL Data Directory Location of the Data Directory Structure of the Data Directory Relocating Data Directory Contents Chapter 11. General MySQL Administration Securing a New MySQL Installation Arranging for MySQL Server Startup and Shutdown Managing MySQL User Accounts Maintaining Log Files Additional Server Configuration Topics Running Multiple Servers Setting Up Replication Servers Updating MySQL Chapter 12. Security Internal Security: Preventing Unauthorized File System Access External Security: Preventing Unauthorized Network Access Setting Up Secure Connections Chapter 13. Database Backups, Maintenance, and Repair Coordinating with the Server Preparing for Disaster Table Repair and Data Recovery Part IV: Appendixes Appendix A. Obtaining and Installing Software Obtaining the sampdb Sample Database Distribution Obtaining MySQL and Related Software Installing MySQL on UNIX Installing MySQL on Windows Appendix B. Column Type Reference Numeric Types String Types Date and Time Types Appendix C. Operator and Function Reference Operators Functions Appendix D. SQL Syntax Reference SQL Statements SQL Variables Comment Syntax Appendix E. MySQL Program Reference Specifying Program Options 'libmysqld' 'myisamchk' and 'isamchk' 'myisampack' and 'pack_isam' 'mysql' 'mysqlaccess' 'mysqladmin' 'mysqlbinlog' 'mysqlbug' 'mysqlcheck' 'mysql_config' 'mysqld' 'mysqld_multi' 'mysqld_safe' 'mysqldump' 'mysqlhotcopy' 'mysqlimport' 'mysql_install_db' 'mysql.server' 'mysqlshow' 'safe_mysqld' Appendix F. C API Reference Compiling and Linking C API Data Types C API Functions Appendix G. Perl DBI API Reference Writing Scripts DBI Methods DBI Utility Functions DBI Attributes DBI Environment Variables Appendix H. PHP API Reference Writing PHP Scripts Functions Appendix I. Internet Service Providers Getting Ready to Shop for an ISP Assessing an ISP?General Considerations Assessing an ISP?MySQL-Specific Considerations