| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How to return values from stored procedure in MQSI V2. |
« View previous topic :: View next topic » |
| Author |
Message
|
| mquser321 |
 Posted: Tue Feb 04, 2003 3:42 pm Post subject: How to return values from stored procedure in MQSI V2. |
 |
|
Newbie
Joined: 04 Feb 2003
Posts: 2
|
Hello,
I am trying to return some values from stored procedure(Sybase) in MQSI V2.1
SET OutputRoot.XML.Row[] = PASSTHRU('{call st_proc_name}');
and getting the following error:
BIP2321.
Is there any solution to this problem?
Regards |
|
| Back to top |
|
 |
| philip.baker |
| Posted: Tue Feb 04, 2003 4:35 pm Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
mquser321,
BIP2321 implies some issues with connecting to the intended database.
Calling a stored procedure still uses an ODBC connection.
On the Broker server, have you created the System DSN to the Sybase database using the Sybase driver?
Have you added the DSN to the compute node that does the calling of the stored procedure?
_________________
Regards,
Phil |
|
| Back to top |
|
|
| mquser321 |
| Posted: Wed Feb 05, 2003 6:11 am Post subject: mquser321 |
|
|
Newbie
Joined: 04 Feb 2003
Posts: 2
|
Phil,
Yes,
On the Broker server, I have created the System DSN to the Sybase database using the Sybase driver
And Yes,
I have added the DSN to the compute node that does the calling of the stored procedure.
I was able to run a simple SELECT statement(using the same DSN, DB, Server).
My Question was how to extract data from ResultSet into the XML Message.
Regards, |
|
| Back to top |
|
|
| philip.baker |
| Posted: Wed Feb 05, 2003 9:00 am Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
mquser321,
The error specified indicates a database error of some kind. I have used WMQI2.1 with Oracle stored procedures. You may want to search the mqseries.net site to see if anyone has had success with using calls to Sybase stored procedures. From a Redbook dated May 3,2002 , see excerpt below:
Chapter 9. Databases and XA
External database stored procedures
You can call external database procedures using ESQL (DB2 or Oracle stored procedure are supported). To call an external database stored procedure you must define it in the databases as well as the broker.
Example ESQL code of defining and calling external database stored procedure in a compute node is shown in Example 9-2 on page 395.
Example 9-2 Define and call the external stored procedure
--Call the external database stored procedure
CALL DeletePassenger1(
InputBody.CancelReservationRequest.ListOfReservations.ReservationNumber[J]);
--define the external database stored procedure
CREATE PROCEDURE DeletePassenger1(IN ReservationNo CHARACTER
)EXTERNAL NAME DBDELETEPASSENGER1;
6579ch09-XA.fm Draft Document for Review May 3, 2002 1:49 pm
Sample to define a DB2 database stored procedure is shown in Example 9-3 on page 396:
Example 9-3 Define DB2 stored procedure
CREATE PROCEDURE DBDELETEPASSENGER1 (IN reservNo CHAR(40))
LANGUAGE SQL
BEGIN
DELETE FROM PASSENGERTB WHERE RESERVATIONNO = reservNo;
END@
Sample to define an Oracle database stored procedure is shown in Example 9-4
on page 396:
Example 9-4 Define Oracle stored procedure
CREATE OR REPLACE PROCEDURE ORADELETEPASSENGER1
(reservNo IN VARCHAR2)
AS
BEGIN
DELETE FROM PASSENGERTB WHERE RESERVATIONNO = reservNo;
END;
/
_________________
Regards,
Phil |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Jun 19, 2003 2:27 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi,
Was searching for something else and noticed this thread.
Thought it worth pointing out that the suggestion by Philip is "not supported" in that the CREATE PROCEDURE ... EXTERNAL NAME command can't be used to call a Sybase stored procedure ...
CREATE PROCEDURE was released as part of WMQIv2.1 CSD02 (I think ?) and the readme says :
| Quote: |
support for this new functionality is limited to DB2 Version 7.1 and Oracle 8.1.6 and 8.1.7
|
The WMQIv2.1 CSD03 readme also includes the following :
| Quote: |
Point 81
New CREATE PROCEDURE command is not currently supported on DB2 6.1, Sybase and SQLServer
|
The PASSTHRU can be used to return a result set (with quite a few limitations) but we would have needed to see the full error message and Sybase stored procedure definition to understand why this was not working in this case.
_________________
Regards, Ian |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
