Prepared statements - Doc to be incorporated in manual

1 files changed, 1422 insertions, 0 deletions

diff --git a/Docs/prepare.texi b/Docs/prepare.texi
new file mode 100755
index 00000000000..7a526800213
--- /dev/null
+++ b/Docs/prepare.texi
@@ -0,0 +1,1422 @@
+\input texinfo  @c -*-texinfo-*-

+@c Copyright 1997-2002 TcX AB, Detron HB and MySQL Finland AB

+@c

+@c This manual is NOT distributed under a GPL style license.

+@c Use of the manual is subject to the following terms:

+@c - Conversion to other formats is allowed, but the actual

+@c   content may not be altered or edited in any way.

+@c - You may create a printed copy for your own personal use.

+@c - For all other uses, such as selling printed copies or

+@c   using (parts of) the manual in another publication,

+@c   prior written agreement from MySQL AB is required.

+@c

+@c Please e-mail docs@mysql.com for more information or if

+@c you are interested in doing a translation.

+@c

+@c *********************************************************

+@c Note that @node names are used on our web site.

+@c So do not change node names without checking

+@c Makefile.am and SitePages first.

+@c *********************************************************

+@c

+@c %**start of header

+

+@c there's a better way to do this... i just don't know it yet

+@c sed will remove the "@c ifnusphere " to make this valid

+@c ifnusphere @set nusphere 1

+

+@setfilename prepare.info

+

+@c We want the types in the same index

+@c @syncodeindex tp fn

+

+@c Get version information. This file is generated by the Makefile!!

+@include include.texi

+

+@ifclear tex-debug

+@c This removes the black squares in the right margin

+@finalout

+@end ifclear

+

+@c Set background for HTML

+@set _body_tags BGCOLOR=#FFFFFF TEXT=#000000 LINK=#101090 VLINK=#7030B0

+@c Set some style elements for the manual in HTML form. 'suggested'

+@c natural language colors: aqua, black, blue, fuchsia, gray, green,

+@c lime, maroon, navy, olive, purple, red, silver, teal, white, and

+@c yellow. From Steeve Buehler <ahr@YogElements.com>

+@set _extra_head <style> code {color:purple} tt {color:green} samp {color:navy} pre {color:maroon} </style>

+

+@settitle MySQL Prepared Statements

+

+@c We want single-sided heading format, with chapters on new pages. To

+@c get double-sided format change 'on' below to 'odd'

+@ifclear nusphere

+@setchapternewpage on

+@end ifclear

+

+@ifset nusphere

+@setchapternewpage odd

+@end ifset

+

+@c @paragraphindent 0

+

+@ifset nusphere

+@smallbook

+@end ifset

+

+@c  @titlepage

+@c @sp 10

+@c @center @titlefont{MySQL Prepared Statements}

+@c @sp 10

+@c @right Copyright @copyright{} 1995-2003 MySQL AB

+@c blank page after title page makes page 1 be a page front.

+@c also makes the back of the title page blank.

+@c @page

+@c @end titlepage

+

+@c Short contents, blank page, long contents.

+@c until i can figure out the blank page, no short contents.

+@c @shortcontents

+@c @page

+@c @page

+@contents

+

+@c This should be added. The HTML conversion also needs a MySQL version

+@c number somewhere.

+

+@iftex

+@c change this to double if you want formatting for double-sided

+@c printing

+@headings single

+@end iftex

+

+@c @node Top, MySQL C API, (dir), (dir)

+

+@c @menu

+@c * MySQL C API::                 

+@c @end menu

+

+@c @node MySQL C API,  , Top, Top

+@c @chapter MySQL C API

+

+@c @menu

+@c * Prepared statements::         

+@c @end menu

+

+@node Top, MySQL prepared statements, (dir), (dir)

+

+@menu

+* MySQL prepared statements::   

+@end menu

+

+@node MySQL prepared statements,  , Top, Top

+@chapter MySQL Prepared Statements

+

+@menu

+* C Prepared statements::       

+* C Prepared statement datatypes::  

+* C Prepared statements function overview::  

+* C Prepared statement functions::  

+@end menu

+

+@node C Prepared statements, C Prepared statement datatypes, MySQL prepared statements, MySQL prepared statements

+@subsection C Prepared Statements

+

+@sp 1

+

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

+

+@sp 1

+

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

+

+@sp 1

+

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

+

+@sp 1

+

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

+

+

+

+@node C Prepared statement datatypes, C Prepared statements function overview, C Prepared statements, MySQL prepared statements

+@subsection C Prepared Statements DataTypes

+

+Prepared statements mainly uses the following two @code{MYSQL_STMT} and

+@code{MYSQL_BIND} structures:

+@sp 1

+

+@table @code

+@tindex MYSQL C type

+@item MYSQL_STMT

+This structure represents a statement handle to prepared statements.It

+is used for all statement related functions. 

+

+@sp 1 

+

+The statement is initialized when the query is prepared using 

+@code{mysql_prepare()}.

+

+@sp 1 

+

+One connection can have 'n' statement handles, and the limit depends up on

+the system resources.

+

+@sp 1

+

+@tindex MYSQL_BIND C type

+@item MYSQL_BIND

+This structure is used in order to bind parameter buffers inorder to

+send the parameters data to @code{mysql_execute()} call; as well as to

+bind row buffers to fetch the result set data using @code{mysql_fetch()}.

+@end table

+

+@sp 1

+

+@noindent

+The @code{MYSQL_BIND} structure contains the members listed here:

+

+

+@table @code

+@item enum enum_field_types buffer_type [input]

+The type of the buffer. The @code{type} value must be one of the following:

+

+

+@itemize @bullet

+@item @code{MYSQL_TYPE_TINY}

+@item @code{MYSQL_TYPE_SHORT}

+@item @code{MYSQL_TYPE_LONG}  

+@item @code{MYSQL_TYPE_LONGLONG}

+@item @code{MYSQL_TYPE_FLOAT}

+@item @code{MYSQL_TYPE_DOUBLE}

+@item @code{MYSQL_TYPE_STRING}

+@item @code{MYSQL_TYPE_VAR_STRING}

+@item @code{MYSQL_TYPE_TINY_BLOB}

+@item @code{MYSQL_TYPE_MEDIUM_BLOB}

+@item @code{MYSQL_TYPE_LONG_BLOB}

+@item @code{MYSQL_TYPE_BLOB}

+@end itemize

+@sp 1

+

+@item 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.

+

+@sp 1

+

+@item 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 @code{mysql_execute()} is called, contains one of the 

+following:

+

+@itemize @bullet

+@item

+The length of the parameter value stored in *buffer. This is ignored

+except for character or binary C data.

+@item 

+MYSQL_NULL_DATA. The parameter value is NULL.

+@item

+MYSQL_LONG_DATA. The parameter value is a long data and is supplied in

+chunks through @code{mysql_send_long_data()}.

+@end itemize

+

+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 @code{mysql_fetch()}

+return the following values in the length buffer:

+

+@itemize @bullet

+@item

+The length of the data that is returned

+@item

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

+@end itemize

+

+

+@c @item bool is_null [input]

+@c To indicate the parameter data is NULL. This is same as supplying

+@c MYSQL_NULL_DATA, -1 as the length in length pointer.

+

+

+@c @item bool is_long_data [input]

+@c To indicate the parameter data is a long data, and the data will be

+@c supplied in chunks through @code{mysql_send_long_data()}.This is also

+@c same as supplying MYSQL_LONG_DATA, -2 as the length in length pointer.

+@c @end table

+@end table

+

+

+@node C Prepared statements function overview, C Prepared statement functions, C Prepared statement datatypes, MySQL prepared statements

+@subsection C Prepared Statements Function Overview

+

+@cindex C Prepared statements API, functions

+@cindex functions, C Prepared statements API

+

+The functions available in the prepared statements are listed here and

+are described in greater detail in the later section.

+@xref{C Prepared statement functions}.

+

+@multitable @columnfractions .32 .68

+@item @strong{Function} @tab @strong{Description}

+

+@item @strong{mysql_prepare()} @tab Prepares an SQL string for execution.

+

+@item @strong{mysql_param_count()} @tab Returns the number of parameters in a prepared SQL statement.

+

+@item @strong{mysql_prepare_result()} @tab Returns prepared statement meta information in the form of resultset.

+

+@item @strong{mysql_bind_param()} @tab Binds a buffer to parameter markers in a prepared SQL statement.

+

+@item @strong{mysql_execute()} @tab Executes the prepared statement.

+

+@item @strong{mysql_stmt_affected_rows()} @tab Returns the number of rows changes/deleted/inserted by the last UPDATE,DELETE,or INSERT query

+

+@item @strong{mysql_bind_result()} @tab Binds application data buffers to columns in the resultset.

+

+@item @strong{mysql_fetch()} @tab Fetches the next rowset of data from the result set and returns data for all bound columns.

+

+@item @strong{mysql_stmt_close()} @tab Frees memory used by prepared statement.

+

+@item @strong{mysql_stmt_errno()} @tab Returns the error number for the last statement execution.

+

+@item @strong{mysql_stmt_error()} @tab Returns the error message for the last statement execution.

+

+@item @strong{mysql_send_long_data()} @tab Sends long data in chunks to server.

+

+@c TO BE MOVED TO MAIN C API FUCTIONS

+@item @strong{mysql_commit()} @tab Commits the transaction.

+

+@item @strong{mysql_rollback()} @tab Rollbacks the transaction.

+

+@item @strong{mysql_autocommit()} @tab Toggles the autocommit mode to on/off.

+

+@item @strong{mysql_more_results()} @tab Returns if there are any more results exists

+

+@item @strong{mysql_next_result()} @tab Returns/Initiates the next result in the multi-query executions

+

+@end multitable

+

+@sp 1

+Call @code{mysql_prepare()} to prepare and initialize the statement

+handle, then call @code{mysql_bind_param()} to supply the parameters

+data, and then call @code{mysql_execute()} to execute the query. You can

+repeat the @code{mysql_execute()} by changing parameter values from the

+respective buffer supplied through @code{mysql_bind_param()}.

+

+@sp 1

+

+

+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 @code{MYSQL_RES } result set 

+through @code{mysql_prepare_result()}.

+

+@sp 1

+

+You can supply the result buffers using @code{mysql_bind_result()}, so

+that the @code{mysql_fetch()} will automatically returns data to this

+buffers. This is row by row fetching.

+

+@sp 1

+

+You can also send the text or binary data in chunks to server using

+@code{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 @code{mysql_bind_param()}.

+

+@sp 1

+

+Once the statement execution is over, it must be freed using

+@code{mysql_stmt_close} so that it frees all the alloced resources for

+the statement handle.

+

+

+@subsubheading Execution Steps:

+

+To prepare and execute a statement, the application:

+

+@itemize @bullet

+@item

+Calls @strong{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

+@item

+If the query results in a resultset, then @strong{mysql_prepare_result}

+returns the result set meta info..

+@item

+Sets the values of any parameters using @strong{mysql_bind_param}. All

+parameters must be set; else it will return an error or produce

+un-expected results

+@item

+Calls @strong{mysql_execute} to execute the statement.

+@item

+Repeat steps 2 and 3 as necessary, by changing the parameter values and

+re-executing the statement.

+@item

+Bind the data buffers to return the row values, if it is a result set

+query; using @strong{mysql_bind_result()}.

+@item

+Fetch the data to buffers row by row by calling @strong{mysql_fetch()}

+repetedely until no more rows found.

+@item

+When @strong{mysql_prepare()} is called, in the MySQL client/server protocol:

+@itemize @minus

+@item

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

+@item

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

+@end itemize

+@item

+When @strong{mysql_execute()} is called, in the MySQL client/server protocol:

+@itemize @minus

+@item

+Client uses the statement handle and sends the parameters data to

+server.

+@item

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

+@end itemize

+@item

+When @strong{mysql_fetch()} is called, in the MySQL client/server protocol:

+@itemize @minus

+@item

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

+@end itemize

+@end itemize

+

+

+

+You can get the statement error code and message using

+@code{mysql_stmt_errno()} and @code{mysql_stmt_error()} respectively.

+

+

+@node C Prepared statement functions,  , C Prepared statements function overview, MySQL prepared statements

+@subsection C Prepared Statement Function Descriptions

+

+You need to use the following functions when you want to prepare and

+execute the queries.

+

+

+@menu

+* mysql_prepare::               

+* mysql_param_count::           

+* mysql_prepare_result::        

+* mysql_bind_param::            

+* mysql_execute::               

+* mysql_stmt_affected_rows::    

+* mysql_bind_result::           

+* mysql_fetch::                 

+* mysql_send_long_data::        

+* mysql_stmt_close::            

+* mysql_stmt_errno::            

+* mysql_stmt_error::            

+* mysql_commit::                

+* mysql_rollback::              

+* mysql_autocommit::            

+@end menu

+

+@node mysql_prepare, mysql_param_count, C Prepared statement functions, C Prepared statement functions

+@subsubsection @code{mysql_prepare()}

+

+@findex @code{mysql_prepare()}

+

+@code{MYSQL_STMT * mysql_prepare(MYSQL *mysql, const char *query, unsigned

+long length)}

+

+@subsubheading 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.

+

+@sp 1

+The application can include one or more parameter markers in the SQL

+statement. To include a parameter marker, the appication embeds a

+question mark (@code{?}) into the SQL string at the appropriate

+position. 

+

+@sp 1

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

+

+@sp 1

+The parameter markers are then bound to application variables using

+@code{mysql_bind_param()}.

+

+

+

+@subsubheading Return Values

+

+@code{MYSQL_STMT} if the prepare was successful. NULL if an error

+occured.

+

+@subsubheading Errors

+

+If the prepare is not successful, i.e. when @code{mysql_prepare()} returned a

+NULL statement, errors can be obtained by calling @code{mysql_error()}. 

+

+

+@subsubheading Example

+

+For the usage of @code{mysql_prepare()} refer to the Example from

+@ref{mysql_execute,mysql_execute()}.

+

+

+

+

+@node mysql_param_count, mysql_prepare_result, mysql_prepare, C Prepared statement functions

+@subsubsection @code{mysql_param_count()}

+

+@findex @code{mysql_param_count()}

+

+@code{unsigned int mysql_param_count(MYSQL_STMT *stmt)}

+

+@subsubheading Description

+

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

+

+@subsubheading Return Values

+

+An unsigned integer representing the number of parameters in a

+statement.

+

+@subsubheading Errors

+

+None

+

+@subsubheading Example

+

+For the usage of @code{mysql_param_count()} refer to the Example from

+@ref{mysql_execute,mysql_execute()}.

+

+

+

+@node mysql_prepare_result, mysql_bind_param, mysql_param_count, C Prepared statement functions

+@subsubsection @code{mysql_prepare_result()}

+

+

+@findex @code{mysql_prepare_result}.

+

+@code{MYSQL_RES *mysql_prepare_result(MYSQL_STMT *stmt)}

+

+@subsubheading Description

+

+If the @code{mysql_prepare()} resulted in a result set query, then

+@code{mysql_prepare_result()} returns the result set meta data in the form of

+@code{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:

+

+@itemize @minus

+@item

+mysql_num_fields()

+@item 

+mysql_fetch_field()

+@item

+mysql_fetch_field_direct()

+@item

+mysql_fetch_fields()

+@item

+mysql_field_count()

+@item

+mysql_field_seek()

+@item

+mysql_field_tell() and

+@item

+mysql_free_result()

+@end itemize

+

+

+@subsubheading Return Values

+

+A @code{MYSQL_RES} result structure. NULL if no meta information exists from

+the prepared query.

+

+

+@subsubheading Errors

+

+None

+

+

+@subsubheading Example

+

+For the usage of @code{mysql_prepare_result()} refer to the Example from

+@ref{mysql_fetch,mysql_fetch()}

+

+

+

+@node mysql_bind_param, mysql_execute, mysql_prepare_result, C Prepared statement functions

+@subsubsection @code{mysql_bind_param()}

+

+@findex @code{mysql_bind_param()}

+

+@code{int mysql_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)}

+

+@subsubheading Description

+

+@code{mysql_bind_param} is used to bind data for the parameter markers

+in the SQL statement from @code{mysql_prepare}. It uses the structure

+MYSQL_BIND to supply the data.

+ 

+The supported buffer types are:

+

+@itemize @bullet

+@item

+MYSQL_TYPE_TINY

+@item

+MYSQL_TYPE_SHORT

+@item

+MYSQL_TYPE_LONG

+@item

+MYSQL_TYPE_LONGLONG

+@item

+MYSQL_TYPE_FLOAT

+@item

+MYSQL_TYPE_DOUBLE

+@item

+MYSQL_TYPE_STRING

+@item

+MYSQL_TYPE_VAR_STRING

+@item

+MYSQL_TYPE_TINY_BLOB

+@item

+MYSQL_TYPE_MEDIUM_BLOB

+@item

+MYSQL_TYPE_LONG_BLOB

+@end itemize

+

+@subsubheading Return Values

+

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

+

+@subsubheading Errors

+@table @code

+@item CR_NO_PREPARE_STMT

+No prepared statement exists

+@item CR_NO_PARAMETERS_EXISTS

+No parameters exists to bind

+@item 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

+@item 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.

+@end table

+

+@subsubheading Example

+

+For the usage of @code{mysql_bind_param()} refer to the Example from

+@ref{mysql_execute,mysql_execute()}.

+

+

+

+@node mysql_execute, mysql_stmt_affected_rows, mysql_bind_param, C Prepared statement functions

+@subsubsection @code{mysql_execute()}

+

+@findex @code{mysql_execute()}

+

+@code{int mysql_execute(MYSQL_STMT *stmt}.

+

+@subsubheading Description

+

+@code{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.

+

+@sp 1

+

+If the statement is UPDATE,DELETE,or INSERT, the total number of

+changed/deletd/inserted values can be found by calling

+@code{mysql_stmt_affected_rows}. If this is a result set query, then one

+must call @code{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 @ref{mysql_fetch}.

+ 

+

+@subsubheading Return Values

+

+@code{mysql_execute()} returns the following return values:

+

+@multitable @columnfractions .30 .65

+@item @strong{Return Value}         @tab @strong{Description}

+@item MYSQL_SUCCESS,      0            @tab Successful

+@item MYSQL_STATUS_ERROR, 1            @tab Error occured. Error code and

+message can be obtained by calling @code{mysql_stmt_errno()} and @code{mysql_stmt_error()}.

+@item MYSQL_NEED_DATA,    99           @tab One of the parameter buffer is

+indicating the data suppy in chunks, and the supply is not yet complete.

+@end multitable

+

+

+@subsubheading Errors

+

+@table @code

+@item CR_NO_PREPARE_QUERY

+No query prepared prior to execution

+@item CR_ALL_PARAMS_NOT_BOUND

+Not all parameters data is supplied

+@item CR_SERVER_GONE_ERROR

+The MySQL server has gone away

+@item CR_UNKNOWN_ERROR

+An unkown error occured

+@end table

+

+

+@subsubheading Example

+

+The following example explains the uasage of @code{mysql_prepare},

+@code{mysql_param_count}, @code{mysql_bind_param}, @code{mysql_execute}

+and @code{mysql_stmt_affected_rows()}.

+

+@example

+

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

+@end example

+

+

+

+

+@node mysql_stmt_affected_rows, mysql_bind_result, mysql_execute, C Prepared statement functions

+@subsubsection @code{mysql_stmt_affected_rows()}

+

+@findex @code{mysql_stmt_affected_rows()}

+

+@code{ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt)}

+

+@subsubheading 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().

+

+@subsubheading 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(). 

+

+@subsubheading Errors

+

+None.

+

+@subsubheading Example

+

+For the usage of @code{mysql_stmt_affected_rows()} refer to the Example

+from @ref{mysql_execute,mysql_execute()}.

+

+

+

+

+

+@node mysql_bind_result, mysql_fetch, mysql_stmt_affected_rows, C Prepared statement functions

+@subsubsection @code{mysql_bind_result()}

+

+@findex @code{mysql_bind_result()}

+

+@code{my_bool mysql_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind)}

+

+@subsubheading Description

+

+@code{mysql_bind_result()} is ised to associate, or bind, columns in the

+resultset to data buffers and length buffers. When @code{mysql_fetch()} is

+called to fetch data, the MySQL client protocol returns the data for the

+bound columns in the specified buffers.

+

+@sp 1

+

+Note that all columns must be bound prior to calling @code{mysql_fetch()}

+in case of fetching the data to buffers; else @code{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.

+

+@sp 1

+

+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

+@code{mysql_fetch()} is called. For example, suppose an application binds

+the columns in a result set and calls @code{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 @code{mysql_fetch()} is called.

+

+@sp 1

+

+To bind a column, an application calls @code{mysql_bind_result()} and

+passes the type, address, and the address of the length buffer.

+ 

+The supported buffer types are:

+

+@itemize @bullet

+@item

+MYSQL_TYPE_TINY

+@item

+MYSQL_TYPE_SHORT

+@item

+MYSQL_TYPE_LONG

+@item

+MYSQL_TYPE_LONGLONG

+@item

+MYSQL_TYPE_FLOAT

+@item

+MYSQL_TYPE_DOUBLE

+@item

+MYSQL_TYPE_STRING

+@item

+MYSQL_TYPE_VAR_STRING

+@item

+MYSQL_TYPE_BLOB

+@item

+MYSQL_TYPE_TINY_BLOB

+@item

+MYSQL_TYPE_MEDIUM_BLOB

+@item

+MYSQL_TYPE_LONG_BLOB

+@end itemize

+

+@subsubheading Return Values

+

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

+

+@subsubheading Errors

+@table @code

+@item CR_NO_PREPARE_STMT

+No prepared statement exists

+@item CR_UNSUPPORTED_PARAM_TYPE

+The conversion is not supported, possibly the buffer_type is illegal or

+its not from the list of supported types.

+@end table

+

+@subsubheading Example

+

+For the usage of @code{mysql_bind_result()} refer to the Example from

+@ref{mysql_fetch,mysql_fetch()}

+

+

+

+@node mysql_fetch, mysql_send_long_data, mysql_bind_result, C Prepared statement functions

+@subsubsection @code{mysql_fetch()}

+

+@findex code{mysql_fetch()}

+

+@code{int mysql_fetch(MYSQL_STMT *stmt)}

+

+@subsubheading Description

+

+@code{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

+@code{mysql_execute()} that creates a result set.

+

+@sp 1

+

+If row buffers are bound using @code{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.

+

+@sp 1

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

+

+@sp 1

+If the data fetched is a NULL data, then the length buffer will have a

+value of @strong{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:

+

+@multitable @columnfractions .10 .30

+@item @strong{Type}           @tab @strong{Length} 

+@item MYSQL_TYPE_TINY         @tab 1

+@item MYSQL_TYPE_SHORT        @tab 2

+@item MYSQL_TYPE_LONG         @tab 4

+@item MYSQL_TYPE_FLOAT        @tab 4

+@item MYSQL_TYPE_LONGLONG     @tab 8

+@item MYSQL_TYPE_DOUBLE       @tab 8

+@item MYSQL_TYPE_STRING       @tab data length

+@item MYSQL_TYPE_VAR_STRING   @tab data_length

+@item MYSQL_TYPE_BLOB         @tab data_length

+@item MYSQL_TYPE_TINY_BLOB    @tab data_length

+@item MYSQL_TYPE_MEDIUM_BLOB  @tab data_length

+@item MYSQL_TYPE_LONG_BLOB    @tab data_length

+@end multitable

+

+@*

+where @code{*data_length} is nothing but the 'Actual length of the data'.

+

+@subsubheading Return Values

+

+@multitable @columnfractions .30 .65

+@item @strong{Return Value}         @tab @strong{Description}

+@item MYSQL_SUCCESS,      0            @tab Successful, the data has been

+fetched to application data buffers.

+@item MYSQL_STATUS_ERROR, 1            @tab Error occured. Error code and

+message can be obtained by calling @code{mysql_stmt_errno()} and @code{mysql_stmt_error()}.

+@item MYSQL_NO_DATA,      100           @tab No more rows/data exists

+@end multitable

+

+

+@subsubheading Errors

+@table @code

+@item CR_UNSUPPORTED_PARAM_TYPE

+If the field type is DATE,DATETIME,TIME,or TIMESTAMP; and the

+application buffer type is non string based.

+@item

+All other un-supported conversions are returned from

+@code{mysql_bind_result()}.

+@end table

+

+@subsubheading Example

+

+The following example explains the usage of @code{mysql_prepare_result},

+@code{mysql_bind_result()}, and @code{mysql_fetch()}

+

+@example

+  

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

+  @}  

+ 

+@end example

+

+

+

+@node mysql_send_long_data, mysql_stmt_close, mysql_fetch, C Prepared statement functions

+@subsubsection @code{mysql_send_long_data()}

+

+

+@findex @code{mysql_send_long_data()}.

+

+@code{int mysql_send_long_data(MYSQL_STMT *stmt, unsigned int

+parameter_number, const char *data, ulong length, my_bool is_last_data)}

+

+@subsubheading 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.

+

+@sp 1

+The @code{data} is a pointer to buffer containing the actual data for

+the parameter represendted by @code{parameter_number}. The @code{length}

+indicates the amount of data to be sent in bytes, and @code{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 @code{mysql_send_long_data()}, then the

+@code{mysql_execute()} will return @strong{MYSQL_NEED_DATA}.

+

+

+

+@subsubheading Return Values

+

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

+occured.

+

+

+@subsubheading Errors

+

+@table @code

+@item CR_INVALID_PARAMETER_NO

+Invalid parameter number

+@item CR_SERVER_GONE_ERROR

+The MySQL server has gone away

+@item CR_UNKNOWN_ERROR

+An unkown error occured

+@end table

+

+@subsubheading Example

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

+column:

+@example

+  

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

+@end example

+

+

+

+@node mysql_stmt_close, mysql_stmt_errno, mysql_send_long_data, C Prepared statement functions

+@subsubsection @code{mysql_stmt_close()}

+

+@findex @code{mysql_stmt_close()}

+

+@code{my_bool mysql_stmt_close(MYSQL_STMT *)}

+

+@subsubheading Description

+

+Closes the prepared statement. @code{mysql_stmt_close()} also

+deallocates the statement handle pointed to by @code{stmt}.

+

+@subsubheading Return Values

+

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

+

+

+@subsubheading Errors

+

+@table @code

+@item CR_SERVER_GONE_ERROR

+The MySQL server has gone away

+@item CR_UNKNOWN_ERROR

+An unkown error occured

+@end table

+

+@subsubheading Example

+

+For the usage of @code{mysql_stmt_close()} refer to the Example from

+@ref{mysql_execute,mysql_execute()}.

+

+

+

+@node mysql_stmt_errno, mysql_stmt_error, mysql_stmt_close, C Prepared statement functions

+@subsubsection @code{mysql_stmt_errno()}

+

+

+@findex @code{mysql_stmt_errno()}

+

+@code{unsigned int mysql_stmt_errno(MYSQL_STMT *stmt)}

+

+@subsubheading Description

+

+For the statement specified by @code{stmt}, @code{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

+

+@subsubheading Return Values

+

+An error code value. Zero if no error occured.

+

+@subsubheading Errors

+

+None

+

+

+@node mysql_stmt_error, mysql_commit, mysql_stmt_errno, C Prepared statement functions

+@subsubsection @code{mysql_stmt_error()}

+

+

+@findex @code{mysql_stmt_error()}.

+

+@code{char *mysql_stmt_error(MYSQL_STMT *stmt)}

+

+@subsubheading Description

+

+For the statement specified by @code{stmt}, @code{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:

+

+@example

+

+if (mysql_stmt_errno(stmt))

+@{

+  // an error occured

+@}

+

+if (mysql_stmt_error(stmt))

+@{

+  // an error occured

+@}

+@end example

+

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

+

+

+@subsubheading Return Values

+

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

+occured.

+

+@subsubheading Errors

+

+None

+

+

+

+@node mysql_commit, mysql_rollback, mysql_stmt_error, C Prepared statement functions

+@subsubsection @code{mysql_commit()}

+

+

+@findex @code{mysql_commit()}.

+

+@code{my_bool mysql_commit(MYSQL *mysql)}

+

+@subsubheading Description

+

+Commits the current transaction

+

+@subsubheading Return Values

+

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

+

+@subsubheading Errors

+

+None

+

+

+

+@node mysql_rollback, mysql_autocommit, mysql_commit, C Prepared statement functions

+@subsubsection @code{mysql_rollback()}

+

+

+@findex @code{mysql_rollback()}.

+

+@code{my_bool mysql_rollback(MYSQL *mysql)}

+

+@subsubheading Description

+

+Rollbacks the current transaction.

+

+

+@subsubheading Return Values

+

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

+

+@subsubheading Errors

+

+None.

+

+

+

+

+@node mysql_autocommit,  , mysql_rollback, C Prepared statement functions

+@subsubsection @code{mysql_autocommit()}

+

+

+@findex @code{mysql_autocommit()}.

+

+@code{my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)}

+

+@subsubheading Description

+

+Sets the autocommit mode to on or off. If the @code{mode} is '1', then it

+sets the autocommit mode to on, else to off in case of '0'.

+

+@subsubheading Return Values

+

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

+

+@subsubheading Errors

+

+None.

+

+@bye