On Thu, 14 Nov 2002 08:44:48 -0500 "Kipp, James" <[EMAIL PROTECTED]> wrote:
> I have a simple query using a LIKE in the statement and I just can get it to
> return any data. I know it works because i can do it in sqlplus and get data
> returned. I have tried everything to try to quote it correctly, like qq, qw,
> dbh->quote and finally placeholders. Code is below, any ideas ?
Stick with placeholders. They are much more effecient and much safer.
> # my $string = $dbh->quote("%7-Aug%");
> my $date = qq(7-Aug);
>
> my $sth = $dbh->prepare("SELECT * FROM uptime WHERE sdate LIKE ?" )
> or die "can't prepare: $DBI::errstr\n";
>
> $sth->execute("%$date%") or die "can't execute $DBI::errstr\n";
> my @row;
> while (@row = $sth->fetchrow_array() ) {
> print "@row\n";
> }
This looks fine, _IF_ uptime.sdate is a VARCHAR column. If it is a DATE
column, then you are causing an implicit type conversion for every row in
the table and probably have the wrong case for comparison with the
resulting string.
For a DATE column, if you really want everything that happened on 07-AUG or
17-AUG of any year, you need to use uppercase for the month. If you just
want everything in 07-AUG of this year, you'd be better off with something
like this:
SELECT *
FROM uptime
WHERE sdate >= TO_DATE( ?, 'DD-MON' )
AND sdate < TO_DATE( ?, 'DD-MON' ) + 1
Note that if you run this on 01-JAN for the preceeding 31-DEC, you will get
nothing because the implied year will be wrong. You are probably better
off giving the full year explicitely. I prefer 'YYYY-MM-DD' over the
default VARCHAR format for DATEs.
I recommend using explicit column names instead of '*'. If someone changes
the column order on you it could ruin the rest of your processing.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
