DB_OUTPUT_TABLE output port 1- rows but no data

nelsonwd

• July 15, 2011 00:00

• Answered

Follow

Hi,
I'm new to Clover. I'm trying to use the DB_OUTPUT_TABLE component to insert a row and then pass the auto_generated id to the next component. As a prototype I have a file with 2 records (one field per record) that is read into the input port of the DB_OUTPUT_TABLE component. The graph executes without error, the 2 records are inserted into the DB, and output port 1 does produce two rows, but the row fields are empty. I'm using CloverETL 3.1.0 and a Sybase connection (dbURL=jdbc\:jtds\:sybase\://myURL\:4100/tairtest) My meta data and sql are:
Input0

<Record name="recordName1" recordDelimiter="\n" type="delimited">
<Field name="tair_object_type_id" type="long"/>
</Record>

SQL

INSERT INTO TairObject(tair_object_type_id) 
VALUES($tair_object_type_id) 
returning $tair_object_id := auto_generated, 
               $tair_object_type_id := $tair_object_type_id

Output1

<Record name="testOut" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\n" type="delimited">
<Field delimiter="," name="tair_object_type_id" type="long"/>
<Field name="tair_object_id" type="long"/>
</Record>

Thanks,
Bill

• 

  avackova

  • July 18, 2011 08:50

  0

  Comment actions Permalink

  Hello Bill,
  it seems, that Sybase connection doesn't handle properly auto-generated keys. I've created an issue in our bug tracking system (https://bug.javlin.eu/browse/CL-1853). You can browse it with guest/guest credentials.
  As a workaround use MSSQL specific:Connection1.png
  and set transactionIsolation=2 (READ_COMMITED) :
  Connection2.png

  • Connection1.png
  • Connection2.png
• 

  nelsonwd

  • July 21, 2011 18:20

  0

  Comment actions Permalink

  Thank you very much for the information.
  Sorry for the slow reply, I was on vacation.
  Bill
• 

  rawdrix

  • October 25, 2011 02:10

  0

  Comment actions Permalink

  I have the exact same issue with the MySQL driver. Data inserts to the table work fine and PORT 1 data is outputed but the fields are not populated (just blank rows)
  
  I've tried the JDBC driver suggestions as well. Any update with this issue?
• 

  rawdrix

  • October 25, 2011 03:10

  0

  Comment actions Permalink

  In addition to my last post, the issue seems to be with the MYSQL driver. The same graph works with the Derby Driver.
• 

  avackova

  • October 25, 2011 07:56

  0

  Comment actions Permalink

  Hello,
  I've tried it with MySql and the keys are returned properly. Please check if you have column defined as AUTO_INCREMENT in your table.
• 

  rawdrix

  • October 25, 2011 11:36

  0

  Comment actions Permalink

  Hi agata
  
  Im not trying to return any keys, just the values from my input records through Port 1. Moreover, this only happens using the MySQL driver and with an INSERT statement. Update statements work as expected and I can return my input values. Also the same insert statement works as expected with the Derby driver and data is passed through port1. I'm using commercial version 3.1.2.
• 

  avackova

  • October 25, 2011 12:50

  0

  Comment actions Permalink

  Hello,
  unfortunately it is not possible (I've found an issue in our bug tracking system for it - https://bug.javlin.eu/browse/CL-1200). If you have unique numeric field in the table you can sign it as AUTO_INCREMENT, than it will work even if you put there unique values explicitly. Another workaround is to duplicate the data by SimpleCopy before the DBOutputTable and join them with the DBOutputTable's error port.

Please sign in to leave a comment.