Thread: 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:
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.
Yes there is an option to change bools to charOn 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/
- 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.
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.
Attachment
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