| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| ESQL Stored Procedure OUT parameter error |
« View previous topic :: View next topic » |
| Author |
Message
|
| sam926 |
 Posted: Wed Sep 09, 2015 12:09 pm Post subject: ESQL Stored Procedure OUT parameter error |
 |
|
Novice
Joined: 01 Apr 2015
Posts: 13
|
Hi,
ENV - WMB V8, SQL Server 2008
Calling a stored procedure in ESQL which inserts records into database. The actual stored procedure on SQL Server, has error handling, which returns an error in case of any.
In ESQL, added a new INOUT parameter declared as CHARACTER to get the error message from DB. But the flow is failing with below error.
| Quote: |
| Invalid parameter 18 (''): Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead. |
ESQL code is :
| Code: |
DECLARE var1 CHARACTER;
DECLARE var2 CHARACTER;
DECLARE Result CHARACTER;
CALL INSERT_DB(var1,
var2,
Result); |
| Code: |
CREATE PROCEDURE INSERT_DB(IN var1 CHARACTER,
IN var2 CHARACTER,
INOUT DBResult CHARACTER)
LANGUAGE DATABASE EXTERNAL NAME "db.Insert_DB"; |
SQL Server procedure code :
| Code: |
ALTER PROCEDURE [db].[Insert_DB]
@var1 [varchar](100)= NULL,
@var2 [varchar](100)= NULL,
@Result [varchar](max) OUT
BEGIN TRY
|
|
|
END TRY
BEGIN CATCH
SELECT error_message()
SET @Result = error_message()
RETURN
END CATCH
|
The error returning from SQL Server is a STRING and I have declared Result as CHARACTER in ESQL. Am I declaring the parameters correctly? |
|
| Back to top |
|
 |
| nelson |
| Posted: Wed Sep 09, 2015 12:55 pm Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
| are you able to call the SP outside broker? |
|
| Back to top |
|
|
| sam926 |
| Posted: Wed Sep 09, 2015 12:59 pm Post subject: |
|
|
Novice
Joined: 01 Apr 2015
Posts: 13
|
I'm able to execute the procedure in SQL Server and error handling is working correctly.
In ESQL, instead of INOUT, if I mentioned the Result variable as OUT, I'm getting below error.
| Quote: |
| The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored procedure. |
|
|
| Back to top |
|
|
| mgk |
| Posted: Wed Sep 09, 2015 7:56 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Which platform are you running WMB on?
This sounds like a defect - if you change the varchar(max) to be varchar(1000) does it work then? If it does, raise a PMR.
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| sam926 |
| Posted: Thu Sep 10, 2015 6:26 am Post subject: |
|
|
Novice
Joined: 01 Apr 2015
Posts: 13
|
RHEL platform. I did change varchar(max) to varchar(500) and I see no change in the error.
Before raising a PMR,
- I read in one of the posts in this forum, that odbc.ini file should have a setting where procedure returns results need to be enabled. My odbc.ini file doesn't have this setting and I'm really not sure how to to do this setting for SQL Server DB.
- I followed https://www-01.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04970_.htm . Is mentioning Result parameter as INOUT in ESQL correct for SQLServer database? |
|
| Back to top |
|
|
| sam926 |
| Posted: Wed Sep 30, 2015 6:30 am Post subject: |
|
|
Novice
Joined: 01 Apr 2015
Posts: 13
|
| Raised PMR, but got informed that WMB8 doesn't support SQL Server 2005. We are already in the process of SQL Server upgrade to 2008. For now, this issue is on hold. |
|
| 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
|
|
|
|
