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.