But he was to be able to provide the args as {‘0’:’one, ‘10’:’ten’} and since
he only uses positional rags 1 and 10 that it be OK, i.e. positional args are
treated as key word args, and only those actually used are needed. THAT can’t
be provided by the API.
> On Jul 22, 2019, at 9:22 AM, Keith Medcalf <[email protected]> wrote:
>
>
> 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-
>> [email protected]] 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 <[email protected]>
>> wrote:
>>
>>>
>>> On Monday, 22 July, 2019 04:34, Enzo <[email protected]>
>> 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
>>> [email protected]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
