Jay, what is the difference between my way and your way - why
does yours work and mine not? I can't seem much of a difference -
only that you use "selectrow_array", and I use prepare/execute/
fetchrow_array. What am I missing?
Jay Strauss [[EMAIL PROTECTED]] wrote:
> You can do it like this:
>
> #!/usr/bin/perl
>
> use DBI;
> use strict;
>
> my ($start,$end) = @ARGV;
>
> my $dbh=DBI->connect("dbi:Oracle:",'system','manager');
>
> my $sql = q[
> select '1'
> from dual
> where to_char(sysdate,'HH24') >= ?
> and to_char(sysdate,'HH24') < ?
> ];
>
> my $count = ($dbh->selectrow_array($sql,undef,$start,$end)) || 0;
>
> print $count,"\n";
>
> ----- Original Message -----
> From: "Hardy Merrill" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, January 11, 2002 11:01 AM
> Subject: DBI and Oracle date comparison
>
>
> > 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
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
