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 dj...@apache.org on 2006/06/01 01:56:12 UTC
svn commit: r410702 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/DerbyNet/blobclob4BLOB.out master/DerbyNetClient/blobclob4BLOB.out
master/blobclob4BLOB.out tests/jdbcapi/blobclob4BLOB.java
Author: djd
Date: Wed May 31 16:56:12 2006
New Revision: 410702
URL: http://svn.apache.org/viewvc?rev=410702&view=rev
Log:
DERBY-438 (partial) Add statement trigger tests involving selecting large CLOB values.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/blobclob4BLOB.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/blobclob4BLOB.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/blobclob4BLOB.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/blobclob4BLOB.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/blobclob4BLOB.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/blobclob4BLOB.out?rev=410702&r1=410701&r2=410702&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/blobclob4BLOB.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/blobclob4BLOB.out Wed May 31 16:56:12 2006
@@ -43,6 +43,39 @@
Succeeded to match, row 6
PASSED, row 6, length was 5016
clobTest12 finished
+START: clobTest13Trigger
+testCLOB_MAIN: NULL
+testCLOB_MAIN: length 0 crc32 0 0
+testCLOB_MAIN: length 0 crc32 0 0
+testCLOB_MAIN: length 26 crc32 3726329377 26
+testCLOB_MAIN: length 42 crc32 384104526 42
+testCLOB_MAIN: length 56 crc32 4255880856 56
+testCLOB_MAIN: length 65 crc32 3470395945 65
+testCLOB_MAIN: length 5000 crc32 2272896610 5000
+testCLOB_MAIN: length 10000 crc32 1814912934 10000
+testCLOB_MAIN: length 300000 crc32 3781770763 300000
+main update row count :10
+row trigger: NULL
+row trigger: length 0 crc32 0 0
+row trigger: length 0 crc32 0 0
+row trigger: length 26 crc32 3726329377 26
+row trigger: length 42 crc32 384104526 42
+row trigger: length 56 crc32 4255880856 56
+row trigger: length 65 crc32 3470395945 65
+row trigger: length 5000 crc32 2272896610 5000
+row trigger: length 10000 crc32 1814912934 10000
+row trigger: length 300000 crc32 3781770763 300000
+statement trigger: NULL
+statement trigger: length 0 crc32 0 0
+statement trigger: length 0 crc32 0 0
+statement trigger: length 26 crc32 3726329377 26
+statement trigger: length 42 crc32 384104526 42
+statement trigger: length 56 crc32 4255880856 56
+statement trigger: length 65 crc32 3470395945 65
+statement trigger: length 5000 crc32 2272896610 5000
+statement trigger: length 10000 crc32 1814912934 10000
+statement trigger: length 300000 crc32 3781770763 300000
+clobTest13Trigger finished
START: clobTest2
CLOB getSubString 9905 > 0
EXPECTED Out of bounds exception
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/blobclob4BLOB.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/blobclob4BLOB.out?rev=410702&r1=410701&r2=410702&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/blobclob4BLOB.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/blobclob4BLOB.out Wed May 31 16:56:12 2006
@@ -43,6 +43,39 @@
Succeeded to match, row 6
PASSED, row 6, length was 5016
clobTest12 finished
+START: clobTest13Trigger
+testCLOB_MAIN: NULL
+testCLOB_MAIN: length 0 crc32 0 0
+testCLOB_MAIN: length 0 crc32 0 0
+testCLOB_MAIN: length 26 crc32 3726329377 26
+testCLOB_MAIN: length 42 crc32 384104526 42
+testCLOB_MAIN: length 56 crc32 4255880856 56
+testCLOB_MAIN: length 65 crc32 3470395945 65
+testCLOB_MAIN: length 5000 crc32 2272896610 5000
+testCLOB_MAIN: length 10000 crc32 1814912934 10000
+testCLOB_MAIN: length 300000 crc32 3781770763 300000
+main update row count :10
+row trigger: NULL
+row trigger: length 0 crc32 0 0
+row trigger: length 0 crc32 0 0
+row trigger: length 26 crc32 3726329377 26
+row trigger: length 42 crc32 384104526 42
+row trigger: length 56 crc32 4255880856 56
+row trigger: length 65 crc32 3470395945 65
+row trigger: length 5000 crc32 2272896610 5000
+row trigger: length 10000 crc32 1814912934 10000
+row trigger: length 300000 crc32 3781770763 300000
+statement trigger: NULL
+statement trigger: length 0 crc32 0 0
+statement trigger: length 0 crc32 0 0
+statement trigger: length 26 crc32 3726329377 26
+statement trigger: length 42 crc32 384104526 42
+statement trigger: length 56 crc32 4255880856 56
+statement trigger: length 65 crc32 3470395945 65
+statement trigger: length 5000 crc32 2272896610 5000
+statement trigger: length 10000 crc32 1814912934 10000
+statement trigger: length 300000 crc32 3781770763 300000
+clobTest13Trigger finished
START: clobTest2
CLOB getSubString 9905 > 0
EXPECTED Out of bounds exception
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/blobclob4BLOB.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/blobclob4BLOB.out?rev=410702&r1=410701&r2=410702&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/blobclob4BLOB.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/blobclob4BLOB.out Wed May 31 16:56:12 2006
@@ -43,6 +43,39 @@
Succeeded to match, row 6
PASSED, row 6, length was 5016
clobTest12 finished
+START: clobTest13Trigger
+testCLOB_MAIN: NULL
+testCLOB_MAIN: length 0 crc32 0 0
+testCLOB_MAIN: length 0 crc32 0 0
+testCLOB_MAIN: length 26 crc32 3726329377 26
+testCLOB_MAIN: length 42 crc32 384104526 42
+testCLOB_MAIN: length 56 crc32 4255880856 56
+testCLOB_MAIN: length 65 crc32 3470395945 65
+testCLOB_MAIN: length 5000 crc32 2272896610 5000
+testCLOB_MAIN: length 10000 crc32 1814912934 10000
+testCLOB_MAIN: length 300000 crc32 3781770763 300000
+main update row count :10
+row trigger: NULL
+row trigger: length 0 crc32 0 0
+row trigger: length 0 crc32 0 0
+row trigger: length 26 crc32 3726329377 26
+row trigger: length 42 crc32 384104526 42
+row trigger: length 56 crc32 4255880856 56
+row trigger: length 65 crc32 3470395945 65
+row trigger: length 5000 crc32 2272896610 5000
+row trigger: length 10000 crc32 1814912934 10000
+row trigger: length 300000 crc32 3781770763 300000
+statement trigger: NULL
+statement trigger: length 0 crc32 0 0
+statement trigger: length 0 crc32 0 0
+statement trigger: length 26 crc32 3726329377 26
+statement trigger: length 42 crc32 384104526 42
+statement trigger: length 56 crc32 4255880856 56
+statement trigger: length 65 crc32 3470395945 65
+statement trigger: length 5000 crc32 2272896610 5000
+statement trigger: length 10000 crc32 1814912934 10000
+statement trigger: length 300000 crc32 3781770763 300000
+clobTest13Trigger finished
START: clobTest2
CLOB getSubString 9905 > 0
EXPECTED SQLSTATE(XJ076): The position argument '9,905' exceeds the size of the BLOB/CLOB.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/blobclob4BLOB.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/blobclob4BLOB.java?rev=410702&r1=410701&r2=410702&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/blobclob4BLOB.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/blobclob4BLOB.java Wed May 31 16:56:12 2006
@@ -159,6 +159,7 @@
clobTest0(conn);
clobTest11(conn);
clobTest12(conn);
+ clobTest13Trigger(conn);
clobTest2(conn);
clobTest22(conn);
clobTest3(conn);
@@ -346,6 +347,7 @@
System.out.println("FAIL. No rows in table testCLOB_MAIN");
if (realNumRows != numRows)
System.out.println("FAIL. numRows is incorrect");
+ rs.close();
}
catch (SQLException e) {
@@ -666,7 +668,7 @@
+ ", but it is " + clob.length() + ", i = " + i);
}
}
-
+ rs.close();
conn.commit();
System.out.println("clobTest0 finished");
}
@@ -722,6 +724,7 @@
+ ", but it is " + clob.length() + ", i = " + i);
}
+ rs.close();
conn.commit();
System.out.println("clobTest11 finished");
}
@@ -803,6 +806,72 @@
}
}
+ /**
+ * Test triggers on CLOB columns.
+ */
+ private static void clobTest13Trigger(Connection conn)
+ {
+ System.out.println(START + "clobTest13Trigger");
+ try {
+ Statement stmt = conn.createStatement();
+ stmt.executeUpdate("CREATE TABLE clobTest13TriggerA (a CLOB(400k), b int)");
+ stmt.executeUpdate("CREATE TABLE clobTest13TriggerB (a CLOB(400k), b int)");
+ stmt.executeUpdate(
+ "create trigger T13A after update on testCLOB_MAIN " +
+ "referencing new as n old as o " +
+ "for each row mode db2sql "+
+ "insert into clobTest13TriggerA(a, b) values (n.a, n.b)");
+ stmt.executeUpdate(
+ "create trigger T13B after INSERT on clobTest13TriggerA " +
+ "referencing new_table as n " +
+ "for each statement mode db2sql "+
+ "insert into clobTest13TriggerB(a, b) select n.a, n.b from n");
+
+ conn.commit();
+ ResultSet rs = stmt.executeQuery(
+ "select a,length(a),b from testCLOB_MAIN order by b");
+
+ showClobContents("testCLOB_MAIN", rs);
+
+ rs.close();
+ conn.commit();
+ int rowCount = stmt.executeUpdate("UPDATE testCLOB_MAIN set b = b + 0");
+ System.out.println("main update row count :" + rowCount);
+ conn.commit();
+ rs = stmt.executeQuery(
+ "select a,length(a),b from clobTest13TriggerA order by b");
+ showClobContents("row trigger", rs);
+ rs.close();
+ conn.commit();
+
+ rs = stmt.executeQuery(
+ "select a,length(a),b from clobTest13TriggerB order by b");
+ showClobContents("statement trigger", rs);
+ rs.close();
+ conn.commit();
+
+ stmt.executeUpdate("DROP TRIGGER T13A");
+ stmt.executeUpdate("DROP TABLE clobTest13TriggerB");
+ stmt.executeUpdate("DROP TABLE clobTest13TriggerA");
+
+ stmt.close();
+ conn.commit();
+ System.out.println("clobTest13Trigger finished");
+ }
+ catch (SQLException e) {
+ TestUtil.dumpSQLExceptions(e);
+ do {
+ e.printStackTrace(System.out);
+ e = e.getNextException();
+ } while (e != null);
+
+ }
+ catch (Throwable e) {
+ System.out.println("FAIL -- unexpected exception:" + e.toString());
+ if (debug) e.printStackTrace();
+ }
+
+ }
@@ -856,6 +925,7 @@
System.out.println(res);
}
}
+ rs.close();
System.out.println("clobTest2 finished");
}
catch (SQLException e) {
@@ -956,6 +1026,7 @@
blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",5911,clob, clobLength);
blobclob4BLOB.printPosition(i,"Position-9907",1,clob, clobLength);
}
+ rs.close();
System.out.println("clobTest3 finished");
}
catch (SQLException e) {
@@ -1077,6 +1148,7 @@
printPositionClob(i,searchStr,1,clob,j,searchClob);
}
}
+ rs.close();
System.out.println("clobTest4 finished");
}
catch (SQLException e) {
@@ -1374,9 +1446,12 @@
Clob clob;
rs.next();
clob = rs.getClob(1);
+ clobLength = rs.getInt(2);
+ rs.close();
if (clob == null)
return;
- clobLength = rs.getInt(2);
+
+
// 0 or negative position value
if (isDerbyNet)
System.out.println(" negative tests for clob.getSubstring won't run for network server until 5243 is fixed");
@@ -4442,6 +4517,25 @@
}
in.close();
return sum.getValue();
+ }
+
+ private static void showClobContents(String tag, ResultSet rs)
+ throws SQLException, IOException
+ {
+ while (rs.next())
+ {
+ InputStream is = rs.getAsciiStream(1);
+ if (is == null) {
+ System.out.println(tag + ": NULL");
+ continue;
+ }
+ long clobcrc = getStreamCheckSum(is);
+ int clobLength = rs.getInt(2);
+ int b = rs.getInt(3);
+
+ System.out.println(tag + ": length " +
+ clobLength + " crc32 " + clobcrc + " " + b);
+ }
}
}