executeUpdate stored procedure return params retrieval ?

[jlambhl7]

Hi

Using Mirth 3.10, I'm executing MSSQL stored with parameters (3 in, 2 in/out) using executeUpdate(), and I can't get the return parameter values.

It seems as though nothing is returned in results,.

The final 2 parameters are output in SQL server with int and varchar types respectively.

var results = dbConn.executeUpdate("exec insertTable 'input1','input2','input3',0,'responseText'");

while(results.next()){
logger.info("Getting results from executeUpdate");
var responseCode = results.getInt(1);
logger.info(" ResponseCode from Stored Proc" + responseCode);
var responseText = results.getString(2);
logger.info(" ResponseText from Stored Proc" + responseText);
}

Exception:
TypeError: Cannot find function next in object -1

Thanks
Jarrod

** EDIT**
Also tried this method which gets inside the results loop

var sql = "exec insertTable ?,?,?,?,?"
var paramList = new java.util.ArrayList();

paramList.add(input1);
paramList.add(input2);
paramList.add(input3);
paramList.add(0);
paramList.add(responseText);

var results = dbConn.executeUpdateAndGetGeneratedKeys(sql,paramList);

while(results.next()){
logger.info("Getting results from executeUpdate");
var responseCode = results.getInt('responseCode');
logger.info(" ResponseCode from Stored Proc" + responseCode);
var responseText = results.getString('responseText');
logger.info(" ResponseText from Stored Proc" + responseText);
}

How do I get the output parameters? I've also tried results.getInt(1) etc

Wrapped java.sql.SQLException: Invalid column name
Wrapped java.sql.SQLException: Invalid column index

[jlambhl7]

Figured it out I'll put the details here in case anyone encounters this. Need to use an executeCachedQuery and ensure the stored procedure returns a result set with column names matching the results.getInt, getString etc.

var sql = "exec insertTable ?,?,?,?,?"
var paramList = new java.util.ArrayList();

paramList.add(input1);
paramList.add(input2);
paramList.add(input3);
paramList.add(0);
paramList.add(responseText);

var results = dbConn.executeCachedQuery (sql,paramList);

while(results.next()){
logger.info("Getting results from executeUpdate");
var responseCode = results.getInt('responseCode');
logger.info(" ResponseCode from Stored Proc" + responseCode);
var responseText = results.getString('responseText');
logger.info(" ResponseText from Stored Proc" + responseText);
}

[jonbartels]

Thank you for sharing the solution!

It helps the next person!