Querying

Connection.query() runs a SELECT and returns a Cursor — a forward-only iterator over the result rows.

fun ref query(sql: String val): (Cursor | ExecError)

A Cursor has three operations:

• fetch() — next Row, EndOfRows, or FetchError
• values() — iterator adapter for Pony’s for loop
• close() — releases the SQLHSTMT

Iterating a result set

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

      match \exhaustive\ conn.query(
        "SELECT id, name FROM tut_query ORDER BY id")
      | let cursor: Cursor =>
        for result in cursor.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 name =
                match \exhaustive\ row.text(ColIndex(2))?
                | let v: String val => v
                | SqlNull => "NULL"
                end
              env.out.print("  " + id + " " + name)
            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_query")
      conn.close()

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

./build/03-query

  1 widget
  2 gadget

What’s happening

values() and the iterator contract

Cursor.values() returns a CursorIterator yielding (Row val | FetchError).

for result in cursor.values() do
  match \exhaustive\ result
  | let row: Row => ...
  | let e: FetchError => ...
  end
end

EndOfRows is what stops the iterator — it’s never yielded — so the match inside has just two arms.

!!! note “Why fetch errors are values” A previous version terminated iteration silently on fetch errors, hiding real problems. The current iterator surfaces the FetchError on the iteration where it happens, then stops. You always find out.

fetch() directly

For a single-row lookup (e.g. SELECT COUNT(*)), skip the iterator:

match cursor.fetch()
| let row: Row => ...      // got a row
| EndOfRows => ...         // empty result set
| let e: FetchError => ... // driver reported an error
end

The Transactions sample uses this pattern.

Close your cursors

cursor.close() is idempotent, but unlike Connection.close() it doesn’t auto-close as reliably on finalization — drivers hold per-statement resources (cursors, temp tables, work buffers) that you want released promptly.

What’s next

Reading Rows covers the typed accessors and SqlNull.