psqlODBC driver does not support escape sequence for calling SQL procedures '{call procedure-name[([parameter][,[parameter]]...)]}'

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

psqlODBC driver does not support escape sequence for calling SQL procedures '{call procedure-name[([parameter][,[parameter]]...)]}'

J.Bartkowiak (Inetia)
ODBC Driver version: 12.00
PostgreSQLdatabase version: PostgreSQL 12.0
Operating system: Windows 10 Pro

SQL-level procedures (new in PostgreSQL 11) are not supported by ODBC call
sequence '{call procedure-name[([parameter][,[parameter]]...)]}'.

Example:

Definition of SQL my_proc() procedure:

CREATE OR REPLACE PROCEDURE my_proc()
AS $$
BEGIN
   raise notice 'Hello from my_proc() procedure.';
END;
$$ LANGUAGE plpgsql;

Call to procedure my_test():

SQLExecDirect(hstmt, "{call my_test()}", SQL_NTS);

returns error 'my_test() is a procedure;'.

The problem is that psqlODBC driver is treating procedures like functions and
translates '{call ...}' to 'SELECT * FROM ...', which is wrong for procedures.
They must be executed using 'CALL' keyword.


Regards,
Jarek Bartkowiak


Reply | Threaded
Open this post in threaded view
|

Re: psqlODBC driver does not support escape sequence for calling SQL procedures '{call procedure-name[([parameter][,[parameter]]...)]}'

Inoue, Hiroshi-2
Hi Jarek,

There was a discussion of the CALL behavior: https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us .

regards,
Hiroshi Inoue

On 2019/10/14 22:16, J.Bartkowiak (Inetia) wrote:
ODBC Driver version: 12.00
PostgreSQLdatabase version: PostgreSQL 12.0
Operating system: Windows 10 Pro

SQL-level procedures (new in PostgreSQL 11) are not supported by ODBC call sequence '{call procedure-name[([parameter][,[parameter]]...)]}'.

Example:

Definition of SQL my_proc() procedure:

CREATE OR REPLACE PROCEDURE my_proc()
AS $$
BEGIN
  raise notice 'Hello from my_proc() procedure.';
END;
$$ LANGUAGE plpgsql;

Call to procedure my_test():

SQLExecDirect(hstmt, "{call my_test()}", SQL_NTS);

returns error 'my_test() is a procedure;'.

The problem is that psqlODBC driver is treating procedures like functions and translates '{call ...}' to 'SELECT * FROM ...', which is wrong for procedures. They must be executed using 'CALL' keyword.


Regards,
Jarek Bartkowiak


ウイルス フリー。 www.avg.com