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