0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc4.PreparedStatementTest
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to you under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.jdbc4;
0023:
0024: import junit.framework.*;
0025:
0026: import org.apache.derbyTesting.junit.BaseJDBCTestCase;
0027: import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
0028: import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream;
0029:
0030: import java.io.*;
0031: import java.sql.*;
0032: import javax.sql.*;
0033:
0034: import org.apache.derby.iapi.services.io.DerbyIOException;
0035: import org.apache.derby.impl.jdbc.EmbedSQLException;
0036:
0037: /**
0038: * This class is used to test JDBC4 specific methods in the PreparedStatement(s)
0039: * object.
0040: *
0041: * A number of methods and variables are in place to aid the writing of tests:
0042: * <ul><li>setBinaryStreamOnBlob
0043: * <li>setAsciiStream
0044: * <li>key - an id. One is generated each time setUp is run.
0045: * <li>reqeustKey() - generate a new unique id.
0046: * <li>psInsertX - prepared statements for insert.
0047: * <li>psFetchX - prepared statements for fetching values.
0048: * </ul>
0049: *
0050: * For table creation, see the <code>suite</code>-method.
0051: */
0052: public class PreparedStatementTest extends BaseJDBCTestCase {
0053:
0054: private static final String BLOBTBL = "BlobTestTable";
0055: private static final String CLOBTBL = "ClobTestTable";
0056: private static final String LONGVARCHAR = "LongVarcharTestTable";
0057:
0058: /** Key used to id data inserted into the database. */
0059: private static int globalKey = 1;
0060:
0061: /** Byte array passed in to the database. **/
0062: private static final byte[] BYTES = { 0x65, 0x66, 0x67, 0x68, 0x69,
0063: 0x69, 0x68, 0x67, 0x66, 0x65 };
0064:
0065: // Default connection and prepared statements that are used by the tests.
0066: /**
0067: * Default key to use for insertions.
0068: * Is unique for each fixture. More keys can be fetched by calling
0069: * <link>requestKey</link>.
0070: */
0071: private int key;
0072: /** Default connection object. */
0073: /** PreparedStatement object with no positional arguments. */
0074: private PreparedStatement ps = null;
0075: /** PreparedStatement to fetch BLOB with specified id. */
0076: private PreparedStatement psFetchBlob = null;
0077: /** PreparedStatement to insert a BLOB with specified id. */
0078: private PreparedStatement psInsertBlob = null;
0079: /** PreparedStatement to fetch CLOB with specified id. */
0080: private PreparedStatement psFetchClob = null;
0081: /** PreparedStatement to insert a CLOB with specified id. */
0082: private PreparedStatement psInsertClob = null;
0083: /** PreparedStatement to insert a LONG VARCHAR with specified id. */
0084: private PreparedStatement psInsertLongVarchar = null;
0085: //Statement object
0086: private Statement s = null;
0087:
0088: /**
0089: * Create a test with the given name.
0090: *
0091: * @param name name of the test.
0092: */
0093: public PreparedStatementTest(String name) {
0094: super (name);
0095: }
0096:
0097: /**
0098: *
0099: * Obtain a "regular" connection and PreparedStatement that the tests
0100: * can use.
0101: *
0102: * @throws SQLException
0103: */
0104: public void setUp() throws SQLException {
0105: key = requestKey();
0106: //create the statement object
0107: s = createStatement();
0108: //Create the PreparedStatement that will then be used as the basis
0109: //throughout this test henceforth
0110: //This prepared statement will however NOT be used for testing
0111: //setClob and setBlob
0112: ps = prepareStatement("select count(*) from sys.systables");
0113:
0114: // Prepare misc statements.
0115: psFetchBlob = prepareStatement("SELECT dBlob FROM " + BLOBTBL
0116: + " WHERE sno = ?");
0117: psInsertBlob = prepareStatement("INSERT INTO " + BLOBTBL
0118: + " VALUES (?, ?)");
0119: psFetchClob = prepareStatement("SELECT dClob FROM " + CLOBTBL
0120: + " WHERE sno = ?");
0121: psInsertClob = prepareStatement("INSERT INTO " + CLOBTBL
0122: + " VALUES (?, ?)");
0123: psInsertLongVarchar = prepareStatement("INSERT INTO "
0124: + LONGVARCHAR + " VALUES (?, ?)");
0125: }
0126:
0127: /**
0128: *
0129: * Release the resources that are used in this test
0130: *
0131: * @throws SQLException
0132: *
0133: */
0134: public void tearDown() throws Exception {
0135:
0136: psFetchBlob.close();
0137: psFetchClob.close();
0138: psInsertBlob.close();
0139: psInsertClob.close();
0140: psInsertLongVarchar.close();
0141:
0142: super .tearDown();
0143: }
0144:
0145: public static Test suite() {
0146: TestSuite suite = new TestSuite();
0147: suite.addTestSuite(PreparedStatementTest.class);
0148: suite.addTest(SetObjectUnsupportedTest.suite(false));
0149: return new BaseJDBCTestSetup(suite) {
0150: public void setUp() throws java.lang.Exception {
0151: try {
0152: create();
0153: } catch (SQLException sqle) {
0154: if (sqle.getSQLState().equals("X0Y32")) {
0155: drop();
0156: create();
0157: } else {
0158: throw sqle;
0159: }
0160: }
0161: }
0162:
0163: public void tearDown() throws java.lang.Exception {
0164: drop();
0165: super .tearDown();
0166: }
0167:
0168: private void create() throws SQLException {
0169: Statement stmt = getConnection().createStatement();
0170: stmt.execute("create table " + BLOBTBL
0171: + " (sno int, dBlob BLOB(1M))");
0172: stmt.execute("create table " + CLOBTBL
0173: + " (sno int, dClob CLOB(1M))");
0174: stmt.execute("create table " + LONGVARCHAR
0175: + " (sno int, dLongVarchar LONG VARCHAR)");
0176: stmt.close();
0177: }
0178:
0179: private void drop() throws SQLException {
0180: Statement stmt = getConnection().createStatement();
0181: stmt.execute("drop table " + BLOBTBL);
0182: stmt.execute("drop table " + CLOBTBL);
0183: stmt.execute("drop table " + LONGVARCHAR);
0184: stmt.close();
0185: }
0186: };
0187: }
0188:
0189: //--------------------------------------------------------------------------
0190: //BEGIN THE TEST OF THE METHODS THAT THROW AN UNIMPLEMENTED EXCEPTION IN
0191: //THIS CLASS
0192:
0193: /**
0194: * Tests the setRowId method of the PreparedStatement interface
0195: *
0196: * @throws SQLException upon any failure that occurs in the
0197: * call to the method.
0198: */
0199: public void testSetRowId() throws SQLException {
0200: try {
0201: RowId rowid = null;
0202: ps.setRowId(0, rowid);
0203: fail("setRowId should not be implemented");
0204: } catch (SQLFeatureNotSupportedException sqlfne) {
0205: //Do Nothing, This happens as expected
0206: }
0207: }
0208:
0209: /**
0210: * Tests the setNString method of the PreparedStatement interface
0211: *
0212: * @throws SQLException upon any failure that occurs in the
0213: * call to the method.
0214: */
0215: public void testSetNString() throws SQLException {
0216: try {
0217: String str = null;
0218: ps.setNString(0, str);
0219: fail("setNString should not be implemented");
0220: } catch (SQLFeatureNotSupportedException sqlfne) {
0221: //Do Nothing, This happens as expected
0222: }
0223: }
0224:
0225: /**
0226: * Tests the setNCharacterStream method of the PreparedStatement interface
0227: *
0228: * @throws SQLException upon any failure that occurs in the
0229: * call to the method.
0230: */
0231: public void testSetNCharacterStream() throws SQLException {
0232: try {
0233: Reader r = null;
0234: ps.setNCharacterStream(0, r, 0);
0235: fail("setNCharacterStream should not be implemented");
0236: } catch (SQLFeatureNotSupportedException sqlfne) {
0237: //Do Nothing, This happens as expected
0238: }
0239: }
0240:
0241: public void testSetNCharacterStreamLengthlessNotImplemented()
0242: throws SQLException {
0243: try {
0244: ps.setNCharacterStream(1, new StringReader("A string"));
0245: fail("setNCharacterStream(int,Reader) should not be implemented");
0246: } catch (SQLFeatureNotSupportedException sfnse) {
0247: // Do nothing, this is expected behavior.
0248: }
0249: }
0250:
0251: public void testSetNClobLengthlessNotImplemented()
0252: throws SQLException {
0253: try {
0254: ps.setNClob(1, new StringReader("A string"));
0255: fail("setNClob(int,Reader) should not be implemented");
0256: } catch (SQLFeatureNotSupportedException sfnse) {
0257: // Do nothing, this is expected behaviour.
0258: }
0259: }
0260:
0261: /**
0262: * Tests the setNClob method of the PreparedStatement interface
0263: *
0264: * @throws SQLException upon any failure that occurs in the
0265: * call to the method.
0266: */
0267: public void testSetNClob1() throws SQLException {
0268: try {
0269: NClob nclob = null;
0270: ps.setNClob(0, nclob);
0271: fail("setNClob should not be implemented");
0272: } catch (SQLFeatureNotSupportedException sqlfne) {
0273: //Do Nothing, This happens as expected
0274: }
0275: }
0276:
0277: /**
0278: * Tests the setNClob method of the PreparedStatement interface
0279: *
0280: * @throws SQLException upon any failure that occurs in the
0281: * call to the method.
0282: */
0283: public void testSetNClob2() throws SQLException {
0284: try {
0285: Reader reader = null;
0286: ps.setNClob(0, reader, 0);
0287: fail("setNClob should not be implemented");
0288: } catch (SQLFeatureNotSupportedException sqlfne) {
0289: //Do Nothing, This happens as expected
0290: }
0291: }
0292:
0293: /**
0294: * Tests the setSQLXML method of the PreparedStatement interface
0295: *
0296: * @throws SQLException upon any failure that occurs in the
0297: * call to the method.
0298: */
0299: public void testSetSQLXML() throws SQLException {
0300: try {
0301: SQLXML sqlxml = null;
0302: ps.setSQLXML(0, sqlxml);
0303: fail("setNClob should not be implemented");
0304: } catch (SQLFeatureNotSupportedException sqlfne) {
0305: //Do Nothing, This happens as expected
0306: }
0307: }
0308:
0309: //--------------------------------------------------------------------------
0310: //Now test the methods that are implemented in the PreparedStatement
0311: //interface
0312:
0313: public void testIsWrapperForStatement() throws SQLException {
0314: assertTrue(ps.isWrapperFor(Statement.class));
0315: }
0316:
0317: public void testIsWrapperForPreparedStatement() throws SQLException {
0318: assertTrue(ps.isWrapperFor(PreparedStatement.class));
0319: }
0320:
0321: public void testIsNotWrapperForCallableStatement()
0322: throws SQLException {
0323: assertFalse(ps.isWrapperFor(CallableStatement.class));
0324: }
0325:
0326: public void testIsNotWrapperForResultSet() throws SQLException {
0327: assertFalse(ps.isWrapperFor(ResultSet.class));
0328: }
0329:
0330: public void testUnwrapStatement() throws SQLException {
0331: Statement stmt = ps.unwrap(Statement.class);
0332: assertSame("Unwrap returned wrong object.", ps, stmt);
0333: }
0334:
0335: public void testUnwrapPreparedStatement() throws SQLException {
0336: PreparedStatement ps2 = ps.unwrap(PreparedStatement.class);
0337: assertSame("Unwrap returned wrong object.", ps, ps2);
0338: }
0339:
0340: public void testUnwrapCallableStatement() {
0341: try {
0342: CallableStatement cs = ps.unwrap(CallableStatement.class);
0343: fail("Unwrap didn't fail.");
0344: } catch (SQLException e) {
0345: assertSQLState("XJ128", e);
0346: }
0347: }
0348:
0349: public void testUnwrapResultSet() {
0350: try {
0351: ResultSet rs = ps.unwrap(ResultSet.class);
0352: fail("Unwrap didn't fail.");
0353: } catch (SQLException e) {
0354: assertSQLState("XJ128", e);
0355: }
0356: }
0357:
0358: //-----------------------------------------------------------------------
0359: // Begin test for setClob and setBlob
0360:
0361: /*
0362: we need a table in which a Clob or a Blob can be stored. We basically
0363: need to write tests for the setClob and the setBlob methods.
0364: Proper process would be
0365: a) Do a createClob or createBlob
0366: b) Populate data in the LOB
0367: c) Store in Database
0368:
0369: But the createClob and createBlob implementations are not
0370: available on the EmbeddedServer. So instead the workaround adopted
0371: is
0372:
0373: a) store a Clob or Blob in Database.
0374: b) Retrieve it from the database.
0375: c) store it back using setClob or setBlob
0376:
0377: */
0378:
0379: /**
0380: *
0381: * Test the setClob() method
0382: *
0383: * @throws SQLException if a failure occurs during the call to setClob
0384: *
0385: */
0386: public void testSetClob() throws IOException, SQLException {
0387: //insert default values into the table
0388:
0389: String str = "Test data for the Clob object";
0390: StringReader is = new StringReader(
0391: "Test data for the Clob object");
0392: is.reset();
0393:
0394: //initially insert the data
0395: psInsertClob.setInt(1, key);
0396: psInsertClob.setClob(2, is, str.length());
0397: psInsertClob.executeUpdate();
0398:
0399: //Now query to retrieve the Clob
0400: psFetchClob.setInt(1, key);
0401: ResultSet rs = psFetchClob.executeQuery();
0402: rs.next();
0403: Clob clobToBeInserted = rs.getClob(1);
0404: rs.close();
0405:
0406: //Now use the setClob method
0407: int secondKey = requestKey();
0408: psInsertClob.setInt(1, secondKey);
0409: psInsertClob.setClob(2, clobToBeInserted);
0410: psInsertClob.execute();
0411:
0412: psInsertClob.close();
0413:
0414: //Now test to see that the Clob has been stored correctly
0415: psFetchClob.setInt(1, secondKey);
0416: rs = psFetchClob.executeQuery();
0417: rs.next();
0418: Clob clobRetrieved = rs.getClob(1);
0419:
0420: assertEquals(clobToBeInserted, clobRetrieved);
0421: }
0422:
0423: /**
0424: * Insert <code>Clob</code> without specifying length and read it back
0425: * for verification.
0426: *
0427: * Beacuse we don't yet support <code>Connection.createClob</code> in the
0428: * client driver, we must first insert data into the database and read back
0429: * a <code>Clob</code> object. This object is then inserted into the
0430: * database again.
0431: */
0432: public void testSetClobLengthless() throws IOException,
0433: SQLException {
0434: // Insert test data.
0435: String testString = "Test string for setCharacterStream\u1A00";
0436: Reader reader = new StringReader(testString);
0437: psInsertClob.setInt(1, key);
0438: psInsertClob.setCharacterStream(2, reader);
0439: psInsertClob.execute();
0440: reader.close();
0441: // Must fetch Clob from database because we don't support
0442: // Connection.createClob on the client yet.
0443: psFetchClob.setInt(1, key);
0444: ResultSet rs = psFetchClob.executeQuery();
0445: assertTrue("No results retrieved", rs.next());
0446: int secondKey = requestKey();
0447: Clob insertClob = rs.getClob(1);
0448: psInsertClob.setInt(1, secondKey);
0449: psInsertClob.setClob(2, insertClob);
0450: psInsertClob.execute();
0451:
0452: // Read back test data from database.
0453: psFetchClob.setInt(1, secondKey);
0454: rs = psFetchClob.executeQuery();
0455: assertTrue("No results retrieved", rs.next());
0456: Clob clobRetrieved = rs.getClob(1);
0457:
0458: // Verify test data.
0459: assertEquals(insertClob, clobRetrieved);
0460: }
0461:
0462: /**
0463: *
0464: * Test the setBlob() method
0465: *
0466: * @throws SQLException if a failure occurs during the call to setBlob
0467: *
0468: */
0469: public void testSetBlob() throws IOException, SQLException {
0470: //insert default values into the table
0471:
0472: InputStream is = new java.io.ByteArrayInputStream(BYTES);
0473: is.reset();
0474:
0475: //initially insert the data
0476: psInsertBlob.setInt(1, key);
0477: psInsertBlob.setBlob(2, is, BYTES.length);
0478: psInsertBlob.executeUpdate();
0479:
0480: //Now query to retrieve the Blob
0481: psFetchBlob.setInt(1, key);
0482: ResultSet rs = psFetchBlob.executeQuery();
0483: rs.next();
0484: Blob blobToBeInserted = rs.getBlob(1);
0485: rs.close();
0486:
0487: //Now use the setBlob method
0488: int secondKey = requestKey();
0489: psInsertBlob.setInt(1, secondKey);
0490: psInsertBlob.setBlob(2, blobToBeInserted);
0491: psInsertBlob.execute();
0492:
0493: psInsertBlob.close();
0494:
0495: //Now test to see that the Blob has been stored correctly
0496: psFetchBlob.setInt(1, secondKey);
0497: rs = psFetchBlob.executeQuery();
0498: rs.next();
0499: Blob blobRetrieved = rs.getBlob(1);
0500:
0501: assertEquals(blobToBeInserted, blobRetrieved);
0502: }
0503:
0504: /**
0505: * Insert <code>Blob</code> without specifying length and read it back
0506: * for verification.
0507: *
0508: * Beacuse we don't yet support <code>Connection.createBlob</code> in the
0509: * client driver, we must first insert data into the database and read back
0510: * a <code>Blob</code> object. This object is then inserted into the
0511: * database again.
0512: */
0513: public void testSetBlobLengthless() throws IOException,
0514: SQLException {
0515: // Insert test data.
0516: InputStream is = new ByteArrayInputStream(BYTES);
0517: psInsertBlob.setInt(1, key);
0518: psInsertBlob.setBinaryStream(2, is);
0519: psInsertBlob.execute();
0520: is.close();
0521: // Must fetch Blob from database because we don't support
0522: // Connection.createBlob on the client yet.
0523: psFetchBlob.setInt(1, key);
0524: ResultSet rs = psFetchBlob.executeQuery();
0525: assertTrue("No results retrieved", rs.next());
0526: Blob insertBlob = rs.getBlob(1);
0527: int secondKey = requestKey();
0528: psInsertBlob.setInt(1, secondKey);
0529: psInsertBlob.setBlob(2, insertBlob);
0530: psInsertBlob.execute();
0531:
0532: // Read back test data from database.
0533: psFetchBlob.setInt(1, secondKey);
0534: rs = psFetchBlob.executeQuery();
0535: assertTrue("No results retrieved", rs.next());
0536: Blob blobRetrieved = rs.getBlob(1);
0537:
0538: // Verify test data.
0539: assertEquals(insertBlob, blobRetrieved);
0540: }
0541:
0542: //-------------------------------------------------
0543: //Test the methods used to test poolable statements
0544:
0545: /**
0546: *
0547: * Tests the PreparedStatement interface method setPoolable
0548: *
0549: * @throws SQLException
0550: */
0551:
0552: public void testSetPoolable() throws SQLException {
0553: try {
0554: // Set the poolable statement hint to false
0555: ps.setPoolable(false);
0556: if (ps.isPoolable())
0557: fail("Expected a non-poolable statement");
0558: // Set the poolable statement hint to true
0559: ps.setPoolable(true);
0560: if (!ps.isPoolable())
0561: fail("Expected a poolable statement");
0562: } catch (SQLException sqle) {
0563: // Check which SQLException state we've got and if it is
0564: // expected, do not print a stackTrace
0565: // Embedded uses XJ012, client uses XCL31.
0566: if (sqle.getSQLState().equals("XJ012")
0567: || sqle.getSQLState().equals("XCL31")) {
0568: // All is good and is expected
0569: } else {
0570: fail("Unexpected SQLException " + sqle);
0571: }
0572: } catch (Exception e) {
0573: fail("Unexpected exception thrown in method " + e);
0574: }
0575: }
0576:
0577: /**
0578: *
0579: * Tests the PreparedStatement interface method isPoolable
0580: *
0581: * @throws SQLException
0582: *
0583: */
0584:
0585: public void testIsPoolable() throws SQLException {
0586: try {
0587: // By default a prepared statement is poolable
0588: if (!ps.isPoolable())
0589: fail("Expected a poolable statement");
0590: } catch (SQLException sqle) {
0591: // Check which SQLException state we've got and if it is
0592: // expected, do not print a stackTrace
0593: // Embedded uses XJ012, client uses XCL31.
0594: if (sqle.getSQLState().equals("XJ012")
0595: || sqle.getSQLState().equals("XCL31")) {
0596: // All is good and is expected
0597: } else {
0598: fail("Unexpected SQLException " + sqle);
0599: }
0600: } catch (Exception e) {
0601: fail("Unexpected exception thrown in method " + e);
0602: }
0603: }
0604:
0605: /**
0606: *
0607: * Tests the PreparedStatement interface method setCharacterStream
0608: *
0609: * @throws SQLException
0610: *
0611: */
0612: public void testSetCharacterStream() throws Exception {
0613: String str = "Test data for the Clob object";
0614: StringReader is = new StringReader(
0615: "Test data for the Clob object");
0616:
0617: is.reset();
0618:
0619: //initially insert the data
0620: psInsertClob.setInt(1, key);
0621: psInsertClob.setCharacterStream(2, is, str.length());
0622: psInsertClob.executeUpdate();
0623:
0624: //Now query to retrieve the Clob
0625: psFetchClob.setInt(1, key);
0626: ResultSet rs = psFetchClob.executeQuery();
0627: rs.next();
0628: Clob clobRetrieved = rs.getClob(1);
0629: rs.close();
0630:
0631: String str_out = clobRetrieved.getSubString(1L,
0632: (int) clobRetrieved.length());
0633:
0634: assertEquals("Error in inserting data into the Clob object",
0635: str, str_out);
0636: psInsertClob.close();
0637: }
0638:
0639: public void testSetCharacterStreamLengthless() throws IOException,
0640: SQLException {
0641: // Insert test data.
0642: String testString = "Test string for setCharacterStream\u1A00";
0643: Reader reader = new StringReader(testString);
0644: psInsertClob.setInt(1, key);
0645: psInsertClob.setCharacterStream(2, reader);
0646: psInsertClob.execute();
0647: reader.close();
0648:
0649: // Read back test data from database.
0650: psFetchClob.setInt(1, key);
0651: ResultSet rs = psFetchClob.executeQuery();
0652: assertTrue("No results retrieved", rs.next());
0653: Clob clobRetrieved = rs.getClob(1);
0654:
0655: // Verify test data.
0656: assertEquals("Mismatch test data in/out", testString,
0657: clobRetrieved.getSubString(1, testString.length()));
0658: }
0659:
0660: /**
0661: *
0662: * Tests the PreparedStatement interface method setAsciiStream
0663: *
0664: * @throws SQLException
0665: *
0666: */
0667:
0668: public void testSetAsciiStream() throws Exception {
0669: //insert default values into the table
0670:
0671: byte[] bytes1 = new byte[10];
0672:
0673: InputStream is = new java.io.ByteArrayInputStream(BYTES);
0674:
0675: is.reset();
0676:
0677: //initially insert the data
0678: psInsertClob.setInt(1, key);
0679: psInsertClob.setAsciiStream(2, is, BYTES.length);
0680: psInsertClob.executeUpdate();
0681:
0682: //Now query to retrieve the Clob
0683: psFetchClob.setInt(1, key);
0684: ResultSet rs = psFetchClob.executeQuery();
0685: rs.next();
0686: Clob ClobRetrieved = rs.getClob(1);
0687: rs.close();
0688:
0689: try {
0690: InputStream is_ret = ClobRetrieved.getAsciiStream();
0691: is_ret.read(bytes1);
0692: } catch (IOException ioe) {
0693: fail("IOException while reading the Clob from the database");
0694: }
0695: for (int i = 0; i < BYTES.length; i++) {
0696: assertEquals("Error in inserting data into the Clob",
0697: BYTES[i], bytes1[i]);
0698: }
0699: psInsertClob.close();
0700: }
0701:
0702: public void testSetAsciiStreamLengthless() throws IOException,
0703: SQLException {
0704: // Insert test data.
0705: InputStream is = new ByteArrayInputStream(BYTES);
0706: psInsertClob.setInt(1, key);
0707: psInsertClob.setAsciiStream(2, is);
0708: psInsertClob.execute();
0709: is.close();
0710:
0711: // Read back test data from database.
0712: psFetchClob.setInt(1, key);
0713: ResultSet rs = psFetchClob.executeQuery();
0714: assertTrue("No results retrieved", rs.next());
0715: Clob clobRetrieved = rs.getClob(1);
0716:
0717: // Verify read back data.
0718: byte[] dbBytes = new byte[10];
0719: InputStream isRetrieved = clobRetrieved.getAsciiStream();
0720: assertEquals("Unexpected number of bytes read", BYTES.length,
0721: isRetrieved.read(dbBytes));
0722: assertEquals("Stream should be exhausted", -1, isRetrieved
0723: .read());
0724: for (int i = 0; i < BYTES.length; i++) {
0725: assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]);
0726: }
0727:
0728: // Cleanup
0729: isRetrieved.close();
0730: psInsertClob.close();
0731: }
0732:
0733: /**
0734: *
0735: * Tests the PreparedStatement interface method setBinaryStream
0736: *
0737: * @throws SQLException
0738: *
0739: */
0740:
0741: public void testSetBinaryStream() throws Exception {
0742: //insert default values into the table
0743:
0744: byte[] bytes1 = new byte[10];
0745:
0746: InputStream is = new java.io.ByteArrayInputStream(BYTES);
0747:
0748: is.reset();
0749:
0750: //initially insert the data
0751: psInsertBlob.setInt(1, key);
0752: psInsertBlob.setBinaryStream(2, is, BYTES.length);
0753: psInsertBlob.executeUpdate();
0754:
0755: //Now query to retrieve the Clob
0756: psFetchBlob.setInt(1, key);
0757: ResultSet rs = psFetchBlob.executeQuery();
0758: rs.next();
0759: Blob blobRetrieved = rs.getBlob(1);
0760: rs.close();
0761:
0762: try {
0763: InputStream is_ret = blobRetrieved.getBinaryStream();
0764: is_ret.read(bytes1);
0765: } catch (IOException ioe) {
0766: fail("IOException while reading the Clob from the database");
0767: }
0768:
0769: for (int i = 0; i < BYTES.length; i++) {
0770: assertEquals("Error in inserting data into the Blob",
0771: BYTES[i], bytes1[i]);
0772: }
0773: psInsertBlob.close();
0774: }
0775:
0776: public void testSetBinaryStreamLengthless() throws IOException,
0777: SQLException {
0778: // Insert test data.
0779: InputStream is = new ByteArrayInputStream(BYTES);
0780: psInsertBlob.setInt(1, key);
0781: psInsertBlob.setBinaryStream(2, is);
0782: psInsertBlob.execute();
0783: is.close();
0784:
0785: // Read back test data from database.
0786: psFetchBlob.setInt(1, key);
0787: ResultSet rs = psFetchBlob.executeQuery();
0788: assertTrue("No results retrieved", rs.next());
0789: Blob blobRetrieved = rs.getBlob(1);
0790:
0791: // Verify read back data.
0792: byte[] dbBytes = new byte[10];
0793: InputStream isRetrieved = blobRetrieved.getBinaryStream();
0794: assertEquals("Unexpected number of bytes read", BYTES.length,
0795: isRetrieved.read(dbBytes));
0796: assertEquals("Stream should be exhausted", -1, isRetrieved
0797: .read());
0798: for (int i = 0; i < BYTES.length; i++) {
0799: assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]);
0800: }
0801:
0802: // Cleanup
0803: isRetrieved.close();
0804: psInsertBlob.close();
0805: }
0806:
0807: public void testSetBinaryStreamLengthLess1KOnBlob()
0808: throws IOException, SQLException {
0809: int length = 1 * 1024;
0810: setBinaryStreamOnBlob(key, length, -1, 0, true);
0811: psFetchBlob.setInt(1, key);
0812: ResultSet rs = psFetchBlob.executeQuery();
0813: assertTrue("Empty resultset", rs.next());
0814: assertEquals(new LoopingAlphabetStream(length), rs
0815: .getBinaryStream(1));
0816: assertFalse("Resultset should have been exhausted", rs.next());
0817: rs.close();
0818: }
0819:
0820: public void testSetBinaryStreamLengthLess32KOnBlob()
0821: throws IOException, SQLException {
0822: int length = 32 * 1024;
0823: setBinaryStreamOnBlob(key, length, -1, 0, true);
0824: psFetchBlob.setInt(1, key);
0825: ResultSet rs = psFetchBlob.executeQuery();
0826: assertTrue("Empty resultset", rs.next());
0827: assertEquals(new LoopingAlphabetStream(length), rs
0828: .getBinaryStream(1));
0829: assertFalse("Resultset should have been exhausted", rs.next());
0830: rs.close();
0831: }
0832:
0833: public void testSetBinaryStreamLengthLess65KOnBlob()
0834: throws IOException, SQLException {
0835: int length = 65 * 1024;
0836: setBinaryStreamOnBlob(key, length, -1, 0, true);
0837: psFetchBlob.setInt(1, key);
0838: ResultSet rs = psFetchBlob.executeQuery();
0839: assertTrue("Empty resultset", rs.next());
0840: LoopingAlphabetStream s1 = new LoopingAlphabetStream(length);
0841: assertEquals(new LoopingAlphabetStream(length), rs
0842: .getBinaryStream(1));
0843: assertFalse("Resultset should have been exhausted", rs.next());
0844: rs.close();
0845: }
0846:
0847: public void testSetBinaryStreamLengthLessOnBlobTooLong() {
0848: int length = 1 * 1024 * 1024 + 512;
0849: try {
0850: setBinaryStreamOnBlob(key, length, -1, 0, true);
0851: } catch (SQLException sqle) {
0852: if (usingEmbedded()) {
0853: assertSQLState("XSDA4", sqle);
0854: } else {
0855: assertSQLState("22001", sqle);
0856: }
0857: }
0858: }
0859:
0860: public void testExceptionPathOnePage_bs() throws SQLException {
0861: int length = 11;
0862: try {
0863: setBinaryStreamOnBlob(key, length - 1, length, 0, false);
0864: fail("Inserted a BLOB with fewer bytes than specified");
0865: } catch (SQLException sqle) {
0866: if (usingEmbedded()) {
0867: assertSQLState("XSDA4", sqle);
0868: } else {
0869: assertSQLState("XN017", sqle);
0870: }
0871: }
0872: }
0873:
0874: public void testExceptionPathMultiplePages_bs() throws SQLException {
0875: int length = 1 * 1024 * 1024;
0876: try {
0877: setBinaryStreamOnBlob(key, length - 1, length, 0, false);
0878: fail("Inserted a BLOB with fewer bytes than specified");
0879: } catch (SQLException sqle) {
0880: if (usingEmbedded()) {
0881: assertSQLState("XSDA4", sqle);
0882: } else {
0883: assertSQLState("XN017", sqle);
0884: }
0885: }
0886: }
0887:
0888: public void testBlobExceptionDoesNotRollbackOtherStatements()
0889: throws IOException, SQLException {
0890: getConnection().setAutoCommit(false);
0891: int[] keys = { key, requestKey(), requestKey() };
0892: for (int i = 0; i < keys.length; i++) {
0893: psInsertBlob.setInt(1, keys[i]);
0894: psInsertBlob.setNull(2, Types.BLOB);
0895: assertEquals(1, psInsertBlob.executeUpdate());
0896: }
0897: // Now insert a BLOB that fails because the stream is too short.
0898: int failedKey = requestKey();
0899: int length = 1 * 1024 * 1024;
0900: try {
0901: setBinaryStreamOnBlob(failedKey, length - 1, length, 0,
0902: false);
0903: fail("Inserted a BLOB with less data than specified");
0904: } catch (SQLException sqle) {
0905: if (usingEmbedded()) {
0906: assertSQLState("XSDA4", sqle);
0907: } else {
0908: assertSQLState("XN017", sqle);
0909: }
0910: }
0911: // Now make sure the previous statements are there, and that the last
0912: // BLOB is not.
0913: ResultSet rs;
0914: for (int i = 0; i < keys.length; i++) {
0915: psFetchBlob.setInt(1, keys[i]);
0916: rs = psFetchBlob.executeQuery();
0917: assertTrue(rs.next());
0918: assertFalse(rs.next());
0919: rs.close();
0920: }
0921: psFetchBlob.setInt(1, failedKey);
0922: rs = psFetchBlob.executeQuery();
0923: // When using the Derby client driver, the data seems to be padded
0924: // with 0s and inserted... Thus, the select returns a row.
0925: if (!usingEmbedded()) {
0926: assertTrue(rs.next());
0927: InputStream is = rs.getBinaryStream(1);
0928: int lastByte = -1;
0929: int b = 99; // Just a value > 0.
0930: while (b > -1) {
0931: lastByte = b;
0932: b = is.read();
0933: }
0934: assertEquals("Last padded byte is not 0", 0, lastByte);
0935: }
0936: assertFalse(rs.next());
0937: rs.close();
0938: rollback();
0939: // Make sure all data is gone after the rollback.
0940: for (int i = 0; i < keys.length; i++) {
0941: psFetchBlob.setInt(1, keys[i]);
0942: rs = psFetchBlob.executeQuery();
0943: assertFalse(rs.next());
0944: rs.close();
0945: }
0946: // Make sure the failed insert has not "reappeared" somehow...
0947: psFetchBlob.setInt(1, failedKey);
0948: rs = psFetchBlob.executeQuery();
0949: assertFalse(rs.next());
0950:
0951: }
0952:
0953: public void testSetAsciiStreamLengthLess1KOnClob()
0954: throws IOException, SQLException {
0955: int length = 1 * 1024;
0956: setAsciiStream(psInsertClob, key, length, -1, 0, true);
0957: psFetchClob.setInt(1, key);
0958: ResultSet rs = psFetchClob.executeQuery();
0959: assertTrue("Empty resultset", rs.next());
0960: assertEquals(new LoopingAlphabetStream(length), rs
0961: .getAsciiStream(1));
0962: assertFalse("Resultset should have been exhausted", rs.next());
0963: rs.close();
0964: }
0965:
0966: public void testSetAsciiStreamLengthLess32KOnClob()
0967: throws IOException, SQLException {
0968: int length = 32 * 1024;
0969: setAsciiStream(psInsertClob, key, length, -1, 0, true);
0970: psFetchClob.setInt(1, key);
0971: ResultSet rs = psFetchClob.executeQuery();
0972: assertTrue("Empty resultset", rs.next());
0973: assertEquals(new LoopingAlphabetStream(length), rs
0974: .getAsciiStream(1));
0975: assertFalse("Resultset should have been exhausted", rs.next());
0976: rs.close();
0977: }
0978:
0979: public void testSetAsciiStreamLengthLess65KOnClob()
0980: throws IOException, SQLException {
0981: int length = 65 * 1024;
0982: setAsciiStream(psInsertClob, key, length, -1, 0, true);
0983: psFetchClob.setInt(1, key);
0984: ResultSet rs = psFetchClob.executeQuery();
0985: assertTrue("Empty resultset", rs.next());
0986: assertEquals(new LoopingAlphabetStream(length), rs
0987: .getAsciiStream(1));
0988: assertFalse("Resultset should have been exhausted", rs.next());
0989: rs.close();
0990: }
0991:
0992: public void testSetAsciiStreamLengthLessOnClobTooLong() {
0993: int length = 1 * 1024 * 1024 + 512;
0994: try {
0995: setAsciiStream(psInsertClob, key, length, -1, 0, true);
0996: } catch (SQLException sqle) {
0997: if (usingEmbedded()) {
0998: assertSQLState("XSDA4", sqle);
0999: } else {
1000: assertSQLState("22001", sqle);
1001: }
1002: }
1003: }
1004:
1005: public void testSetAsciiStreamLengthLessOnClobTooLongTruncate()
1006: throws SQLException {
1007: int trailingBlanks = 512;
1008: int length = 1 * 1024 * 1024 + trailingBlanks;
1009: setAsciiStream(psInsertClob, key, length, -1, trailingBlanks,
1010: true);
1011: }
1012:
1013: public void testSetAsciiStreamLengthlessOnLongVarCharTooLong() {
1014: int length = 32700 + 512;
1015: try {
1016: setAsciiStream(psInsertLongVarchar, key, length, -1, 0,
1017: true);
1018: fail("Inserted a LONG VARCHAR that is too long");
1019: } catch (SQLException sqle) {
1020: if (usingEmbedded()) {
1021: assertInternalDerbyIOExceptionState("XCL30", "22001",
1022: sqle);
1023: } else {
1024: assertSQLState("22001", sqle);
1025: }
1026: }
1027: }
1028:
1029: public void testSetAsciiStreamLengthlessOnLongVarCharDontTruncate() {
1030: int trailingBlanks = 2000;
1031: int length = 32000 + trailingBlanks;
1032: try {
1033: setAsciiStream(psInsertLongVarchar, key, length, -1,
1034: trailingBlanks, true);
1035: fail("Truncation is not allowed for LONG VARCHAR");
1036: } catch (SQLException sqle) {
1037: if (usingEmbedded()) {
1038: assertInternalDerbyIOExceptionState("XCL30", "22001",
1039: sqle);
1040: } else {
1041: assertSQLState("22001", sqle);
1042: }
1043: }
1044: }
1045:
1046: /************************************************************************
1047: * A U X I L I A R Y M E T H O D S *
1048: ************************************************************************/
1049:
1050: /**
1051: * Insert data into a Blob column with setBinaryStream.
1052: *
1053: * @param id unique id for inserted row
1054: * @param actualLength the actual length of the stream
1055: * @param specifiedLength the specified length of the stream
1056: * @param trailingBlanks number of characters at the end that is blank
1057: * @param lengthLess whether to use the length less overloads or not
1058: */
1059: private void setBinaryStreamOnBlob(int id, int actualLength,
1060: int specifiedLength, int trailingBlanks, boolean lengthLess)
1061: throws SQLException {
1062: psInsertBlob.setInt(1, id);
1063: if (lengthLess) {
1064: psInsertBlob.setBinaryStream(2, new LoopingAlphabetStream(
1065: actualLength, trailingBlanks));
1066: } else {
1067: psInsertBlob.setBinaryStream(2, new LoopingAlphabetStream(
1068: actualLength, trailingBlanks), specifiedLength);
1069: }
1070: assertEquals("Insert with setBinaryStream failed", 1,
1071: psInsertBlob.executeUpdate());
1072: }
1073:
1074: /**
1075: * Insert data into a column with setAsciiStream.
1076: * The prepared statement passed must have two positional parameters;
1077: * one int and one more. Depending on the last parameter, the execute
1078: * might succeed or it might fail. This is intended behavior, and should
1079: * be handled by the caller. For instance, calling this method on an
1080: * INT-column would fail, calling it on a CLOB-column would succeed.
1081: *
1082: * @param id unique id for inserted row
1083: * @param actualLength the actual length of the stream
1084: * @param specifiedLength the specified length of the stream
1085: * @param trailingBlanks number of characters at the end that is blank
1086: * @param lengthLess whether to use the length less overloads or not
1087: */
1088: private void setAsciiStream(PreparedStatement ps, int id,
1089: int actualLength, int specifiedLength, int trailingBlanks,
1090: boolean lengthLess) throws SQLException {
1091: ps.setInt(1, id);
1092: if (lengthLess) {
1093: ps.setAsciiStream(2, new LoopingAlphabetStream(
1094: actualLength, trailingBlanks));
1095: } else {
1096: ps.setAsciiStream(2, new LoopingAlphabetStream(
1097: actualLength, trailingBlanks), specifiedLength);
1098: }
1099: assertEquals("Insert with setAsciiStream failed", 1, ps
1100: .executeUpdate());
1101: }
1102:
1103: /**
1104: * Get next key to id inserted data with.
1105: */
1106: private static int requestKey() {
1107: return globalKey++;
1108: }
1109:
1110: /**
1111: * Return the last chained SQLException.
1112: * If there are no exceptions chained, the original one is returned.
1113: */
1114: private SQLException getLastSQLException(SQLException sqle) {
1115: SQLException last = sqle;
1116: SQLException next = sqle;
1117: while (next != null) {
1118: last = next;
1119: next = last.getNextException();
1120: }
1121: return last;
1122: }
1123:
1124: /**
1125: * This methods is not to be used, but sometimes you have to!
1126: *
1127: * @param preSQLState the expected outer SQL state
1128: * @param expectedInternal the expected internal SQL state
1129: * @param sqle the outer SQLException
1130: */
1131: private void assertInternalDerbyIOExceptionState(
1132: String preSQLState, String expectedInternal,
1133: SQLException sqle) {
1134: assertSQLState("Outer/public SQL state incorrect", preSQLState,
1135: sqle);
1136: // We need to dig a little with the current way exceptions are
1137: // being reported. We can use getCause because we always run with
1138: // Mustang/Java SE 6.
1139: Throwable cause = getLastSQLException(sqle).getCause();
1140: assertTrue("Exception not an EmbedSQLException",
1141: cause instanceof EmbedSQLException);
1142: cause = ((EmbedSQLException) cause).getJavaException();
1143: assertTrue("Exception not a DerbyIOException",
1144: cause instanceof DerbyIOException);
1145: DerbyIOException dioe = (DerbyIOException) cause;
1146: assertEquals("Incorrect internal SQL state", expectedInternal,
1147: dioe.getSQLState());
1148: }
1149: }
|