10 Replies Latest reply on Feb 10, 2017 11:54 AM by Richard Moore

    SQLItemReader and greater/less than character

    Richard Moore Master

      How do I code the sql in the jsl when conditions need to have the greater/less than character?

       

      I am getting the following error when using < in the sql -

      [Fatal Error] :24:1990: The value of attribute "value" associated with an element type "property" must not contain the '<' character.

        • 1. Re: SQLItemReader and greater/less than character
          Cheng Fang Master

          I think replacing it with escape sequence shoudl work.  Have you replaced all such occurrences?

          • 2. Re: SQLItemReader and greater/less than character
            Richard Moore Master

            We think we have. Here is what we have -

             

            <?xml version="1.0" encoding="UTF-8"?>

             

             

            <!DOCTYPE job [

              <!ENTITY job-listeners SYSTEM "entities/awg-job-listeners.xml">

              <!ENTITY step-listeners SYSTEM "entities/awg-step-listeners.xml">

            ]>

             

             

            <job id="RPE_Daily_AIMAPVNDRpt1" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee" schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/jobXML_1_0.xsd">

             

             

              <!--

             

             

              Job Name: Retail Pricing Execution - SPM Store Override

              Description:

              Notes: File size of this job will vary based on how many stores are provided via the command line argument.

             

             

              C H A N G E L O G

             

             

              ServiceNow# Changed By Changed On Description of Changes

              ================= ===================== ========== =================================================

              PRJ0011375 Matthew Parmeter 02/03/2017 Initial creation

              -->

             

             

            &job-listeners;

             

             

              <properties>

              <property name="processedDate" value="#{systemProperties['fileDate']}?:#{jobParameters['yyyyMMdd_HHmmss']}"/>

              </properties>

             

              <step id="gatherData">

             

             

              &step-listeners;

             

              <chunk item-count="3000">

              <reader ref="jdbcItemReader">

              <properties>

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

              <property name="sql" value="

              SELECT   Distinct PVEND.VENDOR_ID,

                      PVEND.NAME1,

                      VADD.ADDRESS1,

                      VADD.ADDRESS2,

                      VADD.CITY,

                      VADD.STATE,

                      SUBSTR(VADD.POSTAL,1,5) AS AP_ZIP_CD,

                      SUBSTR(VADD.POSTAL,7,4) AS AP_ZIP4_CD,

                      VPHN1.PHONE AS AP_PHN_NB,

                      VPHN2.PHONE AS AP_FAX_NB,

                      PCUST.NAME1,

                      CADD.ADDRESS1,

                      CADD.ADDRESS2,

                      CADD.CITY,

                      CADD.STATE,

                      SUBSTR(CADD.POSTAL,1,5) AS AR_ZIP_CD,

                      SUBSTR(CADD.POSTAL,7,4) AS AR_ZIP4_CD

              FROM     DBO.SOR_PS_VENDOR PVEND 

                      INNER JOIN DBO.SOR_PS_CUSTOMER PCUST ON PCUST.CUST_ID = PVEND.CUST_ID AND PCUST.SETID = 'VENDR' AND PVEND.SETID = 'SHARE'

                      INNER JOIN (SELECT C1.ADDRESS1, C1.ADDRESS2, C1.CITY, C1.STATE, C1.CUST_ID, C1.EFFDT, C1.SETID, C1.ADDRESS_SEQ_NUM, C1.EFF_STATUS, C1.POSTAL FROM DBO.SOR_PS_CUST_ADDRESS C1

                      INNER JOIN (SELECT CUST_ID, MAX(EFFDT) EFFDT FROM DBO.SOR_PS_CUST_ADDRESS GROUP BY CUST_ID) C2 ON C1.CUST_ID = C2.CUST_ID AND C1.EFFDT = C2.EFFDT WHERE C1.SETID = 'VENDR' AND C1.ADDRESS_SEQ_NUM = 1 AND C1.EFF_STATUS = 'A') CADD ON PCUST.CUST_ID = CADD.CUST_ID

                      INNER JOIN (SELECT V1.VENDOR_ID, V1.ADDRESS1, V1.ADDRESS2, V1.CITY, V1.STATE, V1.EFFDT, V1.SETID, V1.ADDRESS_SEQ_NUM, V1.EFF_STATUS, V1.POSTAL FROM DBO.SOR_PS_VENDOR_ADDR V1

                      INNER JOIN (SELECT VENDOR_ID, MAX(EFFDT) EFFDT FROM DBO.SOR_PS_VENDOR_ADDR GROUP BY VENDOR_ID) V2 ON V1.VENDOR_ID = V2.VENDOR_ID AND V1.EFFDT = V2.EFFDT WHERE V1.SETID = 'SHARE' AND V1.ADDRESS_SEQ_NUM = 1 AND V1.EFF_STATUS = 'A') VADD ON PVEND.VENDOR_ID = VADD.VENDOR_ID

                      LEFT OUTER JOIN (Select PHN.VENDOR_ID, PHN.PHONE, PHN.EFFDT FROM DBO.SOR_PS_VNDR_CNTCT_PHN PHN

                      LEFT OUTER JOIN DBO.SOR_PS_VNDR_CNTCT_PHN PHN2 ON PHN.VENDOR_ID = PHN2.VENDOR_ID AND PHN.EFFDT &lt; PHN2.EFFDT WHERE PHN.PHONE_TYPE = 'BUSN' AND PHN.SETID = 'SHARE' AND PHN2.VENDOR_ID IS NULL) VPHN1 ON PVEND.VENDOR_ID = VPHN1.VENDOR_ID

                      LEFT OUTER JOIN (Select PHN.VENDOR_ID, PHN.PHONE, PHN.EFFDT FROM DBO.SOR_PS_VNDR_CNTCT_PHN PHN

                      LEFT OUTER JOIN DBO.SOR_PS_VNDR_CNTCT_PHN PHN2 ON PHN.VENDOR_ID = PHN2.VENDOR_ID AND PHN.EFFDT &lt; PHN2.EFFDT WHERE PHN.PHONE_TYPE = 'FAX' AND PHN.SETID = 'SHARE' AND PHN2.VENDOR_ID IS NULL) VPHN2 ON PVEND.VENDOR_ID = VPHN2.VENDOR_ID

              WHERE    PCUST.CUST_STATUS = 'A'

              AND      PVEND.VENDOR_STATUS = 'A'

              AND  PVEND.VENDOR_CLASS IN ('L', 'C', 'P', 'M', 'I')

              "/>

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

              </properties>

              </reader>

             

             

              <writer ref="jdbcItemWriter">

              <properties>

              <property name="dataSourceDeposit" value="jdbc/db2" />

              <property name="sql" value="TRUNCATE TABLE AWGT1.AIM_AP_VNDR; INSERT INTO AWGT1.AIM_AP_VNDR (AP_VNDR_CD, AP_NM, AP_STRT1_AD, AP_STRT2_AD, AP_CITY_NM, AP_STT_CD, AP_ZIP_CD, AP_ZIP4_CD, AP_PHN_NB, AP_FAX_NB, AR_NM, AR_STRT1_AD, AR_STRT2_AD, AR_CITY_NM, AR_STT_CD, AR_ZIP_CD, AR_ZIP4_CD) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"/>

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

              </properties>

              </writer>

              </chunk>

             

              <end on="COMPLETED" />

              <end on ""/> <!-- Another thing to figure out for now, leaving this as a placeholder. -->

              <fail on="*" exit-status="FAILED"/>

              </step>

             

            </job>

            • 3. Re: SQLItemReader and greater/less than character
              Cheng Fang Master

              Is it possible JBeret picks up another job xml somewhere in your classpath that contains the un-escapted characters?

               

              [Fatal Error] :24:1990: The value of attribute "value" associated with an element type "property" must not contain the '<' character.

               

              Can you check if the line number, column number in the error match your intended job.xml?  look at the line number, 24, which is at the beginning of the file.  Your job.xml pasted above is much longer than that

               

              I tested in my machine, shows using &lt; and &gt; works fine in batch property value.

              • 4. Re: SQLItemReader and greater/less than character
                Richard Moore Master

                I tried to run the above jsl and I know I do not have another copy in my workspace and I get the same error as my team member. If I count down to line 24 of the SQL statement it is the first line containing the &lt;  Are you able to take the above jsl and get past the error we are getting.

                • 5. Re: SQLItemReader and greater/less than character
                  Cheng Fang Master

                  I think 24 is the lien number of the whole file, starting from <xml version=...>.

                   

                  I did copy & paste your sql value into a test job.xml (with &lt;) and the value is correctly resolved and injected.

                   

                  I'm using the xml parser in Java / JDK, and there shouldn't need any xml parser jars in classpath. 

                  • 6. Re: SQLItemReader and greater/less than character
                    Richard Moore Master

                    Here is my exact classpath for my jars -

                     

                    cdi-api-1.2.jar

                    guava-18.0.jar

                    javax.inject-1.jar

                    jberet-core-1.3.0.Beta3.jar

                    jberet-se-1.3.0.Beta3.jar

                    jboss-batch-api_1.0_spec-1.0.0.Final.jar

                    jboss-marshalling-1.4.10.Final.jar

                    jboss-transaction-api_1.2_spec-1.0.0.Final.jar

                    jboss-logging-3.3.0.Final.jar

                    weld-core-2.3.4.Final.jar

                    weld-se-2.3.4.Final.jar

                    wildfly-security-manager-1.1.2.Final.jar

                    beanio-2.1.0.jar

                    bson4jackson-2.4.0.jar

                    commons-codec-1.10.jar

                    commons-compress-1.3.jar

                    commons-io-2.4.jar

                    commons-lang3-3.1.jar

                    de.flapdoodle.embed.mongo-1.46.4.jar

                    de.flapdoodle.embed.process-1.40.1.jar

                    dom4j-1.6.1.jar

                    h2-1.3.173.jar

                    hibernate-jpa-2.1-api-1.0.0.Final.jar

                    infinispan-commons-8.1.2.Final.jar

                    infinispan-core-8.1.2.Final.jar

                    jackson-annotations-2.5.4.jar

                    jackson-core-2.5.4.jar

                    jackson-databind-2.5.4.jar

                    jackson-dataformat-csv-2.5.4.jar

                    jackson-dataformat-xml-2.5.4.jar

                    jackson-module-jaxb-annotations-2.5.4.jar

                    jandex-2.0.2.Final.jar

                    javax.el-api-3.0.0.jar

                    javax.interceptor-api-1.2.jar

                    jberet-support-1.3.0.Beta3.jar

                    jboss-annotations-api_1.2_spec-1.0.0.Final.jar

                    jboss-classfilewriter-1.1.2.Final.jar

                    jboss-interceptors-api_1.2_spec-1.0.0.Final.jar

                    jboss-jms-api_2.0_spec-1.0.0.Final.jar

                    jboss-marshalling-osgi-1.4.10.Final.jar

                    jboss-transaction-api_1.1_spec-1.0.1.Final.jar

                    jgroups-3.6.9.Final.jar

                    jna-4.0.0.jar

                    jna-platform-4.0.0.jar

                    mongo-java-driver-3.0.3.jar

                    mongojack-2.3.0.jar

                    poi-3.12.jar

                    poi-ooxml-3.12.jar

                    poi-ooxml-schemas-3.12.jar

                    stax2-api-4.0.0.jar

                    super-csv-2.3.1.jar

                    validation-api-1.1.0.Final.jar

                    weld-api-2.3.SP2.jar

                    weld-spi-2.3.SP2.jar

                    xmlbeans-2.6.0.jar

                    asm-5.0.4.jar

                    junit-4.12.jar

                    log4j-1.2-api-2.6.1.jar

                    log4j-api-2.6.1.jar

                    log4j-core-2.6.1.jar

                    log4j-jul-2.6.1.jar

                    commons-net-3.4.jar

                    commons-dbutils-1.6.jar

                    commons-csv-1.4.jar

                    commons-vfs2-2.1.jar

                    jsch-0.1.52.jar

                    hamcrest-core-1.3.jar

                    commons-email-1.4.jar

                    commons-dbcp2-2.1.jar

                    commons-pool2-2.4.2.jar

                    antlr-2.7.7.jar

                    api-all-1.0.0-M33.jar

                    api-asn1-api-1.0.0-M33.jar

                    api-asn1-ber-1.0.0-M33.jar

                    api-i18n-1.0.0-M33.jar

                    api-ldap-client-api-1.0.0-M33.jar

                    api-ldap-codec-core-1.0.0-M33.jar

                    api-ldap-extras-aci-1.0.0-M33.jar

                    api-ldap-extras-codec-1.0.0-M33.jar

                    api-ldap-extras-codec-api-1.0.0-M33.jar

                    api-ldap-model-1.0.0-M33.jar

                    api-ldap-schema-converter-1.0.0-M33.jar

                    api-ldap-schema-data-1.0.0-M33.jar

                    api-util-1.0.0-M33.jar

                    commons-collections-3.2.2.jar

                    mina-core-2.0.10.jar

                    org.apache.servicemix.bundles.antlr-2.7.7_5.jar

                    org.apache.servicemix.bundles.dom4j-1.6.1_5.jar

                    org.apache.servicemix.bundles.xpp3-1.1.4c_6.jar

                    xml-apis-2.0.2.jar

                    commons-configuration2-2.1.jar

                    commons-beanutils-1.9.2.jar

                    axis2-kernel-1.6.2.jar

                    axis2-adb-1.6.2.jar

                    activation-1.1.jar

                    axiom-api-1.2.13.jar

                    axiom-impl-1.2.13.jar

                    axis2-transport-http-1.6.2.jar

                    axis2-transport-local-1.6.2.jar

                    commons-codec-1.3.jar

                    commons-httpclient-3.1.jar

                    commons-logging-1.1.1.jar

                    httpcore-4.0.jar

                    mail-1.4.jar

                    neethi-3.0.2.jar

                    wsdl4j-1.6.2.jar

                    XmlSchema-1.4.7.jar

                    db2jcc4.jar

                    org.eclipse.jgit-4.5.0.201609210915-r.jar

                    slf4j-api-1.7.14.jar

                    slf4j-simple-1.7.14.jar

                    mysql-connector-java-5.1.38-bin.jar

                    jboss-logging-annotations-1.2.0.Beta1.jar

                    ant.jar

                    • 7. Re: SQLItemReader and greater/less than character
                      Richard Moore Master

                      The problem jar is the last one - ant.jar

                      Got any suggestions on a way around this issue? I have one job that uses an ant build.xml process.

                      • 8. Re: SQLItemReader and greater/less than character
                        Cheng Fang Master

                        Did ant already resolved &lt; to <, and so the job xml parser just sees the resolved <?

                         

                        You can try pass sql value from the job parameters:

                         

                        <property name="sql" value="#{jobParameters['sql']}"/>

                         

                        or you can even just substitute the problematic part in sql text:

                         

                        <property name="sql" value="select * from xxx where id #{jobParameters['lessThanSign']} 100"/>

                         

                        <property name="sql" value="select * from xxx where id #{systemProperties['lessThanSign']} 100"/>

                         

                        or you can change your sql to use >, instead of <.  But changing such a complex sql seems pretty risky.

                        • 9. Re: SQLItemReader and greater/less than character
                          Richard Moore Master

                          I did some more testing and it is not the ant.jar, stuck again.

                          • 10. Re: SQLItemReader and greater/less than character
                            Richard Moore Master

                            Okay, I figured it out. I am trying to put together the resolved XML to display in my log, at that point it is already translated from &lt; to < so the DocumentBuilder is failing.