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
