Uploaded image for project: 'Camel'
  1. Camel
  2. CAMEL-3803

Component camel-jdbc does not support Transactions

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 2.8.0
    • camel-jdbc
    • None

    Description

      The component camel-jdbc does not support transaction as the component creates SQL statement but does not handle errors, exceptions generated and propagated by TransactionErrorHandler. This is a clear limitation of this component (which is not the case with component camel-sql).

      I suggest that :

      (A) we improve this component to provide Transaction support
      OR
      (B) We keep it like now but mention that limitation into Camel documentation
      OR
      (C) we merge it with camel-sql component to have one providing transaction support even if it uses behind the scene JDBCTemplate of Spring. Remark : If we decided to merge the two components, that should be an excellent idea to allow the SQL component to receive the SQL query as an exchange to avoid to hard code it in the query parameter of the endpoint.

      I provide hereafter example of code that we can use to reproduce that :

      <?xml version="1.0" encoding="UTF-8"?>
      <beans xmlns="http://www.springframework.org/schema/beans"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             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
          ">
      
          <!-- Bean generating the error -->
          <bean id="jdbcReportIncident" class="org.apache.camel.example.SqlSpringTxReportIncident"/>
      
          <!-- TransactionManager -->
          <bean id="required" class="org.apache.camel.spring.spi.SpringTransactionPolicy">
              <property name="transactionManager" ref="txManager"/>
              <property name="propagationBehaviorName" value="PROPAGATION_REQUIRED"/>
          </bean>
      
          <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
              <property name="dataSource" ref="reportdb"/>
          </bean>
      
      
          <camelContext id="camel" xmlns="http://camel.apache.org/schema/spring">
      
              <!-- Camel Transactional Route with NO rollback  -->
              <route id="transaction-non-working-camel-jdbc">
                 <from uri="file://target/datainsertnotrollback?moveFailed=failed"/>
                 <transacted ref="required"/>
                 <split>
                     <tokenize token=","/>
                     <setBody>
                         <simple>INSERT INTO REPORT.T_INCIDENT (INCIDENT_REF,INCIDENT_DATE,GIVEN_NAME,FAMILY_NAME,SUMMARY,DETAILS,EMAIL,PHONE) VALUES
                                 ('${body}','2011-03-21','Charles','Moulliard','Incident-${body}','This is a report incident for camel-${body}','cmoulliard@fusesource.com','+111 10 20 300')
                         </simple>
                     </setBody>
                     <log message=">>> SQL Query : ${body}"/>
                     <to uri="jdbc:reportdb"/>
                     <bean ref="jdbcReportIncident" method="generateError"/>
                     <log message=">>> Result : ${body}"/>
                 </split>
             </route>
      
      
              <route id="trigger-database">
                  <from uri="timer://webinar?delay=20000&amp;period=20000"/>
                  <to uri="sql:SELECT * FROM REPORT.T_INCIDENT"/>
                  <split>
                      <simple>${body}</simple>
                      <log message=">>> Select all : ${body}"/>
                  </split>
              </route>
      
          </camelContext>
      
          <bean id="reportdb" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
              <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
              <property name="url" value="jdbc:hsqldb:hsql://localhost/reportdb"/>
              <property name="username" value="sa"/>
              <property name="password" value=""/>
          </bean>
      
      </beans>
      
      

      Java class generating the error

      public class SqlSpringTxReportIncident {
      
          public void generateError() throws Exception {
              throw new Exception("Cannot connect to Database ....");
          }
      }
      

      and Script to create DB in HSQLDB

      CREATE TABLE PUBLIC.T_INCIDENT (
          INCIDENT_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
          INCIDENT_REF VARCHAR(55),
          INCIDENT_DATE TIMESTAMP,
          GIVEN_NAME VARCHAR(35),
          FAMILY_NAME VARCHAR(35),
          SUMMARY VARCHAR(35),
          DETAILS VARCHAR(255),
          EMAIL VARCHAR(60),
          PHONE VARCHAR(35),
          CREATION_DATE TIMESTAMP,
          CREATION_USER VARCHAR(255)
      )
      
      The key file must only contain data like '001', '002', ...
      

      Attachments

        1. patch-3808-updated.diff
          8 kB
          Heath Kesler
        2. patch-3808.diff
          8 kB
          Heath Kesler
        3. JdbcTransactedRouteTest.java
          5 kB
          Christian Müller

        Issue Links

          Activity

            People

              muellerc Christian Müller
              cmoulliard Moulliard Charles
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: