Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Insert / Update error

  1. #1
    Join Date
    Jun 2007
    Posts
    476

    Default Insert / Update error

    Another problem, i guess you will have nightmares with me.. LOL.

    I have my transformation that loads my fact table, in my fact table i have 6 ids (agrupador_id, tienda_id, cuenta_id, region_id, territorio_id and fecha_fecha) and the measure (monto). The last point of the trans is to do a Insert / Update, i need that when all my 6 ids are found at the table, it just updates the monto field, so my step looks like the attached screenshot (insert.jpg). As you can see, i set all the ids in the keys to lookup and all the ids in the update fields as "N" in update and monto as "Y" in update.

    When I run it and it finds a matching value with all the keys, it should update the monto field, instead it crashes with the following error:

    Code:
    2007/11/27 22:37:39 - org.pentaho.di.trans.steps.insertupdate.InsertUpdate - Setting preparedStatement to [SELECT tienda_id, agrupador_id, cuenta_id, territorio_id, region_id, monto, fecha_fecha FROM resultadofact WHERE tienda_id = ?  AND agrupador_id = ?  AND cuenta_id = ?  AND territorio_id = ?  AND region_id = ?  AND fecha_fecha = ? ]
    2007/11/27 22:37:39 - olapnew - Preparing statement: 
    2007/11/27 22:37:39 - olapnew - INSERT INTO resultadofact(tienda_id, agrupador_id, cuenta_id, territorio_id, region_id, monto, fecha_fecha) VALUES ( ?,  ?,  ?,  ?,  ?,  ?,  ?)
    2007/11/27 22:37:39 - org.pentaho.di.trans.steps.insertupdate.InsertUpdate - Setting update preparedStatement to [UPDATE resultadofact
    2007/11/27 22:37:39 - org.pentaho.di.trans.steps.insertupdate.InsertUpdate - SET ,   monto = ?
    2007/11/27 22:37:39 - org.pentaho.di.trans.steps.insertupdate.InsertUpdate - WHERE tienda_id = ? AND   agrupador_id = ? AND   cuenta_id = ? AND   territorio_id = ? AND   region_id = ? AND   fecha_fecha = ? ]
    2007/11/27 22:37:39 - Insert / Update.0 - ERROR (version 3.0.1, build 301011 from 2007/11/23 14:23:37) : Unexpected error : 
    2007/11/27 22:37:39 - Insert / Update.0 - ERROR (version 3.0.1, build 301011 from 2007/11/23 14:23:37) : java.lang.ClassCastException: java.lang.Long
    2007/11/27 22:37:39 - Insert / Update.0 - ERROR (version 3.0.1, build 301011 from 2007/11/23 14:23:37) :     at org.pentaho.di.core.row.ValueMeta.getNumber(ValueMeta.java:1296)
    2007/11/27 22:37:39 - Insert / Update.0 - ERROR (version 3.0.1, build 301011 from 2007/11/23 14:23:37) :     at org.pentaho.di.core.row.ValueMeta.compare(ValueMeta.java:2800)
    2007/11/27 22:37:39 - Insert / Update.0 - ERROR (version 3.0.1, build 301011 from 2007/11/23 14:23:37) :     at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:127)
    2007/11/27 22:37:39 - Insert / Update.0 - ERROR (version 3.0.1, build 301011 from 2007/11/23 14:23:37) :     at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:272)
    2007/11/27 22:37:39 - Insert / Update.0 - ERROR (version 3.0.1, build 301011 from 2007/11/23 14:23:37) :     at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.run(InsertUpdate.java:486)
    Any idea on this? the only thing I notices is that i found a "," in the update that should not be there: "UPDATE resultadofact SET , monto = ?"
    Attached Images Attached Images  

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

    Default

    You are mixing Numbers and Integers.

  3. #3
    Join Date
    Jun 2007
    Posts
    476

    Default

    Thanks, i just sulved that, now everything is inserted correctly, but the second time i run it, it crashes, it sends me a mysql error:


    Code:
    INFO  28-11 22:47:08,640 (LogWriter.java:println:403)  -Filter rows 2.0 - linenr 10000
    ERROR 28-11 22:47:08,953 (LogWriter.java:println:400)  -Insert / Update.0 - Unexpected error :
    ERROR 28-11 22:47:08,953 (LogWriter.java:println:400)  -Insert / Update.0 - org.pentaho.di.core.exception.KettleStepException:
    Error in step, asking everyone to stop because of:
    
    Error inserting row
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '   monto = 9595
    7
    WHERE tienda_id = 93 AND   agrupador_id = 16 AND   cuenta_id =' at line 2
    
    
            at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:287)
            at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.run(InsertUpdate.java:486)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Error inserting row
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '   monto = 9595
    7
    WHERE tienda_id = 93 AND   agrupador_id = 16 AND   cuenta_id =' at line 2
    
            at org.pentaho.di.core.database.Database.insertRow(Database.java:1333)
            at org.pentaho.di.core.database.Database.insertRow(Database.java:1220)
            at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:154)
            at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:272)
            ... 1 more
    Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right s
    yntax to use near '   monto = 95957
    WHERE tienda_id = 93 AND   agrupador_id = 16 AND   cuenta_id =' at line 2
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
            at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
            at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
            at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
            at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1166)
            at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1082)
            at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1067)
            at org.pentaho.di.core.database.Database.insertRow(Database.java:1283)
            ... 4 more
    I checked my mysql query log and noticed this:

    Code:
    UPDATE resultadofact
    SET ,   monto = 14773
    WHERE tienda_id = 0 AND   agrupador_id = 15 AND   cuenta_id = 57 AND   territorio_id = 0 AND   region_id = 0 AND   fecha_fecha = 200706
    As you can se, there is a "," just after the set, thing that is wrong.

    Thanks

    EDITED: "I know, its not the same update, the mysql queyr log is of a newer test"

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

    Default

    Kettle just executes what you tell it to execute.
    The SQL statements you see in the error message and the MySQL log is not what we send to MySQL either.
    Try running in "Detailed" logging mode to see what SQL we sent to the database.

    Also, if you don't attach a transformation and show us the layout of the target table, we can't help you.

    Finally, if you thinker with the table and database layouts yourself, make sure to clear the database metadata cache. (right click on the database connection)

    Matt

  5. #5
    Join Date
    Nov 2007
    Posts
    29

    Default

    Ciao,
    it is the same error that I have. You can see "3.0.0.GA: Error with Insert/update". This is due because the first filed to update have the combo box "Update" set on value "N". The SQL statement generate for update is wrong.

    Luca

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

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

  8. #8
    Join Date
    Jun 2007
    Posts
    476

    Default

    Beautiful!! Thanks, nice job, this new set of jars also fixed my problem that it took a minute to load spoon because its trying to check internet and i'm behind firewall.

    Still have some problems that i'll be posting here in the day!

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

    Default

    A few of my colleagues tried to grow a brain and inserted a version checker.
    Apparently it's the same as the one in the Pentaho Pre-configured Installation (PCI), but the timeouts, exceptions, etc made me rip it out again.

    Don't get me wrong, a version checker is a great way to see what versions people are using and a great way to inform the user when a new version arrives.
    Next week we'll make another attempt to get it going.

    ;-)

    Matt

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.