Either I broke PostgreSQL or I found an ODBC bug

I am using the ODBC provider [8.02.05.0000] to connect my postgresql database [8.2.5] with my MS ACCESS XP front-end.

This is what I am seeing: When I issue a select statement from with-in ms-access, the Boolean field that contain null values are returning as false( as 0 in the datasheet view). My expectation is that null Boolean values display as null rather than false. When I issue the same query with-in psql the null boolean values correctly display as NULL.

A while back I applied the following directions to MS-Access' [yes/no] type to create a better mapping with PostgreSQL's Boolean type. I don't know if these direction are redundant to what is already provided in the ODBC provider. Perhaps the operator that I've created is causing the problem I am seeing.
http://archives.postgresql.org/pgsql-odbc/2006-09/msg00103.p hp


FROM MS-Access:
SELECT did, chktagdesc
WHERE did = 13601 AND chktagdesc IS NULL;
did |chktagdesc
--------+-----------
13601 | 0

FROM psql
proj02u20411=> select did, chktagdesc from docs.lfworkscope where did = 13601 and chktagdesc is null;
did | chktagdesc
-------+------------
13601 |
(1 row)

Can anyone where find where this problem is coming from?

Regards, Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Either I broke PostgreSQL or I found an ODBC bug

Richard Broersma Jr wrote:
> I am using the ODBC provider [8.02.05.0000] to connect my postgresql database [8.2.5] with my MS ACCESS XP front-end.
>
> This is what I am seeing: When I issue a select statement from with-in ms-access, the Boolean field that contain null values are returning as false( as 0 in the datasheet view). My expectation is that null Boolean values display as null rather than false. When I issue the same query with-in psql the null boolean values correctly display as NULL.
>
> A while back I applied the following directions to MS-Access' [yes/no] type to create a better mapping with PostgreSQL's Boolean type. I don't know if these direction are redundant to what is already provided in the ODBC provider. Perhaps the operator that I've created is causing the problem I am seeing.
> http://archives.postgresql.org/pgsql-odbc/2006-09/msg00103.p hp

Using inttobool(int, bool) defined in the above URL, I see the
following.

xxxxx=> select inttobool(NULL, true);
inttobool
-----------
f
(1 row)

xxxxx=> select inttobool(NULL, false);
inttobool
-----------
f
(1 row)

Is it what you expected ?

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Either I broke PostgreSQL or I found an ODBC bug

--- On Mon, 11/5/07, Hiroshi Inoue wrote:

> Using inttobool(int, bool) defined in the above URL, I see
> the
> following.
>
> xxxxx=> select inttobool(NULL, true);
> inttobool
> -----------
> f
> (1 row)
>
> xxxxx=> select inttobool(NULL, false);
> inttobool
> -----------
> f
> (1 row)
>
> Is it what you expected ?

No, I guess I did break Postgres with the inttobool function. :-(

I was expecting a null boolean to return a null and not return a false. I guess my next question would be, how do I go about extracting or fix the inttobool() function in postgres?


Ms-access can't update any of these records that have fields that incorrectly appear to be false when the contents are actually boolean null's, since Access includes these fields in the as part of the update statement's where condition that is sent to postgresql. When this happens PostgreSQL notifies MS-access that zero records where updated and the transaction is rolled back. i.e. update ... where chkfield = '0' -- when chkfield is acutally null.

Thanks for the help!
Regards, Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Either I broke PostgreSQL or I found an ODBC bug

--- On Mon, 11/5/07, Richard Broersma Jr wrote:

> No, I guess I did break Postgres with the inttobool
> function. :-(

On closer inspection, Crystal reports, using the ODBC provider produces the expected results with using boolean. The problem only occurs in ms-access, so I guess that it is a MS-Access bug.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Either I broke PostgreSQL or I found an ODBC bug

Richard Broersma Jr wrote:
> --- On Mon, 11/5/07, Richard Broersma Jr wrote:
>
>> No, I guess I did break Postgres with the inttobool
>> function. :-(
>
> On closer inspection, Crystal reports, using the ODBC provider produces the expected results with using boolean.
> The problem only occurs in ms-access, so I guess that it is a
MS-Access bug.

Hmm I found the following at http://allenbrowne.com/xbase-05.html .

Unfortunately, Access 95 onwards do not permit Null values in
Yes/No fields.

regards,
Hiroshi Inoue


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Either I broke PostgreSQL or I found an ODBC bug

--- On Tue, 11/6/07, Hiroshi Inoue wrote:

> Unfortunately, Access 95 onwards do not permit Null values in
> Yes/No fields.

That explains why I was see FALSE instead of NULL. As a work around, I've altered all of the boolean fields to be NOT NULL with a DEFAULT value as FALSE. Now MS-access is able to update these records without complaining.

Thanks!
Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly