| Subject: | Prepare/Execute binding in SELECT with HAVING |
$_STMT = $_DBH->prepare("SELECT COUNT(*) AS total, ip FROM log WHERE
time > ? GROUP BY ip HAVING total > ?");
$_STMT->execute($timestamp - $trigger_period, $trigger_val);
This doesn't fail, but it also doesn't work. Even though it appears
as if the value is getting passed through to the statement, the query
isn't being executed with the second value passed through.
Using the statment without the 2nd variable (or hard-wired) works
fine, and returns the expected results.
$_DBH->trace(5);
DBI::db=HASH(0x90058a4) trace level set to 0x0/5 (DBI @ 0x0/0) in
DBI 1.48-ithread (pid 23041)
-> prepare for DBD::SQLite::db (DBI::db=HASH(0x8ffaa64)
~0x90058a4 'SELECT COUNT(*) AS total, ip FROM log WHERE time > ? GROUP
BY ip HAVING total > ?') thr#8b2f008
dbih_setup_handle(DBI::st=HASH(0x900597c)=>DBI::st=HASH
(0x900e780), DBD::SQLite::st, 9005988, Null!)
dbih_make_com(DBI::db=HASH(0x90058a4), 8b467b8, DBD::SQLite::st,
124, 0) thr#8b2f008
dbih_setup_attrib(DBI::st=HASH(0x900e780), Err, DBI::db=HASH
(0x90058a4)) SCALAR(0x8c3ff84) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x900e780), State, DBI::db=HASH
(0x90058a4)) SCALAR(0x8c3ffe4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x900e780), Errstr, DBI::db=HASH
(0x90058a4)) SCALAR(0x8c3ffb4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x900e780), TraceLevel, DBI::db=HASH
(0x90058a4)) 5 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x900e780), FetchHashKeyName,
DBI::db=HASH(0x90058a4)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x900e780), HandleSetErr,
DBI::db=HASH(0x90058a4)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x900e780), HandleError,
DBI::db=HASH(0x90058a4)) undef (not defined)
sqlite trace: prepare statement: SELECT COUNT(*) AS total, ip FROM log
WHERE time > ? GROUP BY ip HAVING total > ? at dbdimp.c line 258
<- prepare= DBI::st=HASH(0x900597c) at ./reject_log_parse.pl line
146
-> execute for DBD::SQLite::st (DBI::st=HASH(0x900597c)~0x900e780
1140663781 100) thr#8b2f008
sqlite trace: bind into 0x900c96c: 1 => 1140663781 (0) pos 0
at dbdimp.c line 443
sqlite trace: bind into 0x900c96c: 2 => 100 (0) pos 2
at dbdimp.c line 443
sqlite trace: params left in 0x900c96c: 2 at dbdimp.c line 339
sqlite trace: bind 0 type 0 as 1140663781 at dbdimp.c line 340
sqlite trace: params left in 0x900c96c: 0 at dbdimp.c line 339
sqlite trace: bind 1 type 0 as 100 at dbdimp.c line 340
sqlite trace: Execute returned 2 cols
at dbdimp.c line 391
sqlite trace: exec ok - 0 rows, 2 cols
at dbdimp.c line 413
<- execute= '0E0' at ./reject_log_parse.pl line 147
-> fetchrow_hashref in DBD::_::st for DBD::SQLite::st (DBI::st=HASH
(0x900597c)~0x900e780) thr#8b2f008
1 -> FETCH for DBD::SQLite::st (DBI::st=HASH(0x900e780)
~INNER 'NAME') thr#8b2f008
1 <- FETCH= [ 'total' 'ip' ] at ./reject_log_parse.pl line 147
1 -> fetch for DBD::SQLite::st (DBI::st=HASH(0x900e780)~INNER)
thr#8b2f008
1 <- fetch= undef row-1 at ./reject_log_parse.pl line 147
<- fetchrow_hashref= undef row-1 at ./reject_log_parse.pl line 147
-> trace for DBD::SQLite::db (DBI::db=HASH(0x8ffaa64)~0x90058a4 0)
thr#8b2f008
<- trace= 5 at ./reject_log_parse.pl line 156
-> DESTROY for DBD::SQLite::st (DBI::st=HASH(0x900e780)~INNER)
thr#8b2f008
<- DESTROY= undef at ./reject_log_parse.pl line 131
This is on a Fedora-core 4 box, using the yum supplied package 'perl-
DBD-SQLite-1.11-1.fc4' (with perl-5.8.6-22 + perl-DBI-1.48-4 + sqlite-
3.1.2-3).