Fetching data from PostgreSQL database in Mule flow using JDBC transport

Question

I have a Mule flow to fetch data from a table in a PostgreSQL database and convert the data into XML format and write to a file:

<mule ...>
    <spring:bean id="Postgres-jdbcDataSource"
        class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
        <spring:property name="driverName" value="org.postgresql.Driver" />
        <spring:property name="url"
            value="jdbc:postgresql://host:port/schema?user=username&password=password" />
    </spring:bean>

    <jdbc:connector name="Postgres-jdbcConnector"
        dataSource-ref="Postgres-jdbcDataSource" pollingFrequency="60000"
        transactionPerMessage="false">
        <jdbc:query key="read" value="SELECT * FROM tablename" />
    </jdbc:connector>

    <file:connector name="file_connector" fileAge="500"
        streaming="false" pollingFrequency="60000" />

    <flow name="Postgres-flow">
        <jdbc:inbound-endpoint queryKey="read"
            connector-ref="Postgres-jdbcConnector">
            <jdbc:transaction action="ALWAYS_BEGIN" />
            <property key="receiveMessageInTransaction" value="true" />
        </jdbc:inbound-endpoint>

        <custom-transformer name="Postgres-transformer"
            class="com.example.transformer.DbToXmlTransformer" ignoreBadInput="false"
            encoding="UTF-8" />

        <file:outbound-endpoint connector-ref="file_connector"
            path="/home/path" outputPattern="file.xml" responseTimeout="10000"
            encoding="UTF-8" />
    </flow>
</mule>

When I run this flow, the flow does not fetch data from DB and write to file. It does not throw any errors or exceptions either. But when I run the same flow for MySQL or SQLServer database, changing driverName and url properties accordingly, the flow works fine.

Any idea why the Postgres database does not work? Probably it requires different DataSource class?

Answer 1

There is also a Postgre data source for mule and you can use it instead of spring beans :

<jdbc:postgresql-data-source name="PostgreSQL_Data_Source" user="your user name" password="your pwd" url="jdbc:postgresql://localhost:5432/TestDB" transactionIsolation="UNSPECIFIED" doc:name="PostgreSQL Data Source"/>

Anyways... , with your existing config you can just check by keeping the JDBC inbound endpoint in a poll component and place a logger before File outbound to check the payload value.. If it gets payload value in logger ..that means it is fetching the value ..Let me know if it works ... you can try the following :-

   <mule ...>
        <spring:bean id="Postgres-jdbcDataSource"
            class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
            <spring:property name="driverName" value="org.postgresql.Driver" />
            <spring:property name="url"
                value="jdbc:postgresql://host:port/schema?user=username&password=password" />
        </spring:bean>

        <jdbc:connector name="Postgres-jdbcConnector"
            dataSource-ref="Postgres-jdbcDataSource" pollingFrequency="60000"
            transactionPerMessage="false">
            <jdbc:query key="read" value="SELECT * FROM tablename" />
        </jdbc:connector>

        <file:connector name="file_connector" fileAge="500"
            streaming="false" pollingFrequency="60000" />

        <flow name="Postgres-flow">
          <poll frequency="1000" doc:name="Poll">
            <jdbc:inbound-endpoint queryKey="read"
                connector-ref="Postgres-jdbcConnector">
                <jdbc:transaction action="ALWAYS_BEGIN" />
                <property key="receiveMessageInTransaction" value="true" />
            </jdbc:inbound-endpoint>
    </poll>

     <!-- You can also use object to xml transformer if you are not using any custom transformer -->
     <!--<mulexml:object-to-xml-transformer doc:name="Object to XML"/> -->

            <custom-transformer name="Postgres-transformer"
                class="com.example.transformer.DbToXmlTransformer" ignoreBadInput="false"
                encoding="UTF-8" />

<logger message="Payload :- #[message.payload]" level="INFO" doc:name="Logger"/>

            <file:outbound-endpoint connector-ref="file_connector"
                path="/home/path" outputPattern="file.xml" responseTimeout="10000"
                encoding="UTF-8" />
        </flow>
    </mule>

For more find the reference here for Postgre Database with Mule :- http://www.dotnetfunda.com/articles/show/2068/using-mule-studio-to-read-data-from-postgresqlinbound-and-write-it-to