I don't see what is so hard. APSW does it:
>python
Python 2.7.16 (v2.7.16:413a49145e, Mar 4 2019, 01:30:55) [MSC v.1500 32 bit
(Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('')
>>> db.execute('select ?, ?10;', ('one', 'ten')).fetchone()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
return self.cursor().execute(*args, **kwargs)
apsw.BindingsError: Incorrect number of bindings supplied. The current
statement uses 10 and there are 2 supplied. Current offset is 0
>>>
The statement required 10 parameters and only 2 were supplied. ERROR!
Internally it uses sqlite3_bind_parameter_count to find out how many parameters
need binding and requires that the number of "parameters" passed match the
number of parameters "expected" when binding positionally.
>>> db.execute('select :1, :10;', {'2': 'one', '10': 'ten'}).fetchone()
Row(_0=None, _1=u'ten')
>>> db.execute('select :1, :10;', {'1': 'one', '10': 'ten'}).fetchone()
Row(_0=u'one', _1=u'ten')
>>>
When binding by name, it looks up the names in the provided dictionary and
binds those it finds.
You can also bind named parameters to a positional list (in which case it is
the programmers job to keep track of what they are doing) since a named
parameter is merely syntactic sugar on top of positional parameters:
>>> db.execute('select :1, :10;', ('one', 'two')).fetchone()
Row(_0=u'one', _1=u'two')
>>>
>>>
It is not very difficult. You call sqlite3_bind_parameter_count. Then if you
are binding positionally you make sure there are sufficient positional
parameters provided to bind them all. It you are binding by name, you cycle
through the parameters, get the name, and then bind the given named parameter
to that parameter. apsw chooses to ignore missing items when binding by name
-- sounds like you simply want to ERROR instead ...
The only issue I can see is that when requesting the name of an positional
parameter that has no name it returns null rather than the positional name,
however, this is pretty easy to work around in pretty much any programming
language ... that is if sqlite3_parameter_name(stmt, x) return null then the
name is a ? followed by x in decimal (sprintf("?%d", x) or thereabouts having
appropriate accomodations for the language syntax and buffer safety, etc)
However, you cannot bind to positional parameters by name:
>>> db.execute('select ?1, ?10;', {'2': 'one', '10': 'ten'}).fetchone()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
return self.cursor().execute(*args, **kwargs)
apsw.BindingsError: Binding 1 has no name, but you supplied a dict (which only
has names).
*the builtin python sqlite3 wrapper does the same thing I expect (though I have
never actually looked) but it is somewhat braindead otherwise, so I don't use
it and haven't bothered to test what it does in these circumstances. Though my
expectation is that it would behave somewhat similarly.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of test user
>Sent: Monday, 22 July, 2019 06:36
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] Determining valid statement
>placeholders.
>
>I understand the problem, but I want my library to be able to detect
>the
>problem programatically.
>
>Currently if a user mixes index-based and key-based placeholders, the
>only
>thing a library using SQLite can do us run the query with unbound
>placeholders set to null (the SQLite default).
>
>Id like the ability to instead throw an error in this case.
>
>
>I think SQLite internally knows how many placeholders are in the
>query at
>parse time.
>
>My question is how can I get the data via the API, or if it would be
>considered to add a function to get this data?
>
>
>On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <kmedc...@dessus.com>
>wrote:
>
>>
>> On Monday, 22 July, 2019 04:34, Enzo <contact.enz...@gmail.com>
>wrote:
>>
>> >It is not the same information.
>>
>> >I want to be able to determine "has the user bound all placeholder
>> >values with data?".
>>
>> >The user provides this as input:
>>
>> >query="SELECT ?, ?10"
>> >data={"0": "data-a", "10": "data-b"}
>>
>> >Note: This IS valid, as they have provided all data for
>placeholders.
>>
>> >Using the APIs you mentioned:
>>
>> >count=10
>> >name(10) = "?10"
>>
>> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9,
>10]
>> >are placeholders in the query.
>>
>> >As you can see, only indexes [1, 10] are valid placeholders in the
>> >query.
>>
>> >So, as far as I can tell, it is not possible to get this from the
>> >API:
>>
>> >query="SELECT ?, ?10"
>> >valid_placeholder_indexes=[1, 10]
>>
>> >It is only possible to get this:
>>
>> >query="SELECT ?, ?10"
>> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>>
>> The fundamental issue is that you are confusing POSITIONAL
>parameters with
>> NAMED parameters. ?nnn where nnn is numeric describe POSITIONAL
>> parameters, so in the query SELECT ?, ?10 you are using positional
>> parameter 1 and positional parameter 10. That implies that there
>are 10
>> positional parameters. The fact that you are only using those
>positional
>> parameters sparsely (only using positional parameter 1 and
>positional
>> parameter 10) does not mean that you are using two parameters, it
>means
>> that you are using 10, but only referencing 2 of them.
>>
>> Said another way, you are declaring a function that looks like
>this:
>>
>> int stmt(a, b, c, d, e, f, g, h, i, j)
>> return a + j
>>
>> and expecting to be able to call it as
>>
>> z = stmt(1, 6)
>>
>> expecting some magic to know that the second parameter is really
>parameter
>> 10.
>>
>> https://www.sqlite.org/lang_expr.html#varparam
>>
>> There may be many positional parameters (like 999 in the default
>build)
>> and sqlite3_bind_parameter_count returns the "index" of the
>greatest
>> parameter number used in the statement. Having created 10
>parameters
>> sqlite has absolutely no clue that you happen to be using only
>parameter 1
>> and parameter 10. If you only needed 2 parameters you should have
>only
>> created 2, not 10. That is what NAMED parameters are for.
>>
>> If you change from using positional (?) parameters to using named
>(:) or
>> (@) what happens?
>>
>> select :1, :10; should only create 2 parameters named :1 and :10
>...
>>
>> --
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
