Hello camel user community,
I have strange problem with a Microsoft sql server database joined query where
I would like to have some advice.
I have 2 spring dsl steps in a route:
<to uri="sql://select s.SITENAME, l.LOCATIONNAME from VGMSITE s, VGMLOCATION l
where l.SITENO=:#siteno and l.LOCATIONNO=:#locationno and
s.SITENO=l.SITENO?outputType=SelectOne"/>
<to uri="log://sql-output?ShowAll=true&multiline=true"/>
The siteno and locationno are in a header.
Both steps run perfectly good in a firebird database and produce following logs:
[org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy] Prepared
query: select s.SITENAME, l.LOCATIONNAME from VGMSITE s, VGMLOCATION l where
l.SITENO=? and l.LOCATIONNO=? and s.SITENO=l.SITENO
[org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy] Setting
parameter #1 with value: 1
[org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy] Setting
parameter #2 with value: 1
[org.apache.camel.component.sql.SqlProducer] Got result list from query:
org.apache.commons.dbcp.DelegatingResultSet@7270bbb6, outputType=SelectOne
[sql-output] Exchange[
, Id: ID-LPT-030915-001-59524-1466882394560-0-25
, ExchangePattern: InOut
, Properties: {CamelCreatedTimestamp=Sat Jun 25 21:20:18 CEST 2016,
CamelFilterMatched=true,
CamelMessageHistory=[DefaultMessageHistory[routeId=dpwa.solas.vgm.location.route,
node=setHeader16],
DefaultMessageHistory[routeId=dpwa.solas.vgm.location.route, node=log17],
DefaultMessageHistory[routeId=dpwa.solas.vgm.location.route, node=choice7],
DefaultMessageHistory[routeId=dpwa.solas.vgm.location.route, node=to30],
DefaultMessageHistory[routeId=dpwa.solas.vgm.location.route, node=to31]],
CamelToEndpoint=log://sql-output?ShowAll=true&multiline=true}
, Headers: {accept=application/json, text/javascript, */*; q=0.01,
accept-encoding=gzip, deflate, sdch,
accept-language=en-GB,en;q=0.8,en-US;q=0.6,nl;q=0.4,
breadcrumbId=ID-LPT-030915-001-59524-1466882394560-0-23,
cache-control=max-age=0, CamelCreatedTimestamp=Sat Jun 25 21:20:18 CEST 2016,
CamelHttpMethod=GET, CamelHttpPath=, CamelHttpQuery=siteno=1&locationno=1,
CamelHttpServletRequest=org.apache.catalina.connector.RequestFacade@bd60042,
CamelHttpServletResponse=org.apache.catalina.connector.ResponseFacade@44782f90,
CamelHttpUri=/TrisESB-dpwa-dvl-vgm%23%232016.06.14/web/camel/dpwa/vgm/location.ajax,
CamelHttpUrl=http://lpt-030915-001:8080/TrisESB-dpwa-dvl-vgm%23%232016.06.14/web/camel/dpwa/vgm/location.ajax,
CamelServletContextPath=/dpwa/vgm/location.ajax, CamelSqlRowCount=1,
connection=keep-alive, Content-Type=null,
cookie=JSESSIONID=C54F4B1B756A13257236FF39067522EB, host=lpt-030915-001:8080,
locationno=1,
referer=http://lpt-030915-001:8080/TrisESB-dpwa-dvl-vgm%23%232016.06.14/web/vgmweigher.jsp?siteno=1&locationno=1,
siteno=1, user-agent=Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36
(KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36,
x-requested-with=XMLHttpRequest}
, BodyType: org.springframework.util.LinkedCaseInsensitiveMap
, Body: {SITENAME=AGW, LOCATIONNAME=yard}
, Out: null:
]
When I run exactly the same query in a sql driver db (with
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver and
url=jdbc:sqlserver://lpt-030915-001;databaseName=dpwa-solas-vgm;integratedSecurity=true),
I get an error The multi-part identifier "l.SITENO" could not be bound.
The log don't show the "Setting parameter" lines but I don't know why.
The logs for sql server:
[org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy] Prepared
query: select s.SITENAME, l.LOCATIONNAME from VGMSITE s, VGMLOCATION l where
l.SITENO=? and l.LOCATIONNO=? and s.SITENO=l.SITENO
[org.springframework.beans.factory.xml.XmlBeanDefinitionReader] Loading XML
bean definitions from class path resource
[org/springframework/jdbc/support/sql-error-codes.xml]
[org.springframework.jdbc.support.SQLErrorCodesFactory] SQLErrorCodes loaded:
[DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase,
Hana]
[org.apache.camel.processor.DefaultErrorHandler] Failed delivery for
(MessageId: ID-LPT-030915-001-59810-1466882533439-0-16 on ExchangeId:
ID-LPT-030915-001-59810-1466882533439-0-18). Exhausted after delivery attempt:
1 caught: org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
[null]; error code [0]; com.microsoft.sqlserver.jdbc.SQLServerException: The
multi-part identifier "l.SITENO" could not be bound.; nested exception is
com.microsoft.sqlserver.jdbc.SQLServerException:
com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier
"l.SITENO" could not be bound.
When I do a sql server query with only one table, everything works just fine.
Has anyone experience similar problem and found a solution for this (except
using a db view)?
--
Kind regards,
Ronny Aerts<mailto:ronny.ae...@intris.be> - Intris nv - Wapenstilstandlaan 47,
2600 Berchem, Belgium
R&D Integration Architect
Prince II<http://en.wikipedia.org/wiki/PRINCE2> certified -
ITIL<http://en.wikipedia.org/wiki/Information_Technology_Infrastructure_Library>
certified
Tel: +32-3-326.50.75
Intris nv
Wapenstilstandlaan 47
B-2600 Berchem Tel. +32 3 326 50 75
Fax +32 3 326 42 23
www.intris.be<http://www.intris.be/>
[http://www.intris.be/mail/AEO_Sticker_108pxRGB.jpg] <http://www.intris.be>
DISCLAIMER
This is an e-mail from Intris. The information contained in this communication
is intended solely for use by the individual or entity to whom it is addressed.
Use of this communication by others is prohibited. If the e-mail message was
sent to you by mistake, please notify
supp...@intris.be<mailto:supp...@intris.be>, destroy it without reading, using,
copying or disclosing its contents to any other person.
We accept no liability for damage related to data and/or documents which are
communicated by electronic mail.
