I don't see an effective difference either. I think the main problem is
that TO_CHAR() produces a string, but 5 and 17 are numbers. Try wrapping
the TO_CHAR() calls with TO_NUMBER() or padding 5 to '05' and see if either
helps.
--
Mac :})
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: "Hardy Merrill" <[EMAIL PROTECTED]>
To: "Jay Strauss" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, January 11, 2002 12:48
Subject: Re: DBI and Oracle date comparison
> 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
>
