Update to useTransaction="true" with DBReport Mediator

[anupama-pathirage]

Current Limitation

Some details to include.

1. In order to commit at the end you have to add <transaction action="commit"/>

2. You have to use XA datasource class name instead of the driver class name, as you have to configure that to work in the XA transaction. Use the following db config as an example,

<definition type="RDBMS">
                        <configuration>
                                <dataSourceClassName>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</dataSourceClassName>
                                <defaultAutoCommit>false</defaultAutoCommit>
                                <maxActive>200</maxActive>
                                <maxWait>60000</maxWait>
                                <validationQuery>select 1 </validationQuery>
                                <validationInterval>30000</validationInterval>
                                <testWhileIdle>true</testWhileIdle>
                                <minIdle>5</minIdle>
                                <logAbandoned>true</logAbandoned>
                                <testOnBorrow>true</testOnBorrow>
                                <dataSourceProps>
                                         <property name="url">jdbc:mysql://*****:3306/yourdbname</property>
                                         <property name="user">******</property>
                                         <property name="password">*******</property>
                                </dataSourceProps>
                        </configuration>
                </definition>

3. Set the defaultAutoCommit to false, to avoid auto committing.

4. When you define the datasource configuration inline, it is using DBCP connection pooling. Due to that, in the Transaction manager, it is throwing the mentioned exception. To avoid that we can define the datasource externally, using a datasource config project, as discussed during the call and referring that in the DBReportMediator

<dbreport useTransaction="true">
                <connection>
                    <pool>
                        <dsName>jdbc/MYSQLDS</dsName>
                    </pool>
                </connection>

Suggested Improvement

Refer above

Version

No response