Re: standard LOB support

Hi,

I'm developing a software that supports several RDBMSs including PostgreSQL.

The software needs an ability to handle large objects and now it uses 'bytea' datatype for binary
data and 'text' for text data.
But for portability, I'd rather use BLOB and CLOB defined by the SQL standards indeed.

Is there any plan to support BLOB and CLOB in future releases?

Thanks in advance,

ebi

EBIHARA, Yuichiro wrote:
> Hi,
>
> I'm developing a software that supports several RDBMSs including PostgreSQL.
>
> The software needs an ability to handle large objects and now it uses 'bytea' datatype for binary
> data and 'text' for text data.
> But for portability, I'd rather use BLOB and CLOB defined by the SQL standards indeed.
>
> Is there any plan to support BLOB and CLOB in future releases?
>
Looking at the spec, and postgresql's implementation, I can't see much
reason you couldn't just use a domain to declare that a bytea is a blob
and varchar is a clob.

Unless there's some spefici thing you need I'm not seeing.

Hi Scott,

Thank you for your comment.

> > Is there any plan to support BLOB and CLOB in future releases?
> >
> Looking at the spec, and postgresql's implementation, I can't
> see much reason you couldn't just use a domain to declare that
> a bytea is a blob and varchar is a clob.

That sounds a good idea!
My application accesses databases via JDBC and PostgreSQL JDBC driver can handle 'bytea' as BLOB.
I'm not sure if 'text' is compatible with CLOB, but I guess it'll also work well.

Thanks again!

ebi

Hi,

I found my understanding was incorrect.

> > > Is there any plan to support BLOB and CLOB in future releases?
> > >
> > Looking at the spec, and postgresql's implementation, I can't
> > see much reason you couldn't just use a domain to declare that
> > a bytea is a blob and varchar is a clob.
>
> That sounds a good idea!
> My application accesses databases via JDBC and PostgreSQL
> JDBC driver can handle 'bytea' as BLOB. I'm not sure if
> 'text' is compatible with CLOB, but I guess it'll also work well.

It seems like PG JDBC driver CANNOT handle 'bytea' as BLOB nor 'text' as CLOB.
getBlob()/setBlob()/getClob()/setClob() can work with only Large Objects (at least with
postgresql-8.1-405.jdbc3.jar).

org.postgresql.util.PSQLException: Bad Integer Z\273\330x\336\335\226\243
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.toInt(AbstractJdbc1ResultSet.java:862)
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getInt(AbstractJdbc1ResultSet.java:287)
at org.postgresql.jdbc3.Jdbc3ResultSet.getBlob(Jdbc3ResultSet.java:42)
at PgTest.main(PgTest.java:33)

The same exception occurs when using getClob() against a text column.

Using Large Objects may solve my issue but I have to note that a large object is not automatically
deleted when the record referring to it is deleted.

Thanks,

ebi

EBIHARA, Yuichiro wrote on 22.06.2007 06:09:
> It seems like PG JDBC driver CANNOT handle 'bytea' as BLOB nor 'text' as CLOB.
> getBlob()/setBlob()/getClob()/setClob() can work with only Large Objects (at least with
> postgresql-8.1-405.jdbc3.jar).
>
> org.postgresql.util.PSQLException: Bad Integer Z\273\330x\336\335\226\243
> at org.postgresql.jdbc1.AbstractJdbc1ResultSet.toInt(AbstractJdbc1ResultSet.java:862)
> at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getInt(AbstractJdbc1ResultSet.java:287)
> at org.postgresql.jdbc3.Jdbc3ResultSet.getBlob(Jdbc3ResultSet.java:42)
> at PgTest.main(PgTest.java:33)
>
> The same exception occurs when using getClob() against a text column.
>
> Using Large Objects may solve my issue but I have to note that a large object is not automatically
> deleted when the record referring to it is deleted.
>
I found that using getBinaryStream(), setBinaryStream(), getCharacterStream()
and setCharacterStream() to handle LOBs across different DBMS is much more
portable (and reliably) than using the Clob()/Blob() methods.

The Postgres JDBC driver handles the stream/writer methods just fine to read and
write text and bytea columns.

Thomas

"EBIHARA, Yuichiro" <ebihara(at)iplocks(dot)co(dot)jp> writes:
> Using Large Objects may solve my issue but I have to note that a large
> object is not automatically deleted when the record referring to it is
> deleted.

The contrib/lo module can help with this.

regards, tom lane

Thomas,

Thank you for your comment.

> I found that using getBinaryStream(), setBinaryStream(),
> getCharacterStream()
> and setCharacterStream() to handle LOBs across different DBMS
> is much more
> portable (and reliably) than using the Clob()/Blob() methods.

According to JDBC 3.0 specifiction, those 4 methods may not be compatible to BLOB/CLOB.
Some databases may support them to access LOB data but not all databases.

But my target databases are, actually, only PostgreSQL, Oracle and DB2 and there is no problem with
PostgreSQL.
Also, according to the Oracle JDBC driver manual, Oracle supports stream access to LOB through the 4
methods.

I'll also try DB2 soon.

Thanks,

ebi

Hi,

> > I found that using getBinaryStream(), setBinaryStream(),
> > getCharacterStream()
> > and setCharacterStream() to handle LOBs across different DBMS
> > is much more
> > portable (and reliably) than using the Clob()/Blob() methods.
>
> According to JDBC 3.0 specifiction, those 4 methods may not
> be compatible to BLOB/CLOB. Some databases may support them
> to access LOB data but not all databases.
>
> But my target databases are, actually, only PostgreSQL,
> Oracle and DB2 and there is no problem with PostgreSQL. Also,
> according to the Oracle JDBC driver manual, Oracle supports
> stream access to LOB through the 4 methods.
>
> I'll also try DB2 soon.

DB2 is ok too!

According to manuals, both of Oracle and DB2 support
getBytes()/setBytes()/getBinaryStream()/setBinaryStream() for BLOB and
getString()/setString()/getCharacterStream()/setCharacterStream() for CLOB.

Therefore, I can develop portable JDBC applications with LOB by using those methods and bytea/text
data types on PostgreSQL.
In addition, I can use DDL scripts including BLOB/CLOB with PostgreSQL too if I define domains as
follows.

CREATE DOMAIN BLOB AS BYTEA;
CREATE DOMAIN CLOB AS TEXT;

Tom,
I also say thank you to you. But contrib/lo looks a little too much to me for this time.

Thanks,

ebi

EBIHARA, Yuichiro wrote on 22.06.2007 08:34:
> Thomas,
>
> Thank you for your comment.
>
>> I found that using getBinaryStream(), setBinaryStream(),
>> getCharacterStream()
>> and setCharacterStream() to handle LOBs across different DBMS
>> is much more
>> portable (and reliably) than using the Clob()/Blob() methods.
>
> According to JDBC 3.0 specifiction, those 4 methods may not be compatible to BLOB/CLOB.
> Some databases may support them to access LOB data but not all databases.
>
Hmm. At least for updating LOBs, "my method" should be "legal".
This is a quote from jdbc-3_0-fr-spec.pdf

"The setBinaryStream and setObject methods may also be used to set a Blob
object as a parameter in a PreparedStatement object. The setAsciiStream,
setCharacterStream, and setObject methods are alternate means of setting a
Clob object as a parameter."

But I have to admit that I never read the specs in detail until now. Those
methods were simply working fine (and were the only reliable way to handle LOBs
with the Oracle drivers).
Btw: these methods are working (for me) with Oracle, SQL Server (jTDS and MS
Driver), DB2 (8.x), Firebird, Derby, MySQL, HSQL, H2, Informix and Sybase
Adaptive Server Anywhere.

But I do think that the exception thrown when using getClob() or getBlob() is an
error in the JDBC driver. Maybe we should file an issue for this.

Regards
Thomas

Thomas,

> > According to JDBC 3.0 specifiction, those 4 methods may not be
> > compatible to BLOB/CLOB. Some databases may support them to
> access LOB
> > data but not all databases.
> >
> Hmm. At least for updating LOBs, "my method" should be
> "legal". This is a quote from jdbc-3_0-fr-spec.pdf
>
> "The setBinaryStream and setObject methods may also be used
> to set a Blob object as a parameter in a PreparedStatement
> object. The setAsciiStream, setCharacterStream, and setObject
> methods are alternate means of setting a Clob object as a parameter."

Sorry, my comment was partially incorrect.
See B-182(TABLE B-6) of the spec.
getBinaryStream()/getCharacterStream are not compatible to LOB.

Also, there were some limitations with Oracle JDBC "Thin" driver 9.2 and those methods didn't work
with LOB.

Thanks,

ebi

EBIHARA, Yuichiro wrote on 22.06.2007 09:28:
>> Hmm. At least for updating LOBs, "my method" should be
>> "legal". This is a quote from jdbc-3_0-fr-spec.pdf
>>
>> "The setBinaryStream and setObject methods may also be used
>> to set a Blob object as a parameter in a PreparedStatement
>> object. The setAsciiStream, setCharacterStream, and setObject
>> methods are alternate means of setting a Clob object as a parameter."
>
> Sorry, my comment was partially incorrect.
> See B-182(TABLE B-6) of the spec.
> getBinaryStream()/getCharacterStream are not compatible to LOB.
Thanks for the pointer ;)

According to that table, the PG driver is actually correct, as bytea is reported
as Types.BINARY not Types.BLOB

> Also, there were some limitations with Oracle JDBC "Thin" driver 9.2 and those methods didn't work
> with LOB.
Yes I found that as well. Only the 10.x driver work correctly

Regards
Thomas

Finally, I'd like to bring up my first question again.

Is there any plan to support BLOB and CLOB in future releases?
Don't you guys need a standard LOB feature? I no longer need it, though ;-p

With the current large objects feature, I don't think it's not difficult to support it...

Thanks,

ebi

Thomas Kellerer wrote:
> Hmm. At least for updating LOBs, "my method" should be "legal".
> This is a quote from jdbc-3_0-fr-spec.pdf
>
> "The setBinaryStream and setObject methods may also be used to set a
Blob
> object as a parameter in a PreparedStatement object. The
setAsciiStream,
> setCharacterStream, and setObject methods are alternate means of
setting a
> Clob object as a parameter."
>
> But I have to admit that I never read the specs in detail until now.
Those
> methods were simply working fine (and were the only reliable way to
handle LOBs
> with the Oracle drivers).

Yuichiro, I'd use these methods for BLOBs if they work on all
the DBMS Thomas mentioned.

> But I do think that the exception thrown when using getClob() or
getBlob() is an
> error in the JDBC driver. Maybe we should file an issue for this.

I'm certainly not a core developer of the JDBC provider, but working
with it and reading the code it seems quite clear to me that the driver
treats PostgreSQL large objects as java.sql.BLOBs and byteas as
java.sql.Types.BINARY, and that this is intentional.

I don't think it would be a simple change to allow byteas to be treated
as BLOBs.

I have CC'ed the JDBC mailing list as I think this should go there
(too).

Yours,
Laurenz Albe