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
