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