> oh wait. 7-Aug won't work on a date in oracle. Oracle defaults to upper
> caseing everything so a like on "%7-Aug%" will not match "07-AUG-02" due
> to the case difference. Try uppering the data you are testing for. do an
> " '%' || upper($date) || '%' " and see what that does for you.
>
> Something like this is what is happening
>
> SQL> select * from dual;
>
> D
> -
> X
>
> SQL> select * from dual where dummy like '%x%';
>
> no rows selected
>
> SQL> select * from dual where dummy like '%'||upper('x')||'%'
>
> D
> -
> X
>
> So in your sth->execute try this.
> $sth->execute("'%'||upper($date)||'%'");
>
> Adam
>
>
> Hmmm, I don't know if I agree with the %$date is a reference sentiment:
>
> $ cat t.pl
> $d = "7-Aug";
> print "%$d%";
> print "\n";
> $ perl t.pl
> %7-Aug%
> $
>
> (I first did this with 'perl -e', bash needs too many backslashes to make
> it readable.)
>
> Now, if I try this, I find I can't get it to work:
> SQL> desc t1
> Name Null? Type
> ------------------------------- -------- ----------------------------
> D DATE
>
> SQL> select * from t1;
>
> D
> ---------
> 07-AUG-02
>
> SQL> select * from t1 where d like '%7-Aug%';
>
> no rows selected
>
> SQL>
>
> Jim, is your column date or another type? My test was on Oracle 8.1.7 for
> RH Linux. Changing the datatype to varchar2 works for me, but makes
> comparisons for dates impossible (e.g. select * from t where d >
> '7-Aug-2002';).
>
> Dave
>
> On Nov 14, Hapworth, Adam scribed:
>
> >
> >
> >
> > > 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 ?
> > >
> > > Thanks
> > > Jim
> > > ------
> > >
> > > # 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";
> > > }
> > >
> >
> > You may want to use concationation on this or escape the %
> > ("%".$date."%")
> > $sth->execute("%$date%") or die "can't execute $DBI::errstr\n";
> > the double quotes will allow for the %$date to be read a has reference
> > instead of a "%"$date"%" like you are trying to do.
> >
>
