Re: Update on Access 97 and = NULL - Mailing list pgsql-hackers

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