On Monday, 22 July, 2019 08:18, Richard Damon <rich...@damon-family.org>:
>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.
Well, actually, it is not valid. No clue what he is doing but it is VERY
inconsistent. The first positional argument is numbered 1 not 0, and the ?10
only has that name because it was a referenced positional. If you have a
statement like:
select ?, ?, ?, ?, ?10, ?1, ?2, ?50
you require 50 positional arguments. Assuming you bind for(x=1; x<=50; x++)
sqlite3_bind_int(stmt, x, x) then the result of the execution will be:
1 2 3 4 10 1 2 50
and positional parameters 1, 2, 10, and 50 will have names ?1, ?2, ?10, and ?50
respectively and positional parameters 3, 4, 5, 6, 7, 8, 9, and 11 through 49
will be unnamed. unnamed parameters 3 and 4 are used in the query despite not
having names. unnamed parameters 5 through 9 and 11 through 49 are not
accessed but obviously are expected to exist (otherwise why did the programmer
request that they be created?)
In other words, names that start with '?' should be treated as if the name were
null because those names have zero meaning. The only meaning is the positional
which was used to retrieve the fact that there was no valid name (valid names
will commence with : @ $).
The programmer is expected to know what they are doing. If they do not, then
the correct expectation is that an ERROR will be thrown. The programmer has
said (in the above select) that 50 positional arguments are required. If the
programmer fails to provide them, then that is a programmer error and the
computer should execute the "halt and execute programmer with extreme
prejudice" instruction. It is not for the computer to "guess" what the
programmer meant ... this is not PL/1. Down that road there be dragons.
(though PL/1 is a very nice language)
The :name prefix was originally used to embed host variable name into EXEC SQL
statements and that is how the EXEC SQL pre-processor knew what host variable
in the current scope to bind. This was maintained to allow "naming" of
positional parameters. Don't know where the @ came from (probably Sybase
TRANSACT-SQL which was appropriated by Microsoft and became SQL Server). The
$name is because that is how TCL variables are accessed and SQLite3 was
originally a TCL extension.
>> On Jul 22, 2019, at 9:22 AM, Keith Medcalf <kmedc...@dessus.com>
>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-
>>> 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
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
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
