How can I access Excel Sheetname in Reformat node?

CloverDX Platform
1

My excel file is defined on a XLS Data Reader which has an output EDGE connection to a Reformat Node. The Reformat node has output EDGE connection to a DB Output Table.

  1. in Reformat Node, I would like to access the EXCEL Sheetname and insert the value to the DB table row.

  2. Also, I would like to be able to pass a custom parameter value to be inserted to DB table row.

I am new to this product ( 2 days of reading), and would appreciate any help from this forum. thanks.

2

Hello,
1. it is impossible. You can only send it by another port.
2. Usage of parameters is described on Property and property file. You can do something like that: insert into my_table values ($inputField1, ${my_param});

3

Hi Agata:

  1. Thanks for the property information. but I don’t see
    how to access the Excel Sheetname or the INPUT RECORD name.
    Although, I can see examples of accessing the input record fields ${in.record_ordinal_num. fieldname field_name} http://wiki.clovergui.net/doku.php?id=t … preprocess

  2. I fixed my reformat Node and mapped my input fields from excel columns
    to my DB table columns. However, when running the graph. I am getting
    java.sql.SQLException: Missing IN or OUT parameter at index:: 1.
    can you give me a clue or what parameters were set by the Prepared Statement? Thanks again.

org.jetel.exception.JetelException: Maximum # of errors exceeded when inserting record. Exeption thrown by: INSERT INTO BEN_BULKUPLOADDATA (SESSIONID, UPLOADTYPESTR, FILEID, UPLOADROWNUM, COLUMN01STR )
VALUES ($?, $?, $?, $?, $?). Message: Missing IN or OUT parameter at index:: 1 caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at org.jetel.component.DBOutputTable.runInNormalMode(DBOutputTable.java:667)
at org.jetel.component.DBOutputTable.execute(DBOutputTable.java:588)
at org.jetel.graph.Node.run(Node.java:371)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1681)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
at org.jetel.connection.SQLCloverStatement.executeUpdate(SQLCloverStatement.java:244)
at org.jetel.component.DBOutputTable.runInNormalMode(DBOutputTable.java:630)
… 5 more

4

Hi,
I don’t exactly understand what you want to do. If you want insert data from xls file to database it is enough to have XLSReader and DBOutputTable:

<?xml version="1.0" encoding="UTF-8"?>
<Graph author="avackova" created="Fri Mar 21 16:56:02 CET 2008" guiVersion="1.9" id="1206534077725" licenseType="Evaluation license." modified="Fri May 16 09:35:12 CEST 2008" modifiedBy="avackova" name="Test" revision="1.36">
<Global>
<Metadata id="Metadata2">
<Record fieldDelimiter=";" name="employee" recordDelimiter="\n" recordSize="-1" type="delimited">
<Field format="#0.###" name="id" nullable="true" shift="0" skipFirstLine="false" type="numeric"/>
<Field name="full_name" nullable="true" shift="0" skipFirstLine="false" type="string"/>
<Field name="first_name" nullable="true" shift="0" skipFirstLine="false" type="string"/>
<Field format="dd MM yyyy hh:mm:ss" name="hire_date" nullable="true" shift="0" skipFirstLine="false" type="date"/>
<Field format="dd MM yyyy hh:mm:ss" name="end_date" nullable="true" shift="0" skipFirstLine="false" type="date"/>
</Record>
</Metadata>
<Connection dbConfig="koule_postgre.cfg" id="Connection1" type="JDBC"/>
<Property id="GraphParameter0" name="sheet" value="team1"/>
</Global>
<Phase number="0">
<Node dbConnection="Connection1" enabled="enabled" guiHeight="0" guiName="DB Output Table" guiWidth="0" guiX="339" guiY="71" id="DB_OUTPUT_TABLE0" sqlQuery="INSERT INTO ${sheet} (employee_id, full_name, first_name, hire_date, end_date)
VALUES ($id, $full_name, $first_name, $last_name, $hire_date, $end_date);" type="DB_OUTPUT_TABLE"/>
<Node enabled="enabled" fileURL="employess.txt" guiHeight="0" guiName="XLS Data Reader" guiWidth="0" guiX="73" guiY="73" id="XLS_READER0" sheetName="${sheet}" startRow="1" type="XLS_READER"/>
<Edge fromNode="XLS_READER0:0" guiBendpoints="" id="Edge2" inPort="Port 0 (in)" metadata="Metadata2" outPort="Port 0 (out)" toNode="DB_OUTPUT_TABLE0:0"/>
</Phase>
</Graph>

Unfortunately you can’t access sheet name dynamically; you have to change value of sheet parameter in each graph execution.