| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc4.PreparedStatementTest |
| |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to you under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.jdbc4; |
| |
| import java.io.ByteArrayInputStream; |
| import java.io.IOException; |
| import java.io.InputStream; |
| import java.io.OutputStream; |
| import java.io.Reader; |
| import java.io.StringReader; |
| import java.sql.Blob; |
| import java.sql.CallableStatement; |
| import java.sql.Clob; |
| import java.sql.Connection; |
| import java.sql.NClob; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.RowId; |
| import java.sql.SQLException; |
| import java.sql.SQLFeatureNotSupportedException; |
| import java.sql.SQLXML; |
| import java.sql.Statement; |
| import java.sql.Types; |
| import junit.framework.Test; |
| import org.apache.derby.iapi.services.io.DerbyIOException; |
| import org.apache.derby.shared.common.sanity.SanityManager; |
| import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * This class is used to test JDBC4 specific methods in the PreparedStatement(s) |
| * object. |
| * |
| * A number of methods and variables are in place to aid the writing of tests: |
| * <ul><li>setBinaryStreamOnBlob |
| * <li>setAsciiStream |
| * <li>key - an id. One is generated each time setUp is run. |
| * <li>reqeustKey() - generate a new unique id. |
| * <li>psInsertX - prepared statements for insert. |
| * <li>psFetchX - prepared statements for fetching values. |
| * </ul> |
| * |
| * For table creation, see the <code>suite</code>-method. |
| */ |
| public class PreparedStatementTest extends BaseJDBCTestCase { |
| |
| private static final String BLOBTBL = "BlobTestTable"; |
| private static final String CLOBTBL = "ClobTestTable"; |
| private static final String LONGVARCHAR = "LongVarcharTestTable"; |
| |
| /** Key used to id data inserted into the database. */ |
| private static int globalKey = 1; |
| |
| /** Byte array passed in to the database. **/ |
| private static final byte[] BYTES = { |
| 0x65, 0x66, 0x67, 0x68, 0x69, |
| 0x69, 0x68, 0x67, 0x66, 0x65 |
| }; |
| |
| // Default connection and prepared statements that are used by the tests. |
| /** |
| * Default key to use for insertions. |
| * Is unique for each fixture. More keys can be fetched by calling |
| * <link>requestKey</link>. |
| */ |
| private int key; |
| /** Default connection object. */ |
| /** PreparedStatement object with no positional arguments. */ |
| private PreparedStatement ps = null; |
| /** PreparedStatement to fetch BLOB with specified id. */ |
| private PreparedStatement psFetchBlob = null; |
| /** PreparedStatement to insert a BLOB with specified id. */ |
| private PreparedStatement psInsertBlob = null; |
| /** PreparedStatement to fetch CLOB with specified id. */ |
| private PreparedStatement psFetchClob = null; |
| /** PreparedStatement to insert a CLOB with specified id. */ |
| private PreparedStatement psInsertClob = null; |
| /** PreparedStatement to insert a LONG VARCHAR with specified id. */ |
| private PreparedStatement psInsertLongVarchar = null; |
| //Statement object |
| private Statement s = null; |
| |
| |
| |
| /** |
| * Create a test with the given name. |
| * |
| * @param name name of the test. |
| */ |
| public PreparedStatementTest(String name) { |
| super(name); |
| } |
| |
| /** |
| * |
| * Obtain a "regular" connection and PreparedStatement that the tests |
| * can use. |
| * |
| * @throws SQLException |
| */ |
| public void setUp() |
| throws SQLException { |
| key = requestKey(); |
| //create the statement object |
| s = createStatement(); |
| //Create the PreparedStatement that will then be used as the basis |
| //throughout this test henceforth |
| //This prepared statement will however NOT be used for testing |
| //setClob and setBlob |
| ps = prepareStatement("select count(*) from sys.systables"); |
| |
| // Prepare misc statements. |
| psFetchBlob = prepareStatement("SELECT dBlob FROM " + |
| BLOBTBL + " WHERE sno = ?"); |
| psInsertBlob = prepareStatement("INSERT INTO " + BLOBTBL + |
| " VALUES (?, ?)"); |
| psFetchClob = prepareStatement("SELECT dClob FROM " + |
| CLOBTBL + " WHERE sno = ?"); |
| psInsertClob = prepareStatement("INSERT INTO " + CLOBTBL + |
| " VALUES (?, ?)"); |
| psInsertLongVarchar = prepareStatement("INSERT INTO " + LONGVARCHAR + |
| " VALUES (?, ?)"); |
| } |
| |
| /** |
| * |
| * Release the resources that are used in this test |
| * |
| * @throws SQLException |
| * |
| */ |
| public void tearDown() |
| throws Exception { |
| |
| s.close(); |
| ps.close(); |
| |
| s = null; |
| ps = null; |
| |
| psFetchBlob.close(); |
| psFetchClob.close(); |
| psInsertBlob.close(); |
| psInsertClob.close(); |
| psInsertLongVarchar.close(); |
| |
| psFetchBlob = null; |
| psFetchClob = null; |
| psInsertBlob = null; |
| psInsertClob = null; |
| psInsertLongVarchar = null; |
| |
| super.tearDown(); |
| } |
| |
| public static Test suite() { |
| BaseTestSuite suite = |
| new BaseTestSuite("PreparedStatementTest suite"); |
| |
| suite.addTest(baseSuite("PreparedStatementTest:embedded")); |
| suite.addTest( |
| TestConfiguration.connectionXADecorator( |
| baseSuite("PreparedStatementTest:embedded XADataSource"))); |
| |
| suite.addTest(TestConfiguration.clientServerDecorator( |
| baseSuite("PreparedStatementTest:client"))); |
| |
| suite.addTest(TestConfiguration.clientServerDecorator( |
| TestConfiguration.connectionCPDecorator( baseSuite |
| ("PreparedStatementTest:logical")))); |
| |
| // Tests for the client side JDBC statement cache. |
| suite.addTest(TestConfiguration.clientServerDecorator( |
| statementCachingSuite())); |
| |
| suite.addTest( |
| TestConfiguration.clientServerDecorator( |
| TestConfiguration.connectionXADecorator( |
| baseSuite("PreparedStatementTest:client XXXXADataSource")))); |
| |
| return suite; |
| } |
| |
| private static Test baseSuite(String name) { |
| BaseTestSuite suite = new BaseTestSuite(name); |
| suite.addTestSuite(PreparedStatementTest.class); |
| return new CleanDatabaseTestSetup(suite) { |
| |
| protected void decorateSQL(Statement stmt) throws SQLException |
| { |
| stmt.execute("create table " + BLOBTBL + |
| " (sno int, dBlob BLOB(1M))"); |
| stmt.execute("create table " + CLOBTBL + |
| " (sno int, dClob CLOB(1M))"); |
| stmt.execute("create table " + LONGVARCHAR + |
| " (sno int, dLongVarchar LONG VARCHAR)"); |
| } |
| }; |
| } |
| |
| /** |
| * Returns a suite for tests that need JDBC statement caching to be enabled. |
| */ |
| private static Test statementCachingSuite() { |
| BaseTestSuite suite = |
| new BaseTestSuite("JDBC statement caching suite"); |
| |
| suite.addTest(new PreparedStatementTest("cpTestIsPoolableHintFalse")); |
| suite.addTest(new PreparedStatementTest("cpTestIsPoolableHintTrue")); |
| return TestConfiguration.connectionCPDecorator( |
| new CleanDatabaseTestSetup(suite) { |
| |
| protected void decorateSQL(Statement stmt) |
| throws SQLException { |
| stmt.execute("create table " + BLOBTBL + |
| " (sno int, dBlob BLOB(1M))"); |
| stmt.execute("create table " + CLOBTBL + |
| " (sno int, dClob CLOB(1M))"); |
| stmt.execute("create table " + LONGVARCHAR + |
| " (sno int, dLongVarchar LONG VARCHAR)"); |
| } |
| }); |
| } |
| |
| //-------------------------------------------------------------------------- |
| //BEGIN THE TEST OF THE METHODS THAT THROW AN UNIMPLEMENTED EXCEPTION IN |
| //THIS CLASS |
| |
| /** |
| * Tests the setRowId method of the PreparedStatement interface |
| * |
| * @throws SQLException upon any failure that occurs in the |
| * call to the method. |
| */ |
| public void testSetRowId() throws SQLException{ |
| try { |
| RowId rowid = null; |
| ps.setRowId(0,rowid); |
| fail("setRowId should not be implemented"); |
| } |
| catch(SQLFeatureNotSupportedException sqlfne) { |
| //Do Nothing, This happens as expected |
| } |
| } |
| |
| /** |
| * Tests the setNString method of the PreparedStatement interface |
| * |
| * @throws SQLException upon any failure that occurs in the |
| * call to the method. |
| */ |
| public void testSetNString() throws SQLException{ |
| try { |
| String str = null; |
| ps.setNString(0,str); |
| fail("setNString should not be implemented"); |
| } |
| catch(SQLFeatureNotSupportedException sqlfne) { |
| //Do Nothing, This happens as expected |
| } |
| } |
| |
| /** |
| * Tests the setNCharacterStream method of the PreparedStatement interface |
| * |
| * @throws SQLException upon any failure that occurs in the |
| * call to the method. |
| */ |
| public void testSetNCharacterStream() throws SQLException{ |
| try { |
| Reader r = null; |
| ps.setNCharacterStream(0,r,0); |
| fail("setNCharacterStream should not be implemented"); |
| } |
| catch(SQLFeatureNotSupportedException sqlfne) { |
| //Do Nothing, This happens as expected |
| } |
| } |
| |
| public void testSetNCharacterStreamLengthlessNotImplemented() |
| throws SQLException { |
| try { |
| ps.setNCharacterStream(1, new StringReader("A string")); |
| fail("setNCharacterStream(int,Reader) should not be implemented"); |
| } catch (SQLFeatureNotSupportedException sfnse) { |
| // Do nothing, this is expected behavior. |
| } |
| } |
| |
| public void testSetNClobLengthlessNotImplemented() |
| throws SQLException { |
| try { |
| ps.setNClob(1, new StringReader("A string")); |
| fail("setNClob(int,Reader) should not be implemented"); |
| } catch (SQLFeatureNotSupportedException sfnse) { |
| // Do nothing, this is expected behaviour. |
| } |
| } |
| |
| /** |
| * Tests the setNClob method of the PreparedStatement interface |
| * |
| * @throws SQLException upon any failure that occurs in the |
| * call to the method. |
| */ |
| public void testSetNClob1() throws SQLException{ |
| try { |
| NClob nclob = null; |
| ps.setNClob(0,nclob); |
| fail("setNClob should not be implemented"); |
| } |
| catch(SQLFeatureNotSupportedException sqlfne) { |
| //Do Nothing, This happens as expected |
| } |
| } |
| |
| /** |
| * Tests the setNClob method of the PreparedStatement interface |
| * |
| * @throws SQLException upon any failure that occurs in the |
| * call to the method. |
| */ |
| public void testSetNClob2() throws SQLException{ |
| try { |
| Reader reader = null; |
| ps.setNClob(0,reader,0); |
| fail("setNClob should not be implemented"); |
| } |
| catch(SQLFeatureNotSupportedException sqlfne) { |
| //Do Nothing, This happens as expected |
| } |
| } |
| |
| /** |
| * Tests the setSQLXML method of the PreparedStatement interface |
| * |
| * @throws SQLException upon any failure that occurs in the |
| * call to the method. |
| */ |
| public void testSetSQLXML() throws SQLException{ |
| try { |
| SQLXML sqlxml = null; |
| ps.setSQLXML(0,sqlxml); |
| fail("setNClob should not be implemented"); |
| } |
| catch(SQLFeatureNotSupportedException sqlfne) { |
| //Do Nothing, This happens as expected |
| } |
| } |
| |
| //-------------------------------------------------------------------------- |
| //Now test the methods that are implemented in the PreparedStatement |
| //interface |
| |
| public void testIsWrapperForStatement() throws SQLException { |
| assertTrue(ps.isWrapperFor(Statement.class)); |
| } |
| |
| public void testIsWrapperForPreparedStatement() throws SQLException { |
| assertTrue(ps.isWrapperFor(PreparedStatement.class)); |
| } |
| |
| public void testIsNotWrapperForCallableStatement() throws SQLException { |
| assertFalse(ps.isWrapperFor(CallableStatement.class)); |
| } |
| |
| public void testIsNotWrapperForResultSet() throws SQLException { |
| assertFalse(ps.isWrapperFor(ResultSet.class)); |
| } |
| |
| public void testIsWrapperForSelf() throws SQLException { |
| assertTrue(ps.isWrapperFor(ps.getClass())); |
| } |
| |
| public void testUnwrapStatement() throws SQLException { |
| Statement stmt = ps.unwrap(Statement.class); |
| assertSame("Unwrap returned wrong object.", ps, stmt); |
| } |
| |
| public void testUnwrapPreparedStatement() throws SQLException { |
| PreparedStatement ps2 = ps.unwrap(PreparedStatement.class); |
| assertSame("Unwrap returned wrong object.", ps, ps2); |
| } |
| |
| public void testUnwrapAsSelf() throws SQLException { |
| PreparedStatement ps2 = ps.unwrap(ps.getClass()); |
| assertSame("Unwrap returned wrong object.", ps, ps2); |
| } |
| |
| public void testUnwrapCallableStatement() { |
| try { |
| CallableStatement cs = ps.unwrap(CallableStatement.class); |
| fail("Unwrap didn't fail."); |
| } catch (SQLException e) { |
| assertSQLState("XJ128", e); |
| } |
| } |
| |
| public void testUnwrapResultSet() { |
| try { |
| ResultSet rs = ps.unwrap(ResultSet.class); |
| fail("Unwrap didn't fail."); |
| } catch (SQLException e) { |
| assertSQLState("XJ128", e); |
| } |
| } |
| |
| //----------------------------------------------------------------------- |
| // Begin test for setClob and setBlob |
| |
| /* |
| we need a table in which a Clob or a Blob can be stored. We basically |
| need to write tests for the setClob and the setBlob methods. |
| Proper process would be |
| a) Do a createClob or createBlob |
| b) Populate data in the LOB |
| c) Store in Database |
| |
| But the createClob and createBlob implementations are not |
| available on the EmbeddedServer. So instead the workaround adopted |
| is |
| |
| a) store a Clob or Blob in Database. |
| b) Retrieve it from the database. |
| c) store it back using setClob or setBlob |
| |
| */ |
| |
| /** |
| * |
| * Test the setClob() method |
| * |
| * @throws SQLException if a failure occurs during the call to setClob |
| * |
| */ |
| public void testSetClob() |
| throws IOException, SQLException { |
| // Life span of Clob objects are limited by the transaction. Need |
| // autocommit off so Clob objects survive execution of next statement. |
| getConnection().setAutoCommit(false); |
| |
| //insert default values into the table |
| |
| String str = "Test data for the Clob object"; |
| StringReader is = new StringReader("Test data for the Clob object"); |
| is.reset(); |
| |
| //initially insert the data |
| psInsertClob.setInt(1, key); |
| psInsertClob.setClob(2, is, str.length()); |
| psInsertClob.executeUpdate(); |
| |
| //Now query to retrieve the Clob |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| rs.next(); |
| Clob clobToBeInserted = rs.getClob(1); |
| rs.close(); |
| |
| //Now use the setClob method |
| int secondKey = requestKey(); |
| psInsertClob.setInt(1, secondKey); |
| psInsertClob.setClob(2, clobToBeInserted); |
| psInsertClob.execute(); |
| |
| psInsertClob.close(); |
| |
| //Now test to see that the Clob has been stored correctly |
| psFetchClob.setInt(1, secondKey); |
| rs = psFetchClob.executeQuery(); |
| rs.next(); |
| Clob clobRetrieved = rs.getClob(1); |
| |
| assertEquals(clobToBeInserted,clobRetrieved); |
| } |
| |
| /** |
| * Insert <code>Clob</code> without specifying length and read it back |
| * for verification. |
| * |
| * @throws IOException If an IOException during the close operation on the |
| * reader. |
| * @throws SQLException If an SQLException occurs. |
| */ |
| public void testSetClobLengthless() |
| throws IOException, SQLException { |
| // Life span of Clob objects are the transaction. Need autocommit off |
| // to have Clob objects survive execution of next statement. |
| getConnection().setAutoCommit(false); |
| |
| //Create the Clob and insert data into it. |
| Clob insertClob = getConnection().createClob(); |
| OutputStream os = insertClob.setAsciiStream(1); |
| os.write(BYTES); |
| |
| //Insert the Clob created above into the |
| //database. |
| psInsertClob.setInt(1, key); |
| psInsertClob.setClob(2, insertClob); |
| psInsertClob.execute(); |
| |
| // Read back test data from database. |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| assertTrue("No results retrieved", rs.next()); |
| Clob clobRetrieved = rs.getClob(1); |
| |
| // Verify test data. |
| assertEquals(insertClob, clobRetrieved); |
| } |
| |
| /** |
| * |
| * Test the setBlob() method |
| * |
| * @throws SQLException if a failure occurs during the call to setBlob |
| * |
| */ |
| public void testSetBlob() |
| throws IOException, SQLException { |
| // Life span of Blob objects are limited by the transaction. Need |
| // autocommit off so Blob objects survive execution of next statement. |
| getConnection().setAutoCommit(false); |
| |
| //insert default values into the table |
| InputStream is = new java.io.ByteArrayInputStream(BYTES); |
| is.reset(); |
| |
| //initially insert the data |
| psInsertBlob.setInt(1, key); |
| psInsertBlob.setBlob(2, is, BYTES.length); |
| psInsertBlob.executeUpdate(); |
| |
| //Now query to retrieve the Blob |
| psFetchBlob.setInt(1, key); |
| ResultSet rs = psFetchBlob.executeQuery(); |
| rs.next(); |
| Blob blobToBeInserted = rs.getBlob(1); |
| rs.close(); |
| |
| //Now use the setBlob method |
| int secondKey = requestKey(); |
| psInsertBlob.setInt(1, secondKey); |
| psInsertBlob.setBlob(2, blobToBeInserted); |
| psInsertBlob.execute(); |
| |
| psInsertBlob.close(); |
| |
| //Now test to see that the Blob has been stored correctly |
| psFetchBlob.setInt(1, secondKey); |
| rs = psFetchBlob.executeQuery(); |
| rs.next(); |
| Blob blobRetrieved = rs.getBlob(1); |
| |
| assertEquals(blobToBeInserted, blobRetrieved); |
| } |
| |
| /** |
| * Insert <code>Blob</code> without specifying length and read it back |
| * for verification. |
| */ |
| public void testSetBlobLengthless() |
| throws IOException, SQLException { |
| // Life span of Blob objects are the transaction. Need autocommit off |
| // to have Blob objects survive execution of next statement. |
| getConnection().setAutoCommit(false); |
| // Create Blob to be inserted |
| Blob insertBlob = getConnection().createBlob(); |
| OutputStream os = insertBlob.setBinaryStream(1); |
| os.write(BYTES); |
| int secondKey = requestKey(); |
| psInsertBlob.setInt(1, secondKey); |
| psInsertBlob.setBlob(2, insertBlob); |
| psInsertBlob.execute(); |
| os.close(); |
| psInsertBlob.close(); |
| |
| // Read back test data from database. |
| psFetchBlob.setInt(1, secondKey); |
| ResultSet rs = psFetchBlob.executeQuery(); |
| assertTrue("No results retrieved", rs.next()); |
| Blob blobRetrieved = rs.getBlob(1); |
| |
| // Verify test data. |
| assertEquals(insertBlob, blobRetrieved); |
| } |
| |
| //------------------------------------------------- |
| //Test the methods used to test poolable statements |
| |
| /** |
| * |
| * Tests the PreparedStatement interface method setPoolable |
| * |
| * @throws SQLException |
| */ |
| |
| public void testSetPoolable() throws SQLException { |
| // Set the poolable statement hint to false |
| ps.setPoolable(false); |
| assertFalse("Expected a non-poolable statement", ps.isPoolable()); |
| // Set the poolable statement hint to true |
| ps.setPoolable(true); |
| assertTrue("Expected a non-poolable statement", ps.isPoolable()); |
| } |
| |
| /** |
| * |
| * Tests the PreparedStatement interface method setPoolable on a closed |
| * PreparedStatement |
| * |
| * @throws SQLException |
| */ |
| public void testSetPoolableOnClosed() throws SQLException { |
| try { |
| ps.close(); |
| // Set the poolable statement hint to false |
| ps.setPoolable(false); |
| fail("Expected an exception on closed statement"); |
| } catch(SQLException sqle) { |
| // Check which SQLException state we've got and if it is |
| // expected, do not print a stackTrace |
| // Embedded uses XJ012, client uses XCL31. |
| if (sqle.getSQLState().equals("XJ012") || |
| sqle.getSQLState().equals("XCL31")) { |
| // All is good and is expected |
| } else { |
| fail("Unexpected SQLException " + sqle); |
| } |
| } |
| } |
| |
| /** |
| * |
| * Tests the PreparedStatement interface method isPoolable |
| * |
| * @throws SQLException |
| * |
| */ |
| public void testIsPoolableDefault() throws SQLException { |
| // By default a prepared statement is poolable |
| assertTrue("Expected a poolable statement", ps.isPoolable()); |
| } |
| |
| /** |
| * Tests that the {@code isPoolable}-hint works by exploiting the fact that |
| * the client cannot prepare a statement referring to a deleted table |
| * (unless the statement is already in the statement cache). |
| * |
| * @throws SQLException if something goes wrong... |
| */ |
| public void cpTestIsPoolableHintFalse() |
| throws SQLException { |
| getConnection().setAutoCommit(false); |
| // Create a table, insert a row, then create a statement selecting it. |
| Statement stmt = createStatement(); |
| stmt.executeUpdate("create table testispoolablehint (id int)"); |
| stmt.executeUpdate("insert into testispoolablehint values 1"); |
| PreparedStatement ps = prepareStatement( |
| "select * from testispoolablehint"); |
| ps.setPoolable(false); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "1"); |
| // Close statement, which should be discarded. |
| ps.close(); |
| // Now delete the table. |
| stmt.executeUpdate("drop table testispoolablehint"); |
| stmt.close(); |
| // Since there is no cached statement, we'll get exception here. |
| try { |
| ps = prepareStatement("select * from testispoolablehint"); |
| fail("Prepared statement not valid, referring non-existing table"); |
| } catch (SQLException sqle) { |
| assertSQLState("42X05", sqle); |
| } |
| } |
| |
| /** |
| * Tests that the {@code isPoolable}-hint works by exploiting the fact that |
| * the client can prepare a statement referring to a deleted table if JDBC |
| * statement caching is enabled and the statement is already in the cache. |
| * |
| * @throws SQLException if something goes wrong... |
| */ |
| public void cpTestIsPoolableHintTrue() |
| throws SQLException { |
| getConnection().setAutoCommit(false); |
| // Create a table, insert a row, then create a statement selecting it. |
| Statement stmt = createStatement(); |
| stmt.executeUpdate("create table testispoolablehint (id int)"); |
| stmt.executeUpdate("insert into testispoolablehint values 1"); |
| PreparedStatement ps = prepareStatement( |
| "select * from testispoolablehint"); |
| ps.setPoolable(true); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), "1"); |
| // Put the statement into the cache. |
| ps.close(); |
| // Now delete the table and fetch the cached prepared statement. |
| stmt.executeUpdate("drop table testispoolablehint"); |
| stmt.close(); |
| ps = prepareStatement("select * from testispoolablehint"); |
| // If we get this far, there is a big change we have fetched an |
| // invalid statement from the cache, but we won't get the exception |
| // until we try to execute it. |
| try { |
| ps.executeQuery(); |
| fail("Prepared statement not valid, referring non-existing table"); |
| } catch (SQLException sqle) { |
| assertSQLState("42X05", sqle); |
| } |
| } |
| |
| /** |
| * |
| * Tests the PreparedStatement interface method isPoolable on closed |
| * PreparedStatement |
| * |
| * @throws SQLException |
| * |
| */ |
| public void testIsPoolableOnClosed() throws SQLException { |
| try { |
| ps.close(); |
| boolean p = ps.isPoolable(); |
| fail("Should throw exception on closed statement"); |
| } catch(SQLException sqle) { |
| // Check which SQLException state we've got and if it is |
| // expected, do not print a stackTrace |
| // Embedded uses XJ012, client uses XCL31. |
| if (sqle.getSQLState().equals("XJ012") || |
| sqle.getSQLState().equals("XCL31")) { |
| // All is good and is expected |
| } else { |
| fail("Unexpected SQLException " + sqle); |
| } |
| } |
| } |
| |
| /** |
| * |
| * Tests the PreparedStatement interface method setCharacterStream |
| * |
| * @throws SQLException |
| * |
| */ |
| public void testSetCharacterStream() throws Exception { |
| String str = "Test data for the Clob object"; |
| StringReader is = new StringReader("Test data for the Clob object"); |
| |
| is.reset(); |
| |
| //initially insert the data |
| psInsertClob.setInt(1, key); |
| psInsertClob.setCharacterStream(2, is, str.length()); |
| psInsertClob.executeUpdate(); |
| |
| //Now query to retrieve the Clob |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| rs.next(); |
| Clob clobRetrieved = rs.getClob(1); |
| |
| String str_out = clobRetrieved.getSubString(1L,(int)clobRetrieved.length()); |
| |
| assertEquals("Error in inserting data into the Clob object",str,str_out); |
| psInsertClob.close(); |
| |
| //Since auto-commit is true in this test |
| //this will invalidate the clob object |
| //Hence closing the ResultSet after |
| //accessing the Clob object. |
| //follows the same pattern as testSetBinaryStream(). |
| rs.close(); |
| } |
| |
| public void testSetCharacterStreamLengthless() |
| throws IOException, SQLException { |
| // Insert test data. |
| String testString = "Test string for setCharacterStream\u1A00"; |
| Reader reader = new StringReader(testString); |
| psInsertClob.setInt(1, key); |
| psInsertClob.setCharacterStream(2, reader); |
| psInsertClob.execute(); |
| reader.close(); |
| |
| // Read back test data from database. |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| assertTrue("No results retrieved", rs.next()); |
| Clob clobRetrieved = rs.getClob(1); |
| |
| // Verify test data. |
| assertEquals("Mismatch test data in/out", testString, |
| clobRetrieved.getSubString(1, testString.length())); |
| } |
| |
| /** |
| * |
| * Tests the PreparedStatement interface method setAsciiStream |
| * |
| * @throws SQLException |
| * |
| */ |
| |
| public void testSetAsciiStream() throws Exception { |
| //insert default values into the table |
| |
| byte [] bytes1 = new byte[10]; |
| |
| InputStream is = new java.io.ByteArrayInputStream(BYTES); |
| |
| is.reset(); |
| |
| //initially insert the data |
| psInsertClob.setInt(1, key); |
| psInsertClob.setAsciiStream(2, is, BYTES.length); |
| psInsertClob.executeUpdate(); |
| |
| //Now query to retrieve the Clob |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| rs.next(); |
| Clob ClobRetrieved = rs.getClob(1); |
| |
| try { |
| InputStream is_ret = ClobRetrieved.getAsciiStream(); |
| is_ret.read(bytes1); |
| } catch(IOException ioe) { |
| fail("IOException while reading the Clob from the database"); |
| } |
| for(int i=0;i<BYTES.length;i++) { |
| assertEquals("Error in inserting data into the Clob",BYTES[i],bytes1[i]); |
| } |
| psInsertClob.close(); |
| |
| //Since auto-commit is true in this test |
| //this will invalidate the clob object |
| //Hence closing the ResultSet after |
| //accessing the Clob object. |
| //follows the same pattern as testSetBinaryStream(). |
| rs.close(); |
| } |
| |
| public void testSetAsciiStreamLengthless() |
| throws IOException, SQLException { |
| // Insert test data. |
| InputStream is = new ByteArrayInputStream(BYTES); |
| psInsertClob.setInt(1, key); |
| psInsertClob.setAsciiStream(2, is); |
| psInsertClob.execute(); |
| is.close(); |
| |
| // Read back test data from database. |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| assertTrue("No results retrieved", rs.next()); |
| Clob clobRetrieved = rs.getClob(1); |
| |
| // Verify read back data. |
| byte[] dbBytes = new byte[10]; |
| InputStream isRetrieved = clobRetrieved.getAsciiStream(); |
| assertEquals("Unexpected number of bytes read", BYTES.length, |
| isRetrieved.read(dbBytes)); |
| assertEquals("Stream should be exhausted", -1, isRetrieved.read()); |
| for (int i=0; i < BYTES.length; i++) { |
| assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]); |
| } |
| |
| // Cleanup |
| isRetrieved.close(); |
| psInsertClob.close(); |
| } |
| |
| /** |
| * |
| * Tests the PreparedStatement interface method setBinaryStream |
| * |
| * @throws SQLException |
| * |
| */ |
| |
| public void testSetBinaryStream() throws Exception { |
| //insert default values into the table |
| |
| byte [] bytes1 = new byte[10]; |
| |
| InputStream is = new java.io.ByteArrayInputStream(BYTES); |
| |
| is.reset(); |
| |
| //initially insert the data |
| psInsertBlob.setInt(1, key); |
| psInsertBlob.setBinaryStream(2, is, BYTES.length); |
| psInsertBlob.executeUpdate(); |
| |
| // Now query to retrieve the Blob |
| psFetchBlob.setInt(1, key); |
| ResultSet rs = psFetchBlob.executeQuery(); |
| rs.next(); |
| Blob blobRetrieved = rs.getBlob(1); |
| |
| try { |
| InputStream is_ret = blobRetrieved.getBinaryStream(); |
| is_ret.read(bytes1); |
| } catch(IOException ioe) { |
| fail("IOException while reading the Clob from the database"); |
| } |
| rs.close(); // Because of autocommit, this will invalidate blobRetrieved |
| |
| for(int i=0;i<BYTES.length;i++) { |
| assertEquals("Error in inserting data into the Blob",BYTES[i],bytes1[i]); |
| } |
| psInsertBlob.close(); |
| } |
| |
| public void testSetBinaryStreamLengthless() |
| throws IOException, SQLException { |
| // Insert test data. |
| InputStream is = new ByteArrayInputStream(BYTES); |
| psInsertBlob.setInt(1, key); |
| psInsertBlob.setBinaryStream(2, is); |
| psInsertBlob.execute(); |
| is.close(); |
| |
| // Read back test data from database. |
| psFetchBlob.setInt(1, key); |
| ResultSet rs = psFetchBlob.executeQuery(); |
| assertTrue("No results retrieved", rs.next()); |
| Blob blobRetrieved = rs.getBlob(1); |
| |
| // Verify read back data. |
| byte[] dbBytes = new byte[10]; |
| InputStream isRetrieved = blobRetrieved.getBinaryStream(); |
| assertEquals("Unexpected number of bytes read", BYTES.length, |
| isRetrieved.read(dbBytes)); |
| assertEquals("Stream should be exhausted", -1, isRetrieved.read()); |
| for (int i=0; i < BYTES.length; i++) { |
| assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]); |
| } |
| |
| // Cleanup |
| isRetrieved.close(); |
| psInsertBlob.close(); |
| } |
| |
| public void testSetBinaryStreamLengthLess1KOnBlob() |
| throws IOException, SQLException { |
| int length = 1*1024; |
| setBinaryStreamOnBlob(key, length, -1, 0, true); |
| psFetchBlob.setInt(1, key); |
| ResultSet rs = psFetchBlob.executeQuery(); |
| assertTrue("Empty resultset", rs.next()); |
| assertEquals(new LoopingAlphabetStream(length), |
| rs.getBinaryStream(1)); |
| assertFalse("Resultset should have been exhausted", rs.next()); |
| rs.close(); |
| } |
| |
| public void testSetBinaryStreamLengthLess32KOnBlob() |
| throws IOException, SQLException { |
| int length = 32*1024; |
| setBinaryStreamOnBlob(key, length, -1, 0, true); |
| psFetchBlob.setInt(1, key); |
| ResultSet rs = psFetchBlob.executeQuery(); |
| assertTrue("Empty resultset", rs.next()); |
| assertEquals(new LoopingAlphabetStream(length), |
| rs.getBinaryStream(1)); |
| assertFalse("Resultset should have been exhausted", rs.next()); |
| rs.close(); |
| } |
| |
| public void testSetBinaryStreamLengthLess65KOnBlob() |
| throws IOException, SQLException { |
| int length = 65*1024; |
| setBinaryStreamOnBlob(key, length, -1, 0, true); |
| psFetchBlob.setInt(1, key); |
| ResultSet rs = psFetchBlob.executeQuery(); |
| assertTrue("Empty resultset", rs.next()); |
| LoopingAlphabetStream s1 = new LoopingAlphabetStream(length); |
| assertEquals(new LoopingAlphabetStream(length), |
| rs.getBinaryStream(1)); |
| assertFalse("Resultset should have been exhausted", rs.next()); |
| rs.close(); |
| } |
| |
| public void testSetBinaryStreamLengthLessOnBlobTooLong() { |
| int length = 1*1024*1024+512; |
| try { |
| setBinaryStreamOnBlob(key, length, -1, 0, true); |
| } catch (SQLException sqle) { |
| if (usingEmbedded() || |
| usingDerbyNetClient() ) { |
| assertSQLState("XSDA4", sqle); |
| } else { |
| assertSQLState("22001", sqle); |
| } |
| } |
| } |
| |
| public void testExceptionPathOnePage_bs() |
| throws SQLException { |
| int length = 11; |
| try { |
| setBinaryStreamOnBlob(key, length -1, length, 0, false); |
| fail("Inserted a BLOB with fewer bytes than specified"); |
| } catch (SQLException sqle) { |
| if (usingEmbedded()) { |
| assertSQLState("XSDA4", sqle); |
| } else { |
| assertSQLState("XN017", sqle); |
| } |
| } |
| } |
| |
| public void testExceptionPathMultiplePages_bs() |
| throws SQLException { |
| int length = 1*1024*1024; |
| try { |
| setBinaryStreamOnBlob(key, length -1, length, 0, false); |
| fail("Inserted a BLOB with fewer bytes than specified"); |
| } catch (SQLException sqle) { |
| if (usingEmbedded()) { |
| assertSQLState("XSDA4", sqle); |
| } else { |
| assertSQLState("XN017", sqle); |
| } |
| } |
| } |
| |
| public void testBlobExceptionDoesNotRollbackOtherStatements() |
| throws IOException, SQLException { |
| getConnection().setAutoCommit(false); |
| int[] keys = {key, requestKey(), requestKey()}; |
| for (int i=0; i < keys.length; i++) { |
| psInsertBlob.setInt(1, keys[i]); |
| psInsertBlob.setNull(2, Types.BLOB); |
| assertEquals(1, psInsertBlob.executeUpdate()); |
| } |
| // Now insert a BLOB that fails because the stream is too short. |
| int failedKey = requestKey(); |
| int length = 1*1024*1024; |
| try { |
| setBinaryStreamOnBlob(failedKey, length -1, length, 0, false); |
| fail("Inserted a BLOB with less data than specified"); |
| } catch (SQLException sqle) { |
| if (usingEmbedded()) { |
| assertSQLState("XSDA4", sqle); |
| } else { |
| assertSQLState("XN017", sqle); |
| } |
| } |
| // Now make sure the previous statements are there, and that the last |
| // BLOB is not. |
| ResultSet rs; |
| for (int i=0; i < keys.length; i++) { |
| psFetchBlob.setInt(1, keys[i]); |
| rs = psFetchBlob.executeQuery(); |
| assertTrue(rs.next()); |
| assertFalse(rs.next()); |
| rs.close(); |
| } |
| psFetchBlob.setInt(1, failedKey); |
| rs = psFetchBlob.executeQuery(); |
| assertFalse(rs.next()); |
| rs.close(); |
| rollback(); |
| // Make sure all data is gone after the rollback. |
| for (int i=0; i < keys.length; i++) { |
| psFetchBlob.setInt(1, keys[i]); |
| rs = psFetchBlob.executeQuery(); |
| assertFalse(rs.next()); |
| rs.close(); |
| } |
| // Make sure the failed insert has not "reappeared" somehow... |
| psFetchBlob.setInt(1, failedKey); |
| rs = psFetchBlob.executeQuery(); |
| assertFalse(rs.next()); |
| |
| } |
| |
| public void testSetAsciiStreamLengthLess1KOnClob() |
| throws IOException, SQLException { |
| int length = 1*1024; |
| setAsciiStream(psInsertClob, key, length, -1, 0, true); |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| assertTrue("Empty resultset", rs.next()); |
| assertEquals(new LoopingAlphabetStream(length), |
| rs.getAsciiStream(1)); |
| assertFalse("Resultset should have been exhausted", rs.next()); |
| rs.close(); |
| } |
| |
| public void testSetAsciiStreamLengthLess32KOnClob() |
| throws IOException, SQLException { |
| int length = 32*1024; |
| setAsciiStream(psInsertClob, key, length, -1, 0, true); |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| assertTrue("Empty resultset", rs.next()); |
| assertEquals(new LoopingAlphabetStream(length), |
| rs.getAsciiStream(1)); |
| assertFalse("Resultset should have been exhausted", rs.next()); |
| rs.close(); |
| } |
| |
| public void testSetAsciiStreamLengthLess65KOnClob() |
| throws IOException, SQLException { |
| int length = 65*1024; |
| setAsciiStream(psInsertClob, key, length, -1, 0, true); |
| psFetchClob.setInt(1, key); |
| ResultSet rs = psFetchClob.executeQuery(); |
| assertTrue("Empty resultset", rs.next()); |
| assertEquals(new LoopingAlphabetStream(length), |
| rs.getAsciiStream(1)); |
| assertFalse("Resultset should have been exhausted", rs.next()); |
| rs.close(); |
| } |
| |
| public void testSetAsciiStreamLengthLessOnClobTooLong() { |
| int length = 1*1024*1024+512; |
| try { |
| setAsciiStream(psInsertClob, key, length, -1, 0, true); |
| } catch (SQLException sqle) { |
| if (usingEmbedded() || |
| usingDerbyNetClient() ){ |
| assertSQLState("XSDA4", sqle); |
| |
| } else { |
| assertSQLState("22001", sqle); |
| |
| } |
| } |
| } |
| |
| public void testSetAsciiStreamLengthLessOnClobTooLongTruncate() |
| throws SQLException { |
| int trailingBlanks = 512; |
| int length = 1*1024*1024 + trailingBlanks; |
| setAsciiStream(psInsertClob, key, length, -1, trailingBlanks, true); |
| } |
| |
| public void testSetAsciiStreamLengthlessOnLongVarCharTooLong() { |
| int length = 32700+512; |
| try { |
| setAsciiStream(psInsertLongVarchar, key, length, -1, 0, true); |
| fail("Inserted a LONG VARCHAR that is too long"); |
| } catch (SQLException sqle) { |
| if (usingEmbedded()){ |
| assertInternalDerbyIOExceptionState("XCL30", "22001", sqle); |
| |
| } else if ( usingDerbyNetClient() ) { |
| assertSQLState("XCL30", sqle); |
| |
| } else { |
| assertSQLState("22001", sqle); |
| |
| } |
| } |
| } |
| |
| public void testSetAsciiStreamLengthlessOnLongVarCharDontTruncate() { |
| int trailingBlanks = 2000; |
| int length = 32000 + trailingBlanks; |
| try { |
| setAsciiStream(psInsertLongVarchar, key, length, -1, |
| trailingBlanks, true); |
| fail("Truncation is not allowed for LONG VARCHAR"); |
| } catch (SQLException sqle) { |
| if (usingEmbedded()){ |
| assertInternalDerbyIOExceptionState("XCL30", "22001", sqle); |
| |
| } else if( usingDerbyNetClient() ) { |
| assertSQLState("XCL30", sqle); |
| |
| } else { |
| assertSQLState("22001", sqle); |
| |
| } |
| } |
| } |
| |
| /** |
| * Test the large update methods added by JDBC 4.2. |
| */ |
| public void testLargeUpdate_jdbc4_2() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| largeUpdate_jdbc4_2( conn ); |
| } |
| |
| public static void largeUpdate_jdbc4_2( Connection conn ) |
| throws Exception |
| { |
| // |
| // This test makes use of a debug entry point which is a NOP |
| // in an insane production build. |
| // |
| if (!SanityManager.DEBUG) { return; } |
| |
| println( "Running large update test for JDBC 4.2" ); |
| |
| conn.prepareStatement |
| ( |
| "create procedure setRowCountBase( newBase bigint )\n" + |
| "language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.jdbc4.StatementTest.setRowCountBase'\n" |
| ).execute(); |
| conn.prepareStatement |
| ( |
| "create table bigintTable( col1 int generated always as identity, col2 bigint )" |
| ).execute(); |
| |
| Statement stmt = conn.createStatement(); |
| PreparedStatementWrapper psw = new PreparedStatementWrapper |
| ( conn.prepareStatement( "insert into bigintTable( col2 ) values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 )" ) ); |
| |
| largeUpdateTest( stmt, psw, ((long) Integer.MAX_VALUE) + 1L ); |
| |
| StatementTest.setRowCountBase( stmt, false, 0L ); |
| } |
| private static void largeUpdateTest |
| ( Statement stmt, PreparedStatementWrapper psw, long rowCountBase ) |
| throws Exception |
| { |
| StatementTest.setRowCountBase( stmt, false, rowCountBase ); |
| |
| assertEquals( rowCountBase + 5L, psw.executeLargeUpdate() ); |
| } |
| |
| /************************************************************************ |
| * A U X I L I A R Y M E T H O D S * |
| ************************************************************************/ |
| |
| /** |
| * Insert data into a Blob column with setBinaryStream. |
| * |
| * @param id unique id for inserted row |
| * @param actualLength the actual length of the stream |
| * @param specifiedLength the specified length of the stream |
| * @param trailingBlanks number of characters at the end that is blank |
| * @param lengthLess whether to use the length less overloads or not |
| */ |
| private void setBinaryStreamOnBlob(int id, |
| int actualLength, |
| int specifiedLength, |
| int trailingBlanks, |
| boolean lengthLess) |
| throws SQLException { |
| psInsertBlob.setInt(1, id); |
| if (lengthLess) { |
| psInsertBlob.setBinaryStream(2, new LoopingAlphabetStream( |
| actualLength, |
| trailingBlanks)); |
| } else { |
| psInsertBlob.setBinaryStream(2, |
| new LoopingAlphabetStream( |
| actualLength, |
| trailingBlanks), |
| specifiedLength); |
| } |
| assertEquals("Insert with setBinaryStream failed", |
| 1, psInsertBlob.executeUpdate()); |
| } |
| |
| /** |
| * Insert data into a column with setAsciiStream. |
| * The prepared statement passed must have two positional parameters; |
| * one int and one more. Depending on the last parameter, the execute |
| * might succeed or it might fail. This is intended behavior, and should |
| * be handled by the caller. For instance, calling this method on an |
| * INT-column would fail, calling it on a CLOB-column would succeed. |
| * |
| * @param id unique id for inserted row |
| * @param actualLength the actual length of the stream |
| * @param specifiedLength the specified length of the stream |
| * @param trailingBlanks number of characters at the end that is blank |
| * @param lengthLess whether to use the length less overloads or not |
| */ |
| private void setAsciiStream(PreparedStatement ps, |
| int id, |
| int actualLength, |
| int specifiedLength, |
| int trailingBlanks, |
| boolean lengthLess) |
| throws SQLException { |
| ps.setInt(1, id); |
| if (lengthLess) { |
| ps.setAsciiStream(2, |
| new LoopingAlphabetStream( |
| actualLength, |
| trailingBlanks)); |
| } else { |
| ps.setAsciiStream(2, |
| new LoopingAlphabetStream( |
| actualLength, |
| trailingBlanks), |
| specifiedLength); |
| } |
| assertEquals("Insert with setAsciiStream failed", |
| 1, ps.executeUpdate()); |
| } |
| |
| /** |
| * Get next key to id inserted data with. |
| */ |
| private static int requestKey() { |
| return globalKey++; |
| } |
| |
| /** |
| * This methods is not to be used, but sometimes you have to! |
| * |
| * @param preSQLState the expected outer SQL state |
| * @param expectedInternal the expected internal SQL state |
| * @param sqle the outer SQLException |
| */ |
| private void assertInternalDerbyIOExceptionState( |
| String preSQLState, |
| String expectedInternal, |
| SQLException sqle) { |
| assertSQLState("Outer/public SQL state incorrect", |
| preSQLState, sqle); |
| // We need to dig a little with the current way exceptions are |
| // being reported. We can use getCause because we always run with |
| // Java SE 6 or later. |
| Throwable cause = getLastSQLException(sqle).getCause(); |
| assertEquals("org.apache.derby.iapi.error.StandardException", |
| cause.getClass().getName()); |
| cause = cause.getCause(); |
| assertTrue("Exception not a DerbyIOException", |
| cause instanceof DerbyIOException); |
| DerbyIOException dioe = (DerbyIOException)cause; |
| assertEquals("Incorrect internal SQL state", expectedInternal, |
| dioe.getSQLState()); |
| } |
| |
| //////////////////////////////////////////////////////////////////////// |
| // |
| // NESTED JDBC 4.2 WRAPPER AROUND A PreparedStatement |
| // |
| //////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * <p> |
| * This wrapper is used to expose JDBC 4.2 methods which can run on |
| * VM rev levels lower than Java 8. |
| * </p> |
| */ |
| public static class PreparedStatementWrapper extends StatementTest.StatementWrapper |
| { |
| private PreparedStatement _wrappedPreparedStatement; |
| |
| public PreparedStatementWrapper( PreparedStatement wrappedPreparedStatement ) |
| { |
| super( wrappedPreparedStatement ); |
| } |
| |
| PreparedStatement getWrappedPreparedStatement() { return (PreparedStatement) getWrappedStatement(); } |
| |
| public long executeLargeUpdate() throws SQLException |
| { |
| return ((Long) invoke |
| ( |
| "executeLargeUpdate", |
| new Class[] {}, |
| new Object[] {} |
| )).longValue(); |
| } |
| } |
| |
| } |