SQLComponent batch=true issues?

[rjh-sgc <ro...@sungard.com>]

.to("sql:select id,ssn from user?dataSourceRef=dataSource")
.to("sql:insert into dest (id, ssn) values (# ,
#)?batch=true&dataSourceRef=dataSource");

I trying to pull all records from the user table and put it into the dest
table.  There are 2 rows in the user table.  

When i run this code, I get the exception:  
java.sql.SQLException: Number of parameters mismatch. Expected: 2, was:1

it looks like the data is correct though
ERROR Error processing exchange. Exchange[Message: [{id=1, ssn=123456789},
{id=2, ssn=987654321}]]

My best guess is that Camel is treating the body as a single object.  But
this is happening whether batch=true is being set or not.  Since the return
object from the is a List of Map objects, shouldnt batch=true know to
iterate over the list and insert each map of column name/value pairs?

 This is what I gather from the documentation 
http://camel.apache.org/sql-component.html here 

"The SQL component tries to convert the message body to an object of
java.util.Iterator type and then uses this iterator to fill the query
parameters (where each query parameter is represented by a # symbol (or
configured placeholder) in the endpoint URI). If the message body is not an
array or collection, the conversion results in an iterator that iterates
over only one object, which is the body itself.
For example, if the message body is an instance of java.util.List, the first
item in the list is substituted into the first occurrence of # in the SQL
query, the second item in the list is substituted into the second occurrence
of #, and so on.
If batch is set to true, then the interpretation of the inbound message body
changes slightly – instead of an iterator of parameters, the component
expects an iterator that contains the parameter iterators; the size of the
outer iterator determines the batch size."



--
View this message in context: http://camel.465427.n5.nabble.com/SQLComponent-batch-true-issues-tp5719401.html
Sent from the Camel - Users mailing list archive at Nabble.com.

Re: SQLComponent batch=true issues?

[Christian Müller <ch...@gmail.com>]

Checkout this test:
https://svn.apache.org/repos/asf/camel/trunk/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlRouteTest.java
You have to convert the inner map into a list/set/array before you send it
to the second sql endpoint.

Best,
Christian

On Sat, Sep 15, 2012 at 6:03 AM, rjh-sgc <ro...@sungard.com> wrote:

> .to("sql:select id,ssn from user?dataSourceRef=dataSource")
> .to("sql:insert into dest (id, ssn) values (# ,
> #)?batch=true&dataSourceRef=dataSource");
>
> I trying to pull all records from the user table and put it into the dest
> table.  There are 2 rows in the user table.
>
> When i run this code, I get the exception:
> java.sql.SQLException: Number of parameters mismatch. Expected: 2, was:1
>
> it looks like the data is correct though
> ERROR Error processing exchange. Exchange[Message: [{id=1, ssn=123456789},
> {id=2, ssn=987654321}]]
>
> My best guess is that Camel is treating the body as a single object.  But
> this is happening whether batch=true is being set or not.  Since the return
> object from the is a List of Map objects, shouldnt batch=true know to
> iterate over the list and insert each map of column name/value pairs?
>
>  This is what I gather from the documentation
> http://camel.apache.org/sql-component.html here
>
> "The SQL component tries to convert the message body to an object of
> java.util.Iterator type and then uses this iterator to fill the query
> parameters (where each query parameter is represented by a # symbol (or
> configured placeholder) in the endpoint URI). If the message body is not an
> array or collection, the conversion results in an iterator that iterates
> over only one object, which is the body itself.
> For example, if the message body is an instance of java.util.List, the
> first
> item in the list is substituted into the first occurrence of # in the SQL
> query, the second item in the list is substituted into the second
> occurrence
> of #, and so on.
> If batch is set to true, then the interpretation of the inbound message
> body
> changes slightly – instead of an iterator of parameters, the component
> expects an iterator that contains the parameter iterators; the size of the
> outer iterator determines the batch size."
>
>
>
> --
> View this message in context:
> http://camel.465427.n5.nabble.com/SQLComponent-batch-true-issues-tp5719401.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
>



--