Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Getting getMoreResults ERROR when calling db procedure

  1. #1
    Join Date
    May 2007
    Posts
    11

    Default Getting getMoreResults ERROR when calling db procedure

    I am getting the following error when using Spoon and accessing a stored procedure through Call DB Procedure step. It does not matter whether I use a JDNI database connection or a Native one to the SQL 2005 Server. This is the error.

    2007/10/17 14:45:55 - Call DB Procedure.0 - ERROR (version 2.5.1, build 1 from 2007/10/17 14:41:03) : Because of an error, this step can't continue:
    2007/10/17 14:45:55 - Call DB Procedure.0 - ERROR (version 2.5.1, build 1 from 2007/10/17 14:41:03) : Unable to call procedure
    2007/10/17 14:45:55 - Call DB Procedure.0 - ERROR (version 2.5.1, build 1 from 2007/10/17 14:41:03) : Output parameters have not yet been processed. Call getMoreResults().

    On research and looking at the Database.java code, it looks as though getMoreResults() should be called before getting the output parameters. Some drivers, especially the one defaulted with Spoon (jtds) strictly enforces the spec.

    From the javadoc for CallableStatement:
    For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters.
    Everything works fine if I use an Opta driver or MSSQL Server driver which do not strictly enforce the specification.

    For the point of this discussion, my stored procedure has been simplified down to:

    setANSI_NULLSON
    set
    QUOTED_IDENTIFIERON
    go
    CREATE procedure nextval(@SEQ_NAME varchar(80), @INCR INT = 1, @CURR_VALUE numeric = null OUT)
    AS
    BEGIN
    begin tran
    SET NOCOUNT ON
    UPDATE SEQ_TBL
    SET PREV_VALUE = CURR_VALUE,CURR_VALUE = CURR_VALUE + @INCR
    WHERE SEQ_NAME = @SEQ_NAME
    SELECT @CURR_VALUE = CURR_VALUE FROM SEQ_TBL WHERE SEQ_NAME = @SEQ_NAME
    commit tran
    SET NOCOUNT OFF
    RETURN @CURR_VALUE
    END

    If the bold line is in, we get the error. If the bold line is not in the stored procedure , the etl will run fine. In other words, if there is a return value, the DB Proc step fails when using the jtds driver with spoon.

    Attached is my ETL xml. I have tried with 2.5.1 and 2.5.2 and both have the issue.

    Thanks!
    Sandy Garcia
    Attached Files Attached Files

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

    Default

    And other drivers blow up if you call getMoreResults() beforehand ... so it's not that easy a fix. By the way getMoreResults() would normally only be needed if you're returning multiple resultsets.

    Also try 3.0.0-RC1 ... there were some extensive changes in that step.

    Regards,
    Sven

  3. #3
    Join Date
    May 2007
    Posts
    11

    Unhappy So it is not a bug?

    Maybe my solution isn't correct, but don't you consider this a bug in Kettle? Are you saying that Call DB Procedures will not work if there is a return value?

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

    Default

    It's my understanding that most stored procedure languages don't allow procedures to return values.

    If "CURR_VALUE" is already declared as an OUT parameter, why the need for the return clause?


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

    Default

    The full text in the original post is by the way:

    Retrieve OUT Parameters after Results
    Because of limitations imposed by some DBMSs, it is recommended that for maximum portability, all of the results generated by the execution of a CallableStatement object should be retrieved before OUT parameters are retrieved using CallableStatement.getXXX methods.

    If a CallableStatement object returns multiple ResultSet objects (using a call to the method execute), all of the results should be retrieved before OUT parameters are retrieved. In this case, to be sure that all results have been accessed, the Statement methods getResultSet, getUpdateCount, and getMoreResults need to be called until there are no more results.

    After this is done, values from OUT parameters can be retrieved using the CallableStatement.getXXX methods.
    The return value will be registered as an extra return value and will cause the behaviour you see.

    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.