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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: BLOB handling compatibility with PostgreSQL > 7.4

> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: BLOB handling compatibility with PostgreSQL > 7.4

I compiled psqlodbc with --enable-unicode=no , didn't help.



I'll try to give some details:



On 2 postgres servers ( running 7.3.6 and 7.4.2 ) I did the following:



create domain lo as oid;

create table justlo(b lo);



Then  I inserted an entry from a text file via a program that uses
libodbc++ ( attached below )

insert into justlo values(lo_import('/usr/local/pgsql/text.txt'))



The following 2 programs ( first uses libodbc++, second - plain ODBC )
run fine when connecting to postgres 7.3 and crash with postgres 7.4:

/*

 odbc++ example

*/

#include <sstream>

#include <iostream>

#include <string>

#include <odbc++/connection.h>

#include <odbc++/setup.h>

#include <odbc++/types.h>

#include <odbc++/errorhandler.h>

#include <sql.h>

#include <odbc++/drivermanager.h>

#include <odbc++/resultset.h>

#include <odbc++/resultsetmetadata.h>

#include <odbc++/preparedstatement.h>

#include <odbc++/databasemetadata.h>

#include <fstream>



using namespace odbc;

using namespace std;



int main(int argc, char *argv[])

{

  Connection* con = 0;

  Statement* stmt = 0;

  ResultSet* rs = 0;

  string query;

  unsigned int numcol;



  try

    {

       con = DriverManager::getConnection("test74", "postgres", "");

       //con = DriverManager::getConnection("test", "postgres", "");

      cout << con->getMetaData()->getDriverVersion()
<< endl;

    }

  catch (SQLException& e)

    {

      cout << e.getMessage() << endl;

      return 1;

    }

 

  //on nuvi, postgreSQL 7.3.6

  query = "insert into justlo
values(lo_import('/usr/local/pgsql/text.txt'))";

  //on sql, postgreSQL 7.4.2

  //query = "insert into justlo
values(lo_import('/var/lib/pgsql/text.txt'))";



  stmt = con->createStatement();

  try{

    //stmt->executeUpdate(query.c_str());

  }

  catch (SQLException& e)

    {

      cout << e.getMessage() << endl;

      return 1;

    }

 

  query = "select * from justlo";

  stmt = con->createStatement();

  try{

    rs = stmt->executeQuery(query.c_str());

  }

  catch (SQLException& e)

    {

      cout << e.getMessage() << endl;

      return 1;

    }

 

  char str[50];

  while( rs->next()){   

    istream * ms = rs->getBinaryStream(1);

    ms->getline(str,50);

    cout << str << endl;

  }



  delete con;

  return 0;

}



========================

/* odbc.c

testing psqlodbc with postgreSQL 7.3.6 and 7.4.2

*/

#include <stdlib.h>

#include <stdio.h>

#include <sql.h>

#include <sqlext.h>

#include <sqltypes.h>



SQLHENV             V_OD_Env;     // Handle ODBC environment

long             V_OD_erg;     // result of functions

SQLHDBC             V_OD_hdbc;    // Handle connection

char             V_OD_stat[10]; // Status SQL

SQLINTEGER         V_OD_err,V_OD_rowanz,V_OD_id;

SQLSMALLINT         V_OD_mlen;

char                     V_OD_msg[200],V_OD_buffer[200];

SQLHSTMT                 V_OD_hstmt;   // Handle for a statement

SQLINTEGER               V_OD_err,V_OD_id;

char                     V_OD_buffer[200];



int main(int argc,char *argv[])

{

  // 1. allocate Environment handle and register version

  V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,& V_OD_Env);

  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))

    {

      printf("Error AllocHandle\n");

      exit(0);

    }

  V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION,

             (void*)SQL_OV_ODBC3, 0);

  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))

    {

      printf("Error SetEnv\n");

      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);

      exit(0);

    }

  // 2. allocate connection handle, set timeout

  V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);

  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))

    {

      printf("Error AllocHDB %d\n",V_OD_erg);

      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);

      exit(0);

    }

  SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);

  // 3. Connect to the datasource "test"

  V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "test", SQL_NTS,

            (SQLCHAR*) "postgres", SQL_NTS,

            (SQLCHAR*) "", SQL_NTS);

  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))

    {

      printf("Error SQLConnect %d\n",V_OD_erg);

      SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1,

            V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen);

      printf("%s (%d)\n",V_OD_msg,V_OD_err);

      SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc);

      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);

      exit(0);

    }

  printf("Connected !\n");



  SQLRETURN retcode;

  SQLHSTMT hstmt;

  SQLCHAR       BinaryPtr[50];

  SQLINTEGER     BinaryLen;



  SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &hstmt);



  retcode = SQLExecDirect(hstmt,"SELECT b  FROM justlo",SQL_NTS);



  if (retcode == SQL_SUCCESS) {

    retcode = SQLFetch(hstmt);

    if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {

      printf(" error \n" );

    }

    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){

      SQLGetData(hstmt, 1, SQL_C_BINARY, BinaryPtr,
sizeof(BinaryPtr),&BinaryLen);

      printf( " %d, %s ",  BinaryLen, BinaryPtr);

    }

  }

  else{

    printf(" error on select\n" );

  }

}

===================================================



I tried to debug and put som code into odbc++, here is the difference
between 2 postgres versions:



with 7.3, no problem:



entering getBinaryStream

DataHandler::getStream. cType_: -2  sqlType -4



with 7.4, segfault:



entering getBinaryStream

DataHandler::getStream. cType_: 4  sqlType 4

UNSUPPORTED_GET

[libodbc++]: Could not get SQL type 4 (INTEGER), C type 4 (SQL_C_LONG)
as an stream



Hope this helps.

Thanks,

Irina



Ludek Finstrle wrote:

cite="mid20051130094509.GA6109@soptik.pzkagis.cz">

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



---------------------------(end of broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster

  

-- 

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: 

Re: BLOB handling compatibility with PostgreSQL > 7.4

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

---------------------------(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

Data showing up as #Deleted in Access

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

Re: Data showing up as #Deleted in Access

This is a multi-part message in MIME format.
--------------090001020906070705000801
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------090001020906070705000801
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------090001020906070705000801
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--------------090001020906070705000801--

Re: 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

Re: Data showing up as #Deleted in Access

> 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

---------------------------(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

Re: Data showing up as #Deleted in Access

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"
To: "'Campbell, Greg'"
Cc:
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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Data showing up as #Deleted in Access

"Ludek Finstrle" 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




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Data showing up as #Deleted in Access

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Data showing up as #Deleted in Access

> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Data showing up as #Deleted in Access

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.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Application crash after error

> > 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 ...

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: [INTERFACES] BLOB handling compatibility with PostgreSQL > 7.4

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4

Ludek Finstrle 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4

> > 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4

Ludek Finstrle 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

---------------------------(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

Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4

> > 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: [ODBC] BLOB handling compatibility with PostgreSQL

Tom Lane wrote:

>Ludek Finstrle 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/m sg01390.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/advan ced_features.html#large_objects_declaration

Best regards,
Steve Howe

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: [ODBC] BLOB handling compatibility with PostgreSQL > 7.4

Ludek Finstrle 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: [INTERFACES] BLOB handling compatibility with PostgreSQL > 7.4

> > 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: 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?

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: BLOB handling compatibility with PostgreSQL > 7.4

Ludek Finstrle 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.




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: BLOB handling compatibility with PostgreSQL > 7.4

Marc Herbert wrote:
> Ludek Finstrle 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/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: BLOB handling compatibility with PostgreSQL > 7.4

Peter Eisentraut writes:

> Marc Herbert wrote:
>> Ludek Finstrle 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.






---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: BLOB handling compatibility with PostgreSQL > 7.4

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/

---------------------------(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

Re: BLOB handling compatibility with PostgreSQL > 7.4

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: BLOB handling compatibility with PostgreSQL > 7.4

Peter Eisentraut 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".




>> 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.




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.




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: BLOB handling compatibility with PostgreSQL > 7.4

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".
>

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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: BLOB handling compatibility with PostgreSQL > 7.4

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/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: BLOB handling compatibility with PostgreSQL > 7.4

Peter Eisentraut writes:

>>
>
> 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.



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: BLOB handling compatibility with PostgreSQL > 7.4

> > 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

---------------------------(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