MQSeries.net :: View topic - Not able to call stored procedure from DB using UDP

chetu777 · Posted: Tue Jan 03, 2012 9:00 pm Post subject:

Hi All,

I am trying to call stored procedure and I was successfull calling it, but when I tried using UDP for the same I was not able to.

I am using the DB schema name and tablename as UDP variables. But when I assign the UDP values to an environment variable to verify whether I am getting the values, yes I am able to see the UDP value assigned to the environment variable.

When I call the stored procedure, it is taking the UDP variable name itself as the value and not the value of the variable.

--UDP variables for connecting to DB
DECLARE DBSCHEMA EXTERNAL CHARACTER 'SYSPROC';
DECLARE DBTABLENAME EXTERNAL CHARACTER 'DB2S';

CREATE COMPUTE MODULE MessageIntegration_StoreProcDB_MsgFlow_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE result BOOLEAN;
CALL Min_MaxPrice()INTO result;
IF ( result = TRUE) then
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;

CREATE PROCEDURE Min_MaxPrice() RETURNS BOOLEAN
BEGIN

DECLARE SQLState1 CHARACTER;
DECLARE SQLErrorText1 CHARACTER;
DECLARE SQLCode1 INTEGER;
DECLARE SQLNativeError1 INTEGER;

DECLARE IN_PARM_ITMS CHARACTER InputRoot.XML.Item.Dir_Item_I;
DECLARE OUT_PARM_RET_CODE INTEGER;
DECLARE OUT_PARM_ITM_DETS CHARACTER;

--Calling the stored procedure from DB using UDP which is not working.
CALL TCI(IN_PARM_ITMS,OUT_PARM_RET_CODE,OUT_PARM_ITM_DETS) IN Database.DBTABLENAME.DBSCHEMA;

--The below call is working fine
--CALL TCI(IN_PARM_ITMS,OUT_PARM_RET_CODE,OUT_PARM_ITM_DETS) IN Database.DB2S.DBSchema;

SET SQLState1 = SQLSTATE;
SET SQLCode1 = SQLCODE;
SET SQLErrorText1 = SQLERRORTEXT;
SET SQLNativeError1 = SQLNATIVEERROR;
IF(SQLCode1 <> 0) THEN
THROW USER EXCEPTION MESSAGE 2950 VALUES( 'DB exception SQL State' , SQLState1 , SQLCode1 , SQLNativeError1 , SQLErrorText1 );
END IF;

SET OutputRoot.XML.Item.DIR_ITEM_I = OUT_PARM_RET_CODE;
SET OutputRoot.XML.Item.Min_Reg_Retl = OUT_PARM_ITM_DETS;
RETURN TRUE;
END;

CREATE PROCEDURE TCI(IN IN_PARM_ITMS CHAR, OUT OUT_PARM_RET_CODE INTEGER, OUT OUT_PARM_ITM_DETS CHAR) LANGUAGE DATABASE EXTERNAL NAME "TCI";

END MODULE;

Can you help me regarding this. Thanks in advance.