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




Fri, 23 Nov 2001 21:09:45 -0800
Joe Conway <joseph.conway@home.com>
bytea/ODBC/MSAccess issue
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

Sat, 24 Nov 2001 00:42:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: bytea/ODBC/MSAccess issue 
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


Sat, 24 Nov 2001 11:03:28 -0800
Joe Conway <joseph.conway@home.com>
Re: bytea/ODBC/MSAccess issue
> -----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

Mon, 26 Nov 2001 06:34:49 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: bytea/ODBC/MSAccess issue 
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


Sun, 25 Nov 2001 23:19:54 -0800
Joe Conway <joseph.conway@home.com>
Re: bytea/ODBC/MSAccess issue