Thread: How to use JDBC to update LargeObject

I am using 8.1 Postgre JDBC. I want to know how can I use JDBC to
update the contents of a LargeObject. For example, say I have created
a LO for a file with 1 Mb of data. Later on, file contents have
changed and are now just 100 kb and I want to update the LO. When I
try to write to a large object using the method largeObject.write(byte
[], off, len), it seems to be updating the first 100 kb data, not
really replacing the existing 100 MB with the new data. pg_largeobject
table still shows the same number of rows. Any idea how can I update
the contents of a large object.

Satish Burnwal (sburnwal) wrote:
> I am using 8.1 Postgre JDBC. I want to know how can I use JDBC to
> update the contents of a LargeObject. For example, say I have created
> a LO for a file with 1 Mb of data. Later on, file contents have
> changed and are now just 100 kb and I want to update the LO. When I
> try to write to a large object using the method largeObject.write(byte
> [], off, len), it seems to be updating the first 100 kb data, not
> really replacing the existing 100 MB with the new data. pg_largeobject
> table still shows the same number of rows. Any idea how can I update
> the contents of a large object.

See manual, http://jdbc.postgresql.org/documentation/84/binary-data.html:

" To use the Large Object functionality you can use either the
LargeObject class provided by the PostgreSQL™ JDBC driver, or by using
the getBLOB() and setBLOB() methods.
Important

You must access Large Objects within an SQL transaction block. You can
start a transaction block by calling setAutoCommit(false). "

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

Even setBlob() method seems to be updating large object only partially. This is my code:

CREATE TABLE image (name TEXT, oid OID);

select * from image;
      name       |  oid
-----------------+-------
bigfile         | 17416
(1 rows)

//update the blob in the database
org.postgresql.jdbc3.Jdbc3Blob blob = new org.postgresql.jdbc3.Jdbc3Blob((org.postgresql.PGConnection)con, 17416);
            InputStream is = new FileInputStream("/root/myfile");
            byte[] bytes = new byte[128];
            int n = 0, pos = 1;
            while ((n = is.read(bytes)) >= 1) {
                blob.setBytes(pos, bytes, 0, n);
                pos = pos + n;
            }
            is.close();
            PreparedStatement sst = con.prepareStatement("update image set oid = ? where name = ?");
            sst.setString(2, "bigfile");
            sst.setBlob(1, blob);
            int count = sst.executeUpdate();

This updates only partially the existing blob. I want the file contents to be changed (from 1 Mb to 100 kb). Tell me if
thereis a way (without changing the OID of the large object).
 

Satish
------------------------

-----Original Message-----
From: Heikki Linnakangas [mailto:heikki.linnakangas@enterprisedb.com] 
Sent: Thursday, January 21, 2010 1:27 PM
To: Satish Burnwal (sburnwal)
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] How to use JDBC to update LargeObject

Satish Burnwal (sburnwal) wrote:
> I am using 8.1 Postgre JDBC. I want to know how can I use JDBC to 
> update the contents of a LargeObject. For example, say I have created 
> a LO for a file with 1 Mb of data. Later on, file contents have 
> changed and are now just 100 kb and I want to update the LO. When I 
> try to write to a large object using the method largeObject.write(byte 
> [], off, len), it seems to be updating the first 100 kb data, not 
> really replacing the existing 100 MB with the new data. pg_largeobject 
> table still shows the same number of rows. Any idea how can I update 
> the contents of a large object. 

See manual, http://jdbc.postgresql.org/documentation/84/binary-data.html:

" To use the Large Object functionality you can use either the
LargeObject class provided by the PostgreSQL™ JDBC driver, or by using
the getBLOB() and setBLOB() methods.
Important

You must access Large Objects within an SQL transaction block. You can
start a transaction block by calling setAutoCommit(false). "

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

On Thu, 21 Jan 2010, Satish Burnwal (sburnwal) wrote:

> Even setBlob() method seems to be updating large object only partially.

If you want to reuse an existing blob you need to truncate it first:

http://java.sun.com/javase/6/docs/api/java/sql/Blob.html#truncate(long)

Note that there is a bug with truncate in all but the CVS JDBC drivers
that prevents you from truncating a blob to zero length.  Released
drivers can only truncate down to one byte, but that's probably fine for
you at the moment.

Kris Jurka

I am using Postgres 8.1 where truncate feature is not supported. Can you
pls confirm this ? This is src code in 8.3-605 AbstractJdbc2BlobClob
class and I do get the exception:

    public synchronized void truncate(long len) throws SQLException
    {
        checkFreed();
        if (!conn.haveMinimumServerVersion("8.3"))
            throw new PSQLException(GT.tr("Truncation of large objects
is only implemented in 8.3 and later servers."),
PSQLState.NOT_IMPLEMENTED);

        assertPosition(len);
        lo.truncate((int)len);
    }

-Satish

On Fri, 22 Jan 2010, Satish Burnwal (sburnwal) wrote:

> I am using Postgres 8.1 where truncate feature is not supported. Can you
> pls confirm this ? This is src code in 8.3-605 AbstractJdbc2BlobClob
> class and I do get the exception:

OK, then you cannot re-use the existing blob.  You've got to remove the
old one and create a new one.

Kris Jurka