SQLItemReader and greater/less than character

[richardmoore]

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.

[cfang]

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

[richardmoore]

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>

[cfang]

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.

[richardmoore]

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.

[cfang]

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. 

[richardmoore]

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

[richardmoore]

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.

[cfang]

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.

[richardmoore]

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

[richardmoore]

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.