-
1. Re: SQLItemReader and greater/less than character
cfang Feb 9, 2017 12:11 PM (in response to richardmoore)I think replacing it with escape sequence shoudl work. Have you replaced all such occurrences?
-
2. Re: SQLItemReader and greater/less than character
richardmoore Feb 9, 2017 12:18 PM (in response to cfang)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 < 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 < 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
cfang Feb 9, 2017 3:15 PM (in response to richardmoore)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 < and > works fine in batch property value.
-
4. Re: SQLItemReader and greater/less than character
richardmoore Feb 9, 2017 3:48 PM (in response to 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 < Are you able to take the above jsl and get past the error we are getting.
-
5. Re: SQLItemReader and greater/less than character
cfang Feb 9, 2017 4:42 PM (in response to richardmoore)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 <) 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
richardmoore Feb 10, 2017 8:54 AM (in response to cfang)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
richardmoore Feb 10, 2017 9:17 AM (in response to 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.
-
8. Re: SQLItemReader and greater/less than character
cfang Feb 10, 2017 9:43 AM (in response to richardmoore)Did ant already resolved < 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
richardmoore Feb 10, 2017 11:48 AM (in response to cfang)I did some more testing and it is not the ant.jar, stuck again.
-
10. Re: SQLItemReader and greater/less than character
richardmoore Feb 10, 2017 11:54 AM (in response to 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 < to < so the DocumentBuilder is failing.