pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedBLOB handling compatibility with PostgreSQL > 7.4
24+ messages / 6 participants
[nested] [flat]
* BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-11-28 21:39 Irina Sourikova <irina@bnl.gov>
0 siblings, 3 replies; 24+ messages in thread
From: Irina Sourikova @ 2005-11-28 21:39 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-11-30 09:45 Ludek Finstrle <luf@pzkagis.cz>
parent: Irina Sourikova <irina@bnl.gov>
2 siblings, 1 reply; 24+ messages in thread
From: Ludek Finstrle @ 2005-11-30 09:45 UTC (permalink / raw)
To: Irina Sourikova <irina@bnl.gov>; +Cc: pgsql-odbc@postgresql.org
> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-11-30 19:16 Irina Sourikova <irina@bnl.gov>
parent: Ludek Finstrle <luf@pzkagis.cz>
0 siblings, 1 reply; 24+ messages in thread
From: Irina Sourikova @ 2005-11-30 19:16 UTC (permalink / raw)
To: Ludek Finstrle <luf@pzkagis.cz>; +Cc: pgsql-odbc@postgresql.org
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
I compiled psqlodbc with --enable-unicode=no , didn't help.<br>
<br>
I'll try to give some details:<br>
<br>
On 2 postgres servers ( running 7.3.6 and 7.4.2 ) I did the following:<br>
<br>
create domain lo as oid;<br>
create table justlo(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 ( first uses 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 justlo
values(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 psqlodbc with 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 connection handle, 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>
SQLRETURN retcode;<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 the difference
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 Finstrle wrote:<br>
<blockquote type="cite"
cite="mid20051130094509.GA6109@soptik.pzkagis.cz">
<blockquote type="cite">
<pre wrap="">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>
</body>
</html>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-01 13:04 Ludek Finstrle <luf@pzkagis.cz>
parent: Irina Sourikova <irina@bnl.gov>
0 siblings, 0 replies; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-01 13:04 UTC (permalink / raw)
To: Irina Sourikova <irina@bnl.gov>; +Cc: pgsql-odbc@postgresql.org
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-05 21:28 Ludek Finstrle <luf@pzkagis.cz>
parent: Irina Sourikova <irina@bnl.gov>
2 siblings, 1 reply; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-05 21:28 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: pgsql-odbc@postgresql.org
> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 14:28 Ludek Finstrle <luf@pzkagis.cz>
parent: Ludek Finstrle <luf@pzkagis.cz>
0 siblings, 1 reply; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-06 14:28 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: pgsql-odbc@postgresql.org
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 15:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ludek Finstrle <luf@pzkagis.cz>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2005-12-06 15:05 UTC (permalink / raw)
To: Ludek Finstrle <luf@pzkagis.cz>; +Cc: pgsql-interfaces; pgsql-odbc@postgresql.org
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 18:44 Ludek Finstrle <luf@pzkagis.cz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-06 18:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces; pgsql-odbc@postgresql.org
> > 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 19:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ludek Finstrle <luf@pzkagis.cz>
0 siblings, 2 replies; 24+ messages in thread
From: Tom Lane @ 2005-12-06 19:07 UTC (permalink / raw)
To: Ludek Finstrle <luf@pzkagis.cz>; +Cc: pgsql-interfaces; pgsql-odbc@postgresql.org
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 19:27 Ludek Finstrle <luf@pzkagis.cz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-06 19:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces; pgsql-odbc@postgresql.org
> > 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL
@ 2005-12-06 19:29 Steve Howe <howe@carcass.dhs.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 24+ messages in thread
From: Steve Howe @ 2005-12-06 19:29 UTC (permalink / raw)
To: ; +Cc: pgsql-interfaces; pgsql-odbc@postgresql.org
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_declarat...
Best regards,
Steve Howe
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 19:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ludek Finstrle <luf@pzkagis.cz>
1 sibling, 1 reply; 24+ messages in thread
From: Tom Lane @ 2005-12-06 19:59 UTC (permalink / raw)
To: Ludek Finstrle <luf@pzkagis.cz>; +Cc: pgsql-interfaces; pgsql-odbc@postgresql.org
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 21:50 Ludek Finstrle <luf@pzkagis.cz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-06 21:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces; pgsql-odbc@postgresql.org
> > 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-06 22:25 Ludek Finstrle <luf@pzkagis.cz>
parent: Irina Sourikova <irina@bnl.gov>
2 siblings, 0 replies; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-06 22:25 UTC (permalink / raw)
To: Irina Sourikova <irina@bnl.gov>; +Cc: pgsql-odbc@postgresql.org
> 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-07 11:21 Marc Herbert <Marc.Herbert@continuent.com>
parent: Ludek Finstrle <luf@pzkagis.cz>
1 sibling, 1 reply; 24+ messages in thread
From: Marc Herbert @ 2005-12-07 11:21 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org; +Cc: pgsql-interfaces
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;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-08 09:55 Peter Eisentraut <peter_e@gmx.net>
parent: Marc Herbert <Marc.Herbert@continuent.com>
0 siblings, 1 reply; 24+ messages in thread
From: Peter Eisentraut @ 2005-12-08 09:55 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
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/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-08 13:59 Marc Herbert <Marc.Herbert@continuent.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 24+ messages in thread
From: Marc Herbert @ 2005-12-08 13:59 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
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.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-08 18:11 Peter Eisentraut <peter_e@gmx.net>
parent: Marc Herbert <Marc.Herbert@continuent.com>
0 siblings, 2 replies; 24+ messages in thread
From: Peter Eisentraut @ 2005-12-08 18:11 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org; +Cc: Marc Herbert <Marc.Herbert@continuent.com>
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/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-09 01:15 Ludek Finstrle <luf@pzkagis.cz>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-09 01:15 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-odbc@postgresql.org
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-09 10:46 Marc Herbert <Marc.Herbert@continuent.com>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 24+ messages in thread
From: Marc Herbert @ 2005-12-09 10:46 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
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.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-09 11:06 Peter Eisentraut <peter_e@gmx.net>
parent: Marc Herbert <Marc.Herbert@continuent.com>
0 siblings, 1 reply; 24+ messages in thread
From: Peter Eisentraut @ 2005-12-09 11:06 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org; +Cc: Marc Herbert <Marc.Herbert@continuent.com>
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/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-09 11:08 Peter Eisentraut <peter_e@gmx.net>
parent: Ludek Finstrle <luf@pzkagis.cz>
0 siblings, 1 reply; 24+ messages in thread
From: Peter Eisentraut @ 2005-12-09 11:08 UTC (permalink / raw)
To: Ludek Finstrle <luf@pzkagis.cz>; +Cc: pgsql-odbc@postgresql.org
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/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-09 11:40 Marc Herbert <Marc.Herbert@continuent.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 24+ messages in thread
From: Marc Herbert @ 2005-12-09 11:40 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
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.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: BLOB handling compatibility with PostgreSQL > 7.4
@ 2005-12-09 14:10 Ludek Finstrle <luf@pzkagis.cz>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 24+ messages in thread
From: Ludek Finstrle @ 2005-12-09 14:10 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-odbc@postgresql.org
> > 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
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2005-12-09 14:10 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-28 21:39 BLOB handling compatibility with PostgreSQL > 7.4 Irina Sourikova <irina@bnl.gov>
2005-11-30 09:45 ` Ludek Finstrle <luf@pzkagis.cz>
2005-11-30 19:16 ` Irina Sourikova <irina@bnl.gov>
2005-12-01 13:04 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-05 21:28 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-06 14:28 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-06 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 18:44 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-06 19:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 19:27 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-06 19:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 21:50 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-07 11:21 ` Marc Herbert <Marc.Herbert@continuent.com>
2005-12-08 09:55 ` Peter Eisentraut <peter_e@gmx.net>
2005-12-08 13:59 ` Marc Herbert <Marc.Herbert@continuent.com>
2005-12-08 18:11 ` Peter Eisentraut <peter_e@gmx.net>
2005-12-09 01:15 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-09 11:08 ` Peter Eisentraut <peter_e@gmx.net>
2005-12-09 14:10 ` Ludek Finstrle <luf@pzkagis.cz>
2005-12-09 10:46 ` Marc Herbert <Marc.Herbert@continuent.com>
2005-12-09 11:06 ` Peter Eisentraut <peter_e@gmx.net>
2005-12-09 11:40 ` Marc Herbert <Marc.Herbert@continuent.com>
2005-12-06 19:29 ` Re: [ODBC] BLOB handling compatibility with PostgreSQL Steve Howe <howe@carcass.dhs.org>
2005-12-06 22:25 ` Ludek Finstrle <luf@pzkagis.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox