Example of Executing Transaction Control Statements

Common Database Transaction

import std.database.sql.*
import std.time.*

main() {
    let SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?, ?, ?)"
    let drv = DriverManager.getDriver("opengauss") ?? return
    let db = drv.open("opengauss://localhost:5432/testdb")
    try(cn = db.connect()) {
        let psInsert = cn.prepareStatement(SQL_INSERT)

        // Creates a transaction object.
        let tx = cn.createTransaction()
        try {
            // Insert the first data record.
            psInsert.set<String>(0, "mkyong")
            psInsert.set<Array<Byte>>(1, Array<Byte>(1, repeat: 10))
            psInsert.set<DateTime>(2, DateTime.now())
            psInsert.update()

            // Insert the second data record.
            psInsert.set<String>(0, "kungfu")
            psInsert.set<Array<Byte>>(1, Array<Byte>(1, repeat: 20))
            psInsert.set<DateTime>(2, DateTime.now())
            psInsert.update()

            // If a database is connected, test the rollback. SQLException is thrown if no value is specified for parameter 3.
            psInsert.set<String>(0, "mkyong")
            psInsert.set<Array<Byte>>(1, Array<Byte>(5, {i  => UInt8(i + 1)}))
            psInsert.update()

            // Commit a transaction.
            tx.commit()

        } catch (e1: SqlException) {
            e1.printStackTrace()
            try {
                // Roll back all transactions when an exception occurs.
                tx.rollback()
            } catch (e2: SqlException) {
                // The rollback fails.
                e2.printStackTrace()
            }
        }
    } catch (e: SqlException) {
        // The connection fails.
        e.printStackTrace()
    }
}

Transaction Savepoint

If the database transaction supports savepoints, refer to the following example:

import std.database.sql.*
import std.time.*

main() {
    let SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?, ?, ?)"
    let drv = DriverManager.getDriver("opengauss") ?? return
    let db = drv.open("opengauss://localhost:5432/testdb")
    try(cn = db.connect()) {
        let psInsert = cn.prepareStatement(SQL_INSERT)

        let tx = cn.createTransaction()
        try {
            // Create savepoint 1.
            tx.save("save1")
            psInsert.set<String>(0, "mkyong")
            psInsert.set<Array<Byte>>(1, Array<Byte>(1, repeat: 10))
            psInsert.set<DateTime>(2, DateTime.now())
            psInsert.update()

            // Create savepoint 2.
            tx.save("save2")
            psInsert.set<String>(0, "kungfu")
            psInsert.set<Array<Byte>>(1, Array<Byte>(1, repeat: 20))
            psInsert.set<DateTime>(2, DateTime.now())
            psInsert.update()

            // Create savepoint 3.
            tx.save("save3")
            psInsert.set<String>(0, "mkyong")
            psInsert.set<Array<Byte>>(1, Array<Byte>(5, {i  => UInt8(i + 1)}))
            psInsert.update()

            // Roll back to savepoint 2.
            tx.rollback("save2")

            // Commit a transaction.
            tx.commit()
        } catch (e1: SqlException) {
            e1.printStackTrace()
            try {
                // Roll back all transactions when an exception occurs.
                tx.rollback()
            } catch (e2: SqlException) {
                e2.printStackTrace()
            }
        }
    } catch (e: SqlException) {
        e.printStackTrace()
    }
}