/**
* Tests the implementation of getCharacterStream(long pos, long length).
*
* @throws Exception
*/
public void testGetCharacterStreamLong() throws Exception {
String str1 = "This is a test String. This is a test String";
Reader r1 = new java.io.StringReader(str1);
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, r1);
ps.execute();
ps.close();
Statement st = createStatement();
ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
rs.next();
Clob clob = rs.getClob(1);
Reader r_1 = clob.getCharacterStream(2L, 5L);
String str2 = str1.substring(1, 6);
Reader r_2 = new java.io.StringReader(str2);
assertEquals(r_2, r_1);
rs.close();
st.close();
}
/**
* Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from
* store. (DERBY-2891)
*/
public void testGetCharacterStreamLongOnLargeClob() throws Exception {
getConnection().setAutoCommit(false);
// create large (>32k) clob that can be read from store
final int size = 33000;
StringBuilder sb = new StringBuilder(size);
for (int i = 0; i < size; i += 10) {
sb.append("1234567890");
}
final int id = BlobClobTestSetup.getID();
PreparedStatement ps =
prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))");
ps.setInt(1, id);
ps.setString(2, sb.toString());
ps.executeUpdate();
ps.close();
Statement s = createStatement();
ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id);
assertTrue(rs.next());
Clob c = rs.getClob(1);
// request a small region of the clob
BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L));
assertEquals("456", r.readLine());
r.close();
c.free();
rs.close();
s.close();
rollback();
}
/**
* Preconditions: 1. xid is known to the RM or it's in prepared state
*
* <p>Implementation deficiency preconditions: 1. xid must be associated with this connection if
* it's not in prepared state.
*
* <p>Postconditions: 1. Transaction is rolled back and disassociated from connection
*/
public void rollback(Xid xid) throws XAException {
if (logger.logDebug()) debug("rolling back xid = " + xid);
// We don't explicitly check precondition 1.
try {
if (currentXid != null && xid.equals(currentXid)) {
state = STATE_IDLE;
currentXid = null;
conn.rollback();
conn.setAutoCommit(localAutoCommitMode);
} else {
String s = RecoveredXid.xidToString(xid);
conn.setAutoCommit(true);
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate("ROLLBACK PREPARED '" + s + "'");
} finally {
stmt.close();
}
}
} catch (SQLException ex) {
throw new PGXAException(
GT.tr("Error rolling back prepared transaction"), ex, XAException.XAER_RMERR);
}
}
/**
* Preconditions: 1. xid must be in prepared state in the server
*
* <p>Implementation deficiency preconditions: 1. Connection must be in idle state
*
* <p>Postconditions: 1. Transaction is committed
*/
private void commitPrepared(Xid xid) throws XAException {
try {
// Check preconditions. The connection mustn't be used for another
// other XA or local transaction, or the COMMIT PREPARED command
// would mess it up.
if (state != STATE_IDLE || conn.getTransactionState() != ProtocolConnection.TRANSACTION_IDLE)
throw new PGXAException(
GT.tr("Not implemented: 2nd phase commit must be issued using an idle connection"),
XAException.XAER_RMERR);
String s = RecoveredXid.xidToString(xid);
localAutoCommitMode = conn.getAutoCommit();
conn.setAutoCommit(true);
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate("COMMIT PREPARED '" + s + "'");
} finally {
stmt.close();
conn.setAutoCommit(localAutoCommitMode);
}
} catch (SQLException ex) {
throw new PGXAException(
GT.tr("Error committing prepared transaction"), ex, XAException.XAER_RMERR);
}
}
//
// Examine BLOBs and CLOBs.
//
private void vetLargeObjects(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
stmt.execute(
"INSERT INTO t (id, b, c) VALUES (1, "
+ "CAST ("
+ TestUtil.stringToHexLiteral("101010001101")
+ "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");
ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");
rs.next();
Blob blob = rs.getBlob(2);
Clob clob = rs.getClob(3);
vetObject(blob, unsupportedList, notUnderstoodList);
vetObject(clob, unsupportedList, notUnderstoodList);
stmt.close();
conn.rollback();
}
/** private method which actually will do all of our work for the sample */
private void executeSample() {
String query = "select anEmployee from staff2";
try {
Statement stmt = _con.createStatement();
;
// Execute the query which will return an Employee object
// We will cast this using the Person interface. Note the
// Person interface class MUST be in your CLASSPATH. You
// Do not need Employee in your CLASSPATH.
ResultSet rs = stmt.executeQuery(query);
output("***Using interface class\n");
while (rs.next()) {
Person aPerson = (Person) rs.getObject(1);
displayMethods(aPerson.getClass());
output(
"The person is: "
+ aPerson.toString()
+ "\nFirst Name= "
+ aPerson.getFirstName()
+ "\nLast Name= "
+ aPerson.getLastName()
+ "\n");
}
// Now execute the same query, but this time we will use
// reflection to access the class. Again, only the interface
// Person is required in the CLASSPATH
rs = stmt.executeQuery(query);
output("***Using reflection\n");
Object theObj = null;
while (rs.next()) {
theObj = rs.getObject(1);
output("The person is: " + theObj.toString() + "\n");
Class theClass = theObj.getClass();
displayMethods(theClass);
Method m1 = theClass.getMethod("toString", new Class[0]);
Method m2 = theClass.getMethod("getFirstName", new Class[0]);
Method m3 = theClass.getMethod("getLastName", new Class[0]);
output(
"The person is: "
+ (Object) m1.invoke(theObj, new Object[0])
+ "\nFirst Name= "
+ (Object) m2.invoke(theObj, new Object[0])
+ "\nLast Name= "
+ (Object) m3.invoke(theObj, new Object[0])
+ "\n");
}
rs.close();
stmt.close();
} catch (SQLException sqe) {
displaySQLEx(sqe);
} catch (Exception e) {
error("Unexpected exception : " + e.toString() + "\n");
e.printStackTrace();
}
}
/**
* Inserts a Clob with the specified length, using a stream source, then fetches it from the
* database and checks the length.
*
* @param length number of characters in the Clob
* @throws IOException if reading from the source fails
* @throws SQLException if something goes wrong
*/
private void insertAndFetchTest(long length) throws IOException, SQLException {
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
long tsStart = System.currentTimeMillis();
ps.execute();
println(
"Inserted "
+ length
+ " chars (length specified) in "
+ (System.currentTimeMillis() - tsStart)
+ " ms");
Statement stmt = createStatement();
tsStart = System.currentTimeMillis();
ResultSet rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
assertTrue("Clob not inserted", rs.next());
Clob aClob = rs.getClob(1);
assertEquals("Invalid length", length, aClob.length());
println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
rs.close();
// Insert same Clob again, using the lengthless override.
id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, new LoopingAlphabetReader(length));
tsStart = System.currentTimeMillis();
ps.executeUpdate();
println(
"Inserted "
+ length
+ " chars (length unspecified) in "
+ (System.currentTimeMillis() - tsStart)
+ " ms");
rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
assertTrue("Clob not inserted", rs.next());
aClob = rs.getClob(1);
assertEquals("Invalid length", length, aClob.length());
println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
rs.close();
rollback();
}
private Statement nextChainedInvoker(Statement methodInvoker) {
Field[] declaredFields = methodInvoker.getClass().getDeclaredFields();
for (Field field : declaredFields) {
Statement statement = statementOrNull(methodInvoker, field);
if (statement != null) return statement;
}
return null;
}
private void runMethodInvoker(
RunNotifier notifier,
Description description,
Statement methodInvoker,
Description methodWithParams) {
try {
methodInvoker.evaluate();
} catch (Throwable e) {
notifier.fireTestFailure(new Failure(methodWithParams, e));
}
}
/**
* Preconditions: 1. flag must be one of TMSTARTRSCAN, TMENDRSCAN, TMNOFLAGS or TMSTARTTRSCAN |
* TMENDRSCAN 2. if flag isn't TMSTARTRSCAN or TMSTARTRSCAN | TMENDRSCAN, a recovery scan must be
* in progress
*
* <p>Postconditions: 1. list of prepared xids is returned
*/
public Xid[] recover(int flag) throws XAException {
// Check preconditions
if (flag != TMSTARTRSCAN
&& flag != TMENDRSCAN
&& flag != TMNOFLAGS
&& flag != (TMSTARTRSCAN | TMENDRSCAN))
throw new PGXAException(GT.tr("Invalid flag"), XAException.XAER_INVAL);
// We don't check for precondition 2, because we would have to add some additional state in
// this object to keep track of recovery scans.
// All clear. We return all the xids in the first TMSTARTRSCAN call, and always return
// an empty array otherwise.
if ((flag & TMSTARTRSCAN) == 0) return new Xid[0];
else {
try {
Statement stmt = conn.createStatement();
try {
// If this connection is simultaneously used for a transaction,
// this query gets executed inside that transaction. It's OK,
// except if the transaction is in abort-only state and the
// backed refuses to process new queries. Hopefully not a problem
// in practise.
ResultSet rs = stmt.executeQuery("SELECT gid FROM pg_prepared_xacts");
LinkedList l = new LinkedList();
while (rs.next()) {
Xid recoveredXid = RecoveredXid.stringToXid(rs.getString(1));
if (recoveredXid != null) l.add(recoveredXid);
}
rs.close();
return (Xid[]) l.toArray(new Xid[l.size()]);
} finally {
stmt.close();
}
} catch (SQLException ex) {
throw new PGXAException(GT.tr("Error during recover"), ex, XAException.XAER_RMERR);
}
}
}
private void closeConnection() {
try {
if (statement != null) statement.close();
if (connection != null) {
JDBCExceptionReporter.logWarnings(connection.getWarnings());
connection.clearWarnings();
connectionProvider.closeConnection(connection);
connectionProvider.close();
}
} catch (Exception e) {
System.err.println("Could not close connection");
e.printStackTrace();
}
}
/**
* Insert a row with a large clob into the test table. Read the row from the database and assign
* the clob value to <code>clob</code>.
*
* @return The id of the row that was inserted
* @throws java.sql.SQLException
*/
private int initializeLongClob() throws SQLException {
// Clob needs to be larger than one page for locking to occur
final int lobLength = 40000;
// Insert a long Clob
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, new LoopingAlphabetReader(lobLength), lobLength);
ps.execute();
ps.close();
commit();
// Fetch the Clob object from the database
Statement st = createStatement();
ResultSet rs = st.executeQuery("select CLOBDATA from BLOBCLOB where ID=" + id);
rs.next();
clob = rs.getClob(1);
rs.close();
st.close();
return id;
}
/**
* Preconditions: 1. xid != null 2. xid is in ended state
*
* <p>Implementation deficiency preconditions: 1. xid was associated with this connection
*
* <p>Postconditions: 1. Transaction is prepared
*/
public int prepare(Xid xid) throws XAException {
if (logger.logDebug()) debug("preparing transaction xid = " + xid);
// Check preconditions
if (!currentXid.equals(xid)) {
throw new PGXAException(
GT.tr(
"Not implemented: Prepare must be issued using the same connection that started the transaction"),
XAException.XAER_RMERR);
}
if (state != STATE_ENDED)
throw new PGXAException(GT.tr("Prepare called before end"), XAException.XAER_INVAL);
state = STATE_IDLE;
currentXid = null;
if (!conn.haveMinimumServerVersion("8.1"))
throw new PGXAException(
GT.tr("Server versions prior to 8.1 do not support two-phase commit."),
XAException.XAER_RMERR);
try {
String s = RecoveredXid.xidToString(xid);
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate("PREPARE TRANSACTION '" + s + "'");
} finally {
stmt.close();
}
conn.setAutoCommit(localAutoCommitMode);
return XA_OK;
} catch (SQLException ex) {
throw new PGXAException(GT.tr("Error preparing transaction"), ex, XAException.XAER_RMERR);
}
}
/**
* Try to update the row with the given error. Flag a failure if a timeout occurs when not
* expected, and vice versa.
*
* @param id The id of the row to be updated
* @param timeoutExpected true if it is expected that the update times out
* @throws java.sql.SQLException
*/
private void executeParallelUpdate(int id, boolean timeoutExpected) throws SQLException {
Connection conn2 = openDefaultConnection();
Statement stmt2 = conn2.createStatement();
try {
stmt2.executeUpdate(
"update BLOBCLOB set BLOBDATA = " + "cast(X'FFFFFF' as blob) where ID=" + id);
stmt2.close();
conn2.commit();
conn2.close();
if (timeoutExpected) {
fail("FAIL - should have gotten lock timeout");
}
} catch (SQLException se) {
stmt2.close();
conn2.rollback();
conn2.close();
if (timeoutExpected) {
assertSQLState(LOCK_TIMEOUT, se);
} else {
throw se;
}
}
}
public void execute(String[] sqls) {
String sql = null;
String showSqlText = properties.getProperty("hibernate.show_sql");
boolean showSql = ("true".equalsIgnoreCase(showSqlText));
try {
createConnection();
statement = connection.createStatement();
for (int i = 0; i < sqls.length; i++) {
sql = sqls[i];
String delimitedSql = sql + getSqlDelimiter();
if (showSql) log.debug(delimitedSql);
statement.executeUpdate(delimitedSql);
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("couldn't execute sql '" + sql + "'", e);
} finally {
closeConnection();
}
}
public String consultarImagen(
Connection conn,
String path,
String pagina,
String sucID,
String vigID,
String padreID,
String secundarioID,
String referenciaID,
String documentoID,
String consecutivoID) {
Vector vRetornoImg = new Vector(1);
Statement st = null;
ResultSet rs = null;
String sql = "";
try {
if (consecutivoID.length() <= 0) consecutivoID = "%";
if (pagina.equalsIgnoreCase("prerequisitodet")) {
sql =
" select PREDI_IMAGEN img, PREDI_NOMBREIMAGEN nameimg"
+ " from COT_PREREQUISITODETIMG"
+ " where suc_id = '"
+ sucID
+ "' "
+ " and vig_id = '"
+ vigID
+ "'"
+ " and prec_id = '"
+ padreID
+ "'"
+ " and tpr_id = '"
+ secundarioID
+ "'"
+ " and tdo_referencia = '"
+ referenciaID
+ "'"
+ " and tdo_id = '"
+ documentoID
+ "'"
+ " and predi_consecutivo like '"
+ consecutivoID
+ "'";
}
if (pagina.equalsIgnoreCase("requisitosdis")) {
sql =
" select REDII_IMAGEN img, REDII_NOMBREIMAGEN nameimg"
+ " from COT_REQHABILITANTEDISIMG "
+ " where suc_id = '"
+ sucID
+ "' "
+ " and vig_id = '"
+ vigID
+ "'"
+ " and disc_id = '"
+ padreID
+ "'"
+ " and tdo_referencia = '"
+ referenciaID
+ "'"
+ " and tdo_id = '"
+ documentoID
+ "'";
}
if (pagina.equalsIgnoreCase("proceso")) {
sql =
" select PROEI_IMAGEN img, PROEI_NOMBREIMAGEN nameimg"
+ " from COT_PROCESOESTIMG "
+ " where suc_id = '"
+ sucID
+ "' "
+ " and vig_id = '"
+ vigID
+ "'"
+ " and proc_id = '"
+ padreID
+ "'"
+ " and epo_id = '"
+ secundarioID
+ "'"
+ " and tdo_referencia = '"
+ referenciaID
+ "'"
+ " and tdo_id = '"
+ documentoID
+ "'"
+ " and proei_consecutivo like '"
+ consecutivoID
+ "'";
}
st = conn.createStatement();
rs = st.executeQuery(sql);
if (rs.next()) {
vRetornoImg.clear();
vRetornoImg.addElement(rs.getBlob("img"));
vRetornoImg.addElement(rs.getString("nameimg"));
return famhn.upload.central.FileUpload.materializarImagen(path + "/", conn, vRetornoImg);
}
return "Pendiente";
} catch (Exception e) {
System.out.println("" + e.toString());
return e.toString();
} finally {
try {
rs.close();
rs = null;
} catch (Exception er) {;
}
try {
st.close();
st = null;
} catch (Exception err) {;
}
}
}
public String eliminarImagenes(
Connection conn,
String pagina,
String sucID,
String vigID,
String padreID,
String secundarioID,
String referenciaID,
String documentoID,
String consecutivoID) {
String sql = "";
Statement stmt = null;
UtilidadesDatos ud = new UtilidadesDatos();
try {
if (consecutivoID.length() <= 0) consecutivoID = "1";
if (pagina.equalsIgnoreCase("prerequisitodet")) {
sql =
"delete from COT_PREREQUISITODETIMG "
+ "where SUC_ID = '"
+ sucID
+ "'"
+ " and VIG_ID = '"
+ vigID
+ "'"
+ " and PREC_ID = '"
+ padreID
+ "'"
+ " and TPR_ID = '"
+ secundarioID
+ "'"
+ " and TDO_REFERENCIA = '"
+ referenciaID
+ "'"
+ " and TDO_ID = '"
+ documentoID
+ "'"
+ " and PREDI_CONSECUTIVO = '"
+ consecutivoID
+ "'";
}
if (pagina.equalsIgnoreCase("requisitosdis")) {
sql =
"delete from COT_REQHABILITANTEDISIMG "
+ "where SUC_ID = '"
+ sucID
+ "'"
+ " and VIG_ID = '"
+ vigID
+ "'"
+ " and DISC_ID = '"
+ padreID
+ "'"
+ " and TDO_REFERENCIA = '"
+ referenciaID
+ "'"
+ " and TDO_ID = '"
+ documentoID
+ "'"
+ " and REDII_CONSECUTIVO = '"
+ consecutivoID
+ "'";
}
if (pagina.equalsIgnoreCase("proceso")) {
sql =
"delete from COT_PROCESOESTIMG "
+ "where SUC_ID = '"
+ sucID
+ "'"
+ " and VIG_ID = '"
+ vigID
+ "'"
+ " and PROC_ID = '"
+ padreID
+ "'"
+ " and EPO_ID like '"
+ secundarioID
+ "'"
+ " and TDO_REFERENCIA like '"
+ referenciaID
+ "'"
+ " and TDO_ID like '"
+ documentoID
+ "'"
+ " and PROEI_CONSECUTIVO like '"
+ consecutivoID
+ "'";
}
stmt = conn.createStatement();
stmt.executeUpdate(sql);
stmt.close();
return "Ok";
} catch (Exception e) {
System.out.println("" + e.toString());
return e.toString();
} finally {
try {
stmt.close();
stmt = null;
} catch (Exception err) {;
}
}
}
/**
* Tests the exceptions thrown by the getCharacterStream (long pos, long length) for the following
* conditions a) pos <= 0 b) pos > (length of LOB) c) length < 0 d) pos + length > (length of
* LOB).
*
* @throws SQLException
*/
public void testGetCharacterStreamLongExceptionConditions() throws SQLException {
String str1 = "This is a test String. This is a test String";
Reader r1 = new java.io.StringReader(str1);
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, r1);
ps.execute();
ps.close();
Statement st = createStatement();
ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
rs.next();
Clob clob = rs.getClob(1);
// check the case where pos <= 0
try {
// set pos as negative
clob.getCharacterStream(-2L, 5L);
// Should not come here. The exception has to be thrown.
fail("FAIL: Expected SQLException for pos being negative " + "not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when pos <= 0 is XJ070
assertSQLState("XJ070", sqle);
}
// check for the case pos > length of clob
try {
// set the pos to any value greater than the Clob length
clob.getCharacterStream(clob.length() + 1, 5L);
// Should not come here. The exception has to be thrown.
fail(
"FAIL: Expected SQLException for position being greater than "
+ "length of LOB not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when pos > length of Clob
// is XJ076
assertSQLState("XJ087", sqle);
}
// check for the case when length < 0
try {
// set length as negative
clob.getCharacterStream(2L, -5L);
// Should not come here. The exception has to be thrown.
fail("Fail: expected exception for the length being negative " + "not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when length < 0 of Clob
// is XJ071
assertSQLState("XJ071", sqle);
}
// check for the case when pos + length > length of Clob
try {
// set pos + length > length of Clob
clob.getCharacterStream((clob.length() - 4), 10L);
// Should not come here. The exception has to be thrown.
fail(
"Fail: expected exception for the sum of position and length"
+ " being greater than the LOB size not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when length < 0 of Clob
// is XJ087
assertSQLState("XJ087", sqle);
}
}
