camel sql joined query with parameters won't run on sql server

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

camel sql joined query with parameters won't run on sql server

Ronny Aerts
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&amp;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:[hidden email]> - 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 [hidden email]<mailto:[hidden email]>, 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.
Reply | Threaded
Open this post in threaded view
|

Re: camel sql joined query with parameters won't run on sql server

arno noordover
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
Reply | Threaded
Open this post in threaded view
|

RE: camel sql joined query with parameters won't run on sql server

Ronny Aerts
In reply to this post by Ronny Aerts
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:[hidden email]]
Sent: zaterdag 25 juni 2016 23:04
To: [hidden email]
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 <[hidden email]> 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&amp;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.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]
> [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:[hidden email]> - 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_Infrastructur
> e_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 [hidden email]<mailto:[hidden email]>, 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.


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 [hidden email]<mailto:[hidden email]>, 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.
Reply | Threaded
Open this post in threaded view
|

RE: camel sql joined query with parameters won't run on sql server

Karts
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.