You can use a prepared statement to efficiently execute it multiple times. The statement is parsed and then executed once or multiple times by using the statement handle returned by the initialization function.
An application uses OceanBase Connector/C to prepare and execute a statement by performing the following steps:
Use
mysql_stmt_init()to create a prepared statement handle. Callmysql_stmt_prepare()to pass a string that contains the SQL statement to the OBServer.Use
mysql_stmt_bind_param()to bind values to parameters. All parameters must be bound to values. Otherwise, the statement execution returns an error or generates unexpected results.Call
mysql_stmt_execute()to execute the statement.If the statement is
SELECTor any other statement that generates a result set, callmysql_stmt_result_metadata()if necessary to obtain the result set metadata.If the statement generates a result set, call
mysql_stmt_bind_result()to bind the data buffer for retrieving row values.Repeatedly call
mysql_stmt_fetch()to pull data row by row into the buffer until the data of all rows are pulled.Repeat steps 3 to 6 as needed. You can repeatedly call
mysql_stmt_execute()to execute the statement again by changing the parameter values in the corresponding buffers provided bymysql_stmt_bind_param().After the statement is executed, use
mysql_stmt_close()to close the statement handle and release all associated resources. After that, the handle becomes invalid and cannot be used again.If you obtain the result set metadata of the
SELECTstatement by callingmysql_stmt_result_metadata(), you need to usemysql_free_result()to release the metadata.
For more information about APIs, see C API functions.
Example:
MYSQL_STMT *stmt;
MYSQL_BIND ps_params[3]; /* Buffer of input parameters. */
int int_data[3]; /* Input/output values. */
my_bool is_null[3]; /* The output value can be nullable. */
int status;
/* Set the stored procedure. */
status = mysql_query(mysql, "DROP PROCEDURE IF EXISTS proc1");
test_error(mysql, status);
status = mysql_query(mysql,
"CREATE PROCEDURE proc1("
" IN p_in INT, "
" OUT p_out INT, "
" INOUT p_inout INT) "
"BEGIN "
" SELECT p_in, p_out, p_inout; "
" SET p_in = 110, p_out = 220, p_inout = 330; "
" SELECT p_in, p_out, p_inout; "
"END");
test_error(mysql, status);
/* Use parameter placeholders to initialize and prepare the CALL statement. */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
printf("Could not initialize statement\n");
exit(1);
}
status = mysql_stmt_prepare(stmt, "CALL proc1(?, ?, ?)", 12);
test_stmt_error(stmt, status);
/* Initialize the parameters: p_in, p_out, and p_inout (all INT). */
memset(ps_params, 0, sizeof (ps_params));
ps_params[0].buffer_type = MYSQL_TYPE_LONG;
ps_params[0].buffer = (char *) &int_data[0];
ps_params[0].length = 0;
ps_params[0].is_null = 0;
ps_params[1].buffer_type = MYSQL_TYPE_LONG;
ps_params[1].buffer = (char *) &int_data[1];
ps_params[1].length = 0;
ps_params[1].is_null = 0;
ps_params[2].buffer_type = MYSQL_TYPE_LONG;
ps_params[2].buffer = (char *) &int_data[2];
ps_params[2].length = 0;
ps_params[2].is_null = 0;
/*Bind the parameters.*/
status = mysql_stmt_bind_param(stmt, ps_params);
test_stmt_error(stmt, status);
/* Assign values to parameters and execute the statement. */
int_data[0]= 10; /* p_in */
int_data[1]= 20; /* p_out */
int_data[2]= 30; /* p_inout */
status = mysql_stmt_execute(stmt);
test_stmt_error(stmt, status);
/* Process the result until no rows can be found. */
do {
int i;
int num_counts; /* The number of columns in the result. */
MYSQL_FIELD *fields; /* This is used for the result set metadata. */
MYSQL_BIND *rs_bind; /* This is used for the output buffer. */
/* If a result set is generated, the number of columns is greater than 0. */
/* If no result set is generated, the number of columns is 0. */
num_fields = mysql_stmt_field_count(stmt);
if (num_counts> 0)
{
/* One result set needs to be fetched. */
printf("Number of columns in result: %d\n", (int) num_counts);
/* Determine the type of the result set. */
printf("Data:");
if(mysql->server_status & SERVER_PS_OUT_PARAMS)
printf("The result set contains OUT/INOUT parameters\n");
else
printf("The result set is generated by procedure\n");
MYSQL_RES *rs_metadata = mysql_stmt_result_metadata(stmt);
test_stmt_error(stmt, rs_metadata == NULL);
fields = mysql_fetch_fields(rs_metadata);
rs_bind = (MYSQL_BIND *) malloc(sizeof (MYSQL_BIND) * num_counts);
if (!rs_bind)
{
printf("Cannot allocate output buffers\n");
exit(1);
}
memset(rs_bind, 0, sizeof (MYSQL_BIND) * num_counts);
/* Set and bind the result set output buffer. */
for (i = 0; i < num_counts; ++i)
{
rs_bind[i].buffer_type = fields[i].type;
rs_bind[i].is_null = &is_null[i];
switch (fields[i].type)
{
case MYSQL_TYPE_LONG:
rs_bind[i].buffer = (char *) &(int_data[i]);
rs_bind[i].buffer_length = sizeof (int_data);
break;
default:
fprintf(stderr, "ERROR: unexpected type: %d.\n", fields[i].type);
exit(1);
}
}
status = mysql_stmt_bind_result(stmt, rs_bind);
test_stmt_error(stmt, status);
/* Obtain and display rows of the result set. */
while (1)
{
status = mysql_stmt_fetch(stmt);
if (status == 1 status == MYSQL_NO_DATA)
break;
for (i = 0; i < num_fields; ++i)
{
switch (rs_bind[i].buffer_type)
{
case MYSQL_TYPE_LONG:
if (*rs_bind[i].is_null)
printf(" val[%d] = NULL;", i);
else
printf(" val[%d] = %ld;",
i, (long) *((int *) rs_bind[i].buffer));
break;
default:
printf(" unexpected type (%d)\n",
rs_bind[i].buffer_type);
}
}
printf("\n");
}
mysql_free_result(rs_metadata); /* Release the metadata. */
free(rs_bind); /* Release the output buffer. */
}
else
{
/* No column = final status package. */
printf("End of procedure output\n");
}
/* Check whether more results are generated. The value -1 indicates no. The value 0 indicates yes, which means the loop continues. A value greater than 0 indicates that an error occurred. */
if (status > 0)
test_stmt_error(stmt, status);
} while (status == 0);
mysql_stmt_close(stmt);