The C API code is distributed with MySQL. It is included in the mysqlclient library and allows C programs to access a database.
Many of the clients in the MySQL source distribution are written in C. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. You can find these in the clients directory in the MySQL source distribution.
Most of the other client APIs (all except Java) use the mysqlclient library to communicate with the MySQL server. This means that, for example, you can take advantage of many of the same environment variables that are used by other client programs because they are referenced from the library. See Section 4.8, for a list of these variables.
The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16K bytes) is automatically increased up to the maximum size (the maximum is 16M). Because buffer sizes are increased only as demand warrants, simply increasing the default maximum limit does not in itself cause more resources to be used. This size check is mostly a check for erroneous queries and communication packets.
The communication buffer must be large enough to contain a single SQL statement (for client-to-server traffic) and one row of returned data (for server-to-client traffic). Each thread’s communication buffer is dynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOB values that contain up to 16M of data, you must have a communication buffer limit of at least 16M (in both server and client). The client’s default maximum is 16M, but the default maximum in the server is 1M. You can increase this by changing the value of the max_allowed_packet parameter when the server is started. See Section 5.5.2.
MySQL server shrinks each communication buffer to net_buffer_length bytes after each query. For clients, the size of the buffer associated with a connection is not decreased until the connection is closed, at which time client memory is reclaimed.
For programming with threads, see Section 8.4.8. For creating a stand-alone application that includes the “server” and “client” in the same program (and does not communicate with an external MySQL server), see Section 8.4.9.
- MYSQL
This structure represents a handle to one database connection. It is used for almost all MySQL functions.
- MYSQL_RES
This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from a query is called the result set in the remainder of this section.
- MYSQL_ROW
This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data because such values may contain null bytes internally.) Rows are obtained by calling mysql_fetch_row( ).
- MYSQL_FIELD
This structure contains information about a field, such as the field’s name, type, and size. Its members are described in more detail later. You may obtain the MYSQL_FIELD structures for each field by calling mysql_fetch_field( ) repeatedly. Field values are not part of this structure; they are contained in a MYSQL_ROW structure.
- MYSQL_FIELD_OFFSET
This is a type-safe representation of an offset into a MySQL field list (used by mysql_field_seek( )). Offsets are field numbers within a row, beginning at zero.
- my_ulonglong
The type used for the number of rows and for mysql_affected_rows( ), mysql_num_rows( ), and mysql_insert_id( ). This type provides a range of 0 to 1.84e19.
On some systems, attempting to print a value of type my_ulonglong will not work. To print such a value, convert it to unsigned long and use a %lu print format. Example:
printf (Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));
The MYSQL_FIELD structure contains the following members:
- char * name
The name of the field, as a null-terminated string.
- char * table
The name of the table containing this field, if it isn’t a calculated field. For calculated fields, the table value is an empty string.
- char * def
The default value of this field, as a null-terminated string. This is set only if you use mysql_list_fields( ).
- enum enum_field_types type
The type of the field. The type value may be one of the following:
Type value
Type description
FIELD_TYPE_TINY
TINYINT field
FIELD_TYPE_SHORT
SMALLINT field
FIELD_TYPE_LONG
INTEGER field
FIELD_TYPE_INT24
MEDIUMINT field
FIELD_TYPE_LONGLONG
BIGINT field
FIELD_TYPE_DECIMAL
DECIMAL or NUMERIC field
FIELD_TYPE_FLOAT
FLOAT field
FIELD_TYPE_DOUBLE
DOUBLE or REAL field
FIELD_TYPE_TIMESTAMP
TIMESTAMP field
FIELD_TYPE_DATE
DATE field
FIELD_TYPE_TIME
TIME field
FIELD_TYPE_DATETIME
DATETIME field
FIELD_TYPE_YEAR
YEAR field
FIELD_TYPE_STRING
String (CHAR or VARCHAR) field
FIELD_TYPE_BLOB
BLOB or TEXT field (use max_length to determine the maximum length)
FIELD_TYPE_SET
SET field
FIELD_TYPE_ENUM
ENUM field
FIELD_TYPE_NULL
NULL-type field
FIELD_TYPE_CHAR
Deprecated; use FIELD_TYPE_TINY instead
You can use the IS_NUM( ) macro to test whether a field has a numeric type. Pass the type value to IS_NUM( ) and it will evaluate to TRUE if the field is numeric:
if (IS_NUM(field->type)) printf("Field is numeric\n");
- unsigned int length
The width of the field, as specified in the table definition.
- unsigned int max_length
The maximum width of the field for the result set (the length of the longest field value for the rows actually in the result set). If you use mysql_store_result( ) or mysql_list_fields( ), this contains the maximum length for the field. If you use mysql_use_result( ), the value of this variable is zero.
- unsigned int flags
Different bit-flags for the field. The flags value may have zero or more of the following bits set:
Flag value
Flag description
NOT_NULL_FLAG
Field can’t be NULL.
PRI_KEY_FLAG
Field is part of a primary key.
UNIQUE_KEY_FLAG
Field is part of a unique key.
MULTIPLE_KEY_FLAG
Field is part of a non-unique key.
UNSIGNED_FLAG
Field has the UNSIGNED attribute.
ZEROFILL_FLAG
Field has the ZEROFILL attribute.
BINARY_FLAG
Field has the BINARY attribute.
AUTO_INCREMENT_FLAG
Field has the AUTO_INCREMENT attribute.
ENUM_FLAG
Field is an ENUM (deprecated).
SET_FLAG
Field is a SET (deprecated).
BLOB_FLAG
Field is a BLOB or TEXT (deprecated).
TIMESTAMP_FLAG
Field is a TIMESTAMP (deprecated).
Use of the BLOB_FLAG, ENUM_FLAG, SET_FLAG, and TIMESTAMP_FLAG flags is deprecated because they indicate the type of a field rather than an attribute of its type. It is preferable to test field->type against FIELD_TYPE_BLOB, FIELD_TYPE_ENUM, FIELD_TYPE_SET, or FIELD_TYPE_TIMESTAMP instead.
The following example illustrates a typical use of the flags value:
if (field->flags & NOT_NULL_FLAG) printf("Field can't be null\n");
You may use the following convenience macros to determine the boolean status of the flags value:
Flag status
Description
IS_NOT_NULL(flags)
True if this field is defined as NOT NULL
IS_PRI_KEY(flags)
True if this field is a primary key
IS_BLOB(flags)
True if this field is a BLOB or TEXT (deprecated; test field->type instead)
- unsigned int decimals
The number of decimals for numeric fields.
The functions available in the C API are listed next and are described in greater detail in a later section. See Section 8.4.3.
Function |
Description |
---|---|
mysql_affected_rows( ) |
Returns the number of rows changed/deleted/inserted by the last UPDATE, DELETE, or INSERT query. |
mysql_change_user( ) |
Changes user and database on an open connection. |
mysql_character_set_name( ) |
Returns the name of the default character set for the connection. |
mysql_close( ) |
Closes a server connection. |
mysql_connect( ) |
Connects to a MySQL server. This function is deprecated; use mysql_real_connect( ) instead. |
mysql_create_db( ) |
Creates a database. This function is deprecated; use the SQL command CREATE DATABASE instead. |
mysql_data_seek( ) |
Seeks to an arbitrary row in a query result set. |
mysql_debug( ) |
Does a DBUG_PUSH with the given string. |
mysql_drop_db( ) |
Drops a database. This function is deprecated; use the SQL command DROP DATABASE instead. |
mysql_dump_debug_info( ) |
Makes the server write debug information to the log. |
mysql_eof( ) |
Determines whether the last row of a result set has been read. This function is deprecated; mysql_errno( ) or mysql_error( ) may be used instead. |
mysql_errno( ) |
Returns the error number for the most recently invoked MySQL function. |
mysql_error( ) |
Returns the error message for the most recently invoked MySQL function. |
mysql_escape_string( ) |
Escapes special characters in a string for use in an SQL statement. |
mysql_fetch_field( ) |
Returns the type of the next table field. |
mysql_fetch_field_direct( ) |
Returns the type of a table field, given a field number. |
mysql_fetch_fields( ) |
Returns an array of all field structures. |
mysql_fetch_lengths( ) |
Returns the lengths of all columns in the current row. |
mysql_fetch_row( ) |
Fetches the next row from the result set. |
mysql_field_seek( ) |
Puts the column cursor on a specified column. |
mysql_field_count( ) |
Returns the number of result columns for the most recent query. |
mysql_field_tell( ) |
Returns the position of the field cursor used for the last mysql_fetch_field( ). |
mysql_free_result( ) |
Frees memory used by a result set. |
mysql_get_client_info( ) |
Returns client version information. |
mysql_get_host_info( ) |
Returns a string describing the connection. |
mysql_get_proto_info( ) |
Returns the protocol version used by the connection. |
mysql_get_server_info( ) |
Returns the server version number. |
mysql_info( ) |
Returns information about the most recently executed query. |
mysql_init( ) |
Gets or initialises a MySQL structure. |
mysql_insert_id( ) |
Returns the ID generated for an AUTO_INCREMENT column by the previous query. |
mysql_kill( ) |
Kills a given thread. |
mysql_list_dbs( ) |
Returns database names matching a simple regular expression. |
mysql_list_fields( ) |
Returns field names matching a simple regular expression. |
mysql_list_processes( ) |
Returns a list of the current server threads. |
mysql_list_tables( ) |
Returns table names matching a simple regular expression. |
mysql_num_fields( ) |
Returns the number of columns in a result set. |
mysql_num_rows( ) |
Returns the number of rows in a result set. |
mysql_options( ) |
Sets connect options for mysql_connect( ). |
mysql_ping( ) |
Checks whether the connection to the server is working, reconnecting as necessary. |
mysql_query( ) |
Executes an SQL query specified as a null-terminated string. |
mysql_real_connect( ) |
Connects to a MySQL server. |
mysql_real_escape_string( ) |
Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection. |
mysql_real_query( ) |
Executes an SQL query specified as a counted string. |
mysql_reload( ) |
Tells the server to reload the grant tables. |
mysql_row_seek( ) |
Seeks to a row in a result set, using the value returned from mysql_row_tell( ). |
mysql_row_tell( ) |
Returns the row cursor position. |
mysql_select_db( ) |
Selects a database. |
mysql_shutdown( ) |
Shuts down the database server. |
mysql_stat( ) |
Returns the server status as a string. |
mysql_store_result( ) |
Retrieves a complete result set to the client. |
mysql_thread_id( ) |
Returns the current thread ID. |
mysql_thread_safe( ) |
Returns 1 if the clients are compiled as thread-safe. |
mysql_use_result( ) |
Initiates a row-by-row result set retrieval. |
To connect to the server, call mysql_init( ) to initialise a connection handler, then call mysql_real_connect( ) with that handler (along with other information such as the hostname, username, and password). Upon connection, mysql_real_connect( ) sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up. When you are done with the connection, call mysql_close( ) to terminate it.
While a connection is active, the client may send SQL queries to the server using mysql_query( ) or mysql_real_query( ). The difference between the two is that mysql_query( ) expects the query to be specified as a null-terminated string, whereas mysql_real_query( ) expects a counted string. If the string contains binary data (which may include null bytes), you must use mysql_real_query( ).
For each non-SELECT query (for example, INSERT, UPDATE, DELETE), you can find out how many rows were changed (affected) by calling mysql_affected_rows( ).
For SELECT queries, you retrieve the selected rows as a result set. (Note that some statements are SELECT-like in that they return rows. These include SHOW, DESCRIBE, and EXPLAIN. They should be treated the same way as SELECT statements.)
There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once by calling mysql_store_result( ). This function acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval by calling mysql_use_result( ). This function initialises the retrieval, but does not actually get any rows from the server.
In both cases, you access rows by calling mysql_fetch_row( ). With mysql_store_result( ), mysql_fetch_row( ) accesses rows that have already been fetched from the server. With mysql_use_result( ), mysql_fetch_row( ) actually retrieves the row from the server. Information about the size of the data in each row is available by calling mysql_fetch_lengths( ).
After you are done with a result set, call mysql_free_result( ) to free the memory used for it.
The two retrieval mechanisms are complementary. Client programs should choose the approach that is most appropriate for their requirements. In practice, clients tend to use mysql_store_result( ) more commonly.
An advantage of mysql_store_result( ) is that because the rows have all been fetched to the client, you not only can access rows sequentially, but you also can move back and forth in the result set using mysql_data_seek( ) or mysql_row_seek( ) to change the current row position within the result set. You can also find out how many rows there are by calling mysql_num_rows( ). On the other hand, the memory requirements for mysql_store_result( ) may be very high for large result sets, and thus you are more likely to encounter out-of-memory conditions.
An advantage of mysql_use_result( ) is that the client requires less memory for the result set because it maintains only one row at a time (and because there is less allocation overhead, mysql_use_result( ) can be faster). Disadvantages are that you must process each row quickly to avoid tying up the server, you don’t have random access to rows within the result set (you can only access rows sequentially), and you don’t know how many rows are in the result set until you have retrieved them all. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you’ve found the information you were looking for.
The API makes it possible for clients to respond appropriately to queries (retrieving rows only as necessary) without knowing whether the query is a SELECT. You can do this by calling mysql_store_result( ) after each mysql_query( ) (or mysql_real_query( )). If the result set call succeeds, the query was a SELECT and you can read the rows. If the result set call fails, call mysql_field_count( ) to determine whether a result was actually to be expected. If mysql_field_count( ) returns zero, the query returned no data (indicating that it was an INSERT, UPDATE, DELETE, etc.), and was not expected to return rows. If mysql_field_count( ) is non-zero, the query should have returned rows, but didn’t. This indicates that the query was a SELECT that failed. See the description for mysql_field_count( ) for an example of how this can be done.
Both mysql_store_result( ) and mysql_use_result( ) allow you to obtain information about the fields that make up the result set (the number of fields, their names and types, etc.). You can access field information sequentially within the row by calling mysql_fetch_field( ) repeatedly, or by field number within the row by calling mysql_fetch_field_direct( ). The current field cursor position may be changed by calling mysql_field_seek( ). Setting the field cursor affects subsequent calls to mysql_fetch_field( ). You can also get information for fields all at once by calling mysql_fetch_fields( ).
For detecting and reporting errors, MySQL provides access to error information by means of the mysql_errno( ) and mysql_error( ) functions. These return the error code or error message for the most recently invoked function that can succeed or fail, allowing you to determine when an error occurred and what it was.
In the following descriptions, a parameter or return value of NULL means NULL in the sense of the C programming language, not a MySQL NULL value.
Functions that return a value generally return a pointer or an integer. Unless specified otherwise, functions returning a pointer return a non-NULL value to indicate success or a NULL value to indicate an error, and functions returning an integer return zero to indicate success or non-zero to indicate an error. Note that “non-zero” means just that. Unless the function description says otherwise, do not test against a value other than zero:
if (result) /* correct */ ... error ... if (result < 0) /* incorrect */ ... error ... if (result == -1) /* incorrect */ ... error ...
When a function returns an error, the Errors subsection of the function description lists the possible types of errors. You can find out which of these occurred by calling mysql_errno( ). A string representation of the error may be obtained by calling mysql_error( ).
Returns the number of rows changed by the last UPDATE, deleted by the last DELETE, or inserted by the last INSERT statement. May be called immediately after mysql_query( ) for UPDATE, DELETE, or INSERT statements. For SELECT statements, mysql_affected_rows( ) works like mysql_num_rows( ).
An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query, or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query, mysql_affected_rows( ) was called prior to calling mysql_store_result( ).
mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10"); printf("%ld products updated",(long) mysql_affected_rows(&mysql));
If one specifies the flag CLIENT_FOUND_ROWS when connecting to mysqld, mysql_affected_rows( ) will return the number of rows matched by the WHERE statement for UPDATE statements.
Note that when one uses a REPLACE command, mysql_affected_rows( ) will return 2 if the new row replaced an old row. This is because in this case one row was inserted and then the duplicate was deleted.
Changes the user and causes the database specified by db to become the default (current) database on the connection specified by mysql. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.
This function was introduced in MySQL Version 3.23.3.
mysql_change_user( ) fails unless the connected user can be authenticated or if he doesn’t have permission to use the database. In this case the user and database are not changed.
The db parameter may be set to NULL if you don’t want to have a default database.
The same that you can get from mysql_real_connect( ):
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
- ER_UNKNOWN_COM_ERROR
The MySQL server doesn’t implement this command (probably an old server).
- ER_ACCESS_DENIED_ERROR
The user or password was wrong.
- ER_BAD_DB_ERROR
The database didn’t exist.
- ER_DBACCESS_DENIED_ERROR
The user did not have access rights to the database.
- ER_WRONG_DB_NAME
The database name was too long.
if (mysql_change_user(&mysql, "user", "password", "new_database")) { fprintf(stderr, "Failed to change user. Error: %s\n", mysql_error(&mysql)); }
Closes a previously opened connection. mysql_close( ) also deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init( ) or mysql_connect( ).
This function is deprecated. It is preferable to use mysql_real_connect( ) instead.
mysql_connect( ) attempts to establish a connection to a MySQL database engine running on host. mysql_connect( ) must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info( ).
The meanings of the parameters are the same as for the corresponding parameters for mysql_real_connect( ) with the difference that the connection parameter may be NULL. In this case the C API allocates memory for the connection structure automatically and frees it when you call mysql_close( ). The disadvantage of this approach is that you can’t retrieve an error message if the connection fails. (To get error information from mysql_errno( ) or mysql_error( ), you must provide a valid MYSQL pointer.)
Creates the database named by the db parameter.
This function is deprecated. It is preferable to use mysql_query( ) to issue a SQL CREATE DATABASE statement instead.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
if(mysql_create_db(&mysql, "my_database")) { fprintf(stderr, "Failed to create new database. Error: %s\n", mysql_error(&mysql)); }
Seeks to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_data_seek( ) may be used in conjunction only with mysql_store_result( ), not with mysql_use_result( ).
The offset should be a value in the range from 0 to mysql_num_rows(result)-1.
Does a DBUG_PUSH with the given string. mysql_debug( ) uses the Fred Fish debug library. To use this function, you must compile the client library to support debugging. See Section D.1, and Section D.2.
The following call causes the client library to generate a trace file in
/tmp/client.trace
on the client machine:
mysql_debug("d:t:O,/tmp/client.trace");
Drops the database named by the db parameter.
This function is deprecated. It is preferable to use mysql_query( ) to issue a SQL DROP DATABASE statement instead.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
if(mysql_drop_db(&mysql, "my_database")) fprintf(stderr, "Failed to drop the database: Error: %s\n", mysql_error(&mysql));
Instructs the server to write some debug information to the log. The connected user must have the process privilege for this to work.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
This function is deprecated. mysql_errno( ) or mysql_error( ) may be used instead.
mysql_eof( ) determines whether the last row of a result set has been read.
If you acquire a result set from a successful call to mysql_store_result( ), the client receives the entire set in one operation. In this case, a NULL return from mysql_fetch_row( ) always means the end of the result set has been reached and it is unnecessary to call mysql_eof( ). When used with mysql_store_result( ), mysql_eof( ) will always return true.
On the other hand, if you use mysql_use_result( ) to initiate a result set retrieval, the rows of the set are obtained from the server one by one as you call mysql_fetch_row( ) repeatedly. Because an error may occur on the connection during this process, a NULL return value from mysql_fetch_row( ) does not necessarily mean the end of the result set was reached normally. In this case, you can use mysql_eof( ) to determine what happened. mysql_eof( ) returns a non-zero value if the end of the result set was reached and zero if an error occurred.
Historically, mysql_eof( ) predates the standard MySQL error functions mysql_errno( ) and mysql_error( ). Because those error functions provide the same information, their use is preferred over mysql_eof( ), which is now deprecated. (In fact, they provide more information because mysql_eof( ) returns only a boolean value whereas the error functions indicate a reason for the error when one occurs.)
The following example shows how you might use mysql_eof( ):
mysql_query(&mysql,"SELECT * FROM some_table"); result = mysql_use_result(&mysql); while((row = mysql_fetch_row(result))) { // do something with data } if(!mysql_eof(result)) // mysql_fetch_row( ) failed due to an error { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); }
However, you can achieve the same effect with the standard MySQL error functions:
mysql_query(&mysql,"SELECT * FROM some_table"); result = mysql_use_result(&mysql); while((row = mysql_fetch_row(result))) { // do something with data } if(mysql_errno(&mysql)) // mysql_fetch_row( ) failed due to an error { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); }
For the connection specified by mysql, mysql_errno( ) returns
the error code for the most recently invoked API function that can succeed
or fail. A return value of zero means that no error occurred. Client error
message numbers are listed in the MySQL errmsg.h
header file.
Server error message numbers are listed in mysqld_error.h
. In the
MySQL source distribution you can find a complete list of
error messages and error numbers in the file Docs/mysqld_error.txt
.
For the connection specified by mysql, mysql_error( ) returns the error message for the most recently invoked API function that can succeed or fail. An empty string (" ") is returned if no error occurred. This means the following two tests are equivalent:
if(mysql_errno(&mysql)) { // an error occurred } if(mysql_error(&mysql)[0] != '\0') { // an error occurred }
The language of the client error messages may be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages. See Section 4.6.2.
You should use mysql_real_escape_string( ) instead!
This function is identical to mysql_real_escape_string( ) except that mysql_real_escape_string( ) takes a connection handler as its first argument and escapes the string according to the current character set. mysql_escape_string( ) does not take a connection argument and does not respect the current charset setting.
Returns the definition of one column of a result set as a MYSQL_FIELD structure. Call this function repeatedly to retrieve information about all columns in the result set. mysql_fetch_field( ) returns NULL when no more fields are left.
mysql_fetch_field( ) is reset to return information about the first field each time you execute a new SELECT query. The field returned by mysql_fetch_field( ) is also affected by calls to mysql_field_seek( ).
If you’ve called mysql_query( ) to perform a SELECT on a table but have not called mysql_store_result( ), MySQL returns the default blob length (8K bytes) if you call mysql_fetch_field( ) to ask for the length of a BLOB field. (The 8K size is chosen because MySQL doesn’t know the maximum length for the BLOB. This should be made configurable sometime.) Once you’ve retrieved the result set, field->max_length contains the length of the largest value for this column in the specific query.
MYSQL_FIELD *field; while((field = mysql_fetch_field(result))) { printf("field name %s\n", field->name); }
Returns an array of all MYSQL_FIELD structures for a result set. Each structure provides the field definition for one column of the result set.
unsigned int num_fields; unsigned int i; MYSQL_FIELD *fields; num_fields = mysql_num_fields(result); fields = mysql_fetch_fields(result); for(i = 0; i < num_fields; i++) { printf("Field %u is %s\n", i, fields[i].name); }
Given a field number fieldnr for a column within a result set, returns that column’s field definition as a MYSQL_FIELD structure. You may use this function to retrieve the definition for an arbitrary column. The value of fieldnr should be in the range from 0 to mysql_num_fields(result)-1.
unsigned int num_fields; unsigned int i; MYSQL_FIELD *field; num_fields = mysql_num_fields(result); for(i = 0; i < num_fields; i++) { field = mysql_fetch_field_direct(result, i); printf("Field %u is %s\n", i, field->name); }
Returns the lengths of the columns of the current row within a result set. If you plan to copy field values, this length information is also useful for optimisation because you can avoid calling strlen( ). In addition, if the result set contains binary data, you must use this function to determine the size of the data because strlen( ) returns incorrect results for any field containing null characters.
The length for empty columns and for columns containing NULL values is zero. To see how to distinguish these two cases, see the description for mysql_fetch_row( ).
An array of unsigned long integers representing the size of each column (not including any terminating null characters). NULL if an error occurred.
mysql_fetch_lengths( ) is valid only for the current row of the result set. It returns NULL if you call it before calling mysql_fetch_row( ) or after retrieving all rows in the result.
MYSQL_ROW row; unsigned long *lengths; unsigned int num_fields; unsigned int i; row = mysql_fetch_row(result); if (row) { num_fields = mysql_num_fields(result); lengths = mysql_fetch_lengths(result); for(i = 0; i < num_fields; i++) { printf("Column %u is %lu bytes in length.\n", i, lengths[i]); } }
Retrieves the next row of a result set. When used after mysql_store_result( ), mysql_fetch_row( ) returns NULL when there are no more rows to retrieve. When used after mysql_use_result( ), mysql_fetch_row( ) returns NULL when there are no more rows to retrieve or if an error occurred.
The number of values in the row is given by mysql_num_fields(result). If row holds the return value from a call to mysql_fetch_row( ), pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1]. NULL values in the row are indicated by NULL pointers.
The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths( ). Empty fields and fields containing NULL both have length 0; you can distinguish these by checking the pointer for the field value. If the pointer is NULL, the field is NULL; otherwise, the field is empty.
A MYSQL_ROW structure for the next row. NULL if there are no more rows to retrieve or if an error occurred.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
MYSQL_ROW row; unsigned int num_fields; unsigned int i; num_fields = mysql_num_fields(result); while ((row = mysql_fetch_row(result))) { unsigned long *lengths; lengths = mysql_fetch_lengths(result); for(i = 0; i < num_fields; i++) { printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL"); } printf("\n"); }
unsigned int mysql_field_count(MYSQL *mysql)
If you are using a version of MySQL earlier than Version 3.22.24, you should use unsigned int mysql_num_fields(MYSQL *mysql) instead.
Returns the number of columns for the most recent query on the connection.
The normal use of this function is when mysql_store_result( ) returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count( ) to determine whether mysql_store_result( ) should have produced a non-empty result. This allows the client program to take proper action without knowing whether the query was a SELECT (or SELECT-like) statement. The following example illustrates how this may be done.
MYSQL_RES *result; unsigned int num_fields; unsigned int num_rows; if (mysql_query(&mysql,query_string)) { // error } else // query succeeded, process any data returned by it { result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result( ) returned nothing; should it have? { if(mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT) num_rows = mysql_affected_rows(&mysql); } else // mysql_store_result( ) should have returned data { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } } }
An alternative is to replace the mysql_field_count(&mysql) call with mysql_errno(&mysql). In this case, you are checking directly for an error from mysql_store_result( ) rather than inferring from the value of mysql_field_count( ) whether the statement was a SELECT.
Sets the field cursor to the given offset. The next call to mysql_fetch_field( ) will retrieve the field definition of the column associated with that offset.
To seek to the beginning of a row, pass an offset value of zero.
Returns the position of the field cursor used for the last mysql_fetch_field( ). This value can be used as an argument to mysql_field_seek( ).
Frees the memory allocated for a result set by mysql_store_result( ), mysql_use_result( ), mysql_list_dbs( ), etc. When you are done with a result set, you must free the memory it uses by calling mysql_free_result( ).
Returns a string describing the type of connection in use, including the server hostname.
Retrieves a string providing information about the most recently executed query, but only for the following statements. For other statements, mysql_info( ) returns NULL. The format of the string varies depending on the type of query. The numbers are illustrative only; the string will contain values appropriate for the query.
- INSERT INTO ... SELECT ...
String format: Records: 100 Duplicates: 0 Warnings: 0
- INSERT INTO ... VALUES (...),(...),(...)...
String format: Records: 3 Duplicates: 0 Warnings: 0
- LOAD DATA INFILE ...
String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
- ALTER TABLE
String format: Records: 3 Duplicates: 0 Warnings: 0
- UPDATE
String format: Rows matched: 40 Changed: 40 Warnings: 0
Note that mysql_info( ) returns a non-NULL value for the INSERT ... VALUES statement only if multiple value lists are specified in the statement.
A character string representing additional information about the most recently executed query. NULL if no information is available for the query.
Allocates or initialises a MySQL object suitable for mysql_real_connect( ). If mysql is a NULL pointer, the function allocates, initialises, and returns a new object. Otherwise, the object is initialised and the address of the object is returned. If mysql_init( ) allocates a new object, it will be freed when mysql_close( ) is called to close the connection.
An initialised MYSQL* handle. NULL if there was insufficient memory to allocate a new object.
Returns the ID generated for an AUTO_INCREMENT column by the previous query. Use this function after you have performed an INSERT query into a table that contains an AUTO_INCREMENT field.
Note that mysql_insert_id( ) returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id( ) immediately after the query that generates the value.
mysql_insert_id( ) is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column value to LAST_INSERT_ID(expr). See Section 6.3.6.2.
Also note that the value of the SQL LAST_INSERT_ID( ) function always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries because the value of that function is maintained in the server.
The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Returns a result set consisting of database names on the server that match
the simple regular expression specified by the wild parameter.
wild may contain the wildcard characters %
or _
, or may
be a NULL pointer to match all databases. Calling
mysql_list_dbs( ) is similar to executing the query SHOW
databases [LIKE wild].
You must free the result set with mysql_free_result( ).
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_OUT_OF_MEMORY
Out of memory.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Returns a result set consisting of field names in the given table that match
the simple regular expression specified by the wild parameter.
wild may contain the wildcard characters %
or _
, or may
be a NULL pointer to match all fields. Calling
mysql_list_fields( ) is similar to executing the query SHOW
COLUMNS FROM tbl_name [LIKE wild].
Note that it’s recommended that you use SHOW COLUMNS FROM tbl_name instead of mysql_list_fields( ).
You must free the result set with mysql_free_result( ).
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Returns a result set describing the current server threads. This is the same kind of information as that reported by mysqladmin processlist or a SHOW PROCESSLIST query.
You must free the result set with mysql_free_result( ).
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Returns a result set consisting of table names in the current database that
match the simple regular expression specified by the wild parameter.
wild may contain the wildcard characters %
or _
, or may
be a NULL pointer to match all tables. Calling
mysql_list_tables( ) is similar to executing the query SHOW
tables [LIKE wild].
You must free the result set with mysql_free_result( ).
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
unsigned int mysql_num_fields(MYSQL_RES *result)
or
unsigned int mysql_num_fields(MYSQL *mysql)
The second form doesn’t work on MySQL Version 3.22.24 or newer. To pass a MYSQL* argument, you must use unsigned int mysql_field_count(MYSQL *mysql) instead.
Returns the number of columns in a result set.
Note that you can get the number of columns either from a pointer to a result set or to a connection handle. You would use the connection handle if mysql_store_result( ) or mysql_use_result( ) returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count( ) to determine whether mysql_store_result( ) should have produced a non-empty result. This allows the client program to take proper action without knowing whether or not the query was a SELECT (or SELECT-like) statement. The following example illustrates how this may be done.
MYSQL_RES *result; unsigned int num_fields; unsigned int num_rows; if (mysql_query(&mysql,query_string)) { // error } else // query succeeded, process any data returned by it { result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result( ) returned nothing; should it have? { if (mysql_errno(&mysql)) { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } else if (mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT) num_rows = mysql_affected_rows(&mysql); } } }
An alternative (if you know that your query should have returned a result set) is to replace the mysql_errno(&mysql) call with a check if mysql_field_count(&mysql) is = 0. This will only happen if something went wrong.
Returns the number of rows in the result set.
The use of mysql_num_rows( ) depends on whether you use mysql_store_result( ) or mysql_use_result( ) to return the result set. If you use mysql_store_result( ), mysql_num_rows( ) may be called immediately. If you use mysql_use_result( ), mysql_num_rows( ) will not return the correct value until all the rows in the result set have been retrieved.
Can be used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options.
mysql_options( ) should be called after mysql_init( ) and before mysql_connect( ) or mysql_real_connect( ).
The option argument is the option that you want to set; the arg argument is the value for the option. If the option is an integer, arg should point to the value of the integer.
Possible option values:
Option |
Argument type |
Function |
---|---|---|
MYSQL_OPT_CONNECT_TIMEOUT |
unsigned int * |
Connect timeout in seconds. |
MYSQL_OPT_COMPRESS |
Not used |
Use the compressed client/server protocol. |
MYSQL_OPT_LOCAL_INFILE |
Optional pointer to uint |
If no pointer is given or if pointer points to a nonzero unsigned int != 0, the command LOAD LOCAL INFILE is enabled. |
MYSQL_OPT_NAMED_PIPE |
Not used |
Use named pipes to connect to a MySQL server on NT. |
MYSQL_INIT_COMMAND |
char * |
Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting. |
MYSQL_READ_DEFAULT_FILE |
char * |
Read options from the named option file instead of from |
MYSQL_READ_DEFAULT_GROUP |
char * |
Read options from the named group from |
Note that the group client is always read if you use MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP.
The specified group in the option file may contain the following options:
Option |
Description |
---|---|
connect-timeout |
Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server. |
compress |
Use the compressed client/server protocol. |
database |
Connect to this database if no database was specified in the connect command. |
debug |
Debug options. |
disable-local-infile |
Disable use of LOAD DATA LOCAL. |
host |
Default hostname. |
init-command |
Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting. |
interactive-timeout |
Same as specifying CLIENT_INTERACTIVE to mysql_real_connect( ). See Section 8.4.3.171. |
local-infile[=(0|1)] |
If no argument or argument != 0, then enable use of LOAD DATA LOCAL. |
password |
Default password. |
pipe |
Use named pipes to connect to a MySQL server on NT. |
port |
Default port number. |
return-found-rows |
Tell mysql_info( ) to return found rows instead of updated rows when using UPDATE. |
socket |
Default socket number. |
user |
Default user. |
Note that timeout has been replaced by connect-timeout, but timeout will still work for a while.
For more information about option files, see Section 4.1.2.
MYSQL mysql; mysql_init(&mysql); mysql_options(&mysql,MYSQL_OPT_COMPRESS,0); mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc"); if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)) { fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql)); }
This requests that the client use the compressed client/server protocol and
read the additional options from the odbc section in the my.cnf
file.
Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and to reconnect if necessary.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Executes the SQL query pointed to by the null-terminated string query.
The query must consist of a single SQL statement. You should not add
a terminating semicolon (;
) or \g to the statement.
mysql_query( ) cannot be used for queries that contain binary data; you
should use mysql_real_query( ) instead. (Binary data may contain the
\0
character, which mysql_query( ) interprets as the end of the
query string.)
If you want to know if the query should return a result set, you can use mysql_field_count( ) to check for this. See Section 8.4.3.85.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned int client_flag)
mysql_real_connect( ) attempts to establish a connection to a MySQL database engine running on host. mysql_real_connect( ) must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info( ).
The parameters are specified as follows:
The first parameter should be the address of an existing MYSQL structure. Before calling mysql_real_connect( ) you must call mysql_init( ) to initialise the MYSQL structure. You can change a lot of connect options with the mysql_options( ) call. See Section 8.4.3.159.
The value of host may be either a hostname or an IP address. If host is NULL or the string "localhost", a connection to the local host is assumed. If the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.
The user parameter contains the user’s MySQL login ID. If user is NULL, the current user is assumed. Under Unix, this is the current login name. Under Windows ODBC, the current username must be specified explicitly. See Section 8.3.2.
The passwd parameter contains the password for user. If passwd is NULL, only entries in the user table for the user that have a blank (empty) password field will be checked for a match. This allows the database administrator to set up the MySQL privilege system in such a way that users get different privileges depending on whether they have specified a password.
Note: do not attempt to encrypt the password before calling mysql_real_connect( ); password encryption is handled automatically by the client API.
db is the database name. If db is not NULL, the connection will set the default database to this value.
If port is not 0, the value will be used as the port number for the TCP/IP connection. Note that the host parameter determines the type of the connection.
If unix_socket is not NULL, the string specifies the socket or named pipe that should be used. Note that the host parameter determines the type of the connection.
The value of client_flag is usually 0, but can be set to a combination of the following flags in very special circumstances:
Flag name
Flag description
CLIENT_COMPRESS
Use compression protocol.
CLIENT_FOUND_ROWS
Return the number of found (matched) rows, not the number of affected rows.
CLIENT_IGNORE_SPACE
Allow spaces after function names. Makes all function names reserved words.
CLIENT_INTERACTIVE
Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection.
CLIENT_NO_SCHEMA
Don’t allow the db_name.tbl_name.col_name syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs.
CLIENT_ODBC
The client is an ODBC client. This changes mysqld to be more ODBC-friendly.
CLIENT_SSL
Use SSL (encrypted protocol).
A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter.
- CR_CONN_HOST_ERROR
Failed to connect to the MySQL server.
- CR_CONNECTION_ERROR
Failed to connect to the local MySQL server.
- CR_IPSOCK_ERROR
Failed to create an IP socket.
- CR_OUT_OF_MEMORY
Out of memory.
- CR_SOCKET_CREATE_ERROR
Failed to create a Unix socket.
- CR_UNKNOWN_HOST
Failed to find the IP address for the hostname.
- CR_VERSION_ERROR
A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version. This can happen if you use a very old client library to connect to a new server that wasn’t started with the --old-protocol option.
- CR_NAMEDPIPEOPEN_ERROR
Failed to create a named pipe on Windows.
- CR_NAMEDPIPEWAIT_ERROR
Failed to wait for a named pipe on Windows.
- CR_NAMEDPIPESETSTATE_ERROR
Failed to get a pipe handler on Windows.
- CR_SERVER_LOST
If connect_timeout > 0 and it took longer than connect_timeout seconds to connect to the server or if the server died while executing the init-command.
MYSQL mysql; mysql_init(&mysql); mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name"); if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)) { fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql)); }
By using mysql_options( ) the MySQL library will read the
[client] and your_prog_name sections in the my.cnf
file, which will ensure that your program will work, even if someone has
set up MySQL in some non-standard way.
Note that upon connection, mysql_real_connect( ) sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, that you should try reconnecting to the server before giving up.
unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length)
This function is used to create a legal SQL string that you can use in an SQL statement. See Section 6.1.1.1.
The string in from is encoded to an escaped SQL string, taking
into account the current character set of the connection. The result is placed
in to and a terminating null byte is appended. Characters
encoded are NUL (ASCII 0), \n
, \r
, \
,
', “, and Control-Z (see Section 6.1.1).
The string pointed to by from must be length bytes long. You must allocate the to buffer to be at least length*2+1 bytes long. (In the worse case, each character may need to be encoded as using two bytes, and you need room for the terminating null byte.) When mysql_escape_string( ) returns, the contents of to will be a null-terminated string. The return value is the length of the encoded string, not including the terminating null character.
char query[1000],*end; end = strmov(query,"INSERT INTO test_table values("); *end++ = '\''; end += mysql_real_escape_string(&mysql, end,"What's this",11); *end++ = '\''; *end++ = ','; *end++ = '\''; end += mysql_real_escape_string(&mysql, end,"binary data: \0\r\n",16); *end++ = '\''; *end++ = ')'; if (mysql_real_query(&mysql,query,(unsigned int) (end - query))) { fprintf(stderr, "Failed to insert row, Error: %s\n", mysql_error(&mysql)); }
The strmov( ) function used in the example is included in the mysqlclient library and works like strcpy( ) but returns a pointer to the terminating null of the first parameter.
Executes the SQL query pointed to by query, which should be a string
length bytes long. The query must consist of a single SQL statement.
You should not add a terminating semicolon (;
) or \g to the
statement.
You must use mysql_real_query( ) rather than
mysql_query( ) for queries that contain binary data because binary data
may contain the \0
character. In addition, mysql_real_query( )
is faster than mysql_query( ) because it does not call strlen( ) on
the query string.
If you want to know whether the query should return a result set, you can use mysql_field_count( ) to check for this. See Section 8.4.3.85.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Asks the MySQL server to reload the grant tables. The connected user must have the reload privilege.
This function is deprecated. It is preferable to use mysql_query( ) to issue a SQL FLUSH PRIVILEGES statement instead.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Sets the row cursor to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_row_seek( ) may be used in conjunction only with mysql_store_result( ), not with mysql_use_result( ).
The offset should be a value returned from a call to mysql_row_tell( ) or to mysql_row_seek( ). This value is not simply a row number; if you want to seek to a row within a result set using a row number, use mysql_data_seek( ) instead.
The previous value of the row cursor. This value may be passed to a subsequent call to mysql_row_seek( ).
Returns the current position of the row cursor for the last mysql_fetch_row( ). This value can be used as an argument to mysql_row_seek( ).
You should use mysql_row_tell( ) only after mysql_store_result( ), not after mysql_use_result( ).
Causes the database specified by db to become the default (current) database on the connection specified by mysql. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.
mysql_select_db( ) fails unless the connected user can be authenticated as having permission to use the database.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Returns a character string containing information similar to that provided by the mysqladmin status command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
You must call mysql_store_result( ) or mysql_use_result( ) for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).
You don’t have to call mysql_store_result( ) or mysql_use_result( ) for other queries, but it will not do any harm or cause any notable performance if you call mysql_store_result( ) in all cases. You can detect if the query didn’t have a result set by checking if mysql_store_result( ) returns 0 (more about this later).
If you want to know whether the query should return a result set, you can use mysql_field_count( ) to check for this. See Section 8.4.3.85.
mysql_store_result( ) reads the entire result of a query to the client, allocates a MYSQL_RES structure, and places the result into this structure.
mysql_store_result( ) returns a null pointer if the query didn’t return a result set (if the query was, for example, an INSERT statement).
mysql_store_result( ) also returns a null pointer if reading of the result set failed. You can check if you got an error by checking if mysql_error( ) doesn’t return a null pointer, if mysql_errno( ) returns <> 0, or if mysql_field_count( ) returns <> 0.
An empty result set is returned if there are no rows returned. (An empty result set differs from a null pointer as a return value.)
Once you have called mysql_store_result( ) and got a result back that isn’t a null pointer, you may call mysql_num_rows( ) to find out how many rows are in the result set.
You can call mysql_fetch_row( ) to fetch rows from the result set, or mysql_row_seek( ) and mysql_row_tell( ) to obtain or set the current row position within the result set.
You must call mysql_free_result( ) once you are done with the result set.
- CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
- CR_OUT_OF_MEMORY
Out of memory.
- CR_SERVER_GONE_ERROR
The MySQL server has gone away.
- CR_SERVER_LOST
The connection to the server was lost during the query.
- CR_UNKNOWN_ERROR
An unknown error occurred.
Returns the thread ID of the current connection. This value can be used as an argument to mysql_kill( ) to kill the thread.
If the connection is lost and you reconnect with mysql_ping( ), the thread ID will change. This means you should not get the thread ID and store it for later. You should get it when you need it.
You must call mysql_store_result( ) or mysql_use_result( ) for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).
mysql_use_result( ) initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result( ) does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row( ). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result( ). The client will only allocate memory for the current row and a communication buffer that may grow up to max_allowed_packet bytes.
On the other hand, you shouldn’t use mysql_use_result( ) if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This will tie up the server and prevent other threads from updating any tables from which the data is being fetched.
When using mysql_use_result( ), you must execute mysql_fetch_row( ) until a NULL value is returned, otherwise, the unfetched rows will be returned as part of the result set for your next query. The C API will give the error Commands out of sync; you can't run this command now if you forget to do this!
You may not use mysql_data_seek( ), mysql_row_seek( ), mysql_row_tell( ), mysql_num_rows( ), or mysql_affected_rows( ) with a result returned from mysql_use_result( ), nor may you issue other queries until the mysql_use_result( ) has finished. (However, after you have fetched all the rows, mysql_num_rows( ) will accurately return the number of rows fetched.)
You must call mysql_free_result( ) once you are done with the result set.
You need to use the following functions when you want to create a threaded client. See Section 8.4.8.
This function needs to be called once in the program before calling any MySQL function. This initialises some global variables that MySQL needs. If you are using a thread-safe client library, this will also call mysql_thread_init( ) for this thread.
This is automatically called by mysql_init( ), mysql_server_init( ), and mysql_connect( ).
This function needs to be called for each created thread to initialise thread-specific variables.
This is automatically called by my_init( ) and mysql_connect( ).
This function needs to be called before calling pthread_exit( ) to free memory allocated by mysql_thread_init( ).
Note that this function is not invoked automatically by the client library. It must be called explicitly to avoid a memory leak.
You must use the following functions if you want to allow your application to be linked against the embedded MySQL server library. See Section 8.4.9.
If the program is linked with -lmysqlclient instead of -lmysqld, these functions do nothing. This makes it possible to choose between using the embedded MySQL server and a stand-alone server without modifying any code.
This function must be called once in the program before calling any other MySQL function. It starts up the server and initialises any subsystems (mysys, InnoDB, etc.) that the server uses. If this function is not called, the program will crash. If you are using the DBUG package that comes with MySQL, you should call this after you have called MY_INIT( ).
The argc and argv arguments are analogous to the arguments to main( ). The first element of argv is ignored (it typically contains the program name). For convenience, argc may be 0 (zero) if there are no command-line arguments for the server.
The NULL-terminated list of strings in groups selects which groups in the option files will be active. See Section 4.1.2. For convenience, groups may be NULL, in which case the [server] and [emedded] groups will be active.
#include <mysql.h> #include <stdlib.h> static char *server_args[] = { "this_program", /* this string is not used */ "--datadir=.", "--set-variable=key_buffer_size=32M" }; static char *server_groups[] = { "embedded", "server", "this_program_SERVER", (char *)NULL }; int main(void) { mysql_server_init(sizeof(server_args) / sizeof(char *), server_args, server_groups); /* Use any MySQL API functions here */ mysql_server_end( ); return EXIT_SUCCESS; }
This function must be called once in the program after all other MySQL functions. It shuts down the embedded server.
It is possible for mysql_store_result( ) to return NULL following a successful call to mysql_query( ). When this happens, it means one of the following conditions occurred:
There was a malloc( ) failure (for example, if the result set was too large).
The data couldn’t be read (an error occurred on the connection).
The query returned no data (for example, it was an INSERT, UPDATE, or DELETE).
You can always check whether the statement should have produced a non-empty result by calling mysql_field_count( ). If mysql_field_count( ) returns zero, the result is empty and the last query was a statement that does not return values (for example, an INSERT or a DELETE). If mysql_field_count( ) returns a non-zero value, the statement should have produced a non-empty result. See the description of the mysql_field_count( ) function for an example.
You can test for an error by calling mysql_error( ) or mysql_errno( ).
In addition to the result set returned by a query, you can also get the following information:
mysql_affected_rows( ) returns the number of rows affected by the last query when doing an INSERT, UPDATE, or DELETE. An exception is that if DELETE is used without a WHERE clause, the table is re-created empty, which is much faster! In this case, mysql_affected_rows( ) returns zero for the number of records affected.
mysql_num_rows( ) returns the number of rows in a result set. With mysql_store_result( ), mysql_num_rows( ) may be called as soon as mysql_store_result( ) returns. With mysql_use_result( ), mysql_num_rows( ) may be called only after you have fetched all the rows with mysql_fetch_row( ).
mysql_insert_id( ) returns the ID generated by the last query that inserted a row into a table with an AUTO_INCREMENT index. See Section 8.4.3.126.
Some queries (LOAD DATA INFILE ..., INSERT INTO ... SELECT ..., UPDATE) return additional information. The result is returned by mysql_info( ). See the description for mysql_info( ) for the format of the string that it returns. mysql_info( ) returns a NULL pointer if there is no additional information.
If you insert a record in a table containing a column that has the AUTO_INCREMENT attribute, you can get the most recently generated ID by calling the mysql_insert_id( ) function.
You can also retrieve the ID by using the LAST_INSERT_ID( ) function in a query string that you pass to mysql_query( ).
You can check if an AUTO_INCREMENT index is used by executing the following code. This also checks if the query was an INSERT with an AUTO_INCREMENT index:
if (mysql_error(&mysql)[0] == 0 && mysql_num_fields(result) == 0 && mysql_insert_id(&mysql) != 0) { used_id = mysql_insert_id(&mysql); }
The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).
If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like these:
INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID( ),'text'); # use ID in second table
When linking with the C API, the following errors may occur on some systems:
gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl Undefined first referenced symbol in file floor /usr/local/lib/mysql/libmysqlclient.a(password.o) ld: fatal: Symbol referencing errors. No output written to client
If this happens on your system, you must include the math library by adding -lm to the end of the compile/link line.
If you compile MySQL clients that you’ve written yourself or that
you obtain from a third-party, they must be linked using the
-lmysqlclient -lz option on the link command. You may also need to
specify a -L option to tell the linker where to find the library. For
example, if the library is installed in /usr/local/mysql/lib
, use
-L/usr/local/mysql/lib -lmysqlclient -lz on the link command.
For clients that use MySQL header files, you may need to specify a -I option when you compile them (for example, -I/usr/local/mysql/include), so the compiler can find the header files.
The client library is almost thread-safe. The biggest problem is
that the subroutines in net.c
that read from sockets are not
interrupt-safe. This was done with the thought that you might want to
have your own alarm that can break a long read to a server. If you
install interrupt handlers for the SIGPIPE interrupt,
the socket handling should be thread-safe.
In the older binaries we distribute on our web site (http://www.mysql.com/), the client libraries are not normally compiled with the thread-safe option (the Windows binaries are, by default, compiled to be thread-safe). Newer binary distributions should have both a normal and a thread-safe client library.
To get a threaded client where you can interrupt the client from other threads and set timeouts when talking with the MySQL server, you should use the -lmysys, -lstring, and -ldbug libraries and the net_serv.o code that the server uses.
If you don’t need interrupts or timeouts, you can just compile a thread-safe client library (mysqlclient_r) and use this. See Section 8.4. In this case you don’t have to worry about the net_serv.o object file or the other MySQL libraries.
When using a threaded client and you want to use timeouts and
interrupts, you can make great use of the routines in the
thr_alarm.c
file. If you are using routines from the
mysys library, the only thing you must remember is to call
my_init( ) first! See Section 8.4.4.
All functions except mysql_real_connect( ) are, by default, thread-safe. The following notes describe how to compile a thread-safe client library and use it in a thread-safe manner. (These notes for mysql_real_connect( ) actually apply to mysql_connect( ) as well, but because mysql_connect( ) is deprecated, you should be using mysql_real_connect( ) anyway.)
To make mysql_real_connect( ) thread-safe, you must recompile the client library with this command:
shell> ./configure --enable-thread-safe-client
This will create a thread-safe client library libmysqlclient_r. --enable-thread-safe-client. This library is thread-safe per connection. You can let two threads share the same connection with the following caveats:
Two threads can’t send a query to the MySQL server at the same time on the same connection. In particular, you have to ensure that between a mysql_query( ) and mysql_store_result( ) no other thread is using the same connection.
Many threads can access different result sets that are retrieved with mysql_store_result( ).
If you use mysql_use_result, you have to ensure that no other thread is using the same connection until the result set is closed. However, it really is best for threaded clients that share the same connection to use mysql_store_result( ).
If you want to use multiple threads on the same connection, you must have a mutex lock around your mysql_query( ) and mysql_store_result( ) call combination. Once mysql_store_result( ) is ready, the lock can be released and other threads may query the same connection.
If you program with POSIX threads, you can use pthread_mutex_lock( ) and pthread_mutex_unlock( ) to establish and release a mutex lock.
You need to know the following if you have a thread that is calling MySQL functions that did not create the connection to the MySQL database:
When you call mysql_init( ) or mysql_connect( ), MySQL will create a thread-specific variable for the thread that is used by the debug library (among other things).
If you call a MySQL function, before the thread has called mysql_init( ) or mysql_connect( ), the thread will not have the necessary thread-specific variables in place and you are likely to end up with a core dump sooner or later.
The get things to work smoothly you have to do the following:
Call my_init( ) at the start of your program if it calls any other MySQL function before calling mysql_real_connect( ).
Call mysql_thread_init( ) in the thread handler before calling any MySQL function.
In the thread, call mysql_thread_end( ) before calling pthread_exit( ). This will free the memory used by MySQL thread-specific variables.
You may get some errors because of undefined symbols when linking your client with libmysqlclient_r. In most cases this is because you haven’t included the thread libraries on the link/compile line.
The embedded MySQL server library makes it possible to run a full-featured MySQL server inside the client application. The main benefits are increased speed and simpler management for embedded applications.
The API is identical for the embedded MySQL version and the client/server version. To change an old threaded application to use the embedded library, you normally only have to add calls to the following functions:
Function |
When to call |
---|---|
mysql_server_init( ) |
Should be called before any other MySQL function is called, preferably early in the main( ) function. |
mysql_server_end( ) |
Should be called before your program exits. |
mysql_thread_init( ) |
Should be called in each thread you create that will access MySQL. |
mysql_thread_end( ) |
Should be called before calling pthread_exit( ). |
Then you must link your code with libmysqld.a instead of libmysqlclient.a.
The preceding mysql_server_xxx functions are also included in libmysqlclient.a to allow you to change between the embedded and the client/server version by just linking your application with the right library. See Section 8.4.5.1.
To get a libmysqld library you should configure MySQL with the --with-embedded-server option.
When you link your program with libmysqld, you must also include the system-specific pthread libraries and some libraries that the MySQL server uses. You can get the full list of libraries by executing mysql_config --libmysqld-libs.
The correct flags for compiling and linking a threaded program must be used, even if you do not directly call any thread functions in your code.
The embedded server has the following limitations:
No support for ISAM tables. (This is mainly done to make the library smaller.)
No UDF functions.
No stack trace on core dump.
No internal RAID support. (This is not normally needed, as most OSes nowadays have support for big files.)
You can set this up as a server or a master (no replication).
You can’t connect to the embedded server from an outside process with sockets or TCP/IP.
Some of these limitations can be changed by editing the mysql_embed.h
include file and recompiling MySQL.
The following is the recommended way to use option files to make it easy to switch between a client/server application and one where MySQL is embedded. See Section 4.1.2.
Put common options in the [server] section. These will be read by both MySQL versions.
Put client/server-specific options in the [mysqld] section.
Put embedded MySQL-specific options in the [embedded] section.
Put application-specific options in the [ApplicationName_SERVER] section.
Currently we only provide a static version of the mysqld library. In the future we will also provide a shared library for this.
We are going to provide options to leave out some parts of MySQL to make the library smaller.
There is still a lot of speed optimisation to do.
Errors are written to stderr. We will add an option to specify a filename for these.
We have to change InnoDB to not be so verbose when used in the embedded version.
This example program and makefile should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes will be needed. This example is designed to give enough details to understand the problem, without the clutter that is a necessary part of a real application.
To try out the example, create a test_libmysqld
directory
at the same level as the mysql-4.0 source directory. Save
the test_libmysqld.c
source and the GNUmakefile
in the
directory, and run GNU make
from inside the test_libmysqld
directory.
test_libmysqld.c
/* * A simple example client, using the embedded MySQL server library */ #include <mysql.h> #include <stdarg.h> #include <stdio.h> #include <stdlib.h> MYSQL *db_connect(const char *dbname); void db_disconnect(MYSQL *db); void db_do_query(MYSQL *db, const char *query); const char *server_groups[] = { "test_libmysqld_SERVER", "embedded", "server", NULL }; int main(int argc, char **argv) { MYSQL *one, *two; /* mysql_server_init( ) must be called before any other mysql * functions. * * You can use mysql_server_init(0, NULL, NULL), and it will * initialise the server using groups = { * "server", "embedded", NULL * }. * * In your $HOME/.my.cnf file, you probably want to put: [test_libmysqld_SERVER] language = /path/to/source/of/mysql/sql/share/english * You could, of course, modify argc and argv before passing * them to this function. Or you could create new ones in any * way you like. But all of the arguments in argv (except for * argv[0], which is the program name) should be valid options * for the MySQL server. * * If you link this client against the normal mysqlclient * library, this function is just a stub that does nothing. */ mysql_server_init(argc, argv, (char **)server_groups); one = db_connect("test"); two = db_connect(NULL); db_do_query(one, "SHOW TABLE STATUS"); db_do_query(two, "SHOW DATABASES"); mysql_close(two); mysql_close(one); /* This must be called after all other mysql functions */ mysql_server_end( ); exit(EXIT_SUCCESS); } static void die(MYSQL *db, char *fmt, ...) { va_list ap; va_start(ap, fmt); vfprintf(stderr, fmt, ap); va_end(ap); (void)putc('\n', stderr); if (db) db_disconnect(db); exit(EXIT_FAILURE); } MYSQL * db_connect(const char *dbname) { MYSQL *db = mysql_init(NULL); if (!db) die(db, "mysql_init failed: no memory"); /* * Notice that the client and server use separate group names. * This is critical because the server will not accept the * client's options, and vice versa. */ mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test_libmysqld_CLIENT"); if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0)) die(db, "mysql_real_connect failed: %s", mysql_error(db)); return db; } void db_disconnect(MYSQL *db) { mysql_close(db); } void db_do_query(MYSQL *db, const char *query) { if (mysql_query(db, query) != 0) goto err; if (mysql_field_count(db) > 0) { MYSQL_RES *res; MYSQL_ROW row, end_row; int num_fields; if (!(res = mysql_store_result(db))) goto err; num_fields = mysql_num_fields(res); while ((row = mysql_fetch_row(res))) { (void)fputs(">> ", stdout); for (end_row = row + num_fields; row < end_row; ++row) (void)printf("%s\t", row ? (char*)*row : "NULL"); (void)fputc('\n', stdout); } (void)fputc('\n', stdout); } else (void)printf("Affected rows: %lld\n", mysql_affected_rows(db)); return; err: die(db, "db_do_query failed: %s [%s]", mysql_error(db), query); }
GNUmakefile
# This assumes the MySQL software is installed in /usr/local/mysql inc := /usr/local/mysql/include/mysql lib := /usr/local/mysql/lib # If you have not installed the MySQL software yet, try this instead #inc := $(HOME)/mysql-4.0/include #lib := $(HOME)/mysql-4.0/libmysqld CC := gcc CPPFLAGS := -I$(inc) -D_THREAD_SAFE -D_REENTRANT CFLAGS := -g -W -Wall LDFLAGS := -static # You can change -lmysqld to -lmysqlclient to use the # client/server library LDLIBS = -L$(lib) -lmysqld -lz -lm -lcrypt ifneq (,$(shell grep FreeBSD /COPYRIGHT 2>/dev/null)) # FreeBSD LDFLAGS += -pthread else # Assume Linux LDLIBS += -lpthread endif
# This works for simple one-file test programs sources := $(wildcard *.c) objects := $(patsubst %c,%o,$(sources)) targets := $(basename $(sources)) all: $(targets) clean: rm -f $(targets) $(objects) *.core
The MySQL source code is covered by the GNU GPL license (see http://www.mysql.com/doc/G/P/GPL-license.html). One result of this is that any program which includes, by linking with libmysqld, the MySQL source code must be released as free software (under a license compatible with the GPL).
We encourage everyone to promote free software by releasing code under the GPL or a compatible license. For those who are not able to do this, another option is to purchase a commercial licence for the MySQL code from MySQL AB. For details, please see Section 1.4.3.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.