Re: '= NULL' is not the same as 'IS NULL'

I have a question for the mailing list.
More generally, let us assume that when in Access, you change a record
focus (after a delete, insert, update. upon opening a form, or moving the
Access cursor), it may be reasonable to execute one or more SELECT queries
to refresh the form.
Why does it tend to use the WHERE clause to match each field value, instead
of a primary key for a match? What are the conditions that force primary
key usages vs. "match every field" syntax?

And it appears Geert may have found a bug perhaps (in pgODBC or in Access)?
Where the WHERE clause match for NULL values tries to use an =NULL syntax
instead of IS NULL .
When I turn on ODBC tracing , from ODBC Administrator -- outside of the
pgODBC DSN, the trace shows parameterized ODBC syntax. I am not sure if
the basic query (for example:
"SELECT ? WHERE emp_id=? AND customer_id=? and item_code=?" comes from
Access/Jet or from the pgODBC driver. It seems like it would come from the
client (Access) and mere mortals would be helpless to change it. The fact
that a parameter has a NULL value (resulting in perhaps customer =NULL,
which of course is not valid SQL) does not change that basic query. A fix
would require the driver to understand a NULL value and change the syntax
from =NULL or <>NULL to IS NULL or NOT IS NULL on the fly.

I am afraid I might be speculating over my head. I really am seeking to
understand. So anybody who can shed light on these things please pitch in.

Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg(dot)campbell(at)us(dot)michelin(dot)com

Geert Janssens
<info(at)kobaltwit(dot)b
e> To
Sent by: pgsql-odbc(at)postgresql(dot)org
pgsql-odbc-owner@ cc
postgresql.org
Subject
[ODBC] '= NULL' is not the same as
09/06/2006 09:55 'IS NULL'

Hi,

I managed to fix my write conflict problems in Ms Access. Now I already
stumble upon another issue:

I have a psqlODBC linked table 'tarticles' in my Ms Access 2000
application,
and a form 'Artikels' to make changes to this table. One peculiarity of
this
form is, that one of the varchar fields gets set programmatically.

For example, when I create a new record, and save it (by moving to a new
empty
record), I find this in the commlog:
conn=995a250, query='INSERT INTO "public"."tarticles"
("vendorid","reference","category","type","islot","amount","invoicecurrency",

"invoiceprice","priceunit","pricecustomer","invoiceid","check","label","imgname","amountlot")

VALUES
(235,'00-00007',2,7,'1','100',1,'100','1','0',921,'0','Diverse',NULL,'100')'

The field imgname was set to NULL programmatically.
When I move one record back in the form, to the record that I just added,
it
will show #deleted# in every field.

This move back is represented in the commlog by:
conn=995a250, query='declare "SQL_CUR0995ED90" cursor with hold for
SELECT "public"."tarticles"."articleid" FROM "public"."tarticles"
WHERE "vendorid" = 235 AND "reference" = '00-00007' AND "category" = 2
AND "type" = 7 AND "islot" = '1' AND "amount" = '100' AND "invoicecurrency"
=
1 AND "invoiceprice" = '100' AND "priceunit" = '1' AND "pricecustomer" =
'0'
AND "invoiceid" = 921 AND "check" = '0' AND "label" = 'Diverse' AND
"imgname" = NULL AND "amountlot" = '100''

I tried this query directly in phpPgSQL, and indeed it returns no results.
On
the other hand, if I modify "imgname" = NULL to "imgname" IS NULL, the
query
works fine.

I found references via google that '= NULL' and 'IS NULL' are not the same
thing as far as PostgreSQL is concerned, which I understand.
They even provide a runtime command to override this:
SET transform_null_equals TO ON;
Unfortunatly it seems this should be called before each query that is
affected. It's not set globally.

So I'm not sure how I can solve this issue. I could work around it and make

sure no NULL values can happen, but I wonder if there might be an easier
way
by means of a preference setting in Access or psqlODBC or PostgreSQL.

Has anybody else stumbled upon this issue ?

Thanks,

Geert
--
Kobalt W.I.T.
Web & Information Technology
Brusselsesteenweg 152
1850 Grimbergen

Tel : +32 479 339 655
Email: info(at)kobaltwit(dot)be

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match