Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Problems with Table Input with parameters

  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Unhappy Problems with Table Input with parameters

    I am trying to pull data from a DB2 database based on what is already in Kettle.
    I am currently trying to use a Table Input to do this. My SQL for the Table Input is:

    SELECT L.LOOKUP_ID, L.LOOKUP_DESCRIPTION, LA.LOOKUP_PARENT_ID
    FROM OTPUSER.LOOKUP L
    INNER JOIN
    OTPUSER.LOOKUP_ASSOC LA
    on L.LOOKUP_ID = LA.LOOKUP_CHILD_ID
    WHERE LA.LOOKUP_PARENT_ID = ?
    AND L.LOOKUP_DESCRIPTION = CAST(? AS CHAR(30))

    When I use preview it outputs one row and spits out the error. The output is correct, except I should be getting 9 more rows. The SQL works fine without the parameters.

    Here is the error:

    Unexpected error :
    org.pentaho.di.core.exception.KettleDatabaseException:
    Couldn't get row from result set
    [ibm][db2][jcc][10120][10898] Invalid operation: result set is closed.

    at org.pentaho.di.core.database.Database.getRow(Database.java:2727)
    at org.pentaho.di.core.database.Database.getRow(Database.java:2647)
    at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:129)
    at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:314)
    Caused by: com.ibm.db2.jcc.c.SqlException: [ibm][db2][jcc][10120][10898] Invalid operation: result set is closed.
    at com.ibm.db2.jcc.c.wf.mb(wf.java:3193)
    at com.ibm.db2.jcc.c.wf.c(wf.java:247)
    at com.ibm.db2.jcc.c.wf.next(wf.java:234)
    at org.pentaho.di.core.database.Database.getRow(Database.java:2662)
    ... 3 more
    2007/12/06 11:17:11 - Table input.0 - Finished reading query, closing connection.

    Any clue what might be causing this? I am using 3.0.0.GA, and I have already updated the db2 drivers for my database.

    Thanks in advance,

    Billy
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2007
    Posts
    4

    Default More Info

    I get the error when I use the SQL statement:

    SELECT L.LOOKUP_ID, L.LOOKUP_DESCRIPTION
    , CAST(? AS CHAR(30)) AS X
    FROM OTPUSER.LOOKUP L
    INNER JOIN
    OTPUSER.LOOKUP_ASSOC LA
    on L.LOOKUP_ID = LA.LOOKUP_CHILD_ID
    WHERE L.LOOKUP_DESCRIPTION = 'Interactive'


    but when I take out the WHERE statement it works fine.

    SELECT L.LOOKUP_ID, L.LOOKUP_DESCRIPTION
    , CAST(? AS CHAR(30)) AS X
    FROM OTPUSER.LOOKUP L
    INNER JOIN
    OTPUSER.LOOKUP_ASSOC LA
    on L.LOOKUP_ID = LA.LOOKUP_CHILD_ID

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

    Default

    Looks like DB2 closes the connection before we get a chance to read more rows. If it changes with the query you launch, then it has all the markings of a DB2 bug. (I've had similar things with Oracle queries). Try to run them on the server (db2 client tools), try to figure out if it's a JDBC problem or a server problem.

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

    Default

    It's a known feature with DB2 (of v7 and v8 I'm sure off), they have a "strange" interpretation of the JDBC spec:

    When ResultSet is TYPE_FORWARD_ONLY it reaches the end of the ResultSet it returns false and closes the ResultSet.

    The typical use is while(rs.next) {...} and you usually dont use the resultset anymore. But in case you do a rs.next again, it will result a "com.ibm.db2.jcc.a.SqlException: Invalid operation: result set closed" exception.

    This doesn't occur with TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE mode.

    Type forward can still be used but instead of relying on rs.next(), the state of the cursor should be kept somewhere external of the resultset... so that next() never gets called when the end of the cursor has already been seen.

    Regards,
    Sven
    Last edited by sboden; 12-07-2007 at 03:31 AM.

  5. #5
    Join Date
    Dec 2007
    Posts
    4

    Default

    So what does that mean? Is there a change I can make in the connection properties, or does this require code changes. I am having difficulties understanding the options tab. I am unable to add any db2 properties to it without breaking it.

    Thanks for the help,

    Billy

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

    Default

    Code change. Still have to check but that will be for tomorrow. It's something that works out wel in most databases, but the DB2 driver implemented it a bit too strict and off-beat.

    Regards,
    Sven

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

    Default

    No luck faking it so far.

    Regards,
    Sven

  8. #8
    Join Date
    Dec 2007
    Posts
    4

    Default Status?

    What is the status on this problem? Do I need to submit a bug report?

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

    Default

    yep... no bug report and it gets forgotten.

    Also attach your transformation to the JIRA.

    Regards,
    Sven
    Last edited by sboden; 12-28-2007 at 11:56 AM.

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.