ms-access and booleans ?

Hi,

I transferred a couple of tables from an Access MDB to Postgres.
Among the involved column-types are booleans.

I could dump the contend of the Access table to PG via an odbc linked table.

In psql I can run :
SELECT count(*) FROM my_table WHERE some_boolean;
It shows some number as expected.

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

How can have booleans that don't break my Access front-end ?

cu
Andreas

--- Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> Hi,
>
> I transferred a couple of tables from an Access MDB
> to Postgres.
> Among the involved column-types are booleans.
>
> I could dump the contend of the Access table to PG
> via an odbc linked table.
>
> In psql I can run :
> SELECT count(*) FROM my_table WHERE
> some_boolean;
> It shows some number as expected.
>
> 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.
>
> How can have booleans that don't break my Access
> front-end ?
>
>
> cu
> Andreas
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo(at)postgresql(dot)org)

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

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.

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.

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

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

On Monday 19 January 2004 07:36 am, Jeff Eckermann wrote:
> --- Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> > Hi,
> >
> > I transferred a couple of tables from an Access MDB
> > to Postgres.
> > Among the involved column-types are booleans.
> >
> > I could dump the contend of the Access table to PG
> > via an odbc linked table.
> >
> > In psql I can run :
> > SELECT count(*) FROM my_table WHERE
> > some_boolean;
> > It shows some number as expected.
> >
> > 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.
>
> > How can have booleans that don't break my Access
> > front-end ?
> >
> >
> > cu
> > Andreas
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> > (send "unregister YourEmailAddressHere" to
>
> majordomo(at)postgresql(dot)org)
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
> http://hotjobs.sweepstakes.yahoo.com/signingbonus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

The way I have handled this is to avoid check boxes and use a combo box
instead. I supply the values as True;1 ,False;0 and bind the field to the
second value of each pair. To make things easier for data entry I hide the
second column by giving it a width of 0". In my DSN settings I check bool as
char and uncheck true as -1.
--
Adrian Klaver
aklaver(at)comcast(dot)net