I am trying to insert blob in Oracle SQL 11 by the following JDBC java code. But it throws an exception:-
private static final String INSERT_INTERIOR_ROOM_LOG = "INSERT INTO INTERIOR_ROOM_LOG"
+ "(PNO12, STR_WEEK_FROM, STR_WEEK_TO, RESPONSE_XML, MODIFIED_DATE, LOG_TIME ) " + "VALUES(?, ?, ?, EMPTY_BLOB(), SYSDATE, SYSTIMESTAMP)";
private static final String selectNextSequenceId = "select INTERIOR_LOG_INCREMENT_SEQ.nextval from dual";
private static final String selectIteriorRoomUpdate = "select RESPONSE_XML from INTERIOR_ROOM_LOG where id = ? for update";
static public Long insertIntoInteriorMaster(Connection connection, String pno12, long startWeek, long endWeek, String responseXml) {
Long retValue = -1l;
PreparedStatement pst = null;
ResultSet rset = null;
try {
pst = connection.prepareStatement(INSERT_INTERIOR_ROOM_LOG);
pst.setString(1, pno12);
pst.setLong(2, startWeek);
pst.setLong(3, endWeek);
pst.execute();
connection.commit();
PreparedStatement selectNextSequenceIdStatement = null;
ResultSet selectNextSequenceIdRs = null;
int sequenceId = -1;
PreparedStatement insertLogQueryStatement = null;
PreparedStatement selectMqMessageUpdateStatement = null;
ResultSet selectMqMessageUpdateRs = null;
selectNextSequenceIdStatement = connection.prepareStatement(selectNextSequenceId);
selectNextSequenceIdRs = selectNextSequenceIdStatement.executeQuery();
selectNextSequenceIdRs.next();
sequenceId = selectNextSequenceIdRs.getInt(1);
System.out.print("next id: " + sequenceId);
byte[] byteXaml = responseXml.getBytes();
if (byteXaml != null) {
selectMqMessageUpdateStatement =
connection.prepareStatement(selectIteriorRoomUpdate);
selectMqMessageUpdateStatement.setInt(1, sequenceId);
selectMqMessageUpdateRs =
selectMqMessageUpdateStatement.executeQuery();
selectMqMessageUpdateRs.next();
BLOB queuedMessage = (BLOB)selectMqMessageUpdateRs.getBlob(1); // exception in this line
OutputStream bos = queuedMessage.setBinaryStream(1);
int bufferSize = queuedMessage.getBufferSize();
int bytesToWrite = byteXaml.length;
for (int i = 0; i <= bytesToWrite && bufferSize > 0; i +=
bufferSize) {
if (i + bufferSize >= bytesToWrite) {
bufferSize = bytesToWrite - i;
}
bos.write(byteXaml, i, bufferSize);
}
bos.flush();
bos.close();
connection.commit();
}
System.out.println("Interior Log insert commited");
} catch (SQLException e) {
String errorMsg = String.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
System.out.println(errorMsg);
} catch (Exception ex) {
System.out.println("Error when insert in interior log. Handle error " + ex.getMessage());
}
return retValue;
}
SQL State: 99999 Exhausted result set
Could please tell me how do I insert blob into Oracle SQL?