Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Mysql calling a SP with a prepared statement, passing a string (input) parameter
Forum Updated to NodeBB v4.3 + New Features

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

Scheduled Pinned Locked Moved General and Desktop
1 Posts 1 Posters 1.5k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • H Offline
    H Offline
    hal9k
    wrote on last edited by
    #1

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

    1 Reply Last reply
    0

    • Login

    • Login or register to search.
    • First post
      Last post
    0
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Get Qt Extensions
    • Unsolved