import ballerina/io;
import ballerinax/java.jdbc;
jdbc:Client testDB = new ({
    url: "jdbc:h2:file:./local-transactions/testdb",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5 }
});public function main() {
    var ret = testDB->update("CREATE TABLE CUSTOMER (ID INTEGER, NAME
                              VARCHAR(30))");
    handleUpdate(ret, "Create CUSTOMER table");    ret = testDB->update("CREATE TABLE SALARY (ID INTEGER, MON_SALARY FLOAT)");
    handleUpdate(ret, "Create SALARY table");
    transaction with retries = 4 {
        ret = testDB->update("INSERT INTO CUSTOMER(ID,NAME)
                                     VALUES (1, 'Anne')");
        ret = testDB->update("INSERT INTO SALARY (ID, MON_SALARY)
                                 VALUES (1, 2500)");
        if (ret is jdbc:UpdateResult) {
            io:println("Inserted count: ", ret.updatedRowCount);
            if (ret.updatedRowCount == 0) {
                abort;
            }
        } else {
            retry;
        }
    } onretry {
        io:println("Retrying transaction");
    } committed {
        io:println("Transaction committed");
    } aborted {
        io:println("Transaction aborted");
    }
    ret = testDB->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop table CUSTOMER");    ret = testDB->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop table SALARY");
    var stopRet = testDB.stop();
    if (stopRet is error) {
        io:println(stopRet.detail()["message"]);
    }
}
function handleUpdate(jdbc:UpdateResult|error returned, string message) {
    if (returned is jdbc:UpdateResult) {
        io:println(message + " status: ", returned.updatedRowCount);
    } else {
        io:println(message + " failed: ", returned.reason());
    }
}# Before running this sample, change the H2 DB connection properties as required.

Local Transactions

A Ballerina transaction is a series of data manipulation statements that must either fully complete or fully fail, thereby, leaving the system in a consistent state. This sample uses an H2 database, which is created when running the sample. Prior to running this sample, the DB connection properties need to be changed as required. Ballerina transactions are at the experimental stage, Thus, use the –experimental flag to enable them.

import ballerina/io;
import ballerinax/java.jdbc;
jdbc:Client testDB = new ({
    url: "jdbc:h2:file:./local-transactions/testdb",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5 }
});

Creates an endpoint for the H2 database. Changes the DB details before running the example.

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

Creates the tables that are required for the transaction.

    ret = testDB->update("CREATE TABLE SALARY (ID INTEGER, MON_SALARY FLOAT)");
    handleUpdate(ret, "Create SALARY table");
    transaction with retries = 4 {

The below is a transaction block. Any transacted action within the transaction block may return errors such as backend DB errors, connection pool errors etc. The user can decide whether to abort or retry based on the returned error. If you do not explicitly abort or retry a returned error, the transaction will be automatically retried until the retry count is reached and aborted. The retry count that is given via retries is the number of times the transaction is retried before it being aborted. By default, a transaction is tried three times before aborting it. Only integer literals or constants are allowed as the retry count.

        ret = testDB->update("INSERT INTO CUSTOMER(ID,NAME)
                                     VALUES (1, 'Anne')");

This is the first remote function participant of the transaction.

        ret = testDB->update("INSERT INTO SALARY (ID, MON_SALARY)
                                 VALUES (1, 2500)");
        if (ret is jdbc:UpdateResult) {
            io:println("Inserted count: ", ret.updatedRowCount);

This is the second remote function participant of the transaction.

            if (ret.updatedRowCount == 0) {
                abort;
            }
        } else {

If the transaction is forced to abort, it will roll back the transaction and exit the transaction block without retrying.

            retry;
        }

If the transaction is forced to retry, it will roll back the transaction, go to the onretry block, and retry from the beginning until the defined retry count is reached.

    } onretry {

The end curly bracket marks the end of the transaction and the transaction will be committed or rolled back at this point.

        io:println("Retrying transaction");
    } committed {

The onretry block will be executed whenever the transaction is retried until it reaches the retry count. A transaction could be retried if it fails due to an exception or throw statement or from an explicit retry statement.

        io:println("Transaction committed");
    } aborted {

Any action that needs to be performed once the transaction is committed should be added as shown below.

        io:println("Transaction aborted");
    }

Any action that needs to perform if the transaction is aborted should be added as shown below.

    ret = testDB->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop table CUSTOMER");

Drops the tables.

    ret = testDB->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop table SALARY");
    var stopRet = testDB.stop();
    if (stopRet is error) {
        io:println(stopRet.detail()["message"]);
    }
}

Closes the connection pool.

function handleUpdate(jdbc:UpdateResult|error returned, string message) {
    if (returned is jdbc:UpdateResult) {
        io:println(message + " status: ", returned.updatedRowCount);
    } else {
        io:println(message + " failed: ", returned.reason());
    }
}

This function handles the return of the update operation.

# Before running this sample, change the H2 DB connection properties as required.
# To run this sample, navigate to the directory that contains the
# `.bal` file, and execute the `ballerina run` command.
$ ballerina run  --experimental local_transactions.bal
[ballerina/http] started HTTP/WS listener 172.17.0.1:49479
Create CUSTOMER table status: 0
Create SALARY table status: 0
2018-12-04 20:16:37,124 INFO  [ballerina/log] - Created transaction: 1fba678d-d677-4301-bd1b-a7fa60664f6a
Inserted count: 1
2018-12-04 20:16:37,137 INFO  [ballerina/log] - Running 2-phase commit for transaction: 1fba678d-d677-4301-bd1b-a7fa60664f6a:$anon$.$1
Transaction committed
Drop table CUSTOMER status: 0
Drop table SALARY status: 0
[ballerina/http] started HTTP/WS listener 10.100.7.118:64298
[ballerina/http] stopped HTTP/WS listener 10.100.7.118:64298