New component upsert

CloverDX Platform
1

Hi,

I am using cloverETL gui.
I have created a component as mentioned in http://www.cloveretl.com/_upload/clover … /ch01.html. This link provides all the required information on crating a component.

I want to create a component that upserts (insert/update) record. I have already created a java program in line with DBOutputTable. I have created plugin.xml too. While creating customcomponents.xml, I want the dbconnection to read from the connection already created (already externalized connection). How do I create a drop down to show the externalized connections? I also want to create a functionality which allows us to select tablename and columns.

Thanks in advance.
Nag

2

Hello,
you can also add your component to CloverGUI (see Chapter 2. Integrating a Custom Component in CloverGUI) and then use it as all other components (d&d it from palette to your graph, connect with edges etc).
By the way: do you need something what DBOutputTable can’t do? DBOutputTable has all functionality your write in your post about.

3

Thanks for the reply.
I am following the chapter2 to build and integrate my component.
I am trying to execute the following query

merge into t1 using t2 on (t1.first_name = t2.first_name and t1.last_name=t2.last_name)
when matched then
update set salary = t2.salary
when not matched then
insert(first_name,last_name, salary) values (t2.first_name,t2.last_name, t2.salary);

I had put this in the query property. It gives me a error. (Description Resource Path Location Type
ENGINE: Error during graph initialization (No enum const class org.jetel.connection.jdbc.SQLCloverStatement$QueryType.MERGE). importdata.grf testinsert/graph 1239999735546 Clover Problems
).

This is the reason, I went ahead to develop a custom component.
I have created a customcomponents.xml as described in tht tutorial but it doesn’t say how we can get a dropdown with all the connections for the graph. Can you suggest me how to achieve this?
Thanks
Nag

4

Hello,
as I understand you want to execute a “static” sql query, when there is no input to your component, don’t you? In such case you should use DBExecute component, while DBOutputTable can handle insert, update or delete queries only and expected clover records on input.
customcomponents.xml file defines properties needed for creation of your component (used by fromXML(TransformationGraph graph, Element xmlElement) method of your component). You can inform CloverGUI, which editors to use also. Eg. if your component requires connection parameter, the proper line in customcomponents.xml should look like follow:

      <property category="basic" displayName="DB connection" modifiable="true" name="dbConnection" nullable="true" required="true">
        <singleType name="connection"/>
      </property>

It means:

  • category=“basic” - property is displayed in basic properties group

  • displayName=“DB connection” - name visible when editing component

  • modifiable=“true” - property value can be changed

  • name=“dbConnection” - name used by cloveretl.engine while reading from xml - the same as the property name in your component java code

  • nullable=“true”

  • required=“true”

  • singleType name=“connection” - uses “connection” editor - combo with db connections available in graph

For choosing table from database you can use singleType name=“table”