ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » calling Stored procedure with OUT Params from MYSQL in IIB

Post new topic  Reply to topic
 calling Stored procedure with OUT Params from MYSQL in IIB « View previous topic :: View next topic » 
Author Message
kastle
PostPosted: Tue Jan 28, 2014 4:32 am    Post subject: calling Stored procedure with OUT Params from MYSQL in IIB Reply with quote

Novice

Joined: 28 Jan 2014
Posts: 14

/** 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]
Back to top
View user's profile Send private message
smdavies99
PostPosted: Tue Jan 28, 2014 5:59 am    Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

Moderator, can this be moved to the Broker Forum
_________________
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
View user's profile Send private message
Vitor
PostPosted: Tue Jan 28, 2014 7:17 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

Don't double post; it won't get you an answer any faster!

The error message seems clear, and seems to tie back to the code you've posted. Fix the code.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
kastle
PostPosted: Wed Jan 29, 2014 11:28 pm    Post subject: Reply with quote

Novice

Joined: 28 Jan 2014
Posts: 14

here, I have tried with only IN parameters in MYSQL is working properly . But it is giving problem with the OUT and INOUT parameters in MYSQL .

I have also checked with IN and OUT parameters in DB2(database ) it is working fine .
Please send me the working code for this issue.
Back to top
View user's profile Send private message
dogorsy
PostPosted: Thu Jan 30, 2014 3:05 am    Post subject: Reply with quote

Knight

Joined: 13 Mar 2013
Posts: 553
Location: Home Office

kastle wrote:
.
Please send me the working code for this issue.

What ?!!!
Please read "supported databases" in the infocentre.
Back to top
View user's profile Send private message
kastle
PostPosted: Thu Jan 30, 2014 4:06 am    Post subject: Reply with quote

Novice

Joined: 28 Jan 2014
Posts: 14

But it is working with IN parameters in MYSQL procedure but it is giving problem with OUT parms in IIB .

May be this issue has arised with ODBC Driver version(5.2(a))and i am trying to make a connectivity with MYSQL(5.6).
Please give me the solution for rectify this issue.
Back to top
View user's profile Send private message
dogorsy
PostPosted: Thu Jan 30, 2014 4:12 am    Post subject: Reply with quote

Knight

Joined: 13 Mar 2013
Posts: 553
Location: Home Office

kastle wrote:
But it is working with IN parameters in MYSQL procedure but it is giving problem with OUT parms in IIB .

May be this issue has arised with ODBC Driver version(5.2(a))and i am trying to make a connectivity with MYSQL(5.6).
Please give me the solution for rectify this issue.


Have you read the "supported databases" doc in the infocentre as suggested ?!!!
Back to top
View user's profile Send private message
kastle
PostPosted: Fri Jan 31, 2014 9:20 am    Post subject: Reply with quote

Novice

Joined: 28 Jan 2014
Posts: 14

i got it . thanks for your reply
Back to top
View user's profile Send private message
saviobarr
PostPosted: Mon Mar 27, 2017 9:29 am    Post subject: Reply with quote

Centurion

Joined: 21 Oct 2014
Posts: 100
Location: Sao Paulo, Brazil

kastle wrote:
i got it . thanks for your reply

Hi kastle,
Did you get the solution? Can you share here? I am getting the same error. I read the documentation available on KC, but it does not mention restrictions to call MySQL procedures from ESQL... I am about to use Java instead of ESQL.

Many thanks

Savio Barros
_________________
Go as far as you can go. Then go farther!
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » calling Stored procedure with OUT Params from MYSQL in IIB
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.