import ballerina/io;
import ballerina/h2;
import ballerina/sql;
h2:Client testDB1 = new({
    path: "./xa-transactions/",
    name: "Testdb1",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});
h2:Client testDB2 = new({
    path: "./xa-transactions/",
    name: "Testdb2",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});public function main() {
    var ret = testDB1->update("CREATE TABLE CUSTOMER (ID INTEGER
                    AUTO_INCREMENT, NAME VARCHAR(30))");
    handleUpdate(ret, "Create CUSTOMER table");
    ret = testDB2->update("CREATE TABLE SALARY (ID INT, VALUE FLOAT)");
    handleUpdate(ret, "Create SALARY table");
    transaction {
        var result = testDB1->update("INSERT INTO CUSTOMER(NAME)
                                        VALUES ('Anne')");
        int key = -1;
        if (result is sql:UpdateResult) {
            int count = result.updatedRowCount;
            key = <int>result.generatedKeys.ID;
            io:println("Inserted row count: " + count);
            io:println("Generated key: " + key);
        } else {
            io:println("Insert to student table failed: " + result.reason());
        }
        ret = testDB2->update("INSERT INTO SALARY (ID, VALUE) VALUES (?, ?)",
                                    key, 2500);
        handleUpdate(ret, "Insert to SALARY table");
    } onretry {
        io:println("Retrying transaction");
    } committed {
        io:println("Transaction committed");
    } aborted {
        io:println("Transaction aborted");
    }
    ret = testDB1->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop Table CUSTOMER");    ret = testDB2->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop Table SALARY");
    stopClient(testDB1);
    stopClient(testDB2);
}function onCommitFunction(string transactionId) {
    io:println("Transaction: " + transactionId + " committed");
}function onAbortFunction(string transactionId) {
    io:println("Transaction: " + transactionId + " aborted");
}
function handleUpdate(sql:UpdateResult|error returned, string message) {
    if (returned is sql:UpdateResult) {
        io:println(message + " status: " + returned.updatedRowCount);
    } else {
        io:println(message + " failed: " + returned.reason());
    }
}function stopClient(h2:Client db) {
    var stopRet = db.stop();
    if (stopRet is error) {
        io:println(stopRet.detail().message);
    }
}

XA Transactions

Ballerina XA transactions are used when the transaction is happening over two or more databases. This sample uses two H2 DBs (these are created when running the sample). Before running the sample, change the DB connection properties as required. Ballerina transactions are at experimental stage, please use –experimental flag to enable them.

import ballerina/io;
import ballerina/h2;
import ballerina/sql;
h2:Client testDB1 = new({
    path: "./xa-transactions/",
    name: "Testdb1",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});

Create an endpoint for the first database named testdb1. Since this endpoint participates in a distributed transaction, the isXA property should be true.

h2:Client testDB2 = new({
    path: "./xa-transactions/",
    name: "Testdb2",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});

Create an endpoint for the second database named testdb2. Since this endpoint participates in a distributed transaction, the isXA property should be true.

public function main() {
    var ret = testDB1->update("CREATE TABLE CUSTOMER (ID INTEGER
                    AUTO_INCREMENT, NAME VARCHAR(30))");
    handleUpdate(ret, "Create CUSTOMER table");

Create the table named CUSTOMER in the first database.

    ret = testDB2->update("CREATE TABLE SALARY (ID INT, VALUE FLOAT)");
    handleUpdate(ret, "Create SALARY table");

Create the table named SALARY in the second database.

    transaction {

Begins the transaction.

        var result = testDB1->update("INSERT INTO CUSTOMER(NAME)
                                        VALUES ('Anne')");
        int key = -1;
        if (result is sql:UpdateResult) {
            int count = result.updatedRowCount;
            key = <int>result.generatedKeys.ID;
            io:println("Inserted row count: " + count);
            io:println("Generated key: " + key);
        } else {
            io:println("Insert to student table failed: " + result.reason());
        }

This is the first remote function to participate in the transaction. It inserts customer name to the first DB and gets the generated key.

        ret = testDB2->update("INSERT INTO SALARY (ID, VALUE) VALUES (?, ?)",
                                    key, 2500);
        handleUpdate(ret, "Insert to SALARY table");
    } onretry {
        io:println("Retrying transaction");
    } committed {
        io:println("Transaction committed");
    } aborted {
        io:println("Transaction aborted");
    }

This is the second remote function to participate in the transaction. It inserts the salary info to the second DB along with the key generated in the first DB.

    ret = testDB1->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop Table CUSTOMER");

Drop the tables created for this sample.

    ret = testDB2->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop Table SALARY");
    stopClient(testDB1);
    stopClient(testDB2);
}

Stop database clients.

function onCommitFunction(string transactionId) {
    io:println("Transaction: " + transactionId + " committed");
}
function onAbortFunction(string transactionId) {
    io:println("Transaction: " + transactionId + " aborted");
}
function handleUpdate(sql:UpdateResult|error returned, string message) {
    if (returned is sql:UpdateResult) {
        io:println(message + " status: " + returned.updatedRowCount);
    } else {
        io:println(message + " failed: " + returned.reason());
    }
}

Function to handle return values of the update() remote function.

function stopClient(h2:Client db) {
    var stopRet = db.stop();
    if (stopRet is error) {
        io:println(stopRet.detail().message);
    }
}
# To run this sample, navigate to the directory that contains the
# `.bal` file and execute the `ballerina run` command.
$ ballerina run --experimental xa_transactions.bal
[ballerina/http] started HTTP/WS endpoint 172.17.0.1:53599
Create CUSTOMER table status: 0
Create SALARY table status: 0
2018-12-04 20:12:03,507 INFO  [ballerina/log] - Created transaction: aec7b091-cbac-4d61-8834-bed8e1a3c9ec
Inserted row count: 1
Generated key: 1
Insert to SALARY table status: 1
2018-12-04 20:12:03,541 INFO  [ballerina/log] - Running 2-phase commit for transaction: aec7b091-cbac-4d61-8834-bed8e1a3c9ec:$anon$.$1
Transaction committed
Drop Table CUSTOMER status: 0
Drop Table SALARY status: 0