Re: '= NULL' is not the same as 'IS NULL' - Mailing list pgsql-odbc
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.campbell@us.michelin.comGeert Janssens <info@kobaltwit.be>
Sent by: pgsql-odbc-owner@postgresql.org 09/06/2006 09:55 |
|
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@kobaltwit.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
Attachment
pgsql-odbc by date: