MQSeries.net :: View topic - Invoke postgres DBprocedures with cursor

vickas

We had interfaces invoking DB procedures with cursors on ORACLE database with only one result set and were able to invoke , however now the database was replaced from ORACLE to POSTGRES and are expected to invoke the db procedures with cursors hosted on postgres DB.
The difference i see is that with the parameters in the definition of DB procedure on oracle vs postgres is, for oracle the cursor is always OUT whereas postrgres is INOUT , meaning it expects the name of the cursor to be passed.

The ESQL shows compilation error when passed all 6 parameters saying not declared for these arguments untill DYNAMIC RESULT SETS 1 is commented out but if we comment out how would be get the result set ?
When invoked with same parameters as of oracle it throws runtime error saying
The procedure "&1" with "&2" parameters could not be match with a corresponding Database stored porocedure.

How do we invoke such from ESQL ?
How and what we pass in for the last INOUT cursor parameter ?

CREATE PROCEDURE fetch_data(IN char1 CHARACTER,IN char2 CHARACTER,IN char3 CHARACTER,IN char4 CHARACTER, INOUT Error CHARACTER,INOUT cursorName CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "FETCH_DATA";

postgres DB proc definition

CREATE OR REPLACE PROCEDURE fetch_data(
IN char1 character,
IN char2 character,
IN char3 character,
IN char4 character,
INOUT char5 character,
INOUT cur refcursor)
LANGUAGE 'plpgsql'

However it was like this in oracle and was invoking with below esql and working is fine

create or replace PROCEDURE "FETCH_DATA" (
char1 IN CHARACTER,
char2 IN CHARACTER,
char3 IN CHARACTER,
char4 IN CHARACTER,
char5 OUT CHARACTER,
Cur OUT SYS_REFCURSOR )

CREATE PROCEDURE fetch_data(IN char1 CHARACTER,IN char2 CHARACTER,IN char3 CHARACTER,IN char4 CHARACTER, OUT Error CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "FETCH_DATA";

CALL fetch_data('input1','input2','input3','input4',Errorout,outputRow.Details.rec[]) IN Database.{DSN}.{SCHEMA};