tecnocomputo.cotecnocomputo.co/almacen/root/informatica pdf/informatica_anonimo... · 24. apis de...

24. APIS DE MYSQLEste capítulo describe las APIs (Application Programming Interface, o Interfaz de Programación deAplicaciones) disponibles para MySQL, dónde se las puede obtener, y cómo utilizarlas. La API C es tratadaen mayor medida, ya que fue desarrollada por el equipo de MySQL, y es la base para la mayoría de lasotras APIs.

24.1. Utilidades para el desarrollo de programas MySQLEsta sección describe algunas utilidades que pueden ser de utilidad al desarrollar programas para MySQL.

msql2mysql Un script del shell que convierte programas mSQL a MySQL. No contempla todos los casos, peroproporciona un buen punto de partida para realizar la conversión.

mysql_config

Un script del shell que establece los valores de opciones necesarios al compilar programas MySQL.

24.1.1. msql2mysql — En un principio, la API C de MySQL se desarrolló con el fin de que fuera muy similar a la existente para elsistema de bases de datos mSQL. Debido a esto, los programas mSQL a menudo pueden convertirse demanera relativamente sencilla para ser empleados con MySQL, cambiando los nombres de las funcionesde la API C. La utilidad msql2mysql lleva a cabo la conversión de llamadas a funciones de la API C de mSQL hacia susequivalentes en MySQL. msql2mysql trabaja directamente sobre el fichero original, por lo que debehacerse una copia del mismo antes de intentar la conversión. Por ejemplo, msql2mysql puede emplearsede esta manera: shell> cp client-prog.c client-prog.c.origshell> msql2mysql client-prog.cclient-prog.c convertedThen examine client-prog.c and make any post-conversion revisions that may be necessary. msql2mysql uses the replace utility to make the function name substitutions. See Sección 8.12, “La utilidad replace de cambio de cadenas de caracteres” .

24.1.2. mysql_config — mysql_config provides you with useful information for compiling your MySQL client and connecting it toMySQL. mysql_config supports the following options:

--cflags Compiler flags to find include files and critical compiler flags and defines used when compiling thelibmysqlclient library.

--include Compiler options to find MySQL include files. (Note that normally you would use --cflags instead ofthis option.)

--libmysqld-libs, ---embedded Libraries and options required to link with the MySQL embedded server.

--libs Libraries and options required to link with the MySQL client library.

--libs_r Libraries and options required to link with the thread-safe MySQL client library.

--port

The default TCP/IP port number, defined when configuring MySQL. --socket

The default Unix socket file, defined when configuring MySQL. --version

Version number and version for the MySQL distribution. If you invoke mysql_config with no options, it displays a list of all options that it supports, and their values: shell> mysql_configUsage: /usr/local/mysql/bin/mysql_config [options]Options: --cflags [-I/usr/local/mysql/include/mysql -mcpu=pentiumpro] --include [-I/usr/local/mysql/include/mysql] --libs [-L/usr/local/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto] --libs_r [-L/usr/local/mysql/lib/mysql -lmysqlclient_r -lpthread -lz -lcrypt -lnsl -lm -lpthread] --socket [/tmp/mysql.sock] --port [3306] --version [4.0.16] --libmysqld-libs [-L/usr/local/mysql/lib/mysql -lmysqld -lpthread -lz -lcrypt -lnsl -lm -lpthread -lrt]You can use mysql_config within a command line to include the value that it displays for a particular option.For example, to compile a MySQL client program, use mysql_config as follows: shell> CFG=/usr/local/mysql/bin/mysql_configshell> sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"

When you use mysql_config this way, be sure to invoke it within backtick ('̀ ') characters. That tells the shellto execute it and substitute its output into the surrounding command.

24.2. La API C de MySQLThe C API code is distributed with MySQL. It is included in the mysqlclient library and allows C programsto access a database. Many of the clients in the MySQL source distribution are written in C. If you are looking for examples thatdemonstrate how to use the C API, take a look at these clients. You can find these in the clients directoryin the MySQL source distribution.

Most of the other client APIs (all except Connector/J) use the mysqlclient library to communicate with theMySQL server. This means that, for example, you can take advantage of many of the same environmentvariables that are used by other client programs, because they are referenced from the library. SeeCapítulo 8, Programas cliente y utilidades MySQL , for a list of these variables. The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16KB)is automatically increased up to the maximum size (the maximum is 16MB). Because buffer sizes areincreased only as demand warrants, simply increasing the default maximum limit does not in itself causemore resources to be used. This size check is mostly a check for erroneous queries and communicationpackets. The communication buffer must be large enough to contain a single SQL statement (for client-to-servertraffic) and one row of returned data (for server-to-client traffic). Each thread's communication buffer isdynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOBvalues that contain up to 16MB of data, you must have a communication buffer limit of at least 16MB (in

both server and client). The client's default maximum is 16MB, but the default maximum in the server is1MB. You can increase this by changing the value of the max_allowed_packet parameter when the serveris started. See Sección 7.5.2, “Afinar parámetros del servidor” .

The MySQL server shrinks each communication buffer to net_buffer_length bytes after each query. Forclients, the size of the buffer associated with a connection is not decreased until the connection is closed, atwhich time client memory is reclaimed. For programming with threads, see Sección 24.2.15, “Cómo hacer un cliente multihilo” . For creating astandalone application which includes the "server" and "client" in the same program (and does notcommunicate with an external MySQL server), see Sección 24.2.16, “libmysqld, la biblioteca del servidor MySQL incrustado (embedded)”.

24.2.1. Tipos de datos de la API C MYSQL

This structure represents a handle to one database connection. It is used for almost all MySQLfunctions. You should not try to make a copy of a MYSQL structure. There is no guarantee that sucha copy will be usable.

MYSQL_RES

This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE,EXPLAIN). The information returned from a query is called the result set in the remainder of thissection.

MYSQL_ROW

This is a type-safe representation of one row of data. It is currently implemented as an array ofcounted byte strings. (You cannot treat these as null-terminated strings if field values may containbinary data, because such values may contain null bytes internally.) Rows are obtained by callingmysql_fetch_row().

MYSQL_FIELD

This structure contains information about a field, such as the field's name, type, and size. Itsmembers are described in more detail here. You may obtain the MYSQL_FIELD structures for eachfield by calling mysql_fetch_field() repeatedly. Field values are not part of this structure; they arecontained in a MYSQL_ROW structure.

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_affected_rows(), mysql_num_rows(), andmysql_insert_id(). This type provides a range of 0 to 1.84e19.

On some systems, attempting to print a value of type my_ulonglong does not work. To print such avalue, convert it to unsigned long and use a %lu print format. Example:

printf ("Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));

The MYSQL_FIELD structure contains the members listed here: char * name

The name of the field, as a null-terminated string. char * table

The name of the table containing this field, if it isn't a calculated field. For calculated fields, the tablevalue is an empty string.

char * def The default value of this field, as a null-terminated string. This is set only if you usemysql_list_fields().

enum enum_field_types type

The type of the field. The type value may be one of the MYSQL_TYPE_ symbols shown in thefollowing table.

Type Value Type Description

MYSQL_TYPE_TINY TINYINT field

MYSQL_TYPE_SHORT

SMALLINT field

MYSQL_TYPE_LONG

INTEGER field

MYSQL_TYPE_INT24 MEDIUMINT field

MYSQL_TYPE_LONGLONG

BIGINT field

MYSQL_TYPE_DECIMAL

DECIMAL or NUMERIC field

MYSQL_TYPE_NEWDECIMAL

Precision math DECIMAL or NUMERIC field (MySQL5.0.3 and up)

MYSQL_TYPE_FLOAT

FLOAT field

MYSQL_TYPE_DOUBLE

DOUBLE or REAL field

MYSQL_TYPE_TIMESTAMP

TIMESTAMP field

MYSQL_TYPE_DATE DATE field

MYSQL_TYPE_TIME TIME field

MYSQL_TYPE_DATETIME

DATETIME field

MYSQL_TYPE_YEAR YEAR field

MYSQL_TYPE_STRING

CHAR field

MYSQL_TYPE_VAR_STRING

VARCHAR field

MYSQL_TYPE_BLOB

BLOB or TEXT field (use max_length to determinethe maximum length)

MYSQL_TYPE_SET SET field

MYSQL_TYPE_ENUM

ENUM field

MYSQL_TYPE_NULL NULL-type field

MYSQL_TYPE_CHAR

Deprecated; use MYSQL_TYPE_TINY instead

You can use the IS_NUM() macro to test whether a field has a numeric type. Pass the type value toIS_NUM() and it evaluates 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, as specified in the table definition. unsigned int max_length

The maximum width of the field for the result set (the length of the longest field value for the rowsactually in the result set). If you use mysql_store_result() or mysql_list_fields(), this contains themaximum length for the field. If you use mysql_use_result(), the value of this variable is zero.

unsigned int flags

Different bit-flags for the field. The flags value may have zero or more of the following bits set:

Flag Value Flag Description

NOT_NULL_FLAG Field can't be NULL

PRI_KEY_FLAG Field is part of a primary key

UNIQUE_KEY_FLAG

Field is part of a unique key

MULTIPLE_KEY_FLAG

Field is part of a non-unique key

UNSIGNED_FLAG Field has the UNSIGNEDattribute

ZEROFILL_FLAG Field has the ZEROFILLattribute

BINARY_FLAG Field has the BINARY attribute

AUTO_INCREMENT_FLAG

Field has theAUTO_INCREMENT attribute

ENUM_FLAG Field is an ENUM (deprecated)

SET_FLAG Field is a SET (deprecated)

BLOB_FLAG Field is a BLOB or TEXT(deprecated)

TIMESTAMP_FLAG

Field is a TIMESTAMP(deprecated)

Use of the BLOB_FLAG, ENUM_FLAG, SET_FLAG, and TIMESTAMP_FLAG flags is deprecatedbecause they indicate the type of a field rather than an attribute of its type. It is preferable to testfield->type against MYSQL_TYPE_BLOB, MYSQL_TYPE_ENUM, MYSQL_TYPE_SET, orMYSQL_TYPE_TIMESTAMP instead.

The following example illustrates a typical use of the flags value:

if (field->flags & NOT_NULL_FLAG) printf("Field can't be null\n");You may use the following convenience macros to determine the boolean status of the flags value:

Flag Status Description

IS_NOT_NULL(flags)

True if this field is defined as NOT NULL

IS_PRI_KEY(flags)

True if this field is a primary key

IS_BLOB(flags)

True if this field is a BLOB or TEXT (deprecated; testfield->type instead)

unsigned int decimals

The number of decimals for numeric fields.

24.2.2. Panorámica de funciones de la API CThe functions available in the C API are summarized here and described in greater detail in a later section.See Sección 24.2.3, “Descripción de funciones de la API C” .

Function Description

mysql_affected_rows()

Returns the number of rows changed/deleted/inserted by the last UPDATE,DELETE, or INSERT query.

mysql_change_user()

Changes user and database on an open connection.

mysql_charset_name()

Returns the name of the default character set for the connection.

mysql_close() Closes a server connection.

mysql_connect() Connects to a MySQL server. This function is deprecated; use mysql_real_connect() instead.

mysql_create_db() Creates a database. This function is deprecated; use the SQL statement CREATEDATABASE instead.

mysql_data_seek() Seeks to an arbitrary row number in a query result set.

mysql_debug() Does a DBUG_PUSH with the given string.

mysql_drop_db() Drops a database. This function is deprecated; use the SQL statement DROPDATABASE instead.

mysql_dump_debug_info()

Makes the server write debug information to the log.

mysql_eof() Determines whether the last row of a result set has been read. This function isdeprecated; mysql_errno() or mysql_error() may be used instead.

mysql_errno() Returns the error number for the most recently invoked MySQL function.

mysql_error() Returns the error message for the most recently invoked MySQL function.

mysql_escape_string()

Escapes special characters in a string for use in an SQL statement.

mysql_fetch_field() Returns the type of the next table field.

mysql_fetch_field_direct()

Returns the type of a table field, given a field number.

mysql_fetch_fields()

Returns an array of all field structures.

mysql_fetch_lengths()

Returns the lengths of all columns in the current row.

mysql_fetch_row() Fetches the next row from the result set.

mysql_field_seek() Puts the column cursor on a specified column.

mysql_field_count()

Returns the number of result columns for the most recent statement.

mysql_field_tell() Returns the position of the field cursor used for the last mysql_fetch_field().

mysql_free_result() Frees memory used by a result set.

mysql_get_client_info()

Returns client version information as a string.

mysql_get_client_version()

Returns client version information as an integer.

mysql_get_host_info()

Returns a string describing the connection.

mysql_get_server_version()

Returns version number of server as an integer (new in 4.1).

mysql_get_proto_info()

Returns the protocol version used by the connection.

mysql_get_server_info()

Returns the server version number.

mysql_info() Returns information about the most recently executed query.

mysql_init() Gets or initializes a MYSQL structure.

mysql_insert_id() Returns the ID generated for an AUTO_INCREMENT column by the previous query.

mysql_kill() Kills a given thread.

mysql_library_end()

Finalize MySQL C API library.

mysql_library_init() Initialize MySQL C API library.

mysql_list_dbs() Returns database names matching a simple regular expression.

mysql_list_fields() Returns field names matching a simple regular expression.

mysql_list_processes()

Returns a list of the current server threads.

mysql_list_tables() Returns table names matching a simple regular expression.

mysql_num_fields()

Returns the number of columns in a result set.

mysql_num_rows() Returns the number of rows in a result set.

mysql_options() Sets connect options for mysql_connect().

mysql_ping() Checks whether the connection to the server is working, reconnecting as necessary.

mysql_query() Executes an SQL query specified as a null-terminated string.

mysql_real_connect()

Connects to a MySQL server.

mysql_real_escape_string()

Escapes special characters in a string for use in an SQL statement, taking intoaccount the current charset of the connection.

mysql_real_query() Executes an SQL query specified as a counted string.

mysql_reload() Tells the server to reload the grant tables.

mysql_row_seek() Seeks to a row offset in a result set, using value returned from mysql_row_tell().

mysql_row_tell() Returns the row cursor position.

mysql_select_db() Selects a database.

mysql_server_end()

Finalize embedded server library.

mysql_server_init() Initialize embedded server library.

mysql_set_server_option()

Sets an option for the connection (like multi-statements).

mysql_sqlstate() Returns the SQLSTATE error code for the last error.

mysql_shutdown() Shuts down the database server.

mysql_stat() Returns the server status as a string.

mysql_store_result()

Retrieves a complete result set to the client.

mysql_thread_id() Returns the current thread ID.

mysql_thread_safe()

Returns 1 if the clients are compiled as thread-safe.

mysql_use_result() Initiates a row-by-row result set retrieval.

mysql_warning_count()

Returns the warning count for the previous SQL statement.

mysql_commit() Commits the transaction.

mysql_rollback() Rolls back the transaction.

mysql_autocommit()

Toggles autocommit mode on/off.

mysql_more_results()

Checks whether any more results exist.

mysql_next_result()

Returns/initiates the next result in multiple-statement executions.

Application programs should use this general outline for interacting with MySQL:

1. Initialize the MySQL library by calling mysql_library_init(). The library can be either themysqlclient C client library or the mysqld embedded server library, depending on whether theapplication was linked with the -libmysqlclient or -libmysqld flag.

2. Initialize a connection handler by calling mysql_init() and connect to the server by callingmysql_real_connect().

3. Issue SQL statements and process their results. (The following discussion provides moreinformation about how to do this.)

4. Close the connection to the MySQL server by calling mysql_close(). 5. End use of the MySQL library by calling mysql_library_end().

The purpose of calling mysql_library_init() and mysql_library_end() is to provide proper initialization andfinalization of the MySQL library. For applications that are linked with the client library, they provideimproved memory management. If you don't call mysql_library_end(), a block of memory remainsallocated. (This does not increase the amount of memory used by the application, but some memory leakdetectors will complain about it.) For applications that are linked with the embedded server, these calls startand stop the server.

mysql_library_init() and mysql_library_end() are available as of MySQL 4.1.10 and 5.0.3. These actuallyare #define symbols that make them equivalent to mysql_server_init() and mysql_server_end(), but thenames more clearly indicate that they should be called when beginning and ending use of a MySQL libraryno matter whether the application uses the mysqlclient or mysqld library. For older versions of MySQL,you can call mysql_server_init() and mysql_server_end() instead.

If you like, the call to mysql_library_init() may be omitted, because mysql_init() will invoke it automaticallyas necessary.

To connect to the server, call mysql_init() to initialize a connection handler, then call mysql_real_connect() with that handler (along with other information such as the hostname, username, and password). Uponconnection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1 inversions of the API strictly older than 5.0.3, of 0 in newer versions. A value of 1 for this flag indicates, in theevent that a query cannot be performed because of a lost connection, to try reconnecting to the serverbefore giving up. When you are done with the connection, call mysql_close() to terminate it.

While a connection is active, the client may send SQL queries to the server using mysql_query() ormysql_real_query(). The difference between the two is that mysql_query() expects the query to bespecified as a null-terminated string whereas mysql_real_query() expects a counted string. If the stringcontains binary data (which may include null bytes), you must use mysql_real_query(). For each non-SELECT query (for example, INSERT, UPDATE, DELETE), you can find out how many rowswere changed (affected) by calling mysql_affected_rows().

For SELECT queries, you retrieve the selected rows as a result set. (Note that some statements areSELECT-like in that they return rows. These include SHOW, DESCRIBE, and EXPLAIN. They should betreated the same way as SELECT statements.) There are two ways for a client to process result sets. One way is to retrieve the entire result set all at onceby calling mysql_store_result(). This function acquires from the server all the rows returned by the queryand stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval bycalling mysql_use_result(). This function initializes the retrieval, but does not actually get any rows fromthe server.

In both cases, you access rows by calling mysql_fetch_row(). With mysql_store_result(),mysql_fetch_row() accesses rows that have previously been fetched from the server. Withmysql_use_result(), mysql_fetch_row() actually retrieves the row from the server. Information about thesize of the data in each row is available by calling mysql_fetch_lengths(). After you are done with a result set, call mysql_free_result() to free the memory used for it. The two retrieval mechanisms are complementary. Client programs should choose the approach that ismost appropriate for their requirements. In practice, clients tend to use mysql_store_result() morecommonly.

An advantage of mysql_store_result() is that because the rows have all been fetched to the client, you notonly can access rows sequentially, you can move back and forth in the result set using mysql_data_seek()or mysql_row_seek() to change the current row position within the result set. You can also find out howmany rows there are by calling mysql_num_rows(). On the other hand, the memory requirements formysql_store_result() may be very high for large result sets and you are more likely to encounter out-of-memory conditions.

An advantage of mysql_use_result() is that the client requires less memory for the result set because itmaintains only one row at a time (and because there is less allocation overhead, mysql_use_result() canbe faster). Disadvantages are that you must process each row quickly to avoid tying up the server, youdon't have random access to rows within the result set (you can only access rows sequentially), and youdon't know how many rows are in the result set until you have retrieved them all. Furthermore, you mustretrieve all the rows even if you determine in mid-retrieval that you've found the information you werelooking for. The API makes it possible for clients to respond appropriately to queries (retrieving rows only as necessary)without knowing whether or not the query is a SELECT. You can do this by calling mysql_store_result()after each mysql_query() (or mysql_real_query()). If the result set call succeeds, the query was aSELECT and you can read the rows. If the result set call fails, call mysql_field_count() to determinewhether a result was actually to be expected. If mysql_field_count() returns zero, the query returned nodata (indicating that it was an INSERT, UPDATE, DELETE, etc.), and was not expected to return rows. Ifmysql_field_count() is non-zero, the query should have returned rows, but didn't. This indicates that thequery was a SELECT that failed. See the description for mysql_field_count() for an example of how thiscan be done.

Both mysql_store_result() and mysql_use_result() allow you to obtain information about the fields thatmake up the result set (the number of fields, their names and types, etc.). You can access field informationsequentially within the row by calling mysql_fetch_field() repeatedly, or by field number within the row bycalling mysql_fetch_field_direct(). The current field cursor position may be changed by callingmysql_field_seek(). Setting the field cursor affects subsequent calls to mysql_fetch_field(). You can alsoget information for fields all at once by calling mysql_fetch_fields(). For detecting and reporting errors, MySQL provides access to error information by means of themysql_errno() and mysql_error() functions. These return the error code or error message for the mostrecently invoked function that can succeed or fail, allowing you to determine when an error occurred andwhat it was.

24.2.3. Descripción de funciones de la API CIn the descriptions here, a parameter or return value of NULL means NULL in the sense of the Cprogramming language, not a MySQL NULL value.

Functions that return a value generally return a pointer or an integer. Unless specified otherwise, functionsreturning a pointer return a non-NULL value to indicate success or a NULL value to indicate an error, andfunctions returning an integer return zero to indicate success or non-zero to indicate an error. Note that“non-zero” means just that. Unless the function description says otherwise, do not test against a value otherthan zero: if (result) /* correct */ ... error ...

if (result < 0) /* incorrect */ ... error ...

if (result == -1) /* incorrect */ ... error ...

When a function returns an error, the Errors subsection of the function description lists the possible typesof errors. You can find out which of these occurred by calling mysql_errno(). A string representation of theerror may be obtained by calling mysql_error(). 24.2.3.1. mysql_affected_rows() my_ulonglong mysql_affected_rows(MYSQL *mysql) Description

Returns the number of rows changed by the last UPDATE, deleted by the last DELETE or inserted by thelast INSERT statement. May be called immediately after mysql_query() for UPDATE, DELETE, or INSERTstatements. For SELECT statements, mysql_affected_rows() works like mysql_num_rows(). Return Values An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that norecords were updated for an UPDATE statement, no rows matched the WHERE clause in the query or thatno query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query,mysql_affected_rows() was called prior to calling mysql_store_result(). Because mysql_affected_rows() returns an unsigned value, you can check for -1 by comparing the return value to (my_ulonglong)-1 (orto (my_ulonglong)~0, which is equivalent).

Errors None.

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

If you specify the flag CLIENT_FOUND_ROWS when connecting to mysqld, mysql_affected_rows()returns the number of rows matched by the WHERE statement for UPDATE statements.

Note that when you use a REPLACE command, mysql_affected_rows() returns 2 if the new row replacedan old row. This is because in this case one row was inserted after the duplicate was deleted.

If you use INSERT ... ON DUPLICATE KEY UPDATE to insert a row, mysql_affected_rows() returns 1 ifthe row is inserted as a new row and 2 if an existing row is updated.

24.2.3.2. mysql_change_user() my_bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char*db) Description

Changes the user and causes the database specified by db to become the default (current) database onthe connection specified by mysql. In subsequent queries, this database is the default for table referencesthat do not include an explicit database specifier. This function was introduced in MySQL 3.23.3.

mysql_change_user() fails if the connected user cannot be authenticated or doesn't have permission touse the database. In this case the user and database are not changed

The db parameter may be set to NULL if you don't want to have a default database.

Starting from MySQL 4.0.6 this command always performs a ROLLBACK of any active transactions, closesall temporary tables, unlocks all locked tables and resets the state as if one had done a new connect. Thishappens even if the user didn't change.

Return Values Zero for success. Non-zero if an error occurred.

Errors

The same that you can get from mysql_real_connect(). CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order. CR_SERVER_GONE_ERROR

The MySQL server has gone away. CR_SERVER_LOST

The connection to the server was lost during the query. CR_UNKNOWN_ERROR

An unknown error occurred. ER_UNKNOWN_COM_ERROR

The MySQL server doesn't implement this command (probably an old server). ER_ACCESS_DENIED_ERROR

The user or password was wrong. ER_BAD_DB_ERROR

The database didn't exist. ER_DBACCESS_DENIED_ERROR

The user did not have access rights to the database. ER_WRONG_DB_NAME

The database name was too long.

Example if (mysql_change_user(&mysql, "user", "password", "new_database")){ fprintf(stderr, "Failed to change user. Error: %s\n", mysql_error(&mysql));}

24.2.3.3. mysql_character_set_name() const char *mysql_character_set_name(MYSQL *mysql) Description Returns the default character set for the current connection.

Return Values The default character set

Errors None.

24.2.3.4. mysql_close() void mysql_close(MYSQL *mysql) Description

Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed toby mysql if the handle was allocated automatically by mysql_init() or mysql_connect(). Return Values None.

Errors None.

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

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

mysql_connect() attempts to establish a connection to a MySQL database engine running on host.mysql_connect() must complete successfully before you can execute any of the other API functions, withthe exception of mysql_get_client_info(). The meanings of the parameters are the same as for the corresponding parameters formysql_real_connect() with the difference that the connection parameter may be NULL. In this case the CAPI allocates memory for the connection structure automatically and frees it when you call mysql_close().The disadvantage of this approach is that you can't retrieve an error message if the connection fails. (To geterror information from mysql_errno() or mysql_error(), you must provide a valid MYSQL pointer.)

Return Values

Same as for mysql_real_connect(). Errors

Same as for mysql_real_connect(). 24.2.3.6. mysql_create_db() int mysql_create_db(MYSQL *mysql, const char *db) Description

Creates the database named by the db parameter.

This function is deprecated. It is preferable to use mysql_query() to issue an SQL CREATE DATABASEstatement instead.

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

Errors CR_COMMANDS_OUT_OF_SYNC


The MySQL server has gone away.

CR_SERVER_LOST


An unknown error occurred.

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

24.2.3.7. mysql_data_seek() void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset) Description

Seeks to an arbitrary row in a query result set. The offset value is a row number and should be in the rangefrom 0 to mysql_num_rows(result)-1. This function requires that the result set structure contains the entire result of the query, somysql_data_seek() may be used only in conjunction with mysql_store_result(), not withmysql_use_result(). Return Values None.

Errors None.

24.2.3.8. mysql_debug() void mysql_debug(const char *debug) Description

Does a DBUG_PUSH with the given string. mysql_debug() uses the Fred Fish debug library. To use thisfunction, you must compile the client library to support debugging. See Sección D.1, “Depurar un servidor MySQL”. See Sección D.2, “Depuración de un cliente MySQL” .

Return Values None.

Errors None.

Example The call shown here causes the client library to generate a trace file in /tmp/client.trace on the clientmachine: mysql_debug("d:t:O,/tmp/client.trace");

24.2.3.9. mysql_drop_db() int mysql_drop_db(MYSQL *mysql, const char *db) Description

Drops the database named by the db parameter.

This function is deprecated. It is preferable to use mysql_query() to issue an SQL DROP DATABASEstatement instead.

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






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

24.2.3.10. mysql_dump_debug_info() int mysql_dump_debug_info(MYSQL *mysql) Description Instructs the server to write some debug information to the log. For this to work, the connected user musthave the SUPER privilege.

Return Values Zero if the command was successful. Non-zero if an error occurred.






24.2.3.11. mysql_eof() my_bool mysql_eof(MYSQL_RES *result) Description

This function is deprecated. mysql_errno() or mysql_error() may be used instead.

mysql_eof() determines whether the last row of a result set has been read.

If you acquire a result set from a successful call to mysql_store_result(), the client receives the entire setin one operation. In this case, a NULL return from mysql_fetch_row() always means the end of the resultset has been reached and it is unnecessary to call mysql_eof(). When used with mysql_store_result(),mysql_eof() always returns true.

On the other hand, if you use mysql_use_result() to initiate a result set retrieval, the rows of the set areobtained from the server one by one as you call mysql_fetch_row() repeatedly. Because an error mayoccur on the connection during this process, a NULL return value from mysql_fetch_row() does notnecessarily mean the end of the result set was reached normally. In this case, you can use mysql_eof() todetermine what happened. mysql_eof() returns a non-zero value if the end of the result set was reachedand zero if an error occurred.

Historically, mysql_eof() predates the standard MySQL error functions mysql_errno() and mysql_error().Because those error functions provide the same information, their use is preferred over mysql_eof(), whichis deprecated. (In fact, they provide more information, because mysql_eof() returns only a boolean valuewhereas the error functions indicate a reason for the error when one occurs.)

Return Values Zero if no error occurred. Non-zero if the end of the result set has been reached.

Errors None.

Example

The following example shows how you might use mysql_eof(): mysql_query(&mysql,"SELECT * FROM some_table");result = mysql_use_result(&mysql);while((row = mysql_fetch_row(result))){ // do something with data}if(!mysql_eof(result)) // mysql_fetch_row() failed due to an error{ fprintf(stderr, "Error: %s\n", mysql_error(&mysql));}However, you can achieve the same effect with the standard MySQL error functions: mysql_query(&mysql,"SELECT * FROM some_table");result = mysql_use_result(&mysql);while((row = mysql_fetch_row(result))){ // do something with data}if(mysql_errno(&mysql)) // mysql_fetch_row() failed due to an error{ fprintf(stderr, "Error: %s\n", mysql_error(&mysql));}

24.2.3.12. mysql_errno() unsigned int mysql_errno(MYSQL *mysql) Description

For the connection specified by mysql, mysql_errno() returns the error code for the most recently invokedAPI function that can succeed or fail. A return value of zero means that no error occurred. Client errormessage numbers are listed in the MySQL errmsg.h header file. Server error message numbers are listedin mysqld_error.h. In the MySQL source distribution you can find a complete list of error messages and

error numbers in the file Docs/mysqld_error.txt. The server error codes also are listed at Capítulo 26, Manejo de errores en MySQL.

Note that some functions like mysql_fetch_row() don't set mysql_errno() if they succeed.

A rule of thumb is that all functions that have to ask the server for information reset mysql_errno() if theysucceed.

Return Values

An error code value for the last mysql_xxx() call, if it failed. zero means no error occurred.

Errors None.

24.2.3.13. mysql_error() const char *mysql_error(MYSQL *mysql) Description

For the connection specified by mysql, mysql_error() returns a null-terminated string containing the errormessage for the most recently invoked API function that failed. If a function didn't fail, the return value ofmysql_error() may be the previous error or an empty string to indicate no error.

A rule of thumb is that all functions that have to ask the server for information reset mysql_error() if theysucceed.

For functions that reset mysql_errno(), the following two tests are equivalent: if(mysql_errno(&mysql)){ // an error occurred}

if(mysql_error(&mysql)[0] != '\0'){ // an error occurred}The language of the client error messages may be changed by recompiling the MySQL client library.Currently you can choose error messages in several different languages. See Sección 5.9.2, “Escoger el idioma de los mensajes de error”.

Return Values A null-terminated character string that describes the error. An empty string if no error occurred.

Errors None.

24.2.3.14. mysql_escape_string() You should use mysql_real_escape_string() instead!

This function is identical to mysql_real_escape_string() except that mysql_real_escape_string() takes aconnection handler as its first argument and escapes the string according to the current character set.mysql_escape_string() does not take a connection argument and does not respect the current charsetsetting.

24.2.3.15. mysql_fetch_field() MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result) Description

Returns the definition of one column of a result set as a MYSQL_FIELD structure. Call this functionrepeatedly to retrieve information about all columns in the result set. mysql_fetch_field() returns NULLwhen no more fields are left.

mysql_fetch_field() is reset to return information about the first field each time you execute a newSELECT query. The field returned by mysql_fetch_field() is also affected by calls to mysql_field_seek(). If you've called mysql_query() to perform a SELECT on a table but have not called mysql_store_result(),MySQL returns the default blob length (8KB) if you call mysql_fetch_field() to ask for the length of a BLOBfield. (The 8KB size is chosen because MySQL doesn't know the maximum length for the BLOB. Thisshould be made configurable sometime.) Once you've retrieved the result set, field->max_length containsthe length of the largest value for this column in the specific query.

Return Values

The MYSQL_FIELD structure for the current column. NULL if no columns are left.

Errors None.

Example MYSQL_FIELD *field;

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

24.2.3.16. mysql_fetch_fields() MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result) Description

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

Return Values

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

Errors None.

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

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

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

Description

Given a field number fieldnr for a column within a result set, returns that column's field definition as aMYSQL_FIELD structure. You may use this function to retrieve the definition for an arbitrary column. Thevalue of fieldnr should be in the range from 0 to mysql_num_fields(result)-1.

Return Values

The MYSQL_FIELD structure for the specified column.

Errors None.

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

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

24.2.3.18. mysql_fetch_lengths() unsigned long *mysql_fetch_lengths(MYSQL_RES *result) Description Returns the lengths of the columns of the current row within a result set. If you plan to copy field values, thislength information is also useful for optimization, because you can avoid calling strlen(). In addition, if theresult set contains binary data, you must use this function to determine the size of the data, because strlen() returns incorrect results for any field containing null characters.

The length for empty columns and for columns containing NULL values is zero. To see how to distinguishthese two cases, see the description for mysql_fetch_row(). Return Values An array of unsigned long integers representing the size of each column (not including any terminating nullcharacters). NULL if an error occurred.

Errors

mysql_fetch_lengths() is valid only for the current row of the result set. It returns NULL if you call it beforecalling mysql_fetch_row() or after retrieving all rows in the result.

Example MYSQL_ROW row;unsigned long *lengths;unsigned int num_fields;unsigned int i;

row = mysql_fetch_row(result);if (row){

num_fields = mysql_num_fields(result); lengths = mysql_fetch_lengths(result); for(i = 0; i < num_fields; i++) { printf("Column %u is %lu bytes in length.\n", i, lengths[i]); }}

24.2.3.19. mysql_fetch_row() MYSQL_ROW mysql_fetch_row(MYSQL_RES *result) Description

Retrieves the next row of a result set. When used after mysql_store_result(), mysql_fetch_row() returnsNULL when there are no more rows to retrieve. When used after mysql_use_result(), mysql_fetch_row()returns NULL when there are no more rows to retrieve or if an error occurred.

The number of values in the row is given by mysql_num_fields(result). If row holds the return value froma call to mysql_fetch_row(), pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1]. NULL values in the row are indicated by NULL pointers.

The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths(). Empty fieldsand fields containing NULL both have length 0; you can distinguish these by checking the pointer for thefield value. If the pointer is NULL, the field is NULL; otherwise, the field is empty.

Return Values

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

Errors

Note that error is not reset between calls to mysql_fetch_row() CR_SERVER_LOST



Example MYSQL_ROW row;unsigned int num_fields;unsigned int i;

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

printf("\n");}

24.2.3.20. mysql_field_count() unsigned int mysql_field_count(MYSQL *mysql) If you are using a version of MySQL earlier than Version 3.22.24, you should use unsigned intmysql_num_fields(MYSQL *mysql) instead.

Description Returns the number of columns for the most recent query on the connection.

The normal use of this function is when mysql_store_result() returned NULL (and thus you have no resultset pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result()should have produced a non-empty result. This allows the client program to take proper action withoutknowing whether the query was a SELECT (or SELECT-like) statement. The example shown hereillustrates how this may be done.

See Sección 24.2.13.1, “¿Por qué mysql_store_result() a veces devuelve NULL después de que mysql_query() haya dado un resultado?” .

Return Values An unsigned integer representing the number of columns in a result set.

Errors None.

Example MYSQL_RES *result;unsigned int num_fields;unsigned int num_rows;

if (mysql_query(&mysql,query_string)){ // error}else // query succeeded, process any data returned by it{ result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result() returned nothing; should it have? { if(mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT)

num_rows = mysql_affected_rows(&mysql); } else // mysql_store_result() should have returned data { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } }}

An alternative is to replace the mysql_field_count(&mysql) call with mysql_errno(&mysql). In this case,you are checking directly for an error from mysql_store_result() rather than inferring from the value ofmysql_field_count() whether the statement was a SELECT.

24.2.3.21. mysql_field_seek() MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset) Description

Sets the field cursor to the given offset. The next call to mysql_fetch_field() retrieves the field definition ofthe column associated with that offset.

To seek to the beginning of a row, pass an offset value of zero.

Return Values The previous value of the field cursor.

Errors None.

24.2.3.22. mysql_field_tell() MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result) Description

Returns the position of the field cursor used for the last mysql_fetch_field(). This value can be used as anargument to mysql_field_seek(). Return Values The current offset of the field cursor.

Errors None.

24.2.3.23. mysql_free_result() void mysql_free_result(MYSQL_RES *result) Description

Frees the memory allocated for a result set by mysql_store_result(), mysql_use_result(),mysql_list_dbs(), etc. When you are done with a result set, you must free the memory it uses by callingmysql_free_result(). Do not attempt to access a result set after freeing it.

Return Values None.

Errors None.

24.2.3.24. mysql_get_character_set_info()

void mysql_get_character_set_info(MYSQL *mysql, MY_CHARSET_INFO *cs) Description This function provides information about the default client character set. The default character set may bechanged with the mysql_set_character_set() function. This function was added in MySQL 5.0.10.

Example if (!mysql_set_character_set_name(&mysql, "utf8")){ MY_CHARSET_INFO cs; mysql_get_character_set_info(&mysql, &cs); printf("character set information:\n"); printf("character set name: %s\n", cs->name); printf("collation name: %s\n", cs->csname); printf("comment: %s\n", cs->comment); printf("directory: %s\n", cs->dir); printf("multi byte character min. length: %s\n", cs->mbminlen); printf("multi byte character max. length: %s\n", cs->mbmaxlen);}

24.2.3.25. mysql_get_client_info() char *mysql_get_client_info(void) Description Returns a string that represents the client library version.

Return Values A character string that represents the MySQL client library version.

Errors None.

24.2.3.26. mysql_get_client_version() unsigned long mysql_get_client_version(void) Description

Returns an integer that represents the client library version. The value has the format XYYZZ where X is themajor version, YY is the release level, and ZZ is the version number within the release level. For example, avalue of 40102 represents a client library version of 4.1.2. This function was added in MySQL 4.0.16.

Return Values An integer that represents the MySQL client library version.

Errors None.

24.2.3.27. mysql_get_host_info() char *mysql_get_host_info(MYSQL *mysql) Description Returns a string describing the type of connection in use, including the server hostname.

Return Values A character string representing the server hostname and the connection type.

Errors None.

24.2.3.28. mysql_get_proto_info() unsigned int mysql_get_proto_info(MYSQL *mysql) Description Returns the protocol version used by current connection.

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

Errors None.

24.2.3.29. mysql_get_server_info() char *mysql_get_server_info(MYSQL *mysql) Description Returns a string that represents the server version number.

Return Values A character string that represents the server version number.

Errors None.

24.2.3.30. mysql_get_server_version() unsigned long mysql_get_server_version(MYSQL *mysql) Description Returns the version number of the server as an integer. This function was added in MySQL 4.1.0.

Return Values A number that represents the MySQL server version in this format: major_version*10000 + minor_version *100 + sub_versionFor example, 4.1.2 is returned as 40102. This function is useful in client programs for quickly determining whether some version-specific servercapability exists.

Errors None.

24.2.3.31. mysql_hex_string() unsigned long mysql_hex_string(char *to, const char *from, unsigned long length) Description This function is used to create a legal SQL string that you can use in a SQL statement. See Sección 9.1.1, “Cadenas de caracteres”.

The string in from is encoded to hexadecimal format, with each character encoded as two hexadecimaldigits. The result is placed in to and a terminating null byte is appended.

The string pointed to by from must be length bytes long. You must allocate the to buffer to be at leastlength*2+1 bytes long. When mysql_hex_string() returns, the contents of to is a null-terminated string.The return value is the length of the encoded string, not including the terminating null character.

The return value can be placed into an SQL statement using either 0xvalue or X'value' format. However,the return value does not include the 0x or X'...'. The caller must supply whichever of those is desired.

mysql_hex_string() was added in MySQL 4.0.23 and 4.1.8.

Example char query[1000],*end;

end = strmov(query,"INSERT INTO test_table values(");end = strmov(end,"0x");end += mysql_hex_string(end,"What's this",11);end = strmov(end,",0x");end += mysql_hex_string(end,"binary data: \0\r\n",16);*end++ = ')';

if (mysql_real_query(&mysql,query,(unsigned int) (end - query))){ fprintf(stderr, "Failed to insert row, Error: %s\n", mysql_error(&mysql));}

The strmov() function used in the example is included in the mysqlclient library and works like strcpy() butreturns a pointer to the terminating null of the first parameter.

Return Values

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

Errors None.

24.2.3.32. mysql_info() char *mysql_info(MYSQL *mysql) Description Retrieves a string providing information about the most recently executed query, but only for the statementslisted here. For other statements, mysql_info() returns NULL. The format of the string varies depending onthe type of query, as described here. The numbers are illustrative only; the string contains valuesappropriate for the query.

INSERT INTO ... SELECT ... String format: Records: 100 Duplicates: 0 Warnings: 0

INSERT INTO ... VALUES (...),(...),(...)... String format: Records: 3 Duplicates: 0 Warnings: 0

LOAD DATA INFILE ... String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

ALTER TABLE

String format: Records: 3 Duplicates: 0 Warnings: 0

UPDATE

String format: Rows matched: 40 Changed: 40 Warnings: 0

Note that mysql_info() returns a non-NULL value for INSERT ... VALUES only for the multiple-row form ofthe statement (that is, only if multiple value lists are specified).

Return Values

A character string representing additional information about the most recently executed query. NULL if noinformation is available for the query.

Errors None.

24.2.3.33. mysql_init() MYSQL *mysql_init(MYSQL *mysql) Description

Allocates or initializes a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, thefunction allocates, initializes, and returns a new object. Otherwise, the object is initialized and the addressof the object is returned. If mysql_init() allocates a new object, it is freed when mysql_close() is called toclose the connection.

Return Values

An initialized MYSQL* handle. NULL if there was insufficient memory to allocate a new object.

Errors

In case of insufficient memory, NULL is returned.

24.2.3.34. mysql_insert_id() my_ulonglong mysql_insert_id(MYSQL *mysql) Description

Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATEstatement. Use this function after you have performed an INSERT statement into a table that contains anAUTO_INCREMENT field.

More precisely, mysql_insert_id() is updated under these conditions: INSERT statements that store a value into an AUTO_INCREMENT column. This is true whether the

value is automatically generated by storing the special values NULL or 0 into the column, or is anexplicit non-special value.

In the case of a multiple-row INSERT statement, mysql_insert_id() returns the first automaticallygenerated AUTO_INCREMENT value; if no such value is generated, it returns the last last explicitvalue inserted into the AUTO_INCREMENT column.

INSERT statements that generate an AUTO_INCREMENT value by inserting LAST_INSERT_ID(expr) into any column.

INSERT statements that generate an AUTO_INCREMENT value by updating any column toLAST_INSERT_ID(expr).

The value of mysql_insert_id() is not affected by statements such as SELECT that return a resultset.

If the previous statement returned an error, the value of mysql_insert_id() is undefined.

Note that mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENTvalue. If you need to save the value for later, be sure to call mysql_insert_id() immediately after thestatement that generates the value.

The value of mysql_insert_id() is affected only by statements issued within the current client connection. Itis not affected by statements issued by other clients.

See Sección 12.9.3, “Funciones de información” .

Also note that the value of the SQL LAST_INSERT_ID() function always contains the most recentlygenerated AUTO_INCREMENT value, and is not reset between statements because the value of thatfunction is maintained in the server. Another difference is that LAST_INSERT_ID() is not updated if you setan AUTO_INCREMENT column to a specific non-special value.

The reason for the difference between LAST_INSERT_ID() and mysql_insert_id() is thatLAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide a little moreexact information of what happens to the AUTO_INCREMENT column.

Return Values Described in the preceding discussion.

Errors None.

24.2.3.35. mysql_kill() int mysql_kill(MYSQL *mysql, unsigned long pid) Description

Asks the server to kill the thread specified by pid.


Errors

24.2.3.36. mysql_library_init() int mysql_library_init(int argc, char **argv, char **groups) Description

This is a synonym for the mysql_server_init() function. It was added in MySQL 4.1.10 and 5.0.3. See Sección 24.2.2, “Panorámica de funciones de la API C” for usage information.

CR_COMMANDS_OUT_OF_SYNC





24.2.3.37. mysql_library_end() void mysql_library_end(void) Description

This is a synonym for the mysql_server_end() function. It was added in MySQL 4.1.10 and 5.0.3. See Sección 24.2.2, “Panorámica de funciones de la API C” for usage information.

24.2.3.38. mysql_list_dbs() MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild) Description

Returns a result set consisting of database names on the server that match the simple regular expressionspecified by the wild parameter. wild may contain the wildcard characters '%' or '_', or may be a NULLpointer to match all databases. Calling mysql_list_dbs() is similar to executing the query SHOWdatabases [LIKE wild]. You must free the result set with mysql_free_result(). Return Values

A MYSQL_RES result set for success. NULL if an error occurred.


Commands were executed in an improper order. CR_OUT_OF_MEMORY

Out of memory. CR_SERVER_GONE_ERROR




24.2.3.39. mysql_list_fields() MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild) Description Returns a result set consisting of field names in the given table that match the simple regular expressionspecified by the wild parameter. wild may contain the wildcard characters '%' or '_', or may be a NULLpointer to match all fields. Calling mysql_list_fields() is similar to executing the query SHOW COLUMNSFROM tbl_name [LIKE wild]. Note that it's recommended that you use SHOW COLUMNS FROM tbl_name instead of mysql_list_fields(). You must free the result set with mysql_free_result(). Return Values







24.2.3.40. mysql_list_processes() MYSQL_RES *mysql_list_processes(MYSQL *mysql) Description

Returns a result set describing the current server threads. This is the same kind of information as thatreported by mysqladmin processlist or a SHOW PROCESSLIST query.

You must free the result set with mysql_free_result(). Return Values







24.2.3.41. mysql_list_tables() MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild) Description Returns a result set consisting of table names in the current database that match the simple regularexpression specified by the wild parameter. wild may contain the wildcard characters '%' or '_', or may be aNULL pointer to match all tables. Calling mysql_list_tables() is similar to executing the query SHOWtables [LIKE wild]. You must free the result set with mysql_free_result(). Return Values







24.2.3.42. mysql_num_fields() unsigned int mysql_num_fields(MYSQL_RES *result) Or:

unsigned int mysql_num_fields(MYSQL *mysql) The second form doesn't work on MySQL 3.22.24 or newer. To pass a MYSQL* argument, you must useunsigned int mysql_field_count(MYSQL *mysql) instead.

Description Returns the number of columns in a result set.

Note that you can get the number of columns either from a pointer to a result set or to a connection handle.You would use the connection handle if mysql_store_result() or mysql_use_result() returned NULL (andthus you have no result set pointer). In this case, you can call mysql_field_count() to determine whethermysql_store_result() should have produced a non-empty result. This allows the client program to takeproper action without knowing whether or not the query was a SELECT (or SELECT-like) statement. Theexample shown here illustrates how this may be done.



Errors None.

Example MYSQL_RES *result;unsigned int num_fields;unsigned int num_rows;

if (mysql_query(&mysql,query_string)){ // error}else // query succeeded, process any data returned by it{ result = mysql_store_result(&mysql); if (result) // there are rows { num_fields = mysql_num_fields(result); // retrieve rows, then call mysql_free_result(result) } else // mysql_store_result() returned nothing; should it have? { if (mysql_errno(&mysql)) { fprintf(stderr, "Error: %s\n", mysql_error(&mysql)); } else if (mysql_field_count(&mysql) == 0) { // query does not return data // (it was not a SELECT) num_rows = mysql_affected_rows(&mysql); } }

}

An alternative (if you know that your query should have returned a result set) is to replace the mysql_errno(&mysql) call with a check whether mysql_field_count(&mysql) is = 0. This happens only if somethingwent wrong.

24.2.3.43. mysql_num_rows() my_ulonglong mysql_num_rows(MYSQL_RES *result) Description Returns the number of rows in the result set.

The use of mysql_num_rows() depends on whether you use mysql_store_result() or mysql_use_result() to return the result set. If you use mysql_store_result(), mysql_num_rows() may be calledimmediately. If you use mysql_use_result(), mysql_num_rows() does not return the correct value until allthe rows in the result set have been retrieved.

Return Values The number of rows in the result set.

Errors None.

24.2.3.44. mysql_options() int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg) Description Can be used to set extra connect options and affect behavior for a connection. This function may be calledmultiple times to set several options.

mysql_options() should be called after mysql_init() and before mysql_connect() or mysql_real_connect(). The option argument is the option that you want to set; the arg argument is the value for the option. If theoption is an integer, then arg should point to the value of the integer. Possible option values:

Option Argument Type

Function

MYSQL_INIT_COMMAND char * Command to execute when connecting to the MySQLserver. Will automatically be re-executed whenreconnecting.

MYSQL_OPT_COMPRESS Notused

Use the compressed client/server protocol.

MYSQL_OPT_CONNECT_TIMEOUT

unsigned int *

Connect timeout in seconds.

MYSQL_OPT_GUESS_CONNECTION

Notused

For an application linked against libmysqld, this allows thelibrary to guess whether to use the embedded server or aremote server. “Guess” means that if the hostname is setand is not localhost, it uses a remote server. This behavioris the default.MYSQL_OPT_USE_EMBEDDED_CONNECTION andMYSQL_OPT_USE_REMOTE_CONNECTION can beused to override it. This option is ignored for applicationslinked against libmysqlclient.

MYSQL_OPT_LOCAL_INFILE optionalpointerto uint

If no pointer is given or if pointer points to an unsignedint != 0 the command LOAD LOCAL INFILE is enabled.

MYSQL_OPT_NAMED_PIPE Notused

Use named pipes to connect to a MySQL server on NT.

MYSQL_OPT_PROTOCOL unsigned int *

Type of protocol to use. Should be one of the enum valuesof mysql_protocol_type defined in mysql.h. New in 4.1.0.

MYSQL_OPT_READ_TIMEOUT unsigned int *

Timeout for reads from server (works currently only onWindows on TCP/IP connections). New in 4.1.1.

MYSQL_OPT_SET_CLIENT_IP char * For an application linked against linked against libmysqld(with libmysqld compiled with authentication support), thismeans that the user is considered to have connected fromthe specified IP address (specified as a string) forauthentication purposes. This option is ignored forapplications linked against libmysqlclient.

MYSQL_OPT_USE_EMBEDDED_CONNECTION

Notused

For an application linked against libmysqld, this forces theuse of the embedded server for the connection. This optionis ignored for applications linked against libmysqlclient.

MYSQL_OPT_USE_REMOTE_CONNECTION

Notused

For an application linked against libmysqld, this forces theuse of a remote server for the connection. This option isignored for applications linked against libmysqlclient.

MYSQL_OPT_USE_RESULT Notused

This option is new in 4.1.1, but is unused.

MYSQL_OPT_WRITE_TIMEOUT unsigned int *

Timeout for writes to server (works currently only onWindows on TCP/IP connections). New in 4.1.1.

MYSQL_READ_DEFAULT_FILE char * Read options from the named option file instead of frommy.cnf.

MYSQL_READ_DEFAULT_GROUP

char * Read options from the named group from my.cnf or the filespecified with MYSQL_READ_DEFAULT_FILE.

MYSQL_REPORT_DATA_TRUNCATION

my_bool *

Enable or disable reporting of data truncation errors forprepared statements via MYSQL_BIND.error. (Default:disabled) New in 5.0.3.

MYSQL_SECURE_AUTH my_bool*

Whether to connect to a server that does not support thenew 4.1.1 password hashing. New in 4.1.1.

MYSQL_SET_CHARSET_DIR char* The pathname to the directory that contains character setdefinition files.

MYSQL_SET_CHARSET_NAME char* The name of the character set to use as the defaultcharacter set.

MYSQL_SHARED_MEMORY_BASE_NAME

char* Named of shared memory object for communication toserver. Should be same as the option -shared-memory-base-name used for the mysqld server you want's toconnect to. New in 4.1.0.

Note that the client group is always read if you use MYSQL_READ_DEFAULT_FILE orMYSQL_READ_DEFAULT_GROUP. The specified group in the option file may contain the following options:

Option Description

connect-timeout Connect timeout in seconds. On Linux this timeout is also used for waitingfor the first answer from the server.

compress Use the compressed client/server protocol.

database Connect to this database if no database was specified in the connectcommand.

debug Debug options.

disable-local-infile Disable use of LOAD DATA LOCAL.

host Default hostname.

init-command Command to execute when connecting to MySQL server. Will automaticallybe re-executed when reconnecting.

interactive-timeout Same as specifying CLIENT_INTERACTIVE to mysql_real_connect(). SeeSección 24.2.3.47, “ mysql_real_connect() ” .

local-infile[=(0|1)] If no argument or argument != 0 then enable use of LOAD DATA LOCAL.

max_allowed_packet Max size of packet client can read from server.

multi-results Allow multiple result sets from multiple-statement executions or storedprocedures. New in 4.1.1.

multi-statements Allow the client to send multiple statements in a single string (separated by';'). New in 4.1.9.

password Default password.

pipe Use named pipes to connect to a MySQL server on NT.

protocol={TCP | SOCKET |PIPE | MEMORY}

The protocol to use when connecting to server (New in 4.1)

port Default port number.

return-found-rows Tell mysql_info() to return found rows instead of updated rows when usingUPDATE.

shared-memory-base-name=name

Shared memory name to use to connect to server (default is "MYSQL").New in MySQL 4.1.

socket Default socket file.

user Default user.

Note that timeout has been replaced by connect-timeout, but timeout still works for a while. For more information about option files, see Sección 4.3.2, “Usar ficheros de opciones” .

Return Values

Zero for success. Non-zero if you used an unknown option.

Example MYSQL mysql;

mysql_init(&mysql);mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc");if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)){ fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));}This code requests the client to use the compressed client/server protocol and read the additional optionsfrom the odbc section in the my.cnf file.

24.2.3.45. mysql_ping() int mysql_ping(MYSQL *mysql) Description Checks whether the connection to the server is working. If the connection has gone down, an automaticreconnection is attempted. This function can be used by clients that remain idle for a long while, to check whether the server hasclosed the connection and reconnect if necessary.

Return Values Zero if the connection to the server is alive. Non-zero if an error occurred. A non-zero return does notindicate whether the MySQL server itself is down; the connection might be broken for other reasons such asnetwork problems.



The MySQL server has gone away. CR_UNKNOWN_ERROR


24.2.3.46. mysql_query() int mysql_query(MYSQL *mysql, const char *query) Description

Executes the SQL query pointed to by the null-terminated string query. Normally, the string must consist ofa single SQL statement and you should not add a terminating semicolon (';') or \g to the statement. Ifmultiple-statement execution has been enabled, the string can contain several statements separated bysemicolons. See Sección 24.2.9, “Tratamiento por parte de la API C de la ejecución de múltiples consultas”.

mysql_query() cannot be used for queries that contain binary data; you should use mysql_real_query()instead. (Binary data may contain the '\0' character, which mysql_query() interprets as the end of the querystring.)

If you want to know whether the query should return a result set, you can use mysql_field_count() tocheck for this. See Sección 24.2.3.20, “ mysql_field_count() ” . Return Values Zero if the query was successful. Non-zero if an error occurred.






24.2.3.47. mysql_real_connect() MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char*passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag) Description

mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host.mysql_real_connect() must complete successfully before you can execute any other API functions thatrequire a valid MYSQL connection handle structure. The parameters are specified as follows:

The first parameter should be the address of an existing MYSQL structure. Before callingmysql_real_connect() you must call mysql_init() to initialize the MYSQL structure. You canchange a lot of connect options with the mysql_options() call. See Sección 24.2.3.44, “ mysql_options() ” .

The value of host may be either a hostname or an IP address. If host is NULL or the string"localhost", a connection to the local host is assumed. If the OS supports sockets (Unix) or namedpipes (Windows), they are used instead of TCP/IP to connect to the server.

The user parameter contains the user's MySQL login ID. If user is NULL or the empty string "", thecurrent user is assumed. Under Unix, this is the current login name. Under Windows ODBC, thecurrent username must be specified explicitly. See Sección 25.1.3.2, “Configuring a Connector/ODBC DSN on Windows”.

The passwd parameter contains the password for user. If passwd is NULL, only entries in the usertable for the user that have a blank (empty) password field are checked for a match. This allows thedatabase administrator to set up the MySQL privilege system in such a way that users get differentprivileges depending on whether or not they have specified a password. Note: Do not attempt to encrypt the password before calling mysql_real_connect(); passwordencryption is handled automatically by the client API.

db is the database name. If db is not NULL, the connection sets the default database to this value.

If port is not 0, the value is used as the port number for the TCP/IP connection. Note that the hostparameter determines the type of the connection.

If unix_socket is not NULL, the string specifies the socket or named pipe that should be used. Notethat the host parameter determines the type of the connection.

The value of client_flag is usually 0, but can be set to a combination of the following flags in veryspecial circumstances:

Flag Name Flag Description

CLIENT_COMPRESS Use compression protocol.

CLIENT_FOUND_ROWS

Return the number of found (matched) rows, not the number of affectedrows.

CLIENT_IGNORE_SPACE

Allow spaces after function names. Makes all functions names reservedwords.

CLIENT_INTERACTIVE

Allow interactive_timeout seconds (instead of wait_timeout seconds) ofinactivity before closing the connection. The client's session wait_timeoutvariable is set to the value of the session interactive_timeout variable.

CLIENT_LOCAL_FILES

Enable LOAD DATA LOCAL handling.

CLIENT_MULTI_STATEMENTS

Tell the server that the client may send multiple statements in a singlestring (separated by ';'). If this flag is not set, multiple-statement executionis disabled. New in 4.1.

CLIENT_MULTI_RESULTS

Tell the server that the client can handle multiple result sets from multiple-statement executions or stored procedures. This is automatically set ifCLIENT_MULTI_STATEMENTS is set. New in 4.1.

CLIENT_NO_SCHEMA

Don't allow the db_name.tbl_name.col_name syntax. This is for ODBC. Itcauses the parser to generate an error if you use that syntax, which isuseful for trapping bugs in some ODBC programs.

CLIENT_ODBC The client is an ODBC client. This changes mysqld to be more ODBC-friendly.

CLIENT_SSL Use SSL (encrypted protocol). This option should not be set by applicationprograms; it is set internally in the client library.

Return Values

A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful.For a successful connection, the return value is the same as the value of the first parameter.

Errors CR_CONN_HOST_ERROR

Failed to connect to the MySQL server. CR_CONNECTION_ERROR

Failed to connect to the local MySQL server. CR_IPSOCK_ERROR

Failed to create an IP socket. CR_OUT_OF_MEMORY

Out of memory. CR_SOCKET_CREATE_ERROR

Failed to create a Unix socket. CR_UNKNOWN_HOST

Failed to find the IP address for the hostname.

CR_VERSION_ERROR

A protocol mismatch resulted from attempting to connect to a server with a client library that uses adifferent protocol version. This can happen if you use a very old client library to connect to a newserver that wasn't started with the --old-protocol option.

CR_NAMEDPIPEOPEN_ERROR

Failed to create a named pipe on Windows. CR_NAMEDPIPEWAIT_ERROR

Failed to wait for a named pipe on Windows. CR_NAMEDPIPESETSTATE_ERROR

Failed to get a pipe handler on Windows. CR_SERVER_LOST

If connect_timeout > 0 and it took longer than connect_timeout seconds to connect to the serveror if the server died while executing the init-command.


mysql_init(&mysql);mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name");if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)){ fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));}

By using mysql_options() the MySQL library reads the [client] and [your_prog_name] sections in themy.cnf file which ensures that your program works, even if someone has set up MySQL in some non-standard way.

Note that upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure)to a value of 1 in versions of the API strictly older than 5.0.3, of 0 in newer versions. A value of 1 for this flagindicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting tothe server before giving up.

24.2.3.48. mysql_real_escape_string() unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned longlength) Note that mysql must be a valid, open connection. This is needed because the escaping depends on thecharacter set in use by the server.

Description This function is used to create a legal SQL string that you can use in a SQL statement. See Sección 9.1.1, “Cadenas de caracteres”.

The string in from is encoded to an escaped SQL string, taking into account the current character set of theconnection. The result is placed in to and a terminating null byte is appended. Characters encoded are NUL(ASCII 0), '\n', '\r', '\', ''', '"', and Control-Z (see Sección 9.1, “Valores literales” ). (Strictly speaking, MySQLrequires only that backslash and the quote character used to quote the string in the query be escaped. Thisfunction quotes the other characters to make them easier to read in log files.)

The string pointed to by from must be length bytes long. You must allocate the to buffer to be at leastlength*2+1 bytes long. (In the worst case, each character may need to be encoded as using two bytes, and

you need room for the terminating null byte.) When mysql_real_escape_string() returns, the contents ofto is a null-terminated string. The return value is the length of the encoded string, not including theterminating null character.

If you need to change the character set of the connection, you should use the mysql_set_character_set()function rather than executing a SET NAMES (or SET CHARACTER SET) statement.mysql_set_character_set() works like SET NAMES but also affects the character set used bymysql_real_escape_string(), which SET NAMES does not.

Example char query[1000],*end;

end = strmov(query,"INSERT INTO test_table values(");*end++ = '\'';end += mysql_real_escape_string(&mysql, end,"What's this",11);*end++ = '\'';*end++ = ',';*end++ = '\'';end += mysql_real_escape_string(&mysql, end,"binary data: \0\r\n",16);*end++ = '\'';*end++ = ')';

if (mysql_real_query(&mysql,query,(unsigned int) (end - query))){ fprintf(stderr, "Failed to insert row, Error: %s\n", mysql_error(&mysql));}

The strmov() function used in the example is included in the mysqlclient library and works like strcpy() butreturns a pointer to the terminating null of the first parameter.

Return Values

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

Errors None.

24.2.3.49. mysql_real_query() int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length) Description

Executes the SQL query pointed to by query, which should be a string length bytes long. Normally, thestring must consist of a single SQL statement and you should not add a terminating semicolon (';') or \g tothe statement. If multiple-statement execution has been enabled, the string can contain several statementsseparated by semicolons. See Sección 24.2.9, “Tratamiento por parte de la API C de la ejecución de múltiples consultas”.

You must use mysql_real_query() rather than mysql_query() for queries that contain binary data,because binary data may contain the '\0' character. In addition, mysql_real_query() is faster thanmysql_query() because it does not call strlen() on the query string.

If you want to know whether the query should return a result set, you can use mysql_field_count() tocheck for this. See Sección 24.2.3.20, “ mysql_field_count() ” .

Return Values Zero if the query was successful. Non-zero if an error occurred.






24.2.3.50. mysql_reload() int mysql_reload(MYSQL *mysql) Description

Asks the MySQL server to reload the grant tables. The connected user must have the RELOAD privilege.

This function is deprecated. It is preferable to use mysql_query() to issue an SQL FLUSH PRIVILEGESstatement instead.







24.2.3.51. mysql_row_seek() MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset) Description

Sets the row cursor to an arbitrary row in a query result set. The offset value is a row offset that should bea value returned from mysql_row_tell() or from mysql_row_seek(). This value is not a row number; if youwant to seek to a row within a result set by number, use mysql_data_seek() instead. This function requires that the result set structure contains the entire result of the query, somysql_row_seek() may be used only in conjunction with mysql_store_result(), not withmysql_use_result(). Return Values

The previous value of the row cursor. This value may be passed to a subsequent call to mysql_row_seek(). Errors None.

24.2.3.52. mysql_row_tell() MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result) Description

Returns the current position of the row cursor for the last mysql_fetch_row(). This value can be used as anargument to mysql_row_seek(). You should use mysql_row_tell() only after mysql_store_result(), not after mysql_use_result(). Return Values The current offset of the row cursor.

Errors None.

24.2.3.53. mysql_select_db() int mysql_select_db(MYSQL *mysql, const char *db) Description

Causes the database specified by db to become the default (current) database on the connection specifiedby mysql. In subsequent queries, this database is the default for table references that do not include anexplicit database specifier.

mysql_select_db() fails unless the connected user can be authenticated as having permission to use thedatabase.







24.2.3.54. mysql_set_character_set() int mysql_set_character_set(MYSQL *mysql, char *csname) Description

This function is used to set the default character set for the current connection. The string csnamespecifies a valid character set name. The connection collation becomes the default collation of thecharacter set. This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string() This function was added in MySQL 5.0.7.



mysql_init(&mysql);if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)){ fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));}

if (!mysql_set_charset_name(&mysql, "utf8")) { printf("New client character set: %s\n", mysql_character_set_name(&mysql));}

24.2.3.55. mysql_set_server_option() int mysql_set_server_option(MYSQL *mysql, enum enum_mysql_set_option option) Description

Enables or disables an option for the connection. option can have one of the following values:

MYSQL_OPTION_MULTI_STATEMENTS_ON

Enable multi statementsupport.

MYSQL_OPTION_MULTI_STATEMENTS_OFF

Disable multi statementsupport.

This function was added in MySQL 4.1.1.





The connection to the server was lost during the query. ER_UNKNOWN_COM_ERROR

The server didn't support mysql_set_server_option() (which is the case that the server is olderthan 4.1.1) or the server didn't support the option one tried to set.

24.2.3.56. mysql_shutdown() int mysql_shutdown(MYSQL *mysql, enum enum_shutdown_level shutdown_level) Description

Asks the database server to shut down. The connected user must have SHUTDOWN privileges. Theshutdown_level argument was added in MySQL 4.1.3 (and 5.0.1). The MySQL server currently supportsonly one type (level of gracefulness) of shutdown; shutdown_level must be equal toSHUTDOWN_DEFAULT. Later we wil add more levels and then the shutdown_level argument will enableus to choose the desired level. MySQL servers and MySQL clients before and after 4.1.3 are compatible;MySQL servers newer than 4.1.3 accept the mysql_shutdown(MYSQL *mysql) call, and MySQL serversolder than 4.1.3 accept the new mysql_shutdown() call. But dynamically linked executables which have

been compiled with older versions of libmysqlclient headers, and call mysql_shutdown(), need to beused with the old libmysqlclient dynamic library. The shutdown process is described in Sección 5.4, “El proceso de cierre del servidor MySQL” .







24.2.3.57. mysql_sqlstate() const char *mysql_sqlstate(MYSQL *mysql) Description Returns a null-terminated string containing the SQLSTATE error code for the last error. The error codeconsists of five characters. '00000' means “no error.” The values are specified by ANSI SQL and ODBC.For a list of possible values, see Capítulo 26, Manejo de errores en MySQL .

Note that not all MySQL errors are yet mapped to SQLSTATE's. The value 'HY000' (general error) is usedfor unmapped errors. This function was added to MySQL 4.1.1.

Return Values A null-terminated character string containing the SQLSTATE error code.

See Also

See Sección 24.2.3.12, “ mysql_errno() ” . See Sección 24.2.3.13, “ mysql_error() ” . See Sección 24.2.7.26, “ mysql_stmt_sqlstate() ” . 24.2.3.58. mysql_ssl_set() int mysql_ssl_set(MYSQL *mysql, const char *key, const char *cert, const char *ca, const char*capath, const char *cipher) Description

mysql_ssl_set() is used for establishing secure connections using SSL. It must be called beforemysql_real_connect(). mysql_ssl_set() does nothing unless OpenSSL support is enabled in the client library.

mysql is the connection handler returned from mysql_init(). The other parameters are specified as follows: key is the pathname to the key file.

cert is the pathname to the certificate file.

ca is the pathname to the certificate authority file.

capath is the pathname to a directory that contains trusted SSL CA certificates in pem format.

cipher is a list of allowable ciphers to use for SSL encryption.

Any unused SSL parameters may be given as NULL.

Return Values

This function always returns 0. If SSL setup is incorrect, mysql_real_connect() returns an error when youattempt to connect.

24.2.3.59. mysql_stat() char *mysql_stat(MYSQL *mysql) Description Returns a character string containing information similar to that provided by the mysqladmin statuscommand. This includes uptime in seconds and the number of running threads, questions, reloads, andopen tables.

Return Values

A character string describing the server status. NULL if an error occurred.






24.2.3.60. mysql_store_result() MYSQL_RES *mysql_store_result(MYSQL *mysql) Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data(SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, and so forth).

You don't have to call mysql_store_result() or mysql_use_result() for other queries, but it does not doany harm or cause any notable performance degredation if you call mysql_store_result() in all cases. Youcan detect if the query didn't have a result set by checking if mysql_store_result() returns 0 (more aboutthis later on).

If you want to know whether the query should return a result set, you can use mysql_field_count() tocheck for this. See Sección 24.2.3.20, “ mysql_field_count() ” . mysql_store_result() reads the entire result of a query to the client, allocates a MYSQL_RES structure,and places the result into this structure.

mysql_store_result() returns a null pointer if the query didn't return a result set (if the query was, forexample, an INSERT statement).

mysql_store_result() also returns a null pointer if reading of the result set failed. You can check whetheran error occurred by checking if mysql_error() returns a non-empty string, if mysql_errno() returns non-zero, or if mysql_field_count() returns zero. An empty result set is returned if there are no rows returned. (An empty result set differs from a null pointeras a return value.)

Once you have called mysql_store_result() and got a result back that isn't a null pointer, you may callmysql_num_rows() to find out how many rows are in the result set.

You can call mysql_fetch_row() to fetch rows from the result set, or mysql_row_seek() andmysql_row_tell() to obtain or set the current row position within the result set.

You must call mysql_free_result() once you are done with the result set.


Return Values

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

Errors

mysql_store_result() resets mysql_error() and mysql_errno() if it succeeds. CR_COMMANDS_OUT_OF_SYNC






24.2.3.61. mysql_thread_id() unsigned long mysql_thread_id(MYSQL *mysql) Description

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

If the connection is lost and you reconnect with mysql_ping(), the thread ID changes. This means youshould not get the thread ID and store it for later. You should get it when you need it.

Return Values The thread ID of the current connection.

Errors None.

24.2.3.62. mysql_use_result() MYSQL_RES *mysql_use_result(MYSQL *mysql) Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data(SELECT, SHOW, DESCRIBE, EXPLAIN).

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client likemysql_store_result() does. Instead, each row must be retrieved individually by making calls tomysql_fetch_row(). This reads the result of a query directly from the server without storing it in atemporary table or local buffer, which is somewhat faster and uses much less memory thanmysql_store_result(). The client allocates memory only for the current row and a communication bufferthat may grow up to max_allowed_packet bytes.

On the other hand, you shouldn't use mysql_use_result() if you are doing a lot of processing for each rowon the client side, or if the output is sent to a screen on which the user may type a ̂ S (stop scroll). This tiesup the server and prevent other threads from updating any tables from which the data is being fetched.

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned,otherwise, the unfetched rows are returned as part of the result set for your next query. The C API gives theerror Commands out of sync; you can't run this command now if you forget to do this!

You may not use mysql_data_seek(), mysql_row_seek(), mysql_row_tell(), mysql_num_rows(), ormysql_affected_rows() with a result returned from mysql_use_result(), nor may you issue other queriesuntil the mysql_use_result() has finished. (However, after you have fetched all the rows,mysql_num_rows() accurately returns the number of rows fetched.)

You must call mysql_free_result() once you are done with the result set.

Return Values

A MYSQL_RES result structure. NULL if an error occurred.

Errors

mysql_use_result() resets mysql_error() and mysql_errno() if it succeeds. CR_COMMANDS_OUT_OF_SYNC






24.2.3.63. mysql_warning_count() unsigned int mysql_warning_count(MYSQL *mysql) Description Returns the number of warnings generated during execution of the previous SQL statement. This function was added in MySQL 4.1.0.

Return Values The warning count.

Errors None.

24.2.3.64. mysql_commit() my_bool mysql_commit(MYSQL *mysql) Description Commits the current transaction. This function was added in MySQL 4.1.0.

Return Values Zero if successful. Non-zero if an error occurred.

Errors None.

24.2.3.65. mysql_rollback()

my_bool mysql_rollback(MYSQL *mysql) Description Rolls back the current transaction. This function was added in MySQL 4.1.0.


Errors None.

24.2.3.66. mysql_autocommit() my_bool mysql_autocommit(MYSQL *mysql, my_bool mode) Description

Sets autocommit mode on if mode is 1, off if mode is 0. This function was added in MySQL 4.1.0.


Errors None.

24.2.3.67. mysql_more_results() my_bool mysql_more_results(MYSQL *mysql) Description Returns true if more results exist from the currently executed query, and the application must callmysql_next_result() to fetch the results. This function was added in MySQL 4.1.0.

Return Values

TRUE (1) if more results exist. FALSE (0) if no more results exist.

In most cases, you can call mysql_next_result() instead to test whether more results exist and initiateretrieval if so. See Sección 24.2.9, “Tratamiento por parte de la API C de la ejecución de múltiples consultas” . SeeSección 24.2.3.68, “ mysql_next_result() ” . Errors None.

24.2.3.68. mysql_next_result() int mysql_next_result(MYSQL *mysql) Description

If more query results exist, mysql_next_result() reads the next query results and returns the status back toapplication.

You must call mysql_free_result() for the preceding query if it returned a result set.

After calling mysql_next_result() the state of the connection is as if you had called mysql_real_query() ormysql_query() for the next query. This means that you can call mysql_store_result(),mysql_warning_count(), mysql_affected_rows(), and so forth.

If mysql_next_result() returns an error, no other statements are executed and there are no more results tofetch.

See Sección 24.2.9, “Tratamiento por parte de la API C de la ejecución de múltiples consultas” . This function was added in MySQL 4.1.0.

Return Values

ReturnValue

Description

0 Successful and there are moreresults

-1 Successful and there are nomore results

>0 An error occurred


Commands were executed in an improper order. For example if you didn't call mysql_use_result()for a previous result set.

CR_SERVER_GONE_ERROR




24.2.4. Sentencias preparadas de la API CAs of MySQL 4.1, the client/server protocol provides for the use of prepared statements. This capabilityuses the MYSQL_STMT statement handler data structure returned by the mysql_stmt_init() initializationfunction. Prepared execution is an efficient way to execute a statement more than once. The statement isfirst parsed to prepare it for execution. Then it is executed one or more times at a later time, using thestatement handle returned by the initialization function. Prepared execution is faster than direct execution for statements executed more than once, primarilybecause the query is parsed only once. In the case of direct execution, the query is parsed every time it isexecuted. Prepared execution also can provide a reduction of network traffic because for each execution ofthe prepared statement, it is necessary only to send the data for the parameters. Another advantage of prepared statements is that it uses a binary protocol that makes data transferbetween client and server more efficient.

The following statements can be used as prepared statements: CREATE TABLE, DELETE, DO, INSERT,REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not yetsupported.

24.2.5. Tipos de datos de sentencias preparadas de la API CNote: Some incompatible changes were made in MySQL 4.1.2. See Sección 24.2.7, “Descripciones de funciones de sentencias preparadas de la API C” for details.

Prepared statements mainly use the MYSQL_STMT and MYSQL_BIND data structures. A third structure,MYSQL_TIME, is used to transfer temporal data.

MYSQL_STMT

This structure represents a prepared statement. A statement is created by calling mysql_stmt_init(), which returns a statement handle, that is, a pointer to a MYSQL_STMT. The handle is used for allsubsequent statement-related functions until you close it with mysql_stmt_close().

The MYSQL_STMT structure has no members that are for application use. Also, you should not tryto make a copy of a MYSQL_STMT structure. There is no guarantee that such a copy will beusable. Multiple statement handles can be associated with a single connection. The limit on the number ofhandles depends on the available system resources.

MYSQL_BIND

This structure is used both for statement input (data values sent to the server) and output (resultvalues returned from the server). For input, it is used with mysql_stmt_bind_param() to bindparameter data values to buffers for use by mysql_stmt_execute(). For output, it is used withmysql_stmt_bind_result() to bind result set buffers for use in fetching rows withmysql_stmt_fetch(). The MYSQL_BIND structure contains the following members for use by application programs. Eachis used both for input and for output, although sometimes for different purposes depending on thedirection of data transfer.

o enum enum_field_types buffer_type

The type of the buffer. The allowable buffer_type values are listed later in this section. Forinput, buffer_type indicates what type of value you are binding to a statement parameter.For output, it indicates what type of value you expect to receive in a result buffer.

o void *buffer For input, this is a pointer to the buffer in which a statement parameter's data value is stored.For output, it is a pointer to the buffer in which to return a result set column value. Fornumeric column types, buffer should point to a variable of the proper C type. (If you areassociating the variable with a column that has the UNSIGNED attribute, the variable shouldbe an unsigned C type. Indicate whether the variable is signed or unsigned by using theis_unsigned member, described later in this list.) For date and time column types, buffershould point to a MYSQL_TIME structure. For character and binary string column types,buffer should point to a character buffer.

o unsigned long buffer_length

The actual size of *buffer in bytes. This indicates the maximum amount of data that can bestored in the buffer. For character and binary C data, the buffer_length value specifies thelength of *buffer when used with mysql_stmt_bind_param(), or the maximum number ofdata bytes that can be fetched into the buffer when used with mysql_stmt_bind_result().

o unsigned long *length

A pointer to an unsigned long variable that indicates the actual number of bytes of datastored in *buffer. length is used for character or binary C data. For input parameter databinding, length points to an unsigned long variable that indicates the length of theparameter value stored in *buffer; this is used by mysql_stmt_execute(). For output valuebinding, mysql_stmt_fetch() places the length of the column value that is returned into thevariable that length points to.

length is ignored for numeric and temporal data types because the length of the data valueis determined by the buffer_type value.

o my_bool *is_null This member points to a my_bool variable that is true if a value is NULL, false if it is notNULL. For input, set *is_null to true to indicate that you are passing a NULL value as astatement parameter. For output, this value is set to true after you fetch a row if the result setcolumn value returned from the statement is NULL.

o my_bool is_unsigned

This member is used for integer types. (These correspond to the MYSQL_TYPE_TINY,MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG, and MYSQL_TYPE_LONGLONG typecodes.) is_unsigned should be set to true for unsigned types and false for signed types.

o my_bool error For output, this member is used output to report data truncation errors. Truncation reportingmust be enabled by calling mysql_options() with theMYSQL_REPORT_DATA_TRUNCATION option. When enabled, mysql_stmt_fetch()returns MYSQL_DATA_TRUNCATED and error is true in the MYSQL_BIND structures forparameters in which truncation occurred. Truncation indicates loss of sign or significantdigits, or that a string was too long to fit in a column. The error member was added inMySQL 5.0.3.

To use a MYSQL_BIND structure, you should zero its contents to initialize it, and then set themembers just described appropriately. For example, to declare and initialize an array of threeMYSQL_BIND structures, use this code:

MYSQL_BIND bind[3];memset(bind, 0, sizeof(bind));

MYSQL_TIME

This structure is used to send and receive DATE, TIME, DATETIME, and TIMESTAMP data directlyto and from the server. This is done by setting the buffer_type member of a MYSQL_BINDstructure to one of the temporal types, and setting the buffer member to point to a MYSQL_TIMEstructure. The MYSQL_TIME structure contains the following members:

o unsigned int year The year.

o unsigned int month

The month of the year. o unsigned int day

The day of the month. o unsigned int hour

The hour of the day. o unsigned int minute

The minute of the hour. o unsigned int second

The second of the minute. o my_bool neg

A boolean flag to indicate whether the time is negative. o unsigned long second_part

The fractional part of the second. This member currently is unused. Only those parts of a MYSQL_TIME structure that apply to a given type of temporal value are used:The year, month, and day elements are used for DATE, DATETIME, and TIMESTAMP values. Thehour, minute, and second elements are used for TIME, DATETIME, and TIMESTAMP values. SeeSección 24.2.10, “Manejo de valores de fecha y hora por parte de la API C” .

The following table shows the allowable values that may be specified in the buffer_type member ofMYSQL_BIND structures. The table also shows those SQL types that correspond most closely to eachbuffer_type value, and, for numeric and temporal types, the corresponding C type.

buffer_type Value SQL Type C Type

MYSQL_TYPE_TINY TINYINT char

MYSQL_TYPE_SHORT

SMALLINT short int

MYSQL_TYPE_LONG INT int

MYSQL_TYPE_LONGLONG

BIGINT long longint

MYSQL_TYPE_FLOAT

FLOAT float

MYSQL_TYPE_DOUBLE

DOUBLE double

MYSQL_TYPE_TIME TIME MYSQL_TIME

MYSQL_TYPE_DATE DATE MYSQL_TIME

MYSQL_TYPE_DATETIME

DATETIME MYSQL_TIME

MYSQL_TYPE_TIMESTAMP

TIMESTAMP MYSQL_TIME

MYSQL_TYPE_STRING

CHAR

MYSQL_TYPE_VAR_STRING

VARCHAR

MYSQL_TYPE_TINY_BLOB

TINYBLOB/TINYTEXT

MYSQL_TYPE_BLOB BLOB/TEXT

MYSQL_TYPE_MEDIUM_BLOB

MEDIUMBLOB/MEDIUMTEXT

MYSQL_TYPE_LONG_BLOB

LONGBLOB/LONGTEXT

Implicit type conversion may be performed in both directions.

24.2.6. Panorámica de las funciones de sentencias preparadas de la API CNote: Some incompatible changes were made in MySQL 4.1.2. See Sección 24.2.7, “Descripciones de funciones de sentencias preparadas de la API C” for details. The functions available for prepared statement processing are summarized here and described in greaterdetail in a later section. See Sección 24.2.7, “Descripciones de funciones de sentencias preparadas de la API C”.

Function Description

mysql_stmt_affected_rows()

Returns the number of rows changes, deleted, or inserted by prepared UPDATE,DELETE, or INSERT statement.

mysql_stmt_attr_get()

Get value of an attribute for a prepared statement.

mysql_stmt_attr_set() Sets an attribute for a prepared statement.

mysql_stmt_bind_param()

Associates application data buffers with the parameter markers in a prepared SQLstatement.

mysql_stmt_bind_result()

Associates application data buffers with columns in the result set.

mysql_stmt_close() Frees memory used by prepared statement.

mysql_stmt_data_seek()

Seeks to an arbitrary row number in a statement result set.

mysql_stmt_errno() Returns the error number for the last statement execution.

mysql_stmt_error() Returns the error message for the last statement execution.

mysql_stmt_execute()

Executes the prepared statement.

mysql_stmt_fetch() Fetches the next row of data from the result set and returns data for all boundcolumns.

mysql_stmt_fetch_column()

Fetch data for one column of the current row of the result set.

mysql_stmt_field_count()

Returns the number of result columns for the most recent statement.

mysql_stmt_free_result()

Free the resources allocated to the statement handle.

mysql_stmt_init() Allocates memory for MYSQL_STMT structure and initializes it.

mysql_stmt_insert_id()

Returns the ID generated for an AUTO_INCREMENT column by preparedstatement.

mysql_stmt_num_rows()

Returns total rows from the statement buffered result set.

mysql_stmt_param_count()

Returns the number of parameters in a prepared SQL statement.

mysql_stmt_param_metadata()

Return parameter metadata in the form of a result set.

mysql_stmt_prepare()

Prepares an SQL string for execution.

mysql_stmt_reset() Reset the statement buffers in the server.

mysql_stmt_result_metadata()

Returns prepared statement metadata in the form of a result set.

mysql_stmt_row_seek()

Seeks to a row offset in a statement result set, using value returned frommysql_stmt_row_tell().

mysql_stmt_row_tell()

Returns the statement row cursor position.

mysql_stmt_send_long_data()

Sends long data in chunks to server.

mysql_stmt_sqlstate()

Returns the SQLSTATE error code for the last statement execution.

mysql_stmt_store_result()

Retrieves the complete result set to the client.

Call mysql_stmt_init() to create a statement handle, then mysql_stmt_prepare to prepare it,mysql_stmt_bind_param() to supply the parameter data, and mysql_stmt_execute() to execute thestatement. You can repeat the mysql_stmt_execute() by changing parameter values in the respectivebuffers supplied through mysql_stmt_bind_param(). If the statement is a SELECT or any other statement that produces a result set, mysql_stmt_prepare()also returns the result set metadata information in the form of a MYSQL_RES result set throughmysql_stmt_result_metadata(). You can supply the result buffers using mysql_stmt_bind_result(), so that the mysql_stmt_fetch()automatically returns data to these buffers. This is row-by-row fetching.

You can also send the text or binary data in chunks to server using mysql_stmt_send_long_data(). SeeSección 24.2.7.25, “ mysql_stmt_send_long_data() ” . When statement execution has been completed, the statement handle must be closed usingmysql_stmt_close() so that all resources associated with it can be freed.

If you obtained a SELECT statement's result set metadata by calling mysql_stmt_result_metadata(), youshould also free the metadata using mysql_free_result(). Execution Steps To prepare and execute a statement, an application follows these steps:

1. Create a prepared statement handle with msyql_stmt_init(). To prepare the statement on theserver, call mysql_stmt_prepare() and pass it a string containing the SQL statement.

2. If the statement produces a result set, call mysql_stmt_result_metadata() to obtain the result setmetadata. This metadata is itself in the form of result set, albeit a separate one from the one thatcontains the rows returned by the query. The metadata result set indicates how many columns arein the result and contains information about each column.

3. Set the values of any parameters using mysql_stmt_bind_param(). All parameters must be set.Otherwise, statement execution returns an error or produces unexpected results.

4. Call mysql_stmt_execute() to execute the statement. 5. If the statement produces a result set, bind the data buffers to use for retrieving the row values by

calling mysql_stmt_bind_result(). 6. Fetch the data into the buffers row by row by calling mysql_stmt_fetch() repeatedly until no more

rows are found. 7. Repeat steps 3 through 6 as necessary, by changing the parameter values and re-executing the

statement.

When mysql_stmt_prepare() is called, the MySQL client/server protocol performs these actions:

The server parses the statement and sends the okay status back to the client by assigning astatement ID. It also sends total number of parameters, a column count, and its metadata if it is aresult set oriented statement. All syntax and semantics of the statement are checked by the serverduring this call.

The client uses this statement ID for the further operations, so that the server can identify thestatement from among its pool of statements.

When mysql_stmt_execute() is called, the MySQL client/server protocol performs these actions: The client uses the statement handle and sends the parameter data to the server. The server identifies the statement using the ID provided by the client, replaces the parameter

markers with the newly supplied data, and executes the statement. If the statement produces aresult set, the server sends the data back to the client. Otherwise, it sends an okay status and totalnumber of rows changed, deleted, or inserted.

When mysql_stmt_fetch() is called, the MySQL client/server protocol performs these actions: The client reads the data from the packet row by row and places it into the application data buffers

by doing the necessary conversions. If the application buffer type is same as that of the field typereturned from the server, the conversions are straightforward.

If an error occurs, you can get the statement error code, error message, and SQLSTATE value usingmysql_stmt_errno(), mysql_stmt_error(), and mysql_stmt_sqlstate(), respectively.

24.2.7. Descripciones de funciones de sentencias preparadas de la API CTo prepare and execute queries, use the functions in the following sections. In MySQL 4.1.2, the names of several prepared statement functions were changed:

Old Name New Name

mysql_bind_param()

mysql_stmt_bind_param()

mysql_bind_result()

mysql_stmt_bind_result()

mysql_prepare() mysql_stmt_prepare()

mysql_execute() mysql_stmt_execute()

mysql_fetch() mysql_stmt_fetch()

mysql_fetch_column()

mysql_stmt_fetch_column()

mysql_param_count()

mysql_stmt_param_count()

mysql_param_result()

mysql_stmt_param_metadata()

mysql_get_metadata()

mysql_stmt_result_metadata()

mysql_send_long_data()

mysql_stmt_send_long_data()

All functions that operate with a MYSQL_STMT structure begin with the prefix mysql_stmt_.

Also in 4.1.2, the signature of the mysql_stmt_prepare() function was changed to intmysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length). To create aMYSQL_STMT handle, you should use the mysql_stmt_init() function.

24.2.7.1. mysql_stmt_affected_rows() my_ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt) Description Returns the total number of rows changed, deleted, or inserted by the last executed statement. May becalled immediately after mysql_stmt_execute() for UPDATE, DELETE, or INSERT statements. ForSELECT statements, mysql_stmt_affected_rows() works like mysql_num_rows(). This function was added in MySQL 4.1.0.

Return Values An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that norecords were updated for an UPDATE statement, no rows matched the WHERE clause in the query, or thatno query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query,mysql_stmt_affected_rows() was called prior to calling mysql_stmt_store_result(). Becausemysql_stmt_affected_rows() returns an unsigned value, you can check for -1 by comparing the returnvalue to (my_ulonglong)-1 (or to (my_ulonglong)~0, which is equivalent).

See Sección 24.2.3.1, “ mysql_affected_rows() ” for additional information on the return value.

Errors None.

Example

For the usage of mysql_stmt_affected_rows(), refer to the Example from Sección 24.2.7.10, “ mysql_stmt_execute() ” . 24.2.7.2. mysql_stmt_attr_get() int mysql_stmt_attr_get(MYSQL_STMT *stmt, enum enum_stmt_attr_type option, void *arg) Description Can be used to get the current value for a statement attribute.

The option argument is the option that you want to get; the arg should point to a variable that shouldcontain the option value. If the option is an integer, then arg should point to the value of the integer.

See mysql_stmt_attr_set() for a list of options and option types. See Sección 24.2.7.3, “ mysql_stmt_attr_set() ” . This function was added in MySQL 4.1.2.

Return Values

0 if okay. Non-zero if option is unknown.

Errors None.

24.2.7.3. mysql_stmt_attr_set() int mysql_stmt_attr_set(MYSQL_STMT *stmt, enum enum_stmt_attr_type option, const void *arg) Description Can be used to set affect behavior for a statement. This function may be called multiple times to set severaloptions.

The option argument is the option that you want to set; the arg argument is the value for the option. If theoption is an integer, then arg should point to the value of the integer.

Possible option values:

Option Argument Type

Function

STMT_ATTR_UPDATE_MAX_LENGTH

my_bool*

If set to 1: Update metadata MYSQL_FIELD->max_length inmysql_stmt_store_result().

STMT_ATTR_CURSOR_TYPE

unsigned long *

Type of cursor to open for statement when mysql_stmt_execute() is invoked. *arg can be CURSOR_TYPE_NO_CURSOR (thedefault) or CURSOR_TYPE_READ_ONLY.

STMT_ATTR_PREFETCH_ROWS

unsigned long *

Number of rows to fetch from server at a time when using acursor. *arg can be in the range from 1 to the maximum value ofunsigned long. The default is 1.

If you use the STMT_ATTR_CURSOR_TYPE option with CURSOR_TYPE_READ_ONLY, a cursor isopened for the statement when you invoke mysql_stmt_execute(). If there is already an open cursor froma previous mysql_stmt_execute() call, it closes the cursor before opening a new one. mysql_stmt_reset()also closes any open cursor before preparing the statement for re-execution. mysql_stmt_free_result()closes any open cursor.

This function was added in MySQL 4.1.2. The STMT_ATTR_CURSOR_TYPE option was added in MySQL5.0.2. The STMT_ATTR_PREFETCH_ROWS option was added in MySQL 5.0.6.

Return Values

0 if okay. Non-zero if option is unknown.

Errors None.

24.2.7.4. mysql_stmt_bind_param() my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind) Description

mysql_stmt_bind_param() is used to bind data for the parameter markers in the SQL statement that waspassed to mysql_stmt_prepare(). It uses MYSQL_BIND structures to supply the data. bind is the addressof an array of MYSQL_BIND structures. The client library expects the array to contain an element for each'?' parameter marker that is present in the query. Suppose that you prepare the following statement: INSERT INTO mytbl VALUES(?,?,?)

When you bind the parameters, the array of MYSQL_BIND structures must contain three elements, andcan be declared like this: MYSQL_BIND bind[3];

The members of each MYSQL_BIND element that should be set are described in Sección 24.2.5, “Tipos de datos de sentencias preparadas de la API C”. This function was added in MySQL 4.1.2.

Return Values Zero if the bind was successful. Non-zero if an error occurred.

Errors CR_INVALID_BUFFER_USE

Indicates if the bind is to supply the long data in chunks and if the buffer type is non string or binary. CR_UNSUPPORTED_PARAM_TYPE

The conversion is not supported. Possibly the buffer_type value is illegal or is not one of thesupported types.

CR_OUT_OF_MEMORY

Out of memory. CR_UNKNOWN_ERROR


Example

For the usage of mysql_stmt_bind_param(), refer to the Example from Sección 24.2.7.10, “ mysql_stmt_execute() ” . 24.2.7.5. mysql_stmt_bind_result() my_bool mysql_stmt_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind) Description

mysql_stmt_bind_result() is used to associate (bind) columns in the result set to data buffers and lengthbuffers. When mysql_stmt_fetch() is called to fetch data, the MySQL client/server protocol places the datafor the bound columns into the specified buffers.

All columns must be bound to buffers prior to calling mysql_stmt_fetch(). bind is the address of an arrayof MYSQL_BIND structures. The client library expects the array to contain an element for each column ofthe result set. If you do not bind columns to MYSQL_BIND structures, mysql_stmt_fetch() simply ignoresthe data fetch. The buffers should be large enough to hold the data values, because the protocol doesn'treturn data values in chunks. A column can be bound or rebound at any time, even after a result set has been partially retrieved. The newbinding takes effect the next time mysql_stmt_fetch() is called. Suppose that an application binds thecolumns in a result set and calls mysql_stmt_fetch(). The client/server protocol returns data in the boundbuffers. Then suppose that the application binds the columns to a different set of buffers. The protocol doesnot place data into the newly bound buffers until the next call to mysql_stmt_fetch() occurs.

To bind a column, an application calls mysql_stmt_bind_result() and passes the type, address, and theaddress of the length buffer. The members of each MYSQL_BIND element that should be set aredescribed in Sección 24.2.5, “Tipos de datos de sentencias preparadas de la API C” . This function was added in MySQL 4.1.2.

Return Values Zero if the bind was successful. Non-zero if an error occurred.

Errors CR_UNSUPPORTED_PARAM_TYPE

The conversion is not supported. Possibly the buffer_type value is illegal or is not one of thesupported types.

CR_OUT_OF_MEMORY



Example

For the usage of mysql_stmt_bind_result(), refer to the Example from Sección 24.2.7.13, “ mysql_stmt_fetch() ” . 24.2.7.6. mysql_stmt_close() my_bool mysql_stmt_close(MYSQL_STMT *) Description

Closes the prepared statement. mysql_stmt_close() also deallocates the statement handle pointed to bystmt. If the current statement has pending or unread results, this function cancels them so that the next querycan be executed. This function was added in MySQL 4.1.0.

Return Values Zero if the statement was freed successfully. Non-zero if an error occurred.

Errors CR_SERVER_GONE_ERROR

The MySQL server has gone away. CR_UNKNOWN_ERROR


Example

For the usage of mysql_stmt_close(), refer to the Example from Sección 24.2.7.10, “ mysql_stmt_execute() ” . 24.2.7.7. mysql_stmt_data_seek() void mysql_stmt_data_seek(MYSQL_STMT *stmt, my_ulonglong offset) Description

Seeks to an arbitrary row in a statement result set. The offset value is a row number and should be in therange from 0 to mysql_stmt_num_rows(stmt)-1. This function requires that the statement result set structure contains the entire result of the last executedquery, so mysql_stmt_data_seek() may be used only in conjunction with mysql_stmt_store_result(). This function was added in MySQL 4.1.1.

Return Values None.

Errors None.

24.2.7.8. mysql_stmt_errno() unsigned int mysql_stmt_errno(MYSQL_STMT *stmt) Description

For the statement specified by stmt, mysql_stmt_errno() returns the error code for the most recentlyinvoked statement API function that can succeed or fail. A return value of zero means that no erroroccurred. Client error message numbers are listed in the MySQL errmsg.h header file. Server errormessage numbers are listed in mysqld_error.h. In the MySQL source distribution you can find a completelist of error messages and error numbers in the file Docs/mysqld_error.txt. The server error codes also arelisted at Capítulo 26, Manejo de errores en MySQL . This function was added in MySQL 4.1.0.

Return Values An error code value. Zero if no error occurred.

Errors None.

24.2.7.9. mysql_stmt_error() const char *mysql_stmt_error(MYSQL_STMT *stmt)

Description

For the statement specified by stmt, mysql_stmt_error() returns a null-terminated string containing theerror message for the most recently invoked statement API function that can succeed or fail. An emptystring ("") is returned if no error occurred. This means the following two tests are equivalent: if (mysql_stmt_errno(stmt)){ // an error occurred}

if (mysql_stmt_error(stmt)[0]){ // an error occurred}The language of the client error messages may be changed by recompiling the MySQL client library.Currently you can choose error messages in several different languages. This function was added in MySQL 4.1.0.

Return Values A character string that describes the error. An empty string if no error occurred.

Errors None.

24.2.7.10. mysql_stmt_execute() int mysql_stmt_execute(MYSQL_STMT *stmt) Description

mysql_stmt_execute() executes the prepared query associated with the statement handle. The currentlybound parameter marker values are sent to server during this call, and the server replaces the markers withthis newly supplied data.

If the statement is an UPDATE, DELETE, or INSERT, the total number of changed, deleted, or insertedrows can be found by calling mysql_stmt_affected_rows(). If this is a statement such as SELECT thatgenerates a result set, you must call mysql_stmt_fetch() to fetch the data prior to calling any otherfunctions that result in query processing. For more information on how to fetch the results, refer toSección 24.2.7.13, “ mysql_stmt_fetch() ” .

For statements that generate a result set, you can request that mysql_stmt_execute() open a cursor forthe statement by calling mysql_stmt_attr_set() before executing the statement. If you execute a statementmultiple times, mysql_stmt_execute() closes any open cursor before opening a new one. This function was added in MySQL 4.1.2. Cursor support was added in MySQL 5.

Return Values Zero if execution was successful. Non-zero if an error occurred.




The MySQL server has gone away.

CR_SERVER_LOST



Example

The following example demonstrates how to create and populate a table using mysql_stmt_init(),mysql_stmt_prepare(), mysql_stmt_param_count(), mysql_stmt_bind_param(), mysql_stmt_execute(), and mysql_stmt_affected_rows(). The mysql variable is assumed to be a valid connection handle. #define STRING_SIZE 50

#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"#define CREATE_SAMPLE_TABLE "CREATE TABLE test_table(col1 INT,\ col2 VARCHAR(40),\ col3 SMALLINT,\ col4 TIMESTAMP)"#define INSERT_SAMPLE "INSERT INTO test_table(col1,col2,col3) VALUES(?,?,?)"

MYSQL_STMT *stmt;MYSQL_BIND bind[3];my_ulonglong affected_rows;int param_count;short small_data;int int_data;char str_data[STRING_SIZE];unsigned long str_length;my_bool is_null;

if (mysql_query(mysql, DROP_SAMPLE_TABLE)){ fprintf(stderr, " DROP TABLE failed\n"); fprintf(stderr, " %s\n", mysql_error(mysql)); exit(0);}

if (mysql_query(mysql, CREATE_SAMPLE_TABLE)){ fprintf(stderr, " CREATE TABLE failed\n"); fprintf(stderr, " %s\n", mysql_error(mysql)); exit(0);}

/* Prepare an INSERT query with 3 parameters *//* (the TIMESTAMP column is not named; the server *//* sets it to the current date and time) */stmt = mysql_stmt_init(mysql);if (!stmt){ fprintf(stderr, " mysql_stmt_init(), out of memory\n"); exit(0);}if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE))){ fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}fprintf(stdout, " prepare, INSERT successful\n");

/* Get the parameter count from the statement */param_count= mysql_stmt_param_count(stmt);fprintf(stdout, " total parameters in INSERT: %d\n", param_count);

if (param_count != 3) /* validate parameter count */{ fprintf(stderr, " invalid parameter count returned by MySQL\n"); exit(0);}

/* Bind the data for all 3 parameters */

memset(bind, 0, sizeof(bind));

/* INTEGER PARAM *//* This is a number type, so there is no need to specify buffer_length */bind[0].buffer_type= MYSQL_TYPE_LONG;bind[0].buffer= (char *)&int_data;bind[0].is_null= 0;bind[0].length= 0;

/* STRING PARAM */bind[1].buffer_type= MYSQL_TYPE_STRING;bind[1].buffer= (char *)str_data;

bind[1].buffer_length= STRING_SIZE;bind[1].is_null= 0;bind[1].length= &str_length;

/* SMALLINT PARAM */bind[2].buffer_type= MYSQL_TYPE_SHORT;bind[2].buffer= (char *)&small_data;bind[2].is_null= &is_null;bind[2].length= 0;

/* Bind the buffers */if (mysql_stmt_bind_param(stmt, bind)){ fprintf(stderr, " mysql_stmt_bind_param() failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Specify the data values for the first row */int_data= 10; /* integer */strncpy(str_data, "MySQL", STRING_SIZE); /* string */str_length= strlen(str_data);

/* INSERT SMALLINT data as NULL */is_null= 1;

/* Execute the INSERT statement - 1*/if (mysql_stmt_execute(stmt)){ fprintf(stderr, " mysql_stmt_execute(), 1 failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Get the total number of affected rows */affected_rows= mysql_stmt_affected_rows(stmt);fprintf(stdout, " total affected rows(insert 1): %lu\n", (unsigned long) affected_rows);

if (affected_rows != 1) /* validate affected rows */{

fprintf(stderr, " invalid affected rows by MySQL\n"); exit(0);}

/* Specify data values for second row, then re-execute the statement */int_data= 1000;strncpy(str_data, "The most popular Open Source database", STRING_SIZE);str_length= strlen(str_data);small_data= 1000; /* smallint */is_null= 0; /* reset */

/* Execute the INSERT statement - 2*/if (mysql_stmt_execute(stmt)){ fprintf(stderr, " mysql_stmt_execute, 2 failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Get the total rows affected */affected_rows= mysql_stmt_affected_rows(stmt);fprintf(stdout, " total affected rows(insert 2): %lu\n", (unsigned long) affected_rows);

if (affected_rows != 1) /* validate affected rows */{ fprintf(stderr, " invalid affected rows by MySQL\n"); exit(0);}

/* Close the statement */if (mysql_stmt_close(stmt)){ fprintf(stderr, " failed while closing the statement\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

Note: For complete examples on the use of prepared statement functions, refer to the filetests/mysql_client_test.c. This file can be obtained from a MySQL source distribution or from the BitKeepersource repository.

24.2.7.11. mysql_stmt_free_result()

my_bool mysql_stmt_free_result(MYSQL_STMT *stmt) Description Releases memory associated with the result set produced by execution of the prepared statement. If thereis a cursor open for the statement, mysql_stmt_free_result() closes it. This function was added in MySQL 4.1.1. Cursor support was added in MySQL 5.

Return Values Zero if the result set was freed successfully. Non-zero if an error occurred.

Errors

24.2.7.12. mysql_stmt_insert_id() my_ulonglong mysql_stmt_insert_id(MYSQL_STMT *stmt) Description

Returns the value generated for an AUTO_INCREMENT column by the prepared INSERT or UPDATEstatement. Use this function after you have executed prepared INSERT statement into a table whichcontains an AUTO_INCREMENT field.

See Sección 24.2.3.34, “ mysql_insert_id() ” for more information. This function was added in MySQL 4.1.2.

Return Values

Value for AUTO_INCREMENT column which was automatically generated or explicitly set during executionof prepared statement, or value generated by LAST_INSERT_ID(expr) function. Return value is undefined ifstatement does not set AUTO_INCREMENT value.

Errors None.

24.2.7.13. mysql_stmt_fetch() int mysql_stmt_fetch(MYSQL_STMT *stmt) Description

mysql_stmt_fetch() returns the next row in the result set. It can be called only while the result set exists,that is, after a call to mysql_stmt_execute() that creates a result set or after mysql_stmt_store_result(),which is called after mysql_stmt_execute() to buffer the entire result set.

mysql_stmt_fetch() returns row data using the buffers bound by mysql_stmt_bind_result(). It returns thedata in those buffers for all the columns in the current row set and the lengths are returned to the lengthpointer.

All columns must be bound by the application before calling mysql_stmt_fetch(). If a fetched data value is a NULL value, the *is_null value of the corresponding MYSQL_BIND structurecontains TRUE (1). Otherwise, the data and its length are returned in the *buffer and *length elementsbased on the buffer type specified by the application. Each numeric and temporal type has a fixed length,as listed in the following table. The length of the string types depends on the length of the actual data value,as indicated by data_length.

Type Length

MYSQL_TYPE_TINY

1

MYSQL_TYPE_SHORT

2

MYSQL_TYPE_LONG

4

MYSQL_TYPE_LONGLONG

8

MYSQL_TYPE_FLOAT

4

MYSQL_TYPE_DOUBLE

8

MYSQL_TYPE_TIME

sizeof(MYSQL_TIME)

MYSQL_TYPE_DATE

sizeof(MYSQL_TIME)

MYSQL_TYPE_DATETIME

sizeof(MYSQL_TIME)

MYSQL_TYPE_STRING

data length

MYSQL_TYPE_BLOB

data_length


Return Values

Return Value Description

0 Successful, the data has been fetched to application data buffers.

1 Error occurred. Error code and message can be obtained by callingmysql_stmt_errno() and mysql_stmt_error().

MYSQL_NO_DATA No more rows/data exists

MYSQL_DATA_TRUNCATED

Data truncation occurred

MYSQL_DATA_TRUNCATED is not returned unless truncation reporting is enabled with mysql_options().To determine which parameters were truncated when this value is returned, check the error members ofthe MYSQL_BIND parameter structures.





The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred. CR_UNSUPPORTED_PARAM_TYPE

The buffer type is MYSQL_TYPE_DATE, MYSQL_TYPE_TIME, MYSQL_TYPE_DATETIME, orMYSQL_TYPE_TIMESTAMP, but the data type is not DATE, TIME, DATETIME, or TIMESTAMP.

All other unsupported conversion errors are returned from mysql_stmt_bind_result(). Example

The following example demonstrates how to fetch data from a table using mysql_stmt_result_metadata(),mysql_stmt_bind_result(), and mysql_stmt_fetch(). (This example expects to retrieve the two rowsinserted by the example shown in Sección 24.2.7.10, “ mysql_stmt_execute() ” .) The mysql variable isassumed to be a valid connection handle. #define STRING_SIZE 50

#define SELECT_SAMPLE "SELECT col1, col2, col3, col4 FROM test_table"

MYSQL_STMT *stmt;MYSQL_BIND bind[4];MYSQL_RES *prepare_meta_result;MYSQL_TIME ts;unsigned long length[4];int param_count, column_count, row_count;short small_data;int int_data;char str_data[STRING_SIZE];my_bool is_null[4];

/* Prepare a SELECT query to fetch data from test_table */stmt = mysql_stmt_init(mysql);if (!stmt){ fprintf(stderr, " mysql_stmt_init(), out of memory\n"); exit(0);}if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE))){ fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}fprintf(stdout, " prepare, SELECT successful\n");

/* Get the parameter count from the statement */

param_count= mysql_stmt_param_count(stmt);fprintf(stdout, " total parameters in SELECT: %d\n", param_count);

if (param_count != 0) /* validate parameter count */{ fprintf(stderr, " invalid parameter count returned by MySQL\n"); exit(0);}

/* Fetch result set meta information */prepare_meta_result = mysql_stmt_result_metadata(stmt);if (!prepare_meta_result){ fprintf(stderr, " mysql_stmt_result_metadata(), returned no meta information\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Get total columns in the query */column_count= mysql_num_fields(prepare_meta_result);fprintf(stdout, " total columns in SELECT statement: %d\n", column_count);

if (column_count != 4) /* validate column count */{ fprintf(stderr, " invalid column count returned by MySQL\n"); exit(0);}

/* Execute the SELECT query */if (mysql_stmt_execute(stmt)){ fprintf(stderr, " mysql_stmt_execute(), failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Bind the result buffers for all 4 columns before fetching them */

memset(bind, 0, sizeof(bind));

/* INTEGER COLUMN */bind[0].buffer_type= MYSQL_TYPE_LONG;bind[0].buffer= (char *)&int_data;bind[0].is_null= &is_null[0];bind[0].length= &length[0];

/* STRING COLUMN */bind[1].buffer_type= MYSQL_TYPE_STRING;bind[1].buffer= (char *)str_data;bind[1].buffer_length= STRING_SIZE;bind[1].is_null= &is_null[1];bind[1].length= &length[1];

/* SMALLINT COLUMN */bind[2].buffer_type= MYSQL_TYPE_SHORT;bind[2].buffer= (char *)&small_data;bind[2].is_null= &is_null[2];bind[2].length= &length[2];

/* TIMESTAMP COLUMN */bind[3].buffer_type= MYSQL_TYPE_TIMESTAMP;bind[3].buffer= (char *)&ts;bind[3].is_null= &is_null[3];bind[3].length= &length[3];

/* Bind the result buffers */if (mysql_stmt_bind_result(stmt, bind)){ fprintf(stderr, " mysql_stmt_bind_result() failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Now buffer all results to client */if (mysql_stmt_store_result(stmt)){ fprintf(stderr, " mysql_stmt_store_result() failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

/* Fetch all rows */row_count= 0;fprintf(stdout, "Fetching results ...\n");while (!mysql_stmt_fetch(stmt)){ row_count++; fprintf(stdout, " row %d\n", row_count);

/* column 1 */ fprintf(stdout, " column1 (integer) : "); if (is_null[0]) fprintf(stdout, " NULL\n"); else fprintf(stdout, " %d(%ld)\n", int_data, length[0]);

/* column 2 */ fprintf(stdout, " column2 (string) : "); if (is_null[1]) fprintf(stdout, " NULL\n"); else fprintf(stdout, " %s(%ld)\n", str_data, length[1]);

/* column 3 */ fprintf(stdout, " column3 (smallint) : "); if (is_null[2]) fprintf(stdout, " NULL\n"); else fprintf(stdout, " %d(%ld)\n", small_data, length[2]);

/* column 4 */ fprintf(stdout, " column4 (timestamp): "); if (is_null[3]) fprintf(stdout, " NULL\n"); else fprintf(stdout, " %04d-%02d-%02d %02d:%02d:%02d (%ld)\n", ts.year, ts.month, ts.day, ts.hour, ts.minute, ts.second, length[3]); fprintf(stdout, "\n");}

/* Validate rows fetched */fprintf(stdout, " total rows fetched: %d\n", row_count);if (row_count != 2){ fprintf(stderr, " MySQL failed to return all rows\n"); exit(0);}

/* Free the prepared result metadata */mysql_free_result(prepare_meta_result);

/* Close the statement */if (mysql_stmt_close(stmt)){ fprintf(stderr, " failed while closing the statement\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0);}

24.2.7.14. mysql_stmt_fetch_column() int mysql_stmt_fetch_column(MYSQL_STMT *stmt, MYSQL_BIND *bind, unsigned int column,unsigned long offset) Description

Fetch one column from the current result set row. bind provides the buffer where data should be placed. Itshould be set up the same way as for mysql_stmt_bind_result(). column indicates which column to fetch.The first column is numbered 0. offset is the offset within the data value at which to begin retrieving data.This can be used for fetching the data value in pieces. The beginning of the value is offset 0. This function was added in MySQL 4.1.2.

Return Values Zero if the value was fetched successfully. Non-zero if an error occurred.

Errors CR_INVALID_PARAMETER_NO

Invalid column number. CR_NO_DATA

The end of the result set has already been reached.

24.2.7.15. mysql_stmt_field_count() unsigned int mysql_stmt_field_count(MYSQL_STMT *stmt) Description Returns the number of columns for the most recent statement for the statement handler. This value is zerofor statements such as INSERT or DELETE that do not produce result sets.

mysql_stmt_field_count() can be called after you have prepared a statement by invokingmysql_stmt_prepare().



Errors None.

24.2.7.16. mysql_stmt_init() MYSQL_STMT *mysql_stmt_init(MYSQL *mysql) Description

Create a MYSQL_STMT handle. The handle should be freed with mysql_stmt_close(MYSQL_STMT *). This function was added in MySQL 4.1.2.

Return values

A pointer to a MYSQL_STMT structure in case of success. NULL if out of memory.

Errors CR_OUT_OF_MEMORY

Out of memory.

24.2.7.17. mysql_stmt_num_rows() my_ulonglong mysql_stmt_num_rows(MYSQL_STMT *stmt) Description Returns the number of rows in the result set.

The use of mysql_stmt_num_rows() depends on whether or not you used mysql_stmt_store_result() tobuffer the entire result set in the statement handle.

If you use mysql_stmt_store_result(), mysql_stmt_num_rows() may be called immediately. This function was added in MySQL 4.1.1.

Return Values The number of rows in the result set.

Errors None.

24.2.7.18. mysql_stmt_param_count() unsigned long mysql_stmt_param_count(MYSQL_STMT *stmt) Description Returns the number of parameter markers present in the prepared statement. This function was added in MySQL 4.1.2.

Return Values An unsigned long integer representing the number of parameters in a statement.

Errors None.

Example

For the usage of mysql_stmt_param_count(), refer to the Example from Sección 24.2.7.10, “ mysql_stmt_execute() ” . 24.2.7.19. mysql_stmt_param_metadata() MYSQL_RES *mysql_stmt_param_metadata(MYSQL_STMT *stmt)

To be added. This function was added in MySQL 4.1.2.

Description

Return Values

Errors

24.2.7.20. mysql_stmt_prepare() int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length) Description

Given the statement handle returned by mysql_stmt_init(), prepares the SQL statement pointed to by thestring query and returns a status value. The string length should be given by the length argument. Thestring must consist of a single SQL statement. You should not add a terminating semicolon (';') or \g to thestatement. The application can include one or more parameter markers in the SQL statement by embedding questionmark ('?') characters into the SQL string at the appropriate positions. The markers are legal only in certain places in SQL statements. For example, they are allowed in theVALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with acolumn in a WHERE clause to specify a comparison value. However, they are not allowed for identifiers(such as table or column names), or to specify both operands of a binary operator such as the = equal sign.The latter restriction is necessary because it would be impossible to determine the parameter type. Ingeneral, parameters are legal only in Data Manipulation Language (DML) statements, and not in DataDefinition Language (DDL) statements.

The parameter markers must be bound to application variables using mysql_stmt_bind_param() beforeexecuting the statement. This function was added in MySQL 4.1.2.

Return Values Zero if the statement was prepared successfully. Non-zero if an error occurred.





The connection to the server was lost during the query CR_UNKNOWN_ERROR


If the prepare operation was unsuccessful (that is, mysql_stmt_prepare() returns non-zero), the errormessage can be obtained by calling mysql_stmt_error(). Example

For the usage of mysql_stmt_prepare(), refer to the Example from Sección 24.2.7.10, “ mysql_stmt_execute() ” . 24.2.7.21. mysql_stmt_reset() my_bool mysql_stmt_reset(MYSQL_STMT *stmt)

Description Reset the prepared statement on the client and server to state after prepare. This is mainly used to resetdata sent with mysql_stmt_send_long_data(). Any open cursor for the statement is closed.

To re-prepare the statement with another query, use mysql_stmt_prepare(). This function was added in MySQL 4.1.1. Cursor support was added in MySQL 5.

Return Values Zero if the statement was reset successfully. Non-zero if an error occurred.




The connection to the server was lost during the query CR_UNKNOWN_ERROR


24.2.7.22. mysql_stmt_result_metadata() MYSQL_RES *mysql_stmt_result_metadata(MYSQL_STMT *stmt) Description

If a statement passed to mysql_stmt_prepare() is one that produces a result set,mysql_stmt_result_metadata() returns the result set metadata in the form of a pointer to a MYSQL_RESstructure that can be used to process the meta information such as total number of fields and individual fieldinformation. This result set pointer can be passed as an argument to any of the field-based API functionsthat process result set metadata, such as:

mysql_num_fields() mysql_fetch_field() mysql_fetch_field_direct() mysql_fetch_fields() mysql_field_count() mysql_field_seek() mysql_field_tell() mysql_free_result()

The result set structure should be freed when you are done with it, which you can do by passing it tomysql_free_result(). This is similar to the way you free a result set obtained from a call tomysql_store_result(). The result set returned by mysql_stmt_result_metadata() contains only metadata. It does not contain anyrow results. The rows are obtained by using the statement handle with mysql_stmt_fetch(). This function was added in MySQL 4.1.2.

Return Values

A MYSQL_RES result structure. NULL if no meta information exists for the prepared query.

Errors CR_OUT_OF_MEMORY



Example

For the usage of mysql_stmt_result_metadata(), refer to the Example from Sección 24.2.7.13, “ mysql_stmt_fetch() ” . 24.2.7.23. mysql_stmt_row_seek() MYSQL_ROW_OFFSET mysql_stmt_row_seek(MYSQL_STMT *stmt, MYSQL_ROW_OFFSET offset) Description

Sets the row cursor to an arbitrary row in a statement result set. The offset value is a row offset that shouldbe a value returned from mysql_stmt_row_tell() or from mysql_stmt_row_seek(). This value is not a rownumber; if you want to seek to a row within a result set by number, use mysql_stmt_data_seek() instead. This function requires that the result set structure contains the entire result of the query, somysql_stmt_row_seek() may be used only in conjunction with mysql_stmt_store_result(). This function was added in MySQL 4.1.1.

Return Values The previous value of the row cursor. This value may be passed to a subsequent call tomysql_stmt_row_seek(). Errors None.

24.2.7.24. mysql_stmt_row_tell() MYSQL_ROW_OFFSET mysql_stmt_row_tell(MYSQL_STMT *stmt) Description

Returns the current position of the row cursor for the last mysql_stmt_fetch(). This value can be used asan argument to mysql_stmt_row_seek(). You should use mysql_stmt_row_tell() only after mysql_stmt_store_result(). This function was added in MySQL 4.1.1.

Return Values The current offset of the row cursor.

Errors None.

24.2.7.25. mysql_stmt_send_long_data() my_bool mysql_stmt_send_long_data(MYSQL_STMT *stmt, unsigned int parameter_number, constchar *data, unsigned long length) Description Allows an application to send parameter data to the server in pieces (or “chunks”). This function can becalled multiple times to send the parts of a character or binary data value for a column, which must be oneof the TEXT or BLOB data types.

parameter_number indicates which parameter to associate the data with. Parameters are numberedbeginning with 0. data is a pointer to a buffer containing data to be sent, and length indicates the numberof bytes in the buffer.

Note: The next mysql_stmt_execute() call ignores the bind buffer for all parameters that have been usedwith mysql_stmt_send_long_data() since last mysql_stmt_execute() or mysql_stmt_reset().

If you want to reset/forget the sent data, you can do it with mysql_stmt_reset(). See Sección 24.2.7.21, “ mysql_stmt_reset() ” . This function was added in MySQL 4.1.2.

Return Values Zero if the data is sent successfully to server. Non-zero if an error occurred.



The MySQL server has gone away. CR_OUT_OF_MEMORY



Example

The following example demonstrates how to send the data for a TEXT column in chunks. It inserts the datavalue 'MySQL - The most popular Open Source database' into the text_column column. The mysqlvariable is assumed to be a valid connection handle. #define INSERT_QUERY "INSERT INTO test_long_data(text_column) VALUES(?)"

MYSQL_BIND bind[1];long length;

smtt = mysql_stmt_init(mysql);if (!stmt){ fprintf(stderr, " mysql_stmt_init(), out of memory\n"); exit(0);}if (mysql_stmt_prepare(stmt, INSERT_QUERY, strlen(INSERT_QUERY))){ fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed"); fprintf(stderr, "\n %s", mysql_stmt_error(stmt)); exit(0);} memset(bind, 0, sizeof(bind)); bind[0].buffer_type= MYSQL_TYPE_STRING; bind[0].length= &length; bind[0].is_null= 0;

/* Bind the buffers */

if (mysql_stmt_bind_param(stmt, bind)){ fprintf(stderr, "\n param bind failed"); fprintf(stderr, "\n %s", mysql_stmt_error(stmt)); exit(0);}

/* Supply data in chunks to server */ if (!mysql_stmt_send_long_data(stmt,0,"MySQL",5)){ fprintf(stderr, "\n send_long_data failed"); fprintf(stderr, "\n %s", mysql_stmt_error(stmt)); exit(0);}

/* Supply the next piece of data */ if (mysql_stmt_send_long_data(stmt,0," - The most popular Open Source database",40)){ fprintf(stderr, "\n send_long_data failed"); fprintf(stderr, "\n %s", mysql_stmt_error(stmt)); exit(0);}

/* Now, execute the query */ if (mysql_stmt_execute(stmt)){ fprintf(stderr, "\n mysql_stmt_execute failed"); fprintf(stderr, "\n %s", mysql_stmt_error(stmt)); exit(0);}

24.2.7.26. mysql_stmt_sqlstate() const char *mysql_stmt_sqlstate(MYSQL_STMT *stmt) Description

For the statement specified by stmt, mysql_stmt_sqlstate() returns a null-terminated string containing theSQLSTATE error code for the most recently invoked prepared statement API function that can succeed orfail. The error code consists of five characters. "00000" means “no error.” The values are specified by ANSISQL and ODBC. For a list of possible values, see Capítulo 26, Manejo de errores en MySQL .

Note that not all MySQL errors are yet mapped to SQLSTATE's. The value "HY000" (general error) is usedfor unmapped errors. This function was added to MySQL 4.1.1.

Return Values A null-terminated character string containing the SQLSTATE error code.

24.2.7.27. mysql_stmt_store_result() int mysql_stmt_store_result(MYSQL_STMT *stmt) Description

You must call mysql_stmt_store_result() for every statement that successfully produces a result set(SELECT, SHOW, DESCRIBE, EXPLAIN), and only if you want to buffer the complete result set by theclient, so that the subsequent mysql_stmt_fetch() call returns buffered data.

It is unnecessary to call mysql_stmt_store_result() for other statements, but if you do, it does not harm orcause any notable performance problem. You can detect whether the statement produced a result set bychecking if mysql_stmt_result_metadata() returns NULL. For more information, refer toSección 24.2.7.22, “ mysql_stmt_result_metadata() ” . Note: MySQL doesn't by default calculate MYSQL_FIELD->max_length for all columns inmysql_stmt_store_result() because calculating this would slow down mysql_stmt_store_result()considerably and most applications doesn't need max_length. If you want max_length to be updated, youcan call mysql_stmt_attr_set(MYSQL_STMT, STMT_ATTR_UPDATE_MAX_LENGTH, &flag) to enablethis. See Sección 24.2.7.3, “ mysql_stmt_attr_set() ” . This function was added in MySQL 4.1.0.

Return Values Zero if the results are buffered successfully. Non-zero if an error occurred.







24.2.8. Problemas con sentencias preparadas de la API CHere follows a list of the currently known problems with prepared statements:

TIME, TIMESTAMP, and DATETIME don't support sub seconds (for example from DATE_FORMAT().

When converting an integer to string, ZEROFILL is honored with prepared statements in somecases where the MySQL server doesn't print the leading zeros. (For example, with MIN(number-with-zerofill)).

When converting a floating point number to a string in the client, the value may be slightly differentin the last digits.

Prepared statements do not use the Query Cache, even in cases where a query does not containany placeholders. See Sección 5.12.1, “Cómo opera la caché de consultas” .

24.2.9. Tratamiento por parte de la API C de la ejecución de múltiples consultasFrom version 4.1, MySQL supports the execution of multiple statements specified in a single query string.To use this capability with a given connection, you must specify the CLIENT_MULTI_STATEMENTS optionin the flags parameter of mysql_real_connect() when opening the connection. You can also set this for anexisting connection by calling mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)

By default, mysql_query() and mysql_real_query() return only the first query status and the subsequentqueries status can be processed using mysql_more_results() and mysql_next_result(). /* Connect to server with option CLIENT_MULTI_STATEMENTS */mysql_real_connect(..., CLIENT_MULTI_STATEMENTS);

/* Now execute multiple queries */mysql_query(mysql,"DROP TABLE IF EXISTS test_table;\ CREATE TABLE test_table(id INT);\ INSERT INTO test_table VALUES(10);\ UPDATE test_table SET id=20 WHERE id=10;\ SELECT * FROM test_table;\ DROP TABLE test_table");do{ /* Process all results */ ... printf("total affected rows: %lld", mysql_affected_rows(mysql)); ... if (!(result= mysql_store_result(mysql))) { printf(stderr, "Got fatal error processing query\n"); exit(1); } process_result_set(result); /* client function */ mysql_free_result(result);} while (!mysql_next_result(mysql));

The multiple-statement capability can be used with mysql_query() or mysql_real_query(). It cannot beused with the prepared statement interface. Prepared statement handles are defined to work only withstrings that contain a single statement.

24.2.10. Manejo de valores de fecha y hora por parte de la API CThe new binary protocol available in MySQL 4.1 and above allows you to send and receive date and timevalues (DATE, TIME, DATETIME, and TIMESTAMP), using the MYSQL_TIME structure. The members ofthis structure are described in Sección 24.2.5, “Tipos de datos de sentencias preparadas de la API C” .

To send temporal data values, you create a prepared statement with mysql_stmt_prepare(). Then, beforecalling mysql_stmt_execute() to execute the statement, use the following procedure to set up eachtemporal parameter:

1. In the MYSQL_BIND structure associated with the data value, set the buffer_type member to thetype that indicates what kind of temporal value you're sending. For DATE, TIME, DATETIME, orTIMESTAMP values, set buffer_type to MYSQL_TYPE_DATE, MYSQL_TYPE_TIME,MYSQL_TYPE_DATETIME, or MYSQL_TYPE_TIMESTAMP, respectively.

2. Set the buffer member of the MYSQL_BIND structure to the address of the MYSQL_TIME structurein which you pass the temporal value.

3. Fill in the members of the MYSQL_TIME structure that are appropriate for the type of temporalvalue you're passing.

Use mysql_stmt_bind_param() to bind the parameter data to the statement. Then you can callmysql_stmt_execute(). To retrieve temporal values, the procedure is similar, except that you set the buffer_type member to thetype of value you expect to receive, and the buffer member to the address of a MYSQL_TIME structureinto which the returned value should be placed. Use mysql_bind_results() to bind the buffers to thestatement after calling mysql_stmt_execute() and before fetching the results.

Here is a simple example that inserts DATE, TIME, and TIMESTAMP data. The mysql variable is assumedto be a valid connection handle. MYSQL_TIME ts; MYSQL_BIND bind[3]; MYSQL_STMT *stmt;

strmov(query, "INSERT INTO test_table(date_field, time_field, timestamp_field) VALUES(?,?,?");

stmt = mysql_stmt_init(mysql); if (!stmt) { fprintf(stderr, " mysql_stmt_init(), out of memory\n"); exit(0); } if (mysql_stmt_prepare(mysql, query, strlen(query))) { fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed"); fprintf(stderr, "\n %s", mysql_stmt_error(stmt)); exit(0); }

/* set up input buffers for all 3 parameters */ bind[0].buffer_type= MYSQL_TYPE_DATE; bind[0].buffer= (char *)&ts; bind[0].is_null= 0; bind[0].length= 0; ... bind[1]= bind[2]= bind[0]; ...

mysql_stmt_bind_param(stmt, bind);

/* supply the data to be sent in the ts structure */ ts.year= 2002; ts.month= 02;

ts.day= 03;

ts.hour= 10; ts.minute= 45; ts.second= 20;

mysql_stmt_execute(stmt); ..

24.2.11. Descripción de funciones de la API C para el control de subprocesosYou need to use the following functions when you want to create a threaded client. See Sección 24.2.15, “Cómo hacer un cliente multihilo”.

24.2.11.1. my_init() void my_init(void) Description This function needs to be called once in the program before calling any MySQL function. This initializessome global variables that MySQL needs. If you are using a thread-safe client library, this also callsmysql_thread_init() for this thread.

This is automatically called by mysql_init(), mysql_library_init(), mysql_server_init() andmysql_connect(). Return Values None.

24.2.11.2. mysql_thread_init() my_bool mysql_thread_init(void) Description This function needs to be called for each created thread to initialize thread-specific variables.

This is automatically called by my_init() and mysql_connect(). Return Values Zero if successful. Non-zero if an error occurred.

24.2.11.3. mysql_thread_end() void mysql_thread_end(void) Description

This function needs to be called before calling pthread_exit() to free memory allocated bymysql_thread_init(). Note that this function is not invoked automatically by the client library. It must be called explicitly to avoida memory leak.

Return Values None.

24.2.11.4. mysql_thread_safe() unsigned int mysql_thread_safe(void) Description This function indicates whether the client is compiled as thread-safe.

Return Values 1 is the client is thread-safe, 0 otherwise.

24.2.12. Descripción de las funciones de la API C del servidor incrustado (embedded)24.2.12.1. mysql_server_init() int mysql_server_init(int argc, char **argv, char **groups) Description

This function must be called once in the program using the embedded server before calling any otherMySQL function. It starts the server and initializes any subsystems (mysys, InnoDB, etc.) that the serveruses. If this function is not called, the next call to mysql_init() executes mysql_server_init(). If you areusing the DBUG package that comes with MySQL, you should call this after you have called my_init(). The argc and argv arguments are analogous to the arguments to main(). The first element of argv isignored (it typically contains the program name). For convenience, argc may be 0 (zero) if there are nocommand-line arguments for the server. mysql_server_init() makes a copy of the arguments so it's safe todestroy argv or groups after the call. If you want to connect to an external server without starting the embedded server, you have to specify anegative value for argc.

The NULL-terminated list of strings in groups selects which groups in the option files are active. SeeSección 4.3.2, “Usar ficheros de opciones” . For convenience, groups may be NULL, in which case the[server] and [embedded] groups are active.

Example #include <mysql.h>#include <stdlib.h>

static char *server_args[] = { "this_program", /* this string is not used */ "--datadir=.", "--key_buffer_size=32M"};static char *server_groups[] = { "embedded", "server", "this_program_SERVER", (char *)NULL};

int main(void) { if (mysql_server_init(sizeof(server_args) / sizeof(char *), server_args, server_groups)) exit(1);

/* Use any MySQL API functions here */

mysql_server_end();

return EXIT_SUCCESS;}

Return Values 0 if okay, 1 if an error occurred. If you want to allow your application to be linked against the embedded MySQL server library, you must usethe mysql_server_init() and mysql_server_end() functions. See Sección 24.2.16, “libmysqld, la biblioteca del servidor MySQL incrustado (embedded)”.

However, to provide improved memory management, even programs that are linked with -lmysqlclientrather than -lmysqld should include calls to begin and end use of the library. As of MySQL 4.1.10 and5.0.3, the mysql_library_init() and mysql_library_end() functions can be used to do this. These actuallyare #define symbols that make them equivalent to mysql_server_init() and mysql_server_end(), but thenames more clearly indicate that they should be called when beginning and ending use of a MySQL C APIlibrary no matter whether the application uses libmysqlclient or libmysqld. For more information, seeSección 24.2.2, “Panorámica de funciones de la API C” .

24.2.12.2. mysql_server_end() void mysql_server_end(void) Description

This function must be called once in the program after all other MySQL functions. It shuts down theembedded server.

Return Values None.

24.2.13. Preguntas y problemas comunes en el uso de la API C

24.2.13.1. ¿Por qué mysql_store_result() a veces devuelve NULL después de que mysql_query()haya dado un resultado?It is possible for mysql_store_result() to return NULL following a successful call to mysql_query(). Whenthis happens, it means one of the following conditions occurred:

There was a malloc() failure (for example, if the result set was too large).

The data couldn't be read (an error occurred on the connection). The query returned no data (for example, it was an INSERT, UPDATE, or DELETE).

You can always check whether the statement should have produced a non-empty result by callingmysql_field_count(). If mysql_field_count() returns zero, the result is empty and the last query was astatement that does not return values (for example, an INSERT or a DELETE). If mysql_field_count()returns a non-zero value, the statement should have produced a non-empty result. See the description ofthe mysql_field_count() function for an example.

You can test for an error by calling mysql_error() or mysql_errno().

24.2.13.2. Qué resultados se puede obtener de una consultaIn addition to the result set returned by a query, you can also get the following information:

mysql_affected_rows() returns the number of rows affected by the last query when doing anINSERT, UPDATE, or DELETE.

In MySQL 3.23, there is an exception when DELETE is used without a WHERE clause. In this case,the table is re-created as an empty table and mysql_affected_rows() returns zero for the numberof records affected. In MySQL 4.0, DELETE always returns the correct number of rows deleted. Fora fast recreate, use TRUNCATE TABLE.

mysql_num_rows() returns the number of rows in a result set. With mysql_store_result(),mysql_num_rows() may be called as soon as mysql_store_result() returns. Withmysql_use_result(), mysql_num_rows() may be called only after you have fetched all the rowswith mysql_fetch_row().

mysql_insert_id() returns the ID generated by the last query that inserted a row into a table with anAUTO_INCREMENT index. See Sección 24.2.3.34, “ mysql_insert_id() ” .

Some queries (LOAD DATA INFILE ..., INSERT INTO ... SELECT ..., UPDATE) return additionalinformation. The result is returned by mysql_info(). See the description for mysql_info() for theformat of the string that it returns. mysql_info() returns a NULL pointer if there is no additionalinformation.

24.2.13.3. Cómo obtener el ID único del último registro insertadoIf you insert a record into a table that contains an AUTO_INCREMENT column, you can obtain the valuestored into that column by calling the mysql_insert_id() function.

You can check from your C applications whether a value was stored into an AUTO_INCREMENT column byexecuting the following code (which assumes that you've checked that the statement succeeded). Itdetermines whether the query was an INSERT with an AUTO_INCREMENT index: if ((result = mysql_store_result(&mysql)) == 0 && mysql_field_count(&mysql) == 0 && mysql_insert_id(&mysql) != 0){ used_id = mysql_insert_id(&mysql);}

For more information, see Sección 24.2.3.34, “ mysql_insert_id() ” .

When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing aSELECT LAST_INSERT_ID() statement with mysql_query() and retrieving the value from the result setreturned by the statement.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connectionbasis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENTcolumn with a non-magic value (that is, a value that is not NULL and not 0). If you want to use the ID that was generated for one table and insert it into a second table, you can useSQL statements like this: INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULLINSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table

Note that mysql_insert_id() returns the value stored into an AUTO_INCREMENT column, whether thatvalue is automatically generated by storing NULL or 0 or was specified as an explicit value.LAST_INSERT_ID() returns only automatically generated AUTO_INCREMENT values. If you store anexplicit value other than NULL or 0, it does not affect the value returned by LAST_INSERT_ID(). 24.2.13.4. Problemas enlazando con la API CWhen linking with the C API, the following errors may occur on some systems: gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl

Undefined first referenced symbol in filefloor /usr/local/lib/mysql/libmysqlclient.a(password.o)

ld: fatal: Symbol referencing errors. No output written to client

If this happens on your system, you must include the math library by adding -lm to the end of thecompile/link line.

24.2.14. Generar programas clienteIf you compile MySQL clients that you've written yourself or that you obtain from a third-party, they must belinked using the -lmysqlclient -lz option on the link command. You may also need to specify a -L option totell the linker where to find the library. For example, if the library is installed in /usr/local/mysql/lib, use -L/usr/local/mysql/lib -lmysqlclient -lz on the link command.

For clients that use MySQL header files, you may need to specify a -I option when you compile them (forexample, -I/usr/local/mysql/include), so the compiler can find the header files. To make it simpler to compile MySQL programs on Unix, we have provided the mysql_config script for you.See Sección 24.1.2, “mysql_config — ” . You can use it to compile a MySQL client as follows: CFG=/usr/local/mysql/bin/mysql_configsh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"

The sh -c is needed to get the shell not to treat the output from mysql_config as one word.

24.2.15. Cómo hacer un cliente multihiloThe client library is almost thread-safe. The biggest problem is that the subroutines in net.c that read fromsockets are not interrupt safe. This was done with the thought that you might want to have your own alarmthat can break a long read to a server. If you install interrupt handlers for the SIGPIPE interrupt, the sockethandling should be thread-safe.

New in 4.0.16: To not abort the program when a connection terminates, MySQL blocks SIGPIPE on the firstcall to mysql_server_init(), mysql_init() or mysql_connect(). If you want to have your own SIGPIPEhandler, you should first call mysql_server_init() and then install your handler. In older versions of MySQLSIGPIPE was blocked, but only in the thread safe client library, for every call to mysql_init(). In the older binaries we distribute on our Web site (http://www.mysql.com/), the client libraries are notnormally compiled with the thread-safe option (the Windows binaries are by default compiled to be thread-safe). Newer binary distributions should have both a normal and a thread-safe client library. To get a threaded client where you can interrupt the client from other threads and set timeouts when talkingwith the MySQL server, you should use the -lmysys, -lmystrings, and -ldbug libraries and the net_serv.ocode that the server uses.

If you don't need interrupts or timeouts, you can just compile a thread-safe client library (mysqlclient_r)and use this. See Sección 24.2, “La API C de MySQL” . In this case, you don't have to worry about thenet_serv.o object file or the other MySQL libraries. When using a threaded client and you want to use timeouts and interrupts, you can make great use of theroutines in the thr_alarm.c file. If you are using routines from the mysys library, the only thing you mustremember is to call my_init() first! See Sección 24.2.11, “Descripción de funciones de la API C para el control de subprocesos”.

All functions except mysql_real_connect() are by default thread-safe. The following notes describe how tocompile a thread-safe client library and use it in a thread-safe manner. (The notes below formysql_real_connect() actually apply to mysql_connect() as well, but because mysql_connect() isdeprecated, you should be using mysql_real_connect() anyway.)

To make mysql_real_connect() thread-safe, you must recompile the client library with this command: shell> ./configure --enable-thread-safe-client

This creates a thread-safe client library libmysqlclient_r. (Assuming that your OS has a thread-safegethostbyname_r() function.) This library is thread-safe per connection. You can let two threads share thesame connection with the following caveats:

Two threads can't send a query to the MySQL server at the same time on the same connection. Inparticular, you have to ensure that between a mysql_query() and mysql_store_result() no otherthread is using the same connection.

Many threads can access different result sets that are retrieved with mysql_store_result(). If you use mysql_use_result, you have to ensure that no other thread is using the same connection

until the result set is closed. However, it really is best for threaded clients that share the sameconnection to use mysql_store_result().

If you want to use multiple threads on the same connection, you must have a mutex lock aroundyour mysql_query() and mysql_store_result() call combination. Once mysql_store_result() isready, the lock can be released and other threads may query the same connection.

If you program with POSIX threads, you can use pthread_mutex_lock() andpthread_mutex_unlock() to establish and release a mutex lock.

You need to know the following if you have a thread that is calling MySQL functions which did not create theconnection to the MySQL database:

When you call mysql_init() or mysql_connect(), MySQL creates a thread-specific variable for the threadthat is used by the debug library (among other things).

If you call a MySQL function, before the thread has called mysql_init() or mysql_connect(), the threaddoes not have the necessary thread-specific variables in place and you are likely to end up with a coredump sooner or later. The get things to work smoothly you have to do the following:

1. Call my_init() at the start of your program if it calls any other MySQL function before callingmysql_real_connect().

2. Call mysql_thread_init() in the thread handler before calling any MySQL function.

3. In the thread, call mysql_thread_end() before calling pthread_exit(). This frees the memory usedby MySQL thread-specific variables.

You may get some errors because of undefined symbols when linking your client with libmysqlclient_r. Inmost cases this is because you haven't included the thread libraries on the link/compile line.

24.2.16. libmysqld, la biblioteca del servidor MySQL incrustado (embedded)24.2.16.1. Panorámica de la librería del servidor MySQL incrustado (embedded)The embedded MySQL server library makes it possible to run a full-featured MySQL server inside a clientapplication. The main benefits are increased speed and more simple management for embeddedapplications. The embedded server library is based on the client/server version of MySQL, which is written in C/C++.Consequently, the embedded server also is written in C/C++. There is no embedded server available inother languages. The API is identical for the embedded MySQL version and the client/server version. To change an oldthreaded application to use the embedded library, you normally only have to add calls to the followingfunctions:

Function When to Call

mysql_server_init()

Should be called before any other MySQL function is called, preferablyearly in the main() function.

mysql_server_end()

Should be called before your program exits.

mysql_thread_init()

Should be called in each thread you create that accesses MySQL.

mysql_thread_end()

Should be called before calling pthread_exit()

Then you must link your code with libmysqld.a instead of libmysqlclient.a.

The mysql_server_xxx() functions are also included in libmysqlclient.a to allow you to change between theembedded and the client/server version by just linking your application with the right library. SeeSección 24.2.12.1, “ mysql_server_init() ” .

24.2.16.2. Compilar programas con libmysqld To get a libmysqld library you should configure MySQL with the --with-embedded-server option. SeeSección 2.8.2, “Opciones típicas de configure ” .

When you link your program with libmysqld, you must also include the system-specific pthread librariesand some libraries that the MySQL server uses. You can get the full list of libraries by executingmysql_config --libmysqld-libs. The correct flags for compiling and linking a threaded program must be used, even if you do not directly callany thread functions in your code. To compile a C program to include the necessary files to embed the MySQL server library into a compiledversion of a program, use the GNU C compiler (gcc). The compiler will need to know where to find variousfiles and need instructions on how to compile the program. Below is an example of how a program could becompiled from the command-line: gcc mysql_test.c -o mysql_test -lz \`/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`

Immediately following the gcc command is the name of the uncompiled C program file. After it, the -o optionis given to indicate that the file name that follows is the name that the compiler is to give to the output file,the compiled program. The next line of code tells the compiler to obtain the location of the include files andlibraries and other settings for the system on which it's compiled. Because of a problem with mysql_config,the option -lz (for compression) is added here. The mysql_config piece is contained in backticks, not singlequotes.

24.2.16.3. Restricciones cuando se utiliza el servidor MySQL incrustado (embedded)The embedded server has the following limitations:

No support for ISAM tables. (This is mainly done to make the library smaller)

No user-defined functions (UDFs). No stack trace on core dump. No internal RAID support. (This is not normally needed as most current operating systems support

big files). You cannot set this up as a master or a slave (no replication). You cannot connect to an embedded server from an outside process with sockets or TCP/IP.

However, you can connect to an intermediate application, which in turn can connect to an embeddedserver on the behalf of a remote client or outside process.

Some of these limitations can be changed by editing the mysql_embed.h include file and recompilingMySQL.

24.2.16.4. Opciones con el servidor incrustado (embedded)Any options that may be given with the mysqld server daemon, may be used with an embedded serverlibrary. Server options may be given in an array as an argument to the mysql_server_init(), whichinitializes the server. They also may be given in an option file like my.cnf. To specify an option file for a Cprogram, use the --defaults-file option as one of the elements of the second argument of themysql_server_init() function. See Sección 24.2.12.1, “ mysql_server_init() ” for more information on themysql_server_init() function. Using option files can make it easier to switch between a client/server application and one where MySQL isembedded. Put common options under the [server] group. These are read by both MySQL versions.

Client/server-specific options should go under the [mysqld] section. Put options specific to the embeddedMySQL server library in the [embedded] section. Options specific to applications go under section labeled[ApplicationName_SERVER]. See Sección 4.3.2, “Usar ficheros de opciones” .

24.2.16.5. Cosas por hacer (TODO) en el servidor incrustado (embedded) We are going to provide options to leave out some parts of MySQL to make the library smaller. There is still a lot of speed optimization to do. Errors are written to stderr. We will add an option to specify a filename for these.

We have to change InnoDB not to be so verbose when using the embedded version. If yourdatabase does not contain InnoDB tables, to suppress related messages you can add the --skip-innodb option to the options file under the group [libmysqd_server], or when initializing the serverwith mysql_server_init().

24.2.16.6. Ejemplos de servidor incrustado (embedded)These two example programs should work without any changes on a Linux or FreeBSD system. For otheroperating systems, minor changes are needed, mostly with file paths. These examples are designed to giveenough details for you to understand the problem, without the clutter that is a necessary part of a realapplication. The first example is very straightforward. The second example is a little more advanced withsome error checking. The first is followed by a command-line entry for compiling the program. The secondis followed by a GNUmake file that may be used for compiling instead.

Example 1 test1_libmysqld.c #include <stdio.h>#include <stdlib.h>#include <stdarg.h>#include "mysql.h"

MYSQL *mysql;MYSQL_RES *results;MYSQL_ROW record;

static char *server_options[] = { "mysql_test", "--defaults-file=my.cnf" };int num_elements = sizeof(server_options)/ sizeof(char *);

static char *server_groups[] = { "libmysqld_server", "libmysqld_client" };

int main(void){ mysql_server_init(num_elements, server_options, server_groups); mysql = mysql_init(NULL); mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client"); mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

mysql_real_connect(mysql, NULL,NULL,NULL, "database1", 0,NULL,0);

mysql_query(mysql, "SELECT column1, column2 FROM table1");

results = mysql_store_result(mysql);

while((record = mysql_fetch_row(results))) { printf("%s - %s \n", record[0], record[1]); }

mysql_free_result(results); mysql_close(mysql); mysql_server_end();

return 0;}Here is the command line for compiling the above program: gcc test1_libmysqld.c -o test1_libmysqld -lz \ `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs`

Example 2 To try out the example, create an test2_libmysqld directory at the same level as the mysql-4.0 sourcedirectory. Save the test2_libmysqld.c source and the GNUmakefile in the directory, and run GNU makefrom inside the test2_libmysqld directory. test2_libmysqld.c /* * A simple example client, using the embedded MySQL server library*/

#include <mysql.h>#include <stdarg.h>#include <stdio.h>#include <stdlib.h>

MYSQL *db_connect(const char *dbname);void db_disconnect(MYSQL *db);void db_do_query(MYSQL *db, const char *query);

const char *server_groups[] = { "test2_libmysqld_SERVER", "embedded", "server", NULL};

int

main(int argc, char **argv){ MYSQL *one, *two;

/* mysql_server_init() must be called before any other mysql * functions. * * You can use mysql_server_init(0, NULL, NULL), and it * initializes the server using groups = { * "server", "embedded", NULL * }. * * In your $HOME/.my.cnf file, you probably want to put:

[test2_libmysqld_SERVER]language = /path/to/source/of/mysql/sql/share/english

* You could, of course, modify argc and argv before passing * them to this function. Or you could create new ones in any * way you like. But all of the arguments in argv (except for * argv[0], which is the program name) should be valid options * for the MySQL server. * * If you link this client against the normal mysqlclient * library, this function is just a stub that does nothing. */ mysql_server_init(argc, argv, (char **)server_groups);

one = db_connect("test"); two = db_connect(NULL);

db_do_query(one, "SHOW TABLE STATUS"); db_do_query(two, "SHOW DATABASES");

mysql_close(two); mysql_close(one);

/* This must be called after all other mysql functions */ mysql_server_end();

exit(EXIT_SUCCESS);

}

static voiddie(MYSQL *db, char *fmt, ...){ va_list ap; va_start(ap, fmt); vfprintf(stderr, fmt, ap); va_end(ap); (void)putc('\n', stderr); if (db) db_disconnect(db); exit(EXIT_FAILURE);}

MYSQL *db_connect(const char *dbname){ MYSQL *db = mysql_init(NULL); if (!db) die(db, "mysql_init failed: no memory"); /* * Notice that the client and server use separate group names. * This is critical, because the server does not accept the * client's options, and vice versa. */ mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test2_libmysqld_CLIENT"); if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0)) die(db, "mysql_real_connect failed: %s", mysql_error(db));

return db;}

voiddb_disconnect(MYSQL *db){ mysql_close(db);}

voiddb_do_query(MYSQL *db, const char *query)

{ if (mysql_query(db, query) != 0) goto err;

if (mysql_field_count(db) > 0) { MYSQL_RES *res; MYSQL_ROW row, end_row; int num_fields;

if (!(res = mysql_store_result(db))) goto err; num_fields = mysql_num_fields(res); while ((row = mysql_fetch_row(res))) { (void)fputs(">> ", stdout); for (end_row = row + num_fields; row < end_row; ++row) (void)printf("%s\t", row ? (char*)*row : "NULL"); (void)fputc('\n', stdout); } (void)fputc('\n', stdout); mysql_free_result(res); } else (void)printf("Affected rows: %lld\n", mysql_affected_rows(db));

return;

err: die(db, "db_do_query failed: %s [%s]", mysql_error(db), query);}GNUmakefile # This assumes the MySQL software is installed in /usr/local/mysqlinc := /usr/local/mysql/include/mysqllib := /usr/local/mysql/lib

# If you have not installed the MySQL software yet, try this instead#inc := $(HOME)/mysql-4.0/include#lib := $(HOME)/mysql-4.0/libmysqld

CC := gcc

CPPFLAGS := -I$(inc) -D_THREAD_SAFE -D_REENTRANTCFLAGS := -g -W -WallLDFLAGS := -static# You can change -lmysqld to -lmysqlclient to use the# client/server libraryLDLIBS = -L$(lib) -lmysqld -lz -lm -lcrypt

ifneq (,$(shell grep FreeBSD /COPYRIGHT 2>/dev/null))# FreeBSDLDFLAGS += -pthreadelse# Assume LinuxLDLIBS += -lpthreadendif

# This works for simple one-file test programssources := $(wildcard *.c)objects := $(patsubst %c,%o,$(sources))targets := $(basename $(sources))

all: $(targets)

clean: rm -f $(targets) $(objects) *.core

24.2.16.7. Licenciamiento del servidor incrustado (embedded)We encourage everyone to promote free software by releasing code under the GPL or a compatiblelicense. For those who are not able to do this, another option is to purchase a commercial license for theMySQL code from MySQL AB. For details, please see http://www.mysql.com/company/legal/licensing/.

24.3. API PHP de MySQL[+/-]24.3.1. Problemas comunes con MySQL y PHPPHP is a server-side, HTML-embedded scripting language that may be used to create dynamic Web pages.It is available for most operating systems and Web servers, and can access most common databases,including MySQL. PHP may be run as a separate program or compiled as a module for use with the ApacheWeb server. PHP actually provides two different MySQL API extensions:

mysql - Available for PHP versions 4 and 5, this extension is intended for use with MySQL versionsprior to MySQL 4.1. This extension does not support the improved authentication protocol used inMySQL 4.1.1 and later, nor does it support prepared statements or multiple statements. If you wishto use this extension with MySQL 4.1 and more recent versions of MySQL, you will likely want toconfigure the MySQL server to use the --old-passwords option (see Sección A.2.3, “ Client does not support authentication protocol ” ). This extension is documented on the PHP Website athttp://php.net/mysql.

mysqli - Stands for "MySQL, Improved", this extension is available only in PHP 5. It is intended foruse with MySQL 4.1.1 and later. This extension fully supports the authentication protocol used inMySQL 4.1.1 and subsequent MySQL releases as well as the Prepared Statements and MultipleStatements APIs. In addition, this extension provides an advanced, object-oriented programminginterface. You can read the documentation for the mysqli extension at http://php.net/mysqli. Ahelpful article can be found at http://www.zend.com/php5/articles/php5-mysqli.php.

The PHP distribution and documentation are available from the PHP Website.

24.3.1. Problemas comunes con MySQL y PHP Error: "Maximum Execution Time Exceeded" This is a PHP limit; go into the php.ini file and set the

maximum execution time up from 30 seconds to something higher, as needed. It is also not a badidea to double the RAM allowed per script to 16MB instead of 8MB.

Error: "Fatal error: Call to unsupported or undefined function mysql_connect() in .." This means thatyour PHP version isn't compiled with MySQL support. You can either compile a dynamic MySQLmodule and load it into PHP or recompile PHP with built-in MySQL support. This is described indetail in the PHP manual.

Error: "undefined reference to `uncompress'" This means that the client library is compiled withsupport for a compressed client/server protocol. The fix is to add -lz last when linking with-lmysqlclient.

Error: "Client does not support authentication protocol" This is most often encountered when tryingto use the older mysql extension with MySQL 4.1.1 and later. Possible solutions are: downgrade toMySQL 4.0; switch to PHP 5 and the newer mysqli extension; or configure the MySQL server with --old-passwords. (See Sección A.2.3, “ Client does not support authentication protocol ” , for moreinformation.)

24.4. La API Perl de MySQLThe Perl DBI module provides a generic interface for database access. You can write a DBI script thatworks with many different database engines without change. To use DBI, you must install the DBI module,as well as a DataBase Driver (DBD) module for each type of server you want to access. For MySQL, thisdriver is the DBD::mysql module.

Perl DBI is the recommended Perl interface. It replaces an older interface called mysqlperl, which shouldbe considered obsolete. Installation instructions for Perl DBI support are given in Sección 2.13, “Notas sobre la instalación de Perl” . DBI information is available at the command line, online, or in printed form:

Once you have the DBI and DBD::mysql modules installed, you can get information about them atthe command line with the perldoc command:

shell> perldoc DBI shell> perldoc DBI::FAQ shell> perldoc DBD::mysql

You can also use pod2man, pod2html, and so forth to translate this information into other formats.

For online information about Perl DBI, visit the DBI Web site, http://dbi.perl.org/. That site hosts ageneral DBI mailing list. MySQL AB hosts a list specifically about DBD::mysql; see Sección 1.6.1.1, “Las listas de correo de MySQL”.

For printed information, the official DBI book is Programming the Perl DBI (Alligator Descartes andTim Bunce, O'Reilly & Associates, 2000). Information about the book is available at the DBI Website, http://dbi.perl.org/. For information that focuses specifically on using DBI with MySQL, see MySQL and Perl for theWeb (Paul DuBois, New Riders, 2001). This book's Web site is http://www.kitebird.com/mysql-perl/.

24.5. API C++ de MySQLMySQL++ is a MySQL API for C++. Warren Young has taken over this project. More information can befound at http://www.mysql.com/products/mysql++/.

24.5.1. Borland C++You can compile the MySQL Windows source with Borland C++ 5.02. (The Windows source includes onlyprojects for Microsoft VC++, for Borland C++ you have to do the project files yourself.) One known problem with Borland C++ is that it uses a different structure alignment than VC++. This meansthat you run into problems if you try to use the default libmysql.dll libraries (that were compiled with VC++)with Borland C++. To avoid this problem, only call mysql_init() with NULL as an argument, not a pre-allocated MYSQL structure.

24.7. La API Tcl de MySQLMySQLtcl is a simple API for accessing a MySQL database server from the Tcl programming language. Itcan be found at http://www.xdobry.de/mysqltcl/.

24.8. El visor de MySQL EiffelEiffel MySQL is an interface to the MySQL database server using the Eiffel programming language, writtenby Michael Ravits. It can be found at http://efsa.sourceforge.net/archive/ravits/mysql.htm.

tecnocomputo.cotecnocomputo.co/almacen/root/informatica pdf/informatica_anonimo... · 24. apis de...

Documents