Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Using step "Call DB Procedure"

  1. #1
    Join Date
    Jan 2007
    Posts
    17

    Question Using step "Call DB Procedure"

    Hi,

    I have a set of jobs/transformations that load a database from various sources. In order to verify data integrity and coherence, I prepared a stored procedure that performs a few calculus to produce statistical results (no params for that procedure). It runs fine when I try it on my DB Client.

    My problem is, I do not manage to call it from a spoon transfo.

    When I try to configure the "Call DB Proc" step, the "Find it..." returns a message saying i can't find any procedure on my connection (which is ok since I uses it in other transformations), so I attempted to enter my proc name manually. But then when I execute the transfo (containing only that call step), I get the following trace (which obviously shows my proc is not executed since it is a quite heavy one that takes some time to perform).

    2007/02/15 10:44:33 - Call DB Procedure.0 - Connected to database...
    2007/02/15 10:44:33 - Exec_stored_proc_GET_STATS - Step [Call DB Procedure.0] initialized flawlessly.
    2007/02/15 10:44:33 - Exec_stored_proc_GET_STATS - Transformation has allocated 1 threads and 0 rowsets.
    2007/02/15 10:44:33 - Call DB Procedure.0 - Starting to run...
    2007/02/15 10:44:33 - Call DB Procedure.0 - Signaling output done to 0 output rowsets.
    2007/02/15 10:44:33 - db_target - Connection to database closed!
    2007/02/15 10:44:33 - Call DB Procedure.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0
    2007/02/15 10:44:33 - Spoon - The transformation has finished!!

    My database is a MS SQL Server 2005 DB and I'm using Kettle 2.4.0.

    Should I be able to run my procedure that way or did I misunderstood the use of that step ?

    Thanks for any advice.

  2. #2

    Default

    Hello jcoste,

    When I try to configure the "Call DB Proc" step, the "Find it..." returns a message saying i can't find any procedure on my connection

    ---> the list returned is filtered with user/owner.The good situation is to diaplay all proc/functions no matter the owner.

    I posted a request change and i made change today..you can try to grab a jar file in the coming hour.

    --> But then when I execute the transfo (containing only that call step),

    You need to generate row before calling this step.
    Insert a generate rows step (with 1 row) before Call Db step.

    Samatar

  3. #3
    Join Date
    Jan 2007
    Posts
    17

    Default

    Quote Originally Posted by shassan2 View Post
    --> But then when I execute the transfo (containing only that call step),

    You need to generate row before calling this step.
    Insert a generate rows step (with 1 row) before Call Db step.

    Samatar
    I think that focusing on my procedure I slightly forgot the logic of a transformation...
    I will try the new .jar asap and check javaforge more regularly as suggested Matt yesterday.

    Thanks for your help !

    Regards,

  4. #4
    Join Date
    Apr 2007
    Posts
    6

    Default Call DB Procedure

    Hi

    I was facing an error while calling a Procedure from Oracle DB.

    2007/04/03 10:14:05 - Call DB Procedure.0 - ERROR (version 2.4.0, build 742 from 2007/01/27 12:29:00) : Because of an error, this step can't continue:
    2007/04/03 10:14:05 - Call DB Procedure.0 - ERROR (version 2.4.0, build 742 from 2007/01/27 12:29:00) : Unable to prepare database procedure call
    2007/04/03 10:14:05 - Call DB Procedure.0 - ERROR (version 2.4.0, build 742 from 2007/01/27 12:29:00) : [Microsoft][ODBC driver for Oracle]Syntax error or access violation

    Please let me know how to solve this.?

    Thanks in advance
    Basha

  5. #5

    Default

    Hi Basha,
    Can you give us more details..maybe a sample :-)

    Thanks

    Samatar

  6. #6
    Join Date
    Dec 2006
    Posts
    12

    Default Why won't DB procedure not see the procedures?

    I have a similar problem. I have used the DB procedure before and it worked fine. I was able to browse my list of procdedures select the one I want and I good to go. Now It doesn't seem to work for all the procedures ( only one shows up) I know its not a user issue as the procedures were compiled by the same user I am using in the DB connection parameters. Can someone please point me in some direction in my attempt to trouble shoot this issue.

    ps. I tried entering the procedures manually but that doesn't work. I was using kettle 2.3 now using kettle 3.0

    -B

  7. #7
    Join Date
    Oct 2007
    Posts
    3

    Default Query: Stored procedure connection with report

    Hi,
    I'm working with this tool to know more about features that it can support. This task is assigned to me. Our company is going to replace Crystal Report with Pentaho.

    I have query regarding stored procedure as,
    I'm using Report Designer 1.6......
    Can I connect SQL Server Stored Procedure with Report?
    Please help, because i'm not getting help regarding this topic in forums....

    thanks in advance

    ....Sachin

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

    Default

    sigh... don't hijack threads, and post in the right forums...

    Regards,
    Sven

  9. #9
    Join Date
    Nov 1999
    Posts
    459

    Default

    Basha,
    > [Microsoft][ODBC driver for Oracle]Syntax error or access violation
    Please try this with the JDBC driver instead of ODBC.
    Cheers,
    Jens

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.