Re: Access97 to pgSQL, error Bad boolean external representation

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