Re: [sqlite] parameters in a view, disallowed? (docs issue)

[Larry Brasfield]

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)

[Kevin Benson]

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)

[Larry Brasfield]

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?

[Igor Tandetnik]

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?

[Simon Slavin]

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)

[Igor Tandetnik]

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)

[Larry Brasfield]

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?

[Igor Tandetnik]

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?

[Larry Brasfield]

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?

[Igor Tandetnik]

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

[sqlite] parameters in a view, disallowed?

[Larry Brasfield]

With recent versions of SQLite, a prepare call fails when there are 
parameters in the SQL for a 'create view' statement.  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.)


Upon thinking over how parameter substitution must be implemented, I can 
see that there may be difficulty with parameters in a view since its 
select appears to be stored as SQL, not subject to substitution at the 
time the view is created.  However, that seems like a feature rather 
than a bug.


Would there be any downside to allowing named parameters to remain in a 
view's SQL?


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.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters

[Baruch Burstein]

Thank you for the quick answer.

On Mon, May 14, 2012 at 4:55 PM, Richard Hipp  wrote:

> On Mon, May 14, 2012 at 9:49 AM, Baruch Burstein  >wrote:
>
> > Are text parameters bound with sqlite3_bind_text automatically escaped
> and
> > quoted, just escaped, just quoted, or neither?
> >
>
>
> Both.  And neither.
>
> The content of the parameter is not modified in any way.  It is copied
> directly into the database file, byte for byte.  This has the same effect
> as if the parameter had been both escaped and quoted and inserted into the
> SQL and then parsed, but is much faster since no conversions take place.
>
>
> >
> > --
> > Programming today is a race between software engineers striving to build
> > bigger and better idiot-proof programs, and the Universe trying to
> produce
> > bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters

[Richard Hipp]

On Mon, May 14, 2012 at 9:49 AM, Baruch Burstein wrote:

> Are text parameters bound with sqlite3_bind_text automatically escaped and
> quoted, just escaped, just quoted, or neither?
>


Both.  And neither.

The content of the parameter is not modified in any way.  It is copied
directly into the database file, byte for byte.  This has the same effect
as if the parameter had been both escaped and quoted and inserted into the
SQL and then parsed, but is much faster since no conversions take place.


>
> --
> Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] parameters

[Baruch Burstein]

Are text parameters bound with sqlite3_bind_text automatically escaped and
quoted, just escaped, just quoted, or neither?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Petite Abeille]

On Dec 23, 2011, at 2:31 PM, Chris Mets wrote:

> A parameterized view allows me to do that just fine in other SQL engines,

Well, MSSQL sports so-called parameterized views, but that's about it.

> but apparently not SQLite. 

If you insist on that approach, you could rewrite your view in term of a 
parameter table, as pointed out by Kees Nuyt somewhere else in this thread.

So:

(1) create temporary table parameter( value )

(2) create view foo as select bar from baz where bar = (select value from 
parameter)

(3) insert into parameter( value ) values( 'fubar' )

(4) select * from foo

Seems clumsy though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Peter Aronson]

Another possibility might be to create a parameters table, say:

CREATE TABLE tabparams (p1,p2,p2,p4,p5);
INSERT INTO tabparams VALUES (null,null,null,null,null);

And when creating the view, access tabparams.p1, tabparams.p2, etc. instead
of variables (with an appropriate join clause).  Then, before accessing the
view, update the tabparams table with the values you want.

This may be more expensive, depending on the optimizer, since SQLite will no
longer be dealing with a constant but rather a join.

A more elaborate method would be to program a pair of parameter_set and
parameter_get functions in C.

Integer parameter_set (integer parameter_no, value pvalue)
Value parameter_get (integer parameter_no)

The parameter values would actually be stored during the session in the
application data for the sqlite3_create_function_v2 function, and deleted at
the session end by the destroy function you pass to it.

This has the possible advantage that I think functions with constant
arguments are treated themselves as constants.

This has the disadvantage that software that didn't load your functions
would not be able to access the views at all.

Best regards,

Peter

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Chris Mets]

Hi Kees, Thank you for the input.  I had your first workaround in mind if I 
cannot get the real parameterized views to work. It still achieves the goal of 
keeping the sql logic in the database, and only make the logic in the code a 
little less elegant (string substitution instead of sql parameter 
substitution). Thanks & Best regards, Chris > From: k.n...@zonnet.nl
> To: sqlite-users@sqlite.org
> Date: Fri, 23 Dec 2011 18:03:16 +0100
> Subject: Re: [sqlite] Parameters are not allowed in views
> 
> On Fri, 23 Dec 2011 06:31:33 -0700, Chris Mets <chrism...@hotmail.com>
> wrote:
> 
> >
> > Thanks for the response.  In the solution you propose,
> > the view is no longer a parameterized view. I assume
> > you suggest putting the select statement with the
> > paramterized where clause in the code. In my question,
> > I simplified the query. In reality, it is a huge query
> > (view with left join to two sub-views). I prefer to
> > avoid embedded that SQL complexity into my code.
> > A parameterized view allows me to do that just fine
> > in other SQL engines, but apparently not SQLite.
> 
> I can think of a few workarounds:
> 
> In an application, it might be an option to create the view with fancy
> values as placeholders like par1,par2 instead of ? in the WHERE clause.
> When you want to use it, retrieve the view from sqlite_master(sql),
> strip 'CREATE VIEW viewname AS' from the sql string, replace the fancy
> values by the correct placeholders and then run it.
> 
> 
> Some SQLite browsers allow a user to right click on a text cell in a
> result set grid and select 'execute as sql' from the contextmenu. It
> will then prompt for actual values for any placeholders.
> The text can be anything, even a select statement with placeholders.
> 
> I know sqlite3explorer (by Mike Cariotoglou) allows this.
> 
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> 
> In some use cases I offered a "Search" table with end user descriptions
> and sql parameter statements which could easily be used as described
> above.
> 
> 
> -- 
> Regards,
> 
> Kees Nuyt
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Kees Nuyt]

On Fri, 23 Dec 2011 06:31:33 -0700, Chris Mets 
wrote:

>
> Thanks for the response.  In the solution you propose,
> the view is no longer a parameterized view. I assume
> you suggest putting the select statement with the
> paramterized where clause in the code. In my question,
> I simplified the query. In reality, it is a huge query
> (view with left join to two sub-views). I prefer to
> avoid embedded that SQL complexity into my code.
> A parameterized view allows me to do that just fine
> in other SQL engines, but apparently not SQLite.

I can think of a few workarounds:

In an application, it might be an option to create the view with fancy
values as placeholders like par1,par2 instead of ? in the WHERE clause.
When you want to use it, retrieve the view from sqlite_master(sql),
strip 'CREATE VIEW viewname AS' from the sql string, replace the fancy
values by the correct placeholders and then run it.


Some SQLite browsers allow a user to right click on a text cell in a
result set grid and select 'execute as sql' from the contextmenu. It
will then prompt for actual values for any placeholders.
The text can be anything, even a select statement with placeholders.

I know sqlite3explorer (by Mike Cariotoglou) allows this.

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

In some use cases I offered a "Search" table with end user descriptions
and sql parameter statements which could easily be used as described
above.


-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Simon Slavin]

On 23 Dec 2011, at 1:31pm, Chris Mets wrote:

> Thanks for the response.  In the solution you propose, the view is no longer 
> a parameterized view. I asume you suggest putting the select statement with 
> the paramterized where clause in the code. In my question, I simplified the 
> query. In reality, it is a huge query (view with left join to two sub-views). 
> I prefer to avoid embedded that SQL complexity into my code. A parameterized 
> view allows me to do that just fine in other SQL engines, but apparently not 
> SQLite. 

I know the syntax diagrams aren't definitive, but the syntax diagram on



doesn't suggest that any values are forbidden.  I have no trouble running 
_exec() on this statement:

CREATE VIEW theThrees AS SELECT second FROM myTable WHERE first=3

so I don't see why it shouldn't work.  Perhaps the problem is with binding.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Chris Mets]

Thanks for the response.  In the solution you propose, the view is no longer a 
parameterized view. I asume you suggest putting the select statement with the 
paramterized where clause in the code. In my question, I simplified the query. 
In reality, it is a huge query (view with left join to two sub-views). I prefer 
to avoid embedded that SQL complexity into my code. A parameterized view allows 
me to do that just fine in other SQL engines, but apparently not SQLite. 
> From: petite.abei...@gmail.com
> Date: Thu, 22 Dec 2011 00:00:30 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Parameters are not allowed in views
> 
> 
> On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:
> 
> > Is this truly a limitation of SQLite or am I doing something wrong?
> 
> The later. Simply create your view. Then restrict it.
> 
> In other words:
> 
> create view foo as select bar from baz
> 
> select * from foo where bar = ?
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Don V Nielsen]

Where can I learn more about "restrict it".  I'm not familiar with the
syntax for using the question mark.  Is there a specific part of the
documentation that explains it and how it works?

Thanks,
dvn

On Wed, Dec 21, 2011 at 5:00 PM, Petite Abeille wrote:

>
> On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:
>
> > Is this truly a limitation of SQLite or am I doing something wrong?
>
> The later. Simply create your view. Then restrict it.
>
> In other words:
>
> create view foo as select bar from baz
>
> select * from foo where bar = ?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Petite Abeille]

On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:

> Is this truly a limitation of SQLite or am I doing something wrong?

The later. Simply create your view. Then restrict it.

In other words:

create view foo as select bar from baz

select * from foo where bar = ?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Parameters are not allowed in views

[Chris Mets]

When I execute the following SQL statement:  select * from test2 where f2 = 
@param;
 
it prompts me correctly for a parameter value.
 
However, when I try to create a view:  create view testview as select * from 
test2 where f2 = @param;
 
I receive the following error message: Parameters are not allowed in views.
 
Other SQL databases allow me to do this. When reviewing the SQLite limitations, 
this is not called out as one of the limitations. I have tried from three 
different environment (Visual Studio, SQLite Database Browser, and SQLite3 
command line utility). All give me the same error.
 
Is this truly a limitation of SQLite or am I doing something wrong?

Thanks in advance for any help,

Chris
 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Simon Slavin]

On 26 Aug 2010, at 8:29pm, Alan Chandler wrote:

> On 26/08/10 17:38, Simon Slavin wrote:
> 
>> So someone can check it out.  Try it with a VIEW that definitely doesn't 
>> exist, or use
>> 
>> CREATE VIEW IF NOT EXISTS ...
>> 
> 
> As far as I can work it out, the statement then prepares OK - but seems 
> then to execute as a no op.

Which makes some kind of sense.  So the thing to do seems to be to use

DROP VIEW IF EXISTS ...

and then either

CREATE VIEW ...orCREATE VIEW IF NOT EXISTS ...

Both of those should always both prepare and execute.

Like you, I am not enough of an expert on the intentions behind SQLite design 
to tell whether it should be possible to prepare a statement which will 
currently fail.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 20:29, Alan Chandler wrote:
> On 26/08/10 17:38, Simon Slavin wrote:
>
>> So someone can check it out.  Try it with a VIEW that definitely doesn't 
>> exist, or use
>>
>> CREATE VIEW IF NOT EXISTS ...
>>
>
> As far as I can work it out, the statement then prepares OK - but seems
> then to execute as a no op.  Since having completed that script and then
> checking the schema with the command line sqlite3 utility, the view no
> longer exists.
>
> This seems completely wrong.  Surely it should do this check at
> *execute* time not at *prepare* time.
>


Life is just too short.  The benefit of a prepared statement is lost 
when you can't have parameters and you are only using it once anyway, so 
whilst there is a slight lengthening of the time when the database is 
Locked, its just easier to move the whole thing into a PDO::exec 
function (which in SQLITE terms I think prepares and then executes 
immediately).

I did this, and my code now works fine.

There might be a discussion about the difference between semantic and 
syntactic validation of prepared statements, but I am not expert enough 
in SQL to know what the perceived wisdom in this area is.

-- 
Alan Chandler
http://www.chandlerfamily.org.uk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 17:38, Simon Slavin wrote:

> So someone can check it out.  Try it with a VIEW that definitely doesn't 
> exist, or use
>
> CREATE VIEW IF NOT EXISTS ...
>

As far as I can work it out, the statement then prepares OK - but seems 
then to execute as a no op.  Since having completed that script and then 
checking the schema with the command line sqlite3 utility, the view no 
longer exists.

This seems completely wrong.  Surely it should do this check at 
*execute* time not at *prepare* time.

-- 
Alan Chandler
http://www.chandlerfamily.org.uk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Simon Slavin]

On 26 Aug 2010, at 4:59pm, Igor Tandetnik wrote:

> Jay A. Kreibich  wrote:
>> On Thu, Aug 26, 2010 at 04:32:11PM +0100, Simon Slavin scratched on the wall:
>>> 
>>> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
>>> 
 On 26/08/10 13:38, Simon Slavin wrote:
>>> 
>>> So you are trying to create a VIEW which does already exist.  In that
>>> case, there's no mystery about why you're getting an error message.
>> 
>> No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
>> that already exists.  Prepare != execute.
> 
> It's quite reasonable that the schema is checked at prepare time. For 
> example, you would expect an error on preparing, rather than executing, a 
> SELECT statement that mentions a non-existent table.

So someone can check it out.  Try it with a VIEW that definitely doesn't exist, 
or use 

CREATE VIEW IF NOT EXISTS ...

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Jay A. Kreibich]

On Thu, Aug 26, 2010 at 11:59:03AM -0400, Igor Tandetnik scratched on the wall:
> Jay A. Kreibich  wrote:

> >> So you are trying to create a VIEW which does already exist.  In that
> >> case, there's no mystery about why you're getting an error message.
> > 
> >  No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
> >  that already exists.  Prepare != execute.
> 
> It's quite reasonable that the schema is checked at prepare time.
> For example, you would expect an error on preparing, rather than
> executing, a SELECT statement that mentions a non-existent table.

  I suppose it is reasonable, but I can see both sides.  Doing it at
  prepare makes a lot of code simpler, but it also makes some
  techniques (like pre-preparing big sets of statements) more
  difficult.

  Given that SQLite DDL commands are transactional (a somewhat unique
  feature), there is also a fixed, "known state" for executing.  Part
  of me thinks it odd to couple preparing with executing, since that's
  different from how the data operations work.

  On the other hand, most simple things would be much more complex
  if errors were only returned on execution.

  It's too bad these can't be some kind of warning, rather than an
  all-out error.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Igor Tandetnik]

Jay A. Kreibich  wrote:
> On Thu, Aug 26, 2010 at 04:32:11PM +0100, Simon Slavin scratched on the wall:
>> 
>> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
>> 
>>> On 26/08/10 13:38, Simon Slavin wrote:
 
 On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
 
> This time it reported that the view it would have created failed because
> the table (view) already existed.
 
 I'm sorry to ask this, but can you check for us whether a VIEW by that 
 name really does exist ?  Don't forget, VIEWs get saved
 in the file, they're not part of the attachment. 
>>> 
>>> Yes it does
>> 
>> So you are trying to create a VIEW which does already exist.  In that
>> case, there's no mystery about why you're getting an error message.
> 
>  No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
>  that already exists.  Prepare != execute.

It's quite reasonable that the schema is checked at prepare time. For example, 
you would expect an error on preparing, rather than executing, a SELECT 
statement that mentions a non-existent table.
-- 
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Jay A. Kreibich]

On Thu, Aug 26, 2010 at 04:32:11PM +0100, Simon Slavin scratched on the wall:
> 
> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
> 
> > On 26/08/10 13:38, Simon Slavin wrote:
> >> 
> >> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
> >> 
> >>> This time it reported that the view it would have created failed because
> >>> the table (view) already existed.
> >> 
> >> I'm sorry to ask this, but can you check for us whether a VIEW by that 
> >> name really does exist ?  Don't forget, VIEWs get saved in the file, 
> >> they're not part of the attachment.
> > 
> > Yes it does
> 
> So you are trying to create a VIEW which does already exist.  In that
> case, there's no mystery about why you're getting an error message.

  No, he's trying the *PREPARE* a CREATE VIEW *statement* for a view
  that already exists.  Prepare != execute.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Pavel Ivanov]

>>> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
>>> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
>>> part of the attachment.
>>
>> Yes it does
>
> So you are trying to create a VIEW which does already exist.  In that case, 
> there's no mystery about why you're getting an error message.

Just to let conversation continue I'll quote Alan's earlier message in
this thread:

--
I replaced all the parameter placeholders with a quoted version of the
parameter and undertook the prepare statement again.

This time it reported that the view it would have created failed because
the table (view) already existed.
---

Note that he gets error when he prepares statement, not when executes them.


Pavel

On Thu, Aug 26, 2010 at 11:32 AM, Simon Slavin  wrote:
>
> On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:
>
>> On 26/08/10 13:38, Simon Slavin wrote:
>>>
>>> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>>>
 This time it reported that the view it would have created failed because
 the table (view) already existed.
>>>
>>> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
>>> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
>>> part of the attachment.
>>
>> Yes it does
>
> So you are trying to create a VIEW which does already exist.  In that case, 
> there's no mystery about why you're getting an error message.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Simon Slavin]

On 26 Aug 2010, at 3:36pm, Alan Chandler wrote:

> On 26/08/10 13:38, Simon Slavin wrote:
>> 
>> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>> 
>>> This time it reported that the view it would have created failed because
>>> the table (view) already existed.
>> 
>> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
>> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
>> part of the attachment.
> 
> Yes it does

So you are trying to create a VIEW which does already exist.  In that case, 
there's no mystery about why you're getting an error message.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 13:38, Simon Slavin wrote:
>
> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>
>> This time it reported that the view it would have created failed because
>> the table (view) already existed.
>
> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
> part of the attachment.

Yes it does - this VIEW outputs the transactions in the default currency 
(rather than the currency of the transaction) and the whole objective of 
my this particular php script is to update this view when the default 
currency changes.


a...@kanga:~/dev/money/db[master]$ sqlite3 money.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema dfxaction
CREATE VIEW dfxaction AS
 SELECT t.id,t.date,t.version, src, srccode, dst, 
dstcode,t.description, rno, repeat,
 CASE
 WHEN t.currency = 'GBP' THEN t.amount
 WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN 
t.srcamount
 WHEN t.dstamount IS NOT NULL AND da .currency = 'GBP' THEN 
t.dstamount
 ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS 
INTEGER)
 END AS dfamount
 FROM
 xaction AS t
 LEFT JOIN account AS sa ON t.src = sa.name
 LEFT JOIN account AS da ON t.dst = da.name
 LEFT JOIN currency ON
 t.currency != 'GBP' AND
 (t.srcamount IS NULL OR sa.currency != 'GBP') AND
 (t.dstamount IS NULL OR da.currency != 'GBP') AND
 t.currency = currency.name;
sqlite>


-- 
Alan Chandler
http://www.chandlerfamily.org.uk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 12:20, Pavel Ivanov wrote:

> Yes, "validation" happens only at the time of execution. So you are
> apparently doing something wrong and you better show your code.
>

easiest is to provide links to a copy.  I've added a .txt extension to 
all the files to stop them being executed by the web server

The key php file I am talking about is this one

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/updatedefcur.php.txt

It is trying to update the view (dfxaction) which provides transaction 
amounts in the default currency because the default currency is changing..


You will see it requires db.inc which is here

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/db.inc.txt

This initializes the global variable $db by opening the sqlite database.

The whole database schema is loaded on initial initialization by reading 
this file

The dfxaction view is created almost at the end of this file.

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/database.sql.txt

(someone asked further down the thread whether the view really exists. 
Yes it does and its not temporary).



-- 
Alan Chandler
http://www.chandlerfamily.org.uk

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Drake Wilson]

Quoth Simon Slavin , on 2010-08-26 13:38:36 +0100:
> I'm sorry to ask this, but can you check for us whether a VIEW by
> that name really does exist ?  Don't forget, VIEWs get saved in the
> file, they're not part of the attachment.

And to add to that: if you want them to merely be attached to the
database connection rather than part of the permanent schema, you can
use CREATE TEMPORARY VIEW instead.

> Simon.

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Pavel Ivanov]

> In my code, I delete the view before attempting to recreate it by
> executing the prepared statement.  Isn't that the time to validate
> whether there are semantic problems with the statement?

Yes, "validation" happens only at the time of execution. So you are
apparently doing something wrong and you better show your code.

As to your initial question: the text of view is remembered by SQLite
as is for execution in future statements, so it would need to inline
your parameters anyway. And thus you can't use parameters in the view
creation.


Pavel

On Thu, Aug 26, 2010 at 7:12 AM, Alan Chandler
 wrote:
> On 26/08/10 10:22, Alan Chandler wrote:
>> I am using PHP PDO to access sqlite and have reached a peculiar error
>> situation.  Basically its saying I can't have parameters in a CREATE
>> VIEW sql statement when I am preparing it.
>
> The plot thickens
>
> I replaced all the parameter placeholders with a quoted version of the
> parameter and undertook the prepare statement again.
>
> This time it reported that the view it would have created failed because
> the table (view) already existed.
>
> Is it correct to undertake the semantic validation at prepare time?
>
> In my code, I delete the view before attempting to recreate it by
> executing the prepared statement.  Isn't that the time to validate
> whether there are semantic problems with the statement?
>
> The reason I was pre-preparing the statement was to limit the time I
> have to lock the database with a "BEGIN IMMEDIATE" (and the later
> "COMMIT").
> --
> Alan Chandler
> http://www.chandlerfamily.org.uk
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters

[Clark Christensen]

Difficult to read, so it's unclear what your intention is.  But you mention 
JavaScript, so I'll take a stab...

Bound parameters can only contain literal values.  You can't use a bound 
parameter as an identifier for a table or column.


Do you really mean this:
...
 WHERE
users.user_id = ?
AND schedule.user_id = users.user_id
AND schedule.schedule_id = ?
AND main_detail.main_detail_id = ?
AND main_detail.property_id = properties.property_id
AND main_detail.order_id = orders.order_id
AND main_detail.activity_id = activities.activity_id
AND properties.organisation_id = organisation.organisation_id
AND activities.trade_id = trades.trade_id
AND users.user_type != ?

with bound param values:

[
this_user_id,
this_schedule_id,
this_main_detail_id,
'Subcontractor'
]


I believe that'll get you the join you're looking for.

 -Clark


- Original Message 
From: meerkat <t...@tappass.com>
To: sqlite-users@sqlite.org
Sent: Tuesday, March 17, 2009 8:00:42 AM
Subject: [sqlite] parameters

Hello,



I am trying to bind some parameters in a query but I can't do it. I have the
following (JavaScript in html page):







var rs = db.execute('SELECT distinct '+

'schedule.schedule_id, ' +

'orders.order_no, '+

'orders.order_no_iteration, '+

'organisation.organisation_name, '+

'strftime(\'%H:%M\',schedule.the_start_time,\'unixepoch\')AS the_start_time,
'+

'strftime(\'%H:%M\',schedule.the_end_time,\'unixepoch\')AS the_end_time, '+

'date(schedule.the_date,\'unixepoch\',\'localtime\') AS the_date, '+

'schedule.user_id, '+

'properties.property_id, '+

'properties.property_name_number, '+

'properties.property_address1, '+

'properties.property_address2, '+

'properties.property_town, '+

'properties.property_postcode, '+

'properties.tenant_name, '+

'properties.tenant_tel, '+

'properties.tenant_mobile, '+

'date(orders.estimated_completion_date,\'unixepoch\',\'localtime\') AS
estimiated_completion_date, '+

'orders.other_contact_tel, '+

'main_detail.quantity, '+

'trades.trade_code, '+

'activities.activity_code, '+

'activities.unit_type, '+

'main_detail.activity_description, '+

'main_detail.main_detail_id, '+

'activities.activity_id, '+

'users.user_id, '+

'users.user_type, '+

'trades.trade_id '+

'FROM schedule,users,main_detail,properties,orders,activities,organisation,
trades '+

'WHERE schedule.user_id = ? '+

'AND users.user_id = ? '+

'AND schedule.schedule_id = ? '+

'AND main_detail.main_detail_id = ? '+

'AND main_detail.property_id = ? '+

'AND main_detail.order_id = ? '+

'AND main_detail.activity_id = ? '+

'AND properties.organisation_id = ? '+

'AND activities.trade_id = ? '+

'AND users.user_type != ?',['users.user_id',this_user_id
,this_schedule_id,this_main_detail_id,'properties.property_id','orders.order
_id','activities.activity_id','organisation.organisation_id','trades.trade_i
d','Subcontractor']);



I have tried $this_schedule_id or :this_schedule_id or ?this_schedule_id or
'$this_schedule_id' or ':this_schedule_id' or '?this_schedule_id'



I get the url variables ok with:

 var this_schedule_id = getValue("my_schedule_id");

  var this_user_id = getValue("user_id");

  var this_main_detail_id = getValue("main_detail_id");

  

  document.write("SID "+ this_schedule_id);

  document.write("\\");

  document.write("uID "+ this_user_id);

  document.write("\\");

  document.write("mdID "+ this_main_detail_id);



I get an empty result set even though I know that the variables produce a
row (tested in external sql program).



If anyone can suggest what I am doing wrong I would really appreciate your
help.



meerkat









___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters

[MikeW]

meerkat  writes:

> 
> Hello,
> 
> I am trying to bind some parameters in a query but I can't do it. I have the
> following (JavaScript in html page):

SNIP

> 
> I get an empty result set even though I know that the variables produce a
> row (tested in external sql program).
> 
> If anyone can suggest what I am doing wrong I would really appreciate your
> help.
> 
> meerkat

Have you tried just a basic 1-term query first of all ?

That should establish that you are calling the API (whatever it is
for .js) correctly.

And "estimiated" is spelt "estimated" !

MikeW

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] parameters

[meerkat]

Hello,

 

I am trying to bind some parameters in a query but I can't do it. I have the
following (JavaScript in html page):

 

 

 

var rs = db.execute('SELECT distinct '+

'schedule.schedule_id, ' +

'orders.order_no, '+

'orders.order_no_iteration, '+

'organisation.organisation_name, '+

'strftime(\'%H:%M\',schedule.the_start_time,\'unixepoch\')AS the_start_time,
'+

'strftime(\'%H:%M\',schedule.the_end_time,\'unixepoch\')AS the_end_time, '+

'date(schedule.the_date,\'unixepoch\',\'localtime\') AS the_date, '+

'schedule.user_id, '+

'properties.property_id, '+

'properties.property_name_number, '+

'properties.property_address1, '+

'properties.property_address2, '+

'properties.property_town, '+

'properties.property_postcode, '+

'properties.tenant_name, '+

'properties.tenant_tel, '+

'properties.tenant_mobile, '+

'date(orders.estimated_completion_date,\'unixepoch\',\'localtime\') AS
estimiated_completion_date, '+

'orders.other_contact_tel, '+

'main_detail.quantity, '+

'trades.trade_code, '+

'activities.activity_code, '+

'activities.unit_type, '+

'main_detail.activity_description, '+

'main_detail.main_detail_id, '+

'activities.activity_id, '+

'users.user_id, '+

'users.user_type, '+

'trades.trade_id '+

'FROM schedule,users,main_detail,properties,orders,activities,organisation,
trades '+

'WHERE schedule.user_id = ? '+

'AND users.user_id = ? '+

'AND schedule.schedule_id = ? '+

'AND main_detail.main_detail_id = ? '+

'AND main_detail.property_id = ? '+

'AND main_detail.order_id = ? '+

'AND main_detail.activity_id = ? '+

'AND properties.organisation_id = ? '+

'AND activities.trade_id = ? '+

'AND users.user_type != ?',['users.user_id',this_user_id
,this_schedule_id,this_main_detail_id,'properties.property_id','orders.order
_id','activities.activity_id','organisation.organisation_id','trades.trade_i
d','Subcontractor']);

 

I have tried $this_schedule_id or :this_schedule_id or ?this_schedule_id or
'$this_schedule_id' or ':this_schedule_id' or '?this_schedule_id'

 

I get the url variables ok with:

 var this_schedule_id = getValue("my_schedule_id");

  var this_user_id = getValue("user_id");

  var this_main_detail_id = getValue("main_detail_id");

  

  document.write("SID "+ this_schedule_id);

  document.write("\\");

  document.write("uID "+ this_user_id);

  document.write("\\");

  document.write("mdID "+ this_main_detail_id);

 

I get an empty result set even though I know that the variables produce a
row (tested in external sql program).

 

If anyone can suggest what I am doing wrong I would really appreciate your
help.

 

meerkat

 

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "parameters are not allowed in views"

[Arjen Markus]

Hello Alexey,

uh, yes, that is true. But preprocessing the value of
user_id, etc. should insulate you from that sort of things, right?
Unfortunately, the Tclers' Wiki does not give a ready solution for
that. But with

[string map {\; "" \[ "" \] "" $user_id]

you can get rid of most threats, right?

Regards,

Arjen

On 2009-02-27 16:01, Alexey Pechnikov wrote:
> Hello!
> 
> On Friday 27 February 2009 17:32:36 Arjen Markus wrote:
>> This is the Tcl binding, right?
>> You could replace the variable by its value using [string map]:
>>
>> db eval [string map [list USER_ID $user_id ...] $sql_statement]
>>
>> or more directly:
>>
>> db eval \
>>  "CREATE TABLE view_report_01 AS ...
>>  WHERE u.id = $user_id
>>  ..."
>>
> 
> With SQL injection security problems as result. It's not a good decision.
> 
> 
> Best regards.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Delft Hydraulics, GeoDelft, the Subsurface and Groundwater unit of TNO and 
parts of Rijkswaterstaat have joined forces in a new independent institute for 
delta technology, Deltares. Deltares combines knowledge and experience in the 
field of water, soil and the subsurface. We provide innovative solutions to 
make living in deltas, coastal areas and river basins safe, clean and 
sustainable. 

 

DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited.
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "parameters are not allowed in views"

[Arjen Markus]

This is the Tcl binding, right?
You could replace the variable by its value using [string map]:

db eval [string map [list USER_ID $user_id ...] $sql_statement]

or more directly:

db eval \
 "CREATE TABLE view_report_01 AS ...
 WHERE u.id = $user_id
 ..."

Regards,

Arjen
On 2009-02-27 15:28, Alexey Pechnikov wrote:
> Hello!
> 
> Is there way to careate view such as
> 
> db eval {
> CREATE TABLE view_report_01 AS
> ?SELECT s.name ?AS service_name,
> ? t_l_r.cost AS cost
> ?FROM work.users ? AS u,
> ? work.user_contracts ?AS u_c,
> ? work.user_services ?AS u_s,
> ? work.services ? AS s,
> ? telephony.telephony_log_rating AS t_l_r,
> ? telephony.telephony_log ?AS t_l
> ?WHERE u.id = $user_id
> ?AND u.id = u_c.user_id
> ?AND u_c.id = u_s.owner_id
> ?AND u_s.service_id = s.id
> ?AND u_s.id = t_l_r.user_service_id
> ?AND t_l_r.log_id = t_l.rowid
> ?AND ($date_from = '' OR julianday(date2iso($date_from)) < t_l.date_start)
> ?AND ($date_to = '' OR (julianday(date2iso($date_to)) + 1) > t_l.date_start)
> }
> 
> I have params $user_id, $date_from, $date_to. There are a lot of queries to 
> this view and is needed to place params in the view.
> 
> Best regards.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Delft Hydraulics, GeoDelft, the Subsurface and Groundwater unit of TNO and 
parts of Rijkswaterstaat have joined forces in a new independent institute for 
delta technology, Deltares. Deltares combines knowledge and experience in the 
field of water, soil and the subsurface. We provide innovative solutions to 
make living in deltas, coastal areas and river basins safe, clean and 
sustainable. 

 

DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited.
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "parameters are not allowed in views"

[Alexey Pechnikov]

Hello!

On Friday 27 February 2009 18:08:19 you wrote:
> [string map {\; "" \[ "" \] "" $user_id]
>
> you can get rid of most threats, right?

We can do
set param {test' sql with some injection}
puts $param
set param [db onecolumn {select quote($param)}]
puts $param

and get result
test' sql with some injection
'test'' sql with some injection'

Well, it's good. And now query 
db eval "create view view_events as select * from events where value!=$param"
is valid.

But how about construction such as
db eval {create view view_events as select * from events where value!=#param}
were #param wiil be automatically replaced by result of {select 
quote($param)}?

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "parameters are not allowed in views"

[Alexey Pechnikov]

Hello!

Is there way to careate view such as

db eval {
CREATE TABLE view_report_01 AS
?SELECT s.name ?AS service_name,
? t_l_r.cost AS cost
?FROM work.users ? AS u,
? work.user_contracts ?AS u_c,
? work.user_services ?AS u_s,
? work.services ? AS s,
? telephony.telephony_log_rating AS t_l_r,
? telephony.telephony_log ?AS t_l
?WHERE u.id = $user_id
?AND u.id = u_c.user_id
?AND u_c.id = u_s.owner_id
?AND u_s.service_id = s.id
?AND u_s.id = t_l_r.user_service_id
?AND t_l_r.log_id = t_l.rowid
?AND ($date_from = '' OR julianday(date2iso($date_from)) < t_l.date_start)
?AND ($date_to = '' OR (julianday(date2iso($date_to)) + 1) > t_l.date_start)
}

I have params $user_id, $date_from, $date_to. There are a lot of queries to 
this view and is needed to place params in the view.

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users