Blobs and "No results were returned by the query." - Mailing list pgsql-jdbc

From Michael Andreasen
Subject Blobs and "No results were returned by the query."
Date
Msg-id 3D7DD6CA.3000808@dunlops.com
Whole thread Raw
List pgsql-jdbc
I have upgraded to 7.2.1, and I am trying to write a little fixit to
extract JPG's stored in OID's and restore them back into BYTEA's.

Howerver, I am getting a "No results were returned by the query."
exception where trying to read back my Blobs. I have seen this message a
few times before in my apps but have never been able to reproduce it on
demand. My understanding is that this error occurs when one tries to
execute a second query where still in a transaction.

I don't see any reason why I should get it in this case!

Application output ....

FastPath call returned ERROR:  inv_open: large object 0 not found
ID = 0, no blob
ID = 1, Blob size = 34858
ID = 2, Blob size = 8447
ID = 6, Blob size = 20756
ID = 7, Blob size = 17403
ID = 8, Blob size = 18353
ID = 9, Blob size = 19935
No results were returned by the query.
        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
        at
org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java:99)
        at misc.BlobFix.changeData(BlobFix.java:49)
        at misc.BlobFix.main(BlobFix.java:27)
Exception in thread "main"


Test case.....

package misc;

import to.systems.base.DatabaseConnection;
import java.sql.*;
import java.util.Vector;

public class BlobFix
{
    public static PreparedStatement readBlobStmt, readStmt, beginStmt;
    public static Connection conn;

    public static void main(String[] args) throws Exception
    {
        conn = // whatever 7.2.1 database
        changeData();
    }

    public static void changeData() throws Exception
    {
        readBlobStmt = conn.prepareStatement(
            "select picture from employee where id=?"); // picture is a
OID with a JPG image
        readStmt = conn.prepareStatement(
            "select id from employee order by 1");
        beginStmt = conn.prepareStatement(
            "begin");
        Vector ids = new Vector();
        ResultSet rs = readStmt.executeQuery();
        while (rs.next()) {
            ids.addElement(rs.getObject(1));
        }
        rs.close();
        conn.setAutoCommit(false);
        for (int i=0; i<ids.size(); i++) {
            beginStmt.execute();
            readBlobStmt.setObject(1,ids.elementAt(i));
            rs = readBlobStmt.executeQuery(); // this is the line that fail!
            rs.next();
            int id = ((Integer)ids.elementAt(i)).intValue();
            Blob blob;
            try {
                blob = rs.getBlob(1);
                long length = blob.length();
                byte[] bytes = blob.getBytes(0,(int)length);
                System.out.println("ID = "+id+", Blob size =
"+length);
                rs.close();
                conn.commit();
            }
            catch (Exception ex) {
                System.out.println(ex.toString());
                blob = null;
                rs.close();
                conn.rollback();
            }
            if (blob==null) {
                System.out.println("ID = "+id+", no blob");
            }
            Thread.sleep(250);
        }
    }


}





pgsql-jdbc by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] problem with new autocommit config parameter and jdbc
Next
From: Daryl Beattie
Date:
Subject: Re: [GENERAL] Selecting Varchar range (through JDBC).