SQL Types¶

SqlValue is the closed union of every value type the library carries across the ODBC boundary. Every parameter you bind is a SqlValue. Every column you fetch is a SqlValue (wrapped in a Row or MutableRow).

type SqlValue is
  ( SqlNull
  | SqlBool
  | SqlTinyInt | SqlSmallInt | SqlInteger | SqlBigInt
  | SqlFloat
  | SqlText
  | SqlDate | SqlTime | SqlTimestamp
  | SqlDecimal )

Closed means there’s no extension point — if the library doesn’t have a variant for a SQL type, you can’t add one without modifying the library. The trade-off: exhaustive matching and no hidden dispatch, at the cost of flexibility.

The variants¶

Variant	Pony carrier	Typical SQL types
`SqlNull`	(primitive)	`NULL` in any column
`SqlBool`	`Bool`	`BOOLEAN`, `BIT`
`SqlTinyInt`	`I8`	`TINYINT` (not on Postgres)
`SqlSmallInt`	`I16`	`SMALLINT`
`SqlInteger`	`I32`	`INTEGER`, `INT`
`SqlBigInt`	`I64`	`BIGINT`
`SqlFloat`	`F64`	`REAL`, `FLOAT`, `DOUBLE PRECISION`
`SqlText`	`String val`	`CHAR`, `VARCHAR`, `TEXT`, `LONGVARCHAR`
`SqlDate`	`(year, month, day)`	`DATE`
`SqlTime`	`(hour, minute, second)`	`TIME`
`SqlTimestamp`	`(date, time, fraction)`	`TIMESTAMP`, `TIMESTAMPTZ`
`SqlDecimal`	`String val`	`NUMERIC`, `DECIMAL`

A few variants deserve attention.

`SqlFloat` absorbs all floats¶

REAL, FLOAT, and DOUBLE PRECISION all come back through SQL_C_DOUBLE as SqlFloat (F64). There’s no separate SqlReal. Precision differences on the write side are your problem to enforce at the SQL level.

`SqlText` is UTF-8 validated by default¶

Odbc.connect(dsn, validate_utf8 = true) — the default — checks every text column against UTF-8 rules on fetch. Invalid bytes raise FetchError(InvalidUtf8). Pass false only when you trust the source (or want dirty bytes anyway).

`SqlDecimal` is a `String`¶

Exact numerics (NUMERIC(20, 4) etc.) don’t fit cleanly into F64, and silent conversion loses precision. SqlDecimal stores the driver’s text representation verbatim. Round-tripping is lossless; arithmetic is your problem.

`SqlTimestamp` has a nanosecond fraction¶

class val SqlTimestamp
  let year: I16
  let month: U16
  let day: U16
  let hour: U16
  let minute: U16
  let second: U16
  let fraction: U32  // nanoseconds

Not all drivers populate fraction. Postgres sends microsecond precision, surfaced as nanoseconds with three trailing zeros.

Timezones aren’t represented directly. TIMESTAMPTZ comes back converted to the session’s time zone; select the offset separately or set the session zone explicitly if you need it.

The four integer types¶

Widths are preserved on both sides. SqlTinyInt(3) binds as SQL_TINYINT; SqlBigInt(3) binds as SQL_BIGINT. Some databases (Postgres) lack narrower column types — drivers widen for you on write.

Reads preserve width too: a SMALLINT column comes back as SqlSmallInt, not SqlBigInt. Row.int() exists so you don’t have to match on the width when you don’t care.

Round-tripping every variant¶

The sample creates a table with one column per supported variant, binds a value into each, and reads them back:

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_types")
      match \exhaustive\ conn.exec(
        "CREATE TABLE tut_types ("
          + "flag BOOLEAN, "
          + "big BIGINT, "
          + "ratio DOUBLE PRECISION, "
          + "label TEXT, "
          + "born DATE, "
          + "at TIME, "
          + "ts TIMESTAMP, "
          + "amount NUMERIC(20, 4))")
      | let _: (USize | NoRowCount) => None
      | let e: ExecError =>
        env.err.print("create: " + e.string()); conn.close(); return
      end

      match \exhaustive\ conn.prepare(
        "INSERT INTO tut_types VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
      | let stmt: Statement =>
        try
          stmt.bind_p(ParamIndex(1), SqlBool(true))?
          stmt.bind_p(ParamIndex(2), SqlBigInt(9_000_000_000))?
          stmt.bind_p(ParamIndex(3), SqlFloat(3.14159))?
          stmt.bind_p(ParamIndex(4), SqlText("hello"))?
          stmt.bind_p(ParamIndex(5), SqlDate(2026, 4, 16))?
          stmt.bind_p(ParamIndex(6), SqlTime(9, 30, 0))?
          stmt.bind_p(ParamIndex(7),
            SqlTimestamp(2026, 4, 16, 9, 30, 0))?
          stmt.bind_p(ParamIndex(8), SqlDecimal("1234.5678"))?
          stmt.execute_update_p()?
        else
          env.err.print("bind/execute failed")
        end
        stmt.close()
      | let e: PrepareError =>
        env.err.print("prepare: " + e.string())
      end

      match \exhaustive\ conn.query(
        "SELECT flag, big, ratio, label, born, at, ts, amount "
          + "FROM tut_types")
      | let cursor: Cursor =>
        match cursor.fetch()
        | let row: Row =>
          try
            let flag =
              match \exhaustive\ row.bool(ColIndex(1))?
              | let v: Bool => v.string()
              | SqlNull => "(null)"
              end
            let big =
              match \exhaustive\ row.int(ColIndex(2))?
              | let v: I64 => v.string()
              | SqlNull => "(null)"
              end
            let ratio =
              match \exhaustive\ row.float(ColIndex(3))?
              | let v: F64 => v.string()
              | SqlNull => "(null)"
              end
            let label =
              match \exhaustive\ row.text(ColIndex(4))?
              | let v: String val => v
              | SqlNull => "(null)"
              end
            let born =
              match \exhaustive\ row.date(ColIndex(5))?
              | let v: SqlDate => v.string()
              | SqlNull => "(null)"
              end
            let at =
              match \exhaustive\ row.time(ColIndex(6))?
              | let v: SqlTime => v.string()
              | SqlNull => "(null)"
              end
            let ts =
              match \exhaustive\ row.timestamp(ColIndex(7))?
              | let v: SqlTimestamp => v.string()
              | SqlNull => "(null)"
              end
            let amount =
              match \exhaustive\ row.decimal(ColIndex(8))?
              | let v: SqlDecimal => v.string()
              | SqlNull => "(null)"
              end
            env.out.print("flag:   " + flag)
            env.out.print("big:    " + big)
            env.out.print("ratio:  " + ratio)
            env.out.print("label:  " + label)
            env.out.print("born:   " + born)
            env.out.print("at:     " + at)
            env.out.print("ts:     " + ts)
            env.out.print("amount: " + amount)
          else
            env.err.print("  read error")
          end
        end
        cursor.close()
      | let e: ExecError =>
        env.err.print("query: " + e.string())
      end

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

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

./build/08-all-types

flag:   true
big:    9000000000
ratio:  3.14159
label:  hello
born:   2026-04-16
at:     09:30:00
ts:     2026-04-16 09:30:00
amount: 1234.5678

!!! note “Missing variants” Postgres lacks distinct column types for SqlTinyInt, SqlSmallInt, and SqlInteger in this sample — we’d just be watching the driver widen them to BIGINT. On MariaDB, pick TINYINT / SMALLINT / INT columns to see the narrower variants come back.

What’s next¶

So far every statement has been a literal string. Prepared Statements fixes that.

SQL Types¶

The variants¶

SqlFloat absorbs all floats¶

SqlText is UTF-8 validated by default¶

SqlDecimal is a String¶

SqlTimestamp has a nanosecond fraction¶