Mysql C API调用存储过程的总结

最新推荐文章于 2021-02-18 08:13:56 发布

weixin_33806914

最新推荐文章于 2021-02-18 08:13:56 发布

阅读量432

点赞数

文章标签：数据库 c/c++ python

原文链接：https://my.oschina.net/u/1011760/blog/534975

版权

2019独角兽企业重金招聘Python工程师标准>>>

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
);

2. procedure

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;
end

3. mysql c api调用

void 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

转载于:https://my.oschina.net/u/1011760/blog/534975