Thread: 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@iplocks.co.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