DB_OUTPUT_TABLE output port 1- rows but no data

CloverDX Platform
1

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

2

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

3

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

4

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?

5

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

6

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.

7

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.

8

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.