Thread: CRecordset::Open postgresql procedure call don't work
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 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
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?
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
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
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