Adding array parameter support?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Adding array parameter support?

wmertens
Hi,

I am wondering if the sqlite API could be changed to accommodate array
parameters, for example when using the literal `@?`.

This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or `SELECT
* FROM t WHERE v IN (@?)`.

Apart from the ease of use benefit, I think this will help with prepared
statement reuse.

I'm not sure what the bind interface would look like, since each array
entry can have a different type. How about binding a value on the array
parameter index pushes onto the parameter array value? So to populate the
array value, call the bind functions on the same index, in array order.

It would also be nice to have a way to detect if the API supports array
parameters. How to do that, performing a query and see it fail, or is there
some function to request capabilities?

The literals could accommodate naming and numbering parameters with `@:AAA`
and `@?NNN` syntaxes.

Wout.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding array parameter support?

Darren Duncan
I agree that being able to bind Array values is very useful, but this shouldn't
need any new syntax in the SQL, the same ? should be acceptable; this shouldn't
be any different than the distinction between binding a number vs text etc. --
Darren Duncan

On 2017-09-15 11:40 PM, Wout Mertens wrote:

> I am wondering if the sqlite API could be changed to accommodate array
> parameters, for example when using the literal `@?`.
>
> This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or `SELECT
> * FROM t WHERE v IN (@?)`.
>
> Apart from the ease of use benefit, I think this will help with prepared
> statement reuse.
>
> I'm not sure what the bind interface would look like, since each array
> entry can have a different type. How about binding a value on the array
> parameter index pushes onto the parameter array value? So to populate the
> array value, call the bind functions on the same index, in array order.
>
> It would also be nice to have a way to detect if the API supports array
> parameters. How to do that, performing a query and see it fail, or is there
> some function to request capabilities?
>
> The literals could accommodate naming and numbering parameters with `@:AAA`
> and `@?NNN` syntaxes.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding array parameter support?

wmertens
Oh right, didn't really think about that one. What about binding the array
though, maybe a bind_array call that specifies that a given parameter will
be an array, and then push elements with the regular bind calls?

On Sat, Sep 16, 2017 at 8:51 AM Darren Duncan <[hidden email]>
wrote:

> I agree that being able to bind Array values is very useful, but this
> shouldn't
> need any new syntax in the SQL, the same ? should be acceptable; this
> shouldn't
> be any different than the distinction between binding a number vs text
> etc. --
> Darren Duncan
>
> On 2017-09-15 11:40 PM, Wout Mertens wrote:
> > I am wondering if the sqlite API could be changed to accommodate array
> > parameters, for example when using the literal `@?`.
> >
> > This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or
> `SELECT
> > * FROM t WHERE v IN (@?)`.
> >
> > Apart from the ease of use benefit, I think this will help with prepared
> > statement reuse.
> >
> > I'm not sure what the bind interface would look like, since each array
> > entry can have a different type. How about binding a value on the array
> > parameter index pushes onto the parameter array value? So to populate the
> > array value, call the bind functions on the same index, in array order.
> >
> > It would also be nice to have a way to detect if the API supports array
> > parameters. How to do that, performing a query and see it fail, or is
> there
> > some function to request capabilities?
> >
> > The literals could accommodate naming and numbering parameters with
> `@:AAA`
> > and `@?NNN` syntaxes.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding array parameter support?

Jens Alfke-2
In reply to this post by wmertens


> On Sep 15, 2017, at 11:40 PM, Wout Mertens <[hidden email]> wrote:
>
> This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or `SELECT
> * FROM t WHERE v IN (@?)`.

I'm definitely not an expert on the SQLite virtual machine, but I believe that changing these parameters [or at least the number of parameters] would alter the opcodes generated for the statement. Which would require recompiling the statement. So it wouldn't be feasible to make them bindings.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding array parameter support?

Richard Hipp-3
On 9/16/17, Jens Alfke <[hidden email]> wrote:

>
>
>> On Sep 15, 2017, at 11:40 PM, Wout Mertens <[hidden email]> wrote:
>>
>> This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or
>> `SELECT
>> * FROM t WHERE v IN (@?)`.
>
> I'm definitely not an expert on the SQLite virtual machine, but I believe
> that changing these parameters [or at least the number of parameters] would
> alter the opcodes generated for the statement. Which would require
> recompiling the statement. So it wouldn't be feasible to make them bindings.
>

That is correct.

For the second case ("SELECT * FROM t WHERE v IN ?") you could use the
carray table-valued function (https://www.sqlite.org/carray.html).
But for the INSERT statement, a recompile would be necessary, making
that impractical to do with binding.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding array parameter support?

Darren Duncan
In reply to this post by Jens Alfke-2
On 2017-09-16 12:01 PM, Jens Alfke wrote:
>> On Sep 15, 2017, at 11:40 PM, Wout Mertens <[hidden email]> wrote:
>>
>> This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or `SELECT
>> * FROM t WHERE v IN (@?)`.
>
> I'm definitely not an expert on the SQLite virtual machine, but I believe that changing these parameters [or at least the number of parameters] would alter the opcodes generated for the statement. Which would require recompiling the statement. So it wouldn't be feasible to make them bindings.

I agree with you.  The first use case, the INSERT, given by Wout is not feasible
and misunderstands how things work.

The second use case however is entirely reasonable, saying "SELECT * FROM t
WHERE v in ?" where that single bind parameter is an array.

Or, if a bind parameter could correspond to an entire tuple/row, which is
reasonable, then the ? could only replace the entire "VALUES(...)" and not the
field list for INTO, such as to say "INSERT INTO t ?".

Generally speaking, the only places where a parameter makes sense is anywhere a
single value literal of any type makes sense, such as anywhere one can use a
VALUES clause or whatever can go in the second argument for an IN.

-- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding array parameter support?

Darren Duncan
In reply to this post by Richard Hipp-3
On 2017-09-16 12:37 PM, Richard Hipp wrote:

> On 9/16/17, Jens Alfke <[hidden email]> wrote:
>>
>>
>>> On Sep 15, 2017, at 11:40 PM, Wout Mertens <[hidden email]> wrote:
>>>
>>> This would be handy when specifying `INSERT INTO t(@?) VALUES(@?)` or
>>> `SELECT
>>> * FROM t WHERE v IN (@?)`.
>>
>> I'm definitely not an expert on the SQLite virtual machine, but I believe
>> that changing these parameters [or at least the number of parameters] would
>> alter the opcodes generated for the statement. Which would require
>> recompiling the statement. So it wouldn't be feasible to make them bindings.
>>
>
> That is correct.
>
> For the second case ("SELECT * FROM t WHERE v IN ?") you could use the
> carray table-valued function (https://www.sqlite.org/carray.html).
> But for the INSERT statement, a recompile would be necessary, making
> that impractical to do with binding.

I would expect though that if the column list is constant (the INTO is written
normally) then the VALUES clause should be replaceable with an array-valued
parameter, and no recompile should be needed because what specific fields to
expect is known when parsing the SQL. -- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users