因为懒得自己动手测试了,谁如果有已经执行成功的mysql c api 执行存储过程并,读取存储过程返回结果集的小例子,贴一个代码出来共享一下。
特别声明,一般情况下我都自己调试,圣诞了,那就搞点共享,放点分。
注意啊,可别贴java的,java的俺用多了,很长时间没有摸C了,有点生疏。
最后祝大家圣诞快乐!!!!
特别声明,一般情况下我都自己调试,圣诞了,那就搞点共享,放点分。
注意啊,可别贴java的,java的俺用多了,很长时间没有摸C了,有点生疏。
最后祝大家圣诞快乐!!!!
private string shuju_lianjie(string name, string email, string password)
{
//////////////////////////////////////////////////////////////////////////////
///获取用户名、邮件及设定密码,增加到数据库的用户表后返回该用户注册id值,不考虑 安全,仅仅表示代码的写作范例是否合乎要求
///存储过程为:
///CREATE PROCEDURE `yuan_user_cc`(in xname varchar(100),in xemail varchar(100),in xpassword varchar(100),out xid integer)
///begin
///insert into yuan_user(name,email,password) values(xname,xemail,xpassword);
///select id into xid from yuan_user where name=xname;
///end
//////////////////////////////////////////////////////////////////////////////
Int16 id;//返回的id值
MySqlConnection mysqlcon;
MySqlDataAdapter mysqldata = new MySqlDataAdapter();
DataSet dataset = new DataSet();
mysqlcon = new MySqlConnection("server=localhost; user id=root; password=123; database=yuan_test; pooling=false;charset=gb2312");
mysqldata.SelectCommand = new MySqlCommand();
mysqldata.SelectCommand.Connection = mysqlcon;
mysqldata.SelectCommand.CommandText = "yuan_user_cc";
mysqldata.SelectCommand.CommandType = CommandType.StoredProcedure;
//设置参数,添加到数据库
MySqlParameter name_parameter = new MySqlParameter("?xname", MySqlDbType.VarChar,100);//mysql的存储过程参数是以?打头的!!!!
name_parameter.Value = name;
mysqldata.SelectCommand.Parameters.Add(name_parameter);
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MySqlParameter email_parameter = new MySqlParameter("?xemail", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!!
email_parameter.Value = email;
mysqldata.SelectCommand.Parameters.Add(email_parameter);
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MySqlParameter password_parameter = new MySqlParameter("?xpassword", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!!
password_parameter.Value = password;
mysqldata.SelectCommand.Parameters.Add(password_parameter);
//++++++++++++++++++++++++++返回值++++++++++++++++++++++++++++++++++++++++++
MySqlParameter id_parameter = new MySqlParameter("?xid", MySqlDbType.Int16,15);//mysql的存储过程参数是以?打头的!!!!
id_parameter.Direction=ParameterDirection.Output;
mysqldata.SelectCommand.Parameters.Add(id_parameter);
//+++++++++++++++++++++++++打开数据库,插入数据,返回id值+++++++++++++++++++
try
{
mysqldata.Fill(dataset, "yuan_user_cc");
id = (Int16)id_parameter.Value;
return id.ToString();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return String.Empty;
} }
用于存放用户信息
Create table student(
id int auto_increment,
name varchar(20),
age tinyint,
re varchar(50),
primary key(id)
);
4、创建存储过程
如下:
delimiter //
create procedure querystudent(
in in_id int , #0-字符id 1-数字id
#
out out_ret int, #返回结果
out out_name varchar(20), #名字
out out_age int #年龄
)
label_a:begin
declare v_name varchar(20) ;
declare v_age tinyint ;
#参数判断
if (in_id<=0) then
set out_ret=-1; #id error
leave label_a;
end if;
SELECT name,age into v_name,v_age from student where id=in_id limit 1;
if v_age is NULL then
set out_ret=-2; #don't found
leave label_a;
end if;
set out_ret=0;
set out_name=v_name;
set out_age=v_age;
end;
//
delimiter ;
5、c语言调用存储过程
调用方法或步骤:
5.1、初始化Mysql句柄
if(!mysql_init(&mysql))
{
printf("mysql_init failed!\n");
return 0;
}
5.2、连接到mysql
//login or connect
if(!mysql_real_connect(&mysql,"localhost","root","","billingdb",0,NULL,CLIENT_MULTI_STATEMENTS))
{
printf("mysql_real_connect() failed!\n");
mysql_close(&mysql);
return 0;
}
5.3、调用存储过程
//call
strcpy(query,"call querystudent (1,@ret,@ out_name,@ out_age)");
printf("query sql=[%s]\n",query);
ret= mysql_real_query(&mysql,query,(unsigned int)strlen(query));
5.4、查询结果集并保存
mysql_query(&mysql, "SELECT @ret,@ out_name,@ out_age ");
//get result
if (ret)
{
printf("Error exec query: %s\n",mysql_error(&mysql));
}
else
{
printf("[%s] exec...\n", query);
}
results = mysql_store_result(&mysql);
5.5、获取查询结果
while((record = mysql_fetch_row(results))) {
printf("[%s]-[%s]-[%s]\n", record[0], record[1],record[2]);
}
一般存储过程只会有一行的返回结果,^_^.
5.6、释放资源与mysql连接句柄
mysql_free_result(results);
mysql_close(&mysql);
#2的不错,符合官方手册需求。不知道有没有存储过程直接select一个不太大的结果集出来。然后通过
mysql_store_result() 获取结果集,再一行一行取数据的。
results = mysql_store_result(&mysql);
while((record = mysql_fetch_row(results))) {
printf("[%s]-[%s]-[%s]\n", record[0], record[1],record[2]);
}
就是说存储过程只有一句话(不考虑容错)SELECT id, name,age from student where id > in_id limit 500然后通过mysql_store_result(&mysql);就得到一个结果集。对吧。
这可是很方便,执行效率大大提高啦,呵呵