The functions available in the C API are summarized here and described in greater detail in a later section. See Section 17.6.3, “C API Function Descriptions”.
Function | Description |
my_init() |
Initialize global variables, and thread handler in thread-safe programs |
mysql_affected_rows() |
Returns the number of rows changed/deleted/inserted by the last
UPDATE ,
DELETE , or
INSERT query |
mysql_autocommit() |
Toggles autocommit mode on/off |
mysql_change_user() |
Changes user and database on an open connection |
mysql_character_set_name() |
Return default character set name for current connection |
mysql_close() |
Closes a server connection |
mysql_commit() |
Commits the transaction |
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 statement
CREATE DATABASE instead) |
mysql_data_seek() |
Seeks to an arbitrary row number 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 statement
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_count() |
Returns the number of result columns for the most recent statement |
mysql_field_seek() |
Puts the column cursor on a specified column |
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 as a string |
mysql_get_client_version() |
Returns client version information as an integer |
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_get_server_version() |
Returns version number of server as an integer |
mysql_hex_string() |
Encode string in hexadecimal format |
mysql_info() |
Returns information about the most recently executed query |
mysql_init() |
Gets or initializes 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_library_end() |
Finalize the MySQL C API library |
mysql_library_init() |
Initialize the MySQL C API library |
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_more_results() |
Checks whether any more results exist |
mysql_next_result() |
Returns/initiates the next result in multiple-result executions |
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_real_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 character set of the connection |
mysql_real_query() |
Executes an SQL query specified as a counted string |
mysql_refresh() |
Flush or reset tables and caches |
mysql_reload() |
Tells the server to reload the grant tables |
mysql_rollback() |
Rolls back the transaction |
mysql_row_seek() |
Seeks to a row offset in a result set, using value returned from
mysql_row_tell()
|
mysql_row_tell() |
Returns the row cursor position |
mysql_select_db() |
Selects a database |
mysql_server_end() |
Finalize the MySQL C API library |
mysql_server_init() |
Initialize the MySQL C API library |
mysql_set_character_set() |
Set default character set for current connection |
mysql_set_local_infile_default() |
Set the LOAD DATA LOCAL
INFILE handler callbacks to their default values |
mysql_set_local_infile_handler() |
Install application-specific
LOAD DATA LOCAL
INFILE handler callbacks |
mysql_set_server_option() |
Sets an option for the connection (like
multi-statements ) |
mysql_sqlstate() |
Returns the SQLSTATE error code for the last error |
mysql_shutdown() |
Shuts down the database server |
mysql_ssl_set() |
Prepare to establish SSL connection to server |
mysql_stat() |
Returns the server status as a string |
mysql_store_result() |
Retrieves a complete result set to the client |
mysql_thread_end() |
Finalize thread handler |
mysql_thread_id() |
Returns the current thread ID |
mysql_thread_init() |
Initialize thread handler |
mysql_thread_safe() |
Returns 1 if the clients are compiled as thread-safe |
mysql_use_result() |
Initiates a row-by-row result set retrieval |
mysql_warning_count() |
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()
. This
function exists in both the mysqlclient
C
client library and the mysqld
embedded
server library, so it is used whether you build a regular
client program by linking with the
-libmysqlclient
flag, or an embedded server
application by linking with the -libmysqld
flag.
Initialize a connection handler by calling
mysql_init()
and connect to
the server by calling
mysql_real_connect()
.
Issue SQL statements and process their results. (The following discussion provides more information about how to do this.)
Close the connection to the MySQL server by calling
mysql_close()
.
End use of the MySQL library by calling
mysql_library_end()
.
The purpose of calling
mysql_library_init()
and
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_init()
and
mysql_library_end()
are available
as of MySQL 4.1.10. For older versions of MySQL, you can call
mysql_server_init()
and
mysql_server_end()
instead.
In a nonmulti-threaded environment, the call to
mysql_library_init()
may be
omitted, because mysql_init()
will
invoke it automatically as necessary. However,
mysql_library_init()
is not
thread-safe in a multi-threaded environment, and thus neither is
mysql_init()
, which calls
mysql_library_init()
. You must
either call mysql_library_init()
prior to spawning any threads, or else use a mutex to protect the
call, whether you invoke
mysql_library_init()
or indirectly
via mysql_init()
. This should be
done prior to any other client library call.
To connect to the server, call
mysql_init()
to initialize a
connection handler, then call
mysql_real_connect()
with that
handler (along with other information such as the host name, user
name, and password). Upon connection,
mysql_real_connect()
sets the
reconnect
flag (part of the
MYSQL
structure) to a value of
1
. 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. 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_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
initializes 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 previously 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, you 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 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
statements (retrieving rows only as necessary) without knowing
whether the statement 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 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
INSERT
,
UPDATE
,
DELETE
, and so forth), and was not
expected to return rows. If
mysql_field_count()
is nonzero,
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.
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, 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
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.
User Comments
Add your own comment.