Thanks to Michael Chase for his suggestion of using TO_NUMBER on
the outside of the TO_CHAR - it works *WITH* placeholders this
way:
use DBI qw(:sql_types);
my $sql = qq{
SELECT TO_CHAR(SYSDATE, 'HH24') AS TIME
FROM DUAL
WHERE TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) >= ?
AND TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) < ?
};
my $sth = $dbh->prepare($sql);
$sth->bind_param( 1, $start_time, SQL_INTEGER);
$sth->bind_param( 2, $stop_time, SQL_INTEGER);
$sth->execute();
my ($time) = $sth->fetchrow_array();
-----------------------------------------------------------
When the current hour(HH24) is between $start_time and $stop_time
(both of those also HH24), then this code with TO_NUMBER *DOES*
now find a row, like it's supposed to, whereas id didn't before
(without TO_NUMBER).
Michael, this works, but please see my question below.
Thanks.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
Michael A Chase [[EMAIL PROTECTED]] wrote:
> When the Oracle SQL parser sees a number on one side of the '=' in the WHERE
> clause it can tell that character to number conversion is needed and does it
> for you. With placeholders, it doesn't have that information.
Using placeholders with bind_params and SQL_INTEGER, wouldn't that
be the same as having "a number on one side"?
> --
> 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: "Michael A Chase" <[EMAIL PROTECTED]>
> Sent: Wednesday, January 16, 2002 08:31
> Subject: Re: DBI and Oracle date comparison
>
>
> > Michael, do you understand why this works if I *don't* use
> > placeholders, and why it doesn't work if I do use placeholders?
> > Could this be a bug with DBD::Oracle?
> >
> > Michael A Chase [[EMAIL PROTECTED]] wrote:
> > > 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.
> > > > > ----- 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.
