Minimal SQL program that fails, what is wrong with it

OpenGL

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

Christian Ehrlicher

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

OpenGL

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

?

Christian Ehrlicher

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

OpenGL

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.

Christian Ehrlicher

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

OpenGL

I guess so, thank you for your replies.