Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: Call DB Procedure error

  1. #1

    Unhappy Call DB Procedure error

    Hi, I try to call a Stored Procedure on a IBM DB2 UniversalDatabase.

    1. Step: Generate a row with one field.
    2. Step: Call the Procedure "ADMIN_CMD" with the value of the generated field as Parameter.
    3. Step: Write the result to a file ("/tmp/logfile.txt")

    When I run this transformation, I get the following error:

    Call DB Procedure.0 - ERROR (version 2.5.1, build 1 from 2007/10/10 06:41:43) : Because of an error, this step can't continue:
    Call DB Procedure.0 - ERROR (version 2.5.1, build 1 from 2007/10/10 06:41:43) : Unable to prepare database procedure call
    Call DB Procedure.0 - ERROR (version 2.5.1, build 1 from 2007/10/10 06:41:43) : Parameter 1 is the return caluse of the stored procedure call, it can only be registered as an integer type


    I don't understand this. The stored procedure needs one argument as u can see here.

    I've tested it in Version 2.5.1 and 3.0.0-M2
    Attached Images Attached Images    

  2. #2

    Default

    Any hints for me?

  3. #3
    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......
    Is it possible to connect SQL Server Stored Procedure with Report?
    Please help, because i'm not getting help regarding this topic in forums....

    thanks in advance

    ....Sachin

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

    Default

    Well... maybe if you post in the right forum... and don't hijack threads

    On your question, my first hunch would be "no, not without a lot of workarounds".

    But try the reporting forums.

    Regards,
    Sven

  5. #5
    Join Date
    Nov 1999
    Posts
    459

    Default

    Sachin,

    yes, you can have a Kettle transformation with a stored procedure and have this result as an input for your report.

    Cheers,
    Jens

  6. #6
    Join Date
    Nov 1999
    Posts
    459

    Default

    Michael,

    this looks like an error - can you please file a bug at jira.pentaho.org?
    And add your transformation (without confidential information) to this case.

    Thanks,
    Jens

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

    Default

    Please don't file bugs for every database error you get.

    Parameter 1 is the return caluse of the stored procedure call, it can only be registered as an integer type
    It's not even a DB2 database you are using. I doubted DB2 would have incorrect spelling for the word "clause".
    So in fact it's the Derby database you are using.

    And for some obscure reason only you can figure out, the first parameter of the procedure call has to be an integer.

    Matt

  8. #8

    Default

    Maybe, that I misunderstand your posting Matt, but i'm really sure that I'm using a DB2 Database ;-)

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

    Default

    Derby is open source. I found the exact error message in the Derby source code.
    It might be that the same exact error message lives in DB2 UDB as well, but somehow I doubt it.

    I think they both where called DB2 by the way. IIRC IBM opened up the source code at a certain point.

    All the best,

    Matt

  10. #10

    Default

    It's really an IBM DB2 UDB ;-)

    We have searched for the problem now a few hours and it seems that there are problems with the SQL-Datatype of the returnvalue.

    In Database.java Line 3389 (PDI 2.5.1) the VALUE_TYPE_INTEGER is mapped to java.sql.Types.BIGINT.

    It seems, that DB2 needs java.sql.Types.INTEGER here. When we change the SQL-Datatype to Integer we don't have any problems with the transformation step.
    Last edited by michael.mest; 10-18-2007 at 03:47 AM.

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

    Default

    Hi Michael,

    Make sure to file a bug report for that Derby... eeuh ... DB2 UDB problem you had.
    I'm sure we can make an exception for DB2 in the setting of the return type somewhere in DBProc.

    All the best,

    Matt

  12. #12

    Thumbs up

    Ok, I've reported the problem in jira:

    http://jira.pentaho.org/browse/PDI-325

    Thanx for your help

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.