0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.largedata.LobLimits
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.largedata;
0023:
0024: import java.sql.*;
0025: import java.io.*;
0026:
0027: import org.apache.derby.tools.ij;
0028: import org.apache.derby.tools.JDBCDisplayUtil;
0029:
0030: /**
0031: * This test is part of the "largedata" suite because this test tests data for
0032: * lobs to the limits ( ie blob and clob can be 2G-1 maximum) and so this test
0033: * may take considerable disk space as well as time to run. Hence it is not part
0034: * of the derbyall suite but should ideally be run at some intervals to test if
0035: * no regression has occurred.
0036: */
0037:
0038: public class LobLimits {
0039:
0040: static boolean trace = false;
0041: static final int _2GB = 2 * 1024 * 1024 * 1024 - 1;
0042: static final int _100MB = 100 * 1024 * 1024;
0043: static final int MORE_DATA_THAN_COL_WIDTH = (_100MB) + 1;
0044: static final int NUM_TRAILING_SPACES = 33 * 1024;
0045:
0046: static PreparedStatement insertBlob = null;
0047: static PreparedStatement selectBlob = null;
0048: static PreparedStatement insertClob = null;
0049: static PreparedStatement selectClob = null;
0050: static PreparedStatement deleteBlob = null;
0051: static PreparedStatement deleteClob = null;
0052: static PreparedStatement insertBlob2 = null;
0053: static PreparedStatement selectBlob2 = null;
0054: static PreparedStatement insertClob2 = null;
0055: static PreparedStatement selectClob2 = null;
0056: static PreparedStatement deleteBlob2 = null;
0057: static PreparedStatement deleteClob2 = null;
0058:
0059: static final String DATAFILE = "byteLobLimits.dat";
0060:
0061: static final String CHARDATAFILE = "charLobLimits.txt";
0062:
0063: /**
0064: * setup prepared statements and schema for the tests
0065: * @param conn
0066: * @throws SQLException
0067: */
0068: private void setup(Connection conn) throws SQLException {
0069: System.out.println("-----------------------------------");
0070: System.out.println(" START setup");
0071:
0072: conn.setAutoCommit(true);
0073: // Create a test table.
0074: Statement s = conn.createStatement();
0075: try {
0076: s.execute("DROP TABLE BLOBTBL");
0077: } catch (Exception e) {
0078: }
0079: try {
0080: s.execute("DROP TABLE CLOBTBL");
0081: } catch (Exception e) {
0082: }
0083: try {
0084: s.execute("DROP TABLE BLOBTBL2");
0085: } catch (Exception e) {
0086: }
0087: try {
0088: s.execute("DROP TABLE CLOBTBL2");
0089: } catch (Exception e) {
0090: }
0091:
0092: s.execute("CREATE TABLE BLOBTBL (ID INT NOT NULL PRIMARY KEY, "
0093: + "POS BIGINT, DLEN BIGINT, CONTENT BLOB(2G))");
0094:
0095: insertBlob = conn
0096: .prepareStatement("INSERT INTO BLOBTBL values (?,?,?,?)");
0097:
0098: s.execute("CREATE TABLE CLOBTBL (ID INT NOT NULL PRIMARY KEY,"
0099: + "POS BIGINT, DLEN BIGINT, CONTENT CLOB(2G))");
0100:
0101: insertBlob = conn
0102: .prepareStatement("INSERT INTO BLOBTBL values (?,?,?,?)");
0103: selectBlob = conn
0104: .prepareStatement("SELECT CONTENT,DLEN FROM BLOBTBL WHERE ID = ?");
0105:
0106: insertClob = conn
0107: .prepareStatement("INSERT INTO CLOBTBL values (?,?,?,?)");
0108:
0109: selectClob = conn
0110: .prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL WHERE ID = ?");
0111:
0112: deleteBlob = conn.prepareStatement("DELETE FROM BLOBTBL");
0113: deleteClob = conn.prepareStatement("DELETE from CLOBTBL");
0114:
0115: s
0116: .execute("CREATE TABLE BLOBTBL2 (ID INT NOT NULL PRIMARY KEY, "
0117: + "POS BIGINT, CONTENT BLOB("
0118: + _100MB
0119: + "),DLEN BIGINT)");
0120:
0121: insertBlob2 = conn
0122: .prepareStatement("INSERT INTO BLOBTBL2 values (?,?,?,?)");
0123:
0124: // Please dont change the clob column width,since tests use this width to
0125: // test for truncation of trailing spaces.
0126: s.execute("CREATE TABLE CLOBTBL2 (ID INT NOT NULL PRIMARY KEY,"
0127: + "POS BIGINT, CONTENT CLOB(" + _100MB
0128: + "), DLEN BIGINT)");
0129:
0130: insertBlob2 = conn
0131: .prepareStatement("INSERT INTO BLOBTBL2 values (?,?,?,?)");
0132: selectBlob2 = conn
0133: .prepareStatement("SELECT CONTENT,DLEN FROM BLOBTBL2 WHERE ID = ?");
0134:
0135: insertClob2 = conn
0136: .prepareStatement("INSERT INTO CLOBTBL2 values (?,?,?,?)");
0137:
0138: selectClob2 = conn
0139: .prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL2 WHERE ID = ?");
0140: System.out.println("-----------------------------------");
0141: System.out.println(" END setup");
0142:
0143: deleteBlob2 = conn.prepareStatement("DELETE FROM BLOBTBL2");
0144: deleteClob2 = conn.prepareStatement("DELETE from CLOBTBL2");
0145: System.out.println("-----------------------------------");
0146: System.out.println(" END setup");
0147:
0148: }
0149:
0150: /**
0151: * Create an instance of this class and do the test.
0152: */
0153: public static void main(String[] args) {
0154: //trace = Boolean.getBoolean("trace");
0155: new LobLimits().runTests(args);
0156:
0157: }
0158:
0159: /**
0160: * Create a JDBC connection using the arguments passed in from the harness,
0161: * and then run the LOB tests.
0162: *
0163: * @param args
0164: * Arguments from the harness.
0165: */
0166: public void runTests(String[] args) {
0167: Connection conn = null;
0168: try {
0169:
0170: // use the ij utility to read the property file and
0171: // make the initial connection.
0172: ij.getPropertyArg(args);
0173: conn = ij.startJBMS();
0174:
0175: // do the initial setup,drop and create tables
0176: // prepare stmts
0177: setup(conn);
0178:
0179: conn.setAutoCommit(false);
0180:
0181: clobTests(conn);
0182: blobTests(conn);
0183: //cleanup
0184: cleanup(conn);
0185: } catch (Exception e) {
0186: System.out.println("FAIL -- Unexpected exception:");
0187: e.printStackTrace(System.out);
0188: }
0189: }
0190:
0191: /**
0192: * Close all prepared statements and connection
0193: * @param conn
0194: * @throws Exception
0195: */
0196: private void cleanup(Connection conn) throws Exception {
0197: insertBlob.close();
0198: selectBlob.close();
0199: selectClob.close();
0200: insertClob.close();
0201: deleteClob.close();
0202: deleteBlob.close();
0203: insertBlob2.close();
0204: selectBlob2.close();
0205: selectClob2.close();
0206: insertClob2.close();
0207: deleteBlob2.close();
0208: deleteClob2.close();
0209: conn.close();
0210: new File(DATAFILE).delete();
0211: new File(CHARDATAFILE).delete();
0212: }
0213:
0214: /**
0215: * tests specific for blobs
0216: * @param conn
0217: * @throws Exception
0218: */
0219: private static void blobTests(Connection conn) throws Exception {
0220:
0221: try {
0222: // Test - 2Gb blob ( actually it is 2gb -1)
0223: // Note with setBinaryStream interface the maximum size for the
0224: // stream, can be max value for an int.
0225: // Also note, that lobs in derby currently supports
0226: // maximum size of 2gb -1
0227:
0228: // first do insert blob of 2g, 2 rows
0229: insertBlob_SetBinaryStream("BlobTest #1", conn, insertBlob,
0230: _2GB, 0, 2, _2GB);
0231: // do a select to see if the inserts in test above went ok
0232: selectBlob("BlobTest #2", conn, selectBlob, _2GB, 0, 1);
0233: selectBlob("BlobTest #3", conn, selectBlob, _2GB, 1, 1);
0234:
0235: // now do a select of one of the 2gb rows and update another 2g row
0236: // using the setBlob api, updated blob is of length 2gb
0237: // Fix for Bug entry -DERBY-599[setBlob should not materialize blob
0238: // into memory]
0239: selectUpdateBlob("BlobTest #4", conn, selectBlob, _2GB, 0,
0240: 1, 1);
0241: // select row from blobtbl and then do insert into the blobtbl
0242: // using setBlob
0243: selectInsertBlob("BlobTest #4.1", conn, selectBlob,
0244: insertBlob, _2GB, 0, 3, 1);
0245:
0246: // Test - generate random data, write to a file, use it to insert
0247: // data into blob and then read back and compare if all is ok
0248: // currently in fvt ( derbyall), tests check for substrings etc and
0249: // for small amounts of data. This test will test for 100mb of blob data
0250:
0251: FileOutputStream fos = new FileOutputStream(DATAFILE);
0252: RandomByteStream r = new RandomByteStream(
0253: new java.util.Random(), _100MB);
0254: // write in chunks of 32k buffer
0255: byte[] buffer = new byte[32 * 1024];
0256: int count = 0;
0257:
0258: while ((count = r.read(buffer)) >= 0)
0259: fos.write(buffer, 0, count);
0260:
0261: fos.flush();
0262: fos.close();
0263:
0264: insertBlob2("BlobTest #5.1 ", conn, insertBlob2, _100MB, 0,
0265: 1, _100MB, DATAFILE);
0266: selectBlob2("BlobTest #5.2 ", conn, selectBlob2, _100MB, 0,
0267: 1, DATAFILE);
0268:
0269: // update the 2gb row in blobtbl with the 100mb data and compare if the update
0270: // went ok.
0271: selectUpdateBlob2("BlobTest #6", conn, selectBlob2,
0272: selectBlob, _100MB, 0, 1, 1, DATAFILE);
0273:
0274: deleteTable(conn, deleteBlob2, 1);
0275:
0276: } catch (Exception e) {
0277: System.out.println("FAIL -- Unexpected exception:");
0278: e.printStackTrace(System.out);
0279: }
0280:
0281: conn.commit();
0282:
0283: deleteTable(conn, deleteBlob, 3);
0284:
0285: // Negative Test, use setBlob api to insert a 4GB blob.
0286: long _4GB = 4 * 1024 * 1024 * (1024L);
0287: BlobImpl _4GbBlob = new BlobImpl(new RandomByteStream(
0288: new java.util.Random(), _4GB), _4GB);
0289:
0290: try {
0291: insertBlob_SetBlob("BlobTest #7 (setBlob with 4Gb blob",
0292: conn, insertBlob, _4GbBlob, _4GB, 0, 1, 0);
0293: } catch (SQLException sqle) {
0294: System.out
0295: .println("DERBY DOES NOT SUPPORT INSERT OF 4GB BLOB ");
0296: expectedException(sqle);
0297: }
0298: // ADD NEW TESTS HERE
0299: }
0300:
0301: /**
0302: * tests using clobs
0303: * @param conn
0304: * @throws Exception
0305: */
0306: private static void clobTests(Connection conn) throws Exception {
0307: try {
0308: // Test - 2Gb blob
0309: // Note with setCharacterStream interface the maximum size for the
0310: // stream has to be max value for a int which is (2GB -1 )
0311: // first do insert clob of 2g, 2 rows
0312: insertClob_SetCharacterStream("ClobTest #1", conn,
0313: insertClob, _2GB, 0, 2, _2GB);
0314: // do a select to see if the inserts in test above went ok
0315: selectClob("ClobTest #2", conn, selectClob, _2GB, 0, 1);
0316: selectClob("ClobTest #3", conn, selectClob, _2GB, 0, 1);
0317: // do a select and then update a row of 2gb size: uses getClob
0318: selectUpdateClob("ClobTest #4", conn, selectClob, _2GB, 0,
0319: 1, 1);
0320:
0321: // Test - generate random data, write to a file, use it to insert
0322: // data into clob and then read back and compare if all is ok
0323: // currently in fvt ( derbyall), tests check for substrings etc and
0324: // for small amounts of data. This test will test for 100mb of clob data
0325: writeToFile(CHARDATAFILE, new RandomCharReader(
0326: new java.util.Random(), _100MB));
0327: insertClob2("ClobTest #5.1 ", conn, insertClob2, _100MB, 0,
0328: 1, _100MB, CHARDATAFILE);
0329: selectClob2("ClobTest #5.2 ", conn, selectClob2, _100MB, 0,
0330: 1, CHARDATAFILE);
0331:
0332: // Disabled for now, this will materialize, will open
0333: // jira for it.
0334: //updateClob2("ClobTest #8.1",conn,selectClob,_100MB,0,0,10,1,CHARDATAFILE);
0335:
0336: // update the 2gb row in clobtbl with the 100mb data and compare if the update
0337: // went ok.
0338: selectUpdateClob2("ClobTest #8.2", conn, selectClob2,
0339: selectClob, _100MB, 0, 1, 1, CHARDATAFILE);
0340:
0341: // test for trailing space truncation
0342: // insert 100mb+33k of data which has 33k of trailing space,
0343: // into a column of 100mb
0344: // insert should be successful, select should retrieve 100mb of data
0345:
0346: // Generate random data and write to a file, this file will be used
0347: // in the verification process after inserts and updates.
0348: writeToFile(CHARDATAFILE,
0349: new RandomCharReader(new java.util.Random(),
0350: (NUM_TRAILING_SPACES + _100MB),
0351: NUM_TRAILING_SPACES));
0352: insertClob2("ClobTest #6.1 ", conn, insertClob2, _100MB, 3,
0353: 1, (NUM_TRAILING_SPACES + _100MB), CHARDATAFILE);
0354: // select will retrieve data and verify the data inserted.
0355: selectClob2("ClobTest #6.2 ", conn, selectClob2, _100MB, 3,
0356: 1, CHARDATAFILE);
0357:
0358: negativeSpaceTruncationTest("ClobTest #7", conn);
0359:
0360: // Test - for stream contains a trailing non-space character
0361: // insert should throw an error
0362: writeToFile(CHARDATAFILE, new RandomCharReader(
0363: new java.util.Random(), MORE_DATA_THAN_COL_WIDTH));
0364: try {
0365: insertClob2("ClobTest #9.1 ", conn, insertClob2,
0366: MORE_DATA_THAN_COL_WIDTH, 4, 1,
0367: MORE_DATA_THAN_COL_WIDTH, CHARDATAFILE);
0368: } catch (SQLException sqle) {
0369: System.out
0370: .println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed");
0371: expectedException(sqle);
0372: }
0373: // no row must be retrieved.
0374: selectClob2("ClobTest #9.2 ", conn, selectClob2, _100MB, 4,
0375: 0, CHARDATAFILE);
0376:
0377: try {
0378: insertClob2("ClobTest #10 ", conn, insertClob2,
0379: MORE_DATA_THAN_COL_WIDTH, 4, 1,
0380: MORE_DATA_THAN_COL_WIDTH + 1, CHARDATAFILE);
0381: } catch (SQLException sqle) {
0382: System.out
0383: .println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed and"
0384: + " stream length is one greater than actual length of the stream ");
0385: expectedException(sqle);
0386: }
0387:
0388: try {
0389: insertClob2("ClobTest #11 ", conn, insertClob2,
0390: MORE_DATA_THAN_COL_WIDTH, 4, 1,
0391: MORE_DATA_THAN_COL_WIDTH - 1, CHARDATAFILE);
0392: } catch (SQLException sqle) {
0393: System.out
0394: .println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed and"
0395: + " stream length is one less than actual length of the stream ");
0396: expectedException(sqle);
0397: }
0398: deleteTable(conn, deleteClob2, 2);
0399: } catch (Exception e) {
0400: System.out.println("FAIL -- Unexpected exception:");
0401: e.printStackTrace(System.out);
0402: }
0403:
0404: try {
0405: // give -ve streamlength
0406: insertClob_SetCharacterStream("ClobTest #12.1", conn,
0407: insertClob, _100MB, 4, 1, -1);
0408: } catch (SQLException sqle) {
0409: System.out.println("NEGATIVE TEST - Expected Exception:");
0410: expectedException(sqle);
0411: }
0412:
0413: selectClob("ClobTest #12.2", conn, selectClob, _100MB, 4, 0);
0414:
0415: deleteTable(conn, deleteClob, 2);
0416:
0417: // Negative tests use the setClob API to insert a 4GB clob
0418:
0419: long _4GB = 4 * 1024 * 1024 * (1024L);
0420:
0421: ClobImpl _4GBClob = new ClobImpl(new RandomCharReader(
0422: new java.util.Random(), _4GB), _4GB);
0423:
0424: try {
0425: insertClob_SetClob("ClobTest #13 (setClob with 4Gb clob",
0426: conn, insertClob, _4GBClob, _4GB, 0, 1, 0);
0427: } catch (SQLException sqle) {
0428: System.out
0429: .println("DERBY DOES NOT SUPPORT INSERT OF 4GB CLOB ");
0430: expectedException(sqle);
0431: }
0432:
0433: // ADD NEW TESTS HERE
0434: }
0435:
0436: private static void negativeSpaceTruncationTest(String msg,
0437: Connection conn) throws Exception {
0438: // Negative test, stream has trailing spaces but the stream length is one
0439: // more than the actual length of the stream
0440: try {
0441: insertClob2(msg, conn, insertClob2, _100MB, 4, 1,
0442: (NUM_TRAILING_SPACES + _100MB - 1), CHARDATAFILE);
0443: } catch (SQLException sqle) {
0444: System.out
0445: .println("EXPECTED EXCEPTION - stream has trailing spaces,but stream "
0446: + " length is 1 less than actual length of stream");
0447: expectedException(sqle);
0448: }
0449:
0450: try {
0451: insertClob2(msg, conn, insertClob2, _100MB, 5, 1,
0452: (NUM_TRAILING_SPACES + _100MB + 1), CHARDATAFILE);
0453: } catch (SQLException sqle) {
0454: System.out
0455: .println("EXPECTED EXCEPTION - stream has trailing spaces,but stream "
0456: + " length is 1 greater than actual length of stream");
0457: expectedException(sqle);
0458: }
0459: }
0460:
0461: /**
0462: * insert blob
0463: * @param bloblen length of blob to insert
0464: * @param start start id value for insert
0465: * @param rows insert rows number of rows
0466: * @param streamLength stream length passed to setBinaryStream(,,length)
0467: */
0468: private static void insertBlob_SetBinaryStream(String testId,
0469: Connection conn, PreparedStatement ps, int bloblen,
0470: int start, int rows, int streamLength) throws SQLException {
0471: System.out.println("========================================");
0472: System.out.println("START " + testId + "insertBlob of size = "
0473: + bloblen);
0474: long ST = 0;
0475: if (trace)
0476: ST = System.currentTimeMillis();
0477:
0478: int count = 0;
0479: java.util.Random random = new java.util.Random();
0480: for (int i = start; i < start + rows; i++) {
0481: ps.setInt(1, i);
0482: ps.setInt(2, 0);
0483: ps.setLong(3, bloblen);
0484: ps
0485: .setBinaryStream(4, new RandomByteStream(random,
0486: bloblen), streamLength);
0487: count += ps.executeUpdate();
0488: }
0489: conn.commit();
0490: if (trace) {
0491: System.out.println("Insert Blob (" + bloblen + ")"
0492: + " rows= " + count + " = "
0493: + (long) (System.currentTimeMillis() - ST));
0494:
0495: }
0496: verifyTest(count, rows, " Rows inserted with blob of size ("
0497: + bloblen + ") =");
0498: System.out.println("========================================");
0499:
0500: }
0501:
0502: /**
0503: * insert blob, using a setBlob api.
0504: * @param bloblen
0505: * length of blob to insert
0506: * @param blob
0507: * blob to insert
0508: * @param start
0509: * start id value for insert
0510: * @param rows
0511: * insert rows number of rows
0512: * @param expectedRows
0513: * rows expected to be inserted
0514: */
0515: private static void insertBlob_SetBlob(String testId,
0516: Connection conn, PreparedStatement ps, java.sql.Blob blob,
0517: long bloblen, int start, int rows, int expectedRows)
0518: throws SQLException {
0519: System.out.println("========================================");
0520: System.out.println("START " + testId + "insertBlob of size = "
0521: + bloblen);
0522: long ST = 0;
0523: if (trace)
0524: ST = System.currentTimeMillis();
0525: int count = 0;
0526:
0527: try {
0528:
0529: for (int i = start; i < start + rows; i++) {
0530: ps.setInt(1, i);
0531: ps.setInt(2, 0);
0532: ps.setLong(3, bloblen);
0533: ps.setBlob(4, blob);
0534: count += ps.executeUpdate();
0535: }
0536: conn.commit();
0537: if (trace) {
0538: System.out.println("Insert Blob (" + bloblen + ")"
0539: + " rows= " + count + " = "
0540: + (long) (System.currentTimeMillis() - ST));
0541:
0542: }
0543: } catch (SQLException e) {
0544: verifyTest(count, expectedRows,
0545: " Rows inserted with blob of size (" + bloblen
0546: + ") =");
0547: System.out
0548: .println("========================================");
0549: throw e;
0550: }
0551:
0552: verifyTest(count, expectedRows,
0553: " Rows inserted with blob of size (" + bloblen + ") =");
0554: System.out.println("========================================");
0555:
0556: }
0557:
0558: /**
0559: * select from blob table (BLOBTBL)
0560: * @param bloblen select expects to retrieve a blob of this length
0561: * @param id id of the row to retrieve
0562: * @param expectedRows number of rows expected to match id
0563: */
0564: private static void selectBlob(String testId, Connection conn,
0565: PreparedStatement ps, int bloblen, int id, int expectedRows)
0566: throws SQLException {
0567: System.out.println("========================================");
0568: System.out.println("START " + testId
0569: + " - SELECT BLOB of size = " + bloblen);
0570:
0571: long ST = 0;
0572: ResultSet rs = null;
0573:
0574: if (trace)
0575: ST = System.currentTimeMillis();
0576:
0577: int count = 0;
0578: ps.setInt(1, id);
0579: rs = ps.executeQuery();
0580:
0581: while (rs.next()) {
0582: count++;
0583: Blob value = rs.getBlob(1);
0584: long l = value.length();
0585: long dlen = rs.getLong(2);
0586: if (dlen != l) {
0587: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
0588: + " expected " + dlen
0589: + " for row in BLOBTBL with ID=" + id);
0590: }
0591: }
0592: conn.commit();
0593:
0594: verifyTest(count, expectedRows,
0595: "Matched rows selected with blob of size(" + bloblen
0596: + ") =");
0597:
0598: if (trace) {
0599: System.out.println("Select Blob (" + bloblen + ")"
0600: + " rows= " + expectedRows + " = "
0601: + (long) (System.currentTimeMillis() - ST));
0602: }
0603: System.out.println("========================================");
0604: }
0605:
0606: /**
0607: * insert blob into BLOBTBL2
0608: * @param bloblen length of blob to insert
0609: * @param start id value for insert
0610: * @param rows insert rows number of rows
0611: * @param streamLength stream length passed to setBinaryStream(,,length)
0612: * @param file filename to match retrieved data against
0613: */
0614:
0615: private static void insertBlob2(String testId, Connection conn,
0616: PreparedStatement ps, int bloblen, int start, int rows,
0617: int streamLength, String file) throws Exception {
0618: System.out.println("========================================");
0619: System.out.println("START " + testId + "insert Blob of size = "
0620: + bloblen);
0621: int count = 0;
0622: java.util.Random random = new java.util.Random();
0623: FileInputStream fis = null;
0624:
0625: long ST = 0;
0626: if (trace)
0627: ST = System.currentTimeMillis();
0628:
0629: for (int i = start; i < start + rows; i++) {
0630: fis = new FileInputStream(file);
0631: ps.setInt(1, i);
0632: ps.setInt(2, 0);
0633: ps.setLong(4, bloblen);
0634: ps.setBinaryStream(3, fis, streamLength);
0635: count += ps.executeUpdate();
0636: fis.close();
0637: }
0638: conn.commit();
0639: if (trace) {
0640: System.out.println("Insert Blob (" + bloblen + ")"
0641: + " rows= " + count + " = "
0642: + (long) (System.currentTimeMillis() - ST));
0643:
0644: }
0645: verifyTest(count, rows, " Rows inserted with blob of size ("
0646: + bloblen + ") =");
0647: System.out.println("========================================");
0648:
0649: }
0650:
0651: /**
0652: * select from blob table (BLOBTBL2)
0653: * @param bloblen select expects to retrieve a blob of this length
0654: * @param id id of the row to retrieve
0655: * @param expectedRows number of rows expected to match id
0656: * @param file name of the file,against which the retrieved data is
0657: * compared
0658: */
0659: private static void selectBlob2(String testId, Connection conn,
0660: PreparedStatement ps, int bloblen, int id,
0661: int expectedRows, String file) throws Exception {
0662: System.out.println("========================================");
0663: System.out.println("START " + testId
0664: + " - SELECT BLOB of size = " + bloblen);
0665:
0666: long ST = 0;
0667: ResultSet rs = null;
0668:
0669: if (trace)
0670: ST = System.currentTimeMillis();
0671:
0672: int count = 0;
0673: ps.setInt(1, id);
0674: rs = ps.executeQuery();
0675:
0676: while (rs.next()) {
0677: count++;
0678: Blob value = rs.getBlob(1);
0679: long l = value.length();
0680: long dlen = rs.getLong(2);
0681: if (dlen != l) {
0682: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
0683: + " expected " + dlen
0684: + " for row in BLOBTBL with ID=" + id);
0685: } else
0686: compareBlobToFile(value.getBinaryStream(), file);
0687: }
0688: conn.commit();
0689:
0690: verifyTest(count, expectedRows,
0691: "Matched rows selected with blob of size(" + bloblen
0692: + ") =");
0693:
0694: if (trace) {
0695: System.out.println("Select Blob (" + bloblen + ")"
0696: + " rows= " + expectedRows + " = "
0697: + (long) (System.currentTimeMillis() - ST));
0698: }
0699: System.out.println("========================================");
0700: }
0701:
0702: /**
0703: * Basically this test will do an update using setBlob api -
0704: * select row from blobtbl and then update a row in blobtbl
0705: * and verify updated data in blobtbl
0706: * @param ps select statement from which blob is retrieved
0707: * @param bloblen updating value is of length bloblen
0708: * @param id id of the row retrieved, for the update
0709: * @param updateId id of the row that is updated
0710: * @param expectedRows to be updated
0711: */
0712: private static void selectUpdateBlob(String testId,
0713: Connection conn, PreparedStatement ps, int bloblen, int id,
0714: int updateId, int expectedRows) throws Exception {
0715: System.out.println("========================================");
0716: System.out.println("START " + testId
0717: + " - select and then update blob of size= " + bloblen
0718: + " - Uses getBlob api");
0719:
0720: ResultSet rs = null;
0721:
0722: ps.setInt(1, id);
0723: rs = ps.executeQuery();
0724: rs.next();
0725: Blob value = rs.getBlob(1);
0726: long l = value.length();
0727: long dlen = rs.getLong(2);
0728: if (dlen != l) {
0729: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
0730: + " expected " + dlen
0731: + " for row in BLOBTBL with ID=" + id);
0732: }
0733:
0734: PreparedStatement psUpd = conn
0735: .prepareStatement("update BLOBTBL set content=?,dlen =? where id = ?");
0736: psUpd.setBlob(1, value);
0737: psUpd.setLong(2, l);
0738: psUpd.setInt(3, updateId);
0739:
0740: System.out.println("Rows Updated = " + psUpd.executeUpdate());
0741: conn.commit();
0742:
0743: // now select and verify that update went through ok.
0744: ps.setInt(1, updateId);
0745: ResultSet rs2 = ps.executeQuery();
0746: rs2.next();
0747: Blob updatedValue = rs2.getBlob(1);
0748:
0749: if (updatedValue.length() != l)
0750: System.out
0751: .println("FAIL - Retrieving the updated blob length does not match "
0752: + "expected length = "
0753: + l
0754: + " found = "
0755: + updatedValue.length());
0756:
0757: // close resultsets
0758: conn.commit();
0759: rs.close();
0760: rs2.close();
0761: psUpd.close();
0762: System.out.println("========================================");
0763: }
0764:
0765: /**
0766: * Basically this test will do an insert using setBlob api -
0767: * select row from blobtbl and then insert a row in blobtbl
0768: * and verify updated data in blobtbl
0769: * @param ps select statement from which blob is retrieved
0770: * @param bloblen updating value is of length bloblen
0771: * @param id id of the row retrieved, for the update
0772: * @param insertId id of the row that is inserted
0773: * @param expectedRows to be updated
0774: */
0775: private static void selectInsertBlob(String testId,
0776: Connection conn, PreparedStatement ps,
0777: PreparedStatement ins, int bloblen, int id, int insertId,
0778: int expectedRows) throws Exception {
0779: System.out.println("========================================");
0780: System.out
0781: .println("START "
0782: + testId
0783: + " - select and then insert blob of size= "
0784: + bloblen
0785: + " - Uses getBlob api to do select and setBlob for insert");
0786:
0787: ResultSet rs = null;
0788:
0789: ps.setInt(1, id);
0790: rs = ps.executeQuery();
0791: rs.next();
0792: Blob value = rs.getBlob(1);
0793: long l = value.length();
0794: long dlen = rs.getLong(2);
0795: if (dlen != l) {
0796: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
0797: + " expected " + dlen
0798: + " for row in BLOBTBL with ID=" + id);
0799: }
0800:
0801: ins.setInt(1, insertId);
0802: ins.setInt(2, 0);
0803: ins.setLong(3, l);
0804: ins.setBlob(4, value);
0805:
0806: System.out.println("Rows Updated = " + ins.executeUpdate());
0807: conn.commit();
0808:
0809: // now select and verify that update went through ok.
0810: ps.setInt(1, insertId);
0811: ResultSet rs2 = ps.executeQuery();
0812: rs2.next();
0813: Blob insertedValue = rs2.getBlob(1);
0814:
0815: if (insertedValue.length() != l)
0816: System.out
0817: .println("FAIL - Retrieving the updated blob length does not match "
0818: + "expected length = "
0819: + l
0820: + " found = "
0821: + insertedValue.length());
0822:
0823: // close resultsets
0824: conn.commit();
0825: rs.close();
0826: rs2.close();
0827: System.out.println("========================================");
0828: }
0829:
0830: /**
0831: * Basically this test will do an update using setBinaryStream api and verifies the
0832: * updated data. select row from blobtbl2 and then update a row in blobtbl
0833: * and verify updated data in blobtbl
0834: * @param bloblen updating value is of length bloblen
0835: * @param id id of the row retrieved, for the update
0836: * @param updateId id of the row that is updated
0837: * @param expectedRows to be updated
0838: * @param file name of the file,against which the updated data is
0839: * compared
0840: */
0841: private static void selectUpdateBlob2(String testId,
0842: Connection conn, PreparedStatement ps,
0843: PreparedStatement sel, int bloblen, int id, int updateId,
0844: int expectedRows, String file) throws Exception {
0845: System.out.println("========================================");
0846: System.out.println("START " + testId
0847: + " - select and then update blob of size= " + bloblen
0848: + " - Uses getBlob and setBlob api");
0849:
0850: ResultSet rs = null;
0851:
0852: // retrieve row from blobtbl2
0853: ps.setInt(1, id);
0854: rs = ps.executeQuery();
0855: rs.next();
0856: Blob value = rs.getBlob(1);
0857: long l = value.length();
0858: long dlen = rs.getLong(2);
0859: if (dlen != l) {
0860: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
0861: + " expected " + dlen
0862: + " for row in BLOBTBL2 with ID=" + id);
0863: }
0864:
0865: PreparedStatement psUpd = conn
0866: .prepareStatement("update BLOBTBL set content=?,dlen =? where id = ?");
0867: psUpd.setBlob(1, value);
0868: psUpd.setLong(2, l);
0869: psUpd.setInt(3, updateId);
0870:
0871: System.out.println("Rows Updated = " + psUpd.executeUpdate());
0872: conn.commit();
0873:
0874: // now select and verify that update went through ok.
0875: sel.setInt(1, updateId);
0876: ResultSet rs2 = sel.executeQuery();
0877: rs2.next();
0878: Blob updatedValue = rs2.getBlob(1);
0879:
0880: if (updatedValue.length() != l) {
0881: System.out
0882: .println("FAIL - MISMATCH length of updated blob value : expected="
0883: + l + " found =" + updatedValue.length());
0884: } else
0885: compareBlobToFile(updatedValue.getBinaryStream(), file);
0886:
0887: // close resultsets
0888: conn.commit();
0889: rs.close();
0890: rs2.close();
0891: psUpd.close();
0892: System.out.println("========================================");
0893:
0894: }
0895:
0896: private static void compareBlobToFile(InputStream lobstream,
0897: String filename) throws Exception {
0898: FileInputStream file = new FileInputStream(filename);
0899: int l = 0;
0900: int b = 0;
0901: do {
0902: l = lobstream.read();
0903: b = file.read();
0904: if (l != b) {
0905: System.out
0906: .println("FAIL -- MISMATCH in data stored versus"
0907: + "data retrieved");
0908: break;
0909: }
0910: } while (l != -1 && b != -1);
0911: }
0912:
0913: private static void deleteTable(Connection conn,
0914: PreparedStatement ps, int expectedRows) throws SQLException {
0915: int count = ps.executeUpdate();
0916: conn.commit();
0917: verifyTest(count, expectedRows, "Rows deleted =");
0918: }
0919:
0920: /**
0921: * insert clob
0922: * @param cloblen length of clob to insert
0923: * @param start id value for insert
0924: * @param rows insert rows number of rows
0925: * @param streamLength stream length passed to setCharacterStream(...,length)
0926: */
0927: private static void insertClob_SetCharacterStream(String testId,
0928: Connection conn, PreparedStatement ps, int cloblen,
0929: int start, int rows, int streamLength) throws SQLException {
0930: System.out.println("========================================");
0931: System.out.println("START " + testId
0932: + " -insertClob of size = " + cloblen);
0933:
0934: long ST = 0;
0935: java.util.Random random = new java.util.Random();
0936: int count = 0;
0937: if (trace)
0938: ST = System.currentTimeMillis();
0939:
0940: for (int i = start; i < start + rows; i++) {
0941: ps.setInt(1, i);
0942: ps.setInt(2, 0);
0943: ps.setLong(3, cloblen);
0944: ps.setCharacterStream(4, new RandomCharReader(random,
0945: cloblen), streamLength);
0946: count += ps.executeUpdate();
0947: }
0948: conn.commit();
0949: if (trace) {
0950: System.out.println("Insert Clob (" + cloblen + ")"
0951: + " rows= " + count + " = "
0952: + (long) (System.currentTimeMillis() - ST));
0953:
0954: }
0955: verifyTest(count, rows, "Rows inserted with clob of size ("
0956: + cloblen + ") = ");
0957: System.out.println("========================================");
0958:
0959: }
0960:
0961: /**
0962: * insert clob, using a setClob api.
0963: * @param cloblen
0964: * length of clob to insert
0965: * @param clob
0966: * clob to insert
0967: * @param start
0968: * start id value for insert
0969: * @param rows
0970: * insert rows number of rows
0971: * @param expectedRows
0972: * rows expected to be inserted
0973: */
0974: private static void insertClob_SetClob(String testId,
0975: Connection conn, PreparedStatement ps, java.sql.Clob clob,
0976: long cloblen, int start, int rows, int expectedRows)
0977: throws SQLException {
0978: System.out.println("========================================");
0979: System.out.println("START " + testId + "insertClob of size = "
0980: + cloblen);
0981: long ST = 0;
0982: if (trace)
0983: ST = System.currentTimeMillis();
0984: int count = 0;
0985:
0986: try {
0987:
0988: for (int i = start; i < start + rows; i++) {
0989: ps.setInt(1, i);
0990: ps.setInt(2, 0);
0991: ps.setLong(3, cloblen);
0992: ps.setClob(4, clob);
0993: count += ps.executeUpdate();
0994: }
0995: conn.commit();
0996: if (trace) {
0997: System.out.println("Insert Clob (" + cloblen + ")"
0998: + " rows= " + count + " = "
0999: + (long) (System.currentTimeMillis() - ST));
1000:
1001: }
1002: } catch (SQLException e) {
1003: verifyTest(count, expectedRows,
1004: " Rows inserted with clob of size (" + cloblen
1005: + ") =");
1006: System.out
1007: .println("========================================");
1008: throw e;
1009: }
1010:
1011: verifyTest(count, expectedRows,
1012: " Rows inserted with clob of size (" + cloblen + ") =");
1013: System.out.println("========================================");
1014:
1015: }
1016:
1017: /**
1018: * select from clob table
1019: * @param cloblen select expects to retrieve a clob of this length
1020: * @param id id of the row to retrieve
1021: * @param expectedRows number of rows expected to match id
1022: */
1023: private static void selectClob(String testId, Connection conn,
1024: PreparedStatement ps, int cloblen, int id, int expectedRows)
1025: throws SQLException {
1026: System.out.println("========================================");
1027: System.out.println("START " + testId
1028: + " - SELECT CLOB of size = " + cloblen);
1029:
1030: long ST = 0;
1031: int count = 0;
1032: ResultSet rs = null;
1033: if (trace)
1034: ST = System.currentTimeMillis();
1035:
1036: ps.setInt(1, id);
1037: rs = ps.executeQuery();
1038: while (rs.next()) {
1039: count++;
1040: Clob value = rs.getClob(1);
1041: long l = value.length();
1042: long dlen = rs.getLong(2);
1043: if (dlen != l) {
1044: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
1045: + " expected " + dlen
1046: + " for row in CLOBTBL with ID=" + id);
1047: }
1048:
1049: }
1050: conn.commit();
1051: if (trace) {
1052: System.out.println("Select Clob (" + cloblen + ")"
1053: + " rows= " + expectedRows + " = "
1054: + (long) (System.currentTimeMillis() - ST));
1055:
1056: }
1057:
1058: verifyTest(count, expectedRows,
1059: "Matched rows selected with clob of size(" + cloblen
1060: + ") =");
1061: System.out.println("========================================");
1062:
1063: }
1064:
1065: /**
1066: * insert clob into CLOBTBL2
1067: * @param cloblen length of clob to insert
1068: * @param start id value for insert
1069: * @param rows insert rows number of rows
1070: * @param streamLength stream length passed to setCharacterStream(pos,reader,streamLength)
1071: * @param file name of the file that has data to be inserted
1072: */
1073: private static void insertClob2(String testId, Connection conn,
1074: PreparedStatement ps, int cloblen, int start, int rows,
1075: int streamLength, String file) throws Exception {
1076: System.out.println("========================================");
1077: System.out.println("START " + testId + "insert Clob of size = "
1078: + cloblen);
1079: int count = 0;
1080: FileReader reader = null;
1081: long ST = 0;
1082: if (trace)
1083: ST = System.currentTimeMillis();
1084:
1085: for (int i = start; i < start + rows; i++) {
1086: reader = new FileReader(file);
1087: ps.setInt(1, i);
1088: ps.setInt(2, 0);
1089: ps.setLong(4, cloblen);
1090: ps.setCharacterStream(3, reader, streamLength);
1091: count += ps.executeUpdate();
1092: reader.close();
1093: }
1094: conn.commit();
1095: if (trace) {
1096: System.out.println("Insert Clob (" + cloblen + ")"
1097: + " rows= " + count + " = "
1098: + (long) (System.currentTimeMillis() - ST));
1099:
1100: }
1101: verifyTest(count, rows, " Rows inserted with clob of size ("
1102: + cloblen + ") =");
1103: System.out.println("========================================");
1104:
1105: }
1106:
1107: /**
1108: * select from clob table (CLOBTBL2)
1109: * @param cloblen select expects to retrieve a clob of this length
1110: * @param id id of the row to retrieve
1111: * @param expectedRows number of rows expected to match id
1112: * @param file filename to compare the retrieved data against
1113: */
1114: private static void selectClob2(String testId, Connection conn,
1115: PreparedStatement ps, int cloblen, int id,
1116: int expectedRows, String file) throws SQLException,
1117: Exception {
1118: System.out.println("========================================");
1119: System.out.println("START " + testId
1120: + " - SELECT CLOB of size = " + cloblen);
1121:
1122: long ST = 0;
1123: ResultSet rs = null;
1124:
1125: if (trace)
1126: ST = System.currentTimeMillis();
1127:
1128: int count = 0;
1129: ps.setInt(1, id);
1130: rs = ps.executeQuery();
1131:
1132: while (rs.next()) {
1133: count++;
1134: Clob value = rs.getClob(1);
1135: long l = value.length();
1136: long dlen = rs.getLong(2);
1137: if (cloblen != l) {
1138: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
1139: + " expected " + dlen
1140: + " for row in CLOBTBL2 with ID=" + id);
1141: } else
1142: compareClobToFile(value.getCharacterStream(), file,
1143: cloblen);
1144: }
1145: conn.commit();
1146:
1147: verifyTest(count, expectedRows,
1148: "Matched rows selected with clob of size(" + cloblen
1149: + ") =");
1150:
1151: if (trace) {
1152: System.out.println("Select Clob (" + cloblen + ")"
1153: + " rows= " + expectedRows + " = "
1154: + (long) (System.currentTimeMillis() - ST));
1155: }
1156: System.out.println("========================================");
1157: }
1158:
1159: /*
1160: * Basically this test will do an update using setClob api -
1161: * select row from clobtbl and then update a row in clobtbl
1162: * and verify updated data in clobtbl
1163: */
1164: private static void selectUpdateClob(String testId,
1165: Connection conn, PreparedStatement ps, int cloblen, int id,
1166: int updateId, int expectedRows) throws Exception {
1167: System.out.println("========================================");
1168: System.out.println("START " + testId
1169: + " - select and then update clob of size= " + cloblen
1170: + " - Uses setClob api");
1171:
1172: ResultSet rs = null;
1173:
1174: ps.setInt(1, id);
1175: rs = ps.executeQuery();
1176: rs.next();
1177: Clob value = rs.getClob(1);
1178: long l = value.length();
1179: long dlen = rs.getLong(2);
1180: if (dlen != l) {
1181: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
1182: + " expected " + dlen
1183: + " for row in CLOBTBL with ID=" + id);
1184: }
1185:
1186: PreparedStatement psUpd = conn
1187: .prepareStatement("update CLOBTBL set content=?,dlen =? where id = ?");
1188: psUpd
1189: .setCharacterStream(1, value.getCharacterStream(),
1190: (int) l);
1191: psUpd.setLong(2, l);
1192: psUpd.setInt(3, updateId);
1193:
1194: System.out.println("Rows Updated = " + psUpd.executeUpdate());
1195: conn.commit();
1196:
1197: // now select and verify that update went through ok.
1198: ps.setInt(1, updateId);
1199: ResultSet rs2 = ps.executeQuery();
1200: rs2.next();
1201: Clob updatedValue = rs2.getClob(1);
1202:
1203: if (updatedValue.length() != l)
1204: System.out
1205: .println("FAIL - Retrieving the updated clob length does not match "
1206: + "expected length = "
1207: + l
1208: + " found = "
1209: + updatedValue.length());
1210:
1211: // close resultsets
1212: conn.commit();
1213: rs.close();
1214: rs2.close();
1215: psUpd.close();
1216: System.out.println("========================================");
1217: }
1218:
1219: /*
1220: * Basically this test will do an update using setBlob api and verifies the
1221: * updated data. select row from clobtbl2 and then update a row in clobtbl
1222: * and verify updated data in clobtbl against the data in the original file
1223: */
1224: private static void selectUpdateClob2(String testId,
1225: Connection conn, PreparedStatement ps,
1226: PreparedStatement sel, int cloblen, int id, int updateId,
1227: int expectedRows, String file) throws Exception {
1228: System.out.println("========================================");
1229: System.out.println("START " + testId
1230: + " - select and then update clob of size= " + cloblen
1231: + " - Uses setClob api");
1232:
1233: ResultSet rs = null;
1234:
1235: // retrieve row from clobtbl2
1236: ps.setInt(1, id);
1237: rs = ps.executeQuery();
1238: rs.next();
1239: Clob value = rs.getClob(1);
1240: long l = value.length();
1241: long dlen = rs.getLong(2);
1242: if (dlen != l) {
1243: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
1244: + " expected " + dlen
1245: + " for row in CLOBTBL2 with ID=" + id);
1246: }
1247:
1248: PreparedStatement psUpd = conn
1249: .prepareStatement("update CLOBTBL set content=?,dlen =? where id = ?");
1250: psUpd.setClob(1, value);
1251: psUpd.setLong(2, l);
1252: psUpd.setInt(3, updateId);
1253:
1254: System.out.println("Rows Updated = " + psUpd.executeUpdate());
1255: conn.commit();
1256:
1257: // now select and verify that update went through ok.
1258: sel.setInt(1, updateId);
1259: ResultSet rs2 = sel.executeQuery();
1260: rs2.next();
1261: Clob updatedValue = rs2.getClob(1);
1262:
1263: if (updatedValue.length() != l) {
1264: System.out
1265: .println("FAIL - MISMATCH length of updated clob value , found="
1266: + updatedValue.length()
1267: + ",expected = "
1268: + l);
1269: } else
1270: compareClobToFile(updatedValue.getCharacterStream(), file,
1271: (int) l);
1272:
1273: // close resultsets
1274: conn.commit();
1275: rs.close();
1276: rs2.close();
1277: psUpd.close();
1278: System.out.println("========================================");
1279:
1280: }
1281:
1282: /*
1283: * Basically this test will do an update using updateClob api and verifies the
1284: * updated data. select row from clobtbl2 and then update a row in clobtbl
1285: * and verify updated data in clobtbl against the data in the original file
1286: * @param updateRowId id of the row that needs to be updated
1287: */
1288: private static void updateClob2(String testId, Connection conn,
1289: PreparedStatement sel, int cloblen, int id,
1290: int updateRowId, int updateIdVal, int expectedRows,
1291: String file) throws Exception {
1292: System.out.println("========================================");
1293: System.out.println("START " + testId
1294: + " - select and then update clob of size= " + cloblen
1295: + " - Uses updateClob api");
1296:
1297: PreparedStatement ps1 = conn
1298: .prepareStatement("SELECT * FROM CLOBTBL FOR UPDATE",
1299: ResultSet.TYPE_FORWARD_ONLY,
1300: ResultSet.CONCUR_UPDATABLE);
1301: PreparedStatement ps = conn
1302: .prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL2 where ID =?");
1303:
1304: ResultSet rs = null;
1305: ps.setInt(1, id);
1306: // retrieve row from clobtbl2
1307: rs = ps.executeQuery();
1308: rs.next();
1309: Clob value = rs.getClob(1);
1310: long l = value.length();
1311: long dlen = rs.getLong(2);
1312: if (dlen != l) {
1313: System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
1314: + " expected " + dlen
1315: + " for row in CLOBTBL2 with ID=" + id);
1316: }
1317:
1318: ResultSet rs1 = ps1.executeQuery();
1319: while (rs1.next()) {
1320: if (rs1.getInt(1) == updateRowId) {
1321: rs1.updateClob(4, value);
1322: rs1.updateInt(1, updateIdVal);
1323: rs1.updateInt(2, 0);
1324: rs1.updateLong(3, dlen);
1325: rs1.updateRow();
1326: break;
1327: }
1328: }
1329: // close resultsets
1330: conn.commit();
1331: rs.close();
1332: rs1.close();
1333: ps1.close();
1334: ps.close();
1335:
1336: // verify
1337: // now select and verify that update went through ok.
1338: sel.setInt(1, updateIdVal);
1339: ResultSet rs2 = sel.executeQuery();
1340: rs2.next();
1341: Clob updatedValue = rs2.getClob(1);
1342:
1343: if (updatedValue.length() != l) {
1344: System.out
1345: .println("FAIL - MISMATCH length of updated clob value , found="
1346: + updatedValue.length()
1347: + ",expected = "
1348: + l);
1349: } else
1350: compareClobToFile(updatedValue.getCharacterStream(), file,
1351: (int) l);
1352:
1353: System.out.println("========================================");
1354:
1355: }
1356:
1357: private static void compareClobToFile(Reader lobstream,
1358: String filename, int length) throws Exception {
1359: FileReader file = new FileReader(filename);
1360: int c1 = 0;
1361: int c2 = 0;
1362: long count = 0;
1363: do {
1364: c1 = lobstream.read();
1365: c2 = file.read();
1366: if (c1 != c2) {
1367: System.out
1368: .println("FAIL -- MISMATCH in data stored versus data retrieved at "
1369: + count);
1370: break;
1371: }
1372: count++;
1373: length--;
1374: } while (c1 != -1 && c2 != -1 && length > 0);
1375: }
1376:
1377: private static void expectedException(SQLException sqle) {
1378:
1379: while (sqle != null) {
1380: String sqlState = sqle.getSQLState();
1381: if (sqlState == null) {
1382: sqlState = "<NULL>";
1383: }
1384: System.out.println("EXPECTED SQL Exception: (" + sqlState
1385: + ") " + sqle.getMessage());
1386:
1387: sqle = sqle.getNextException();
1388: }
1389: }
1390:
1391: private static void verifyTest(int affectedRows, int expectedRows,
1392: String test) {
1393: if (affectedRows != expectedRows)
1394: System.out.println("FAIL --" + test + affectedRows
1395: + " , but expected rows =" + expectedRows);
1396: else
1397: System.out.println(test + affectedRows);
1398: }
1399:
1400: private static void writeToFile(String file, Reader r)
1401: throws IOException {
1402: // does file exist, if so delete and write to a fresh file
1403: File f = new File(file);
1404: if (f.exists())
1405: f.delete();
1406: FileWriter writer = new FileWriter(file);
1407: // write in chunks of 32k buffer
1408: char[] buffer = new char[32 * 1024];
1409: int count = 0;
1410:
1411: while ((count = r.read(buffer)) >= 0)
1412: writer.write(buffer, 0, count);
1413: writer.flush();
1414: writer.close();
1415: }
1416: }
1417:
1418: /**
1419: * Class to generate random byte data
1420: */
1421: class RandomByteStream extends java.io.InputStream {
1422: private long length;
1423:
1424: private java.util.Random dpr;
1425:
1426: RandomByteStream(java.util.Random dpr, long length) {
1427: this .length = length;
1428: this .dpr = dpr;
1429:
1430: }
1431:
1432: public int read() {
1433: if (length <= 0)
1434: return -1;
1435:
1436: length--;
1437: return (byte) (dpr.nextInt() >>> 25);
1438: }
1439:
1440: public int read(byte[] data, int off, int len) {
1441:
1442: if (length <= 0)
1443: return -1;
1444:
1445: if (len > length)
1446: len = (int) length;
1447:
1448: for (int i = 0; i < len; i++) {
1449: // chop off bits and return a +ve byte value.
1450: data[off + i] = (byte) (dpr.nextInt() >>> 25);
1451: }
1452:
1453: length -= len;
1454: return len;
1455: }
1456: }
1457:
1458: /*
1459: * Class to generate random char data, generates 1,2,3bytes character.
1460: */
1461: class RandomCharReader extends java.io.Reader {
1462: private long length;
1463: private long numTrailingSpaces;
1464:
1465: private java.util.Random dpr;
1466:
1467: RandomCharReader(java.util.Random dpr, long length) {
1468: this .length = length;
1469: this .dpr = dpr;
1470: this .numTrailingSpaces = 0;
1471: }
1472:
1473: RandomCharReader(java.util.Random dpr, long length,
1474: long numTrailingSpaces) {
1475: this .length = length;
1476: this .dpr = dpr;
1477: this .numTrailingSpaces = numTrailingSpaces;
1478: }
1479:
1480: private int randomInt(int min, int max) {
1481: return dpr.nextInt(max - min) + min;
1482: }
1483:
1484: private char getChar() {
1485: // return space for trailing spaces.
1486: if (length <= numTrailingSpaces) {
1487: return ' ';
1488: }
1489:
1490: double drand = dpr.nextDouble();
1491: char c = 'a';
1492: if (drand < 0.25)
1493: c = (char) randomInt((int) 'A', (int) 'Z');
1494: else if (drand < 0.5)
1495: switch (randomInt(1, 10)) {
1496: case 1:
1497: c = '\u00c0';
1498: break;
1499: case 2:
1500: c = '\u00c1';
1501: break;
1502: case 3:
1503: c = '\u00c2';
1504: break;
1505: case 4:
1506: c = '\u00ca';
1507: break;
1508: case 5:
1509: c = '\u00cb';
1510: break;
1511: case 6:
1512: c = '\u00d4';
1513: break;
1514: case 7:
1515: c = '\u00d8';
1516: break;
1517: case 8:
1518: c = '\u00d1';
1519: break;
1520: case 9:
1521: c = '\u00cd';
1522: break;
1523: default:
1524: c = '\u00dc';
1525: break;
1526: }
1527: else if (drand < 0.75)
1528: c = (char) randomInt((int) 'a', (int) 'z');
1529: else if (drand < 1.0)
1530: switch (randomInt(1, 10)) {
1531: case 1:
1532: c = '\u00e2';
1533: break;
1534: case 2:
1535: c = '\u00e4';
1536: break;
1537: case 3:
1538: c = '\u00e7';
1539: break;
1540: case 4:
1541: c = '\u00e8';
1542: break;
1543: case 5:
1544: c = '\u00ec';
1545: break;
1546: case 6:
1547: c = '\u00ef';
1548: break;
1549: case 7:
1550: c = '\u00f6';
1551: break;
1552: case 8:
1553: c = '\u00f9';
1554: break;
1555: case 9:
1556: c = '\u00fc';
1557: break;
1558: default:
1559: c = '\u00e5';
1560: break;
1561: }
1562:
1563: return c;
1564:
1565: }
1566:
1567: public int read() {
1568: if (length <= 0)
1569: return -1;
1570:
1571: length--;
1572: return getChar();
1573: }
1574:
1575: public int read(char[] data, int off, int len) {
1576:
1577: if (length <= 0)
1578: return -1;
1579:
1580: if (len > length)
1581: len = (int) length;
1582:
1583: for (int i = 0; i < len; i++) {
1584: data[off + i] = getChar();
1585: length -= 1;
1586: }
1587:
1588: return len;
1589: }
1590:
1591: public void close() {
1592:
1593: }
1594: }
1595:
1596: /**
1597: * Class used to simulate a 4GB Clob implementation to
1598: * check whether derby implements such large Clobs correctly.
1599: * Derby throws an error if the clob size exceeds 2GB
1600: **/
1601:
1602: class ClobImpl implements java.sql.Clob {
1603: long length;
1604: Reader myReader;
1605:
1606: public ClobImpl(Reader myReader, long length) {
1607: this .length = length;
1608: this .myReader = myReader;
1609: }
1610:
1611: public long length() throws SQLException {
1612: return length;
1613: }
1614:
1615: public String getSubString(long pos, int length)
1616: throws SQLException {
1617: throw new SQLException("Not implemented");
1618: }
1619:
1620: public java.io.Reader getCharacterStream() throws SQLException {
1621: return myReader;
1622: }
1623:
1624: public java.io.InputStream getAsciiStream() throws SQLException {
1625: throw new SQLException("Not implemented");
1626: }
1627:
1628: public long position(String searchstr, long start)
1629: throws SQLException {
1630: throw new SQLException("Not implemented");
1631: }
1632:
1633: public long position(Clob searchstr, long start)
1634: throws SQLException {
1635: throw new SQLException("Not implemented");
1636: }
1637:
1638: public int setString(long pos, String str) throws SQLException {
1639: throw new SQLException("Not implemented");
1640: }
1641:
1642: public int setString(long pos, String str, int offset, int len)
1643: throws SQLException {
1644: throw new SQLException("Not implemented");
1645: }
1646:
1647: public java.io.OutputStream setAsciiStream(long pos)
1648: throws SQLException {
1649: throw new SQLException("Not implemented");
1650: }
1651:
1652: public java.io.Writer setCharacterStream(long pos)
1653: throws SQLException {
1654: throw new SQLException("Not implemented");
1655: }
1656:
1657: public void truncate(long len) throws SQLException {
1658: throw new SQLException("Not implemented");
1659: }
1660:
1661: public void free() throws SQLException {
1662: throw new SQLException("Not implemented");
1663: }
1664:
1665: public Reader getCharacterStream(long pos, long length)
1666: throws SQLException {
1667: throw new SQLException("Not implemented");
1668: }
1669:
1670: }
1671:
1672: /***
1673: * Class to simulate a 4Gb blob impl in order to test if Derby
1674: * handles such large blobs correctly. The main methods here are
1675: * only the length() and the getBinaryStream(). Rest are just
1676: * placeholders/dummy methods in order to implement the java.sql.Blob
1677: * interface
1678: * ----
1679: * Derby throws an error if the blob length exceeds the max range of
1680: * int.
1681: */
1682: class BlobImpl implements java.sql.Blob {
1683: long length;
1684: InputStream myStream;
1685:
1686: public BlobImpl(InputStream is, long length) {
1687: this .myStream = is;
1688: this .length = length;
1689: }
1690:
1691: public InputStream getBinaryStream() throws SQLException {
1692: return myStream;
1693: }
1694:
1695: public byte[] getBytes() throws SQLException {
1696: throw new SQLException("Not implemented");
1697: }
1698:
1699: public long length() throws SQLException {
1700: return length;
1701: }
1702:
1703: public long position(Blob pattern, long start) throws SQLException {
1704: throw new SQLException("Not implemented");
1705: }
1706:
1707: public long position(byte[] pattern, long start)
1708: throws SQLException {
1709: throw new SQLException("Not implemented");
1710: }
1711:
1712: public OutputStream setBinaryStream(long pos) throws SQLException
1713:
1714: {
1715: throw new SQLException("Not implemented");
1716: }
1717:
1718: public int setBytes(long pos, byte[] bytes) throws SQLException {
1719: throw new SQLException("Not implemented");
1720: }
1721:
1722: public int setBytes(long pos, byte[] bytes, int offset, int len)
1723: throws SQLException {
1724: throw new SQLException("Not implemented");
1725: }
1726:
1727: public void truncate(long len) throws SQLException {
1728: throw new SQLException("Not implemented");
1729: }
1730:
1731: public byte[] getBytes(long pos, int length) throws SQLException {
1732: throw new SQLException("Not implemented");
1733: }
1734:
1735: }
|