In Postgresql 8.0 I wrote the following functions, which will
probably work in 7.4 as well.
They have removed the bool problem completely. My ODBC settings are:
Bools as Char UNCHECKED
TRUE is -1 UNCHECKED

I am using Access 2000, with a lot of checkboxes and boolean fields in
the front end with no problems

Sim
set search_path=pg_catalog;

create or replace function inttobool(num int,val bool) returns bool as
$$
begin
if num=0 and not val then
return true;
elsif num<>0 and val then
return true;
else return false;
end if;
end;
$$ language 'plpgsql';
create or replace function inttobool(val bool, num int) returns bool as
$$
begin
return inttobool(num,val);
end;
$$ language 'plpgsql';
create or replace function notinttobool(val bool, num int) returns bool as
$$
begin
return not inttobool(num,val);
end;
$$ language 'plpgsql';
create or replace function notinttobool(num int, val bool) returns bool as
$$
begin
return not inttobool(num,val);
end;
$$ language 'plpgsql';

CREATE OPERATOR = (
leftarg = integer,
rightarg = boolean,
procedure = inttobool,
commutator = =,
negator = !=
);
CREATE OPERATOR = (
leftarg = boolean,
rightarg = integer,
procedure = inttobool,
commutator = =,
negator = !=
);
CREATE OPERATOR <> (
leftarg = integer,
rightarg = boolean,
procedure = notinttobool,
commutator = <>,
negator = =
);
CREATE OPERATOR <> (
leftarg = boolean,
rightarg = integer,
procedure = notinttobool,
commutator = <>,
negator = =
);


__________________________________________________ ______________________________

Hi,

PG version 7.4 and Access version 2003.

Its the old Access boolean issue which needs as simple an answer as
possible?


I have a client where many users were running off the same Access
database which obviously got corrupted and recently failed completely.

After finding a backup the data has been moved to a Postgresql DB
running on a Debian stable server.

The data was exported via ODBC and I've tidied up the
autonumber-to-sequence issue.* The original Access tables were renamed
to tablebnname_old and the new Postgresql tables have been set up as
linked tables with the original names.* Most of the application is
working fine.

Seems like only one issue remains.

On a couple of forms there are check boxes and radio buttons which
linked originally to Yes/No (i.e. Boolean) fields in the original
Access table.

I have a fairly free hand to sort this out - and there are only 4
tables which contain boolean fields and I can alter the Access
application as I see fit.

There are quite a few queries (dozens) but again I can ask them to cut
them down and re-write needed queries if necessary.

What is the simplest way forward?

What should the ODBC connection be set as?

Here are some possible scenarios.

1. Should I set the fields to be int2 data type and then set the ODBC
driver to not treat bools as char but treat -1 as true.*

Will queries written in Access then run correctly?* I thought I'd tried
this and it didn't work possibly because I did not relink the table.

2. If I simply uncheck the treat bools as char option will the data be
saved correctly as booleans - will the ODBC driver be ok with the data
- i.e. reading and writing.

3. Should I simply set the field as a char(1) and then in Access
somehow or other set the check boxes to save the data as 't' or 'f'.*
How would the control do with reading the data.

4. I understand there may be some extra functions which may be added to
PG to get Access play properly - is there a simple function which can
be added.* Is there a well documented, proven and established method to
acheive this.

5. Have these methods been 'tried and tested'

http://www.mail-archive.com/pgsql-do.../msg01563.html
http://community.seattleserver.com/viewtopic.php?p=8 sid=3add118a6924da03531fcbbbcc2c3ca8
http://www.mrayyan.com/?p=42

6. Change the check boxes to combo boxes - the form is horribly
cluttered but the following seems like a simple answer.

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


There are however dozens of queries and many of them use booleans which
then may not work - however, if
needed I can ask the client to remove the unneeded queries and I could
then re-write the existing queries to take into account the new field.

Maybe I should create the field as int2 and in the combo box have the
bound fields as -1 (label True) and 0 (label False).* Maybe this way
the existing Access queries would work ok without changes?




Any thoughts would be gratefully received.

Kevin


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings