Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: How to ....Call DB Procedure

  1. #1
    Join Date
    Oct 2006
    Posts
    14

    Question How to ....Call DB Procedure

    Hi,

    1. Can I have "Call DB" without any input object (except Row Generator) as in example attached ? Is this a correct transoformation ?

    2. How can I load store procedure results to Text filte output using "Call DB" ?

    Store Procedure is as below-

    CREATE PROCEDURE dbo.spc_sel_IMIData
    AS

    SELECT c.ApplicationID, s.Server

    FROM Component c

    INNER JOIN Servers s ON c.ComponentID = s.ComponentID

    WHERE s.ImportStatus <> 'D'

    ORDER BY c.ApplicationID, s.Server

    GO

    Appreciate your help.

    thanks and regards,
    bhavin
    Attached Images Attached Images  

  2. #2

    Default

    Hi bhavin,

    1. Can I have "Call DB" without any input object (except Row Generator) as in example attached ? Is this a correct transoformation ?

    --> No. Call Db proc need to be triggered !
    In a design way, your trans looks good but you have to check requested inputs fields for each step.

    2. How can I load store procedure results to Text filte output using "Call DB" ?

    The field "Result name" will contain proc return.

    That being said, i don't see why you have to use stored proc !

    You want to get 2 fields (c.ApplicationID, s.Server), use Table input step.

    Rgds

    Samatar

  3. #3
    Join Date
    Oct 2006
    Posts
    14

    Default

    Samatar

    Thanks for your reply.

    First, let's merge this thread with my other thread of "Patch #5604". after listing this thread, I found some headaway for the problem.

    But before closing thread, answer to your question-

    Q-
    "i don't see why you have to use stored proc !

    You want to get 2 fields (c.ApplicationID, s.Server), use Table input step. "

    A-
    DBA only allows access to execute procedure. If it is simplest query, it has to be thru Store Procedure. Thought I didn't udnerstand logic, I have follow the policy and procedure set forth.

    thanks.

    bhavin.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    As said a "few" times before on this very forum, you can use this as a hotfix: http://www.kettle.be/dloads/kettle.jar

  5. #5

    Default Need Help in calling procedure

    1. Can I have "Call DB" without any input object (except Row Generator) as in example attached ? Is this a correct transoformation ?

    --> No. Call Db proc need to be triggered !
    Can you explain what do you mean by triggered?
    I am using a DB and not giving any INPUT and its giving me error.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    You need a row generator... just generate 1 empty row.

    Regards,
    Sven

  7. #7

    Default Help Needed

    Senario is like this ....

    I have to load data from Oracle D/B to Informix D/B. Informix table has 84 columns and from Oracle side i have to take data from 5 tables and 8 columns has to be populated.
    Problem is that in Oracle tables one table(say t1) has 2000+ records and i am taking 72 columns from it, the other table(say t2) has 10000+ records and i am taking 3 columns from it. Both of them has a single column on which i have to sort the data. Now when i am using sql query to equate i am getting error, if i am using DB Procedure I am getting error.
    Please guide me what to do? Its very urgent. I am struck with it for the last 2 days...
    Thanks,

  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    Your description is too vague to comment on... what kind of errors do you get, what do you want to "equate" on.
    Attach your transformation and we may be able to figure out what you're trying to do.

    Regards,
    Sven

  9. #9

    Default Here is the attachment

    I am sending it as screen shot.I do not know how to send the transformation as .ktr file. New to pentaho.....
    May be u can figure out the transformation......T1 is drug_profile. T2 is GCN_MASTER. Am i going right or wrong?
    Attached Files Attached Files

  10. #10
    Join Date
    May 2006
    Posts
    4,882

    Default

    Very wrong for the moment. I look at your picture and have no idea what you try to do.

    What I can tell you is that the cartesian join is probably for 99% wrong. Take a copy of the .ktr file and remove all but 2 or 3 steps from the cartesian join step. And then preview it, you will see you will probably get much more rows than expect.

    Also:
    - You can just attach .ktr files as you did with the .doc document, but it's probably best to open up the .ktr in notepad or so and blank out the connection information
    - Which error do you get? Or is just not working?

    Regards,
    Sven

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.