Re: PostgreSQL Documentation Submission: MS Access and PostgreSQL

FYI, is this content useful in any of our documentation or web sites?

---------------------------------------------------------------------------

Benn Bollay wrote:

No reason at all. I'm new to postgresql and to the documentation
structure in general, and don't know these locations :) Would you be
willing to repost this to those lists, and anywhere else you think is
appropriate?
--B

At 04:29 02/02/2006, you wrote:

Is there a reason this has not been posted to the docs or odbc lists?
It might be good on techdocs too.

---------------------------------------------------------------------------

Benn Bollay wrote:

Mornin' Bruce -

I have a brief note on MS Access 2003 and PGSQL 8.1 Interaction
regarding boolean values. This has been partially answered in
several places in the mailing lists, though no one has posted (as
least that I found in google) a concise "For these problems, do this"
summary. I've drafted it up below, and if you could pass it on to
the best agency for addition to the docs where poor souls like myself
could find it, I would much appreciate it.

-- Begin Note --
Title: Microsoft Access and PostgreSQL Boolean Field Interaction

Summary:
Microsoft Access does not handle boolean values in a
default-compatible fashion with a PostgreSQL backend. Several errors
will occur, but are solved via the addition of both of the necessary
operator= and operator<> functions to manage the conversion, plus
properly configuring the ODBC connection.

Environment:
This was tested with Microsoft Access 2003 and PostgreSQL 8.1 using
the shipped-with ODBC drivers. The Access database utilizes linked
tables to a system DSN for the PostgreSQL database running on the

same machine.

Error #1:
This problem appears in several different error formats. The first
error I encountered was:
ERROR: invalid input syntax for type boolean "" (#7)

This error has also been seen on the usegroups as:
ERROR: invalid input syntax for type boolean "-" (#7)

The two errors are equivilient for this issue.

Resolution #1:
Resolving this error requires several alterations.

First, for the DSN Configuration, within the Options group select the
Datasource options. Set the following options on Page 1:
DataTypeOptions --> Bools as Char FALSE

And on Page 2:
True is -1 TRUE

At this point, please make sure you refresh the table links within
Microsoft Access using the Linked Table Manager.

Now, in order to properly translate the boolean values between
Microsoft Access and PostgreSQL we need to add the following stored
procedures. These can be added by pasting directly into the 'psql'
tool after logging into the database. Several of the posts out there
neglect to include the CREATE OPERATOR <> statement. This causes the
second error:

Error #2:
fmgr_info: function 0: cache lookup failed

This error is particularly uninformative. Essentially, you encounter
this error if you did not add the CREATE OPERATOR<> statement, but
only the CREATE OPERATOR= statement as per several of the usenet
posts. Translated, this error is saying that the expected stored
procedure is not found -- in this case, it's looking for the

negator operator.

Resolution Continued:
To remove both of these errors, add the following stored procedure code

--- BEGIN CODE ---
DROP OPERATOR = (bool, int4);
DROP OPERATOR <> (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
DROP FUNCTION MsAccessBoolEq (bool, int4);
DROP FUNCTION MsAccessBoolNeq (bool, int4);

CREATE FUNCTION MsAccessBoolEq (bool, int4) RETURNS BOOL AS '
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;

IF $1 IS TRUE THEN
IF $2 <> 0 THEN
RETURN TRUE;
END IF;
ELSE
IF $2 = 0 THEN
RETURN TRUE;
END IF;
END IF;
RETURN FALSE;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION MsAccessBoolNeq (bool, int4) RETURNS BOOL AS '
BEGIN
RETURN NOT MsAccessBoolEq($1, $2);
END;

' LANGUAGE 'plpgsql';

CREATE OPERATOR = (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBoolEq,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);

CREATE OPERATOR <> (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBoolNeq,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);
--- END CODE ---

This code creates the MsAccessBoolEq and MsAccessBoolNeq stored
procedures, and registers them as the handler for the equality and
negator operators.

Verification:
You can verify that the code is working correctly by applying the
following schema:
-- BEGIN SCHEMA --
DROP TABLE "test_table";

CREATE TABLE "test_table" ("id" serial not null, "data" boolean not
null default true);
INSERT INTO "test_table" ("data") VALUES (true);
INSERT INTO "test_table" ("data") VALUES (false);
-- END SCHEMA --

Within Microsoft Access, add the 'test_table' to your environment,
open up a new query window and execute the following SQL queries:
SQL:
SELECT * FROM public_test_table WHERE data = True
Expected:
id data
1 -1

SQL:
SELECT * FROM public_test_table WHERE data <> True
Expected:
id data
2 0

As you can see, the queries now properly compare boolean values!

Hope this helps everyone out there who's making the jump!
--Benn Bollay

--
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073