Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Get raw text from stored procedure and put into text file.

  1. #1
    Join Date
    Nov 2017
    Posts
    4

    Default Get raw text from stored procedure and put into text file.

    Hello. I have a SQL stored procedure that returns results from multiple tables within the database so consequently they return different columns. I use some "print" statements within SQL to help make the results more readable. When you run the stored procedure you get results something like this.

    ========================================================

    Employee info
    ---------------------------
    Name: John Smith
    Address: 123 Main Street
    Phone: 555-555-5555


    Employee Timesheet
    ------------------------------
    Date TimeIn TimeOut
    ----------- -------- --------
    Jan-1-2017 8:02 AM 4:05 PM
    Jan-2-2017 8:01 AM 4:00 PM
    Jan-3-2017 8:04 AM 4:14 PM

    (3 rows affected)


    Employee Sales
    ---------------------------------------------
    SaleId SaleDate SaleTotal CustomerName
    ------- ----------- ---------- -------------
    23 Jan-1-2017 $40.17 Brian Jones
    67 Jan-1-2017 $78.56 Steve Meier
    89 Jan-2-2017 $208.01 Dan Webber
    92 Jan-2-2017 $175.99 Kelly Sloan
    93 Jan-3-2017 $10.70 Brenda Watson

    (5 rows affected)

    ========================================================

    As you can see the results are not displayed in a single dataset. I am trying to get Kettle to call this stored procedure, pass it a start date variable, return the results to Kettle, save those results to a text file and then attach that text file to an email. I am using a "Generate Rows" to pass a value to "Call DB Procedure" which calls the stored procedure and then passes the output to a "Text file output". The problem is that the transformation does not yield accurate results. When I run the transformation the text file ends up with the variable name and its value that I specified in the "Generate Rows" step but there is no indication of what happened to the results from the stored procedure. I am still a bit new to Pentaho so could someone tell me if I am talking the right approach or if there is some other method that I should use to get these results into a text file. I am using "Kettle - Spoon General Availability Release - 5.2.1.0-148". Thanks.
    Last edited by chris4; 11-10-2017 at 02:04 PM.

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

    Default

    You are using Call-DB-Procedure for this?
    That's a lookup step designed to retrieve a single output parameter value.
    The only step returning a result set is Table-Input.

    I never tried to retrieve multiple result sets using Table-Input, but I expect only the first result set to be returned.
    Multiple result sets with different row layout would have a disastrous effect on Kettle row processing.

    I could be wrong, though.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Nov 2017
    Posts
    4

    Default

    You are correct that using a Table-Input only returns the first set of results. I am trying to determine if there is a way to have multiple results sets with different row layouts returned. Am I to understand that you are not familiar with a any method of accomplishing this?

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

    Default

    I know how to read multiple result sets via JDBC, but that's not leading anywhere with Kettle since you emphasise they are not union compatible.

  5. #5
    Join Date
    Nov 2017
    Posts
    4

    Default

    Then it would appear I must take a different approach. My question is answered. Thank you for your assistance.

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

    Default

    Usually, a command line tool comes with your database server.
    It's easy to redirect output to a file that way.
    No need for an ETL tool.

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.