Example of Executing Database Operation Statements

Inserting Data

import std.database.sql.*

main() {
    // Example of obtaining the database connection
    let drv = DriverManager.getDriver("opengauss") ?? return
    let ds = drv.open("opengauss://testuser:testpwd@localhost:5432/testdb", [])
    let conn = ds.connect()

    // Example of inserting data
    var stmt = conn.prepareStatement("INSERT INTO test VALUES(?, ?)")
    var name = SqlVarchar("li lei")
    var age = SqlNullableInteger(12)
    var ur = stmt.update(name, age)
    println("Update Result: ${ur.rowCount} ${ur.lastInsertId}")
    name.value = "han meimei"
    age.value = 13
    ur = stmt.update(name, age)
    println("Update Result: ${ur.rowCount} ${ur.lastInsertId}")

    // Performs the following operations to return the inserted ID after data is inserted:
    let sql = "INSERT INTO test (name, age) VALUES (?,?) RETURNING id, name"
    try (stmt = conn.prepareStatement(sql)) {
        var name = SqlVarchar("li hua")
        var age = SqlNullableInteger(12)
        let qr = stmt.query(name, age)
        let id = SqlInteger(0)
        while (qr.next(id, name)) {
            println("id = ${id.value}, name=${name.value}")
        }
    } catch (e: Exception) {
        e.printStackTrace()
    }
    stmt.close()
}

Querying Data

import std.database.sql.*

main() {
    // Example of obtaining the database connection
    let drv = DriverManager.getDriver("opengauss") ?? return
    let ds = drv.open("opengauss://testuser:testpwd@localhost:5432/testdb", [])
    let conn = ds.connect()

    // Example of the query operation
    var stmt = conn.prepareStatement("select * from test where name = ?")
    var name = SqlNullableVarchar("li lei")
    let id = SqlInteger(0)
    let qr = stmt.query(name)
    var age = SqlNullableInteger(0)
    while (qr.next(id, name, age)) {
        println("id = ${id.value}, name = ${name.value}, age=${age.value}")
    }
    stmt.close()
}

Updating Data

import std.database.sql.*

main() {
    // Example of obtaining the database connection
    let drv = DriverManager.getDriver("opengauss") ?? return
    let ds = drv.open("opengauss://testuser:testpwd@localhost:5432/testdb", [])
    let conn = ds.connect()

    // Example of the update operation
    var stmt = conn.prepareStatement("update test set age = ? where name = ?")
    var age = SqlNullableInteger(15)
    var name = SqlNullableVarchar("li lei")
    var ur = stmt.update(age, name)
    println("Update Result: ${ur.rowCount} ${ur.lastInsertId}")
    stmt.close()
}

Deleting Data

import std.database.sql.*

main() {
    // Example of obtaining the database connection
    let drv = DriverManager.getDriver("opengauss") ?? return
    let ds = drv.open("opengauss://testuser:testpwd@localhost:5432/testdb", [])
    let conn = ds.connect()

    // Example of the deletion operation
    var stmt = conn.prepareStatement("delete from test where name = ?")
    var name = SqlNullableVarchar("li lei")
    var ur = stmt.update(name)
    println("Update Result: ${ur.rowCount} ${ur.lastInsertId}")
    stmt.close()
}