Re: [sqlite] parameters in a view, disallowed? (docs issue)
Kevin Benson wrote:
At least, there's this:
http://sqlite.org/docsrc/info/7276f4a4a3e338ea187cb5e50c57e4f9806aed89?sbs=0
+ERROR_MSG {parameters are not allowed in views} {
+ The right-hand side of a CREATE VIEW (that is to say,
+ the SELECT statement that defines the view) may not contain
+ bound parameter names like "?", "?123", ":term", "@xyz", or "$var".
+}
That certainly indicates what the program's author(s) intend, so we can
say this is a documentation issue (as detailed in my 3rd post on this
thread). The syntax diagrams show that, just as parameters may appear
in select expressions, they may appear in a "create view xxx as select
...", and there is no *documentation* to the contrary.
Best regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed? (docs issue)
On Fri, Dec 21, 2012 at 12:53 AM, Larry Brasfield wrote:
> On 12/20/2012 4:10 PM, Larry Brasfield wrote:
>> > Igor Tandetnik wrote:
>> [regarding where parameters allowed, "where literals are"]
>> >> >
>> >> > How did you discern this?
>> >>
>> >> I know from experience where parameters work (SELECT, INSERT and
>> >> similar; also ATTACH as one of my projects happens to use it this way),
>> >> and where they don't (all forms of CREATE; I haven't tried ALTER but
>> I'm
>> >> 99% sure it won't work there either). I've just tested PRAGMA. I
>> briefly
>> >> scanned the rest at http://sqlite.org/lang.html to confirm that their
>> >> syntax doesn't involve expressions.
>> >
>> > I do not mean to be argumentative here, but I think the documentation on
>> > this leads to a different result. If you examine the syntax diagram for
>> > "create view"
>>
>> Why should I? I never made any claim about CREATE VIEW's syntax. In my
>> explanation, "All forms of CREATE", which includes CREATE VIEW, fall
>> under "know from experience" bucket, not "syntax doesn't involve
>> expressions" bucket.
>>
>
> You assert (believably) that SQLite has not allowed parameters in views
> for awhile, and that you know this from experience rather than the
> documentation. I assert (without refutation) that the documentation
> clearly allows parameters in a view. We have no disagreement, but it does
> appear that SQLite's operation differs from what its documentation clearly
> implies. Hence my revision of the topic.
>
> I will not suggest that you should be interested in this discrepancy, but
> others might be.
>
> Best regards,
>
At least, there's this:
http://sqlite.org/docsrc/info/7276f4a4a3e338ea187cb5e50c57e4f9806aed89?sbs=0
+ERROR_MSG {parameters are not allowed in views} {
+ The right-hand side of a CREATE VIEW (that is to say,
+ the SELECT statement that defines the view) may not contain
+ bound parameter names like "?", "?123", ":term", "@xyz", or "$var".
+}
--
--
--
--Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed? (docs issue)
On 12/20/2012 4:10 PM, Larry Brasfield wrote: > Igor Tandetnik wrote: [regarding where parameters allowed, "where literals are"] >> > >> > How did you discern this? >> >> I know from experience where parameters work (SELECT, INSERT and >> similar; also ATTACH as one of my projects happens to use it this way), >> and where they don't (all forms of CREATE; I haven't tried ALTER but I'm >> 99% sure it won't work there either). I've just tested PRAGMA. I briefly >> scanned the rest at http://sqlite.org/lang.html to confirm that their >> syntax doesn't involve expressions. > > I do not mean to be argumentative here, but I think the documentation on > this leads to a different result. If you examine the syntax diagram for > "create view" Why should I? I never made any claim about CREATE VIEW's syntax. In my explanation, "All forms of CREATE", which includes CREATE VIEW, fall under "know from experience" bucket, not "syntax doesn't involve expressions" bucket. You assert (believably) that SQLite has not allowed parameters in views for awhile, and that you know this from experience rather than the documentation. I assert (without refutation) that the documentation clearly allows parameters in a view. We have no disagreement, but it does appear that SQLite's operation differs from what its documentation clearly implies. Hence my revision of the topic. I will not suggest that you should be interested in this discrepancy, but others might be. Best regards, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed?
On 12/20/2012 5:20 PM, Simon Slavin wrote: I've understood that the optimizer can be usefully used on prepared statements before the parameters are known. An implication from this is that table names cannot be parameterized. Table names cannot be parameterized for the simple reason that, syntactically, they are not literals. The syntax only allows parameter placeholders where a literal (like 123 or 'xyz') may legitimately appear. That is not at all in contention. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed?
On 20 Dec 2012, at 8:26pm, Igor Tandetnik wrote: > I know from experience where parameters work (SELECT, INSERT and similar; > also ATTACH as one of my projects happens to use it this way), and where they > don't (all forms of CREATE; I haven't tried ALTER but I'm 99% sure it won't > work there either). I've understood that the optimizer can be usefully used on prepared statements before the parameters are known. An implication from this is that table names cannot be parameterized. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed? (docs issue)
On 12/20/2012 4:10 PM, Larry Brasfield wrote: Igor Tandetnik wrote: [regarding where parameters allowed, "where literals are"] > > How did you discern this? I know from experience where parameters work (SELECT, INSERT and similar; also ATTACH as one of my projects happens to use it this way), and where they don't (all forms of CREATE; I haven't tried ALTER but I'm 99% sure it won't work there either). I've just tested PRAGMA. I briefly scanned the rest at http://sqlite.org/lang.html to confirm that their syntax doesn't involve expressions. I do not mean to be argumentative here, but I think the documentation on this leads to a different result. If you examine the syntax diagram for "create view" Why should I? I never made any claim about CREATE VIEW's syntax. In my explanation, "All forms of CREATE", which includes CREATE VIEW, fall under "know from experience" bucket, not "syntax doesn't involve expressions" bucket. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed? (docs issue)
Igor Tandetnik wrote: [regarding where parameters allowed, "where literals are"] > > How did you discern this? I know from experience where parameters work (SELECT, INSERT and similar; also ATTACH as one of my projects happens to use it this way), and where they don't (all forms of CREATE; I haven't tried ALTER but I'm 99% sure it won't work there either). I've just tested PRAGMA. I briefly scanned the rest at http://sqlite.org/lang.html to confirm that their syntax doesn't involve expressions. I do not mean to be argumentative here, but I think the documentation on this leads to a different result. If you examine the syntax diagram for "create view", it refers to the construct 'select-stmt', which in turn refers to the construct 'expr'. (See http://sqlite.org/lang_createview.html , http://sqlite.org/lang_select.html , http://sqlite.org/lang_expr.html ) There, one alternative for expr is given as 'bind-parameter', which seems to not be defined under that name. However, the same page on 'expr' defines parameters. Since using parameters in a view appears to be allowed by the syntax diagrams, and since I saw nothing to the contrary where it logically should appear (as a semantic exception to the syntax), I went ahead and tried to use them in a view. Best regards, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed?
On 12/20/2012 2:30 PM, Larry Brasfield wrote: I believe it's pretty simple. Parameters are allowed everywhere a literal may appear, except in schema definition statements, namely various CREATE statements as well as ALTER TABLE; and also PRAGMA (not sure why). Currently, this leaves SELECT, INSERT, UPDATE, DELETE, REPLACE, ATTACH (the file name is an expression which allows parameters) and EXPLAIN (when the query being explained itself allows parameters). None of the remaining statements allow for literals in their syntax, so the issue is moot for them. How did you discern this? I know from experience where parameters work (SELECT, INSERT and similar; also ATTACH as one of my projects happens to use it this way), and where they don't (all forms of CREATE; I haven't tried ALTER but I'm 99% sure it won't work there either). I've just tested PRAGMA. I briefly scanned the rest at http://sqlite.org/lang.html to confirm that their syntax doesn't involve expressions. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed?
Igor Tandetnik wrote: On 12/20/2012 1:27 PM, Larry Brasfield wrote: > With recent versions of SQLite, a prepare call fails when there are > parameters in the SQL for a 'create view' statement. Did it ever work with any version of SQLite? Parameters in DDL statements don't make sense. Agreed with respect to DDL. However, creating a view is not quite fully DDL. A view, at least in read-only usage, can be considered a deferred or encapsulated query (or subquery). > I stumbled into > this for two reasons: The documentation for parameters and ..._prepare > does not contra-indicate such usage; it seemed perfectly sensible; and > it was useful in my application. (I have a complex view setup which I > had wanted to vary, parametrically, when the view was queried.) By what mechanism did you plan to feed parameter values into the view while SELECTing against it? What would the (hypothetical) syntax be like for such a beast? In whatever query finally uses the view, the parameters would have to substituted just as when they appear directly in a query. > Would there be any downside to allowing named parameters to remain in a > view's SQL? Ah, I think I see what you have in mind. If you do "select * from MyView", this query, even though it doesn't on the face of it appear to contain any placeholders, would be deemed to sort of incorporate by reference parameter placeholders from MyView. Yes. Specifying such a feature would sure be fun. Say, if I select against two views that each have parameter named :X, should the query be considered to have one parameter (so that a single bound value applies to both views), or two (and then how would you refer to them?) What to do with a parameter like ?1 - where do you start counting? If somebody were such a masochist as to use placement-identified parameters, they would have to be counted as if appearing where the view is expanded. This seems harmless except to those who invite the potential for confusion by using nameless parameters. > I am tempted to offer a paragraph with which to enhance the doc page on > parameters, explaining where they are allowed and where not. But I can > only guess what it should say without a lot of experimentation or study > of the code. I believe it's pretty simple. Parameters are allowed everywhere a literal may appear, except in schema definition statements, namely various CREATE statements as well as ALTER TABLE; and also PRAGMA (not sure why). Currently, this leaves SELECT, INSERT, UPDATE, DELETE, REPLACE, ATTACH (the file name is an expression which allows parameters) and EXPLAIN (when the query being explained itself allows parameters). None of the remaining statements allow for literals in their syntax, so the issue is moot for them. How did you discern this? If it is in the docs, it is scattered and not tied to the sections that are primarily about parameters. By the way, thanks for your help elsewhere. Best regards, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] parameters in a view, disallowed?
On 12/20/2012 1:27 PM, Larry Brasfield wrote: With recent versions of SQLite, a prepare call fails when there are parameters in the SQL for a 'create view' statement. Did it ever work with any version of SQLite? Parameters in DDL statements don't make sense. I stumbled into this for two reasons: The documentation for parameters and ..._prepare does not contra-indicate such usage; it seemed perfectly sensible; and it was useful in my application. (I have a complex view setup which I had wanted to vary, parametrically, when the view was queried.) By what mechanism did you plan to feed parameter values into the view while SELECTing against it? What would the (hypothetical) syntax be like for such a beast? Would there be any downside to allowing named parameters to remain in a view's SQL? Ah, I think I see what you have in mind. If you do "select * from MyView", this query, even though it doesn't on the face of it appear to contain any placeholders, would be deemed to sort of incorporate by reference parameter placeholders from MyView. Specifying such a feature would sure be fun. Say, if I select against two views that each have parameter named :X, should the query be considered to have one parameter (so that a single bound value applies to both views), or two (and then how would you refer to them?) What to do with a parameter like ?1 - where do you start counting? I am tempted to offer a paragraph with which to enhance the doc page on parameters, explaining where they are allowed and where not. But I can only guess what it should say without a lot of experimentation or study of the code. I believe it's pretty simple. Parameters are allowed everywhere a literal may appear, except in schema definition statements, namely various CREATE statements as well as ALTER TABLE; and also PRAGMA (not sure why). Currently, this leaves SELECT, INSERT, UPDATE, DELETE, REPLACE, ATTACH (the file name is an expression which allows parameters) and EXPLAIN (when the query being explained itself allows parameters). None of the remaining statements allow for literals in their syntax, so the issue is moot for them. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
