本节描述预备语句支持C API的使用存储过程执行调用声明:

使用存储过程执行准备调用语句可用于以下方面:

下面的讨论显示了如何使用这些功能通过预处理语句的C API。使用准备调用语句通过准备和执行语句,看到调用语句。

一个应用程序,该应用程序执行一个准备调用声明应该使用一个循环,获取结果,然后调用mysql_stmt_next_result ()是否有更多的结果。任何产生的结果集的结果由存储过程之后,最后一个状态值,表明是否成功过程终止。

如果程序出或INOUT前最后一个状态值参数,结果集包含它们的值。确定一个结果集是否包含参数值,测试是否SERVER_PS_OUT_PARAMS中设置server_status的成员MYSQL连接处理程序:

mysql - > server_status & SERVER_PS_OUT_PARAMS

下面的例子使用了一个准备调用语句执行一个存储过程,产生多个结果集,提供参数值返回给调用者通过出和INOUT参数。所有三种类型的过程需要参数(在,出,INOUT),显示他们的初始值,分配新值,显示更新后的值,并返回。因此预期收益信息过程由多个结果集和一个最终状态:

执行程序的代码:

MYSQL_STMT *支撑;MYSQL_BIND ps_params [3];/ *输入参数的缓冲区* / int int_data [3];/ *输入/输出值* / my_bool is_null [3];/ *输出值nullability * / int状态;/ *设置存储过程* /状态= mysql_query (mysql,下降过程中如果存在p1);test_error (mysql、状态);状态= mysql_query (mysql,“创建过程p1 (p_in INT”“,”“p_out INT,”“INOUT p_inout INT)”“开始”“选择p_in、p_out p_inout;”“p_in = 100, p_out = 200, p_inout = 300; " " SELECT p_in, p_out, p_inout; " "END"); test_error(mysql, status); /* initialize and prepare CALL statement with parameter placeholders */ stmt = mysql_stmt_init(mysql); if (!stmt) { printf("Could not initialize statement\n"); exit(1); } status = mysql_stmt_prepare(stmt, "CALL p1(?, ?, ?)", 16); test_stmt_error(stmt, status); /* initialize parameters: p_in, p_out, 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 parameters */ status = mysql_stmt_bind_param(stmt, ps_params); test_stmt_error(stmt, status); /* assign values to parameters and execute 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 results until there are no more */ do { int i; int num_fields; /* number of columns in result */ MYSQL_FIELD *fields; /* for result set metadata */ MYSQL_BIND *rs_bind; /* for output buffers */ /* the column count is > 0 if there is a result set */ /* 0 if the result is only the final status packet */ num_fields = mysql_stmt_field_count(stmt); if (num_fields > 0) { /* there is a result set to fetch */ printf("Number of columns in result: %d\n", (int) num_fields); /* what kind of result set is this? */ printf("Data: "); if(mysql->server_status & SERVER_PS_OUT_PARAMS) printf("this result set contains OUT/INOUT parameters\n"); else printf("this result set is produced by the 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_fields); if (!rs_bind) { printf("Cannot allocate output buffers\n"); exit(1); } memset(rs_bind, 0, sizeof (MYSQL_BIND) * num_fields); /* set up and bind result set output buffers */ for (i = 0; i < num_fields; ++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); /* fetch and display result set rows */ 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); /* free metadata */ free(rs_bind); /* free output buffers */ } else { /* no columns = final status packet */ printf("End of procedure output\n"); } /* more results? -1 = no, >0 = error, 0 = yes (keep looking) */ status = mysql_stmt_next_result(stmt); if (status > 0) test_stmt_error(stmt, status); } while (status == 0); mysql_stmt_close(stmt);

执行过程将产生以下输出:

结果:3中列数数据:这个过程产生的结果集是val [0] = 10;val[1] =零;val [2] = 30;结果:3中列数数据:这个过程产生的结果集是val [0] = 100;val [1] = 200;val [2] = 300;结果:2中列数数据:这个结果集包含/ INOUT参数val [0] = 200;val [1] = 300;结束程序输出

代码使用了两个实用程序例程,test_error ()和test_stmt_error ()检查错误和终止后打印诊断信息如果发生错误:

静态孔隙test_error (MYSQL * MYSQL, int状态){如果(地位){流(stderr,“错误:% s (errno: % d) \ n”, mysql_error (MYSQL), mysql_errno (MYSQL));退出(1);}}静态孔隙test_stmt_error (MYSQL_STMT *支撑,int状态){如果(地位){流(stderr,“错误:% s (errno: % d) \ n”, mysql_stmt_error(支撑),mysql_stmt_errno(支撑));退出(1);}}