Re: [ODBC] PostgreSQL Documentation Submission: MS Access and PostgreSQL

Dear Bruce-san.

I am sorry to be a very late reaction....

The guidance can help some users actually. As for me, it is very glad to see it by
ACCESS2000 to PostgreSQL.:-)
http://cre-ent.skcapi.co.jp/~saito/pgadmin3/psqlODBC/psqlODBC_bool1.png
http://cre-ent.skcapi.co.jp/~saito/pgadmin3/psqlODBC/psqlODBC_bool2.png
http://cre-ent.skcapi.co.jp/~saito/pgadmin3/psqlODBC/psqlODBC_bool3.png
When using it, it is necessary to turn OFF the bools option of psqlODBC.
However, It is a solution to a certain problem. Therefore, "Yes", I think sufficient
contents to appear in FAQ.

Regards,
Hiroshi Saito

>
> 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
> >