MySQL Reference Manual for version 3.22.14b-gamma.


18 MySQL client tools and API's

18.1 MySQL C API

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.

18.2 C API datatypes

MYSQL
This structure represents a handle to one database connection. It is used for almost all MySQL functions.
MYSQL_RES
This structure represents the result of a query that returns rows (SELECT or SHOW). The information returned from a query is called the result set in the remainder of this section.
MYSQL_ROW
This is a type-safe representation of one row of data. It is currently implemented as an array of byte strings.
MYSQL_FIELD
This structure contains information about a field, such as the field's name, type and size. Its members are described in more detail below. You may obtain the MYSQL_FIELD structures for each field by calling mysql_fetch_field() repeatedly.
MYSQL_FIELD_OFFSET
This is a type-safe representation of an offset into a MySQL field list. (Used by mysql_field_seek().) Offsets are field numbers within a row, beginning at zero.
my_ulonglong
The type used for the number of rows and for mysql_insert_id(). This type provides a range of 0 to 1.84e19.

The MYSQL_FIELD structure contains the following members:

char * name
The name of the field.
char * table
The name of the table containing this field if it isn't a calculated field. For calculated fields, the table value is a NULL pointer.
char * def
The default value of this field (set only if you use mysql_list_fields()).
enum enum_field_types type
The type of the field. The 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
The 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
The width of the field.
unsigned int max_length
The maximum width of the field for the selected set. If you used mysql_list_fields(), this contains the maximum length for the field.
unsigned int flags
Different bit-flags for the field These are the bits in 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
The example below illustrates a typical use of the flag 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 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?
The use of 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 number of decimals for numeric fields.

18.3 C API function overview

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.

18.4 C API function descriptions

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.

18.4.1 mysql_affected_rows()

my_ulonglong mysql_affected_rows(MYSQL *mysql)

18.4.1.1 Description

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.

18.4.1.2 Return values

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.

18.4.1.3 Errors

None.

18.4.1.4 Example

mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10");
printf("%d products updated",mysql_affected_rows(&mysql));

18.4.2 mysql_close()

void mysql_close(MYSQL *mysql)

18.4.2.1 Description

Closes a previously opened connection.

18.4.2.2 Return values

None.

18.4.2.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.3 mysql_connect()

MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)

18.4.3.1 Description

This function is deprecated. It is preferable to use mysql_real_connect() instead.

mysql_connect() attempts to establish a connection to a MySQL database engine running on host. 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.

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.

18.4.3.2 Return values

A MYSQL* connection handle if the connection was successful. A C NULL pointer if the connection was unsuccessful.

18.4.3.3 Errors

CR_CONN_HOST_ERROR
Failed to connect to the MySQL server.
CR_CONNECTION_ERROR
Failed to connect to the local MySQL server.
CR_IPSOCK_ERROR
Failed to create an IP socket.
CR_OUT_OF_MEMORY
Out of memory.
CR_SOCKET_CREATE_ERROR
Failed to create a Unix socket.
CR_UNKNOWN_HOST
Failed to find the IP address for the hostname.
CR_VERSION_ERROR
A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version. This can happen if you use a very old client library to connect to a new server that wasn't started with the --old-protocol option.
CR_NAMEDPIPEOPEN_ERROR;
Failed to create a named pipe on Win32.
CR_NAMEDPIPEWAIT_ERROR;
Failed to wait for a named pipe on Win32.
CR_NAMEDPIPESETSTATE_ERROR;
Failed to get a pipe handler on Win32.

18.4.3.4 Example

MYSQL mysql;

if(!mysql_connect(&mysql, "host", "username", "password"))
  fprintf(stderr, "Failed to connect to database: Error: %s\n",
          mysql_error(&mysql));

18.4.4 mysql_create_db()

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

18.4.4.1 Description

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.

18.4.4.2 Return values

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

18.4.4.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.4.4 Example

if(mysql_create_db(&mysql, "my_new_db"))
   fprintf(stderr, "Failed to create new database.  Error: %s\n",
           mysql_error(&mysql));

18.4.5 mysql_data_seek()

void mysql_data_seek(MYSQL_RES *res, unsigned int offset)

18.4.5.1 Description

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

18.4.5.2 Return values

None.

18.4.5.3 Errors

None.

18.4.6 mysql_debug()

void mysql_debug(char *debug)

18.4.6.1 Description

Does a DBUG_PUSH with the given string. mysql_debug() uses the Fred Fish debug library. To use this function, you must compile the client library to support debugging.

18.4.6.2 Return values

None.

18.4.6.3 Errors

None.

18.4.6.4 Example

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");

18.4.7 mysql_drop_db()

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

18.4.7.1 Description

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.

18.4.7.2 Return values

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

18.4.7.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.7.4 Example

if(mysql_drop_db(&mysql, "some_database"))
  fprintf(stderr, "Failed to drop the database: Error: %s\n",
          mysql_error(&mysql));

18.4.8 mysql_dump_debug_info()

int mysql_dump_debug_info(MYSQL *mysql)

18.4.8.1 Description

Instructs the server to dump some debug information to the log. The connected user must have process privileges for this to work.

18.4.8.2 Return values

Zero if the command was successful. Non-zero if the command failed.

18.4.8.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.9 mysql_eof()

my_bool mysql_eof(MYSQL_RES *result)

18.4.9.1 Description

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

18.4.9.2 Return values

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.

18.4.9.3 Errors

None.

18.4.9.4 Example

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
}

18.4.10 mysql_errno()

unsigned int mysql_errno(MYSQL *mysql)

18.4.10.1 Description

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'

18.4.10.2 Return values:

An error code value. Zero if no error has occurred.

18.4.10.3 Errors

None.

18.4.11 mysql_error()

char *mysql_error(MYSQL *mysql)

18.4.11.1 Description

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.

18.4.11.2 Return values

A character string that describes the error.

18.4.11.3 Errors

None.

18.4.12 mysql_escape_string()

unsigned int mysql_escape_string(char *to, const char *from, unsigned int length)

18.4.12.1 Description

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 `''.

18.4.12.2 Example

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.

18.4.12.3 Return values

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

18.4.12.4 Errors

None.

18.4.13 mysql_fetch_field()

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)

18.4.13.1 Description

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.

18.4.13.2 Return values

The MYSQL_FIELD structure of the current column (NULL is returned if no columns are left).

18.4.13.3 Errors

None.

18.4.13.4 Example

MYSQL_FIELD *field;

while((field = mysql_fetch_field(result)))
{
    printf("field name %s\n", field->name);
}

18.4.14 mysql_fetch_fields()

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES * result)

18.4.14.1 Description

Returns an array of all MYSQL_FIELD structures for a result. Each structure provides the field definition for one column of the result set.

18.4.14.2 Return values

An array of MYSQL_FIELD structures for all columns of a result set.

18.4.14.3 Errors

None.

18.4.14.4 Example

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *fields;

num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++)
{
   printf("Field %u is %s\n", i, fields[i].name);
}

18.4.15 mysql_fetch_field_direct()

MYSQL_FIELD * mysql_fetch_field_direct(MYSQL_RES * result, unsigned int fieldnr)

18.4.15.1 Description

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.

18.4.15.2 Return values

The MYSQL_FIELD structure of the specified column.

18.4.15.3 Errors

None.

18.4.15.4 Example

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *field;

num_fields = mysql_num_fields(result);
for(i = 0; i < num_fields; i++)
{
    field = mysql_fetch_field_direct(result, i);
    printf("Field %u is %s\n", i, field->name);
}

18.4.16 mysql_fetch_lengths()

unsigned long *mysql_fetch_lengths(MYSQL_RES *result)

18.4.16.1 Description

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

18.4.16.2 Return values

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.

18.4.16.3 Errors

NULL is returned if you call this before calling mysql_fetch_row() or after retrieving all rows in the result.

18.4.16.4 Example

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]);
    }
}

18.4.17 mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

18.4.17.1 Description

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.

18.4.17.2 Return values

A MYSQL_ROW structure for the next row, or NULL if there is an error or there are no more rows to retrieve.

18.4.17.3 Errors

CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.17.4 Example

MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;

num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
   unsigned long *lengths;
   lengths = mysql_fetch_lengths(result);
   for(i = 0; i < num_fields; i++)
   {
       printf("[%.*s] ", (int) lengths[i],row[i]);
   }
   printf("\n");
}

18.4.18 mysql_field_seek()

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)

18.4.18.1 Description

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.

18.4.18.2 Return values

The previous value of the field cursor.

18.4.18.3 Errors

None.

18.4.19 mysql_field_tell()

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)

18.4.19.1 Description

Returns the position of the field cursor used for the last mysql_fetch_field(). This value can be used as an argument to mysql_field_seek().

18.4.19.2 Return values

The current offset of the field cursor.

18.4.19.3 Errors

None.

18.4.20 mysql_free_result()

void mysql_free_result(MYSQL_RES *result)

18.4.20.1 Description

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

18.4.20.2 Return values

None.

18.4.20.3 Errors

None.

18.4.21 mysql_get_client_info()

char *mysql_get_client_info(void)

18.4.21.1 Description

Returns a string that represents the client library version.

18.4.21.2 Return values

A character string that represents the MySQL client library version.

18.4.21.3 Errors

None.

18.4.22 mysql_get_host_info()

char *mysql_get_host_info(MYSQL *mysql)

18.4.22.1 Description

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

18.4.22.2 Return values

A character string representing the server host name and the connection type.

18.4.22.3 Errors

None.

18.4.23 mysql_get_proto_info()

unsigned int mysql_get_proto_info(MYSQL *mysql)

18.4.23.1 Description

Returns the protocol version used by current connection.

18.4.23.2 Return values

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

18.4.23.3 Errors

None.

18.4.24 mysql_get_server_info()

char *mysql_get_server_info(MYSQL *mysql)

18.4.24.1 Description

Returns a string that represents the server version number.

18.4.24.2 Return values

A character string that represents the server version number.

18.4.24.3 Errors

None.

18.4.25 mysql_info()

char * mysql_info(MYSQL *mysql)

18.4.25.1 Description

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 ...
String format: Records: 100 Duplicates: 0 Warnings: 0
LOAD DATA INFILE ...
String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
ALTER TABLE
String format: Records: 3 Duplicates: 0 Warnings: 0
INSERT INTO TABLE ... VALUES (...),(...),(...)...
String format: Records: 3 Duplicates: 0 Warnings: 0

18.4.25.2 Return values

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.

18.4.25.3 Errors

None.

18.4.26 mysql_init()

MYSQL * mysql_init(MYSQL *mysql)

18.4.26.1 Description

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.

18.4.26.2 Return values

An initialized MYSQL* handle or a NULL pointer if there wasn't enough memory to allocate a new object.

18.4.26.3 Errors

In case of low memory a NULL is returned.

18.4.27 mysql_insert_id()

my_ulonglong mysql_insert_id(MYSQL *mysql)

18.4.27.1 Description

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.

18.4.27.2 Return values

The value of the last AUTO_INCREMENT field updated.

18.4.27.3 Errors

None.

18.4.28 mysql_kill()

int mysql_kill(MYSQL *mysql, unsigned long pid)

18.4.28.1 Description

Asks the server to kill the thread specified by pid.

18.4.28.2 Return values

Zero on success. Non-zero on failure.

18.4.28.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.29 mysql_list_dbs()

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

18.4.29.1 Description

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

18.4.29.2 Return values

A MYSQL_RES result set for success. NULL if there is a failure.

18.4.29.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.30 mysql_list_fields()

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)

18.4.30.1 Description

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

18.4.30.2 Return values

A MYSQL_RES result set for success. NULL if there is a failure.

18.4.30.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.31 mysql_list_processes()

MYSQL_RES *mysql_list_processes(MYSQL *mysql)

18.4.31.1 Description

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

18.4.31.2 Return values

A MYSQL_RES result set for success. NULL if there is a failure.

18.4.31.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.32 mysql_list_tables()

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

18.4.32.1 Description

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

18.4.32.2 Return values

A MYSQL_RES result set for success. NULL if there is a failure.

18.4.32.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.33 mysql_num_fields()

unsigned int mysql_num_fields(MYSQL_RES *result)

18.4.33.1 Description

Returns the number of columns in a result set.

18.4.33.2 Return values

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

18.4.33.3 Errors

None.

18.4.34 mysql_num_rows()

int mysql_num_rows(MYSQL_RES *result)

18.4.34.1 Description

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.

18.4.34.2 Return values

The number of rows in the result set.

18.4.34.3 Errors

None.

18.4.35 mysql_ping()

int mysql_ping(MYSQL *mysql)

18.4.35.1 Description

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.

18.4.35.2 Return values

Zero if the server is alive. Any other value indicates an error.

18.4.35.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.36 mysql_query()

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

18.4.36.1 Description

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.

18.4.36.2 Return values

Zero if the query was successful. Non-zero if the query failed.

18.4.36.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

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

18.4.37.1 Description

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.

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.

18.4.37.2 Return values

A MYSQL* connection handle if the connection was successful. A C NULL pointer if the connection was unsuccessful.

18.4.37.3 Errors

CR_CONN_HOST_ERROR
Failed to connect to the MySQL server.
CR_CONNECTION_ERROR
Failed to connect to the local MySQL server.
CR_IPSOCK_ERROR
Failed to create an IP socket.
CR_OUT_OF_MEMORY
Out of memory.
CR_SOCKET_CREATE_ERROR
Failed to create a Unix socket.
CR_UNKNOWN_HOST
Failed to find the IP address for the hostname.
CR_VERSION_ERROR
A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version. This can happen if you use a very old client library to connect to a new server that wasn't started with the --old-protocol option.
CR_NAMEDPIPEOPEN_ERROR;
Failed to create a named pipe on Win32.
CR_NAMEDPIPEWAIT_ERROR;
Failed to wait for a named pipe on Win32.
CR_NAMEDPIPESETSTATE_ERROR;
Failed to get a pipe handler on Win32.

18.4.37.4 Example

NEED EXAMPLE HERE

18.4.38 mysql_real_query()

int mysql_real_query(MYSQL *mysql, const char *query, unsigned int length)

18.4.38.1 Description

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.

18.4.38.2 Return values

Zero if the query was successful. Non-zero if the query failed.

18.4.38.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.39 mysql_reload()

int mysql_reload(MYSQL *mysql)

18.4.39.1 Description

Asks the MySQL server to reload the access permissions tables. The connected user must have reload privileges.

18.4.39.2 Return values

Zero on success. Non-zero on failure.

18.4.39.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.40 mysql_row_tell()

unsigned int mysql_row_tell(MYSQL_RES *result)

18.4.40.1 Description

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

18.4.40.2 Return values

The current offset of the row cursor.

18.4.40.3 Errors

None.

18.4.41 mysql_select_db()

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

18.4.41.1 Description

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.

18.4.41.2 Return values

Zero on success. Non-zero on failure.

18.4.41.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.42 mysql_shutdown()

int mysql_shutdown(MYSQL *mysql)

18.4.42.1 Description

Asks the database server to shutdown. The connected user must have shutdown privileges.

18.4.42.2 Return values

Zero on success. Non-zero on failure.

18.4.42.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.43 mysql_stat()

char *mysql_stat(MYSQL *mysql)

18.4.43.1 Description

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.

18.4.43.2 Return values

A character string describing the server status. NULL if the command failed.

18.4.43.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.44 mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql)

18.4.44.1 Description

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.

18.4.44.2 Return values

A MYSQL_RES result structure with the results. NULL if there was an error.

18.4.44.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.45 mysql_thread_id()

unsigned long mysql_thread_id(MYSQL * mysql)

18.4.45.1 Description

Returns the thread id of the current connection. This value can be used as an argument to mysql_kill() to kill the thread.

18.4.45.2 Return values

The thread id of the current connection.

18.4.45.3 Errors

None.

18.4.46 mysql_use_result()

MYSQL_RES *mysql_use_result(MYSQL *mysql)

18.4.46.1 Description

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.

18.4.46.2 Return values

A MYSQL_RES result structure. NULL if there was an error.

18.4.46.3 Errors

CR_COMMANDS_OUT_OF_SYNC
Commands were executed in an improper order.
CR_OUT_OF_MEMORY
Out of memory.
CR_SERVER_GONE_ERROR
The MySQL server has gone away.
CR_SERVER_LOST
The connection to the server was lost during the query.
CR_UNKNOWN_ERROR
An unknown error occurred.

18.4.47 Why is it that after 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:

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

18.4.48 What results can I get from a query?

In addition to the result set returned by a query, you can also get the following information:

18.4.49 How can I get the unique ID for the last inserted row?

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

18.4.50 Problems linking with the C API

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.

18.4.51 How to make a thread-safe client

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

18.5 MySQL Perl API's

Since DBI/DBD now is the recommended Perl interface, mysqlperl is not documented here.

18.5.1 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

18.5.1.1 The DBI interface

Portable 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)
Use the 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)
Prepares a SQL statement for execution by the database engine and returns a statement handle ($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)
The 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
The 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)
The 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
The 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
This method fetches the next row of data and returns it as an array of field values. Example:
while(@row = $sth->fetchrow_array) {
        print qw($row[0]\t$row[1]\t$row[2]\n);
}
fetchrow_arrayref
This method fetches the next row of data and returns it as a reference to an array of field values. Example:
while($row_ref = $sth->fetchrow_arrayref) {
        print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n);
}
fetchrow_hashref
This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example:
while($hash_ref = $sth->fetchrow_hashref) {
        print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\
                $hash_ref- > title}\n);
}
fetchall_arrayref
This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of arrays of references to each row. You access/print the data by using a nested loop. Example:
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
Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources it may be holding. Example:
$rc = $sth->finish;
rows
Returns the number of rows affected (updated, deleted, etc.) from the last command. This is usually used after a do or non-SELECT execute statement. Example:
$rv = $sth->rows;
NULLABLE
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that this column may contain NULL values. Example:
$null_possible = $sth->{NULLABLE};
NUM_OF_FIELDS
This attribute indicates the number of fields returned by a 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)
This method returns an array containing names of databases available to the MySQL server on the host 'localhost'. Example:
@dbs = DBI->data_sources("mysql");
ChopBlanks
This attribute determines whether the fetchrow_* methods will chop leading and trailing blanks from the returned values. Example:
$sth->{'ChopBlanks'} =1;

MySQL-specific methods

insertid
If you use the AUTO_INCREMENT feature of MySQL, the new auto-incremented values will be stored here. Example:
$new_id = $sth->{insertid};
is_blob
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a BLOB. Example:
$keys = $sth->{is_blob};
is_key
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a key. Example:
$keys = $sth->{is_key};
is_num
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column contains numeric values. Example:
$nums = $sth->{is_num};
is_pri_key
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a primary key. Example:
$pri_keys = $sth->{is_pri_key};
is_not_null
Returns a reference to an array of boolean values; for each element of the array, a value of FALSE indicates that this column may contain 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
Each of these methods returns a reference to an array of column sizes. The 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
Returns a reference to an array of column names. Example:
$names = $sth->{NAME};
table
Returns a reference to an array of table names. Example:
$tables = $sth->{table};
type
Returns a reference to an array of column types. Example:
$types = $sth->{type};
_CreateDB
Creates a database. This method is deprecated. It is preferable to issue a CREATE DATABASE statement using the do method instead, since do is a DBI standard.
_DropDB
Drops a database. THIS IS DANGEROUS. This method is deprecated. It is preferable to issue a DROP DATABASE statement using the do method instead, since do is a DBI standard.

18.5.1.2 More 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/

18.6 MySQL Java connectivity (JDBC)

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.

18.7 MySQL PHP API's

PHP documentation.

18.8 MySQL C++ API's

Insert pointers/descriptions for C++.

18.9 MySQL Python API's

The http://www.mysql.com/Contrib,Contrib directory contains a Python interface written by Joseph Skinner.

18.10 MySQL TCL API's

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.


This document was generated on 3 January 1999 using the texi2html translator version 1.52 (extended by davida@detron.se).