Thread: BLOB handling compatibility with PostgreSQL > 7.4
Dear experts, 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? Thank you, Irina -- Irina Sourikova Brookhaven National Laboratory phone: +1-631-344-3776 Physics Department, Bldg 510 C fax: +1-631-344-3253 Upton, NY 11973-5000 email: irina@bnl.gov
> We use unixODBC-2.2.11 and psqlodbc-08.01.0101. I don't exactly know how it's on linux. But which version of psqlodbc do you use (unicode x ansi). Try the second type and let us know if it helps. > With postgres 7.3 lo type was mapped to SQL_C_BINARY and that's changed > since postgres 7.4. That's changed to what type? Luf
I compiled psqlodbc with --enable-unicode=no , didn't help.<br /><br /> I'll try to give some details:<br /><br /> On
2postgres servers ( running 7.3.6 and 7.4.2 ) I did the following:<br /><br /> create domain lo as oid;<br /> create
tablejustlo(b lo);<br /><br /> Then I inserted an entry from a text file via a program that uses libodbc++ ( attached
below) <br /> insert into justlo values(lo_import('/usr/local/pgsql/text.txt'))<br /><br /> The following 2 programs (
firstuses libodbc++, second - plain ODBC ) run fine when connecting to postgres 7.3 and crash with postgres 7.4:<br />
/*<br/> odbc++ example<br /> */<br /> #include <sstream><br /> #include <iostream><br /> #include
<string><br/> #include <odbc++/connection.h><br /> #include <odbc++/setup.h><br /> #include
<odbc++/types.h><br/> #include <odbc++/errorhandler.h><br /> #include <sql.h><br /> #include
<odbc++/drivermanager.h><br/> #include <odbc++/resultset.h><br /> #include
<odbc++/resultsetmetadata.h><br/> #include <odbc++/preparedstatement.h><br /> #include
<odbc++/databasemetadata.h><br/> #include <fstream><br /><br /> using namespace odbc;<br /> using namespace
std;<br/><br /> int main(int argc, char *argv[])<br /> {<br /> Connection* con = 0;<br /> Statement* stmt = 0;<br
/> ResultSet* rs = 0;<br /> string query;<br /> unsigned int numcol;<br /><br /> try<br /> {<br />
con= DriverManager::getConnection("test74", "postgres", "");<br /> //con = DriverManager::getConnection("test",
"postgres","");<br /> cout << con->getMetaData()->getDriverVersion() << endl;<br /> }<br />
catch (SQLException& e)<br /> {<br /> cout << e.getMessage() << endl;<br /> return
1;<br/> }<br /> <br /> //on nuvi, postgreSQL 7.3.6<br /> query = "insert into justlo
values(lo_import('/usr/local/pgsql/text.txt'))";<br/> //on sql, postgreSQL 7.4.2<br /> //query = "insert into
justlovalues(lo_import('/var/lib/pgsql/text.txt'))";<br /><br /> stmt = con->createStatement();<br /> try{<br />
//stmt->executeUpdate(query.c_str());<br /> }<br /> catch (SQLException& e)<br /> {<br /> cout
<<e.getMessage() << endl; <br /> return 1;<br /> }<br /> <br /> query = "select * from
justlo";<br/> stmt = con->createStatement();<br /> try{<br /> rs = stmt->executeQuery(query.c_str());<br
/> }<br /> catch (SQLException& e)<br /> {<br /> cout << e.getMessage() << endl; <br />
return 1;<br /> }<br /> <br /> char str[50];<br /> while( rs->next()){ <br /> istream * ms =
rs->getBinaryStream(1);<br/> ms->getline(str,50);<br /> cout << str << endl;<br /> }<br
/><br/> delete con;<br /> return 0;<br /> }<br /><br /> ========================<br /> /* odbc.c<br /> testing
psqlodbcwith postgreSQL 7.3.6 and 7.4.2<br /> */<br /> #include <stdlib.h><br /> #include <stdio.h><br />
#include<sql.h><br /> #include <sqlext.h><br /> #include <sqltypes.h><br /><br /> SQLHENV
V_OD_Env; // Handle ODBC environment<br /> long V_OD_erg; // result of functions<br /> SQLHDBC
V_OD_hdbc; // Handle connection<br /> char V_OD_stat[10]; // Status SQL<br /> SQLINTEGER
V_OD_err,V_OD_rowanz,V_OD_id;<br/> SQLSMALLINT V_OD_mlen;<br /> char
V_OD_msg[200],V_OD_buffer[200];<br/> SQLHSTMT V_OD_hstmt; // Handle for a statement<br />
SQLINTEGER V_OD_err,V_OD_id;<br /> char V_OD_buffer[200];<br /><br /> int main(int
argc,char*argv[])<br /> {<br /> // 1. allocate Environment handle and register version <br />
V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);<br/> if ((V_OD_erg != SQL_SUCCESS) &&
(V_OD_erg!= SQL_SUCCESS_WITH_INFO))<br /> {<br /> printf("Error AllocHandle\n");<br /> exit(0);<br />
}<br /> V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, <br /> (void*)SQL_OV_ODBC3, 0); <br
/> if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))<br /> {<br /> printf("Error
SetEnv\n");<br/> SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);<br /> exit(0);<br /> }<br /> // 2. allocate
connectionhandle, set timeout<br /> V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); <br /> if
((V_OD_erg!= SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))<br /> {<br /> printf("Error
AllocHDB%d\n",V_OD_erg);<br /> SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);<br /> exit(0);<br /> }<br />
SQLSetConnectAttr(V_OD_hdbc,SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);<br /> // 3. Connect to the datasource "test" <br
/> V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "test", SQL_NTS,<br /> (SQLCHAR*) "postgres", SQL_NTS,<br
/> (SQLCHAR*) "", SQL_NTS);<br /> if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))<br/> {<br /> printf("Error SQLConnect %d\n",V_OD_erg);<br />
SQLGetDiagRec(SQL_HANDLE_DBC,V_OD_hdbc,1, <br /> V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen);<br
/> printf("%s (%d)\n",V_OD_msg,V_OD_err);<br /> SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc);<br />
SQLFreeHandle(SQL_HANDLE_ENV,V_OD_Env);<br /> exit(0);<br /> }<br /> printf("Connected !\n");<br /><br />
SQLRETURNretcode;<br /> SQLHSTMT hstmt;<br /> SQLCHAR BinaryPtr[50];<br /> SQLINTEGER BinaryLen;<br
/><br/> SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &hstmt);<br /><br /> retcode = SQLExecDirect(hstmt,"SELECT
b FROM justlo",SQL_NTS);<br /><br /> if (retcode == SQL_SUCCESS) {<br /> retcode = SQLFetch(hstmt);<br /> if
(retcode== SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {<br /> printf(" error \n" );<br /> }<br /> if
(retcode== SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){<br /> SQLGetData(hstmt, 1, SQL_C_BINARY, BinaryPtr,
sizeof(BinaryPtr),&BinaryLen);<br/> printf( " %d, %s ", BinaryLen, BinaryPtr);<br /> }<br /> }<br />
else{<br/> printf(" error on select\n" );<br /> }<br /> }<br />
===================================================<br/><br /> I tried to debug and put som code into odbc++, here is
thedifference between 2 postgres versions:<br /><br /> with 7.3, no problem:<br /><br /> entering getBinaryStream<br />
DataHandler::getStream.cType_: -2 sqlType -4<br /><br /> with 7.4, segfault:<br /><br /> entering getBinaryStream<br
/>DataHandler::getStream. cType_: 4 sqlType 4<br /> UNSUPPORTED_GET<br /> [libodbc++]: Could not get SQL type 4
(INTEGER),C type 4 (SQL_C_LONG) as an stream<br /><br /> Hope this helps.<br /> Thanks,<br /> Irina<br /><br /> Ludek
Finstrlewrote:<br /><blockquote cite="mid20051130094509.GA6109@soptik.pzkagis.cz" type="cite"><blockquote
type="cite"><prewrap="">We use unixODBC-2.2.11 and psqlodbc-08.01.0101. </pre></blockquote><pre wrap="">
I don't exactly know how it's on linux. But which version of psqlodbc
do you use (unicode x ansi). Try the second type and let us know
if it helps.
</pre><blockquote type="cite"><pre wrap="">With postgres 7.3 lo type was mapped to SQL_C_BINARY and that's changed
since postgres 7.4. </pre></blockquote><pre wrap="">
That's changed to what type?
Luf
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster </pre></blockquote><br /><pre class="moz-signature" cols="72">--
Irina Sourikova
Brookhaven National Laboratory phone: +1-631-344-3776
Physics Department, Bldg 510 C fax: +1-631-344-3253
Upton, NY 11973-5000 email: <a class="moz-txt-link-abbreviated"
href="mailto:irina@bnl.gov">irina@bnl.gov</a>
</pre>
Hello, could you try newer version of psqlodbc? There is version 8.01.0102 or try using CVS version, please. I need to know your psqlodbc settings and backend encoding. I have no time for this problem till end of week so be patient. Luf
I'm trying to get ODBC working for the first time so that I can access a Postgres database from Access. I'm running PostgrSQL 8.0.3, PostgreSQL ODBC 8.01.01.01, and Access 2003 version 11.6355.6360 SP1. The database is on a system running RedHat Linux 2.6.9-6.37-EL and the client is running Windows XP SP2. When I try to open the ODBC data source in Access I see the list of tables correctly. If I pick one of the tables and open it I get the correct field names and the correct number of rows, but every field in every row says "#Deleted" instead of having data. Can anyone suggest what might be wrong? Thanks, Chris
Access is trying to maintain an updateable recordset when you just open a table. For an updateable table it need to be able to identify each record, in case you trying to change a value on the record. So it tries to determine if there is a primary key or by matching each field, can it determine a virtual primary key. If it knows the record "was/is" there but cannot determine a primary key, it says deleted -- This may not be exact in the detail, but seems to be the principle. The situation is usually aided by having tables with good primary keys. If you change the indexes or keys, update the linked table in Access. It is also helped sometimes by turning on row-versioning for the connection -- I usually use the ODBC administrator and then use Access to refresh the linked tables, or drop and re-link the tables. If these steps do not help, post a response for more help. You can also search this mailing lists website archives for the "deleted" thing. Chris Moore wrote: > I'm trying to get ODBC working for the first time so that I can access > a Postgres database from Access. I'm running PostgrSQL 8.0.3, PostgreSQL > ODBC 8.01.01.01, and Access 2003 version 11.6355.6360 SP1. The database > is on a system running RedHat Linux 2.6.9-6.37-EL and the client is running > Windows XP SP2. > > When I try to open the ODBC data source in Access I see the list of tables > correctly. If I pick one of the tables and open it I get the correct field > names and the correct number of rows, but every field in every row says > "#Deleted" > instead of having data. > > Can anyone suggest what might be wrong? > > Thanks, > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Attachment
I verified that the primary key was correct, and also turned on row versioning, but still got the same thing. Then I noticed that it was only one particular table that was having problems. It occurred to me that this table had several fields of type "text", one of which had rows in which this field had close to a thousand characters in it. I changed all the "text" fields to "varchar", making the one big one a varchar(2048). That seems to have fixed the problem. By the way, I did try searching for "#Deleted", but the search engine tries to be helpful and also find instances of "delete", "deleting", etc. There were just too many hits to be able to weed through them all and find the relevant ones. Thanks for your help. Chris
> Then I noticed that it was only one particular table that was having > problems. It occurred to me that this table had several fields of > type "text", one of which had rows in which this field had close to It seems current ODBC driver has problem with "text". Please could you fill bug report with attached mylog output to psqlodbc bug tracker at pgFoundry.org? You could attach minimalistic exe file with table specification. It helps us reproduce the problem by us. Thanks Luf
Hello. Just redesign your database. You need to have numeric primary key field in every table. Don't use any other type as primary key, especially not any kind of textual fields, because Access won't be able to refresh data properly and you will face "#Deleted", which is very common problem with Access/ODBC linked tables. You can find an article about "#Deleted" on www.microsoft.com, too. The best way to avoid "#Deleted" is to have a serial field (autonumber) as primary key, in every table. That will solve your problem forever. Zlatko ----- Original Message ----- From: "Chris Moore" <chrismoore@surewest.net> To: "'Campbell, Greg'" <greg.campbell@us.michelin.com> Cc: <pgsql-odbc@postgresql.org> Sent: Thursday, December 01, 2005 7:40 PM Subject: Re: [ODBC] Data showing up as #Deleted in Access >I verified that the primary key was correct, and also turned on > row versioning, but still got the same thing. > > Then I noticed that it was only one particular table that was having > problems. It occurred to me that this table had several fields of > type "text", one of which had rows in which this field had close to > a thousand characters in it. I changed all the "text" fields to > "varchar", making the one big one a varchar(2048). That seems to have > fixed the problem. > > By the way, I did try searching for "#Deleted", but the search engine > tries to be helpful and also find instances of "delete", "deleting", > etc. There were just too many hits to be able to weed through them > all and find the relevant ones. > > Thanks for your help. > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
"Ludek Finstrle" <luf@pzkagis.cz> wrote in message news:20051201195932.GA16735@soptik.pzkagis.cz... >> Then I noticed that it was only one particular table that was having >> problems. It occurred to me that this table had several fields of >> type "text", one of which had rows in which this field had close to > > It seems current ODBC driver has problem with "text". Please could > you fill bug report with attached mylog output to psqlodbc bug > tracker at pgFoundry.org? > You could attach minimalistic exe file with table specification. It > helps us reproduce the problem by us. Luf, my problem in thread "application crash after error" occurs also only with text field. So this may be the same problem . Andrus
> my problem in thread "application crash after error" occurs also only with > text field. > So this may be the same problem . I don't think so. Your problem is specific. I try to describe it as good as I can. I hope Dave or another guru can show me the right way. I can correct your problem but I don't know if it doesn't break another scenario ... I don't give my hand off your problem. I only need advice from someone who knows ODBC (not psqlODBC) better. Hmmm. I get idea right now. I'll try it againist MySQL. It's ODBC is open source so I can learn from it ... Please could you change your example to support MySQL ODBC? Maybe (or maybe not) it helps. So please be patient I'm doing everything I can Luf
Ludek,
> Hmmm. I get idea right now. I'll try it againist MySQL. It's ODBC is
> open source so I can learn from it ...
> Please could you change your example to support MySQL ODBC?
> Maybe (or maybe not) it helps.
If you change the connection string in testsql.prg and remove WITHOUT OIDS
clauses in CREATE TABLE commands I think this sample must work with other
odbc drivers also.
Also, I do'nt know will mysql support CREATE temp TABLE clause. If it does
not temp can be removed also. So the TEXT command in testsql.prg may be
TEXT TO csql TEXTMERGE noshow
CREATE TABLE klient (
kood char(12),
nimi char(70),
info text);
CREATE UNIQUE INDEX klient_nimi_unique_idx ON klient(nimi);
CREATE temp TABLE dok( doktyyp char(1) );
insert into klient (kood,nimi) values ('AKU', 'Akuexpert O');
endtext
also the line in former testsql.prg
insert into testk (kood,nimi,info) values ('AKU', 'Akuexpert O�','')
should me changed to
insert into testk (kood,nimi,info) values ('AKU', 'Akuexpert O','')
to avoid possible issues with � character. After making changes main.exe
compiles and runs testsql.prg automatically.
I do'nt have mysql server access. Please re-confirm if you need that I must
verify this code in mysql. In this
case I must install mysql in my development computer. Or maybe there is some
mysql test server accessible over internet?
> So please be patient I'm doing everything I can
Thank you. I have one unchecked thought. If null instead of empty string is
passed as value to text type column, maybe the error does not occur.
Andrus.
> > Hmmm. I get idea right now. I'll try it againist MySQL. It's ODBC is > > open source so I can learn from it ... > > Please could you change your example to support MySQL ODBC? > > Maybe (or maybe not) it helps. > > If you change the connection string in testsql.prg and remove WITHOUT OIDS > clauses in CREATE TABLE commands I think this sample must work with other > odbc drivers also. As I wrote you main.exe doesn't use testsql.prg. I changed connect string in testsql.prg but connect string in ODBC still contains user 'postgresql' with password you typed. It didn't use my changes in testsql.prg. > Thank you. I have one unchecked thought. If null instead of empty string is > passed as value to text type column, maybe the error does not occur. I don't think so. It's text column so it need SQLPutData. That's the biggest problem. Luf P.S. I change subject as it isn't about #Deleted ...
> 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
> > 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
> 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
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
Hello, There is a workaround, just check the "Show OID column" and "Fake index" in the ODBC configuration. This used to work for me (some time ago, I must say). Regards, Miguel Juan
Ludek Finstrle <luf@pzkagis.cz> writes: > Type oid doesn't represent only large objects. This is IMHO the original sin. oid is overloaded as both an internal type and something the basic user has to play with. <http://archives.postgresql.org/pgsql-jdbc/2005-07/msg00322.php>
Marc Herbert wrote: > Ludek Finstrle <luf@pzkagis.cz> writes: > > Type oid doesn't represent only large objects. > > This is IMHO the original sin. oid is overloaded as both an internal > type and something the basic user has to play with. Why aren't you using bytea? Is that not supported by the ODBC driver? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes: > Marc Herbert wrote: >> Ludek Finstrle <luf@pzkagis.cz> writes: >> > Type oid doesn't represent only large objects. >> >> This is IMHO the original sin. oid is overloaded as both an internal >> type and something the basic user has to play with. > > Why aren't you using bytea? Is that not supported by the ODBC driver? We are developping a middleware and are not free to choose our data types. As far as i know, the implementation of bytea and large objects in the database are totally different and I suspect there are good reasons to use either one of them, depending on the actual data and application involved.
Marc Herbert wrote: > We are developping a middleware and are not free to choose our data > types. I was thinking the ODBC driver should provide the appropriate mapping. > As far as i know, the implementation of bytea and large objects in > the database are totally different and I suspect there are good > reasons to use either one of them, depending on the actual data and > application involved. There is no real reason to use large objects for anything instead of bytea. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Hello > > As far as i know, the implementation of bytea and large objects in > > the database are totally different and I suspect there are good > > reasons to use either one of them, depending on the actual data and > > application involved. > > There is no real reason to use large objects for anything instead of > bytea. There is a reason when you have already written application. I don't use lo. I'm new in development of psqlODBC and one request was: lo changed between 7.3 and 7.4 I try to solve it. You can find it in pgsql-odbc archive. It has same subject. I'm sorry I didn't read FAQ for psqlODBC earlier. Regards, Luf
Peter Eisentraut <peter_e@gmx.net> writes: > Marc Herbert wrote: >> We are developping a middleware and are not free to choose our data >> types. > > I was thinking the ODBC driver should provide the appropriate mapping. We aim to be as transparent as possible and avoid "mappings". <http://www.continuent.org/> >> As far as i know, the implementation of bytea and large objects in >> the database are totally different and I suspect there are good >> reasons to use either one of them, depending on the actual data and >> application involved. > > There is no real reason to use large objects for anything instead of > bytea. <http://www.postgresql.org/docs/7.4/static/jdbc-binary-data.html> <http://jdbc.postgresql.org/documentation/head/binary-data.html> PostgreSQL provides two distinct ways to store binary data. Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table. In order to determine which method is appropriate you need to understand the limitations of each method.... Moreover, the respective interfaces have very different semantics (pointer or not). This can be a reason in itself to prefer one or the other type, whatever the underlying implementation is.
Marc Herbert wrote: > > I was thinking the ODBC driver should provide the appropriate > > mapping. > > We aim to be as transparent as possible and avoid "mappings". > <http://www.continuent.org/> So continuent is now writing an ODBC driver for PostgreSQL, or what are you trying to say? > Moreover, the respective interfaces have very different semantics > (pointer or not). This can be a reason in itself to prefer one or the > other type, whatever the underlying implementation is. This is handwaving. I know the section in the documentation; I wrote it. But what actual reasons do you have for using one or the other? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Ludek Finstrle wrote: > There is a reason when you have already written application. > I don't use lo. I'm new in development of psqlODBC and one request > was: lo changed between 7.3 and 7.4 I'm certainly not defending incompatible changes that break applications. I'm just wondering whether these problems can be avoided altogether in the future. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes: >> <http://www.continuent.org/> > > So continuent is now writing an ODBC driver for PostgreSQL, or what are > you trying to say? No more than what you'll find at the link above, if you are interested. And you don't have to; this is becoming more and more irrelevant to this list or to this thread. >> Moreover, the respective interfaces have very different semantics >> (pointer or not). This can be a reason in itself to prefer one or the >> other type, whatever the underlying implementation is. > > This is handwaving. Sorry, I don't get this. > I know the section in the documentation; I wrote it. Then thanks in advance for fixing it, putting it in line with what you said above. PS: unless stated otherwise, I read the forums where I post. Thanks in advance for not sending me copies.
> > There is a reason when you have already written application. > > I don't use lo. I'm new in development of psqlODBC and one request > > was: lo changed between 7.3 and 7.4 > > I'm certainly not defending incompatible changes that break > applications. I'm just wondering whether these problems can be avoided > altogether in the future. I think ODBC driver is doing some mapping with lo. But the driver have to determine if it is LO column or not (when user choose to use lo). Luf