ERROR: syntax error at or near "("\nLINE 1: EXECUTE (0, '2023-01-01', 2)\n ^\n(42601) QPSQL: Unable to create query

John Hon

Hello all,

I have a problem insert a record through procedure call. My function is:

CREATE OR REPLACE PROCEDURE public.insert_expense_detail_record(
IN expense_id integer,
IN expense_date date,
IN expense_amount numeric)
LANGUAGE 'sql'
AS $BODY$
insert into expense_details(expense_id, expense_date, expense_amount) values ($1, $2, $3);
$BODY$;
ALTER PROCEDURE public.insert_expense_detail_record(integer, date, numeric)
OWNER TO postgres;

My code is:
QSqlQuery query;

query.prepare("call insert_expense_detail_record(:expense_id, :expense_date, :expense_amount)");
// query.prepare("insert into expense_details(expense_id, expense_date, expense_amount, remarks) values (:expense_id, :expense_date, "
// ":expense_amount, :remarks)");
query.bindValue(":expense_id", expense_details.get_expense_details_id());
query.bindValue(":expense_date", expense_details.get_expense_date());
query.bindValue(":expense_amount", expense_details.get_expense_amount());
//query.bindValue("remarks", expense_details.get_remarks());
if (query.exec() != true)
{
QString err = query.lastError().text();
}

the debugger reports this error:

ERROR: syntax error at or near "("\nLINE 1: EXECUTE (0, '2023-01-01', 2)\n ^\n(42601) QPSQL: Unable to create query

if I replace the procedure call with the above insert table statement, it work perfect fine. I run the procedure call in pgadmin 4, a record is inserted successfully. What did I do wrong? Please advice.

Christian Ehrlicher

Please format your code with the code tags (</>). And then add error checking to your code - QSqlQuery::prepare() also returns a boolean which you have to check.

ChrisW67

@John-Hon Please use the code tool (</>) above, or surround code with matching sets of three backticks.

This looks like the procedure name is not being resolved in the Postgresql plugin. Does the behaviour change if you explicitly use the full name?

bool ok = query.prepare("call public.insert_expense_detail_record(:expense_id, :expense_date, :expense_amount)");

John Hon

@Christian-Ehrlicher said in ERROR: syntax error at or near "("\nLINE 1: EXECUTE (0, '2023-01-01', 2)\n ^\n(42601) QPSQL: Unable to create query:

Please format your code with the code tags (</>). And then add error checking to your code - QSqlQuery::prepare() also returns a boolean which you have to check.

I am new to the system and thanks for the informing about the code tags. I repost the stored procedure statement below. I tested this stored procedure in pgadmin 4, it works. In additional, I added what you suggested to query.prepare statement below, too.

CREATE OR REPLACE PROCEDURE public.insert_expense_detail_record(
	IN expense_id integer,
	IN expense_date date,
	IN expense_amount numeric)
LANGUAGE 'sql'
AS $BODY$
insert into expense_details(expense_id, expense_date, expense_amount) values ($1, $2, $3);
$BODY$;
ALTER PROCEDURE public.insert_expense_detail_record(integer, date, numeric)
    OWNER TO postgres;

bool statement_ok = query.prepare("call public.insert_expense_detail_record(:expense_id, :expense_date, :expense_amount)"); 
if (statement_ok == false)
{
        QString err = query.lastError().text();
}

the query preparation fails, the error is

ERROR: syntax error at or near "call"\nLINE 1: PREPARE qpsqlpstmt_1 AS call public.insert_expense_detail_re...\n ^\n(42601) QPSQL: Unable to prepare statement

Is there a way to figure out what goes wrong with my code?

Christian Ehrlicher

I would guess postgresql does not allow 'PREPARE <stmtid> as CALL' as described here: https://www.postgresql.org/docs/current/sql-prepare.html - the QPSql driver needs to use other functions then but this is not implemented.

ChrisW67

If your stored procedure is only going to do an INSERT i.e. no other logic, you could just prepare the INSERT statement once in your code and then bind and execute that statement as required. This code be wrapped into a single function in your C++ code to provide somewhere central your C++ might do other checks/work before the insert.

If the stored procedure is going to do more than just the insert perhaps you could explore the options available to triggers in Postgresql to have that extra functionality in the database. A BEFORE INSERT trigger can be useful to do other logic/checks and even abort an insert.