Reusing Statements

The point of preparing is to reuse. A single Statement can execute many times: rebind, run, repeat. For a batch insert of a thousand rows, that’s one prepare and a thousand executes instead of a thousand parse-and-plans.

The reuse pattern

Two operations drive the lifecycle:

• Rebind and re-execute — after execute_update(), the statement is back in “ready to bind”. Bind new values and execute again.
• close_cursor() — after execute() (SELECT) the statement has an open cursor. close_cursor() closes it and returns to “ready to bind”.

A batch of inserts, followed by a SELECT

The sample prepares an INSERT, executes it three times, then prepares a SELECT and iterates:

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 =>
      conn.exec("DROP TABLE IF EXISTS tut_reuse")
      match \exhaustive\ conn.exec(
        "CREATE TABLE tut_reuse (id INTEGER, label VARCHAR(32))")
      | let _: (USize | NoRowCount) => None
      | let e: ExecError =>
        env.err.print("create: " + e.string()); conn.close(); return
      end

      // Batch insert: prepare once, re-bind each row.
      let items: Array[(I32, String val)] val =
        [(1, "alpha"); (2, "bravo"); (3, "charlie")]

      match \exhaustive\ conn.prepare(
        "INSERT INTO tut_reuse (id, label) VALUES (?, ?)")
      | let stmt: Statement =>
        for (id, label) in items.values() do
          try
            stmt.bind_p(ParamIndex(1), SqlInteger(id))?
            stmt.bind_p(ParamIndex(2), SqlText(label))?
            stmt.execute_update_p()?
          else
            env.err.print("insert " + id.string() + " failed")
          end
        end

        stmt.close()
      | let e: PrepareError =>
        env.err.print("prepare: " + e.string())
      end

      // Now prepare a SELECT and fetch rows back using the same pattern:
      // execute() opens a cursor, values() iterates, close_cursor() resets.
      match \exhaustive\ conn.prepare(
        "SELECT id, label FROM tut_reuse ORDER BY id")
      | let stmt: Statement =>
        match \exhaustive\ stmt.execute()
        | Executed =>
          for result in stmt.values() do
            match \exhaustive\ result
            | let row: Row =>
              try
                let id =
                  match \exhaustive\ row.int(ColIndex(1))?
                  | let v: I64 => v.string()
                  | SqlNull => "NULL"
                  end
                let label =
                  match \exhaustive\ row.text(ColIndex(2))?
                  | let v: String val => v
                  | SqlNull => "NULL"
                  end
                env.out.print(id + " " + label)
              end
            | let e: FetchError =>
              env.err.print("fetch: " + e.string())
            end
          end
          stmt.close_cursor()
        | let e: ExecError =>
          env.err.print("execute: " + e.string())
        end
        stmt.close()
      | let e: PrepareError =>
        env.err.print("prepare: " + e.string())
      end

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

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

./build/06-prepared-reuse

1 alpha
2 bravo
3 charlie

What to notice

Binding marks a parameter dirty

The library tracks which parameters have been rebound since the last execute and only pushes changed ones to the driver — matters when parameters carry large text or binary. Invisible to callers: just bind what changes and execute.

Partial variants make batches terse

for (id, label) in items.values() do
  try
    stmt.bind_p(ParamIndex(1), SqlInteger(id))?
    stmt.bind_p(ParamIndex(2), SqlText(label))?
    stmt.execute_update_p()?
  else
    env.err.print("insert " + id.string() + " failed")
  end
end

One try/else per iteration. Any step erroring drops into else; the loop moves on. Wrap the whole for in a single try to abort the batch on first failure.

Trade-off: the else branch doesn’t know which error fired. For identical inserts the item value tells you enough. When you need the kind, use the non-partial bind() / execute_update().

close_cursor() vs close()

• close_cursor() — closes any open cursor, unbinds columns, leaves the statement ready to bind and re-execute.
• close() — frees the SQLHSTMT. Idempotent. After this the statement is done.

The sample calls close_cursor() after the SELECT loop, then close() when done.

Prepare once, use many — but only on the same connection

Statement is tied to the Connection it was prepared on. Close the connection and the statement is useless. Both are ref (non-sendable), so you can’t send a prepared statement across actors.

For cross-actor reuse: either each actor owns its own DbSession and prepares its own statements, or you funnel all database work through a single actor owning the connection. DbSession shows the second pattern.

What’s next

So far every Statement has been used for bind / execute / fetch. Prepare-Time Metadata shows what else a Statement will tell you — parameter types, column types, nullability — before any value is bound.