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 showing how to use the C API, take a look at these clients.
Most of the other client APIs (all except Java) use this library to connect. So, for example, you can use the same environment variables as the ones used by other client programs. See section 12.1 Overview of the different MySQL programs.
The client has a maximum communication buffer size. The size of the buffer that is allocated initially (8192 bytes) is automatically increased up to the maximum size (the default for this is 24M). Since buffers are increased on demand (but not decreased until close), simply increasing the default limit doesn't cause more resources to be used. This size check is mostly a check for erroneous queries and communication packets.
The communication buffer must be big enough to contain a single SQL
statement and one row of returned data (not at the same time, of
course). Each thread's communication buffer is dynamically enlarged to
handle any row or query up to the imposed limit. For example, if you have
BLOB values that contain up to 16M of data, you must have at least 16M
as your communication buffer limit (in both server and client). See section 10.1 Changing the size of MySQL buffers.
The MySQL server shrinks each communication buffer to
net_buffer_length bytes after each query. The client doesn't
shrink the buffer automatically; Client memory is reclaimed when the
connection is closed.
MYSQL
MYSQL_RES
SELECT or SHOW).
The information returned from a query is called the result set in the
remainder of this section.
MYSQL_ROW
MYSQL_FIELD
MYSQL_FIELD structures for each field by
calling mysql_fetch_field() repeatedly.
MYSQL_FIELD_OFFSET
mysql_field_seek().) Offsets are field numbers
within a row, beginning at zero.
my_ulonglong
mysql_insert_id(). This
type provides a range of 0 to 1.84e19.
The MYSQL_FIELD structure contains the following members:
char * name
char * table
table value is a NULL pointer.
char * def
mysql_list_fields()).
enum enum_field_types type
type value may be one of the following:
| Type name | Type meaning |
FIELD_TYPE_TINY | TINYINT field
|
FIELD_TYPE_ENUM | ENUM field
|
FIELD_TYPE_DECIMAL | DECIMAL or NUMERIC field
|
FIELD_TYPE_SHORT | SMALLINT field
|
FIELD_TYPE_LONG | INTEGER field
|
FIELD_TYPE_FLOAT | FLOAT field
|
FIELD_TYPE_DOUBLE | DOUBLE or REAL field
|
FIELD_TYPE_NULL | NULL-type field
|
FIELD_TYPE_TIMESTAMP | TIMESTAMP field
|
FIELD_TYPE_LONGLONG | BIGINT field
|
FIELD_TYPE_INT24 | MEDIUMINT field
|
FIELD_TYPE_DATE | DATE field
|
FIELD_TYPE_TIME | TIME field
|
FIELD_TYPE_DATETIME | DATETIME field
|
FIELD_TYPE_YEAR | YEAR field
|
FIELD_TYPE_SET | SET field
|
FIELD_TYPE_BLOB | BLOB or TEXT field (use max_length to determine the maximum length)
|
FIELD_TYPE_STRING | String (CHAR or VARCHAR) field
|
FIELD_TYPE_CHAR | Deprecated: use FIELD_TYPE_TINY instead
|
IS_NUM() macro allows you to test if a field has a numeric type.
Pass the type member 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_list_fields(), this contains the maximum length for the field.
unsigned int flags
flags that you may use:
| Flag name | 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 key |
BLOB_FLAG | Field is a BLOB or TEXT
|
UNSIGNED_FLAG | Field is UNSIGNED
|
ZEROFILL_FLAG | Field has the ZEROFILL attribute
|
BINARY_FLAG | Field has the BINARY attribute
|
ENUM_FLAG | Field is an ENUM
|
AUTO_INCREMENT_FLAG | Field has the AUTO_INCREMENT attribute
|
TIMESTAMP_FLAG | Field is a TIMESTAMP
|
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 member:
IS_PRI_KEY(flags) | Is this field a primary key? |
IS_NOT_NULL(flags) | Is this field defined as NOT NULL?
|
IS_BLOB(flags) | Is this field a BLOB or TEXT?
|
BLOB_FLAG, ENUM_FLAG and TIMESTAMP_FLAG is
deprecated, since they indicate the type of a field rather than an attribute
of the type. It is preferable to test field->type against
FIELD_TYPE_BLOB, FIELD_TYPE_ENUM or FIELD_TYPE_TIMESTAMP
instead.
unsigned int decimals
The functions available in the C API are listed below. These functions are described in greater detail in the next section. See section 18.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_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 dump debug information to the log. |
| mysql_eof() | Determines whether or not the last row of a result set has been read. |
| mysql_errno() | Returns the error number from the last MySQL function. |
| mysql_error() | Returns the error message from the last MySQL function. |
| mysql_escape_string() | Escapes a string for a SQL statement. |
| mysql_fetch_field() | Returns the type of the next table field. |
| mysql_fetch_field_direct() | Returns the type of a numbered table field. |
| mysql_fetch_fields() | Returns an array of all field structures. |
| mysql_fetch_lengths() | Returns the length for 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_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() | Information about the most recently executed query. |
| mysql_init() | Get or initialize a MYSQL structure. |
| mysql_insert_id() |
Returns the last ID generated for an AUTO_INCREMENT field.
|
| 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_ping() | Checks if the connection to the server is working. |
| 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 access permissions tables. |
| mysql_row_tell() | Returns the row cursor. |
| 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() | Reads a result set to the client. |
| mysql_thread_id() | Returns the current thread id. |
| mysql_use_result() | Initiates a dynamic result set for each row. |
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.
mysql_affected_rows()
my_ulonglong mysql_affected_rows(MYSQL *mysql)
Returns the number of rows affected by the last UPDATE,
DELETE or INSERT query. May be called immediately after
mysql_query() for INSERT or UPDATE statements. For
SELECT statements, this works like mysql_num_rows().
mysql_affected_rows() is currently implemented as a macro.
An integer > 0 indicates the number of rows affected or retrieved.
Zero if no records matched the WHERE clause in the query or no query has yet
been executed.
-1 if the query returned an error or was called before
mysql_store_result() was called for a SELECT query.
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.
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. The value of host may be either a hostname or an IP
address. The user parameter contains the user's MySQL login
ID, and the passwd parameter contains the password for user.
NOTE: Do not attempt to encrypt passwd before calling
mysql_connect(). Password encryption is handled automatically by the
client API.
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 is NULL, the current user is assumed. Under Windows ODBC,
the current user must be specified explicitly. Under Unix, the current
login name is assumed.
passwd is NULL, only records 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.
mysql_connect() must complete successfully before you can execute any
of the other API functions, with the exception of
mysql_get_client_info().
You may optionally specify the first argument of mysql_connect() to
be a NULL pointer. This will force the C API to allocate memory for
the connection structure automatically and to free it when you call
mysql_close(). The disadvantage of this approach is that you can't
retrieve an error message from mysql_connect() if the connection
fails.
If the first argument is not a NULL pointer, it should be the address
of an existing MYSQL structure.
A MYSQL* connection handle if the connection was successful. A C
NULL pointer if the connection was unsuccessful.
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;
if(!mysql_connect(&mysql, "host", "username", "password"))
fprintf(stderr, "Failed to connect to database: 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 argument.
This function is deprecated. It is preferable to use mysql_query()
to issue a SQL CREATE DATABASE statement instead.
Zero if the database was successfully created. 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_new_db"))
fprintf(stderr, "Failed to create new database. Error: %s\n",
mysql_error(&mysql));
mysql_data_seek()
void mysql_data_seek(MYSQL_RES *res, unsigned int offset)
Seeks to an arbitrary row in a query result set. This function may be
used in conjunction only with mysql_store_result)(, not with
mysql_use_result().
The offset can be any value: 0 <= offset <= mysql_num_rows() -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.
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 argument.
This function is deprecated. It is preferable to use mysql_query()
to issue a SQL DROP DATABASE statement instead.
Zero if the database was successfully dropped. 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, "some_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 dump some debug information to the log. The connected user must have process privileges for this to work.
Zero if the command was successful. Non-zero if the command failed.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_eof()
my_bool mysql_eof(MYSQL_RES *result)
When mysql_fetch_row() returns nothing, mysql_eof()
returns a non-zero value if the end of the result set was reached and
zero if an error occurred. If an error occurred, the preferred method
of finding out what the was was is to call mysql_errno().
mysql_eof() may only be used with mysql_use_result(),
not with mysql_store_result().
Zero if the end of the result set has not yet been reached. Non-zero if the end of the result set has been reached.
None.
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 some error
}
mysql_errno()
unsigned int mysql_errno(MYSQL *mysql)
Returns the error code for the last error that occurred on the connection
specified by mysql. A return value of zero means that no error
occurred. Client error message numbers are listed in `errmsg.h'. Server
error message numbers are listed in `mysqld_error.h'
An error code value. Zero if no error has occurred.
None.
mysql_error()
char *mysql_error(MYSQL *mysql)
Returns the error message, if any, describing the last MySQL error
that occurred on the connection specified by mysql. An empty string is
returned if no error occurred. The language of the client error messages may
be changed by recompiling the MySQL client library. You currently
can choose between English or German client error messages.
A character string that describes the error.
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. 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_escape_string()
returns, the contents of to will be a NUL-terminated
string.
See section 7.1 Literals: how to write strings and numbers.
Characters encoded are `NUL' (ASCII 0), `\n', `\r', `\' and `''.
char query[1000],*end;
end=strmov(query,"INSERT INTO test_table values(");
*end++='\"
end+=mysql_escape_string(query,"What's this");
*end++='\";
*end++=','
*end++='\"
end+=mysql_escape_string(query,"binary data: \0\r\n");
*end++='\"
*end++=')';
if (mysql_real_query(&mysql,query,(int) (end-query)))
{
fprintf(stderr, "Failed to insert row, Error: %s\n",
mysql_error(&mysql));
}
The strmov() function above is included in the mysqlclient
library and works like strcpy() but returns a pointer to the
terminating null of the first argument.
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 as a MYSQL_FIELD structure. Call
this function repeatedly to retrieve information about all columns in the
result set.
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().
When querying for the length of a BLOB without retrieving a
result, MySQL returns the default blob length, which is
8192, when doing a SELECT on the table. After you retrieve a result,
column_object->max_length contains the length of the biggest value
for this column in the specific query.
The 8192 size is chosen because MySQL doesn't know the maximum
length for the BLOB. This should be made configurable sometime.
The MYSQL_FIELD structure of the current column (NULL is
returned 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.
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, returns the
fieldnr column's field definition of a result set as a
MYSQL_FIELD structure. fieldnr begins at zero. You may use this
function to retrieve the definition for any arbitrary column.
The MYSQL_FIELD structure of 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. If you have
binary data, you must use this function to determine the size of the data.
If you copy the data, this length information is also useful for
optimization, because you can avoid calling strlen().
An array of unsigned long integers representing the size of each column (does
not include terminating NUL characters).
A C NULL pointer if there is an error.
NULL is returned if you call this before calling
mysql_fetch_row() or after retrieving all rows in the result.
MYSQL_ROW row;
unsigned int * 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. Returns NULL when
there are no more rows to retrieve. When used with
mysql_use_result(), data are dynamically retrieved from the server
and thus errors may occur in this situation.
A MYSQL_ROW structure for the next row, or NULL if there is an
error or there are no more rows to retrieve.
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]);
}
printf("\n");
}
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 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 the 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. 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
LOAD DATA INFILE ...
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
ALTER TABLE
Records: 3 Duplicates: 0 Warnings: 0
INSERT INTO TABLE ... VALUES (...),(...),(...)...
Records: 3 Duplicates: 0 Warnings: 0
A character string representing additional information about the query that
was most recently executed. A NULL pointer 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 the argument is a NULL pointer, the
function allocates, initializes and returns a new object, otherwise the
object is initialized and the address to the object is returned.
If a new object is allocated, mysql_close() will free this object.
An initialized MYSQL* handle or a NULL pointer if there wasn't
enough memory to allocate a new object.
In case of low memory a NULL is returned.
mysql_insert_id()
my_ulonglong mysql_insert_id(MYSQL *mysql)
Returns the ID generated for an AUTO_INCREMENT field. Use this
function after you have performed an INSERT query into a table that
contains an AUTO_INCREMENT field.
The value of the last AUTO_INCREMENT field updated.
None.
mysql_kill()
int mysql_kill(MYSQL *mysql, unsigned long pid)
Asks the server to kill the thread specified by pid.
Zero on success. Non-zero on failure.
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 argument.
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 there is a failure.
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 argument.
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
fields FROM table [LIKE wild].
You must free the result set with mysql_free_result().
A MYSQL_RES result set for success. NULL if there is a failure.
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.
You must free the result set with mysql_free_result().
A MYSQL_RES result set for success. NULL if there is a failure.
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 argument.
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 there is a failure.
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)
Returns the number of columns in a result set.
An unsigned integer representing the number of fields in a result set.
None.
mysql_num_rows()
int mysql_num_rows(MYSQL_RES *result)
Returns the number of rows in the result set.
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_ping()
int mysql_ping(MYSQL *mysql)
Checks if the connection to the server is working. If it has gone down, an automatic reconnection will be attempted.
This function can be used in clients that stay silent for a long while, to check (and reconnect) if the server has closed the connection.
Zero if the server is alive. Any other value indicates an error.
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.
mysql_query() cannot be used for queries that contain binary data.
(Binary data may contain the `\0' character, which would be
interpreted as the end of the query string.) For such cases, use
mysql_real_query() instead.
Zero if the query was successful. Non-zero if the query failed.
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,
uint port, const char *unix_socket, uint client_flag)
Attempts to establish a connection to a MySQL database engine running
on host. The value of host may be either a hostname or an IP
address. The user parameter contains the user's MySQL login
ID, and the passwd parameter contains the password for user.
NOTE: Do not attempt to encrypt passwd before calling
mysql_real_connect(). Password encryption is handled automatically by
the client API.
Note that before calling mysql_real_connect() you have to call
mysql_init() to get or initialize a MYSQL structure.
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 is NULL, the current user is assumed. Under Windows ODBC,
the current user must be specified explicitly. Under Unix, the current
login name is assumed.
passwd is NULL, only records 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.
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 it's the host parameter that
decides the type of the connection.
unix_socket is not NULL, the string specifies the
socket or named pipe that should be used. Note that it's the host
parameter that decides the type of the connection.
client_flag is usually 0, but can be set to a combination
of the following flags is very special circumstances:
| Flag name | Flag meaning |
CLIENT_FOUND_ROWS | Return the number of found rows, not the number of affected rows |
CLIENT_NO_SCHEMA | Don't allow the database.table.column
|
CLIENT_COMPRESS | Use compression protocol |
CLIENT_ODBC | The client is an ODBC client. This changes
mysqld to be more ODBC-friendly.
|
mysql_real_connect() must complete successfully before you can execute
any of the other API functions, with the exception of
mysql_get_client_info().
You may optionally specify the first argument of mysql_real_connect() to
be a NULL pointer. This will force the C API to allocate memory for
the connection structure automatically and to free it when you call
mysql_close(). The disadvantage of this approach is that you can't
retrieve an error message from mysql_real_connect() if the connection
fails.
If the first argument is not a NULL pointer, it should be the address
of an existing MYSQL structure.
A MYSQL* connection handle if the connection was successful. A C
NULL pointer if the connection was unsuccessful.
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;
NEED EXAMPLE HERE
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. You must use mysql_real_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.
Zero if the query was successful. Non-zero if the query failed.
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 access permissions tables. The connected user must have reload privileges.
Zero on success. Non-zero on failure.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_row_tell()
unsigned int 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().
The current offset of the row cursor.
None.
mysql_select_db()
int mysql_select_db(MYSQL *mysql, const char *db)
Instructs the current connection specified by mysql to use the
database specified by db as the default (current) database. In
subsequent queries, this database becomes the default for table references
that do not indicate an explicit database specifier.
mysql_select_db() fails unless the connected user can be authenticated
as having permission to use the database.
Zero on success. Non-zero on failure.
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 on success. Non-zero on failure.
CR_COMMANDS_OUT_OF_SYNC
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
CR_UNKNOWN_ERROR
mysql_stat()
char *mysql_stat(MYSQL *mysql)
Returns information similar to that provided by mysqladmin status as
a character string. 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 the
command failed.
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)
Reads the result of a query to the client, allocates a MYSQL_RES
structure, and places the results into this structure. You must call
mysql_store_result() or mysql_use_result() after every query
which successfully retrieves data.
An empty result set is returned if there are no rows returned.
You must call mysql_free_result() once you are done with the result
set.
A MYSQL_RES result structure with the results. NULL if
there was an error.
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.
The thread id of the current connection.
None.
mysql_use_result()
MYSQL_RES *mysql_use_result(MYSQL *mysql)
mysql_use_result() reads the result of a query directly from the
server without storing it in a temporary table or local buffer. This is
somewhat faster and uses much less memory than mysql_store_result().
In this case the client will only allocate memory for the current row and
a communication buffer of size max_allowed_packet.
On the other hand, you shouldn't use mysql_use_result() if you are
doing a lot of processing for each row at the client side, or if the output
is sent to a screen on which the user may type a ^S (stop scroll).
This would tie up the server and then other threads couldn't update the used
tables.
When using mysql_use_result(), you must execute
mysql_fetch_row() until you get back a NULL value,
otherwise the next query will get results from the previous 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_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.
You must call mysql_free_result() once you are done with the result
set.
A MYSQL_RES result structure. NULL if there was an error.
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.
INSERT, UPDATE or DELETE).
You can always check whether or not the statement should have produced a
non-empty result by calling mysql_num_fields(). If
mysql_num_fields() 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_num_fields() returns a
non-zero value, the statement should have produced a non-empty result.
You can also 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 affected
rows in 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_insert_id() returns the ID generated by the last
query that inserted a row into a table with an AUTO_INCREMENT index.
See section 18.4.49 How can I get the unique ID for the last inserted row?.
LOAD DATA INFILE ..., INSERT INTO
... SELECT ..., UPDATE) return additional info. The result is
returned by mysql_info(). 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 given ID with 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(MYSQL_RESULT) == 0 &&
mysql_insert_id(MYSQL) != 0)
used_id = mysql_insert_id(MYSQL);
The last ID that was generated 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).
When linking with the C API, you can get the following errors 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
This means that on your system you must include the math library
(-lm) at the end of the compile/link line.
The client is `almost' thread-safe. The biggest problem is that `net.c' (the file containing the subroutines that read from sockets) is 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 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_connect() are currently thread-safe.
To make mysql_connect() thread-safe, you must recompile the client 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 `libmysqld.a' library is now thread-safe.
Two threads can't use the same connection handle (returned by
mysql_connect()) at the same time, even if two threads can use
different MYSQL_RES pointers that were created with
mysql_store_result().
Since DBI/DBD now is the recommended Perl interface,
mysqlperl 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.hermetica.com/technologia/DBI/
For more information on Object Oriented Programming (OOP) as defined in Perl5, see the Perl OOP page:
http://language.perl.com/info/documentation.html
DBI interfacePortable DBI methods
connect | Establishes a connection to a database server |
prepare | Prepares a SQL statement for execution |
do | Prepares and executes a SQL statement |
disconnect | Disconnects from the database server |
quote | Quotes string or BLOB values to be inserted
|
execute | Executes prepared statements |
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
|
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 |
_CreateDB | Create a database |
_DropDB | Drop a database. THIS IS DANGEROUS. |
The Perl methods are described in more detail in the following sections:
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).
prepare($statement)
($sth) which you can use to invoke
the execute method. Example:
$sth = $dbh->prepare($statement)
or die "Can't prepare $statement: $dbh->errstr\n";
do($statement)
do method prepares and executes a SQL statement and returns the
number of rows affected. 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:
$rc = $dbh->do($statement)
or die "Can't execute $statement: $dbh- >errstr\n";
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;
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)
execute
execute method executes the prepared statement. For
non-SELECT statements, it returns the number of rows affected. 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;
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
do or non-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;
MySQL-specific methods
insertid
AUTO_INCREMENT feature of MySQL, the new
auto-incremented values will be stored here.
Example:
$new_id = $sth->{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};
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};
_CreateDB
CREATE DATABASE
statement using the do method instead, since do is a DBI
standard.
_DropDB
DROP DATABASE
statement using the do method instead, since do is a DBI
standard.
DBI/DBD information
You can use the perldoc command to get more information about
DBI.
perldoc DBI perldoc DBI::FAQ perldoc 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.hermetica.com/technologia/DBI/
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.
Insert pointers/descriptions for C++.
The http://www.mysql.com/Contrib,Contrib directory contains a Python interface written by Joseph Skinner.
http://www.binevolve.com/~tdarugar/tcl-sql/, TCL at binevolve The http://www.mysql.com/Contrib,Contrib directory contains a TCL interface that is based on msqltcl 1.50.
Go to the first, previous, next, last section, table of contents.