web stats
Output multiple SQL rows to file - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 10-01-2019, 06:37 AM
jcurry5 jcurry5 is offline
OBX.2 Kenobi
 
Join Date: May 2013
Posts: 59
jcurry5 is on a distinguished road
Default Output multiple SQL rows to file

I have a SQL query that is returning 10 rows that I'm looking to output to a file. In my Destination transformer, I have JavaScript code that is outputting 10 rows but it's the same record.

Code snippet:

Code:
var resultSize = results.size();
if (resultSize > 0) {
  for (var i = 0; i<resultSize; i++){
      results.next();
      var r = results.getClob(1);
      released = r.getSubString(1,r.length());
      released = released+"\r\n";
      channelMap.put('released_accts,released);
   }
}
I even tried an ArrayList which somewhat worked - I was getting the 10 results but it was being added to my output file 10 times.

Any guidance would be greatly appreciated!
Reply With Quote
  #2  
Old 10-01-2019, 06:50 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,348
cory_cole is on a distinguished road
Default

while(results.next())
{
var r = results.getClob(1);
released = r.getSubString(1,r.length());
released = released+"\r\n";
channelMap.put('released_accts,released);
}
Reply With Quote
  #3  
Old 10-01-2019, 06:58 AM
jcurry5 jcurry5 is offline
OBX.2 Kenobi
 
Join Date: May 2013
Posts: 59
jcurry5 is on a distinguished road
Default

Quote:
Originally Posted by cory_cole View Post
while(results.next())
{
var r = results.getClob(1);
released = r.getSubString(1,r.length());
released = released+"\r\n";
channelMap.put('released_accts,released);
}
Thanks for the quick response.

I updated my code to the above but still the same output of one entry being listed 10 times.

Is there anything in my Source that I need to investigate or is this strictly driven by the Destination transformer?
Reply With Quote
  #4  
Old 10-01-2019, 07:40 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 1,106
agermano is on a distinguished road
Default

You are overwriting the same channelMap variable when processing each row. It's only going to hold the value of the last one.

If you are trying to accumulate the values in a newline separated string, you can do this.

Code:
var released = new java.lang.StringBuilder();

while(results.next()) {
    var r = results.getClob(1);
    released.append(r.getSubString(1,r.length()));
    released.append("\r\n");
}

channelMap.put('released_accts', released.toString());
Reply With Quote
  #5  
Old 10-01-2019, 08:22 AM
jcurry5 jcurry5 is offline
OBX.2 Kenobi
 
Join Date: May 2013
Posts: 59
jcurry5 is on a distinguished road
Default

Quote:
Originally Posted by agermano View Post
You are overwriting the same channelMap variable when processing each row. It's only going to hold the value of the last one.

If you are trying to accumulate the values in a newline separated string, you can do this.

Code:
var released = new java.lang.StringBuilder();

while(results.next()) {
    var r = results.getClob(1);
    released.append(r.getSubString(1,r.length()));
    released.append("\r\n");
}

channelMap.put('released_accts', released.toString());
Okay thanks, that makes sense.

I updated the code to the above and it works but now it's being added to my file 10 times.

I have my Destination File Exists set to Append and then setting it to Overwrite but that doesn't seem like a solution. I also put logger.info on results and it's only showing 10 rows too.

Thanks again!


---
Edit:
So I think the issue is while(results.next())
When I put the logger.info inside the brackets it's outputting the 100 entries. So is while(results.next()) the right function to use here?

Last edited by jcurry5; 10-01-2019 at 08:30 AM.
Reply With Quote
  #6  
Old 10-01-2019, 09:35 AM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 1,106
agermano is on a distinguished road
Default

It is the right function to use. next() advances the cursor to the next row in the resultset and returns true if there was another row or false if you have advanced past the last record. Nothing in that code is making it repeat 10 times.

Likely your query is incorrect or your data actually contains duplicate rows.
Reply With Quote
  #7  
Old 10-01-2019, 10:50 AM
jcurry5 jcurry5 is offline
OBX.2 Kenobi
 
Join Date: May 2013
Posts: 59
jcurry5 is on a distinguished road
Default

Quote:
Originally Posted by agermano View Post
It is the right function to use. next() advances the cursor to the next row in the resultset and returns true if there was another row or false if you have advanced past the last record. Nothing in that code is making it repeat 10 times.

Likely your query is incorrect or your data actually contains duplicate rows.
So that actually helped narrow things down.

It turned out that my Source SQL query was returning all 10 rows, thus making the Destination iterate 10 times.

Thanks for the help!
Reply With Quote
  #8  
Old 10-01-2019, 11:49 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,348
cory_cole is on a distinguished road
Default

If you use a select distinct that will get rid of that problem.
Reply With Quote
  #9  
Old 10-01-2019, 12:33 PM
jcurry5 jcurry5 is offline
OBX.2 Kenobi
 
Join Date: May 2013
Posts: 59
jcurry5 is on a distinguished road
Default

Quote:
Originally Posted by cory_cole View Post
If you use a select distinct that will get rid of that problem.
Yep!

Switched my Source SQL query to pull only the top 1 as I just need it to see if an entry returns.

Thanks!
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:09 PM.


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