Mysql calling a SP with a prepared statement, passing a string (input) parameter

hal9k

Hi everyone,

I'm passing a hard time trying to do something as simple as using a prepared statement with qt (5.0.2/mingw 4.7) using QMYSQL driver on mysql 5.6.10, for Win32 (x86)

I just prepared a simple program to replicate my issue.

Table structure, data, and a simple SP to retrieve the data
@
CREATE TABLE usr_usuario (
usr_id int(11) NOT NULL AUTO_INCREMENT,
usr_nombre varchar(20) NOT NULL,
usr_password varchar(50) NOT NULL,
prf_id int(11) NOT NULL,
usr_baja tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (usr_id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf
@

@
INSERT INTO usr_usuario (usr_nombre, usr_password, prf_id, usr_baja) VALUES ('A','A',0,0);
@

@
CREATE PROCEDURE get_usr(
usrnombre varchar(20)
)
BEGIN
select usr_id,usr_nombre,usr_password,usr_baja,prf_id
from usr_usuario
where usr_nombre=IFNULL(usrnombre,usr_nombre);
END
@

Test program:
@
#include <QCoreApplication>
#include <QDebug>
#include <QtSql>

int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setUserName("username");
db.setPassword("password");
db.setHostName("localhost");
db.setDatabaseName("test");
if(!db.open())qCritical()<<db.lastError().text();
QSqlQuery q(db);
//running the query directly actually works
//q.exec("call get_usr('A')");
if(!q.prepare("call get_usr(?)")) qCritical()<<"prepare error"<<q.lastError().text();
q.bindValue(0,"A");
if(!q.exec())qCritical()<<"exec error"<<q.lastError().text();
qDebug()<<"active"<<q.isActive()<<"valid"<<q.isValid()<<"lasterror"<<q.lastError();
qDebug()<<"first"<<q.first(); //false using prepare, true using exec directly
qDebug()<<"active"<<q.isActive()<<"select"<<q.isSelect()<<"valid"<<q.isValid();
qDebug()<<q.lastError().text();
return a.exec();
}
@

Query Log
@
130427 14:27:44 25 Connect username#localhost on test
25 Init DB test
25 Query SET NAMES utf8
25 Prepare call get_usr(?)
25 Reset stmt
25 Execute call get_usr('A')
@

On the other hand, running another SP with a integer input parameter using a prepared statement works well.

Any ideas would be appreciated :)