Thread: bytea/ODBC/MSAccess issue
I just discovered an issue related to one of the bytea changes in 7.2. When creating a linked table in MS Access, bytea columns get mapped to "OLE Object" as a datatype, and this type is not able to be indexed. Previously, it was not possible to create an index on a bytea column, so this was not an issue, but now you can. Trying to create a link to a table with an index on a bytea column, you get an error: "Invalid field definition 'bytea_field_name' in definition of index or relationship". I confirmed that the index was in fact the issue by dropping it and then successfully creating the link. Any thoughts on how to get around this, short of dropping indexes on all bytea columns? Is their any way to suppress bytea column indexes from view to the ODBC driver? Thanks, Joe
Joe Conway <joseph.conway@home.com> writes:
> When creating a linked table in MS Access, bytea columns get mapped to
> "OLE Object" as a datatype, and this type is not able to be indexed.
Could we make our ODBC driver map bytea to some datatype that Access
doesn't choke on?
Sooner or later our response to this sort of problem will have to be
"MS Access will be first against the wall when the revolution comes".
In the meantime I'm willing to entertain marginal hacks in ODBC...
regards, tom lane
Tom Lane wrote: > Joe Conway <joseph.conway@home.com> writes: > >>When creating a linked table in MS Access, bytea columns get mapped to >>"OLE Object" as a datatype, and this type is not able to be indexed. >> > > Could we make our ODBC driver map bytea to some datatype that Access > doesn't choke on? > I did a bit of testing, and it seems that the MS Access "Memo" datatype (which PostgreSQL TEXT maps to) can handle zero bytes. The help page has this to say: "Up to 65,535 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)" So it seems to indicate it will work for bytea up to 65K in size. I guess it would be ideal if we could control the mapping through the ODBC settings dialog (i.e. either map bytea to Memo or OLE Object as options). I've never hacked on ODBC before, but I guess I'll take a look. Is the release cycle for ODBC tied to that of the main distribution? If so, I guess this is a 7.3 fix. > Sooner or later our response to this sort of problem will have to be > "MS Access will be first against the wall when the revolution comes". :) > In the meantime I'm willing to entertain marginal hacks in ODBC... > I don't use MS Access very often, and could easily do without this change myself, but it is a likely source of complaints after 7.2 is released if nothing is done about it. Joe
> -----Original Message----- > From: Tom Lane > > Joe Conway <joseph.conway@home.com> writes: > > When creating a linked table in MS Access, bytea columns get mapped to > > "OLE Object" as a datatype, and this type is not able to be indexed. > > Could we make our ODBC driver map bytea to some datatype that Access > doesn't choke on? IIRC our ODBC driver maps bytea to SQL_VARBINARY which is able to be indexed in MS Access. Probably Joe is changing the *Max Varchar* option > 255. Currently the mapping of our driver is SQL_VARBINARY (the length <= 255) <---> bytea SQL_LONGVARBINARY (the lengthcan be > 255) <---> lo . MS Access couldn't handle the binary type index > 255 bytes. PostgreSQL hasn't been able to have indexes on bytea until quite recently and bytea is unavailable for LO. Every application has its limitation. regards, Hiroshi Inoue
Hiroshi Inoue wrote: >>-----Original Message----- >>From: Tom Lane >> >>Joe Conway <joseph.conway@home.com> writes: >> >>>When creating a linked table in MS Access, bytea columns get mapped to >>>"OLE Object" as a datatype, and this type is not able to be indexed. >>> >>Could we make our ODBC driver map bytea to some datatype that Access >>doesn't choke on? >> > > IIRC our ODBC driver maps bytea to SQL_VARBINARY which is > able to be indexed in MS Access. Probably Joe is changing the > *Max Varchar* option > 255. > Currently the mapping of our driver is > SQL_VARBINARY (the length <= 255) <---> bytea > SQL_LONGVARBINARY (the length can be > 255) <---> lo > . > MS Access couldn't handle the binary type index > 255 bytes. > PostgreSQL hasn't been able to have indexes on bytea until > quite recently and bytea is unavailable for LO. > Every application has its limitation. > > regards, > Hiroshi Inoue Thanks for the reply, Hiroshi. This advice worked, and after a little research I see (as you said) that the limitation is with MS Access -- so not much we can do :( Joe