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/05/30 20:09:11 UTC
camel-sql - SQL IN Query issue (2.17.1)
Hi,
I am facing following error while using SQL IN clause
from("file://D:/Activity?fileName=BATCH_11.csv&noop=true")
.split( body().tokenize( System.getProperty("line.separator") )
).streaming()
.aggregate( constant(true), new ArrayListAggregationStrategy() )
.completionSize(1000)
.to("sql:select status from subscriber where mobilenumber in
(:#in:mobilenumbers)?dataSource=#oracleDS")
.....
.end();
it gives error
org.apache.camel.RuntimeExchangeException: Cannot find key [mobilenumbers]
in message body or headers to use when setting named parameter in query
I changed my route like below
from("file://D:/Activity?fileName=BATCH_11.csv&noop=true")
.split( body().tokenize( System.getProperty("line.separator") )
).streaming()
.aggregate( constant(true), new ArrayListAggregationStrategy() )
.completionSize(1000)
.setHeader("mobilenumbers", simple("${body}"))
.to("sql:select status from subscriber where mobilenumber in
(:#in:mobilenumbers)?dataSource=#oracleDS")
Now facing following error
org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
[null]; error code [0]; Number of parameters mismatch. Expected: 1, was:
1000; nested exception is java.sql.SQLException: Number of parameters
mismatch. Expected: 1, was: 1000
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
Hi,
1. CSV contents
--> i used camel-csv same result
12345
34567
89073
2. Camel Version
--> 2.17.1
Regards
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783267.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by yogu13 <yo...@gmail.com>.
Hi,
Following information will help!
1. CSV contents
2. Camel Version
Alternatively you can also check if the CSV
<http://camel.apache.org/csv.html> component available can be used in your
case.
Regards,
-Yogesh
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783266.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
HI Claus,
thanks our suggestions worked perfect, i am currently using ojdbc6 version
12.1.0.2, will verify if its fixed in any other driver.
Regards,
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783350.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by Claus Ibsen <cl...@gmail.com>.
Hi
I wonder if its the Oracle JDBC driver issue, that it does not return
correct value for number of parameters in the prepared statement.
There is an option parametersCount you can use to set a fixed value if
the JDBC driver cannot return correct data. See its documentation
http://camel.apache.org/sql-component.html
However that value is fixed, and if you use a dynamic IN list then you
would need to use <toD> to make the uri dynamic. (less good but
possible). But at least maybe try to set that value = 1000 as you use
in your example, and see if that at least work.
On Tue, May 31, 2016 at 6:09 PM, yogu13 <yo...@gmail.com> wrote:
> i see unit tests written for supporting in clause not sure how r they
> passing
>
> regards,
> -Yogesh
>
> On Tuesday 31 May 2016, imranrazakhan [via Camel] <
> ml-node+s465427n5783319h18@n5.nabble.com> wrote:
>
>> Hi Yogesh,
>>
>> it look like there is issue in Using IN queries with dynamic values, as
>> its working for 1 value but not for multiple values, if here is issue in
>> datasource parameter then it should not work for 1 value too.
>>
>>
>> As mentioned earlier is expecting 1 value, below error clearly indicating
>>
>> org.springframework.jdbc.UncategorizedSQLException:
>> PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
>> [null]; error code [0];
>> Number of parameters mismatch. Expected: 1, was: 5; nested exception is
>> java.sql.SQLException: Number of parameters mismatch. Expected: 1, was: 5
>>
>>
>> Regards
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the discussion
>> below:
>>
>> http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783319.html
>> To unsubscribe from camel-sql - SQL IN Query issue (2.17.1), click here
>> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5783264&code=eW9ndTEzQGdtYWlsLmNvbXw1NzgzMjY0fC04ODQzODQxMjM=>
>> .
>> NAML
>> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
>
>
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783321.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
--
Claus Ibsen
-----------------
http://davsclaus.com @davsclaus
Camel in Action 2: https://www.manning.com/ibsen2
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by yogu13 <yo...@gmail.com>.
i see unit tests written for supporting in clause not sure how r they
passing
regards,
-Yogesh
On Tuesday 31 May 2016, imranrazakhan [via Camel] <
ml-node+s465427n5783319h18@n5.nabble.com> wrote:
> Hi Yogesh,
>
> it look like there is issue in Using IN queries with dynamic values, as
> its working for 1 value but not for multiple values, if here is issue in
> datasource parameter then it should not work for 1 value too.
>
>
> As mentioned earlier is expecting 1 value, below error clearly indicating
>
> org.springframework.jdbc.UncategorizedSQLException:
> PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
> [null]; error code [0];
> Number of parameters mismatch. Expected: 1, was: 5; nested exception is
> java.sql.SQLException: Number of parameters mismatch. Expected: 1, was: 5
>
>
> Regards
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783319.html
> To unsubscribe from camel-sql - SQL IN Query issue (2.17.1), click here
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5783264&code=eW9ndTEzQGdtYWlsLmNvbXw1NzgzMjY0fC04ODQzODQxMjM=>
> .
> NAML
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783321.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
Hi Yogesh,
it look like there is issue in Using IN queries with dynamic values, as its
working for 1 value but not for multiple values, if here is issue in
datasource parameter then it should not work for 1 value too.
As mentioned earlier is expecting 1 value, below error clearly indicating
org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
[null]; error code [0];
Number of parameters mismatch. Expected: 1, was: 5; nested exception is
java.sql.SQLException: Number of parameters mismatch. Expected: 1, was: 5
Regards
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783319.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by yogu13 <yo...@gmail.com>.
looks like the context for route and context where datasource is set are
different and hence datasource is not found..
try setting the datasource first and pass the same camel context to your
route builder
regards,
-Yogesh
On Tuesday 31 May 2016, Yogesh Rao <yo...@gmail.com> wrote:
> your datasiyrce is not configured.. can you make sure its working?
>
> regards,
> -yogesh
>
> On Tuesday 31 May 2016, imranrazakhan [via Camel] <
> ml-node+s465427n5783303h22@n5.nabble.com
> <javascript:_e(%7B%7D,'cvml','ml-node%2Bs465427n5783303h22@n5.nabble.com');>>
> wrote:
>
>> Following main class
>>
>> Main main = new Main();
>> main.addRouteBuilder( new GetAccountStatusBulk() );
>> main.getOrCreateCamelContext().getComponent("sql",
>> SqlComponent.class).setDataSource(setupDataSource());
>> main.run(args);
>>
>> Following change in roue
>>
>> .to("sql:select status from subscriber where mobilenumber in
>> (:#in:mobilenumbers)")
>>
>> Error :
>> Exception in thread "main" org.apache.camel.FailedToCreateRouteException:
>> Failed to create route route1 at:
>> Caused by: java.lang.IllegalArgumentException: DataSource must be
>> configured
>>
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the discussion
>> below:
>>
>> http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783303.html
>> To unsubscribe from camel-sql - SQL IN Query issue (2.17.1), click here
>> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5783264&code=eW9ndTEzQGdtYWlsLmNvbXw1NzgzMjY0fC04ODQzODQxMjM=>
>> .
>> NAML
>> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783307.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by yogu13 <yo...@gmail.com>.
your datasiyrce is not configured.. can you make sure its working?
regards,
-yogesh
On Tuesday 31 May 2016, imranrazakhan [via Camel] <
ml-node+s465427n5783303h22@n5.nabble.com> wrote:
> Following main class
>
> Main main = new Main();
> main.addRouteBuilder( new GetAccountStatusBulk() );
> main.getOrCreateCamelContext().getComponent("sql",
> SqlComponent.class).setDataSource(setupDataSource());
> main.run(args);
>
> Following change in roue
>
> .to("sql:select status from subscriber where mobilenumber in
> (:#in:mobilenumbers)")
>
> Error :
> Exception in thread "main" org.apache.camel.FailedToCreateRouteException:
> Failed to create route route1 at:
> Caused by: java.lang.IllegalArgumentException: DataSource must be
> configured
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783303.html
> To unsubscribe from camel-sql - SQL IN Query issue (2.17.1), click here
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5783264&code=eW9ndTEzQGdtYWlsLmNvbXw1NzgzMjY0fC04ODQzODQxMjM=>
> .
> NAML
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783306.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
Following main class
Main main = new Main();
main.addRouteBuilder( new GetAccountStatusBulk() );
main.getOrCreateCamelContext().getComponent("sql",
SqlComponent.class).setDataSource(setupDataSource());
main.run(args);
Following change in roue
.to("sql:select status from subscriber where mobilenumber in
(:#in:mobilenumbers)")
Error :
Exception in thread "main" org.apache.camel.FailedToCreateRouteException:
Failed to create route route1 at:
Caused by: java.lang.IllegalArgumentException: DataSource must be configured
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783303.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by yogu13 <yo...@gmail.com>.
can you try removing the datasource as a parameter to the sql component and
instead set the data source as and check
// required for the sql component
getContext().getComponent("sql",
SqlComponent.class).setDataSource(db);
Regards,
-Yogesh
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783298.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
Hi,
Same error
org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
[null]; error code [0]; Number of parameters mismatch. Expected: 1, was: 2;
nested exception is java.sql.SQLException: Number of parameters mismatch.
Expected: 1, was: 2
Regards
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783294.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by yogu13 <yo...@gmail.com>.
instead of simple("12345,56789") can you try new String[]{"12345", "56789"}
Regards,
-Yogesh
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783293.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
Hi Claus,
I did some more testing and it works only one scenario
from("timer://foo?fixedRate=true&period=6000")
.setHeader("mobilenumbers", simple("12345"))
.to("sql:select status from subscriber where mobilenumber
in (:#in:mobilenumbers)?dataSource=#oracleDS")
Even if i tried like below it again failed
from("timer://foo?fixedRate=true&period=6000")
.setHeader("mobilenumbers", simple("12345,56789"))
.to("sql:select status from subscriber where mobilenumber
in (:#in:mobilenumbers)?dataSource=#oracleDS")
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783279.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
As per oracle it support 1000
I tried with 5 records, still same.
org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
[null]; error code [0]; Number of parameters mismatch. Expected: 1, was: 5;
nested exception is java.sql.SQLException: Number of parameters mismatch.
Expected: 1, was: 5
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783273.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by Claus Ibsen <cl...@gmail.com>.
Try with lower value than 1000 as that is likely too many values in an
IN operator / prepared statement.
On Tue, May 31, 2016 at 8:18 AM, imranrazakhan <im...@gmail.com> wrote:
> Hi Claus Ibsen,
>
> I tried above code by following this section, Sorry for my ignorance if i m
> missing something.
>
> I am using // use a list, and my aggregator is returning ArrayList of
> strings
>
> Following is my custom aggregator code
>
> Message newIn = newExchange.getIn();
> Object newBody = newIn.getBody();
> ArrayList msisdns = null;
> if (oldExchange == null) {
> msisdns = new ArrayList<String>();
> msisdns.add(newBody);
> newIn.setBody(msisdns);
> return newExchange;
> } else {
> Message in = oldExchange.getIn();
> msisdns = in.getBody(ArrayList.class);
> msisdns.add(newBody);
> return oldExchange;
> }
>
> Regards,
>
>
>
>
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783271.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
--
Claus Ibsen
-----------------
http://davsclaus.com @davsclaus
Camel in Action 2: https://www.manning.com/ibsen2
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by imranrazakhan <im...@gmail.com>.
Hi Claus Ibsen,
I tried above code by following this section, Sorry for my ignorance if i m
missing something.
I am using // use a list, and my aggregator is returning ArrayList of
strings
Following is my custom aggregator code
Message newIn = newExchange.getIn();
Object newBody = newIn.getBody();
ArrayList msisdns = null;
if (oldExchange == null) {
msisdns = new ArrayList<String>();
msisdns.add(newBody);
newIn.setBody(msisdns);
return newExchange;
} else {
Message in = oldExchange.getIn();
msisdns = in.getBody(ArrayList.class);
msisdns.add(newBody);
return oldExchange;
}
Regards,
--
View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264p5783271.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: camel-sql - SQL IN Query issue (2.17.1)
Posted by Claus Ibsen <cl...@gmail.com>.
See the documentation at
http://camel.apache.org/sql-component.html
The section:
Using IN queries with dynamic values
On Mon, May 30, 2016 at 10:09 PM, imranrazakhan <im...@gmail.com> wrote:
> Hi,
>
> I am facing following error while using SQL IN clause
>
> from("file://D:/Activity?fileName=BATCH_11.csv&noop=true")
> .split( body().tokenize( System.getProperty("line.separator") )
> ).streaming()
> .aggregate( constant(true), new ArrayListAggregationStrategy() )
> .completionSize(1000)
> .to("sql:select status from subscriber where mobilenumber in
> (:#in:mobilenumbers)?dataSource=#oracleDS")
> .....
> .end();
>
> it gives error
>
> org.apache.camel.RuntimeExchangeException: Cannot find key [mobilenumbers]
> in message body or headers to use when setting named parameter in query
>
> I changed my route like below
>
> from("file://D:/Activity?fileName=BATCH_11.csv&noop=true")
> .split( body().tokenize( System.getProperty("line.separator") )
> ).streaming()
> .aggregate( constant(true), new ArrayListAggregationStrategy() )
> .completionSize(1000)
> .setHeader("mobilenumbers", simple("${body}"))
> .to("sql:select status from subscriber where mobilenumber in
> (:#in:mobilenumbers)?dataSource=#oracleDS")
>
> Now facing following error
>
> org.springframework.jdbc.UncategorizedSQLException:
> PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state
> [null]; error code [0]; Number of parameters mismatch. Expected: 1, was:
> 1000; nested exception is java.sql.SQLException: Number of parameters
> mismatch. Expected: 1, was: 1000
>
>
>
>
>
>
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/camel-sql-SQL-IN-Query-issue-2-17-1-tp5783264.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
--
Claus Ibsen
-----------------
http://davsclaus.com @davsclaus
Camel in Action 2: https://www.manning.com/ibsen2