Thread: Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
> 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?
Hello,
Irine report problem with ODBC. I take deep look at it and find that
lib return base type aid instead of type aid. I'll describe it better.
We have this test suite:
CREATE DOMAIN lo as oid;
CREATE TABLE ow)(b lo);
insert one row:
INSERT INTO es=# select oid from pg_type where typname='lo'; VALUES (lo_import('file'));
we try get it throught libpq (maybe I miss some command):
- PQsetNoticeProcessor(, CC_handle_notice, qres);
- pgres = PQexec(pgconn,query);
- PQsetNoticeProcessor(pgconn, CC_handle_notice, NULL);
...
- typid = PQftype(pgres,i);
this return typid = 26 (oid) instead of 25087 (lo)
postgres=# select oid from pg_type where typname='oid';
oid
-----
26
(1 row)
postgres=# select oid from pg_type where typname='lo';
oid
-------
25087
(1 row)
Is there a way to get 25087? It seems this behaviour is changed between
PgSQL 7.3 and 7.4. It looks like backend issue. Becouse with same binary
it return different values for 7.3 and >= 7.4 (all PgSQL > 7.3 return
typid = 26 - directly tested 7.4, 8.1).
Thanks a lot for help
Luf
Oh, I paste bad text and with combination of vi it gets into bad result.
I correct it now.
> 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?
Hello,
Irina reports problem with ODBC. I take deep look at it and find that
libpq returns base type oid instead of type oid. I'll describe it better.
We have this test suite:
CREATE DOMAIN lo as oid;
CREATE TABLE justlo(b lo);
insert one row:
INSERT INTO justlo VALUES (lo_import('file'));
we try get it throught libpq (maybe I miss some command):
SELECT b FROM justlo;
- PQsetNoticeProcessor(pgconn, CC_handle_notice, qres);
- pgres = PQexec(pgconn,query);
- PQsetNoticeProcessor(pgconn, CC_handle_notice, NULL);
...
- typid = PQftype(pgres,i);
this returns typid = 26 (oid) instead of 25087 (lo)
postgres=# select oid from pg_type where typname='oid';
oid
-----
26
(1 row)
postgres=# select oid from pg_type where typname='lo';
oid
-------
25087
(1 row)
Is there any way to get 25087? It seems this behaviour is changed
between PgSQL 7.3 and 7.4. It looks like backend issue. Becouse it
returns different values with same binary for 7.3 and >= 7.4
(all PgSQL >= 7.4 returns typid = 26 - directly tested 7.4, 8.1).
Thanks a lot for help
Luf
Ludek Finstrle <luf@pzkagis.cz> writes:
> Irina reports problem with ODBC. I take deep look at it and find that
> libpq returns base type oid instead of type oid. I'll describe it better.
Yes, this was an intentional backend-side change. For most purposes
it's the right thing.
regards, tom lane
> > Irina reports problem with ODBC. I take deep look at it and find that > > libpq returns base type oid instead of type oid. I'll describe it better. > > Yes, this was an intentional backend-side change. For most purposes > it's the right thing. Is there any way to get type oid? Or we have to exec another query againist system tables to get type oid. Or is there any other flag saying we can use lo_import for the column? I don't want to reinvent the wheel. Thanks Luf
Ludek Finstrle <luf@pzkagis.cz> writes:
> I don't want to reinvent the wheel.
Why do you feel a need to distinguish the domain from its underlying
type on the client side? They're the same as regards representation
and so on.
The reason for the backend change was that client-side drivers (such as
JDBC and ODBC) want to know the underlying datatype so that they know
what representation to use etc. Distinguishing domains made their job
harder not easier.
If you want an add-on datatype that is really different from OID, then
make a real datatype (CREATE TYPE). You can still piggyback on OID as
the representation type --- steal its I/O functions and so on.
regards, tom lane
> > I don't want to reinvent the wheel. > > Why do you feel a need to distinguish the domain from its underlying > type on the client side? They're the same as regards representation > and so on. I need to determine wheter to use lo_import for large objects. There is implementation in ODBC used type named "lo" (comapring type oid). Type oid doesn't represent only large objects. > what representation to use etc. Distinguishing domains made their job > harder not easier. I agree with you except lo implementation in ODBC ;-) > If you want an add-on datatype that is really different from OID, then > make a real datatype (CREATE TYPE). You can still piggyback on OID as > the representation type --- steal its I/O functions and so on. Does it cover lo_export which need oid as second parameter? I'm sorry I'm new in using large objects and creating new types. Thanks a lot Luf
Ludek Finstrle <luf@pzkagis.cz> writes:
>> If you want an add-on datatype that is really different from OID, then
>> make a real datatype (CREATE TYPE). You can still piggyback on OID as
>> the representation type --- steal its I/O functions and so on.
> Does it cover lo_export which need oid as second parameter?
You could make an implicit cast from lo to oid ... perhaps not the other
direction, though.
regards, tom lane
Tom Lane wrote: >Ludek Finstrle <luf@pzkagis.cz> writes: > > >>I don't want to reinvent the wheel. >> >> > >Why do you feel a need to distinguish the domain from its underlying >type on the client side? They're the same as regards representation >and so on. > >The reason for the backend change was that client-side drivers (such as >JDBC and ODBC) want to know the underlying datatype so that they know >what representation to use etc. Distinguishing domains made their job >harder not easier. > > >If you want an add-on datatype that is really different from OID, then >make a real datatype (CREATE TYPE). You can still piggyback on OID as >the representation type --- steal its I/O functions and so on. > > A clear example situation in here is how to be able to distinguish a large object field. The ODBC driver, for instance, uses the 'lo' type, which is the same as an oid (Large Object). I ran into exactly the same situation as I wrote the pgExpress driver for Vita Voom Software: while declaring a domain looks like the clear choice, the original type's oid would be returned by the pq_ftype() function. So I based my solution more or less like this post by Hiroshi Saito: http://www.mail-archive.com/pgadmin-hackers@postgresql.org/msg01390.html ... and asked the users to create the 'lo' type that way, which would create a real type, just like Tom suggests. For more details on how it was implemented on the pgExpress, please check kits documentation: http://www.vitavoom.com/Products/pgExpress_Driver/docs/advanced_features.html#large_objects_declaration Best regards, Steve Howe
> > Does it cover lo_export which need oid as second parameter? > > You could make an implicit cast from lo to oid ... perhaps not the other > direction, though. Thank you. This way helps. I have to create implicit cast in oid->lo too becouse there is lo_import function. Is any reason to don't do it? I don't see this reasen as lo is same type as oid. 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; Thank you very much Luf