Blobs and "No results were returned by the query."

From: Michael Andreasen <michael(at)dunlops(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Blobs and "No results were returned by the query."
Date: 2002-09-10 11:26:02
Message-ID: 3D7DD6CA.3000808@dunlops.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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);
}
}


}

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Daryl Beattie 2002-09-10 12:27:48 Re: [JDBC] Selecting Varchar range (through JDBC).
Previous Message Tom Lane 2002-09-10 03:27:13 Re: [JDBC] problem with new autocommit config parameter and jdbc