     [file include.texi not included]

MySQL Prepared Statements
*************************

C Prepared Statements
---------------------

From MySQL 4.1 and above, you can also make use of the prepared statements using the statement handler 'MYSQL_STMT', which supports simultanious query executions along with input and output binding.

Prepared execution is an efficient way to execute a statement more than once. The statement is first parsed, or prepared. This is executed one or more times at a later time using the statement handle that is returned during the prepare.

Another advantage of prepared statements is that, it uses a binary protocol which makes the data tranfer between client and server in a more efficient way than the old MySQL protocol.

Prepared execution is faster than direct execution for statements executed more than once, primarly becuase the query is parsed only once; In the case of direct execution, the query is parsed every time. Prepared execution also can provide a reduction in the network traffic becuase during the execute call, it only sends the data for the parameters.

C Prepared Statements DataTypes
-------------------------------

Prepared statements mainly uses the following two MYSQL_STMT and MYSQL_BIND structures:

MYSQL_STMT
     This structure represents a statement handle to prepared statements.It is used for all statement related functions.

     The statement is initialized when the query is prepared using mysql_prepare().

     One connection can have 'n' statement handles, and the limit depends up on the system resources.

MYSQL_BIND
     This structure is used in order to bind parameter buffers inorder to send the parameters data to mysql_execute() call; as well as to bind row buffers to fetch the result set data using mysql_fetch().

The MYSQL_BIND structure contains the members listed here:

enum enum_field_types buffer_type [input]
     The type of the buffer. The type value must be one of the following:

       * MYSQL_TYPE_TINY

       * MYSQL_TYPE_SHORT

       * MYSQL_TYPE_LONG

       * MYSQL_TYPE_LONGLONG

       * MYSQL_TYPE_FLOAT

       * MYSQL_TYPE_DOUBLE

       * MYSQL_TYPE_STRING

       * MYSQL_TYPE_VAR_STRING

       * MYSQL_TYPE_TINY_BLOB

       * MYSQL_TYPE_MEDIUM_BLOB

       * MYSQL_TYPE_LONG_BLOB

       * MYSQL_TYPE_BLOB

void *buffer [input/output]
     A pointer to a buffer for the parameters data in case if it is used to supply parameters data or pointer to a buffer in which to return the data when the structure is used for result set bind.

long *length [input/output]
     Pointer to the buffer for the parameter's length. When the structure is used as a input parameter data binding, then this argument points to a buffer that, when mysql_execute() is called, contains one of the following:

       * The length of the parameter value stored in *buffer. This is ignored except for character or binary C data.

       * MYSQL_NULL_DATA. The parameter value is NULL.

       * MYSQL_LONG_DATA. The parameter value is a long data and is supplied in chunks through mysql_send_long_data().

     If the length is a null pointer, then the protocol assumes that all input parameter values are non-NULL and that character and binary data are null terminated.

     When this structure is used in output binding, then mysql_fetch() return the following values in the length buffer:

       * The length of the data that is returned

       * MYSQL_NULL_DATA, indicating the data returned is a NULL data.

C Prepared Statements Function Overview
---------------------------------------

The functions available in the prepared statements are listed here and are described in greater detail in the later section. See C Prepared statement functions.

Function   Description

mysql_prepare()   Prepares an SQL string for execution.

mysql_param_count()   Returns the number of parameters in a prepared SQL statement.

mysql_prepare_result()   Returns prepared statement meta information in the form of resultset.

mysql_bind_param()   Binds a buffer to parameter markers in a prepared SQL statement.

mysql_execute()   Executes the prepared statement.

mysql_stmt_affected_rows()   Returns the number of rows changes/deleted/inserted by the last UPDATE,DELETE,or INSERT query

mysql_bind_result()   Binds application data buffers to columns in the resultset.

mysql_fetch()   Fetches the next rowset of data from the result set and returns data for all bound columns.

mysql_stmt_close()   Frees memory used by prepared statement.

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_send_long_data()   Sends long data in chunks to server.

mysql_commit()   Commits the transaction.

mysql_rollback()   Rollbacks the transaction.

mysql_autocommit()   Toggles the autocommit mode to on/off.

mysql_more_results()   Returns if there are any more results exists

mysql_next_result()   Returns/Initiates the next result in the multi-query executions

Call mysql_prepare() to prepare and initialize the statement handle, then call mysql_bind_param() to supply the parameters data, and then call mysql_execute() to execute the query. You can repeat the mysql_execute() by changing parameter values from the respective buffer supplied through mysql_bind_param().

In case if the query is a SELECT statement or any other query which results in a resultset, then mysql_prepare() will also return the result set meta data information in the form of MYSQL_RES  result set through mysql_prepare_result().

You can supply the result buffers using mysql_bind_result(), so that the mysql_fetch() will automatically returns data to this buffers. This is row by row fetching.

You can also send the text or binary data in chunks to server using mysql_send_long_data(), by specifying the option is_long_data=1 or length=MYSQL_LONG_DATA or -2 in the MYSQL_BIND structure supplied with mysql_bind_param().

Once the statement execution is over, it must be freed using mysql_stmt_close so that it frees all the alloced resources for the statement handle.

Execution Steps:
................

To prepare and execute a statement, the application:

  * Calls mysql_prepare() and passes it a string containing the SQL statement. On a successful prepare, mysql_prepare returns the valid statement handle back to the application

  * If the query results in a resultset, then mysql_prepare_result returns the result set meta info..

  * Sets the values of any parameters using mysql_bind_param. All parameters must be set; else it will return an error or produce un-expected results

  * Calls mysql_execute to execute the statement.

  * Repeat steps 2 and 3 as necessary, by changing the parameter values and re-executing the statement.

  * Bind the data buffers to return the row values, if it is a result set query; using mysql_bind_result().

  * Fetch the data to buffers row by row by calling mysql_fetch() repetedely until no more rows found.

  * When mysql_prepare() is called, in the MySQL client/server protocol:

       - Server parses the query and sends the ok status back to client by assinging a statement id. It also sends total number of parameters, columns count and its meta information if it is a result set oriented query. All syntax and symantecs of the query is checked during this call by the server.

       - Client uses this statement id for the further executions, so that server identifies the statement back from the pool of statements. Now, client allocates a statement handle with this id and returns back to application.

  * When mysql_execute() is called, in the MySQL client/server protocol:

       - Client uses the statement handle and sends the parameters data to server.

       - Server identifies the statement using the id provided by the client, and replaces the parameter markers with the newly supplied data and executes the query. If it results in a result set, then sends the data back to client, else sends an OK status with total number of rows changes/deleted/inserted.

  * When mysql_fetch() is called, in the MySQL client/server protocol:

       - Client reads the data from the packet row by row and places it to application data buffers by doing the necessary conversions. If the application buffer type is same as that of field type, then the conversions are stright forward.

You can get the statement error code and message using mysql_stmt_errno() and mysql_stmt_error() respectively.

C Prepared Statement Function Descriptions
------------------------------------------

You need to use the following functions when you want to prepare and execute the queries.

mysql_prepare()
...............

MYSQL_STMT * mysql_prepare(MYSQL *mysql, const char *query, unsigned long length)

Description
...........

Prepares the SQL query pointed to by the null-terminated string 'query'. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;`) or \g to the statement.

The application can include one or more parameter markers in the SQL statement. To include a parameter marker, the appication embeds a question mark (?) into the SQL string at the appropriate position.

The markers are legal only in certain places in SQL statements. For example, they are not allowed in the select list(the list of columns to be returned by a SELECT statement), nor are they allowed as both operands of a binary operator such as the equal sign (=), becuase it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Languange(DML) statements, and not in Data Defination Language(DDL) statements.

The parameter markers are then bound to application variables using mysql_bind_param().

Return Values
.............

MYSQL_STMT if the prepare was successful. NULL if an error occured.

Errors
......

If the prepare is not successful, i.e. when mysql_prepare() returned a NULL statement, errors can be obtained by calling mysql_error().

Example
.......

For the usage of mysql_prepare() refer to the Example from mysql_execute().

mysql_param_count()
...................

unsigned int mysql_param_count(MYSQL_STMT *stmt)

Description
...........

Returns the number of parameter markers present from the prepared query.

Return Values
.............

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

Errors
......

None

Example
.......

For the usage of mysql_param_count() refer to the Example from mysql_execute().

mysql_prepare_result()
......................

MYSQL_RES *mysql_prepare_result(MYSQL_STMT *stmt)

Description
...........

If the mysql_prepare() resulted in a result set query, then mysql_prepare_result() returns the result set meta data in the form of MYSQL_RES structure; which can further be used to process the meta information such as total number of fields and individual field information. This resulted result set can be passed as an argument to any of the field based APIs in order to process the result set meta data information such as:

  - mysql_num_fields()

  - mysql_fetch_field()

  - mysql_fetch_field_direct()

  - mysql_fetch_fields()

  - mysql_field_count()

  - mysql_field_seek()

  - mysql_field_tell() and

  - mysql_free_result()

Return Values
.............

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

Errors
......

None

Example
.......

For the usage of mysql_prepare_result() refer to the Example from mysql_fetch()

mysql_bind_param()
..................

int mysql_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)

Description
...........

mysql_bind_param is used to bind data for the parameter markers in the SQL statement from mysql_prepare. It uses the structure MYSQL_BIND to supply the data.

The supported buffer types are:

  * MYSQL_TYPE_TINY

  * MYSQL_TYPE_SHORT

  * MYSQL_TYPE_LONG

  * MYSQL_TYPE_LONGLONG

  * MYSQL_TYPE_FLOAT

  * MYSQL_TYPE_DOUBLE

  * MYSQL_TYPE_STRING

  * MYSQL_TYPE_VAR_STRING

  * MYSQL_TYPE_TINY_BLOB

  * MYSQL_TYPE_MEDIUM_BLOB

  * MYSQL_TYPE_LONG_BLOB

Return Values
.............

Zero if the bind was successful. Non-zero if an error occured.

Errors
......

CR_NO_PREPARE_STMT
     No prepared statement exists

CR_NO_PARAMETERS_EXISTS
     No parameters exists to bind

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 is illegal or its not from the above list of supported types.

Example
.......

For the usage of mysql_bind_param() refer to the Example from mysql_execute().

mysql_execute()
...............

int mysql_execute(MYSQL_STMT *stmt.

Description
...........

mysql_execute() executes the prepared query associated with the statement handle. The parameter marker values will be sent to server during this call, so that server replaces markers with this newly supplied data.

If the statement is UPDATE,DELETE,or INSERT, the total number of changed/deletd/inserted values can be found by calling mysql_stmt_affected_rows. If this is a result set query, then one must call mysql_fetch() to fetch the data prior to calling any other calls which results in query processing. For more information on how to fetch the statement binary data, refer to mysql_fetch.

Return Values
.............

mysql_execute() returns the following return values:

Return Value           Description
MYSQL_SUCCESS,      0              Successful
MYSQL_STATUS_ERROR, 1              Error occured. Error code and
message can be obtained by calling mysql_stmt_errno() and mysql_stmt_error().
MYSQL_NEED_DATA,    99             One of the parameter buffer is
indicating the data suppy in chunks, and the supply is not yet complete.

Errors
......

CR_NO_PREPARE_QUERY
     No query prepared prior to execution

CR_ALL_PARAMS_NOT_BOUND
     Not all parameters data is supplied

CR_SERVER_GONE_ERROR
     The MySQL server has gone away

CR_UNKNOWN_ERROR
     An unkown error occured

Example
.......

The following example explains the uasage of mysql_prepare, mysql_param_count, mysql_bind_param, mysql_execute and mysql_stmt_affected_rows().

     MYSQL_BIND   bind[3];
     MYSQL_STMT   *stmt;
     ulonglong    affected_rows;
     long         length;
     unsigned int param_count;
     int          int_data;
     char         str_data[50], query[255];

       /* Set autocommit mode to true */
       mysql_autocommit(mysql, 1);

       if (mysql_query(mysql,"DROP TABLE IF EXISTS test_table"))
       {
         fprintf(stderr, "\n drop table failed");
         fprintf(stderr, "\n %s", mysql_error(mysql));
         exit(0);
       }
       if (mysql_query(mysql,"CREATE TABLE test_table(col1 int, col2 varchar(50), \
                                                      col3 smallint,\
                                                      col4 timestamp(14))"))
       {
         fprintf(stderr, "\n create table failed");
         fprintf(stderr, "\n %s", mysql_error(mysql));
         exit(0);
       }

       /* Prepare a insert query with 3 parameters */
       strcpy(query, "INSERT INTO test_table(col1,col2,col3) values(?,?,?)");
       if(!(stmt = mysql_prepare(mysql,query,strlen(query))))
       {
         fprintf(stderr, "\n prepare, insert failed");
         fprintf(stderr, "\n %s", mysql_error(mysql));
         exit(0);
       }
       fprintf(stdout, "\n prepare, insert successful");

       /* Get the parameter count from the statement */
       param_count= mysql_param_count(stmt);

       fprintf(stdout, "\n total parameters in insert: %d", param_count);
       if (param_count != 3) /* validate parameter count */
       {
         fprintf(stderr, "\n invalid parameter count returned by MySQL");
         exit(0);
       }

       /* Bind the data for the parameters */

       /* INTEGER PART */
       memset(bind,0,sizeof(bind));
       bind[0].buffer_type= MYSQL_TYPE_LONG;
       bind[0].buffer= (void *)&int_data;

       /* STRING PART */
       bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
       bind[1].buffer= (void *)str_data;
       bind[1].buffer_length= sizeof(str_data);

       /* SMALLINT PART */
       bind[2].buffer_type= MYSQL_TYPE_SHORT;
       bind[2].buffer= (void *)&small_data;
       bind[2].length= (long *)&length;

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

       /* Specify the data */
       int_data= 10;             /* integer */
       strcpy(str_data,"MySQL"); /* string  */
       /* INSERT SMALLINT data as NULL */
       length= MYSQL_NULL_DATA;

       /* Execute the insert statement - 1*/
       if (mysql_execute(stmt))
       {
         fprintf(stderr, "\n execute 1 failed");
         fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
         fprintf(stderr, "\n send a bug report to bugs@lists.mysql.com, by asking why this is not working ?");
         exit(0);
       }

       /* Get the total rows affected */
       affected_rows= mysql_stmt_affected_rows(stmt);

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

       /* Re-execute the insert, by changing the values */
       int_data= 1000;
       strcpy(str_data,"The most popular open source database");
       small_data= 1000;         /* smallint */
       length= 0;

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

       /* Get the total rows affected */
       affected_rows= mysql_stmt_affected_rows(stmt);

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

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

       /* DROP THE TABLE */
       if (mysql_query(mysql,"DROP TABLE test_table"))
       {
         fprintf(stderr, "\n drop table failed");
         fprintf(stderr, "\n %s", mysql_error(mysql));
         exit(0);
       }
       fprintf(stdout, "Success, MySQL prepared statements are working great !!!");

mysql_stmt_affected_rows()
..........................

ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt)

Description
...........

Returns total number of rows changed by the last execute statement. May be called immediatlely after mysql_execute() for UPDATE,DELETE,or INSERT statements.For SELECT statements, mysql_stmt_affected rows works like mysql_num_rows().

Return Values
.............

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query, mysql_stmt_affected_rows() was called prior to calling mysql_fetch().

Errors
......

None.

Example
.......

For the usage of mysql_stmt_affected_rows() refer to the Example from mysql_execute().

mysql_bind_result()
...................

my_bool mysql_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind)

Description
...........

mysql_bind_result() is ised to associate, or bind, columns in the resultset to data buffers and length buffers. When mysql_fetch() is called to fetch data, the MySQL client protocol returns the data for the bound columns in the specified buffers.

Note that all columns must be bound prior to calling mysql_fetch() in case of fetching the data to buffers; else mysql_fetch() simply ignores the data fetch; also the buffers should be sufficient enough to hold the data as the ptotocol doesn't return the data in chunks.

A column can be bound or rebound at any time, even after data has been fetched from the result set. The new binding takes effect the next time mysql_fetch() is called. For example, suppose an application binds the columns in a result set and calls mysql_fetch(). The mysql protocol returns data in the bound buffers. Now suppose the application binds the columns to a different set of buffers, then the protocol does not place the data for the just fetched row in the newly bound buffers. Instead, it does when the next mysql_fetch() is called.

To bind a column, an application calls mysql_bind_result() and passes the type, address, and the address of the length buffer.

The supported buffer types are:

  * MYSQL_TYPE_TINY

  * MYSQL_TYPE_SHORT

  * MYSQL_TYPE_LONG

  * MYSQL_TYPE_LONGLONG

  * MYSQL_TYPE_FLOAT

  * MYSQL_TYPE_DOUBLE

  * MYSQL_TYPE_STRING

  * MYSQL_TYPE_VAR_STRING

  * MYSQL_TYPE_BLOB

  * MYSQL_TYPE_TINY_BLOB

  * MYSQL_TYPE_MEDIUM_BLOB

  * MYSQL_TYPE_LONG_BLOB

Return Values
.............

Zero if the bind was successful. Non-zero if an error occured.

Errors
......

CR_NO_PREPARE_STMT
     No prepared statement exists

CR_UNSUPPORTED_PARAM_TYPE
     The conversion is not supported, possibly the buffer_type is illegal or its not from the list of supported types.

Example
.......

For the usage of mysql_bind_result() refer to the Example from mysql_fetch()

mysql_fetch()
.............

int mysql_fetch(MYSQL_STMT *stmt)

Description
...........

mysql_fetch() returns the next rowset in the result set. It can be called only while the result set exists i.e. after a call to mysql_execute() that creates a result set.

If row buffers are bound using mysql_bind_result(), it returns the data in those buffers for all the columns in the current row set and the lengths are returned to the length pointer.

Note that, all columns must be bound by the application.

If the data fetched is a NULL data, then the length buffer will have a value of MYSQL_NULL_DATA, -1, else it will have the length of the data being fetched based on the buffer type specified by the application. All numeric, float and double types have the fixed length(in bytes) as listed below:

Type             Length
MYSQL_TYPE_TINY           1
MYSQL_TYPE_SHORT          2
MYSQL_TYPE_LONG           4
MYSQL_TYPE_FLOAT          4
MYSQL_TYPE_LONGLONG       8
MYSQL_TYPE_DOUBLE         8
MYSQL_TYPE_STRING         data length
MYSQL_TYPE_VAR_STRING     data_length
MYSQL_TYPE_BLOB           data_length
MYSQL_TYPE_TINY_BLOB      data_length
MYSQL_TYPE_MEDIUM_BLOB    data_length
MYSQL_TYPE_LONG_BLOB      data_length

where *data_length is nothing but the 'Actual length of the data'.

Return Values
.............

Return Value           Description
MYSQL_SUCCESS,      0              Successful, the data has been
fetched to application data buffers.
MYSQL_STATUS_ERROR, 1              Error occured. Error code and
message can be obtained by calling mysql_stmt_errno() and mysql_stmt_error().
MYSQL_NO_DATA,      100             No more rows/data exists

Errors
......

CR_UNSUPPORTED_PARAM_TYPE
     If the field type is DATE,DATETIME,TIME,or TIMESTAMP; and the application buffer type is non string based.

     All other un-supported conversions are returned from mysql_bind_result().

Example
.......

The following example explains the usage of mysql_prepare_result, mysql_bind_result(), and mysql_fetch()

     MYSQL_STMT *stmt;
     MYSQL_BIND bind[2];
     MYSQL_RES  *result;
     int        int_data;
     long       int_length, str_length;
     char       str_data[50];

       query= "SELECT col1, col2 FROM test_table WHERE col1= 10)");
       if (!(stmt= mysql_prepare(&mysql, query, strlen(query)))
       {
         fprintf(stderr, "\n prepare failed");
         fprintf(stderr, "\n %s", mysql_error(&stmt));
         exit(0);
       }

       /* Get the fields meta information */
       if (!(result= mysql_prepare_result(stmt)))
       {
         fprintf(stderr, "\n prepare_result failed");
         fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
         exit(0);
       }

       fprintf(stdout, "Total fields: %ld", mysql_num_fields(result));

       if (mysql_num_fields(result) != 2)
       {
         fprintf(stderr, "\n prepare returned invalid field count");
         exit(0);
       }

       /* Execute the SELECT query */
       if (mysql_execute(stmt))
       {
         fprintf(stderr, "\n execute didn't retuned expected return code, MYSQL_NEED_DATA");
         exit(0);
       }

       /* Bind the result data buffers */
       bzero(bind, 0, sizeof(bind));

       bind[0].buffer_type= MYSQL_TYPE_LONG;
       bind[0].buffer= (void *)&int_data;
       bind[0].length= &int_length;

       bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
       bind[1].buffer= (void *)str_data;
       bind[1].length= &str_length;

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

       /* Now fetch data to buffers */
       if (mysql_fetch(stmt))
       {
         fprintf(stderr, "\n fetch failed");
         fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
         exit(0);
       }

       fprintf(stdout, "\n int_data: %d, length: %ld", int_data, int_length);
       fprintf(stdout, "\n str_data: %s, length: %ld", str_data, str_length);

       /* call mysql_fetch again */
       if (mysql_fetch(stmt) |= MYSQL_NO_DATA)
       {
         fprintf(stderr, "\n fetch return more than one row);
         exit(0);
       }

       /* Free the prepare result */
       mysql_free_result(result);

       /* Free the statement handle */
       if (mysql_stmt_free(stmt))
       {
         fprintf(stderr, "\n failed to free the statement handle);
         fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
         exit(0);
       }

mysql_send_long_data()
......................

int mysql_send_long_data(MYSQL_STMT *stmt, unsigned int parameter_number, const char *data, ulong length, my_bool is_last_data)

Description
...........

Allows an application to send the data in pieces or chunks to server. This function can be used to send character or binary data values in parts to a column(it must be a text or blob) with a character or binary data type.

The data is a pointer to buffer containing the actual data for the parameter represendted by parameter_number. The length indicates the amount of data to be sent in bytes, and is_last_data is a boolean flag to indicate the end of the data. If it is != 0, then the current call will be the end of the data, else it waits for the application to send all data. If the application doesn't ended the data supply from mysql_send_long_data(), then the mysql_execute() will return MYSQL_NEED_DATA.

Return Values
.............

Zero if the data is sent successfully to server. Non-zero if an error occured.

Errors
......

CR_INVALID_PARAMETER_NO
     Invalid parameter number

CR_SERVER_GONE_ERROR
     The MySQL server has gone away

CR_UNKNOWN_ERROR
     An unkown error occured

Example
.......

The following example explains how to send the data in chunks to text column:

     MYSQL_BIND bind[1];
     long       length;

       query= "INSERT INTO test_long_data(text_column) VALUES(?)");
       if (!mysql_prepare(&mysql, query, strlen(query))
       {
         fprintf(stderr, "\n prepare failed");
         fprintf(stderr, "\n %s", mysql_error(&stmt));
         exit(0);
       }
        memset(bind, 0, sizeof(bind));
        bind[0].buffer_type= MYSQL_TYPE_STRING;
        bind[0].length= &length;

        /* Indicate that the data supply is in CHUNKS */
        length= MYSQL_LONG_DATA;

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

       /* Execute the insert statement - It should return MYSQL_NEED_DATA */
       if (mysql_execute(stmt) != MYSQL_NEED_DATA)
       {
         fprintf(stderr, "\n execute didn't retuned expected return code, MYSQL_NEED_DATA");
         exit(0);
       }

        /* Supply data in chunks to server */
        if (!mysql_send_long_data(stmt,1,"MySQL",5,0))
       {
         fprintf(stderr, "\n send_long_data failed");
         fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
         exit(0);
       }
        /* Supply the last piece of data */
        if (mysql_send_long_data(stmt,1," - The most popular open source database",40,1))
       {
         fprintf(stderr, "\n send_long_data failed");
         fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
         exit(0);
       }

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

        This inserts the data, "MySQL - The most popular open source database"
        to the field 'text_column'.

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 by stmt.

Return Values
.............

Zero if the statement was freed successfully. Non-zero if an error occured.

Errors
......

CR_SERVER_GONE_ERROR
     The MySQL server has gone away

CR_UNKNOWN_ERROR
     An unkown error occured

Example
.......

For the usage of mysql_stmt_close() refer to the Example from mysql_execute().

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 recently invoked statement API function that can succeed or fail. A return value of zero means that no error occured. Client error message numbers are listed in the MySQL errmsg.h header file. Server error message numbers are listed in 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

Return Values
.............

An error code value. Zero if no error occured.

Errors
......

None

mysql_stmt_error()
..................

char *mysql_stmt_error(MYSQL_STMT *stmt)

Description
...........

For the statement specified by stmt, mysql_stmt_error() returns the error message for the most recently invoked statement API that can succeed or fail. An empty string ("") is returned if no error occured. This means the following two sets are equivalent:

     if (mysql_stmt_errno(stmt))
     {
       // an error occured
     }

     if (mysql_stmt_error(stmt))
     {
       // an error occured
     }

The language of the client error messages many be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages.

Return Values
.............

A character string that describes the error. An empry string if no error occured.

Errors
......

None

mysql_commit()
..............

my_bool mysql_commit(MYSQL *mysql)

Description
...........

Commits the current transaction

Return Values
.............

Zero if successful. Non-zero if an error occured.

Errors
......

None

mysql_rollback()
................

my_bool mysql_rollback(MYSQL *mysql)

Description
...........

Rollbacks the current transaction.

Return Values
.............

Zero if successful. Non-zero if an error occured.

Errors
......

None.

mysql_autocommit()
..................

my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)

Description
...........

Sets the autocommit mode to on or off. If the mode is '1', then it sets the autocommit mode to on, else to off in case of '0'.

Return Values
.............

Zero if successful. Non-zero if an error occured

Errors
......

None.