Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: Problem in populating a table

  1. #1

    Default Problem in populating a table

    Hello,
    I have a table of 6 columns which I want to populate with data from another table having 5 columns and a generated column.
    (1) I used the execute sql script to insert data in the first column.
    (2) I used the Input Table tool to get the rows of the 5 columns.
    (3)Both items in (1) and (2) are linked to Append stream in order to maintain the right sequence { data from (1) must be inserted in the first column}
    (4)The append stream tool is then linked to an Output Table tool

    When I run it I get an error. Can someone advice me on how best to achieve this.

  2. #2

    Default Problem in populating a table

    can you attach the transformation?
    Andrea Alberici
    --
    Studio Synthesis Consulenti per la PMI
    http://www.studiosynthesis.biz
    a.alberici@studiosynthesis.biz

  3. #3

    Default

    Hello Andrea, attached is the transformation diagram.

    Thanks
    Attached Files Attached Files

  4. #4

    Default maybe you can use 'add sequence' step

    hi Ejay!

    if the generated column is a sequence or a constant,
    you can use the 'add sequence' or 'add constant' step directly.

    steps order:

    'table input' -> 'add sequence'/'add constant' -> 'table output'
    Jason chu
    Beijing Aofei BI Consulting.
    Web: http://www.pentahochina.com
    Email: jianjunchu@gmail.com

  5. #5

    Default

    Hello,

    I tried the connection with sequence but it is not working. I also used insert/update and it failed . I do not know if the problem is because I am using different column names at the destination database. I cannot just figure-out why it is not working. The extra column to be added is a sequence.

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

    Default

    Append steps works row based, not column based... so I have no idea what you're trying to do... no error messages ....

    Regards,
    Sven

  7. #7

    Default try this...

    ...try using a sequence as suggested
    Attached Images Attached Images  
    Attached Files Attached Files
    Andrea Alberici
    --
    Studio Synthesis Consulenti per la PMI
    http://www.studiosynthesis.biz
    a.alberici@studiosynthesis.biz

  8. #8

    Default

    Quote Originally Posted by sboden View Post
    Append steps works row based, not column based... so I have no idea what you're trying to do... no error messages ....

    Regards,
    Sven
    Hello Sven,

    You are right on Append steps and thanks for the information. I wanted to add a sequence on a table. My origin table has 5 columns and the destination table has 6 columns. The extra column in the destination table is for the sequence. I tried the tipps I got from the forum but got the following errors:

    2008/05/13 09:14:36 - Spoon - Transformation opened.
    2008/05/13 09:14:36 - Spoon - Launching transformation [DataWarehouse]...
    2008/05/13 09:14:36 - Spoon - Started the transformation execution.
    2008/05/13 09:14:36 - DataWarehouse - Dispatching started for transformation [DataWarehouse]
    2008/05/13 09:14:36 - DataWarehouse - Nr of arguments detected:0
    2008/05/13 09:14:36 - DataWarehouse - This is not a replay transformation
    2008/05/13 09:14:36 - DataWarehouse - This transformation can be replayed with replay date: 2008/05/13 09:14:36
    2008/05/13 09:14:36 - DataWarehouse - Initialising 3 steps...
    2008/05/13 09:14:36 - Table output.0 - Connected to database [Localhost/Datawarehouse] (commit=0)
    2008/05/13 09:14:36 - Add sequence.0 - Starting to run...
    2008/05/13 09:14:36 - Table output.0 - Starting to run...
    2008/05/13 09:14:36 - Supplier Input Table.0 - Starting to run...
    2008/05/13 09:14:36 - Add sequence.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Because of an error, this step can't continue:
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Error performing rollback on connection
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Can't call rollback when autocommit=true
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Unexpected error rolling back the database connection.
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : org.pentaho.di.core.exception.KettleDatabaseException:
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Error performing rollback on connection
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Can't call rollback when autocommit=true
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) :
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.core.database.Database.rollback(Database.java:651)
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.core.database.Database.rollback(Database.java:625)
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:489)
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.java:520)
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Caused by: java.sql.SQLException: Can't call rollback when autocommit=true
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at com.mysql.jdbc.Connection.rollback(Connection.java:4805)
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.core.database.Database.rollback(Database.java:640)
    2008/05/13 09:14:36 - Table output.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : ... 3 more
    2008/05/13 09:14:36 - Table output.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2008/05/13 09:14:36 - Supplier Input Table.0 - Finished reading query, closing connection.
    2008/05/13 09:14:36 - Supplier Input Table.0 - Finished processing (I=386, O=0, R=0, W=384, U=0, E=0)
    2008/05/13 09:14:36 - Spoon - The transformation has finished!!

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

    Default

    Error messages, but no .ktr

    If you just want to add a sequence use "add sequence" step. For the error message you see, apparently an error occurred during output processing and rollback was called (while this is not possible in autocommit mode).

    Regards,
    Sven

  10. #10

    Default

    Hello Sven,

    I used the same diagram which was posted by Aalberici - check the earlier postings. I am really dumbfounded because it is suppose to be an easy transformation.

    Thanks

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

    Default

    The problem is that Andrea Alberici seems to be confused as well.
    Just because he posts this, it doesn't mean the transformation is correct or anything.

    That is why Sven was asking for your transformation.

    Just for the record, the "try this" transformation can not possible work because it is mixing rows with different layout.
    For example, the "Add sequence 2" (sic) step needs to be put in between "Test_Input_Table_2" and "Table Output 3".

    Although I lack the motivation to check, it's reasonable to assume that the "Append" step is also abused in this case.

    Tip: try to give your steps more meaningful names :-)

    Matt

  12. #12
    DEinspanjer Guest

    Default

    Ejay,

    I've attached a simple example transformation that takes a stream with five fields (columns) in it and appends a new column with a simple sequential value. It then re-orders the columns to put the sequence column first and renames the fields a bit.

    Does this example show what you are trying? If not, please try to adjust it to your actual case, add notes to the transformation to document problems or questions, then attach the modified version to this thread so we can all see it. Be sure to include any error logs if they pertain to your problem.
    Attached Files Attached Files

  13. #13

    Default

    Hello All,
    Thanks for your support. It works now. I appreciate your efforts.

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.