Thread: 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 valuesare returning as false( as 0 in the datasheet view). My expectation is that null Boolean values display as null ratherthan 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'sBoolean 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.php 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.
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 nullvalues are returning as false( as 0 in the datasheet view). My expectation is that null Boolean values display as nullrather 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'sBoolean 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.php 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
--- On Mon, 11/5/07, Hiroshi Inoue <inoue@tpf.co.jp> 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 aboutextracting 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 actuallyboolean null's, since Access includes these fields in the as part of the update statement's where condition thatis sent to postgresql. When this happens PostgreSQL notifies MS-access that zero records where updated and the transactionis rolled back. i.e. update ... where chkfield = '0' -- when chkfield is acutally null. Thanks for the help! Regards, Richard Broersma Jr.
--- On Mon, 11/5/07, Richard Broersma Jr <rabroersma@yahoo.com> 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 problemonly occurs in ms-access, so I guess that it is a MS-Access bug. Regards, Richard Broersma Jr.
Richard Broersma Jr wrote: > --- On Mon, 11/5/07, Richard Broersma Jr <rabroersma@yahoo.com> 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
--- On Tue, 11/6/07, Hiroshi Inoue <inoue@tpf.co.jp> 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 NULLwith a DEFAULT value as FALSE. Now MS-access is able to update these records without complaining. Thanks! Regards, Richard Broersma Jr.