import ballerina/sql;
import ballerina/io;function main (string[] args) { endpoint sql:Client testDBEP {
database:sql:DB_MYSQL,
host:"localhost",
port:3306,
name:"testdb",
username:"root",
password:"root",
options:{maximumPoolSize:5}
}; int updatedRows;
var updatedRowsResult = testDBEP -> update("CREATE TABLE IF NOT EXISTS CUSTOMER (ID INT,
NAME VARCHAR(30))", null);
match updatedRowsResult {
int rows => {
updatedRows = rows;
}
sql:SQLConnectorError err => {
io:println("CUSTOMER table Creation failed:" + err.message);
return;
}
}
updatedRowsResult = testDBEP -> update("CREATE TABLE IF NOT EXISTS SALARY (ID INT,
MON_SALARY FLOAT)", null);
match updatedRowsResult {
int rows => {
updatedRows = rows;
}
sql:SQLConnectorError err => {
io:println("SALARY table Creation failed:" + err.message);
return;
}
}
boolean transactionSuccess = false;
transaction with retries=4 {
var result = testDBEP -> update("INSERT INTO CUSTOMER(ID,NAME) VALUES (1, 'Anne')", null);
result = testDBEP -> update("INSERT INTO SALARY (ID, MON_SALARY) VALUES (1, 2500)", null);
match result {
int c => {
io:println("Inserted count:" + c);
if (c == 0) {
abort;
}
}
sql:SQLConnectorError err => {
fail;
}
} transactionSuccess = true;
} onretry {
io:println("Transaction failed");
transactionSuccess = false;
}
if (transactionSuccess) {
io:println("Transaction committed");
}
var closed = testDBEP -> close();
}
TransactionsBallerina transaction is a series of data manipulation statements that must either fully complete or fully fail, leaving the system in a consistent state. This sample use MySQL DB and before running the sample copy the MySQL JDBC driver to the BALLERINA_HOME/bre/lib folder and change the DB connection properties as required. |
|
import ballerina/sql;
import ballerina/io;
|
|
function main (string[] args) {
|
|
endpoint sql:Client testDBEP {
database:sql:DB_MYSQL,
host:"localhost",
port:3306,
name:"testdb",
username:"root",
password:"root",
options:{maximumPoolSize:5}
};
|
|
int updatedRows;
|
|
var updatedRowsResult = testDBEP -> update("CREATE TABLE IF NOT EXISTS CUSTOMER (ID INT,
NAME VARCHAR(30))", null);
match updatedRowsResult {
int rows => {
updatedRows = rows;
}
sql:SQLConnectorError err => {
io:println("CUSTOMER table Creation failed:" + err.message);
return;
}
}
updatedRowsResult = testDBEP -> update("CREATE TABLE IF NOT EXISTS SALARY (ID INT,
MON_SALARY FLOAT)", null);
match updatedRowsResult {
int rows => {
updatedRows = rows;
}
sql:SQLConnectorError err => {
io:println("SALARY table Creation failed:" + err.message);
return;
}
}
|
Create the tables required for the transaction. |
boolean transactionSuccess = false;
transaction with retries=4 {
|
Here is the transaction block. You can use a Try catch here since update action can throw errors due to SQL errors, connection pool errors etc. The retry count is the number of times the transaction is tried before aborting. By default a transaction is tried three times before aborting. Only integer literals or constants are allowed for retry count. |
var result = testDBEP -> update("INSERT INTO CUSTOMER(ID,NAME) VALUES (1, 'Anne')", null);
|
This is the first action participate in the transaction. |
result = testDBEP -> update("INSERT INTO SALARY (ID, MON_SALARY) VALUES (1, 2500)", null);
match result {
int c => {
io:println("Inserted count:" + c);
|
This is the second action participate in the transaction. |
if (c == 0) {
abort;
}
}
sql:SQLConnectorError err => {
fail;
}
}
|
Anytime the transaction can be forcefully aborted using the abort keyword. |
transactionSuccess = true;
|
|
} 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("Transaction failed");
transactionSuccess = false;
}
if (transactionSuccess) {
io:println("Transaction committed");
}
|
The failed block will be executed if the transaction is failed due to an exception or a throw statement. This block will execute each time transaction is failed until retry count is reached. |
var closed = testDBEP -> close();
}
|
Close the connection pool. |
$ ballerina run transactions.bal
2018-03-03 11:29:39,803 INFO [ballerina.transactions] - Created transaction: 35a7a1b737a14322b0ce7070a9bb3090
Inserted count:1
2018-03-03 11:29:39,809 INFO [ballerina.transactions] - Committing transaction: 35a7a1b737a14322b0ce7070a9bb3090
2018-03-03 11:29:39,810 INFO [ballerina.transactions] - Running 2-phase commit for transaction: 35a7a1b737a14322b0ce7070a9bb3090
Transaction committed
|
|