RE: can't write a BLOB, 7.1.2

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
>