Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: what is the difference between table insert, table output and sql file?

  1. #1

    Default what is the difference between table insert, table output and sql file?

    Hi forum,
    Seems to be not a bug, otherwise others would complain about the same issue ...
    I'm running since a while in issues with my old, existing transformations. They worked already, but since 3.1 (or is this because I updated Postgres to 8.3?!) some work as usually, others worked for a while (my feeling at least ...), but after having modified some steps, I can't get to the production.
    Checked also my libext directory, in my 3.1 environment I used the old Postgres jdbc 8.2. So that should be not the root cause, after updating them to 8.3 exactly the same behaviour. And: The update/insert works, so it's not a jdbc issue.
    In the Postgres DBMS log I get an error message, telling me, the client lost the connection.

    I'm trying to write to PostgreSQL 8.3 imported accounting records.
    I'm transforming them to the right formats, if I write them to SQL file, that's fine, I can use plsql and write to my Postgres Database.
    This works also for insert/update.

    But with Table output I get - I tried several days many different combinations in the meantime ... - tried detailed logging and checked my output, seems to look ok:

    INSERT INTO facts_gl_pl_accounting(amount_debted, company_id, version_id, currency_id, conslevel_id, company_ic_id, division_id, nation_id, costunit_id, profitcenter_id, account_id, account_contra_id, costcenter_id, period_id, movement, "date", amount_credited) VALUES (206560.1,1,1,1,1,1,1,1,1,1,1970,361,1,492,118723,'20090101',0);INSERT INTO facts_gl_pl_accounting(amount_debted, company_id, version_id, currency_id, conslevel_id, company_ic_id, division_id, nation_id, costunit_id, profitcenter_id, account_id, account_contra_id, costcenter_id, period_id, movement, "date", amount_credited) VALUES (153177.8,1,1,1,1,1,1,1,1,1,1970,380,1,492,118725,'20090101',0);INSERT INTO facts_gl_pl_accounting(amount_debted, company_id, version_id, currency_id, conslevel_id, company_ic_id, division_id, nation_id, costunit_id, profitcenter_id, account_id, account_contra_id, costcenter_id, period_id, movement, "date", amount_credited) VALUES (4000000,1,1,1,1,1,1,1,1,1,1970,360,1,492,118727,'20090101',0);INSERT INTO facts_gl_pl_accounting(amount_debted, company_id, version_id, currency_id, conslevel_id, company_ic_id, division_id, nation_id, costunit_id, profitcenter_id, account_id, account_contra_id, costcenter_id, period_id, movement, "date", amount_credited) VALUES (206560.1,1,1,1,1,1,1,1,1,1,361,1970,1,492,118724,'20090101',0);INSERT INTO facts_gl_pl_accounting(amount_debted, company_id, version_id, currency_id, conslevel_id, company_ic_id, division_id, nation_id, costunit_id, profitcenter_id, account_id, account_contra_id, costcenter_id, period_id, movement, "date", amount_credited) VALUES (153177.8,1,1,1,1,1,1,1,1,1,380,1970,1,492,118726,'20090101',0);INSERT INTO facts_gl_pl_accounting(amount_debted, company_id, version_id, currency_id, conslevel_id, company_ic_id, division_id, nation_id, costunit_id, profitcenter_id, account_id, account_contra_id, costcenter_id, period_id, movement, "date", amount_credited) VALUES (4000000,1,1,1,1,1,1,1,1,1,360,1970,1,492,118728,'20090101',0);

    The error message from Kettle:
    2009/07/02 20:07:45 - Felder GegKto und Kto - _id manipulieren.0 - Finished processing (I=0, O=0, R=10909, W=10909, U=10909, E=0)
    2009/07/02 20:07:45 - Table facts_gl_pl_accounting Output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Unexpected error :
    2009/07/02 20:07:45 - Table facts_gl_pl_accounting Output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : java.lang.ArithmeticException: / by zero
    2009/07/02 20:07:45 - Table facts_gl_pl_accounting Output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:241)
    2009/07/02 20:07:45 - Table facts_gl_pl_accounting Output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:78)
    2009/07/02 20:07:45 - Table facts_gl_pl_accounting Output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2009/07/02 20:07:45 - Table facts_gl_pl_accounting Output.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.java:630)
    2009/07/02 20:07:45 - Delete fields - Felder löschen.0 - Finished processing (I=0, O=0, R=14, W=14, U=14, E=0)

    It's always throwing this error, when the record with the amount 400000 should be written. Seems the missing dot is the cause?
    But why is this working with insert/update, why with psql?
    Is this a similar issue as with selecting records from postgres and writing them back, but null values are not accepted?

    Any hints?!
    Cheers,
    Alexander

  2. #2

    Default

    yup there is a field (forget its name) in the table output step thats the batch commit amount, normally you'd give it 1000 or something but magically on occasion it changes to 0, just give it a different number

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  3. #3

    Default wow THANKS a lot, that is the solution

    Hi Tom
    MANY, MANY thanks!!!!!

    I tried already beforehand with other values, I had here 10 as a setting (which I didn't modify as you pointed out), thought, ok, I use not batch, so I put it to 0 (I thought, this is no limit at all) and played around with batch yes or no.

    But now I used your 10000 and it runs as a charme!

    So I'm happy to hear, I didn't do something really stupid and I'm getting an answer like, read the documentation, no one asks this stupid question ;-)

    Cheers,
    Alexander

  4. #4

    Default

    hehe, well i only know cause the same thing happened to me
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

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.