Here's my query:
SELECT TO_CHAR(SYSDATE, 'HH24') AS TIME
FROM DUAL
WHERE TO_CHAR(SYSDATE, 'HH24') >= ?
AND TO_CHAR(SYSDATE, 'HH24') < ?
The TO_CHAR(SYSDATE, 'HH24') produces 11, since the hour
here now is 11. The 1st placeholder is for $start_time, which
contains 5, and 2nd placholder is for $stop_time, which contains
17.
Since 11 is between 5 and 17, this query should return 1 row,
but it is not returning any rows. I've tried
my $sth = $dbh->prepare($sql);
$sth->execute($start_time, $stop_time);
and I've tried
use DBI qw(:sql_types); ### at the top
my $sth = $dbh->prepare($sql);
$sth->bind_param( 1, $start_time, SQL_INTEGER);
$sth->bind_param( 2, $stop_time, SQL_INTEGER);
$sth->execute();
but neither way works.
***However, if I don't use placeholders:
SELECT TO_CHAR(SYSDATE, 'HH24') AS TIME
FROM DUAL
WHERE TO_CHAR(SYSDATE, 'HH24') >= $start_time
AND TO_CHAR(SYSDATE, 'HH24') < $stop_time
it works.
I can certainly use this last method, but I want to understand
why I can't get placeholders to work. Can someone please
clue me in.
Here is the trace(2) output using SQL_INTEGER bind_params:
DBI::db=HASH(0x847d770) trace level set to 2 in DBI 1.20-nothread
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x847d7e8)~0x847d770 '
SELECT TO_CHAR(SYSDATE, 'HH24') AS TIME
FROM DUAL
WHERE TO_CHAR(SYSDATE, 'HH24') >= ?
AND TO_CHAR(SYSDATE, 'HH24') < ?
')
dbd_preparse scanned 2 distinct placeholders
fbh 1: 'TIME' NULLable, otype 1-> 5, dbsize 2/3, p2.s0
<- prepare= DBI::st=HASH(0x84c9f88) at New_Pager.pm line 615
-> execute for DBD::Oracle::st (DBI::st=HASH(0x84c9f88)~0x84dbc34 5 17)
bind :p1 <== 5 (type 0)
bind :p1 <== 5 (size 1/2/0, ptype 6, otype 1)
bind :p2 <== 17 (type 0)
bind :p2 <== 17 (size 2/3/0, ptype 6, otype 1)
dbd_st_execute SELECT (out0, lob0)...
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at New_Pager.pm line 616
-> trace for DBD::Oracle::db (DBI::db=HASH(0x847d7e8)~0x847d770 0)
<- trace= 2 at New_Pager.pm line 617
-> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x84c9f88)~0x84dbc34)
<- fetchrow_array= ( ) [0 items] at New_Pager.pm line 619
-> finish for DBD::Oracle::st (DBI::st=HASH(0x84c9f88)~0x84dbc34)
<- finish= 1 at New_Pager.pm line 623
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x84dbc34)~INNER)
<- DESTROY= undef at New_Pager.pm line 625
-------------------------------------------------------------------------
TIA.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
