8 Replies Latest reply on Sep 1, 2016 10:51 AM by Cheng Fang

    JDBC parameters from a file on jdbcItemReader

    Richard Moore Master

      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?

        • 1. Re: JDBC parameters from a file on jdbcItemReader
          Cheng Fang Master

          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
            Richard Moore Master

            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
              Cheng Fang Master

              it should be "jobProperties":

               

              #{jobProperties['xxx']}

              • 4. Re: JDBC parameters from a file on jdbcItemReader
                Cheng Fang Master

                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".

                1 of 1 people found this helpful
                • 5. Re: JDBC parameters from a file on jdbcItemReader
                  Richard Moore Master

                  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
                    Cheng Fang Master

                    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
                      Richard Moore Master

                      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
                        Cheng Fang Master

                        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.

                        1 of 1 people found this helpful