Hiroshi asked me to reproduce the problem WRT Access 97 and NULLs. I
could not. A couple of years ago the problem was appearing on the
interfaces list as some users using ODBC were experiencing the problem.
After about 1/2 day trying to reproduce it under a new operating system,
upgrades of MDAC etc. I finally gave up. However, I did find this
explanation as to why on the Microsoft Knowledge Base:

http://support.microsoft.com/support/kb/articles/Q237/9/92.ASP

I'm not quite sure of the sequence of events and conditions at this
point. But it appears that if someone uses the Form's filter function,
it makes a call into the Jet, which generates what it thinks is an ODBC
compatible query, submits the query to the ODBC driver, which forwards
it on to the database. Depending upon your version of MDAC and the Jet
engine, the Forms filter function will *not* exhibit the behavior. If it
can be pinned down, then we (we meaning you guys ;-)) can rip out the "
= NULL " hack and simply tell users that encounter the problem to
upgrade their MDAC components. If someone knows under what condition
Access 97 would be running under Jet 3.5 vs. Jet 4.0, and whether an
upgrade of the downloadable MDAC components includes Jet upgrades, it
would help.

Mike Mascari
mascarm@mascari.com

Thu, 14 Jun 2001 16:03:53 -0400
Mike Mascari <mascarm@mascari.com>
Update on Access 97 and = NULL
Mike Mascari <mascarm@mascari.com> writes:
> However, I did find this
> explanation as to why on the Microsoft Knowledge Base:

> http://support.microsoft.com/support/kb/articles/Q237/9/92.ASP

Interesting.  Apparently, at this point our "Microsoft-compatible"
hack is arguably *failing* to be Microsoft-compatible.

The KB article is carefully written to make it sound like the only
"foo = NULL" queries out there are mistakes made by users; but wasn't
the original issue that Access97 itself would generate a bogus query?

I'm confused about which component is what here ... what exactly is
the relationship between Access, Jet, MDAC, etc?

			regards, tom lane

Thu, 14 Jun 2001 19:18:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Update on Access 97 and = NULL 
Tom Lane wrote:
> 
> Mike Mascari <mascarm@mascari.com> writes:
> > However, I did find this
> > explanation as to why on the Microsoft Knowledge Base:
> 
> > http://support.microsoft.com/support/kb/articles/Q237/9/92.ASP
> 
> Interesting.  Apparently, at this point our "Microsoft-compatible"
> hack is arguably *failing* to be Microsoft-compatible.
> 
> The KB article is carefully written to make it sound like the only
> "foo = NULL" queries out there are mistakes made by users; but wasn't
> the original issue that Access97 itself would generate a bogus query?

Yes it was. Its been a couple of years but IIRC it was using the either
Filter tool or the Find tool in Forms which generated the bad query.
ODBC in Access also automatically requires the data in the current Form
by default every 1500 seconds. That's why in the archive there was a
post from someone saying that things worked fine until the Form was idle
for around 20 minutes. The guy turned ODBC logging on and saw Access was
sending the = NULL query.

> I'm confused about which component is what here ... what exactly is
> the relationship between Access, Jet, MDAC, etc?

I believe the layers are like this:

Access 97 -> Jet -> OLE DB Provider -> PostgreSQL ODBC ->
..wire..
PostgreSQL

where OLE DB Provider is an MDAC component. If that is the case, then it
would seem the problem is with Access 97 itself (or us, see below). And
I might not be seeing it any more because I'm running Access 97 Service
Release 2.

Another possibility, one that Hiroshi suggested and that can be seen in
the ODBC log files, is that Access 97 is using prepared statements which
eventually call the ODBC driver's SQLPrepare() function like this:

SQLPrepare(hstmt, "UPDATE employees SET salary = ? WHERE employeeid =
?", SQL_NTS);

and later, a call to:

SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 
		 7, 0, &salary, 0, &salaryind);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER,
10, 0,
                 &employee, 0, &employeeind);

If the last parameter is set to SQL_NULL_DATA, then that means that the
last parameter is NULL. That means that the query must be rewritten by
the driver to "IS NULL" instead of "= NULL". Unfortunately, it *appears*
our ODBC driver doesn't do that. In bind.c it stores in the parameter
indicator in the 'used' data member of the StatementClass structure.
Then in convert.c in the function:

int copy_statement_with_parameters(StatementClass *stmt)

is has this piece of code:

if (used == SQL_NULL_DATA) {
	CVT_APPEND_STR("NULL");
	continue;
}

which just appends a NULL to the growing statement it is constructing.
Therefore, the ODBC driver would generate:

"UPDATE employees SET salary = 2000 WHERE employeeid = NULL"

So it appears that the driver is definitely doing the wrong thing with
parameters. Whether or not that is the direct cause, I don't know. I
suspect that older versions of the Jet accepted this form of query for
the same reason. It could potentially be difficult to do the right
thing.   

Now I'm lost and don't know who to blame :-(

Mike Mascari
mascarm@mascari.com

Thu, 14 Jun 2001 21:45:00 -0400
Mike Mascari <mascarm@mascari.com>
Re: Update on Access 97 and = NULL
> -----Original Message-----
> From: Mike Mascari [mailto:mascarm@mascari.com]
> 
> Tom Lane wrote:
> > 
> > Mike Mascari <mascarm@mascari.com> writes:
> > > However, I did find this
> > > explanation as to why on the Microsoft Knowledge Base:
> > 
> > > http://support.microsoft.com/support/kb/articles/Q237/9/92.ASP
> > 
> > Interesting.  Apparently, at this point our "Microsoft-compatible"
> > hack is arguably *failing* to be Microsoft-compatible.
> > 
> > The KB article is carefully written to make it sound like the only
> > "foo = NULL" queries out there are mistakes made by users; but wasn't
> > the original issue that Access97 itself would generate a bogus query?
> 

[snip]

> 
> Another possibility, one that Hiroshi suggested and that can be seen in
> the ODBC log files, is that Access 97 is using prepared statements which
> eventually call the ODBC driver's SQLPrepare() function like this:
> 
> SQLPrepare(hstmt, "UPDATE employees SET salary = ? WHERE employeeid =
> ?", SQL_NTS);
> 
> and later, a call to:
> 
> SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 
> 		 7, 0, &salary, 0, &salaryind);
> SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER,
> 10, 0,
>                  &employee, 0, &employeeind);
> 
> If the last parameter is set to SQL_NULL_DATA, then that means that the
> last parameter is NULL. That means that the query must be rewritten by
> the driver to "IS NULL" instead of "= NULL". Unfortunately, it *appears*
> our ODBC driver doesn't do that. 

You are right. I'd like to fix it.
When could we translate "= NULL" into "IS NULL" ?

  SELECT statement OK ?
  SET xxxx = NULL   NG 
  , xxxx = NULL        NG ?
  otherwise               OK ?
.....

Comments ?

regards,
Hiroshi Inoue

Fri, 15 Jun 2001 19:28:07 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Update on Access 97 and = NULL
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> If the last parameter is set to SQL_NULL_DATA, then that means that the
>> last parameter is NULL. That means that the query must be rewritten by
>> the driver to "IS NULL" instead of "= NULL". Unfortunately, it *appears*
>> our ODBC driver doesn't do that. 

This sounds like a plausible explanation.

> You are right. I'd like to fix it.
> When could we translate "= NULL" into "IS NULL" ?

Hmm ... before we go marching off in that direction, it would be a good
idea to verify that this is *really* what is supposed to happen.  Is
there any ODBC standard that we can consult to confirm that binding a
NULL parameter should cause that to happen?  My suspicion is that it's
just as much a spec violation if the ODBC driver does it as if the
backend does it.  Note that the KB article tells users they must fix
their queries, not that the ODBC layer will take care of it.

But, taking that assumption as correct for the moment ---

Given the choice of having the ODBC driver do this or letting the
backend do it, I'd have to say that I prefer leaving it in the backend,
because the ODBC driver just doesn't have enough understanding of SQL
syntax to be able to get it right.  It would take a major upgrade of the
driver's SQL parser before I'd have any confidence in the translation
being done correctly.

Yet ... in the long run that does need to happen, I think.  Some day
it'd be nice to see ODBC contain a full parser similar to ecpg's.
What I'm wondering about that is how we can reduce the maintenance load.
Michael Meskes spends a great deal of work trying to keep ecpg's grammar
in sync with the backend, and I fear it's a losing battle.  Is there
some way we could automatically generate backend/ecpg/ODBC parsers from
a common source?

			regards, tom lane

Fri, 15 Jun 2001 09:45:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Update on Access 97 and = NULL 
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> >> If the last parameter is set to SQL_NULL_DATA, then that means that the
> >> last parameter is NULL. That means that the query must be rewritten by
> >> the driver to "IS NULL" instead of "= NULL". Unfortunately, it
> *appears*
> >> our ODBC driver doesn't do that.
>
> This sounds like a plausible explanation.
>
> > You are right. I'd like to fix it.
> > When could we translate "= NULL" into "IS NULL" ?
>
[snip]
>
> But, taking that assumption as correct for the moment ---
>
> Given the choice of having the ODBC driver do this or letting the
> backend do it, I'd have to say that I prefer leaving it in the backend,
> because the ODBC driver just doesn't have enough understanding of SQL
> syntax to be able to get it right.  It would take a major upgrade of the
> driver's SQL parser before I'd have any confidence in the translation
> being done correctly.
>

OK agreed.

> Yet ... in the long run that does need to happen, I think.  Some day
> it'd be nice to see ODBC contain a full parser similar to ecpg's.

Hmm I fear the strong binding between the driver and the server.
Would the parser always be backward compatible ?

regards,
Hiroshi Inoue


Sat, 16 Jun 2001 10:05:38 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: Update on Access 97 and = NULL 
Hiroshi Inoue wrote:
> 
> > Yet ... in the long run that does need to happen, I think.  Some day
> > it'd be nice to see ODBC contain a full parser similar to ecpg's.
> 
> Hmm I fear the strong binding between the driver and the server.
> Would the parser always be backward compatible ?
> 
AFAIK, we would need a completely new ODBC-SQL parser that perses the 
query and then reconstructs it in PostgreSQL-SQL before passing it
forward
unless it is for "pass-thru" queries that are the only ones meant to be 
sent directly.

--------------
Hannu

Mon, 18 Jun 2001 12:29:22 +0200
Hannu Krosing <hannu@tm.ee>
Re: Update on Access 97 and = NULL
Hannu Krosing wrote:
> 
> Hiroshi Inoue wrote:
> >
> > > Yet ... in the long run that does need to happen, I think.  Some day
> > > it'd be nice to see ODBC contain a full parser similar to ecpg's.
> >
> > Hmm I fear the strong binding between the driver and the server.
> > Would the parser always be backward compatible ?
> >
> AFAIK, we would need a completely new ODBC-SQL parser that perses the
> query and then reconstructs it in PostgreSQL-SQL before passing it
> forward
> unless it is for "pass-thru" queries that are the only ones meant to be
> sent directly.
> 

How could we know it's a "pass-thru" query ?

regards,
Hiroshi Inoue

Tue, 19 Jun 2001 08:48:43 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Update on Access 97 and = NULL