Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Table Input with Replace Variables

  1. #1

    Default Table Input with Replace Variables

    Hi,

    I got a problem with Table Input step.

    This is my test transformation,

    'Excel input' -> 'Table Input' with replace variable -> ...

    my environment is...
    1) kettle 3.0.1
    2) Oracle 10g r2 (on table input step)


    At design time, I try to see 'preview' on table input setp, Exceptions like below...

    Code:
    2008/01/10 15:26:59 - Trans - Transformation is pre-loaded.
    2008/01/10 15:26:59 - Trans - This is not a replay transformation
    2008/01/10 15:26:59 - Trans - I found 2 different steps to launch.
    2008/01/10 15:26:59 - Trans - Allocating rowsets...
    2008/01/10 15:26:59 - Trans -  Allocating rowsets for step 0 --> Table input
    2008/01/10 15:26:59 - Trans -   prevcopies = 1, nextcopies=1
    2008/01/10 15:26:59 - Trans - Transformation allocated new rowset [Table input.0 - dummy.0]
    2008/01/10 15:26:59 - Trans -  Allocated 1 rowsets for step 0 --> Table input  
    2008/01/10 15:26:59 - Trans -  Allocating rowsets for step 1 --> dummy
    2008/01/10 15:26:59 - Trans -  Allocated 1 rowsets for step 1 --> dummy  
    2008/01/10 15:26:59 - Trans - Allocating Steps & StepData...
    2008/01/10 15:26:59 - Trans -  Transformation is about to allocate step [Table input] of type [TableInput]
    2008/01/10 15:26:59 - Table input.0 - distribution activated
    2008/01/10 15:26:59 - Table input.0 - Starting allocation of buffers & new threads...
    2008/01/10 15:26:59 - Table input.0 - Step info: nrinput=0 nroutput=1
    2008/01/10 15:26:59 - Table input.0 - output rel. is  1:1
    2008/01/10 15:26:59 - Table input.0 - Found output rowset [Table input.0 - dummy.0]
    2008/01/10 15:26:59 - Table input.0 - Finished dispatching
    2008/01/10 15:26:59 - Trans -  Transformation has allocated a new step: [Table input].0
    2008/01/10 15:26:59 - Trans -  Transformation is about to allocate step [dummy] of type [Dummy]
    2008/01/10 15:26:59 - dummy.0 - distribution activated
    2008/01/10 15:26:59 - dummy.0 - Starting allocation of buffers & new threads...
    2008/01/10 15:26:59 - dummy.0 - Step info: nrinput=1 nroutput=0
    2008/01/10 15:26:59 - dummy.0 - Got previous step from [dummy] #0 --> Table input
    2008/01/10 15:26:59 - dummy.0 - input rel is 1:1
    2008/01/10 15:26:59 - dummy.0 - Found input rowset [Table input.0 - dummy.0]
    2008/01/10 15:26:59 - dummy.0 - Finished dispatching
    2008/01/10 15:26:59 - Trans -  Transformation has allocated a new step: [dummy].0
    2008/01/10 15:26:59 - Trans - This transformation can be replayed with replay date: 2008/01/10 15:26:59
    2008/01/10 15:26:59 - Trans - Initialising 2 steps...
    2008/01/10 15:26:59 - dummy.0 - Running on slave server #0/1.
    2008/01/10 15:26:59 - Table input.0 - Running on slave server #0/1.
    2008/01/10 15:26:59 - 10.120.42.70 spf-shop-db01 (shopcmp) - New database connection defined
    2008/01/10 15:27:00 - 10.120.42.70 spf-shop-db01 (shopcmp) - Connected to database.
    2008/01/10 15:27:00 - 10.120.42.70 spf-shop-db01 (shopcmp) - Auto commit off
    2008/01/10 15:27:00 - Table input.0 - Connected to database...
    2008/01/10 15:27:00 - Trans - Step [Table input.0] initialized flawlessly.
    2008/01/10 15:27:00 - Trans - Step [dummy.0] initialized flawlessly.
    2008/01/10 15:27:00 - Trans - Transformation has allocated 2 threads and 1 rowsets.
    2008/01/10 15:27:00 - Table input.0 - Starting to run...
    2008/01/10 15:27:00 - dummy.0 - Starting to run...
    2008/01/10 15:27:00 - Table input.0 - Reading query parameters from stream [Excel Input]
    2008/01/10 15:27:00 - Table input.0 - Reading from step [Excel Input]
    2008/01/10 15:27:00 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Unexpected error : 
    2008/01/10 15:27:00 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.lang.NullPointerException
    2008/01/10 15:27:00 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.step.BaseStep.getRowFrom(BaseStep.java:1417)
    2008/01/10 15:27:00 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.tableinput.TableInput.readStartDate(TableInput.java:58)
    2008/01/10 15:27:00 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:98)
    2008/01/10 15:27:00 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:318)
    2008/01/10 15:27:00 - 10.120.42.70 spf-shop-db01 (shopcmp) - Statement canceled!
    2008/01/10 15:27:00 - 10.120.42.70 spf-shop-db01 (shopcmp) - Statement canceled!
    2008/01/10 15:27:00 - Table input.0 - Finished reading query, closing connection.
    2008/01/10 15:27:00 - dummy.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
    2008/01/10 15:27:00 - 10.120.42.70 spf-shop-db01 (shopcmp) - Connection to database closed!
    2008/01/10 15:27:00 - Table input.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
    and,
    I try to run the transformation, exceptions like below...
    Code:
    2008/01/10 15:28:38 - Text file output.0 - Running on slave server #0/1.
    2008/01/10 15:28:38 - Text file output.0 - Opening output stream in nocompress mode
    2008/01/10 15:28:38 - Text file output.0 - Opening output stream in default encoding
    2008/01/10 15:28:38 - Text file output.0 - Opened new file with name [D:\temp\file.txt]
    2008/01/10 15:28:38 - 10.120.42.70 spf-shop-db01 (shopcmp) - Connected to database.
    2008/01/10 15:28:38 - 10.120.42.70 spf-shop-db01 (shopcmp) - Auto commit off
    2008/01/10 15:28:38 - Table input.0 - Connected to database...
    2008/01/10 15:28:38 - Transformation (test) - Step [Excel Input.0] initialized flawlessly.
    2008/01/10 15:28:38 - Transformation (test) - Step [Table input.0] initialized flawlessly.
    2008/01/10 15:28:38 - Transformation (test) - Step [Select values.0] initialized flawlessly.
    2008/01/10 15:28:38 - Transformation (test) - Step [Text file output.0] initialized flawlessly.
    2008/01/10 15:28:38 - Transformation (test) - Transformation has allocated 4 threads and 3 rowsets.
    2008/01/10 15:28:38 - Excel Input.0 - Starting to run...
    2008/01/10 15:28:38 - Table input.0 - Starting to run...
    2008/01/10 15:28:38 - Excel Input.0 - Opening openFile #0 : D:\temp\shop_test.xls
    2008/01/10 15:28:38 - Text file output.0 - Starting to run...
    2008/01/10 15:28:38 - Table input.0 - Reading query parameters from stream [Excel Input]
    2008/01/10 15:28:38 - Table input.0 - Reading from step [Excel Input]
    2008/01/10 15:28:38 - Select values.0 - Starting to run...
    2008/01/10 15:28:38 - Excel Input.0 - Get sheet #0.0
    2008/01/10 15:28:38 - Excel Input.0 - Get sheet #0.0
    2008/01/10 15:28:38 - Excel Input.0 - Get sheet #0.0
    2008/01/10 15:28:39 - Excel Input.0 - No more files to be processed! (1 files done)
    2008/01/10 15:28:39 - Excel Input.0 - Finished processing (I=0, O=0, R=0, W=2, U=0, E=0)
    2008/01/10 15:28:39 - Table input.0 - Query parameters found = [Ljava.lang.Object;@121fd61
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [my_product_1 String], [my_product_1 String]
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [my_product_1 String] on prepared statement (String)
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: 부적합한 열 인덱스
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 부적합한 열 인덱스
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ERROR in part: P Set values
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Unexpected error : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : An error occurred executing SQL in part [P Set values]:
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : select a.productid ,a.shopprodid,a.shopid ,b.imgserver server, a.imageurl imgpath, c.imgurl filepath, b.nasshoppath,
    sysdate as regdtm, sysdate as occdtm, '0' as priority, 'I' as status, null as errorcode, 0 as errorcnt
    from masterproduct a,shopinfo b ,masterprodimage c 
    where  a.productid= ?
    and a.shopid=b.shopid 
    and b.validyn='Y' 
    and a.prodstatus in ('Y') 
    and a.productid=c.productid 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [my_product_1 String], [my_product_1 String]
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [my_product_1 String] on prepared statement (String)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: 부적합한 열 인덱스
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 부적합한 열 인덱스
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.openQuery(Database.java:1751)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:192)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:119)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:318)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [my_product_1 String], [my_product_1 String]
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [my_product_1 String] on prepared statement (String)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: 부적합한 열 인덱스
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 부적합한 열 인덱스
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.setValues(Database.java:1051)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.setValues(Database.java:792)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.openQuery(Database.java:1685)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     ... 3 more
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [my_product_1 String] on prepared statement (String)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: 부적합한 열 인덱스
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 부적합한 열 인덱스
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : 
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.setValue(Database.java:1024)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.setValues(Database.java:1047)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     ... 5 more
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Caused by: java.sql.SQLException: 부적합한 열 인덱스
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5306)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5298)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     at org.pentaho.di.core.database.Database.setValue(Database.java:899)
    2008/01/10 15:28:39 - Table input.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :     ... 6 more
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - Statement canceled!
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - Statement canceled!
    2008/01/10 15:28:39 - Table input.0 - Finished reading query, closing connection.
    2008/01/10 15:28:39 - Text file output.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
    2008/01/10 15:28:39 - Select values.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
    2008/01/10 15:28:39 - 10.120.42.70 spf-shop-db01 (shopcmp) - Connection to database closed!
    2008/01/10 15:28:39 - Table input.0 - Finished processing (I=0, O=0, R=2, W=0, U=0, E=1)
    2008/01/10 15:28:39 - Spoon - The transformation has finished!!
    is this a known bug?? (I found this, http://jira.pentaho.org/browse/PDI-588)

    I read the forum threads related table input with replace variables, but I dont konw what the problem is...

    I attached my tranformation & excel file.

    thanks.

    regards,
    yw kim.
    Attached Files Attached Files

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

    Default

    It's not related to PDI-588.

    Can you
    1) Switch off "Replace variables" in the table input step: you're not using any %{} or %%%% for the moment.
    2) Switch on "Execute for each row"

    Rerun if possible using english locale (or providing translations for the "funny" characters).

    Regards,
    Sven

  3. #3

    Default thanks, sboden.

    Hi, sboden.

    It's solved.

    2) switch on 'execute for each row'

    and then, It works fine.

    It's my fault, not bug.

    thanks.

    regards,
    yw kim.

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.