Обсуждение: Blobs and "No results were returned by the query."

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);
        }
    }


}

It's unfortunate, but the new blob code is pretty buggy.  I think the dev
builds are better, but I can't say for sure since they are not exactly
current (so you'd have to extract from CVS and do your own builds).

I'm not sure, but is it possible your Blob was NULL in that case?  I got a
similar fastpath error when doing a rs.getBlob() and the OID was 0 (NULL),
but while I'm comfortable looking at the JDBC part of the code, I'm unable
to easily tweak the fastpath parts to properly detect NULL.  Of course, in
your sample code, you're not doing an rs.wasNull() to check that you
actually got a blob.

David

Michael,

Please try either the 7.3beta1 version or the latest development version
from jdbc.postgresql.org.  There was a bug fixed recently that I think
may be the same issue you are running into.  It had to deal with garbage
collection.  Everything would work fine until the garbage collector ran
and then you would start seeing errors like the one you are reporting.
And since garbage collection runs at different times, it seemed that
this error was 'random'.  This could be a very different issue, but lets
first eliminate this already fixed bug.

thanks,
--Barry

Michael Andreasen wrote:
> 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);
>        }
>    }
>     }
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>