Thread: Update on Access 97 and = NULL
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
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
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
> -----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
"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
> -----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
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
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