Help - ORA-01002 from JDBC but autoCommit is set to false (8.1.7)
Date: Tue, 30 Oct 2001 21:47:53 GMT
Message-ID: <deFD7.525229$8c3.89138043_at_typhoon.tampabay.rr.com>
Hey all,
I'm struggling to figure out why I'm getting this message. I am explicitly setting autoCommit to false. The writes to the table occur in multiple loops.
Here is a code snippet:
... snip ...
private static final String insSql = "INSERT INTO THETABLE(TREEENTRYID, ATTRCATEGORY, ATTRID, ATTRBINARYVALUE) VALUES(?,?,?,empty_blob())";
private static final String updSql = "SELECT ATTRBINARYVALUE FROM THETABLE WHERE TREEENTRYID = ? AND ATTRCATEGORY = ? AND ATTRID = ? FOR UPDATE"; private static final String CNTSQL = "{call GETDOESEXIST(?,?,?,?)}";
/* the GETDOESEXIST simply uses a count(*) from the table, and returns the result into the last parameter */
public int save(Connection con) throws SQLException, RepositoryException {
int rtn = -1;
PreparedStatement stmt;
try {
boolean autoComm = con.getAutoCommit();
int isThere = 0;
con.setAutoCommit(false); // Turn off autoCommit temporarily for this
transaction...
// Is the row already there? If not, insert it, otherwise, skip down to the update
try {
CallableStatement iitSt = con.prepareCall(CNTSQL);
try {
iitSt.setInt(1, (int)getTreeId());
iitSt.setInt(2, (int)getAttrCategory());
iitSt.setInt(3, (int)getAttrId());
iitSt.registerOutParameter(4, java.sql.Types.INTEGER);
iitSt.executeUpdate();
isThere = iitSt.getInt(4);
if ( iitSt.wasNull() ) {
// throw an exception?
isThere = 0;
}
} finally {
iitSt.close();
}
BLOB theBlob = null;
if (isThere == 0) {
// Row isn't there - insert the row...
stmt = con.prepareStatement(insSql);
try {
stmt.setInt(1, (int)getTreeId());
stmt.setInt(2, (int)getAttrCategory());
stmt.setInt(3, (int)getAttrId());
stmt.executeUpdate();
con.commit(); // Commit the insert
} finally {
stmt.close();
}
}
// Update the existing row...
stmt = con.prepareStatement(updSql);
try {
stmt.setInt(1, (int)getTreeId());
stmt.setInt(2, (int)getAttrCategory());
stmt.setInt(3, (int)getAttrId());
OracleResultSet rs = (OracleResultSet)(stmt.executeQuery());
try {
boolean rsrtn = rs.next();
if (rsrtn) {
BLOB blob = (BLOB) rs.getBLOB(1);
InputStream is = new ByteArrayInputStream(getBinaryValue());
OutputStream os = blob.getBinaryOutputStream();
int bufSize = blob.getBufferSize();
byte[] buf = new byte[bufSize];
int length = 0;
while ((length = is.read(buf)) != -1) {
os.write(buf, 0, length);
}
os.flush();
os.close();
con.commit(); // Commit the update
}
} finally {
rs.close();
}
} finally {
stmt.close();
}
rtn = 0;
isDirty = false;
} finally {
con.setAutoCommit(autoComm);
}
} catch (IOException ex) {
ex.printStackTrace();
throw new RepositoryIOException(ex.toString());
}
return rtn;
}
Thanks for any help you could provide,
Marc
- NOTE **********************
ORA-22990 LOB locators cannot span transactions
Cause: A LOB locator selected in one transaction cannot be used in a different transaction.
Action: Reselect the LOB locator and retry the operation.
:
private static final String insSql = "begin INSERT INTO THETABLE(TREEENTRYID, ATTRCATEGORY, ATTRID, ATTRBINARYVALUE) VALUES(?,?,?,empty_blob()) return ATTRBINARYVALUE into ?; end;";
private static final String updSql = "begin UPDATE THETABLE SET ATTRBINARYVALUE = empty_blob() WHERE TREEENTRYID = ? AND ATTRCATEGORY = ? AND ATTRID = ? RETURN ATTRBINARYVALUE INTO ?; end;";
private static final String CNTSQL = "{call GETDOESEXIST(?,?,?,?)}";
public int save(Connection con) throws SQLException, RepositoryException{
int rtn = -1;
try {
boolean autoComm = con.getAutoCommit();
con.setAutoCommit(false);
int isThere = 0;
ResultSet rs = null;
try {
CallableStatement iitSt = con.prepareCall(CNTSQL);
try {
iitSt.setInt(1, (int)getTreeId());
iitSt.setInt(2, (int)getAttrCategory());
iitSt.setInt(3, (int)getAttrId());
iitSt.registerOutParameter(4, java.sql.Types.INTEGER);
iitSt.executeUpdate();
isThere = iitSt.getInt(4);
if ( iitSt.wasNull() ) {
// throw an exception?
isThere = 0;
}
} finally {
iitSt.close();
}
BLOB theBlob = null;
if (isThere == 0) {
OracleCallableStatement stmt =
(OracleCallableStatement)con.prepareCall(insSql);
try {
stmt.setInt(1, (int)getTreeId());
stmt.setInt(2, (int)getAttrCategory());
stmt.setInt(3, (int)getAttrId());
stmt.registerOutParameter(4, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBLOB(4);
InputStream is = new ByteArrayInputStream(getBinaryValue());
OutputStream os = blob.getBinaryOutputStream();
byte[] buf = new byte[1024];
int length = 0;
try {
while ((length = is.read(buf)) != -1) {
os.write(buf, 0, length);
}
} finally {
os.close();
}
} finally {
stmt.close();
}
} else {
OracleCallableStatement stmt =
(OracleCallableStatement)con.prepareCall(updSql);
try {
stmt.setInt(1, (int)getTreeId());
stmt.setInt(2, (int)getAttrCategory());
stmt.setInt(3, (int)getAttrId());
stmt.registerOutParameter(4, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBLOB(4);
InputStream is = new ByteArrayInputStream(getBinaryValue());
OutputStream os = blob.getBinaryOutputStream();
byte[] buf = new byte[1024];
int length = 0;
try {
while ((length = is.read(buf)) != -1) {
os.write(buf, 0, length);
}
} finally {
os.close();
}
} finally {
stmt.close();
}
}
rtn = 0;
isDirty = false;
} finally {
con.commit();
con.setAutoCommit(autoComm);
}
} catch (IOException ex) {
ex.printStackTrace();
throw new RepositoryIOException(ex.toString());
}
return rtn;
} Received on Tue Oct 30 2001 - 22:47:53 CET