ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Invoke postgres DBprocedures with cursor - ACEv12 via ODBC

Post new topic  Reply to topic
 Invoke postgres DBprocedures with cursor - ACEv12 via ODBC « View previous topic :: View next topic » 
Author Message
vickas
PostPosted: Fri Jun 02, 2023 5:51 am    Post subject: Invoke postgres DBprocedures with cursor - ACEv12 via ODBC Reply with quote

Centurion

Joined: 18 Aug 2013
Posts: 126

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};
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Invoke postgres DBprocedures with cursor - ACEv12 via ODBC
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.