|
|
Table of Contents
libmysqld
This chapter describes the APIs available for MySQL, where to get
them, and how to use them. The C API is the most extensively
covered, because it was developed by the MySQL team, and is the
basis for most of the other APIs. This chapter also covers the
libmysqld
library (the embedded server), as well
as some programs that are useful for application developers.
libmysqld
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 accesses MySQL. |
mysql_thread_end() | Should be called before calling pthread_exit() |
Then you must link your code with
libmysqld.a
instead of
libmysqlclient.a
.
The
mysql_server_
functions are also included in
xxx
()libmysqlclient.a
to allow you to change
between the embedded and the client/server version by just
linking your application with the right library. See
Section 22.2.12.1, “mysql_server_init()
”.
One difference between the embedded server and the standalone
server is that for the embedded server, authentication for
connections is disabled by default. To use authentication for
the embedded server, specify the
--with-embedded-privilege-control
option when
you invoke configure to configure your MySQL
distribution.
To get a libmysqld
library you should
configure MySQL with the --with-embedded-server
option. See Section 2.8.2, “Typical configure Options”.
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.
To compile a C program to include the necessary files to embed
the MySQL server library into a compiled version of a program,
use the GNU C compiler (gcc
). The compiler
will need to know where to find various files and need
instructions on how to compile the program. The following
example shows how a program could be compiled from the command
line:
gcc mysql_test.c -o mysql_test -lz \ `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`
Immediately following the gcc
command is the
name of the uncompiled C program file. After it, the
-o
option is given to indicate that the file
name that follows is the name that the compiler is to give to
the output file, the compiled program. The next line of code
tells the compiler to obtain the location of the include files
and libraries and other settings for the system on which it's
compiled. Because of a problem with
mysql_config, the option -lz
(for compression) is added here. The
mysql_config piece is contained in backticks,
not single quotes.
The embedded server has the following limitations:
No support for ISAM
tables. (This is
mainly done to make the library smaller.)
No user-defined functions (UDFs).
No stack trace on core dump.
No internal RAID support. (This is not normally needed as most current operating systems support big files.)
You cannot set this up as a master or a slave (no replication).
Very large result sets may be unusable on low memory systems.
You cannot connect to an embedded server from an outside process with sockets or TCP/IP. However, you can connect to an intermediate application, which in turn can connect to an embedded server on the behalf of a remote client or outside process.
Some of these limitations can be changed by editing the
mysql_embed.h
include file and recompiling
MySQL.
Any options that may be given with the mysqld
server daemon, may be used with an embedded server library.
Server options may be given in an array as an argument to the
mysql_server_init()
, which initializes the
server. They also may be given in an option file like
my.cnf
. To specify an option file for a C
program, use the --defaults-file
option as one
of the elements of the second argument of the
mysql_server_init()
function. See
Section 22.2.12.1, “mysql_server_init()
”, for more information on the
mysql_server_init()
function.
Using option files can make it easier to switch between a
client/server application and one where MySQL is embedded. Put
common options under the [server]
group.
These are read by both MySQL versions. Client/server-specific
options should go under the [mysqld]
section.
Put options specific to the embedded MySQL server library in the
[embedded]
section. Options specific to
applications go under section labeled
[ApplicationName_SERVER]
. See
Section 4.3.2, “Using Option Files”.
We are going to provide options to leave out some parts of MySQL to make the library smaller.
There is still a lot of speed optimization to do.
Errors are written to stderr
. We will add
an option to specify a filename for these.
We have to change InnoDB
not to be so
verbose when using the embedded version. If your database
does not contain InnoDB
tables, to
suppress related messages you can add the
--skip-innodb
option to the options file
under the group [libmysqd_server]
, or
when initializing the server with
mysql_server_init().
These two example programs should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes are needed, mostly with file paths. These examples are designed to give enough details for you to understand the problem, without the clutter that is a necessary part of a real application. The first example is very straightforward. The second example is a little more advanced with some error checking. The first is followed by a command-line entry for compiling the program. The second is followed by a GNUmake file that may be used for compiling instead.
Example 1
test1_libmysqld.c
#include <stdio.h> #include <stdlib.h> #include <stdarg.h> #include "mysql.h" MYSQL *mysql; MYSQL_RES *results; MYSQL_ROW record; static char *server_options[] = { "mysql_test", "--defaults-file=my.cnf" }; int num_elements = sizeof(server_options)/ sizeof(char *); static char *server_groups[] = { "libmysqld_server", "libmysqld_client" }; int main(void) { mysql_server_init(num_elements, server_options, server_groups); mysql = mysql_init(NULL); mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client"); mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL); mysql_real_connect(mysql, NULL,NULL,NULL, "database1", 0,NULL,0); mysql_query(mysql, "SELECT column1, column2 FROM table1"); results = mysql_store_result(mysql); while((record = mysql_fetch_row(results))) { printf("%s - %s \n", record[0], record[1]); } mysql_free_result(results); mysql_close(mysql); mysql_server_end(); return 0; }
Here is the command line for compiling the above program:
gcc test1_libmysqld.c -o test1_libmysqld -lz \ `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`
Example 2
To try out the example, create an
test2_libmysqld
directory at the same level
as the MySQL source directory. Save the
test2_libmysqld.c
source and the
GNUmakefile
in the directory, and run GNU
make
from inside the
test2_libmysqld
directory.
test2_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[] = { "test2_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 * initializes the server using groups = { * "server", "embedded", NULL * }. * * In your $HOME/.my.cnf file, you probably want to put: [test2_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 does not accept the * client's options, and vice versa. */ mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test2_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-5.0/include #lib := $(HOME)/mysql-5.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/.
The C API code is distributed with MySQL. It is included in the
mysqlclient
library and allows C programs to
access a database.
Many of the clients in the MySQL source distribution are written
in C. If you are looking for examples that demonstrate how to use
the C API, take a look at these clients. You can find these in the
clients
directory in the MySQL source
distribution.
Most of the other client APIs (all except Connector/J and
Connector/NET) use the mysqlclient
library to
communicate with the MySQL server. This means that, for example,
you can take advantage of many of the same environment variables
that are used by other client programs, because they are
referenced from the library. See
Chapter 8, Client and Utility Programs, for a list of these
variables.
The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16KB) is automatically increased up to the maximum size (the maximum is 16MB). Because buffer sizes are increased only as demand warrants, simply increasing the default maximum limit does not in itself cause more resources to be used. This size check is mostly a check for erroneous statements and communication packets.
The communication buffer must be large enough to contain a single
SQL statement (for client-to-server traffic) and one row of
returned data (for server-to-client traffic). Each thread's
communication buffer is dynamically enlarged to handle any query
or row up to the maximum limit. For example, if you have
BLOB
values that contain up to 16MB of data,
you must have a communication buffer limit of at least 16MB (in
both server and client). The client's default maximum is 16MB, but
the default maximum in the server is 1MB. You can increase this by
changing the value of the max_allowed_packet
parameter when the server is started. See
Section 7.5.2, “Tuning Server Parameters”.
The MySQL server shrinks each communication buffer to
net_buffer_length
bytes after each query. For
clients, the size of the buffer associated with a connection is
not decreased until the connection is closed, at which time client
memory is reclaimed.
For programming with threads, see Section 22.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 22.1, “libmysqld, the Embedded MySQL Server Library”.
This structure represents a handle to one database
connection. It is used for almost all MySQL functions. You
should not try to make a copy of a MYSQL
structure. There is no guarantee that such a copy will be
usable.
This structure represents the result of a query that returns
rows (SELECT
, SHOW
,
DESCRIBE
, EXPLAIN
).
The information returned from a query is called the
result set in the remainder of this
section.
This is a type-safe representation of one row of data. It is
currently implemented as an array of counted byte strings.
(You cannot treat these as null-terminated strings if field
values may contain binary data, because such values may
contain null bytes internally.) Rows are obtained by calling
mysql_fetch_row()
.
This structure contains information about a field, such as
the field's name, type, and size. Its members are described
in more detail here. You may obtain the
MYSQL_FIELD
structures for each field by
calling mysql_fetch_field()
repeatedly.
Field values are not part of this structure; they are
contained in a MYSQL_ROW
structure.
This is a type-safe representation of an offset into a MySQL
field list. (Used by mysql_field_seek()
.)
Offsets are field numbers within a row, beginning at zero.
The type used for the number of rows and for
mysql_affected_rows()
,
mysql_num_rows()
, and
mysql_insert_id()
. This type provides a
range of 0
to 1.84e19
.
On some systems, attempting to print a value of type
my_ulonglong
does not work. To print such
a value, convert it to unsigned long
and
use a %lu
print format. Example:
printf ("Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));
The MYSQL_FIELD
structure contains the
members listed here:
char * name
The name of the field, as a null-terminated string. If the
field was given an alias with an AS
clause, the value of name
is the alias.
char * org_name
The name of the field, as a null-terminated string. Aliases are ignored.
char * table
The name of the table containing this field, if it isn't a
calculated field. For calculated fields, the
table
value is an empty string. If the
table was given an alias with an AS
clause, the value of table
is the alias.
char * org_table
The name of the table, as a null-terminated string. Aliases are ignored.
char * db
The name of the database that the field comes from, as a
null-terminated string. If the field is a calculated field,
db
is an empty string.
char * catalog
The catalog name. This value is always
"def"
.
char * def
The default value of this field, as a null-terminated
string. This is set only if you use
mysql_list_fields()
.
unsigned long length
The width of the field, as specified in the table definition.
unsigned long max_length
The maximum width of the field for the result set (the
length of the longest field value for the rows actually in
the result set). If you use
mysql_store_result()
or
mysql_list_fields()
, this contains the
maximum length for the field. If you use
mysql_use_result()
, the value of this
variable is zero.
unsigned int name_length
The length of name
.
unsigned int org_name_length
The length of org_name
.
unsigned int table_length
The length of table
.
unsigned int org_table_length
The length of org_table
.
unsigned int db_length
The length of db
.
unsigned int catalog_length
The length of catalog
.
unsigned int def_length
The length of def
.
unsigned int flags
Different bit-flags for the field. The
flags
value may have zero or more of the
following bits set:
Flag Value | Flag Description |
NOT_NULL_FLAG | Field can't be NULL |
PRI_KEY_FLAG | Field is part of a primary key |
UNIQUE_KEY_FLAG | Field is part of a unique key |
MULTIPLE_KEY_FLAG | Field is part of a non-unique key |
UNSIGNED_FLAG | Field has the UNSIGNED attribute |
ZEROFILL_FLAG | Field has the ZEROFILL attribute |
BINARY_FLAG | Field has the BINARY attribute |
AUTO_INCREMENT_FLAG | Field has the AUTO_INCREMENT attribute |
ENUM_FLAG | Field is an ENUM (deprecated) |
SET_FLAG | Field is a SET (deprecated) |
BLOB_FLAG | Field is a BLOB or TEXT
(deprecated) |
TIMESTAMP_FLAG | Field is a TIMESTAMP (deprecated) |
Use of the BLOB_FLAG
,
ENUM_FLAG
, SET_FLAG
,
and TIMESTAMP_FLAG
flags is deprecated
because they indicate the type of a field rather than an
attribute of its type. It is preferable to test
field->type
against
MYSQL_TYPE_BLOB
,
MYSQL_TYPE_ENUM
,
MYSQL_TYPE_SET
, or
MYSQL_TYPE_TIMESTAMP
instead.
The following example illustrates a typical use of the
flags
value:
if (field->flags & NOT_NULL_FLAG) printf("Field can't be null\n");
You may use the following convenience macros to determine
the boolean status of the flags
value:
Flag Status | Description |
IS_NOT_NULL(flags) | True if this field is defined as NOT NULL |
IS_PRI_KEY(flags) | True if this field is a primary key |
IS_BLOB(flags) | True if this field is a BLOB or
TEXT (deprecated; test
field->type instead) |
unsigned int decimals
The number of decimals for numeric fields.
unsigned int charset_nr
The character set number for the field.
enum enum_field_types type
The type of the field. The type
value may
be one of the MYSQL_TYPE_
symbols shown
in the following table.
Type Value | Type Description |
MYSQL_TYPE_TINY | TINYINT field |
MYSQL_TYPE_SHORT | SMALLINT field |
MYSQL_TYPE_LONG | INTEGER field |
MYSQL_TYPE_INT24 | MEDIUMINT field |
MYSQL_TYPE_LONGLONG | BIGINT field |
MYSQL_TYPE_DECIMAL | DECIMAL or NUMERIC field |
MYSQL_TYPE_NEWDECIMAL | Precision math DECIMAL or NUMERIC
field (MySQL 5.0.3 and up) |
MYSQL_TYPE_FLOAT | FLOAT field |
MYSQL_TYPE_DOUBLE | DOUBLE or REAL field |
MYSQL_TYPE_BIT | BIT field (MySQL 5.0.3 and up) |
MYSQL_TYPE_TIMESTAMP | TIMESTAMP field |
MYSQL_TYPE_DATE | DATE field |
MYSQL_TYPE_TIME | TIME field |
MYSQL_TYPE_DATETIME | DATETIME field |
MYSQL_TYPE_YEAR | YEAR field |
MYSQL_TYPE_STRING | CHAR or BINARY field |
MYSQL_TYPE_VAR_STRING | VARCHAR or VARBINARY field |
MYSQL_TYPE_BLOB | BLOB or TEXT field (use
max_length to determine the
maximum length) |
MYSQL_TYPE_SET | SET field |
MYSQL_TYPE_ENUM | ENUM field |
MYSQL_TYPE_GEOMETRY | Spatial field |
MYSQL_TYPE_NULL | NULL -type field |
MYSQL_TYPE_CHAR | Deprecated; use MYSQL_TYPE_TINY instead |
You can use the IS_NUM()
macro to test
whether a field has a numeric type. Pass the
type
value to IS_NUM()
and it evaluates to TRUE if the field is numeric:
if (IS_NUM(field->type)) printf("Field is numeric\n");
To distinguish between binary and non-binary data for string
data types, check whether the charset_nr
value is 63. If so, the character set is
binary
, which indicates binary rather
than non-binary data. This is how to distinguish between
BINARY
and CHAR
,
VARBINARY
and VARCHAR
,
and BLOB
and TEXT
.
The functions available in the C API are summarized here and described in greater detail in a later section. See Section 22.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_autocommit() | Toggles autocommit mode on/off. |
mysql_change_user() | Changes user and database on an open connection. |
mysql_close() | Closes a server connection. |
mysql_commit() | Commits the transaction. |
mysql_connect() | Connects to a MySQL server. This function is deprecated; use
mysql_real_connect() instead. |
mysql_create_db() | Creates a database. This function is deprecated; use the SQL statement
CREATE DATABASE instead. |
mysql_data_seek() | Seeks to an arbitrary row number in a query result set. |
mysql_debug() | Does a DBUG_PUSH with the given string. |
mysql_drop_db() | Drops a database. This function is deprecated; use the SQL statement
DROP DATABASE instead. |
mysql_dump_debug_info() | Makes the server write debug information to the log. |
mysql_eof() | Determines whether the last row of a result set has been read. This
function is deprecated; mysql_errno()
or mysql_error() may be used instead. |
mysql_errno() | Returns the error number for the most recently invoked MySQL function. |
mysql_error() | Returns the error message for the most recently invoked MySQL function. |
mysql_escape_string() | Escapes special characters in a string for use in an SQL statement. |
mysql_fetch_field() | Returns the type of the next table field. |
mysql_fetch_field_direct() | Returns the type of a table field, given a field number. |
mysql_fetch_fields() | Returns an array of all field structures. |
mysql_fetch_lengths() | Returns the lengths of all columns in the current row. |
mysql_fetch_row() | Fetches the next row from the result set. |
mysql_field_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. |
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_library_end() | Finalize MySQL C API library. |
mysql_library_init() | Initialize MySQL C API library. |
mysql_list_dbs() | Returns database names matching a simple regular expression. |
mysql_list_fields() | Returns field names matching a simple regular expression. |
mysql_list_processes() | Returns a list of the current server threads. |
mysql_list_tables() | Returns table names matching a simple regular expression. |
mysql_more_results() | Checks whether any more results exist. |
mysql_next_result() | Returns/initiates the next result in multiple-statement executions. |
mysql_num_fields() | Returns the number of columns in a result set. |
mysql_num_rows() | Returns the number of rows in a result set. |
mysql_options() | Sets connect options for mysql_connect() . |
mysql_ping() | Checks whether the connection to the server is working, reconnecting as necessary. |
mysql_query() | Executes an SQL query specified as a null-terminated string. |
mysql_real_connect() | Connects to a MySQL server. |
mysql_real_escape_string() | Escapes special characters in a string for use in an SQL statement, taking into account the current character set of the connection. |
mysql_real_query() | Executes an SQL query specified as a counted string. |
mysql_refresh() | Flush or reset tables and caches. |
mysql_reload() | Tells the server to reload the grant tables. |
mysql_rollback() | Rolls back the transaction. |
mysql_row_seek() | Seeks to a row offset in a result set, using value returned from
mysql_row_tell() . |
mysql_row_tell() | Returns the row cursor position. |
mysql_select_db() | Selects a database. |
mysql_server_end() | Finalize embedded server library. |
mysql_server_init() | Initialize embedded server library. |
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. |
Application programs should use this general outline for interacting with MySQL:
Initialize the MySQL library by calling
mysql_library_init()
. The library can be
either the mysqlclient
C client library
or the mysqld
embedded server library,
depending on whether the application was linked with the
-libmysqlclient
or
-libmysqld
flag.
Initialize a connection handler by calling
mysql_init()
and connect to the server by
calling mysql_real_connect()
.
Issue SQL statements and process their results. (The following discussion provides more information about how to do this.)
Close the connection to the MySQL server by calling
mysql_close()
.
End use of the MySQL library by calling
mysql_library_end()
.
The purpose of calling mysql_library_init()
and mysql_library_end()
is to provide proper
initialization and finalization of the MySQL library. For
applications that are linked with the client library, they
provide improved memory management. If you don't call
mysql_library_end()
, a block of memory
remains allocated. (This does not increase the amount of memory
used by the application, but some memory leak detectors will
complain about it.) For applications that are linked with the
embedded server, these calls start and stop the server.
mysql_library_init()
and
mysql_library_end()
are available as of MySQL
5.0.3. These actually are #define
symbols
that make them equivalent to
mysql_server_init()
and
mysql_server_end()
, but the names more
clearly indicate that they should be called when beginning and
ending use of a MySQL library no matter whether the application
uses the mysqlclient
or
mysqld
library. For older versions of MySQL,
you can call mysql_server_init()
and
mysql_server_end()
instead.
If you like, the call to mysql_library_init()
may be omitted, because mysql_init()
will
invoke it automatically as necessary.
To connect to the server, call mysql_init()
to initialize a connection handler, then call
mysql_real_connect()
with that handler (along
with other information such as the hostname, username, and
password). Upon connection,
mysql_real_connect()
sets the
reconnect
flag (part of the
MYSQL
structure) to a value of
1
in versions of the API older than 5.0.3, or
0
in newer versions. A value of
1
for this flag indicates that if a statement
cannot be performed because of a lost connection, to try
reconnecting to the server before giving up. As of MySQL 5.0.13,
you can use the MYSQL_OPT_RECONNECT
option to
mysql_options()
to control reconnection
behavior. When you are done with the connection, call
mysql_close()
to terminate it.
While a connection is active, the client may send SQL statements
to the server using mysql_query()
or
mysql_real_query()
. The difference between
the two is that mysql_query()
expects the
query to be specified as a null-terminated string whereas
mysql_real_query()
expects a counted string.
If the string contains binary data (which may include null
bytes), you must use mysql_real_query()
.
For each non-SELECT
query (for example,
INSERT
, UPDATE
,
DELETE
), you can find out how many rows were
changed (affected) by calling
mysql_affected_rows()
.
For SELECT
queries, you retrieve the selected
rows as a result set. (Note that some statements are
SELECT
-like in that they return rows. These
include SHOW
, DESCRIBE
,
and EXPLAIN
. They should be treated the same
way as SELECT
statements.)
There are two ways for a client to process result sets. One way
is to retrieve the entire result set all at once by calling
mysql_store_result()
. This function acquires
from the server all the rows returned by the query and stores
them in the client. The second way is for the client to initiate
a row-by-row result set retrieval by calling
mysql_use_result()
. This function initializes
the retrieval, but does not actually get any rows from the
server.
In both cases, you access rows by calling
mysql_fetch_row()
. With
mysql_store_result()
,
mysql_fetch_row()
accesses rows that have
previously been fetched from the server. With
mysql_use_result()
,
mysql_fetch_row()
actually retrieves the row
from the server. Information about the size of the data in each
row is available by calling
mysql_fetch_lengths()
.
After you are done with a result set, call
mysql_free_result()
to free the memory used
for it.
The two retrieval mechanisms are complementary. Client programs
should choose the approach that is most appropriate for their
requirements. In practice, clients tend to use
mysql_store_result()
more commonly.
An advantage of mysql_store_result()
is that
because the rows have all been fetched to the client, you not
only can access rows sequentially, you can move back and forth
in the result set using mysql_data_seek()
or
mysql_row_seek()
to change the current row
position within the result set. You can also find out how many
rows there are by calling mysql_num_rows()
.
On the other hand, the memory requirements for
mysql_store_result()
may be very high for
large result sets and you are more likely to encounter
out-of-memory conditions.
An advantage of mysql_use_result()
is that
the client requires less memory for the result set because it
maintains only one row at a time (and because there is less
allocation overhead, mysql_use_result()
can
be faster). Disadvantages are that you must process each row
quickly to avoid tying up the server, you don't have random
access to rows within the result set (you can only access rows
sequentially), and you don't know how many rows are in the
result set until you have retrieved them all. Furthermore, you
must retrieve all the rows even
if you determine in mid-retrieval that you've found the
information you were looking for.
The API makes it possible for clients to respond appropriately
to statements (retrieving rows only as necessary) without
knowing whether the statement is a SELECT
.
You can do this by calling
mysql_store_result()
after each
mysql_query()
(or
mysql_real_query()
). If the result set call
succeeds, the statement was a SELECT
and you
can read the rows. If the result set call fails, call
mysql_field_count()
to determine whether a
result was actually to be expected. If
mysql_field_count()
returns zero, the
statement returned no data (indicating that it was an
INSERT
, UPDATE
,
DELETE
, and so forth), and was not expected
to return rows. If mysql_field_count()
is
non-zero, the statement should have returned rows, but didn't.
This indicates that the statement was a
SELECT
that failed. See the description for
mysql_field_count()
for an example of how
this can be done.
Both mysql_store_result()
and
mysql_use_result()
allow you to obtain
information about the fields that make up the result set (the
number of fields, their names and types, and so forth). You can
access field information sequentially within the row by calling
mysql_fetch_field()
repeatedly, or by field
number within the row by calling
mysql_fetch_field_direct()
. The current field
cursor position may be changed by calling
mysql_field_seek()
. Setting the field cursor
affects subsequent calls to
mysql_fetch_field()
. You can also get
information for fields all at once by calling
mysql_fetch_fields()
.
For detecting and reporting errors, MySQL provides access to
error information by means of the
mysql_errno()
and
mysql_error()
functions. These return the
error code or error message for the most recently invoked
function that can succeed or fail, allowing you to determine
when an error occurred and what it was.
mysql_affected_rows()
mysql_autocommit()
mysql_change_user()
mysql_character_set_name()
mysql_close()
mysql_commit()
mysql_connect()
mysql_create_db()
mysql_data_seek()
mysql_debug()
mysql_drop_db()
mysql_dump_debug_info()
mysql_eof()
mysql_errno()
mysql_error()
mysql_escape_string()
mysql_fetch_field()
mysql_fetch_field_direct()
mysql_fetch_fields()
mysql_fetch_lengths()
mysql_fetch_row()
mysql_field_count()
mysql_field_seek()
mysql_field_tell()
mysql_free_result()
mysql_get_character_set_info()
mysql_get_client_info()
mysql_get_client_version()
mysql_get_host_info()
mysql_get_proto_info()
mysql_get_server_info()
mysql_get_server_version()
mysql_hex_string()
mysql_info()
mysql_init()
mysql_insert_id()
mysql_kill()
mysql_library_end()
mysql_library_init()
mysql_list_dbs()
mysql_list_fields()
mysql_list_processes()
mysql_list_tables()
mysql_more_results()
mysql_next_result()
mysql_num_fields()
mysql_num_rows()
mysql_options()
mysql_ping()
mysql_query()
mysql_real_connect()
mysql_real_escape_string()
mysql_real_query()
mysql_refresh()
mysql_reload()
mysql_rollback()
mysql_row_seek()
mysql_row_tell()
mysql_select_db()
mysql_set_character_set()
mysql_set_server_option()
mysql_shutdown()
mysql_sqlstate()
mysql_ssl_set()
mysql_stat()
mysql_store_result()
mysql_thread_id()
mysql_use_result()
mysql_warning_count()
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()
.
my_ulonglong mysql_affected_rows(MYSQL
*mysql)
Description
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()
.
Return Values
An integer greater than zero indicates the number of rows
affected or retrieved. Zero indicates that no records were
updated for an UPDATE
statement, no rows
matched the WHERE
clause in the query or
that no query has yet been executed. -1 indicates that the
query returned an error or that, for a
SELECT
query,
mysql_affected_rows()
was called prior to
calling mysql_store_result()
. Because
mysql_affected_rows()
returns an unsigned
value, you can check for -1 by comparing the return value to
(my_ulonglong)-1
(or to
(my_ulonglong)~0
, which is equivalent).
Errors
None.
Example
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()
returns 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, 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.
my_bool mysql_autocommit(MYSQL *mysql, my_bool
mode)
Description
Sets autocommit mode on if mode
is 1, off
if mode
is 0.
Return Values
Zero if successful. Non-zero if an error occurred.
Errors
None.
my_bool mysql_change_user(MYSQL *mysql, const char
*user, const char *password, const char *db)
Description
Changes the user and causes the database specified by
db
to become the default (current) database
on the connection specified by mysql
. In
subsequent queries, this database is the default for table
references that do not include an explicit database specifier.
mysql_change_user()
fails if the connected
user cannot be authenticated or doesn't have permission to use
the database. In this case, the user and database are not
changed
The db
parameter may be set to
NULL
if you don't want to have a default
database.
This command always performs a ROLLBACK
of
any active transactions, closes all temporary tables, unlocks
all locked tables and resets the state as if one had done a
new connect. This happens even if the user didn't change.
Return Values
Zero for success. Non-zero if an error occurred.
Errors
The same that you can get from
mysql_real_connect()
.
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
ER_UNKNOWN_COM_ERROR
The MySQL server doesn't implement this command (probably an old server).
ER_ACCESS_DENIED_ERROR
The user or password was wrong.
ER_BAD_DB_ERROR
The database didn't exist.
ER_DBACCESS_DENIED_ERROR
The user did not have access rights to the database.
ER_WRONG_DB_NAME
The database name was too long.
Example
if (mysql_change_user(&mysql, "user", "password", "new_database")) { fprintf(stderr, "Failed to change user. Error: %s\n", mysql_error(&mysql)); }
const char *mysql_character_set_name(MYSQL
*mysql)
Description
Returns the default character set for the current connection.
Return Values
The default character set
Errors
None.
void mysql_close(MYSQL *mysql)
Description
Closes a previously opened connection.
mysql_close()
also deallocates the
connection handle pointed to by mysql
if
the handle was allocated automatically by
mysql_init()
or
mysql_connect()
.
Return Values
None.
Errors
None.
my_bool mysql_commit(MYSQL *mysql)
Description
Commits the current transaction.
As of MySQL 5.0.3, the action of this function is subject to
the value of the completion_type
system
variable. In particular, if the value of
completion_type
is 2, the server performs a
release after terminating a transaction and closes the client
connection. The client program should call
mysql_close()
to close the connection from
the client side.
Return Values
Zero if successful. Non-zero if an error occurred.
Errors
None.
MYSQL *mysql_connect(MYSQL *mysql, const char *host,
const char *user, const char *passwd)
Description
This function is deprecated. It is preferable to use
mysql_real_connect()
instead.
mysql_connect()
attempts to establish a
connection to a MySQL database engine running on
host
. mysql_connect()
must complete successfully before you can execute any of the
other API functions, with the exception of
mysql_get_client_info()
.
The meanings of the parameters are the same as for the
corresponding parameters for
mysql_real_connect()
with the difference
that the connection parameter may be NULL
.
In this case, the C API allocates memory for the connection
structure automatically and frees it when you call
mysql_close()
. The disadvantage of this
approach is that you can't retrieve an error message if the
connection fails. (To get error information from
mysql_errno()
or
mysql_error()
, you must provide a valid
MYSQL
pointer.)
Return Values
Same as for mysql_real_connect()
.
Errors
Same as for mysql_real_connect()
.
int mysql_create_db(MYSQL *mysql, const char
*db)
Description
Creates the database named by the db
parameter.
This function is deprecated. It is preferable to use
mysql_query()
to issue an SQL
CREATE DATABASE
statement instead.
Return Values
Zero if the database was created successfully. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
if(mysql_create_db(&mysql, "my_database")) { fprintf(stderr, "Failed to create new database. Error: %s\n", mysql_error(&mysql)); }
void mysql_data_seek(MYSQL_RES *result, my_ulonglong
offset)
Description
Seeks to an arbitrary row in a query result set. The
offset
value is a row number and should be
in the range from 0
to
mysql_num_rows(result)-1
.
This function requires that the result set structure contains
the entire result of the query, so
mysql_data_seek()
may be used only in
conjunction with mysql_store_result()
, not
with mysql_use_result()
.
Return Values
None.
Errors
None.
void mysql_debug(const char *debug)
Description
Does a DBUG_PUSH
with the given string.
mysql_debug()
uses the Fred Fish debug
library. To use this function, you must compile the client
library to support debugging. See
Section E.1, “Debugging a MySQL Server”, and
Section E.2, “Debugging a MySQL Client”.
Return Values
None.
Errors
None.
Example
The call shown here causes the client library to generate a
trace file in /tmp/client.trace
on the
client machine:
mysql_debug("d:t:O,/tmp/client.trace");
int mysql_drop_db(MYSQL *mysql, const char
*db)
Description
Drops the database named by the db
parameter.
This function is deprecated. It is preferable to use
mysql_query()
to issue an SQL DROP
DATABASE
statement instead.
Return Values
Zero if the database was dropped successfully. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
if(mysql_drop_db(&mysql, "my_database")) fprintf(stderr, "Failed to drop the database: Error: %s\n", mysql_error(&mysql));
int mysql_dump_debug_info(MYSQL *mysql)
Description
Instructs the server to write some debug information to the
log. For this to work, the connected user must have the
SUPER
privilege.
Return Values
Zero if the command was successful. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
my_bool mysql_eof(MYSQL_RES *result)
Description
This function is deprecated. mysql_errno()
or mysql_error()
may be used instead.
mysql_eof()
determines whether the last row
of a result set has been read.
If you acquire a result set from a successful call to
mysql_store_result()
, the client receives
the entire set in one operation. In this case, a
NULL
return from
mysql_fetch_row()
always means the end of
the result set has been reached and it is unnecessary to call
mysql_eof()
. When used with
mysql_store_result()
,
mysql_eof()
always returns true.
On the other hand, if you use
mysql_use_result()
to initiate a result set
retrieval, the rows of the set are obtained from the server
one by one as you call mysql_fetch_row()
repeatedly. Because an error may occur on the connection
during this process, a NULL
return value
from mysql_fetch_row()
does not necessarily
mean the end of the result set was reached normally. In this
case, you can use mysql_eof()
to determine
what happened. mysql_eof()
returns a
non-zero value if the end of the result set was reached and
zero if an error occurred.
Historically, mysql_eof()
predates the
standard MySQL error functions
mysql_errno()
and
mysql_error()
. Because those error
functions provide the same information, their use is preferred
over mysql_eof()
, which is deprecated. (In
fact, they provide more information, because
mysql_eof()
returns only a boolean value
whereas the error functions indicate a reason for the error
when one occurs.)
Return Values
Zero if no error occurred. Non-zero if the end of the result set has been reached.
Errors
None.
Example
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)); }
unsigned int mysql_errno(MYSQL *mysql)
Description
For the connection specified by mysql
,
mysql_errno()
returns the error code for
the most recently invoked API function that can succeed or
fail. A return value of zero means that no error occurred.
Client error message numbers are listed in the MySQL
errmsg.h
header file. Server error
message numbers are listed in
mysqld_error.h
. Errors also are listed at
Appendix B, Error Codes and Messages.
Note that some functions like
mysql_fetch_row()
don't set
mysql_errno()
if they succeed.
A rule of thumb is that all functions that have to ask the
server for information reset mysql_errno()
if they succeed.
Return Values
An error code value for the last
mysql_
call, if it failed. zero means no error occurred.
xxx
()
Errors
None.
const char *mysql_error(MYSQL *mysql)
Description
For the connection specified by mysql
,
mysql_error()
returns a null-terminated
string containing the error message for the most recently
invoked API function that failed. If a function didn't fail,
the return value of mysql_error()
may be
the previous error or an empty string to indicate no error.
A rule of thumb is that all functions that have to ask the
server for information reset mysql_error()
if they succeed.
For functions that reset mysql_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.11.2, “Setting the Error Message Language”.
Return Values
A null-terminated character string that describes the error. An empty string if no error occurred.
Errors
None.
You should use mysql_real_escape_string()
instead!
This function is identical to
mysql_real_escape_string()
except that
mysql_real_escape_string()
takes a
connection handler as its first argument and escapes the
string according to the current character set.
mysql_escape_string()
does not take a
connection argument and does not respect the current character
set.
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES
*result)
Description
Returns the definition of one column of a result set as a
MYSQL_FIELD
structure. Call this function
repeatedly to retrieve information about all columns in the
result set. mysql_fetch_field()
returns
NULL
when no more fields are left.
mysql_fetch_field()
is reset to return
information about the first field each time you execute a new
SELECT
query. The field returned by
mysql_fetch_field()
is also affected by
calls to mysql_field_seek()
.
If you've called mysql_query()
to perform a
SELECT
on a table but have not called
mysql_store_result()
, MySQL returns the
default blob length (8KB) if you call
mysql_fetch_field()
to ask for the length
of a BLOB
field. (The 8KB size is chosen
because MySQL doesn't know the maximum length for the
BLOB
. This should be made configurable
sometime.) Once you've retrieved the result set,
field->max_length
contains the length of
the largest value for this column in the specific query.
Return Values
The MYSQL_FIELD
structure for the current
column. NULL
if no columns are left.
Errors
None.
Example
MYSQL_FIELD *field; while((field = mysql_fetch_field(result))) { printf("field name %s\n", field->name); }
MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES
*result, unsigned int fieldnr)
Description
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
.
Return Values
The MYSQL_FIELD
structure for the specified
column.
Errors
None.
Example
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_FIELD *mysql_fetch_fields(MYSQL_RES
*result)
Description
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.
Return Values
An array of MYSQL_FIELD
structures for all
columns of a result set.
Errors
None.
Example
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); }
unsigned long *mysql_fetch_lengths(MYSQL_RES
*result)
Description
Returns the lengths of the columns of the current row within a
result set. If you plan to copy field values, this length
information is also useful for optimization, because you can
avoid calling strlen()
. In addition, if the
result set contains binary data, you
must use this function to
determine the size of the data, because
strlen()
returns incorrect results for any
field containing null characters.
The length for empty columns and for columns containing
NULL
values is zero. To see how to
distinguish these two cases, see the description for
mysql_fetch_row()
.
Return Values
An array of unsigned long integers representing the size of
each column (not including any terminating null characters).
NULL
if an error occurred.
Errors
mysql_fetch_lengths()
is valid only for the
current row of the result set. It returns
NULL
if you call it before calling
mysql_fetch_row()
or after retrieving all
rows in the result.
Example
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_ROW mysql_fetch_row(MYSQL_RES
*result)
Description
Retrieves the next row of a result set. When used after
mysql_store_result()
,
mysql_fetch_row()
returns
NULL
when there are no more rows to
retrieve. When used after
mysql_use_result()
,
mysql_fetch_row()
returns
NULL
when there are no more rows to
retrieve or if an error occurred.
The number of values in the row is given by
mysql_num_fields(result)
. If
row
holds the return value from a call to
mysql_fetch_row()
, pointers to the values
are accessed as row[0]
to
row[mysql_num_fields(result)-1]
.
NULL
values in the row are indicated by
NULL
pointers.
The lengths of the field values in the row may be obtained by
calling mysql_fetch_lengths()
. Empty fields
and fields containing NULL
both have length
0; you can distinguish these by checking the pointer for the
field value. If the pointer is NULL
, the
field is NULL
; otherwise, the field is
empty.
Return Values
A MYSQL_ROW
structure for the next row.
NULL
if there are no more rows to retrieve
or if an error occurred.
Errors
Note that error is not reset between calls to
mysql_fetch_row()
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
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"); }
unsigned int mysql_field_count(MYSQL
*mysql)
Description
Returns the number of columns for the most recent query on the connection.
The normal use of this function is when
mysql_store_result()
returned
NULL
(and thus you have no result set
pointer). In this case, you can call
mysql_field_count()
to determine whether
mysql_store_result()
should have produced a
non-empty result. This allows the client program to take
proper action without knowing whether the query was a
SELECT
(or SELECT
-like)
statement. The example shown here illustrates how this may be
done.
Return Values
An unsigned integer representing the number of columns in a result set.
Errors
None.
Example
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_OFFSET mysql_field_seek(MYSQL_RES
*result, MYSQL_FIELD_OFFSET offset)
Description
Sets the field cursor to the given offset. The next call to
mysql_fetch_field()
retrieves the field
definition of the column associated with that offset.
To seek to the beginning of a row, pass an
offset
value of zero.
Return Values
The previous value of the field cursor.
Errors
None.
MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES
*result)
Description
Returns the position of the field cursor used for the last
mysql_fetch_field()
. This value can be used
as an argument to mysql_field_seek()
.
Return Values
The current offset of the field cursor.
Errors
None.
void mysql_free_result(MYSQL_RES *result)
Description
Frees the memory allocated for a result set by
mysql_store_result()
,
mysql_use_result()
,
mysql_list_dbs()
, and so forth. When you
are done with a result set, you must free the memory it uses
by calling mysql_free_result()
.
Do not attempt to access a result set after freeing it.
Return Values
None.
Errors
None.
void mysql_get_character_set_info(MYSQL *mysql,
MY_CHARSET_INFO *cs)
Description
This function provides information about the default client
character set. The default character set may be changed with
the mysql_set_character_set()
function.
This function was added in MySQL 5.0.10.
Example
if (!mysql_set_character_set(&mysql, "utf8")) { MY_CHARSET_INFO cs; mysql_get_character_set_info(&mysql, &cs); printf("character set information:\n"); printf("character set name: %s\n", cs.name); printf("collation name: %s\n", cs.csname); printf("comment: %s\n", cs.comment); printf("directory: %s\n", cs.dir); printf("multi byte character min. length: %d\n", cs.mbminlen); printf("multi byte character max. length: %d\n", cs.mbmaxlen); }
char *mysql_get_client_info(void)
Description
Returns a string that represents the client library version.
Return Values
A character string that represents the MySQL client library version.
Errors
None.
unsigned long
mysql_get_client_version(void)
Description
Returns an integer that represents the client library version.
The value has the format XYYZZ
where
X
is the major version,
YY
is the release level, and
ZZ
is the version number within the release
level. For example, a value of 40102
represents a client library version of
4.1.2
.
Return Values
An integer that represents the MySQL client library version.
Errors
None.
char *mysql_get_host_info(MYSQL *mysql)
Description
Returns a string describing the type of connection in use, including the server hostname.
Return Values
A character string representing the server hostname and the connection type.
Errors
None.
unsigned int mysql_get_proto_info(MYSQL
*mysql)
Description
Returns the protocol version used by current connection.
Return Values
An unsigned integer representing the protocol version used by the current connection.
Errors
None.
char *mysql_get_server_info(MYSQL *mysql)
Description
Returns a string that represents the server version number.
Return Values
A character string that represents the server version number.
Errors
None.
unsigned long mysql_get_server_version(MYSQL
*mysql)
Description
Returns the version number of the server as an integer.
Return Values
A number that represents the MySQL server version in this format:
major_version*10000 + minor_version *100 + sub_version
For example, 5.0.12 is returned as 50012.
This function is useful in client programs for quickly determining whether some version-specific server capability exists.
Errors
None.
unsigned long mysql_hex_string(char *to, const char
*from, unsigned long length)
Description
This function is used to create a legal SQL string that you can use in an 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
is a null-terminated string. The return
value is the length of the encoded string, not including the
terminating null character.
The return value can be placed into an SQL statement using
either 0x
or value
X'
format. However, the return value does not include the
value
'0x
or X'...'
. The caller
must supply whichever of those is desired.
Example
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.
Return Values
The length of the value placed into to
, not
including the terminating null character.
Errors
None.
char *mysql_info(MYSQL *mysql)
Description
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 contains values
appropriate for the query.
INSERT INTO ... SELECT ...
String format: Records: 100 Duplicates: 0
Warnings: 0
INSERT INTO ... VALUES
(...),(...),(...)...
String format: Records: 3 Duplicates: 0 Warnings:
0
LOAD DATA INFILE ...
String format: Records: 1 Deleted: 0 Skipped: 0
Warnings: 0
ALTER TABLE
String format: Records: 3 Duplicates: 0 Warnings:
0
UPDATE
String format: Rows matched: 40 Changed: 40
Warnings: 0
Note that mysql_info()
returns a
non-NULL
value for INSERT ...
VALUES
only for the multiple-row form of the
statement (that is, only if multiple value lists are
specified).
Return Values
A character string representing additional information about
the most recently executed query. NULL
if
no information is available for the query.
Errors
None.
MYSQL *mysql_init(MYSQL *mysql)
Description
Allocates or initializes a MYSQL
object
suitable for mysql_real_connect()
. If
mysql
is a NULL
pointer,
the function allocates, initializes, and returns a new object.
Otherwise, the object is initialized and the address of the
object is returned. If mysql_init()
allocates a new object, it is freed when
mysql_close()
is called to close the
connection.
Return Values
An initialized MYSQL*
handle.
NULL
if there was insufficient memory to
allocate a new object.
Errors
In case of insufficient memory, NULL
is
returned.
my_ulonglong mysql_insert_id(MYSQL *mysql)
Description
Returns the value generated for an
AUTO_INCREMENT
column by the previous
INSERT
or UPDATE
statement. Use this function after you have performed an
INSERT
statement into a table that contains
an AUTO_INCREMENT
field.
More precisely, mysql_insert_id()
is
updated under these conditions:
INSERT
statements that store a value
into an AUTO_INCREMENT
column. This is
true whether the value is automatically generated by
storing the special values NULL
or
0
into the column, or is an explicit
non-special value.
In the case of a multiple-row INSERT
statement, mysql_insert_id()
returns
the first automatically
generated AUTO_INCREMENT
value; if no
such value is generated, it returns the last
last explicit value
inserted into the AUTO_INCREMENT
column.
INSERT
statements that generate an
AUTO_INCREMENT
value by inserting
LAST_INSERT_ID(
into any column.
expr
)
INSERT
statements that generate an
AUTO_INCREMENT
value by updating any
column to
LAST_INSERT_ID(
.
expr
)
The value of mysql_insert_id()
is not
affected by statements such as SELECT
that return a result set.
If the previous statement returned an error, the value of
mysql_insert_id()
is undefined.
Note that mysql_insert_id()
returns
0
if the previous statement does not use an
AUTO_INCREMENT
value. If you need to save
the value for later, be sure to call
mysql_insert_id()
immediately after the
statement that generates the value.
The value of mysql_insert_id()
is affected
only by statements issued within the current client
connection. It is not affected by statements issued by other
clients.
See Section 12.9.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.
Return Values
Described in the preceding discussion.
Errors
None.
int mysql_kill(MYSQL *mysql, unsigned long
pid)
Description
Asks the server to kill the thread specified by
pid
.
Return Values
Zero for success. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
void mysql_library_end(void)
Description
This is a synonym for the
mysql_server_end()
function. It was added
in MySQL 5.0.3.
See Section 22.2.2, “C API Function Overview”, for usage information.
int mysql_library_init(int argc, char **argv, char
**groups)
Description
This is a synonym for the
mysql_server_init()
function. It was added
in MySQL 5.0.3. See Section 22.2.12.1, “mysql_server_init()
”.
See Section 22.2.2, “C API Function Overview” for usage information.
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char
*wild)
Description
Returns a result set consisting of database names on the
server that match the simple regular expression specified by
the wild
parameter. wild
may contain the wildcard characters
‘%
’ or
‘_
’, or may be a
NULL
pointer to match all databases.
Calling mysql_list_dbs()
is similar to
executing the query SHOW databases [LIKE
wild]
.
You must free the result set with
mysql_free_result()
.
Return Values
A MYSQL_RES
result set for success.
NULL
if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char
*table, const char *wild)
Description
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
instead of
tbl_name
mysql_list_fields()
.
You must free the result set with
mysql_free_result()
.
Return Values
A MYSQL_RES
result set for success.
NULL
if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
MYSQL_RES *mysql_list_processes(MYSQL
*mysql)
Description
Returns a result set describing the current server threads.
This is the same kind of information as that reported by
mysqladmin processlist or a SHOW
PROCESSLIST
query.
You must free the result set with
mysql_free_result()
.
Return Values
A MYSQL_RES
result set for success.
NULL
if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char
*wild)
Description
Returns a result set consisting of table names in the current
database that match the simple regular expression specified by
the wild
parameter. wild
may contain the wildcard characters
‘%
’ or
‘_
’, or may be a
NULL
pointer to match all tables. Calling
mysql_list_tables()
is similar to executing
the query SHOW tables [LIKE
.
wild
]
You must free the result set with
mysql_free_result()
.
Return Values
A MYSQL_RES
result set for success.
NULL
if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
my_bool mysql_more_results(MYSQL *mysql)
Description
Returns true if more results exist from the currently executed
query, and the application must call
mysql_next_result()
to fetch the results.
Return Values
TRUE
(1) if more results exist.
FALSE
(0) if no more results exist.
In most cases, you can call
mysql_next_result()
instead to test whether
more results exist and initiate retrieval if so.
See Section 22.2.9, “C API Handling of Multiple Query Execution”, and
Section 22.2.3.45, “mysql_next_result()
”.
Errors
None.
int mysql_next_result(MYSQL *mysql)
Description
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 call
mysql_store_result()
,
mysql_warning_count()
,
mysql_affected_rows()
, and so forth.
If mysql_next_result()
returns an error, no
other statements are executed and there are no more results to
fetch.
See Section 22.2.9, “C API Handling of Multiple Query Execution”.
Return Values
Return Value | Description |
0 | Successful and there are more results |
-1 | Successful and there are no more results |
>0 | An error occurred |
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order. For example
if you didn't call mysql_use_result()
for a previous result set.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
unsigned int mysql_num_fields(MYSQL_RES
*result)
To pass a MYSQL*
argument instead, use
unsigned int mysql_field_count(MYSQL
*mysql)
.
Description
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 the query was a
SELECT
(or SELECT
-like)
statement. The example shown here illustrates how this may be
done.
Return Values
An unsigned integer representing the number of columns in a result set.
Errors
None.
Example
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 happens only if something went wrong.
my_ulonglong mysql_num_rows(MYSQL_RES
*result)
Description
Returns the number of rows in the result set.
The use of mysql_num_rows()
depends on
whether you use mysql_store_result()
or
mysql_use_result()
to return the result
set. If you use mysql_store_result()
,
mysql_num_rows()
may be called immediately.
If you use mysql_use_result()
,
mysql_num_rows()
does not return the
correct value until all the rows in the result set have been
retrieved.
Return Values
The number of rows in the result set.
Errors
None.
int mysql_options(MYSQL *mysql, enum mysql_option
option, const char *arg)
Description
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_GUESS_CONNECTION | Not used | For an application linked against libmysqld , this
allows the library to guess whether to use the
embedded server or a remote server.
“Guess” means that if the hostname is set
and is not localhost , it uses a
remote server. This behavior is the default.
MYSQL_OPT_USE_EMBEDDED_CONNECTION
and MYSQL_OPT_USE_REMOTE_CONNECTION
can be used to override it. This option is ignored for
applications linked against
libmysqlclient . |
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 . |
MYSQL_OPT_READ_TIMEOUT | unsigned int * | Timeout for reads from server (works currently only on Windows on TCP/IP connections). |
MYSQL_OPT_RECONNECT | my_bool * | Enable or disable automatic reconnection to the server if the connection is found to have been lost. Reconnect has been off by default since MySQL 5.0.3; this option is new in 5.0.13 and provides a way to set reconnection behavior explicitly. |
MYSQL_OPT_SET_CLIENT_IP | char * | For an application linked against linked against
libmysqld (with
libmysqld compiled with
authentication support), this means that the user is
considered to have connected from the specified IP
address (specified as a string) for authentication
purposes. This option is ignored for applications
linked against libmysqlclient . |
MYSQL_OPT_USE_EMBEDDED_CONNECTION | Not used | For an application linked against libmysqld , this
forces the use of the embedded server for the
connection. This option is ignored for applications
linked against libmysqlclient . |
MYSQL_OPT_USE_REMOTE_CONNECTION | Not used | For an application linked against libmysqld , this
forces the use of a remote server for the connection.
This option is ignored for applications linked against
libmysqlclient . |
MYSQL_OPT_USE_RESULT | Not used | This option is unused. |
MYSQL_OPT_WRITE_TIMEOUT | unsigned int * | Timeout for writes to server (works currently only on Windows on TCP/IP connections). |
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) Added in 5.0.3. |
MYSQL_SECURE_AUTH | my_bool* | Whether to connect to a server that does not support the password hashing used in MySQL 4.1.1 and later. |
MYSQL_SET_CHARSET_DIR | 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 to
connect to. |
Note that the client
group is always read
if you use MYSQL_READ_DEFAULT_FILE
or
MYSQL_READ_DEFAULT_GROUP
.
The specified group in the option file may contain the following options:
Option | Description |
connect-timeout | Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server. |
compress | Use the compressed client/server protocol. |
database | Connect to this database if no database was specified in the connect command. |
debug | Debug options. |
disable-local-infile | Disable use of LOAD DATA LOCAL . |
host | Default hostname. |
init-command | 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 22.2.3.51, “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. |
multi-statements | Allow the client to send multiple statements in a single string
(separated by ‘; ’). |
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 the server. |
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= | Shared-memory name to use to connect to server (default is "MYSQL"). |
socket | Default socket file. |
user | Default user. |
Note that timeout
has been replaced by
connect-timeout
, but
timeout
is still supported in MySQL
5.0.19 for backward compatibility.
For more information about option files, see Section 4.3.2, “Using Option Files”.
Return Values
Zero for success. Non-zero if you used an unknown option.
Example
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.
int mysql_ping(MYSQL *mysql)
Description
Checks whether the connection to the server is working. If the connection 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.
Return Values
Zero if the connection to the server is alive. Non-zero if an error occurred. A non-zero return does not indicate whether the MySQL server itself is down; the connection might be broken for other reasons such as network problems.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_UNKNOWN_ERROR
An unknown error occurred.
int mysql_query(MYSQL *mysql, const char
*query)
Description
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 22.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 22.2.3.22, “mysql_field_count()
”.
Return Values
Zero if the query was successful. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
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)
Description
mysql_real_connect()
attempts to establish
a connection to a MySQL database engine running on
host
.
mysql_real_connect()
must complete
successfully before you can execute any other API functions
that require a valid MYSQL
connection
handle structure.
The parameters are specified as follows:
The first parameter should be the address of an existing
MYSQL
structure. Before calling
mysql_real_connect()
you must call
mysql_init()
to initialize the
MYSQL
structure. You can change a lot
of connect options with the
mysql_options()
call. See
Section 22.2.3.48, “mysql_options()
”.
The value of host
may be either a
hostname or an IP address. If host
is
NULL
or the string
"localhost"
, a connection to the local
host is assumed. If the OS supports sockets (Unix) or
named pipes (Windows), they are used instead of TCP/IP to
connect to the server.
The user
parameter contains the user's
MySQL login ID. If user
is
NULL
or the empty string
""
, the current user is assumed. Under
Unix, this is the current login name. Under Windows ODBC,
the current username must be specified explicitly. See
Section 23.1.9.2, “Configuring a MyODBC DSN on Windows”.
The passwd
parameter contains the
password for user
. If
passwd
is NULL
, only
entries in the user
table for the user
that have a blank (empty) password field are checked for a
match. This allows the database administrator to set up
the MySQL privilege system in such a way that users get
different privileges depending on whether they have
specified a password.
Note: Do not attempt to
encrypt the password before calling
mysql_real_connect()
; password
encryption is handled automatically by the client API.
db
is the database name. If
db
is not NULL
, the
connection sets the default database to this value.
If port
is not 0, the value is used as
the port number for the TCP/IP connection. Note that the
host
parameter determines the type of
the connection.
If unix_socket
is not
NULL
, the string specifies the socket
or named pipe that should be used. Note that the
host
parameter determines the type of
the connection.
The value of client_flag
is usually 0,
but can be set to a combination of the following flags to
enable certain features:
Flag Name | Flag Description |
CLIENT_COMPRESS | Use compression protocol. |
CLIENT_FOUND_ROWS | Return the number of found (matched) rows, not the number of 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 is 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. |
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. |
CLIENT_NO_SCHEMA | Don't allow the db_name.tbl_name.col_name
syntax. This is for ODBC. It causes the parser to
generate an error if you use that syntax, which is
useful for trapping bugs in some ODBC programs. |
CLIENT_ODBC | The client is an ODBC client. This changes mysqld to be more ODBC-friendly. |
CLIENT_SSL | Use SSL (encrypted protocol). This option should not be set by
application programs; it is set internally in the
client library. Instead, use
mysql_ssl_set() before calling
mysql_real_connect() . |
For some parameters, it is possible to have the value taken
from an option file rather than from an explicit value in the
mysql_real_connect()
call. To do this, call
mysql_options()
with the
MYSQL_READ_DEFAULT_FILE
or
MYSQL_READ_DEFAULT_GROUP
option before
calling mysql_real_connect()
. Then, in the
mysql_real_connect()
call, specify the
“no-value” value for each parameter to be read
from an option file:
For host
, specify a value of
NULL
or the empty string
(""
).
For user
, specify a value of
NULL
or the empty string.
For passwd
, specify a value of
NULL
. (For the password, a value of the
empty string in the
mysql_real_connect()
call cannot be
overridden in an option file, because the empty string
indicates explicitly that the MySQL account must have an
empty password.)
For db
, specify a value of
NULL
or the empty string.
For port
, specify a value of 0.
For unix_socket
, specify a value of
NULL
.
If no value is found in an option file for a parameter, its default value is used as indicated in the descriptions given earlier in this section.
Return Values
A MYSQL*
connection handle if the
connection was successful, NULL
if the
connection was unsuccessful. For a successful connection, the
return value is the same as the value of the first parameter.
Errors
CR_CONN_HOST_ERROR
Failed to connect to the MySQL server.
CR_CONNECTION_ERROR
Failed to connect to the local MySQL server.
CR_IPSOCK_ERROR
Failed to create an IP socket.
CR_OUT_OF_MEMORY
Out of memory.
CR_SOCKET_CREATE_ERROR
Failed to create a Unix socket.
CR_UNKNOWN_HOST
Failed to find the IP address for the hostname.
CR_VERSION_ERROR
A protocol mismatch resulted from attempting to connect to
a server with a client library that uses a different
protocol version. This can happen if you use a very old
client library to connect to a new server that wasn't
started with the --old-protocol
option.
CR_NAMEDPIPEOPEN_ERROR
Failed to create a named pipe on Windows.
CR_NAMEDPIPEWAIT_ERROR
Failed to wait for a named pipe on Windows.
CR_NAMEDPIPESETSTATE_ERROR
Failed to get a pipe handler on Windows.
CR_SERVER_LOST
If connect_timeout
> 0 and it took
longer than connect_timeout
seconds to
connect to the server or if the server died while
executing the init-command
.
Example
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
reads the [client]
and
[your_prog_name]
sections in the
my.cnf
file which ensures that your
program works, even if someone has set up MySQL in some
non-standard way.
Note that upon connection,
mysql_real_connect()
sets the
reconnect
flag (part of the
MYSQL
structure) to a value of
1
in versions of the API older than 5.0.3,
or 0
in newer versions. A value of
1
for this flag indicates that if a
statement cannot be performed because of a lost connection, to
try reconnecting to the server before giving up. As of MySQL
5.0.13, you can use the MYSQL_OPT_RECONNECT
option to mysql_options()
to control
reconnection behavior.
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.
Description
This function is used to create a legal SQL string that you can use in an 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
is a null-terminated string.
The return value is the length of the encoded string, not
including the terminating null character.
If you need to change the character set of the connection, you
should use the mysql_set_character_set()
function rather than executing a SET NAMES
(or SET CHARACTER SET
) statement.
mysql_set_character_set()
works like
SET NAMES
but also affects the character
set used by mysql_real_escape_string()
,
which SET NAMES
does not.
Example
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.
Return Values
The length of the value placed into to
, not
including the terminating null character.
Errors
None.
int mysql_real_query(MYSQL *mysql, const char *query,
unsigned long length)
Description
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 22.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 22.2.3.22, “mysql_field_count()
”.
Return Values
Zero if the query was successful. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
int mysql_refresh(MYSQL *mysql, unsigned int
options)
Description
This functions flushes tables or caches, or resets replication
server information. The connected user must have the
RELOAD
privilege.
The options
argument is a bit mask composed
from any combination of the following values. Multiple values
can be OR'ed together to perform multiple operations with a
single call.
REFRESH_GRANT
Refresh the grant tables, like FLUSH
PRIVILEGES
.
REFRESH_LOG
Flush the logs, like FLUSH LOGS
.
REFRESH_TABLES
Flush the table cache, like FLUSH
TABLES
.
REFRESH_HOSTS
Flush the host cache, like FLUSH HOSTS
.
REFRESH_STATUS
Reset status variables, like FLUSH
STATUS
.
REFRESH_THREADS
Flush the thread cache.
REFRESH_SLAVE
On a slave replication server, reset the master server
information and restart the slave, like RESET
SLAVE
.
REFRESH_MASTER
On a master replication server, remove the binary log
files listed in the binary log index and truncate the
index file, like RESET MASTER
.
Return Values
Zero for success. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
int mysql_reload(MYSQL *mysql)
Description
Asks the MySQL server to reload the grant tables. The
connected user must have the RELOAD
privilege.
This function is deprecated. It is preferable to use
mysql_query()
to issue an SQL
FLUSH PRIVILEGES
statement instead.
Return Values
Zero for success. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
my_bool mysql_rollback(MYSQL *mysql)
Description
Rolls back the current transaction.
As of MySQL 5.0.3, the action of this function is subject to
the value of the completion_type
system
variable. In particular, if the value of
completion_type
is 2, the server performs a
release after terminating a transaction and closes the client
connection. The client program should call
mysql_close()
to close the connection from
the client side.
Return Values
Zero if successful. Non-zero if an error occurred.
Errors
None.
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result,
MYSQL_ROW_OFFSET offset)
Description
Sets the row cursor to an arbitrary row in a query result set.
The offset
value is a row offset that
should be a value returned from
mysql_row_tell()
or from
mysql_row_seek()
. This value is not a row
number; if you want to seek to a row within a result set by
number, use mysql_data_seek()
instead.
This function requires that the result set structure contains
the entire result of the query, so
mysql_row_seek()
may be used only in
conjunction with mysql_store_result()
, not
with mysql_use_result()
.
Return Values
The previous value of the row cursor. This value may be passed
to a subsequent call to mysql_row_seek()
.
Errors
None.
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES
*result)
Description
Returns the current position of the row cursor for the last
mysql_fetch_row()
. This value can be used
as an argument to mysql_row_seek()
.
You should use mysql_row_tell()
only after
mysql_store_result()
, not after
mysql_use_result()
.
Return Values
The current offset of the row cursor.
Errors
None.
int mysql_select_db(MYSQL *mysql, const char
*db)
Description
Causes the database specified by db
to
become the default (current) database on the connection
specified by mysql
. In subsequent queries,
this database is the default for table references that do not
include an explicit database specifier.
mysql_select_db()
fails unless the
connected user can be authenticated as having permission to
use the database.
Return Values
Zero for success. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
int mysql_set_character_set(MYSQL *mysql, char
*csname)
Description
This function is used to set the default character set for the
current connection. The string csname
specifies a valid character set name. The connection collation
becomes the default collation of the character set. This
function works like the SET NAMES
statement, but also sets the value of
mysql->charset
, and thus affects the
character set used by
mysql_real_escape_string()
This function was added in MySQL 5.0.7.
Return Values
Zero for success. Non-zero if an error occurred.
Example
MYSQL mysql; mysql_init(&mysql); 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)); } if (!mysql_set_charset_name(&mysql, "utf8")) { printf("New client character set: %s\n", mysql_character_set_name(&mysql)); }
int mysql_set_server_option(MYSQL *mysql, enum
enum_mysql_set_option option)
Description
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. |
Return Values
Zero for success. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
ER_UNKNOWN_COM_ERROR
The server didn't support
mysql_set_server_option()
(which is the
case that the server is older than 4.1.1) or the server
didn't support the option one tried to set.
int mysql_shutdown(MYSQL *mysql, enum
enum_shutdown_level shutdown_level)
Description
Asks the database server to shut down. The connected user must
have SHUTDOWN
privileges. The
shutdown_level
argument was added in MySQL
5.0.1. MySQL 5.0 servers support only one type of
shutdown; shutdown_level
must be equal to
SHUTDOWN_DEFAULT
. Additional shutdown
levels are planned to make it possible to choose the desired
level. Dynamically linked executables which have been compiled
with older versions of the libmysqlclient
headers and call mysql_shutdown()
need to
be used with the old libmysqlclient
dynamic
library.
The shutdown process is described in Section 5.2.6, “The MySQL Server Shutdown Process”.
Return Values
Zero for success. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
const char *mysql_sqlstate(MYSQL *mysql)
Description
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
Appendix B, Error Codes and Messages.
Note that not all MySQL errors are mapped to SQLSTATE error
codes. The value 'HY000'
(general error) is
used for unmapped errors.
Return Values
A null-terminated character string containing the SQLSTATE error code.
See Also
See Section 22.2.3.14, “mysql_errno()
”,
Section 22.2.3.15, “mysql_error()
”, and
Section 22.2.7.26, “mysql_stmt_sqlstate()
”.
int mysql_ssl_set(MYSQL *mysql, const char *key,
const char *cert, const char *ca, const char *capath, const
char *cipher)
Description
mysql_ssl_set()
is used for establishing
secure connections using SSL. It must be called before
mysql_real_connect()
.
mysql_ssl_set()
does nothing unless OpenSSL
support is enabled in the client library.
mysql
is the connection handler returned
from mysql_init()
. The other parameters are
specified as follows:
key
is the pathname to the key file.
cert
is the pathname to the certificate
file.
ca
is the pathname to the certificate
authority file.
capath
is the pathname to a directory
that contains trusted SSL CA certificates in pem format.
cipher
is a list of allowable ciphers
to use for SSL encryption.
Any unused SSL parameters may be given as
NULL
.
Return Values
This function always returns 0
. If SSL
setup is incorrect, mysql_real_connect()
returns an error when you attempt to connect.
char *mysql_stat(MYSQL *mysql)
Description
Returns a character string containing information similar to that provided by the mysqladmin status command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.
Return Values
A character string describing the server status.
NULL
if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
MYSQL_RES *mysql_store_result(MYSQL *mysql)
Description
You must call mysql_store_result()
or
mysql_use_result()
for every query that
successfully retrieves data (SELECT
,
SHOW
, DESCRIBE
,
EXPLAIN
, CHECK TABLE
,
and so forth).
You don't have to call mysql_store_result()
or mysql_use_result()
for other queries,
but it does not do any harm or cause any notable performance
degradation if you call
mysql_store_result()
in all cases. You can
detect 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 22.2.3.22, “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.
Return Values
A MYSQL_RES
result structure with the
results. NULL
if an error occurred.
Errors
mysql_store_result()
resets
mysql_error()
and
mysql_errno()
if it succeeds.
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
unsigned long mysql_thread_id(MYSQL *mysql)
Description
Returns the thread ID of the current connection. This value
can be used as an argument to mysql_kill()
to kill the thread.
If the connection is lost and you reconnect with
mysql_ping()
, the thread ID changes. This
means you should not get the thread ID and store it for later.
You should get it when you need it.
Return Values
The thread ID of the current connection.
Errors
None.
MYSQL_RES *mysql_use_result(MYSQL *mysql)
Description
You must call mysql_store_result()
or
mysql_use_result()
for every query that
successfully retrieves data (SELECT
,
SHOW
, DESCRIBE
,
EXPLAIN
).
mysql_use_result()
initiates a result set
retrieval but does not actually read the result set into the
client like mysql_store_result()
does.
Instead, each row must be retrieved individually by making
calls to mysql_fetch_row()
. This reads the
result of a query directly from the server without storing it
in a temporary table or local buffer, which is somewhat faster
and uses much less memory than
mysql_store_result()
. The client allocates
memory only for the current row and a communication buffer
that may grow up to max_allowed_packet
bytes.
On the other hand, you shouldn't use
mysql_use_result()
if you are doing a lot
of processing for each row on the client side, or if the
output is sent to a screen on which the user may type a
^S
(stop scroll). This ties up the server
and prevent other threads from updating any tables from which
the data is being fetched.
When using mysql_use_result()
, you must
execute mysql_fetch_row()
until a
NULL
value is returned, otherwise, the
unfetched rows are returned as part of the result set for your
next query. The C API gives the error Commands out of
sync; you can't run this command now
if you forget
to do this!
You may not use mysql_data_seek()
,
mysql_row_seek()
,
mysql_row_tell()
,
mysql_num_rows()
, or
mysql_affected_rows()
with a result
returned from mysql_use_result()
, nor may
you issue other queries until
mysql_use_result()
has finished. (However,
after you have fetched all the rows,
mysql_num_rows()
accurately returns the
number of rows fetched.)
You must call mysql_free_result()
once you
are done with the result set.
When using the libmysqld
embedded server,
the memory benefits are essentially lost because memory usage
incrementally increases with each row retrieved until
mysql_free_result()
is called.
Return Values
A MYSQL_RES
result structure.
NULL
if an error occurred.
Errors
mysql_use_result()
resets
mysql_error()
and
mysql_errno()
if it succeeds.
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
The MySQL client/server protocol provides for the use of
prepared statements. This capability uses the
MYSQL_STMT
statement handler data structure
returned by the mysql_stmt_init()
initialization function. Prepared execution is an efficient way
to execute a statement more than once. The statement is first
parsed to prepare it for execution. Then it is executed one or
more times at a later time, using the statement handle returned
by the initialization function.
Prepared execution is faster than direct execution for statements executed more than once, primarily because the query is parsed only once. In the case of direct execution, the query is parsed every time it is executed. Prepared execution also can provide a reduction of network traffic because for each execution of the prepared statement, it is necessary only to send the data for the parameters.
Another advantage of prepared statements is that it uses a binary protocol that makes data transfer between client and server more efficient.
The following statements can be used as prepared statements:
CREATE TABLE
, DELETE
,
DO
, INSERT
,
REPLACE
, SELECT
,
SET
, UPDATE
, and most
SHOW
statements. Other statements are not
supported in MySQL 5.0.
Prepared statements mainly use the MYSQL_STMT
and MYSQL_BIND
data structures. A third
structure, MYSQL_TIME
, is used to transfer
temporal data.
This structure represents a prepared statement. A statement
is created by calling mysql_stmt_init()
,
which returns a statement handle (that is, a pointer to a
MYSQL_STMT
). The handle is used for all
subsequent statement-related functions until you close it
with mysql_stmt_close()
.
The MYSQL_STMT
structure has no members
that are for application use. Also, you should not try to
make a copy of a MYSQL_STMT
structure.
There is no guarantee that such a copy will be usable.
Multiple statement handles can be associated with a single connection. The limit on the number of handles depends on the available system resources.
This structure is used both for statement input (data values
sent to the server) and output (result values returned from
the server). For input, it is used with
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
The type of the buffer. The allowable
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
For input, this is a pointer to the buffer in which a
statement parameter's data value is stored. For output,
it is a pointer to the buffer in which to return a
result set column value. For numeric data types,
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 data types,
buffer
should point to a
MYSQL_TIME
structure. For character
and binary string data types, buffer
should point to a character buffer.
unsigned long buffer_length
The actual size of *buffer
in bytes.
This indicates the maximum amount of data that can be
stored in the buffer. For character and binary C data,
the buffer_length
value specifies the
length of *buffer
when used with
mysql_stmt_bind_param()
, or the
maximum number of data bytes that can be fetched into
the buffer when used with
mysql_stmt_bind_result()
.
unsigned long *length
A pointer to an unsigned long
variable that indicates the actual number of bytes of
data stored in *buffer
.
length
is used for character or
binary C data. For input parameter data binding,
length
points to an unsigned
long
variable that indicates the 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
This member points to a my_bool
variable that is true if a value is
NULL
, false if it is not
NULL
. For input, set
*is_null
to true to indicate that you
are passing a NULL
value as a
statement parameter. For output, this value is set to
true after you fetch a row if the result set column
value returned from the statement is
NULL
.
is_null
is a pointer to a boolean
rather than a boolean scalar so that it can be used in
the following way:
If your data values are always
NULL
, use
MYSQL_TYPE_NULL
to bind the
column.
If your data values are always NOT
NULL
, set is_null = (my_bool*)
0
.
In all other cases, you should set
is_null
to the address of a
my_bool
variable and change that
variable's value appropriately between executions to
indicate whether data values are
NULL
or NOT
NULL
.
my_bool is_unsigned
This member is used for integer types. (These correspond
to the 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
For output, this member is used output to report data
truncation errors. Truncation reporting must be enabled
by calling 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.
To use a 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));
This structure is used to send and receive
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
The year.
unsigned int month
The month of the year.
unsigned int day
The day of the month.
unsigned int hour
The hour of the day.
unsigned int minute
The minute of the hour.
unsigned int second
The second of the minute.
my_bool neg
A boolean flag to indicate whether the time is negative.
unsigned long second_part
The fractional part of the second. This member currently is unused.
Only those parts of a MYSQL_TIME
structure that apply to a given type of temporal value are
used: The year
, month
,
and day
elements are used for
DATE
, DATETIME
, and
TIMESTAMP
values. The
hour
, minute
, and
second
elements are used for
TIME
, DATETIME
, and
TIMESTAMP
values. See
Section 22.2.10, “C API Handling of Date and Time Values”.
The following table shows the allowable values that may be
specified in the buffer_type
member of
MYSQL_BIND
structures. 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_BIT | BIT |
|
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/BINARY | |
MYSQL_TYPE_VAR_STRING | VARCHAR/VARBINARY | |
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.
The functions available for prepared statement processing are summarized here and described in greater detail in a later section. See Section 22.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.) Currently, this function does nothing. |
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()
also returns the result
set metadata information in the form of a
MYSQL_RES
result set through
mysql_stmt_result_metadata()
.
You can supply the result buffers using
mysql_stmt_bind_result()
, so that the
mysql_stmt_fetch()
automatically returns data
to these buffers. This is row-by-row fetching.
You can also send the text or binary data in chunks to server
using mysql_stmt_send_long_data()
. See
Section 22.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()
.
Execution Steps
To prepare and execute a statement, an application follows these steps:
Create a prepared statement handle with
msyql_stmt_init()
. To prepare the
statement on the server, call
mysql_stmt_prepare()
and pass it a string
containing the SQL statement.
If the statement produces a result set, call
mysql_stmt_result_metadata()
to obtain
the result set metadata. This metadata is itself in the form
of result set, albeit a separate one from the one that
contains the rows returned by the query. The metadata result
set indicates how many columns are in the result and
contains information about each column.
Set the values of any parameters using
mysql_stmt_bind_param()
. All parameters
must be set. Otherwise, statement execution returns an error
or produces unexpected results.
Call mysql_stmt_execute()
to execute the
statement.
If the statement produces a result set, bind the data
buffers to use for retrieving the row values by calling
mysql_stmt_bind_result()
.
Fetch the data into the buffers row by row by calling
mysql_stmt_fetch()
repeatedly until no
more rows are found.
Repeat steps 3 through 6 as necessary, by changing the parameter values and re-executing the statement.
When mysql_stmt_prepare()
is called, the
MySQL client/server protocol performs these actions:
The server parses the statement and sends the okay status back to the client by assigning a statement ID. It also sends total number of parameters, a column count, and its metadata if it is a result set oriented statement. All syntax and semantics of the statement are checked by the server during this call.
The client uses this statement ID for the further operations, so that the server can identify the statement from among its pool of statements.
When mysql_stmt_execute()
is called, the
MySQL client/server protocol performs these actions:
The client uses the statement handle and sends the parameter data to the server.
The server identifies the statement using the ID provided by the client, replaces the parameter markers with the newly supplied data, and executes the statement. If the statement produces a result set, the server sends the data back to the client. Otherwise, it sends an okay status and total number of rows changed, deleted, or inserted.
When mysql_stmt_fetch()
is called, the MySQL
client/server protocol performs these actions:
The client reads the data from the packet row by row and places it into the application data buffers by doing the necessary conversions. If the application buffer type is same as that of the field type returned from the server, the conversions are straightforward.
If an error occurs, you can get the statement error code, error
message, and SQLSTATE value using
mysql_stmt_errno()
,
mysql_stmt_error()
, and
mysql_stmt_sqlstate()
, respectively.
Prepared Statement Logging
For prepared statements that are executed with the
mysql_stmt_prepare()
and
mysql_stmt_execute()
C API functions, the
server writes Prepare
and
Execute
lines to the general query log so
that you can tell when statements are prepared and executed.
Suppose that you prepare and execute a statement as follows:
Call mysql_stmt_prepare()
to prepare the
statement string "SELECT ?"
.
Call mysql_stmt_bind_param()
to bind the
value 3
to the parameter in the prepared
statement.
Call mysql_stmt_execute()
to execute the
prepared statement.
As a result of the preceding calls, the server writes the following lines to the general query log:
Prepare [1] SELECT ? Execute [1] SELECT 3
Each Prepare
and Execute
line in the log is tagged with a
[
statement
identifier so that you can keep track of which prepared
statement is being logged. N
]N
is a
positive integer. If there are multiple prepared statements
active simultaneously for the client,
N
may be greater than 1. Each
Execute
lines shows a prepared statement
after substitution of data values for ?
parameters.
Version notes: Prepare
lines are displayed
without [
before
MySQL 4.1.10. N
]Execute
lines are not displayed
at all before MySQL 4.1.10.
mysql_stmt_affected_rows()
mysql_stmt_attr_get()
mysql_stmt_attr_set()
mysql_stmt_bind_param()
mysql_stmt_bind_result()
mysql_stmt_close()
mysql_stmt_data_seek()
mysql_stmt_errno()
mysql_stmt_error()
mysql_stmt_execute()
mysql_stmt_fetch()
mysql_stmt_fetch_column()
mysql_stmt_field_count()
mysql_stmt_free_result()
mysql_stmt_init()
mysql_stmt_insert_id()
mysql_stmt_num_rows()
mysql_stmt_param_count()
mysql_stmt_param_metadata()
mysql_stmt_prepare()
mysql_stmt_reset()
mysql_stmt_result_metadata()
mysql_stmt_row_seek()
mysql_stmt_row_tell()
mysql_stmt_send_long_data()
mysql_stmt_sqlstate()
mysql_stmt_store_result()
To prepare and execute queries, use the functions described in detail in the following sections.
Note that all functions operating with a
MYSQL_STMT
structure begin with the prefix
mysql_stmt_
.
To create a MYSQL_STMT
handle, use the
mysql_stmt_init()
function.
my_ulonglong mysql_stmt_affected_rows(MYSQL_STMT
*stmt)
Description
Returns the total number of rows changed, deleted, or inserted
by the last executed statement. May be called immediately
after mysql_stmt_execute()
for
UPDATE
, DELETE
, or
INSERT
statements. For
SELECT
statements,
mysql_stmt_affected_rows()
works like
mysql_num_rows()
.
Return Values
An integer greater than zero indicates the number of rows
affected or retrieved. Zero indicates that no records were
updated for an UPDATE
statement, no rows
matched the WHERE
clause in the query, or
that no query has yet been executed. -1 indicates that the
query returned an error or that, for a
SELECT
query,
mysql_stmt_affected_rows()
was called prior
to calling mysql_stmt_store_result()
.
Because mysql_stmt_affected_rows()
returns
an unsigned value, you can check for -1 by comparing the
return value to (my_ulonglong)-1
(or to
(my_ulonglong)~0
, which is equivalent).
See Section 22.2.3.1, “mysql_affected_rows()
”, for additional
information on the return value.
Errors
None.
Example
For the usage of
mysql_stmt_affected_rows()
, refer to the
Example from Section 22.2.7.10, “mysql_stmt_execute()
”.
int mysql_stmt_attr_get(MYSQL_STMT *stmt, enum
enum_stmt_attr_type option, void *arg)
Description
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 Section 22.2.7.3, “mysql_stmt_attr_set()
”, for a list of
options and option types.
Return Values
0
if okay. Non-zero if
option
is unknown.
Errors
None.
int mysql_stmt_attr_set(MYSQL_STMT *stmt, enum
enum_stmt_attr_type option, const void *arg)
Description
Can be used to affect behavior for a prepared statement. This function may be called multiple times to set several options.
The option
argument is the option that you
want to set; the arg
argument is the value
for the option. If the option is an integer, then
arg
should point to the value of the
integer.
Possible option
values:
Option | Argument Type | Function |
STMT_ATTR_UPDATE_MAX_LENGTH | my_bool * | If set to 1: Update metadata
MYSQL_FIELD->max_length in
mysql_stmt_store_result() . |
STMT_ATTR_CURSOR_TYPE | unsigned long * | Type of cursor to open for statement when
mysql_stmt_execute() is invoked.
*arg can be
CURSOR_TYPE_NO_CURSOR (the default)
or CURSOR_TYPE_READ_ONLY . |
STMT_ATTR_PREFETCH_ROWS | unsigned long * | Number of rows to fetch from server at a time when using a cursor.
*arg can be in the range from 1 to
the maximum value of unsigned long .
The default is 1. |
If you use the STMT_ATTR_CURSOR_TYPE
option
with CURSOR_TYPE_READ_ONLY
, a cursor is
opened for the statement when you invoke
mysql_stmt_execute()
. If there is already
an open cursor from a previous
mysql_stmt_execute()
call, it closes the
cursor before opening a new one.
mysql_stmt_reset()
also closes any open
cursor before preparing the statement for re-execution.
mysql_stmt_free_result()
closes any open
cursor.
If you open a cursor for a prepared statement,
mysql_stmt_store_result()
is unnecessary,
because that function causes the result set to be buffered on
the client side.
The STMT_ATTR_CURSOR_TYPE
option was added
in MySQL 5.0.2. The STMT_ATTR_PREFETCH_ROWS
option was added in MySQL 5.0.6.
Return Values
0
if okay. Non-zero if
option
is unknown.
Errors
None.
Example
The following example opens a cursor for a prepared statement and sets the number of rows to fetch at a time to 5:
MYSQL_STMT *stmt; int rc; unsigned long type; unsigned long prefetch_rows = 5; stmt = mysql_stmt_init(mysql); type = (unsigned long) CURSOR_TYPE_READ_ONLY; rc = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*) &type); /* ... check return value ... */ rc = mysql_stmt_attr_set(stmt, STMT_ATTR_PREFETCH_ROWS, (void*) &prefetch_rows); /* ... check return value ... */
my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt,
MYSQL_BIND *bind)
Description
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 22.2.5, “C API Prepared Statement Data types”.
Return Values
Zero if the bind was successful. Non-zero if an error occurred.
Errors
CR_INVALID_BUFFER_USE
Indicates if the bind is to supply the long data in chunks and if the buffer type is non string or binary.
CR_UNSUPPORTED_PARAM_TYPE
The conversion is not supported. Possibly the
buffer_type
value is illegal or is not
one of the supported types.
CR_OUT_OF_MEMORY
Out of memory.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
For the usage of mysql_stmt_bind_param()
,
refer to the Example from
Section 22.2.7.10, “mysql_stmt_execute()
”.
my_bool mysql_stmt_bind_result(MYSQL_STMT *stmt,
MYSQL_BIND *bind)
Description
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 that 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 22.2.5, “C API Prepared Statement Data types”.
Return Values
Zero if the bind was successful. Non-zero if an error occurred.
Errors
CR_UNSUPPORTED_PARAM_TYPE
The conversion is not supported. Possibly the
buffer_type
value is illegal or is not
one of the supported types.
CR_OUT_OF_MEMORY
Out of memory.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
For the usage of mysql_stmt_bind_result()
,
refer to the Example from Section 22.2.7.11, “mysql_stmt_fetch()
”.
my_bool mysql_stmt_close(MYSQL_STMT *)
Description
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.
Return Values
Zero if the statement was freed successfully. Non-zero if an error occurred.
Errors
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
For the usage of mysql_stmt_close()
, refer
to the Example from Section 22.2.7.10, “mysql_stmt_execute()
”.
void mysql_stmt_data_seek(MYSQL_STMT *stmt,
my_ulonglong offset)
Description
Seeks to an arbitrary row in a statement result set. The
offset
value is a row number and should be
in the range from 0
to
mysql_stmt_num_rows(stmt)-1
.
This function requires that the statement result set structure
contains the entire result of the last executed query, so
mysql_stmt_data_seek()
may be used only in
conjunction with mysql_stmt_store_result()
.
Return Values
None.
Errors
None.
unsigned int mysql_stmt_errno(MYSQL_STMT
*stmt)
Description
For the statement specified by stmt
,
mysql_stmt_errno()
returns the error code
for the most recently invoked statement API function that can
succeed or fail. A return value of zero means that no error
occurred. Client error message numbers are listed in the MySQL
errmsg.h
header file. Server error
message numbers are listed in
mysqld_error.h
. Errors also are listed at
Appendix B, Error Codes and Messages.
Return Values
An error code value. Zero if no error occurred.
Errors
None.
const char *mysql_stmt_error(MYSQL_STMT
*stmt)
Description
For the statement specified by stmt
,
mysql_stmt_error()
returns a
null-terminated string containing the error message for the
most recently invoked statement API function that can succeed
or fail. An empty string (""
) is returned
if no error occurred. This means the following two tests are
equivalent:
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.
Return Values
A character string that describes the error. An empty string if no error occurred.
Errors
None.
int mysql_stmt_execute(MYSQL_STMT *stmt)
Description
mysql_stmt_execute()
executes the prepared
query associated with the statement handle. The currently
bound parameter marker values are sent to server during this
call, and the server replaces the markers with this newly
supplied data.
If the statement is an UPDATE
,
DELETE
, or INSERT
, the
total number of changed, deleted, or inserted rows can be
found by calling
mysql_stmt_affected_rows()
. If this is a
statement such as SELECT
that generates a
result set, you must call
mysql_stmt_fetch()
to fetch the data prior
to calling any other functions that result in query
processing. For more information on how to fetch the results,
refer to Section 22.2.7.11, “mysql_stmt_fetch()
”.
For statements that generate a result set, you can request
that mysql_stmt_execute()
open a cursor for
the statement by calling
mysql_stmt_attr_set()
before executing the
statement. If you execute a statement multiple times,
mysql_stmt_execute()
closes any open cursor
before opening a new one.
Return Values
Zero if execution was successful. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
The following example demonstrates how to create and populate
a table using mysql_stmt_init()
,
mysql_stmt_prepare()
,
mysql_stmt_param_count()
,
mysql_stmt_bind_param()
,
mysql_stmt_execute()
, and
mysql_stmt_affected_rows()
. The
mysql
variable is assumed to be a valid
connection handle.
#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 */ /* sets 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/mysql_client_test.c
. This file can
be obtained from a MySQL source distribution or from the
BitKeeper source repository.
int mysql_stmt_fetch(MYSQL_STMT *stmt)
Description
mysql_stmt_fetch()
returns the next row in
the result set. It can be called only while the result set
exists; that is, after a call to
mysql_stmt_execute()
that creates a result
set or after mysql_stmt_store_result()
,
which is called after mysql_stmt_execute()
to buffer the entire result set.
mysql_stmt_fetch()
returns row data using
the buffers bound by
mysql_stmt_bind_result()
. It returns the
data in those buffers for all the columns in the current row
set and the lengths are returned to the
length
pointer.
All columns must be bound by the application before calling
mysql_stmt_fetch()
.
If a fetched data value is a NULL
value,
the *is_null
value of the corresponding
MYSQL_BIND
structure contains TRUE (1).
Otherwise, the data and its length are returned in the
*buffer
and *length
elements based on the buffer type specified by the
application. Each numeric and temporal type has a fixed
length, as listed in the following table. The length of the
string types depends on the length of the actual data value,
as indicated by data_length
.
Type | Length |
MYSQL_TYPE_TINY | 1 |
MYSQL_TYPE_SHORT | 2 |
MYSQL_TYPE_LONG | 4 |
MYSQL_TYPE_LONGLONG | 8 |
MYSQL_TYPE_FLOAT | 4 |
MYSQL_TYPE_DOUBLE | 8 |
MYSQL_TYPE_TIME | sizeof(MYSQL_TIME) |
MYSQL_TYPE_DATE | sizeof(MYSQL_TIME) |
MYSQL_TYPE_DATETIME | sizeof(MYSQL_TIME) |
MYSQL_TYPE_STRING | data length |
MYSQL_TYPE_BLOB | data_length |
Return Values
Return Value | Description |
0 | Successful, the data has been fetched to application data buffers. |
1 | Error occurred. Error code and message can be obtained by calling
mysql_stmt_errno() and
mysql_stmt_error() . |
MYSQL_NO_DATA | No more rows/data exists |
MYSQL_DATA_TRUNCATED | Data truncation occurred |
MYSQL_DATA_TRUNCATED
is 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.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
CR_UNSUPPORTED_PARAM_TYPE
The buffer type is MYSQL_TYPE_DATE
,
MYSQL_TYPE_TIME
,
MYSQL_TYPE_DATETIME
, or
MYSQL_TYPE_TIMESTAMP
, but the data type
is not DATE
, TIME
,
DATETIME
, or
TIMESTAMP
.
All other unsupported conversion errors are returned from
mysql_stmt_bind_result()
.
Example
The following example demonstrates how to fetch data from a
table using mysql_stmt_result_metadata()
,
mysql_stmt_bind_result()
, and
mysql_stmt_fetch()
. (This example expects
to retrieve the two rows inserted by the example shown in
Section 22.2.7.10, “mysql_stmt_execute()
”.) The
mysql
variable is assumed to be a valid
connection handle.
#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); }
int mysql_stmt_fetch_column(MYSQL_STMT *stmt,
MYSQL_BIND *bind, unsigned int column, unsigned long
offset)
Description
Fetch one column from the current result set row.
bind
provides the buffer where data should
be placed. It should be set up the same way as for
mysql_stmt_bind_result()
.
column
indicates which column to fetch. The
first column is numbered 0. offset
is the
offset within the data value at which to begin retrieving
data. This can be used for fetching the data value in pieces.
The beginning of the value is offset 0.
Return Values
Zero if the value was fetched successfully. Non-zero if an error occurred.
Errors
CR_INVALID_PARAMETER_NO
Invalid column number.
CR_NO_DATA
The end of the result set has already been reached.
unsigned int mysql_stmt_field_count(MYSQL_STMT
*stmt)
Description
Returns the number of columns for the most recent statement
for the statement handler. This value is zero for statements
such as INSERT
or DELETE
that do not produce result sets.
mysql_stmt_field_count()
can be called
after you have prepared a statement by invoking
mysql_stmt_prepare()
.
Return Values
An unsigned integer representing the number of columns in a result set.
Errors
None.
my_bool mysql_stmt_free_result(MYSQL_STMT
*stmt)
Description
Releases memory associated with the result set produced by
execution of the prepared statement. If there is a cursor open
for the statement, mysql_stmt_free_result()
closes it.
Return Values
Zero if the result set was freed successfully. Non-zero if an error occurred.
Errors
MYSQL_STMT *mysql_stmt_init(MYSQL *mysql)
Description
Create a MYSQL_STMT
handle. The handle
should be freed with mysql_stmt_close(MYSQL_STMT
*)
.
Return values
A pointer to a MYSQL_STMT
structure in case
of success. NULL
if out of memory.
Errors
CR_OUT_OF_MEMORY
Out of memory.
my_ulonglong mysql_stmt_insert_id(MYSQL_STMT
*stmt)
Description
Returns the value generated for an
AUTO_INCREMENT
column by the prepared
INSERT
or UPDATE
statement. Use this function after you have executed a
prepared INSERT
statement on a table which
contains an AUTO_INCREMENT
field.
See Section 22.2.3.36, “mysql_insert_id()
”, for more information.
Return Values
Value for AUTO_INCREMENT
column which was
automatically generated or explicitly set during execution of
prepared statement, or value generated by
LAST_INSERT_ID(
function. Return value is undefined if statement does not set
expr
)AUTO_INCREMENT
value.
Errors
None.
my_ulonglong mysql_stmt_num_rows(MYSQL_STMT
*stmt)
Description
Returns the number of rows in the result set.
The use of mysql_stmt_num_rows()
depends on
whether you used mysql_stmt_store_result()
to buffer the entire result set in the statement handle.
If you use mysql_stmt_store_result()
,
mysql_stmt_num_rows()
may be called
immediately.
Return Values
The number of rows in the result set.
Errors
None.
unsigned long mysql_stmt_param_count(MYSQL_STMT
*stmt)
Description
Returns the number of parameter markers present in the prepared statement.
Return Values
An unsigned long integer representing the number of parameters in a statement.
Errors
None.
Example
For the usage of mysql_stmt_param_count()
,
refer to the Example from
Section 22.2.7.10, “mysql_stmt_execute()
”.
MYSQL_RES *mysql_stmt_param_metadata(MYSQL_STMT
*stmt)
This function currently does nothing.
Description
Return Values
Errors
int mysql_stmt_prepare(MYSQL_STMT *stmt, const char
*query, unsigned long length)
Description
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 Language (DML) statements, and
not in Data Definition Language (DDL) statements.
The parameter markers must be bound to application variables
using mysql_stmt_bind_param()
before
executing the statement.
Return Values
Zero if the statement was prepared successfully. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query
CR_UNKNOWN_ERROR
An unknown error occurred.
If the prepare operation was unsuccessful (that is,
mysql_stmt_prepare()
returns non-zero), the
error message can be obtained by calling
mysql_stmt_error()
.
Example
For the usage of mysql_stmt_prepare()
,
refer to the Example from
Section 22.2.7.10, “mysql_stmt_execute()
”.
my_bool mysql_stmt_reset(MYSQL_STMT *stmt)
Description
Reset the prepared statement on the client and server to state
after prepare. This is mainly used to reset data sent with
mysql_stmt_send_long_data()
. Any open
cursor for the statement is closed.
To re-prepare the statement with another query, use
mysql_stmt_prepare()
.
Return Values
Zero if the statement was reset successfully. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query
CR_UNKNOWN_ERROR
An unknown error occurred.
MYSQL_RES *mysql_stmt_result_metadata(MYSQL_STMT
*stmt)
Description
If a statement passed to
mysql_stmt_prepare()
is one that produces a
result set, mysql_stmt_result_metadata()
returns the result set metadata in the form of a pointer to a
MYSQL_RES
structure that can be used to
process the meta information such as total number of fields
and individual field information. This result set pointer can
be passed as an argument to any of the field-based API
functions that process result set metadata, such as:
mysql_num_fields()
mysql_fetch_field()
mysql_fetch_field_direct()
mysql_fetch_fields()
mysql_field_count()
mysql_field_seek()
mysql_field_tell()
mysql_free_result()
The result set structure should be freed when you are done
with it, which you can do by passing it to
mysql_free_result()
. This is similar to the
way you free a result set obtained from a call to
mysql_store_result()
.
The result set returned by
mysql_stmt_result_metadata()
contains only
metadata. It does not contain any row results. The rows are
obtained by using the statement handle with
mysql_stmt_fetch()
.
Return Values
A MYSQL_RES
result structure.
NULL
if no meta information exists for the
prepared query.
Errors
CR_OUT_OF_MEMORY
Out of memory.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
For the usage of
mysql_stmt_result_metadata()
, refer to the
Example from Section 22.2.7.11, “mysql_stmt_fetch()
”.
MYSQL_ROW_OFFSET mysql_stmt_row_seek(MYSQL_STMT
*stmt, MYSQL_ROW_OFFSET offset)
Description
Sets the row cursor to an arbitrary row in a statement result
set. The offset
value is a row offset that
should be a value returned from
mysql_stmt_row_tell()
or from
mysql_stmt_row_seek()
. This value is not a
row number; if you want to seek to a row within a result set
by number, use mysql_stmt_data_seek()
instead.
This function requires that the result set structure contains
the entire result of the query, so
mysql_stmt_row_seek()
may be used only in
conjunction with mysql_stmt_store_result()
.
Return Values
The previous value of the row cursor. This value may be passed
to a subsequent call to
mysql_stmt_row_seek()
.
Errors
None.
MYSQL_ROW_OFFSET mysql_stmt_row_tell(MYSQL_STMT
*stmt)
Description
Returns the current position of the row cursor for the last
mysql_stmt_fetch()
. This value can be used
as an argument to mysql_stmt_row_seek()
.
You should use mysql_stmt_row_tell()
only
after mysql_stmt_store_result()
.
Return Values
The current offset of the row cursor.
Errors
None.
my_bool mysql_stmt_send_long_data(MYSQL_STMT *stmt,
unsigned int parameter_number, const char *data, unsigned long
length)
Description
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 ignores the bind
buffer for all parameters that have been used with
mysql_stmt_send_long_data()
since last
mysql_stmt_execute()
or
mysql_stmt_reset()
.
If you want to reset/forget the sent data, you can do it with
mysql_stmt_reset()
. See
Section 22.2.7.21, “mysql_stmt_reset()
”.
Return Values
Zero if the data is sent successfully to server. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_OUT_OF_MEMORY
Out of memory.
CR_UNKNOWN_ERROR
An unknown error occurred.
Example
The following example demonstrates how to send the data for a
TEXT
column in chunks. It inserts the data
value 'MySQL - The most popular Open Source
database'
into the text_column
column. The mysql
variable is assumed to be
a valid connection handle.
#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); }
const char *mysql_stmt_sqlstate(MYSQL_STMT
*stmt)
Description
For the statement specified by stmt
,
mysql_stmt_sqlstate()
returns a
null-terminated string containing the SQLSTATE error code for
the most recently invoked prepared statement API function that
can succeed or fail. The error code consists of five
characters. "00000"
means “no
error.” The values are specified by ANSI SQL and ODBC.
For a list of possible values, see
Appendix B, Error Codes and Messages.
Note that not all MySQL errors are yet mapped to SQLSTATE
codes. The value "HY000"
(general error) is
used for unmapped errors.
Return Values
A null-terminated character string containing the SQLSTATE error code.
int mysql_stmt_store_result(MYSQL_STMT
*stmt)
Description
You must call mysql_stmt_store_result()
for
every statement that successfully produces a result set
(SELECT
, SHOW
,
DESCRIBE
, EXPLAIN
), and
only if you want to buffer the complete result set by the
client, so that the subsequent
mysql_stmt_fetch()
call returns buffered
data.
It is unnecessary to call
mysql_stmt_store_result()
for other
statements, but if you do, it does not harm or cause any
notable performance problem. You can detect whether the
statement produced a result set by checking if
mysql_stmt_result_metadata()
returns
NULL
. For more information, refer to
Section 22.2.7.22, “mysql_stmt_result_metadata()
”.
Note: MySQL doesn't by
default calculate
MYSQL_FIELD->max_length
for all columns
in mysql_stmt_store_result()
because
calculating this would slow down
mysql_stmt_store_result()
considerably and
most applications doesn't need max_length
.
If you want max_length
to be updated, you
can call mysql_stmt_attr_set(MYSQL_STMT,
STMT_ATTR_UPDATE_MAX_LENGTH, &flag)
to enable
this. See Section 22.2.7.3, “mysql_stmt_attr_set()
”.
Return Values
Zero if the results are buffered successfully. Non-zero if an error occurred.
Errors
CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.
Here follows a list of the currently known problems with prepared statements:
TIME
, TIMESTAMP
, and
DATETIME
do not support parts of seconds
(for example from DATE_FORMAT()
.
When converting an integer to string,
ZEROFILL
is honored with prepared
statements in some cases where the MySQL server doesn't
print the leading zeros. (For example, with
MIN(number-with-zerofill)
).
When converting a floating point number to a string in the client, the rightmost digits of the converted value may differ slightly from those of the original value.
Prepared statements do not use the Query Cache, even in cases where a query does not contain any placeholders. See Section 5.14.1, “How the Query Cache Operates”.
MySQL 5.0 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 to
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 multiple-statement capability can be used with
mysql_query()
or
mysql_real_query()
. It cannot be used with
the prepared statement interface. Prepared statement handles are
defined to work only with strings that contain a single
statement.
The binary protocol allows you to send and receive date and time
values (DATE
, TIME
,
DATETIME
, and TIMESTAMP
),
using the MYSQL_TIME
structure. The members
of this structure are described in
Section 22.2.5, “C API Prepared Statement Data types”.
To send temporal data values, create a prepared statement using
mysql_stmt_prepare()
. Then, before calling
mysql_stmt_execute()
to execute the
statement, use the following procedure to set up each temporal
parameter:
In the MYSQL_BIND
structure associated
with the data value, set the buffer_type
member to the type that indicates what kind of temporal
value you're sending. For DATE
,
TIME
, DATETIME
, or
TIMESTAMP
values, set
buffer_type
to
MYSQL_TYPE_DATE
,
MYSQL_TYPE_TIME
,
MYSQL_TYPE_DATETIME
, or
MYSQL_TYPE_TIMESTAMP
, respectively.
Set the buffer
member of the
MYSQL_BIND
structure to the address of
the MYSQL_TIME
structure in which you
pass the temporal value.
Fill in the members of the MYSQL_TIME
structure that are appropriate for the type of temporal
value to be passed.
Use mysql_stmt_bind_param()
to bind the
parameter data to the statement. Then you can call
mysql_stmt_execute()
.
To retrieve temporal values, the procedure is similar, except
that you set the buffer_type
member to the
type of value you expect to receive, and the
buffer
member to the address of a
MYSQL_TIME
structure into which the returned
value should be placed. Use
mysql_bind_results()
to bind the buffers to
the statement after calling
mysql_stmt_execute()
and before fetching the
results.
Here is a simple example that inserts DATE
,
TIME
, and TIMESTAMP
data.
The mysql
variable is assumed to be a valid
connection handle.
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 22.2.15, “How to Make a Threaded Client”.
void my_init(void)
Description
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 also calls
mysql_thread_init()
for this thread.
This is automatically called by
mysql_init()
,
mysql_library_init()
,
mysql_server_init()
and
mysql_connect()
.
Return Values
None.
my_bool mysql_thread_init(void)
Description
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()
.
Return Values
Zero if successful. Non-zero if an error occurred.
void mysql_thread_end(void)
Description
This function needs to be called before calling
pthread_exit()
to free memory allocated by
mysql_thread_init()
.
Note that this function is not invoked automatically by the client library. It must be called explicitly to avoid a memory leak.
Return Values
None.
If you want to allow your application to be linked against the
embedded MySQL server library, you must use the
mysql_server_init()
and
mysql_server_end()
functions. See
Section 22.1, “libmysqld, the Embedded MySQL Server Library”.
However, to provide improved memory management, even programs
that are linked with -lmysqlclient
rather than
-lmysqld
should include calls to begin and end
use of the library. As of MySQL 5.0.3, the
mysql_library_init()
and
mysql_library_end()
functions can be used to
do this. These actually are #define
symbols
that make them equivalent to
mysql_server_init()
and
mysql_server_end()
, but the names more
clearly indicate that they should be called when beginning and
ending use of a MySQL C API library no matter whether the
application uses libmysqlclient
or
libmysqld
. For more information, see
Section 22.2.2, “C API Function Overview”.
int mysql_server_init(int argc, char **argv, char
**groups)
Description
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
, and so forth) 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 are active. See Section 4.3.2, “Using Option Files”. For
convenience, groups
may be
NULL
, in which case the
[server]
and [embedded]
groups are active.
Example
#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; }
Return Values
0 if okay, 1 if an error occurred.
It is possible for mysql_store_result()
to
return NULL
following a successful call to
mysql_query()
. When this happens, it means
one of the following conditions occurred:
There was a malloc()
failure (for
example, if the result set was too large).
The data couldn't be read (an error occurred on the connection).
The query returned no data (for example, it was an
INSERT
, UPDATE
, or
DELETE
).
You can always check whether the statement should have
produced a non-empty result by calling
mysql_field_count()
. If
mysql_field_count()
returns zero, the
result is empty and the last query was a statement that does
not return values (for example, an INSERT
or a DELETE
). If
mysql_field_count()
returns a non-zero
value, the statement should have produced a non-empty result.
See the description of the
mysql_field_count()
function for an
example.
You can test for an error by calling
mysql_error()
or
mysql_errno()
.
In addition to the result set returned by a query, you can also get the following information:
mysql_affected_rows()
returns the
number of rows affected by the last query when doing an
INSERT
, UPDATE
, or
DELETE
.
For a fast re-create, use TRUNCATE
TABLE
.
mysql_num_rows()
returns the number of
rows in a result set. With
mysql_store_result()
,
mysql_num_rows()
may be called as soon
as mysql_store_result()
returns. With
mysql_use_result()
,
mysql_num_rows()
may be called only
after you have fetched all the rows with
mysql_fetch_row()
.
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 22.2.3.36, “mysql_insert_id()
”.
Some queries (LOAD DATA INFILE ...
,
INSERT INTO ... SELECT ...
,
UPDATE
) return additional information.
The result is returned by mysql_info()
.
See the description for mysql_info()
for the format of the string that it returns.
mysql_info()
returns a
NULL
pointer if there is no additional
information.
If you insert a record into a table that contains an
AUTO_INCREMENT
column, you can obtain the
value stored into that column by calling the
mysql_insert_id()
function.
You can check from your C applications whether a value was
stored in an AUTO_INCREMENT
column by
executing the following code (which assumes that you've
checked that the statement succeeded). It determines whether
the query was an INSERT
with an
AUTO_INCREMENT
index:
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 22.2.3.36, “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 is not changed by another client. It is not even
changed if you update another
AUTO_INCREMENT
column with a non-magic
value (that is, a value that is not NULL
and not 0
).
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
options in the link
command. You may also need to specify a -L
option to tell the linker where to find the library. For
example, if the library is installed in
/usr/local/mysql/lib
, use
-L/usr/local/mysql/lib -lmysqlclient -lz
in the
link command.
For clients that use MySQL header files, you may need to specify
an -I
option when you compile them (for
example, -I/usr/local/mysql/include
), so that
the compiler can find the header files.
To make it simpler to compile MySQL programs on Unix, we have provided the mysql_config script for you. See Section 22.9.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.
To avoid aborting 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 use your own
SIGPIPE
handler, you should first call
mysql_server_init()
and then install your
handler.
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 22.2, “MySQL C API”. In this case, you don't
have to worry about the net_serv.o
object
file or the other MySQL libraries.
When using a threaded client and you want to use timeouts and
interrupts, you can make great use of the routines in the
thr_alarm.c
file. If you are using routines
from the mysys
library, the only thing you
must remember is to call my_init()
first! See
Section 22.2.11, “C API Threaded Function Descriptions”.
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 creates 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:
Two threads can't send a query to the MySQL server at the
same time on the same connection. In particular, you have to
ensure that between a mysql_query()
and
mysql_store_result()
no other thread is
using the same connection.
Many threads can access different result sets that are
retrieved with mysql_store_result()
.
If you use mysql_use_result
, you 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()
.
If you want to use multiple threads on the same connection,
you must have a mutex lock around your
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.
If you program with POSIX threads, you can use
pthread_mutex_lock()
and
pthread_mutex_unlock()
to establish and
release a mutex lock.
You need to know the following if 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 creates a
thread-specific variable for the thread that is used by the
debug library (among other things).
If you call a MySQL function, before the thread has called
mysql_init()
or
mysql_connect()
, the thread does not have the
necessary thread-specific variables in place and you are likely
to end up with a core dump sooner or later.
To get things to work smoothly you have to do the following:
Call my_init()
at the start of your
program if it calls any other MySQL function before calling
mysql_real_connect()
.
Call mysql_thread_init()
in the thread
handler before calling any MySQL function.
In the thread, call mysql_thread_end()
before calling pthread_exit()
. This frees
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.
PHP is a server-side, HTML-embedded scripting language that may be used to create dynamic Web pages. It is available for most operating systems and Web servers, and can access most common databases, including MySQL. PHP may be run as a separate program or compiled as a module for use with the Apache Web server.
PHP actually provides two different MySQL API extensions:
mysql
: Available for PHP versions 4 and 5,
this extension is intended for use with MySQL versions prior
to MySQL 4.1. This extension does not support the improved
authentication protocol used in MySQL 5.0, nor
does it support prepared statements or multiple statements. If
you wish to use this extension with MySQL 5.0,
you will likely want to configure the MySQL server to use the
--old-passwords option (see
Section A.2.3, “Client does not support authentication protocol
”). This extension is documented on
the PHP Web site at http://php.net/mysql.
mysqli
- Stands for “MySQL,
Improved”; this extension is available only in PHP 5.
It is intended for use with MySQL 4.1.1 and later. This
extension fully supports the authentication protocol used in
MySQL 5.0, as well as the Prepared Statements and
Multiple Statements APIs. In addition, this extension provides
an advanced, object-oriented programming interface. You can
read the documentation for the mysqli
extension at http://php.net/mysqli. A helpful
article can be found at
http://www.zend.com/php5/articles/php5-mysqli.php.
If you're experiencing problems with enabling both the
mysql
and the mysqli
extension when building PHP on Linux yourself, see
Section 22.3.2, “Enabling Both mysql
and
mysqli
in PHP”.
The PHP distribution and documentation are available from the
PHP Web site. MySQL
provides the mysql
and
mysqli
extensions for the Windows operating
system for MySQL versions as of 5.0.18 on
http://dev.mysql.com/downloads/connector/php/.
You can find information why you should preferably use the
extensions provided by MySQL on that page.
Error: Maximum Execution Time Exceeded
:
This is a PHP limit; go into the
php.ini
file and set the maximum
execution time up from 30 seconds to something higher, as
needed. It is also not a bad idea to double the RAM allowed
per script to 16MB instead of 8MB.
Fatal error: Call to unsupported or undefined
function mysql_connect() in ...
: This means that
your PHP version isn't compiled with MySQL support. You can
either compile a dynamic MySQL module and load it into PHP
or recompile PHP with built-in MySQL support. This process
is described in detail in the PHP manual.
Error: Undefined reference to
'uncompress'
: This means that the client library
is compiled with support for a compressed client/server
protocol. The fix is to add -lz
last when
linking with -lmysqlclient
.
Error: Client does not support authentication
protocol
: This is most often encountered when
trying to use the older mysql
extension
with MySQL 4.1.1 and later. Possible solutions are:
downgrade to MySQL 4.0; switch to PHP 5 and the newer
mysqli
extension; or configure the MySQL
server with --old-passwords
. (See
Section A.2.3, “Client does not support authentication protocol
”, for more information.)
Those with PHP4 legacy code can make use of a compatibility layer for the old and new MySQL libraries, such as this one: http://www.coggeshall.org/oss/mysql2i.
If you're experiencing problems with enabling both the
mysql
and the mysqli
extension when building PHP on Linux yourself, you should try
the following procedure.
Configure PHP like this:
./configure --with-mysqli=/usr/bin/mysql_config --with-mysql=/usr
Edit the Makefile
and search for a line
that starts with EXTRA_LIBS
. It might
look like this (all on one line):
EXTRA_LIBS = -lcrypt -lcrypt -lmysqlclient -lz -lresolv -lm -ldl -lnsl -lxml2 -lz -lm -lxml2 -lz -lm -lmysqlclient -lz -lcrypt -lnsl -lm -lxml2 -lz -lm -lcrypt -lxml2 -lz -lm -lcrypt
Remove all duplicates, so that the line looks like this (all on one line):
EXTRA_LIBS = -lcrypt -lcrypt -lmysqlclient -lz -lresolv -lm -ldl -lnsl -lxml2
Build and install PHP:
make make install
The Perl DBI
module provides a generic
interface for database access. You can write a DBI script that
works with many different database engines without change. To use
DBI, you must install the DBI
module, as well
as a DataBase Driver (DBD) module for each type of server you want
to access. For MySQL, this driver is the
DBD::mysql
module.
Perl DBI is the recommended Perl interface. It replaces an older
interface called mysqlperl
, which should be
considered obsolete.
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:
Once you have the 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::mysql
You can also use pod2man
,
pod2html
, and so forth to translate this
information into other formats.
For online information about Perl DBI, visit the DBI Web site,
http://dbi.perl.org/. That site hosts a general
DBI mailing list. MySQL AB hosts a list specifically about
DBD::mysql
; see
Section 1.7.1, “MySQL Mailing Lists”.
For printed information, the official DBI book is Programming the Perl DBI (Alligator Descartes and Tim Bunce, O'Reilly & Associates, 2000). Information about the book is available at the DBI Web site, http://dbi.perl.org/.
For information that focuses specifically on using DBI with MySQL, see MySQL and Perl for the Web (Paul DuBois, New Riders, 2001). This book's Web site is http://www.kitebird.com/mysql-perl/.
MySQL++
is a MySQL API for C++. Warren Young
has taken over this project. 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 run into
problems if you try to use the default
libmysql.dll
libraries (that were compiled
using 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.
This section describes some utilities that you may find useful when developing MySQL programs.
msql2mysql
A shell script that converts mSQL
programs
to MySQL. It doesn't handle every case, but it gives a good
start when converting.
mysql_config
A shell script that produces the option values needed when compiling MySQL programs.
Initially, the MySQL C API was developed to be very similar to that for the mSQL database system. Because of this, mSQL programs often can be converted relatively easily for use with MySQL by changing the names of the C API functions.
The msql2mysql utility performs the conversion of mSQL C API function calls to their MySQL equivalents. msql2mysql converts the input file in place, so make a copy of the original before converting it. For example, use msql2mysql like this:
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.16, “replace — A String-Replacement Utility”.
mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL.
mysql_config supports the following options:
--cflags
Compiler flags to find include files and critical
compiler flags and defines used when compiling the
libmysqlclient
library.
--include
Compiler options to find MySQL include files. (Note that
normally you would use --cflags
instead
of this option.)
--libmysqld-libs
,
--embedded
Libraries and options required to link with the MySQL embedded server.
--libs
Libraries and options required to link with the MySQL client library.
--libs_r
Libraries and options required to link with the thread-safe MySQL client library.
--port
The default TCP/IP port number, defined when configuring MySQL.
--socket
The default Unix socket file, defined when configuring MySQL.
--version
Version number for the MySQL distribution.
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:
shell>CFG=/usr/local/mysql/bin/mysql_config
shell>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 substitute its output into the
surrounding command.