How to generate multiple CSV files on source transformer in one go?

[husmansip]

I am trying to generate a CSV file from rows returned by a select query on the source transformer. Here are the steps I want to perform:

1. Get Placer_Order_Numbers values from ORDER table against OrderID.
2. For each Placer_Order_Numbers value, execute another select query to get OrderID, Patient_Last_Name, and Patient_First_Name from ORDER_DETAILS table and generate a CSV file for all the rows returned by the 2nd select query.
3. Put the generated CSV in channelMap and pass to the destination to be written to SFTP Location.

However, each time I execute the channel, the resultant CSV only has the row returned by the last query inside the while loop.

try
{
    dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://**.**.**.**:****/dbname','user','password');
   
    getPlacerOrderNumber = dbConnMirth.executeCachedQuery(" SELECT * PLACER_ORDER_NUMBER FROM ORDER WHERE ORDER_ID IN (11055,11054,11053); ");
    
    if (getPlacerOrderNumber.size() > 0){
        
        while(getPlacerOrderNumber.next()){
            
            var resultOrderDetailsQuery = "SELECT ORDER_DETAIL_ID,PATIENT_LAST_NAME, PATIENT_FIRST_NAME FROM ORDER_DETAILS WHERE ORDER_DETAILS_PLACER_ORDER_NUMBER = '" + placerOrderNumber + "'; ";
            var resultOrderDetailsQueryResult = dbConn.executeCachedQuery(resultOrderDetailsQuery);
            
            if (resultOrderDetailsQueryResult.size() > 0){
                resultOrderDetailsQueryResult.next();
                
                var orderDetailId = resultOrderDetailsQueryResult.getInt(1);
                var patientFirst = resultOrderDetailsQueryResult.getString(4);
                var patientLast = resultOrderDetailsQueryResult.getString(3);
                var csvName = patientLast + "_" + patientFirst + ".csv";
                
                channelMap.put('csvName', csvName);
                
                csv = 'Order Detail Id' + delimiter + 'Patient Last Name' + delimiter + 'Patient First Name' + delimiter +'\n';
                csv = csv + orderDetailId + delimiter+ patientLast + delimiter + patientFirst +'\n';
            }
            channelMap.put('csv', csv);
        }
    }
}

Am I doing something wrong while passing the CSV in transformer step? Any expert suggestion will be highly appreciated.

[pacmano1]

You might consider calling your query to return JSON since you can return the entire result set in one query, no need to run multiple selects. Also, you can COALESCE the SQL return value to return an empty array if no results - so you always return a result, there is then no need to then check for size (i.e. just call result.next() and JSON.parse the returned value from the query).

You should also consider using a cached db connection, see: https://github.com/pacmano1/Mirth-Snippets/blob/main/executeSharedDBStatement.js

[rjkroll]

Three things I noticed. First, you probably shouldn't be using a SELECT * in any query. Just select what you need. Second, your getString() indexes are incorrect when assigning your First/Last name variables. Third, your CSV channel map is being overwritten by your While loop, which is why you only get the last row of your query. Below is a tweaked and formatted version of your script:

dbConn = DatabaseConnectionFactory.createDatabaseConnection(
    'net.sourceforge.jtds.jdbc.Driver',
    'jdbc:jtds:sqlserver://**.**.**.**:****/dbname',
    'user',
    'password'
);

var getPlacerOrderNumber = dbConn.executeCachedQuery(
    "SELECT PLACER_ORDER_NUMBER FROM [ORDER] WHERE ORDER_ID IN (11055,11054,11053);"
);

var csv = 'Order Detail Id,Patient Last Name,Patient First Name\n';

if (getPlacerOrderNumber.size() > 0) {
    while (getPlacerOrderNumber.next()) {
        var placerOrderNumber = getPlacerOrderNumber.getString(1);

        var resultOrderDetailsQuery = "SELECT ORDER_DETAIL_ID, PATIENT_LAST_NAME, PATIENT_FIRST_NAME FROM ORDER_DETAILS WHERE ORDER_DETAILS_PLACER_ORDER_NUMBER = '" + placerOrderNumber + "';";
        var resultOrderDetailsQueryResult = dbConn.executeCachedQuery(resultOrderDetailsQuery);

        while (resultOrderDetailsQueryResult.next()) {
            var orderDetailId = resultOrderDetailsQueryResult.getInt(1);
            var patientLast = resultOrderDetailsQueryResult.getString(2);
            var patientFirst = resultOrderDetailsQueryResult.getString(3);

            csv += orderDetailId + ',' + patientLast + ',' + patientFirst + '\n';
        }
    }
}

channelMap.put('csv', csv);

if (dbConn != null) {
    dbConn.close();
}

[husmansip]

@rjkroll thanks for suggesting the code tweaks which works fine for now. However, I'm still facing issues in generating a single CSV against all the rows returned by the second query result resultOrderDetailsQueryResult. My first select query returns placer order numbers and against each placer order number value, I want to execute the second query and generate a CSV file from all the rows. The same process needs to be repeated for the second and third placer order number values respectively.

I want to upload each CSV file to an SFTP location. For now, only a single CSV is generated every time.
I have also tried getting the resulting CSV file on my destination transformer but I get the resultant CSV data in full (not against a single placer order number).

Is there any way to pass a single CSV (containing data specific to a single placer order number) to the destination for uploading to the SFTP location in the while loop? Say in each while loop iteration, can I upload to SFTP?