Thread: [Re: JDBC compliance]

Abel,

The problem is that Postgres doesn't have a datatype that supports BLOB
and CLOB very well.  There are two different datatypes that come close
(bytea and LargeObjects/oid).  The pros and cons of each type can be
found in the jdbc chapter of the postgres documentation in the section
on Storing Binary Data.

In 7.1 and earlier only the LargeObject way of doing things was
supported.  In 7.2 the bytea way was added.  But unfortunately this has
left the driver in a bit of a confused state as you point out.

I am not sure this will change in the near future until the server has a
   datatype the better matches the ansi BLOB/CLOB types.

If you haven't done so, I would strongly suggest reading the above
mentioned documentation.

The problem with setBinaryStream() is that the driver doesn't know the
underlying datatype in the database.  So it has to infer from the set
method what the datatype is.  For things like setString, setTimestamp
that is easy, however for some (setBinaryStream in this case) it isn't
as straight forward.

thanks,
--Barry



Abel Muiño wrote:
> Hello.
>
>     I'm evolving an application where BLOBs and CLOBs where being used over
> Oracle. Now I'm extending this to use other databases, but I've found
> that PostgreSQL support for them is "broken".
>
>     According to the JDBC standard (downloadable from
> http://java.sun.com/products/jdbc/download.html#corespec30), section
> 16.3.2: "The setBinaryStream and setObject methods may also be used to
> set a Blob object as a parameter in a PreparedStatement object.".
>
>     But postgres' JDBC driver uses "bytea" database type for
> setBinaryStream and "oid" (large object interface) when using getBlob.
>
>     I have worked around it by falling back to the 7.1 compatible mode,
> where Large Objects were used for both, but I would like to know which
> is the current state of this issue.
>
>     Thanks a lot.
>
> --
>     Abel.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

El mié, 08-01-2003 a las 17:08, Barry Lind escribió:

> In 7.1 and earlier only the LargeObject way of doing things was
> supported.  In 7.2 the bytea way was added.  But unfortunately this has
> left the driver in a bit of a confused state as you point out.
    In my opinion, setBinaryStream should work on the same data type as
get/setBlob (even when the database doesn't properly support those),
because it is the only standard (i.e. without database specific code)
way of creating BLOBs. As far as I know, bytea columns can be read and
set with get/setBytes.

> If you haven't done so, I would strongly suggest reading the above
> mentioned documentation.
    I've read that, but unfortunately, I need to work with old code (i.e.
use BLOBs/CLOBs) so I can't switch to setBytes/getBytes and I need to
create BLOBs/CLOBs in a standard-JDBC way (so can't use largeobjects
API).

    By now, I'll stick to the 7.1 compatibility mode with database rules
for deleting the large objects when deleting the row.

    Thanks for your help
--
Abel Muiño Vizcaino

El mié, 08-01-2003 a las 17:31, Abel Muiño escribió:
>     By now, I'll stick to the 7.1 compatibility mode with database rules
> for deleting the large objects when deleting the row.

        Hum... but it seems that I can't delete rows from pg_largeobject
with a
user different from "postgres", even when I "GRANT DELETE ON
pg_catalog.pg_largeobject TO myuser".

        What am I missing?

(Sorry for the off-topic and replying to myself)
--
Abel Muiño Vizcaino