MySQL APIs
**********
of the other
APIs.
MySQL C API
===========
The C API code is distributed with MySQL. It is included in the
`mysqlclient' library and allows C programs to access a database.
directory in the MySQL source distribution.
Most of the other client APIs (all except Connector/J) 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 *Note Client-Side Scripts::,
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. *Note Server parameters::.
The 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 *Note Threaded clients::. For
creating a stand-alone application which includes the "server" and
"client" in the same program (and does not communicate with an external
MySQL server), see *Note libmysqld::.
C API Datatypes
---------------
`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()'.
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 members listed here:
`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:
`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' `CHAR' field
`FIELD_TYPE_VAR_STRING' `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.
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:
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.
C API Function Overview
-----------------------
The functions available in the C API are summarised here and described
in greater detail in a later section. *Note C API functions::.
`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 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 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_server_version()* Returns version number of server as an integer
(new in 4.1).
*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 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_sqlstate()* Returns the SQLSTATE error code for the last
error.
*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.
*mysql_commit()* Commits the transaction (new in 4.1).
*mysql_rollback()* Rolls back the transaction (new in 4.1).
*mysql_autocommit()* Toggles autocommit mode on/off (new in 4.1).
*mysql_more_results()* Checks whether any more results exist (new in
4.1).
*mysql_next_result()* Returns/Initiates the next result in
multi-query executions (new in 4.1).
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.
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()'.
be
treated the same way as `SELECT' statements.)
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,
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
queries (retrieving rows only as necessary) without knowing whether or
not 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.
[Назад] [Содержание] [Вперед]
| Главная |