Maybe the perl interface always binds variables as strings. This
won't work - just like the second "SELECT count(*)" query below
didn't work as one might expect.
SQLite version 3.3.0
Enter ".help" for instructions
sqlite> create table ab(a,b);
sqlite> insert into ab values(1, 2);
sqlite> select count(*) from ab group by a having count(*) > 0;
1
sqlite> select count(*) from ab group by a having count(*) > '0';
sqlite>
One workaround would be to cast your bound variable:
sqlite> select count(*) from ab group by a having count(*) > CAST ('0' AS
INTEGER);
1
Although - now that I've said all that - does the dbd interface
actually use sqlite3, or just version 2? And does it use sqlite
variables, or something else layered on top?
--- Dennis Cote <[EMAIL PROTECTED]> wrote:
> Clark Christensen wrote:
>
> >What's not clear is whether the limitation is with SQLite, DBI, or
> >DBD-SQLite, or whether later
> revisions of any has enabled placeholders at that level.
> >
> >Does sqlite3_prepare/step work with placeholders in the HAVING expression?
> >
> Clark,
>
> The syntax description for the SELECT statement (at
> http://www.sqlite.org/lang_select.html) says that the HAVING clause
> takes an expression. This is the same thing used for a WHERE clause.
> Furthermore, it looks like the parser handles both in the same way. So
> it would be unlikely that SQLite would treat it differently.
>
> The following explain output shows that SQLite does accept a variable in
> the HAVING clause.
>
> SQLite version 3.3.2
> Enter ".help" for instructions
> sqlite> create table t(a,b);
> sqlite> .explain on
> sqlite> explain select a from t group by b having count(*) > ?;
> addr opcode p1 p2 p3
> ---- -------------- ---------- ----------
> ---------------------------------
> 0 OpenVirtual 1 3 keyinfo(1,BINARY)
> 1 MemInt 0 3
> 2 MemInt 0 2
> 3 Goto 0 18
> 4 MemInt 1 3
> 5 Return 0 0
> 6 IfMemPos 2 8
> 7 Return 0 0
> 8 AggFinal 1 0 count(0)
> 9 MemLoad 1 0
> 10 Variable 1 0
> 11 Le 354 7
> 12 MemLoad 0 0
> 13 Callback 1 0
> 14 Return 0 0
> 15 MemNull 0 0
> 16 MemNull 1 0
> 17 Return 0 0
> 18 Gosub 0 15
> 19 Goto 0 47
> 20 Integer 0 0
> 21 OpenRead 0 2
> 22 SetNumColumns 0 2
> 23 Rewind 0 30
> 24 Column 0 1
> 25 Sequence 1 0
> 26 Column 0 0
> 27 MakeRecord 3 0
> 28 IdxInsert 1 0
> 29 Next 0 24
> 30 Close 0 0
> 31 Sort 1 46
> 32 Column 1 0
> 33 MemStore 5 0
> 34 MemLoad 4 0
> 35 Eq 512 40 collseq(BINARY)
> 36 MemMove 4 5
> 37 Gosub 0 6
> 38 IfMemPos 3 46
> 39 Gosub 0 15
> 40 AggStep 1 0 count(0)
> 41 Column 1 2
> 42 MemStore 0 1
> 43 MemInt 1 2
> 44 Next 1 32
> 45 Gosub 0 6
> 46 Halt 0 0
> 47 Transaction 0 0
> 48 VerifyCookie 0 1
> 49 Goto 0 20
> 50 Noop 0 0
> sqlite>
>
> Line 10 is where it load the variable to check the count before making a
> callback to return a result row.
>
> Look like the problem is in the Perl wrapper.
>
> HTH
> Dennis Cote
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
