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 Java) use the
mysqlclient
library to communicate with the MySQL
server. This means that, for example, you can take advantage of many of the same
environment variables that are used by other client programs, because they are
referenced from the library. See section 13.1
Overview of the different MySQL programs, for a list of these variables.
The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16K bytes) is automatically increased up to the maximum size (the default maximum is 24M). Since buffer sizes are increased only as demand warrants, simply increasing the default maximum limit does not in inself cause more resources to be used. This size check is mostly a check for erroneous queries and communication packets.
The communication buffer must be large enough to contain a single SQL
statement (for client-to-server traffic) and one row of returned data (for
server-to-client traffic). Each thread's communication buffer is dynamically
enlarged to handle any query or row up to the maximum limit. For example, if you
have BLOB
values that contain up to 16M of data, you must have a
communication buffer limit of at least 16M (in both server and client). The
client's default maximum is 24M, but the default maximum in the server is 1M.
You can increase this by changing the value of the
max_allowed_packet
parameter when the server is started. See
section 11.2.3
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.
If you are programming with threads, you should compile the
MySQL C API with --with-thread-safe-client
. This
will make the C API thread safe per connection. You can let two threads share
the same connection as long as you do the following:
mysql_query()
and mysql_store_result()
no other
thread is using the same connection.
mysql_store_result()
.
mysql_use_result
, you have to ensure that no other
thread is asking anything on the same connection until the result set is
closed. MYSQL
MYSQL_RES
SELECT
, SHOW
, DESCRIBE
,
EXPLAIN
). The information returned from a query is called the
result set in the remainder of this section.
MYSQL_ROW
mysql_fetch_row()
.
MYSQL_FIELD
MYSQL_FIELD
structures for each field by calling
mysql_fetch_field()
repeatedly. Field values are not part of this
structure; they are contained in a MYSQL_ROW
structure.
MYSQL_FIELD_OFFSET
mysql_field_seek()
.)
Offsets are field numbers within a row, beginning at zero.
my_ulonglong
mysql_affected_rows()
, mysql_num_rows()
and
mysql_insert_id()
. This type provides a range of 0
to 1.84e19
. On some systems, attempting to print a value of type
my_ulonglong
will not work. To print such a value, convert it to
unsigned long
and use a %lu
print format. Example: printf (Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));
The MYSQL_FIELD
structure contains the members listed below:
char * name
char * table
table
value is an empty string.
char * def
mysql_list_fields()
.
enum enum_field_types type
type
value may be one of the
following:
Type value | Type meaning |
FIELD_TYPE_TINY |
TINYINT field |
FIELD_TYPE_SHORT |
SMALLINT field |
FIELD_TYPE_LONG |
INTEGER field |
FIELD_TYPE_INT24 |
MEDIUMINT field |
FIELD_TYPE_LONGLONG |
BIGINT field |
FIELD_TYPE_DECIMAL |
DECIMAL or NUMERIC field |
FIELD_TYPE_FLOAT |
FLOAT field |
FIELD_TYPE_DOUBLE |
DOUBLE or REAL field |
FIELD_TYPE_TIMESTAMP |
TIMESTAMP field |
FIELD_TYPE_DATE |
DATE field |
FIELD_TYPE_TIME |
TIME field |
FIELD_TYPE_DATETIME |
DATETIME field |
FIELD_TYPE_YEAR |
YEAR field |
FIELD_TYPE_STRING |
String (CHAR or VARCHAR ) field |
FIELD_TYPE_BLOB |
BLOB or TEXT field (use
max_length to determine the maximum length) |
FIELD_TYPE_SET |
SET field |
FIELD_TYPE_ENUM |
ENUM field |
FIELD_TYPE_NULL |
NULL -type field |
FIELD_TYPE_CHAR |
Deprecated; use FIELD_TYPE_TINY instead
|
IS_NUM()
macro to test
whether or not a field has a numeric type. Pass the type
value to
IS_NUM()
and it will evaluate to TRUE if the field is numeric: if (IS_NUM(field->type)) printf("Field is numeric\n");
unsigned int length
unsigned int max_length
mysql_store_result()
or mysql_list_fields()
, this
contains the maximum length for the field. If you use
mysql_use_result()
, the value of this variable is zero.
unsigned int flags
flags
value may have
zero or more of the following bits set:
Flag value | Flag meaning |
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) |
BLOB_FLAG |
Field is a BLOB or TEXT (deprecated) |
TIMESTAMP_FLAG |
Field is a TIMESTAMP (deprecated)
|
BLOB_FLAG
,
ENUM_FLAG
and TIMESTAMP_FLAG
flags is deprecated
because they indicate the type of a field rather than an attribute of its
type. It is preferable to test field->type
against
FIELD_TYPE_BLOB
, FIELD_TYPE_ENUM
or
FIELD_TYPE_TIMESTAMP
instead. The example below 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:
IS_NOT_NULL(flags) |
True if this field is defined as NOT NULL |
IS_PRI_KEY(flags) |
True if this field is a primary key |
IS_BLOB(flags) |
True if this field is a BLOB or TEXT
(deprecated; test field->type instead)
|
unsigned int decimals
The functions available in the C API are listed below and are described in greater detail in the next section. See section 21.4 C API function descriptions.
mysql_affected_rows() | Returns the number of rows affected by the last UPDATE ,
DELETE or INSERT query. |
mysql_close() | Closes a server connection. |
mysql_connect() | Connects to a MySQL server. This function is
deprecated; use mysql_real_connect() instead. |
mysql_change_user() | Change user and database on an open connection. |
mysql_create_db() | Creates a database. This function is deprecated; use the SQL command
CREATE DATABASE instead. |
mysql_data_seek() | Seeks to an arbitrary row in a query result set. |
mysql_debug() | Does a DBUG_PUSH with the given string. |
mysql_drop_db() | Drops a database. This function is deprecated; use the SQL command
DROP DATABASE instead. |
mysql_dump_debug_info() | Makes the server write debug information to the log. |
mysql_eof() | Determines whether or not 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 a SQL statement. |
mysql_fetch_field() | Returns the type of the next table field. |
mysql_fetch_field_direct() | Returns the type of a table field, given a field number. |
mysql_fetch_fields() | Returns an array of all field structures. |
mysql_fetch_lengths() | Returns the lengths of all columns in the current row. |
mysql_fetch_row() | Fetches the next row from the result set. |
mysql_field_seek() | Puts the column cursor on a specified column. |
mysql_field_count() | Returns the number of result columns for the most recent query. |
mysql_field_tell() | Returns the position of the field cursor used for the last
mysql_fetch_field() . |
mysql_free_result() | Frees memory used by a result set. |
mysql_get_client_info() | Returns client version information. |
mysql_get_host_info() | Returns a string describing the connection. |
mysql_get_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() | Kill a given thread. |
mysql_list_dbs() | Returns database names matching a simple regular expression. |
mysql_list_fields() | Returns field names matching a simple regular expression. |
mysql_list_processes() | Returns a list of the current server threads. |
mysql_list_tables() | Returns table names matching a simple regular expression. |
mysql_num_fields() | Returns the number of columns in a result set. |
mysql_num_rows() | Returns the number of rows in a result set. |
mysql_options() | Set connect options for mysql_connect() . |
mysql_ping() | Checks whether or not the connection to the server is working, reconnecting as necessary. |
mysql_query() | Executes a SQL query specified as a null-terminated string. |
mysql_real_connect() | Connects to a MySQL server. |
mysql_real_query() | Executes a SQL query specified as a counted string. |
mysql_reload() | Tells the server to reload the grant tables. |
mysql_row_seek() | Seeks to a row in a result set, using value returned from
mysql_row_tell() . |
mysql_row_tell() | Returns the row cursor position. |
mysql_select_db() | Connects to a database. |
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_use_result() | Initiates a row-by-row result set retrieval. |
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, user name and
password). When you are done with the connection, call
mysql_close()
to terminate it.
While a connection is active, the client may send SQL queries to the server
using mysql_query()
or mysql_real_query()
. The
difference between the two is that mysql_query()
expects the query
to be specified as a null-terminated string whereas
mysql_real_query()
expects a counted string. If the string contains
binary data (which may include null bytes), you must use
mysql_real_query()
.
For each non-SELECT
query (e.g., INSERT
,
UPDATE
, DELETE
), you can found out how many rows were
affected (changed) by calling mysql_affected_rows()
.
For SELECT
queries, you retrieve the selected rows as a result
set. (Note that some statements are SELECT
-like in that they return
rows. These include SHOW
, DESCRIBE
and
EXPLAIN
. They should be treated the same way as SELECT
statements.)
There are two ways for a client to process result sets. One way is to
retrieve the entire result set all at once by calling
mysql_store_result()
. This function acquires from the server all
the rows returned by the query and stores them in the client. The second way is
for the client to initiate a row-by-row result set retrieval by calling
mysql_use_result()
. This function initializes the retrieval, but
does not actually get any rows from the server.
In both cases, you access rows by calling mysql_fetch_row()
.
With mysql_store_result()
, mysql_fetch_row()
accesses
rows that have already been fetched from the server. With
mysql_use_result()
, mysql_fetch_row()
actually
retrieves the row from the server. Information about as the size of the data
values 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 since 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 since it maintains only one row at a time (and
since there is less allocation overhead, mysql_use_result()
can be
faster). Disadvantages are that you must process each row quickly to avoid tying
up the server, you don't have random access to rows within the result set (you
can only access rows sequentially), and you don't know how many rows are in the
result set until you have retrieved them all. Furthermore, you must
retrieve all the rows even if you determine in mid-retrieval that you've found
the information you were looking for.
The API makes it possible for clients to respond appropriately to queries
(retrieving rows only as necessary) without knowing whether or not the query is
a SELECT
. You can do this by calling
mysql_store_result()
after each mysql_query()
(or
mysql_real_query()
). If the result set call succeeds, the query was
a SELECT
and you can read the rows. If the result set call fails,
call mysql_field_count()
to determine whether or not a result was
actually to be expected. If mysql_field_count()
returns zero, the
query returned no data (indicating that it was an INSERT
,
UPDATE
, DELETE
, etc.), and thus not expected to return
rows. If mysql_field_count()
is non-zero, the query should have
returned rows, but didn't. This indicates that the query was a
SELECT
that failed. See the description for
mysql_field_count()
for an example of how this can be done.
Both mysql_store_result()
and mysql_use_result()
allow you to obtain information about the fields that make up the result set
(the number of fields, their names and types, etc.). You can access field
information sequentially within the row by calling
mysql_fetch_field()
repeatedly, or by field number within the row
by calling mysql_fetch_field_direct()
. The current field cursor
position may be changed by calling mysql_field_seek()
. Setting the
field cursor affects subsequent calls to mysql_fetch_field()
. You
can also get information for fields all at once by calling
mysql_fetch_fields()
.
For detecting and reporting errors, MySQL provides access to
error information by means of the mysql_errno()
and
mysql_error()
functions. These return the error code or error
message for the most recently invoked function that can succeed or fail,
allowing you to determine when an error occurred and what it was.
In the descriptions below, a parameter or return value of NULL
means NULL
in the sense of the C programming language, not a
MySQL NULL
value.
Functions that return a value generally return a pointer or an integer.
Unless specified otherwise, functions returning a pointer return a
non-NULL
value to indicate success or a NULL
value to
indicate an error, and functions returning an integer return zero to indicate
success or non-zero to indicate an error. Note that ``non-zero'' means just
that. Unless the function description says otherwise, do not test against a
value other than zero:
if (result) /* correct */ ... error ... if (result < 0) /* incorrect */ ... error ... if (result == -1) /* incorrect */ ... error ...
When a function returns an error, the Errors subsection of
the function description lists the possible types of errors. You can find out
which of these occurred by calling mysql_errno()
. A string
representation of the error may be obtained by calling
mysql_error()
.
mysql_affected_rows()
my_ulonglong mysql_affected_rows(MYSQL *mysql)
Returns the number of rows affected (changed) by the last
UPDATE
, DELETE
or INSERT
query. May be
called immediately after mysql_query()
for UPDATE
,
DELETE
or INSERT
statements. For SELECT
statements, mysql_affected_rows()
works like
mysql_num_rows()
.
mysql_affected_rows()
is currently implemented as a macro.
An integer greater than zero indicates the number of rows affected or
retrieved. Zero indicates that no records 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()
.
None.
mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10"); printf("%d products updated",mysql_affected_rows(&mysql));
mysql_close()
void mysql_close(MYSQL *mysql)
Closes a previously opened connection. mysql_close()
also
deallocates the connection handle pointed to by mysql
if the handle
was allocated automatically by mysql_init()
or
mysql_connect()
.
None.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_connect()
MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user,
const char *passwd)
This function is deprecated. It is preferable to use
mysql_real_connect()
instead.
mysql_connect()
attempts to establish a connection to a
MySQL database engine running on host
.
mysql_connect()
must complete successfully before you can execute
any of the other API functions, with the exception of
mysql_get_client_info()
.
The meanings of the parameters are the same as for the corresponding
parameters for mysql_real_connect()
with the difference that the
connection paramater may be NULL
. In this case the C API allocates
memory for the connection structure automatically and frees it when you call
mysql_close()
. The disadvantage of this approach is that you can't
retrieve an error message if the connection fails. (To get error information
from mysql_errno()
or mysql_error()
, you must provide
a valid MYSQL
pointer.)
Same as for mysql_real_connect()
.
Same as for mysql_real_connect()
.
mysql_change_user()
my_bool mysql_change_user(MYSQL *mysql, const char *user, const char
*password, const char *db)
Changes the user and causes the database specified by db
to
become the default (current) database on the connection specified by
mysql
. In subsequent queries, this database is the default for
table references that do not include an explicit database specifier.
This function was introduced in MySQL 3.23.3.
mysql_change_user()
fails unless the connected user can be
authenticated or if he 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.
Zero for success. Non-zero if an error occurred.
The same that you can get from mysql_real_connect()
.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
ER_UNKNOWN_COM_ERROR
ER_ACCESS_DENIED_ERROR
ER_BAD_DB_ERROR
ER_DBACCESS_DENIED_ERROR
ER_WRONG_DB_NAME
if (mysql_change_user(&mysql, "user", "password", "new_database")) { fprintf(stderr, "Failed to change user. Error: %s\n", mysql_error(&mysql)); }
mysql_create_db()
int mysql_create_db(MYSQL *mysql, const char *db)
Creates the database named by the db
parameter.
This function is deprecated. It is preferable to use
mysql_query()
to issue a SQL CREATE DATABASE
statement
instead.
Zero if the database was created successfully. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
if(mysql_create_db(&mysql, "my_database")) { fprintf(stderr, "Failed to create new database. Error: %s\n", mysql_error(&mysql)); }
mysql_data_seek()
void mysql_data_seek(MYSQL_RES *result, unsigned long long
offset)
Seeks to an arbitrary row in a query result set. This requires that the
result set structure contains the entire result of the query, so
mysql_data_seek()
may be used in conjunction only with
mysql_store_result()
, not with mysql_use_result()
.
The offset should be a value in the range from 0 to
mysql_num_rows(result)-1
.
None.
None.
mysql_debug()
void mysql_debug(char *debug)
Does a DBUG_PUSH
with the given string.
mysql_debug()
uses the Fred Fish debug library. To use this
function, you must compile the client library to support debugging. See section
G.1
Debugging a MySQL server. See section G.2
Debugging a MySQL client.
None.
None.
The call shown below causes the client library to generate a trace file in `/tmp/client.trace' on the client machine:
mysql_debug("d:t:O,/tmp/client.trace");
mysql_drop_db()
int mysql_drop_db(MYSQL *mysql, const char *db)
Drops the database named by the db
parameter.
This function is deprecated. It is preferable to use
mysql_query()
to issue a SQL DROP DATABASE
statement
instead.
Zero if the database was dropped successfully. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
if(mysql_drop_db(&mysql, "my_database")) fprintf(stderr, "Failed to drop the database: Error: %s\n", mysql_error(&mysql));
mysql_dump_debug_info()
int mysql_dump_debug_info(MYSQL *mysql)
Instructs the server to write some debug information to the log. The connected user must have the process privilege for this to work.
Zero if the command was successful. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_eof()
my_bool mysql_eof(MYSQL_RES *result)
This function is deprecated. mysql_errno()
or
mysql_error()
may be used instead.
mysql_eof()
determines whether or not 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()
.
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()
. Since those error functions provide the same
information, their use is preferred over mysql_eof()
, which is now
deprecated. (In fact, they provide more information, since
mysql_eof()
returns only a boolean value whereas the error
functions indicate a reason for the error when one occurs.)
Zero if an error occurred. Non-zero if the end of the result set has been reached.
None.
The following example shows how you might use mysql_eof()
:
mysql_query(&mysql,"SELECT * FROM some_table"); result = mysql_use_result(&mysql); while((row = mysql_fetch_row(result))) { // do something with data } if(!mysql_eof(result)) // mysql_fetch_row() failed due to an error { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); }
However, you can achieve the same effect with the standard MySQL error functions:
mysql_query(&mysql,"SELECT * FROM some_table"); result = mysql_use_result(&mysql); while((row = mysql_fetch_row(result))) { // do something with data } if(mysql_errno(&mysql)) // mysql_fetch_row() failed due to an error { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); }
mysql_errno()
unsigned int mysql_errno(MYSQL *mysql)
For the connection specified by mysql
,
mysql_errno()
returns the error code for the most recently invoked
API function that can succeed or fail. A return value of zero means that no
error occurred. Client error message numbers are listed in the
MySQL `errmsg.h' header file. Server error message
numbers are listed in `mysqld_error.h'
An error code value. Zero if no error occurred.
None.
mysql_error()
char *mysql_error(MYSQL *mysql)
For the connection specified by mysql
,
mysql_error()
returns the error message for the most recently
invoked 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_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 10.1 What languages are supported by MySQL?.
A character string that describes the error. An empty string if no error occurred.
None.
mysql_escape_string()
unsigned int mysql_escape_string(char *to, const char *from, unsigned
int length)
Encodes the string in from
to an escaped SQL string that can be
sent to the server in a SQL statement, places the result in to
, and
adds a terminating null byte. Characters encoded are NUL
(ASCII 0),
`\n', `\r', `\', `'',
`"' and Control-Z (see section 7.1
Literals: how to write strings and numbers).
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 worse case, each character may need
to be encoded as using two bytes, and you need room for the terminating null
byte.) When mysql_escape_string()
returns, the contents of
to
will be a null-terminated string. The return value is the length
of the encoded string, not including the terminating null character.
char query[1000],*end; end = strmov(query,"INSERT INTO test_table values("); *end++ = '\''; end += mysql_escape_string(end,"What's this",11); *end++ = '\''; *end++ = ','; *end++ = '\''; end += mysql_escape_string(end,"binary data: \0\r\n",16); *end++ = '\''; *end++ = ')'; if (mysql_real_query(&mysql,query,(unsigned int) (end - query))) { fprintf(stderr, "Failed to insert row, Error: %s\n", mysql_error(&mysql)); }
The strmov()
function used in the example is included in the
mysqlclient
library and works like strcpy()
but
returns a pointer to the terminating null of the first parameter.
The length of the value placed into to
, not including the
terminating null character.
None.
mysql_fetch_field()
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)
Returns the definition of one column of a result set as a
MYSQL_FIELD
structure. Call this function repeatedly to retrieve
information about all columns in the result set.
mysql_fetch_field()
returns NULL
when no more fields
are left.
mysql_fetch_field()
is reset to return information about the
first field each time you execute a new SELECT
query. The field
returned by mysql_fetch_field()
is also affected by calls to
mysql_field_seek()
.
If you've called mysql_query()
to perform a SELECT
on a table but have not called mysql_store_result()
,
MySQL returns the default blob length (8K bytes) if you call
mysql_fetch_field()
to ask for the length of a BLOB
field. (The 8K size is chosen because MySQL doesn't know the
maximum length for the BLOB
. This should be made configurable
sometime.) Once you've retrieved the result set,
field->max_length
contains the length of the largest value for
this column in the specific query.
The MYSQL_FIELD
structure for the current column.
NULL
if no columns are left.
None.
MYSQL_FIELD *field; while((field = mysql_fetch_field(result))) { printf("field name %s\n", field->name); }
mysql_fetch_fields()
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)
Returns an array of all MYSQL_FIELD
structures for a result set.
Each structure provides the field definition for one column of the result set.
An array of MYSQL_FIELD
structures for all columns of a result
set.
None.
unsigned int num_fields; unsigned int i; MYSQL_FIELD *fields; num_fields = mysql_num_fields(result); fields = mysql_fetch_fields(result); for(i = 0; i < num_fields; i++) { printf("Field %u is %s\n", i, fields[i].name); }
mysql_fetch_field_direct()
MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int
fieldnr)
Given a field number fieldnr
for a column within a result set,
returns that column's field definition as a MYSQL_FIELD
structure.
You may use this function to retrieve the definition for an arbitrary column.
The value of fieldnr
should be in the range from 0 to
mysql_num_fields(result)-1
.
The MYSQL_FIELD
structure for the specified column.
None.
unsigned int num_fields; unsigned int i; MYSQL_FIELD *field; num_fields = mysql_num_fields(result); for(i = 0; i < num_fields; i++) { field = mysql_fetch_field_direct(result, i); printf("Field %u is %s\n", i, field->name); }
mysql_fetch_lengths()
unsigned long *mysql_fetch_lengths(MYSQL_RES *result)
Returns the lengths of the columns of the current row within a result set. If
you plan to copy field values, this length information is also useful for
optimization, because you can avoid calling strlen()
. In addition,
if the result set contains binary data, you must use this function to
determine the size of the data, because strlen()
returns incorrect
results for any field containing null characters.
The length for empty columns and for columns containing NULL
values is zero. To see how to distinguish these two cases, see the description
for mysql_fetch_row()
.
An array of unsigned long integers representing the size of each column (not
including any terminating null characters). NULL
if an error
occurred.
mysql_fetch_lengths()
is valid only for the current row of the
result set. It returns NULL
if you call it before calling
mysql_fetch_row()
or after retrieving all rows in the result.
MYSQL_ROW row; unsigned long *lengths; unsigned int num_fields; unsigned int i; row = mysql_fetch_row(result); if (row) { num_fields = mysql_num_fields(result); lengths = mysql_fetch_lengths(result); for(i = 0; i < num_fields; i++) { printf("Column %u is %lu bytes in length.\n", i, lengths[i]); } }
mysql_fetch_row()
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
Retrieves the next row of a result set. When used after
mysql_store_result()
, mysql_fetch_row()
returns
NULL
when there are no more rows to retrieve. When used after
mysql_use_result()
, mysql_fetch_row()
returns
NULL
when there are no more rows to retrieve or if an error
occurred.
The number of values in the row is given by
mysql_num_fields(result)
. If row
holds the return
value from a call to mysql_fetch_row()
, pointers to the values are
accessed as row[0]
to row[mysql_num_fields(result)-1]
.
NULL
values in the row are indicated by NULL
pointers.
The lengths of the field values in the row may be obtained by calling
mysql_fetch_lengths()
. Empty fields and fields containing
NULL
both have length 0; you can distinguish these by checking the
pointer for the field value. If the pointer is NULL
, the field is
NULL
; otherwise the field is empty.
A MYSQL_ROW
structure for the next row. NULL
if
there are no more rows to retrieve or if an error occurred.
CR_SERVER_LOST
CR_UNKNOWN_ERROR
MYSQL_ROW row; unsigned int num_fields; unsigned int i; num_fields = mysql_num_fields(result); while ((row = mysql_fetch_row(result))) { unsigned long *lengths; lengths = mysql_fetch_lengths(result); for(i = 0; i < num_fields; i++) { printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL"); } printf("\n"); }
mysql_field_count()
unsigned int mysql_field_count(MYSQL *mysql)
If you are using a version of MySQL earlier than 3.22.24,
you should use unsigned int mysql_num_fields(MYSQL *mysql)
instead.
Returns the number of columns for the most recent query on the connection.
The normal use of this function is when mysql_store_result()
returned NULL
(and thus you have no result set pointer). In this
case, you can call mysql_field_count()
to determine whether or not
mysql_store_result()
should have produced a non-empty result. This
allows the client program to take proper action without knowing whether or not
the query was a SELECT
(or SELECT
-like) statement. The
example shown below illustrates how this may be done.
See section 21.4.51
Why is it that after mysql_query()
returns success,
mysql_store_result()
sometimes returns NULL?
.
An unsigned integer representing the number of fields in a result set.
None.
MYSQL_RES *result; unsigned int num_fields; unsigned int num_rows; if (mysql_query(&mysql,query_string)) { // error } else // query succeeded, process any data returned by it { result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result() returned nothing; should it have? { if(mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT) num_rows = mysql_affected_rows(&mysql); } else // mysql_store_result() should have returned data { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } } }
An alternative is to replace the mysql_field_count(&mysql)
call with mysql_errno(&mysql)
. In this case, you are checking
directly for an error from mysql_store_result()
rather than
inferring from the value of mysql_field_count()
whether or not the
statement was a SELECT
.
mysql_field_seek()
MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result,
MYSQL_FIELD_OFFSET offset)
Sets the field cursor to the given offset. The next call to
mysql_fetch_field()
will retrieve the field definition of the
column associated with that offset.
To seek to the beginning of a row, pass an offset
value of zero.
The previous value of the field cursor.
None.
mysql_field_tell()
MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)
Returns the position of the field cursor used for the last
mysql_fetch_field()
. This value can be used as an argument to
mysql_field_seek()
.
The current offset of the field cursor.
None.
mysql_free_result()
void mysql_free_result(MYSQL_RES *result)
Frees the memory allocated for a result set by
mysql_store_result()
, mysql_use_result()
,
mysql_list_dbs()
, etc. When you are done with a result set, you
must free the memory it uses by calling mysql_free_result()
.
None.
None.
mysql_get_client_info()
char *mysql_get_client_info(void)
Returns a string that represents the client library version.
A character string that represents the MySQL client library version.
None.
mysql_get_host_info()
char *mysql_get_host_info(MYSQL *mysql)
Returns a string describing the type of connection in use, including the server host name.
A character string representing the server host name and the connection type.
None.
mysql_get_proto_info()
unsigned int mysql_get_proto_info(MYSQL *mysql)
Returns the protocol version used by current connection.
An unsigned integer representing the protocol version used by the current connection.
None.
mysql_get_server_info()
char *mysql_get_server_info(MYSQL *mysql)
Returns a string that represents the server version number.
A character string that represents the server version number.
None.
mysql_info()
char *mysql_info(MYSQL *mysql)
Retrieves a string providing information about the most recently executed
query, but only for the statements listed below. For other statements,
mysql_info()
returns NULL
. The format of the string
varies depending on the type of query, as described below. The numbers are
illustrative only; the string will contain values appropriate for the query.
INSERT INTO ... SELECT ...
Records: 100 Duplicates: 0 Warnings: 0
INSERT INTO ... VALUES (...),(...),(...)...
Records: 3 Duplicates: 0 Warnings: 0
LOAD DATA INFILE ...
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
ALTER TABLE
Records: 3 Duplicates: 0 Warnings: 0
UPDATE
Rows matched: 40 Changed: 40 Warnings: 0
Note that mysql_info()
returns a non-NULL
value for
the INSERT ... VALUES
statement only if multiple value lists are
specified in the statement.
A character string representing additional information about the most
recently executed query. NULL
if no information is available for
the query.
None.
mysql_init()
MYSQL *mysql_init(MYSQL *mysql)
Allocates or initializes a MYSQL
object suitable for
mysql_real_connect()
. If mysql
is a NULL
pointer, the function allocates, initializes and returns a new object. Otherwise
the object is initialized and the address of the object is returned. If
mysql_init()
allocates a new object, it will be freed when
mysql_close()
is called to close the connection.
An initialized MYSQL*
handle. NULL
if there was
insufficient memory to allocate a new object.
In case of insufficient memory, NULL
is returned.
mysql_insert_id()
my_ulonglong mysql_insert_id(MYSQL *mysql)
Returns the ID generated for an AUTO_INCREMENT
column by the
previous query. Use this function after you have performed an
INSERT
query into a table that contains an
AUTO_INCREMENT
field.
Note that mysql_insert_id()
returns 0
if the
previous query does not generate an AUTO_INCREMENT
value. If you
need to save the value for later, be sure to call mysql_insert_id()
immediately after the query that generates the value.
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 queries since the value of that function is maintained
in the server.
The value of the AUTO_INCREMENT
field that was updated by the
previous query. Returns zero if there was no previous query on the connection or
if the query did not update an AUTO_INCREMENT
value.
None.
mysql_kill()
int mysql_kill(MYSQL *mysql, unsigned long pid)
Asks the server to kill the thread specified by pid
.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_dbs()
MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
Returns a result set consisting of database names on the server that match
the simple regular expression specified by the wild
parameter.
wild
may contain the wildcard characters `%' or
`_', or may be a NULL
pointer to match all databases.
Calling mysql_list_dbs()
is similar to executing the query
SHOW databases [LIKE wild]
.
You must free the result set with mysql_free_result()
.
A MYSQL_RES
result set for success. NULL
if an
error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_fields()
MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const
char *wild)
Returns a result set consisting of field names in the given table that match
the simple regular expression specified by the wild
parameter.
wild
may contain the wildcard characters `%' or
`_', or may be a NULL
pointer to match all fields.
Calling mysql_list_fields()
is similar to executing the query
SHOW COLUMNS FROM tbl_name [LIKE wild]
.
Note that it's recommended that you use SHOW COLUMNS FROM
tbl_name
instead of mysql_list_fields()
.
You must free the result set with mysql_free_result()
.
A MYSQL_RES
result set for success. NULL
if an
error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_processes()
MYSQL_RES *mysql_list_processes(MYSQL *mysql)
Returns a result set describing the current server threads. This is the same
kind of information as that reported by mysqladmin processlist
or a
SHOW PROCESSLIST
query.
You must free the result set with mysql_free_result()
.
A MYSQL_RES
rsult set for success. NULL
if an error
occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_list_tables()
MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
Returns a result set consisting of table names in the current database that
match the simple regular expression specified by the wild
parameter. wild
may contain the wildcard characters
`%' or `_', or may be a NULL
pointer to
match all tables. Calling mysql_list_tables()
is similar to
executing the query SHOW tables [LIKE wild]
.
You must free the result set with mysql_free_result()
.
A MYSQL_RES
result set for success. NULL
if an
error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_num_fields()
unsigned int mysql_num_fields(MYSQL_RES *result)
or
unsigned int mysql_num_fields(MYSQL *mysql)
The second form doesn't work on MySQL 3.22.24 or newer. To
pass a MYSQL*
argument, you must use unsigned int
mysql_field_count(MYSQL *mysql)
instead.
Returns the number of columns in a result set.
Note that you can get the number of columns either from a pointer to a result
set or to a connection handle. You would use the connection handle if
mysql_store_result()
or mysql_user_result()
returned
NULL
(and thus you have no result set pointer). In this case, you
can call mysql_field_count()
to determine whether or not
mysql_store_result()
should have produced a non-empty result. This
allows the client program to take proper action without knowing whether or not
the query was a SELECT
(or SELECT
-like) statement. The
example shown below illustrates how this may be done.
See section 21.4.51
Why is it that after mysql_query()
returns success,
mysql_store_result()
sometimes returns NULL?
.
An unsigned integer representing the number of fields in a result set.
None.
MYSQL_RES *result; unsigned int num_fields; unsigned int num_rows; if (mysql_query(&mysql,query_string)) { // error } else // query succeeded, process any data returned by it { result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result() returned nothing; should it have? { if (mysql_errno(&mysql)) { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } else if (mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT) num_rows = mysql_affected_rows(&mysql); } } }
An alternative (if you KNOW that your query should have returned a result
set) is to replace the mysql_errno(&mysql)
call with a check if
mysql_field_count(&mysql)
is = 0. This will only happen if
something went wrong.
mysql_num_rows()
my_ulonglong mysql_num_rows(MYSQL_RES *result)
Returns the number of rows in the result set.
The use of mysql_num_rows()
depends on whether you use
mysql_store_result()
or mysql_use_result()
to return
the result set. If you use mysql_store_result()
,
mysql_num_rows()
may be called immediately. If you use
mysql_use_result()
, mysql_num_rows()
will not return
the correct value until all the rows in the result set have been retrieved.
The number of rows in the result set.
None.
mysql_options()
int mysql_options(MYSQL *mysql, enum mysql_option option, const char
*arg)
Can be used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options.
mysql_options()
should be called after mysql_init()
and before mysql_connect()
or mysql_real_connect()
.
The option
argument is the option that you want to set; the
arg
argument is the value for the option. If the option is an
integer, then arg
should point to the value of the integer.
Possible options values:
Option | Argument type | Function |
MYSQL_OPT_CONNECT_TIMEOUT |
unsigned int * |
Connect timeout in seconds. |
MYSQL_OPT_COMPRESS |
Not used | Use the compressed client/server protocol. |
MYSQL_OPT_NAMED_PIPE |
Not used | Use named pipes to connect to a MySQL server on NT. |
MYSQL_INIT_COMMAND |
char * |
Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting. |
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 . |
Note that the group client
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:
compress |
Use the compressed client/server protocol. |
database |
Connect to this database if there no database was specified in the connect command |
debug |
Debug options |
host |
Default host name |
init-command |
Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting. |
password |
Default password |
pipe |
Use named pipes to connect to a MySQL server on NT. |
port |
Default port number |
return-found-rows |
Tell mysql_info() to return found rows instead of updated
rows when using UPDATE . |
socket |
Default socket number |
timeout |
Connect timeout in seconds. |
user |
Default user |
For more information about option files, see section 4.15.4 Option files.
Zero for success. Non-zero if you used an unknown option.
MYSQL mysql; mysql_init(&mysql); mysql_options(&mysql,MYSQL_OPT_COMPRESS,0); mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc"); if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)) { fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql)); }
The above requests the client to use the compressed client/server protocol
and read the additional options from the odbc
section in the
my.cnf
file.
mysql_ping()
int mysql_ping(MYSQL *mysql)
Checks whether or not the connection to the server is working. If it has gone down, an automatic reconnection is attempted.
This function can be used by clients that remain idle for a long while, to check whether or not the server has closed the connection and reconnect if necessary.
Zero if the server is alive. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_UNKNOWN_ERROR
mysql_query()
int mysql_query(MYSQL *mysql, const char *query)
Executes the SQL query pointed to by the null-terminated string
query
. The query must consist of a single SQL statement. You should
not add a terminating semicolon (`;') or \g
to the
statement.
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.)
Zero if the query was successful. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_real_connect()
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char
*user, const char *passwd, const char *db, unsigned int port, const char
*unix_socket, unsigned int client_flag)
mysql_real_connect()
attempts to establish a connection to a
MySQL database engine running on host
.
mysql_real_connect()
must complete successfully before you can
execute any of the other API functions, with the exception of
mysql_get_client_info()
.
The parameters are specified as follows:
MYSQL
structure. Before calling mysql_real_connect()
you must call mysql_init()
to initialize the MYSQL
structure. See the example below.
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 (Win32), they are used instead of
TCP/IP to connect to the server.
user
parameter contains the user's MySQL
login ID. If user
is NULL
, the current user is
assumed. Under Unix, this is the current login name. Under Windows ODBC, the
current user name must be specified explicitly. See section 17.2
How to fill in the various fields in the ODBC administrator program.
passwd
parameter contains the password for
user
. If passwd
is NULL
, only entries
in the user
table for the user that have a blank password field
will be checked for a match. This allows the database administrator to set up
the MySQL privilege system in such a way that users get
different privileges depending on whether or not they have specified a
password. Note: Do not attempt to encrypt the password before calling
mysql_real_connect()
; password encryption is handled
automatically by the client API.
db
is the database name. If db
is not
NULL
, the connection will set the default database to this value.
port
is not 0, the value will be used as the port number
for the TCP/IP connection. Note that the host
parameter
determines the type of the connection.
unix_socket
is not NULL
, the string specifies
the socket or named pipe that should be used. Note that the host
parameter determines the type of the connection.
client_flag
is usually 0, but can be set to a
combination of the following flags in very special circumstances:
Flag name | Flag meaning |
CLIENT_FOUND_ROWS |
Return the number of found (matched) rows, not the number of affected rows |
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_COMPRESS |
Use compression protocol |
CLIENT_ODBC |
The client is an ODBC client. This changes mysqld to be
more ODBC-friendly. |
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,
unless you pass NULL
for that parameter.
CR_CONN_HOST_ERROR
CR_CONNECTION_ERROR
CR_IPSOCK_ERROR
CR_OUT_OF_MEMORY
CR_SOCKET_CREATE_ERROR
CR_UNKNOWN_HOST
CR_VERSION_ERROR
--old-protocol
option.
CR_NAMEDPIPEOPEN_ERROR;
CR_NAMEDPIPEWAIT_ERROR;
CR_NAMEDPIPESETSTATE_ERROR;
MYSQL mysql; mysql_init(&mysql); mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name"); if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)) { fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql)); }
By using mysql_options()
the MySQL library will
read the [client]
and your_prog_name
sections in the
my.cnf
file which will ensure that your program will work, even if
someone has set up MySQL in some not standard way.
mysql_real_query()
int mysql_real_query(MYSQL *mysql, const char *query, unsigned int
length)
Executes the SQL query pointed to by query, which should be a string
length
bytes long. The query must consist of a single SQL
statement. You should not add a terminating semicolon (`;') or
\g
to the statement.
You must use mysql_real_query()
rather than
mysql_query()
for queries that contain binary data, since binary
data may contain the `\0' character. In addition,
mysql_real_query()
is faster than mysql_query()
since
it does not call strlen()
on the query string.
Zero if the query was successful. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_reload()
int mysql_reload(MYSQL *mysql)
Asks the MySQL server to reload the grant tables. The connected user must have the reload privilege.
This function is deprecated. It is preferable to use
mysql_query()
to issue a SQL FLUSH PRIVILEGES
statement instead.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_row_seek()
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET
offset)
Sets the row cursor to an arbitrary row in a query result set. This requires
that the result set structure contains the entire result of the query, so
mysql_row_seek()
may be used in conjunction only with
mysql_store_result()
, not with mysql_use_result()
.
The offset should be a value returned from a call to
mysql_row_tell()
or to mysql_row_seek()
. This value is
not simply a row number; if you want to seek to a row within a result set using
a row number, use mysql_data_seek()
instead.
The previous value of the row cursor. This value may be passed to a
subsequent call to mysql_row_seek()
.
None.
mysql_row_tell()
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)
Returns the current position of the row cursor for the last
mysql_fetch_row()
. This value can be used as an argument to
mysql_row_seek()
.
You should use mysql_row_tell()
only after
mysql_store_result()
, not after mysql_use_result()
.
The current offset of the row cursor.
None.
mysql_select_db()
int mysql_select_db(MYSQL *mysql, const char *db)
Causes the database specified by db
to become the default
(current) database on the connection specified by mysql
. In
subsequent queries, this database is the default for table references that do
not include an explicit database specifier.
mysql_select_db()
fails unless the connected user can be
authenticated as having permission to use the database.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_shutdown()
int mysql_shutdown(MYSQL *mysql)
Asks the database server to shutdown. The connected user must have shutdown privileges.
Zero for success. Non-zero if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_stat()
char *mysql_stat(MYSQL *mysql)
Returns a character string containing information similar to that provided by
the mysqladmin status
command. This includes uptime in seconds and
the number of running threads, questions, reloads and open tables.
A character string describing the server status. NULL
if an
error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_store_result()
MYSQL_RES *mysql_store_result(MYSQL *mysql)
You must call mysql_store_result()
or
mysql_use_result()
for every query which successfully retrieves
data (SELECT
, SHOW
, DESCRIBE
,
EXPLAIN
).
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.
An empty result set is returned if there are no rows returned. (An empty
result set differs from a NULL
return value.)
Once you have called mysql_store_result()
, you may call
mysql_num_rows()
to find out how many rows are in the result set.
You can call mysql_fetch_row()
to fetch rows from the result
set, or mysql_row_seek()
and mysql_row_tell()
to
obtain or set the current row position within the result set.
You must call mysql_free_result()
once you are done with the
result set.
See section 21.4.51
Why is it that after mysql_query()
returns success,
mysql_store_result()
sometimes returns NULL?
.
A MYSQL_RES
result structure with the results. NULL
if an error occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_thread_id()
unsigned long mysql_thread_id(MYSQL *mysql)
Returns the thread ID of the current connection. This value can be used as an
argument to mysql_kill()
to kill the thread.
If the connection is lost and you reconnect with mysql_ping()
,
the thread ID will change. This means you should not get the thread ID and store
it for later, you should get it when you need it.
The thread ID of the current connection.
None.
mysql_use_result()
MYSQL_RES *mysql_use_result(MYSQL *mysql)
You must call mysql_store_result()
or
mysql_use_result()
for every query which successfully retrieves
data (SELECT
, SHOW
, DESCRIBE
,
EXPLAIN
).
mysql_use_result()
initiates a result set retrieval but does not
actually read the result set into the client like
mysql_store_result()
does. Instead, each row must be retrieved
individually by making calls to mysql_fetch_row()
. This reads the
result of a query directly from the server without storing it in a temporary
table or local buffer, which is somewhat faster and uses much less memory than
mysql_store_result()
. The client will only allocate memory for the
current row and a communication buffer that may grow up to
max_allowed_packet
bytes.
On the other hand, you shouldn't use mysql_use_result()
if you
are doing a lot of processing for each row on the client side, or if the output
is sent to a screen on which the user may type a ^S
(stop scroll).
This will tie up the server and prevent other threads from updating any tables
from which the data are fetched.
When using mysql_use_result()
, you must execute
mysql_fetch_row()
until a NULL
value is returned,
otherwise the unfetched rows will be returned as part of the result set for your
next query. The C API will give the error Commands out of sync; You can't
run this command now
if you forget to do this!
You may not use mysql_data_seek()
,
mysql_row_seek()
, mysql_row_tell()
,
mysql_num_rows()
or mysql_affected_rows()
with a
result returned from mysql_use_result()
, nor may you issue other
queries until the mysql_use_result()
has finished. (However, after
you have fetched all the rows, mysql_num_rows()
will accurately
return the number of rows fetched.)
You must call mysql_free_result()
once you are done with the
result set.
A MYSQL_RES
result structure. NULL
if an error
occurred.
CR_COMMANDS_OUT_OF_SYNC
CR_OUT_OF_MEMORY
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_query()
returns success, mysql_store_result()
sometimes returns NULL?
It is possible for mysql_store_result()
to return
NULL
following a successful call to mysql_query()
.
When this happens, it means one of the following conditions occurred:
malloc()
failure (for example, if the result set
was too large).
INSERT
,
UPDATE
or DELETE
). You can always check whether or not 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
. An exception is that if DELETE
is used
without a WHERE
clause, the table is truncated, which is much
faster! In this case, mysql_affected_rows()
returns zero for the
number of records affected.
mysql_num_rows()
returns the number of rows in a result set.
With mysql_store_result()
, mysql_num_rows()
may be
called as soon as mysql_store_result()
returns. With
mysql_use_result()
, mysql_num_rows()
may be called
only after you have fetched all the rows with mysql_fetch_row()
.
mysql_insert_id()
returns the ID generated by the last query
that inserted a row into a table with an AUTO_INCREMENT
index.
See section 21.4.29
mysql_insert_id()
.
LOAD DATA INFILE ...
, INSERT INTO ...
SELECT ...
, UPDATE
) return additional info. 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 in a table containing a column that has the
AUTO_INCREMENT
attribute, you can get the most recently generated
ID by calling the mysql_insert_id()
function.
You can also retrieve the ID by using the LAST_INSERT_ID()
function in a query string that you pass to mysql_query()
.
You can check if an AUTO_INCREMENT
index is used by executing
the following code. This also checks if the query was an INSERT
with an AUTO_INCREMENT
index:
if (mysql_error(&mysql)[0] == 0 && mysql_num_fields(result) == 0 && mysql_insert_id(&mysql) != 0) { used_id = mysql_insert_id(&mysql); }
The most recently generated ID is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not even
be changed if you update another AUTO_INCREMENT
column with a
non-magic value (that is, a value that is not NULL
and not
0
).
If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:
INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
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.
The client 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.
The standard client libraries are not compiled with the thread options.
To get a thread-safe client, use the -lmysys
,
-lstring
and -ldbug
libraries and
net_serv.o
that the server uses.
When using a threaded client, 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!
All functions except mysql_real_connect()
are currently
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 since 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> CPPFLAGS=-DTHREAD_SAFE_CLIENT ./configure ...
You may get some errors because of undefined symbols when linking the standard client, because the pthread libraries are not included by default.
The resulting `libmysqlclient.a' library is now thread-safe. What
this means is that client code is thread-safe as long as two threads don't query
the same connection handle returned by mysql_real_connect()
at the
same time; the client/server protocol allows only one request at a time on a
given connection. 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. (In other words, different threads can
use different MYSQL_RES
pointers that were created with
mysql_store_result()
, as long as they use the proper locking
protocol.) If you program with POSIX threads, you can use
pthread_mutex_lock()
and pthread_mutex_unlock()
to
establish and release a mutex lock.
If you used mysql_use_result()
rather than
mysql_store_result()
, the lock would need to surround
mysql_use_result()
and the calls to mysql_fetch_row()
.
However, it really is best for threaded clients not to use
mysql_use_result()
.
This section documents the Perl DBI
interface. The former
interface was called mysqlperl
. Since
DBI
/DBD
now is the recommended Perl interface,
mysqlperl
is obsolete and is not documented here.
DBI
with DBD::mysql
DBI
is a generic interface for many databases. That means that
you can write a script that works with many different database engines without
change. You need a DataBase Driver (DBD) defined for each database type. For
MySQL, this driver is called DBD::mysql
.
For more information on the Perl5 DBI, please visit the DBI
web
page and read the documentation:
http://www.symbolstone.org/technology/perl/DBI/index.html
For more information on Object Oriented Programming (OOP) as defined in Perl5, see the Perl OOP page:
http://language.perl.com/info/documentation.html
Installation instructions for MySQL Perl support are given in section 4.10 Perl installation comments.
DBI
interfacePortable DBI methods
connect |
Establishes a connection to a database server |
disconnect |
Disconnects from the database server |
prepare |
Prepares a SQL statement for execution |
execute |
Executes prepared statements |
do |
Prepares and executes a SQL statement |
quote |
Quotes string or BLOB values to be inserted |
fetchrow_array |
Fetches the next row as an array of fields. |
fetchrow_arrayref |
Fetches next row as a reference array of fields |
fetchrow_hashref |
Fetches next row as a reference to a hashtable |
fetchall_arrayref |
Fetches all data as an array of arrays |
finish |
Finishes a statement and let the system free resources |
rows |
Returns the number of rows affected |
data_sources |
Returns an array of databases available on localhost |
ChopBlanks |
Controls whether fetchrow_* methods trim spaces |
NUM_OF_PARAMS |
The number of placeholders in the prepared statement |
NULLABLE |
Which columns can be NULL |
trace |
Perform tracing for debugging |
MySQL-specific methods
insertid |
The latest AUTO_INCREMENT value |
is_blob |
Which column are BLOB values |
is_key |
Which columns are keys |
is_num |
Which columns are numeric |
is_pri_key |
Which columns are primary keys |
is_not_null |
Which columns CANNOT be NULL . See NULLABLE .
|
length |
Maximum possible column sizes |
max_length |
Maximum column sizes actually present in result |
NAME |
Column names |
NUM_OF_FIELDS |
Number of fields returned |
table |
Table names in returned set |
type |
All column types |
The Perl methods are described in more detail in the following sections. Variables used for method return values have these meanings:
$dbh
$sth
$rc
$rv
Portable DBI methods
connect($data_source, $username, $password)
connect
method
to make a database connection to the data source. The
$data_source
value should begin with
DBI:driver_name:
. Example uses of connect
with the
DBD::mysql
driver: $dbh = DBI->connect("DBI:mysql:$database", $user, $password); $dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password); $dbh = DBI->connect("DBI:mysql:$database:$hostname:$port", $user, $password);If the user name and/or password are undefined,
DBI
uses
the values of the DBI_USER
and DBI_PASS
environment
variables, respectively. If you don't specify a hostname, it defaults to
'localhost'
. If you don't specify a port number, it defaults to
the default MySQL port (3306). As of
Msql-Mysql-modules
version 1.2009, the $data_source
value allows certain modifiers:
mysql_read_default_file=file_name
mysql_read_default_group=group_name
[client]
group. By specifying the
mysql_read_default_group
option, the default group becomes the
[group_name]
group.
mysql_compression=1
mysql_socket=/path/to/socket
DBI
script,
you can take them from the user's `~/.my.cnf' option file instead by
writing your connect
call like this: $dbh = DBI->connect("DBI:mysql:$database" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf", $user, $password);This call will read options defined for the
[client]
group
in the option file. If you wanted to do the same thing, but use options
specified for the [perl]
group as well, you could use this: $dbh = DBI->connect("DBI:mysql:$database" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf" . ";mysql_read_default_group=perl", $user, $password);
disconnect
disconnect
method disconnects the database handle from
the database. This is typically called right before you exit from the program.
Example: $rc = $dbh->disconnect;
prepare($statement)
($sth)
which you can use to invoke the
execute
method. Typically you handle SELECT
statements (and SELECT
-like statements such as SHOW
,
DESCRIBE
and EXPLAIN
) by means of
prepare
and execute
. Example: $sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
execute
execute
method executes a prepared statement. For
non-SELECT
statements, execute
returns the number of
rows affected. If no rows are affected, execute
returns
"0E0"
, which Perl treats as zero but regards as true. For
SELECT
statements, execute
only starts the SQL query
in the database; you need to use one of the fetch_*
methods
described below to retrieve the data. Example: $rv = $sth->execute or die "can't execute the query: $sth->errstr;
do($statement)
do
method prepares and executes a SQL statement and
returns the number of rows affected. If no rows are affected, do
returns "0E0"
, which Perl treats as zero but regards as true.
This method is generally used for non-SELECT
statements which
cannot be prepared in advance (due to driver limitations) or which do not need
to executed more than once (inserts, deletes, etc.). Example: $rv = $dbh->do($statement) or die "Can't execute $statement: $dbh- >errstr\n";
quote($string)
quote
method is used to "escape" any special characters
contained in the string and to add the required outer quotation marks.
Example: $sql = $dbh->quote($string)
fetchrow_array
while(@row = $sth->fetchrow_array) { print qw($row[0]\t$row[1]\t$row[2]\n); }
fetchrow_arrayref
while($row_ref = $sth->fetchrow_arrayref) { print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n); }
fetchrow_hashref
while($hash_ref = $sth->fetchrow_hashref) { print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\ $hash_ref- > title}\n); }
fetchall_arrayref
my $table = $sth->fetchall_arrayref or die "$sth->errstr\n"; my($i, $j); for $i ( 0 .. $#{$table} ) { for $j ( 0 .. $#{$table->[$i]} ) { print "$table->[$i][$j]\t"; } print "\n"; }
finish
$rc = $sth->finish;
rows
SELECT
execute
statement. Example: $rv = $sth->rows;
NULLABLE
NULL
values. Example: $null_possible = $sth->{NULLABLE};
NUM_OF_FIELDS
SELECT
or SHOW FIELDS
statement. You may use this
for checking whether a statement returned a result: A zero value indicates a
non-SELECT
statement like INSERT
,
DELETE
or UPDATE
. Example: $nr_of_fields = $sth->{NUM_OF_FIELDS};
data_sources($driver_name)
'localhost'
.
Example: @dbs = DBI->data_sources("mysql");
ChopBlanks
fetchrow_*
methods will
chop leading and trailing blanks from the returned values. Example: $sth->{'ChopBlanks'} =1;
trace($trace_level)
trace($trace_level, $trace_filename)
trace
method enables or disables tracing. When invoked as
a DBI
class method, it affects tracing for all handles. When
invoked as a database or statement handle method, it affects tracing for the
given handle (and any future children of the handle). Setting
$trace_level
to 2 provides detailed trace information. Setting
$trace_level
to 0 disables tracing. Trace output goes to the
standard error output by default. If $trace_filename
is
specified, the file is opened in append mode and output for all
traced handles is written to that file. Example: DBI->trace(2); # trace everything DBI->trace(2,"/tmp/dbi.out"); # trace everything to /tmp/dbi.out $dth->trace(2); # trace this database handle $sth->trace(2); # trace this statement handleYou can also enable
DBI
tracing by setting the DBI_TRACE
environment
variable. Setting it to a numeric value is equivalent to calling
DBI->(value)
. Setting it to a pathname is equivalent to
calling DBI->(2,value)
. MySQL-specific methods
The methods shown below are MySQL-specific and not part of
the DBI
standard. Several of them are now deprecated:
is_blob
, is_key
, is_num
,
is_pri_key
, is_not_null
, length
,
max_length
, and table
. Where DBI
-standard
alternatives exist, they are noted below.
insertid
AUTO_INCREMENT
feature of MySQL, the new
auto-incremented values will be stored here. Example: $new_id = $sth->{insertid};As an alternative, you can use
$dbh->{'mysql_insertid'}
.
is_blob
BLOB
. Example: $keys = $sth->{is_blob};
is_key
$keys = $sth->{is_key};
is_num
$nums = $sth->{is_num};
is_pri_key
$pri_keys = $sth->{is_pri_key};
is_not_null
NULL
values. Example: $not_nulls = $sth->{is_not_null};
is_not_null
is deprecated; it is preferable to use the
NULLABLE
attribute (described above), since that is a DBI
standard.
length
max_length
length
array indicates the maximum possible sizes that each
column may be (as declared in the table description). The
max_length
array indicates the maximum sizes actually present in
the result table. Example: $lengths = $sth->{length}; $max_lengths = $sth->{max_length};
NAME
$names = $sth->{NAME};
table
$tables = $sth->{table};
type
$types = $sth->{type};
DBI
/DBD
informationYou can use the perldoc
command to get more information about
DBI
.
perldoc DBI perldoc DBI::FAQ perldoc DBD::mysql
You can also use the pod2man
, pod2html
, etc., tools
to translate to other formats.
And of course you can find the latest DBI
information at the
DBI
web page:
http://www.symbolstone.org/technology/perl/DBI/index.html
The MySQL Contrib directory contains an Eiffel wrapper written by Michael Ravits.
You can also find this at: http://www.netpedia.net/hosting/newplayer/
There are 2 supported JDBC drivers for MySQL (the twz and mm driver). You can find a copy of these at http://www.mysql.com/Contrib. For documentation consult any JDBC documentation and the drivers own documentation for MySQL specific features.
PHP is a server-side, HTML embedded scripting language that may be used to create dynamic web pages. It contains support for accessing several databases, including MySQL. PHP may be run as a separate program, or compiled as a module for use with the Apache web server.
The distribution and documentation are available at the PHP website.
MySQL
support. This is described in detail in the PHP
manual. Two API's are available in the MySQL Contrib directory.
The MySQL Contrib directory contains a Python interface written by Joseph Skinner.
You can also use the Python interface to iODBC to access a MySQL server. mxODBC
TCL at binevolve. The Contrib directory contains a TCL interface that is based on msqltcl 1.50.
Go to the first, previous, next, last section, table of contents.