Thread: can't write a BLOB, 7.1.2

Greetings,

I'm trying to create a BLOB with the 7.1.2 JDBC driver and both
setBytes() and setBinaryStream() fail with this message:

  FastPath call returned ERROR:  lo_write: invalid large obj descriptor (0)

Any ideas?

Thanks,

--dv
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

"Dejan Vucinic" <hotdejan@hotmail.com> writes:
> I'm trying to create a BLOB with the 7.1.2 JDBC driver and both
> setBytes() and setBinaryStream() fail with this message:
>   FastPath call returned ERROR:  lo_write: invalid large obj descriptor (0)

Probably you are not wrapping the operation into a transaction
(BEGIN/COMMIT).  Large object descriptors are only good to the end of
the current transaction.

            regards, tom lane

>"Dejan Vucinic" <hotdejan@hotmail.com> writes:
> > I'm trying to create a BLOB with the 7.1.2 JDBC driver and both
> > setBytes() and setBinaryStream() fail with this message:
> >   FastPath call returned ERROR:  lo_write: invalid large obj descriptor
>(0)

>From: Tom Lane <tgl@sss.pgh.pa.us>
>Probably you are not wrapping the operation into a transaction
>(BEGIN/COMMIT).  Large object descriptors are only good to the end of
>the current transaction.

I believe I am, the code looks like this:

  connection.setAutoCommit(false);
  insert = connection.prepareStatement(
             "INSERT INTO FOO (A, B) VALUES (?, ?)");
  insert.setString(1, somestring);
  insert.setBytes(2, byte[] xx);
  insert.executeUpdate();
  connection.commit();
  connection.setAutoCommit(true);

The exception gets thrown out of setBytes, at the point where the
driver tries to close the stream that copied the bytes into the LOB,
and long before the SQL statement actually gets executed.

[Note that this is neither the first nor the last statement on that
connection, since connections get pooled by the app server; could
this be causing the problem?]

Thanks,

--dv

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

"Dejan Vucinic" <hotdejan@hotmail.com> writes:
>> From: Tom Lane <tgl@sss.pgh.pa.us>
>> Probably you are not wrapping the operation into a transaction
>> (BEGIN/COMMIT).  Large object descriptors are only good to the end of
>> the current transaction.

> I believe I am, the code looks like this:

>   connection.setAutoCommit(false);
>   insert = connection.prepareStatement(
>              "INSERT INTO FOO (A, B) VALUES (?, ?)");
>   insert.setString(1, somestring);
>   insert.setBytes(2, byte[] xx);
>   insert.executeUpdate();
>   connection.commit();
>   connection.setAutoCommit(true);

I have no idea what that actually does at the database level.
In particular, does the setAutoCommit(false) routine send a
BEGIN, or does it only set some state that affects later operations?
If there's not a BEGIN sent before the LO operations, they'll fail.

You might try turning on query logging at the postmaster to see
what requests are really being transmitted.

            regards, tom lane

This normally should work (calling con.setAutoCommit(false);) for large
objects. Are you using the latest JDBC driver or the one that came with
7.1.2?

Here's a standard kind of method I use to store a BLOB, this is against 7.1
RC1 with the JDBC source that came with it:

---
    try {
        con.setAutoCommit(false);
        String sql = "UPDATE formfields SET mimetype = ?, fieldblob = ? " +
              "WHERE fieldid = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        int paramindex = 1;
        ps.setString(paramindex++, image.getContentType());
        ps.setBytes(paramindex++, image.getBytes());
        ps.setInt(paramindex++, fieldid);
        ps.executeUpdate();
        ps.close();
        con.commit();
    } catch ( SQLException se ) {
        log("Couldn't update field image data: "+se);
    } finally {
        try { con.setAutoCommit(true); } catch ( Exception ignored ) {}
        ...
    }
---

Regards,
Joe

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, 12 July 2001 5:39 AM
> To: Dejan Vucinic
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] can't write a BLOB, 7.1.2
>
>
> "Dejan Vucinic" <hotdejan@hotmail.com> writes:
> >> From: Tom Lane <tgl@sss.pgh.pa.us>
> >> Probably you are not wrapping the operation into a transaction
> >> (BEGIN/COMMIT).  Large object descriptors are only good to
> the end of
> >> the current transaction.
>
> > I believe I am, the code looks like this:
>
> >   connection.setAutoCommit(false);
> >   insert = connection.prepareStatement(
> >              "INSERT INTO FOO (A, B) VALUES (?, ?)");
> >   insert.setString(1, somestring);
> >   insert.setBytes(2, byte[] xx);
> >   insert.executeUpdate();
> >   connection.commit();
> >   connection.setAutoCommit(true);
>
> I have no idea what that actually does at the database level.
> In particular, does the setAutoCommit(false) routine send a
> BEGIN, or does it only set some state that affects later operations?
> If there's not a BEGIN sent before the LO operations, they'll fail.
>
> You might try turning on query logging at the postmaster to see
> what requests are really being transmitted.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>