Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: problem using insert update component

  1. #1

    Default problem using insert update component

    I have a strange problem with my transformation. Can some 1 help me

    I have a simple transformation, while using the insert/update flow. Below is the error, also attached is the transformation file. Can some one give me some input as to what the problem could be?

    Regards,
    Raju

    2007/11/20 18:29:40 - CENTER_INVOLVED_PARTY.0 - Finished reading query, closing connection.
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : java.lang.RuntimeException: Unable to verify if [STUDY_ID_1 String(50)<binary-string>] is null or not because of an error:java.lang.ClassCastException: java.lang.String
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.row.ValueMeta.isNull(ValueMeta.java:2726)
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.row.ValueMeta.compare(ValueMeta.java:2761)
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:127)
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:272)
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.run(InsertUpdate.java:486)
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Caused by: java.lang.ClassCastException: java.lang.String
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.row.ValueMeta.isNull(ValueMeta.java:2719)
    2007/11/20 18:29:40 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : ... 4 more
    2007/11/20 18:29:40 - CENTER_INVOLVED_PARTY.0 - Finished processing (I=1170, O=0, R=0, W=1170, U=0, E=0)
    Attached Files Attached Files

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

    Default

    Dear Raju,

    Just for fun, I'm showing your very simple transformation here below:


    Personally I don't think that this qualifies as a simple transformation, but let me come to the point...
    You are either mixing data types, storage types or something (hard to say without seeing the database layout).

    However, I would start by disabling the "lazy conversion" option in the "Table Input" steps. (GSK_CENTER_CONTACTS, CENTER_INVOLVED_PARTY)

    Let me know if that fixes the problem.

    Thanks,

    Matt


  3. #3

    Default

    Thanks Matt, for reorganizing my graph and giving it to me. Find the attached transformation for which i disabled the lazy conversion. It still does not work. I get the following errors. Also note , if i connect 252 kettle version with the same repository of that of 3.0 i was able to run the transformation. not sure how and why? And if i want to change this transformation using 252, it wnt let me do it(obviously 252 might not be able to work with 3.0 repository structures). Can you give me some inputs on this..

    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Unexpected error :
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : org.pentaho.di.core.exception.KettleStepException:
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Error in step, asking everyone to stop because of:
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) :
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Error inserting row
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : ORA-01747: invalid user.table.column, table.column, or column specification
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) :
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) :
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:287)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.run(InsertUpdate.java:486)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Error inserting row
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : ORA-01747: invalid user.table.column, table.column, or column specification
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) :
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1333)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1220)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:154)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:272)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : ... 1 more
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Caused by: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1283)
    2007/11/21 11:09:57 - Insert / Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : ... 4 more
    2007/11/21 11:09:57 - GCCJoin.0 - Finished processing (I=0, O=0, R=1031, W=731, U=0, E=0)
    2007/11/21 11:09:57 - GSK_CENTER_CONTACTS.0 - Finished reading query, closing connection.
    2007/11/21 11:09:57 - MJS.0 - Finished processing (I=0, O=0, R=750, W=749, U=0, E=0)
    2007/11/21 11:09:57 - Insert / Update.0 - Finished processing (I=5, O=0, R=5, W=4, U=0, E=1)
    2007/11/21 11:09:57 - GSK_CENTER_CONTACTS.0 - Finished processing (I=3031, O=0, R=0, W=3029, U=0, E=0)
    2007/11/21 11:09:57 - CIPJoin.0 - Finished processing (I=0, O=0, R=719, W=698, U=0, E=0)
    2007/11/21 11:09:57 - Center Involved Party - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Errors detected!
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: Load Studies
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: CIPJoin
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: CENTER_INVOLVED_PARTY
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: GCCJoin
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: Merge Rows
    2007/11/21 11:09:57 - Merge Rows.0 - Finished processing (I=0, O=0, R=1429, W=885, U=0, E=0)
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: GSK_CENTER_CONTACTS
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: MJS
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: Insert / Update
    2007/11/21 11:09:57 - Center Involved Party - Looking at step: Insert / Update Mapping
    2007/11/21 11:09:57 - Spoon - The transformation has finished!!

    Regards,
    Raju
    Attached Files Attached Files

  4. #4

    Default

    Can some 1 help me with this issue. Where ever iam using insert update i get the same error as mentioned below. Not sure why thou.

    Regards,
    Raju

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

    Default

    RTFM ... of Oracle that is

    ORA-01747: invalid user.table.column, table.column, or column specification

    Cause: A column name was specified improperly in the current SQL statement.

    Action: Check the statement's syntax, especially references to column names, and retry the statement.
    Unless there's a bug somewhere in PDI with insert/update, you probably changed the DDL of the table or you made some manual changes in the job. Or you're just not using the right schema, ...


    Regards,
    Sven

  6. #6
    Join Date
    Dec 2007
    Posts
    1

    Default I am having the same issue

    I am having the same issue, but with no SQL error. Could someone give me a tip?

    2007/12/05 10:30:48 - SharedObjects - Reading the shared objects file [file:///C:/Documents and Settings/baum/.kettle/shared.xml]
    2007/12/05 10:30:48 - Spoon - Transformation opened.
    2007/12/05 10:30:48 - Spoon - Launching transformation [TRY1]...
    2007/12/05 10:30:48 - Spoon - Started the transformation execution.
    2007/12/05 10:30:48 - TRY1 - Dispatching started for transformation [TRY1]
    2007/12/05 10:30:48 - TRY1 - Nr of arguments detected:0
    2007/12/05 10:30:48 - TRY1 - This is not a replay transformation
    2007/12/05 10:30:48 - TRY1 - I found 2 different steps to launch.
    2007/12/05 10:30:48 - TRY1 - Allocating rowsets...
    2007/12/05 10:30:48 - TRY1 - Allocating rowsets for step 0 --> Table input
    2007/12/05 10:30:48 - TRY1 - prevcopies = 1, nextcopies=1
    2007/12/05 10:30:48 - TRY1 - Transformation allocated new rowset [Table input.0 - Update.0]
    2007/12/05 10:30:48 - TRY1 - Allocated 1 rowsets for step 0 --> Table input
    2007/12/05 10:30:48 - TRY1 - Allocating rowsets for step 1 --> Update
    2007/12/05 10:30:48 - TRY1 - Allocated 1 rowsets for step 1 --> Update
    2007/12/05 10:30:48 - TRY1 - Allocating Steps & StepData...
    2007/12/05 10:30:48 - TRY1 - Transformation is about to allocate step [Table input] of type [TableInput]
    2007/12/05 10:30:48 - Table input.0 - distribution activated
    2007/12/05 10:30:48 - Table input.0 - Starting allocation of buffers & new threads...
    2007/12/05 10:30:48 - Table input.0 - Step info: nrinput=0 nroutput=1
    2007/12/05 10:30:48 - Table input.0 - output rel. is 1:1
    2007/12/05 10:30:48 - Table input.0 - Found output rowset [Table input.0 - Update.0]
    2007/12/05 10:30:48 - Table input.0 - Finished dispatching
    2007/12/05 10:30:48 - TRY1 - Transformation has allocated a new step: [Table input].0
    2007/12/05 10:30:48 - TRY1 - Transformation is about to allocate step [Update] of type [Update]
    2007/12/05 10:30:48 - Update.0 - distribution activated
    2007/12/05 10:30:48 - Update.0 - Starting allocation of buffers & new threads...
    2007/12/05 10:30:48 - Update.0 - Step info: nrinput=1 nroutput=0
    2007/12/05 10:30:48 - Update.0 - Got previous step from [Update] #0 --> Table input
    2007/12/05 10:30:48 - Update.0 - input rel is 1:1
    2007/12/05 10:30:48 - Update.0 - Found input rowset [Table input.0 - Update.0]
    2007/12/05 10:30:48 - Update.0 - Finished dispatching
    2007/12/05 10:30:48 - TRY1 - Transformation has allocated a new step: [Update].0
    2007/12/05 10:30:48 - TRY1 - This transformation can be replayed with replay date: 2007/12/05 10:30:48
    2007/12/05 10:30:48 - TRY1 - Initialising 2 steps...
    2007/12/05 10:30:48 - Update.0 - Running on slave server #0/1.
    2007/12/05 10:30:48 - TOWNBETH - New database connection defined
    2007/12/05 10:30:48 - Table input.0 - Running on slave server #0/1.
    2007/12/05 10:30:48 - hydrants - New database connection defined
    2007/12/05 10:30:48 - hydrants - Connected to database.
    2007/12/05 10:30:48 - hydrants - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Can't turn auto commit off
    2007/12/05 10:30:48 - Table input.0 - Connected to database...
    2007/12/05 10:30:48 - TOWNBETH - Connected to database.
    2007/12/05 10:30:48 - Update.0 - Connected to database...
    2007/12/05 10:30:48 - TOWNBETH - Auto commit off
    2007/12/05 10:30:48 - TRY1 - Step [Table input.0] initialized flawlessly.
    2007/12/05 10:30:48 - TRY1 - Step [Update.0] initialized flawlessly.
    2007/12/05 10:30:48 - TRY1 - Transformation has allocated 2 threads and 1 rowsets.
    2007/12/05 10:30:48 - Table input.0 - Starting to run...
    2007/12/05 10:30:48 - Update.0 - Starting to run...
    2007/12/05 10:30:48 - Update.0 - Checking row: [FH1028], [668527.99], [1379938.24]
    2007/12/05 10:30:48 - org.pentaho.di.trans.steps.update.Update - Setting preparedStatement to [SELECT AHKTNB, AHK6NB FROM WFAHREP WHERE AHALCD = ? ]
    2007/12/05 10:30:48 - org.pentaho.di.trans.steps.update.Update - Setting update preparedStatement to [UPDATE WFAHREP
    2007/12/05 10:30:48 - org.pentaho.di.trans.steps.update.Update - SET AHKTNB = ?
    2007/12/05 10:30:48 - org.pentaho.di.trans.steps.update.Update - , AHK6NB = ?
    2007/12/05 10:30:48 - org.pentaho.di.trans.steps.update.Update - WHERE AHALCD = ? ]
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Unexpected error :
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : java.lang.RuntimeException: Unable to verify if [Expr1001 String(254)<binary-string>] is null or not because of an error:java.lang.ClassCastException: java.lang.Double
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.row.ValueMeta.isNull(ValueMeta.java:2726)
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.row.ValueMeta.compare(ValueMeta.java:2761)
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.update.Update.lookupValues(Update.java:137)
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.update.Update.processRow(Update.java:246)
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.trans.steps.update.Update.run(Update.java:468)
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Caused by: java.lang.ClassCastException: java.lang.Double
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : at org.pentaho.di.core.row.ValueMeta.isNull(ValueMeta.java:2719)
    2007/12/05 10:30:48 - Update.0 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : ... 4 more
    2007/12/05 10:30:48 - hydrants - Statement canceled!
    2007/12/05 10:30:48 - hydrants - Statement canceled!
    2007/12/05 10:30:48 - TOWNBETH - Connection to database closed!
    2007/12/05 10:30:48 - Update.0 - Finished processing (I=1, O=0, R=1, W=0, U=0, E=1)
    2007/12/05 10:30:48 - Table input.0 - Finished reading query, closing connection.
    2007/12/05 10:30:48 - hydrants - No commit possible on database connection [hydrants]
    2007/12/05 10:30:48 - TRY1 - ERROR (version 3.0.0, build 500 from 2007/11/14 14:59:11) : Errors detected!
    2007/12/05 10:30:48 - TRY1 - Looking at step: Table input
    2007/12/05 10:30:48 - hydrants - Statement canceled!
    2007/12/05 10:30:48 - hydrants - Statement canceled!
    2007/12/05 10:30:48 - hydrants - Statement canceled!
    2007/12/05 10:30:48 - hydrants - Statement canceled!
    2007/12/05 10:30:48 - hydrants - Connection to database closed!
    2007/12/05 10:30:48 - Table input.0 - Finished processing (I=1003, O=0, R=0, W=1002, U=0, E=0)
    2007/12/05 10:30:48 - TRY1 - Looking at step: Update
    2007/12/05 10:30:48 - Spoon - The transformation has finished!!

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.