MQSeries.net :: View topic - calling Stored procedure with OUT Params from MYSQL in IIB

kastle · Posted: Tue Jan 28, 2014 7:14 am Post subject:

/** here i am calling stored Procedure with OUT Parameters in MYSQL (version 5.6) from IIB (version 9.0) . I have mentioned the stored Procedure , ESQL Code as well as Exception List . Please reply ASAP **/

Stored Procedure in MYSQL version 5.6 ------------

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `newprocedure`(IN abc int, OUT response int)
BEGIN
INSERT INTO `eifhsmysql`.`eifhs_abve_svnty_leave`
(`clstr_ID`,
`emp_code`,
`abve_svnty_leave_rsc_code`,
`abve_svnty_leave_preprd_mnth`,
`abve_svnty_leave_tot_num_stff`,
`abve_svnty_leave_tot_acumlat_lveday`,
`abve_svnty_leave_ID`)
VALUES ('CS001','EMP001','CIT',97.854200,14,1178,abc);
SET @response =123;

SELECT * from `eifhsmysql`.`eifhs_abve_svnty_leave`;
END

----------------------

ESQL Code for calling Procedure in IIB version 9.0

CREATE PROCEDURE newprocedureout(IN p1 INTEGER, OUT response INTEGER) LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "eifhsmysql.new_procedure";

CREATE COMPUTE MODULE callProc_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
-- CALL CopyMessageHeaders();
-- CALL CopyEntireMessage();

DECLARE p1 INTEGER ;
SET p1 = 752;
DECLARE response INTEGER;

CALL newprocedureout(p1,response,Environment.result[]);
RETURN TRUE;
END;

CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;

CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;
-------------------------------------

Exception while using OUT Parameters

Text [MySQL][ODBC 5.2(a) Driver][mysqld-5.6.15]OUT or INOUT argument 2 for routine eifhsmysql.newprocedure is not a variable or NEW pseudo-variable in BEFORE trigger

[/code]