At Alden Hosting we eat and breathe Java! We are the industry leader in providing
affordable, quality and efficient Java web hosting in the shared hosting marketplace.
All our sites run on our Java hosing platform configured for
optimum performance using Java 1.6, Tomcat 6, MySQL 5, Apache 2.2 and web
application frameworks such as Struts, Hibernate, Cocoon, Ant, etc.

We offer only one type of Java hosting - Private Tomcat. Hosting accounts on the Private
Tomcat environment get their very own Tomcat server. You can start and re-start
your entire Tomcat server yourself.

This chapter describes the APIs available for MySQL, where to get
them, and how to use them. The C API is the most extensively
covered, because it was developed by the MySQL team, and is the
basis for most of the other APIs. This chapter also covers some
programs that are useful for application developers.

22.1. libmysqld, the Embedded MySQL Server Library

The embedded MySQL server library is NOT
part of MySQL 5.0. It is part of previous editions and will be
included in future versions, starting with MySQL 5.1. You can find
appropriate documentation in the corresponding manuals for these
versions. In this manual, only an overview of the embedded library
is provided.

The embedded MySQL server library makes it possible to run a
full-featured MySQL server inside a client application. The main
benefits are increased speed and more simple management for
embedded applications.

The embedded server library is based on the client/server version
of MySQL, which is written in C/C++. Consequently, the embedded
server also is written in C/C++. There is no embedded server
available in other languages.

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_library_init()

Should be called before any other MySQL function is called, preferably
early in the main() function.

mysql_library_end()

Should be called before your program exits.

mysql_thread_init()

Should be called in each thread you create that accesses MySQL.

mysql_thread_end()

Should be called before calling pthread_exit()

Then you must link your code with libmysqld.a
instead of libmysqlclient.a. To ensure binary
compatibility between your application and the server library, be
sure to compile your application against headers for the same
series of MySQL that was used to compile the server library. For
example, if libmysqld was compiled against
MySQL 4.1 headers, do not compile your application against MySQL
5.1 headers, or vice versa.

The
mysql_library_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 22.2.3.40, “mysql_library_init()”.

One difference between the embedded server and the standalone
server is that for the embedded server, authentication for
connections is disabled by default. To use authentication for the
embedded server, specify the
--with-embedded-privilege-control option when you
invoke configure to configure your MySQL
distribution.

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 Connector/J and
Connector/NET) 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 Chapter 8, Client and Utility Programs, for a
list of these variables.

The client has a maximum communication buffer size. The size of the
buffer that is allocated initially (16KB) is automatically increased
up to the maximum size (the maximum is 16MB). 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 statements 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 16MB of data, you must have a
communication buffer limit of at least 16MB (in both server and
client). The client's default maximum is 16MB, but the default
maximum in the server is 1MB. You can increase this by changing the
value of the max_allowed_packet parameter when
the server is started. See Section 7.5.2, “Tuning 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.

22.2.1. C API Data types

MYSQL

This structure represents a handle to one database connection.
It is used for almost all MySQL functions. You should not try
to make a copy of a MYSQL structure. There
is no guarantee that such a copy will be usable.

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 here. 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 does not work. To print such a
value, convert it to unsigned long and use
a %lu print format. Example:

The name of the field, as a null-terminated string. If the
field was given an alias with an AS clause,
the value of name is the alias.

char * org_name

The name of the field, as a null-terminated string. Aliases
are ignored.

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. If the
column is selected from a view, table names
the view. If the table or view was given an alias with an
AS clause, the value of
table is the alias.

char * org_table

The name of the table, as a null-terminated string. Aliases
are ignored. If the column is selected from a view,
org_table names the underlying table.

char * db

The name of the database that the field comes from, as a
null-terminated string. If the field is a calculated field,
db is an empty string.

char * catalog

The catalog name. This value is always
"def".

char * def

The default value of this field, as a null-terminated string.
This is set only if you use
mysql_list_fields().

unsigned long length

The width of the field. This corresponds to the display
length, in bytes.

unsigned long max_length

The maximum width of the field for the result set (the length
in bytes 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.

The value of max_length is the length of
the string representation of the values in the result set. For
example, if you retrieve a FLOAT column and
the “widest” value is -12.345,
max_length is 7 (the length of
'-12.345').

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
MYSQL_TYPE_BLOB,
MYSQL_TYPE_ENUM,
MYSQL_TYPE_SET, or
MYSQL_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.

unsigned int charsetnr

The character set number for the field.

enum enum_field_types type

The type of the field. The type value may
be one of the MYSQL_TYPE_ symbols shown in
the following table.

Type Value

Type Description

MYSQL_TYPE_TINY

TINYINT field

MYSQL_TYPE_SHORT

SMALLINT field

MYSQL_TYPE_LONG

INTEGER field

MYSQL_TYPE_INT24

MEDIUMINT field

MYSQL_TYPE_LONGLONG

BIGINT field

MYSQL_TYPE_DECIMAL

DECIMAL or NUMERIC field

MYSQL_TYPE_NEWDECIMAL

Precision math DECIMAL or NUMERIC
field (MySQL 5.0.3 and up)

MYSQL_TYPE_FLOAT

FLOAT field

MYSQL_TYPE_DOUBLE

DOUBLE or REAL field

MYSQL_TYPE_BIT

BIT field (MySQL 5.0.3 and up)

MYSQL_TYPE_TIMESTAMP

TIMESTAMP field

MYSQL_TYPE_DATE

DATE field

MYSQL_TYPE_TIME

TIME field

MYSQL_TYPE_DATETIME

DATETIME field

MYSQL_TYPE_YEAR

YEAR field

MYSQL_TYPE_STRING

CHAR or BINARY field

MYSQL_TYPE_VAR_STRING

VARCHAR or VARBINARY field

MYSQL_TYPE_BLOB

BLOB or TEXT field (use
max_length to determine the maximum
length)

MYSQL_TYPE_SET

SET field

MYSQL_TYPE_ENUM

ENUM field

MYSQL_TYPE_GEOMETRY

Spatial field

MYSQL_TYPE_NULL

NULL-type field

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 evaluates to TRUE if the field is numeric:

if (IS_NUM(field->type))
printf("Field is numeric\n");

To distinguish between binary and non-binary data for string
data types, check whether the charsetnr
value is 63. If so, the character set is
binary, which indicates binary rather than
non-binary data. This is how to distinguish between
BINARY and CHAR,
VARBINARY and VARCHAR,
and BLOB and TEXT.

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
5.0.3. For older versions of MySQL, you can call
mysql_server_init() and
mysql_server_end() instead.

In a non-multi-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 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_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
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.

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.

In the descriptions here, 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:

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().

22.2.3.1. mysql_affected_rows()

my_ulonglong mysql_affected_rows(MYSQL
*mysql)

Description

After executing a statement with
mysql_query() or
mysql_real_query(), returns the number of
rows changed (for) UPDATE), deleted (for
DELETE, or inserted (for
INSERT. For SELECT
statements, mysql_affected_rows() works like
mysql_num_rows().

Return Values

An integer greater than zero indicates the number of rows
affected or retrieved. Zero indicates that no records were
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(). Because
mysql_affected_rows() returns an unsigned
value, you can check for -1 by comparing the return value to
(my_ulonglong)-1 (or to
(my_ulonglong)~0, which is equivalent).

For UPDATE statements, if you specify the
CLIENT_FOUND_ROWS flag when connecting to
mysqld,
mysql_affected_rows() returns the number of
rows matched by the WHERE clause. Otherwise,
the default behavior is to return the number of rows actually
changed.

Note that when you use a REPLACE command,
mysql_affected_rows() returns 2 if the new
row replaced an old row, because in this case, one row was
inserted after the duplicate was deleted.

If you use INSERT ... ON DUPLICATE KEY UPDATE
to insert a row, mysql_affected_rows()
returns 1 if the row is inserted as a new row and 2 if an
existing row is updated.

22.2.3.2. mysql_autocommit()

my_bool mysql_autocommit(MYSQL *mysql, my_bool
mode)

Description

Sets autocommit mode on if mode is 1, off if
mode is 0.

Return Values

Zero if successful. Non-zero if an error occurred.

Errors

None.

22.2.3.3. mysql_change_user()

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.

mysql_change_user() fails if the connected
user cannot be authenticated or 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.

This command resets the state as if one had done a new connect.
(See Section 22.2.13, “Controlling Automatic Reconnect Behavior”.) It always performs a
ROLLBACK of any active transactions, closes
and drops all temporary tables, and unlocks all locked tables.
Session system variables are reset to the values of the
corresponding global system variables. Prepared statements are
released and HANDLER variables are closed.
Locks acquired with GET_LOCK() are released.
These effects occur even if the user didn't change.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

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

22.2.3.5. mysql_close()

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().

Return Values

None.

Errors

None.

22.2.3.6. mysql_commit()

my_bool mysql_commit(MYSQL *mysql)

Description

Commits the current transaction.

As of MySQL 5.0.3, the action of this function is subject to the
value of the completion_type system variable.
In particular, if the value of
completion_type is 2, the server performs a
release after terminating a transaction and closes the client
connection. The client program should call
mysql_close() to close the connection from
the client side.

Return Values

Zero if successful. Non-zero if an error occurred.

Errors

None.

22.2.3.7. 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.)

Return Values

Same as for mysql_real_connect().

Errors

Same as for mysql_real_connect().

22.2.3.8. mysql_create_db()

int mysql_create_db(MYSQL *mysql, const char
*db)

Description

Creates the database named by the db
parameter.

This function is deprecated. It is preferable to use
mysql_query() to issue an SQL CREATE
DATABASE statement instead.

Return Values

Zero if the database was created successfully. Non-zero if an
error occurred.

22.2.3.9. mysql_data_seek()

Seeks to an arbitrary row in a query result set. The
offset value is a row number and should be in
the range from 0 to
mysql_num_rows(result)-1.

This function requires that the result set structure contains
the entire result of the query, so
mysql_data_seek() may be used only in
conjunction with mysql_store_result(), not
with mysql_use_result().

Return Values

None.

Errors

None.

22.2.3.10. mysql_debug()

void mysql_debug(const char *debug)

Description

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
MySQL
Internals: Porting.

Return Values

None.

Errors

None.

Example

The call shown here 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");

22.2.3.11. mysql_drop_db()

int mysql_drop_db(MYSQL *mysql, const char
*db)

Description

Drops the database named by the db parameter.

This function is deprecated. It is preferable to use
mysql_query() to issue an SQL DROP
DATABASE statement instead.

Return Values

Zero if the database was dropped successfully. Non-zero if an
error occurred.

22.2.3.12. mysql_dump_debug_info()

Instructs the server to write some debug information to the log.
For this to work, the connected user must have the
SUPER privilege.

Return Values

Zero if the command was successful. Non-zero if an error
occurred.

Errors

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.

22.2.3.13. mysql_eof()

my_bool mysql_eof(MYSQL_RES *result)

Description

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() always returns 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 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.)

Return Values

Zero if no error occurred. Non-zero if the end of the result set
has been reached.

22.2.3.14. mysql_errno()

unsigned int mysql_errno(MYSQL *mysql)

Description

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.
Errors also are listed at Appendix B, Errors, Error Codes, and Common Problems.

Note that some functions like
mysql_fetch_row() don't set
mysql_errno() if they succeed.

A rule of thumb is that all functions that have to ask the
server for information reset mysql_errno() if
they succeed.

MySQL-specific error numbers returned by
mysql_errno() differ from SQLSTATE values
returned by mysql_sqlstate(). For example,
the mysql client program displays errors
using the following format, where 1146 is the
mysql_errno() value and
'42S02' is the corresponding
mysql_sqlstate() value:

An error code value for the last
mysql_xxx() call,
if it failed. zero means no error occurred.

Errors

None.

22.2.3.15. mysql_error()

const char *mysql_error(MYSQL *mysql)

Description

For the connection specified by mysql,
mysql_error() returns a null-terminated
string containing the error message for the most recently
invoked API function that failed. If a function didn't fail, the
return value of mysql_error() may be the
previous error or an empty string to indicate no error.

A rule of thumb is that all functions that have to ask the
server for information reset mysql_error() if
they succeed.

For functions that reset mysql_error(), the
following two tests are equivalent:

A null-terminated character string that describes the error. An
empty string if no error occurred.

Errors

None.

22.2.3.16. mysql_escape_string()

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 character
set.

22.2.3.17. mysql_fetch_field()

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES
*result)

Description

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 (8KB) if you call
mysql_fetch_field() to ask for the length of
a BLOB field. (The 8KB 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.

Return Values

The MYSQL_FIELD structure for the current
column. NULL if no columns are left.

22.2.3.18. mysql_fetch_field_direct()

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.

22.2.3.20. mysql_fetch_lengths()

unsigned long *mysql_fetch_lengths(MYSQL_RES
*result)

Description

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 optimization, 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().

Return Values

An array of unsigned long integers representing the size of each
column (not including any terminating null characters).
NULL if an error occurred.

Errors

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.

22.2.3.21. mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

Description

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.

MySQL-specific error numbers returned by
mysql_errno() differ from SQLSTATE values
returned by mysql_sqlstate(). For example,
the mysql client program displays errors
using the following format, where 1146 is the
mysql_errno() value and
'42S02' is the corresponding
mysql_sqlstate() value:

22.2.3.22. mysql_field_count()

unsigned int mysql_field_count(MYSQL *mysql)

Description

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 example shown here illustrates how this may be
done.

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.

22.2.3.23. mysql_field_seek()

Sets the field cursor to the given offset. The next call to
mysql_fetch_field() retrieves the field
definition of the column associated with that offset.

To seek to the beginning of a row, pass an
offset value of zero.

Return Values

The previous value of the field cursor.

Errors

None.

22.2.3.24. mysql_field_tell()

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES
*result)

Description

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().

Return Values

The current offset of the field cursor.

Errors

None.

22.2.3.25. mysql_free_result()

void mysql_free_result(MYSQL_RES *result)

Description

Frees the memory allocated for a result set by
mysql_store_result(),
mysql_use_result(),
mysql_list_dbs(), and so forth. When you are
done with a result set, you must free the memory it uses by
calling mysql_free_result().

Do not attempt to access a result set after freeing it.

Return Values

None.

Errors

None.

22.2.3.26. mysql_get_character_set_info()

void mysql_get_character_set_info(MYSQL *mysql,
MY_CHARSET_INFO *cs)

Description

This function provides information about the default client
character set. The default character set may be changed with the
mysql_set_character_set() function.

22.2.3.28. mysql_get_client_version()

unsigned long mysql_get_client_version(void)

Description

Returns an integer that represents the client library version.
The value has the format XYYZZ where
X is the major version, YY
is the release level, and ZZ is the version
number within the release level. For example, a value of
40102 represents a client library version of
4.1.2.

Return Values

An integer that represents the MySQL client library version.

Errors

None.

22.2.3.29. mysql_get_host_info()

const char *mysql_get_host_info(MYSQL *mysql)

Description

Returns a string describing the type of connection in use,
including the server hostname.

Return Values

A character string representing the server hostname and the
connection type.

Errors

None.

22.2.3.30. mysql_get_proto_info()

unsigned int mysql_get_proto_info(MYSQL
*mysql)

Description

Returns the protocol version used by current connection.

Return Values

An unsigned integer representing the protocol version used by
the current connection.

Errors

None.

22.2.3.31. mysql_get_server_info()

const char *mysql_get_server_info(MYSQL
*mysql)

Description

Returns a string that represents the server version number.

Return Values

A character string that represents the server version number.

Errors

None.

22.2.3.32. mysql_get_server_version()

unsigned long mysql_get_server_version(MYSQL
*mysql)

Description

Returns the version number of the server as an integer.

Return Values

A number that represents the MySQL server version in this
format:

major_version*10000 + minor_version *100 + sub_version

For example, 5.0.12 is returned as 50012.

This function is useful in client programs for quickly
determining whether some version-specific server capability
exists.

Errors

None.

22.2.3.33. mysql_get_ssl_cipher()

const char *mysql_get_ssl_cipher(MYSQL
*mysql)

Description

mysql_get_ssl_cipher() returns the SSL cipher
used for the given connection to the server.
mysql is the connection handler returned from
mysql_init().

This function was added in MySQL 5.1.9.

Return Values

A string naming the SSL cipher used for the connection, or
NULL if no cipher is being used.

The string in from is encoded to hexadecimal
format, with each character encoded as two hexadecimal digits.
The result is placed in to and a terminating
null byte is appended.

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. When
mysql_hex_string() returns, the contents of
to is a null-terminated string. The return
value is the length of the encoded string, not including the
terminating null character.

The return value can be placed into an SQL statement using
either 0xvalue or
X'value' format.
However, the return value does not include the
0x or X'...'. The caller
must supply whichever of those is desired.

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.

Return Values

The length of the value placed into to, not
including the terminating null character.

Errors

None.

22.2.3.35. mysql_info()

const char *mysql_info(MYSQL *mysql)

Description

Retrieves a string providing information about the most recently
executed statement, but only for the statements listed here. For
other statements, mysql_info() returns
NULL. The format of the string varies
depending on the type of statement, as described here. The
numbers are illustrative only; the string contains values
appropriate for the statement.

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 INSERT ...
VALUES only for the multiple-row form of the statement
(that is, only if multiple value lists are specified).

Return Values

A character string representing additional information about the
most recently executed statement. NULL if no
information is available for the statement.

Errors

None.

22.2.3.36. mysql_init()

MYSQL *mysql_init(MYSQL *mysql)

Description

Allocates or initializes a MYSQL object
suitable for mysql_real_connect(). If
mysql is a NULL pointer,
the function allocates, initializes, and returns a new object.
Otherwise, the object is initialized and the address of the
object is returned. If mysql_init() allocates
a new object, it is freed when mysql_close()
is called to close the connection.

Return Values

An initialized MYSQL* handle.
NULL if there was insufficient memory to
allocate a new object.

Errors

In case of insufficient memory, NULL is
returned.

22.2.3.37. mysql_insert_id()

my_ulonglong mysql_insert_id(MYSQL *mysql)

Description

Returns the value generated for an
AUTO_INCREMENT column by the previous
INSERT or UPDATE
statement. Use this function after you have performed an
INSERT statement into a table that contains
an AUTO_INCREMENT field.

More precisely, mysql_insert_id() is updated
under these conditions:

INSERT statements that store a value into
an AUTO_INCREMENT column. This is true
whether the value is automatically generated by storing the
special values NULL or
0 into the column, or is an explicit
non-special value.

In the case of a multiple-row INSERT
statement, mysql_insert_id() returns the
first automatically
generated AUTO_INCREMENT value; if no
such value is generated, it returns the last
last explicit value
inserted into the AUTO_INCREMENT column.

INSERT statements that generate an
AUTO_INCREMENT value by inserting
LAST_INSERT_ID(expr)
into any column.

INSERT statements that generate an
AUTO_INCREMENT value by updating any
column to
LAST_INSERT_ID(expr).

The value of mysql_insert_id() is not
affected by statements such as SELECT
that return a result set.

If the previous statement returned an error, the value of
mysql_insert_id() is undefined.

Note that mysql_insert_id() returns
0 if the previous statement does not use an
AUTO_INCREMENT value. If you need to save the
value for later, be sure to call
mysql_insert_id() immediately after the
statement that generates the value.

The value of mysql_insert_id() is affected
only by statements issued within the current client connection.
It is not affected by statements issued by other clients.

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 statements because the value of that
function is maintained in the server. Another difference is that
LAST_INSERT_ID() is not updated if you set an
AUTO_INCREMENT column to a specific
non-special value.

The reason for the difference between
LAST_INSERT_ID() and
mysql_insert_id() is that
LAST_INSERT_ID() is made easy to use in
scripts while mysql_insert_id() tries to
provide a little more exact information of what happens to the
AUTO_INCREMENT column.

Return Values

Described in the preceding discussion.

Errors

None.

22.2.3.38. mysql_kill()

int mysql_kill(MYSQL *mysql, unsigned long
pid)

Description

Asks the server to kill the thread specified by
pid.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

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.

22.2.3.39. mysql_library_end()

void mysql_library_end(void)

Description

This function finalizes the MySQL library. You should call it
when you are done using the library (for example, after
disconnecting from the server). The action taken by the call
depends on whether your application is linked to the MySQL
client library or the MySQL embedded server library. For a
client program linked against the
libmysqlclient library by using the
-lmysqlclient flag,
mysql_library_end() performs some memory
management to clean up. For an embedded server application
linked against the libmysqld library by using
the -lmysqld flag,
mysql_library_end() shuts down the embedded
server and then cleans up.

22.2.3.40. mysql_library_init()

int mysql_library_init(int argc, char **argv, char
**groups)

Description

This function should be called to initialize the MySQL library
before you call any other MySQL function. If your application
uses the embedded server, this call starts the server and
initializes any subsystems (mysys,
InnoDB, and so forth) that the server uses.

In a non-multi-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.

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.
mysql_library_init() makes a copy of the
arguments so it's safe to destroy argv or
groups after the call.

If you want to connect to an external server without starting
the embedded server, you have to specify a negative value for
argc.

The groups argument should be an array of
strings that indicate the groups in option files from which
options should be read. See Section 4.3.2, “Using Option Files”. The
final entry in the array should be NULL. For
convenience, if the groups argument itself is
NULL, the [server] and
[embedded] groups are used by default.

22.2.3.41. mysql_list_dbs()

MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char
*wild)

Description

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().

Return Values

A MYSQL_RES result set for success.
NULL if an error occurred.

Errors

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.

22.2.3.42. mysql_list_fields()

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().

Return Values

A MYSQL_RES result set for success.
NULL if an error occurred.

Errors

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.

22.2.3.43. mysql_list_processes()

MYSQL_RES *mysql_list_processes(MYSQL *mysql)

Description

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().

Return Values

A MYSQL_RES result set for success.
NULL if an error occurred.

Errors

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.

22.2.3.44. mysql_list_tables()

MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char
*wild)

Description

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().

Return Values

A MYSQL_RES result set for success.
NULL if an error occurred.

Errors

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.

22.2.3.45. mysql_more_results()

my_bool mysql_more_results(MYSQL *mysql)

Description

This function is used when you execute multiple statements
specified as a single statement string, or when you execute
CALL statements, which can return multiple
result sets.

mysql_more_results() true if more results
exist from the currently executed statement, in which case the
application must call mysql_next_result() to
fetch the results.

Return Values

TRUE (1) if more results exist.
FALSE (0) if no more results exist.

In most cases, you can call
mysql_next_result() instead to test whether
more results exist and initiate retrieval if so.

22.2.3.46. mysql_next_result()

This function is used when you execute multiple statements
specified as a single statement string, or when you execute
CALL statements, which can return multiple
result sets.

If more statement results exist,
mysql_next_result() reads the next statement
result and returns the status back to the application.

Before calling mysql_next_result(), you must
call mysql_free_result() for the preceding
statement if it is a query that returned a result set.

After calling mysql_next_result() the state
of the connection is as if you had called
mysql_real_query() or
mysql_query() for the next statement. This
means that you can call mysql_store_result(),
mysql_warning_count(),
mysql_affected_rows(), and so forth.

If mysql_next_result() returns an error, no
other statements are executed and there are no more results to
fetch.

If your program executes stored procedures with the
CALL SQL statement, you
must set the
CLIENT_MULTI_RESULTS flag explicitly, or
implicitly by setting CLIENT_MULTI_STATEMENTS
when you call mysql_real_connect(). This is
because each CALL returns a result to
indicate the call status, in addition to any results sets that
might be returned by statements executed within the procedure.
In addition, because CALL can return multiple
results, you should process those results using a loop that
calls mysql_next_result() to determine
whether there are more results.

22.2.3.47. mysql_num_fields()

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 the query was a
SELECT (or SELECT-like)
statement. The example shown here illustrates how this may be
done.

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
whether mysql_field_count(&mysql) returns
0. This happens only if something went wrong.

22.2.3.48. mysql_num_rows()

my_ulonglong mysql_num_rows(MYSQL_RES
*result)

Description

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() does not return the correct
value until all the rows in the result set have been retrieved.

mysql_num_rows() is intended for use with
statements that return a result set, such as
SELECT. For statements such as
INSERT, UPDATE, or
DELETE, the number of affected rows can be
obtained with mysql_affected_rows().

22.2.3.49. mysql_options()

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.

The following list describes the possible options, their effect,
and how arg is used for each option. Several
of the options apply only when the application is linked against
the libmysqld embedded server library and are
unused for applications linked against the
libmysql client library. For option
descriptions that indicate arg is unused, its
value is irrelevant; it is conventional to pass 0.

MYSQL_INIT_COMMAND (argument type:
char *)

Statement to execute when connecting to the MySQL server.
Automatically re-executed if reconnection occurs.

MYSQL_OPT_COMPRESS (argument: not used)

Use the compressed client/server protocol.

MYSQL_OPT_CONNECT_TIMEOUT (argument type:
unsigned int *)

Connect timeout in seconds.

MYSQL_OPT_GUESS_CONNECTION (argument: not
used)

For an application linked against the
libmysqld embedded server library, this
allows the library to guess whether to use the embedded
server or a remote server. “Guess” means that
if the hostname is set and is not
localhost, it uses a remote server. This
behavior is the default.
MYSQL_OPT_USE_EMBEDDED_CONNECTION and
MYSQL_OPT_USE_REMOTE_CONNECTION can be
used to override it. This option is ignored for applications
linked against the libmysqlclient client
library.

If no pointer is given or if pointer points to an
unsigned int that has a non-zero value,
the LOAD LOCAL INFILE statement is
enabled.

MYSQL_OPT_NAMED_PIPE (argument: not used)

Use named pipes to connect to a MySQL server on Windows, if
the server allows named-pipe connections.

MYSQL_OPT_PROTOCOL (argument type:
unsigned int *)

Type of protocol to use. Should be one of the enum values of
mysql_protocol_type defined in
mysql.h.

MYSQL_OPT_READ_TIMEOUT (argument type:
unsigned int *)

Timeout for reads from server (works only for TCP/IP
connections, and only for Windows prior to MySQL 5.0.25).
You can this option so that a lost connection can be
detected earlier than the TCP/IP
Close_Wait_Timeout value of 10 minutes.

MYSQL_OPT_RECONNECT (argument type:
my_bool *)

Enable or disable automatic reconnection to the server if
the connection is found to have been lost. Reconnect has
been off by default since MySQL 5.0.3; this option is new in
5.0.13 and provides a way to set reconnection behavior
explicitly.

MYSQL_OPT_SET_CLIENT_IP (argument type:
char *)

For an application linked against linked against the
libmysqld embedded server library (when
libmysqld is compiled with authentication
support), this means that the user is considered to have
connected from the specified IP address (specified as a
string) for authentication purposes. This option is ignored
for applications linked against the
libmysqlclient client library.

MYSQL_OPT_SSL_VERIFY_SERVER_CERT
(argument type: my_bool *)

Enable or disable verification of the server's Common Name
value in its certificate against the hostname used when
connecting to the server. The connection is rejected if
there is a mismatch. This feature can be used to prevent
man-in-the-middle attacks. Verification is disabled by
default. Added in MySQL 5.0.23.

MYSQL_OPT_USE_EMBEDDED_CONNECTION
(argument: not used)

For an application linked against the
libmysqld embedded server library, this
forces the use of the embedded server for the connection.
This option is ignored for applications linked against the
libmysqlclient client library.

MYSQL_OPT_USE_REMOTE_CONNECTION
(argument: not used)

For an application linked against the
libmysqld embedded server library, this
forces the use of a remote server for the connection. This
option is ignored for applications linked against the
libmysqlclient client library.

MYSQL_OPT_USE_RESULT (argument: not used)

This option is unused.

MYSQL_OPT_WRITE_TIMEOUT (argument type:
unsigned int *)

Timeout for writes to server (works currently only on
Windows on TCP/IP connections).

MYSQL_READ_DEFAULT_FILE (argument type:
char *)

Read options from the named option file instead of from
my.cnf.

MYSQL_READ_DEFAULT_GROUP (argument type:
char *)

Read options from the named group from
my.cnf or the file specified with
MYSQL_READ_DEFAULT_FILE.

Whether to connect to a server that does not support the
password hashing used in MySQL 4.1.1 and later.

MYSQL_SET_CHARSET_DIR (argument type:
char *)

The pathname to the directory that contains character set
definition files.

MYSQL_SET_CHARSET_NAME (argument type:
char *)

The name of the character set to use as the default
character set.

MYSQL_SHARED_MEMORY_BASE_NAME (argument
type: char *)

The name of the shared-memory object for communication to
the server on Windows, if the server supports shared-memory
connection. Should have the same value as the
--shared-memory-base-name option used for
the mysqld server you want to connect to.

Note that the client group 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

Statement to execute when connecting to MySQL server. Automatically
re-executed if reconnection occurs.

This code requests that the client use the compressed
client/server protocol and read the additional options from the
odbc section in the
my.cnf file.

22.2.3.50. mysql_ping()

int mysql_ping(MYSQL *mysql)

Description

Checks whether the connection to the server is working. If the
connection has gone down, an attempt to reconnect is made unless
auto-reconnect is disabled.

This function can be used by clients that remain idle for a long
while, to check whether the server has closed the connection and
reconnect if necessary.

Return Values

Zero if the connection to the server is alive. Non-zero if an
error occurred. A non-zero return does not indicate whether the
MySQL server itself is down; the connection might be broken for
other reasons such as network problems.

Errors

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.

22.2.3.51. mysql_query()

int mysql_query(MYSQL *mysql, const char
*stmt_str)

Description

Executes the SQL statement pointed to by the null-terminated
string stmt_str. Normally, the string must
consist of a single SQL statement and you should not add a
terminating semicolon (‘;’) or
\g to the statement. If multiple-statement
execution has been enabled, the string can contain several
statements separated by semicolons. See
Section 22.2.9, “C API Handling of Multiple Statement Execution”.

mysql_query() cannot be used for statements
that contain binary data; you must use
mysql_real_query() instead. (Binary data may
contain the ‘\0’ character, which
mysql_query() interprets as the end of the
statement string.)

22.2.3.52. mysql_real_connect()

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 other API
functions that require a valid MYSQL
connection handle structure.

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 initialize the
MYSQL structure. You can change a lot of
connect options with the mysql_options()
call. See Section 22.2.3.49, “mysql_options()”.

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. For Windows, the client connects using a
shared-memory connection, if the server has shared-memory
connections enabled. Otherwise, TCP/IP is used. For Unix,
the client connects using a Unix socket file. For local
connections, you can also influence the type of connection
to use with the MYSQL_OPT_PROTOCOL or
MYSQL_OPT_NAMED_PIPE options to
mysql_options(). The type of connection
must be supported by the server. For a
host value of "." on
Windows, the client connects using a named pipe, if the
server has named-pipe connections enabled. If named-pipe
connections are not enabled, an error occurs.

The user parameter contains the user's
MySQL login ID. If user is
NULL or the empty string
"", the current user is assumed. Under
Unix, this is the current login name. Under Windows ODBC,
the current username must be specified explicitly. See the
MyODBC section of Chapter 23, Connectors.

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 are 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 sets the default database to this value.

If port is not 0, the value is 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 to
enable certain features:

Flag Name

Flag Description

CLIENT_COMPRESS

Use compression protocol.

CLIENT_FOUND_ROWS

Return the number of found (matched) rows, not the number of changed
rows.

Allow interactive_timeout seconds (instead of
wait_timeout seconds) of
inactivity before closing the connection. The
client's session wait_timeout
variable is set to the value of the session
interactive_timeout variable.

CLIENT_LOCAL_FILES

Enable LOAD DATA LOCAL handling.

CLIENT_MULTI_RESULTS

Tell the server that the client can handle multiple result sets from
multiple-statement executions or stored procedures.
This is automatically set if
CLIENT_MULTI_STATEMENTS is set.
See the note following this table for more
information about this flag.

CLIENT_MULTI_STATEMENTS

Tell the server that the client may send multiple statements in a single
string (separated by
‘;’). If this flag is
not set, multiple-statement execution is disabled.
See the note following this table for more
information about this flag.

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). This option should not be set by
application programs; it is set internally in the
client library. Instead, use
mysql_ssl_set() before calling
mysql_real_connect().

If your program uses the CALL SQL statement
to execute stored procedures that produce result sets, you
must set the
CLIENT_MULTI_RESULTS flag, either explicitly,
or implicitly by setting
CLIENT_MULTI_STATEMENTS when you call
mysql_real_connect(). This is because each
such stored procedure produces multiple results: the result sets
returned by statements executed within the procedure, as well as
a result to indicate the call status.

If you enable CLIENT_MULTI_STATEMENTS or
CLIENT_MULTI_RESULTS, you should process the
result for every call to mysql_query() or
mysql_real_query() by using a loop that calls
mysql_next_result() to determine whether
there are more results. For an example, see
Section 22.2.9, “C API Handling of Multiple Statement Execution”.

For some parameters, it is possible to have the value taken from
an option file rather than from an explicit value in the
mysql_real_connect() call. To do this, call
mysql_options() with the
MYSQL_READ_DEFAULT_FILE or
MYSQL_READ_DEFAULT_GROUP option before
calling mysql_real_connect(). Then, in the
mysql_real_connect() call, specify the
“no-value” value for each parameter to be read from
an option file:

For host, specify a value of
NULL or the empty string
("").

For user, specify a value of
NULL or the empty string.

For passwd, specify a value of
NULL. (For the password, a value of the
empty string in the mysql_real_connect()
call cannot be overridden in an option file, because the
empty string indicates explicitly that the MySQL account
must have an empty password.)

For db, specify a value of
NULL or the empty string.

For port, specify a value of 0.

For unix_socket, specify a value of
NULL.

If no value is found in an option file for a parameter, its
default value is used as indicated in the descriptions given
earlier in this section.

Return Values

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.

Errors

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.

By using mysql_options() the MySQL library
reads the [client] and
[your_prog_name] sections in the
my.cnf file which ensures that your program
works, 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 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.

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 9.1, “Literal Values”). (Strictly speaking, MySQL requires
only that backslash and the quote character used to quote the
string in the query be escaped. This function quotes the other
characters to make them easier to read in log files.)

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 worst case,
each character may need to be encoded as using two bytes, and
you need room for the terminating null byte.) When
mysql_real_escape_string() returns, the
contents of to is a null-terminated string.
The return value is the length of the encoded string, not
including the terminating null character.

If you need to change the character set of the connection, you
should use the mysql_set_character_set()
function rather than executing a SET NAMES
(or SET CHARACTER SET) statement.
mysql_set_character_set() works like
SET NAMES but also affects the character set
used by mysql_real_escape_string(), which
SET NAMES does not.

22.2.3.54. mysql_real_query()

Executes the SQL statement pointed to by
stmt_str, which should be a string
length bytes long. Normally, the string must
consist of a single SQL statement and you should not add a
terminating semicolon (‘;’) or
\g to the statement. If multiple-statement
execution has been enabled, the string can contain several
statements separated by semicolons. See
Section 22.2.9, “C API Handling of Multiple Statement Execution”.

mysql_query() cannot be used for statements
that contain binary data; you must use
mysql_real_query() instead. (Binary data may
contain the ‘\0’ character, which
mysql_query() interprets as the end of the
statement string.) In addition,
mysql_real_query() is faster than
mysql_query() because it does not call
strlen() on the statement string.

22.2.3.55. mysql_refresh()

This functions flushes tables or caches, or resets replication
server information. The connected user must have the
RELOAD privilege.

The options argument is a bit mask composed
from any combination of the following values. Multiple values
can be OR'ed together to perform multiple operations with a
single call.

REFRESH_GRANT

Refresh the grant tables, like FLUSH
PRIVILEGES.

REFRESH_LOG

Flush the logs, like FLUSH LOGS.

REFRESH_TABLES

Flush the table cache, like FLUSH TABLES.

REFRESH_HOSTS

Flush the host cache, like FLUSH HOSTS.

REFRESH_STATUS

Reset status variables, like FLUSH
STATUS.

REFRESH_THREADS

Flush the thread cache.

REFRESH_SLAVE

On a slave replication server, reset the master server
information and restart the slave, like RESET
SLAVE.

REFRESH_MASTER

On a master replication server, remove the binary log files
listed in the binary log index and truncate the index file,
like RESET MASTER.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

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.

22.2.3.56. mysql_reload()

int mysql_reload(MYSQL *mysql)

Description

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 an SQL FLUSH
PRIVILEGES statement instead.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

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.

22.2.3.57. mysql_rollback()

my_bool mysql_rollback(MYSQL *mysql)

Description

Rolls back the current transaction.

As of MySQL 5.0.3, the action of this function is subject to the
value of the completion_type system variable.
In particular, if the value of
completion_type is 2, the server performs a
release after terminating a transaction and closes the client
connection. The client program should call
mysql_close() to close the connection from
the client side.

Return Values

Zero if successful. Non-zero if an error occurred.

Errors

None.

22.2.3.58. mysql_row_seek()

Sets the row cursor to an arbitrary row in a query result set.
The offset value is a row offset that should
be a value returned from mysql_row_tell() or
from mysql_row_seek(). This value is not a
row number; if you want to seek to a row within a result set by
number, use mysql_data_seek() instead.

This function requires that the result set structure contains
the entire result of the query, so
mysql_row_seek() may be used only in
conjunction with mysql_store_result(), not
with mysql_use_result().

Return Values

The previous value of the row cursor. This value may be passed
to a subsequent call to mysql_row_seek().

Errors

None.

22.2.3.59. mysql_row_tell()

MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES
*result)

Description

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().

Return Values

The current offset of the row cursor.

Errors

None.

22.2.3.60. mysql_select_db()

int mysql_select_db(MYSQL *mysql, const char
*db)

Description

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.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

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.

22.2.3.61. mysql_set_character_set()

int mysql_set_character_set(MYSQL *mysql, char
*csname)

Description

This function is used to set the default character set for the
current connection. The string csname
specifies a valid character set name. The connection collation
becomes the default collation of the character set. This
function works like the SET NAMES statement,
but also sets the value of mysql->charset,
and thus affects the character set used by
mysql_real_escape_string()

22.2.3.62. mysql_set_local_infile_default()

void
mysql_set_local_infile_default(MYSQL *mysql);

Description

Sets the LOAD LOCAL DATA INFILE handler
callback functions to the defaults used internally by the C
client library. The library calls this function automatically if
mysql_set_local_infile_handler() has not been
called or does not supply valid functions for each of its
callbacks.

The mysql_set_local_infile_default() function
was added in MySQL 4.1.2.

This function installs callbacks to be used during the execution
of LOAD DATA LOCAL INFILE statements. It
enables application programs to exert control over local
(client-side) data file reading. The arguments are the
connection handler, a set of pointers to callback functions, and
a pointer to a data area that the callbacks can use to share
information.

To use mysql_set_local_infile_handler(), you
must write the following callback functions:

The initialization function. This is called once to do any setup
necessary, open the data file, allocate data structures, and so
forth. The first void** argument is a pointer
to a pointer. You can set the pointer (that is,
*ptr) to a value that will be passed to each
of the other callbacks (as a void*). The
callbacks can use this pointed-to value to maintain state
information. The userdata argument is the
same value that is passed to
mysql_set_local_infile_handler().

The initialization function should return zero for success,
non-zero for an error.

int
local_infile_read(void *ptr, char *buf, unsigned int buf_len);

The data-reading function. This is called repeatedly to read the
data file. buf points to the buffer where the
read data should be stored, and buf_len is
the maximum number of bytes that the callback can read and store
in the buffer. (It can read fewer bytes, but should not read
more.)

The return value is the number of bytes read, or zero when no
more data could be read (this indicates EOF). Return a value
less than zero if an error occurs.

void
local_infile_end(void *ptr)

The termination function. This is called once after
local_infile_read() has returned zero (EOF)
or an error. This function should deallocate any memory
allocated by local_infile_init() and perform
any other cleanup necessary. It is invoked even if the
initalization function returns an error.

The error-handling function. This is called to get a textual
error message to return to the user in case any of your other
functions returns an error. error_msg points
to the buffer into which the message should be written, and
error_msg_len is the length of the buffer.
The message should be written as a null-terminated string, so
the message can be at most
error_msg_len–1 bytes long.

The return value is the error number.

Typically, the other callbacks store the error message in the
data structure pointed to by ptr, so that
local_infile_error() can copy the message
from there into error_msg.

After calling
mysql_set_local_infile_handler() in your C
code and passing pointers to your callback functions, you can
then issue a LOAD DATA LOCAL INFILE statement
(for example, by using mysql_query()). The
client library automatically invokes your callbacks. The
filename specified in LOAD DATA LOCAL INFILE
will be passed as the second parameter to the
local_infile_init() callback.

The mysql_set_local_infile_handler() function
was added in MySQL 4.1.2.

Enabling multiple-statement support with
MYSQL_OPTION_MULTI_STATEMENTS_ON does not
have quite the same effect as enabling it by passing the
CLIENT_MULTI_STATEMENTS flag to
mysql_real_connect():
CLIENT_MULTI_STATEMENTS also enables
CLIENT_MULTI_RESULTS. If you are using the
CALL SQL statement in your programs,
multiple-result support must be enabled; this means that
MYSQL_OPTION_MULTI_STATEMENTS_ON by itself is
insufficient to allow the use of CALL.

Return Values

Zero for success. Non-zero if an error occurred.

Errors

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.

ER_UNKNOWN_COM_ERROR

The server didn't support
mysql_set_server_option() (which is the
case that the server is older than 4.1.1) or the server
didn't support the option one tried to set.

22.2.3.65. mysql_shutdown()

Asks the database server to shut down. The connected user must
have SHUTDOWN privileges. The
shutdown_level argument was added in MySQL
5.0.1. MySQL 5.0 servers support only one type of
shutdown; shutdown_level must be equal to
SHUTDOWN_DEFAULT. Additional shutdown levels
are planned to make it possible to choose the desired level.
Dynamically linked executables which have been compiled with
older versions of the libmysqlclient headers
and call mysql_shutdown() need to be used
with the old libmysqlclient dynamic library.

22.2.3.66. mysql_sqlstate()

const char *mysql_sqlstate(MYSQL *mysql)

Description

Returns a null-terminated string containing the SQLSTATE error
code for the most recently executed SQL statement. The error
code consists of five characters. '00000'
means “no error.” The values are specified by ANSI
SQL and ODBC. For a list of possible values, see
Appendix B, Errors, Error Codes, and Common Problems.

SQLSTATE values returned by mysql_sqlstate()
differ from MySQL-specific error numbers returned by
mysql_errno(). For example, the
mysql client program displays errors using
the following format, where 1146 is the
mysql_errno() value and
'42S02' is the corresponding
mysql_sqlstate() value:

Not all MySQL error numbers are mapped to SQLSTATE error codes.
The value 'HY000' (general error) is used for
unmapped error numbers.

If you call mysql_sqlstate() after
mysql_real_connect() fails,
mysql_sqlstate() might not return a useful
value. For example, this happens if a host is blocked by the
server and the connection is closed without any SQLSTATE value
being sent to the client.

Return Values

A null-terminated character string containing the SQLSTATE error
code.

22.2.3.67. mysql_ssl_set()

mysql_ssl_set() is used for establishing
secure connections using SSL. It must be called before
mysql_real_connect().

mysql_ssl_set() does nothing unless OpenSSL
support is enabled in the client library.

mysql is the connection handler returned from
mysql_init(). The other parameters are
specified as follows:

key is the pathname to the key file.

cert is the pathname to the certificate
file.

ca is the pathname to the certificate
authority file.

capath is the pathname to a directory
that contains trusted SSL CA certificates in pem format.

cipher is a list of allowable ciphers to
use for SSL encryption.

Any unused SSL parameters may be given as
NULL.

Return Values

This function always returns 0. If SSL setup
is incorrect, mysql_real_connect() returns an
error when you attempt to connect.

22.2.3.68. mysql_stat()

const char *mysql_stat(MYSQL *mysql)

Description

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.

Return Values

A character string describing the server status.
NULL if an error occurred.

Errors

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.

22.2.3.69. mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql)

Description

After invoking mysql_query() or
mysql_real_query(), you must call
mysql_store_result() or
mysql_use_result() for every statement that
successfully retrieves data (SELECT,
SHOW, DESCRIBE,
EXPLAIN, CHECK TABLE, and
so forth). You must also call
mysql_free_result() after you are done with
the result set.

You don't have to call mysql_store_result()
or mysql_use_result() for other statements,
but it does not do any harm or cause any notable performance
degradation if you call mysql_store_result()
in all cases. You can detect whether the statement has a result
set by checking whether mysql_store_result()
returns a non-zero value (more about this later on).

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 statement didn't return a result set (for example, if it
was an INSERT statement).

mysql_store_result() also returns a null
pointer if reading of the result set failed. You can check
whether an error occurred by checking whether
mysql_error() returns a non-empty string,
mysql_errno() returns non-zero, or
mysql_field_count() returns zero.

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

After you have called mysql_store_result()
and gotten back a result that isn't a null pointer, you can 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.

A MYSQL_RES result structure with the
results. NULL (0) if an error occurred.

Errors

mysql_store_result() resets
mysql_error() and
mysql_errno() if it succeeds.

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.

22.2.3.70. mysql_thread_id()

unsigned long mysql_thread_id(MYSQL *mysql)

Description

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 changes. This
means you should not get the thread ID and store it for later.
You should get it when you need it.

Return Values

The thread ID of the current connection.

Errors

None.

22.2.3.71. mysql_use_result()

MYSQL_RES *mysql_use_result(MYSQL *mysql)

Description

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 allocates
memory only 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 ties 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 are returned as part of the result set for your
next query. The C API gives 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 mysql_use_result() has
finished. (However, after you have fetched all the rows,
mysql_num_rows() accurately returns the
number of rows fetched.)

You must call mysql_free_result() once you
are done with the result set.

When using the libmysqld embedded server, the
memory benefits are essentially lost because memory usage
incrementally increases with each row retrieved until
mysql_free_result() is called.

Return Values

A MYSQL_RES result structure.
NULL if an error occurred.

Errors

mysql_use_result() resets
mysql_error() and
mysql_errno() if it succeeds.

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.

22.2.3.72. mysql_warning_count()

unsigned int mysql_warning_count(MYSQL
*mysql)

Description

Returns the number of warnings generated during execution of the
previous SQL statement.

Return Values

The warning count.

Errors

None.

22.2.4. C API Prepared Statements

The MySQL client/server protocol provides for the use of prepared
statements. This capability uses the MYSQL_STMT
statement handler data structure returned by the
mysql_stmt_init() initialization function.
Prepared execution is an efficient way to execute a statement more
than once. The statement is first parsed to prepare it for
execution. Then it is executed one or more times at a later time,
using the statement handle returned by the initialization
function.

Prepared execution is faster than direct execution for statements
executed more than once, primarily because the query is parsed
only once. In the case of direct execution, the query is parsed
every time it is executed. Prepared execution also can provide a
reduction of network traffic because for each execution of the
prepared statement, it is necessary only to send the data for the
parameters.

Prepared statements might not provide a performance increase in
some situations. For best results, test your application both with
prepared and non-prepared statements and choose whichever yields
best performance.

Another advantage of prepared statements is that it uses a binary
protocol that makes data transfer between client and server more
efficient.

The following statements can be used as prepared statements:
CREATE TABLE, DELETE,
DO, INSERT,
REPLACE, SELECT,
SET, UPDATE, and most
SHOW statements. Other statements are not
supported in MySQL 5.0.

22.2.5. C API Prepared Statement Data types

Prepared statements use several data structures:

To prepare a statement, pass the statement string to
mysql_stmt_init(), which returns a pointer
to a MYSQL_STMT data structure.

To provide input parameters for a prepared statement, set up
MYSQL_BIND structures and pass them to
mysql_stmt_bind_param(). To receive output
column values, set up MYSQL_BIND structures
and pass them to mysql_stmt_bind_result().

The MYSQL_TIME structure is used to
transfer temporal data in both directions.

The following discussion describes the prepared statement data
types in detail.

MYSQL_STMT

This structure represents a prepared statement. A statement is
created by calling mysql_stmt_init(), which
returns a statement handle (that is, a pointer to a
MYSQL_STMT). The handle is used for all
subsequent operations with the statement until you close it
with mysql_stmt_close(), at which point the
handle becomes invalid.

The MYSQL_STMT structure has no members
that are intended for application use. Also, you should not
try to make a copy of a MYSQL_STMT
structure. There is no guarantee that such a copy will be
usable.

Multiple statement handles can be associated with a single
connection. The limit on the number of handles depends on the
available system resources.

MYSQL_BIND

This structure is used both for statement input (data values
sent to the server) and output (result values returned from
the server):

For input, MYSQL_BIND is used with
mysql_stmt_bind_param() to bind
parameter data values to buffers for use by
mysql_stmt_execute().

For output, MYSQL_BIND is used with
mysql_stmt_bind_result() to bind result
set buffers for use in fetching rows with
mysql_stmt_fetch().

To use a MYSQL_BIND structure, you should
zero its contents to initialize it, and then set its members
appropriately. For example, to declare and initialize an array
of three MYSQL_BIND structures, use this
code:

MYSQL_BIND bind[3];
memset(bind, 0, sizeof(bind));

The MYSQL_BIND structure contains the
following members for use by application programs. For several
of the members, the manner of use depends on whether the
structure is used for input or output.

enum enum_field_types buffer_type

The type of the buffer. This member indicates the data
type of the C language variable that you are binding to
the statement parameter. The allowable
buffer_type values are listed later in
this section. For input, buffer_type
indicates the type of the variable containing the value
that you will send to the server. For output, it indicates
the type of the variable into which you want a value
received from the server to be stored.

void *buffer

A pointer to the buffer to be used for data transfer. This
is the address of a variable.

For input, buffer is a pointer to the
variable in which a statement parameter's data value is
stored. When you call
mysql_stmt_execute(), MySQL takes the
value that you have stored in the variable and uses it in
place of the corresponding parameter marker in the
statement.

For output, buffer is a pointer to the
variable in which to return a result set column value.
When you call mysql_stmt_fetch(), MySQL
returns a column value and stores it in this variable. You
can access the value when the call returns.

To minimize the need for MySQL to perform type conversions
between C language values on the client side and SQL
values on the server side, use variables that have types
similar to those of the corresponding SQL values. For
numeric data types, buffer should point
to a variable of the proper numeric C type. (For
char or integer variables, you should
also indicate whether the variable has the
unsigned attribute by setting the
is_unsigned member, described later in
this list.) For character (non-binary) and binary string
data types, buffer should point to a
character buffer. For date and time data types,
buffer should point to a
MYSQL_TIME structure.

See the notes about type conversions later in the section.

unsigned long buffer_length

The actual size of *buffer in bytes.
This indicates the maximum amount of data that can be
stored in the buffer. For character and binary C data, the
buffer_length value specifies the
length of *buffer when used with
mysql_stmt_bind_param() to specify
input values, or the maximum number of output data bytes
that can be fetched into the buffer when used with
mysql_stmt_bind_result().

unsigned long *length

A pointer to an unsigned long variable
that indicates the actual number of bytes of data stored
in *buffer. length
is used for character or binary C data.

For input parameter data binding,
length points to an unsigned
long variable that indicates the actual length
of the parameter value stored in
*buffer; this is used by
mysql_stmt_execute().

For output value binding, the return value of
mysql_stmt_fetch() determines the
interpretation of the length:

If mysql_stmt_fetch() returns 0,
*length indicates the actual length
of the parameter value.

If mysql_stmt_fetch() returns
MYSQL_DATA_TRUNCATED,
*length indicates the non-truncated
length of the parameter value. In this case, the
minimum of *length and
buffer_length indicates the actual
length of the value.

length is ignored for numeric and
temporal data types because the length of the data value
is determined by the buffer_type value.

my_bool *is_null

This member points to a my_bool
variable that is true if a value is
NULL, false if it is not
NULL. For input, set
*is_null to true to indicate that you
are passing a NULL value as a statement
parameter.

The reason that is_null is not a
boolean scalar but is instead a
pointer to a boolean scalar is to
provide flexibility in how you specify
NULL values:

If your data values are always
NULL, use
MYSQL_TYPE_NULL as the
buffer_type value when you bind the
column. The other members do not matter.

If your data values are always NOT
NULL, set the other members appropriately
for the variable you are binding, and set
is_null = (my_bool*) 0.

In all other cases, set the other members
appriopriately, and set is_null to
the address of a my_bool variable.
Set that variable's value to true or false
appropriately between executions to indicate whether
data values are NULL or
NOT NULL, respectively.

For output, the value pointed to by
is_null is set to true after you fetch
a row if the result set column value returned from the
statement is NULL.

my_bool is_unsigned

This member is used for C variables with data types that
can be unsigned
(char, short int,
int, long long int).
Set is_unsigned to true if the variable
pointed to by buffer is
unsigned and false otherwise. For
example, if you bind a signed char
variable to buffer, specify a type code
of MYSQL_TYPE_TINY and set
is_unsigned to false. If you bind an
unsigned char instead, the type code is
the same but is_unsigned should be
true. (For char, it is not defined
whether it is signed or unsigned, so it is best to be
explicit about signedness by using signed
char or unsigned char.)

is_unsigned applies only to the C
language variable on the client side. It indicates nothing
about the signedness of the corresponding SQL value on the
server side. For example, if you use an
int variable to supply a value for a
BIGINT UNSIGNED column,
is_unsigned should be false because
int is a signed type. If you use an
unsigned int variable to supply a value
for a BIGINT column,
is_unsigned should be true because
unsigned int is an unsigned type. MySQL
performs the proper conversion between signed and unsigned
values in both directions, although a warning occurs if
truncation results.

my_bool *error

For output, set this member to point to a
my_bool variable to have truncation
information for the parameter stored there after a row
fetching operation. (Truncation reporting is enabled by
default, but can be controlled by calling
mysql_options() with the
MYSQL_REPORT_DATA_TRUNCATION option.)
When truncation reporting is enabled,
mysql_stmt_fetch() returns
MYSQL_DATA_TRUNCATED and
*error is true in the
MYSQL_BIND structures for parameters in
which truncation occurred. Truncation indicates loss of
sign or significant digits, or that a string was too long
to fit in a column. The error member
was added in MySQL 5.0.3.

MYSQL_TIME

This structure is used to send and receive
DATE, TIME,
DATETIME, and TIMESTAMP
data directly to and from the server. Set the
buffer_type member of a
MYSQL_BIND structure to one of the temporal
types (MYSQL_TYPE_TIME,
MYSQL_TYPE_DATE,
MYSQL_TYPE_DATETIME,
MYSQL_TYPE_TIMESTAMP), and set the
buffer member to point to a
MYSQL_TIME structure.

The MYSQL_TIME structure contains the
members listed in the following table.

Member

Description

unsigned int year

The year

unsigned int month

The month of the year

unsigned int day

The day of the month

unsigned int hour

The hour of the day

unsigned int minute

The minute of the hour

unsigned int second

The second of the minute

my_bool neg

A boolean flag to indicate whether the time is negative

unsigned long second_part

The fractional part of the second in microseconds; currently unused

Only those parts of a MYSQL_TIME structure
that apply to a given type of temporal value are used. The
year, month, and
day elements are used for
DATE, DATETIME, and
TIMESTAMP values. The
hour, minute, and
second elements are used for
TIME, DATETIME, and
TIMESTAMP values. See
Section 22.2.10, “C API Handling of Date and Time Values”.

The following table shows the allowable values that may be
specified in the buffer_type member of
MYSQL_BIND structures for input values. The
value should be chosen according to the data type of the C
language variable that you are binding. If the variable is
unsigned, you should also set the
is_unsigned member to true. The table shows the
C variable types that you can use, the corresponding type codes,
and the SQL data types for which the supplied value can be used
without conversion.

Input Variable C Type

buffer_typeValue

SQL Type of Destination Value

signed char

MYSQL_TYPE_TINY

TINYINT

short int

MYSQL_TYPE_SHORT

SMALLINT

int

MYSQL_TYPE_LONG

INT

long long int

MYSQL_TYPE_LONGLONG

BIGINT

float

MYSQL_TYPE_FLOAT

FLOAT

double

MYSQL_TYPE_DOUBLE

DOUBLE

MYSQL_TIME

MYSQL_TYPE_TIME

TIME

MYSQL_TIME

MYSQL_TYPE_DATE

DATE

MYSQL_TIME

MYSQL_TYPE_DATETIME

DATETIME

MYSQL_TIME

MYSQL_TYPE_TIMESTAMP

TIMESTAMP

char[]

MYSQL_TYPE_STRING (for non-binary data)

TEXT, CHAR, VARCHAR

char[]

MYSQL_TYPE_BLOB (for binary data)

BLOB, BINARY, VARBINARY

MYSQL_TYPE_NULL

NULL

The use of MYSQL_TYPE_NULL is described earlier
in connection with the is_null member.

The following table shows the allowable values that may be
specified in the buffer_type member of
MYSQL_BIND structures for output values. The
value should be chosen according to the data type of the C
language variable that you are binding. If the variable is
unsigned, you should also set the
is_unsigned member to true. The table shows the
SQL types of received values, the corresponding type code that
such values have in result set metadata, and the recommended C
language data types to bind to the MYSQL_BIND
structure to receive the SQL values without conversion.

SQL Type of Received Value

buffer_typeValue

Output Variable C Type

TINYINT

MYSQL_TYPE_TINY

signed char

SMALLINT

MYSQL_TYPE_SHORT

short int

MEDIUMINT

MYSQL_TYPE_INT24

int

INT

MYSQL_TYPE_LONG

int

BIGINT

MYSQL_TYPE_LONGLONG

long long int

FLOAT

MYSQL_TYPE_FLOAT

float

DOUBLE

MYSQL_TYPE_DOUBLE

double

DECIMAL

MYSQL_TYPE_NEWDECIMAL

char[]

YEAR

MYSQL_TYPE_SHORT

short int

TIME

MYSQL_TYPE_TIME

MYSQL_TIME

DATE

MYSQL_TYPE_DATE

MYSQL_TIME

DATETIME

MYSQL_TYPE_DATETIME

MYSQL_TIME

TIMESTAMP

MYSQL_TYPE_TIMESTAMP

MYSQL_TIME

CHAR, BINARY

MYSQL_TYPE_STRING

char[]

VARCHAR, VARBINARY

MYSQL_TYPE_VAR_STRING

char[]

TINYBLOB, TINYTEXT

MYSQL_TYPE_TINY_BLOB

char[]

BLOB, TEXT

MYSQL_TYPE_BLOB

char[]

MEDIUMBLOB, MEDIUMTEXT

MYSQL_TYPE_MEDIUM_BLOB

char[]

LONGBLOB, LONGTEXT

MYSQL_TYPE_LONG_BLOB

char[]

BIT

MYSQL_TYPE_BIT

char[]

The C language variable types are those recommended if you want to
avoid type conversions. If there is a mismatch between the C
variable type on the client side and the corresponding SQL value
on the server side, MySQL performs implicit type conversions in
both directions.

MySQL knows the type code for the SQL value on the server side.
The buffer_type value indicates the MySQL the
type code of the C variable that holds the value on the client
side. The two codes together tell MySQL what conversion must be
performed, if any. Here are some examples:

If you use MYSQL_TYPE_LONG with an
int variable to pass an integer value to
the server that is to be stored into a
FLOAT column, MySQL converts the value to
floating-point format before storing it.

If you fetch a SQL MEDIUMINT column value,
but specify a buffer_type value of
MYSQL_TYPE_LONGLONG and use a C variable of
type long long int as the destination
buffer, MySQL will convert the MEDIUMINT
value (which requires less than 8 bytes) for storage into the
long long int (an 8-byte variable).

If you fetch a numeric column with a value of 255 into a
char[4] character array and specify a
buffer_type value of
MYSQL_TYPE_STRING, the resulting value in
the array will be a 4-byte string containing
'255\0'.

DECIMAL values are returned as strings,
which is why the corresponding C type is
char[]. DECIMAL values
returned by the server correspond to the string representation
of the original server-side value. For example,
12.345 is returned to the client as
'12.345'. If you specify
MYSQL_TYPE_NEWDECIMAL and bind a string
buffer to the MYSQL_BIND structure,
mysql_stmt_fetch() stores the value in the
buffer without conversion. If instead you specify a numeric
variable and type code, mysql_stmt_fetch()
converts the string-format DECIMAL value to
numeric form.

For the MYSQL_TYPE_BIT type code,
BIT values are returned into a string
buffer (thus, the corresponding C type is
char[] here, too). The value represents a
bit string that requires interpretation on the client side. To
return the value as a type that is easier to deal with, you
can use a query of the following form that uses +
0 to cause the value to be cast to integer:

SELECT bit_col + 0 FROM t

To retrieve the value, bind an integer variable large enough
to hold the value and specify the appropriate corresponding
integer type code.

Before binding variables to the MYSQL_BIND
structures that are to be used for fetching column values, you can
check the type codes for each column of the result set. This might
be desirable if you want to determine which variable types would
be best to use to avoid type conversions. To get the type codes,
call mysql_stmt_result_metadata() after
executing the prepared statement with
mysql_stmt_execute(). The metadata provides
access to the type codes for the result set as described in
Section 22.2.7.22, “mysql_stmt_result_metadata()”, and
Section 22.2.1, “C API Data types”.

If you cause the max_length member of the
MYSQL_FIELD column metadata structures to be
set (by calling mysql_stmt_attr_set()), be
aware that the max_length values for the result
set indicate the lengths of the longest string representation of
the result values, not the lengths of the binary representation.
That is, max_length does not necessarily
correspond to the size of the buffers needed to fetch the values
with the binary protocol used for prepared statements. The size of
the buffers should be chosen according to the types of the
variables into which you fetch the values.

For input character (non-binary) string data (indicated by
MYSQL_TYPE_STRING), the value is assumed to be
in the character set indicated by the
character_set_client system variable. If the
value is stored into a column with a different character set, the
appropriate conversion to that character set occurs. For input
binary string data (indicated by
MYSQL_TYPE_BLOB), the value is treated as
having the binary character set; that is, it is
treated as a byte string and no conversion occurs.

To determine whether output string values in a result set returned
from the server contain binary or non-binary data, check whether
the charsetnr value of the result set metadata
is 63 (see Section 22.2.1, “C API Data types”). If so, the
character set is binary, which indicates binary
rather than non-binary data. This enables you to distinguish
between BINARY and CHAR,
VARBINARY and VARCHAR, and
the BLOB and TEXT types.

Call mysql_stmt_init() to create a statement
handle, then mysql_stmt_prepare to prepare it,
mysql_stmt_bind_param() to supply the parameter
data, and mysql_stmt_execute() to execute the
statement. You can repeat the
mysql_stmt_execute() by changing parameter
values in the respective buffers supplied through
mysql_stmt_bind_param().

If the statement is a SELECT or any other
statement that produces a result set,
mysql_stmt_prepare() also returns the result
set metadata information in the form of a
MYSQL_RES result set through
mysql_stmt_result_metadata().

You can supply the result buffers using
mysql_stmt_bind_result(), so that the
mysql_stmt_fetch() automatically returns data
to these buffers. This is row-by-row fetching.

When statement execution has been completed, the statement handle
must be closed using mysql_stmt_close() so that
all resources associated with it can be freed.

If you obtained a SELECT statement's result set
metadata by calling
mysql_stmt_result_metadata(), you should also
free the metadata using mysql_free_result().

Execution Steps

To prepare and execute a statement, an application follows these
steps:

Create a prepared statement handle with
mysql_stmt_init(). To prepare the statement
on the server, call mysql_stmt_prepare()
and pass it a string containing the SQL statement.

If the statement produces a result set, call
mysql_stmt_result_metadata() to obtain the
result set metadata. This metadata is itself in the form of
result set, albeit a separate one from the one that contains
the rows returned by the query. The metadata result set
indicates how many columns are in the result and contains
information about each column.

Set the values of any parameters using
mysql_stmt_bind_param(). All parameters
must be set. Otherwise, statement execution returns an error
or produces unexpected results.

Call mysql_stmt_execute() to execute the
statement.

If the statement produces a result set, bind the data buffers
to use for retrieving the row values by calling
mysql_stmt_bind_result().

Fetch the data into the buffers row by row by calling
mysql_stmt_fetch() repeatedly until no more
rows are found.

Repeat steps 3 through 6 as necessary, by changing the
parameter values and re-executing the statement.

When mysql_stmt_prepare() is called, the MySQL
client/server protocol performs these actions:

The server parses the statement and sends the okay status back
to the client by assigning a statement ID. It also sends total
number of parameters, a column count, and its metadata if it
is a result set oriented statement. All syntax and semantics
of the statement are checked by the server during this call.

The client uses this statement ID for the further operations,
so that the server can identify the statement from among its
pool of statements.

When mysql_stmt_execute() is called, the MySQL
client/server protocol performs these actions:

The client uses the statement handle and sends the parameter
data to the server.

The server identifies the statement using the ID provided by
the client, replaces the parameter markers with the newly
supplied data, and executes the statement. If the statement
produces a result set, the server sends the data back to the
client. Otherwise, it sends an okay status and total number of
rows changed, deleted, or inserted.

When mysql_stmt_fetch() is called, the MySQL
client/server protocol performs these actions:

The client reads the data from the packet row by row and
places it into the application data buffers by doing the
necessary conversions. If the application buffer type is same
as that of the field type returned from the server, the
conversions are straightforward.

If an error occurs, you can get the statement error code, error
message, and SQLSTATE value using
mysql_stmt_errno(),
mysql_stmt_error(), and
mysql_stmt_sqlstate(), respectively.

Prepared Statement Logging

For prepared statements that are executed with the
mysql_stmt_prepare() and
mysql_stmt_execute() C API functions, the
server writes Prepare and
Execute lines to the general query log so that
you can tell when statements are prepared and executed.

Suppose that you prepare and execute a statement as follows:

Call mysql_stmt_prepare() to prepare the
statement string "SELECT ?".

Call mysql_stmt_bind_param() to bind the
value 3 to the parameter in the prepared
statement.

Call mysql_stmt_execute() to execute the
prepared statement.

As a result of the preceding calls, the server writes the
following lines to the general query log:

Prepare [1] SELECT ?
Execute [1] SELECT 3

Each Prepare and Execute
line in the log is tagged with a
[N] statement
identifier so that you can keep track of which prepared statement
is being logged. N is a positive
integer. If there are multiple prepared statements active
simultaneously for the client, N may be
greater than 1. Each Execute lines shows a
prepared statement after substitution of data values for
? parameters.

Version notes: Prepare lines are displayed
without [N] before
MySQL 4.1.10. Execute lines are not displayed
at all before MySQL 4.1.10.

To prepare and execute queries, use the functions described in
detail in the following sections.

Note that all functions operating with a
MYSQL_STMT structure begin with the prefix
mysql_stmt_.

To create a MYSQL_STMT handle, use the
mysql_stmt_init() function.

22.2.7.1. mysql_stmt_affected_rows()

my_ulonglong mysql_stmt_affected_rows(MYSQL_STMT
*stmt)

Description

Returns the total number of rows changed, deleted, or inserted
by the last executed statement. May be called immediately after
mysql_stmt_execute() for
UPDATE, DELETE, or
INSERT statements. For
SELECT statements,
mysql_stmt_affected_rows() works like
mysql_num_rows().

Return Values

An integer greater than zero indicates the number of rows
affected or retrieved. Zero indicates that no records were
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_stmt_affected_rows() was called
prior to calling mysql_stmt_store_result().
Because mysql_stmt_affected_rows() returns an
unsigned value, you can check for -1 by comparing the return
value to (my_ulonglong)-1 (or to
(my_ulonglong)~0, which is equivalent).

22.2.7.2. mysql_stmt_attr_get()

The option argument is the option that you
want to get; the arg should point to a
variable that should contain the option value. If the option is
an integer, then arg should point to the
value of the integer.

22.2.7.3. mysql_stmt_attr_set()

Can be used to affect behavior for a prepared statement. This
function may be called multiple times to set several options.

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, then
arg should point to the value of the integer.

Possible option values:

Option

Argument Type

Function

STMT_ATTR_UPDATE_MAX_LENGTH

my_bool *

If set to 1: Update metadata
MYSQL_FIELD->max_length in
mysql_stmt_store_result().

STMT_ATTR_CURSOR_TYPE

unsigned long *

Type of cursor to open for statement when
mysql_stmt_execute() is invoked.
*arg can be
CURSOR_TYPE_NO_CURSOR (the default)
or CURSOR_TYPE_READ_ONLY.

STMT_ATTR_PREFETCH_ROWS

unsigned long *

Number of rows to fetch from server at a time when using a cursor.
*arg can be in the range from 1 to
the maximum value of unsigned long.
The default is 1.

Note: In MySQL
5.0, mysql_stmt_attr_get() uses
unsigned int *, not my_bool
*, for STMT_ATTR_UPDATE_MAX_LENGTH.
This is corrected in MySQL 5.1.7.

If you use the STMT_ATTR_CURSOR_TYPE option
with CURSOR_TYPE_READ_ONLY, a cursor is
opened for the statement when you invoke
mysql_stmt_execute(). If there is already an
open cursor from a previous
mysql_stmt_execute() call, it closes the
cursor before opening a new one.
mysql_stmt_reset() also closes any open
cursor before preparing the statement for re-execution.
mysql_stmt_free_result() closes any open
cursor.

If you open a cursor for a prepared statement,
mysql_stmt_store_result() is unnecessary,
because that function causes the result set to be buffered on
the client side.

The STMT_ATTR_CURSOR_TYPE option was added in
MySQL 5.0.2. The STMT_ATTR_PREFETCH_ROWS
option was added in MySQL 5.0.6.

Return Values

Zero if successful. Non-zero if option is
unknown.

Errors

None.

Example

The following example opens a cursor for a prepared statement
and sets the number of rows to fetch at a time to 5:

22.2.7.4. mysql_stmt_bind_param()

my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt,
MYSQL_BIND *bind)

Description

mysql_stmt_bind_param() is used to bind input
data for the parameter markers in the SQL statement that was
passed to mysql_stmt_prepare(). It uses
MYSQL_BIND structures to supply the data.
bind is the address of an array of
MYSQL_BIND structures. The client library
expects the array to contain one element for each
‘?’ parameter marker that is
present in the query.

Suppose that you prepare the following statement:

INSERT INTO mytbl VALUES(?,?,?)

When you bind the parameters, the array of
MYSQL_BIND structures must contain three
elements, and can be declared like this:

22.2.7.5. mysql_stmt_bind_result()

my_bool mysql_stmt_bind_result(MYSQL_STMT *stmt,
MYSQL_BIND *bind)

Description

mysql_stmt_bind_result() is used to associate
(that is, bind) output columns in the result set to data buffers
and length buffers. When mysql_stmt_fetch()
is called to fetch data, the MySQL client/server protocol places
the data for the bound columns into the specified buffers.

All columns must be bound to buffers prior to calling
mysql_stmt_fetch(). bind
is the address of an array of MYSQL_BIND
structures. The client library expects the array to contain one
element for each column of the result set. If you do not bind
columns to MYSQL_BIND structures,
mysql_stmt_fetch() simply ignores the data
fetch. The buffers should be large enough to hold the data
values, because the protocol doesn't return data values in
chunks.

A column can be bound or rebound at any time, even after a
result set has been partially retrieved. The new binding takes
effect the next time mysql_stmt_fetch() is
called. Suppose that an application binds the columns in a
result set and calls mysql_stmt_fetch(). The
client/server protocol returns data in the bound buffers. Then
suppose that the application binds the columns to a different
set of buffers. The protocol places data into the newly bound
buffers when the next call to
mysql_stmt_fetch() occurs.

To bind a column, an application calls
mysql_stmt_bind_result() and passes the type,
address, and length of the output buffer into which the value
should be stored.
Section 22.2.5, “C API Prepared Statement Data types”, describes
the members of each MYSQL_BIND element and
how they should be set to receive output values.

Return Values

Zero if the bind operation was successful. Non-zero if an error
occurred.

Errors

CR_UNSUPPORTED_PARAM_TYPE

The conversion is not supported. Possibly the
buffer_type value is illegal or is not
one of the supported types.

22.2.7.7. mysql_stmt_data_seek()

Seeks to an arbitrary row in a statement result set. The
offset value is a row number and should be in
the range from 0 to
mysql_stmt_num_rows(stmt)-1.

This function requires that the statement result set structure
contains the entire result of the last executed query, so
mysql_stmt_data_seek() may be used only in
conjunction with mysql_stmt_store_result().

Return Values

None.

Errors

None.

22.2.7.8. mysql_stmt_errno()

unsigned int mysql_stmt_errno(MYSQL_STMT
*stmt)

Description

For the statement specified by stmt,
mysql_stmt_errno() returns the error code for
the most recently invoked statement 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.
Errors also are listed at Appendix B, Errors, Error Codes, and Common Problems.

Return Values

An error code value. Zero if no error occurred.

Errors

None.

22.2.7.9. mysql_stmt_error()

const char *mysql_stmt_error(MYSQL_STMT
*stmt)

Description

For the statement specified by stmt,
mysql_stmt_error() returns a null-terminated
string containing the error message for the most recently
invoked statement API function that can succeed or fail. An
empty string ("") is returned if no error
occurred. This means the following two tests are equivalent:

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.

Return Values

A character string that describes the error. An empty string if
no error occurred.

Errors

None.

22.2.7.10. mysql_stmt_execute()

int mysql_stmt_execute(MYSQL_STMT *stmt)

Description

mysql_stmt_execute() executes the prepared
query associated with the statement handle. The currently bound
parameter marker values are sent to server during this call, and
the server replaces the markers with this newly supplied data.

If the statement is an UPDATE,
DELETE, or INSERT, the
total number of changed, deleted, or inserted rows can be found
by calling mysql_stmt_affected_rows(). If
this is a statement such as SELECT that
generates a result set, you must call
mysql_stmt_fetch() to fetch the data prior to
calling any other functions that result in query processing. For
more information on how to fetch the results, refer to
Section 22.2.7.11, “mysql_stmt_fetch()”.

For statements that generate a result set, you can request that
mysql_stmt_execute() open a cursor for the
statement by calling mysql_stmt_attr_set()
before executing the statement. If you execute a statement
multiple times, mysql_stmt_execute() closes
any open cursor before opening a new one.

Return Values

Zero if execution was successful. Non-zero if an error occurred.

Errors

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.

Example

The following example demonstrates how to create and populate a
table using mysql_stmt_init(),
mysql_stmt_prepare(),
mysql_stmt_param_count(),
mysql_stmt_bind_param(),
mysql_stmt_execute(), and
mysql_stmt_affected_rows(). The
mysql variable is assumed to be a valid
connection handle.

Note: For complete examples on
the use of prepared statement functions, refer to the file
tests/mysql_client_test.c. This file can be
obtained from a MySQL source distribution or from the BitKeeper
source repository.

22.2.7.11. mysql_stmt_fetch()

int mysql_stmt_fetch(MYSQL_STMT *stmt)

Description

mysql_stmt_fetch() returns the next row in
the result set. It can be called only while the result set
exists; that is, after a call to
mysql_stmt_execute() that creates a result
set or after mysql_stmt_store_result(), which
is called after mysql_stmt_execute() to
buffer the entire result set.

mysql_stmt_fetch() returns row data using the
buffers bound by mysql_stmt_bind_result(). It
returns the data in those buffers for all the columns in the
current row set and the lengths are returned to the
length pointer.

All columns must be bound by the application before calling
mysql_stmt_fetch().

If a fetched data value is a NULL value, the
*is_null value of the corresponding
MYSQL_BIND structure contains TRUE (1).
Otherwise, the data and its length are returned in the
*buffer and *length
elements based on the buffer type specified by the application.
Each numeric and temporal type has a fixed length, as listed in
the following table. The length of the string types depends on
the length of the actual data value, as indicated by
data_length.

Type

Length

MYSQL_TYPE_TINY

1

MYSQL_TYPE_SHORT

2

MYSQL_TYPE_LONG

4

MYSQL_TYPE_LONGLONG

8

MYSQL_TYPE_FLOAT

4

MYSQL_TYPE_DOUBLE

8

MYSQL_TYPE_TIME

sizeof(MYSQL_TIME)

MYSQL_TYPE_DATE

sizeof(MYSQL_TIME)

MYSQL_TYPE_DATETIME

sizeof(MYSQL_TIME)

MYSQL_TYPE_STRING

data length

MYSQL_TYPE_BLOB

data_length

Return Values

Return Value

Description

0

Successful, the data has been fetched to application data buffers.

1

Error occurred. Error code and message can be obtained by calling
mysql_stmt_errno() and
mysql_stmt_error().

MYSQL_NO_DATA

No more rows/data exists

MYSQL_DATA_TRUNCATED

Data truncation occurred

MYSQL_DATA_TRUNCATED is returned when
truncation reporting is enabled. (Reporting is enabled by
default, but can be controlled with
mysql_options().) To determine which
parameters were truncated when this value is returned, check the
error members of the
MYSQL_BIND parameter structures.

Errors

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.

CR_UNSUPPORTED_PARAM_TYPE

The buffer type is MYSQL_TYPE_DATE,
MYSQL_TYPE_TIME,
MYSQL_TYPE_DATETIME, or
MYSQL_TYPE_TIMESTAMP, but the data type
is not DATE, TIME,
DATETIME, or
TIMESTAMP.

All other unsupported conversion errors are returned from
mysql_stmt_bind_result().

Example

The following example demonstrates how to fetch data from a
table using mysql_stmt_result_metadata(),
mysql_stmt_bind_result(), and
mysql_stmt_fetch(). (This example expects to
retrieve the two rows inserted by the example shown in
Section 22.2.7.10, “mysql_stmt_execute()”.) The
mysql variable is assumed to be a valid
connection handle.

22.2.7.12. mysql_stmt_fetch_column()

Fetch one column from the current result set row.
bind provides the buffer where data should be
placed. It should be set up the same way as for
mysql_stmt_bind_result().
column indicates which column to fetch. The
first column is numbered 0. offset is the
offset within the data value at which to begin retrieving data.
This can be used for fetching the data value in pieces. The
beginning of the value is offset 0.

Return Values

Zero if the value was fetched successfully. Non-zero if an error
occurred.

Errors

CR_INVALID_PARAMETER_NO

Invalid column number.

CR_NO_DATA

The end of the result set has already been reached.

22.2.7.13. mysql_stmt_field_count()

unsigned int mysql_stmt_field_count(MYSQL_STMT
*stmt)

Description

Returns the number of columns for the most recent statement for
the statement handler. This value is zero for statements such as
INSERT or DELETE that do
not produce result sets.

mysql_stmt_field_count() can be called after
you have prepared a statement by invoking
mysql_stmt_prepare().

Return Values

An unsigned integer representing the number of columns in a
result set.

Errors

None.

22.2.7.14. mysql_stmt_free_result()

my_bool mysql_stmt_free_result(MYSQL_STMT
*stmt)

Description

Releases memory associated with the result set produced by
execution of the prepared statement. If there is a cursor open
for the statement, mysql_stmt_free_result()
closes it.

Return Values

Zero if the result set was freed successfully. Non-zero if an
error occurred.

Errors

22.2.7.15. mysql_stmt_init()

MYSQL_STMT *mysql_stmt_init(MYSQL *mysql)

Description

Create a MYSQL_STMT handle. The handle should
be freed with mysql_stmt_close(MYSQL_STMT *).

Return values

A pointer to a MYSQL_STMT structure in case
of success. NULL if out of memory.

Errors

CR_OUT_OF_MEMORY

Out of memory.

22.2.7.16. mysql_stmt_insert_id()

my_ulonglong mysql_stmt_insert_id(MYSQL_STMT
*stmt)

Description

Returns the value generated for an
AUTO_INCREMENT column by the prepared
INSERT or UPDATE
statement. Use this function after you have executed a prepared
INSERT statement on a table which contains an
AUTO_INCREMENT field.

Value for AUTO_INCREMENT column which was
automatically generated or explicitly set during execution of
prepared statement, or value generated by
LAST_INSERT_ID(expr)
function. Return value is undefined if statement does not set
AUTO_INCREMENT value.

Errors

None.

22.2.7.17. mysql_stmt_num_rows()

my_ulonglong mysql_stmt_num_rows(MYSQL_STMT
*stmt)

Description

Returns the number of rows in the result set.

The use of mysql_stmt_num_rows() depends on
whether you used mysql_stmt_store_result() to
buffer the entire result set in the statement handle.

If you use mysql_stmt_store_result(),
mysql_stmt_num_rows() may be called
immediately.

mysql_stmt_num_rows() is intended for use
with statements that return a result set, such as
SELECT. For statements such as
INSERT, UPDATE, or
DELETE, the number of affected rows can be
obtained with mysql_stmt_affected_rows().

Return Values

The number of rows in the result set.

Errors

None.

22.2.7.18. mysql_stmt_param_count()

unsigned long mysql_stmt_param_count(MYSQL_STMT
*stmt)

Description

Returns the number of parameter markers present in the prepared
statement.

Return Values

An unsigned long integer representing the number of parameters
in a statement.

22.2.7.20. mysql_stmt_prepare()

Given the statement handle returned by
mysql_stmt_init(), prepares the SQL statement
pointed to by the string stmt_str and returns
a status value. The string length should be given by the
length argument. The string must consist of a
single SQL statement. You should not add a terminating semicolon
(‘;’) or \g to
the statement.

The application can include one or more parameter markers in the
SQL statement by embedding question mark
(‘?’) characters into the SQL
string at the appropriate positions.

The markers are legal only in certain places in SQL statements.
For example, they are allowed in the VALUES()
list of an INSERT statement (to specify
column values for a row), or in a comparison with a column in a
WHERE clause to specify a comparison value.
However, they are not allowed for identifiers (such as table or
column names), or to specify both operands of a binary operator
such as the = equal sign. The latter
restriction is necessary because it would be impossible to
determine the parameter type. In general, parameters are legal
only in Data Manipulation Language (DML) statements, and not in
Data Definition Language (DDL) statements.

The parameter markers must be bound to application variables
using mysql_stmt_bind_param() before
executing the statement.

Return Values

Zero if the statement was prepared successfully. Non-zero if an
error occurred.

Errors

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.

If the prepare operation was unsuccessful (that is,
mysql_stmt_prepare() returns non-zero), the
error message can be obtained by calling
mysql_stmt_error().

22.2.7.21. mysql_stmt_reset()

Reset the prepared statement on the client and server to state
after prepare. This is mainly used to reset data sent with
mysql_stmt_send_long_data(). Any open cursor
for the statement is closed.

To re-prepare the statement with another query, use
mysql_stmt_prepare().

Return Values

Zero if the statement was reset successfully. Non-zero if an
error occurred.

Errors

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.

22.2.7.22. mysql_stmt_result_metadata()

MYSQL_RES *mysql_stmt_result_metadata(MYSQL_STMT
*stmt)

Description

If a statement passed to mysql_stmt_prepare()
is one that produces a result set,
mysql_stmt_result_metadata() returns the
result set metadata in the form of a pointer to a
MYSQL_RES structure that can be used to
process the meta information such as total number of fields and
individual field information. This result set pointer can be
passed as an argument to any of the field-based API functions
that process result set metadata, such as:

mysql_num_fields()

mysql_fetch_field()

mysql_fetch_field_direct()

mysql_fetch_fields()

mysql_field_count()

mysql_field_seek()

mysql_field_tell()

mysql_free_result()

The result set structure should be freed when you are done with
it, which you can do by passing it to
mysql_free_result(). This is similar to the
way you free a result set obtained from a call to
mysql_store_result().

The result set returned by
mysql_stmt_result_metadata() contains only
metadata. It does not contain any row results. The rows are
obtained by using the statement handle with
mysql_stmt_fetch().

Return Values

A MYSQL_RES result structure.
NULL if no meta information exists for the
prepared query.

22.2.7.23. mysql_stmt_row_seek()

Sets the row cursor to an arbitrary row in a statement result
set. The offset value is a row offset that
should be a value returned from
mysql_stmt_row_tell() or from
mysql_stmt_row_seek(). This value is not a
row number; if you want to seek to a row within a result set by
number, use mysql_stmt_data_seek() instead.

This function requires that the result set structure contains
the entire result of the query, so
mysql_stmt_row_seek() may be used only in
conjunction with mysql_stmt_store_result().

Return Values

The previous value of the row cursor. This value may be passed
to a subsequent call to
mysql_stmt_row_seek().

Errors

None.

22.2.7.24. mysql_stmt_row_tell()

MYSQL_ROW_OFFSET mysql_stmt_row_tell(MYSQL_STMT
*stmt)

Description

Returns the current position of the row cursor for the last
mysql_stmt_fetch(). This value can be used as
an argument to mysql_stmt_row_seek().

You should use mysql_stmt_row_tell() only
after mysql_stmt_store_result().

Return Values

The current offset of the row cursor.

Errors

None.

22.2.7.25. mysql_stmt_send_long_data()

Allows an application to send parameter data to the server in
pieces (or “chunks”). Call this function after
mysql_stmt_bind_param() and before
mysql_stmt_execute(). It can be called
multiple times to send the parts of a character or binary data
value for a column, which must be one of the
TEXT or BLOB data types.

parameter_number indicates which parameter to
associate the data with. Parameters are numbered beginning with
0. data is a pointer to a buffer containing
data to be sent, and length indicates the
number of bytes in the buffer.

Note: The next
mysql_stmt_execute() call ignores the bind
buffer for all parameters that have been used with
mysql_stmt_send_long_data() since last
mysql_stmt_execute() or
mysql_stmt_reset().

Zero if the data is sent successfully to server. Non-zero if an
error occurred.

Errors

CR_INVALID_BUFFER_USE

The parameter does not have a string or binary type.

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_OUT_OF_MEMORY

Out of memory.

CR_UNKNOWN_ERROR

An unknown error occurred.

Example

The following example demonstrates how to send the data for a
TEXT column in chunks. It inserts the data
value 'MySQL - The most popular Open Source
database' into the text_column
column. The mysql variable is assumed to be a
valid connection handle.

22.2.7.26. mysql_stmt_sqlstate()

const char *mysql_stmt_sqlstate(MYSQL_STMT
*stmt)

Description

For the statement specified by stmt,
mysql_stmt_sqlstate() returns a
null-terminated string containing the SQLSTATE error code for
the most recently invoked prepared statement API function that
can succeed or fail. The error code consists of five characters.
"00000" means “no error.” The
values are specified by ANSI SQL and ODBC. For a list of
possible values, see Appendix B, Errors, Error Codes, and Common Problems.

Note that not all MySQL errors are yet mapped to SQLSTATE codes.
The value "HY000" (general error) is used for
unmapped errors.

Return Values

A null-terminated character string containing the SQLSTATE error
code.

22.2.7.27. mysql_stmt_store_result()

int mysql_stmt_store_result(MYSQL_STMT *stmt)

Description

You must call mysql_stmt_store_result() for
every statement that successfully produces a result set
(SELECT, SHOW,
DESCRIBE, EXPLAIN), and
only if you want to buffer the complete result set by the
client, so that the subsequent
mysql_stmt_fetch() call returns buffered
data.

It is unnecessary to call
mysql_stmt_store_result() for other
statements, but if you do, it does not harm or cause any notable
performance problem. You can detect whether the statement
produced a result set by checking if
mysql_stmt_result_metadata() returns
NULL. For more information, refer to
Section 22.2.7.22, “mysql_stmt_result_metadata()”.

Note: MySQL doesn't by default
calculate MYSQL_FIELD->max_length for all
columns in mysql_stmt_store_result() because
calculating this would slow down
mysql_stmt_store_result() considerably and
most applications doesn't need max_length. If
you want max_length to be updated, you can
call mysql_stmt_attr_set(MYSQL_STMT,
STMT_ATTR_UPDATE_MAX_LENGTH, &flag) to enable
this. See Section 22.2.7.3, “mysql_stmt_attr_set()”.

Return Values

Zero if the results are buffered successfully. Non-zero if an
error occurred.

Errors

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.

22.2.8. C API Prepared statement problems

Here follows a list of the currently known problems with prepared
statements:

TIME, TIMESTAMP, and
DATETIME do not support parts of seconds
(for example from DATE_FORMAT().

When converting an integer to string,
ZEROFILL is honored with prepared
statements in some cases where the MySQL server doesn't print
the leading zeros. (For example, with
MIN(number-with-zerofill)).

When converting a floating point number to a string in the
client, the rightmost digits of the converted value may differ
slightly from those of the original value.

Prepared statements do not support multi-statements (that is,
multiple statements within a single string separated by
‘;’ characters). This also
means that prepared statements cannot invoke stored procedures
that return result sets, because prepared statements do not
support multiple result sets.

22.2.9. C API Handling of Multiple Statement Execution

By default, mysql_query() and
mysql_real_query() interpret their statement
string argument as a single statement to be executed, and you
process the result according to whether the statement produces a
result set (a set of rows, as for SELECT) or an
affected-rows count (as for INSERT,
UPDATE, and so forth).

MySQL 5.0 also supports the execution of a string
containing multiple statements separated by semicolon
(‘;’) characters. This capability
is enabled by special options that are specified either when you
connect to the server with mysql_real_connect()
or after connecting by calling`
mysql_set_server_option().

Executing a multiple-statement string can produce multiple result
sets or row-count indicators. Processing these results involves a
different approach than for the single-statement case: After
handling the result from the first statement, it is necessary to
check whether more results exist and process them in turn if so.
To support multiple-result processing, the C API includes the
mysql_more_results() and
mysql_next_result() functions. Generally, these
functions are used at the end of a loop that iterates as long as
more results are available. Failure to process the
result this way may result in a dropped connection to the
server.

Multiple-result processing also is required if you execute
CALL statements for stored procedures: A stored
procedure returns a status result when it terminates, but it may
also produce result sets as it runs (for example, if it executes
SELECT statements). For any stored procedure
that produces result sets in addition to the final status, you
must be prepared to retrieve multiple results.

The multiple statement and result capabilities can be used only
with mysql_query() or
mysql_real_query(). They cannot be used with
the prepared statement interface. Prepared statement handles are
defined to work only with strings that contain a single statement.
See Section 22.2.4, “C API Prepared Statements”.

To enable multiple-statement execution and result processing, the
following options may be used:

The mysql_real_connect() function has a
flags argument for which two option values
are relevent:

CLIENT_MULTI_RESULTS enables the client
program to process multiple results. This option
must be enabled if you execute
CALL statements for stored procedures
that produce result sets. Otherwise, such procedures
result in an error Error 1312 (0A000): PROCEDURE
proc_name can't return a result
set in the given context.

CLIENT_MULTI_STATEMENTS enables
mysql_query() and
mysql_real_query() to execute statement
strings containing multiple statements separated by
semicolons. This option also enables
CLIENT_MULTI_RESULTS implicitly, so a
flags argument of
CLIENT_MULTI_STATEMENTS to
mysql_real_connect() is equivalent to
an argument of CLIENT_MULTI_STATEMENTS |
CLIENT_MULTI_RESULTS. That is,
CLIENT_MULTI_STATEMENTS is sufficient
to enable multiple-statement execution and all
multiple-result processing.

After the connection to the server has been established, you
can use the mysql_set_server_option()
function to enable or disable multiple-statement execution by
passing it an argument of
MYSQL_OPTION_MULTI_STATEMENTS_ON or
MYSQL_OPTION_MULTI_STATEMENTS_OFF. Enabling
multiple-statement execution with this function also enables
processing of “simple” results for a
multiple-statement string where each statement produces a
single result, but is not sufficient to
allow processing of stored procedures that produce result
sets.

The following procedure outlines a suggested strategy for handling
multiple statements:

The final part of the loop can be reduced to a simple test of
whether mysql_next_result() returns non-zero.
The code as written distinguishes between no more results and an
error, which allows a message to be printed for the latter
occurrence.

22.2.10. C API Handling of Date and Time Values

The binary (prepared statement) protocol allows you to send and
receive date and time values (DATE,
TIME, DATETIME, and
TIMESTAMP), using the
MYSQL_TIME structure. The members of this
structure are described in
Section 22.2.5, “C API Prepared Statement Data types”.

To send temporal data values, create a prepared statement using
mysql_stmt_prepare(). Then, before calling
mysql_stmt_execute() to execute the statement,
use the following procedure to set up each temporal parameter:

In the MYSQL_BIND structure associated with
the data value, set the buffer_type member
to the type that indicates what kind of temporal value you're
sending. For DATE, TIME,
DATETIME, or TIMESTAMP
values, set buffer_type to
MYSQL_TYPE_DATE,
MYSQL_TYPE_TIME,
MYSQL_TYPE_DATETIME, or
MYSQL_TYPE_TIMESTAMP, respectively.

Set the buffer member of the
MYSQL_BIND structure to the address of the
MYSQL_TIME structure in which you pass the
temporal value.

Fill in the members of the MYSQL_TIME
structure that are appropriate for the type of temporal value
to be passed.

Use mysql_stmt_bind_param() to bind the
parameter data to the statement. Then you can call
mysql_stmt_execute().

To retrieve temporal values, the procedure is similar, except that
you set the buffer_type member to the type of
value you expect to receive, and the buffer
member to the address of a MYSQL_TIME structure
into which the returned value should be placed. Use
mysql_bind_results() to bind the buffers to the
statement after calling mysql_stmt_execute()
and before fetching the results.

Here is a simple example that inserts DATE,
TIME, and TIMESTAMP data.
The mysql variable is assumed to be a valid
connection handle.

22.2.11.1. my_init()

void my_init(void)

Description

my_init() initializes some global variables
that MySQL needs. If you are using a thread-safe client library,
it also calls mysql_thread_init() for this
thread.

It is necessary for my_init() to be called
early in the initialization phase of a program's use of the
MySQL library. However, my_init() is
automatically called by mysql_init(),
mysql_library_init(),
mysql_server_init(), and
mysql_connect(). If you ensure that your
program invokes one of those functions before any other MySQL
calls, there is no need to invoke my_init()
explicitly.

To access my_init(), your program must
include the my_sys.h header file:

#include <my_sys.h>

Return Values

None.

22.2.11.2. mysql_thread_init()

my_bool mysql_thread_init(void)

Description

This function must be called early within each created thread to
initialize thread-specific variables. However, you may not
necessarily need to invoke it explicitly:
mysql_thread_init() is automatically called
by my_init(), which itself is automatically
called by mysql_init(),
mysql_library_init(),
mysql_server_init(), and
mysql_connect(). If you invoke any of those
functions, mysql_thread_init() will be called
for you.

Return Values

Zero if successful. Non-zero if an error occurred.

22.2.11.3. mysql_thread_end()

void mysql_thread_end(void)

Description

This function needs to be called before calling
pthread_exit() to free memory allocated by
mysql_thread_init().

Note that this mysql_thread_end()is not invoked automatically by the client
library. It must be called explicitly to avoid a
memory leak.

Return Values

None.

22.2.11.4. mysql_thread_safe()

unsigned int mysql_thread_safe(void)

Description

This function indicates whether the client library is compiled
as thread-safe.

Return Values

1 if the client library is thread-safe, 0 otherwise.

22.2.12. C API Embedded Server Function Descriptions

MySQL applications can be written to use an embedded server. See
Section 22.1, “libmysqld, the Embedded MySQL Server Library”. To write such an application, you
must link it against the libmysqld library by
using the -lmysqld flag rather than linking it
against the libmysqlclient client library by
using the -lmysqlclient flag. However, the calls
to initialize and finalize the library are the same whether you
write a client application or one that uses the embedded server:
Call mysql_library_init() to initialize the
library and mysql_library_end() when you are
done with it. See Section 22.2.2, “C API Function Overview”.

mysql_library_init() and
mysql_library_end() are available as of MySQL
5.0.3. For earlier versions of MySQL 5.0, call
mysql_server_init() and
mysql_server_end() instead, which are
equivalent. (mysql_library_init() and
mysql_library_end() actually are
#define symbols that make them equivalent to
mysql_server_init() and
mysql_server_end(), but the names more clearly
indicate that they should be called when beginning and ending use
of a MySQL C API library no matter whether the application uses
libmysqlclient or
libmysqld.)

22.2.12.1. mysql_server_init()

int mysql_server_init(int argc, char **argv, char
**groups)

Description

This function initializes the MySQL library, which must be done
before you call any other MySQL function.

22.2.13. Controlling Automatic Reconnect Behavior

The MySQL client library can perform an automatic reconnect to the
server if it finds that the connection is down when you attempt to
send a statement to the server to be executed. In this case, the
library tries once to reconnect to the server and send the
statement again.

Automatic reconnection can be convenient because you need not
implement your own reconnect code, but if a reconnection does
occur, several aspects of the connection state are reset and your
application will not know about it. The connection-related state
is affected as follows:

Any active transactions are rolled back and autocommit mode is
reset.

All table locks are released.

All TEMPORARY tables are closed (and
dropped).

Session variables are reinitialized to the values of the
corresponding variables. This also affects variables that are
set implicitly by statements such as SET
NAMES.

User variable settings are lost.

Prepared statements are released.

HANDLER variables are closed.

The value of LAST_INSERT_ID() is reset to
0.

Locks acquired with GET_LOCK() are
released.

mysql_ping() does not attempt a
reconnection if the connection is down. It returns an error
instead.

If it is important for your application to know that the
connection has been dropped (so that is can exit or take action to
adjust for the loss of state information), be sure to disable
auto-reconnect. This can be done explicitly by calling
mysql_options() with the
MYSQL_OPT_RECONNECT option:

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().

22.2.14.2. What Results You Can Get from a Query

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.

For a fast re-create, use TRUNCATE TABLE.

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().

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.

22.2.14.3. How to Get the Unique ID for the Last Inserted Row

If you insert a record into a table that contains an
AUTO_INCREMENT column, you can obtain the
value stored into that column by calling the
mysql_insert_id() function.

You can check from your C applications whether a value was
stored in an AUTO_INCREMENT column by
executing the following code (which assumes that you've checked
that the statement succeeded). It determines whether the query
was an INSERT with an
AUTO_INCREMENT index:

When a new AUTO_INCREMENT value has been
generated, you can also obtain it by executing a SELECT
LAST_INSERT_ID() statement with
mysql_query() and retrieving the value from
the result set returned by the statement.

For LAST_INSERT_ID(), the most recently
generated ID is maintained in the server on a per-connection
basis. It is not changed by another client. It is not even
changed if you update another AUTO_INCREMENT
column with a non-magic value (that is, a value that is not
NULL and not 0). Using
LAST_INSERT_ID() and
AUTO_INCREMENT columns simultaneously from
multiple clients is perfectly valid. Each client will receive
the last inserted ID for the last statement
that client executed.

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
this:

Note that mysql_insert_id() returns the value
stored into an AUTO_INCREMENT column, whether
that value is automatically generated by storing
NULL or 0 or was specified
as an explicit value. LAST_INSERT_ID()
returns only automatically generated
AUTO_INCREMENT values. If you store an
explicit value other than NULL or
0, it does not affect the value returned by
LAST_INSERT_ID().

For more information on obtaining the last ID in an
AUTO_INCREMENT column:

For information on LAST_INSERT_ID(),
which can be used within an SQL statement, see
???.

If this happens on your system, you must include the math
library by adding -lm to the end of the
compile/link line.

22.2.15. Building Client Programs

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 options in 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 in the
link command.

For clients that use MySQL header files, you may need to specify
an -I option when you compile them (for example,
-I/usr/local/mysql/include), so that the compiler
can find the header files.

The sh -c is needed to get the shell not to
treat the output from mysql_config as one word.

22.2.16. How to Make a Threaded Client

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.

To avoid aborting the program when a connection terminates, MySQL
blocks SIGPIPE on the first call to
mysql_library_init(),
mysql_init(), or
mysql_connect(). If you want to use your own
SIGPIPE handler, you should first call
mysql_library_init() and then install your
handler.

In the older binaries that we distribute on our Web site
(http://www.mysql.com/), client libraries other
than those for Windows are not normally compiled with the
thread-safe option. 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 net_serv.o code that the
server uses and the -lmysys,
-lmystrings, and -ldbug
libraries.

If you don't need interrupts or timeouts, you can just compile a
thread-safe client library (mysqlclient_r) and
use this. See Section 22.2, “MySQL C API”. 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 22.2.11, “C API Threaded Function Descriptions”.

In all cases, be sure to initialize the client library by calling
mysql_library_init() before calling any other
MySQL functions. When you are done with the library, call
mysql_library_end().

mysql_real_connect() is not thread-safe by
default. The following notes describe how to compile a thread-safe
client library and use it in a thread-safe manner. (The notes
below for mysql_real_connect() also apply to
mysql_connect() as well, although
mysql_connect() is deprecated.)

To make mysql_real_connect() thread-safe, you
must configure your MySQL distribution with this command:

shell> ./configure --enable-thread-safe-client

Then recompile the distribution to create a thread-safe client
library, libmysqlclient_r. (Assuming that your
operating system has a thread-safe
gethostbyname_r() function.) 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 calls to 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 must
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 pair of
mysql_query() and
mysql_store_result() calls. Once
mysql_store_result() is ready, the lock can
be released and other threads may query the same connection.

If you use 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 a thread that is calling MySQL
functions did not create the connection to the MySQL database:

When you call mysql_init() or
mysql_connect(), MySQL creates 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 does not have the
necessary thread-specific variables in place and you are likely to
end up with a core dump sooner or later.

To get things to work smoothly you have to do the following:

Call mysql_library_init() before any other
MySQL functions. It is not thread-safe, so call it before
threads are created, or protect the call with a mutex.

Arrange for mysql_thread_init() to be
called early in the thread handler before calling any MySQL
function. If you call mysql_init() or
mysql_connect(), they will call
mysql_thread_init() for you.

In the thread, call mysql_thread_end()
before calling pthread_exit(). This frees
the memory used by MySQL thread-specific variables.

If “undefined symbol” errors occur when linking your
client with libmysqlclient_r, in most cases
this is because you haven't included the thread libraries on the
link/compile command.

22.3. MySQL PHP API

PHP is a server-side, HTML-embedded scripting language that may be
used to create dynamic Web pages. It is available for most
operating systems and Web servers, and can access most common
databases, including MySQL. PHP may be run as a separate program
or compiled as a module for use with the Apache Web server.

PHP actually provides two different MySQL API extensions:

mysql: Available for PHP versions 4 and 5,
this extension is intended for use with MySQL versions prior
to MySQL 4.1. This extension does not support the improved
authentication protocol used in MySQL 5.0, nor
does it support prepared statements or multiple statements. If
you wish to use this extension with MySQL 5.0,
you will likely want to configure the MySQL server to use the
--old-passwords option (see
Section B.1.2.3, “Client does not support authentication protocol”). This extension is documented on
the PHP Web site at http://php.net/mysql.

mysqli - Stands for “MySQL,
Improved”; this extension is available only in PHP 5.
It is intended for use with MySQL 4.1.1 and later. This
extension fully supports the authentication protocol used in
MySQL 5.0, as well as the Prepared Statements and
Multiple Statements APIs. In addition, this extension provides
an advanced, object-oriented programming interface. You can
read the documentation for the mysqli
extension at http://php.net/mysqli. A helpful
article can be found at
http://www.zend.com/php5/articles/php5-mysqli.php.

The PHP distribution and documentation are available from the
PHP Web site. MySQL
provides the mysql and
mysqli extensions for the Windows operating
system for MySQL versions as of 5.0.18 on
http://dev.mysql.com/downloads/connector/php/.
You can find information why you should preferably use the
extensions provided by MySQL on that page.

22.3.1. Common Problems with MySQL and PHP

Error: Maximum Execution Time Exceeded:
This is a PHP limit; go into the
php.ini file and set the maximum
execution time up from 30 seconds to something higher, as
needed. It is also not a bad idea to double the RAM allowed
per script to 16MB instead of 8MB.

Fatal error: Call to unsupported or undefined
function mysql_connect() in ...: This means that
your PHP version isn't compiled with MySQL support. You can
either compile a dynamic MySQL module and load it into PHP
or recompile PHP with built-in MySQL support. This process
is described in detail in the PHP manual.

Error: Undefined reference to
'uncompress': This means that the client library
is compiled with support for a compressed client/server
protocol. The fix is to add -lz last when
linking with -lmysqlclient.

Error: Client does not support authentication
protocol: This is most often encountered when
trying to use the older mysql extension
with MySQL 4.1.1 and later. Possible solutions are:
downgrade to MySQL 4.0; switch to PHP 5 and the newer
mysqli extension; or configure the MySQL
server with --old-passwords. (See
Section B.1.2.3, “Client does not support authentication protocol”, for more information.)

22.4. MySQL Perl API

The Perl DBI module provides a generic
interface for database access. You can write a DBI script that
works with many different database engines without change. To use
DBI, you must install the DBI module, as well
as a DataBase Driver (DBD) module for each type of server you want
to access. For MySQL, this driver is the
DBD::mysql module.

Perl DBI is the recommended Perl interface. It replaces an older
interface called mysqlperl, which should be
considered obsolete.

For printed information, the official DBI book is
Programming the Perl DBI (Alligator
Descartes and Tim Bunce, O'Reilly & Associates, 2000).
Information about the book is available at the DBI Web site,
http://dbi.perl.org/.

For information that focuses specifically on using DBI with
MySQL, see MySQL and Perl for the Web
(Paul DuBois, New Riders, 2001). This book's Web site is
http://www.kitebird.com/mysql-perl/.

22.9. MySQL Program Development Utilities

This section describes some utilities that you may find useful
when developing MySQL programs.

msql2mysql

A shell script that converts mSQL programs
to MySQL. It doesn't handle every case, but it gives a good
start when converting.

mysql_config

A shell script that produces the option values needed when
compiling MySQL programs.

22.9.1. msql2mysql — Convert mSQL Programs for Use with MySQL

Initially, the MySQL C API was developed to be very similar
to that for the mSQL database system. Because of this, mSQL
programs often can be converted relatively easily for use
with MySQL by changing the names of the C API functions.

The msql2mysql utility performs the
conversion of mSQL C API function calls to their MySQL
equivalents. msql2mysql converts the
input file in place, so make a copy of the original before
converting it. For example, use
msql2mysql like this:

At Alden Hosting we eat and breathe Java! We are the industry leader in providing
affordable, quality and efficient Java web hosting in the shared hosting marketplace.
All our sites run on our Java hosing platform configured for
optimum performance using Java 1.6, Tomcat 6, MySQL 5, Apache 2.2 and web
application frameworks such as Struts, Hibernate, Cocoon, Ant, etc.

We offer only one type of Java hosting - Private Tomcat. Hosting accounts on the Private
Tomcat environment get their very own Tomcat server. You can start and re-start
your entire Tomcat server yourself.