Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: data-integration 4.2 4.2.1 4.3 Insert Update Don't work

  1. #1
    Join Date
    Aug 2006
    Posts
    141

    Default data-integration 4.2 4.2.1 4.3 Insert Update Don't work

    Hi all,


    I've worked with data-integration some time, and i've made some jobs.

    One of them has two transformations, type master - detaill, over the first use the step INSERT UPDATE to insert the parent of reg and then over the second use another INSERT UPDATE to insert the childrens.

    It' was implemented over linux with data-integration 4.2. and work very good, it runs with the help of a cron task. But now I have to change this work to another pc that use also linux but it don't work.

    I've tested java 1.7 , and data-integration 4.2, 4.2.1 and 4.3. With all the job only run if I run the kitchen command over the shell, but if i try to use the crontab it returns the next error.

    ERROR 31-08 09:50:23,387 - Insert / Update 2 - Unexpected error
    ERROR 31-08 09:50:23,388 - Insert / Update 2 - org.pentaho.di.core.exception.KettleStepException:
    Error in step, asking everyone to stop because of:


    Error inserting/updating row
    ERROR: invalid input syntax for type numeric: "inf"




    at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:307)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    at java.lang.Thread.run(Thread.java:722)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    Error inserting/updating row
    ERROR: invalid input syntax for type numeric: "inf"


    at org.pentaho.di.core.database.Database.insertRow(Database.java:1415)
    at org.pentaho.di.core.database.Database.insertRow(Database.java:1329)
    at org.pentaho.di.core.database.Database.insertRow(Database.java:1317)
    at org.pentaho.di.core.database.Database.insertRow(Database.java:1300)
    at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:105)
    at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:290)
    ... 2 more
    Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type numeric: "inf"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2077)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1810)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:498)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:332)
    at org.pentaho.di.core.database.Database.insertRow(Database.java:1364)
    ... 7 more


    ERROR 31-08 09:50:23,390 - Nuevos_Reg_Detalle - Errors detected!
    ERROR 31-08 09:50:23,390 - Nuevos_Reg_Detalle - Errors detected!


    I verified the tables of database, the JDBC files, and the connection all is fine. also don't exist none field call INF.


    Somebody has had a similar problem?

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    INF does not refer to a field name, it is the number entity "infinity".
    You seem to have problems converting some floating point values.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Aug 2006
    Posts
    141

    Default

    thanks for your answer, that makes sense two of fields are numeric and are of type 123,456. Now I will try to change data properties in the meta tab of my select step, and try again, I will report back if it works.

  4. #4
    Join Date
    Aug 2006
    Posts
    141

    Default

    Hi again, I found the problem but this become more strange.

    Finally I found that has a problem with my last field valor_unitario, but i verified that the fields are well definied on database, for the case valor_unitario Numeric(15, 5),
    also over my select step under Meta tag I verified name = valor_unitario Type = Number Length = 15 Precision = 5

    However the transformation show the next Error.

    INFO 24-09 11:55:18,911 - Insert / Update 2 Mapping - Finished processing (I=0, O=0, R=73, W=73, U=0, E=0)
    ERROR 24-09 11:55:18,930 - Insert / Update 2 - Unexpected error
    ERROR 24-09 11:55:18,932 - Insert / Update 2 - org.pentaho.di.core.exception.KettleStepException:
    Error in step, asking everyone to stop because of:

    offending row : [id_capt_reg Integer(9)], [id_producto Integer(9)], [cantidad_ventas Number(15, 5)], [valor_unitario Number(15, 5)]

    Error setting value #4 [valor_unitario Number(15, 5)] on prepared statement (Number, rounding to precision [5])
    java.lang.NumberFormatException
    at java.lang.Thread.run (Thread.java:722)
    at org.pentaho.di.trans.step.RunThread.run (RunThread.java:40)
    at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow (InsertUpdate.java:290)
    at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues (InsertUpdate.java:102)
    at org.pentaho.di.core.database.Database.setValuesInsert (Database.java:885)
    at org.pentaho.di.core.database.Database.setValues (Database.java:1130)
    at org.pentaho.di.core.database.Database.setValue (Database.java:940)
    at org.pentaho.di.core.Const.round (Const.java:735)
    at java.math.BigDecimal.valueOf (BigDecimal.java:1069)
    at java.math.BigDecimal.<init> (BigDecimal.java:739)
    at java.math.BigDecimal.<init> (BigDecimal.java:470)

  5. #5
    Join Date
    Jul 2009
    Posts
    476

    Default

    In Spoon, preview the rows produced by the step before the Insert/Update step and look for any values that exceed numeric(15,5). I see that you are using PostgreSQL. Note that PostgreSQL will round a number before inserting it. For example:

    Code:
    test=# create table t (x numeric(3,2));
    CREATE TABLE
    test=# insert into t values (9.99);
    INSERT 0 1
    test=# insert into t values (9.995);
    ERROR:  numeric field overflow
    DETAIL:  A field with precision 3, scale 2 must round to an absolute value less than 10^1.
    The insert fails because PostgreSQL round 9.995 up to 10.00, which is numeric(4,2), which exceeds the column capacity.

  6. #6
    Join Date
    Aug 2006
    Posts
    141

    Default

    Thanks for the help, I've found the record fails me indeed as you say the value is out of range numeric (15,5). But this filter value is a couple of steps before, but it seems that for the insert function will always take the greatest value as a reference to build the insert


    Quote Originally Posted by robj View Post
    In Spoon, preview the rows produced by the step before the Insert/Update step and look for any values that exceed numeric(15,5). I see that you are using PostgreSQL. Note that PostgreSQL will round a number before inserting it. For example:

    Code:
    test=# create table t (x numeric(3,2));
    CREATE TABLE
    test=# insert into t values (9.99);
    INSERT 0 1
    test=# insert into t values (9.995);
    ERROR:  numeric field overflow
    DETAIL:  A field with precision 3, scale 2 must round to an absolute value less than 10^1.
    The insert fails because PostgreSQL round 9.995 up to 10.00, which is numeric(4,2), which exceeds the column capacity.

  7. #7
    Join Date
    Aug 2006
    Posts
    141

    Default

    I think that maybe is possible to use round function to the calculator step, for resolve this problem, because I can't modify the table definition.

  8. #8
    Join Date
    Aug 2006
    Posts
    141

    Default

    Thank's marabu and robj your answers help me a lot about what are my problem and how solve it.


    Finally I edit my steps and complete information metadata, also I put one filter for rows with estrange data, and now all the process work fine.

Tags for this Thread

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.