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

[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

[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