Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Database Result Set to Text Stream

  1. #1
    Join Date
    Mar 2011
    Posts
    139

    Default Database Result Set to Text Stream

    Good day!

    I am trying to enable some functionality and I would like the teams input.

    I will like to be able to extract records from a table and put selected data fields into a single text stream value. Here is what I want to accomplish. During the different transformations, I write data to a table related to the records I am processing. These messages maybe something simple like "Number of Records Imported: 357." I want to be able to select the records from this table to format them as a single text stream value so I can append the text stream in an existing email message. For any transformation, I could one to many messages.

    The only part that I have not figured out is how to take a database result set and convert then rows and columns into a single text stream value. I thought about using the JavaScript step using the RowMeta object however I am not familiar enough with this object. I am assuming that the database results will in a series of rows which I would poll by row to extract the fields that I want to include in a text stream value.

    Thoughts?

    Thank you
    Ray

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    Could you use the java.util.Formatter (in a JavaScript or UDJC step) to build your messages? http://docs.oracle.com/javase/7/docs...Formatter.html
    Last edited by darrell.nelson; 11-04-2013 at 12:32 PM. Reason: added link
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Have a look at the "Output steps metrics" that should tell you how many rows are output to the DB in a single line already... Then it's just formatting it, and away you go.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    As formatting is concerned:
    Usually, the Concatenate function of a Group By step is good enough for me.
    It certainly depends on how fancy your output must be.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Mar 2011
    Posts
    139

    Default

    Darrell,

    I will have to look into this function. Yes, it will work to display and format the text.

    The challenge right now is how to get the information out of the database result set into text to manipulate it.

    Thank you!
    Ray

  6. #6
    Join Date
    Mar 2011
    Posts
    139

    Default

    Gutlez - actually I use the Output Steps Metrics step for a couple of items to put the information into the table during the transformation. It is getting the information out of the database into a text message for an email.

    Thanks
    Ray

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Oh shoot!
    I completely misread your initial post.

    Have a solid look at Group By (and specifically the Concatenate with " " option) as Marabu suggested.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  8. #8
    Join Date
    Mar 2011
    Posts
    139

    Default

    Hello Marabu.

    Good suggestion. How would I loop through a database result with any number of records and be able to select specific fields from within each row?

    Thanks
    Ray

  9. #9
    Join Date
    Mar 2011
    Posts
    139

    Default

    Gutlez

    No problem. I am taking a look at that option right now to see how I can use it. I really appreciate your insight!

    Ray

  10. #10
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Have a good read of:
    http://wiki.pentaho.com/display/EAI/Group+By

    Then try building a sample set which does more-or-less what you want it to do.

    If you get really stuck, we can help you with a step or two.

    Group By is designed to take an entire stream, and operate on it. Grouping and summarizing values as instructed. If you have something like:

    JOBID | Output Line | Output text
    1 | 1 | Job Ran Successfully
    1 | 2 | 100 Lines Read
    1 | 3 | 100 Lines Inserted
    2 | 1 | Job Failed to run

    You can tell Group By to group on JOBID and Summarize Output Text by Concatenating with " " getting output:
    JOBID | Summarized Output
    1 | Job Ran Successfully 100 Lines Read 100 Lines Inserted
    2 | Job Failed to run
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  11. #11
    Join Date
    Mar 2011
    Posts
    139

    Default

    Gutlez,

    Actually using the Group By works great! I just finished an example which produced an output what I expected.

    Thanks!
    Ray

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.