Thread: BUG #1780: JDBC driver "setNull" throws for BLOB and CLOB columns

The following bug has been logged online:

Bug reference:      1780
Logged by:          Andrus Adamchik
Email address:      andrus@objectstyle.org
PostgreSQL version: 8.0.1
Operating system:   Mac OS X 10.4
Description:        JDBC driver "setNull" throws for BLOB and CLOB columns
Details:

Whenver I call PreparedStatement.setNull(int, int) on BLOB or CLOB columns,
an exception below occurs. Driver version: postgresql-8.0-310.jdbc3.jar. But
looks like latest CVS version has the same problem.

Test case:

            Driver d = new org.postgresql.Driver();

            Properties p = new Properties();
            p.put("user", "andrus");
            p.put("password", "andrus");

            Connection c =
d.connect("jdbc:postgresql://localhost:5432/cayenne", p);
            PreparedStatement st = c
                    .prepareStatement("insert into BLOB_TEST (BLOB_TEST_ID,
BLOB_COL) values (?, ?)");

            st.setInt(1, 1);

            // this is the line where it blows:
            st.setNull(2, Types.BLOB);


Stack trace:

org.postgresql.util.PSQLException: Unknown Types value.
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.setNull(AbstractJdbc2Statement.j
ava:1003)
    at
org.postgresql.jdbc3.AbstractJdbc3Statement.setNull(AbstractJdbc3Statement.j
ava:1445)
    at NullBlob.main(NullBlob.java:24)

On Fri, 22 Jul 2005, Andrus Adamchik wrote:

> Bug reference:      1780
> PostgreSQL version: 8.0.1
> Description:        JDBC driver "setNull" throws for BLOB and CLOB columns
> Details:
>
> Whenver I call PreparedStatement.setNull(int, int) on BLOB or CLOB columns,
> an exception below occurs. Driver version: postgresql-8.0-310.jdbc3.jar. But
> looks like latest CVS version has the same problem.
>
>

This has been fixed in CVS for the 8.0 and development drivers, we just
haven't done a release in nearly three months.  I'll put out a release
this weekend unless there are any other outstanding fixes I'm unaware of.

Kris Jurka

On Fri, 22 Jul 2005, Kris Jurka wrote:

> On Fri, 22 Jul 2005, Andrus Adamchik wrote:
>
> > Whenver I call PreparedStatement.setNull(int, int) on BLOB or CLOB columns,
> > an exception below occurs. Driver version: postgresql-8.0-310.jdbc3.jar. But
> > looks like latest CVS version has the same problem.
> >
>
> This has been fixed in CVS for the 8.0 and development drivers, we just
> haven't done a release in nearly three months.  I'll put out a release
> this weekend unless there are any other outstanding fixes I'm unaware of.
>

Actually looking more closely, this was fixed in build 311 which is
available, but we should still put out a new release for other fixes.

Kris Jurka

On Mon, 1 Aug 2005, Andrus Adamchik wrote:

> Just tested it with build 312 and the original problem went away. Now
> I am seeing a new problem with null BLOBs only:
>
> java.sql.SQLException: ERROR: column "blob_col" is of type bytea but
> expression is of type oid

bytea is not the correct type to use when storing Blobs.  You need to use
oid as the underlying type, or if you want to use bytea you need to use
setBytes or setNull(x, Types.BINARY).

Kris Jurka

Just tested it with build 312 and the original problem went away. Now
I am seeing a new problem with null BLOBs only:

java.sql.SQLException: ERROR: column "blob_col" is of type bytea but
expression is of type oid
     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse
(QueryExecutorImpl.java:1471)
     at org.postgresql.core.v3.QueryExecutorImpl.processResults
(QueryExecutorImpl.java:1256)
     at org.postgresql.core.v3.QueryExecutorImpl.execute
(QueryExecutorImpl.java:175)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
(AbstractJdbc2Statement.java:389)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags
(AbstractJdbc2Statement.java:330)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
(AbstractJdbc2Statement.java:321)
     at NullBlob.main(NullBlob.java:26)

In other words "bytea" columns allow inserting non-null blobs, but
null generates the exception above. Here is sample code:

  PreparedStatement st = c
     .prepareStatement("insert into BLOB_TEST (BLOB_TEST_ID,
BLOB_COL) values (?, ?)");
  st.setInt(1, 1);
  st.setNull(2, Types.BLOB);
  st.execute(); // this line throws the exception above

Andrus



On Jul 22, 2005, at 11:40 PM, Kris Jurka wrote:

>
>
> On Fri, 22 Jul 2005, Kris Jurka wrote:
>
>
>> On Fri, 22 Jul 2005, Andrus Adamchik wrote:
>>
>>
>>> Whenver I call PreparedStatement.setNull(int, int) on BLOB or
>>> CLOB columns,
>>> an exception below occurs. Driver version:
>>> postgresql-8.0-310.jdbc3.jar. But
>>> looks like latest CVS version has the same problem.
>>>
>>>
>>
>> This has been fixed in CVS for the 8.0 and development drivers, we
>> just
>> haven't done a release in nearly three months.  I'll put out a
>> release
>> this weekend unless there are any other outstanding fixes I'm
>> unaware of.
>>
>>
>
> Actually looking more closely, this was fixed in build 311 which is
> available, but we should still put out a new release for other fixes.
>
> Kris Jurka