int mysql_stmt_execute(MYSQL_STMT *stmt)
Description
mysql_stmt_execute()
executes
the prepared query associated with the statement handle. 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 22.9.7.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 12.6.4, “Automatic Prepared Statement Repreparation”.
Return Values
Zero if execution was successful. Nonzero if an error occurred.
Errors
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.
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. For an example that shows how to retrieve
data, see Section 22.9.7.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]; 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 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); }
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
Bazaar source repository.