Обсуждение: Domains, casts, and MS Access

Hi all,

I'm working on porting an old MS Access form application from Sybase
to postgres/ODBC as part of a larger database port project.

One of the snags that's popped up is that there's some incompatibility
between data types. Specifically, many fields are the Sybase type
"bit", which is basically a boolean, but it accepts and displays
bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility
(especially bareword integers in queries), I've defined a 'sybit' type
in postgres to be a domain.

=> create domain sybit as smallint check ( value in (0,1) );

That is compatible behavior for most applications, but Access gets
confused since it wants to map it to an integer instead of a boolean
(it does the right thing for a native Sybase driver). I thought that
creating casts between sybit and boolean might help, but that won't
work so much it seems.

=# create cast (sybit as smallint) without function as implicit;
CREATE CAST
=# create cast (sybit as integer) with function int4(smallint) as implicit;
CREATE CAST
=# create cast (sybit as boolean) with function bool(integer) as assignment;
ERROR:  argument of cast function must match or be binary-coercible
from source data type

Is there a way to tell Access to do the right thing, or is there a
better way to define the type/domain, or is there some better product
to use?

Thanks much,
Peter

P.S. In case people are interested in the specifics of the sybase
"bit" type, you can look at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks54.htm.

On Wed, Aug 4, 2010 at 10:31 AM, Peter Koczan <pjkoczan@gmail.com> wrote:

> One of the snags that's popped up is that there's some incompatibility
> between data types. Specifically, many fields are the Sybase type
> "bit", which is basically a boolean, but it accepts and displays
> bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility
> (especially bareword integers in queries), I've defined a 'sybit' type
> in postgres to be a domain.

One thought would be see if ODBC configuration options will achieve
this for you. Have you already exhausted this option?


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Wed, Aug 4, 2010 at 12:47 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Wed, Aug 4, 2010 at 10:31 AM, Peter Koczan <pjkoczan@gmail.com> wrote:
>
>> One of the snags that's popped up is that there's some incompatibility
>> between data types. Specifically, many fields are the Sybase type
>> "bit", which is basically a boolean, but it accepts and displays
>> bareword 1 and 0 instead of 't' and 'f'. For the sake of compatibility
>> (especially bareword integers in queries), I've defined a 'sybit' type
>> in postgres to be a domain.
>
> One thought would be see if ODBC configuration options will achieve
> this for you. Have you already exhausted this option?

This is one of my first forays into ODBC, so I didn't know that was a
possibility. Is there any place where these are documented? Searching
for ODBC options yields info on connection options, but none on
behavior that I could find.

Peter

On Wed, Aug 4, 2010 at 11:51 AM, Peter Koczan <pjkoczan@gmail.com> wrote:

> This is one of my first forays into ODBC, so I didn't know that was a
> possibility. Is there any place where these are documented? Searching
> for ODBC options yields info on connection options, but none on
> behavior that I could find.

I know that there are a couple of options that affect the
representation of Booleans in the odbc driver.  I'm not sure it will
do what you need though.

However, here is the official documentation: (hopefully it helpful)

http://psqlodbc.projects.postgresql.org/


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On 8/4/2010 1:56 PM, Richard Broersma wrote:

On Wed, Aug 4, 2010 at 11:51 AM, Peter Koczan <pjkoczan@gmail.com> wrote:
 

This is one of my first forays into ODBC, so I didn't know that was a
possibility. Is there any place where these are documented? Searching
for ODBC options yields info on connection options, but none on
behavior that I could find.   

I know that there are a couple of options that affect the
representation of Booleans in the odbc driver.  I'm not sure it will
do what you need though.

However, here is the official documentation: (hopefully it helpful)

http://psqlodbc.projects.postgresql.org/

 

Yes there is an option to change bools to char

• Data Type Options: affects how some data types are mapped:
   
  • Text as LongVarChar: PostgreSQL TEXT type is mapped to SQLLongVarchar, otherwise SQLVarchar.
  • Unknowns as LongVarChar: Unknown types (arrays, etc) are mapped to SQLLongVarChar, otherwise SQLVarchar
  • Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.

My memory is fuzzy but there are some additional settings in Access that allows data type mapping...


All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

On Wed, Aug 4, 2010 at 1:24 PM, Justin Graf <justin@magwerks.com> wrote:

> My memory is fuzzy but there are some additional settings in Access that
> allows data type mapping...

My experience is that PostgreSQL Integer types work the best for
MS-Access bit datatype considering the fact that in Access -1 = true.

I know that there is a setting in the ODBC driver for true = -1 but it
doesn't work well.  I also remember that filters didn't work on mapped
boolean columns.

For example:

2010-06-08 14:39:43 PDTERROR:  invalid input syntax for type boolean:
"-1" at character 49
2010-06-08 14:39:43 PDTSTATEMENT:
BEGIN;
UPDATE "public"."structures"
SET "scoped"=E'-1'
WHERE "buildingfunction" = E'CRANE OPERATOR STATION'
AND "xmin" = 20497
-06-08 14:39:43 PDTLOG:  duration: 0.000 ms  statement: ROLLBACK

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Wed, Aug 4, 2010 at 4:40 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Wed, Aug 4, 2010 at 1:24 PM, Justin Graf <justin@magwerks.com> wrote:
>
>> My memory is fuzzy but there are some additional settings in Access that
>> allows data type mapping...
>
> My experience is that PostgreSQL Integer types work the best for
> MS-Access bit datatype considering the fact that in Access -1 = true.
>
> I know that there is a setting in the ODBC driver for true = -1 but it
> doesn't work well.  I also remember that filters didn't work on mapped
> boolean columns.

Yep, that's the stumbling block we're running into. ODBC and these
fields' assumptions of true/false are at odds. I'm trying a few other
things with casts in the meantime to see if they'll work.

Does anyone know if another product, like OpenOffice Base with its
native postgres driver, does any better?

Peter

On Wed, Aug 4, 2010 at 3:41 PM, Peter Koczan <pjkoczan@gmail.com> wrote:

> Yep, that's the stumbling block we're running into. ODBC and these
> fields' assumptions of true/false are at odds. I'm trying a few other
> things with casts in the meantime to see if they'll work.

Well there is a solution that I've been toying around with.  In
PostgreSQL, there are many data-types that cannot be expressed
directly in an MS-Access Linked table.  For example, composite types,
arrays, range types, hstores, postgis types et.al.   However, most of
these types can be decomposed in to base types that can be express in
linked tables.

The key is using update-able views to decompose the data for Access
and re-assemble it before it transmitted back to the base table.  The
same can be done for boolean datatype.


> Does anyone know if another product, like OpenOffice Base with its
> native postgres driver, does any better?

From my limited experience, I believe is does do better.  The
following blogs as a few entries about using Base:

http://www.postgresonline.com/journal/archives/167-Importing-data-into-PostgreSQL-using-Open-Office-Base-3.2.html


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Peter Koczan <pjkoczan@gmail.com> writes:

> On Wed, Aug 4, 2010 at 4:40 PM, Richard Broersma
> <richard.broersma@gmail.com> wrote:
(...)
>> I know that there is a setting in the ODBC driver for true = -1 but it
>> doesn't work well.  I also remember that filters didn't work on mapped
>> boolean columns.
>
> Yep, that's the stumbling block we're running into. ODBC and these
> fields' assumptions of true/false are at odds. I'm trying a few other
> things with casts in the meantime to see if they'll work.

I'm still supporting a legacy Access front-end that I converted over
to PostgreSQL a few years back, and did have to do some work for
boolean fields.  It's been a while, but I believe I:
 * Disabled the ODBC driver option "Bools as Char" * Did not enable the "True=-1" option in the driver * Ensured that
noboolean fields were nullable (otherwise in a query   that Access uses to determine if a record changed prior to
postingits   own changes the NULL value would become False by the time it reached   Access, and Access would then fail
topost changes believing the row had   already been edited). * Added a series of functions for integer/boolean
comparisons,so   filters and queries would work.
 

Outside of boolean support, I also needed to add a "lo" domain to work with
some fields across the interface (I think text fields that were memo on the
Access side, but it's been a while).

I think this page was the most helpful overall in identifying the most
stuff in one place while I was getting things set up.  My
boolean/integer comparison functions are also from this page:
   http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html

Note that it recommends enabling the True=-1 option in the driver, but I don't
appear to be running with that, so either it didn't make a difference, or it
caused me problems, I can't be remember.

But with the above, I'm not having any problems with Access working against
the database, including using the boolean fields in filters or queries.

-- David

On Wed, Aug 4, 2010 at 8:49 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Wed, Aug 4, 2010 at 3:41 PM, Peter Koczan <pjkoczan@gmail.com> wrote:
>
>> Yep, that's the stumbling block we're running into. ODBC and these
>> fields' assumptions of true/false are at odds. I'm trying a few other
>> things with casts in the meantime to see if they'll work.
>
> Well there is a solution that I've been toying around with.  In
> PostgreSQL, there are many data-types that cannot be expressed
> directly in an MS-Access Linked table.  For example, composite types,
> arrays, range types, hstores, postgis types et.al.   However, most of
> these types can be decomposed in to base types that can be express in
> linked tables.
>
> The key is using update-able views to decompose the data for Access
> and re-assemble it before it transmitted back to the base table.  The
> same can be done for boolean datatype.
>
>
>> Does anyone know if another product, like OpenOffice Base with its
>> native postgres driver, does any better?
>
> From my limited experience, I believe is does do better.  The
> following blogs as a few entries about using Base:
>
> http://www.postgresonline.com/journal/archives/167-Importing-data-into-PostgreSQL-using-Open-Office-Base-3.2.html

The goal of this is to be as straight a port as possible (as part of a
larger project that's a pretty straight port). The update-able views
and using Open Office are good ideas for when we finally get around to
redesigning the database, whenever that will happen.

Anyway, we got this mostly working. There were a couple other quirks
we found that we have since fixed.

- Access does not like LongVarChar types to be primary keys. If you
are keying on text types, set TextAsLongVarchar=0. It's probably not
the best idea to have text as a primary key in general, but sometimes
that's what the legacy gives you to work with.

- To fix the incompatibility in the bit/boolean type, we mapped the
drop-down menu input to have "Yes" == 1 instead of -1. We had to do
that for each input. It was tedious, but workable. Again, this was
necessary because of legacy and the other workarounds we put in to
account for it.

Thanks for all your help.
Peter