# Queries

Two functions run sql. Both take the connection, the sql and a list of parameters, and an optional `timeout_ms`.

| Function | Gives |
|---|---|
| `Db.query(conn, sql, params, timeout_ms)` | `Result[List[Db.Row]]`, the rows of the statement; a statement without a result gives none |
| `Db.execute(conn, sql, params, timeout_ms)` | `Result[Int]`, the rows inserted, updated or deleted |

`timeout_ms` is the limit of the call in milliseconds (`-1`, the default, takes the connection's `timeout_ms`; `0` has none). A statement that runs over it fails, and `Db.timeout?(e.message)` tells that failure from others. See [Timeouts](https://agentica-lang.github.io/agentica-db/production/timeouts/index.md) and the page of each database.

A `Db.Row` is a `Map[String, Db.Value]`, in column order. Read a column with `row["name"]` and look at the `Db.Value` with `case`.

```agentica
import Db from "pkg/db/db"
import Sqlite from "pkg/db/sqlite"

def run() -> Result[Void]
  conn = try Db.open("sqlite::memory:", [Sqlite.driver()])
  try Db.execute(conn, "create table users (name text not null, age integer)", [])
  changed = try Db.execute(conn, "insert into users (name, age) values (?, ?), (?, ?)",
                           [Db.Value.Text("Ana"), Db.Value.Int(31), Db.Value.Text("Bob"), Db.Value.Null])
  puts(changed)
  rows = try Db.query(conn, "select name, age from users order by name", [])
  for row in rows
    case row["name"]
      Db.Value.Text(name) -> puts(name)
      _ -> puts("no name")
    end
  end

  Db.close(conn)
  return ok()
end

def main() -> Void
  case run()
    ok(_) -> puts("done")
    error(message) -> IO.warn(message)
  end
end
```

```output
2
Ana
Bob
done
```

## Parameters

A placeholder is `?`, and only `?`, on every database. The adapter turns it into what the database wants (`$1` for PostgreSQL) and always binds the value: a value is never put into the sql text. Write the sql as a constant of your program, and pass what comes from outside as parameters.

The number of parameters must match the number of placeholders, or the call is a bad input. A `?` inside a string, a quoted name or a comment is not a placeholder.

A list does not expand one placeholder. For `in (...)`, build the `?, ?, ?` from the length of the list, only the placeholders, and pass the values as parameters.

```agentica
import Db from "pkg/db/db"
import Sqlite from "pkg/db/sqlite"

def main() -> Void
  conn = Db.open("sqlite::memory:", [Sqlite.driver()]).unwrap!
  Db.execute(conn, "create table users (id integer primary key, name text not null)", []).unwrap!
  Db.execute(conn, "insert into users (name) values ('Ana'), ('Bob'), ('Cy'), ('Dee')", []).unwrap!
  ids = [1, 3, 4]
  holes = ids.map do |_id| "?" end
  marks = holes.join(", ")
  params = ids.map do |id| Db.Value.Int(id) end
  rows = Db.query(conn, "select name from users where id in (#{marks}) order by id", params).unwrap!
  puts(rows.size)
  Db.close(conn)
end
```

```output
3
```

## One statement

Only the first and only statement of the string runs. Two statements are an error, and so is a result with two columns of the same name: give them an alias.
