Important: Please read the Qt Code of Conduct -

Minimal SQL program that fails, what is wrong with it

  • Here is a minimal Qt program that tries to run a SQL procedure, but it fails. Why? Can you help me?
    I'm using Qt version 5.12.8, PostgreSQL 13

    The output is:

    db is open
    fail execute
    QSqlError("42601", "QPSQL: Unable to create query", "FEL:  syntaxerror at or near  \"(\"\nLINE 1: EXECUTE  ('c1', 0)\n                 ^\n(42601)")


    QT       += core gui sql
    greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
    CONFIG += c++11
    SOURCES += \
    HEADERS += \
    FORMS += \
    qnx: target.path = /tmp/$${TARGET}/bin
    else: unix:!android: target.path = /opt/$${TARGET}/bin
    !isEmpty(target.path): INSTALLS += target

    File: main.cpp

    #include <QtSql>
    #include <QDebug>
    #include <QSqlQuery>
    #include <QSqlError>
    #include <QSqlDatabase>
    #include <QDate>
    int main(int argc, char *argv[])
    	QSqlDatabase m_db;
    	m_db = QSqlDatabase::addDatabase("QPSQL");
    	if (
    		qDebug()<<"db is open";
    	QSqlQuery query("",m_db);
    	query.prepare(QString("call myproc(:caption, :id)"));
    	query.bindValue(":caption", "c1");
    	query.bindValue(":id", 0, QSql::InOut);
    	if (query.exec())
    		qDebug()<<"fail execute";

    And here is sql to set it up

    create table mytable (
    	id bigint generated always as identity primary key,
    	caption varchar(200) default 'x' not null
    create or replace procedure myproc(
    	p_caption varchar(200),
    	p_id INOUT bigint
    language PLPGSQL 
    AS $$
    	insert into mytable (caption) 
    		values (p_caption) 
    		returning id into p_id;
    -- This proves that the procedure works
    -- call myproc('cap', null);

  • Lifetime Qt Champion

    Please check the return values of QSqlQuery::prepare() and bindValue()

  • I got false, it failed to prepare the statement.

    I read some more and it seems that PostgreSQL can only do select, update, insert, delete command. So how am I supposed to run a procedure from Qt? Is it impossible to do with prepare?
    So I have to do like

    query.exec("call myproc(....)");


  • Lifetime Qt Champion

    @OpenGL said in Minimal SQL program that fails, what is wrong with it:

    So I have to do like

    And why don't you think you can't do it this way?

  • I'm sure it will work fine to do it that way. I just liked the looks of prepared statements where I just give the parameters and don't have to write do any escaping in the sql command.
    I will try it.

  • Lifetime Qt Champion

    Ok, the problem with the escaping is now on your side - correct.

  • I guess so, thank you for your replies.

Log in to reply