[
https://issues.apache.org/jira/browse/CAMEL-3803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13025331#comment-13025331
]
Christian Müller commented on CAMEL-3803:
-----------------------------------------
Yes, those were my thoughts. I will apply your patch without the transacted
option and open a new JIRA that camel sql should also support the exchange in
body for the sql statements.
> Component camel-jdbc does not support Transactions
> --------------------------------------------------
>
> Key: CAMEL-3803
> URL: https://issues.apache.org/jira/browse/CAMEL-3803
> Project: Camel
> Issue Type: Improvement
> Components: camel-jdbc
> Reporter: Charles Moulliard
> Fix For: 3.0.0
>
> Attachments: JdbcTransactedRouteTest.java, patch-3808-updated.diff,
> patch-3808.diff
>
>
> 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 :
> {code}
> <?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}','[email protected]','+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&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>
> {code}
> Java class generating the error
> {code}
> public class SqlSpringTxReportIncident {
> public void generateError() throws Exception {
> throw new Exception("Cannot connect to Database ....");
> }
> }
> {code}
> and Script to create DB in HSQLDB
> {code}
> 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', ...
> {code}
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
