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



  • 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 :)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.