帮助

8453阅读
6回复

无法得到mysql存储过程的输出参数 [复制链接]

上一主题下一主题查看指定楼层

查看指定楼层

离线daimon0316

只看楼主倒序阅读楼主发表于: 2009-03-03

— 本帖被 XChinux 从 General Qt Programming 移动到本区(2011-01-02) —

如题。
想用存储过程封装数据库原子操作，需要返回状态码。但在QT代码中调用存储过程总是失败，错误信息如下：
OUT or INOUT argument 4 for routine myDB.proc_userByAdd is not a variable or NEW pseudo-variable in BEFORE trigger QMYSQL3: Unable to execute statement
注：存储过程本身是正确的，已在mysql客户端测试并通过。
哪位能给出点建议，谢谢。

代码如下：

存储过程CODE：

CREATE PROCEDURE `proc_userByAdd`(in_username varchar(20), in_password varchar(40), OUT out_result int)
BEGIN
DECLARE tmp_userid INT DEFAULT 0;
DECLARE txn_error INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET txn_error=1;
END;
set out_result = 1;
START TRANSACTION;
insert into userinfo(username,password) values(in_username,in_password);
IF txn_error THEN
set out_result = 0;
ROLLBACK ;
END IF;
select LAST_INSERT_ID() into tmp_userid;
insert into user_role(roleid,userid) values(in_roleid,tmp_userid);
IF txn_error THEN
set out_result =0;
ROLLBACK ;
END IF;
COMMIT;
END

QT 部分code:

QSqlQuery query(db_tmp);
int result = 1;
query.prepare("call proc_userByAdd(?,?,?,?)");
query.bindValue(0,username);
query.bindValue(1,password);
query.bindValue(2,roleid);
query.bindValue(3,0,QSql::Out);
if(!query.exec())
{
WRTLOG(tr("[DBERROR][USERMANAGE][addUser]%1\r\n[sql]%2").arg(query.lastError().text(),query.lastQuery()),DEBUG);
result = 0;
}
else
{
result = query.boundValue(3).toInt();
}

博客地址 http://blog.sina.com.cn/daimon0316

分享到 淘江湖新浪 QQ微博 QQ空间开心人人豆瓣网易微博百度鲜果白社会飞信

离线daimon0316

只看该作者 1楼发表于: 2009-03-03

没人给点建议吗？
这是QT助手上的示例，用法相同呀，郁闷的！！！！！
This code calls a stored procedure called AsciiToInt(), passing it a character through its in parameter, and taking its result in the out parameter.
     QSqlQuery query;
     query.prepare("CALL AsciiToInt(?, ?)");
     query.bindValue(0, "A");
     query.bindValue(1, 0, QSql::Out);
     query.exec();
     int i = query.boundValue(1).toInt(); // i is 65
Note that unbound parameters will retain their values.
Stored procedures that uses the return statement to return values, or return multiple result sets, are not fully supported. For specific details see SQL Database Drivers.

博客地址 http://blog.sina.com.cn/daimon0316

离线daimon0316

只看该作者 2楼发表于: 2009-03-03

没人给点建议吗？
这是QT助手上的示例，用法相同呀，郁闷的！！！！！
This code calls a stored procedure called AsciiToInt(), passing it a character through its in parameter, and taking its result in the out parameter.
     QSqlQuery query;
     query.prepare("CALL AsciiToInt(?, ?)");
     query.bindValue(0, "A");
     query.bindValue(1, 0, QSql::Out);
     query.exec();
     int i = query.boundValue(1).toInt(); // i is 65
Note that unbound parameters will retain their values.
Stored procedures that uses the return statement to return values, or return multiple result sets, are not fully supported. For specific details see SQL Database Drivers.

博客地址 http://blog.sina.com.cn/daimon0316

离线daimon0316

只看该作者 3楼发表于: 2009-03-04

？？？？

博客地址 http://blog.sina.com.cn/daimon0316

离线daimon0316

只看该作者 4楼发表于: 2009-03-05

没人遇到过吗？

博客地址 http://blog.sina.com.cn/daimon0316

离线daimon0316

只看该作者 5楼发表于: 2009-03-09

版主？？？？

博客地址 http://blog.sina.com.cn/daimon0316

离线csm55

只看该作者 6楼发表于: 2010-02-03

create procedure qtestproc (OUT param1 INT, OUT param2 INT)
BEGIN
set param1 = 42;
set param2 = 43;
END

QSqlQuery q;
q.exec("call qtestproc (@outval1, @outval2)");
q.exec("select @outval1, @outval2");
q.next();
qDebug() << q.value(0) << q.value(1); // outputs "42" and "43"