Обсуждение: Simple way of storing Access booleans (Yes/No) fields

Hi,<br /><br /> PG version 7.4 and Access version 2003.<br /><br /> Its the old Access boolean issue which needs as
simplean answer as possible?<br /><br /><br /> I have a client where many users were running off the same Access
databasewhich obviously got corrupted and recently failed completely.<br /><br /> After finding a backup the data has
beenmoved to a Postgresql DB running on a Debian stable server.<br /><br /> The data was exported via ODBC and I've
tidiedup the autonumber-to-sequence issue.  The original Access tables were renamed to tablebnname_old and the new
Postgresqltables have been set up as linked tables with the original names.  Most of the application is working
fine.<br/><br /> Seems like only one issue remains.<br /><br /> On a couple of forms there are check boxes and radio
buttonswhich linked originally to Yes/No (i.e. Boolean) fields in the original Access table.<br /><br /> I have a
fairlyfree hand to sort this out - and there are only 4 tables which contain boolean fields and I can alter the Access
applicationas I see fit.<br /><br /> There are quite a few queries (dozens) but again I can ask them to cut them down
andre-write needed queries if necessary.<br /><br /> What is the simplest way forward?<br /><br /> What should the ODBC
connectionbe set as?<br /><br /> Here are some possible scenarios.<br /><br /> 1. Should I set the fields to be int2
datatype and then set the ODBC driver to not treat bools as char but treat -1 as true.  <br /><br /> Will queries
writtenin Access then run correctly?  I thought I'd tried this and it didn't work possibly because I did not relink the
table.<br/><br /> 2. If I simply uncheck the treat bools as char option will the data be saved correctly as booleans -
willthe ODBC driver be ok with the data - i.e. reading and writing.<br /><br /> 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
dowith reading the data.<br /><br /> 4. I understand there may be some extra functions which may be added to PG to get
Accessplay properly - is there a simple function which can be added.  Is there a well documented, proven and
establishedmethod to acheive this.<br /><br /> 5. Have these methods been 'tried and tested'<br /><br /><a
class="moz-txt-link-freetext"
href="http://www.mail-archive.com/pgsql-docs@postgresql.org/msg01563.html">http://www.mail-archive.com/pgsql-docs@postgresql.org/msg01563.html</a><br
/><aclass="moz-txt-link-freetext"
href="http://community.seattleserver.com/viewtopic.php?p=8&sid=3add118a6924da03531fcbbbcc2c3ca8">http://community.seattleserver.com/viewtopic.php?p=8&sid=3add118a6924da03531fcbbbcc2c3ca8</a><br
/><aclass="moz-txt-link-freetext" href="http://www.mrayyan.com/?p=42">http://www.mrayyan.com/?p=42</a><br /><br /> 6.
Changethe check boxes to combo boxes - the form is horribly cluttered but the following seems like a simple answer.<br
/><br/><pre><font face="Arial, Helvetica, sans-serif">The way I have handled this is to avoid check boxes and use a
combobox 
 
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</font></pre><br /> There are however dozens of queries and many of them use booleans
whichthen may not work - however, if needed I can ask the client to remove the unneeded queries and I could then
re-writethe existing queries to take into account the new field.<br /><br /> Maybe I should create the field as int2
andin the combo box have the bound fields as -1 (label True) and 0 (label False).  Maybe this way the existing Access
querieswould work ok without changes?<br /><br /><br /><br /><br /> Any thoughts would be gratefully received.<br /><br
/>Kevin<br /> 

Hi,

Firstly, don't forget to use an Access 2003 native format database in your project, and not an Access 2000 format
database.There are huge slowups when using the Access 2000 format. Don't ask me why! 

For your specific problem, I would personnaly use an int2, and treat on the server everything that is different from 0
astrue. This is quick, and with a little work in PLPGSQL, it might be clean too. Otherwise, you might give a try to the
advancedoptions of the ODBC driver: I'm not sure they can help, but it's worth trying. 

Regards,

---------------
Philippe Lang



________________________________

    De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Kevin Bailey
    Envoyé : jeudi, 28. septembre 2006 00:22
    À : pgsql-odbc@postgresql.org
    Objet : [ODBC] Simple way of storing Access booleans (Yes/No) fields


    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
recentlyfailed 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
wererenamed 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)
fieldsin 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
alterthe 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
-1as true.   

    Will queries written in Access then run correctly?  I thought I'd tried this and it didn't work possibly because I
didnot 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
driverbe 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
dataas '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
simplefunction 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-docs@postgresql.org/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
canask the client to remove the unneeded queries and I could then re-write the existing queries to take into account
thenew 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

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-docs@postgresql.org/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