web stats
Simple Call with Registered Out Parameter Not Working "Boo!" - Mirth Community

Go Back   Mirth Community > Mirth Connect > Development

Reply
 
Thread Tools Display Modes
  #1  
Old 01-22-2018, 07:33 AM
mmall mmall is offline
Mirth Newb
 
Join Date: Jun 2014
Posts: 10
mmall is on a distinguished road
Default Simple Call with Registered Out Parameter Not Working "Boo!"

In the past, I have created several channels with calls to stored procedures that return out parameters with no problems. Those channels continue to work, now. But, suddenly, I now can't create a new channel with a simple, basic call and get it to work. I have no idea what I am doing wrong.

Just to troubleshoot, I'm trying to do a very simple procedure that returns a single out parameter and I'm getting the "boo!" error.

Stored Procedure:

*************
CREATE DEFINER=`[User]`@`%` PROCEDURE `IsInternalDA`(vcDA varchar(200), out iIsInternalDA int)
begin

-- set iIsInternalDA = exists(select USER_NAME from JAMES_USER where USER_NAME = vcDA);

set iIsInternalDA = 0;

end
*************

This procedure works correctly when I call it in MySQL Workbench.

JavaScript in Mirth Channel (This is the ENTIRE script):

*************
importPackage( java.sql );

var dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DirectMail?noAccessToProcedureBodies=true", "[User Name]", "[Password]");

var pCall = dbConn.prepareCall('{call IsInternalDA(?,?)}');

var da = 'test@directdemodomain.com';

pCall.setString(1, da);

pCall.registerOutParameter(2, java.sql.Types.INTEGER);

pCall.executeUpdate();

*************

When I deploy and run this, the following error is logged.

*************
Transformer error
ERROR MESSAGE: Error evaluating transformer
com.mirth.connect.server.MirthJavascriptTransforme rException:
CHANNEL: TestIsInternalDA
CONNECTOR: sourceConnector
SCRIPT SOURCE: TRANSFORMER
SOURCE CODE:
177:
178: pCall.setString(1, da);
179:
180: pCall.registerOutParameter(2, java.sql.Types.INTEGER);
181:
182: pCall.executeUpdate();
183: if ('xml' === typeof msg) {
184: if (msg.hasSimpleContent()) {
185: msg = msg.toXMLString();
186: }
LINE NUMBER: 182
DETAILS: Wrapped java.sql.SQLException: boo!
at 40ad00ff-c451-416d-9bb1-58a568b002b5:182 (doTransform)
at 40ad00ff-c451-416d-9bb1-58a568b002b5:204 (doScript)
...

*************

Lines 183 - 186 above are not in my actual script.

Last edited by mmall; 01-22-2018 at 07:45 AM.
Reply With Quote
  #2  
Old 01-22-2018, 09:21 AM
mmall mmall is offline
Mirth Newb
 
Join Date: Jun 2014
Posts: 10
mmall is on a distinguished road
Default Bizzar: Procedure works with different name

Okay. Something is weird. I created another stored procedure called ReturnOutParam with exactly the same code as the original procedure, IsInternalDA. The new procedure works correctly when called from the channel; but, the old procedure still generates the "boo!" error.

I modified the original procedure called "IsInternalDA" as follows (I also renamed the original stored procedure and created a new one with the original name with the code below. This made no difference.):

***************
CREATE DEFINER=`[USER]`@`%` PROCEDURE `IsInternalDA`(vcDA varchar(100), out iIsInternalDA int)
BEGIN

set iIsInternalDA = exists(select USER_NAME from JAMES_USER where USER_NAME = vcDA);

END
***************

I created a new procedure called "ReturnOutParam" as follows:

***************
CREATE DEFINER=`[USER]`@`%` PROCEDURE `ReturnOutParam`(vcDA varchar(100), out iIsInternalDA int)
BEGIN

set iIsInternalDA = exists(select USER_NAME from JAMES_USER where USER_NAME = vcDA);

END
***************

Both procedures are identical except for their names.

I also modified the channel JavaScript as follows:

***************
importPackage( java.sql );

var dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DirectMail?noAccessToProcedureBodies=true", "[USER NAME]", "[PASSWORD]");

//var pCall = dbConn.prepareCall('{call IsInternalDA(?,?)}');
var pCall = dbConn.prepareCall('{call ReturnOutParam(?,?)}');

var da = connectorMessage.getRawData().toString();

logger.info('Ra DA: ' + da);

pCall.setString(1, da);

pCall.registerOutParameter(2, java.sql.Types.INTEGER);

pCall.executeUpdate();

var isInternalDA = pCall.getInt(2);

logger.info('Is Internal DA: ' + isInternalDA);

***************

Notice the line to prepare the call to IsInternalDA is commented out while the line to prepare the call to the new procedure, ReturnOutParam, is not.

When I deploy and execute the channel this way, it works. When I comment out the line to prepare the new procedure and un-comment the line for the original procedure and run it that way, I still get the "boo!" error.

I tried restarting the services, mysqld and mcservice, and that didn't fix it.

Can anyone explain what could be happening here? Why would the name of the store procedure matter? It appears something got corrupted in Mirth regarding the original procedure.

Thanks in advance for any help you can offer.

Last edited by mmall; 01-22-2018 at 09:32 AM.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 01:03 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation