Reading Rows

A Row is an immutable (val) snapshot of one result row, returned from Cursor.fetch(), the Cursor.values() iterator, or Statement.fetch().

Row is val and therefore sendable: fetch in one actor, send to another, keep fetching. Each fetch() allocates a fresh Row.

Typed accessors

Each accessor takes a 1-based ColIndex and returns the column value or SqlNull. Each ?-raises on two conditions: out-of-range index, or type mismatch.

fun int(i: ColIndex):       (I64       | SqlNull) ?
fun float(i: ColIndex):     (F64       | SqlNull) ?
fun text(i: ColIndex):      (String val | SqlNull) ?
fun bool(i: ColIndex):      (Bool      | SqlNull) ?
fun date(i: ColIndex):      (SqlDate   | SqlNull) ?
fun time(i: ColIndex):      (SqlTime   | SqlNull) ?
fun timestamp(i: ColIndex): (SqlTimestamp | SqlNull) ?
fun decimal(i: ColIndex):   (SqlDecimal | SqlNull) ?

Also column(i)? for the polymorphic SqlValue and is_null(i)? / size() for meta-inspection.

int() widens

int() accepts any of SqlTinyInt, SqlSmallInt, SqlInteger, SqlBigInt and widens to I64. Most callers don’t care about the original width. When you do (bit-width preservation, range checks), use column(i)? and match on the concrete variant.

bool() is forgiving

Drivers disagree about how boolean columns come back. row.bool() accepts:

• SqlBool
• any integer (non-zero → true)
• SqlText containing "1", "0", "t", "f", "true", "false" (case-insensitive)

Anything else raises the partial function’s error.

A worked example

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_rows")
      match \exhaustive\ conn.exec(
        "CREATE TABLE tut_rows "
          + "(id INTEGER, name VARCHAR(32), price DOUBLE PRECISION)")
      | let _: (USize | NoRowCount) => None
      | let e: ExecError =>
        env.err.print("create: " + e.string()); conn.close(); return
      end
      match \exhaustive\ conn.exec(
        "INSERT INTO tut_rows VALUES "
          + "(1, 'widget', 9.99), "
          + "(2, NULL, 14.50), "
          + "(3, 'gadget', NULL)")
      | let _: (USize | NoRowCount) => None
      | let e: ExecError =>
        env.err.print("insert: " + e.string()); conn.close(); return
      end

      match \exhaustive\ conn.query(
        "SELECT id, name, price FROM tut_rows ORDER BY id")
      | let cursor: Cursor =>
        for result in cursor.values() do
          match \exhaustive\ result
          | let row: Row =>
            try
              // row.int() widens any SQL integer to I64.
              let id =
                match \exhaustive\ row.int(ColIndex(1))?
                | let v: I64 => v.string()
                | SqlNull => "NULL"
                end
              // row.text() returns String val or SqlNull.
              let name =
                match \exhaustive\ row.text(ColIndex(2))?
                | let v: String val => v
                | SqlNull => "(null)"
                end
              // row.float() returns F64 or SqlNull.
              let price =
                match \exhaustive\ row.float(ColIndex(3))?
                | let v: F64 => v.string()
                | SqlNull => "(null)"
                end
              env.out.print(
                id + " | " + name + " | " + price)
            else
              env.err.print("  column read error")
            end
          | let e: FetchError =>
            env.err.print("  fetch: " + e.string())
          end
        end
        cursor.close()
      | let e: ExecError =>
        env.err.print("query: " + e.string())
      end

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

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

./build/04-rows

1 | widget | 9.99
2 | (null) | 14.5
3 | gadget | (null)

The SqlNull primitive

SqlNull is a singleton primitive, part of the SqlValue union and of every typed accessor’s return union. There’s no implicit nullable value and no None — a column is either its declared type or SqlNull, and the match forces you to say which.

let id =
  match \exhaustive\ row.int(ColIndex(1))?
  | let v: I64 => v.string()
  | SqlNull => "NULL"
  end

The library leans hard on the type system here: you can’t accidentally read a nullable column as if it weren’t.

Error handling

Accessors wrap in a try:

try
  let id = ...
  let name = ...
  env.out.print(id + " | " + name)
else
  env.err.print("column read error")
end

That catches out-of-range or wrong-type ? errors. Indices and types should match the SELECT’s column list, so hitting else means schema drift or a bug. Wrap the whole row read, not each accessor — distinguishing “column 1” from “column 3” at runtime is rarely useful.

What’s next

SQL Types covers the full SqlValue union and the edge cases around SqlDecimal, SqlTimestamp, and text encoding.