Hi Ronny,
I had the same kind of problem recently and it turned out to be
something to do with the driver not correctly returning the number of
parameters. I ended up needing to use the parametersCount option. It is
described at http://camel.apache.org/sql-component.html
Regards,
-John
Ronny Aerts <ronny.ae...@intris.be> writes:
> 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.
