Graph property in SQL query

martind

• April 26, 2010 00:00

• Answered

Follow

Hi,

I have been trying to reproduce the example about DBInputTable from the wiki (http://wiki.cloveretl.org/doku.php?id=c ... inputtable) programmatically:

I translated:

<Property id="GraphParameter0" name="param1" value="A%"/>
  <Node id="INPUT" type="DB_INPUT_TABLE" dbConnection="NorthwindDB" DataPolicy="Strict" fetchSize="1000">
        <attr name="SQLCode">
            select * from employee_z where last_name = '${param1}'
        </attr>
  </Node>

to:

graph.getGraphProperties().put("param1", "Arthur");
Node nodeParser = new DBInputTable("INPUT", "NorthwindDB",
				"select * from employee_z where last_name = '${param1}'");

However it fails with the following exception:

java.sql.SQLException: Non supported SQL92 token at position: 232: param1
	at org.jetel.connection.jdbc.SQLDataParser.setDataSource(SQLDataParser.java:300)
	at org.jetel.component.DBInputTable.execute(DBInputTable.java:247)
	at org.jetel.graph.Node.run(Node.java:388)
	at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Non supported SQL92 token at position: 232: param1

This works:

select * from employee_z where last_name = 'Arthur'

Is the wiki still current?

• 

  avackova

  • April 27, 2010 07:52

  0

  Comment actions Permalink

  Hello Martin,
  parameters are instantiated, when graph is read from xml, so such direct parameter access doesn't work from java code. To resolve parameters you have to use graph.getGraphProperties().get...Property("param") instead of ${param}
• 

  martind

  • April 27, 2010 14:43

  0

  Comment actions Permalink

  hi avackova,
  
  Thanks for your help.
  
  Does it mean that DBInputTable is not able to resolve parameters in SQL queries and that I should create a query with no parameters before passing it to the DBInputTable?
  
  This is fine in case of Strings but if you want to use Dates and you don't know their format in the database (or you want your query to be portable), this is problematic.
• 

  avackova

  • April 28, 2010 07:06

  0

  Comment actions Permalink

  Hello Martin,
  you can't access parameters with this simple way (${param}), when you build your graph in java. And it doesn't matter if you try to use it in DBOutputTable or anywhere else. Such defined parameters can be used only in xml graph definition. You can write your graph in xml (with parameters) and just run it from java (see cloverETL.examples.rel-2-9-2.zip/cloverETL/examples/javaExamples/testXMLGraph.java)

Please sign in to leave a comment.