You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by imranrazakhan <im...@gmail.com> on 2016/11/20 11:50:18 UTC
Bug in camel-sql IN Clause - 2.18.0
Hi,
One observation regarding camel-sql(2.18.0) IN query , if we have one
dynamic IN it works fine but if we have two dynamic IN it throws error, i
replaced msisdn with static value and it works fine,
select * from subscriber s
where status NOT IN(7,8)
AND s.msisdn IN( :#in:msisdns )
AND s.campaign_id IN( :#in:campId )
Suspected Issue:
I enabled trace and verify resulted Query and found that campaign_id should
have 3 placeholder but it has two like msisdn, which mean prepareQuery
method of DefaultSqlPrepareStatementStrategy.java is setting placeholder
like msisdn
TRACE DefaultSqlPrepareStatementStrategy:88 - Prepared query: select * from
subscriber s
where status NOT IN(7,8)
AND s.msisdn IN( ?,? )
AND s.campaign_id IN( ?,? )
Stacktrace
---------------------------------------------------------------------------------------------------------------------------------------
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException:
ORA-00942: table or view does not exist
at
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at
org.apache.camel.component.sql.SqlProducer.process(SqlProducer.java:116)
--
View this message in context: http://camel.465427.n5.nabble.com/Bug-in-camel-sql-IN-Clause-2-18-0-tp5790243.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: Bug in camel-sql IN Clause - 2.18.0
Posted by imranrazakhan <im...@gmail.com>.
Hi,
I simplify and modify my code its working but i have to use toD, i am using
following oracle driver without toD its not working
<groupId>com.oracle</groupId>
<artifactId>ojdbc7</artifactId>
<version>12.1.0.2</version>
.setHeader("msisdns", simple("${property.cIds.split(',')}",ArrayList.class)
)
.setHeader("campId" ,
simple("${property.cIds.split(',')}",ArrayList.class) )
.toD("sql:select * from ssint where status=1 "+
"AND p_id IN( :#in:msisdns ) " +
"AND c_id IN( :#in:campId ) ?dataSource=#oracleDS¶metersCount=4")
--
View this message in context: http://camel.465427.n5.nabble.com/Bug-in-camel-sql-IN-Clause-2-18-0-tp5790243p5794151.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: Bug in camel-sql IN Clause - 2.18.0
Posted by imranrazakhan <im...@gmail.com>.
Hi,
One difference is
i m setting headers like below
msisdns = new ArrayList<String>();
msisdns.add( newBody.get(0).get(0) );
newExchange.getIn().setHeader("msisdns", msisdns);
newExchange.getIn().setHeader("campId", new ArrayList<String>(
Arrays.asList( prop.getProperty( newBody.get(0).get(1) ).split(","))));
in example you create MAP and then set that accordingly, does this make any
difference?
Map<String, Object> headers = new HashMap<>();
headers.put("names", new String[]{"Camel", "AMQ"});
headers.put("licenses", new String[]{"ASF", "XXX", "YYY"});
--
View this message in context: http://camel.465427.n5.nabble.com/Bug-in-camel-sql-IN-Clause-2-18-0-tp5790243p5794148.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: Bug in camel-sql IN Clause - 2.18.0
Posted by Tomohisa Igarashi <tm...@gmail.com>.
Can you check testcases added here and see what is different from your case?
https://github.com/apache/camel/commit/4cc5bc065985fee3f0fb4b5e5b2ce7c21a852e11
On 02/20/2017 08:48 PM, imranrazakhan wrote:
> HI,
>
> it look like this issue still not resolved, i tested with both 2.18.1 and
> 2.18.2
>
> My Router:
>
> from("file://D:/test?fileName=abc.txt&noop=true")
> .split( body().tokenize( System.getProperty("line.separator") )
> ).streaming()
> .unmarshal().csv()
> .aggregate( simple("${body[0][1]}") , new OfferAggregationStrategy() )
> .completionSize(1000)
> .completionTimeout(100L)
> .to("sql:select * from ssint " +
> "where status=1 "+
> "AND p_id IN( :#in:msisdns ) " +
> "AND c_id IN( :#in:campId ) ")
> .process(new TransformResultSet())
>
> My Aggregator Code:
>
>
> public Exchange aggregate(Exchange oldExchange, Exchange newExchange) {
>
>
> Message newIn = newExchange.getIn();
> List<List<String>> newBody = (List<List<String>>)
> newIn.getBody();
>
>
> ArrayList msisdns = null;
>
> if (oldExchange == null) {
> msisdns = new ArrayList<String>();
> msisdns.add( newBody.get(0).get(0) );
> System.out.println( "new"+newBody.get(0).get(0) );
> newIn.setHeader("msisdns", msisdns);
> newIn.setHeader("campId", new ArrayList<String>( Arrays.asList(
> prop.getProperty( newBody.get(0).get(1) ).split(","))));
>
> return newExchange;
>
> } else {
>
> Message OldIn = oldExchange.getIn();
> msisdns = (ArrayList<String>)OldIn.getHeader("msisdns");
> msisdns.add( newBody.get(0).get(0) );
> OldIn.setHeader("msisdns", msisdns);
> System.out.println( "old"+newBody.get(0).get(0) );
> //OldIn.setHeader("campId", prop.getProperty(
> newBody.get(0).get(1) ).split(","));
> OldIn.setHeader("campId", new ArrayList<String>(
> Arrays.asList( prop.getProperty( newBody.get(0).get(1) ).split(","))));
> return oldExchange;
> }
>
>
> }
>
>
> Stacktrace
> ---------------------------------------------------------------------------------------------------------------------------------------
> org.springframework.jdbc.UncategorizedSQLException:
> PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
> [null]; error code [0]; Number of parameters mismatch. Expected: 2, was: 3;
> nested exception is java.sql.SQLException: Number of parameters mismatch.
> Expected: 2, was: 3
> at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
> at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
> at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
> at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
> at org.apache.camel.component.sql.SqlProducer.process(SqlProducer.java:116)
> at
> org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61)
> at
> org.apache.camel.processor.SendDynamicProcessor$1.doInAsyncProducer(SendDynamicProcessor.java:124)
> at
> org.apache.camel.impl.ProducerCache.doInAsyncProducer(ProducerCache.java:436)
> at
> org.apache.camel.processor.SendDynamicProcessor.process(SendDynamicProcessor.java:119)
> at
> org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
> at
> org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
>
>
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/Bug-in-camel-sql-IN-Clause-2-18-0-tp5790243p5794135.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
>
Re: Bug in camel-sql IN Clause - 2.18.0
Posted by imranrazakhan <im...@gmail.com>.
HI,
it look like this issue still not resolved, i tested with both 2.18.1 and
2.18.2
My Router:
from("file://D:/test?fileName=abc.txt&noop=true")
.split( body().tokenize( System.getProperty("line.separator") )
).streaming()
.unmarshal().csv()
.aggregate( simple("${body[0][1]}") , new OfferAggregationStrategy() )
.completionSize(1000)
.completionTimeout(100L)
.to("sql:select * from ssint " +
"where status=1 "+
"AND p_id IN( :#in:msisdns ) " +
"AND c_id IN( :#in:campId ) ")
.process(new TransformResultSet())
My Aggregator Code:
public Exchange aggregate(Exchange oldExchange, Exchange newExchange) {
Message newIn = newExchange.getIn();
List<List<String>> newBody = (List<List<String>>)
newIn.getBody();
ArrayList msisdns = null;
if (oldExchange == null) {
msisdns = new ArrayList<String>();
msisdns.add( newBody.get(0).get(0) );
System.out.println( "new"+newBody.get(0).get(0) );
newIn.setHeader("msisdns", msisdns);
newIn.setHeader("campId", new ArrayList<String>( Arrays.asList(
prop.getProperty( newBody.get(0).get(1) ).split(","))));
return newExchange;
} else {
Message OldIn = oldExchange.getIn();
msisdns = (ArrayList<String>)OldIn.getHeader("msisdns");
msisdns.add( newBody.get(0).get(0) );
OldIn.setHeader("msisdns", msisdns);
System.out.println( "old"+newBody.get(0).get(0) );
//OldIn.setHeader("campId", prop.getProperty(
newBody.get(0).get(1) ).split(","));
OldIn.setHeader("campId", new ArrayList<String>(
Arrays.asList( prop.getProperty( newBody.get(0).get(1) ).split(","))));
return oldExchange;
}
}
Stacktrace
---------------------------------------------------------------------------------------------------------------------------------------
org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
[null]; error code [0]; Number of parameters mismatch. Expected: 2, was: 3;
nested exception is java.sql.SQLException: Number of parameters mismatch.
Expected: 2, was: 3
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.apache.camel.component.sql.SqlProducer.process(SqlProducer.java:116)
at
org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61)
at
org.apache.camel.processor.SendDynamicProcessor$1.doInAsyncProducer(SendDynamicProcessor.java:124)
at
org.apache.camel.impl.ProducerCache.doInAsyncProducer(ProducerCache.java:436)
at
org.apache.camel.processor.SendDynamicProcessor.process(SendDynamicProcessor.java:119)
at
org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
at
org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:542)
--
View this message in context: http://camel.465427.n5.nabble.com/Bug-in-camel-sql-IN-Clause-2-18-0-tp5790243p5794135.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: Bug in camel-sql IN Clause - 2.18.0
Posted by Tomohisa Igarashi <tm...@gmail.com>.
Hi,
Verified and filed a JIRA.
https://issues.apache.org/jira/browse/CAMEL-10499
Thanks,
Tomo
On 11/20/2016 08:50 PM, imranrazakhan wrote:
> Hi,
>
> One observation regarding camel-sql(2.18.0) IN query , if we have one
> dynamic IN it works fine but if we have two dynamic IN it throws error, i
> replaced msisdn with static value and it works fine,
>
> select * from subscriber s
> where status NOT IN(7,8)
> AND s.msisdn IN( :#in:msisdns )
> AND s.campaign_id IN( :#in:campId )
>
> Suspected Issue:
>
> I enabled trace and verify resulted Query and found that campaign_id should
> have 3 placeholder but it has two like msisdn, which mean prepareQuery
> method of DefaultSqlPrepareStatementStrategy.java is setting placeholder
> like msisdn
>
> TRACE DefaultSqlPrepareStatementStrategy:88 - Prepared query: select * from
> subscriber s
> where status NOT IN(7,8)
> AND s.msisdn IN( ?,? )
> AND s.campaign_id IN( ?,? )
>
>
> Stacktrace
> ---------------------------------------------------------------------------------------------------------------------------------------
> org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
> bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException:
> ORA-00942: table or view does not exist
>
> at
> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
> at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
> at
> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
> at
> org.apache.camel.component.sql.SqlProducer.process(SqlProducer.java:116)
>
>
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/Bug-in-camel-sql-IN-Clause-2-18-0-tp5790243.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
>