Announcement

Collapse
No announcement yet.

Simple Call with Registered Out Parameter Not Working "Boo!"

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 = '[email protected]';

    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, 07:45 AM.

  • #2
    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, 09:32 AM.

    Comment

    Working...
    X