-
1. Re: JDBC parameters from a file on jdbcItemReader
cfang Aug 31, 2016 1:45 PM (in response to richardmoore)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.
-
2. Re: JDBC parameters from a file on jdbcItemReader
richardmoore Aug 31, 2016 3:47 PM (in response to 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?
-
3. Re: JDBC parameters from a file on jdbcItemReader
cfang Aug 31, 2016 4:44 PM (in response to richardmoore)it should be "jobProperties":
#{jobProperties['xxx']}
-
4. Re: JDBC parameters from a file on jdbcItemReader
cfang Aug 31, 2016 4:48 PM (in response to cfang)1 of 1 people found this helpfulthere 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".
-
5. Re: JDBC parameters from a file on jdbcItemReader
richardmoore Sep 1, 2016 10:14 AM (in response to cfang)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
-
6. Re: JDBC parameters from a file on jdbcItemReader
cfang Sep 1, 2016 10:39 AM (in response to richardmoore)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().
-
7. Re: JDBC parameters from a file on jdbcItemReader
richardmoore Sep 1, 2016 10:47 AM (in response to cfang)That is what I thought might be happening and the way I would need to do it, just didn't want to just assume.
-
8. Re: JDBC parameters from a file on jdbcItemReader
cfang Sep 1, 2016 10:51 AM (in response to richardmoore)1 of 1 people found this helpfulactually 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.