0

I need to manually control transaction flow across several DB calls, that needs to be executed sequentially

My use case:

  • Create transaction
  • Insert record to a table
  • Update record in a different table based on ID returned from the previous insert
  • Commit transaction if everything passed or rollback in case of whatever error

I cannot get transaction mediator working with DSS. If I try to force rollback at the end of the flow, I get warning in log and record stays in the database.

[EI-Core]  WARN - ROLLBACK Some How TX null

Sequence:

<transaction action="new"/>
<log level="custom">
        <property name="DB_CALL" value="CreateIdentifier"/>
</log>
<payloadFactory media-type="xml">
    <format>
         <xs:createIdentifier xmlns:xs="cz.example">
             <xs:entity>$1</xs:entity>
             <xs:partner>$2</xs:partner>
             <xs:value>$3</xs:value>
             <xs:pair_id>$4</xs:pair_id>
         </xs:createIdentifier>
    </format>
    <args>
        <arg evaluator="xml" expression="$func:entity"/>
        <arg evaluator="xml" expression="$func:partner"/>
        <arg evaluator="xml" expression="$func:value"/>
        <arg evaluator="xml" expression="$func:pair_id"/>
    </args>
</payloadFactory>
<call>
    <endpoint key="Database"/>
</call>
<log level="custom">
    <property name="DB CALL" value="UpdateInputProcessedAt"/>
</log>
<payloadFactory media-type="xml">
    <format>
        <xs:updateInputProcessedAt xmlns:xs="cz.example">
            <xs:id>$1</xs:id>
            <xs:processed_at>$2</xs:processed_at>
        </xs:updateInputProcessedAt>
    </format>
    <args>
        <arg evaluator="xml" expression="$func:id"/>
        <arg evaluator="xml" expression="$func:processed_at"/>
    </args>
</payloadFactory>
<call>
    <endpoint key="Database"/>
</call>
<transaction action="rollback"/>

DSS:

<data enableBoxcarring="true" enableDTP="true" name="DataService" serviceNamespace="cz.example" transports="local http https">
<config id="config">
    <property name="driverClassName">org.postgresql.Driver</property>
    <property name="url">jdbc:postgresql://url.com</property>
    <property name="username">user</property>
    <property name="password">pw</property>
</config>
<query id="insert_identifier" useConfig="config">
    <sql>INSERT INTO identifiers(entity, partner, value, pair_id) VALUES(?, ?, ?, ?)</sql>
    <param name="entity" sqlType="STRING"/>
    <param name="partner" sqlType="STRING"/>
    <param name="value" sqlType="STRING"/>
    <param name="pair_id" sqlType="INTEGER"/>
</query>
<query id="update_input_processed_at" useConfig="config" returnUpdatedRowCount="true">
    <sql>UPDATE inputs SET processed_at = ? WHERE id = ?</sql>
    <param name="processed_at" sqlType="TIMESTAMP"/>
    <param name="id" sqlType="INTEGER"/>
</query>
<operation name="createIdentifier">
    <call-query href="insert_identifier">
        <with-param name="entity" query-param="entity"/>
        <with-param name="partner" query-param="partner"/>
        <with-param name="value" query-param="value"/>
        <with-param name="pair_id" query-param="pair_id"/>
    </call-query>
</operation>
<operation name="updateInputProcessedAt">
    <call-query href="update_input_processed_at">
        <with-param name="id" query-param="id"/>
        <with-param name="processed_at" query-param="processed_at"/>
    </call-query>
</operation>
</data>
Tomáš Toka Mrázek
  • 23
  • 6
  • I wonder if you can rollback a SOAP message, it seems implausible. I'd say either use dbreport to act on the databases directly or alter the DSS implementation so you can update both tables with a single call. – Jan Feb 21 '19 at 11:14
  • It's a simplified example, the real use case is much more complex. DBReport could solve my issue, but to be honest, I don't want to mix up db mediators with dss. There are currently 20+ queries defined in DSS, it would be a mess to create dbreport for each query and define connection pool everywhere. – Tomáš Toka Mrázek Feb 21 '19 at 11:29
  • Hmm, this got me wondering: https://stackoverflow.com/questions/37203533/how-to-do-database-transaction-rollback-in-wso2-esb-or-wso2-dss seems to imply that there is some solution available to achieve your desired result. – Jan Feb 22 '19 at 12:12
  • I've read that, but it looks like it sends queries at the end of box caring in a batch. I'm gonna test it tho. – Tomáš Toka Mrázek Feb 23 '19 at 22:49

0 Answers0