I checked in the bug database, and this does not seem to have been reported, and IMHO it is definitely a bug. workarounds exist,as pointed out by me and others. still, I would like to hear from the core team whether this is recognized as a bug, and will be dealt with at some point in time. btw, I would have liked to post a script demonstrating the bug, but I do not think this is possible, due to the fact that the command-line sqlite does not handle parametric statements, or at least I don't know how to write one :)
> -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Romano > Sent: Tuesday, December 15, 2009 2:37 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Using Parameters with views > > I know the original question was about views and parameters, > but if the OP just needs the answer to this kind of query and > a view isn't mandatory, I've been able to use a parameter > with the HAVING clause. > The query could be rewritten: > > select artistname > from artist > join ITEM_ARTIST on artist.artistid = ITEM_ARTIST.artistid > group by artistname having count(ITEM_ARTIST.artistid) = > @desireditemcount > > Regards > Tim Romano > > Cariotoglou Mike wrote: > > I thought of that, and the answer is NO. > > actually, since my post, I did a little investigation : > > it is not the view that is the problem, it is the parameter > when used > > on a column that contains the COUNT function. Weird, it > seems like a genuine bug. > > > > if you replace > > > > WHERE CNT LIKE :PARAM > > > > in my original code, or in your example, it will work ! > > > > I tried this : (the subselect was originally the view) > > > > select * from > > ( > > select > > *,(select count(*) from ITEM_ARTIST where > ARTIST_id=artists.artist_id) > > CNT from ARTISTS > > ) where cnt like :a > > > > and it works. > > changing the operator from "LIKE" to "=" breaks it. > > > > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org on behalf of Darren Duncan > > Sent: Mon 12/14/2009 2:44 AM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Using Parameters with views > > > > Do bind parameters work for you if you replace the view > with a subselect? > > > > Does this work for you? > > > > select * from ( > > select t1.*, > > (select count(*) from song_artist t2 where > t1.artist_id=t2.artist_id) as CNT > > from artists > > ) where CNT=:PARAM > > > > -- Darren Duncan > > > > Cariotoglou Mike wrote: > > > >> I don't know if this has come up before, is so please > point me to the > >> right direction :) > >> > >> I believe that using parameterized queries with views does > not work > >> as expected. > >> > >> consider this (more or less self-explanatory) schema: > >> > >> create table artists(artist_id) > >> create table songs(song_id) > >> create table song_artist(song_id,artist_id) > >> > >> create view VARTISTS as > >> select t1.*, > >> (select count(*) from song_artist t2 where > >> t1.artist_id=t2.artist_id) as CNT from artists > >> > >> The sql may be a little off as I am typing this from scratch, but > >> you get the idea. > >> > >> now, if I do this: > >> > >> select * from VARTISTS where CNT=10 > >> > >> it works. > >> > >> This, however, always returns an empty result set : > >> > >> select * from VARTISTS where CNT=:PARAM, > >> > >> for any value I bind to :PARAM. > >> > >> is this known/documented ? > >> > > > > _______________________________________________ > > 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 > > > > > ---------------------------------------------------------------------- > > -- > > > > > > No virus found in this incoming message. > > Checked by AVG - www.avg.com > > Version: 8.5.427 / Virus Database: 270.14.106/2563 - Release Date: > > 12/13/09 19:47:00 > > > > > > _______________________________________________ > 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
