Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/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)")
    

    File: my.pro

    QT       += core gui sql
    
    greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
    
    CONFIG += c++11
    
    DEFINES += QT_DEPRECATED_WARNINGS
    
    SOURCES += \
        main.cpp 
    
    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");
    	m_db.setHostName("localhost");
    	m_db.setDatabaseName("dbname");
    	m_db.setUserName("username");
    	m_db.setPassword("password");
    	if (m_db.open())
    		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()<<"executed";
    	else
    		qDebug()<<"fail execute";
    	qDebug()<<query.lastError();
    	
    	m_db.commit();
    }
    

    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 $$
    begin
    	insert into mytable (caption) 
    		values (p_caption) 
    		returning id into p_id;
    end;
    $$;
    
    -- 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 https://www.postgresql.org/docs/current/sql-prepare.html 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