| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Calling DB2 stored procedure issue |
« View previous topic :: View next topic » |
| Author |
Message
|
| lvraa |
 Posted: Tue Jun 29, 2004 4:16 am Post subject: Calling DB2 stored procedure issue |
 |
|
Novice
Joined: 18 May 2004
Posts: 12
Location: Denmark
|
Hi
I have a WBIMB5.0 CSD2 flow with a few calls to DB2 stored procedures. They all work fine except for one of them.
The error I get is:
| Code: |
| [IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000 |
I have pasted the ExceptionList dump below.
I’m using PASSTHRU:
| Code: |
| SET InsertResult[] = PASSTHRU(INSERTSQL); |
The INSERTSQL variable contain the following data when passed to the PASSTHRU call:
| Code: |
call db2admin.INSERTORGDATA(51005893, '10009009','IT DEVELOPMENT', 949,50
000001, 'CORPORATE IT\n','X', 'OTTILIAVEJ 9','', '2500','Valby', 'DK','+45363013
11', '+4526262626', '0100') |
Executing the same SQL from the DB2 command line works fine. The Procedure code has been pasted in below.
The ODBC Driver is: IBM DB2 ODBC DRIVER 8.01.04.341
Any ideas?
Regards Lars
---------------------------------------------------
ExceptionList Dump
| Code: |
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 153
(0x03000000):Function = 'SqlStatementGroup::execute'
(0x03000000):Type = 'ComIbmDatabaseNode'
(0x03000000):Name = 'WMQ01/HR_ORG_XML/MAIN#FCMComposite_1_12'
(0x03000000):Label = 'WMQ01.HR_ORG_XML.MAIN.Database'
(0x03000000):Text = 'Error detected, rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'WMQ01.HR_ORG_XML.WMQ01_HR_EMPL_XML_MAIN_QUERY_DATABASE.QueryDatabase'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '33.4'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'SET Environment.Variables.result[ ] = PASSTHRU(INSERTSQL);'
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 181
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Root SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2321
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-1'
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 296
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Child SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '24000'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-99999'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000'
)
) |
----------------------------
Stored proc
| Code: |
CREATE PROCEDURE DB2ADMIN.INSERTORGDATA (IN vORGID INTEGER, IN vSHORTNAME VARCHAR(256), IN vLONGNAME VARCHAR(256),
IN vOLDDEPTNUM INTEGER, IN vPARENTORGID INTEGER, IN vDESCRIPTION VARCHAR(1024), IN vSTAFF CHAR(10),
IN vSTREET VARCHAR(256), IN vHOUSENUM CHAR(20), IN vPOSTALCODE CHAR(20), IN vCITY VARCHAR(256),
IN vCOUNTRY VARCHAR(256), IN vPHONE CHAR(20), IN vFAX CHAR(20), IN vCOMPANYCODE CHAR(40))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
---------------------
BEGIN ATOMIC
INSERT INTO db2admin.SAPHRORGDATA VALUES(vORGID, vSHORTNAME, vLONGNAME ,
vOLDDEPTNUM, vPARENTORGID, vDESCRIPTION, vSTAFF,
vSTREET, vHOUSENUM, vPOSTALCODE, vCITY, vCOUNTRY,
vPHONE, vFAX, vCOMPANYCODE);
END |
|
|
| Back to top |
|
 |
| lvraa |
| Posted: Tue Jun 29, 2004 4:58 am Post subject: SOLVED |
|
|
Novice
Joined: 18 May 2004
Posts: 12
Location: Denmark
|
The problem turned out to be quite simple. Trying to assign the result to an array (SET InsertResult[] =...) is not healthy if the procedure does not have an open cursor WITH RETURN when ending execution.
PASSTHRU(INSERTSQL); works fine...
/Lars
_________________
Kind regards
Lars Vraa
lavr@lundbeck.com |
|
| 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
|
|
|
|
