Example of Executing Transaction Control Statements

Common Database Transaction

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

func test_transaction() {
    let SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?, ?, ?)"
    let SQL_UPDATE = "UPDATE EMPLOYEE SET SALARY=? WHERE NAME=?"
    let drv = DriverManager.getDriver("opengauss").getOrThrow()
    let db = drv.open("opengauss://localhost:5432/testdb")
    try(cn = db.connect()) {
        let psInsert = cn.prepareStatement(SQL_INSERT)
        let psUpdate = cn.prepareStatement(SQL_UPDATE)

        // Creates a transaction object.
        let tx = cn.createTransaction()
        try {
            psInsert.update(SqlChar("mkyong"), SqlBinary(Array<Byte>([10])), SqlTime(DateTime.now()))

            psInsert.update(SqlChar("kungfu"), SqlBinary(Array<Byte>([20])), SqlTime(DateTime.now()))

            // error, test rollback SQLException: No value specified for parameter 3.
            psInsert.update(SqlVarchar("mkyong"), SqlBinary(Array<Byte>([1, 2, 3, 4, 5])))

            // Commits the transaction.
            tx.commit()

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

Transaction Savepoint

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

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

func test_savepoint() {
    let SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?, ?, ?)"
    let SQL_UPDATE = "UPDATE EMPLOYEE SET SALARY=? WHERE NAME=?"
    let drv = DriverManager.getDriver("opengauss").getOrThrow()
    let db = drv.open("opengauss://localhost:5432/testdb")
    try(cn = db.connect()) {
        let psInsert = cn.prepareStatement(SQL_INSERT)
        let psUpdate = cn.prepareStatement(SQL_UPDATE)

        let tx = cn.createTransaction()
        try {
            // Creates savepoint 1.
            tx.save("save1")
            psInsert.update([SqlChar("mkyong"), SqlBinary(Array<Byte>([10])), SqlTime(DateTime.now())])

            // Creates savepoint 2.
            tx.save("save2")
            psInsert.update([SqlChar("kungfu"), SqlBinary(Array<Byte>([20])), SqlTime(DateTime.now())])

            // Creates savepoint 3.
            tx.save("save3")
            psInsert.update([SqlVarchar("mkyong"), SqlBinary(Array<Byte>([1, 2, 3, 4, 5]))])

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

            // Commits the transaction.
            tx.commit()

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