Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Error in Transformation :writing the data from txt file to postgresql database table

  1. #1
    Join Date
    Apr 2015
    Posts
    13

    Unhappy Error in Transformation :writing the data from txt file to postgresql database table

    Hi all,
    I'm new to PDI.

    Environment in use :
    installed Postgresql9.4
    unzipped PDI CE 5.3

    Error in transformation where I read data (5 rows) from a textfile 'C:\Anand\EmpSource.txt' and want to write into a postgresql database table.

    text file containing sample data ....
    EID,EName
    101,Anand
    102,Arvind
    103,Tvisha
    104,Vidushi
    105,Sharada


    Steps followed for database connection in transformation-
    connection name - MyPostgreSqlCon
    Connection type - postgresql
    Access - Native(JDBC)
    Hostname - Localhost
    Database name - postgres
    Port no - 5432
    user name - postgres
    Clicking Test button.. it says Connection is OK
    Aslo able to Preview data in "text file Input" Step

    tablename under postgres database used is - test

    Error Log :
    2015/04/17 17:53:33 - Spoon - Save as...
    2015/04/17 17:53:33 - Spoon - Save file as...
    2015/04/17 17:54:13 - Spoon - Transformation opened.
    2015/04/17 17:54:13 - Spoon - Launching transformation [basic]...
    2015/04/17 17:54:13 - Spoon - Started the transformation execution.
    2015/04/17 17:54:13 - basic - Dispatching started for transformation [basic]
    2015/04/17 17:54:13 - Table output.0 - Connected to database [MyConn] (commit=1000)
    2015/04/17 17:54:13 - Text file input.0 - Opening file: file:///C:/Anand/EmpSource.txt
    2015/04/17 17:54:13 - Text file input.0 - Finished processing (I=6, O=0, R=0, W=5, U=1, E=0)
    2015/04/17 17:54:13 - Table output.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Unexpected batch update error committing the database connection.
    2015/04/17 17:54:13 - Table output.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2015/04/17 17:54:13 - Table output.0 - Error updating batch
    2015/04/17 17:54:13 - Table output.0 - Batch entry 0 INSERT INTO test (ID, EmpName) VALUES ( 101, 'Anand') was aborted. Call getNextException to see the cause.
    2015/04/17 17:54:13 - Table output.0 -
    2015/04/17 17:54:13 - Table output.0 - at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1351)
    2015/04/17 17:54:13 - Table output.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1340)
    2015/04/17 17:54:13 - Table output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:571)
    ....
    ....
    Do I need to do some kind of Initialization and Configuration of postgresql before i start using spoon ?

    Plz help

    Regards
    BiSeeker

  2. #2
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Quote Originally Posted by biseeker View Post
    Do I need to do some kind of Initialization and Configuration of postgresql before i start using spoon ?
    Yes, you have to at least create the table first.
    In your table output, turn off Batch Updates -- you will get better error messages that way. Once you have everything working, you can turn it back on.

  3. #3
    Join Date
    Apr 2015
    Posts
    13

    Default

    Hi Gutlez,

    I already have a table by name test with fields ID - (integer ),EmpName - (character varying (30)) under the default postgres database.
    In "Text file input" step , I have fields - EID - integer and EName - string both of length 15.
    In "Table output" step , I mapped EID --> ID and EName --> EmpName
    and also turned off Batch Updates as you mentioned.. ..still the problem persists but with clear error msgs.

    Error Log:
    2015/04/18 13:56:53 - Spoon - Transformation opened.
    2015/04/18 13:56:53 - Spoon - Launching transformation [basic]...
    2015/04/18 13:56:53 - Spoon - Started the transformation execution.
    2015/04/18 13:56:53 - basic - Dispatching started for transformation [basic]
    2015/04/18 13:56:53 - Table output.0 - Connected to database [MyConn] (commit=1000)
    2015/04/18 13:56:53 - Text file input.0 - Opening file: file:///C:/Anand/EmpSource.txt
    2015/04/18 13:56:53 - Text file input.0 - Finished processing (I=6, O=0, R=0, W=5, U=1, E=0)
    2015/04/18 13:56:53 - Table output.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Because of an error, this step can't continue:
    2015/04/18 13:56:53 - Table output.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2015/04/18 13:56:53 - Table output.0 - Error inserting row into table [test] with values: [101], [Anand]
    2015/04/18 13:56:53 - Table output.0 -
    2015/04/18 13:56:53 - Table output.0 - Error inserting/updating row
    2015/04/18 13:56:53 - Table output.0 - ERROR: column "id" of relation "test" does not exist
    ....
    ....
    it says column "id" of relation "test" does not exist.
    But the column name in the test table is "ID" and not "id" . As postgresql is case sensitive, I think if this is sorted out then it would make things easier.But still not sure.
    your help would be appreciated.
    Regards
    BiSeeker
    Last edited by biseeker; 04-18-2015 at 04:48 AM.

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

    Default

    So long, and thanks for all the fish.

  5. #5
    Join Date
    Apr 2015
    Posts
    13

    Default

    Thanks Marabu,
    your post solved the problem in a way.
    I changed the col names of my test table in postgresql database to lowercase and it worked perfectly. but when I again changed to upper case the same problem popped up.
    I tried to alter the col names by adding quotes as...
    alter table "test" rename "empname" to "EmpName"
    alter table "test" rename "id" to "ID"

    and when i clicked the test table in the database i could see this definition in the sql pane (with quotes for ID and EmpName) ..
    CREATE TABLE test
    (
    "ID" integer NOT NULL,
    "EmpName" character varying(30),
    CONSTRAINT "ID" PRIMARY KEY ("ID")
    )

    How would I map the fields in the table output step without altering the field names in the postgresql database and make it look ID in the Spoon?

    Regards
    BiSeeker
    Last edited by biseeker; 04-18-2015 at 02:22 PM.

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.