Mirth Database Reader failed to process row retrieved from the database in channel (index out of range)?

Question

I have a Mirth (v3.10) Database Reader channel source that grabs some test records (from an SQL Server source) using the query...

select * 
  from [mydb].[dbo].[lab_test_MIRTHTEST_001]
  where orc_2_1_placer_order_number 
  in (
  'testid_001', 'testid_002', 'testid_003'
  )

Even though the channel appears to function properly and messages are getting written to the channel destination, I am seeing SQL errors in the server logs in the dashboard when deploying the channel:

[2020-12-16 08:16:28,266]  ERROR  (com.mirth.connect.connectors.jdbc.DatabaseReceiver:268): Failed to process row retrieved from the database in channel "MSSQL2SFTP_TEST"
com.mirth.connect.connectors.jdbc.DatabaseReceiverException: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
    at com.mirth.connect.connectors.jdbc.DatabaseReceiverQuery.runPostProcess(DatabaseReceiverQuery.java:233)
    at com.mirth.connect.connectors.jdbc.DatabaseReceiver.processRecord(DatabaseReceiver.java:260)
    ...

I can run this query fine in the SQL Server Mgmt Studio itself (and the messages seem to be transmitting fine), so not sure why this error is popping up but am concerned there is something I'm missing here.

Anyone with more experience know what is going on here? How to fix?

Answer 1

The issue looks to be in the post-process SQL section of the Database Reader, so it makes sense that the messages appear to be working.

Did you intend to enable the post-process section at the bottom of your source tab?

Answer 2

Kindly share the code that you are using to process data in the result set. In the meantime, you can consider the code below as a staring point. You can place this in Javascript transformer step in the source connector of your channel.

//Declaring variables to hold column values returned from the result set
var variable1;
var variable2;

//defining the sql read command
var  Query = "select * from [mydb].[dbo].[lab_test_MIRTHTEST_001]";
Query += " where orc_2_1_placer_order_number in";
Query += " ('testid_001', 'testid_002', 'testid_003')";

var result = dbconn.executeCachedQuery(Query);  
//where dbconn is your database connection string

//looping through the results
while(result.next())
{
    
variable1=result.getString("variable1");
variable2 = result.getString("variable2");
    
}

//optionally place the returned values in a channel map for use later
$c('variable1',variable1);
$c('variable2',variable2);