Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: offending row - Error setting value java.lang.NullPointerException

  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Default offending row - Error setting value java.lang.NullPointerException

    Hello everyone!

    I use Kettle - Spoon version 3.1.0. I have a job with 2 transformations, and one of them performs select on UDB tables based on parameters I am getting from the first one. I store parameters values in text file and then just do the read using Fixed file Input.


    Here is the piece of SQL where I pass parameters:

    WHERE
    MATRICULATION_TERM = ${TERM1} AND
    MATRICULATION_YEAR = ${YEAR1}

    Here is what I am getting in debugging mode:

    2009/06/26 10:49:39 - Read the file terms.0 - Finished processing (I=1, O=0, R=0, W=1, U=1, E=0)
    2009/06/26 10:49:39 - extract udb data.0 - Query parameters found = [1], [2010], [6], [2009], [9], [2009]
    2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException:
    2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : offending row : [TERM1 String(1)], [YEAR1 String(4)], [TERM2 String(1)], [YEAR2 String(4)], [TERM3 String(1)], [YEAR3 String(4)]
    2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : Error setting value #1 [TERM1 String(1)] on prepared statement (String)
    2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : java.lang.NullPointerException
    2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : ERROR in part: P Set values
    2009/06/26 10:49:39 - extract udb data.0 - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : Unexpected error :
    2009/06/26 10:49:39 - extract udb data.0 - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : org.pentaho.di.core.exception.KettleDatabaseException:
    2009/06/26 10:49:39 - extract udb data.0 - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : An error occurred executing SQL in part [P Set values]:
    2009/06/26 10:49:39 - extract udb data.0 - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : SELECT *


    FROM

    ...
    WHERE
    MATRICULATION_TERM = AND
    MATRICULATION_YEAR =


    What I am doing wrong? It seems it found the query parameters correctly, but it assigns them to null when putting them in SQL.


  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Looks like you aren't setting the variables you are trying to use.

    Remember, you can't use a variable in the same transformation as the one where you set it.

    Now then...

    I don't know what the first transform does, but assume that you have a text file that looks something like:

    Term Year
    04 - 2009
    05 - 2009
    07 - 2009

    And that you have a Text File Input connected to a Table Input.

    So... You want the Table Input to read:

    SELECT *
    FROM

    udbtable
    WHERE
    MATRICULATION_TERM = ? AND
    MATRICULATION_YEAR = ?


    And make sure you have selected the "Insert Data from Step" to use the Text File Input, and the "Execute for each row" selected

    It will then run:
    SELECT * FROM udbtable WHERE MATRICULATION_TERM = 04 AND MATRICULATION_YEAR = 2009
    SELECT * FROM udbtable WHERE MATRICULATION_TERM = 05 AND MATRICULATION_YEAR = 2009
    SELECT * FROM udbtable WHERE MATRICULATION_TERM = 07 AND MATRICULATION_YEAR = 2009

    And stream all the data that matches to the next step as if it were a single query.

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    Default

    Thank you. It does work now exactly the way it should work!

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.