Thread: RE: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards

> > The best solution would be to have the ODBC translate instances 
of '=
> > NULL' into IS NULL before submitting the query to PostgreSQL.

Tom Lane wrote:

> Does Access only talk to Postgres via ODBC?  (Seems plausible, but 
I
> don't know.)  What about people copying-and-pasting queries 
generated
> by Access; is it even possible?

1. Yes, Access only talkes to Postgres via ODBC.

2. The queries generate by Access which use the '= NULL' vs. 'IS 
NULL' language are only generated through the use of Access Forms, 
when some attempts to fetch a record through the forms interface 
where the key of the linked table is an empty edit control. When 
access generates queries, it correctly generates 'IS NULL' language. 
It is a *very specific problem* WRT the user of Access Forms.

> I doubt that this'd really help much, in any case; it merely moves 
the
> necessary switch from the backend to ODBC.

It seems to me that is where it belongs. The ODBC driver is already 
doing translation of some Access functions not found in PostgreSQL 
such as LCASE->lower, etc. The kludge isn't found in any other RDBMS 
of which I am aware (certainly not Oracle), and yet Access works fine 
with them,

Just MHO of course,

Mike Mascari
mascarm@mascari.com

        

Mike Mascari wrote:
> 
> > > The best solution would be to have the ODBC translate instances
> of '=
> > > NULL' into IS NULL before submitting the query to PostgreSQL.
> 
> Tom Lane wrote:
> 
> > Does Access only talk to Postgres via ODBC?  (Seems plausible, but
> I
> > don't know.)  What about people copying-and-pasting queries
> generated
> > by Access; is it even possible?
> 
> 1. Yes, Access only talkes to Postgres via ODBC.
> 
> 2. The queries generate by Access which use the '= NULL' vs. 'IS
> NULL' language are only generated through the use of Access Forms,
> when some attempts to fetch a record through the forms interface
> where the key of the linked table is an empty edit control. When
> access generates queries, it correctly generates 'IS NULL' language.
> It is a *very specific problem* WRT the user of Access Forms.
> 

Could you send me an example using Access Forms ?
DAO seems to translate '= NULL' into 'IS NULL' properly.
Hmm parameter bindings( expr = ? ) could be the cause.

> > I doubt that this'd really help much, in any case; it merely moves
> the
> > necessary switch from the backend to ODBC.
> 
> It seems to me that is where it belongs. The ODBC driver is already
> doing translation of some Access functions not found in PostgreSQL
> such as LCASE->lower, etc.

It doesn't seem that easy.
Detecting '= NULL' isn't sufficient.
'SET .. expr = NULL' mustn't be tranlated into
'SET .. expr IS NULL'. The '= NULL' must be inside
a where clause.
Unfortunately the current psqlodbc driver parses
little.

regards,
Hiroshi Inoue