iZt7NDVK

1import ballerina/io;
2import ballerina/jdbc;
3import ballerina/mysql;
4
5// Create an endpoint for the first database named testdb1. Since this endpoint
6// participates in a distributed transaction, the `isXA` property should be true.
7endpoint mysql:Client testDB1 {
8    host: "localhost",
9    port: 3306,
10    name: "customerdb",
11    username: "root",
12    password: "123",
13    poolOptions: { maximumPoolSize: 5 },
14    dbOptions: { useSSL: false, isXA: true }
15};
16
17
18// Create an endpoint for the second database named testdb2. Since this endpoint
19// participates in a distributed transaction, the `isXA` property should be true.
20endpoint jdbc:Client testDB2 {
21    url: "jdbc:oracle:thin:@localhost:49161:xe",
22    username: "system",
23    password: "oracle",
24    poolOptions: { maximumPoolSize: 5, isXA: true }
25};
26
27function main(string... args) {
28    // Create the table named CUSTOMER in the first database.
29    var ret = testDB1->update("CREATE TABLE IF NOT EXISTS CUSTOMER (ID INT AUTO_INCREMENT, NAME VARCHAR(30), PRIMARY KEY (ID))");
30    handleUpdate(ret, "Create CUSTOMER table");
31    // Create the table named SALARY in the second database.
32    ret = testDB2->update("CREATE TABLE SALARY (ID INT, VALUE FLOAT)");
33    handleUpdate(ret, "Create SALARY table");
34
35    runSuccessfulScenario();
36    runFailureScenario();
37}
38
39function runSuccessfulScenario() {
40    // Begins the transaction.
41    transaction with oncommit = onCommitFunction,
42    onabort = onAbortFunction {
43    // This is the first action to participate in the transaction. It inserts
44    // customer name to the first DB and gets the generated key.
45        var retWithKey = testDB1->updateWithGeneratedKeys("INSERT INTO CUSTOMER(NAME) VALUES('Anne')", ());
46        string generatedKey;
47        match retWithKey {
48            (int, string[]) y => {
49                var (count, ids) = y;
50                generatedKey = ids[0];
51                io:println("Inserted row count: " + count);
52                io:println("Generated key: " + generatedKey);
53            }
54            error err => io:println("Insert to student table failed: "
55                    + err.message);
56        }
57
58        //Converte the returned key into integer.
59        var ret = <int>generatedKey;
60        int key = -1;
61        match ret {
62            int retInt => key = retInt;
63            error err => io:println("Converting key to string failed: "
64                    + err.message);
65        }
66        io:println("Generated key for the inserted row: " + key);
67        // This is the second action to participate in the transaction. It inserts the
68        // salary info to the second DB along with the key generated in the first DB.
69        ret = testDB2->update("INSERT INTO SALARY (ID, VALUE) VALUES (?, ?)",
70            key, 2500);
71        handleUpdate(ret, "Insert to SALARY table");
72    } onretry {
73        io:println("Retrying transaction");
74    }
75}
76
77function runFailureScenario() {
78    // Begins the transaction.
79    transaction with oncommit = onCommitFunction,
80    onabort = onAbortFunction {
81    // This is the first action to participate in the transaction. It inserts
82    // customer name to the first DB and gets the generated key.
83        var retWithKey = testDB1->updateWithGeneratedKeys("INSERT INTO CUSTOMER(NAME) VALUES('Anne')", ());
84        string generatedKey;
85        match retWithKey {
86            (int, string[]) y => {
87                var (count, ids) = y;
88                generatedKey = ids[0];
89                io:println("Inserted row count: " + count);
90                io:println("Generated key: " + generatedKey);
91            }
92            error err => io:println("Insert to student table failed: "
93                    + err.message);
94        }
95
96        //Converte the returned key into integer.
97        var ret = <int>generatedKey;
98        int key = -1;
99        match ret {
100            int retInt => key = retInt;
101            error err => io:println("Converting key to string failed: "
102                    + err.message);
103        }
104        io:println("Generated key for the inserted row: " + key);
105        // This is the second action to participate in the transaction.
106        // We have written a failing query on purpose here.
107        // Once this fails, the previous operation also should be rolled-back.
108        // To verify this, you can connect to testDB1 and check whether data is inserted.
109        ret = testDB2->update("INSERT INTO INVALID_TABLE (ID, VALUE) VALUES (?, ?)",
110            key, 2500);
111        handleUpdate(ret, "Insert to SALARY table");
112    } onretry {
113        io:println("Retrying transaction");
114    }
115}
116
117
118function onCommitFunction(string transactionId) {
119    io:println("Transaction: " + transactionId + " committed");
120}
121
122function onAbortFunction(string transactionId) {
123    io:println("Transaction: " + transactionId + " aborted");
124}
125
126// Function to handle return of the update operation.
127function handleUpdate(int|error returned, string message) {
128    match returned {
129        int retInt => io:println(message + " status: " + retInt);
130        error err => io:println(message + " failed: " + err.message);
131    }
132}