Обсуждение: Blob support...

Which release of PostgreSQL? There were numerous changes in BLOB support
from 7.0 to 7.1 to 7.2, your answer depends on the target release of
PostgreSQL.

Benoit Menendez wrote:

> I use both ODBC and JDBC to access the same database (from two different
> applications) and I need blob support...
>
>
>
> I have created and used the "lo" type which is handled correctly by the
> ODBC driver...
>
>
>
> But JDBC does not like the "lo" type for blobs...
>
>
>
> If I do a getBytes on an "lo" I only get 4 (+1) bytes...
>
>
>
> I tried "bytea" but ODBC does not work properly with this type...
>
>
>
> Is the "lo" type supported in a recent patch?
>
>
>
> Thanks,
>
>
>
>     Benoit
>

I use the latest stable 7.2.2 under cygwin

    Benoit

----- Original Message -----
From: "Marc Lavergne" <mlavergne-pub@richlava.com>
To: "Benoit Menendez" <benoitm@pacbell.net>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Tuesday, February 26, 2002 11:13 AM
Subject: Re: [JDBC] Blob support...


> Which release of PostgreSQL? There were numerous changes in BLOB support
> from 7.0 to 7.1 to 7.2, your answer depends on the target release of
> PostgreSQL.
>
> Benoit Menendez wrote:
>
> > I use both ODBC and JDBC to access the same database (from two different
> > applications) and I need blob support...
> >
> >
> >
> > I have created and used the "lo" type which is handled correctly by the
> > ODBC driver...
> >
> >
> >
> > But JDBC does not like the "lo" type for blobs...
> >
> >
> >
> > If I do a getBytes on an "lo" I only get 4 (+1) bytes...
> >
> >
> >
> > I tried "bytea" but ODBC does not work properly with this type...
> >
> >
> >
> > Is the "lo" type supported in a recent patch?
> >
> >
> >
> > Thanks,
> >
> >
> >
> >     Benoit
> >
>
>

Right ... there's a few "bugs" unconfirmed but it is in my eyes in
org.postgresql.jdbc2.PreparedStatement and ResultSet. Look in the source
(postgresql-7.2/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet and
you'll see that the code was branched right around line 330.
Unfortunately, that breaks support for lo types in 7.2. Not I'm not sure
WHAT the official position is on this. However, there are four things
you can do: use the 7.1.3 JDBC driver, custom fix the 7.2 driver (ie.
remove the if statement to revert to 7.1.3 behavior), submit a diff
patch, file a bug and hope somebody fixes it. Obviously, the first and
second options are the easiest. The third and fourth would require some
kind of authoritative statement on what the position on BLOBs in 7.2 is!

Hope this helps and good luck!

Marc

Benoit Menendez wrote:

> I use the latest stable 7.2.2 under cygwin
>
>     Benoit
>
> ----- Original Message -----
> From: "Marc Lavergne" <mlavergne-pub@richlava.com>
> To: "Benoit Menendez" <benoitm@pacbell.net>
> Cc: <pgsql-jdbc@postgresql.org>
> Sent: Tuesday, February 26, 2002 11:13 AM
> Subject: Re: [JDBC] Blob support...
>
>
>
>>Which release of PostgreSQL? There were numerous changes in BLOB support
>>from 7.0 to 7.1 to 7.2, your answer depends on the target release of
>>PostgreSQL.
>>
>>Benoit Menendez wrote:
>>
>>
>>>I use both ODBC and JDBC to access the same database (from two different
>>>applications) and I need blob support...
>>>
>>>
>>>
>>>I have created and used the "lo" type which is handled correctly by the
>>>ODBC driver...
>>>
>>>
>>>
>>>But JDBC does not like the "lo" type for blobs...
>>>
>>>
>>>
>>>If I do a getBytes on an "lo" I only get 4 (+1) bytes...
>>>
>>>
>>>
>>>I tried "bytea" but ODBC does not work properly with this type...
>>>
>>>
>>>
>>>Is the "lo" type supported in a recent patch?
>>>
>>>
>>>
>>>Thanks,
>>>
>>>
>>>
>>>    Benoit
>>>
>>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

I am using 7.2 and I need to write generic JDBC code, so I cannot use the
postgreSQL specific functions. I could use the "bytea" type, but it has
limitations and the ODBC driver does not handle it correctly.

The "standard" type for large objects is the "lo" type which is not
supported (bug?) by the latest stable 7.2 JDBC driver.

If declared, the "lo" type is reported as type 1111 (OTHER) by getTypeInfo
and getBytes() on it will return the 4 bytes (+1 for some reason) object id.

I wish there was a real standard for binary large objects with:

    - standardization of the blob type name ("lo" is fine, but "blob" would
be better)
    - type already defined in the database at creation (no need to create
the type manually)
    - support in ODBC and JDBC of this type
    - support for the JDBC getBlob, getBytes and getBinaryStream functions
(and corresponding setters)
    - built-in vacuuming of blobs as part of the standard vacuuming

This would make it a lot easier... Maybe 7.3?

    Benoit

----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "Benoit Menendez" <benoitm@pacbell.net>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Tuesday, February 05, 2002 3:02 AM
Subject: Re: [JDBC] Blob support...


> Benoit,
>
> What version are you using?  I would strongly recommend using the 7.2
> jdbc driver (it works fine against a 7.1 database).  Also the 7.2 jdbc
> chapter of the documentation has examples on how to use both
> LargeObjects and bytea datatypes via jdbc.
>
> thanks,
> --Barry
>
>
> Benoit Menendez wrote:
> > I use both ODBC and JDBC to access the same database (from two different
> > applications) and I need blob support...
> >
> >
> >
> > I have created and used the "lo" type which is handled correctly by the
> > ODBC driver...
> >
> >
> >
> > But JDBC does not like the "lo" type for blobs...
> >
> >
> >
> > If I do a getBytes on an "lo" I only get 4 (+1) bytes...
> >
> >
> >
> > I tried "bytea" but ODBC does not work properly with this type...
> >
> >
> >
> > Is the "lo" type supported in a recent patch?
> >
> >
> >
> > Thanks,
> >
> >
> >
> >     Benoit
> >
>
>

Ok, my bad it's intended behavior, not a bug. It took a little digging
but I found it. It's in section 8.6 of the JDBC interface documentation:

/usr/local/pgsql/doc/html/jdbc-binary-data.html

Quote:

"It is possible to revert the driver back to the old 7.1 behavior by
setting the compatible property on the Connection to a value of 7.1"

So let's change that to 2 options ... use the 7.1.3 driver or use the
compatible parameter on 7.2 driver!  ;-)

As a side note, I am in total agreement with the "authoritative
statement". I really like the bytea approach to BLOBs much more than the
lo approach. It makes things much much cleaner in the data store.
However, it does make it difficult to interoperate between databases
that use the JDBC get/setBlob object types (ie. Oracle). I worked around
  pushing BLOB data from Oracle into a PG bytea easily enough, but
pushing PG bytea data into an Oracle BLOB becomes a much slower
multi-step process. Is there a release target for moving the get/setBlob
functionality over to bytea? Perhaps triggered using the same compatible
flag (although it may be a little late for that now that 7.2 is out)?

Cheers,

Marc

Barry Lind wrote:

> Marc,
>
> The "authoritative statement on what the postition on BLOBs in 7.2" is
> in the documentation for 7.2.  The section on storing binary data with
> jdbc explains what 7.1 did, what 7.2 does and how to revert to the 7.1
> behavior for backward compatibility.  It also explains why you might
> want to use bytea vs. LargeObjects to store binary data and gives
> examples on how to do both.
>
> --Barry
>
>
> Marc Lavergne wrote:
>
>> Right ... there's a few "bugs" unconfirmed but it is in my eyes in
>> org.postgresql.jdbc2.PreparedStatement and ResultSet. Look in the
>> source
>> (postgresql-7.2/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet and
>> you'll see that the code was branched right around line 330.
>> Unfortunately, that breaks support for lo types in 7.2. Not I'm not
>> sure WHAT the official position is on this. However, there are four
>> things you can do: use the 7.1.3 JDBC driver, custom fix the 7.2
>> driver (ie. remove the if statement to revert to 7.1.3 behavior),
>> submit a diff patch, file a bug and hope somebody fixes it. Obviously,
>> the first and second options are the easiest. The third and fourth
>> would require some kind of authoritative statement on what the
>> position on BLOBs in 7.2 is!
>>
>> Hope this helps and good luck!
>>
>> Marc
>>
>> Benoit Menendez wrote:
>>
>>> I use the latest stable 7.2.2 under cygwin
>>>
>>>     Benoit
>>>
>>> ----- Original Message -----
>>> From: "Marc Lavergne" <mlavergne-pub@richlava.com>
>>> To: "Benoit Menendez" <benoitm@pacbell.net>
>>> Cc: <pgsql-jdbc@postgresql.org>
>>> Sent: Tuesday, February 26, 2002 11:13 AM
>>> Subject: Re: [JDBC] Blob support...
>>>
>>>
>>>
>>>> Which release of PostgreSQL? There were numerous changes in BLOB
>>>> support
>>>> from 7.0 to 7.1 to 7.2, your answer depends on the target release of
>>>> PostgreSQL.
>>>>
>>>> Benoit Menendez wrote:
>>>>
>>>>
>>>>> I use both ODBC and JDBC to access the same database (from two
>>>>> different
>>>>> applications) and I need blob support...
>>>>>
>>>>>
>>>>>
>>>>> I have created and used the "lo" type which is handled correctly by
>>>>> the
>>>>> ODBC driver...
>>>>>
>>>>>
>>>>>
>>>>> But JDBC does not like the "lo" type for blobs...
>>>>>
>>>>>
>>>>>
>>>>> If I do a getBytes on an "lo" I only get 4 (+1) bytes...
>>>>>
>>>>>
>>>>>
>>>>> I tried "bytea" but ODBC does not work properly with this type...
>>>>>
>>>>>
>>>>>
>>>>> Is the "lo" type supported in a recent patch?
>>>>>
>>>>>
>>>>>
>>>>> Thanks,
>>>>>
>>>>>
>>>>>
>>>>>    Benoit
>>>>>
>>>>>
>>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: Have you searched our list archives?
>>>
>>> http://archives.postgresql.org
>>>
>>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>

Benoit,

What version are you using?  I would strongly recommend using the 7.2
jdbc driver (it works fine against a 7.1 database).  Also the 7.2 jdbc
chapter of the documentation has examples on how to use both
LargeObjects and bytea datatypes via jdbc.

thanks,
--Barry


Benoit Menendez wrote:
> I use both ODBC and JDBC to access the same database (from two different
> applications) and I need blob support...
>
>
>
> I have created and used the "lo" type which is handled correctly by the
> ODBC driver...
>
>
>
> But JDBC does not like the "lo" type for blobs...
>
>
>
> If I do a getBytes on an "lo" I only get 4 (+1) bytes...
>
>
>
> I tried "bytea" but ODBC does not work properly with this type...
>
>
>
> Is the "lo" type supported in a recent patch?
>
>
>
> Thanks,
>
>
>
>     Benoit
>

Marc,

The "authoritative statement on what the postition on BLOBs in 7.2" is
in the documentation for 7.2.  The section on storing binary data with
jdbc explains what 7.1 did, what 7.2 does and how to revert to the 7.1
behavior for backward compatibility.  It also explains why you might
want to use bytea vs. LargeObjects to store binary data and gives
examples on how to do both.

--Barry


Marc Lavergne wrote:
> Right ... there's a few "bugs" unconfirmed but it is in my eyes in
> org.postgresql.jdbc2.PreparedStatement and ResultSet. Look in the source
> (postgresql-7.2/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet and
> you'll see that the code was branched right around line 330.
> Unfortunately, that breaks support for lo types in 7.2. Not I'm not sure
> WHAT the official position is on this. However, there are four things
> you can do: use the 7.1.3 JDBC driver, custom fix the 7.2 driver (ie.
> remove the if statement to revert to 7.1.3 behavior), submit a diff
> patch, file a bug and hope somebody fixes it. Obviously, the first and
> second options are the easiest. The third and fourth would require some
> kind of authoritative statement on what the position on BLOBs in 7.2 is!
>
> Hope this helps and good luck!
>
> Marc
>
> Benoit Menendez wrote:
>
>> I use the latest stable 7.2.2 under cygwin
>>
>>     Benoit
>>
>> ----- Original Message -----
>> From: "Marc Lavergne" <mlavergne-pub@richlava.com>
>> To: "Benoit Menendez" <benoitm@pacbell.net>
>> Cc: <pgsql-jdbc@postgresql.org>
>> Sent: Tuesday, February 26, 2002 11:13 AM
>> Subject: Re: [JDBC] Blob support...
>>
>>
>>
>>> Which release of PostgreSQL? There were numerous changes in BLOB support
>>> from 7.0 to 7.1 to 7.2, your answer depends on the target release of
>>> PostgreSQL.
>>>
>>> Benoit Menendez wrote:
>>>
>>>
>>>> I use both ODBC and JDBC to access the same database (from two
>>>> different
>>>> applications) and I need blob support...
>>>>
>>>>
>>>>
>>>> I have created and used the "lo" type which is handled correctly by the
>>>> ODBC driver...
>>>>
>>>>
>>>>
>>>> But JDBC does not like the "lo" type for blobs...
>>>>
>>>>
>>>>
>>>> If I do a getBytes on an "lo" I only get 4 (+1) bytes...
>>>>
>>>>
>>>>
>>>> I tried "bytea" but ODBC does not work properly with this type...
>>>>
>>>>
>>>>
>>>> Is the "lo" type supported in a recent patch?
>>>>
>>>>
>>>>
>>>> Thanks,
>>>>
>>>>
>>>>
>>>>    Benoit
>>>>
>>>>
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Benoit,

I don't know what you mean by 'lo' is the standard type for
LargeObjects.  AFAIK 'oid' is the standard type for LargeObjects.  Given
the current limitations of LargeObjects I feel bytea is a better option
in most (but certainly not all) circumstances.  I certainly agree that a
better blob type is necessary, but we have to work with what we have
today.  You mention that the ODBC driver has problems with the bytea
datatype.  What are those problems and have you reported them to the
ODBC folks?

You mention that you are trying to write generic JDBC code here.  In my
experiece this will be difficult to do.  The way blobs are created and
handled by different databases (Oracle, MSSQL and postgres are the ones
I am most familiar with) is very different.

thanks,
--Barry


Benoit Menendez wrote:
> I am using 7.2 and I need to write generic JDBC code, so I cannot use the
> postgreSQL specific functions. I could use the "bytea" type, but it has
> limitations and the ODBC driver does not handle it correctly.
>
> The "standard" type for large objects is the "lo" type which is not
> supported (bug?) by the latest stable 7.2 JDBC driver.
>
> If declared, the "lo" type is reported as type 1111 (OTHER) by getTypeInfo
> and getBytes() on it will return the 4 bytes (+1 for some reason) object id.
>
> I wish there was a real standard for binary large objects with:
>
>     - standardization of the blob type name ("lo" is fine, but "blob" would
> be better)
>     - type already defined in the database at creation (no need to create
> the type manually)
>     - support in ODBC and JDBC of this type
>     - support for the JDBC getBlob, getBytes and getBinaryStream functions
> (and corresponding setters)
>     - built-in vacuuming of blobs as part of the standard vacuuming
>
> This would make it a lot easier... Maybe 7.3?
>
>     Benoit
>
> ----- Original Message -----
> From: "Barry Lind" <barry@xythos.com>
> To: "Benoit Menendez" <benoitm@pacbell.net>
> Cc: <pgsql-jdbc@postgresql.org>
> Sent: Tuesday, February 05, 2002 3:02 AM
> Subject: Re: [JDBC] Blob support...
>
>
>
>>Benoit,
>>
>>What version are you using?  I would strongly recommend using the 7.2
>>jdbc driver (it works fine against a 7.1 database).  Also the 7.2 jdbc
>>chapter of the documentation has examples on how to use both
>>LargeObjects and bytea datatypes via jdbc.
>>
>>thanks,
>>--Barry
>>
>>
>>Benoit Menendez wrote:
>>
>>>I use both ODBC and JDBC to access the same database (from two different
>>>applications) and I need blob support...
>>>
>>>
>>>
>>>I have created and used the "lo" type which is handled correctly by the
>>>ODBC driver...
>>>
>>>
>>>
>>>But JDBC does not like the "lo" type for blobs...
>>>
>>>
>>>
>>>If I do a getBytes on an "lo" I only get 4 (+1) bytes...
>>>
>>>
>>>
>>>I tried "bytea" but ODBC does not work properly with this type...
>>>
>>>
>>>
>>>Is the "lo" type supported in a recent patch?
>>>
>>>
>>>
>>>Thanks,
>>>
>>>
>>>
>>>    Benoit
>>>
>>>
>>
>
>