Re: Bad query from Access stopped working

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0404150521.103d2d03_at_posting.google.com...
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message
news:<1082012202.252813_at_yasure>...
> > Randy Harris wrote:
> >
> > > I have an application that uses MS Access 2000 for a front end to an
Oracle
> > > 8.1.7.4 db. All of a sudden several pass-thru queries have stopped
working.
> > > They are of the form:
> > >
> > > SELECT .... FROM ....
> > > WHERE WeekID BETWEEN '282' AND '287' ;
> > >
> > > WeekID is a numeric field so the values should not have been quoted.
The
> > > queries work if the quotes are removed.
> > >
> > > What puzzles me, is how they had been working right along with the
quotes.
> > > Was Oracle automagically doing the conversion to numeric? Nothing
that I
> > > can identify has changed, either on the Oracle end or the Access end.
Why
> > > would it stop working all of a sudden? Any insight into what might
have
> > > caused this change in behavior would be appreciated.
> >
> > Depending on implicit data conversion is something Oracle has
> > warned against for a long long time. Just stop being lazy and
> > use the correct data types.
> >
> > As to why it stopped working? Maybe someone applied a patch to the
> > system or upgraded to a more recent version.
>
> Randy, I would think the most likely cause of the problem is a change
> to the ODBC drivers or something used by ODBC on the client. Daniel
> is correct in that the datatype provided in the SQL string should
> match the table datatype, but obviously something changed either in
> the client or on the db server. Spending some time trying to find out
> what may be beneficial because whatever it is could be changed again
> at some point and I would think whoever is responsible for the change
> should be giving you a head's up.
>
> HTH -- Mark D Powell --

Thanks Mark. I'm still investigating, trying to find out what got changed. I think that whatever got changed, was on the client end. And I certainly agree that the code should not depend on the "implicit data conversion". I wasn't being lazy. It was an error. I've already corrected the code to use the proper data types.

Again thanks,
Randy Received on Thu Apr 15 2004 - 18:01:09 CDT