Reputation: 314
Just started with postgresql and run into difficulties running a stored procedure from a PyQt5 client using (this time) the QPSQL driver. I set up a stored procedure in Postgres consisting in one update query with six parameters. It runs seamlessly form PgAdmin 4.
From the client I'm able to run functions as far as there are no parameters involved. When trying to run using a prepared query, I got error 42601 directly from the prepare statement:
ERROR: syntax error at or near "CALL"
LINE 1: PREPARE qpsqlpstmt_1 AS CALL es_load_text_report($1, $2, $3,...
^
(42601) QPSQL: Unable to prepare statement).
I tried all the available binding options always with the same result. After researching the site and web I could not get to any conclusion besides the guess that this seams to be driver related bug. By the way, the procedure can be run from the client using the unsafe method of placing the variable values directly in the prepare statement. (f"{var1}, {var2}....{varn}")
Stored procedure code:
CREATE OR REPLACE PROCEDURE es_edit_text_report(IN _id integer, IN _report_date,
IN _responsibleid integer,
IN _categoryid integer,
IN _description varchar,
IN _location varchar)
AS $$
BEGIN
UPDATE text_maintenance_news
SET report_date = _report_date,
reporterid = _responsibleid::smallint,
categoryid = _categoryid::smallint,
description = _description,
location = _location
WHERE id = _id;
COMMIT;
END; $$
LANGUAGE plpgsqle
Procedure PyQt5 code:
def test(self):
try:
qry = QSqlQuery(self.db)
qry.prepare("CALL es_load_text_report(:id, :report_date, :reporter, :category,"
" :description, :location)")
qry.bindValue(":id", (QVariant(self.id)))
qry.bindValue(":report_date", (QVariant(self.reportDate.date().toString('yyyy-MM-
dd'))))
qry.bindValue(":reporter", QVariant(self.comboResponsible.getHiddenData(0)))
qry.bindValue(":category", QVariant(self.comboCategory.getHiddenData(0)))
qry.bindValue(":description", QVariant(self.txtDescription.text()))
qry.bindValue(":location", QVariant(self.txtLocation.text()))
#qry.exec()
if qry.lastError().type() != 0:
raise DataError("test: qry", qry.lastError().text())
except DataError as e:
QMessageBox.warning(self, e.source, e.message, QMessageBox.Ok)
Upvotes: 1
Views: 238