Date: Tue, 6 Dec 2005 23:25:30 +0100
From: Ludek Finstrle <luf@pzkagis.cz>
To: Irina Sourikova <irina@bnl.gov>
Cc: pgsql-odbc@postgresql.org
Subject: Re: BLOB handling compatibility with PostgreSQL > 7.4
Message-ID: <20051206222530.GA25779@soptik.pzkagis.cz>
References: <438B792D.6030802@bnl.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <438B792D.6030802@bnl.gov>
User-Agent: Mutt/1.4i

> We would like to upgrade the Postgres version from our current 7.3 but 
> have problems with handling BLOBs via ODBC.
> We use unixODBC-2.2.11 and psqlodbc-08.01.0101.
> With postgres 7.3 lo type was mapped to SQL_C_BINARY and that's changed 
> since postgres 7.4.
> Is it an ODBC or a driver issue? Are there any plans to fix the problem?

This is backend change. You have to change your type definition.

Your type is now:
CREATE DOMAIN lo AS oid;
This doesn't work since PgSQL 7.4 becouse backend returns type oid for
base type (oid not lo).

New way since PgSQL 7.4:
CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );
CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

This way works. I tested it here againist PgSQL 8.1. I looked at PgSQL 7.4
documentation and this way may be supported.
Oh, I read faq (too late) and there is described similar way.

Maybe this can be added to FAQ as this way is more complex.

Luf