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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073