Executing Statements

Connection.exec() runs a SQL statement with no parameters and no result set you want to fetch — DDL (CREATE, DROP, ALTER) and ad-hoc DML where you only care about the affected row count.

fun ref exec(sql: String val): (RowCount | ExecError)

• RowCount is (USize | NoRowCount).
• USize is the affected row count when the driver reports one.
• NoRowCount means the driver returned SQL_NO_ROW_COUNT (-1). Most DDL lands here.
• ExecError is the error branch.

So a full match has three arms. \exhaustive\ makes the compiler enforce them.

Creating a table and inserting rows

use "lib:odbc"
use "odbc"

actor Main
  new create(env: Env) =>
    let dsn_name =
      try env.args(1)?
      else "psqlred"
      end

    match Odbc.connect(Dsn("DSN=" + dsn_name))
    | let conn: Connection =>
      // DDL: exec returns RowCount | NoRowCount | ExecError.
      match conn.exec("DROP TABLE IF EXISTS tut_exec")
      | let e: ExecError => env.err.print("drop: " + e.string())
      end

      let ct =
        "CREATE TABLE tut_exec ("
          + "id INTEGER, name VARCHAR(32))"
      match \exhaustive\ conn.exec(ct)
      | let _: USize => env.out.print("created")
      | NoRowCount => env.out.print("created (no row count)")
      | let e: ExecError =>
        env.err.print("create: " + e.string())
        conn.close()
        return
      end

      // DML: USize branch reports affected row count.
      let ins =
        "INSERT INTO tut_exec VALUES (1, 'widget'), (2, 'gadget')"
      match \exhaustive\ conn.exec(ins)
      | let n: USize => env.out.print("inserted " + n.string() + " rows")
      | NoRowCount => env.out.print("inserted (no row count)")
      | let e: ExecError => env.err.print("insert: " + e.string())
      end

      conn.exec("DROP TABLE IF EXISTS tut_exec")
      conn.close()

    | let e: ConnectError =>
      env.err.print("connect: " + e.string())
    end

./build/02-exec

created
inserted 2 rows

Matching on the three arms

match \exhaustive\ conn.exec(ct)
| let _: USize => env.out.print("created")
| NoRowCount => env.out.print("created (no row count)")
| let e: ExecError =>
  env.err.print("create: " + e.string())
  conn.close()
  return
end

On Postgres, CREATE TABLE returns NoRowCount. Other drivers might return USize 0. The branch structure makes that difference explicit.

Partial match when you don’t care

The sample’s first DROP TABLE IF EXISTS uses a non-exhaustive match:

match conn.exec("DROP TABLE IF EXISTS tut_exec")
| let e: ExecError => env.err.print("drop: " + e.string())
end

An ordinary match is allowed to be partial. A good fit when you only want to know about surprising failures.

exec is for statements without a result set

exec can run a SELECT, but the rows are discarded. For anything where you want them back, use query().

Partial variant for chaining

Every non-trivial Connection method has a _p variant that raises on error:

try
  conn.exec_p("CREATE TABLE t (id INTEGER)")?
  conn.exec_p("INSERT INTO t VALUES (1)")?
  conn.exec_p("INSERT INTO t VALUES (2)")?
else
  env.err.print("setup failed")
end

Convenient when any failure should abort a block. The else loses kind/diagnostic detail — reach for _p when you only need whether, not how.