Bind parameters by their position numbers
saneery opened this issue · 9 comments
I think it would be great to be able to specify the parameter by their position in the list.
Sqlitex.Server.query(@db, "insert into table (name, description, category_id) values($2, $1, $1)", bind: [name, category_id])I think this is already support. See the sqlitex_test.exs file for a few examples.
It looks like the syntax uses ?1 instead of $1. Maybe we need to enhance the documentation around this feature? Was there a place you looked for this documentation and didn't find it?
Wow! This is what was needed, thanks!
I just looked in the README and I saw an example with only $1. For that is this syntax supported? If we have ?1
@saneery trying this in a local iex session, it appears that both $1 and ?1 work.
iex(20)> Sqlitex.exec(db, "CREATE TABLE t3 (id INTEGER, a INTEGER)")
:ok
iex(21)> Sqlitex.query(db, "INSERT INTO t3 VALUES ($1, $1)", bind: [2])
{:ok, []}
iex(22)> Sqlitex.query(db, "INSERT INTO t3 VALUES (?1, ?1)", bind: [3])
{:ok, []}
iex(23)> Sqlitex.query(db, "SELECT * FROM t3")
{:ok, [[id: 2, a: 2], [id: 3, a: 3]]}
Hmm..
This syntax works strange
iex(1)> Sqlitex.Server.query(:sqlite_db, "INSERT INTO t3 VALUES ($1, $2)", bind: [2, 3])
{:ok, []}
iex(2)> Sqlitex.Server.query(:sqlite_db, "INSERT INTO t3 VALUES ($2, $1)", bind: [2, 3])
{:ok, []}
iex(3)> Sqlitex.Server.query(:sqlite_db, "SELECT * FROM t3")
{:ok, [[id: 2, a: 3], [id: 2, a: 3]]}
with ?1 syntax:
iex(1)> Sqlitex.Server.query(:sqlite_db, "INSERT INTO t3 VALUES (?1, ?2)", bind: [2, 3])
{:ok, []}
iex(2)> Sqlitex.Server.query(:sqlite_db, "INSERT INTO t3 VALUES (?2, ?1)", bind: [2, 3])
{:ok, []}
iex(3)> Sqlitex.Server.query(:sqlite_db, "SELECT * FROM t3")
{:ok, [[id: 2, a: 3], [id: 3, a: 2]]}
I'm not a heavy sqlite user so this is new territory for me, but it looks like there is a difference between ? and $ in the binding code that is part of sqlite itself (see the binding documentation).
Maybe we should just stick to ?1 in our README since its usage is a bit simpler to understand. @jazzyb @obmarg either of you have insights on this?
I traced back our binding code and it looks like esqlite (the underlying erlang library that we use) only implements positional binding {see the nif code here). So if we want to support named binding we would need to start with a PR to esqlite that adds support there.
I don't have any strong use-case for supporting this or know of any strong community desire to support this so I'm going to just run through and make sure we are consistently showing the ?NNN form of parameter binding in all of our examples since that is what we support.