Re: camel sql joined query with parameters won't run on sql server
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 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:
>
>
>
> 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.
>
>
Re: camel sql joined query with parameters won't run on sql server
This seems to be a Microsoft sql server database problem. Have you tried to run this query directly against your database. If you get the same error, you should solve this first. I know that Microsoft sql server support the ansi join syntax: eg: FROM VGMSITE s JOIN VGMLOCATION l ON s.SITENO = l.SITENO -- View this message in context: http://camel.465427.n5.nabble.com/camel-sql-joined-query-with-parameters-won-t-run-on-sql-server-tp5784402p5784410.html Sent from the Camel - Users mailing list archive at Nabble.com.
RE: camel sql joined query with parameters won't run on sql server
Hello,
The parametersCount works great.
--
Kind regards,
Ronny Aerts - Intris nv - Wapenstilstandlaan 47, 2600 Berchem, Belgium
R&D Integration Architect
Prince II certified - ITIL certified
Tel: +32-3-326.50.75
-Original Message-
From: John Taylor [mailto:jtt77...@gmail.com]
Sent: zaterdag 25 juni 2016 23:04
To: users@camel.apache.org
Subject: Re: camel sql joined query with parameters won't run on sql server
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 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:
>
>
>
> 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.loca
> tion.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@bd
> 60042,
> 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.1
> 4/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;integr
> atedSecurity=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]
> [
RE: camel sql joined query with parameters won't run on sql server
I have the same issue with sql server, but it seems to be related to alias +
named param rather than a joined query.
The following works fine:
.setHeader("ExternalRef", simple("1234"))
.to("sql:select event from events where id= :#ExternalRef")
But if I alias the table :
.setHeader("ExternalRef", simple("1234"))
.to("sql:select t.event from events t where t.id= :#ExternalRef")
I get an exception
com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier
"t.id" could not be bound.
If I hard-code it works fine:
.to("sql:select t.event from events t where t.id= '1234' ")
This seems specific to sql server driver, it works when using the jTDS
driver.
--
View this message in context:
http://camel.465427.n5.nabble.com/camel-sql-joined-query-with-parameters-won-t-run-on-sql-server-tp5784402p5785280.html
Sent from the Camel - Users mailing list archive at Nabble.com.
