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. Minimal SQL program that fails, what is wrong with it
Forum Updated to NodeBB v4.3 + New Features

Minimal SQL program that fails, what is wrong with it

Scheduled Pinned Locked Moved Unsolved General and Desktop
7 Posts 2 Posters 820 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.
  • O Offline
    O Offline
    OpenGL
    wrote on 1 Nov 2020, 06:58 last edited by
    #1

    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);
    
    1 Reply Last reply
    0
    • C Offline
      C Offline
      Christian Ehrlicher
      Lifetime Qt Champion
      wrote on 1 Nov 2020, 08:51 last edited by
      #2

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

      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
      Visit the Qt Academy at https://academy.qt.io/catalog

      1 Reply Last reply
      1
      • O Offline
        O Offline
        OpenGL
        wrote on 1 Nov 2020, 13:30 last edited by
        #3

        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(....)");
        

        ?

        1 Reply Last reply
        0
        • C Offline
          C Offline
          Christian Ehrlicher
          Lifetime Qt Champion
          wrote on 1 Nov 2020, 14:50 last edited by
          #4

          @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?

          Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
          Visit the Qt Academy at https://academy.qt.io/catalog

          1 Reply Last reply
          0
          • O Offline
            O Offline
            OpenGL
            wrote on 1 Nov 2020, 18:26 last edited by
            #5

            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.

            1 Reply Last reply
            0
            • C Offline
              C Offline
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on 1 Nov 2020, 18:32 last edited by
              #6

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

              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
              Visit the Qt Academy at https://academy.qt.io/catalog

              1 Reply Last reply
              0
              • O Offline
                O Offline
                OpenGL
                wrote on 1 Nov 2020, 18:56 last edited by
                #7

                I guess so, thank you for your replies.

                1 Reply Last reply
                0

                2/7

                1 Nov 2020, 08:51

                topic:navigator.unread, 5
                • Login

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