Rechercher dans le manuel MySQL
28. 7. 11. 10 mysql _stmt _execute ()
int mysql_stmt_execute(MYSQL_STMT *stmt)
Description
mysql_stmt_execute()
executes
the prepared query associated with the statement handler. The
currently bound parameter marker values are sent to server
during this call, and the server replaces the markers with
this newly supplied data.
Statement processing following
mysql_stmt_execute()
depends
on the type of statement:
For an
UPDATE
,DELETE
, orINSERT
, the number of changed, deleted, or inserted rows can be found by callingmysql_stmt_affected_rows()
.For a statement such as
SELECT
that generates a result set, you must callmysql_stmt_fetch()
to fetch the data prior to calling any other functions that result in query processing. For more information on how to fetch the results, refer to Section 28.7.11.11, “mysql_stmt_fetch()”.Do not following invocation of
mysql_stmt_execute()
with a call tomysql_store_result()
ormysql_use_result()
. Those functions are not intended for processing results from prepared statements.
For statements that generate a result set, you can request
that mysql_stmt_execute()
open
a cursor for the statement by calling
mysql_stmt_attr_set()
before
executing the statement. If you execute a statement multiple
times, mysql_stmt_execute()
closes any open cursor before opening a new one.
Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. For more information, see Section 8.10.3, “Caching of Prepared Statements and Stored Programs”.
Commands were executed in an improper order.
Out of memory.
The MySQL server has gone away.
The connection to the server was lost during the query.
An unknown error occurred.
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 handler. For an example that shows how to retrieve
data, see Section 28.7.11.11, “mysql_stmt_fetch()”.
#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];
uint64_t affected_rows;
int param_count;
short small_data;
int int_data;
char str_data[STRING_SIZE];
unsigned long str_length;
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 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))
{
/* mysql_stmt_close() invalidates stmt, so call */
/* mysql_error(mysql) rather than mysql_stmt_error(stmt) */
fprintf(stderr, " failed while closing the statement\n");
fprintf(stderr, " %s\n", mysql_error(mysql));
exit(0);
}
For complete examples on the use of prepared statement
functions, refer to the file
tests/mysql_client_test.c
. This file
can be obtained from a MySQL source distribution or from the
source repository (see
Section 2.9, “Installing MySQL from Source”).
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-mysql-stmt-execute.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.