[mule-user] [v3.1] Problem while reading .csv files and inserting values into MySQL db

[a a]

Hi,

I'm trying to read .csv files from local directory, use "jdbc:csv-to-maps-transformer" to insert values into MySQL database. My configuration file is below :

*******
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ftp="http://www.mulesoft.org/schema/mule/ftp"
xmlns:file="http://www.mulesoft.org/schema/mule/file"
xmlns:jdbc="http://www.mulesoft.org/schema/mule/jdbc"
xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
xmlns:spring="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/ftp http://www.mulesoft.org/schema/mule/ftp/3.1/mule-ftp.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/3.1/mule-file.xsd
http://www.mulesoft.org/schema/mule/jdbc http://www.mulesoft.org/schema/mule/jdbc/3.1/mule-jdbc.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.1/mule.xsd
http://www.mulesoft.org/schema/mule/ajax http://www.mulesoft.org/schema/mule/ajax/3.1/mule-ajax.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/3.1/mule-vm.xsd">

<!-- This placeholder bean will let you import the properties from the db.properties file. -->
<!-- <spring:bean id="property-placeholder" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<spring:property name="location" value="resources/db.properties"/>
</spring:bean> -->

<!-- This data source is used to connect to the database using the values loaded from the properties file -->
<spring:bean id="jdbcDataSource"
class="org.enhydra.jdbc.standard.StandardDataSource"
destroy-method="shutdown">
<spring:property name="driverName" value="com.mysql.jdbc.Driver"/>
<spring:property name="url" value="jdbc:mysql://localhost:3306/db"/>

<!-- NOTE: use these properties if you cannot pass the user/password info in the connection string configured
in the db.properties file
-->
<spring:property name="user" value="root"/>-->
<spring:property name="password" value="pwd"/>-->
</spring:bean>

<!-- This JDBC connector specifies the queries to run and what data source to use. -->
<jdbc:connector name="jdbcConnector" dataSource-ref="jdbcDataSource">
<jdbc:query key="insertIntoDB"
value="insert into bd
(ID, HEURE, DUREE, EMPLACEMENT)
values
(#[map-payload:ID;int;in], #[map-payload:HEURE], #[map-payload:DUREE], #[map-payload:EMPLACEMENT])"/>

</jdbc:connector>

<file:connector name="input"
fileAge="500" autoDelete="true"
pollingFrequency="100"
moveToDirectory="/backup"
moveToPattern="#[header:originalFilename].bat"/>

<!-- This transformer will take CSV data and convert it to a map, based on the format provided in a mapping file. -->
<jdbc:csv-to-maps-transformer name="CSV2Maps" delimiter=";" mappingFile="conf/mules-csv-format.xml" ignoreFirstRecord="true"/>

<jdbc:maps-to-csv-transformer name="Maps2CSV" delimiter=";" mappingFile="conf/mules-csv-format.xml" ignoreFirstRecord="true"/>

<flow name="CSVLoaderAndInsertToDB">
<description>This flow takes a CSV input, parses it and inserts it into the database, returning the inserted data in xml format. If an exception is thrown (such as a primary key violation, trying to insert the same data twice), it will also be shown in the response.</description>
<file:inbound-endpoint connector-ref="input" path="../FTPFiles">
<file:filename-wildcard-filter pattern="*.csv" />
<transformer ref="CSV2Maps"/>
</file:inbound-endpoint>

<jdbc:outbound-endpoint queryKey="insertIntoDB" exchange-pattern="one-way"/>
</flow>

</mule>
********

I got the following error :

**
Root Exception stack trace:
org.xml.sax.SAXParseException: cvc-complex-type.2.3: Element 'spring:bean' cannot have character [children], because the type's content type is element-only.
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:195)
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.error(ErrorHandlerWrapper.java:131)
at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:384)
+ 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
**

Could someone give me a help?

Thx.
**
Stevy

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[David Dossot]

You have two trailing --> in your bean definition.

[a a]

Thanks for your response...

But, I don't understand what you mean... (Do you talk about the part I comment with <!-- --> ?). Please, be more clear. Thanks.

[David Dossot]

No, elsewhere, look for "-->" in your XML.

[Joshua Chaitin-Pollak]

On Feb 16, 2011, at 3:30 PM, a a wrote:

Hi,

I'm trying to read .csv files from local directory, use "jdbc:csv-to-maps-transformer" to insert values into MySQL database. My configuration file is below :

...

   <!-- This data source is used to connect to the database using the values loaded from the properties file -->
   <spring:bean id="jdbcDataSource"
       class="org.enhydra.jdbc.standard.StandardDataSource"
       destroy-method="shutdown">
       <spring:property name="driverName" value="com.mysql.jdbc.Driver"/>
       <spring:property name="url" value="jdbc:mysql://localhost:3306/db"/>

       <!-- NOTE: use these properties if you cannot pass the user/password info in the connection string configured
            in the db.properties file
       -->
       <spring:property name="user" value="root"/>-->
       <spring:property name="password" value="pwd"/>-->

I believe David means the --> in the two lines above are 'extra', either they should be removed or matching <!-- need to be added. 

Remember XML comments cannot be nested.

I got the following error :

**
Root Exception stack trace:
org.xml.sax.SAXParseException: cvc-complex-type.2.3: Element 'spring:bean' cannot have character [children], because the type's content type is element-only.
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:195)
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.error(ErrorHandlerWrapper.java:131)
at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:384)
   + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
**

This makes sense because the spring:property elements are within the spring:bean element above.

-- 

Joshua Chaitin-Pollak | Software Architect 

Kiva Systems, Inc     | 300 Riverpark Drive, North Reading, MA 01864

[a a]

Aaah okay! After user & password spring properties... Thanks!

But, i got another problem:
****
Message : Configuration problem: Unable to locate NamespaceHandler for namespace [http://www.mulesoft.org/schema/mule/ee/jdbc]
****

I got this problem surely because the transformer "<jdbc:csv-to-maps-transformer .../>" i used, is only available for Mule Enterprise Edition!!! Oups, an idea??? Do I need to create a custom transformer?

[Dirk Olmes]

On 02/16/2011 11:05 PM, a a wrote:
> Aaah okay! After user & password spring properties... Thanks!
>
> But, i got another problem: **** Message :
> Configuration problem: Unable to locate NamespaceHandler for
> namespace [http://www.mulesoft.org/schema/mule/ee/jdbc] ****
>
> I got this problem surely because the transformer
> "<jdbc:csv-to-maps-transformer .../>" i used, is only available for
> Mule Enterprise Edition!!! Oups, an idea??? Do I need to create a
> custom transformer?

Or upgrade to Mule EE, obviously.

-dirk