CRecordset::Open postgresql procedure call don't work

On Mon, Jul 15, 2019 at 6:40 AM jeanclaude marzin <jeanclaude.marzin@sfr.fr>
wrote:

‌Hi
I migrate from MSSQL to postgresql 11. I translate MSSQL stored procedure
to Postgresql one :

CREATE PROCEDURE procacp ()
LANGUAGE SQL
AS $$
SELECT tabjdbexploit.jdbeid, tabjdbexploit.jdbeproc,
tabjdbexploit.jdbedate, tabjdbexploit.jdbetypemsg,
tabjdbexploit.jdbeurg, tabjdbexploit.jdbeconfid,
tabjdbexploit.jdbeetat, tabmsgacp.acpid,
tabmsgacp.acpnumserie,
tabmsgacp.acpsignserv, tabmsgacp.acpnumligne,
tabmsgacp.acpiaorigine, tabmsgacp.acpgdh,
tabmsgacp.acperv, tabmsgacp.acpcdu,
tabmsgacp.acpdir, tabmsgacp.acppere,
tabmsgacp.acpcomplement, tabmsgacp.acpsection

FROM tabjdbexploit INNER JOIN
tabmsgacp ON tabjdbexploit.jdbeid = tabmsgacp.acpid

ORDER BY tabjdbexploit.jdbedate ASC
$$;

All seems OK in PgAdmin 4, procedure is created

I use ODBC and Crecorset in C++

When i use the call strSQL = "{CALL procacp()}". It don't work :

Message error : ERROR: procacp() is a procedure

I don't know what to do

Thanks in advance for any assistance

Jean-Claude

I am fairly sure that you need to make an FUNCTION and not a PROCEDURE.
Functions return values. Procedures do not. Why not try replacing the word
PROCEDURE with FUNCTION and give it another try?

ref: https://www.postgresql.org/docs/11/xfunc.html

--
We all agree on the necessity of compromise. We just can't agree on when
it's necessary to compromise. -- Larry Wall

Maranatha! <><
John McKown

Hi

Dne po 15. 7. 2019 13:40 uživatel jeanclaude marzin <
jeanclaude.marzin@sfr.fr> napsal:

‌Hi
I migrate from MSSQL to postgresql 11. I translate MSSQL stored procedure
to Postgresql one :

CREATE PROCEDURE procacp ()
LANGUAGE SQL
AS $$
SELECT tabjdbexploit.jdbeid, tabjdbexploit.jdbeproc,
tabjdbexploit.jdbedate, tabjdbexploit.jdbetypemsg,
tabjdbexploit.jdbeurg, tabjdbexploit.jdbeconfid,
tabjdbexploit.jdbeetat, tabmsgacp.acpid,
tabmsgacp.acpnumserie,
tabmsgacp.acpsignserv, tabmsgacp.acpnumligne,
tabmsgacp.acpiaorigine, tabmsgacp.acpgdh,
tabmsgacp.acperv, tabmsgacp.acpcdu,
tabmsgacp.acpdir, tabmsgacp.acppere,
tabmsgacp.acpcomplement, tabmsgacp.acpsection

FROM tabjdbexploit INNER JOIN
tabmsgacp ON tabjdbexploit.jdbeid = tabmsgacp.acpid

ORDER BY tabjdbexploit.jdbedate ASC
$$;

It cannot to work - PostgreSQL procedures are like Oracle's procedures -
cannot returns any result. Only OUT variables can be changed.

Regards

Pavel