| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| ERROR :: Invoke SQLServer Stored procedure :: VARYING CURSOR |
« View previous topic :: View next topic » |
| Author |
Message
|
| raghavendrarao4 |
 Posted: Tue Jun 14, 2011 8:14 am Post subject: ERROR :: Invoke SQLServer Stored procedure :: VARYING CURSOR |
 |
|
Newbie
Joined: 22 Jan 2007
Posts: 8
|
Hi All,
The scenario is to invoke a stored procedure on SQL Server with the following definition:
| Code: |
CREATE PROCEDURE [dbo].[PROC1]
-- Add the parameters for the stored procedure here
( @ONE varchar(10),
@TWO varchar(1) OUTPUT,
@ResultSetCursor CURSOR VARYING OUTPUT )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ErrNo int,
@ProcName sysname,
@StepMsg varchar(256)
SET @TWO = NULL
-- select required fields from
IF EXISTS(SELECT 1 FROM D_Extract..APP_LOAD S
INNER JOIN D_EDB..T_LN L ON L.LN_NBR=S.LN_NBR
INNER JOIN D_EDB..T_PRPTY P ON L.LN_ID = P.LN_ID
WHERE L.LN_NBR=@ONE AND L.SYS_SRC_CD='ML')
BEGIN
SET @ResultSetCursor = CURSOR FORWARD_ONLY STATIC FOR
SELECT
S.LN_NBR,
--'Y' AS TWO,
S.LNG_FST_NAM,
S.MIDDLE_INT,
S.LNG_LST_NAM,
S.B1_NM_SUFX,
S.CB_FIRST_NAM,
S.CB_MID_INIT,
S.CB_LAST_NAM,
S.CB_NM_SUFX,
S.B3_FIRST_NAME,
S.B3_MID_INIT,
S.LST_NAME_B3,
S.B3_NM_SUFX,
S.B4_FIRST_NAME,
S.B4_MID_INIT,
S.LST_NAME_B4,
S.B4_NM_SUFX,
P.BLD_CD,
P.ST_CD,
S.PROP_CITY,
P.SUBJ_PRPTY_ST_NM,
P.SUBJ_PRPTY_UNT_NBR
FROM D_Extract..APP_LOAD S
INNER JOIN D_EDB..T_LN L ON L.LN_NBR=S.LN_NBR
INNER JOIN D_EDB..T_PRPTY P ON L.LN_ID = P.LN_ID
WHERE L.LN_NBR=@ONE AND L.SYS_SRC_CD='ML' ;
OPEN @ResultSetCursor;
INSERT INTO D_EDB..TEMP_T_MAINT_CNTL
(
[PROCES_DT],
[CMNT_TXT]
)
VALUES
(
GETDATE(),
'Details for the loan :"'+ @ONE + '" was fetched from APP_LOAD.'
)
SELECT @TWO='Y' --'Details for loan :"'+ @ONE + '" was fetched successfully.'
END
ELSE
--IF @@ROWCOUNT= 0
Begin
SET @ResultSetCursor = CURSOR FORWARD_ONLY STATIC FOR
SELECT
S.LN_NBR,
--'Y' AS TWO,
S.LNG_FST_NAM,
S.MIDDLE_INT,
S.LNG_LST_NAM,
S.B1_NM_SUFX,
S.CB_FIRST_NAM,
S.CB_MID_INIT,
S.CB_LAST_NAM,
S.CB_NM_SUFX,
S.B3_FIRST_NAME,
S.B3_MID_INIT,
S.LST_NAME_B3,
S.B3_NM_SUFX,
S.B4_FIRST_NAME,
S.B4_MID_INIT,
S.LST_NAME_B4,
S.B4_NM_SUFX,
P.SUBJ_PRPTY_UNT_NBR,
P.ST_CD,
S.PROP_CITY,
P.SUBJ_PRPTY_ST_NM,
P.SUBJ_PRPTY_UNT_NBR
FROM D_Extract..APP_FULL S
INNER JOIN D_EDB..T_LN L ON L.LN_NBR=S.LN_NBR
INNER JOIN D_EDB..T_PRPTY P ON L.LN_ID = P.LN_ID
WHERE L.LN_NBR=@ONE AND L.SYS_SRC_CD='ML'
OPEN @ResultSetCursor;
IF @@ROWCOUNT<>0
BEGIN
INSERT INTO D_EDB..TEMP_T_MAINT_CNTL
(
[PROCES_DT],
[CMNT_TXT]
)
VALUES
(
GETDATE(),
'Details for the loan :"'+ @ONE + '" was fetched from APP_FULL.'
)
SELECT @TWO='Y'--Details for loan :"'+ @ONE + '" was fetched successfully.'
END
ELSE
BEGIN
INSERT INTO D_EDB..TEMP_T_MAINT_CNTL
(
[PROCES_DT],
[CMNT_TXT]
)
VALUES
(
GETDATE(),
'Details for the loan :"'+ @ONE + '" was not found'
)
SELECT @TWO='N' --'Details for the loan :"'+ @ONE + '" was not found.'
END
END
SET @ErrNo = @@error
IF (@ErrNo <> 0)
BEGIN
SET @StepMsg = 'Failed with error number ' + CONVERT(varchar(15), @ErrNo)
EXEC utl_MaintCntl_SetStepCode @ProcName, @StepMsg
RETURN -100
END
END
|
Following is the ESQL Code used for the stored procedure invocation:
| Code: |
DECLARE loanNumber CHAR;
DECLARE dt_status CHAR;
CALL retrieveLoanDetails(loanNumber, dt_status, Environment.ResultSet[]);
|
And the stored procedure definition in ESQL is as follows:
| Code: |
CREATE PROCEDURE retrieveLoanDetails (
IN ONE CHARACTER,
OUT TWO CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.PROC1";
|
At runtime, we see the following exception invoking the stored procedure:
| Quote: |
(0x01000000:Name ):RecoverableException = (
(0x03000000:NameValue):File = '/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 767 (INTEGER)
(0x03000000:NameValue):Function = 'SqlRoutine::invoke' (CHARACTER)
(0x03000000:NameValue):Type = 'ComIbmComputeNode' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2934 (INTEGER)
(0x03000000:NameValue):Text = 'Error occured in procedure' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'retrieveLoanDetails' (CHARACTER)
)
(0x01000000:Name ):RecoverableException = (
(0x03000000:NameValue):File = '/build/S700_P/src/DataFlowEngine/ImbDatabaseManager.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 2544 (INTEGER)
(0x03000000:NameValue):Function = 'ImbDatabaseManager::getDBProcedureParameterInformation' (CHARACTER)
(0x03000000:NameValue):Type = 'ComIbmDatabaseConnectionManager' (CHARACTER)
(0x03000000:NameValue):Name = 'ComIbmDatabaseConnectionManager' (CHARACTER)
(0x03000000:NameValue):Label = 'ComIbmDatabaseConnectionManager' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2921 (INTEGER)
(0x03000000:NameValue):Text = 'The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'D_EDB.dbo.PROC1' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '2' (CHARACTER)
)
|
Going through the related links on the same forum, we tried PASSTHRU invocation:
| Code: |
DECLARE result ROW;
PASSTHRU('{CALL dbo.PROC1(?,?) INTO ?)}', loanNumber, dt_status, result);
|
which resulted in the following exception:
| Quote: |
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'PASSTHRU('{CALL dbo.PROC1(?,?) INTO ?)}', loanNumber, dt_status, result)' (CHARACTER)
)
(0x01000000:Name ):DatabaseException = (
(0x03000000:NameValue):File = '/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 347 (INTEGER)
(0x03000000:NameValue):Function = 'ImbOdbcHandle::checkRcInner' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2321 (INTEGER)
(0x03000000:NameValue):Text = 'Root SQL exception' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '-1' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 14 (INTEGER)
(0x03000000:NameValue):Text = 'libbipodbc.so' (CHARACTER)
)
(0x01000000:Name ):DatabaseException = (
(0x03000000:NameValue):File = '/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 486 (INTEGER)
(0x03000000:NameValue):Function = 'ImbOdbcHandle::checkRcInner' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2322 (INTEGER)
(0x03000000:NameValue):Text = 'Child SQL exception' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = '20136' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '0' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = '[IBM][ODBC SQL Server Driver]20136' (CHARACTER)
)
)
|
Is this an issue with using the 'VARYING CURSOR' OUTPUT variable with the stored procedure..?
Thanks,
Raghavendra. |
|
| Back to top |
|
 |
| raghavendrarao4 |
| Posted: Tue Jun 14, 2011 8:22 am Post subject: ERROR :: Invoke SQLServer Stored procedure :: VARYING CURSOR |
|
|
Newbie
Joined: 22 Jan 2007
Posts: 8
|
Hi All,
Forgot to mention -
WebSphere Message Broker version - 7.0.0.2 (fix pack 2) on zLinux server. Database :: MS SQL Server 2000 version...
Raghavendra. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jun 14, 2011 8:23 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
What options have you selected in your ODBC connection (windows) or please post the relevant section of the file pointed to by $ODBCINI (unix/Linux)?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| raghavendrarao4 |
| Posted: Tue Jun 14, 2011 8:35 am Post subject: |
|
|
Newbie
Joined: 22 Jan 2007
Posts: 8
|
Here is the relevant section from odbc.ini
| Code: |
SQLSERVERDB=DataDirect 6.0 SQL Server Wire Protocol
[D_EDB]
Driver=/usr/WMB70/ODBC/V6.0/lib/UKmsss24.so
Description=DataDirect 6.0 SQL Server Wire Protocol
Address=host001.corp.abc.com,1433
Database=D_EDB
AnsiNPW=Yes
QuotedId=No
ColumnSizeAsCharacter=1
LoginTimeout=0
|
|
|
| Back to top |
|
|
| missing_link |
| Posted: Wed Jun 15, 2011 12:07 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
MS SQL Server 2000??
Is that actually supported with broker 7? |
|
| Back to top |
|
|
| raghavendrarao4 |
| Posted: Wed Jun 15, 2011 3:52 am Post subject: |
|
|
Newbie
Joined: 22 Jan 2007
Posts: 8
|
We're able to execute SQL SELECT queries against the table on SQL Server 2000, without any issue  . This issue we see is when we invoke the stored procedure  ... |
|
| Back to top |
|
|
| missing_link |
| Posted: Wed Jun 15, 2011 4:25 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
That maybe, but if the DB isnt supported, somethings may work, somethings may not. I'm not familiar with zlinux, but MSSQL Svr 2000 isnt listed here: http://www-01.ibm.com/support/docview.wss?rs=849&uid=swg27017130&wv=1
We've (mistakenly) had overloaded DB stored_procs since v5, but only now on 6109 starting to have the odd issue...and even then not with all overloaded procedures. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 15, 2011 4:27 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
More importantly, if it's not supported, then you can't usually get IBM to fix the things that don't work. |
|
| Back to top |
|
|
| raghavendrarao4 |
| Posted: Wed Jun 15, 2011 7:01 am Post subject: |
|
|
Newbie
Joined: 22 Jan 2007
Posts: 8
|
We did look at the system requirements earlier  . Our trial runs with fix pack 2 (7.0.0.2) and SQLServer 2000 (SELECT) via ODBC went through , however, got stuck at the stored procedure level. 
SQLServer 2000 is in the process of being migrated to 2008, guess we may need to try with the SQL Server 2008 version.
|
|
| Back to top |
|
|
| raghavendrarao4 |
| Posted: Wed Jun 15, 2011 7:28 am Post subject: |
|
|
Newbie
Joined: 22 Jan 2007
Posts: 8
|
To re-validate, we modified the DB stored procedure with simple parameters, removed CURSOR/Result set output parameter:
| Code: |
CREATE PROCEDURE retrieveLoanDetails (
IN ONE CHARACTER,
OUT TWO CHARACTER
)
LANGUAGE DATABASE
EXTERNAL NAME "dbo.PROC1";
|
We still face the same problem:
| Quote: |
(0x01000000:Name ):RecoverableException = (
(0x03000000:NameValue):File = '/build/S700_P/src/DataFlowEngine/ImbDatabaseManager.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 2544 (INTEGER)
(0x03000000:NameValue):Function = 'ImbDatabaseManager::getDBProcedureParameterInformation' (CHARACTER)
(0x03000000:NameValue):Type = 'ComIbmDatabaseConnectionManager' (CHARACTER)
(0x03000000:NameValue):Name = 'ComIbmDatabaseConnectionManager' (CHARACTER)
(0x03000000:NameValue):Label = 'ComIbmDatabaseConnectionManager' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2921 (INTEGER)
(0x03000000:NameValue):Text = 'The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored porocedure.' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'D_EDB.dbo.PROC1' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '2' (CHARACTER)
)
)
|
We wouldn't be able to report this issue with IBM, not sure if we'd face similar issues with SQLServer 2008. |
|
| Back to top |
|
|
| missing_link |
| Posted: Thu Jun 16, 2011 4:27 am Post subject: |
|
|
Acolyte
Joined: 08 Jan 2004
Posts: 60
|
| But if you did get the same issue with 2008, you would be able to log a PMR with IBM. |
|
| 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
|
|
|
|
