25.2.2. C API Function Overview
The functions available in the C API are summarized here and
described in greater detail in a later section. See
Section 25.2.3, “C API Function Descriptions”.
||Returns the number of rows changed/deleted/inserted by the last
||Toggles autocommit mode on/off.
||Changes user and database on an open connection.
||Returns the name of the default character set for the connection.
||Closes a server connection.
||Commits the transaction.
||Connects to a MySQL server. This function is deprecated; use
||Creates a database. This function is deprecated; use the SQL statement
CREATE DATABASE instead.
||Seeks to an arbitrary row number in a query result set.
DBUG_PUSH with the given string.
||Drops a database. This function is deprecated; use the SQL statement
DROP DATABASE instead.
||Makes the server write debug information to the log.
||Determines whether the last row of a result set has been read. This
function is deprecated;
mysql_error() may be used instead.
||Returns the error number for the most recently invoked MySQL function.
||Returns the error message for the most recently invoked MySQL function.
||Escapes special characters in a string for use in an SQL statement.
||Returns the type of the next table field.
||Returns the type of a table field, given a field number.
||Returns an array of all field structures.
||Returns the lengths of all columns in the current row.
||Fetches the next row from the result set.
||Puts the column cursor on a specified column.
||Returns the number of result columns for the most recent statement.
||Returns the position of the field cursor used for the last
||Frees memory used by a result set.
||Returns client version information as a string.
||Returns client version information as an integer.
||Returns a string describing the connection.
||Returns version number of server as an integer.
||Returns the protocol version used by the connection.
||Returns the server version number.
||Returns information about the most recently executed query.
||Gets or initializes a
||Returns the ID generated for an
by the previous query.
||Kills a given thread.
||Finalize MySQL C API library.
||Initialize MySQL C API library.
||Returns database names matching a simple regular expression.
||Returns field names matching a simple regular expression.
||Returns a list of the current server threads.
||Returns table names matching a simple regular expression.
||Checks whether any more results exist.
||Returns/initiates the next result in multiple-statement executions.
||Returns the number of columns in a result set.
||Returns the number of rows in a result set.
||Sets connect options for
||Checks whether the connection to the server is working, reconnecting as
||Executes an SQL query specified as a null-terminated string.
||Connects to a MySQL server.
||Escapes special characters in a string for use in an SQL statement,
taking into account the current character set of the
||Executes an SQL query specified as a counted string.
||Flush or reset tables and caches.
||Tells the server to reload the grant tables.
||Rolls back the transaction.
||Seeks to a row offset in a result set, using value returned from
||Returns the row cursor position.
||Selects a database.
||Finalize embedded server library.
||Initialize embedded server library.
||Sets an option for the connection (like
||Returns the SQLSTATE error code for the last error.
||Shuts down the database server.
||Returns the server status as a string.
||Retrieves a complete result set to the client.
||Returns the current thread ID.
||Returns 1 if the clients are compiled as thread-safe.
||Initiates a row-by-row result set retrieval.
||Returns the warning count for the previous SQL statement.
Application programs should use this general outline for
interacting with MySQL:
Initialize the MySQL library by calling
mysql_library_init(). The library can be
mysqlclient C client library
mysqld embedded server library,
depending on whether the application was linked with the
Initialize a connection handler by calling
mysql_init() and connect to the server by
Issue SQL statements and process their results. (The
following discussion provides more information about how to
Close the connection to the MySQL server by calling
End use of the MySQL library by calling
The purpose of calling
mysql_library_end() is to provide proper
initialization and finalization of the MySQL library. For
applications that are linked with the client library, they
provide improved memory management. If you don't call
mysql_library_end(), a block of memory
remains allocated. (This does not increase the amount of memory
used by the application, but some memory leak detectors will
complain about it.) For applications that are linked with the
embedded server, these calls start and stop the server.
mysql_library_end() are actually
#define symbols that make them equivalent to
mysql_server_end(), but the names more
clearly indicate that they should be called when beginning and
ending use of a MySQL library no matter whether the application
mysqld library. For older versions of MySQL,
you can call
If you like, the call to
may be omitted, because
invoke it automatically as necessary.
To connect to the server, call
to initialize 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 in versions of the API older than 5.0.3, or
0 in newer versions. A value of
1 for this flag indicates that if a statement
cannot be performed because of a lost connection, to try
reconnecting to the server before giving up. As of MySQL 5.0.13,
you can use the
MYSQL_OPT_RECONNECT option to
mysql_options() to control reconnection
behavior. When you are done with the connection, call
mysql_close() to terminate it.
While a connection is active, the client may send SQL statements
to the server using
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
For each non-
SELECT query (for example,
DELETE), you can find out how many rows were
changed (affected) by calling
SELECT queries, you retrieve the selected
rows as a result set. (Note that some statements are
SELECT-like in that they return rows. These
EXPLAIN. They should be treated the same
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 initializes
the retrieval, but does not actually get any rows from the
In both cases, you access rows by calling
mysql_fetch_row() accesses rows that have
previously been fetched from the server. With
mysql_fetch_row() actually retrieves the row
from the server. Information about the size of the data in each
row is available by calling
After you are done with a result set, call
mysql_free_result() to free the memory used
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, you can move back and forth
in the result set using
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
On the other hand, the memory requirements for
mysql_store_result() may be very high for
large result sets and you are more likely to encounter
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
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 statements (retrieving rows only as necessary) without
knowing whether the statement is a
You can do this by calling
mysql_store_result() after each
mysql_real_query()). If the result set call
succeeds, the statement 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
statement returned no data (indicating that it was an
DELETE, and so forth), and was not expected
to return rows. If
non-zero, the statement should have returned rows, but didn't.
This indicates that the statement was a
SELECT that failed. See the description for
mysql_field_count() for an example of how
this can be done.
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, and so forth). 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
For detecting and reporting errors, MySQL provides access to
error information by means of the
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.