Re: ms-access and booleans ?

--- Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> Jeff Eckermann wrote:
>
> >--- Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> >
> >
> >>I Access I get an Error:
> >>ODBC-Call failed.
> >>Error: Operator doesn't exist: boolean = integer
> >>(#7)
> >>
> >>In the System-DSN I have
> >>Bool as Char : NO
> >>True is -1 : YES
> >>
> >>
> >
> >You should have success by playing with these
> >settings. I use different settings, but that is a
> >historical matter. I believe that the ODBC driver
> >handling of booleans for MS Access has been
> optimized
> >somewhat in recent releases. Probably just setting
> >"bool as char" to true will do it. But I admit
> that I
> >haven't played with these lately, not wanting to
> mess
> >up a working setup.
> >
> >
>
> No way :(
> The problem lies in the way a SELECT is handled by
> Access.
>
> I have a test table with a bool-column called "bol".
>
> Access generates :
> SELECT test1.*
> FROM test1
> WHERE (((test1.bol)=True));
>
> Trying this Access throws an error which says there
> is no comparison
> function between boolean and integer.

That's PostgreSQL complaining, not Access:

jeff=# select '1' = true;
?column?
----------
t
(1 row)

jeff=# select 1 = true;
ERROR: Unable to identify an operator '=' for types
'integer' and 'boolean'
You will have to retype this query using an
explicit cast

Access is converting 'true' and 'false' to 0 and -1
behind the scenes.

>
> In contrast pgAdmin as well as psql run the same
> query without complaints.
> Both show the bool-column as t or f.
> Access would display 0 / -1.
>
> Interestingly I can enter TRUE or FALSE into the
> table view of this
> table within Access.
> TRUE gets translated to -1 and FALSE to 0.
> Even 1 gets translated to -1.
>
> But no way I could use the bool-column in an Access
> query as criterium,
> if not "bool as char" is set.
> Then I can query bol = "t"
> On the other hand "bool as char" breaks the Access
> check boxes.
> Initially they show the bool-values correctly but if
> I try to set a
> checkbox to TRUE I get an error that the value was
> too big for the
> column. I guess "-1" doesn't fit into PG's BOOLEAN
> which is CHAR(1) as
> far as I know.

I had this problem too. I solved it by unchecking the
"bool as char" option, and creating the missing
operator in PostgreSQL:

DROP OPERATOR = (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
CREATE FUNCTION MsAccessBool (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 OPERATOR = (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBool,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);

Put this in your template1 database, as well as any
other you are working in, and you should be in good
shape.

I was sure that that I had heard of some enhancements
to the driver that made all of this unnecessary.
Perhaps not that many people are using checkboxes in
Access?

>
>
> Live s*cks I tell ya. =8-}
>
>
>
>
>
>
>
>

__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus