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. -- 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.
