Thanks Keith, I think you are right.
I can enforce only using index-based or key-based placeholders and force
the user to supply data as an array (indexed) or an object (keyed).
I think I was assuming I would allow treating index-based placeholders as
keys {"?10": "data"}, which is where the "detect valid placeholders" need
came from.
Thanks for the help.
On Mon, Jul 22, 2019 at 2:23 PM 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
