Auto Increment primary key

CloverDX Platform
1

Hi,

I am very new to clover and I have been unable to find an answer to the following question. I have searched the documentation and the forums.

I am writing data from an excel file into a mysql database. I have a parent table and several child tables in the database. The parent table is using an auto increment field as the primary key. Each row in the excel file represents a row in the parent table and multiple rows in the child tables. Is it possible to get the value of the auto increment field from the parent table so that I can write the child tables? It is not really going to be possible to uniquely identify the correct record in the parent table from the data in the excel file.

Sorry if there is an obvious answer to this question, but I can not seem to find the answer.

Thank in advance
Marc

2

Hi Marc,

You can use DBOutputTable component for loading data into database. The second output port is used for returned values. You can define returning data by returning statement specified in the query.

Example of MySQL query with returning statement:

INSERT INTO `myUsers` (`firstname`, `lastname`) VALUES (?, ?) RETURNING $inserted_id:=AUTO_GENERATED

Question marks serve as placeholders for Clover field values (from input metadata) and virtual field called “AUTO_GENERATED” is used for returning auto generated id (mapped to the second output port with field named “inserted_id”).

I prepared for you simple example, please see the attached graph (for testing please modify your database connection details).

For more details about returning statement please see the documentation of DataOutputTable component here:
http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/dboutputtable.html

3

Hi Jiri,

Thank you for the very quick response, this does exactly what I was hoping for.

regards
Marc