Announcement

Collapse
No announcement yet.

Output multiple SQL rows to file

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

  • 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!

  • #2
    while(results.next())
    {
    var r = results.getClob(1);
    released = r.getSubString(1,r.length());
    released = released+"\r\n";
    channelMap.put('released_accts,released);
    }

    Comment


    • #3
      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?

      Comment


      • #4
        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());

        Comment


        • #5
          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, 07:30 AM.

          Comment


          • #6
            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.

            Comment


            • #7
              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!

              Comment


              • #8
                If you use a select distinct that will get rid of that problem.

                Comment


                • #9
                  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!

                  Comment

                  Working...
                  X