You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2005/10/28 14:52:21 UTC
svn commit: r329187 [48/66] - in /db/derby/code/trunk: ./
frameworks/NetworkServer/ frameworks/NetworkServer/bin/
frameworks/embedded/bin/ java/build/ java/build/org/apache/derbyBuild/
java/build/org/apache/derbyBuild/eclipse/ java/build/org/apache/der...
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/LobLimits.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/LobLimits.java?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/LobLimits.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/LobLimits.java Fri Oct 28 04:51:50 2005
@@ -1,1346 +1,1346 @@
-/*
-
-Derby - Class org.apache.derbyTesting.functionTests.tests.largedata.LobLimits
-
-Copyright 2003, 2005 The Apache Software Foundation or its licensors, as applicable.
-
-Licensed under the Apache License, Version 2.0 (the "License");
-you may not use this file except in compliance with the License.
-You may obtain a copy of the License at
-
-http://www.apache.org/licenses/LICENSE-2.0
-
-Unless required by applicable law or agreed to in writing, software
-distributed under the License is distributed on an "AS IS" BASIS,
-WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-See the License for the specific language governing permissions and
-limitations under the License.
-
-*/
-
-package org.apache.derbyTesting.functionTests.tests.largedata;
-
-import java.sql.*;
-import java.io.*;
-
-import org.apache.derby.tools.ij;
-import org.apache.derby.tools.JDBCDisplayUtil;
-
-/**
-* This test is part of the "largedata" suite because this test tests data for
-* lobs to the limits ( ie blob and clob can be 2G-1 maximum) and so this test
-* may take considerable disk space as well as time to run. Hence it is not part
-* of the derbyall suite but should ideally be run at some intervals to test if
-* no regression has occurred.
-*/
-
-public class LobLimits {
-
- static boolean trace = false;
- static final int _2GB = 2 * 1024 * 1024* 1024 - 1;
- static final int _100MB = 100 * 1024 * 1024;
- static final int MORE_DATA_THAN_COL_WIDTH= (_100MB)+1;
- static final int NUM_TRAILING_SPACES = 33*1024;
-
- static PreparedStatement insertBlob = null;
- static PreparedStatement selectBlob = null;
- static PreparedStatement insertClob = null;
- static PreparedStatement selectClob = null;
- static PreparedStatement deleteBlob = null;
- static PreparedStatement deleteClob = null;
- static PreparedStatement insertBlob2 = null;
- static PreparedStatement selectBlob2 = null;
- static PreparedStatement insertClob2 = null;
- static PreparedStatement selectClob2 = null;
- static PreparedStatement deleteBlob2 = null;
- static PreparedStatement deleteClob2 = null;
-
- static final String DATAFILE = "byteLobLimits.dat";
-
- static final String CHARDATAFILE = "charLobLimits.txt";
-
- /**
- * setup prepared statements and schema for the tests
- * @param conn
- * @throws SQLException
- */
- private void setup(Connection conn) throws SQLException {
- System.out.println("-----------------------------------");
- System.out.println(" START setup");
-
- conn.setAutoCommit(true);
- // Create a test table.
- Statement s = conn.createStatement();
- try {
- s.execute("DROP TABLE BLOBTBL");
- } catch (Exception e) {
- }
- try {
- s.execute("DROP TABLE CLOBTBL");
- } catch (Exception e) {
- }
- try {
- s.execute("DROP TABLE BLOBTBL2");
- } catch (Exception e) {
- }
- try {
- s.execute("DROP TABLE CLOBTBL2");
- } catch (Exception e) {
- }
-
- s.execute("CREATE TABLE BLOBTBL (ID INT NOT NULL PRIMARY KEY, "
- + "POS BIGINT, DLEN BIGINT, CONTENT BLOB(2G))");
-
- insertBlob = conn
- .prepareStatement("INSERT INTO BLOBTBL values (?,?,?,?)");
-
- s.execute("CREATE TABLE CLOBTBL (ID INT NOT NULL PRIMARY KEY,"
- + "POS BIGINT, DLEN BIGINT, CONTENT CLOB(2G))");
-
- insertBlob = conn
- .prepareStatement("INSERT INTO BLOBTBL values (?,?,?,?)");
- selectBlob = conn
- .prepareStatement("SELECT CONTENT,DLEN FROM BLOBTBL WHERE ID = ?");
-
- insertClob = conn
- .prepareStatement("INSERT INTO CLOBTBL values (?,?,?,?)");
-
- selectClob = conn
- .prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL WHERE ID = ?");
-
- deleteBlob = conn.prepareStatement("DELETE FROM BLOBTBL");
- deleteClob = conn.prepareStatement("DELETE from CLOBTBL");
-
- s.execute("CREATE TABLE BLOBTBL2 (ID INT NOT NULL PRIMARY KEY, "
- + "POS BIGINT, CONTENT BLOB("+_100MB+"),DLEN BIGINT)");
-
- insertBlob2 = conn
- .prepareStatement("INSERT INTO BLOBTBL2 values (?,?,?,?)");
-
- // Please dont change the clob column width,since tests use this width to
- // test for truncation of trailing spaces.
- s.execute("CREATE TABLE CLOBTBL2 (ID INT NOT NULL PRIMARY KEY,"
- + "POS BIGINT, CONTENT CLOB("+_100MB+"), DLEN BIGINT)");
-
- insertBlob2 = conn
- .prepareStatement("INSERT INTO BLOBTBL2 values (?,?,?,?)");
- selectBlob2 = conn
- .prepareStatement("SELECT CONTENT,DLEN FROM BLOBTBL2 WHERE ID = ?");
-
- insertClob2 = conn
- .prepareStatement("INSERT INTO CLOBTBL2 values (?,?,?,?)");
-
- selectClob2 = conn
- .prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL2 WHERE ID = ?");
- System.out.println("-----------------------------------");
- System.out.println(" END setup");
-
- deleteBlob2 = conn.prepareStatement("DELETE FROM BLOBTBL2");
- deleteClob2 = conn.prepareStatement("DELETE from CLOBTBL2");
- System.out.println("-----------------------------------");
- System.out.println(" END setup");
-
- }
-
- /**
- * Create an instance of this class and do the test.
- */
- public static void main(String[] args) {
- //trace = Boolean.getBoolean("trace");
- new LobLimits().runTests(args);
-
- }
-
- /**
- * Create a JDBC connection using the arguments passed in from the harness,
- * and then run the LOB tests.
- *
- * @param args
- * Arguments from the harness.
- */
- public void runTests(String[] args) {
- Connection conn = null;
- try {
-
- // use the ij utility to read the property file and
- // make the initial connection.
- ij.getPropertyArg(args);
- conn = ij.startJBMS();
-
- // do the initial setup,drop and create tables
- // prepare stmts
- setup(conn);
-
- conn.setAutoCommit(false);
-
- clobTests(conn);
- blobTests(conn);
- //cleanup
- cleanup(conn);
- } catch (Exception e) {
- System.out.println("FAIL -- Unexpected exception:");
- e.printStackTrace(System.out);
- }
- }
-
- /**
- * Close all prepared statements and connection
- * @param conn
- * @throws Exception
- */
- private void cleanup(Connection conn) throws Exception {
- insertBlob.close();
- selectBlob.close();
- selectClob.close();
- insertClob.close();
- deleteClob.close();
- deleteBlob.close();
- insertBlob2.close();
- selectBlob2.close();
- selectClob2.close();
- insertClob2.close();
- deleteBlob2.close();
- deleteClob2.close();
- conn.close();
- new File(DATAFILE).delete();
- new File(CHARDATAFILE).delete();
- }
-
- /**
- * tests specific for blobs
- * @param conn
- * @throws Exception
- */
- private static void blobTests(Connection conn) throws Exception {
-
- try {
- // Test - 2Gb blob ( actually it is 2gb -1)
- // Note with setBinaryStream interface the maximum size for the
- // stream, can be max value for an int.
- // Also note, that lobs in derby currently supports
- // maximum size of 2gb -1
-
- // first do insert blob of 2g, 2 rows
- insertBlob_SetBinaryStream("BlobTest #1", conn, insertBlob, _2GB,
- 0, 2, _2GB);
- // do a select to see if the inserts in test above went ok
- selectBlob("BlobTest #2", conn, selectBlob, _2GB, 0, 1);
- selectBlob("BlobTest #3", conn, selectBlob, _2GB, 1, 1);
-
- // now do a select of one of the 2gb rows and update another 2g row
- // using the setBlob api, updated blob is of length 2gb
- // setBlob,materializes, so disable testfor now.
- // Bug entry -DERBY-599
- //selectUpdateBlob("BlobTest #4",conn,selectBlob,_2GB,0,1,1);
-
- // Test - generate random data, write to a file, use it to insert
- // data into blob and then read back and compare if all is ok
- // currently in fvt ( derbyall), tests check for substrings etc and
- // for small amounts of data. This test will test for 100mb of blob data
-
- FileOutputStream fos = new FileOutputStream(DATAFILE);
- RandomByteStream r = new RandomByteStream(new java.util.Random(),
- _100MB);
- // write in chunks of 32k buffer
- byte[] buffer = new byte[32 * 1024];
- int count = 0;
-
- while((count=r.read(buffer))>=0)
- fos.write(buffer,0,count);
-
- fos.flush();
- fos.close();
-
- insertBlob2("BlobTest #5.1 ", conn, insertBlob2, _100MB, 0, 1,
- _100MB, DATAFILE);
- selectBlob2("BlobTest #5.2 ", conn, selectBlob2, _100MB, 0, 1,
- DATAFILE);
- // update the 2gb row in blobtbl with the 100mb data and compare if the update
- // went ok. wont work now, test disabled currently
- // till DERBY599 is fixed
- //selectUpdateBlob2("BlobTest #6",conn,selectBlob2,selectBlob,_100MB,0,1,1,DATAFILE);
-
- deleteTable(conn, deleteBlob2, 1);
-
- } catch (Exception e) {
- System.out.println("FAIL -- Unexpected exception:");
- e.printStackTrace(System.out);
- }
-
- conn.commit();
-
- deleteTable(conn, deleteBlob, 2);
-
- // ADD NEW TESTS HERE
- }
-
- /**
- * tests using clobs
- * @param conn
- * @throws Exception
- */
- private static void clobTests(Connection conn) throws Exception {
- try {
- // Test - 2Gb blob
- // Note with setCharacterStream interface the maximum size for the
- // stream has to be max value for a int which is (2GB -1 )
- // first do insert clob of 2g, 2 rows
- insertClob_SetCharacterStream("ClobTest #1", conn, insertClob,
- _2GB, 0, 2, _2GB);
- // do a select to see if the inserts in test above went ok
- selectClob("ClobTest #2", conn, selectClob, _2GB, 0, 1);
- selectClob("ClobTest #3", conn, selectClob, _2GB, 0, 1);
- // do a select and then update a row of 2gb size: uses getClob
- selectUpdateClob("ClobTest #4",conn,selectClob,_2GB,0,1,1);
-
-
- // Test - generate random data, write to a file, use it to insert
- // data into clob and then read back and compare if all is ok
- // currently in fvt ( derbyall), tests check for substrings etc and
- // for small amounts of data. This test will test for 100mb of clob data
- writeToFile(CHARDATAFILE,new RandomCharReader(new java.util.Random(),_100MB));
- insertClob2("ClobTest #5.1 ", conn, insertClob2, _100MB, 0, 1,
- _100MB, CHARDATAFILE);
- selectClob2("ClobTest #5.2 ", conn, selectClob2, _100MB, 0, 1,
- CHARDATAFILE);
-
- // Disabled for now, this will materialize, will open
- // jira for it.
- //updateClob2("ClobTest #8.1",conn,selectClob,_100MB,0,0,10,1,CHARDATAFILE);
-
- // update the 2gb row in clobtbl with the 100mb data and compare if the update
- // went ok.
- selectUpdateClob2("ClobTest #8.2",conn,selectClob2,selectClob,_100MB,0,1,1,CHARDATAFILE);
-
- // test for trailing space truncation
- // insert 100mb+33k of data which has 33k of trailing space,
- // into a column of 100mb
- // insert should be successful, select should retrieve 100mb of data
-
- // Generate random data and write to a file, this file will be used
- // in the verification process after inserts and updates.
- writeToFile(CHARDATAFILE,new RandomCharReader(new java.util.Random(),
- (NUM_TRAILING_SPACES +_100MB),NUM_TRAILING_SPACES));
- insertClob2("ClobTest #6.1 ", conn, insertClob2,_100MB, 3, 1,
- (NUM_TRAILING_SPACES +_100MB), CHARDATAFILE);
- // select will retrieve data and verify the data inserted.
- selectClob2("ClobTest #6.2 ", conn, selectClob2, _100MB, 3, 1,
- CHARDATAFILE);
-
- negativeSpaceTruncationTest("ClobTest #7",conn);
-
- // Test - for stream contains a trailing non-space character
- // insert should throw an error
- writeToFile(CHARDATAFILE,new RandomCharReader(new java.util.Random(),MORE_DATA_THAN_COL_WIDTH));
- try
- {
- insertClob2("ClobTest #9.1 ", conn, insertClob2,MORE_DATA_THAN_COL_WIDTH, 4, 1,
- MORE_DATA_THAN_COL_WIDTH, CHARDATAFILE);
- }catch(SQLException sqle)
- {
- System.out.println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed");
- expectedException(sqle);
- }
- // no row must be retrieved.
- selectClob2("ClobTest #9.2 ", conn, selectClob2, _100MB, 4, 0,
- CHARDATAFILE);
-
- try
- {
- insertClob2("ClobTest #10 ", conn, insertClob2,MORE_DATA_THAN_COL_WIDTH, 4, 1,
- MORE_DATA_THAN_COL_WIDTH +1 , CHARDATAFILE);
- }catch(SQLException sqle)
- {
- System.out.println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed and"+
- " stream length is one greater than actual length of the stream ");
- expectedException(sqle);
- }
-
- try
- {
- insertClob2("ClobTest #11 ", conn, insertClob2,MORE_DATA_THAN_COL_WIDTH, 4, 1,
- MORE_DATA_THAN_COL_WIDTH -1 , CHARDATAFILE);
- }catch(SQLException sqle)
- {
- System.out.println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed and"+
- " stream length is one less than actual length of the stream ");
- expectedException(sqle);
- }
- deleteTable(conn, deleteClob2, 2);
- } catch (Exception e) {
- System.out.println("FAIL -- Unexpected exception:");
- e.printStackTrace(System.out);
- }
-
- try {
- // give -ve streamlength
- insertClob_SetCharacterStream("ClobTest #12.1", conn, insertClob,
- _100MB, 4, 1, -1);
- } catch (SQLException sqle) {
- System.out.println("NEGATIVE TEST - Expected Exception:");
- expectedException(sqle);
- }
-
- selectClob("ClobTest #12.2", conn, selectClob,_100MB, 4, 0);
-
- deleteTable(conn, deleteClob, 2);
-
- // ADD NEW TESTS HERE
- }
-
- private static void negativeSpaceTruncationTest(String msg,Connection conn)
- throws Exception
- {
- // Negative test, stream has trailing spaces but the stream length is one
- // more than the actual length of the stream
- try
- {
- insertClob2(msg, conn, insertClob2,_100MB, 4, 1,
- (NUM_TRAILING_SPACES +_100MB - 1), CHARDATAFILE);
- }catch(SQLException sqle)
- {
- System.out.println("EXPECTED EXCEPTION - stream has trailing spaces,but stream "+
- " length is 1 less than actual length of stream");
- expectedException(sqle);
- }
-
- try
- {
- insertClob2(msg, conn, insertClob2,_100MB, 5, 1,
- (NUM_TRAILING_SPACES +_100MB + 1), CHARDATAFILE);
- }catch(SQLException sqle)
- {
- System.out.println("EXPECTED EXCEPTION - stream has trailing spaces,but stream "+
- " length is 1 greater than actual length of stream");
- expectedException(sqle);
- }
- }
-
-
- /**
- * insert blob
- * @param bloblen length of blob to insert
- * @param start start id value for insert
- * @param rows insert rows number of rows
- * @param streamLength stream length passed to setBinaryStream(,,length)
- */
- private static void insertBlob_SetBinaryStream(String testId,
- Connection conn, PreparedStatement ps, int bloblen, int start,
- int rows, int streamLength) throws SQLException {
- System.out.println("========================================");
- System.out.println("START " + testId + "insertBlob of size = "
- + bloblen);
- long ST = 0;
- if (trace)
- ST = System.currentTimeMillis();
-
- int count = 0;
- java.util.Random random = new java.util.Random();
- for (int i = start; i < start + rows; i++) {
- ps.setInt(1, i);
- ps.setInt(2, 0);
- ps.setLong(3, bloblen);
- ps.setBinaryStream(4, new RandomByteStream(random, bloblen),
- streamLength);
- count += ps.executeUpdate();
- }
- conn.commit();
- if (trace) {
- System.out.println("Insert Blob (" + bloblen + ")" + " rows= "
- + count + " = " + (long) (System.currentTimeMillis() - ST));
-
- }
- verifyTest(count, rows, " Rows inserted with blob of size (" + bloblen
- + ") =");
- System.out.println("========================================");
-
- }
-
- /**
- * select from blob table (BLOBTBL)
- * @param bloblen select expects to retrieve a blob of this length
- * @param id id of the row to retrieve
- * @param expectedRows number of rows expected to match id
- */
- private static void selectBlob(String testId, Connection conn,
- PreparedStatement ps, int bloblen, int id, int expectedRows)
- throws SQLException {
- System.out.println("========================================");
- System.out.println("START " + testId + " - SELECT BLOB of size = "
- + bloblen);
-
- long ST = 0;
- ResultSet rs = null;
-
- if (trace)
- ST = System.currentTimeMillis();
-
- int count = 0;
- ps.setInt(1, id);
- rs = ps.executeQuery();
-
- while (rs.next()) {
- count++;
- Blob value = rs.getBlob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
- + " expected " + dlen + " for row in BLOBTBL with ID="
- + id);
- }
- }
- conn.commit();
-
- verifyTest(count, expectedRows,
- "Matched rows selected with blob of size(" + bloblen + ") =");
-
- if (trace) {
- System.out.println("Select Blob (" + bloblen + ")" + " rows= "
- + expectedRows + " = "
- + (long) (System.currentTimeMillis() - ST));
- }
- System.out.println("========================================");
- }
-
- /**
- * insert blob into BLOBTBL2
- * @param bloblen length of blob to insert
- * @param start id value for insert
- * @param rows insert rows number of rows
- * @param streamLength stream length passed to setBinaryStream(,,length)
- * @param file filename to match retrieved data against
- */
-
- private static void insertBlob2(String testId, Connection conn,
- PreparedStatement ps, int bloblen, int start, int rows,
- int streamLength, String file) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + "insert Blob of size = "
- + bloblen);
- int count = 0;
- java.util.Random random = new java.util.Random();
- FileInputStream fis = null;
-
- long ST = 0;
- if (trace)
- ST = System.currentTimeMillis();
-
- for (int i = start; i < start + rows; i++) {
- fis = new FileInputStream(file);
- ps.setInt(1, i);
- ps.setInt(2, 0);
- ps.setLong(4, bloblen);
- ps.setBinaryStream(3, fis, streamLength);
- count += ps.executeUpdate();
- fis.close();
- }
- conn.commit();
- if (trace) {
- System.out.println("Insert Blob (" + bloblen + ")" + " rows= "
- + count + " = " + (long) (System.currentTimeMillis() - ST));
-
- }
- verifyTest(count, rows, " Rows inserted with blob of size (" + bloblen
- + ") =");
- System.out.println("========================================");
-
- }
-
- /**
- * select from blob table (BLOBTBL2)
- * @param bloblen select expects to retrieve a blob of this length
- * @param id id of the row to retrieve
- * @param expectedRows number of rows expected to match id
- * @param file name of the file,against which the retrieved data is
- * compared
- */
- private static void selectBlob2(String testId, Connection conn,
- PreparedStatement ps, int bloblen, int id, int expectedRows,
- String file) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + " - SELECT BLOB of size = "
- + bloblen);
-
- long ST = 0;
- ResultSet rs = null;
-
- if (trace)
- ST = System.currentTimeMillis();
-
- int count = 0;
- ps.setInt(1, id);
- rs = ps.executeQuery();
-
- while (rs.next()) {
- count++;
- Blob value = rs.getBlob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
- + " expected " + dlen + " for row in BLOBTBL with ID="
- + id);
- } else
- compareBlobToFile(value.getBinaryStream(), file);
- }
- conn.commit();
-
- verifyTest(count, expectedRows,
- "Matched rows selected with blob of size(" + bloblen + ") =");
-
- if (trace) {
- System.out.println("Select Blob (" + bloblen + ")" + " rows= "
- + expectedRows + " = "
- + (long) (System.currentTimeMillis() - ST));
- }
- System.out.println("========================================");
- }
-
- /**
- * Basically this test will do an update using setBlob api -
- * select row from blobtbl and then update a row in blobtbl
- * and verify updated data in blobtbl
- * @param ps select statement from which blob is retrieved
- * @param bloblen updating value is of length bloblen
- * @param id id of the row retrieved, for the update
- * @param updateId id of the row that is updated
- * @param expectedRows to be updated
- */
- private static void selectUpdateBlob(String testId, Connection conn,
- PreparedStatement ps, int bloblen, int id, int updateId,
- int expectedRows) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + " - select and then update blob of size= "
- + bloblen + " - Uses getBlob api");
-
- ResultSet rs = null;
-
- ps.setInt(1, id);
- rs = ps.executeQuery();
- rs.next();
- Blob value = rs.getBlob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out
- .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
- + dlen + " for row in BLOBTBL with ID=" + id);
- }
-
- PreparedStatement psUpd = conn
- .prepareStatement("update BLOBTBL set content=?,dlen =? where id = ?");
- psUpd.setBlob(1,value);
- psUpd.setLong(2, l);
- psUpd.setInt(3, updateId);
-
- System.out.println("Rows Updated = " + psUpd.executeUpdate());
- conn.commit();
-
- // now select and verify that update went through ok.
- ps.setInt(1, updateId);
- ResultSet rs2 = ps.executeQuery();
- rs2.next();
- Blob updatedValue = rs2.getBlob(1);
-
- if(updatedValue.length() != l)
- System.out.println("FAIL - Retrieving the updated blob length does not match "+
- "expected length = "+l +" found = "+ updatedValue.length());
-
- // close resultsets
- conn.commit();
- rs.close();
- rs2.close();
- psUpd.close();
- System.out.println("========================================");
- }
-
- /**
- * Basically this test will do an update using setBinaryStream api and verifies the
- * updated data. select row from blobtbl2 and then update a row in blobtbl
- * and verify updated data in blobtbl
- * @param bloblen updating value is of length bloblen
- * @param id id of the row retrieved, for the update
- * @param updateId id of the row that is updated
- * @param expectedRows to be updated
- * @param file name of the file,against which the updated data is
- * compared
- */
- private static void selectUpdateBlob2(String testId, Connection conn,
- PreparedStatement ps,PreparedStatement sel,int bloblen, int id, int updateId,
- int expectedRows,String file) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + " - select and then update blob of size= "
- + bloblen + " - Uses getBlob and setBlob api");
-
- ResultSet rs = null;
-
- // retrieve row from blobtbl2
- ps.setInt(1, id);
- rs = ps.executeQuery();
- rs.next();
- Blob value = rs.getBlob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out
- .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
- + dlen + " for row in BLOBTBL2 with ID=" + id);
- }
-
- PreparedStatement psUpd = conn
- .prepareStatement("update BLOBTBL set content=?,dlen =? where id = ?");
- psUpd.setBlob(1,value);
- psUpd.setLong(2, l);
- psUpd.setInt(3, updateId);
-
- System.out.println("Rows Updated = " + psUpd.executeUpdate());
- conn.commit();
-
- // now select and verify that update went through ok.
- sel.setInt(1, updateId);
- ResultSet rs2 = sel.executeQuery();
- rs2.next();
- Blob updatedValue = rs2.getBlob(1);
-
- if (updatedValue.length() != l)
- {
- System.out.println("FAIL - MISMATCH length of updated blob value : expected="+
- l+" found ="+updatedValue.length());
- }
- else
- compareBlobToFile(updatedValue.getBinaryStream(),file);
-
- // close resultsets
- conn.commit();
- rs.close();
- rs2.close();
- psUpd.close();
- System.out.println("========================================");
-
- }
-
- private static void compareBlobToFile(InputStream lobstream, String filename)
- throws Exception {
- FileInputStream file = new FileInputStream(filename);
- int l = 0;
- int b = 0;
- do {
- l = lobstream.read();
- b = file.read();
- if (l != b)
- {
- System.out.println("FAIL -- MISMATCH in data stored versus"+
- "data retrieved");
- break;
- }
- } while (l != -1 && b != -1);
- }
-
- private static void deleteTable(Connection conn, PreparedStatement ps,
- int expectedRows) throws SQLException {
- int count = ps.executeUpdate();
- conn.commit();
- verifyTest(count, expectedRows, "Rows deleted =");
- }
-
-
- /**
- * insert clob
- * @param cloblen length of clob to insert
- * @param start id value for insert
- * @param rows insert rows number of rows
- * @param streamLength stream length passed to setCharacterStream(...,length)
- */
- private static void insertClob_SetCharacterStream(String testId,
- Connection conn, PreparedStatement ps, int cloblen, int start,
- int rows, int streamLength) throws SQLException {
- System.out.println("========================================");
- System.out.println("START " + testId + " -insertClob of size = "
- + cloblen);
-
- long ST = 0;
- java.util.Random random = new java.util.Random();
- int count = 0;
- if (trace)
- ST = System.currentTimeMillis();
-
- for (int i = start; i < start + rows; i++) {
- ps.setInt(1, i);
- ps.setInt(2, 0);
- ps.setLong(3, cloblen);
- ps.setCharacterStream(4, new RandomCharReader(random, cloblen),
- streamLength);
- count += ps.executeUpdate();
- }
- conn.commit();
- if (trace) {
- System.out.println("Insert Clob (" + cloblen + ")" + " rows= "
- + count + " = " + (long) (System.currentTimeMillis() - ST));
-
- }
- verifyTest(count, rows, "Rows inserted with clob of size (" + cloblen
- + ") = ");
- System.out.println("========================================");
-
- }
-
- /**
- * select from clob table
- * @param cloblen select expects to retrieve a clob of this length
- * @param id id of the row to retrieve
- * @param expectedRows number of rows expected to match id
- */
- private static void selectClob(String testId, Connection conn,
- PreparedStatement ps, int cloblen, int id, int expectedRows)
- throws SQLException {
- System.out.println("========================================");
- System.out.println("START " + testId + " - SELECT CLOB of size = "
- + cloblen);
-
- long ST = 0;
- int count = 0;
- ResultSet rs = null;
- if (trace)
- ST = System.currentTimeMillis();
-
- ps.setInt(1, id);
- rs = ps.executeQuery();
- while (rs.next()) {
- count++;
- Clob value = rs.getClob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
- + " expected " + dlen + " for row in CLOBTBL with ID="
- + id);
- }
-
- }
- conn.commit();
- if (trace) {
- System.out.println("Select Clob (" + cloblen + ")" + " rows= "
- + expectedRows + " = "
- + (long) (System.currentTimeMillis() - ST));
-
- }
-
- verifyTest(count, expectedRows,
- "Matched rows selected with clob of size(" + cloblen + ") =");
- System.out.println("========================================");
-
- }
-
- /**
- * insert clob into CLOBTBL2
- * @param cloblen length of clob to insert
- * @param start id value for insert
- * @param rows insert rows number of rows
- * @param streamLength stream length passed to setCharacterStream(pos,reader,streamLength)
- * @param file name of the file that has data to be inserted
- */
- private static void insertClob2(String testId, Connection conn,
- PreparedStatement ps, int cloblen, int start, int rows,
- int streamLength, String file) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + "insert Clob of size = "
- + cloblen);
- int count = 0;
- FileReader reader = null;
- long ST = 0;
- if (trace)
- ST = System.currentTimeMillis();
-
- for (int i = start; i < start + rows; i++) {
- reader = new FileReader(file);
- ps.setInt(1, i);
- ps.setInt(2, 0);
- ps.setLong(4, cloblen);
- ps.setCharacterStream(3, reader, streamLength);
- count += ps.executeUpdate();
- reader.close();
- }
- conn.commit();
- if (trace) {
- System.out.println("Insert Clob (" + cloblen + ")" + " rows= "
- + count + " = " + (long) (System.currentTimeMillis() - ST));
-
- }
- verifyTest(count, rows, " Rows inserted with clob of size (" + cloblen
- + ") =");
- System.out.println("========================================");
-
- }
-
- /**
- * select from clob table (CLOBTBL2)
- * @param cloblen select expects to retrieve a clob of this length
- * @param id id of the row to retrieve
- * @param expectedRows number of rows expected to match id
- * @param file filename to compare the retrieved data against
- */
- private static void selectClob2(String testId, Connection conn,
- PreparedStatement ps, int cloblen, int id, int expectedRows,
- String file) throws SQLException, Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + " - SELECT CLOB of size = "
- + cloblen);
-
- long ST = 0;
- ResultSet rs = null;
-
- if (trace)
- ST = System.currentTimeMillis();
-
- int count = 0;
- ps.setInt(1, id);
- rs = ps.executeQuery();
-
- while (rs.next()) {
- count++;
- Clob value = rs.getClob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (cloblen != l) {
- System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
- + " expected " + dlen + " for row in CLOBTBL2 with ID="
- + id);
- } else
- compareClobToFile(value.getCharacterStream(), file, cloblen);
- }
- conn.commit();
-
- verifyTest(count, expectedRows,
- "Matched rows selected with clob of size(" + cloblen + ") =");
-
- if (trace) {
- System.out.println("Select Clob (" + cloblen + ")" + " rows= "
- + expectedRows + " = "
- + (long) (System.currentTimeMillis() - ST));
- }
- System.out.println("========================================");
- }
-
- /*
- * Basically this test will do an update using setClob api -
- * select row from clobtbl and then update a row in clobtbl
- * and verify updated data in clobtbl
- */
- private static void selectUpdateClob(String testId, Connection conn,
- PreparedStatement ps, int cloblen, int id, int updateId,
- int expectedRows) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + " - select and then update clob of size= "
- + cloblen + " - Uses setClob api");
-
- ResultSet rs = null;
-
- ps.setInt(1, id);
- rs = ps.executeQuery();
- rs.next();
- Clob value = rs.getClob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out
- .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
- + dlen + " for row in CLOBTBL with ID=" + id);
- }
-
- PreparedStatement psUpd = conn
- .prepareStatement("update CLOBTBL set content=?,dlen =? where id = ?");
- psUpd.setCharacterStream(1, value.getCharacterStream(), (int) l);
- psUpd.setLong(2, l);
- psUpd.setInt(3, updateId);
-
- System.out.println("Rows Updated = " + psUpd.executeUpdate());
- conn.commit();
-
- // now select and verify that update went through ok.
- ps.setInt(1, updateId);
- ResultSet rs2 = ps.executeQuery();
- rs2.next();
- Clob updatedValue = rs2.getClob(1);
-
- if(updatedValue.length() != l)
- System.out.println("FAIL - Retrieving the updated clob length does not match "+
- "expected length = "+l +" found = "+ updatedValue.length());
-
- // close resultsets
- conn.commit();
- rs.close();
- rs2.close();
- psUpd.close();
- System.out.println("========================================");
- }
-
-
- /*
- * Basically this test will do an update using setBlob api and verifies the
- * updated data. select row from clobtbl2 and then update a row in clobtbl
- * and verify updated data in clobtbl against the data in the original file
- */
- private static void selectUpdateClob2(String testId, Connection conn,
- PreparedStatement ps, PreparedStatement sel,int cloblen, int id, int updateId,
- int expectedRows,String file) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + " - select and then update clob of size= "
- + cloblen + " - Uses setClob api");
-
- ResultSet rs = null;
-
- // retrieve row from clobtbl2
- ps.setInt(1, id);
- rs = ps.executeQuery();
- rs.next();
- Clob value = rs.getClob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out
- .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
- + dlen + " for row in CLOBTBL2 with ID=" + id);
- }
-
- PreparedStatement psUpd = conn
- .prepareStatement("update CLOBTBL set content=?,dlen =? where id = ?");
- psUpd.setClob(1,value);
- psUpd.setLong(2, l);
- psUpd.setInt(3, updateId);
-
- System.out.println("Rows Updated = " + psUpd.executeUpdate());
- conn.commit();
-
- // now select and verify that update went through ok.
- sel.setInt(1, updateId);
- ResultSet rs2 = sel.executeQuery();
- rs2.next();
- Clob updatedValue = rs2.getClob(1);
-
- if (updatedValue.length() != l)
- {
- System.out.println("FAIL - MISMATCH length of updated clob value , found="+
- updatedValue.length() +",expected = "+l);
- }
- else
- compareClobToFile(updatedValue.getCharacterStream(),file,(int)l);
-
- // close resultsets
- conn.commit();
- rs.close();
- rs2.close();
- psUpd.close();
- System.out.println("========================================");
-
- }
-
- /*
- * Basically this test will do an update using updateClob api and verifies the
- * updated data. select row from clobtbl2 and then update a row in clobtbl
- * and verify updated data in clobtbl against the data in the original file
- * @param updateRowId id of the row that needs to be updated
- */
- private static void updateClob2(String testId, Connection conn,PreparedStatement sel,
- int cloblen, int id, int updateRowId,int updateIdVal,
- int expectedRows,String file) throws Exception {
- System.out.println("========================================");
- System.out.println("START " + testId + " - select and then update clob of size= "
- + cloblen + " - Uses updateClob api");
-
-
- PreparedStatement ps1 = conn.prepareStatement("SELECT * FROM CLOBTBL FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
- PreparedStatement ps = conn.prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL2 where ID =?");
-
- ResultSet rs = null;
- ps.setInt(1,id);
- // retrieve row from clobtbl2
- rs = ps.executeQuery();
- rs.next();
- Clob value = rs.getClob(1);
- long l = value.length();
- long dlen = rs.getLong(2);
- if (dlen != l) {
- System.out
- .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
- + dlen + " for row in CLOBTBL2 with ID=" + id);
- }
-
- ResultSet rs1 = ps1.executeQuery();
- while (rs1.next()) {
- if (rs1.getInt(1) == updateRowId)
- {
- rs1.updateClob(4, value);
- rs1.updateInt(1, updateIdVal);
- rs1.updateInt(2, 0);
- rs1.updateLong(3, dlen);
- rs1.updateRow();
- break;
- }
- }
- // close resultsets
- conn.commit();
- rs.close();
- rs1.close();
- ps1.close();
- ps.close();
-
- // verify
- // now select and verify that update went through ok.
- sel.setInt(1, updateIdVal);
- ResultSet rs2 = sel.executeQuery();
- rs2.next();
- Clob updatedValue = rs2.getClob(1);
-
- if (updatedValue.length() != l)
- {
- System.out.println("FAIL - MISMATCH length of updated clob value , found="+
- updatedValue.length() +",expected = "+l);
- }
- else
- compareClobToFile(updatedValue.getCharacterStream(),file,(int)l);
-
-
- System.out.println("========================================");
-
- }
-
-
- private static void compareClobToFile(Reader lobstream, String filename,int length)
- throws Exception {
- FileReader file = new FileReader(filename);
- int c1 = 0;
- int c2 = 0;
- long count = 0;
- do {
- c1 = lobstream.read();
- c2 = file.read();
- if (c1 != c2)
- {
- System.out.println("FAIL -- MISMATCH in data stored versus data retrieved at " + count);
- break;
- }
- count++;
- length--;
- } while (c1 != -1 && c2 != -1 && length > 0);
- }
-
- private static void expectedException(SQLException sqle) {
-
- while (sqle != null) {
- String sqlState = sqle.getSQLState();
- if (sqlState == null) {
- sqlState = "<NULL>";
- }
- System.out.println("EXPECTED SQL Exception: (" + sqlState + ") "
- + sqle.getMessage());
-
- sqle = sqle.getNextException();
- }
- }
-
- private static void verifyTest(int affectedRows, int expectedRows,
- String test) {
- if (affectedRows != expectedRows)
- System.out.println("FAIL --" + test + affectedRows
- + " , but expected rows =" + expectedRows);
- else
- System.out.println(test + affectedRows);
- }
-
- private static void writeToFile(String file,Reader r)
- throws IOException
- {
- // does file exist, if so delete and write to a fresh file
- File f =new File(file);
- if (f.exists())
- f.delete();
- FileWriter writer = new FileWriter(file);
- // write in chunks of 32k buffer
- char[] buffer = new char[32 * 1024];
- int count = 0;
-
- while((count = r.read(buffer)) >=0)
- writer.write(buffer,0,count);
- writer.flush();
- writer.close();
- }
-}
-
-/**
- * Class to generate random byte data
- */
-class RandomByteStream extends java.io.InputStream {
- private int length;
-
- private java.util.Random dpr;
-
- RandomByteStream(java.util.Random dpr, int length) {
- this.length = length;
- this.dpr = dpr;
-
- }
-
- public int read() {
- if (length <= 0)
- return -1;
-
- length--;
- return (byte) (dpr.nextInt() >>> 25);
- }
-
- public int read(byte[] data, int off, int len) {
-
- if (length <= 0)
- return -1;
-
- if (len > length)
- len = length;
-
- for (int i = 0; i < len; i++) {
- // chop off bits and return a +ve byte value.
- data[off + i] = (byte) (dpr.nextInt() >>> 25);
- }
-
- length -= len;
- return len;
- }
-}
-
-/*
- * Class to generate random char data, generates 1,2,3bytes character.
- */
-class RandomCharReader extends java.io.Reader {
- private int length;
- private int numTrailingSpaces;
-
- private java.util.Random dpr;
-
- RandomCharReader(java.util.Random dpr, int length) {
- this.length = length;
- this.dpr = dpr;
- this.numTrailingSpaces = 0;
- }
-
- RandomCharReader(java.util.Random dpr, int length,int numTrailingSpaces) {
- this.length = length;
- this.dpr = dpr;
- this.numTrailingSpaces = numTrailingSpaces;
- }
-
- private int randomInt(int min, int max) {
- return dpr.nextInt(max - min) + min;
- }
-
- private char getChar() {
- // return space for trailing spaces.
- if (length <= numTrailingSpaces)
- {
- return ' ';
- }
-
- double drand = dpr.nextDouble();
- char c = 'a';
- if (drand < 0.25)
- c = (char) randomInt((int) 'A', (int) 'Z');
- else if (drand < 0.5)
- switch (randomInt(1, 10)) {
- case 1:
- c = '\u00c0';
- break;
- case 2:
- c = '\u00c1';
- break;
- case 3:
- c = '\u00c2';
- break;
- case 4:
- c = '\u00ca';
- break;
- case 5:
- c = '\u00cb';
- break;
- case 6:
- c = '\u00d4';
- break;
- case 7:
- c = '\u00d8';
- break;
- case 8:
- c = '\u00d1';
- break;
- case 9:
- c = '\u00cd';
- break;
- default:
- c = '\u00dc';
- break;
- }
- else if (drand < 0.75)
- c = (char) randomInt((int) 'a', (int) 'z');
- else if (drand < 1.0)
- switch (randomInt(1, 10)) {
- case 1:
- c = '\u00e2';
- break;
- case 2:
- c = '\u00e4';
- break;
- case 3:
- c = '\u00e7';
- break;
- case 4:
- c = '\u00e8';
- break;
- case 5:
- c = '\u00ec';
- break;
- case 6:
- c = '\u00ef';
- break;
- case 7:
- c = '\u00f6';
- break;
- case 8:
- c = '\u00f9';
- break;
- case 9:
- c = '\u00fc';
- break;
- default:
- c = '\u00e5';
- break;
- }
-
- return c;
-
- }
-
- public int read() {
- if (length <= 0)
- return -1;
-
- length--;
- return getChar();
- }
-
- public int read(char[] data, int off, int len) {
-
- if (length <= 0)
- return -1;
-
- if (len > length)
- len = length;
-
- for (int i = 0; i < len; i++) {
- data[off + i] = getChar();
- length -= 1;
- }
-
- return len;
- }
-
- public void close() {
-
- }
-}
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.largedata.LobLimits
+
+Copyright 2003, 2005 The Apache Software Foundation or its licensors, as applicable.
+
+Licensed under the Apache License, Version 2.0 (the "License");
+you may not use this file except in compliance with the License.
+You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyTesting.functionTests.tests.largedata;
+
+import java.sql.*;
+import java.io.*;
+
+import org.apache.derby.tools.ij;
+import org.apache.derby.tools.JDBCDisplayUtil;
+
+/**
+* This test is part of the "largedata" suite because this test tests data for
+* lobs to the limits ( ie blob and clob can be 2G-1 maximum) and so this test
+* may take considerable disk space as well as time to run. Hence it is not part
+* of the derbyall suite but should ideally be run at some intervals to test if
+* no regression has occurred.
+*/
+
+public class LobLimits {
+
+ static boolean trace = false;
+ static final int _2GB = 2 * 1024 * 1024* 1024 - 1;
+ static final int _100MB = 100 * 1024 * 1024;
+ static final int MORE_DATA_THAN_COL_WIDTH= (_100MB)+1;
+ static final int NUM_TRAILING_SPACES = 33*1024;
+
+ static PreparedStatement insertBlob = null;
+ static PreparedStatement selectBlob = null;
+ static PreparedStatement insertClob = null;
+ static PreparedStatement selectClob = null;
+ static PreparedStatement deleteBlob = null;
+ static PreparedStatement deleteClob = null;
+ static PreparedStatement insertBlob2 = null;
+ static PreparedStatement selectBlob2 = null;
+ static PreparedStatement insertClob2 = null;
+ static PreparedStatement selectClob2 = null;
+ static PreparedStatement deleteBlob2 = null;
+ static PreparedStatement deleteClob2 = null;
+
+ static final String DATAFILE = "byteLobLimits.dat";
+
+ static final String CHARDATAFILE = "charLobLimits.txt";
+
+ /**
+ * setup prepared statements and schema for the tests
+ * @param conn
+ * @throws SQLException
+ */
+ private void setup(Connection conn) throws SQLException {
+ System.out.println("-----------------------------------");
+ System.out.println(" START setup");
+
+ conn.setAutoCommit(true);
+ // Create a test table.
+ Statement s = conn.createStatement();
+ try {
+ s.execute("DROP TABLE BLOBTBL");
+ } catch (Exception e) {
+ }
+ try {
+ s.execute("DROP TABLE CLOBTBL");
+ } catch (Exception e) {
+ }
+ try {
+ s.execute("DROP TABLE BLOBTBL2");
+ } catch (Exception e) {
+ }
+ try {
+ s.execute("DROP TABLE CLOBTBL2");
+ } catch (Exception e) {
+ }
+
+ s.execute("CREATE TABLE BLOBTBL (ID INT NOT NULL PRIMARY KEY, "
+ + "POS BIGINT, DLEN BIGINT, CONTENT BLOB(2G))");
+
+ insertBlob = conn
+ .prepareStatement("INSERT INTO BLOBTBL values (?,?,?,?)");
+
+ s.execute("CREATE TABLE CLOBTBL (ID INT NOT NULL PRIMARY KEY,"
+ + "POS BIGINT, DLEN BIGINT, CONTENT CLOB(2G))");
+
+ insertBlob = conn
+ .prepareStatement("INSERT INTO BLOBTBL values (?,?,?,?)");
+ selectBlob = conn
+ .prepareStatement("SELECT CONTENT,DLEN FROM BLOBTBL WHERE ID = ?");
+
+ insertClob = conn
+ .prepareStatement("INSERT INTO CLOBTBL values (?,?,?,?)");
+
+ selectClob = conn
+ .prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL WHERE ID = ?");
+
+ deleteBlob = conn.prepareStatement("DELETE FROM BLOBTBL");
+ deleteClob = conn.prepareStatement("DELETE from CLOBTBL");
+
+ s.execute("CREATE TABLE BLOBTBL2 (ID INT NOT NULL PRIMARY KEY, "
+ + "POS BIGINT, CONTENT BLOB("+_100MB+"),DLEN BIGINT)");
+
+ insertBlob2 = conn
+ .prepareStatement("INSERT INTO BLOBTBL2 values (?,?,?,?)");
+
+ // Please dont change the clob column width,since tests use this width to
+ // test for truncation of trailing spaces.
+ s.execute("CREATE TABLE CLOBTBL2 (ID INT NOT NULL PRIMARY KEY,"
+ + "POS BIGINT, CONTENT CLOB("+_100MB+"), DLEN BIGINT)");
+
+ insertBlob2 = conn
+ .prepareStatement("INSERT INTO BLOBTBL2 values (?,?,?,?)");
+ selectBlob2 = conn
+ .prepareStatement("SELECT CONTENT,DLEN FROM BLOBTBL2 WHERE ID = ?");
+
+ insertClob2 = conn
+ .prepareStatement("INSERT INTO CLOBTBL2 values (?,?,?,?)");
+
+ selectClob2 = conn
+ .prepareStatement("SELECT CONTENT,DLEN FROM CLOBTBL2 WHERE ID = ?");
+ System.out.println("-----------------------------------");
+ System.out.println(" END setup");
+
+ deleteBlob2 = conn.prepareStatement("DELETE FROM BLOBTBL2");
+ deleteClob2 = conn.prepareStatement("DELETE from CLOBTBL2");
+ System.out.println("-----------------------------------");
+ System.out.println(" END setup");
+
+ }
+
+ /**
+ * Create an instance of this class and do the test.
+ */
+ public static void main(String[] args) {
+ //trace = Boolean.getBoolean("trace");
+ new LobLimits().runTests(args);
+
+ }
+
+ /**
+ * Create a JDBC connection using the arguments passed in from the harness,
+ * and then run the LOB tests.
+ *
+ * @param args
+ * Arguments from the harness.
+ */
+ public void runTests(String[] args) {
+ Connection conn = null;
+ try {
+
+ // use the ij utility to read the property file and
+ // make the initial connection.
+ ij.getPropertyArg(args);
+ conn = ij.startJBMS();
+
+ // do the initial setup,drop and create tables
+ // prepare stmts
+ setup(conn);
+
+ conn.setAutoCommit(false);
+
+ clobTests(conn);
+ blobTests(conn);
+ //cleanup
+ cleanup(conn);
+ } catch (Exception e) {
+ System.out.println("FAIL -- Unexpected exception:");
+ e.printStackTrace(System.out);
+ }
+ }
+
+ /**
+ * Close all prepared statements and connection
+ * @param conn
+ * @throws Exception
+ */
+ private void cleanup(Connection conn) throws Exception {
+ insertBlob.close();
+ selectBlob.close();
+ selectClob.close();
+ insertClob.close();
+ deleteClob.close();
+ deleteBlob.close();
+ insertBlob2.close();
+ selectBlob2.close();
+ selectClob2.close();
+ insertClob2.close();
+ deleteBlob2.close();
+ deleteClob2.close();
+ conn.close();
+ new File(DATAFILE).delete();
+ new File(CHARDATAFILE).delete();
+ }
+
+ /**
+ * tests specific for blobs
+ * @param conn
+ * @throws Exception
+ */
+ private static void blobTests(Connection conn) throws Exception {
+
+ try {
+ // Test - 2Gb blob ( actually it is 2gb -1)
+ // Note with setBinaryStream interface the maximum size for the
+ // stream, can be max value for an int.
+ // Also note, that lobs in derby currently supports
+ // maximum size of 2gb -1
+
+ // first do insert blob of 2g, 2 rows
+ insertBlob_SetBinaryStream("BlobTest #1", conn, insertBlob, _2GB,
+ 0, 2, _2GB);
+ // do a select to see if the inserts in test above went ok
+ selectBlob("BlobTest #2", conn, selectBlob, _2GB, 0, 1);
+ selectBlob("BlobTest #3", conn, selectBlob, _2GB, 1, 1);
+
+ // now do a select of one of the 2gb rows and update another 2g row
+ // using the setBlob api, updated blob is of length 2gb
+ // setBlob,materializes, so disable testfor now.
+ // Bug entry -DERBY-599
+ //selectUpdateBlob("BlobTest #4",conn,selectBlob,_2GB,0,1,1);
+
+ // Test - generate random data, write to a file, use it to insert
+ // data into blob and then read back and compare if all is ok
+ // currently in fvt ( derbyall), tests check for substrings etc and
+ // for small amounts of data. This test will test for 100mb of blob data
+
+ FileOutputStream fos = new FileOutputStream(DATAFILE);
+ RandomByteStream r = new RandomByteStream(new java.util.Random(),
+ _100MB);
+ // write in chunks of 32k buffer
+ byte[] buffer = new byte[32 * 1024];
+ int count = 0;
+
+ while((count=r.read(buffer))>=0)
+ fos.write(buffer,0,count);
+
+ fos.flush();
+ fos.close();
+
+ insertBlob2("BlobTest #5.1 ", conn, insertBlob2, _100MB, 0, 1,
+ _100MB, DATAFILE);
+ selectBlob2("BlobTest #5.2 ", conn, selectBlob2, _100MB, 0, 1,
+ DATAFILE);
+ // update the 2gb row in blobtbl with the 100mb data and compare if the update
+ // went ok. wont work now, test disabled currently
+ // till DERBY599 is fixed
+ //selectUpdateBlob2("BlobTest #6",conn,selectBlob2,selectBlob,_100MB,0,1,1,DATAFILE);
+
+ deleteTable(conn, deleteBlob2, 1);
+
+ } catch (Exception e) {
+ System.out.println("FAIL -- Unexpected exception:");
+ e.printStackTrace(System.out);
+ }
+
+ conn.commit();
+
+ deleteTable(conn, deleteBlob, 2);
+
+ // ADD NEW TESTS HERE
+ }
+
+ /**
+ * tests using clobs
+ * @param conn
+ * @throws Exception
+ */
+ private static void clobTests(Connection conn) throws Exception {
+ try {
+ // Test - 2Gb blob
+ // Note with setCharacterStream interface the maximum size for the
+ // stream has to be max value for a int which is (2GB -1 )
+ // first do insert clob of 2g, 2 rows
+ insertClob_SetCharacterStream("ClobTest #1", conn, insertClob,
+ _2GB, 0, 2, _2GB);
+ // do a select to see if the inserts in test above went ok
+ selectClob("ClobTest #2", conn, selectClob, _2GB, 0, 1);
+ selectClob("ClobTest #3", conn, selectClob, _2GB, 0, 1);
+ // do a select and then update a row of 2gb size: uses getClob
+ selectUpdateClob("ClobTest #4",conn,selectClob,_2GB,0,1,1);
+
+
+ // Test - generate random data, write to a file, use it to insert
+ // data into clob and then read back and compare if all is ok
+ // currently in fvt ( derbyall), tests check for substrings etc and
+ // for small amounts of data. This test will test for 100mb of clob data
+ writeToFile(CHARDATAFILE,new RandomCharReader(new java.util.Random(),_100MB));
+ insertClob2("ClobTest #5.1 ", conn, insertClob2, _100MB, 0, 1,
+ _100MB, CHARDATAFILE);
+ selectClob2("ClobTest #5.2 ", conn, selectClob2, _100MB, 0, 1,
+ CHARDATAFILE);
+
+ // Disabled for now, this will materialize, will open
+ // jira for it.
+ //updateClob2("ClobTest #8.1",conn,selectClob,_100MB,0,0,10,1,CHARDATAFILE);
+
+ // update the 2gb row in clobtbl with the 100mb data and compare if the update
+ // went ok.
+ selectUpdateClob2("ClobTest #8.2",conn,selectClob2,selectClob,_100MB,0,1,1,CHARDATAFILE);
+
+ // test for trailing space truncation
+ // insert 100mb+33k of data which has 33k of trailing space,
+ // into a column of 100mb
+ // insert should be successful, select should retrieve 100mb of data
+
+ // Generate random data and write to a file, this file will be used
+ // in the verification process after inserts and updates.
+ writeToFile(CHARDATAFILE,new RandomCharReader(new java.util.Random(),
+ (NUM_TRAILING_SPACES +_100MB),NUM_TRAILING_SPACES));
+ insertClob2("ClobTest #6.1 ", conn, insertClob2,_100MB, 3, 1,
+ (NUM_TRAILING_SPACES +_100MB), CHARDATAFILE);
+ // select will retrieve data and verify the data inserted.
+ selectClob2("ClobTest #6.2 ", conn, selectClob2, _100MB, 3, 1,
+ CHARDATAFILE);
+
+ negativeSpaceTruncationTest("ClobTest #7",conn);
+
+ // Test - for stream contains a trailing non-space character
+ // insert should throw an error
+ writeToFile(CHARDATAFILE,new RandomCharReader(new java.util.Random(),MORE_DATA_THAN_COL_WIDTH));
+ try
+ {
+ insertClob2("ClobTest #9.1 ", conn, insertClob2,MORE_DATA_THAN_COL_WIDTH, 4, 1,
+ MORE_DATA_THAN_COL_WIDTH, CHARDATAFILE);
+ }catch(SQLException sqle)
+ {
+ System.out.println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed");
+ expectedException(sqle);
+ }
+ // no row must be retrieved.
+ selectClob2("ClobTest #9.2 ", conn, selectClob2, _100MB, 4, 0,
+ CHARDATAFILE);
+
+ try
+ {
+ insertClob2("ClobTest #10 ", conn, insertClob2,MORE_DATA_THAN_COL_WIDTH, 4, 1,
+ MORE_DATA_THAN_COL_WIDTH +1 , CHARDATAFILE);
+ }catch(SQLException sqle)
+ {
+ System.out.println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed and"+
+ " stream length is one greater than actual length of the stream ");
+ expectedException(sqle);
+ }
+
+ try
+ {
+ insertClob2("ClobTest #11 ", conn, insertClob2,MORE_DATA_THAN_COL_WIDTH, 4, 1,
+ MORE_DATA_THAN_COL_WIDTH -1 , CHARDATAFILE);
+ }catch(SQLException sqle)
+ {
+ System.out.println("NEGATIVE TEST - Expected Exception: truncation of non-blanks not allowed and"+
+ " stream length is one less than actual length of the stream ");
+ expectedException(sqle);
+ }
+ deleteTable(conn, deleteClob2, 2);
+ } catch (Exception e) {
+ System.out.println("FAIL -- Unexpected exception:");
+ e.printStackTrace(System.out);
+ }
+
+ try {
+ // give -ve streamlength
+ insertClob_SetCharacterStream("ClobTest #12.1", conn, insertClob,
+ _100MB, 4, 1, -1);
+ } catch (SQLException sqle) {
+ System.out.println("NEGATIVE TEST - Expected Exception:");
+ expectedException(sqle);
+ }
+
+ selectClob("ClobTest #12.2", conn, selectClob,_100MB, 4, 0);
+
+ deleteTable(conn, deleteClob, 2);
+
+ // ADD NEW TESTS HERE
+ }
+
+ private static void negativeSpaceTruncationTest(String msg,Connection conn)
+ throws Exception
+ {
+ // Negative test, stream has trailing spaces but the stream length is one
+ // more than the actual length of the stream
+ try
+ {
+ insertClob2(msg, conn, insertClob2,_100MB, 4, 1,
+ (NUM_TRAILING_SPACES +_100MB - 1), CHARDATAFILE);
+ }catch(SQLException sqle)
+ {
+ System.out.println("EXPECTED EXCEPTION - stream has trailing spaces,but stream "+
+ " length is 1 less than actual length of stream");
+ expectedException(sqle);
+ }
+
+ try
+ {
+ insertClob2(msg, conn, insertClob2,_100MB, 5, 1,
+ (NUM_TRAILING_SPACES +_100MB + 1), CHARDATAFILE);
+ }catch(SQLException sqle)
+ {
+ System.out.println("EXPECTED EXCEPTION - stream has trailing spaces,but stream "+
+ " length is 1 greater than actual length of stream");
+ expectedException(sqle);
+ }
+ }
+
+
+ /**
+ * insert blob
+ * @param bloblen length of blob to insert
+ * @param start start id value for insert
+ * @param rows insert rows number of rows
+ * @param streamLength stream length passed to setBinaryStream(,,length)
+ */
+ private static void insertBlob_SetBinaryStream(String testId,
+ Connection conn, PreparedStatement ps, int bloblen, int start,
+ int rows, int streamLength) throws SQLException {
+ System.out.println("========================================");
+ System.out.println("START " + testId + "insertBlob of size = "
+ + bloblen);
+ long ST = 0;
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ int count = 0;
+ java.util.Random random = new java.util.Random();
+ for (int i = start; i < start + rows; i++) {
+ ps.setInt(1, i);
+ ps.setInt(2, 0);
+ ps.setLong(3, bloblen);
+ ps.setBinaryStream(4, new RandomByteStream(random, bloblen),
+ streamLength);
+ count += ps.executeUpdate();
+ }
+ conn.commit();
+ if (trace) {
+ System.out.println("Insert Blob (" + bloblen + ")" + " rows= "
+ + count + " = " + (long) (System.currentTimeMillis() - ST));
+
+ }
+ verifyTest(count, rows, " Rows inserted with blob of size (" + bloblen
+ + ") =");
+ System.out.println("========================================");
+
+ }
+
+ /**
+ * select from blob table (BLOBTBL)
+ * @param bloblen select expects to retrieve a blob of this length
+ * @param id id of the row to retrieve
+ * @param expectedRows number of rows expected to match id
+ */
+ private static void selectBlob(String testId, Connection conn,
+ PreparedStatement ps, int bloblen, int id, int expectedRows)
+ throws SQLException {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - SELECT BLOB of size = "
+ + bloblen);
+
+ long ST = 0;
+ ResultSet rs = null;
+
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ int count = 0;
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+
+ while (rs.next()) {
+ count++;
+ Blob value = rs.getBlob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (dlen != l) {
+ System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
+ + " expected " + dlen + " for row in BLOBTBL with ID="
+ + id);
+ }
+ }
+ conn.commit();
+
+ verifyTest(count, expectedRows,
+ "Matched rows selected with blob of size(" + bloblen + ") =");
+
+ if (trace) {
+ System.out.println("Select Blob (" + bloblen + ")" + " rows= "
+ + expectedRows + " = "
+ + (long) (System.currentTimeMillis() - ST));
+ }
+ System.out.println("========================================");
+ }
+
+ /**
+ * insert blob into BLOBTBL2
+ * @param bloblen length of blob to insert
+ * @param start id value for insert
+ * @param rows insert rows number of rows
+ * @param streamLength stream length passed to setBinaryStream(,,length)
+ * @param file filename to match retrieved data against
+ */
+
+ private static void insertBlob2(String testId, Connection conn,
+ PreparedStatement ps, int bloblen, int start, int rows,
+ int streamLength, String file) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + "insert Blob of size = "
+ + bloblen);
+ int count = 0;
+ java.util.Random random = new java.util.Random();
+ FileInputStream fis = null;
+
+ long ST = 0;
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ for (int i = start; i < start + rows; i++) {
+ fis = new FileInputStream(file);
+ ps.setInt(1, i);
+ ps.setInt(2, 0);
+ ps.setLong(4, bloblen);
+ ps.setBinaryStream(3, fis, streamLength);
+ count += ps.executeUpdate();
+ fis.close();
+ }
+ conn.commit();
+ if (trace) {
+ System.out.println("Insert Blob (" + bloblen + ")" + " rows= "
+ + count + " = " + (long) (System.currentTimeMillis() - ST));
+
+ }
+ verifyTest(count, rows, " Rows inserted with blob of size (" + bloblen
+ + ") =");
+ System.out.println("========================================");
+
+ }
+
+ /**
+ * select from blob table (BLOBTBL2)
+ * @param bloblen select expects to retrieve a blob of this length
+ * @param id id of the row to retrieve
+ * @param expectedRows number of rows expected to match id
+ * @param file name of the file,against which the retrieved data is
+ * compared
+ */
+ private static void selectBlob2(String testId, Connection conn,
+ PreparedStatement ps, int bloblen, int id, int expectedRows,
+ String file) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - SELECT BLOB of size = "
+ + bloblen);
+
+ long ST = 0;
+ ResultSet rs = null;
+
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ int count = 0;
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+
+ while (rs.next()) {
+ count++;
+ Blob value = rs.getBlob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (dlen != l) {
+ System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
+ + " expected " + dlen + " for row in BLOBTBL with ID="
+ + id);
+ } else
+ compareBlobToFile(value.getBinaryStream(), file);
+ }
+ conn.commit();
+
+ verifyTest(count, expectedRows,
+ "Matched rows selected with blob of size(" + bloblen + ") =");
+
+ if (trace) {
+ System.out.println("Select Blob (" + bloblen + ")" + " rows= "
+ + expectedRows + " = "
+ + (long) (System.currentTimeMillis() - ST));
+ }
+ System.out.println("========================================");
+ }
+
+ /**
+ * Basically this test will do an update using setBlob api -
+ * select row from blobtbl and then update a row in blobtbl
+ * and verify updated data in blobtbl
+ * @param ps select statement from which blob is retrieved
+ * @param bloblen updating value is of length bloblen
+ * @param id id of the row retrieved, for the update
+ * @param updateId id of the row that is updated
+ * @param expectedRows to be updated
+ */
+ private static void selectUpdateBlob(String testId, Connection conn,
+ PreparedStatement ps, int bloblen, int id, int updateId,
+ int expectedRows) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - select and then update blob of size= "
+ + bloblen + " - Uses getBlob api");
+
+ ResultSet rs = null;
+
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+ rs.next();
+ Blob value = rs.getBlob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (dlen != l) {
+ System.out
+ .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
+ + dlen + " for row in BLOBTBL with ID=" + id);
+ }
+
+ PreparedStatement psUpd = conn
+ .prepareStatement("update BLOBTBL set content=?,dlen =? where id = ?");
+ psUpd.setBlob(1,value);
+ psUpd.setLong(2, l);
+ psUpd.setInt(3, updateId);
+
+ System.out.println("Rows Updated = " + psUpd.executeUpdate());
+ conn.commit();
+
+ // now select and verify that update went through ok.
+ ps.setInt(1, updateId);
+ ResultSet rs2 = ps.executeQuery();
+ rs2.next();
+ Blob updatedValue = rs2.getBlob(1);
+
+ if(updatedValue.length() != l)
+ System.out.println("FAIL - Retrieving the updated blob length does not match "+
+ "expected length = "+l +" found = "+ updatedValue.length());
+
+ // close resultsets
+ conn.commit();
+ rs.close();
+ rs2.close();
+ psUpd.close();
+ System.out.println("========================================");
+ }
+
+ /**
+ * Basically this test will do an update using setBinaryStream api and verifies the
+ * updated data. select row from blobtbl2 and then update a row in blobtbl
+ * and verify updated data in blobtbl
+ * @param bloblen updating value is of length bloblen
+ * @param id id of the row retrieved, for the update
+ * @param updateId id of the row that is updated
+ * @param expectedRows to be updated
+ * @param file name of the file,against which the updated data is
+ * compared
+ */
+ private static void selectUpdateBlob2(String testId, Connection conn,
+ PreparedStatement ps,PreparedStatement sel,int bloblen, int id, int updateId,
+ int expectedRows,String file) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - select and then update blob of size= "
+ + bloblen + " - Uses getBlob and setBlob api");
+
+ ResultSet rs = null;
+
+ // retrieve row from blobtbl2
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+ rs.next();
+ Blob value = rs.getBlob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (dlen != l) {
+ System.out
+ .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
+ + dlen + " for row in BLOBTBL2 with ID=" + id);
+ }
+
+ PreparedStatement psUpd = conn
+ .prepareStatement("update BLOBTBL set content=?,dlen =? where id = ?");
+ psUpd.setBlob(1,value);
+ psUpd.setLong(2, l);
+ psUpd.setInt(3, updateId);
+
+ System.out.println("Rows Updated = " + psUpd.executeUpdate());
+ conn.commit();
+
+ // now select and verify that update went through ok.
+ sel.setInt(1, updateId);
+ ResultSet rs2 = sel.executeQuery();
+ rs2.next();
+ Blob updatedValue = rs2.getBlob(1);
+
+ if (updatedValue.length() != l)
+ {
+ System.out.println("FAIL - MISMATCH length of updated blob value : expected="+
+ l+" found ="+updatedValue.length());
+ }
+ else
+ compareBlobToFile(updatedValue.getBinaryStream(),file);
+
+ // close resultsets
+ conn.commit();
+ rs.close();
+ rs2.close();
+ psUpd.close();
+ System.out.println("========================================");
+
+ }
+
+ private static void compareBlobToFile(InputStream lobstream, String filename)
+ throws Exception {
+ FileInputStream file = new FileInputStream(filename);
+ int l = 0;
+ int b = 0;
+ do {
+ l = lobstream.read();
+ b = file.read();
+ if (l != b)
+ {
+ System.out.println("FAIL -- MISMATCH in data stored versus"+
+ "data retrieved");
+ break;
+ }
+ } while (l != -1 && b != -1);
+ }
+
+ private static void deleteTable(Connection conn, PreparedStatement ps,
+ int expectedRows) throws SQLException {
+ int count = ps.executeUpdate();
+ conn.commit();
+ verifyTest(count, expectedRows, "Rows deleted =");
+ }
+
+
+ /**
+ * insert clob
+ * @param cloblen length of clob to insert
+ * @param start id value for insert
+ * @param rows insert rows number of rows
+ * @param streamLength stream length passed to setCharacterStream(...,length)
+ */
+ private static void insertClob_SetCharacterStream(String testId,
+ Connection conn, PreparedStatement ps, int cloblen, int start,
+ int rows, int streamLength) throws SQLException {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " -insertClob of size = "
+ + cloblen);
+
+ long ST = 0;
+ java.util.Random random = new java.util.Random();
+ int count = 0;
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ for (int i = start; i < start + rows; i++) {
+ ps.setInt(1, i);
+ ps.setInt(2, 0);
+ ps.setLong(3, cloblen);
+ ps.setCharacterStream(4, new RandomCharReader(random, cloblen),
+ streamLength);
+ count += ps.executeUpdate();
+ }
+ conn.commit();
+ if (trace) {
+ System.out.println("Insert Clob (" + cloblen + ")" + " rows= "
+ + count + " = " + (long) (System.currentTimeMillis() - ST));
+
+ }
+ verifyTest(count, rows, "Rows inserted with clob of size (" + cloblen
+ + ") = ");
+ System.out.println("========================================");
+
+ }
+
+ /**
+ * select from clob table
+ * @param cloblen select expects to retrieve a clob of this length
+ * @param id id of the row to retrieve
+ * @param expectedRows number of rows expected to match id
+ */
+ private static void selectClob(String testId, Connection conn,
+ PreparedStatement ps, int cloblen, int id, int expectedRows)
+ throws SQLException {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - SELECT CLOB of size = "
+ + cloblen);
+
+ long ST = 0;
+ int count = 0;
+ ResultSet rs = null;
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+ while (rs.next()) {
+ count++;
+ Clob value = rs.getClob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (dlen != l) {
+ System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
+ + " expected " + dlen + " for row in CLOBTBL with ID="
+ + id);
+ }
+
+ }
+ conn.commit();
+ if (trace) {
+ System.out.println("Select Clob (" + cloblen + ")" + " rows= "
+ + expectedRows + " = "
+ + (long) (System.currentTimeMillis() - ST));
+
+ }
+
+ verifyTest(count, expectedRows,
+ "Matched rows selected with clob of size(" + cloblen + ") =");
+ System.out.println("========================================");
+
+ }
+
+ /**
+ * insert clob into CLOBTBL2
+ * @param cloblen length of clob to insert
+ * @param start id value for insert
+ * @param rows insert rows number of rows
+ * @param streamLength stream length passed to setCharacterStream(pos,reader,streamLength)
+ * @param file name of the file that has data to be inserted
+ */
+ private static void insertClob2(String testId, Connection conn,
+ PreparedStatement ps, int cloblen, int start, int rows,
+ int streamLength, String file) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + "insert Clob of size = "
+ + cloblen);
+ int count = 0;
+ FileReader reader = null;
+ long ST = 0;
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ for (int i = start; i < start + rows; i++) {
+ reader = new FileReader(file);
+ ps.setInt(1, i);
+ ps.setInt(2, 0);
+ ps.setLong(4, cloblen);
+ ps.setCharacterStream(3, reader, streamLength);
+ count += ps.executeUpdate();
+ reader.close();
+ }
+ conn.commit();
+ if (trace) {
+ System.out.println("Insert Clob (" + cloblen + ")" + " rows= "
+ + count + " = " + (long) (System.currentTimeMillis() - ST));
+
+ }
+ verifyTest(count, rows, " Rows inserted with clob of size (" + cloblen
+ + ") =");
+ System.out.println("========================================");
+
+ }
+
+ /**
+ * select from clob table (CLOBTBL2)
+ * @param cloblen select expects to retrieve a clob of this length
+ * @param id id of the row to retrieve
+ * @param expectedRows number of rows expected to match id
+ * @param file filename to compare the retrieved data against
+ */
+ private static void selectClob2(String testId, Connection conn,
+ PreparedStatement ps, int cloblen, int id, int expectedRows,
+ String file) throws SQLException, Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - SELECT CLOB of size = "
+ + cloblen);
+
+ long ST = 0;
+ ResultSet rs = null;
+
+ if (trace)
+ ST = System.currentTimeMillis();
+
+ int count = 0;
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+
+ while (rs.next()) {
+ count++;
+ Clob value = rs.getClob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (cloblen != l) {
+ System.out.println("FAIL - MISMATCH LENGTHS GOT " + l
+ + " expected " + dlen + " for row in CLOBTBL2 with ID="
+ + id);
+ } else
+ compareClobToFile(value.getCharacterStream(), file, cloblen);
+ }
+ conn.commit();
+
+ verifyTest(count, expectedRows,
+ "Matched rows selected with clob of size(" + cloblen + ") =");
+
+ if (trace) {
+ System.out.println("Select Clob (" + cloblen + ")" + " rows= "
+ + expectedRows + " = "
+ + (long) (System.currentTimeMillis() - ST));
+ }
+ System.out.println("========================================");
+ }
+
+ /*
+ * Basically this test will do an update using setClob api -
+ * select row from clobtbl and then update a row in clobtbl
+ * and verify updated data in clobtbl
+ */
+ private static void selectUpdateClob(String testId, Connection conn,
+ PreparedStatement ps, int cloblen, int id, int updateId,
+ int expectedRows) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - select and then update clob of size= "
+ + cloblen + " - Uses setClob api");
+
+ ResultSet rs = null;
+
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+ rs.next();
+ Clob value = rs.getClob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (dlen != l) {
+ System.out
+ .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
+ + dlen + " for row in CLOBTBL with ID=" + id);
+ }
+
+ PreparedStatement psUpd = conn
+ .prepareStatement("update CLOBTBL set content=?,dlen =? where id = ?");
+ psUpd.setCharacterStream(1, value.getCharacterStream(), (int) l);
+ psUpd.setLong(2, l);
+ psUpd.setInt(3, updateId);
+
+ System.out.println("Rows Updated = " + psUpd.executeUpdate());
+ conn.commit();
+
+ // now select and verify that update went through ok.
+ ps.setInt(1, updateId);
+ ResultSet rs2 = ps.executeQuery();
+ rs2.next();
+ Clob updatedValue = rs2.getClob(1);
+
+ if(updatedValue.length() != l)
+ System.out.println("FAIL - Retrieving the updated clob length does not match "+
+ "expected length = "+l +" found = "+ updatedValue.length());
+
+ // close resultsets
+ conn.commit();
+ rs.close();
+ rs2.close();
+ psUpd.close();
+ System.out.println("========================================");
+ }
+
+
+ /*
+ * Basically this test will do an update using setBlob api and verifies the
+ * updated data. select row from clobtbl2 and then update a row in clobtbl
+ * and verify updated data in clobtbl against the data in the original file
+ */
+ private static void selectUpdateClob2(String testId, Connection conn,
+ PreparedStatement ps, PreparedStatement sel,int cloblen, int id, int updateId,
+ int expectedRows,String file) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - select and then update clob of size= "
+ + cloblen + " - Uses setClob api");
+
+ ResultSet rs = null;
+
+ // retrieve row from clobtbl2
+ ps.setInt(1, id);
+ rs = ps.executeQuery();
+ rs.next();
+ Clob value = rs.getClob(1);
+ long l = value.length();
+ long dlen = rs.getLong(2);
+ if (dlen != l) {
+ System.out
+ .println("FAIL - MISMATCH LENGTHS GOT " + l + " expected "
+ + dlen + " for row in CLOBTBL2 with ID=" + id);
+ }
+
+ PreparedStatement psUpd = conn
+ .prepareStatement("update CLOBTBL set content=?,dlen =? where id = ?");
+ psUpd.setClob(1,value);
+ psUpd.setLong(2, l);
+ psUpd.setInt(3, updateId);
+
+ System.out.println("Rows Updated = " + psUpd.executeUpdate());
+ conn.commit();
+
+ // now select and verify that update went through ok.
+ sel.setInt(1, updateId);
+ ResultSet rs2 = sel.executeQuery();
+ rs2.next();
+ Clob updatedValue = rs2.getClob(1);
+
+ if (updatedValue.length() != l)
+ {
+ System.out.println("FAIL - MISMATCH length of updated clob value , found="+
+ updatedValue.length() +",expected = "+l);
+ }
+ else
+ compareClobToFile(updatedValue.getCharacterStream(),file,(int)l);
+
+ // close resultsets
+ conn.commit();
+ rs.close();
+ rs2.close();
+ psUpd.close();
+ System.out.println("========================================");
+
+ }
+
+ /*
+ * Basically this test will do an update using updateClob api and verifies the
+ * updated data. select row from clobtbl2 and then update a row in clobtbl
+ * and verify updated data in clobtbl against the data in the original file
+ * @param updateRowId id of the row that needs to be updated
+ */
+ private static void updateClob2(String testId, Connection conn,PreparedStatement sel,
+ int cloblen, int id, int updateRowId,int updateIdVal,
+ int expectedRows,String file) throws Exception {
+ System.out.println("========================================");
+ System.out.println("START " + testId + " - select and then update clob of size= "
+ + cloblen + " - Uses updateClob api");
+
+
+ PreparedStatement ps1 = conn.prepareStatement("SELECT * FROM CLOBTBL FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
[... 295 lines stripped ...]