Error "Too many parameter values were provided" when using bind parameter syntax ?1, ?2

[DanielSWolf]

According to the SQLite documentation, there are five different ways of referencing bind parameters in an SQL statement:

1. ?, ?
2. ?1, ?2
3. :first, :second
4. @first, @second
5. $first, $second

It seems that better-sqlite3 supports the 1st and 5th syntax, but not the 2nd one:

const Database = require('better-sqlite3');

const database = new Database(':memory:');

// This works:
const statement1 = database.prepare('select ? + ?');
console.log(statement1.get(10, 20));

// This works, too:
const statement2 = database.prepare('select $first + $second');
console.log(statement2.get({ first: 10, second: 20 }));

// This throws an error:
const statement3 = database.prepare('select ?1 + ?2');
console.log(statement3.get(10, 20));

The last statement throws the following error:

RangeError: Too many parameter values were provided

Is there any chance of getting this syntax to work?

[Prinzhorn]

const statement3 = database.prepare('select ?1 + ?2');
console.log(statement3.get({1:20, 2: 30}));

[DanielSWolf]

I see! So for the ?1, ?2 syntax, an object with numeric keys is expected. Thanks a lot!

[spazmodius]

I'm pretty sure the intent in sqlite is that the binding behavior for select ?1 + ?2 is just like select ? + ?.

[Prinzhorn]

First of all I had never heard of ?NNN before this issue and I don't understand the use-case or the intend. So I'm actually curious why you're using it? Anyway:

I'm pretty sure the intent in sqlite is that the binding behavior for select ?1 + ?2 is just like select ? + ?.

Quoting: https://sqlite.org/c3ref/bind_parameter_count.html

For all forms except ?NNN, this will correspond to the number of unique parameters. If parameters of the ?NNN form are used, there may be gaps in the list.

I believe that this just works by accident in better-sqlite3. Because it doesn't work when skipping indexes, which is totally valid. I think it should be possible to use both array and object syntax in this case. Otherwise you'd have to do this (which should work in my opinion, it's as ugly as the SQL itself):

const stmt = database.prepare('select ?10 + ?20');
console.log(stmt.get([, , , , , , , , , , , , , , , , , , , , 10, , , , , , , , , , , , , , , , , , , , 20]));

So maybe @DanielSWolf you want to reopen this as a feature request to explicitly support ?NNN or explicitly not support it by throwing. Docs say

The simplest way is with anonymous parameters:
[...]
SQLite3 provides 3 different syntaxes for named parameters (@foo, :foo, and $foo), all of which are supported by better-sqlite3.

No mention of ?NNN. I doubt @JoshuaWise overlooked this, but it's entirely possible. Or maybe I'm wrong with some of my assumptions.

[Prinzhorn]

If someone wants to make a PR, relevant places seem to be

https://github.com/JoshuaWise/better-sqlite3/blob/6d05e9e4e796c0a1e0f97bd5c7a535a743caf6ef/src/objects/statement.lzz#L26-L36

and

https://github.com/JoshuaWise/better-sqlite3/blob/6d05e9e4e796c0a1e0f97bd5c7a535a743caf6ef/src/util/bind-map.lzz

[spazmodius]

My use case is straight-froward. I'd like these 2 statements to polymorphically accept the same set of parameters:

const insert = db.prepare('insert into T(name, version) values(?, ?)')
const update = db.prepare('update T set version=?2 where name=?1')

then some independent code that receives one of those can just call

insertOrUpdate.run(name, version)

[Prinzhorn]

@spazmodius thanks for the example. To me this looks fragile, is there a reason you cannot use insertOrUpdate.run({ name, version }) with named parameters? Or is it preference? Fear of garbage collector (I just made that up)?

[spazmodius]

Just preference. What you suggest is exactly what I ended up doing after realizing that ?NNN behaves indecipherably.