Thread: Access97 to pgSQL, error Bad boolean external representation

Apologies if this is a well known problem, I have googled extensively
for the error message and can find what I doing wrong.

I am in the process of converting an Access 97 app to use PostgreSQL as
the backend.  For the moment I´m keeping Access as the frontend.

PostgreSQL is version 7.2.1 running on Debian Woody

ODBC Driver is version 7.03.01, on windows 98.  Downloaded and installed
last week.

I finished the conversion successfully, I think, but I´m having problems
with boolean data types when the app is running.  I´ve set up a test
table, containing only a key, a boolean field and a currency field.
Saving a record from Access with a false value in the boolean works ok,
but saving a true value fails with an ODBC error.  The error message
from the ODBC log is:

SC_get_error: status = 7, msg = #ERROR:  Bad boolean external
representation '-1'#

I´ve tried to change the settings in the ODBC config.  The two settings
I´ve been changing are
Bool as Char, and
True is -1  (on page 2 of the config screen)

I´ve tried all 4 combinations of these settings, both with the
PostgreSQL driver and with the PostgreSQL Legacy driver.  Nothing I´ve
changed has had an effect, ie, I get the same message in all 8 cases.  I
exit out off Access completely between tests.

As I´m getting the same error no matter the setting, I´m tempted to
believe that I´m changing the wrong DSN, or that there´s some trick to
saving the settings so that they´re actioned?  I´ve checked the DSN in
the log each time, so...  What stupid newbie error am I making?

Thanks for your help

Alan Graham

--
Alan Graham <alan.graham@infonetsystems.com.au>

--- Alan Graham <alan.graham@infonetsystems.com.au>
wrote:
> Apologies if this is a well known problem, I have
> googled extensively
> for the error message and can find what I doing
> wrong.

If you search the archives of the odbc list for
"boolean" I am sure you will find plenty.

> I am in the process of converting an Access 97 app
> to use PostgreSQL as
> the backend.  For the moment I�m keeping Access as
> the frontend.
>
> PostgreSQL is version 7.2.1 running on Debian Woody
>
> ODBC Driver is version 7.03.01, on windows 98.
> Downloaded and installed
> last week.
>
> I finished the conversion successfully, I think, but
> I�m having problems
> with boolean data types when the app is running.
> I�ve set up a test
> table, containing only a key, a boolean field and a
> currency field.
> Saving a record from Access with a false value in
> the boolean works ok,
> but saving a true value fails with an ODBC error.
> The error message
> from the ODBC log is:
>
> SC_get_error: status = 7, msg = #ERROR:  Bad boolean
> external
> representation '-1'#
>
> I�ve tried to change the settings in the ODBC
> config.  The two settings
> I�ve been changing are
> Bool as Char, and
> True is -1  (on page 2 of the config screen)
>
> I�ve tried all 4 combinations of these settings,
> both with the
> PostgreSQL driver and with the PostgreSQL Legacy
> driver.  Nothing I�ve
> changed has had an effect, ie, I get the same
> message in all 8 cases.  I
> exit out off Access completely between tests.

If you have any linked tables that include boolean
values, you will need to relink them each time you
make a change in the driver settings.  After you do
this, check out the table definition (in design view)
to see what datatype Access thinks the boolean field
is.  If it says "yes/no", then you are (should be) in
good shape.  If not, you still have a problem.

I had a similar problem, which showed up when using
checkboxes.  My solution was to uncheck "bools as
char", and add a custom function called (IIRC)
"booleqint4" to the database.  If nothing else works
for you, you can find the function code in the
archives, or email me and I will send it to you.

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com