JDBC parameters from a file on jdbcItemReader

[richardmoore]

Is there a way to read a list of records from a file that will become a list of a "where in" clause on a jdbcItemReader step? Is there a property that can be set from a batchlet that the next step in the job can use in the SQL statement?

[cfang]

no, this is not supported in jdbcItemReader.  You can subclass JdbcItemReader, and override its open method to set your own sql field value from your application state, and the rest of the reader logic stays the same.

[richardmoore]

I'm missing something in this. I have -

<reader ref="com.awginc.batch.system.batchlet.JdbcSqlItemReader">

  <properties>

  <property name="dataSourceLookup" value="db2"/>

  <property name="sql" value="SELECT fclty_cd, fclty_ltr2_cd, fclty_nm, dw_fclty_ltr2_cd FROM db2pdba.aim_fclty where fclty_ltr2_cd in (#{stepProperties['faclCdList']})" />

  <property name="beanType" value="java.util.Map" />

  </properties>

  </reader>

In my extended JdbcSqlItemReader class I have overridden the open class and display the sql field and it reads -

SELECT fclty_cd, fclty_ltr2_cd, fclty_nm, dw_fclty_ltr2_cd FROM db2pdba.aim_fclty where fclty_ltr2_cd in ()

How do I insert my file contents which is a string into the SQL statement since my variable is no longer there for me to replace with my values?

[cfang]

it should be "jobProperties":

#{jobProperties['xxx']}

[cfang]

there is no "stepProperties" substitution in job xml.  "jobProperties" is a general term, not just for job-level batch properties.

Quote from the spec:

8.8.1.2 jobProperties Substitution Operator

The jobProperties substitution operator resolves to the value of the job property with the specified target name. This property is found by recursively searching from the innermost containment scope (this includes earlier properties within the current scope) to the outermost scope until a property with the specified target name is found.

E.g.

The batch runtime would attempt resolution of the jobProperties operator specification in each of the two following reader property definitions by first searching for earlier property definitions within the reader properties collection, then the step properties collection (there are none in this example), then the job properties collection (if any). The search stops at the first occurrence of the specified target name.

<job id="job1">

<properties>

<property name="filestem" value="postings"/>

<property name="outputlog" value="jobmessages"/>

</properties>

<step id="step1"> <properties/>

<chunk>

<reader ref=”MyReader”>

<properties>

<property name="infile.name" value="#{jobProperties['filestem']}.txt"/>

<property name="outputlog" value="readermessages"/>

<property name="outfile.name” value="#{jobProperties['outputlog']}.txt"/>

</properties>

</reader>

</chunk>

</step>

</job>

The resolved value for reader property "infile.name" would be "postings.txt".

The resolved value for reader property "outfile.name" would be "readermessages.txt".

[richardmoore]

I tried the following and it is not substituting for the jobProperty. Will this work?

public class JdbcSqlItemReader extends JdbcItemReader {

  @Inject

  JobContext jobContext;

  private Properties jobProperties;

  @Override

    public void open(final Serializable checkpoint) throws Exception {

  jobProperties = jobContext.getProperties();

  Path parmsPath = Paths.get(System.getProperty("APP_DATA") + File.separator + "sqlParmsFromFile.txt");

  String parms = StringUtils.join(Files.readAllLines(parmsPath), "','");

  jobProperties.setProperty("faclCdList", "'" + parms + "'");

  Path sqlPath = Paths.get(System.getProperty("APP_DATA") + File.separator + "MyBatchletTest.sql");

  sql = StringUtils.join(Files.readAllLines(sqlPath), " ");

  System.out.println(sql);

/* Displays -

SELECT fclty_cd, fclty_ltr2_cd, fclty_nm, dw_fclty_ltr2_cd  FROM db2pdba.aim_fclty  where dw_fclty_ltr2_cd in (#{jobProperties['faclCdList']})

*/

  System.out.println(jobProperties);

/* Displays -

{faclCdList='KC','SP','NO'}

*/

	}

}

Results in -

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1064][10658][3.63.123] Unrecognized keyword in escape clause: jobProperties['faclCdList']. ERRORCODE=-4463, SQLSTATE=42601

[cfang]

in your reader's open method, all batch properties resolution and injection by JBeret already occurred, so you can't expect setting a value to jobProperties (which are ready-only) for it to be used in expressions in job.xml.

What you can do is, in your open method, overwrite the sql value with your own value.  You just need to construct your own sql value with whatever text replacing/appending you need, and assign it to sql field. Make sure at the end call super.open().

[richardmoore]

That is what I thought might be happening and the way I would need to do it, just didn't want to just assume.

[cfang]

actually it's better to override the reader's init method, though overriding open() should also work, but init method is designed for this purpose:

@Override

protected void init() throws Exception {

     sql = xxx;

     super.init();

}

you don't need the jobContext injection either.