1. mysql table
create table if not exists hwg_data_20151110
(
id int primary key auto_increment,
dev_type smallint unsigned not null,
dev_id smallint unsigned not null,
data_time datetime not null,
data_time_ms smallint unsigned,
data_time_us smallint unsigned,
yc_ac_ia smallint unsigned,
yc_ac_ic smallint unsigned,
yc_ac_in smallint unsigned,
yx1 smallint unsigned,
yx2 smallint unsigned,
freq smallint unsigned,
tmp1 smallint unsigned,
tmp2 smallint unsigned,
tmp3 smallint unsigned
);drop procedure if exists proc_get_newest_yc;
delimiter //
/*
*@brief 禄帽脠隆脳卯脨脗脪拢虏芒脢媒戮脻
*
*
*/
create procedure ca9100db.proc_get_newest_yc
(
OUT myval float
)
begin
declare mydate varchar(8);
declare date_str varchar(17);
declare sqlstr varchar(128);
select date_format(current_date(), '%Y%m%d') into mydate;
select concat('hwg_data_',mydate) into date_str;
/*select date_str;*/
set @sqlstr = concat('', 'select yc_ac_ia from ');
set @sqlstr = concat(@sqlstr,date_str);
set @sqlstr = concat(@sqlstr,'');
select @sqlstr into sqlstr;
prepare sqlstr from @sqlstr;
execute sqlstr;
endvoid DataServices::data_dispatcher_init()
{
string query_tbl_str;
string tbl_name;
string query_str;
MYSQL_ROW row;
MYSQL_RES *res;
unsigned char has_the_tbl = 0;
int ret;
string select_str;
if (!mysql_real_connect(&mysql, this->mysql_host.c_str(),
this->mysql_user.c_str(), this->mysql_pwd.c_str(), this->mysql_dbname.c_str(),
0, NULL, CLIENT_MULTI_STATEMENTS)) {
mysql_close(&mysql);
exit(1);
}
query_str = "call proc_get_newest_yc\(\@yc_ia\)";
ret = mysql_real_query(&mysql, query_str.c_str(), (unsigned long)query_str.length());
//ret = mysql_query(&(this->mysql), query_str);
res = mysql_store_result(&mysql);
while((row = mysql_fetch_row(res))) {
printf("[%s]\n", row[0]);
}
}这里需要特别注意一点：
在运行mysql_real_query/mysql_query执行存储过程的时候可能会返回1，其错误消息为 "Procedure myprocedure can't return a result set in the given context."
原因：与mysql_set_server_option() & mysql_real_connect()有关，mysql_real_connect函数的最后一个参数为client_flag，默认我们设置为0，但存储过程是在同一字符串中执行多个语句的，所以该参数需要修改默认值为CLIENT_MULTI_STATEMENTS。
参考：
http://stackoverflow.com/questions/2350823/problems-calling-mysql-stored-procedures-from-c-api
