FEAT: Support a variety of parameter types per syntax type

[rathboma]

For example, Postgresql supports:

• $1
• :paramname

SQLite: https://www.sqlite.org/c3ref/bind_blob.html

• ?
• ?NNN
• :VVV
• @vvv
• $VVV

MySQL:

• ?? - identifiers
• ? - values
  https://github.com/mysqljs/mysql#preparing-queries

More research needed on the others

[rathboma]

Comprehensive Parameter Syntax Reference

Here's a full breakdown of parameter placeholder syntax per dialect, plus the current library behavior and identified gaps.

---

Supported Dialects

PostgreSQL (psql)

Current library support: $N numbered ✓

Syntax	Type	Example
$1, $2, $N	Positional numbered	WHERE id = $1 AND status = $2

Notes:

• Parameters can be reused: WHERE user_id = $1 OR created_by = $1
• Some drivers (e.g. Npgsql) support @name as a driver-level alias that maps to $N before sending to the server — the engine itself only understands $N

Source: https://www.postgresql.org/docs/current/sql-prepare.html

---

MySQL / MariaDB (mysql)

Current library support: positional ? ✓

Syntax	Type	Example	Context
?	Positional	WHERE age > ? AND city = ?	Classic protocol (standard)
:name	Named	WHERE age > :min_age	X DevAPI only (port 33060)

Notes:

• The classic MySQL/MariaDB protocol exclusively uses ?
• :name syntax is only available via X DevAPI (.bind('name', value) chaining) — not via mysql2, node-mysql, or the standard C API
• MySQL 8.4 introduced mysql_stmt_bind_named_param() for query attributes (metadata), but SQL placeholders remain ?

Source: https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html, https://dev.mysql.com/doc/x-devapi-userguide/en/parameter-binding.html

---

SQLite (sqlite)

Current library support: positional ?, numbered ?N, named :name and @name

Syntax	Type	Example
?	Positional (anonymous)	WHERE id = ?
?NNN	Positional (explicit index)	WHERE user_id = ?1 OR editor_id = ?1
:name	Named	WHERE id = :user_id
@name	Named	WHERE id = @user_id
$name	Named	WHERE id = $user_id

Gap: $name named syntax is not currently supported in the library.

Notes:

• All five forms are supported natively at the SQLite engine level
• $name originates from Tcl variable substitution but works in all clients
• ?NNN allows the same parameter to appear in multiple places without re-binding

Source: https://sqlite.org/c3ref/bind_blob.html

---

Microsoft SQL Server (mssql)

Current library support: :name named — incorrect

Syntax	Type	Example
@name	Named	WHERE id = @user_id AND status = @status

Bug: The library currently configures : as the named prefix for MSSQL, but T-SQL's native syntax uses @. This should be named: ['@'].

Notes:

• @name is the only native syntax in T-SQL / sp_executesql
• ODBC drivers expose a ? interface that is translated to @p1, @p2, etc. internally — the engine itself only sees @name
• ADO.NET SqlCommand uses @name exclusively
• Parameter names are case-insensitive

Source: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure

---

Oracle (oracle)

Current library support: positional ? — incorrect for native Oracle clients

Syntax	Type	Example	Context
:name	Named	WHERE id = :user_id	Native OCI, python-oracledb, node-oracledb, JDBC OCI
:1, :2, :N	Positional (numeric name)	VALUES (:1, :2)	OCI OCIBindByPos()
?	Positional	WHERE id = ?	JDBC thin driver only (maps to :1, :2 internally)

Gap: Oracle's native client libraries (OCI, python-oracledb, node-oracledb) use :name or :N syntax. The library currently defaults Oracle to positional ?, which only applies to JDBC thin driver. Should be named: [':'] with optional numbered: [':'].

Notes:

• Named bind variables can appear multiple times in the same statement — each occurrence binds the same value (an optimization feature unique to Oracle)
• Mixing positional and named syntax in the same statement is not supported
• Works in SQL and PL/SQL contexts including anonymous blocks

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html, https://docs.oracle.com/en/database/oracle/oracle-database/23/lnoci/binding-and-defining-in-oci.html

---

BigQuery (bigquery)

Current library support: positional ?, @name named, @"name" quoted ✓

Syntax	Type	Example
@name	Named	WHERE corpus = @corpus
?	Positional	WHERE corpus = ?

Notes:

• A single query cannot mix named and positional parameters
• Only supported in GoogleSQL (standard SQL) mode — not legacy SQL
• Parameters are passed as typed values (STRING, INT64, FLOAT64, BOOL, etc.) in the API request

Source: https://cloud.google.com/bigquery/docs/parameterized-queries

---

Dialects Not Currently Supported (potential additions)

Cassandra (CQL)

Syntax	Type	Example
?	Positional (anonymous)	WHERE user_id = ?
:name	Named (preferred)	WHERE user_id = :user_id

Notes:

• Named bind markers are the officially recommended form
• Bind markers also work in LIMIT, TIMESTAMP, and TTL clauses

Source: https://cassandra.apache.org/doc/latest/cassandra/developing/cql/cql_singlefile.html

---

DuckDB

Syntax	Type	Example
?	Positional (auto-incremented)	WHERE name = ? AND age >= ?
$1, $2, $N	Positional (explicit index)	WHERE name = $2 AND age >= $1
$name	Named	WHERE name = $name AND age >= $min_age

Notes:

• ? and $N cannot be mixed in the same statement
• Named $name and positional $N share the same $ prefix — digits = positional, alpha = named
• JDBC client only supports ?; Python, Go, and Node.js support all forms

Source: https://duckdb.org/docs/stable/sql/query_syntax/prepared_statements

---

Snowflake

Syntax	Type	Example	Context
?	Positional	WHERE id = ? AND status = ?	JDBC, ODBC, Python (qmark), Go, Node.js
:name	Named	EXECUTE IMMEDIATE '...' USING (v_id)	Snowflake Scripting (server-side only)

Notes:

• Python connector also supports %s (format) and %(name)s (pyformat) at the driver level — these never reach the server

Source: https://docs.snowflake.com/en/sql-reference/bind-variables

---

Redshift

Identical to PostgreSQL ($1, $2, $N) at the engine level. Python redshift_connector uses %s / %(name)s at the driver level.

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_PREPARE.html

---

Summary of Current Library Gaps

Dialect	Issue
mssql	Uses :name but should use @name (T-SQL native syntax)
oracle	Uses ? but should use :name / :N (native OCI/python-oracledb syntax)
sqlite	Missing $name named syntax
—	No support for DuckDB (?, $N, $name)
—	No support for Cassandra (?, :name)
—	No support for Snowflake (?)