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 section describes some utilities that you may find useful when developing MySQL programs.
msql2mysql
mSQL programs to MySQL. It doesn't
handle every case, but it gives a good start when converting.
mysql_config
msql2mysql, Convert mSQL Programs for Use with MySQLInitially, 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:
shell> cp client-prog.c client-prog.c.orig shell> msql2mysql client-prog.c client-prog.c converted
Then examine `client-prog.c' and make any post-conversion revisions that may be necessary.
msql2mysql uses the replace utility to make the function name
substitutions.
See section 8.13 The replace String-Replacement Utility.
mysql_config, Get compile options for compiling clients
mysql_config provides you with useful information for compiling
your MySQL client and connecting it to MySQL.
mysql_config supports the following options:
--cflags
libmysqlclient library.
--include
--cflags instead of this option.)
--libmysqld-libs, --embedded
--libs
--libs_r
--port
--socket
--version
If you invoke mysql_config with no options, it displays a list of all
options that it supports, and their values:
shell> mysql_config
Usage: /usr/local/mysql/bin/mysql_config [options]
Options:
--cflags [-I/usr/local/mysql/include/mysql -mcpu=pentiumpro]
--include [-I/usr/local/mysql/include/mysql]
--libs [-L/usr/local/mysql/lib/mysql -lmysqlclient -lz
-lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
--libs_r [-L/usr/local/mysql/lib/mysql -lmysqlclient_r
-lpthread -lz -lcrypt -lnsl -lm -lpthread]
--socket [/tmp/mysql.sock]
--port [3306]
--version [4.0.16]
--libmysqld-libs [-L/usr/local/mysql/lib/mysql -lmysqld -lpthread -lz
-lcrypt -lnsl -lm -lpthread -lrt]
You can use mysql_config within a command line to include the value
that it displays for a particular option. For example, to compile a MySQL
client program, use mysql_config as follows:
CFG=/usr/local/mysql/bin/mysql_config sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"
When you use mysql_config this way, be sure to invoke it within
backtick (``') characters. That tells the shell to execute it and
subsitute its output into the surrounding command.
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) use the mysqlclient
library to communicate with the MySQL server. This means that, for
example, you can take advantage of many of the same environment variables
that are used by other client programs, because they are referenced from the
library. See section 8 MySQL 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 queries and communication packets.
The communication buffer must be large enough to contain a single SQL
statement (for client-to-server traffic) and one row of returned data (for
server-to-client traffic). Each thread's communication buffer is dynamically
enlarged to handle any query or row up to the maximum limit. For example, if
you have BLOB values that contain up to 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.
For programming with threads, see section 21.2.15 How to Make a Threaded Client. For creating a standalone application which includes the "server" and "client" in the same program (and does not communicate with an external MySQL server), see section 21.2.16 libmysqld, the Embedded MySQL Server Library.
MYSQL
MYSQL_RES
SELECT, SHOW, DESCRIBE, EXPLAIN). The
information returned from a query is called the result set in the
remainder of this section.
MYSQL_ROW
mysql_fetch_row().
MYSQL_FIELD
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
mysql_field_seek().) Offsets are field numbers
within a row, beginning at zero.
my_ulonglong
mysql_affected_rows(),
mysql_num_rows(), and mysql_insert_id(). This type provides a
range of 0 to 1.84e19.
On some systems, attempting to print a value of type my_ulonglong
will not work. To print such a value, convert it to unsigned long
and use a %lu print format. Example:
printf ("Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));
The MYSQL_FIELD structure contains the members listed here:
char * name
char * table
table value is an empty string.
char * def
mysql_list_fields().
enum enum_field_types type
type value may be one of the following:
| Type Value | Type Description |
FIELD_TYPE_TINY | TINYINT field
|
FIELD_TYPE_SHORT | SMALLINT field
|
FIELD_TYPE_LONG | INTEGER field
|
FIELD_TYPE_INT24 | MEDIUMINT field
|
FIELD_TYPE_LONGLONG | BIGINT field
|
FIELD_TYPE_DECIMAL | DECIMAL or NUMERIC field
|
FIELD_TYPE_FLOAT | FLOAT field
|
FIELD_TYPE_DOUBLE | DOUBLE or REAL field
|
FIELD_TYPE_TIMESTAMP | TIMESTAMP field
|
FIELD_TYPE_DATE | DATE field
|
FIELD_TYPE_TIME | TIME field
|
FIELD_TYPE_DATETIME | DATETIME field
|
FIELD_TYPE_YEAR | YEAR field
|
FIELD_TYPE_STRING | CHAR field
|
FIELD_TYPE_VAR_STRING | VARCHAR field
|
FIELD_TYPE_BLOB | BLOB or TEXT field (use max_length to determine the maximum length)
|
FIELD_TYPE_SET | SET field
|
FIELD_TYPE_ENUM | ENUM field
|
FIELD_TYPE_NULL | NULL-type field
|
FIELD_TYPE_CHAR | Deprecated; use FIELD_TYPE_TINY instead
|
IS_NUM() macro to test whether a field has a
numeric type. Pass the type value to IS_NUM() and it
will evaluate to TRUE if the field is numeric:
if (IS_NUM(field->type))
printf("Field is numeric\n");
unsigned int length
unsigned int max_length
mysql_store_result() or mysql_list_fields(), this contains the
maximum length for the field. If you use mysql_use_result(), the
value of this variable is zero.
unsigned int flags
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)
|
BLOB_FLAG, ENUM_FLAG, SET_FLAG, and
TIMESTAMP_FLAG flags is deprecated because they indicate the type of
a field rather than an attribute of its type. It is preferable to test
field->type against FIELD_TYPE_BLOB, FIELD_TYPE_ENUM,
FIELD_TYPE_SET, or FIELD_TYPE_TIMESTAMP instead.
The following example illustrates a typical use of the flags value:
if (field->flags & NOT_NULL_FLAG)
printf("Field can't be null\n");
You may use the following convenience macros to determine the boolean
status of the flags value:
| Flag Status | Description |
IS_NOT_NULL(flags) | True if this field is defined as NOT NULL
|
IS_PRI_KEY(flags) | True if this field is a primary key |
IS_BLOB(flags) | True if this field is a BLOB or TEXT (deprecated; test field->type instead)
|
unsigned int decimals
The functions available in the C API are summarized here and described in greater detail in a later section. See section 21.2.3 C API Function Descriptions.
| Function | Description |
| mysql_affected_rows() |
Returns the number of rows changed/deleted/inserted by the last UPDATE,
DELETE, or INSERT query.
|
| mysql_change_user() | Changes user and database on an open connection. |
| mysql_charset_name() | Returns the name of the default character set for the connection. |
| mysql_close() | Closes a server connection. |
| mysql_connect() |
Connects to a MySQL server. This function is deprecated; use
mysql_real_connect() instead.
|
| mysql_create_db() |
Creates a database. This function is deprecated; use the SQL statement
CREATE DATABASE instead.
|
| mysql_data_seek() | Seeks to an arbitrary row number in a query result set. |
| mysql_debug() |
Does a DBUG_PUSH with the given string.
|
| mysql_drop_db() |
Drops a database. This function is deprecated; use the SQL statement
DROP DATABASE instead.
|
| mysql_dump_debug_info() | Makes the server write debug information to the log. |
| mysql_eof() |
Determines whether the last row of a result set has been read.
This function is deprecated; mysql_errno() or mysql_error()
may be used instead.
|
| mysql_errno() | Returns the error number for the most recently invoked MySQL function. |
| mysql_error() | Returns the error message for the most recently invoked MySQL function. |
| mysql_escape_string() | Escapes special characters in a string for use in an SQL statement. |
| mysql_fetch_field() | Returns the type of the next table field. |
| mysql_fetch_field_direct() | Returns the type of a table field, given a field number. |
| mysql_fetch_fields() | Returns an array of all field structures. |
| mysql_fetch_lengths() | Returns the lengths of all columns in the current row. |
| mysql_fetch_row() | Fetches the next row from the result set. |
| mysql_field_seek() | Puts the column cursor on a specified column. |
| mysql_field_count() | Returns the number of result columns for the most recent statement. |
| mysql_field_tell() |
Returns the position of the field cursor used for the last
mysql_fetch_field().
|
| mysql_free_result() | Frees memory used by a result set. |
| mysql_get_client_info() | Returns client version information as a string. |
| mysql_get_client_version() | Returns client version information as an integer. |
| mysql_get_host_info() | Returns a string describing the connection. |
| mysql_get_server_version() | Returns version number of server as an integer (new in 4.1). |
| mysql_get_proto_info() | Returns the protocol version used by the connection. |
| mysql_get_server_info() | Returns the server version number. |
| mysql_info() | Returns information about the most recently executed query. |
| mysql_init() |
Gets or initializes a MYSQL structure.
|
| mysql_insert_id() |
Returns the ID generated for an AUTO_INCREMENT column by the previous
query.
|
| mysql_kill() | Kills a given thread. |
| mysql_list_dbs() | Returns database names matching a simple regular expression. |
| mysql_list_fields() | Returns field names matching a simple regular expression. |
| mysql_list_processes() | Returns a list of the current server threads. |
| mysql_list_tables() | Returns table names matching a simple regular expression. |
| mysql_num_fields() | Returns the number of columns in a result set. |
| mysql_num_rows() | Returns the number of rows in a result set. |
| mysql_options() |
Sets connect options for mysql_connect().
|
| mysql_ping() | Checks whether the connection to the server is working, reconnecting as necessary. |
| mysql_query() | Executes an SQL query specified as a null-terminated string. |
| mysql_real_connect() | Connects to a MySQL server. |
| mysql_real_escape_string() | Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection. |
| mysql_real_query() | Executes an SQL query specified as a counted string. |
| mysql_reload() | Tells the server to reload the grant tables. |
| mysql_row_seek() |
Seeks to a row offset in a result set, using value returned from
mysql_row_tell().
|
| mysql_row_tell() | Returns the row cursor position. |
| mysql_select_db() | Selects a database. |
| mysql_set_server_option() |
Sets an option for the connection (like multi-statements).
|
| mysql_sqlstate() | Returns the SQLSTATE error code for the last error. |
| mysql_shutdown() | Shuts down the database server. |
| mysql_stat() | Returns the server status as a string. |
| mysql_store_result() | Retrieves a complete result set to the client. |
| mysql_thread_id() | Returns the current thread ID. |
| mysql_thread_safe() | Returns 1 if the clients are compiled as thread-safe. |
| mysql_use_result() | Initiates a row-by-row result set retrieval. |
| mysql_warning_count() | Returns the warning count for the previous SQL statement. |
| mysql_commit() | Commits the transaction. |
| mysql_rollback() | Rolls back the transaction. |
| mysql_autocommit() | Toggles autocommit mode on/off. |
| mysql_more_results() | Checks whether any more results exist. |
| mysql_next_result() | Returns/initiates the next result in multiple-statement executions. |
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 strictly older than 5.0.3, of 0 in newer
versions. A value of 1 for this flag indicates, in the event that a
query cannot be performed because of a lost connection, to try reconnecting to
the server before giving up. When you are done with the connection, call
mysql_close() to terminate it.
While a connection is active, the client may send SQL queries to the server
using mysql_query() or mysql_real_query(). The difference
between the two is that mysql_query() expects the query to be
specified as a null-terminated string whereas mysql_real_query()
expects a counted string. If the string contains binary data (which may
include null bytes), you must use mysql_real_query().
For each non-SELECT query (for example, INSERT, UPDATE,
DELETE), you can find out how many rows were changed (affected)
by calling mysql_affected_rows().
For SELECT queries, you retrieve the selected rows as a result set.
(Note that some statements are SELECT-like in that they return rows.
These include SHOW, DESCRIBE, and EXPLAIN. They should
be treated the same way as SELECT statements.)
There are two ways for a client to process result sets. One way is to
retrieve the entire result set all at once by calling
mysql_store_result(). This function acquires from the server all the
rows returned by the query and stores them in the client. The second way is
for the client to initiate a row-by-row result set retrieval by calling
mysql_use_result(). This function 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
already been fetched from the server. With mysql_use_result(),
mysql_fetch_row() actually retrieves the row from the server.
Information about the size of the data in each row is available by
calling mysql_fetch_lengths().
After you are done with a result set, call mysql_free_result()
to free the memory used for it.
The two retrieval mechanisms are complementary. Client programs should
choose the approach that is most appropriate for their requirements.
In practice, clients tend to use mysql_store_result() more
commonly.
An advantage of mysql_store_result() is that because the rows have all
been fetched to the client, you not only can access rows sequentially, you
can move back and forth in the result set using mysql_data_seek() or
mysql_row_seek() to change the current row position within the result
set. You can also find out how many rows there are by calling
mysql_num_rows(). On the other hand, the memory requirements for
mysql_store_result() may be very high for large result sets and you
are more likely to encounter out-of-memory conditions.
An advantage of mysql_use_result() is that the client requires less
memory for the result set because it maintains only one row at a time (and
because there is less allocation overhead, mysql_use_result() can be
faster). Disadvantages are that you must process each row quickly to avoid
tying up the server, you don't have random access to rows within the result
set (you can only access rows sequentially), and you don't know how many rows
are in the result set until you have retrieved them all. Furthermore, you
must retrieve all the rows even if you determine in mid-retrieval that
you've found the information you were looking for.
The API makes it possible for clients to respond appropriately to
queries (retrieving rows only as necessary) without knowing whether or
not the query is a SELECT. You can do this by calling
mysql_store_result() after each mysql_query() (or
mysql_real_query()). If the result set call succeeds, the query
was a SELECT and you can read the rows. If the result set call
fails, call mysql_field_count() to determine whether a
result was actually to be expected. If mysql_field_count()
returns zero, the query returned no data (indicating that it was an
INSERT, UPDATE, DELETE, etc.), and was not
expected to return rows. If mysql_field_count() is non-zero, the
query should have returned rows, but didn't. This indicates that the
query was a SELECT that failed. See the description for
mysql_field_count() for an example of how this can be done.
Both mysql_store_result() and mysql_use_result() allow you to
obtain information about the fields that make up the result set (the number
of fields, their names and types, etc.). You can access field information
sequentially within the row by calling mysql_fetch_field() repeatedly,
or by field number within the row by calling
mysql_fetch_field_direct(). The current field cursor position may be
changed by calling mysql_field_seek(). Setting the field cursor
affects subsequent calls to mysql_fetch_field(). You can also get
information for fields all at once by calling mysql_fetch_fields().
For detecting and reporting errors, MySQL provides access to error
information by means of the mysql_errno() and mysql_error()
functions. These return the error code or error message for the most
recently invoked function that can succeed or fail, allowing you to determine
when an error occurred and what it was.
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:
if (result) /* correct */
... error ...
if (result < 0) /* incorrect */
... error ...
if (result == -1) /* incorrect */
... error ...
When a function returns an error, the Errors subsection of the
function description lists the possible types of errors. You can
find out which of these occurred by calling mysql_errno().
A string representation of the error may be obtained by calling
mysql_error().
mysql_affected_rows()
my_ulonglong mysql_affected_rows(MYSQL *mysql)
Returns the number of rows changed by the last UPDATE, deleted by
the last DELETE or inserted by the last INSERT
statement. May be called immediately after mysql_query() for
UPDATE, DELETE, or INSERT statements. For
SELECT statements, mysql_affected_rows() works like
mysql_num_rows().
An integer greater than zero indicates the number of rows affected or
retrieved. Zero indicates that no records 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).
None.
mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10");
printf("%ld products updated",(long) mysql_affected_rows(&mysql));
If you specify the flag CLIENT_FOUND_ROWS when connecting to
mysqld, mysql_affected_rows() will return the number of
rows matched by the WHERE statement for UPDATE statements.
Note that when you use a REPLACE command,
mysql_affected_rows() returns 2 if the new row replaced an
old row. This is 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.
mysql_change_user()
my_bool mysql_change_user(MYSQL *mysql, const char *user, const
char *password, const char *db)
Changes the user and causes the database specified by db to
become the default (current) database on the connection specified by
mysql. In subsequent queries, this database is the default for
table references that do not include an explicit database specifier.
This function was introduced in MySQL 3.23.3.
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.
Starting from MySQL 4.0.6 this command will always ROLLBACK any
active transactions, close all temporary tables, unlock all locked
tables and reset the state as if one had done a new connect.
This will happen even if the user didn't change.
Zero for success. Non-zero if an error occurred.
The same that you can get from mysql_real_connect().
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
ER_UNKNOWN_COM_ERROR
ER_ACCESS_DENIED_ERROR
ER_BAD_DB_ERROR
ER_DBACCESS_DENIED_ERROR
ER_WRONG_DB_NAME
if (mysql_change_user(&mysql, "user", "password", "new_database"))
{
fprintf(stderr, "Failed to change user. Error: %s\n",
mysql_error(&mysql));
}
mysql_character_set_name()
const char *mysql_character_set_name(MYSQL *mysql)
Returns the default character set for the current connection.
The default character set
None.
mysql_close()
void mysql_close(MYSQL *mysql)
Closes a previously opened connection. mysql_close() also deallocates
the connection handle pointed to by mysql if the handle was allocated
automatically by mysql_init() or mysql_connect().
None.
None.
mysql_connect()
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)
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.)
Same as for mysql_real_connect().
Same as for mysql_real_connect().
mysql_create_db()
int mysql_create_db(MYSQL *mysql, const char *db)
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.
Zero if the database was created successfully. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
if(mysql_create_db(&mysql, "my_database"))
{
fprintf(stderr, "Failed to create new database. Error: %s\n",
mysql_error(&mysql));
}
mysql_data_seek()
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)
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().
None.
None.
mysql_debug()
void mysql_debug(const char *debug)
Does a DBUG_PUSH with the given string. mysql_debug() uses the
Fred Fish debug library. To use this function, you must compile the client
library to support debugging.
See section E.1 Debugging a MySQL Server. See section E.2 Debugging a MySQL Client.
None.
None.
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");
mysql_drop_db()
int mysql_drop_db(MYSQL *mysql, const char *db)
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.
Zero if the database was dropped successfully. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
if(mysql_drop_db(&mysql, "my_database"))
fprintf(stderr, "Failed to drop the database: Error: %s\n",
mysql_error(&mysql));
mysql_dump_debug_info()
int mysql_dump_debug_info(MYSQL *mysql)
Instructs the server to write some debug information to the log. For
this to work, the connected user must have the SUPER privilege.
Zero if the command was successful. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_eof()
my_bool mysql_eof(MYSQL_RES *result)
This function is deprecated. mysql_errno() or mysql_error()
may be used instead.
mysql_eof() determines whether the last row of a result
set has been read.
If you acquire a result set from a successful call to
mysql_store_result(), the client receives the entire set in one
operation. In this case, a NULL return from
mysql_fetch_row() always means the end of the result set has been
reached and it is unnecessary to call mysql_eof(). When used
with mysql_store_result(), mysql_eof() will always return
true.
On the other hand, if you use mysql_use_result() to initiate a result
set retrieval, the rows of the set are obtained from the server one by one as
you call mysql_fetch_row() repeatedly. Because an error may occur on
the connection during this process, a NULL return value from
mysql_fetch_row() does not necessarily mean the end of the result set
was reached normally. In this case, you can use mysql_eof() to
determine what happened. mysql_eof() returns a non-zero value if the
end of the result set was reached and zero if an error occurred.
Historically, mysql_eof() predates the standard MySQL error
functions mysql_errno() and mysql_error(). Because those error
functions provide the same information, their use is preferred over
mysql_eof(), which is now deprecated. (In fact, they provide more
information, because mysql_eof() returns only a boolean value whereas
the error functions indicate a reason for the error when one occurs.)
Zero if no error occurred. Non-zero if the end of the result set has been reached.
None.
The following example shows how you might use mysql_eof():
mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
// do something with data
}
if(!mysql_eof(result)) // mysql_fetch_row() failed due to an error
{
fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}
However, you can achieve the same effect with the standard MySQL error functions:
mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
// do something with data
}
if(mysql_errno(&mysql)) // mysql_fetch_row() failed due to an error
{
fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}
mysql_errno()
unsigned int mysql_errno(MYSQL *mysql)
For the connection specified by mysql, mysql_errno() returns
the error code for the most recently invoked API function that can succeed
or fail. A return value of zero means that no error occurred. Client error
message numbers are listed in the MySQL `errmsg.h' header file.
Server error message numbers are listed in `mysqld_error.h'. In the
MySQL source distribution you can find a complete list of
error messages and error numbers in the file `Docs/mysqld_error.txt'.
The server error codes also are listed at section 23 Error Handling in MySQL.
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 will reset mysql_errno() if they succeed.
An error code value for the last mysql_xxx() call, if it failed.
zero means no error occurred.
None.
mysql_error()
const char *mysql_error(MYSQL *mysql)
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 will reset mysql_error() if they succeed.
For functions that reset mysql_errno(), the following two tests
are equivalent:
if(mysql_errno(&mysql))
{
// an error occurred
}
if(mysql_error(&mysql)[0] != '\0')
{
// an error occurred
}
The language of the client error messages may be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages. See section 5.8.2 Setting the Error Message Language.
A null-terminated character string that describes the error. An empty string if no error occurred.
None.
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 charset setting.
mysql_fetch_field()
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
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.
The MYSQL_FIELD structure for the current column. NULL
if no columns are left.
None.
MYSQL_FIELD *field;
while((field = mysql_fetch_field(result)))
{
printf("field name %s\n", field->name);
}
mysql_fetch_fields()
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)
Returns an array of all MYSQL_FIELD structures for a result set.
Each structure provides the field definition for one column of the result
set.
An array of MYSQL_FIELD structures for all columns of a result set.
None.
unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *fields;
num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++)
{
printf("Field %u is %s\n", i, fields[i].name);
}
mysql_fetch_field_direct()
MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int fieldnr)
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.
The MYSQL_FIELD structure for the specified column.
None.
unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *field;
num_fields = mysql_num_fields(result);
for(i = 0; i < num_fields; i++)
{
field = mysql_fetch_field_direct(result, i);
printf("Field %u is %s\n", i, field->name);
}
mysql_fetch_lengths()
unsigned long *mysql_fetch_lengths(MYSQL_RES *result)
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().
An array of unsigned long integers representing the size of each column (not
including any terminating null characters).
NULL if an error occurred.
mysql_fetch_lengths() is valid only for the current row of the result
set. It returns NULL if you call it before calling
mysql_fetch_row() or after retrieving all rows in the result.
MYSQL_ROW row;
unsigned long *lengths;
unsigned int num_fields;
unsigned int i;
row = mysql_fetch_row(result);
if (row)
{
num_fields = mysql_num_fields(result);
lengths = mysql_fetch_lengths(result);
for(i = 0; i < num_fields; i++)
{
printf("Column %u is %lu bytes in length.\n", i, lengths[i]);
}
}
mysql_fetch_row()
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
Retrieves the next row of a result set. When used after
mysql_store_result(), mysql_fetch_row() returns NULL
when there are no more rows to retrieve. When used after
mysql_use_result(), mysql_fetch_row() returns NULL when
there are no more rows to retrieve or if an error occurred.
The number of values in the row is given by mysql_num_fields(result).
If row holds the return value from a call to mysql_fetch_row(),
pointers to the values are accessed as row[0] to
row[mysql_num_fields(result)-1]. NULL values in the row are
indicated by NULL pointers.
The lengths of the field values in the row may be obtained by calling
mysql_fetch_lengths(). Empty fields and fields containing
NULL both have length 0; you can distinguish these by checking
the pointer for the field value. If the pointer is NULL, the field
is NULL; otherwise, the field is empty.
A MYSQL_ROW structure for the next row. NULL if
there are no more rows to retrieve or if an error occurred.
Note that error is not reset between calls to mysql_fetch_row()
CR_SERVER_LOST
CR_UNKNOWN_ERROR
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);
for(i = 0; i < num_fields; i++)
{
printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");
}
printf("\n");
}
mysql_field_count()
unsigned int mysql_field_count(MYSQL *mysql)
If you are using a version of MySQL earlier than Version 3.22.24, you
should use unsigned int mysql_num_fields(MYSQL *mysql) instead.
Returns the number of columns for the most recent query on the connection.
The normal use of this function is when mysql_store_result()
returned NULL (and thus you have no result set pointer).
In this case, you can call mysql_field_count() to
determine whether mysql_store_result() should have produced a
non-empty result. This allows the client program to take proper action
without knowing whether the query was a SELECT (or
SELECT-like) statement. The example shown here illustrates how this
may be done.
See section 21.2.13.1 Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success.
An unsigned integer representing the number of columns in a result set.
None.
MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;
if (mysql_query(&mysql,query_string))
{
// error
}
else // query succeeded, process any data returned by it
{
result = mysql_store_result(&mysql);
if (result) // there are rows
{
num_fields = mysql_num_fields(result);
// retrieve rows, then call mysql_free_result(result)
}
else // mysql_store_result() returned nothing; should it have?
{
if(mysql_field_count(&mysql) == 0)
{
// query does not return data
// (it was not a SELECT)
num_rows = mysql_affected_rows(&mysql);
}
else // mysql_store_result() should have returned data
{
fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}
}
}
An alternative is to replace the mysql_field_count(&mysql) call with
mysql_errno(&mysql). In this case, you are checking directly for an
error from mysql_store_result() rather than inferring from the value
of mysql_field_count() whether the statement was a
SELECT.
mysql_field_seek()
MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)
Sets the field cursor to the given offset. The next call to
mysql_fetch_field() will retrieve the field definition of the column
associated with that offset.
To seek to the beginning of a row, pass an offset value of zero.
The previous value of the field cursor.
None.
mysql_field_tell()
MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)
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().
The current offset of the field cursor.
None.
mysql_free_result()
void mysql_free_result(MYSQL_RES *result)
Frees the memory allocated for a result set by mysql_store_result(),
mysql_use_result(), mysql_list_dbs(), etc. When you are done
with a result set, you must free the memory it uses by calling
mysql_free_result().
Do not attempt to access a result set after freeing it.
None.
None.
mysql_get_client_info()
char *mysql_get_client_info(void)
Returns a string that represents the client library version.
A character string that represents the MySQL client library version.
None.
mysql_get_client_version()
unsigned long mysql_get_client_version(void)
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.
This function was added in MySQL 4.0.16.
An integer that represents the MySQL client library version.
None.
mysql_get_host_info()
char *mysql_get_host_info(MYSQL *mysql)
Returns a string describing the type of connection in use, including the server hostname.
A character string representing the server hostname and the connection type.
None.
mysql_get_proto_info()
unsigned int mysql_get_proto_info(MYSQL *mysql)
Returns the protocol version used by current connection.
An unsigned integer representing the protocol version used by the current connection.
None.
mysql_get_server_info()
char *mysql_get_server_info(MYSQL *mysql)
Returns a string that represents the server version number.
A character string that represents the server version number.
None.
mysql_get_server_version()
unsigned long mysql_get_server_version(MYSQL *mysql)
Returns the version number of the server as an integer.
This function was added in MySQL 4.1.0.
A number that represents the MySQL server version in this format:
major_version*10000 + minor_version *100 + sub_version
For example, 4.1.2 is returned as 40102.
This function is useful in client programs for quickly determining whether some version-specific server capability exists.
None.
mysql_hex_string()
unsigned long mysql_hex_string(char *to, const char *from, unsigned long length)
This function is used to create a legal SQL string that you can use in a SQL statement. See section 9.1.1 Strings.
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 will be 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.
mysql_hex_string() was added in MySQL 4.0.23 and 4.1.8.
char query[1000],*end;
end = strmov(query,"INSERT INTO test_table values(");
end = strmov(end,"0x");
end += mysql_hex_string(end,"What's this",11);
end = strmov(end,",0x");
end += mysql_hex_string(end,"binary data: \0\r\n",16);
*end++ = ')';
if (mysql_real_query(&mysql,query,(unsigned int) (end - query)))
{
fprintf(stderr, "Failed to insert row, Error: %s\n",
mysql_error(&mysql));
}
The strmov() function used in the example is included in the
mysqlclient library and works like strcpy() but returns a
pointer to the terminating null of the first parameter.
The length of the value placed into to, not including the
terminating null character.
None.
mysql_info()
char *mysql_info(MYSQL *mysql)
Retrieves a string providing information about the most recently executed
query, 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 query, as described here. The numbers are
illustrative only; the string will contain values appropriate for the query.
INSERT INTO ... SELECT ...
Records: 100 Duplicates: 0 Warnings: 0
INSERT INTO ... VALUES (...),(...),(...)...
Records: 3 Duplicates: 0 Warnings: 0
LOAD DATA INFILE ...
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
ALTER TABLE
Records: 3 Duplicates: 0 Warnings: 0
UPDATE
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).
A character string representing additional information about the most
recently executed query. NULL if no information is available for the
query.
None.
mysql_init()
MYSQL *mysql_init(MYSQL *mysql)
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 will be freed when
mysql_close() is called to close the connection.
An initialized MYSQL* handle. NULL if there was
insufficient memory to allocate a new object.
In case of insufficient memory, NULL is returned.
mysql_insert_id()
my_ulonglong mysql_insert_id(MYSQL *mysql)
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.
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).
mysql_insert_id() is not affected by statements such as
SELECT that return a result set.
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.
See section 12.8.3 Information Functions.
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.
Described in the preceding discussion.
None.
mysql_kill()
int mysql_kill(MYSQL *mysql, unsigned long pid)
Asks the server to kill the thread specified by pid.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_dbs()
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
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().
A MYSQL_RES result set for success. NULL if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_fields()
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)
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().
A MYSQL_RES result set for success. NULL if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_processes()
MYSQL_RES *mysql_list_processes(MYSQL *mysql)
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().
A MYSQL_RES result set for success. NULL if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_tables()
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
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().
A MYSQL_RES result set for success. NULL if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_num_fields()
unsigned int mysql_num_fields(MYSQL_RES *result)
Or:
unsigned int mysql_num_fields(MYSQL *mysql)
The second form doesn't work on MySQL 3.22.24 or newer. To pass a
MYSQL* argument, you must use
unsigned int mysql_field_count(MYSQL *mysql) instead.
Returns the number of columns in a result set.
Note that you can get the number of columns either from a pointer to a result
set or to a connection handle. You would use the connection handle if
mysql_store_result() or mysql_use_result() returned
NULL (and thus you have no result set pointer). In this case, you can
call mysql_field_count() to determine whether
mysql_store_result() should have produced a non-empty result. This
allows the client program to take proper action without knowing whether or
not the query was a SELECT (or SELECT-like) statement. The
example shown here illustrates how this may be done.
See section 21.2.13.1 Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success.
An unsigned integer representing the number of columns in a result set.
None.
MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;
if (mysql_query(&mysql,query_string))
{
// error
}
else // query succeeded, process any data returned by it
{
result = mysql_store_result(&mysql);
if (result) // there are rows
{
num_fields = mysql_num_fields(result);
// retrieve rows, then call mysql_free_result(result)
}
else // mysql_store_result() returned nothing; should it have?
{
if (mysql_errno(&mysql))
{
fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}
else if (mysql_field_count(&mysql) == 0)
{
// query does not return data
// (it was not a SELECT)
num_rows = mysql_affected_rows(&mysql);
}
}
}
An alternative (if you know that your query should have returned a result set)
is to replace the mysql_errno(&mysql) call with a check whether
mysql_field_count(&mysql) is = 0. This will happen only if something
went wrong.
mysql_num_rows()
my_ulonglong mysql_num_rows(MYSQL_RES *result)
Returns the number of rows in the result set.
The use of mysql_num_rows() depends on whether you use
mysql_store_result() or mysql_use_result() to return the result
set. If you use mysql_store_result(), mysql_num_rows() may be
called immediately. If you use mysql_use_result(),
mysql_num_rows() will not return the correct value until all the rows
in the result set have been retrieved.
The number of rows in the result set.
None.
mysql_options()
int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg)
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, then
arg should point to the value of the integer.
Possible option values:
| Option | Argument Type | Function |
MYSQL_INIT_COMMAND | char * | Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting. |
MYSQL_OPT_COMPRESS | Not used | Use the compressed client/server protocol. |
MYSQL_OPT_CONNECT_TIMEOUT | unsigned int * | Connect timeout in seconds. |
MYSQL_OPT_LOCAL_INFILE | optional pointer to uint | If no pointer is given or if pointer points to an unsigned int != 0 the command LOAD LOCAL INFILE is enabled.
|
MYSQL_OPT_NAMED_PIPE | Not used | Use named pipes to connect to a MySQL server on NT. |
MYSQL_OPT_PROTOCOL | unsigned int * | Type of protocol to use. Should be one of the enum values of mysql_protocol_type defined in `mysql.h'. New in 4.1.0.
|
MYSQL_OPT_READ_TIMEOUT | unsigned int * | Timeout for reads from server (works currently only on Windows on TCP/IP connections). New in 4.1.1. |
MYSQL_OPT_WRITE_TIMEOUT | unsigned int * | Timeout for writes to server (works currently only on Windows on TCP/IP connections). New in 4.1.1. |
MYSQL_READ_DEFAULT_FILE | char * | Read options from the named option file instead of from `my.cnf'. |
MYSQL_READ_DEFAULT_GROUP | char * | Read options from the named group from `my.cnf' or the file specified with MYSQL_READ_DEFAULT_FILE.
|
MYSQL_REPORT_DATA_TRUNCATION | my_bool * | Enable or disable reporting of data truncation errors for prepared statements via MYSQL_BIND.error. (Default: disabled) New in 5.0.3.
|
MYSQL_SECURE_AUTH | my_bool* | Whether to connect to a server that does not support the new 4.1.1 password hashing. New in 4.1.1. |
MYSQL_SET_CHARSET_DIR | char* | The pathname to the directory that contains character set definition files. |
MYSQL_SET_CHARSET_NAME | char* | The name of the character set to use as the default character set. |
MYSQL_SHARED_MEMORY_BASE_NAME | char* | Named of shared memory object for communication to server. Should be same as the option -shared-memory-base-name used for the mysqld server you want's to connect to. New in 4.1.0.
|
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 | Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting. |
interactive-timeout | Same as specifying CLIENT_INTERACTIVE to mysql_real_connect(). See section 21.2.3.44 mysql_real_connect().
|
local-infile[=(0|1)] | If no argument or argument != 0 then enable use of LOAD DATA LOCAL.
|
max_allowed_packet | Max size of packet client can read from server. |
multi-results | Allow multiple result sets from multiple-statement executions or stored procedures. New in 4.1.1. |
multi-statements | Allow the client to send multiple statements in a single string (separated by `;'). New in 4.1.9. |
password | Default password. |
pipe | Use named pipes to connect to a MySQL server on NT. |
protocol={TCP | SOCKET | PIPE | MEMORY} | The protocol to use when connecting to server (New in 4.1) |
port | Default port number. |
return-found-rows | Tell mysql_info() to return found rows instead of updated rows when using UPDATE.
|
shared-memory-base-name=name | Shared memory name to use to connect to server (default is "MYSQL"). New in MySQL 4.1. |
socket | Default socket file. |
user | Default user. |
Note that timeout has been replaced by connect-timeout, but
timeout will still work for a while.
For more information about option files, see section 4.3.2 Using Option Files.
Zero for success. Non-zero if you used an unknown option.
MYSQL mysql;
mysql_init(&mysql);
mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
fprintf(stderr, "Failed to connect to database: Error: %s\n",
mysql_error(&mysql));
}
This code requests the client to use the compressed client/server protocol and
read the additional options from the odbc section in the `my.cnf'
file.
mysql_ping()
int mysql_ping(MYSQL *mysql)
Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.
Zero if the server is alive. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_UNKNOWN_ERROR
mysql_query()
int mysql_query(MYSQL *mysql, const char *query)
Executes the SQL query pointed to by the null-terminated string query.
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 21.2.9 C API Handling of Multiple Query Execution.
mysql_query() cannot be used for queries that contain binary data; you
should use mysql_real_query() instead. (Binary data may contain the
`\0' character, which mysql_query() interprets as the end of the
query string.)
If you want to know whether the query should return a result set, you can
use mysql_field_count() to check for this.
See section 21.2.3.20 mysql_field_count().
Zero if the query was successful. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_real_connect()
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host,
const char *user, const char *passwd, const char *db,
unsigned int port, const char *unix_socket,
unsigned long client_flag)
mysql_real_connect() attempts to establish a connection to a
MySQL database engine running on host.
mysql_real_connect() must complete successfully before you can execute
any of the other API functions, with the exception of
mysql_get_client_info().
The parameters are specified as follows:
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 21.2.3.41 mysql_options().
host may be either a hostname or an IP address. If
host is NULL or the string "localhost", a connection to
the local host is assumed. If the OS supports sockets (Unix) or named pipes
(Windows), they are used instead of TCP/IP to connect to the server.
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 section 22.1.9.2 Configuring a MyODBC DSN on Windows.
passwd parameter contains the password for user. If
passwd is NULL, only entries in the user table for the
user that have a blank (empty) password field will be checked for a match. This
allows the database administrator to set up the MySQL privilege
system in such a way that users get different privileges depending on whether
or not they have specified a password.
Note: Do not attempt to encrypt the password before calling
mysql_real_connect(); password encryption is handled automatically by
the client API.
db is the database name.
If db is not NULL, the connection will set the default
database to this value.
port is not 0, the value will be used as the port number
for the TCP/IP connection. Note that the host parameter
determines the type of the connection.
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.
client_flag is usually 0, but can be set to a combination
of the following flags in very special circumstances:
| Flag Name | Flag Nescription |
CLIENT_COMPRESS | Use compression protocol. |
CLIENT_FOUND_ROWS | Return the number of found (matched) rows, not the number of affected rows. |
CLIENT_IGNORE_SPACE | Allow spaces after function names. Makes all functions names reserved words. |
CLIENT_INTERACTIVE | Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. The client's session wait_timeout variable will be set to the value of the session interactive_timeout variable.
|
CLIENT_LOCAL_FILES | Enable LOAD DATA LOCAL handling.
|
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. New in 4.1. |
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. New in 4.1.
|
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. |
A MYSQL* connection handle if the connection was successful,
NULL if the connection was unsuccessful. For a successful connection,
the return value is the same as the value of the first parameter.
CR_CONN_HOST_ERROR
CR_CONNECTION_ERROR
CR_IPSOCK_ERROR
CR_OUT_OF_MEMORY
CR_SOCKET_CREATE_ERROR
CR_UNKNOWN_HOST
CR_VERSION_ERROR
--old-protocol option.
CR_NAMEDPIPEOPEN_ERROR
CR_NAMEDPIPEWAIT_ERROR
CR_NAMEDPIPESETSTATE_ERROR
CR_SERVER_LOST
connect_timeout > 0 and it took longer than connect_timeout
seconds to connect to the server or if the server died while executing the
init-command.
MYSQL mysql;
mysql_init(&mysql);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
fprintf(stderr, "Failed to connect to database: Error: %s\n",
mysql_error(&mysql));
}
By using mysql_options() the MySQL library will read the
[client] and [your_prog_name] sections in the `my.cnf'
file which will ensure that your program will work, even if someone has
set up MySQL in some non-standard way.
Note that upon connection, mysql_real_connect() sets the
reconnect flag (part of the MYSQL structure) to a value of
1 in versions of the API strictly older than 5.0.3, of 0 in newer
versions. A value of 1 for this flag indicates, in the event that a
query cannot be performed because of a lost connection, to try reconnecting to
the server before giving up.
mysql_real_escape_string()
unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length)
Note that mysql must be a valid, open connection. This is needed
because the escaping depends on the character-set in use by the server.
This function is used to create a legal SQL string that you can use in a SQL statement. See section 9.1.1 Strings.
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 will be a
null-terminated string. The return value is the length of the encoded
string, not including the terminating null character.
char query[1000],*end;
end = strmov(query,"INSERT INTO test_table values(");
*end++ = '\'';
end += mysql_real_escape_string(&mysql, end,"What's this",11);
*end++ = '\'';
*end++ = ',';
*end++ = '\'';
end += mysql_real_escape_string(&mysql, end,"binary data: \0\r\n",16);
*end++ = '\'';
*end++ = ')';
if (mysql_real_query(&mysql,query,(unsigned int) (end - query)))
{
fprintf(stderr, "Failed to insert row, Error: %s\n",
mysql_error(&mysql));
}
The strmov() function used in the example is included in the
mysqlclient library and works like strcpy() but returns a
pointer to the terminating null of the first parameter.
The length of the value placed into to, not including the
terminating null character.
None.
mysql_real_query()
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
Executes the SQL query pointed to by query, 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 21.2.9 C API Handling of Multiple Query Execution.
You must use mysql_real_query() rather than
mysql_query() for queries that contain binary data, because binary data
may contain the `\0' character. In addition, mysql_real_query()
is faster than mysql_query() because it does not call strlen() on
the query string.
If you want to know whether the query should return a result set, you can
use mysql_field_count() to check for this.
See section 21.2.3.20 mysql_field_count().
Zero if the query was successful. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_reload()
int mysql_reload(MYSQL *mysql)
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.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_row_seek()
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset)
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().
The previous value of the row cursor. This value may be passed to a
subsequent call to mysql_row_seek().
None.
mysql_row_tell()
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)
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().
The current offset of the row cursor.
None.
mysql_select_db()
int mysql_select_db(MYSQL *mysql, const char *db)
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.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_set_server_option()
int mysql_set_server_option(MYSQL *mysql, enum enum_mysql_set_option option)
Enables or disables an option for the connection. option can have one
of the following values:
| MYSQL_OPTION_MULTI_STATEMENTS_ON | Enable multi statement support. |
| MYSQL_OPTION_MULTI_STATEMENTS_OFF | Disable multi statement support. |
This function was added in MySQL 4.1.1.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
ER_UNKNOWN_COM_ERROR
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.
mysql_shutdown()
int mysql_shutdown(MYSQL *mysql, enum enum_shutdown_level shutdown_level)
Asks the database server to shut down. The connected user must have
SHUTDOWN privileges.
The shutdown_level argument was added in MySQL 4.1.3 (and 5.0.1). The
MySQL server currently supports only one type (level of gracefulness) of
shutdown; shutdown_level must be equal to
SHUTDOWN_DEFAULT. Later we will add more levels and then the
shutdown_level argument will enable to choose the desired level.
MySQL servers and MySQL clients before and after 4.1.3 are compatible; MySQL
servers newer than 4.1.3 accept the mysql_shutdown(MYSQL *mysql) call,
and MySQL servers older than 4.1.3 accept the new mysql_shutdown()
call. But dynamically linked executables which have been compiled with older
versions of libmysqlclient headers, and call mysql_shutdown(),
need to be used with the old libmysqlclient dynamic library.
The shutdown process is described in section 5.3 The MySQL Server Shutdown Process.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_sqlstate()
const char *mysql_sqlstate(MYSQL *mysql)
Returns a null-terminated string containing the SQLSTATE error code for the
last error. 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 section 23 Error Handling in MySQL.
Note that not all MySQL errors are yet mapped to SQLSTATE's.
The value 'HY000' (general error) is used
for unmapped errors.
This function was added to MySQL 4.1.1.
A null-terminated character string containing the SQLSTATE error code.
See section 21.2.3.12 mysql_errno().
See section 21.2.3.13 mysql_error().
See section 21.2.7.26 mysql_stmt_sqlstate().
mysql_ssl_set()
int mysql_ssl_set(MYSQL *mysql, const char *key,
const char *cert, const char *ca,
const char *capath, const char *cipher)
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.
This function always returns 0. If SSL setup is incorrect,
mysql_real_connect() will return an error when you attempt to connect.
mysql_stat()
char *mysql_stat(MYSQL *mysql)
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.
A character string describing the server status. NULL if an
error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_store_result()
MYSQL_RES *mysql_store_result(MYSQL *mysql)
You must call mysql_store_result() or mysql_use_result()
for every query that successfully retrieves data (SELECT,
SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, and so forth).
You don't have to call mysql_store_result() or
mysql_use_result() for other queries, but it will not do any
harm or cause any notable performance if you call mysql_store_result()
in all cases. You can detect if the query didn't have a result set by
checking if mysql_store_result() returns 0 (more about this later on).
If you want to know whether the query should return a result set, you can
use mysql_field_count() to check for this.
See section 21.2.3.20 mysql_field_count().
mysql_store_result() reads the entire result of a query to the client,
allocates a MYSQL_RES structure, and places the result into this
structure.
mysql_store_result() returns a null pointer if the query didn't return
a result set (if the query was, for example, an INSERT statement).
mysql_store_result() also returns a null pointer if reading of the
result set failed. You can check whether an error occurred by checking if
mysql_error() returns a non-empty string, if
mysql_errno() returns non-zero, or if 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.)
Once you have called mysql_store_result() and got a result back
that isn't a null pointer, you may call mysql_num_rows() to find
out how many rows are in the result set.
You can call mysql_fetch_row() to fetch rows from the result set,
or mysql_row_seek() and mysql_row_tell() to obtain or
set the current row position within the result set.
You must call mysql_free_result() once you are done with the result
set.
See section 21.2.13.1 Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success.
A MYSQL_RES result structure with the results. NULL if
an error occurred.
mysql_store_result() resets mysql_error() and
mysql_errno() if it succeeds.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_thread_id()
unsigned long mysql_thread_id(MYSQL *mysql)
Returns the thread ID of the current connection. This value can be used as
an argument to mysql_kill() to kill the thread.
If the connection is lost and you reconnect with mysql_ping(), the
thread ID will change. This means you should not get the thread ID and store
it for later. You should get it when you need it.
The thread ID of the current connection.
None.
mysql_use_result()
MYSQL_RES *mysql_use_result(MYSQL *mysql)
You must call mysql_store_result() or mysql_use_result() for
every query that successfully retrieves data (SELECT, SHOW,
DESCRIBE, EXPLAIN).
mysql_use_result() initiates a result set retrieval but does not
actually read the result set into the client like mysql_store_result()
does. Instead, each row must be retrieved individually by making calls to
mysql_fetch_row(). This reads the result of a query directly from the
server without storing it in a temporary table or local buffer, which is
somewhat faster and uses much less memory than mysql_store_result().
The client will allocate 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 will tie up the server and prevent other threads from updating any
tables from which the data is being fetched.
When using mysql_use_result(), you must execute
mysql_fetch_row() until a NULL value is returned, otherwise, the
unfetched rows will be returned as part of the result set for your next
query. The C API will give the error Commands out of sync; you can't
run this command now if you forget to do this!
You may not use mysql_data_seek(), mysql_row_seek(),
mysql_row_tell(), mysql_num_rows(), or
mysql_affected_rows() with a result returned from
mysql_use_result(), nor may you issue other queries until the
mysql_use_result() has finished. (However, after you have fetched all
the rows, mysql_num_rows() will accurately return the number of rows
fetched.)
You must call mysql_free_result() once you are done with the result
set.
A MYSQL_RES result structure. NULL if an error occurred.
mysql_use_result() resets mysql_error() and
mysql_errno() if it succeeds.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_warning_count()
unsigned int mysql_warning_count(MYSQL *mysql)
Returns the number of warnings generated during execution of the previous SQL statement.
This function was added in MySQL 4.1.0.
The warning count.
None.
mysql_commit()
my_bool mysql_commit(MYSQL *mysql)
Commits the current transaction.
This function was added in MySQL 4.1.0.
Zero if successful. Non-zero if an error occurred.
None.
mysql_rollback()
my_bool mysql_rollback(MYSQL *mysql)
Rolls back the current transaction.
This function was added in MySQL 4.1.0.
Zero if successful. Non-zero if an error occurred.
None.
mysql_autocommit()
my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)
Sets autocommit mode on if mode is 1, off if mode is 0.
This function was added in MySQL 4.1.0.
Zero if successful. Non-zero if an error occurred.
None.
mysql_more_results()
my_bool mysql_more_results(MYSQL *mysql)
Returns true if more results exist from the currently executed query,
and the application must call mysql_next_result() to fetch the
results.
This function was added in MySQL 4.1.0.
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.
See section 21.2.9 C API Handling of Multiple Query Execution.
See section 21.2.3.64 mysql_next_result().
None.
mysql_next_result()
int mysql_next_result(MYSQL *mysql)
If more query results exist, mysql_next_result() reads the
next query results and returns the status back to application.
You must call mysql_free_result() for the preceding
query if it 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 query.
This means that you can now call mysql_store_result(),
mysql_warning_count(), mysql_affected_rows(), and so forth.
If mysql_next_result() returns an error, no other statements will
be executed and there are no more results to fetch.
See section 21.2.9 C API Handling of Multiple Query Execution.
This function was added in MySQL 4.1.0.
| Return Value | Description |
| 0 | Successful and there are more results |
| -1 | Successful and there are no more results |
| >0 | An error occurred |
CR_COMMANDS_OUT_OF_SYNC
mysql_use_result() for a previous result set.
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
As of MySQL 4.1, the 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, primarly 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.
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 yet supported.
Note: Some incompatible changes were made in MySQL 4.1.2. See section 21.2.7 C API Prepared Statement Function Descriptions for details.
Prepared statements mainly use the MYSQL_STMT and
MYSQL_BIND data structures. A third structure, MYSQL_TIME,
is used to transfer temporal data.
MYSQL_STMT
mysql_stmt_init(),
which returns a statement handle, that is, a pointer to a
MYSQL_STMT.
The handle is used for all subsequent statement-related functions until you
close it with mysql_stmt_close().
The MYSQL_STMT structure has no members that are for application
use.
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
mysql_stmt_bind_param() to bind parameter data values to
buffers for use by mysql_stmt_execute(). For output, it is
used with mysql_stmt_bind_result() to bind result set
buffers for use in fetching rows with mysql_stmt_fetch().
The MYSQL_BIND structure contains the following members for
use by application programs.
Each is used both for input and for output, although sometimes for different
purposes depending on the direction of data transfer.
enum enum_field_types buffer_type
buffer_type values are listed
later in this section. For input, buffer_type indicates what type of
value you are binding to a statement parameter. For output, it indicates what
type of value you expect to receive in a result buffer.
void *buffer
buffer should point to a variable of the proper
C type. (If you are associating the variable with a column that has the
UNSIGNED attribute, the variable should be an unsigned C type.
Indicate whether the variable is signed or unsigned by using the
is_unsigned member, described later in this list.)
For date and time column types, buffer should point to a
MYSQL_TIME structure. For character and binary string column types,
buffer should point to a character buffer.
unsigned long buffer_length
*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(),
or the maximum number of data bytes that can be fetched into the buffer
when used with mysql_stmt_bind_result().
unsigned long *length
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
length of the parameter value stored in *buffer; this is used by
mysql_stmt_execute().
For output value binding, mysql_stmt_fetch() places
the length of the column value that is returned
into the variable that length points to.
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
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. For
output, this value will be set to true after you fetch a row if the result
set column value returned from the statement is NULL.
my_bool is_unsigned
MYSQL_TYPE_TINY, MYSQL_TYPE_SHORT,
MYSQL_TYPE_LONG, and MYSQL_TYPE_LONGLONG type codes.)
is_unsigned
should be set to true for unsigned types and false for signed types.
my_bool error
mysql_options()
with the MYSQL_REPORT_DATA_TRUNCATION option. When 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_BIND structure, you should zero its contents
to initialize it, and then set the members just described 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));
MYSQL_TIME
DATE, TIME,
DATETIME, and TIMESTAMP data directly to and from the server.
This is done by setting the buffer_type member of a MYSQL_BIND
structure to one of the temporal types, and setting the buffer member
to point to a MYSQL_TIME structure.
The MYSQL_TIME structure contains the following members:
unsigned int year
unsigned int month
unsigned int day
unsigned int hour
unsigned int minute
unsigned int second
my_bool neg
unsigned long second_part
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 21.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.
The table also shows those SQL types that correspond most closely to each
buffer_type value, and, for numeric and temporal types, the
corresponding C type.
buffer_type Value | SQL Type | C Type |
MYSQL_TYPE_TINY | TINYINT | char
|
MYSQL_TYPE_SHORT | SMALLINT | short int
|
MYSQL_TYPE_LONG | INT | int
|
MYSQL_TYPE_LONGLONG | BIGINT | long long int
|
MYSQL_TYPE_FLOAT | FLOAT | float
|
MYSQL_TYPE_DOUBLE | DOUBLE | double
|
MYSQL_TYPE_TIME | TIME | MYSQL_TIME
|
MYSQL_TYPE_DATE | DATE | MYSQL_TIME
|
MYSQL_TYPE_DATETIME | DATETIME | MYSQL_TIME
|
MYSQL_TYPE_TIMESTAMP | TIMESTAMP | MYSQL_TIME
|
MYSQL_TYPE_STRING | CHAR | |
MYSQL_TYPE_VAR_STRING | VARCHAR | |
MYSQL_TYPE_TINY_BLOB | TINYBLOB/TINYTEXT | |
MYSQL_TYPE_BLOB | BLOB/TEXT | |
MYSQL_TYPE_MEDIUM_BLOB | MEDIUMBLOB/MEDIUMTEXT | |
MYSQL_TYPE_LONG_BLOB | LONGBLOB/LONGTEXT |
Implicit type conversion may be performed in both directions.
Note: Some incompatible changes were made in MySQL 4.1.2. See section 21.2.7 C API Prepared Statement Function Descriptions for details.
The functions available for prepared statement processing are summarized here and described in greater detail in a later section. See section 21.2.7 C API Prepared Statement Function Descriptions.
| Function | Description |
| mysql_stmt_affected_rows() |
Returns the number of rows changes, deleted, or inserted by prepared
UPDATE, DELETE, or INSERT statement.
|
| mysql_stmt_attr_get() | Get value of an attribute for a prepared statement. |
| mysql_stmt_attr_set() | Sets an attribute for a prepared statement. |
| mysql_stmt_bind_param() | Associates application data buffers with the parameter markers in a prepared SQL statement. |
| mysql_stmt_bind_result() | Associates application data buffers with columns in the result set. |
| mysql_stmt_close() | Frees memory used by prepared statement. |
| mysql_stmt_data_seek() | Seeks to an arbitrary row number in a statement result set. |
| mysql_stmt_errno() | Returns the error number for the last statement execution. |
| mysql_stmt_error() | Returns the error message for the last statement execution. |
| mysql_stmt_execute() | Executes the prepared statement. |
| mysql_stmt_fetch() | Fetches the next row of data from the result set and returns data for all bound columns. |
| mysql_stmt_fetch_column() | Fetch data for one column of the current row of the result set. |
| mysql_stmt_field_count() | Returns the number of result columns for the most recent statement. |
| mysql_stmt_free_result() | Free the resources allocated to the statement handle. |
| mysql_stmt_init() |
Allocates memory for MYSQL_STMT structure and initializes it.
|
| mysql_stmt_insert_id() |
Returns the ID generated for an AUTO_INCREMENT column by prepared
statement.
|
| mysql_stmt_num_rows() | Returns total rows from the statement buffered result set. |
| mysql_stmt_param_count() | Returns the number of parameters in a prepared SQL statement. |
| mysql_stmt_param_metadata() | Return parameter metadata in the form of a result set. |
| mysql_stmt_prepare() | Prepares an SQL string for execution. |
| mysql_stmt_reset() | Reset the statement buffers in the server. |
| mysql_stmt_result_metadata() | Returns prepared statement metadata in the form of a result set. |
| mysql_stmt_row_seek() |
Seeks to a row offset in a statement result set, using value returned from
mysql_stmt_row_tell().
|
| mysql_stmt_row_tell() | Returns the statement row cursor position. |
| mysql_stmt_send_long_data() | Sends long data in chunks to server. |
| mysql_stmt_sqlstate() | Returns the SQLSTATE error code for the last statement execution. |
| mysql_stmt_store_result() | Retrieves the complete result set to the client. |
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() will also return 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() will automatically return data to these
buffers. This is row-by-row fetching.
You can also send the text or binary data in chunks to server using
mysql_stmt_send_long_data().
See section 21.2.7.25 mysql_stmt_send_long_data().
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().
To prepare and execute a statement, an application follows these steps:
msyql_stmt_init().
To prepare the statement on the server, call mysql_stmt_prepare() and
pass it a string containing the SQL statement.
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.
mysql_stmt_bind_param(). All
parameters must be set. Otherwise, statement execution will return an error or
produce unexpected results.
mysql_stmt_execute() to execute the statement.
mysql_stmt_bind_result().
mysql_stmt_fetch() repeatedly until no more rows are found.
When mysql_stmt_prepare() is called, the MySQL client/server protocol
performs these actions:
When mysql_stmt_execute() is called, the MySQL
client/server protocol performs these actions:
When mysql_stmt_fetch() is called, the MySQL client/server protocol
performs these actions:
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.
To prepare and execute queries, use the functions in the following sections.
In MySQL 4.1.2, the names of several prepared statement functions were changed:
| Old Name | New Name |
mysql_bind_param() | mysql_stmt_bind_param()
|
mysql_bind_result() | mysql_stmt_bind_result()
|
mysql_prepare() | mysql_stmt_prepare()
|
mysql_execute() | mysql_stmt_execute()
|
mysql_fetch() | mysql_stmt_fetch()
|
mysql_fetch_column() | mysql_stmt_fetch_column()
|
mysql_param_count() | mysql_stmt_param_count()
|
mysql_param_result() | mysql_stmt_param_metadata()
|
mysql_get_metadata() | mysql_stmt_result_metadata()
|
mysql_send_long_data() | mysql_stmt_send_long_data()
|
All functions that operate with a MYSQL_STMT structure now begin with
the prefix mysql_stmt_.
Also in 4.1.2, the signature of the mysql_stmt_prepare() function was
changed to int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query,
unsigned long length). To create a MYSQL_STMT handle, you should
use the mysql_stmt_init() function.
mysql_stmt_affected_rows()
my_ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt)
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().
This function was added in MySQL 4.1.0.
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).
See section 21.2.3.1 mysql_affected_rows() for additional
information on the return value.
None.
For the usage of mysql_stmt_affected_rows(), refer to the Example
from section 21.2.7.10 mysql_stmt_execute().
mysql_stmt_attr_get()
int mysql_stmt_attr_get(MYSQL_STMT *stmt, enum enum_stmt_attr_type option, void *arg)
Can be used to get the current value for a statement attribute.
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.
See mysql_stmt_attr_set() for a list of options and option
types. See section 21.2.7.3 mysql_stmt_attr_set().
This function was added in MySQL 4.1.2.
0 if okay. Non-zero if option is unknown.
None.
mysql_stmt_attr_set()
int mysql_stmt_attr_set(MYSQL_STMT *stmt, enum enum_stmt_attr_type option, const void *arg)
Can be used to set affect behavior for a 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 options 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().
|
This function was added in MySQL 4.1.2.
0 if okay. Non-zero if option is unknown.
None.
mysql_stmt_bind_param()
my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)
mysql_stmt_bind_param() is used to bind 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 an 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:
MYSQL_BIND bind[3];
The members of each MYSQL_BIND element that should be set are described
in section 21.2.5 C API Prepared Statement Data types.
This function was added in MySQL 4.1.2.
Zero if the bind was successful. Non-zero if an error occurred.
CR_INVALID_BUFFER_USE
CR_UNSUPPORTED_PARAM_TYPE
buffer_type value is
illegal or is not one of the supported types.
CR_OUT_OF_MEMORY
CR_UNKNOWN_ERROR
For the usage of mysql_stmt_bind_param(), refer to the Example from
section 21.2.7.10 mysql_stmt_execute().
mysql_stmt_bind_result()
my_bool mysql_stmt_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind)
mysql_stmt_bind_result() is used to associate (bind) 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
an 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 the application
binds the columns to a different set of buffers. The protocol does
not place data into the newly bound
buffers until 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 the address of the length buffer.
The members of each MYSQL_BIND element that should be set are described
in section 21.2.5 C API Prepared Statement Data types.
This function was added in MySQL 4.1.2.
Zero if the bind was successful. Non-zero if an error occurred.
CR_UNSUPPORTED_PARAM_TYPE
buffer_type value is
illegal or is not one of the supported types.
CR_OUT_OF_MEMORY
CR_UNKNOWN_ERROR
For the usage of mysql_stmt_bind_result(), refer to the Example from
section 21.2.7.13 mysql_stmt_fetch().
mysql_stmt_close()
my_bool mysql_stmt_close(MYSQL_STMT *)
Closes the prepared statement. mysql_stmt_close() also
deallocates the statement handle pointed to by stmt.
If the current statement has pending or unread results, this function cancels them so that the next query can be executed.
This function was added in MySQL 4.1.0.
Zero if the statement was freed successfully. Non-zero if an error occurred.
CR_SERVER_GONE_ERROR
CR_UNKNOWN_ERROR
For the usage of mysql_stmt_close(), refer to the Example from
section 21.2.7.10 mysql_stmt_execute().
mysql_stmt_data_seek()
void mysql_stmt_data_seek(MYSQL_STMT *stmt, my_ulonglong offset)
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().
This function was added in MySQL 4.1.1.
None.
None.
mysql_stmt_errno()
unsigned int mysql_stmt_errno(MYSQL_STMT *stmt)
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'. In the
MySQL source distribution you can find a complete list of
error messages and error numbers in the file `Docs/mysqld_error.txt'.
The server error codes also are listed at section 23 Error Handling in MySQL.
This function was added in MySQL 4.1.0.
An error code value. Zero if no error occurred.
None.
mysql_stmt_error()
const char *mysql_stmt_error(MYSQL_STMT *stmt)
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:
if (mysql_stmt_errno(stmt))
{
// an error occurred
}
if (mysql_stmt_error(stmt)[0])
{
// an error occurred
}
The language of the client error messages may be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages.
This function was added in MySQL 4.1.0.
A character string that describes the error. An empty string if no error occurred.
None.
mysql_stmt_execute()
int mysql_stmt_execute(MYSQL_STMT *stmt)
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 result set query such as
SELECT, 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 21.2.7.13 mysql_stmt_fetch().
This function was added in MySQL 4.1.2.
Zero if execution was successful. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
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.
#define STRING_SIZE 50
#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"
#define CREATE_SAMPLE_TABLE "CREATE TABLE test_table(col1 INT,\
col2 VARCHAR(40),\
col3 SMALLINT,\
col4 TIMESTAMP)"
#define INSERT_SAMPLE "INSERT INTO test_table(col1,col2,col3) VALUES(?,?,?)"
MYSQL_STMT *stmt;
MYSQL_BIND bind[3];
my_ulonglong affected_rows;
int param_count;
short small_data;
int int_data;
char str_data[STRING_SIZE];
unsigned long str_length;
my_bool is_null;
if (mysql_query(mysql, DROP_SAMPLE_TABLE))
{
fprintf(stderr, " DROP TABLE failed\n");
fprintf(stderr, " %s\n", mysql_error(mysql));
exit(0);
}
if (mysql_query(mysql, CREATE_SAMPLE_TABLE))
{
fprintf(stderr, " CREATE TABLE failed\n");
fprintf(stderr, " %s\n", mysql_error(mysql));
exit(0);
}
/* Prepare an INSERT query with 3 parameters */
/* (the TIMESTAMP column is not named; the server */
/* will set it to the current date and time) */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE)))
{
fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, " prepare, INSERT successful\n");
/* Get the parameter count from the statement */
param_count= mysql_stmt_param_count(stmt);
fprintf(stdout, " total parameters in INSERT: %d\n", param_count);
if (param_count != 3) /* validate parameter count */
{
fprintf(stderr, " invalid parameter count returned by MySQL\n");
exit(0);
}
/* Bind the data for all 3 parameters */
memset(bind, 0, sizeof(bind));
/* INTEGER PARAM */
/* This is a number type, so there is no need to specify buffer_length */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= 0;
bind[0].length= 0;
/* STRING PARAM */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= 0;
bind[1].length= &str_length;
/* SMALLINT PARAM */
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer= (char *)&small_data;
bind[2].is_null= &is_null;
bind[2].length= 0;
/* Bind the buffers */
if (mysql_stmt_bind_param(stmt, bind))
{
fprintf(stderr, " mysql_stmt_bind_param() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Specify the data values for the first row */
int_data= 10; /* integer */
strncpy(str_data, "MySQL", STRING_SIZE); /* string */
str_length= strlen(str_data);
/* INSERT SMALLINT data as NULL */
is_null= 1;
/* Execute the INSERT statement - 1*/
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Get the total number of affected rows */
affected_rows= mysql_stmt_affected_rows(stmt);
fprintf(stdout, " total affected rows(insert 1): %lu\n",
(unsigned long) affected_rows);
if (affected_rows != 1) /* validate affected rows */
{
fprintf(stderr, " invalid affected rows by MySQL\n");
exit(0);
}
/* Specify data values for second row, then re-execute the statement */
int_data= 1000;
strncpy(str_data, "The most popular Open Source database", STRING_SIZE);
str_length= strlen(str_data);
small_data= 1000; /* smallint */
is_null= 0; /* reset */
/* Execute the INSERT statement - 2*/
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, " mysql_stmt_execute, 2 failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Get the total rows affected */
affected_rows= mysql_stmt_affected_rows(stmt);
fprintf(stdout, " total affected rows(insert 2): %lu\n",
(unsigned long) affected_rows);
if (affected_rows != 1) /* validate affected rows */
{
fprintf(stderr, " invalid affected rows by MySQL\n");
exit(0);
}
/* Close the statement */
if (mysql_stmt_close(stmt))
{
fprintf(stderr, " failed while closing the statement\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
Note: For complete examples on the use of prepared statement functions, refer to the file `tests/client_test.c'. This file can be obtained from a MySQL source distribution or from the BitKeeper source repository.
mysql_stmt_free_result()
my_bool mysql_stmt_free_result(MYSQL_STMT *stmt)
Releases memory associated with the result set produced by execution of the prepared statement.
This function was added in MySQL 4.1.1.
Zero if the result set was freed successfully. Non-zero if an error occurred.
mysql_stmt_insert_id()
my_ulonglong mysql_stmt_insert_id(MYSQL_STMT *stmt)
Returns the value generated for an AUTO_INCREMENT column by the
prepared INSERT or UPDATE statement. Use this function after
you have executed prepared INSERT statement into a table which
contains an AUTO_INCREMENT field.
See section 21.2.3.33 mysql_insert_id() for more
information.
This function was added in MySQL 4.1.2.
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.
None.
mysql_stmt_fetch()
int mysql_stmt_fetch(MYSQL_STMT *stmt)
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
|
This function was added in MySQL 4.1.2.
| 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 not returned unless truncation reporting is
enabled with mysql_options(). To determine which parameters were
truncated when this value is returned, check the error members of
the MYSQL_BIND parameter structures.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
CR_UNSUPPORTED_PARAM_TYPE
MYSQL_TYPE_DATE,
MYSQL_TYPE_TIME,
MYSQL_TYPE_DATETIME,
or
MYSQL_TYPE_TIMESTAMP,
but the data type is not DATE, TIME, DATETIME, or
TIMESTAMP.
mysql_stmt_bind_result().
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 21.2.7.10 mysql_stmt_execute().)
The mysql variable is assumed to be a valid connection handle.
#define STRING_SIZE 50
#define SELECT_SAMPLE "SELECT col1, col2, col3, col4 FROM test_table"
MYSQL_STMT *stmt;
MYSQL_BIND bind[4];
MYSQL_RES *prepare_meta_result;
MYSQL_TIME ts;
unsigned long length[4];
int param_count, column_count, row_count;
short small_data;
int int_data;
char str_data[STRING_SIZE];
my_bool is_null[4];
/* Prepare a SELECT query to fetch data from test_table */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
{
fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, " prepare, SELECT successful\n");
/* Get the parameter count from the statement */
param_count= mysql_stmt_param_count(stmt);
fprintf(stdout, " total parameters in SELECT: %d\n", param_count);
if (param_count != 0) /* validate parameter count */
{
fprintf(stderr, " invalid parameter count returned by MySQL\n");
exit(0);
}
/* Fetch result set meta information */
prepare_meta_result = mysql_stmt_result_metadata(stmt);
if (!prepare_meta_result)
{
fprintf(stderr,
" mysql_stmt_result_metadata(), returned no meta information\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Get total columns in the query */
column_count= mysql_num_fields(prepare_meta_result);
fprintf(stdout, " total columns in SELECT statement: %d\n", column_count);
if (column_count != 4) /* validate column count */
{
fprintf(stderr, " invalid column count returned by MySQL\n");
exit(0);
}
/* Execute the SELECT query */
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, " mysql_stmt_execute(), failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Bind the result buffers for all 4 columns before fetching them */
memset(bind, 0, sizeof(bind));
/* INTEGER COLUMN */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= &is_null[0];
bind[0].length= &length[0];
/* STRING COLUMN */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= &is_null[1];
bind[1].length= &length[1];
/* SMALLINT COLUMN */
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer= (char *)&small_data;
bind[2].is_null= &is_null[2];
bind[2].length= &length[2];
/* TIMESTAMP COLUMN */
bind[3].buffer_type= MYSQL_TYPE_TIMESTAMP;
bind[3].buffer= (char *)&ts;
bind[3].is_null= &is_null[3];
bind[3].length= &length[3];
/* Bind the result buffers */
if (mysql_stmt_bind_result(stmt, bind))
{
fprintf(stderr, " mysql_stmt_bind_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client */
if (mysql_stmt_store_result(stmt))
{
fprintf(stderr, " mysql_stmt_store_result() failed\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
/* Fetch all rows */
row_count= 0;
fprintf(stdout, "Fetching results ...\n");
while (!mysql_stmt_fetch(stmt))
{
row_count++;
fprintf(stdout, " row %d\n", row_count);
/* column 1 */
fprintf(stdout, " column1 (integer) : ");
if (is_null[0])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %d(%ld)\n", int_data, length[0]);
/* column 2 */
fprintf(stdout, " column2 (string) : ");
if (is_null[1])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %s(%ld)\n", str_data, length[1]);
/* column 3 */
fprintf(stdout, " column3 (smallint) : ");
if (is_null[2])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %d(%ld)\n", small_data, length[2]);
/* column 4 */
fprintf(stdout, " column4 (timestamp): ");
if (is_null[3])
fprintf(stdout, " NULL\n");
else
fprintf(stdout, " %04d-%02d-%02d %02d:%02d:%02d (%ld)\n",
ts.year, ts.month, ts.day,
ts.hour, ts.minute, ts.second,
length[3]);
fprintf(stdout, "\n");
}
/* Validate rows fetched */
fprintf(stdout, " total rows fetched: %d\n", row_count);
if (row_count != 2)
{
fprintf(stderr, " MySQL failed to return all rows\n");
exit(0);
}
/* Free the prepared result metadata */
mysql_free_result(prepare_meta_result);
/* Close the statement */
if (mysql_stmt_close(stmt))
{
fprintf(stderr, " failed while closing the statement\n");
fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
exit(0);
}
mysql_stmt_fetch_column()
int mysql_stmt_fetch_column(MYSQL_STMT *stmt, MYSQL_BIND *bind, unsigned int column, unsigned long offset)
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.
This function was added in MySQL 4.1.2.
Zero if the value was fetched successfully. Non-zero if an error occurred.
CR_INVALID_PARAMETER_NO
CR_NO_DATA
mysql_stmt_field_count()
unsigned int mysql_stmt_field_count(MYSQL_STMT *stmt)
Returns the number of columns for the most recent statement for the statement
handler. This value will be 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().
This function was added in MySQL 4.1.3.
An unsigned integer representing the number of columns in a result set.
None.
mysql_stmt_init()
MYSQL_STMT *mysql_stmt_init(MYSQL *mysql)
Create a MYSQL_STMT handle.
The handle should be freed with mysql_stmt_close(MYSQL_STMT *).
This function was added in MySQL 4.1.2.
A pointer to a MYSQL_STMT structure in case of success.
NULL if out of memory.
CR_OUT_OF_MEMORY
mysql_stmt_num_rows()
my_ulonglong mysql_stmt_num_rows(MYSQL_STMT *stmt)
Returns the number of rows in the result set.
The use of mysql_stmt_num_rows() depends on whether or not 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.
This function was added in MySQL 4.1.1.
The number of rows in the result set.
None.
mysql_stmt_param_count()
unsigned long mysql_stmt_param_count(MYSQL_STMT *stmt)
Returns the number of parameter markers present in the prepared statement.
This function was added in MySQL 4.1.2.
An unsigned long integer representing the number of parameters in a statement.
None.
For the usage of mysql_stmt_param_count(), refer to the Example from
section 21.2.7.10 mysql_stmt_execute().
mysql_stmt_param_metadata()
MYSQL_RES *mysql_stmt_param_metadata(MYSQL_STMT *stmt)
To be added.
This function was added in MySQL 4.1.2.
mysql_stmt_prepare()
int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned
long length)
Given the statement handle returned by mysql_stmt_init(),
prepares the SQL statement pointed to by the string
query 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 Languange (DML)
statements, and not in Data Defination Language (DDL) statements.
The parameter markers must be bound to application variables using
mysql_stmt_bind_param() before executing the statement.
This function was added in MySQL 4.1.2.
Zero if the statement was prepared successfully. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
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().
For the usage of mysql_stmt_prepare(), refer to the Example from
section 21.2.7.10 mysql_stmt_execute().
mysql_stmt_reset()
my_bool mysql_stmt_reset(MYSQL_STMT *stmt)
Reset prepared statement on client and server to state after prepare.
For now this is mainly used to reset data sent with
mysql_stmt_send_long_data().
To re-prepare the statement with another query, use
mysql_stmt_prepare().
This function was added in MySQL 4.1.1.
Zero if the statement was reset successfully. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_stmt_result_metadata()
MYSQL_RES *mysql_stmt_result_metadata(MYSQL_STMT *stmt)
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().
This function was added in MySQL 4.1.2.
A MYSQL_RES result structure. NULL if no meta information exists
for the prepared query.
CR_OUT_OF_MEMORY
CR_UNKNOWN_ERROR
For the usage of mysql_stmt_result_metadata(), refer to the
Example from section 21.2.7.13 mysql_stmt_fetch().
mysql_stmt_row_seek()
MYSQL_ROW_OFFSET mysql_stmt_row_seek(MYSQL_STMT *stmt, MYSQL_ROW_OFFSET offset)
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().
This function was added in MySQL 4.1.1.
The previous value of the row cursor. This value may be passed to a
subsequent call to mysql_stmt_row_seek().
None.
mysql_stmt_row_tell()
MYSQL_ROW_OFFSET mysql_stmt_row_tell(MYSQL_STMT *stmt)
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().
This function was added in MySQL 4.1.1.
The current offset of the row cursor.
None.
mysql_stmt_send_long_data()
my_bool mysql_stmt_send_long_data(MYSQL_STMT *stmt, unsigned int
parameter_number, const char *data, unsigned long length)
Allows an application to send parameter data to the server in pieces
(or ``chunks'').
This function 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 will ignore 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().
If you want to reset/forget the sent data, you can do it with
mysql_stmt_reset(). See section 21.2.7.21 mysql_stmt_reset().
This function was added in MySQL 4.1.2.
Zero if the data is sent successfully to server. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_OUT_OF_MEMORY
CR_UNKNOWN_ERROR
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.
#define INSERT_QUERY "INSERT INTO test_long_data(text_column) VALUES(?)"
MYSQL_BIND bind[1];
long length;
smtt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(stmt, INSERT_QUERY, strlen(INSERT_QUERY)))
{
fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed");
fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
exit(0);
}
memset(bind, 0, sizeof(bind));
bind[0].buffer_type= MYSQL_TYPE_STRING;
bind[0].length= &length;
bind[0].is_null= 0;
/* Bind the buffers */
if (mysql_stmt_bind_param(stmt, bind))
{
fprintf(stderr, "\n param bind failed");
fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
exit(0);
}
/* Supply data in chunks to server */
if (!mysql_stmt_send_long_data(stmt,0,"MySQL",5))
{
fprintf(stderr, "\n send_long_data failed");
fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
exit(0);
}
/* Supply the next piece of data */
if (mysql_stmt_send_long_data(stmt,0," - The most popular Open Source database",40))
{
fprintf(stderr, "\n send_long_data failed");
fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
exit(0);
}
/* Now, execute the query */
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, "\n mysql_stmt_execute failed");
fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
exit(0);
}
mysql_stmt_sqlstate()
const char *mysql_stmt_sqlstate(MYSQL_STMT *stmt)
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 section 23 Error Handling in MySQL.
Note that not all MySQL errors are yet mapped to SQLSTATE's.
The value "HY000" (general error) is used
for unmapped errors.
This function was added to MySQL 4.1.1.
A null-terminated character string containing the SQLSTATE error code.
mysql_stmt_store_result()
int mysql_stmt_store_result(MYSQL_STMT *stmt)
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 will 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 21.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 21.2.7.3 mysql_stmt_attr_set().
This function was added in MySQL 4.1.0.
Zero if the results are buffered successfully. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
Here follows a list of the currently known problems with prepared statements:
TIME, TIMESTAMP, and DATETIME don't support sub seconds
(for example from DATE_FORMAT().
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)).
From version 4.1, MySQL supports the execution of multiple statements
specified in a single query string. To use this capability with a given
connection, you must specify the CLIENT_MULTI_STATEMENTS option in
the flags parameter of mysql_real_connect()
when opening the connection. You can also set this for an existing connection
by calling mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)
By default, mysql_query() and mysql_real_query() return
only the first query status and the subsequent queries status can
be processed using mysql_more_results() and
mysql_next_result().
/* Connect to server with option CLIENT_MULTI_STATEMENTS */
mysql_real_connect(..., CLIENT_MULTI_STATEMENTS);
/* Now execute multiple queries */
mysql_query(mysql,"DROP TABLE IF EXISTS test_table;\
CREATE TABLE test_table(id INT);\
INSERT INTO test_table VALUES(10);\
UPDATE test_table SET id=20 WHERE id=10;\
SELECT * FROM test_table;\
DROP TABLE test_table");
do
{
/* Process all results */
...
printf("total affected rows: %lld", mysql_affected_rows(mysql));
...
if (!(result= mysql_store_result(mysql)))
{
printf(stderr, "Got fatal error processing query\n");
exit(1);
}
process_result_set(result); /* client function */
mysql_free_result(result);
} while (!mysql_next_result(mysql));
The new binary protocol available in MySQL 4.1 and above 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 21.2.5 C API Prepared Statement Data types.
To send temporal data values, you create a prepared statement with
mysql_stmt_prepare(). Then, before calling
mysql_stmt_execute() to execute the statement, use the following
procedure to set up each temporal parameter:
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.
buffer member of the MYSQL_BIND structure to the address
of the MYSQL_TIME structure in which you will pass the temporal value.
MYSQL_TIME structure that are appropriate
for the type of temporal value you're passing.
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.
MYSQL_TIME ts;
MYSQL_BIND bind[3];
MYSQL_STMT *stmt;
strmov(query, "INSERT INTO test_table(date_field, time_field,
timestamp_field) VALUES(?,?,?");
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(mysql, query, strlen(query)))
{
fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed");
fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
exit(0);
}
/* set up input buffers for all 3 parameters */
bind[0].buffer_type= MYSQL_TYPE_DATE;
bind[0].buffer= (char *)&ts;
bind[0].is_null= 0;
bind[0].length= 0;
...
bind[1]= bind[2]= bind[0];
...
mysql_stmt_bind_param(stmt, bind);
/* supply the data to be sent in the ts structure */
ts.year= 2002;
ts.month= 02;
ts.day= 03;
ts.hour= 10;
ts.minute= 45;
ts.second= 20;
mysql_stmt_execute(stmt);
..
You need to use the following functions when you want to create a threaded client. See section 21.2.15 How to Make a Threaded Client.
my_init()
void my_init(void)
This function needs to be called once in the program before calling any
MySQL function. This initializes some global variables that MySQL
needs. If you are using a thread-safe client library, this will also
call mysql_thread_init() for this thread.
This is automatically called by mysql_init(),
mysql_server_init() and mysql_connect().
None.
mysql_thread_init()
my_bool mysql_thread_init(void)
This function needs to be called for each created thread to initialize thread-specific variables.
This is automatically called by my_init() and mysql_connect().
Zero if successful. Non-zero if an error occurred.
mysql_thread_end()
void mysql_thread_end(void)
This function needs to be called before calling pthread_exit() to
free memory allocated by mysql_thread_init().
Note that this function is not invoked automatically by the client library. It must be called explicitly to avoid a memory leak.
None.
mysql_thread_safe()
unsigned int mysql_thread_safe(void)
This function indicates whether the client is compiled as thread-safe.
1 is the client is thread-safe, 0 otherwise.
You must use the following functions if you want to allow your application to be linked against the embedded MySQL server library. See section 21.2.16 libmysqld, the Embedded MySQL Server Library.
If the program is linked with -lmysqlclient instead of
-lmysqld, these functions do nothing. This makes it
possible to choose between using the embedded MySQL server and
a standalone server without modifying any code.
mysql_server_init()
int mysql_server_init(int argc, char **argv, char **groups)
This function must be called once in the program using the
embedded server before calling any other MySQL function. It starts
the server and initializes any subsystems (mysys, InnoDB, etc.)
that the server uses. If this function is not called, the next call to
mysql_init() executes mysql_server_init().
If you are using the DBUG package that comes with MySQL, you should call
this after you have called my_init().
The argc and argv arguments are analogous to the arguments
to main(). The first element of argv is ignored (it
typically contains the program name). For convenience, argc may
be 0 (zero) if there are no command-line arguments for the
server. mysql_server_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 NULL-terminated list of strings in groups
selects which groups in the option files will be active.
See section 4.3.2 Using Option Files. For convenience, groups may be
NULL, in which case the [server] and [emedded] groups
will be active.
#include <mysql.h>
#include <stdlib.h>
static char *server_args[] = {
"this_program", /* this string is not used */
"--datadir=.",
"--key_buffer_size=32M"
};
static char *server_groups[] = {
"embedded",
"server",
"this_program_SERVER",
(char *)NULL
};
int main(void) {
if (mysql_server_init(sizeof(server_args) / sizeof(char *),
server_args, server_groups))
exit(1);
/* Use any MySQL API functions here */
mysql_server_end();
return EXIT_SUCCESS;
}
0 if okay, 1 if an error occurred.
mysql_server_end()
void mysql_server_end(void)
This function must be called once in the program after all other MySQL functions. It shuts down the embedded server.
None.
mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success
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:
malloc() failure (for example, if the result set was too
large).
INSERT,
UPDATE, or DELETE).
You can always check whether the statement should have produced a
non-empty result by calling mysql_field_count(). If
mysql_field_count() returns zero, the result is empty and the last
query was a statement that does not return values (for example, an
INSERT or a DELETE). If mysql_field_count() returns a
non-zero value, the statement should have produced a non-empty result.
See the description of the mysql_field_count() function for an
example.
You can test for an error by calling mysql_error() or
mysql_errno().
In addition to the result set returned by a query, you can also get the following information:
mysql_affected_rows() returns the number of rows affected by the last
query when doing an INSERT, UPDATE, or DELETE.
In MySQL 3.23, there is an exception when DELETE is used without
a WHERE clause. In this case, the table is re-created as an empty
table and mysql_affected_rows() returns zero for the number of
records affected. In MySQL 4.0, DELETE always returns the correct
number of rows deleted. For a fast recreate, 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().
mysql_insert_id() returns the ID generated by the last
query that inserted a row into a table with an AUTO_INCREMENT index.
See section 21.2.3.33 mysql_insert_id().
LOAD DATA INFILE ..., INSERT INTO
... SELECT ..., UPDATE) return additional information. The result is
returned by mysql_info(). See the description for mysql_info()
for the format of the string that it returns. mysql_info() returns a
NULL pointer if there is no additional information.
If you insert a record 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 into an
AUTO_INCREMENT column by executing the following code (which assumes
that you've already checked that the statement succeeded). It determines
whether the query was an INSERT with an AUTO_INCREMENT index:
if ((result = mysql_store_result(&mysql)) == 0 &&
mysql_field_count(&mysql) == 0 &&
mysql_insert_id(&mysql) != 0)
{
used_id = mysql_insert_id(&mysql);
}
For more information, see section 21.2.3.33 mysql_insert_id().
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 will not be changed by another client. It will not
even be changed if you update another AUTO_INCREMENT column with a
non-magic value (that is, a value that is not NULL and not 0).
If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:
INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
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().
When linking with the C API, the following errors may occur on some systems:
gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl Undefined first referenced symbol in file floor /usr/local/lib/mysql/libmysqlclient.a(password.o) ld: fatal: Symbol referencing errors. No output written to client
If this happens on your system, you must include the math library by
adding -lm to the end of the compile/link line.
If you compile MySQL clients that you've written yourself or that
you obtain from a third-party, they must be linked using the
-lmysqlclient -lz option on the link command. You may also need to
specify a -L option to tell the linker where to find the library. For
example, if the library is installed in `/usr/local/mysql/lib', use
-L/usr/local/mysql/lib -lmysqlclient -lz on the link command.
For clients that use MySQL header files, you may need to specify a
-I option when you compile them (for example,
-I/usr/local/mysql/include), so the compiler can find the header
files.
To make it simpler to compile MySQL programs on Unix, we have provided the
mysql_config script for you. See section 21.1.2 mysql_config, Get compile options for compiling clients.
You can use it to compile a MySQL client as follows:
CFG=/usr/local/mysql/bin/mysql_config sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"
The sh -c is needed to get the shell not to treat the output from
mysql_config as one word.
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.
New in 4.0.16: To not abort the program when a connection terminates,
MySQL blocks SIGPIPE on the first call to
mysql_server_init(), mysql_init() or
mysql_connect(). If you want to have your own SIGPIPE
handler, you should first call mysql_server_init() and then
install your handler. In older versions of MySQL SIGPIPE was blocked,
but only in the thread safe client library, for every call to
mysql_init().
In the older binaries we distribute on our Web site (http://www.mysql.com/), the client libraries are not normally compiled with the thread-safe option (the Windows binaries are by default compiled to be thread-safe). Newer binary distributions should have both a normal and a thread-safe client library.
To get a threaded client where you can interrupt the client from other
threads and set timeouts when talking with the MySQL server, you should
use the -lmysys, -lmystrings, and -ldbug libraries and
the net_serv.o code that the server uses.
If you don't need interrupts or timeouts, you can just compile a
thread-safe client library (mysqlclient_r) and use this. See section 21.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 21.2.11 C API Threaded Function Descriptions.
All functions except mysql_real_connect() are by default
thread-safe. The following notes describe how to compile a thread-safe
client library and use it in a thread-safe manner. (The notes below for
mysql_real_connect() actually apply to mysql_connect() as
well, but because mysql_connect() is deprecated, you should be
using mysql_real_connect() anyway.)
To make mysql_real_connect() thread-safe, you must recompile the
client library with this command:
shell> ./configure --enable-thread-safe-client
This will create a thread-safe client library libmysqlclient_r.
(Assuming that your OS 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:
mysql_query() and mysql_store_result() no other thread is using
the same connection.
mysql_store_result().
mysql_use_result, you have to ensure that no other thread
is using the same connection until the result set is closed.
However, it really is best for threaded clients that share the same
connection to use mysql_store_result().
mysql_query() and
mysql_store_result() call combination. Once
mysql_store_result() is ready, the lock can be released and other
threads may query the same connection.
pthread_mutex_lock() and pthread_mutex_unlock() to
establish and release a mutex lock.
You need to know the following if you have a thread that is calling MySQL functions which did not create the connection to the MySQL database:
When you call mysql_init() or mysql_connect(), MySQL will
create a thread-specific variable for the thread that is used by the
debug library (among other things).
If you call a MySQL function, before the thread has
called mysql_init() or mysql_connect(), the thread will
not have the necessary thread-specific variables in place and you are
likely to end up with a core dump sooner or later.
The get things to work smoothly you have to do the following:
my_init() at the start of your program if it calls
any other MySQL function before calling mysql_real_connect().
mysql_thread_init() in the thread handler before calling
any MySQL function.
mysql_thread_end() before calling
pthread_exit(). This will free the memory used by MySQL
thread-specific variables.
You may get some errors because of undefined symbols when linking your
client with libmysqlclient_r. In most cases this is because you haven't
included the thread libraries on the link/compile line.
The embedded MySQL server library makes it possible to run a full-featured MySQL server inside 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_server_init() | Should be called before any other MySQL function is called, preferably early in the main() function.
|
mysql_server_end() | Should be called before your program exits. |
mysql_thread_init() | Should be called in each thread you create that will access MySQL. |
mysql_thread_end() | Should be called before calling pthread_exit()
|
Then you must link your code with `libmysqld.a' instead of `libmysqlclient.a'.
The mysql_server_xxx() functions are also included in
`libmysqlclient.a' to allow you to change between the embedded and the
client/server version by just linking your application with the right
library. See section 21.2.12.1 mysql_server_init().
libmysqld
To get a libmysqld library you should configure MySQL with the
--with-embedded-server option.
When you link your program with libmysqld, you must also include
the system-specific pthread libraries and some libraries that
the MySQL server uses. You can get the full list of libraries by executing
mysql_config --libmysqld-libs.
The correct flags for compiling and linking a threaded program must be used, even if you do not directly call any thread functions in your code.
The embedded server has the following limitations:
ISAM tables. (This is mainly done to make the library smaller)
Some of these limitations can be changed by editing the `mysql_embed.h' include file and recompiling MySQL.
The following is the recommended way to use option files to make it easy to switch between a client/server application and one where MySQL is embedded. See section 4.3.2 Using Option Files.
[server] section. These will be read by
both MySQL versions.
[mysqld] section.
[embedded] section.
[ApplicationName_SERVER]
section.
stderr. We will add an option to specify a
filename for these.
InnoDB to not be so verbose when using in the embedded
version.
This example program and makefile should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes will be needed. This example is designed to give enough details to understand the problem, without the clutter that is a necessary part of a real application.
To try out the example, create an `test_libmysqld' directory at the same level as the mysql-4.0 source directory. Save the `test_libmysqld.c' source and the `GNUmakefile' in the directory, and run GNU `make' from inside the `test_libmysqld' directory.
`test_libmysqld.c'
/*
* A simple example client, using the embedded MySQL server library
*/
#include <mysql.h>
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
MYSQL *db_connect(const char *dbname);
void db_disconnect(MYSQL *db);
void db_do_query(MYSQL *db, const char *query);
const char *server_groups[] = {
"test_libmysqld_SERVER", "embedded", "server", NULL
};
int
main(int argc, char **argv)
{
MYSQL *one, *two;
/* mysql_server_init() must be called before any other mysql
* functions.
*
* You can use mysql_server_init(0, NULL, NULL), and it will
* initialize the server using groups = {
* "server", "embedded", NULL
* }.
*
* In your $HOME/.my.cnf file, you probably want to put:
[test_libmysqld_SERVER]
language = /path/to/source/of/mysql/sql/share/english
* You could, of course, modify argc and argv before passing
* them to this function. Or you could create new ones in any
* way you like. But all of the arguments in argv (except for
* argv[0], which is the program name) should be valid options
* for the MySQL server.
*
* If you link this client against the normal mysqlclient
* library, this function is just a stub that does nothing.
*/
mysql_server_init(argc, argv, (char **)server_groups);
one = db_connect("test");
two = db_connect(NULL);
db_do_query(one, "SHOW TABLE STATUS");
db_do_query(two, "SHOW DATABASES");
mysql_close(two);
mysql_close(one);
/* This must be called after all other mysql functions */
mysql_server_end();
exit(EXIT_SUCCESS);
}
static void
die(MYSQL *db, char *fmt, ...)
{
va_list ap;
va_start(ap, fmt);
vfprintf(stderr, fmt, ap);
va_end(ap);
(void)putc('\n', stderr);
if (db)
db_disconnect(db);
exit(EXIT_FAILURE);
}
MYSQL *
db_connect(const char *dbname)
{
MYSQL *db = mysql_init(NULL);
if (!db)
die(db, "mysql_init failed: no memory");
/*
* Notice that the client and server use separate group names.
* This is critical, because the server will not accept the
* client's options, and vice versa.
*/
mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test_libmysqld_CLIENT");
if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0))
die(db, "mysql_real_connect failed: %s", mysql_error(db));
return db;
}
void
db_disconnect(MYSQL *db)
{
mysql_close(db);
}
void
db_do_query(MYSQL *db, const char *query)
{
if (mysql_query(db, query) != 0)
goto err;
if (mysql_field_count(db) > 0)
{
MYSQL_RES *res;
MYSQL_ROW row, end_row;
int num_fields;
if (!(res = mysql_store_result(db)))
goto err;
num_fields = mysql_num_fields(res);
while ((row = mysql_fetch_row(res)))
{
(void)fputs(">> ", stdout);
for (end_row = row + num_fields; row < end_row; ++row)
(void)printf("%s\t", row ? (char*)*row : "NULL");
(void)fputc('\n', stdout);
}
(void)fputc('\n', stdout);
mysql_free_result(res);
}
else
(void)printf("Affected rows: %lld\n", mysql_affected_rows(db));
return;
err:
die(db, "db_do_query failed: %s [%s]", mysql_error(db), query);
}
`GNUmakefile'
# This assumes the MySQL software is installed in /usr/local/mysql
inc := /usr/local/mysql/include/mysql
lib := /usr/local/mysql/lib
# If you have not installed the MySQL software yet, try this instead
#inc := $(HOME)/mysql-4.0/include
#lib := $(HOME)/mysql-4.0/libmysqld
CC := gcc
CPPFLAGS := -I$(inc) -D_THREAD_SAFE -D_REENTRANT
CFLAGS := -g -W -Wall
LDFLAGS := -static
# You can change -lmysqld to -lmysqlclient to use the
# client/server library
LDLIBS = -L$(lib) -lmysqld -lz -lm -lcrypt
ifneq (,$(shell grep FreeBSD /COPYRIGHT 2>/dev/null))
# FreeBSD
LDFLAGS += -pthread
else
# Assume Linux
LDLIBS += -lpthread
endif
# This works for simple one-file test programs
sources := $(wildcard *.c)
objects := $(patsubst %c,%o,$(sources))
targets := $(basename $(sources))
all: $(targets)
clean:
rm -f $(targets) $(objects) *.core
We encourage everyone to promote free software by releasing code under the GPL or a compatible license. For those who are not able to do this, another option is to purchase a commercial license for the MySQL code from MySQL AB. For details, please see http://www.mysql.com/company/legal/licensing/.
PHP is a server-side, HTML-embedded scripting language that may be used to create dynamic Web pages. It contains support for accessing several databases, including MySQL. PHP may be run as a separate program or compiled as a module for use with the Apache Web server.
The distribution and documentation are available at the PHP Web site (http://www.php.net/).
-lz last when linking
with -lmysqlclient.
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 now the recommended Perl interface. It replaces an older
interface called mysqlperl, which should be considered obsolete.
Installation instructions for Perl DBI support are given in section 2.13 Perl Installation Notes.
DBI information is available at the command line, online, or in printed form:
DBI and DBD::mysql modules installed, you can
get information about them at the command line with the perldoc command:
shell> perldoc DBI shell> perldoc DBI::FAQ shell> perldoc DBD::mysqlYou can also use
pod2man, pod2html, and so forth to translate
this information into other formats.
DBD::mysql; see
section 1.4.1.1 The MySQL Mailing Lists.
MySQL++ is the MySQL API for C++. More
information can be found at http://www.mysql.com/products/mysql++/.
You can compile the MySQL Windows source with Borland C++ 5.02. (The Windows source includes only projects for Microsoft VC++, for Borland C++ you have to do the project files yourself.)
One known problem with Borland C++ is that it uses a different structure
alignment than VC++. This means that you will run into problems if you
try to use the default libmysql.dll libraries (that was compiled
with VC++) with Borland C++. To avoid this problem, only call
mysql_init() with NULL as an argument, not a pre-allocated
MYSQL structure.
MySQLdb provides MySQL support for Python, compliant with the
Python DB API version 2.0. It can be found at
http://sourceforge.net/projects/mysql-python/.
MySQLtcl is a simple API for accessing a MySQL database server from the Tcl
programming language. It can be found at http://www.xdobry.de/mysqltcl/.
Eiffel MySQL is an interface to the MySQL database server using the Eiffel programming language, written by Michael Ravits. It can be found at http://efsa.sourceforge.net/archive/ravits/mysql.htm.
Go to the first, previous, next, last section, table of contents.