You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by "vs.souza" <vs...@gmail.com> on 2013/07/18 02:24:32 UTC
problem processing CSV to Database (camel-csv and camel-sql)
Hello guys,
I built the following camel route using spring dsl:
UserCSVToMySQL.xml
<http://camel.465427.n5.nabble.com/file/n5735821/UserCSVToMySQL.xml>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:camel="http://camel.apache.org/schema/spring"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:osgi="http://www.springframework.org/schema/osgi"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://camel.apache.org/schema/spring
http://camel.apache.org/schema/spring/camel-spring.xsd
http://www.springframework.org/schema/osgi
http://www.springframework.org/schema/spring-osgi.xsd">
<bean id="loaderDS" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/sandbox-db"/>
<property name="username" value="sanduser" />
<property name="password" value="sandpass" />
<property name="initialSize" value="5" />
<property name="maxIdle" value="5" />
<property name="maxActive" value="30" />
<property name="maxWait" value="10000" />
<property name="poolPreparedStatements" value="true" />
<property name="validationQuery" value="SELECT 1" />
</bean>
<camel:camelContext id="CSVLoaderContext" trace="false">
<camel:route id="UserCSVToMYSQL">
<camel:from uri="file:/home/jedi/Java/Workspaces/FileSandbox?delete=true"
/>
<camel:split parallelProcessing="true" streaming="true">
<camel:tokenize token="\n" />
<camel:unmarshal>
<camel:csv/>
</camel:unmarshal>
<camel:to uri="sql:INSERT INTO
user(`id`,`external_user_id`,`first_name`,`last_name`,`email`,`active`)
VALUES (#,#,#,#,#)?dataSourceRef=loaderDS"/>
</camel:split>
</camel:route>
</camel:camelContext>
</beans>
but when I try to execute it keeps displaying the error:
21:12:53,677 | ERROR | read #46 - Split | DefaultErrorHandler |
95 - org.apache.camel.camel-core - 2.10.3 | Failed delivery for (MessageId:
ID-ubuntu-32934-1374106272368-2-1 on ExchangeId:
ID-ubuntu-32934-1374106272368-2-4370). Exhausted after delivery attempt: 1
caught: org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO
user(`id`,`external_user_id`,`first_name`,`last_name`,`email`,`active`)
VALUES (?,?,?,?,?)]; SQL state [null]; error code [0]; Number of parameters
mismatch. Expected: 5, was:1; nested exception is java.sql.SQLException:
Number of parameters mismatch. Expected: 5, was:1
org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO
user(`id`,`external_user_id`,`first_name`,`last_name`,`email`,`active`)
VALUES (?,?,?,?,?)]; SQL state [null]; error code [0]; Number of parameters
mismatch. Expected: 5, was:1; nested exception is java.sql.SQLException:
Number of parameters mismatch. Expected: 5, was:1
Looks like that camel-sql is not understanding the ArrayList that is coming
to assign to each Insert parameter.
Below I send you a couple of registers that I have in my csv file that:
0,firstname0,lastname0,emailfromuser0@usermail.com,1
1,firstname1,lastname1,emailfromuser1@usermail.com,1
2,firstname2,lastname2,emailfromuser2@usermail.com,1
3,firstname3,lastname3,emailfromuser3@usermail.com,1
4,firstname4,lastname4,emailfromuser4@usermail.com,1
5,firstname5,lastname5,emailfromuser5@usermail.com,1
6,firstname6,lastname6,emailfromuser6@usermail.com,1
7,firstname7,lastname7,emailfromuser7@usermail.com,1
8,firstname8,lastname8,emailfromuser8@usermail.com,1
9,firstname9,lastname9,emailfromuser9@usermail.com,1
10,firstname10,lastname10,emailfromuser10@usermail.com,1
11,firstname11,lastname11,emailfromuser11@usermail.com,1
12,firstname12,lastname12,emailfromuser12@usermail.com,1
13,firstname13,lastname13,emailfromuser13@usermail.com,1
14,firstname14,lastname14,emailfromuser14@usermail.com,1
15,firstname15,lastname15,emailfromuser15@usermail.com,1
16,firstname16,lastname16,emailfromuser16@usermail.com,1
17,firstname17,lastname17,emailfromuser17@usermail.com,1
18,firstname18,lastname18,emailfromuser18@usermail.com,1
19,firstname19,lastname19,emailfromuser19@usermail.com,1
20,firstname20,lastname20,emailfromuser20@usermail.com,1
21,firstname21,lastname21,emailfromuser21@usermail.com,1
22,firstname22,lastname22,emailfromuser22@usermail.com,1
23,firstname23,lastname23,emailfromuser23@usermail.com,1
24,firstname24,lastname24,emailfromuser24@usermail.com,1
25,firstname25,lastname25,emailfromuser25@usermail.com,1
26,firstname26,lastname26,emailfromuser26@usermail.com,1
27,firstname27,lastname27,emailfromuser27@usermail.com,1
28,firstname28,lastname28,emailfromuser28@usermail.com,1
And my table structure is:
delimiter $$
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`external_user_id` int(10) unsigned NOT NULL,
`first_name` varchar(45) CHARACTER SET latin1 NOT NULL,
`last_name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
`email` varchar(45) CHARACTER SET latin1 NOT NULL,
`active` bit(1) DEFAULT b'1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
Do you have any ideas about how to solve this problem?
Thanks and regards.
--
View this message in context: http://camel.465427.n5.nabble.com/problem-processing-CSV-to-Database-camel-csv-and-camel-sql-tp5735821.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: problem processing CSV to Database (camel-csv and camel-sql)
Posted by "vs.souza" <vs...@gmail.com>.
Hello Claus...
thank you very much. That worked like a charm.
My best regards.
VinÃcius.
--
View this message in context: http://camel.465427.n5.nabble.com/problem-processing-CSV-to-Database-camel-csv-and-camel-sql-tp5735821p5735852.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: problem processing CSV to Database (camel-csv and camel-sql)
Posted by Claus Ibsen <cl...@gmail.com>.
Hi
I think CSV unmarshal to a List<Map> so you would need to turn that into a Map.
<camel:unmarshal>
<camel:csv/>
</camel:unmarshal>
<transform>
<simple>${body[0]}</simple>
</transform>
On Thu, Jul 18, 2013 at 2:24 AM, vs.souza <vs...@gmail.com> wrote:
> Hello guys,
>
> I built the following camel route using spring dsl:
>
> UserCSVToMySQL.xml
> <http://camel.465427.n5.nabble.com/file/n5735821/UserCSVToMySQL.xml>
>
> <?xml version="1.0" encoding="UTF-8"?>
> <beans xmlns="http://www.springframework.org/schema/beans"
> xmlns:camel="http://camel.apache.org/schema/spring"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:osgi="http://www.springframework.org/schema/osgi"
> xsi:schemaLocation="
> http://www.springframework.org/schema/beans
> http://www.springframework.org/schema/beans/spring-beans.xsd
> http://camel.apache.org/schema/spring
> http://camel.apache.org/schema/spring/camel-spring.xsd
> http://www.springframework.org/schema/osgi
> http://www.springframework.org/schema/spring-osgi.xsd">
>
>
> <bean id="loaderDS" class="org.apache.commons.dbcp.BasicDataSource">
> <property name="driverClassName" value="com.mysql.jdbc.Driver" />
> <property name="url" value="jdbc:mysql://localhost:3306/sandbox-db"/>
> <property name="username" value="sanduser" />
> <property name="password" value="sandpass" />
> <property name="initialSize" value="5" />
> <property name="maxIdle" value="5" />
> <property name="maxActive" value="30" />
> <property name="maxWait" value="10000" />
> <property name="poolPreparedStatements" value="true" />
> <property name="validationQuery" value="SELECT 1" />
> </bean>
>
> <camel:camelContext id="CSVLoaderContext" trace="false">
> <camel:route id="UserCSVToMYSQL">
> <camel:from uri="file:/home/jedi/Java/Workspaces/FileSandbox?delete=true"
> />
> <camel:split parallelProcessing="true" streaming="true">
> <camel:tokenize token="\n" />
> <camel:unmarshal>
> <camel:csv/>
> </camel:unmarshal>
> <camel:to uri="sql:INSERT INTO
> user(`id`,`external_user_id`,`first_name`,`last_name`,`email`,`active`)
> VALUES (#,#,#,#,#)?dataSourceRef=loaderDS"/>
> </camel:split>
> </camel:route>
> </camel:camelContext>
>
> </beans>
>
> but when I try to execute it keeps displaying the error:
>
> 21:12:53,677 | ERROR | read #46 - Split | DefaultErrorHandler |
> 95 - org.apache.camel.camel-core - 2.10.3 | Failed delivery for (MessageId:
> ID-ubuntu-32934-1374106272368-2-1 on ExchangeId:
> ID-ubuntu-32934-1374106272368-2-4370). Exhausted after delivery attempt: 1
> caught: org.springframework.jdbc.UncategorizedSQLException:
> PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO
> user(`id`,`external_user_id`,`first_name`,`last_name`,`email`,`active`)
> VALUES (?,?,?,?,?)]; SQL state [null]; error code [0]; Number of parameters
> mismatch. Expected: 5, was:1; nested exception is java.sql.SQLException:
> Number of parameters mismatch. Expected: 5, was:1
> org.springframework.jdbc.UncategorizedSQLException:
> PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO
> user(`id`,`external_user_id`,`first_name`,`last_name`,`email`,`active`)
> VALUES (?,?,?,?,?)]; SQL state [null]; error code [0]; Number of parameters
> mismatch. Expected: 5, was:1; nested exception is java.sql.SQLException:
> Number of parameters mismatch. Expected: 5, was:1
>
> Looks like that camel-sql is not understanding the ArrayList that is coming
> to assign to each Insert parameter.
>
> Below I send you a couple of registers that I have in my csv file that:
>
> 0,firstname0,lastname0,emailfromuser0@usermail.com,1
> 1,firstname1,lastname1,emailfromuser1@usermail.com,1
> 2,firstname2,lastname2,emailfromuser2@usermail.com,1
> 3,firstname3,lastname3,emailfromuser3@usermail.com,1
> 4,firstname4,lastname4,emailfromuser4@usermail.com,1
> 5,firstname5,lastname5,emailfromuser5@usermail.com,1
> 6,firstname6,lastname6,emailfromuser6@usermail.com,1
> 7,firstname7,lastname7,emailfromuser7@usermail.com,1
> 8,firstname8,lastname8,emailfromuser8@usermail.com,1
> 9,firstname9,lastname9,emailfromuser9@usermail.com,1
> 10,firstname10,lastname10,emailfromuser10@usermail.com,1
> 11,firstname11,lastname11,emailfromuser11@usermail.com,1
> 12,firstname12,lastname12,emailfromuser12@usermail.com,1
> 13,firstname13,lastname13,emailfromuser13@usermail.com,1
> 14,firstname14,lastname14,emailfromuser14@usermail.com,1
> 15,firstname15,lastname15,emailfromuser15@usermail.com,1
> 16,firstname16,lastname16,emailfromuser16@usermail.com,1
> 17,firstname17,lastname17,emailfromuser17@usermail.com,1
> 18,firstname18,lastname18,emailfromuser18@usermail.com,1
> 19,firstname19,lastname19,emailfromuser19@usermail.com,1
> 20,firstname20,lastname20,emailfromuser20@usermail.com,1
> 21,firstname21,lastname21,emailfromuser21@usermail.com,1
> 22,firstname22,lastname22,emailfromuser22@usermail.com,1
> 23,firstname23,lastname23,emailfromuser23@usermail.com,1
> 24,firstname24,lastname24,emailfromuser24@usermail.com,1
> 25,firstname25,lastname25,emailfromuser25@usermail.com,1
> 26,firstname26,lastname26,emailfromuser26@usermail.com,1
> 27,firstname27,lastname27,emailfromuser27@usermail.com,1
> 28,firstname28,lastname28,emailfromuser28@usermail.com,1
>
> And my table structure is:
>
> delimiter $$
>
> CREATE TABLE `user` (
> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
> `external_user_id` int(10) unsigned NOT NULL,
> `first_name` varchar(45) CHARACTER SET latin1 NOT NULL,
> `last_name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
> `email` varchar(45) CHARACTER SET latin1 NOT NULL,
> `active` bit(1) DEFAULT b'1',
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
>
> Do you have any ideas about how to solve this problem?
>
> Thanks and regards.
>
>
>
>
> --
> View this message in context: http://camel.465427.n5.nabble.com/problem-processing-CSV-to-Database-camel-csv-and-camel-sql-tp5735821.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
--
Claus Ibsen
-----------------
Red Hat, Inc.
Email: cibsen@redhat.com
Twitter: davsclaus
Blog: http://davsclaus.com
Author of Camel in Action: http://www.manning.com/ibsen