|
1
21
22 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
23
24 import java.io.ByteArrayInputStream  ;
25 import java.io.File ;
26 import java.io.FileInputStream ;
27 import java.io.FileNotFoundException ;
28 import java.io.FileOutputStream ;
29 import java.io.FileReader ;
30 import java.io.FileWriter ;
31 import java.io.IOException ;
32 import java.io.InputStream ;
33 import java.io.InputStreamReader ;
34 import java.io.OutputStream ;
35 import java.io.OutputStreamWriter ;
36 import java.io.Reader ;
37 import java.io.StringBufferInputStream ;
38 import java.io.StringReader ;
39 import java.io.Writer ;
40 import java.sql.Blob ;
41 import java.sql.Clob ;
42 import java.sql.Connection ;
43 import java.sql.PreparedStatement ;
44 import java.sql.ResultSet ;
45 import java.sql.ResultSetMetaData ;
46 import java.sql.SQLException ;
47 import java.io.UnsupportedEncodingException ;
48 import java.sql.Statement ;
49 import java.sql.Types ;
50 import java.util.zip.CRC32 ;
51
52 import org.apache.derby.tools.JDBCDisplayUtil;
53 import org.apache.derby.tools.ij;
54 import org.apache.derbyTesting.functionTests.util.Formatters;
55 import org.apache.derbyTesting.functionTests.util.TestUtil;
56 import java.util.Arrays ;
57
58
63
64 public class blobclob4BLOB {
65
66 static String [] fileName;
67 static long[] fileCRC32;
68 static String [] basefileName; static String filePath;
70 static String unicodeFilePath;
71 static String sep;
72 static long[] fileLength;
73 static int numFiles;
74 static int numRows;
75 static int numStrings;
76 static String [] unicodeStrings;
77 static int numRowsUnicode;
78 static String unicodeFileName;
79
80 static boolean isDerbyNet = false;
81 static boolean debug = true;
82 private static final String START = "\nSTART: ";
83
84 static
85 {
86 numFiles = 5;
87 filePath = "extin";
88 fileName = new String [numFiles];
89 basefileName = new String [numFiles];
90 fileLength = new long[numFiles];
91 fileCRC32 = new long[numFiles];
92
93 fileName[0] = "short.utf"; fileName[1] = "littleclob.utf"; fileName[2] = "empty.utf"; fileName[3] = "searchclob.utf"; fileName[4] = "aclob.utf";
99 for (int i = 0 ; i < numFiles; i++)
100 {
101 basefileName[i] = fileName[i];
102 }
103
104 numRows = 10;
105
106 numStrings = 3;
107 unicodeStrings = new String [numStrings];
108 unicodeStrings[0] = "\u0061\u0062\u0063"; unicodeStrings[1] = "\u0370\u0371\u0372";
110 unicodeStrings[2] = "\u05d0\u05d1\u05d2";
111 numRowsUnicode = 6;
112
113 unicodeFilePath = "extinout";
114 unicodeFileName = "unicodeFile.txt";
115 }
116
117
118 public static void main(String [] args)
119 {
120 System.out.println("Test blobclob starting");
121
122 isDerbyNet = TestUtil.isNetFramework();
123
124 try
125 {
126 sep = System.getProperty("file.separator");
128 boolean exists = (new File (filePath, fileName[0])).exists();
129 if (!exists)
130 {
131 String userDir = System.getProperty("user.dir");
132 filePath = userDir + sep + ".." + sep + filePath;
133 unicodeFilePath = userDir + sep + ".." + sep + unicodeFilePath;
134 }
135 for (int i=0; i < numFiles; i++)
136 {
137 fileName[i] = filePath + sep + fileName[i];
138
139 FileInputStream fis = new FileInputStream (fileName[i]);
140 fileCRC32[i] = getStreamCheckSum(fis);
141 }
142 unicodeFileName = unicodeFilePath + sep + unicodeFileName;
143
144 ij.getPropertyArg(args);
147 Connection conn = ij.startJBMS();
148 conn.setAutoCommit(false);
151
152 prepareCLOBMAIN(conn);
153 prepareSearchClobTable(conn);
154 prepareUnicodeTable(conn);
155 prepareUnicodeFile(conn);
156
158 setCharacterStreamTest(conn);
159
160
163 clobTest0(conn);
164 clobTest11(conn);
165 clobTest12(conn);
166 clobTest13Trigger(conn);
167 clobTest2(conn);
168 clobTest22(conn);
169 clobTest3(conn);
170 clobTest32(conn);
171 clobTest4(conn);
172 clobTest42(conn);
173 clobTest51(conn);
174 clobTest52(conn);
175 clobTest53(conn);
176 clobTest54(conn);
177 clobTest6(conn);
178 clobTest7(conn);
179
180 clobTest8(conn);
181
182 clobTest91(conn);
183 clobTest92(conn);
184 clobTest93(conn);
185 clobTest94(conn);
186 clobTest95(conn);
187
188 conn = ij.startJBMS();
190
191 if (!TestUtil.isJCCFramework()) {
193 clobTest10(conn);
194 clobTest14(conn);
195 }
196
197 conn.setAutoCommit(false);
198 clobTest96(conn);
199
200 prepareBlobTable(conn);
201 prepareSearchBlobTable(conn);
202
203 blobTest0(conn);
204 blobTest2(conn);
205 blobTest3(conn);
206 blobTest4(conn);
207 blobTest51(conn);
208 blobTest52(conn);
209 blobTest53(conn);
210 blobTest54(conn);
211 blobTest6(conn);
212 blobTest7(conn);
213 blobTest8Trigger(conn);
214 blobTest91(conn);
215 blobTest92(conn);
216 blobTest93(conn);
217 blobTest94(conn);
218 blobTest95(conn);
219
220 conn = ij.startJBMS();
222 conn.setAutoCommit(false);
223 blobTest96(conn);
224
225 if (!TestUtil.isJCCFramework()) {
227 blobTest10(conn);
228 blobTest11(conn);
229 }
230
231 clobTestSelfDestructive(conn);
232 clobTestSelfDestructive2(conn);
233
234 conn.commit();
235 clobNegativeTest_Derby265(conn);
236 blobNegativeTest_Derby265(conn);
237
238 conn = ij.startJBMS();
240 String [] testObjects = {"table testclob_main", "table searchclob",
241 "table testunicode", "table testunicode2",
242 "table testclob10", "table testinteger",
243 "table testclobcolumn", "table testclob2",
244 "table testclob7", "table testlongrowclob",
245 "table testblob", "table searchblob",
246 "table testvarbinary", "table testinteger2",
247 "table testblobcolumn", "table testblob2",
248 "table testblobx", "table testlongrowblob",
249 "table maps", "table maps_blob"};
250 Statement stmt = conn.createStatement();
251 TestUtil.cleanUpTest(stmt, testObjects);
252 conn.close();
253 System.out.println("FINISHED TEST blobclob :-)");
254 }
255 catch (SQLException e)
256 {
257 TestUtil.dumpSQLExceptions(e);
258 if (debug) e.printStackTrace(System.out);
259 }
260 catch (Throwable e)
261 {
262 System.out.println("xFAIL -- unexpected exception:" + e.toString());
263 if (debug) e.printStackTrace(System.out);
265 }
266 System.out.println("Test blobclob finished\n");
267 }
268
269
270 private static void insertRow(PreparedStatement ps, String s)
271 throws SQLException
272 {
273 ps.clearParameters();
274 ps.setString(1, s);
275 ps.setInt(2, s.length());
276 ps.executeUpdate();
277 }
278
279 private static void insertRow(PreparedStatement ps, String s, int i)
280 throws SQLException
281 {
282 ps.setString(1, s);
283 ps.setInt(2, s.length());
284 ps.setInt(3, i);
285 ps.executeUpdate();
286 }
287
288 private static void insertRow(PreparedStatement ps, byte[] b)
289 throws SQLException , IOException
290 {
291 ps.setBytes(1, b);
292 ps.setInt(2, b.length);
293 ps.setLong(3, getStreamCheckSum(new ByteArrayInputStream (b)));
294 ps.executeUpdate();
295 }
296
297
305 private static void prepareCLOBMAIN(Connection conn)
306 {
307 System.out.println(START +"prepareCLOBMAIN");
308 ResultSet rs;
309 Statement stmt;
310
311 try {
312 stmt = conn.createStatement();
313 stmt.execute(
314 "create table testCLOB_MAIN (b integer)");
316 stmt.execute("alter table testCLOB_MAIN add column a CLOB(1M)");
317 PreparedStatement ps = conn.prepareStatement(
318 "insert into testCLOB_MAIN (a, b) values(?,?)");
319
320 insertRow(ps,"");
322 insertRow(ps,"you can lead a horse to water but you can't form it into beverage");
323 insertRow(ps,"a stitch in time says ouch");
324 insertRow(ps,"here is a string with a return \n character");
325
326
327 for (int i = 0; i < numFiles; i++)
329 {
330 File file = new File (fileName[i]);
332 fileLength[i] = file.length();
333
338 InputStream fileIn = new FileInputStream (file);
339
340 System.out.println("===> inserting " + basefileName[i] + " length = "
341 + fileLength[i]);
342
343 ps.setAsciiStream(1, fileIn, (int)fileLength[i]);
345 ps.setInt(2, (int)fileLength[i]);
346 ps.executeUpdate();
347 ps.clearParameters();
348 fileIn.close();
349 }
350
351 ps.setNull(1, Types.CLOB);
353 ps.setInt(2, 0);
354 ps.executeUpdate();
355
356 conn.commit();
357
358 rs = stmt.executeQuery("select count(*) from testCLOB_MAIN");
360 int realNumRows = -1;
361 if (rs.next())
362 realNumRows = rs.getInt(1);
363 if (realNumRows <= 0)
364 System.out.println("FAIL. No rows in table testCLOB_MAIN");
365 if (realNumRows != numRows)
366 System.out.println("FAIL. numRows is incorrect");
367 rs.close();
368
369 }
370 catch (SQLException e) {
371 TestUtil.dumpSQLExceptions(e);
372 if (debug) e.printStackTrace(System.out);
373 }
374 catch (Throwable e) {
375 System.out.println("FAIL -- unexpected exception:" + e.toString());
376 if (debug) e.printStackTrace(System.out);
377 }
378 }
380
381
382
383
388 private static void prepareSearchClobTable(Connection conn)
389 {
390 System.out.println(START + "prepareSearchClobTable");
391 ResultSet rs;
392 Statement stmt;
393
394 try
395 {
396 stmt = conn.createStatement();
397 stmt.execute("create table searchClob (b integer)");
399 stmt.execute("alter table searchClob add column a CLOB(300k)");
400 PreparedStatement ps = conn.prepareStatement(
401 "insert into searchClob (a, b) values(?,?)");
402 insertRow(ps,"horse");
403 insertRow(ps,"ouch");
404 insertRow(ps,"\n");
405 insertRow(ps,"");
406 insertRow(ps,"Beginning");
407 insertRow(ps,"position-69");
408 insertRow(ps,"I-am-hiding-here-at-position-5910");
409 insertRow(ps,"Position-9907");
410
411 for (int i = 0; i < numFiles; i++)
413 {
414 File file = new File (fileName[i]);
416 fileLength[i] = file.length();
417 InputStream fileIn = new FileInputStream (file);
418
419
424 System.out.println("===> inserting " + basefileName[i] + " length = "
425 + fileLength[i]);
426
427
429 ps.setAsciiStream(1, fileIn, (int)fileLength[i]);
430 ps.setInt(2, (int)fileLength[i]);
431 ps.executeUpdate();
432 ps.clearParameters();
433 fileIn.close();
434 }
435
436 ps.setNull(1, Types.CLOB);
438 ps.setInt(2, 0);
439 ps.executeUpdate();
440
441 conn.commit();
442 }
443 catch (SQLException e) {
444 TestUtil.dumpSQLExceptions(e);
445 if (debug) e.printStackTrace(System.out);
446 }
447 catch (Throwable e) {
448 System.out.println("FAIL -- unexpected exception:" + e.toString());
449 if (debug) e.printStackTrace(System.out);
450 }
451 System.out.println("prepareSearchClobTable finished");
452 }
453
454
455
463 private static void prepareUnicodeTable(Connection conn)
464 {
465 ResultSet rs;
466 Statement stmt;
467 System.out.println(START + "prepareUnicodeTable");
468 try {
469 stmt = conn.createStatement();
470 stmt.execute("create table testUnicode (b integer, c integer)");
472 stmt.execute("alter table testUnicode add column a CLOB(100k)");
473 PreparedStatement ps = conn.prepareStatement(
474 "insert into testUnicode (a, b, c) values(?,?,?)");
476
477
479 for (int i = 0; i < numStrings; i++)
480 {
481 insertRow(ps,unicodeStrings[i],i);
482 }
483
484 StringBuffer sb = new StringBuffer (5000);
485 for (int i = 0; i < 5000; i++)
486 sb.append('q');
487 String largeString = new String (sb);
488
489 for (int i = 0; i < numStrings; i++)
491 {
492 insertRow(ps,unicodeStrings[i] + largeString + unicodeStrings[i] + "pppppppppp",i);
493 }
494 conn.commit();
495
496 rs = stmt.executeQuery("select count(*) from testUnicode");
498 int realNumRows = -1;
499 if (rs.next())
500 realNumRows = rs.getInt(1);
501 if (realNumRows <= 0)
502 System.out.println("FAIL. No rows in table testUnicode");
503 if (realNumRows != numRowsUnicode)
504 System.out.println("FAIL. numRowsUnicode is incorrect");
505
506 }
507 catch (SQLException e) {
508 TestUtil.dumpSQLExceptions(e);
509 }
510 catch (Throwable e) {
511 System.out.println("FAIL -- unexpected exception:" + e.toString());
512 if (debug) e.printStackTrace(System.out);
513 }
514
515 }
516
517
518
521 private static void setCharacterStreamTest(Connection conn)
522 {
523 ResultSet rs;
524 Statement stmt;
525 System.out.println(START + "setCharacterStreamTest");
526 try
527 {
528 stmt = conn.createStatement();
529 stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096')");
531 stmt.execute("create table testUnicode2 (a CLOB(100k))");
532 stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','0')");
533 PreparedStatement ps = conn.prepareStatement(
534 "insert into testUnicode2 values(?)");
535
536 File file = new File (unicodeFileName);
539 InputStream fileIS = new FileInputStream (file);
540 Reader filer = new InputStreamReader (fileIS,"UTF8");
541 ps.setCharacterStream(1, filer, 5009);
543 ps.executeUpdate();
544 filer.close();
545 conn.commit();
546
547 rs = stmt.executeQuery("select a from testUnicode2");
548 while (rs.next())
549 {
550 Clob clob = rs.getClob(1);
551 System.out.println("Length of clob is " + clob.length());
552 Reader r = clob.getCharacterStream();
553 char[] buf = new char[3];
554 for (int i = 0; i < numStrings; i++)
555 {
556 r.read(buf);
557 if (unicodeStrings[i].equals(new String (buf)))
558 System.out.println("unicode string " + i + " matched");
559 else
560 System.out.println("unicode string " + i + " not matched");
561 }
562 for (int i = 0; i < 5000; i++)
563 {
564 int c = r.read();
565 if (c == -1)
566 {
567 System.out.println("EOF reached at i = " + i);
568 break;
569 }
570 if ((char)c != 'p')
571 {
572 System.out.println("A p was missed, got a " + (char)c);
573 }
574 }
575 if (r.read() != -1)
576 System.out.println("EOF was missed");
577 else
578 System.out.println("EOF matched");
579 }
580 conn.commit();
581
582 System.out.println("setCharacterStreamTest finished");
583 }
584 catch (SQLException e)
585 {
586 TestUtil.dumpSQLExceptions(e);
587 }
588 catch (Throwable e)
589 {
590 System.out.println("FAIL -- unexpected exception:" + e.toString());
591 if (debug) e.printStackTrace(System.out);
592 }
593 }
594
595
596
599 private static void prepareUnicodeFile(Connection conn)
600 {
601 System.out.println(START + "prepareUnicodeFile");
602 try
603 {
604 File file = new File (unicodeFileName);
605 OutputStream fos = new FileOutputStream (file);
606 Writer filew = new OutputStreamWriter (fos,"UTF8");
607 filew.write(unicodeStrings[0]);
609 filew.write(unicodeStrings[1]);
610 filew.write(unicodeStrings[2]);
611 for (int i = 0; i < 5000; i++)
612 filew.write('p');
613 filew.close();
614
615 InputStream fis = new FileInputStream (file);
616 Reader filer = new InputStreamReader (fis,"UTF8");
617 char bufs[][] = new char[numStrings][3];
619 for (int i = 0; i < numStrings; i++)
620 {
621 filer.read(bufs[i]);
622 String s = new String (bufs[i]);
623 if (s.equals(unicodeStrings[i]))
624 System.out.println("unicode string " + i + " correct");
625 else
626 System.out.println("FAILED: unicode string " + i + " incorrect");
627 }
628 for (int i = 0; i < 5000; i++)
629 if (filer.read() != 'p')
630 System.out.println("Not a p : i = " + i);
631 if (filer.read() != -1)
632 System.out.println("Not EOF");
633 filer.close();
634 System.out.println("Finished prepareUnicodeFile");
635 }
636 catch (Throwable e)
637 {
638 System.out.println("FAIL -- unexpected exception:" + e.toString());
639 if (debug) e.printStackTrace(System.out);
640 }
641 }
642
643
644
645
650 private static void clobTest0(Connection conn)
651 {
652 ResultSet rs;
653 Statement stmt;
654 System.out.println(START + "clobTest0");
655 try
656 {
657 stmt = conn.createStatement();
658 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
659 byte[] buff = new byte[128];
660 Clob clob;
662 int clobLength, i = 0;
663 while (rs.next()) {
664 i++;
665 clob = rs.getClob(1);
667 if (clob == null)
668 continue;
669 InputStream fin = clob.getAsciiStream();
670 int columnSize = 0;
671 for (;;) {
672 int size = fin.read(buff);
673 if (size == -1)
674 break;
675 columnSize += size;
676 }
677 clobLength = rs.getInt(2);
678
679 if (columnSize != clobLength)
680 System.out.println("test failed, columnSize should be " + clobLength
681 + ", but it is " + columnSize + ", i = " + i);
682 if (columnSize != clob.length())
683 {
684 System.out.println("test failed, clob.length() should be " + columnSize
685 + ", but it is " + clob.length() + ", i = " + i);
686 }
687 }
688 rs.close();
689 conn.commit();
690 System.out.println("clobTest0 finished");
691 }
692 catch (SQLException e) {
693 TestUtil.dumpSQLExceptions(e);
694 }
695 catch (Throwable e) {
696 System.out.println("FAIL -- unexpected exception:" + e.toString());
697 if (debug) e.printStackTrace(System.out);
698 }
699 }
700
701
702
707 private static void clobTest11(Connection conn) {
708
709 ResultSetMetaData met;
710 ResultSet rs;
711 Statement stmt;
712 System.out.println(START + "clobTest1");
713 try {
714 stmt = conn.createStatement();
715 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
716 met = rs.getMetaData();
717 char[] buff = new char[128];
718 int i = 0, clobLength = 0;
720 while (rs.next()) {
721 i++;
722 Clob clob = rs.getClob(1);
724 if (clob == null)
725 continue;
726 Reader reader = clob.getCharacterStream();
727 int columnSize = 0;
728 for (;;) {
729 int size = reader.read(buff);
730 if (size == -1)
731 break;
732 columnSize += size;
734 }
735 clobLength = rs.getInt(2);
736 if (columnSize != clobLength)
737 System.out.println("test failed, columnSize should be " + clobLength
738 + ", but it is " + columnSize + ", i = " + i);
739 if (columnSize != clob.length())
740 System.out.println("test failed, clob.length() should be " + columnSize
741 + ", but it is " + clob.length() + ", i = " + i);
742
743 }
744 rs.close();
745 conn.commit();
746 System.out.println("clobTest11 finished");
747 }
748 catch (SQLException e) {
749 TestUtil.dumpSQLExceptions(e);
750 }
751 catch (Throwable e) {
752 System.out.println("FAIL -- unexpected exception:" + e.toString());
753 if (debug) e.printStackTrace(System.out);
754 }
755 }
756
757
758
762 private static void clobTest12(Connection conn)
763 {
764 ResultSet rs;
765 Statement stmt;
766 System.out.println(START + "clobTest12");
767 try
768 {
769 stmt = conn.createStatement();
770 rs = stmt.executeQuery("select a,b,c from testUnicode");
771 int i = 0, colLength = 0, arrayIndex = 0;
772 while (rs.next())
773 {
774 i++;
775 colLength = rs.getInt(2);
776 arrayIndex = rs.getInt(3);
777 Clob clob = rs.getClob(1);
778 if (clob == null)
779 {
780 System.out.println("row " + i + " is null, skipped");
781 continue;
782 }
783 Reader reader = clob.getCharacterStream();
784
785 int columnSize = 0, c;
786 String compareString = "";
787 for (;;)
788 {
789 c = reader.read();
790 if (c == -1)
791 break;
792 if (columnSize < 3)
793 compareString += (char)c;
794 columnSize ++;
795 }
796 if (compareString.equals(unicodeStrings[arrayIndex]))
797 System.out.println("Succeeded to match, row " + i);
798 else
799 {
800 System.out.println("Failed to match, row " + i +
801 ". compareString = " + compareString + ". arrayIndex = " +
802 arrayIndex + ". unicodeStrings[arrayIndex] = " +
803 unicodeStrings[arrayIndex]);
804
805 }
806 if (columnSize != colLength)
807 System.out.println("test failed, columnSize should be " + colLength
808 + ", but it is " + columnSize + ", i = " + i);
809 else
810 System.out.println("PASSED, row " + i + ", length was " + columnSize);
811 }
812 conn.commit();
813 System.out.println("clobTest12 finished");
814 }
815 catch (SQLException e)
816 {
817 TestUtil.dumpSQLExceptions(e);
818 }
819 catch (Throwable e)
820 {
821 System.out.println("FAIL -- unexpected exception:" + e.toString());
822 if (debug) e.printStackTrace(System.out);
823 }
824 }
825
826
829 private static void clobTest13Trigger(Connection conn)
830 {
831 System.out.println(START + "clobTest13Trigger");
832 try {
833 Statement stmt = conn.createStatement();
834 stmt.executeUpdate("CREATE TABLE clobTest13TriggerA (a CLOB(400k), b int)");
835 stmt.executeUpdate("CREATE TABLE clobTest13TriggerB (a CLOB(400k), b int)");
836 stmt.executeUpdate(
837 "create trigger T13A after update on testCLOB_MAIN " +
838 "referencing new as n old as o " +
839 "for each row mode db2sql "+
840 "insert into clobTest13TriggerA(a, b) values (n.a, n.b)");
841 stmt.executeUpdate(
842 "create trigger T13B after INSERT on clobTest13TriggerA " +
843 "referencing new_table as n " +
844 "for each statement mode db2sql "+
845 "insert into clobTest13TriggerB(a, b) select n.a, n.b from n");
846
847 conn.commit();
848 ResultSet rs = stmt.executeQuery(
849 "select a,length(a),b from testCLOB_MAIN order by b");
850
851 showClobContents("testCLOB_MAIN", rs);
852
853 rs.close();
854 conn.commit();
855 int rowCount = stmt.executeUpdate("UPDATE testCLOB_MAIN set b = b + 0");
856 System.out.println("main update row count :" + rowCount);
857 conn.commit();
858 rs = stmt.executeQuery(
859 "select a,length(a),b from clobTest13TriggerA order by b");
860 showClobContents("row trigger", rs);
861 rs.close();
862 conn.commit();
863
864 rs = stmt.executeQuery(
865 "select a,length(a),b from clobTest13TriggerB order by b");
866 showClobContents("statement trigger", rs);
867 rs.close();
868 conn.commit();
869
870 stmt.executeUpdate("DROP TRIGGER T13A");
871 stmt.executeUpdate("DROP TABLE clobTest13TriggerB");
872 stmt.executeUpdate("DROP TABLE clobTest13TriggerA");
873
874 stmt.close();
875 conn.commit();
876 System.out.println("clobTest13Trigger finished");
877 }
878 catch (SQLException e) {
879 TestUtil.dumpSQLExceptions(e);
880 do {
881 e.printStackTrace(System.out);
882 e = e.getNextException();
883 } while (e != null);
884
885 }
886 catch (Throwable e) {
887 System.out.println("FAIL -- unexpected exception:" + e.toString());
888 if (debug) e.printStackTrace(System.out);
889 }
890
891 }
892
893
894
895
896
900 private static void clobTest2(Connection conn)
901 {
902 ResultSet rs;
903 Statement stmt;
904 System.out.println(START + "clobTest2");
905 try
906 {
907 stmt = conn.createStatement();
908 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
909 int i = 0, clobLength = 0;
910 Clob clob;
911 while (rs.next())
912 {
913 i++;
914 clob = rs.getClob(1);
915 if (clob == null)
916 continue;
917 clobLength = rs.getInt(2);
918 blobclob4BLOB.printInterval(clob, 9905, 50, 0, i, clobLength);
919 blobclob4BLOB.printInterval(clob, 5910, 150, 1, i, clobLength);
920 blobclob4BLOB.printInterval(clob, 5910, 50, 2, i, clobLength);
921 blobclob4BLOB.printInterval(clob, 204, 50, 3, i, clobLength);
922 blobclob4BLOB.printInterval(clob, 68, 50, 4, i, clobLength);
923 blobclob4BLOB.printInterval(clob, 1, 50, 5, i, clobLength);
924 blobclob4BLOB.printInterval(clob, 1, 1, 6, i, clobLength);
925 blobclob4BLOB.printInterval(
926 clob, 1, 0, 7, i, clobLength); blobclob4BLOB.printInterval(
928 clob, clobLength + 1, 0, 8, i, clobLength);
938 if (clobLength > 100)
939 {
940 String res = clob.getSubString(clobLength-99,200);
941 System.out.println(i + "(9) ");
942 if (res.length() != 100)
943 System.out.println("FAIL : length of substring is " +
944 res.length() + " should be 100");
945 else
946 System.out.println(res);
947 }
948 }
949 rs.close();
950 System.out.println("clobTest2 finished");
951 }
952 catch (SQLException e) {
953 TestUtil.dumpSQLExceptions(e);
954 }
955 catch (Throwable e) {
956 System.out.println("FAIL -- unexpected exception:" + e.toString());
957 if (debug) e.printStackTrace(System.out);
958 }
959 }
960
961
962
966 private static void clobTest22(Connection conn)
967 {
968 ResultSet rs;
969 Statement stmt;
970 System.out.println(START + "clobTest22");
971 try
972 {
973 stmt = conn.createStatement();
974 rs = stmt.executeQuery("select a,b,c from testUnicode");
975 int i = 0, clobLength = 0, arrayIndex = 0;
976 Clob clob;
977 while (rs.next())
978 {
979 i++;
980 System.out.print("Row " + i + " : ");
981 clob = rs.getClob(1);
982 if (clob == null)
983 continue;
984 clobLength = rs.getInt(2);
985 arrayIndex = rs.getInt(3);
986 if (clob.getSubString(1,3).equals(unicodeStrings[arrayIndex]))
987 System.out.println("Succeeded");
988 else
989 System.out.println("Failed");
990 if (clobLength > 5000)
991 {
992 if (clob.getSubString(5004,3).equals(unicodeStrings[arrayIndex]))
993 System.out.println("Second time Succeeded");
994 else
995 System.out.println("Second time Failed");
996 }
997 }
998 System.out.println("clobTest22 finished");
999 }
1000 catch (SQLException e)
1001 {
1002 TestUtil.dumpSQLExceptions(e);
1003 }
1004 catch (Throwable e)
1005 {
1006 System.out.println("FAIL -- unexpected exception:" + e.toString());
1007 if (debug) e.printStackTrace(System.out);
1008 }
1009 }
1010
1011
1012
1013
1017 private static void clobTest3(Connection conn)
1018 {
1019 ResultSet rs;
1020 Statement stmt;
1021 System.out.println(START + "clobTest3");
1022 try
1023 {
1024 stmt = conn.createStatement();
1025 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
1026 int i = 0, clobLength = 0;
1027 Clob clob;
1028 while (rs.next())
1029 {
1030 i++;
1031 clob = rs.getClob(1);
1032 if (clob == null)
1033 continue;
1034 clobLength = rs.getInt(2);
1035 if (clobLength > 20000)
1036 continue;
1037 blobclob4BLOB.printPosition(i,"horse",1,clob, clobLength);
1038 blobclob4BLOB.printPosition(i,"ouch",1,clob, clobLength);
1039 blobclob4BLOB.printPosition(i,"\n",1,clob, clobLength);
1040 blobclob4BLOB.printPosition(i,"",1,clob, clobLength);
1041 blobclob4BLOB.printPosition(i,"Beginning",1,clob, clobLength);
1042 blobclob4BLOB.printPosition(i,"Beginning",2,clob, clobLength);
1043 blobclob4BLOB.printPosition(i,"position-69",1,clob, clobLength);
1044 blobclob4BLOB.printPosition(i,"This-is-position-204",1,clob, clobLength);
1045 blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",1,clob, clobLength);
1046 blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",5910,clob, clobLength);
1047 blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",5911,clob, clobLength);
1048 blobclob4BLOB.printPosition(i,"Position-9907",1,clob, clobLength);
1049 }
1050 rs.close();
1051 System.out.println("clobTest3 finished");
1052 }
1053 catch (SQLException e) {
1054 TestUtil.dumpSQLExceptions(e);
1055 }
1056 catch (Throwable e) {
1057 System.out.println("FAIL -- unexpected exception:" + e.toString());
1058 if (debug) e.printStackTrace(System.out);
1059 }
1060 }
1061
1062
1063
1067 private static void clobTest32(Connection conn)
1068 {
1069 ResultSet rs;
1070 Statement stmt;
1071 System.out.println(START + "clobTest32");
1072 try
1073 {
1074 stmt = conn.createStatement();
1075 rs = stmt.executeQuery("select a,b,c from testUnicode");
1076 int i = 0, clobLength = 0, arrayIndex = 0;
1077 long pos = 0;
1078 Clob clob;
1079 while (rs.next())
1080 {
1081 i++;
1082 clob = rs.getClob(1);
1083 if (clob == null)
1084 continue;
1085 clobLength = rs.getInt(2);
1086 arrayIndex = rs.getInt(3);
1087
1088 pos = clob.position(unicodeStrings[arrayIndex],1);
1089 if (pos == 1)
1090 System.out.println("Succeeded: Found unicode string " + arrayIndex +
1091 " at position " + pos + ",row " + i);
1092 else
1093 System.out.println("Failed: Found unicode string " + arrayIndex +
1094 " at position " + pos + ",row " + i);
1095
1096 pos = clob.position(unicodeStrings[arrayIndex],4000);
1097 if (pos == 5004 || (pos == -1 && clobLength < 4000))
1098 System.out.println("Succeeded: Found unicode string " + arrayIndex +
1099 " at position " + pos + ",row " + i);
1100 else
1101 System.out.println("Failed: Found unicode string " + arrayIndex +
1102 " at position " + pos + ",row " + i);
1103 }
1104 System.out.println("clobTest32 finished");
1105 }
1106 catch (SQLException e)
1107 {
1108 TestUtil.dumpSQLExceptions(e);
1109 }
1110 catch (Throwable e)
1111 {
1112 System.out.println("FAIL -- unexpected exception:" + e.toString());
1113 if (debug) e.printStackTrace(System.out);
1114 }
1115 }
1116
1117
1118
1122 private static void clobTest4(Connection conn)
1123 {
1124 ResultSet rs, rs2;
1125 Statement stmt, stmt2;
1126 System.out.println(START + "clobTest4");
1127 try
1128 {
1129 stmt = conn.createStatement();
1130 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
1131 int i = 0, clobLength = 0;
1132 Clob clob;
1133 while (rs.next())
1134 {
1135 i++;
1136 clob = rs.getClob(1);
1137 if (clob == null)
1138 continue;
1139 clobLength = rs.getInt(2);
1140 if (clobLength > 20000)
1141 {
1142 System.out.println("testCLOB_MAIN row " + i + " skipped (too large)");
1143 continue;
1144 }
1145 stmt2 = conn.createStatement();
1148 rs2 = stmt2.executeQuery("select a,b from searchClob");
1149 int j = 0, clobLength2 = 0;
1150 Clob searchClob;
1151 String searchStr;
1152 while (rs2.next())
1153 {
1154 j++;
1155 searchClob = rs2.getClob(1);
1156 if (searchClob == null)
1157 continue;
1158 clobLength2 = rs2.getInt(2);
1159 if (clobLength2 > 20000)
1160 {
1161 System.out.println("searchClob row " + j + " skipped (too large)");
1162 continue;
1163 }
1164 if (clobLength2 < 150)
1165 searchStr = rs2.getString(1);
1166 else
1167 searchStr = null;
1168
1169 printPositionClob(i,searchStr,1,clob,j,searchClob);
1170 }
1171 }
1172 rs.close();
1173 System.out.println("clobTest4 finished");
1174 }
1175 catch (SQLException e) {
1176 TestUtil.dumpSQLExceptions(e);
1177 if (debug) e.printStackTrace(System.out);
1178 }
1179 catch (Throwable e) {
1180 System.out.println("FAIL -- unexpected exception:" + e.toString());
1181 if (debug) e.printStackTrace(System.out);
1182 }
1183 }
1184
1185
1186
1190 private static void clobTest42(Connection conn)
1191 {
1192 ResultSet rs;
1193 Statement stmt;
1194 System.out.println(START + "clobTest42");
1195 try
1196 {
1197 stmt = conn.createStatement();
1198 rs = stmt.executeQuery("select a,b,c from testUnicode");
1199 Clob [] clobArray = new Clob [numRowsUnicode];
1200 int i = 0;
1201 long pos = 0;
1202 while (rs.next())
1203 {
1204 clobArray[i++] = rs.getClob(1);
1205 }
1206
1207 for (int j = 0; j < 3; j++)
1208 {
1209 pos = clobArray[j+3].position(clobArray[j],1);
1210 if (pos == 1)
1211 System.out.println("Succeeded: Found clob at position " + pos + ",row " + j);
1212 else
1213 System.out.println("Failed: Found clob at position " + pos + ",row " + j);
1214 }
1216 System.out.println("clobTest42 finished");
1217 }
1218 catch (SQLException e)
1219 {
1220 TestUtil.dumpSQLExceptions(e);
1221 }
1222 catch (Throwable e)
1223 {
1224 System.out.println("FAIL -- unexpected exception:" + e.toString());
1225 if (debug) e.printStackTrace(System.out);
1226 }
1227 }
1228
1229
1230
1231 private static void printPositionClob(
1232 int rowNum,
1233 String searchStr,
1234 long position,
1235 Clob clob,
1236 int searchRowNum,
1237 Clob searchClob)
1238 {
1239 try
1240 {
1241 long result = clob.position(searchClob,position);
1242 if ("".equals(searchStr) && (result == 1)) {
1243 System.out.println("position(clob) FOUND @ 1 with empty search clob in clob of length " + clob.length());
1244 return;
1245 }
1246 if (result != -1)
1247 {
1248 System.out.print("Found ");
1249 if (searchStr != null)
1250 System.out.print(searchStr);
1251 else
1252 System.out.print("clob (row " + searchRowNum + ") ");
1253 System.out.println(" in row " + rowNum + " at position " + result);
1254 }
1255 else {
1256 System.out.println("position(clob) NOT FOUND " + rowNum + " searchStr " +
1257 (searchStr != null ? searchStr : ">150chars"));
1258 }
1259 }
1260 catch (SQLException e)
1261 {
1262 TestUtil.dumpSQLExceptions(e);
1263 }
1264 }
1265
1266
1267
1268
1269 private static void clobTest51(Connection conn) {
1272
1273 ResultSetMetaData met;
1274 ResultSet rs;
1275 Statement stmt;
1276 System.out.println(START + "clobTest51");
1277 try {
1278 stmt = conn.createStatement();
1279 stmt.execute("create table testCLOB10 (a CLOB(10))");
1280
1281 PreparedStatement ps = conn.prepareStatement(
1282 "insert into testCLOB10 values(?)");
1283 String val = "";
1284 for (int i = 0; i < 10; i++)
1285 {
1286 ps.setString(1, val);
1288 ps.executeUpdate();
1289 val += "x";
1290 }
1291
1292 rs = stmt.executeQuery("select a from testCLOB10");
1293 met = rs.getMetaData();
1294 byte[] buff = new byte[128];
1295 int j = 0;
1296 while (rs.next())
1298 {
1299 Clob clob = rs.getClob(1);
1301 if (clob == null)
1302 continue;
1303 InputStream fin = clob.getAsciiStream();
1304 int columnSize = 0;
1305 for (;;)
1306 {
1307 int size = fin.read(buff);
1308 if (size == -1)
1309 break;
1310 columnSize += size;
1311 }
1312 if (columnSize != j)
1313 System.out.println("FAIL - Expected clob size : " + j +
1314 " Got clob size : " + columnSize);
1315 if (clob.length() != j)
1316 System.out.println("FAIL - Expected clob length : " + j +
1317 " Got clob length : " + clob.length());
1318 j++;
1319 }
1320 System.out.println("clobTest51 finished");
1321 }
1322 catch (SQLException e) {
1323 if (isDerbyNet)
1324 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
1325 else
1326 TestUtil.dumpSQLExceptions(e);
1327 }
1328 catch (Throwable e) {
1329 System.out.println("FAIL -- unexpected exception:" + e.toString());
1330 if (debug) e.printStackTrace(System.out);
1331 }
1332 }
1333
1334
1335 private static void clobTest52(Connection conn) {
1337
1338 ResultSetMetaData met;
1339 ResultSet rs;
1340 Statement stmt;
1341
1342 try {
1343 System.out.println(START + "clobTest52");
1344 stmt = conn.createStatement();
1345 System.out.println("create table testInteger (a integer)");
1346 stmt.execute("create table testInteger (a integer)");
1347
1348 int i = 1;
1349 System.out.println("insert into testInteger values('158')");
1350 PreparedStatement ps = conn.prepareStatement("insert into testInteger values(158)");
1351 ps.executeUpdate();
1352
1353 System.out.println("select a from testInteger");
1354 rs = stmt.executeQuery("select a from testInteger");
1355 met = rs.getMetaData();
1356 while (rs.next()) {
1357 System.out.println("getClob(1)");
1359 Clob clob = rs.getClob(1);
1360 if (clob == null)
1361 System.out.println("clob is null");
1362 else
1363 System.out.println("clob is not null");
1364 }
1365 System.out.println("clobTest52 finished");
1366 }
1367 catch (SQLException e) {
1368 System.out.println("52: SQLException");
1369 if (isDerbyNet)
1370 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
1371 else
1372 TestUtil.dumpSQLExceptions(e);
1373 }
1374 catch (Throwable e) {
1375 System.out.println("52: Throwable");
1376 System.out.println("FAIL -- unexpected exception:" + e.toString());
1377 if (debug) e.printStackTrace(System.out);
1378 }
1379 }
1380
1381
1382 private static void clobTest53(Connection conn) {
1385
1386 ResultSetMetaData met;
1387 ResultSet rs;
1388 Statement stmt;
1389 System.out.println(START + "clobTest53");
1390 try {
1391 stmt = conn.createStatement();
1392 stmt.execute("create table testClobColumn (a clob(1K))");
1393
1394 System.out.println("clobTest53 finished");
1395 }
1396 catch (SQLException e) {
1397 TestUtil.dumpSQLExceptions(e);
1398 }
1399 catch (Throwable e) {
1400 System.out.println("FAIL -- unexpected exception:" + e.toString());
1401 if (debug) e.printStackTrace(System.out);
1402 }
1403 }
1404
1405
1406
1410 private static void clobTest54(Connection conn)
1411 {
1412 ResultSet rs;
1413 Statement stmt1, stmt2;
1414 System.out.println(START + "clobTest54");
1415 try
1416 {
1417 stmt1 = conn.createStatement();
1418 stmt1.execute("create table testClob2 (a integer, b integer)");
1419 PreparedStatement ps = conn.prepareStatement(
1420 "insert into testClob2 values(?,?)");
1421 stmt2 = conn.createStatement();
1422 rs = stmt2.executeQuery("select a,b from testCLOB_MAIN");
1423 Clob clob;
1424 int clobLength;
1425 while (rs.next())
1426 {
1427 clob = rs.getClob(1);
1429 if (clob == null)
1430 continue;
1431 clobLength = rs.getInt(2);
1432 ps.setClob(1,clob);
1433 ps.setInt(2,clobLength);
1434 ps.executeUpdate();
1435 }
1436 rs.close();
1437 conn.commit();
1438
1439 System.out.println("clobTest54 finished");
1440 }
1441 catch (SQLException e) {
1442 TestUtil.dumpSQLExceptions(e,true);
1444 }
1445 catch (Throwable e) {
1446 System.out.println("FAIL -- unexpected exception:" + e.toString());
1447 if (debug) e.printStackTrace(System.out);
1448 }
1449 System.out.println("end clobTest54");
1450 }
1451
1452
1453
1457 private static void clobTest6(Connection conn)
1458 {
1459 ResultSet rs;
1460 Statement stmt;
1461 System.out.println(START + "clobTest6");
1462 try
1463 {
1464 stmt = conn.createStatement();
1465 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
1466 int i = 0, clobLength = 0;
1467 Clob clob;
1468 rs.next();
1469 clob = rs.getClob(1);
1470 clobLength = rs.getInt(2);
1471 rs.close();
1472 if (clob == null)
1473 return;
1474
1475
1476 try
1478 {
1479 clob.getSubString(0,5);
1480 System.out.println("FAIL = clob.getSubString(0,5)");
1481 }
1482 catch (SQLException e)
1483 {
1484 boolean isExpected = isOutOfBoundException(e);
1485
1486 TestUtil.dumpSQLExceptions(e, isExpected);
1487 }
1488
1489 try
1491 {
1492 clob.getSubString(1,-76);
1493 System.out.println("FAIL = getSubString(1,-76)");
1494 }
1495 catch (SQLException e)
1496 {
1497 TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
1498 }
1499 try {
1501 clob.getSubString(1,-1);
1502 System.out.println("FAIL = getSubString(1,-1)");
1503 } catch (SQLException e) {
1504 TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
1505 }
1506 try
1508 {
1509 clob.getSubString(0,0);
1510 System.out.println("FAIL = getSubString(0,0)");
1511 }
1512 catch (SQLException e)
1513 {
1514 TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
1515 }
1516 try {
1518 clob.getSubString(clobLength + 2,0);
1519 System.out.println("FAIL = getSubString(clobLength + 2,0)");
1520 } catch (SQLException e) {
1521 TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
1522 }
1523 try
1525 {
1526 clob.position("xx",-4000);
1527 System.out.println("FAIL = position('xx',-4000)");
1528 }
1529 catch (SQLException e)
1530 {
1531 TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
1532 }
1533 try
1535 {
1536 clob.position((String ) null,5);
1537 System.out.println("FAIL = position((String) null,5)");
1538 }
1539 catch (SQLException e)
1540 {
1541 TestUtil.dumpSQLExceptions(e, "XJ072".equals(e.getSQLState()));
1542 }
1543 try
1545 {
1546 clob.position(clob,-42);
1547 System.out.println("FAIL = position(clob,-42)");
1548 }
1549 catch (SQLException e)
1550 {
1551 TestUtil.dumpSQLExceptions(e, isOutOfBoundException(e));
1552 }
1553 try
1555 {
1556 clob.position((Clob ) null,5);
1557 System.out.println("FAIL = pposition((Clob) null,5)");
1558 }
1559 catch (SQLException e)
1560 {
1561 TestUtil.dumpSQLExceptions(e, "XJ072".equals(e.getSQLState()));
1562 }
1563 System.out.println("clobTest6 finished");
1564 }
1565 catch (SQLException e) {
1566 TestUtil.dumpSQLExceptions(e);
1567 }
1568 catch (Throwable e) {
1569 System.out.println("FAIL -- unexpected exception:" + e.toString());
1570 if (debug) e.printStackTrace(System.out);
1571 }
1572 }
1573
1574
1575
1579 private static void clobTest7(Connection conn)
1580 {
1581 ResultSet rs, rs2;
1582 Statement stmt1, stmt2;
1583 System.out.println(START + "clobTest7");
1584 try
1585 {
1586 stmt1 = conn.createStatement();
1587 stmt1.execute("create table testClob7 (a CLOB(300k), b integer)");
1588 PreparedStatement ps = conn.prepareStatement(
1589 "insert into testClob7 values(?,?)");
1590 stmt2 = conn.createStatement();
1591 rs = stmt2.executeQuery("select a,b from testCLOB_MAIN");
1592 Clob clob;
1593 int clobLength;
1594 int rownum = 0;
1595 while (rs.next())
1596 {
1597 clob = rs.getClob(1);
1599 if (clob == null)
1600 continue;
1601 clobLength = rs.getInt(2);
1602 ps.setClob(1,clob);
1603 ps.setInt(2,clobLength);
1604 ps.executeUpdate();
1605 }
1606 rs.close();
1607 conn.commit();
1608
1609 rs2 = stmt2.executeQuery("select a,b from testClob7");
1610 Clob clob2;
1611 int clobLength2, j = 0;
1612 while (rs2.next())
1613 {
1614 j++;
1615 clob2 = rs2.getClob(1);
1617 if (clob2 == null)
1618 continue;
1619 clobLength2 = rs2.getInt(2);
1620 if (clob2.length() != clobLength2)
1621 System.out.println("FAILED at row " + j);
1622 }
1623 rs2.close();
1624
1625 conn.commit();
1626 System.out.println("clobTest7 finished");
1627 }
1628 catch (SQLException e) {
1629 TestUtil.dumpSQLExceptions(e);
1630 if (debug) e.printStackTrace(System.out);
1631 }
1632 catch (Throwable e) {
1633 System.out.println("FAIL -- unexpected exception:" + e.toString());
1634 if (debug) e.printStackTrace(System.out);
1635 }
1636 }
1637
1638
1641 private static void clobTest8(Connection conn)
1642 {
1643 System.out.println(START + "clobTest8");
1644 try {
1645 Statement s = conn.createStatement();
1646
1647 s.execute("CREATE TABLE C8.T8POS(id INT NOT NULL PRIMARY KEY, DD CLOB(1m), pos INT, L INT)");
1648 s.execute("CREATE TABLE C8.T8PATT(PATT CLOB(300k))");
1649
1650 char[] fill = new char[4];
1652 fill[0] = 'd'; fill[1] = '\u03a9'; fill[2] = '\u0e14'; fill[3] = 'j';
1657 char[] base = new char[256 * 1024];
1658
1659 for (int i = 0; i < base.length; i += 4) {
1660
1661 base[i] = fill[0];
1662 base[i+1] = fill[1];
1663 base[i+2] = fill[2];
1664 base[i+3] = fill[3];
1665
1666 }
1667
1668 char[] patternBase = new char[2 * 1024];
1669 for (int i = 0; i < patternBase.length; i += 8) {
1670
1671 patternBase[i] = 'p';
1672 patternBase[i+1] = 'a';
1673 patternBase[i+2] = 't';
1674 patternBase[i+3] = '\u03aa';
1675 patternBase[i+4] = (char) i; patternBase[i+5] = 'b';
1677 patternBase[i+6] = 'm';
1678 patternBase[i+7] = '\u0e15';
1679
1680 }
1681
1682 PreparedStatement ps = conn.prepareStatement("INSERT INTO C8.T8POS VALUES (?, ?, ?, ?)");
1683 PreparedStatement psp = conn.prepareStatement("INSERT INTO C8.T8PATT VALUES (?)");
1684
1685 T8insert(ps, 1, base, 256, patternBase, 8, 100, true);
1686 T8insert(ps, 2, base, 3988, patternBase, 8, 2045, true);
1687 T8insert(ps, 3, base, 16321, patternBase, 8, 4566, true);
1688 T8insert(ps, 4, base, 45662, patternBase, 8, 34555, true);
1689 T8insert(ps, 5, base, 134752, patternBase, 8, 67889, true);
1690 T8insert(ps, 6, base, 303, patternBase, 8, 80, false);
1691 T8insert(ps, 7, base, 4566, patternBase, 8, 2086, false);
1692 T8insert(ps, 8, base, 17882, patternBase, 8, 4426, false);
1693 T8insert(ps, 9, base, 41567, patternBase, 8, 31455, false);
1694 String pstr = T8insert(ps, 10, base, 114732, patternBase, 8, 87809, false);
1695
1696 conn.commit();
1697
1698 psp.setString(1, pstr);
1699 psp.executeUpdate();
1700
1701 System.out.println("small string pattern");
1702 checkClob8(s, pstr);
1703 conn.commit();
1704
1705 System.out.println("small java.sql.Clob pattern");
1706 ResultSet rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
1707 rsc.next();
1708 checkClob8(s, rsc.getClob(1));
1709
1710 rsc.close();
1711
1712
1713 conn.commit();
1714
1715 s.execute("DELETE FROM C8.T8POS");
1716 s.execute("DELETE FROM C8.T8PATT");
1717
1718
1719 T8insert(ps, 1, base, 256, patternBase, 134, 100, true);
1720 T8insert(ps, 2, base, 3988, patternBase, 134, 2045, true);
1721 T8insert(ps, 3, base, 16321, patternBase, 134, 4566, true);
1722 T8insert(ps, 4, base, 45662, patternBase, 134, 34555, true);
1723 T8insert(ps, 5, base, 134752, patternBase, 134, 67889, true);
1724 T8insert(ps, 6, base, 303, patternBase, 134, 80, false);
1725 T8insert(ps, 7, base, 4566, patternBase, 134, 2086, false);
1726 T8insert(ps, 8, base, 17882, patternBase, 134, 4426, false);
1727 T8insert(ps, 9, base, 41567, patternBase, 134, 31455, false);
1728 pstr = T8insert(ps, 10, base, 114732, patternBase, 134, 87809, false);
1729
1730 conn.commit();
1731 psp.setString(1, pstr);
1732 psp.executeUpdate();
1733 conn.commit();
1734
1735
1736 System.out.println("medium string pattern");
1737 checkClob8(s, pstr);
1738 conn.commit();
1739
1740 System.out.println("medium java.sql.Clob pattern");
1741 rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
1742 rsc.next();
1743 checkClob8(s, rsc.getClob(1));
1744
1745 s.execute("DELETE FROM C8.T8POS");
1746 s.execute("DELETE FROM C8.T8PATT");
1747
1748 T8insert(ps, 1, base, 256, patternBase, 679, 100, true);
1749 T8insert(ps, 2, base, 3988, patternBase, 679, 2045, true);
1750 T8insert(ps, 3, base, 16321, patternBase, 679, 4566, true);
1751 T8insert(ps, 4, base, 45662, patternBase, 679, 34555, true);
1752 T8insert(ps, 5, base, 134752, patternBase, 679, 67889, true);
1753 T8insert(ps, 6, base, 303, patternBase, 679, 80, false);
1754 T8insert(ps, 7, base, 4566, patternBase, 679, 2086, false);
1755 T8insert(ps, 8, base, 17882, patternBase, 679, 4426, false);
1756 T8insert(ps, 9, base, 41567, patternBase, 679, 31455, false);
1757 pstr = T8insert(ps, 10, base, 114732, patternBase, 679, 87809, false);
1758
1759 conn.commit();
1760 psp.setString(1, pstr);
1761 psp.executeUpdate();
1762 conn.commit();
1763
1764
1765 System.out.println("long string pattern");
1766 checkClob8(s, pstr);
1767 conn.commit();
1768
1769 System.out.println("long java.sql.Clob pattern");
1770 rsc = s.executeQuery("SELECT PATT FROM C8.T8PATT");
1771 rsc.next();
1772 checkClob8(s, rsc.getClob(1));
1773
1774 s.execute("DELETE FROM C8.T8POS");
1775 s.execute("DELETE FROM C8.T8PATT");
1776 ps.close();
1777 psp.close();
1778
1780 s.execute("DROP TABLE C8.T8POS");
1781 s.execute("DROP TABLE C8.T8PATT");
1782
1783 s.close();
1784
1785 conn.commit();
1786
1787 System.out.println("complete clobTest8");
1788
1789
1790 } catch (SQLException e) {
1791 TestUtil.dumpSQLExceptions(e);
1792 e.printStackTrace(System.out);
1793 }
1794 catch (Throwable e) {
1795 System.out.println("FAIL -- unexpected exception:" + e.toString());
1796 e.printStackTrace(System.out);
1797 }
1798 }
1799
1800 private static void checkClob8(Statement s, String pstr) throws SQLException {
1801
1802 ResultSet rs = s.executeQuery("SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");
1803
1804 while (rs.next()) {
1805
1806 int id = rs.getInt(1);
1807
1808 System.out.print("@" + id + " ");
1809
1810 java.sql.Clob cl = rs.getClob(2);
1811
1812 int pos = rs.getInt(3);
1813 int len = rs.getInt(4);
1814
1815 long clobPosition = cl.position(pstr, 1);
1816 if (clobPosition == (long) pos) {
1817 System.out.print(" position MATCH("+pos+")");
1818 } else {
1819 System.out.print(" position FAIL("+clobPosition+"!=" + pos +")");
1820 }
1821
1822 System.out.println("");
1823 }
1824 rs.close();
1825 }
1826 private static void checkClob8(Statement s, Clob pstr) throws SQLException {
1827 ResultSet rs = s.executeQuery("SELECT ID, DD, POS, L FROM C8.T8POS ORDER BY 1");
1828
1829 while (rs.next()) {
1830
1831 int id = rs.getInt(1);
1832
1833 System.out.print("@" + id + " ");
1834
1835 java.sql.Clob cl = rs.getClob(2);
1836
1837 int pos = rs.getInt(3);
1838 int len = rs.getInt(4);
1839
1840 long clobPosition = cl.position(pstr, 1);
1841 if (clobPosition == (long) pos) {
1842 System.out.print(" position MATCH("+pos+")");
1843 } else {
1844 System.out.print(" position FAIL("+clobPosition+"!=" + pos +")");
1845 }
1846
1847 System.out.println("");
1848 }
1849 rs.close();
1850 }
1851
1852 private static String T8insert(PreparedStatement ps, int id, char[] base, int bl, char[] pattern, int pl, int pos, boolean addPattern)
1853 throws SQLException {
1854
1855 StringBuffer sb = new StringBuffer ();
1856 sb.append(base, 0, bl);
1857
1858
1862 int last = addPatternPrefix(sb, pattern, pl, 5, 10);
1863
1864 if (last >= (pos / 2))
1865 pos = (last + 10) * 2;
1866
1867 last = addPatternPrefix(sb, pattern, pl, pl/2, pos/2);
1869
1870 if (last >= pos)
1871 pos = last + 13;
1872
1873 pos = addPatternPrefix(sb, pattern, pl, pl - 1, pos);
1875
1876 if (addPattern) {
1877 sb.insert(pos, pattern, 0, pl);
1879 } else {
1880 pos = -1;
1881 }
1882
1883
1884 String dd = sb.toString();
1885 String pstr = new String (pattern, 0, pl);
1886
1887 if (pos != dd.indexOf(pstr)) {
1888 System.out.println("FAIL - test confused pattern not at expected location");
1889
1890 System.out.println("POS = " + pos + " index " + dd.indexOf(pstr));
1891 System.out.println("LENG " + dd.length());
1892 }
1894
1895
1896 if (pos != -1)
1898 pos = pos + 1;
1899
1900 ps.setInt(1, id);
1901 ps.setString(2, dd);
1902 ps.setInt(3, pos);
1903 ps.setInt(4, dd.length());
1904 ps.executeUpdate();
1905
1906 return pstr;
1907
1908 }
1909
1910 private static int addPatternPrefix(StringBuffer sb, char[] pattern, int pl, int fakeCount, int pos) {
1911
1912 for (int i = 0; i < fakeCount && i < (pl - 1); i++) {
1913
1914 sb.insert(pos, pattern, 0, i + 1);
1915 pos += i + 1;
1916 }
1917
1918 return pos;
1919 }
1920
1921
1922
1923 private static void clobTest91(Connection conn)
1926 {
1927 ResultSet rs;
1928 Statement stmt;
1929 System.out.println(START + "clobTest91");
1930 try {
1931 stmt = conn.createStatement();
1932 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
1933 byte[] buff = new byte[128];
1934 Clob [] clobArray = new Clob [numRows];
1935 int[] clobLengthArray = new int[numRows];
1936 int j = 0;
1937 while (rs.next())
1938 {
1939 clobArray[j] = rs.getClob(1);
1940 clobLengthArray[j++] = rs.getInt(2);
1941 }
1942 rs.close();
1943 stmt.close();
1944
1945 for (int i = 0; i < numRows; i++)
1946 {
1947 if (clobArray[i] == null)
1948 {
1949 System.out.println("row " + i + " is null, skipped");
1950 continue;
1951 }
1952 InputStream fin = clobArray[i].getAsciiStream();
1953 int columnSize = 0;
1954 for (;;) {
1955 int size = fin.read(buff);
1956 if (size == -1)
1957 break;
1958 columnSize += size;
1959 }
1960 if (columnSize != clobLengthArray[i])
1961 System.out.println("test failed, columnSize should be " +
1962 clobLengthArray[i] + ", but it is " + columnSize + ", i = " + i);
1963 if (columnSize != clobArray[i].length())
1964 System.out.println("test failed, clobArray[i].length() should be " + columnSize
1965 + ", but it is " + clobArray[i].length() + ", i = " + i);
1966 System.out.println("done row " + i + ", length was " + clobLengthArray[i]);
1967 }
1968 System.out.println("clobTest91 finished");
1969 }
1970 catch (SQLException e) {
1971 TestUtil.dumpSQLExceptions(e);
1972 }
1973 catch (Throwable e) {
1974 System.out.println("FAIL -- unexpected exception:" + e.toString());
1975 if (debug) e.printStackTrace(System.out);
1976 }
1977 }
1978
1979
1980
1984 private static void clobTest92(Connection conn)
1985 {
1986 ResultSet rs;
1987 Statement stmt,stmt2;
1988 System.out.println(START + "clobTest92");
1989 try
1990 {
1991 stmt = conn.createStatement();
1992 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
1993 Clob clob = null, shortClob = null;
1995 int clobLength;
1996 while (rs.next())
1997 {
1998 clobLength = rs.getInt(2);
1999 if (clobLength == 10000)
2000 clob = rs.getClob(1);
2001 if (clobLength == 26)
2002 shortClob = rs.getClob(1);
2003 }
2004 rs.close();
2005
2006 Connection conn2 = ij.startJBMS();
2007 conn2.setAutoCommit(false);
2010
2011 stmt2 = conn2.createStatement();
2014 stmt2.executeUpdate("update testCLOB_MAIN set a = 'foo' where b = 26");
2015 if (shortClob.length() != 26)
2016 System.out.println("FAILED: clob length changed to " + shortClob.length());
2017 stmt2 = conn2.createStatement();
2019 stmt2.executeUpdate("update testCLOB_MAIN set b = b + 1 where b = 10000");
2020
2021 conn.commit();
2022 conn2.rollback();
2023 System.out.println("clobTest92 finished");
2024 }
2025 catch (SQLException e) {
2026 TestUtil.dumpSQLExceptions(e);
2027 }
2028 catch (Throwable e) {
2029 System.out.println("FAIL -- unexpected exception:" + e.toString());
2030 if (debug) e.printStackTrace(System.out);
2031 }
2032 }
2033
2034
2035
2038 private static void clobTest93(Connection conn)
2039 {
2040 ResultSet rs;
2041 Statement stmt, stmt2;
2042 System.out.println(START + "clobTest93");
2043 try
2044 {
2045 stmt = conn.createStatement();
2046 stmt.execute("create table testLongRowClob (a varchar(2000))");
2048 stmt.execute("alter table testLongRowClob add column b varchar(3000)");
2049 stmt.execute("alter table testLongRowClob add column c varchar(2000)");
2050 stmt.execute("alter table testLongRowClob add column d varchar(3000)");
2051 stmt.execute("alter table testLongRowClob add column e CLOB(400k)");
2052 PreparedStatement ps = conn.prepareStatement(
2053 "insert into testLongRowClob values(?,?,?,?,?)");
2054 ps.setString(1,Formatters.padString("blaaa",2000));
2055 ps.setString(2,Formatters.padString("tralaaaa",3000));
2056 ps.setString(3,Formatters.padString("foodar",2000));
2057 ps.setString(4,Formatters.padString("moped",3000));
2058 File file = new File (fileName[1]);
2059 if (file.length() < 10000)
2060 System.out.println("ERROR: wrong file tested");
2061 InputStream fileIn = new FileInputStream (file);
2062 ps.setAsciiStream(5, fileIn, (int)file.length());
2063 ps.executeUpdate();
2064 fileIn.close();
2065 conn.commit();
2066
2067 stmt = conn.createStatement();
2068 rs = stmt.executeQuery("select e from testLongRowClob");
2069 Clob clob = null;
2071 while (rs.next())
2072 clob = rs.getClob(1);
2073 rs.close();
2074
2075 Connection conn2 = ij.startJBMS();
2076 conn2.setAutoCommit(false);
2079 stmt2 = conn2.createStatement();
2081 stmt2.executeUpdate("update testLongRowClob set e = 'smurfball' where a = 'blaaa'");
2082
2083 conn.commit();
2084 conn2.commit();
2085 System.out.println("clobTest92 finished");
2086 }
2087 catch (SQLException e) {
2088 TestUtil.dumpSQLExceptions(e);
2089 }
2090 catch (Throwable e) {
2091 System.out.println("FAIL -- unexpected exception:" + e.toString());
2092 if (debug) e.printStackTrace(System.out);
2093 }
2094 }
2095
2096
2097
2101 private static void clobTest94(Connection conn)
2102 {
2103 ResultSet rs;
2104 Statement stmt;
2105 System.out.println(START + "clobTest94");
2106 try
2107 {
2108 stmt = conn.createStatement();
2109 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
2110 Clob clob = null, shortClob = null;
2112 int clobLength;
2113 int i = 0;
2114 while (rs.next())
2115 {
2116 clobLength = rs.getInt(2);
2118 if (clobLength == 10000)
2119 clob = rs.getClob(1);
2120 if (clobLength == 26)
2121 shortClob = rs.getClob(1);
2122 }
2123 rs.close();
2124 conn.commit();
2125
2126 System.out.println("shortClob length after commit is " + shortClob.length());
2128 try
2130 {
2131 clob.length();
2132 }
2133 catch (SQLException e)
2134 {
2135 TestUtil.dumpSQLExceptions(e);
2136 }
2137 try
2138 {
2139 clob.getSubString(2,3);
2140 }
2141 catch (SQLException e)
2142 {
2143 TestUtil.dumpSQLExceptions(e);
2144 }
2145 try
2146 {
2147 clob.getAsciiStream();
2148 }
2149 catch (SQLException e)
2150 {
2151 TestUtil.dumpSQLExceptions(e);
2152 }
2153 try
2154 {
2155 clob.position("foo",2);
2156 }
2157 catch (SQLException e)
2158 {
2159 TestUtil.dumpSQLExceptions(e);
2160 }
2161 try
2162 {
2163 clob.position(clob,2);
2164 }
2165 catch (SQLException e)
2166 {
2167 TestUtil.dumpSQLExceptions(e);
2168 }
2169
2170 System.out.println("clobTest94 finished");
2171 }
2172 catch (SQLException e) {
2173 TestUtil.dumpSQLExceptions(e);
2174 }
2175 catch (Throwable e) {
2176 System.out.println("FAIL -- unexpected exception:" + e.toString());
2177 if (debug) e.printStackTrace(System.out);
2178 }
2179 }
2180
2181
2182
2186 private static void clobTest95(Connection conn)
2187 {
2188 ResultSet rs;
2189 Statement stmt;
2190 System.out.println(START + "clobTest95");
2191 try
2192 {
2193 stmt = conn.createStatement();
2194 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
2195 Clob clob = null, shortClob = null;
2197 int clobLength;
2198 while (rs.next())
2199 {
2200 clobLength = rs.getInt(2);
2201 if (clobLength == 10000)
2202 clob = rs.getClob(1);
2203 if (clobLength == 26)
2204 shortClob = rs.getClob(1);
2205 }
2206 rs.close();
2207 conn.commit();
2208 conn.close();
2209
2210 try {
2211 System.out.println("shortClob length after closing connection is " + shortClob.length());
2213 }
2214 catch (SQLException e)
2215 {
2216 if (isDerbyNet)
2217 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
2218 else
2219 TestUtil.dumpSQLExceptions(e);
2220
2221 }
2222 try
2224 {
2225 clob.length();
2226 }
2227 catch (SQLException e)
2228 {
2229 if (isDerbyNet)
2230 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
2231 else
2232 TestUtil.dumpSQLExceptions(e);
2233 }
2234 try
2235 {
2236 clob.getSubString(2,3);
2237 }
2238 catch (SQLException e)
2239 {
2240 if (isDerbyNet)
2241 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
2242 else
2243 TestUtil.dumpSQLExceptions(e);
2244 }
2245 try
2246 {
2247 clob.getAsciiStream();
2248 }
2249 catch (SQLException e)
2250 {
2251 if (isDerbyNet)
2252 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
2253 else
2254 TestUtil.dumpSQLExceptions(e);
2255 }
2256 try
2257 {
2258 clob.position("foo",2);
2259 }
2260 catch (SQLException e)
2261 {
2262 if (isDerbyNet)
2263 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
2264 else
2265 TestUtil.dumpSQLExceptions(e);
2266 }
2267 try
2268 {
2269 clob.position(clob,2);
2270 }
2271 catch (SQLException e)
2272 {
2273
2274 if (isDerbyNet)
2275 System.out.println("EXPECTED SQL Exception: " + e.getMessage());
2276 else
2277 TestUtil.dumpSQLExceptions(e);
2278 }
2279
2280 System.out.println("clobTest95 finished");
2281 }
2282 catch (SQLException e) {
2283 TestUtil.dumpSQLExceptions(e);
2284 }
2285 catch (Throwable e) {
2286 System.out.println("FAIL -- unexpected exception:" + e.toString());
2287 if (debug) e.printStackTrace(System.out);
2288 }
2289 }
2290
2291
2292
2300 private static void clobTest96(Connection conn)
2301 {
2302 ResultSet rs;
2303 Statement stmt;
2304 System.out.println(START + "clobTest96");
2305 try {
2306 stmt = conn.createStatement();
2307 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
2308 byte[] buff = new byte[128];
2309 Clob [] clobArray = new Clob [numRows];
2310 int[] clobLengthArray = new int[numRows];
2311 int j = 0;
2312 while (rs.next())
2313 {
2314 clobArray[j] = rs.getClob(1);
2315 clobLengthArray[j++] = rs.getInt(2);
2316 }
2317 rs.close();
2318 stmt.close();
2319
2320 for (int i = 0; i < numRows; i++)
2321 {
2322 clobArray[i] = null;
2323 }
2324
2325 System.gc();
2326 System.gc();
2327
2328
2331 System.out.println("clobTest96 finished");
2332 }
2333 catch (SQLException e) {
2334 TestUtil.dumpSQLExceptions(e);
2335 }
2336 catch (Throwable e) {
2337 System.out.println("FAIL -- unexpected exception:" + e.toString());
2338 if (debug) e.printStackTrace(System.out);
2339 }
2340 }
2341
2342
2350
2356
2357 private static void bug2(Connection conn)
2358 {
2359 ResultSet rs;
2360 Statement stmt;
2361 System.out.println(START + "bug2");
2362 try {
2363 stmt = conn.createStatement();
2364 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
2365 byte[] buff = new byte[128];
2366 Clob [] clobArray = new Clob [numRows*2];
2367 int[] clobLengthArray = new int[numRows*2];
2368 int j = 0;
2369 while (rs.next())
2370 {
2371 clobArray[j] = rs.getClob(1);
2372 clobLengthArray[j++] = rs.getInt(2);
2373 clobArray[j] = rs.getClob(1);
2374 clobLengthArray[j++] = rs.getInt(2);
2375 }
2376 rs.close();
2377 stmt.close();
2378
2379 for (int i = 0; i < numRows*2; i=i+2)
2381 {
2382 clobArray[i] = null;
2383 }
2384
2385 System.gc();
2386 System.gc();
2387
2388 System.out.println("after gc");
2389
2391 for (int i = 1; i < numRows*2+1; i=i+2)
2393 {
2394 if (clobArray[i].length() != clobLengthArray[i])
2395 System.out.println("Error at array position " + i);
2396 }
2397
2398 System.out.println("clobTest97 finished");
2399 }
2400 catch (SQLException e) {
2401 TestUtil.dumpSQLExceptions(e);
2402 }
2403 catch (Throwable e) {
2404 System.out.println("FAIL -- unexpected exception:" + e.toString());
2405 if (debug) e.printStackTrace(System.out);
2406 }
2407 }
2408
2409
2413 private static void clobTestGroupfetch(Connection conn)
2414 {
2415 ResultSet rs;
2416 Statement stmt,stmt2;
2417 System.out.println(START + "clobTestGroupFetch");
2418 try
2419 {
2420 stmt = conn.createStatement();
2421 rs = stmt.executeQuery("select a,b from testCLOB_MAIN");
2422 int clobLength;
2424 while (rs.next())
2425 {
2426 clobLength = rs.getInt(2);
2427 String s = rs.getString(1);
2428 printLockTable(conn);
2429 }
2430 rs.close();
2431
2432 System.out.println("clobTestGroupFetch finished");
2433 }
2434 catch (SQLException e) {
2435 TestUtil.dumpSQLExceptions(e);
2436 }
2437 catch (Throwable e) {
2438 System.out.println("FAIL -- unexpected exception:" + e.toString());
2439 if (debug) e.printStackTrace(System.out);
2440 }
2441 }
2442
2443
2444
2450 private static void bug(Connection conn)
2451 {
2452 ResultSet rs;
2453 Statement stmt, stmt2;
2454 System.out.println(START + "bug" );
2455 try
2456 {
2457 stmt = conn.createStatement();
2458 stmt.execute("create table testLongRowClob (a varchar(2000))");
2460 stmt.execute("alter table testLongRowClob add column b varchar(3000)");
2461 stmt.execute("alter table testLongRowClob add column c varchar(2000)");
2462 stmt.execute("alter table testLongRowClob add column d varchar(3000)");
2463 stmt.execute("alter table testLongRowClob add column e CLOB(400k)");
2464 PreparedStatement ps = conn.prepareStatement(
2465 "insert into testLongRowClob values(?,?,?,?,?)");
2466 ps.setString(1,Formatters.padString("blaaa",2000));
2467 ps.setString(2,Formatters.padString("tralaaaa",3000));
2468 ps.setString(3,Formatters.padString("foodar",2000));
2469 ps.setString(4,Formatters.padString("moped",3000));
2470 File file = new File (fileName[1]);
2471 if (file.length() < 10000)
2472 System.out.println("ERROR: wrong file tested");
2473 InputStream fileIn = new FileInputStream (file);
2474 ps.setAsciiStream(5, fileIn, (int)file.length());
2475 ps.executeUpdate();
2476 fileIn.close();
2477 conn.commit();
2478
2479 Connection conn2 = ij.startJBMS();
2480 conn2.setAutoCommit(false);
2483
2484 stmt = conn.createStatement();
2486 rs = stmt.executeQuery("select e from testLongRowClob");
2487 printLockTable(conn2);
2488
2489 Clob clob = null;
2491 int clobLength, i = 0;
2492 while (rs.next())
2493 {
2494 i++;
2495 clob = rs.getClob(1);
2496 System.out.println("got it");
2497 System.out.println("clob length is " + clob.length());
2499 System.out.println("the thing as a string is : \n" + rs.getString(1));
2500 printLockTable(conn2);
2501 }
2502 rs.close();
2503 System.out.println("After closing result set");
2504 printLockTable(conn2);
2505
2506 stmt2 = conn2.createStatement();
2507 stmt2.executeUpdate("update testLongRowClob set e = 'smurfball' where a = 'blaaa'");
2508 printLockTable(conn2);
2509
2510 System.out.println("clob length is " + clob.length());
2511
2512 conn.commit();
2513 conn2.commit();
2514 System.out.println("clobTest92 finished");
2515 }
2516 catch (SQLException e) {
2517 TestUtil.dumpSQLExceptions(e);
2518 }
2519 catch (Throwable e) {
2520 System.out.println("FAIL -- unexpected exception:" + e.toString());
2521 if (debug) e.printStackTrace(System.out);
2522 }
2523 }
2524
2525 private static void clobTest9999(Connection conn) {
2528
2529 ResultSetMetaData met;
2530 ResultSet rs;
2531 Statement stmt;
2532 System.out.println(START + "clobTest9999");
2533 try {
2534 stmt = conn.createStatement();
2535 stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096')");
2536 stmt.execute("create table clobTest9999 (a CLOB(300k))");
2537 stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','0')");
2538
2539 int i = 4;
2540 File file = new File (fileName[i]);
2542 fileLength[i] = file.length();
2543 InputStream fileIn = new FileInputStream (file);
2544
2545 System.out.println("===> testing " + fileName[i] + " length = "
2546 + fileLength[i]);
2547
2548 PreparedStatement ps = conn.prepareStatement("insert into clobTest9999 values(?)");
2550 ps.setAsciiStream(1, fileIn, (int)fileLength[i]);
2551 ps.executeUpdate();
2552 fileIn.close();
2553
2554 rs = stmt.executeQuery("select a from clobTest9999");
2555 met = rs.getMetaData();
2556 while (rs.next()) {
2558 Clob clob = rs.getClob(1);
2560 InputStream fin = clob.getAsciiStream();
2561 int columnSize = 0;
2562 for (;;) {
2563 int j = fin.read();
2564 if (j == -1)
2565 break;
2566 System.out.print((char) j);
2567 }
2568 }
2569 System.out.println("Finished clobTest9999");
2570
2571 }
2572 catch (SQLException e) {
2573 TestUtil.dumpSQLExceptions(e);
2574 }
2575 catch (Throwable e) {
2576 System.out.println("FAIL -- unexpected exception:" + e.toString());
2577 if (debug) e.printStackTrace(System.out);
2578 }
2579 }
2580
2581
2582
2596 private static void clobTest10(Connection conn) throws SQLException {
2597 Statement s = conn.createStatement();
2598 s.execute("CREATE TABLE derby1382 (c1 int, c2 clob)");
2599
2600 String clobData = "initial clob ";
2601 PreparedStatement ps =
2602 conn.prepareStatement("insert into derby1382 values (?, ?)");
2603 for (int i=0; i<10; i++) {
2604 ps.setInt(1, i);
2605 ps.setString(2, clobData + i);
2606 ps.execute();
2607 }
2608 ps.close();
2609
2610 Statement scrollStmt = conn.createStatement(
2611 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
2612 ResultSet rs = scrollStmt.executeQuery("SELECT * FROM derby1382");
2613
2614 String value;
2615 Clob c;
2616
2617 rs.first();
2618 checkContentsBeforeAndAfterUpdatingClob(rs);
2619 rs.next();
2620 checkContentsBeforeAndAfterUpdatingClob(rs);
2621 rs.relative(3);
2622 checkContentsBeforeAndAfterUpdatingClob(rs);
2623 rs.absolute(7);
2624 checkContentsBeforeAndAfterUpdatingClob(rs);
2625 rs.previous();
2626 checkContentsBeforeAndAfterUpdatingClob(rs);
2627 rs.last();
2628 checkContentsBeforeAndAfterUpdatingClob(rs);
2629 rs.previous();
2630 checkContentsBeforeAndAfterUpdatingClob(rs);
2631
2632 rs.close();
2633 scrollStmt.close();
2634
2635 s.execute("DROP TABLE derby1382");
2636 s.close();
2637 }
2638
2639 private static void checkContentsBeforeAndAfterUpdatingClob(ResultSet rs)
2640 throws SQLException
2641 {
2642 Clob c;
2643 String value, expectedValue;
2644 String clobData = "initial clob ";
2645 String updatedClobData = "updated clob ";
2646
2647 c = rs.getClob(2);
2648 value = c.getSubString(1, (int)c.length());
2650 expectedValue = clobData + rs.getInt(1);
2651 if (value.compareToIgnoreCase(expectedValue) != 0) {
2652 System.out.println("clobTest10 - Error: wrong clob value");
2653 }
2654 value = updatedClobData + rs.getInt(1);
2656 rs.updateString(2, value);
2657 rs.updateRow();
2658 rs.next(); rs.previous(); c = rs.getClob(2);
2662 value = c.getSubString(1, (int)c.length());
2664 expectedValue = updatedClobData + rs.getInt(1);
2665 if (value.compareToIgnoreCase(expectedValue) != 0) {
2666 System.out.println("clobTest10 - Error: wrong clob value");
2667 }
2668 }
2669
2670
2679 private static void clobTest14(Connection conn) throws SQLException {
2680 Statement s = conn.createStatement();
2681 s.execute("CREATE TABLE derby1421 (c1 int, c2 clob)");
2682
2683 String clobData = "initial clob ";
2684 PreparedStatement ps =
2685 conn.prepareStatement("insert into derby1421 values (?, ?)");
2686 for (int i=0; i<10; i++) {
2687 ps.setInt(1, i);
2688 ps.setString(2, clobData + i);
2689 ps.execute();
2690 }
2691 ps.close();
2692
2693 Statement scrollStmt = conn.createStatement(
2694 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
2695 ResultSet rs = scrollStmt.executeQuery("SELECT * FROM derby1421");
2696
2697 rs.first();
2698 updateClobWithUpdateCharacterStream(rs);
2699 rs.next();
2700 updateClobWithUpdateCharacterStream(rs);
2701 rs.relative(3);
2702 updateClobWithUpdateCharacterStream(rs);
2703 rs.absolute(7);
2704 updateClobWithUpdateCharacterStream(rs);
2705 rs.previous();
2706 updateClobWithUpdateCharacterStream(rs);
2707 rs.last();
2708 updateClobWithUpdateCharacterStream(rs);
2709 rs.previous();
2710 updateClobWithUpdateCharacterStream(rs);
2711
2712 rs.close();
2713 scrollStmt.close();
2714
2715 s.execute("DROP TABLE derby1421");
2716 s.close();
2717 }
2718
2719 private static void updateClobWithUpdateCharacterStream(ResultSet rs)
2720 throws SQLException
2721 {
2722 Clob c;
2723 String value, expectedValue;
2724 String clobData = "initial clob ";
2725 String updatedClobData = "updated clob ";
2726
2727 c = rs.getClob(2);
2728 value = c.getSubString(1, (int)c.length());
2730 expectedValue = clobData + rs.getInt(1);
2731 if (value.compareToIgnoreCase(expectedValue) != 0) {
2732 System.out.println("clobTest14 - Error: wrong clob value");
2733 }
2734
2735 value = (updatedClobData + rs.getInt(1));
2737 Reader updateValue = new StringReader (value);
2738 rs.updateCharacterStream(2, updateValue, value.length());
2739 rs.updateRow();
2740 rs.next(); rs.previous(); c = rs.getClob(2);
2744 value = c.getSubString(1, (int)c.length());
2746 expectedValue = updatedClobData + rs.getInt(1);
2747 if (value.compareToIgnoreCase(expectedValue) != 0) {
2748 System.out.println("clobTest14 - Error: wrong clob value");
2749 }
2750 }
2751
2752 private static void clobTestSelfDestructive(Connection conn)
2755 {
2756 ResultSet rs;
2757 Statement stmt;
2758 System.out.println(START + "clobTestSelfDestructive");
2759 try
2760 {
2761 stmt = conn.createStatement();
2762 rs = stmt.executeQuery("select a,b from testCLOB_MAIN where b = 10000");
2763 byte[] buff = new byte[128];
2764 Clob clob = null;
2766 InputStream fin;
2767 int clobLength = 0, i = 0;
2768 if (rs.next())
2769 {
2770 i++;
2771 clobLength = rs.getInt(2);
2772 clob = rs.getClob(1);
2774 }
2775 System.out.println("length of clob chosen is " + clobLength);
2776 fin = clob.getAsciiStream();
2777 int columnSize = 0;
2778
2779 PreparedStatement ps = conn.prepareStatement(
2780 "update testCLOB_MAIN set a = ? where b = 10000");
2781 StringBuffer foo = new StringBuffer ();
2782 for (int k = 0; k < 1000; k++)
2783 foo.append('j');
2784 ps.setString(1,foo.toString());
2785 ps.executeUpdate();
2786
2787 System.out.println("After update");
2788
2789 rs = stmt.executeQuery("select a from testCLOB_MAIN where b = 10000");
2790 while (rs.next())
2791 {
2792 int j = 1;
2793 String val = rs.getString(1);
2794 System.out.println("Row " + j + " value.substring(0,50) is " + val.substring(0,50));
2795 j++;
2796 }
2797
2798 while (columnSize < 11000)
2799 {
2800 int size = fin.read(buff);
2801 if (size == -1)
2802 break;
2803 columnSize += size;
2804 }
2806 System.out.println(columnSize + " total bytes read");
2807
2808 if (columnSize != clobLength)
2809 System.out.println("test failed, columnSize should be " + clobLength
2810 + ", but it is " + columnSize + ", i = " + i);
2811 if (columnSize != clob.length())
2812 System.out.println("test failed, clob.length() should be " + columnSize
2813 + ", but it is " + clob.length() + ", i = " + i);
2814 conn.rollback();
2815 System.out.println("clobTestSelfDestructive finished");
2816 }
2817 catch (SQLException e)
2818 {
2819 TestUtil.dumpSQLExceptions(e);
2820 }
2821 catch (Throwable e)
2822 {
2823 System.out.println("FAIL -- unexpected exception:" + e.toString());
2824 if (debug) e.printStackTrace(System.out);
2825 }
2826 }
2827
2828
2829 private static void clobTestSelfDestructive2(Connection conn)
2833 {
2834 ResultSet rs;
2835 Statement stmt;
2836 System.out.println(START + "clobTestSelfDestructive2");
2837 try
2838 {
2839 stmt = conn.createStatement();
2840 rs = stmt.executeQuery("select a,b from testCLOB_MAIN where b = 10000");
2841 byte[] buff = new byte[128];
2842 Clob clob = null;
2844 InputStream fin;
2845 int clobLength = 0, i = 0;
2846 if (rs.next())
2847 {
2848 i++;
2849 clobLength = rs.getInt(2);
2850 clob = rs.getClob(1);
2852 }
2853 System.out.println("length of clob chosen is " + clobLength);
2854 fin = clob.getAsciiStream();
2855 int columnSize = 0;
2856
2857 stmt.executeUpdate("drop table testCLOB_MAIN");
2858 System.out.println("After drop");
2859
2860 System.out.println("Expect to get an IOException, container has been closed");
2861 while (columnSize < 11000)
2862 {
2863 int size = fin.read(buff);
2864 if (size == -1)
2865 break;
2866 columnSize += size;
2867 }
2869 System.out.println(columnSize + " total bytes read");
2870
2871 conn.rollback();
2872 System.out.println("clobTestSelfDestructive2 finished");
2873 }
2874 catch (SQLException e)
2875 {
2876 TestUtil.dumpSQLExceptions(e);
2877 }
2878 catch (java.io.IOException ioe)
2879 {
2880 System.out.println("EXPECTED IO Exception:" + ioe.getMessage());
2881 }
2882 catch (Throwable e)
2883 {
2884 System.out.println("FAIL -- unexpected exception:" + e.toString());
2885 if (debug) e.printStackTrace(System.out);
2886 }
2887 }
2888
2889
2890
2891 private static void printLockTable(Connection conn)
2892 {
2893 ResultSet rs;
2894 Statement stmt,stmt2;
2895 try
2896 {
2897 System.out.println("\nLock table\n----------------------------------------");
2898 stmt = conn.createStatement();
2899 rs = stmt.executeQuery(
2900 "select xid,type,lockcount,mode,tablename,lockname,state from SYSCS_DIAG.LOCK_TABLE t where t.tableType <> 'S'");
2901 while (rs.next())
2902 {
2903 String xid = rs.getString("xid");
2904 String type = rs.getString("type");
2905 String lockcount = rs.getString("lockcount");
2906 String mode = rs.getString("mode");
2907 String tablename = rs.getString("tablename");
2908 String lockname = rs.getString("lockname");
2909 String state = rs.getString("state");
2910 System.out.println("Lock{xid = " + xid + ", type = " + type +
2911 ", lockcount = " + lockcount + ", mode = " + mode +
2912 ", tablename = " + tablename + ", lockname = " + lockname +
2913 ", state = " + state + " } ");
2914 }
2915 System.out.println("----------------------------------------\n");
2916 }
2917 catch (SQLException e) {
2918 TestUtil.dumpSQLExceptions(e);
2919 }
2920 catch (Throwable e) {
2921 System.out.println("FAIL -- unexpected exception:" + e.toString());
2922 if (debug) e.printStackTrace(System.out);
2923 }
2924 }
2925
2926
2927
2928
2929
2930
2931 private static void unicodeTest()
2932 {
2933 System.out.println(START + "unicodeTest");
2934 try
2935 {
2936 byte[] unicodeArray = {0xfffffffe, 0xffffffff, 0x01, 0x68, 0x00, 0x65,
2938 0x00, 0x6c, 0x00, 0x6c, 0x00, 0x6f};
2939 String str = new String (unicodeArray, "Unicode");
2940 System.out.println("First string is : " + str);
2941
2947 byte[] uni2 = {0x68, 0x65, 0x6c, 0x6c, 0x6f, 0x20, 0x74, 0x68, 0x65, 0x72, 0x65};
2948 String suni2 = new String (uni2,"Unicode");
2949 System.out.println("uni2 is :" + uni2);
2950
2951 String uni3 = "\u0020\u0021\u0023";
2952 System.out.println("uni3 is :" + uni3);
2953 String uni4 = "\u0061\u0062\u0063";
2954 System.out.println("uni4 is :" + uni4);
2955 System.out.println("uni4 equals abc ? " + uni4.equals("abc"));
2956 String uni5 = "\u0370\u0371\u0372";
2957 System.out.println("uni5 is :" + uni5);
2958 System.out.println("uni5 equals ??? ? " + uni5.equals("???"));
2959 System.out.println("uni5 equals uni5 ? " + uni5.equals(uni5));
2960 String uni6 = "\u05d0\u05d1\u05d2";
2961 System.out.println("uni6 is :" + uni6);
2962 System.out.println("uni5 equals uni6 ? " + uni5.equals(uni6));
2963 System.out.println("uni6 equals uni6 ? " + uni6.equals(uni6));
2964
2965 FileWriter fw;
2966
2967 }
2968 catch (Throwable e)
2969 {
2970 System.out.println("FAIL -- unexpected exception:" + e.toString());
2971 if (debug) e.printStackTrace(System.out);
2972 }
2973 }
2974
2975
2976
2984 private static void prepareBlobTable(Connection conn)
2985 {
2986 ResultSet rs;
2987 Statement stmt;
2988 System.out.println(START + "prepareBlobTable");
2989 try {
2990 stmt = conn.createStatement();
2991 stmt.execute("create table testBlob (b integer)");
2993 stmt.execute("alter table testBlob add column a blob(300k)");
2994 stmt.execute("alter table testBlob add column crc32 BIGINT");
2995
2996 PreparedStatement ps = conn.prepareStatement(
2997 "insert into testBlob (a, b, crc32) values(?,?,?)");
2998
2999 insertRow(ps,"".getBytes("US-ASCII"));
3001 insertRow(ps,"you can lead a horse to water but you can't form it into beverage".getBytes("US-ASCII"));
3002 insertRow(ps,"a stitch in time says ouch".getBytes("US-ASCII"));
3003 insertRow(ps,"here is a string with a return \n character".getBytes("US-ASCII"));
3004
3005 for (int i = 0; i < numFiles; i++)
3007 {
3008 File file = new File (fileName[i]);
3010 fileLength[i] = file.length();
3011 InputStream fileIn = new FileInputStream (file);
3012
3013 System.out.println("===> inserting " + basefileName[i] + " length = "
3014 + fileLength[i]);
3015
3016 ps.setBinaryStream(1, fileIn, (int)fileLength[i]);
3018 ps.setInt(2, (int)fileLength[i]);
3019 ps.setLong(3, fileCRC32[i]);
3020 ps.executeUpdate();
3021 fileIn.close();
3022 }
3023
3024 ps.setNull(1, Types.BLOB);
3026 ps.setInt(2, 0);
3027 ps.setNull(3, Types.BIGINT);
3028 ps.executeUpdate();
3029
3030 ps.close();
3031 conn.commit();
3032
3033 rs = stmt.executeQuery("select count(*) from testBlob");
3035 int realNumRows = -1;
3036 if (rs.next())
3037 realNumRows = rs.getInt(1);
3038 if (realNumRows <= 0)
3039 System.out.println("FAIL. No rows in table testCLOB_MAIN");
3040 if (realNumRows != numRows)
3041 System.out.println("FAIL. numRows is incorrect");
3042
3043 stmt.close();
3044 conn.commit();
3045
3046 }
3047 catch (SQLException e) {
3048 TestUtil.dumpSQLExceptions(e);
3049 }
3050 catch (Throwable e) {
3051 System.out.println("FAIL -- unexpected exception:" + e.toString());
3052 if (debug) e.printStackTrace(System.out);
3053 }
3054 }
3055
3056
3057
3062 private static void prepareBinaryTable(Connection conn)
3063 {
3064 ResultSet rs;
3065 Statement stmt;
3066 System.out.println(START + "prepareBinaryTable");
3067 try
3068 {
3069 stmt = conn.createStatement();
3070 stmt.execute("create table testBinary (a blob(80), b integer, crc32 bigint)");
3071 PreparedStatement ps = conn.prepareStatement(
3072 "insert into testBinary values(?,?,?)");
3073
3074 insertRow(ps,"".getBytes("US-ASCII"));
3076 insertRow(ps,"you can lead a horse to water but you can't form it into beverage".getBytes("US-ASCII"));
3077 insertRow(ps,"a stitch in time says ouch".getBytes("US-ASCII"));
3078 insertRow(ps,"here is a string with a return \n character".getBytes("US-ASCII"));
3079
3080
3085 conn.commit();
3086 }
3087 catch (SQLException e)
3088 {
3089 TestUtil.dumpSQLExceptions(e);
3090 }
3091 catch (Throwable e)
3092 {
3093 System.out.println("FAIL -- unexpected exception:" + e.toString());
3094 if (debug) e.printStackTrace(System.out);
3095 }
3096 }
3097
3098
3099
3100
3105 private static void prepareSearchBlobTable(Connection conn)
3106 {
3107 ResultSet rs;
3108 Statement stmt;
3109 System.out.println(START + "prepareSearchBlobTable");
3110 try {
3111 stmt = conn.createStatement();
3112 stmt.execute("create table searchBlob (b integer)");
3114 stmt.execute("alter table searchBlob add column a blob(300k)");
3115 stmt.execute("alter table searchBlob add column crc32 BIGINT");
3116 PreparedStatement ps = conn.prepareStatement(
3117 "insert into searchBlob (a, b, crc32) values(?,?,?)");
3118 insertRow(ps,"horse".getBytes("US-ASCII"));
3119 insertRow(ps,"ouch".getBytes("US-ASCII"));
3120 insertRow(ps,"\n".getBytes("US-ASCII"));
3121 insertRow(ps,"".getBytes("US-ASCII"));
3122 insertRow(ps,"Beginning".getBytes("US-ASCII"));
3123 insertRow(ps,"position-69".getBytes("US-ASCII"));
3124 insertRow(ps,"I-am-hiding-here-at-position-5910".getBytes("US-ASCII"));
3125 insertRow(ps,"Position-9907".getBytes("US-ASCII"));
3126
3127 for (int i = 0; i < numFiles; i++)
3129 {
3130 File file = new File (fileName[i]);
3132 fileLength[i] = file.length();
3133 InputStream fileIn = new FileInputStream (file);
3134
3135 System.out.println("===> inserting " + basefileName[i] + " length = "
3136 + fileLength[i]);
3137
3138 ps.setBinaryStream(1, fileIn, (int)fileLength[i]);
3140 ps.setInt(2, (int)fileLength[i]);
3141 ps.setLong(3, fileCRC32[i]);
3142 ps.executeUpdate();
3143 fileIn.close();
3144 }
3145
3146 ps.setNull(1, Types.BLOB);
3148 ps.setInt(2, 0);
3149 ps.setNull(3, Types.BIGINT);
3150 ps.executeUpdate();
3151
3152 conn.commit();
3153 }
3154 catch (SQLException e) {
3155 TestUtil.dumpSQLExceptions(e);
3156 }
3157 catch (Throwable e) {
3158 System.out.println("FAIL -- unexpected exception:" + e.toString());
3159 if (debug) e.printStackTrace(System.out);
3160 }
3161 }
3162
3163
3164
3169 private static void blobTest0(Connection conn)
3170 {
3171 ResultSet rs;
3172 Statement stmt;
3173 System.out.println(START + "blobTest0");
3174 try
3175 {
3176 stmt = conn.createStatement();
3177 rs = stmt.executeQuery("select a,b,crc32 from testBlob");
3178 testBlobContents(rs);
3179 stmt.close();
3180 conn.commit();
3181 System.out.println("blobTest0 finished");
3182 }
3183 catch (SQLException e) {
3184 TestUtil.dumpSQLExceptions(e);
3185 }
3186 catch (Throwable e) {
3187 System.out.println("FAIL -- unexpected exception:" + e.toString());
3188 if (debug) e.printStackTrace(System.out);
3189 }
3190 }
3191
3192
3199 private static void testBlobContents(ResultSet rs)
3200 throws SQLException , IOException
3201 {
3202 int nullCount = 0;
3203 int rowCount = 0;
3204 byte[] buff = new byte[128];
3205 Blob blob;
3207 int blobLength = 0, i = 0;
3208 while (rs.next()) {
3209 i++;
3210 blob = rs.getBlob(1);
3212 long crc32 = rs.getLong(3);
3213 boolean crc2Null = rs.wasNull();
3214 if (blob == null) {
3215 if (!crc2Null)
3216 System.out.println("FAIL: NULL BLOB but non-NULL checksum");
3217 nullCount++;
3218 continue;
3219 }
3220
3221 rowCount++;
3222
3223 long blobcrc32 = getStreamCheckSum(blob.getBinaryStream());
3224
3225 if (blobcrc32 != crc32) {
3226 System.out.println("FAIL: mismatched checksums for blob with length " +
3227 blob.length());
3228 }
3229
3230
3231 InputStream fin = blob.getBinaryStream();
3232 int columnSize = 0;
3233 for (;;) {
3234 int size = fin.read(buff);
3235 if (size == -1)
3236 break;
3237 columnSize += size;
3238 }
3239 blobLength = rs.getInt(2);
3240 if (columnSize != blobLength)
3241 System.out.println("test failed, columnSize should be " + blobLength
3242 + ", but it is " + columnSize + ", i = " + i);
3243 if (columnSize != blob.length())
3244 System.out.println("test failed, blob.length() should be " + columnSize
3245 + ", but it is " + blob.length() + ", i = " + i);
3246 }
3247 System.out.println("Row Count " + rowCount + " Null Row " + nullCount);
3248 }
3249
3250
3254 private static void blobTest2(Connection conn)
3255 {
3256 ResultSet rs;
3257 Statement stmt;
3258 System.out.println(START + "blobTest2");
3259 try
3260 {
3261 stmt = conn.createStatement();
3262 rs = stmt.executeQuery("select a,b from testBlob");
3263 int i = 0, blobLength = 0;
3264 Blob blob;
3265 while (rs.next())
3266 {
3267 i++;
3268 blob = rs.getBlob(1);
3269 if (blob == null)
3270 continue;
3271 blobLength = rs.getInt(2);
3272 blobclob4BLOB.printInterval(blob, 9905, 50, 0, i, blobLength);
3273 blobclob4BLOB.printInterval(blob, 5910, 150, 1, i, blobLength);
3274 blobclob4BLOB.printInterval(blob, 5910, 50, 2, i, blobLength);
3275 blobclob4BLOB.printInterval(blob, 204, 50, 3, i, blobLength);
3276 blobclob4BLOB.printInterval(blob, 68, 50, 4, i, blobLength);
3277 blobclob4BLOB.printInterval(blob, 1, 50, 5, i, blobLength);
3278 blobclob4BLOB.printInterval(blob, 1, 1, 6, i, blobLength);
3279 blobclob4BLOB.printInterval(
3280 blob, 1, 0, 7, i, blobLength); blobclob4BLOB.printInterval(
3282 blob, blobLength + 1, 0, 8, i, blobLength);
3292 if (blobLength > 100)
3293 {
3294 byte[] res = blob.getBytes(blobLength-99,200);
3295 System.out.println(i + "(9) ");
3296 if (res.length != 100)
3297 System.out.println("FAIL : length of bytes is " +
3298 res.length + " should be 100");
3299 else
3300 System.out.println(new String (res, "US-ASCII")); }
3302 }
3303 stmt.close();
3304 conn.commit();
3305 System.out.println("blobTest2 finished");
3306 }
3307 catch (SQLException e) {
3308 TestUtil.dumpSQLExceptions(e);
3309 }
3310 catch (Throwable e) {
3311 System.out.println("FAIL -- unexpected exception:" + e.toString());
3312 if (debug) e.printStackTrace(System.out);
3313 }
3314 }
3315
3316
3317
3321 private static void blobTest3(Connection conn)
3322 {
3323 ResultSet rs;
3324 Statement stmt;
3325 System.out.println(START + "blobTest3");
3326 try
3327 {
3328 stmt = conn.createStatement();
3329 rs = stmt.executeQuery("select a,b from testBlob");
3330 int i = 0, blobLength = 0;
3331 Blob blob;
3332 while (rs.next())
3333 {
3334 i++;
3335 blob = rs.getBlob(1);
3336 if (blob == null)
3337 continue;
3338 blobLength = rs.getInt(2);
3339 if (blobLength > 20000)
3340 continue;
3341 blobLength = rs.getInt(2);
3342 blobclob4BLOB.printPosition(i,"horse",1,blob, blobLength);
3343 blobclob4BLOB.printPosition(i,"ouch",1,blob, blobLength);
3344 blobclob4BLOB.printPosition(i,"\n",1,blob, blobLength);
3345 blobclob4BLOB.printPosition(i,"",1,blob, blobLength);
3346 blobclob4BLOB.printPosition(i,"Beginning",1,blob, blobLength);
3347 blobclob4BLOB.printPosition(i,"Beginning",2,blob, blobLength);
3348 blobclob4BLOB.printPosition(i,"position-69",1,blob, blobLength);
3349 blobclob4BLOB.printPosition(i,"This-is-position-204",1,blob, blobLength);
3350 blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",1,blob, blobLength);
3351 blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",5910,blob, blobLength);
3352 blobclob4BLOB.printPosition(i,"I-am-hiding-here-at-position-5910",5911,blob, blobLength);
3353 blobclob4BLOB.printPosition(i,"Position-9907",1,blob, blobLength);
3354 }
3355 stmt.close();
3356 conn.commit();
3357 System.out.println("blobTest3 finished");
3358 }
3359 catch (SQLException e) {
3360 TestUtil.dumpSQLExceptions(e);
3361 }
3362 catch (Throwable e) {
3363 System.out.println("FAIL -- unexpected exception:" + e.toString());
3364 if (debug) e.printStackTrace(System.out);
3365 }
3366 }
3367
3368
3372 private static void blobTest4(Connection conn)
3373 {
3374 ResultSet rs, rs2;
3375 Statement stmt, stmt2;
3376 System.out.println(START + "blobTest4");
3377 try
3378 {
3379 stmt = conn.createStatement();
3380 rs = stmt.executeQuery("select a,b from testBlob");
3381 int i = 0, blobLength = 0;
3382 Blob blob;
3383 while (rs.next())
3384 {
3385 i++;
3386 blob = rs.getBlob(1);
3387 if (blob == null)
3388 continue;
3389 blobLength = rs.getInt(2);
3390 if (blobLength > 20000)
3391 {
3392 System.out.println("testBlob row " + i + " skipped (too large)");
3393 continue;
3394 }
3395 stmt2 = conn.createStatement();
3398 rs2 = stmt2.executeQuery("select a,b from searchBlob");
3399 int j = 0, blobLength2 = 0;
3400 Blob searchBlob;
3401 String searchStr;
3402 while (rs2.next())
3403 {
3404 j++;
3405 searchBlob = rs2.getBlob(1);
3406 if (searchBlob == null)
3407 continue;
3408 blobLength2 = rs2.getInt(2);
3409 if (blobLength2 > 20000)
3410 {
3411 System.out.println("searchBlob row " + j + " skipped (too large)");
3412 continue;
3413 }
3414 if (blobLength2 < 150)
3415 searchStr = new String (rs2.getBytes(1),"US-ASCII");
3417 else
3418 searchStr = null;
3419
3420 printPositionBlob(i,searchStr,1,blob,j,searchBlob);
3421 }
3422 stmt2.close();
3423 }
3424 stmt.close();
3425 conn.commit();
3426 System.out.println("blobTest4 finished");
3427 }
3428 catch (SQLException e) {
3429 TestUtil.dumpSQLExceptions(e);
3430 }
3431 catch (Throwable e) {
3432 System.out.println("FAIL -- unexpected exception:" + e.toString());
3433 if (debug) e.printStackTrace(System.out);
3434 }
3435 }
3436
3437
3440 private static void blobTest8Trigger(Connection conn)
3441 {
3442 System.out.println(START + "blobTest8Trigger");
3443 try {
3444 Statement stmt = conn.createStatement();
3445 stmt.executeUpdate("CREATE TABLE blobTest8TriggerA (a BLOB(400k), b int, crc32 BIGINT)");
3446 stmt.executeUpdate("CREATE TABLE blobTest8TriggerB (a BLOB(400k), b int, crc32 BIGINT)");
3447 stmt.executeUpdate(
3448 "create trigger T8A after update on testBlob " +
3449 "referencing new as n old as o " +
3450 "for each row mode db2sql "+
3451 "insert into blobTest8TriggerA(a, b, crc32) values (n.a, n.b, n.crc32)");
3452 stmt.executeUpdate(
3453 "create trigger T8B after INSERT on blobTest8TriggerA " +
3454 "referencing new_table as n " +
3455 "for each statement mode db2sql "+
3456 "insert into blobTest8TriggerB(a, b, crc32) select n.a, n.b, n.crc32 from n");
3457
3458 conn.commit();
3459 ResultSet rs = stmt.executeQuery(
3460 "select a,b,crc32 from blobTest8TriggerA");
3461 testBlobContents(rs);
3462 rs.close();
3463 conn.commit();
3464 stmt.executeUpdate("UPDATE testBlob set b = b + 0");
3465 conn.commit();
3466 rs = stmt.executeQuery(
3467 "select a,b,crc32 from blobTest8TriggerA");
3468 testBlobContents(rs);
3469 rs.close();
3470 conn.commit();
3471
3472 rs = stmt.executeQuery(
3473 "select a,b,crc32 from blobTest8TriggerB");
3474 testBlobContents(rs);
3475 rs.close();
3476 conn.commit();
3477 stmt.executeUpdate("DROP TRIGGER T8A");
3478 stmt.executeUpdate("DROP TABLE blobTest8TriggerB");
3479 stmt.executeUpdate("DROP TABLE blobTest8TriggerA");
3480
3481 stmt.close();
3482 conn.commit();
3483 System.out.println("blobTest8Trigger finished");
3484 }
3485 catch (SQLException e) {
3486 TestUtil.dumpSQLExceptions(e);
3487 do {
3488 e.printStackTrace(System.out);
3489 e = e.getNextException();
3490 } while (e != null);
3491
3492 }
3493 catch (Throwable e) {
3494 System.out.println("FAIL -- unexpected exception:" + e.toString());
3495 if (debug) e.printStackTrace(System.out);
3496 }
3497
3498 }
3499
3500
3501 private static void printPositionBlob(
3502 int rowNum,
3503 String searchStr,
3504 long position,
3505 Blob blob,
3506 int searchRowNum,
3507 Blob searchBlob)
3508 {
3509
3510 try
3511 {
3512 long result = blob.position(searchBlob,position);
3513 if ((searchStr != null) && searchStr.equals("") && (result == 1))
3514 return;
3515 if (result != -1)
3516 {
3517 System.out.print("Found ");
3518 if (searchStr != null)
3519 System.out.print(searchStr);
3520 else
3521 System.out.print("blob (row " + searchRowNum + ") ");
3522 System.out.println(" in row " + rowNum + " at position " + result);
3523 }
3524 }
3525 catch (SQLException e)
3526 {
3527 TestUtil.dumpSQLExceptions(e);
3528 }
3529 }
3530
3531
3532
3533 private static void blobTest51(Connection conn) {
3536
3537 ResultSetMetaData met;
3538 ResultSet rs;
3539 Statement stmt;
3540 System.out.println(START + "blobTest51");
3541 try {
3542 stmt = conn.createStatement();
3543 stmt.execute("create table testVarbinary (a blob(13))");
3544
3545 PreparedStatement ps = conn.prepareStatement(
3546 "insert into testVarbinary values(?)");
3547 String val = "";
3548
3549 for (int i = 0; i < 10; i++)
3550 {
3551 ps.setBytes(1, val.getBytes("US-ASCII"));
3553 ps.executeUpdate();
3554 val = val.trim() + "x";
3555 }
3556
3557 rs = stmt.executeQuery("select a from testVarbinary");
3558 met = rs.getMetaData();
3559 byte[] buff = new byte[128];
3560 int j = 0;
3561 while (rs.next())
3563 {
3564 Blob blob = rs.getBlob(1);
3566 if (blob == null)
3567 continue;
3568 InputStream fin = blob.getBinaryStream();
3569 int columnSize = 0;
3570 for (;;)
3571 {
3572 int size = fin.read(buff);
3573 if (size == -1)
3574 break;
3575 columnSize += size;
3576 }
3577 if (columnSize != j)
3578 System.out.println("FAIL - Expected blob size : " + j +
3579 " Got blob size : " + columnSize);
3580 if (blob.length() != j)
3581 System.out.println("FAIL - Expected blob length : " + j +
3582 " Got blob length : " + blob.length());
3583 j++;
3584 }
3585 ps.close();
3586 stmt.close();
3587 conn.commit();
3588 System.out.println("blobTest51 finished");
3589 }
3590 catch (SQLException e) {
3591 TestUtil.dumpSQLExceptions(e);
3592 }
3593 catch (Throwable e) {
3594 System.out.println("FAIL -- unexpected exception:" + e.toString());
3595 if (debug) e.printStackTrace(System.out);
3596 }
3597 }
3598
3599
3600 private static void blobTest52(Connection conn) {
3602
3603 Statement stmt = null;
3604 System.out.println(START + "blobTest52");
3605 try {
3606 stmt = conn.createStatement();
3607 stmt.execute("create table testInteger2 (a integer)");
3608
3609 PreparedStatement ps = conn.prepareStatement("insert into testInteger2 values(158)");
3610 ps.executeUpdate();
3611 ps.close();
3612
3613 ResultSet rs = stmt.executeQuery("select a from testInteger2");
3614 while (rs.next()) {
3615 try {
3617 Blob blob = rs.getBlob(1);
3618 System.out.println("FAIL fetched java.sql.Blob from INT column");
3619 } catch (SQLException e) {
3620 TestUtil.dumpSQLExceptions(e,
3621 "22005".equals(e.getSQLState()) ||
3622 "XCL12".equals(e.getSQLState()));
3623 break;
3624 }
3625 }
3626 stmt.close();
3627 conn.commit();
3628
3629 }
3630 catch (SQLException e) {
3631 TestUtil.dumpSQLExceptions(e);
3632 }
3633 catch (Throwable e) {
3634 System.out.println("FAIL -- unexpected exception:" + e.toString());
3635 if (debug) e.printStackTrace(System.out);
3636 }
3637 System.out.println("blobTest52 finished");
3638 }
3639
3640
3641 private static void blobTest53(Connection conn) {
3644
3645 ResultSetMetaData met;
3646 ResultSet rs;
3647 Statement stmt;
3648 System.out.println(START + "blobTest53");
3649 try {
3650 stmt = conn.createStatement();
3651 stmt.execute("create table testBlobColumn (a blob(1K))");
3652 stmt.close();
3653 System.out.println("blobTest53 finished");
3654 }
3655 catch (SQLException e) {
3656 TestUtil.dumpSQLExceptions(e);
3657 }
3658 catch (Throwable e) {
3659 System.out.println("FAIL -- unexpected exception:" + e.toString());
3660 if (debug) e.printStackTrace(System.out);
3661 }
3662 }
3663
3664
3668 private static void blobTest54(Connection conn)
3669 {
3670 ResultSet rs;
3671 Statement stmt;
3672 System.out.println(START + "blobTest54");
3673 try
3674 {
3675 stmt = conn.createStatement();
3676 stmt.execute("create table testBlob2 (a integer, b integer)");
3677 PreparedStatement ps = conn.prepareStatement(
3678 "insert into testBlob2 values(?,?)");
3679 rs = stmt.executeQuery("select a,b from testBlob");
3680 Blob blob;
3681 int blobLength;
3682 while (rs.next())
3683 {
3684 blob = rs.getBlob(1);
3686 if (blob == null)
3687 continue;
3688 blobLength = rs.getInt(2);
3689 try {
3690 ps.setBlob(1,blob);
3691 ps.setInt(2,blobLength);
3692 ps.executeUpdate();
3693 System.out.println("FAIL setBlob worked on INT column");
3694 } catch (SQLException e) {
3695 TestUtil.dumpSQLExceptions(e,
3696 "22005".equals(e.getSQLState()) ||
3697 "XCL12".equals(e.getSQLState()));
3698 break;
3699 }
3700 }
3701 rs.close();
3702 conn.commit();
3703 stmt.close();
3704
3705 }
3706 catch (SQLException e) {
3707 TestUtil.dumpSQLExceptions(e);
3708 }
3709 catch (Throwable e) {
3710 System.out.println("FAIL -- unexpected exception:" + e.toString());
3711 if (debug) e.printStackTrace(System.out);
3712 }
3713 System.out.println("blobTest54 finished");
3714 }
3715
3716
3717
3721 private static void blobTest6(Connection conn)
3722 {
3723 ResultSet rs;
3724 Statement stmt;
3725 System.out.println(START + "blobTest6");
3726 try
3727 {
3728 stmt = conn.createStatement();
3729 rs = stmt.executeQuery("select a,b from testBlob");
3730 int i = 0, blobLength = 0;
3731 Blob blob;
3732 while (rs.next())
3733 {
3734 if (i > 0)
3735 break;
3736 i++;
3737 blob = rs.getBlob(1);
3738 if (blob == null)
3739 continue;
3740 blobLength = rs.getInt(2);
3741
3743 try
3745 {
3746 blob.getBytes(0,5);
3747 }
3748 catch (SQLException e)
3749 {
3750 TestUtil.dumpSQLExceptions(e,isOutOfBoundException(e));
3751 }
3752 try
3754 {
3755 blob.getBytes(1,-76);
3756 }
3757 catch (SQLException e)
3758 {
3759 TestUtil.dumpSQLExceptions(e,isOutOfBoundException(e));
3760 }
3761 try
3763 {
3764 blob.getBytes(1,-1);
3765 }
3766 catch (SQLException e)
3767 {
3768 TestUtil.dumpSQLExceptions(e,isOutOfBoundException(e));
3769 }
3770 try {
3772 blob.getBytes(0,0);
3773 } catch (SQLException e) {
3774 TestUtil.dumpSQLExceptions(e,isOutOfBoundException(e));
3775 }
3776 try {
3778 blob.getBytes(blobLength + 2,0);
3779 } catch (SQLException e) {
3780 TestUtil.dumpSQLExceptions(e,isOutOfBoundException(e));
3781 }
3782 try
3784 {
3785 blob.position(new byte[0],-4000);
3786 }
3787 catch (SQLException e)
3788 {
3789 TestUtil.dumpSQLExceptions(e,isOutOfBoundException(e));
3790 }
3791 try
3793 {
3794 blob.position((byte[]) null,5);
3796 }
3797 catch (SQLException e)
3798 {
3799 TestUtil.dumpSQLExceptions(e,isNullSearchPattern(e));
3800 }
3801 try
3803 {
3804 blob.position(blob,-42);
3805 }
3806 catch (SQLException e)
3807 {
3808 TestUtil.dumpSQLExceptions(e,isOutOfBoundException(e));
3809 }
3810 try
3812 {
3813 blob.position((Blob ) null,5);
3814 }
3815 catch (SQLException e)
3816 {
3817 TestUtil.dumpSQLExceptions(e,isNullSearchPattern(e));
3818 }
3819 }
3820 stmt.close();
3821 conn.commit();
3822 System.out.println("blobTest6 finished");
3823 }
3824 catch (SQLException e) {
3825 TestUtil.dumpSQLExceptions(e);
3826 }
3827 catch (Throwable e) {
3828 if (e instanceof NullPointerException )
3829 {
3830 if (isDerbyNet)
3831 System.out.println("NullPointerException: KNOWN JCC issue Bug 5247");
3832 }
3833 else
3834 {
3835 System.out.println("FAIL -- unexpected exception:" + e.toString());
3836 if (debug) e.printStackTrace(System.out);
3837 }
3838 }
3839 }
3840
3841
3845 private static void blobTest7(Connection conn)
3846 {
3847 ResultSet rs, rs2;
3848 Statement stmt1, stmt2;
3849 System.out.println(START + "blobTest7");
3850 try
3851 {
3852 stmt1 = conn.createStatement();
3853 stmt1.execute("create table testBlobX (a blob(300K), b integer)");
3854 PreparedStatement ps = conn.prepareStatement(
3855 "insert into testBlobX values(?,?)");
3856 stmt2 = conn.createStatement();
3857 rs = stmt2.executeQuery("select a,b from testBlob");
3858 Blob blob;
3859 int blobLength;
3860 while (rs.next())
3861 {
3862 blob = rs.getBlob(1);
3864 if (blob == null)
3865 continue;
3866 blobLength = rs.getInt(2);
3867 ps.setBlob(1,blob);
3868 ps.setInt(2,blobLength);
3869 ps.executeUpdate();
3870 }
3871 rs.close();
3872 conn.commit();
3873
3874 rs2 = stmt2.executeQuery("select a,b from testBlobX");
3875 Blob blob2;
3876 int blobLength2, j = 0;
3877 while (rs2.next())
3878 {
3879 j++;
3880 blob2 = rs2.getBlob(1);
3882 if (blob2 == null)
3883 continue;
3884 blobLength2 = rs2.getInt(2);
3885 if (blob2.length() != blobLength2)
3886 System.out.println("FAILED at row " + j);
3887 }
3888 rs2.close();
3889
3890 stmt1.close();
3891 stmt2.close();
3892 conn.commit();
3893 System.out.println("blobTest7 finished");
3894 }
3895 catch (SQLException e) {
3896 TestUtil.dumpSQLExceptions(e);
3897 }
3898 catch (Throwable e) {
3899 System.out.println("blobTest7 FAIL -- unexpected exception:" + e.toString());
3900 e.fillInStackTrace();
3901 if (debug) e.printStackTrace(System.out);
3902 }
3903 }
3904
3905
3906
3907
3908 private static void blobTest91(Connection conn)
3911 {
3912 ResultSet rs;
3913 Statement stmt;
3914 System.out.println(START + "blobTest91");
3915 try {
3916 stmt = conn.createStatement();
3917 rs = stmt.executeQuery("select a,b from testBlob");
3918 byte[] buff = new byte[128];
3919 Blob [] blobArray = new Blob [numRows];
3920 int[] blobLengthArray = new int[numRows];
3921 int j = 0;
3922 while (rs.next())
3923 {
3924 blobArray[j] = rs.getBlob(1);
3925 blobLengthArray[j++] = rs.getInt(2);
3926 }
3927 rs.close();
3928 stmt.close();
3929
3930 for (int i = 0; i < numRows; i++)
3931 {
3932 if (blobArray[i] == null)
3933 {
3934 System.out.println("row " + i + " is null, skipped");
3935 continue;
3936 }
3937 InputStream fin = blobArray[i].getBinaryStream();
3938 int columnSize = 0;
3939 for (;;) {
3940 int size = fin.read(buff);
3941 if (size == -1)
3942 break;
3943 columnSize += size;
3944 }
3945 if (columnSize != blobLengthArray[i])
3946 System.out.println("test failed, columnSize should be " +
3947 blobLengthArray[i] + ", but it is " + columnSize + ", i = " + i);
3948 if (columnSize != blobArray[i].length())
3949 System.out.println("test failed, blobArray[i].length() should be " + columnSize
3950 + ", but it is " + blobArray[i].length() + ", i = " + i);
3951 System.out.println("done row " + i + ", length was " + blobLengthArray[i]);
3952 }
3953 System.out.println("blobTest91 finished");
3954 }
3955 catch (SQLException e) {
3956 TestUtil.dumpSQLExceptions(e);
3957 }
3958 catch (Throwable e) {
3959 System.out.println("FAIL -- unexpected exception:" + e.toString());
3960 if (debug) e.printStackTrace(System.out);
3961 }
3962 }
3963
3964
3965
3969 private static void blobTest92(Connection conn)
3970 {
3971 ResultSet rs;
3972 Statement stmt,stmt2;
3973 System.out.println(START + "blobTest92");
3974 try
3975 {
3976 stmt = conn.createStatement();
3977 rs = stmt.executeQuery("select a,b from testBlob");
3978 Blob blob = null, shortBlob = null;
3980 int blobLength;
3981 while (rs.next())
3982 {
3983 blobLength = rs.getInt(2);
3984 if (blobLength == 10000)
3985 blob = rs.getBlob(1);
3986 if (blobLength == 26)
3987 shortBlob = rs.getBlob(1);
3988 }
3989 rs.close();
3990
3991 Connection conn2 = ij.startJBMS();
3992 conn2.setAutoCommit(false);
3995 if (!TestUtil.isNetFramework())
3996 {
3997
4005 stmt2 = conn2.createStatement();
4008 stmt2.executeUpdate("update testBlob set a = null where b = 26");
4009 if (shortBlob.length() != 26)
4010 System.out.println("FAILED: blob length changed to " + shortBlob.length());
4011
4013 stmt2 = conn2.createStatement();
4014 stmt2.executeUpdate("update testBlob set b = b + 1 where b = 10000");
4015 throw new Exception ("FAIL: Should have gotten lock timeout");
4016 }
4017 else
4018 {
4019 System.out.println("Locks not held by Network Server for Blobs since they are materialized on client");
4020 }
4021 conn.commit();
4022 conn2.commit();
4023 System.out.println("blobTest92 finished");
4024 }
4025 catch (SQLException e) {
4026 TestUtil.dumpSQLExceptions(e);
4027 }
4028 catch (Throwable e) {
4029 System.out.println("FAIL -- unexpected exception:" + e.toString());
4030 if (debug) e.printStackTrace(System.out);
4031 }
4032 }
4033
4034
4035
4038 private static void blobTest93(Connection conn)
4039 {
4040 ResultSet rs;
4041 Statement stmt, stmt2;
4042 System.out.println(START + "blobTest93");
4043 try
4044 {
4045 stmt = conn.createStatement();
4046 stmt.execute("create table testLongRowBlob (a varchar(2000))");
4048 stmt.execute("alter table testLongRowBlob add column b varchar(3000)");
4049 stmt.execute("alter table testLongRowBlob add column c varchar(2000)");
4050 stmt.execute("alter table testLongRowBlob add column d varchar(3000)");
4051 stmt.execute("alter table testLongRowBlob add column e blob(300k)");
4052 PreparedStatement ps = conn.prepareStatement(
4053 "insert into testLongRowBlob values(?,?,?,?,?)");
4054 ps.setString(1,Formatters.padString("blaaa",2000));
4055 ps.setString(2,Formatters.padString("tralaaaa",3000));
4056 ps.setString(3,Formatters.padString("foodar",2000));
4057 ps.setString(4,Formatters.padString("moped",3000));
4058 File file = new File (fileName[1]);
4059 if (file.length() < 10000)
4060 System.out.println("ERROR: wrong file tested");
4061 InputStream fileIn = new FileInputStream (file);
4062
4063 ps.setBinaryStream(5, fileIn, (int)file.length());
4064 ps.executeUpdate();
4065 fileIn.close();
4066 conn.commit();
4067
4068 stmt = conn.createStatement();
4069 rs = stmt.executeQuery("select e from testLongRowBlob");
4070 Blob blob = null;
4071 while (rs.next()) {
4072 blob = rs.getBlob(1);
4073 checkBlobAgainstFile(1, blob);
4074 }
4075 rs.close();
4076
4077 Connection conn2 = ij.startJBMS();
4078 conn2.setAutoCommit(false);
4081 stmt2 = conn2.createStatement();
4083 stmt2.executeUpdate("update testLongRowBlob set e = null where a = 'blaaa'");
4084
4085 conn.commit();
4086 conn2.commit();
4087 System.out.println("blobTest93 finished");
4088 }
4089 catch (SQLException e) {
4090 TestUtil.dumpSQLExceptions(e);
4091 }
4092 catch (Throwable e) {
4093 System.out.println("FAIL -- unexpected exception:" + e.toString());
4094 if (debug) e.printStackTrace(System.out);
4095 }
4096 }
4097
4098
4099
4103 private static void blobTest94(Connection conn)
4104 {
4105 ResultSet rs;
4106 Statement stmt;
4107 System.out.println(START + "blobTest94");
4108 try
4109 {
4110 stmt = conn.createStatement();
4111 rs = stmt.executeQuery("select a,b from testBlob");
4112 Blob blob = null, shortBlob = null;
4114 int blobLength;
4115 while (rs.next())
4116 {
4117 blobLength = rs.getInt(2);
4118 if (blobLength == 10000)
4119 blob = rs.getBlob(1);
4120 if (blobLength == 26)
4121 shortBlob = rs.getBlob(1);
4122 }
4123 rs.close();
4124 conn.commit();
4125
4126 if (shortBlob != null)
4128 System.out.println("shortBlob length after commit is " + shortBlob.length());
4129
4131 try
4132 {
4133 blob.length();
4134 }
4135 catch (SQLException e)
4136 {
4137 TestUtil.dumpSQLExceptions(e);
4138 }
4139 try
4140 {
4141 blob.getBytes(2,3);
4142 }
4143 catch (SQLException e)
4144 {
4145 TestUtil.dumpSQLExceptions(e);
4146 }
4147 try
4148 {
4149 blob.getBinaryStream();
4150 }
4151 catch (SQLException e)
4152 {
4153 TestUtil.dumpSQLExceptions(e);
4154 }
4155 try
4156 {
4157 blob.position("foo".getBytes("US-ASCII"),2);
4158 }
4159 catch (SQLException e)
4160 {
4161 TestUtil.dumpSQLExceptions(e);
4162 }
4163 try
4164 {
4165 blob.position(blob,2);
4166 }
4167 catch (SQLException e)
4168 {
4169 TestUtil.dumpSQLExceptions(e);
4170 }
4171
4172 System.out.println("blobTest94 finished");
4173 }
4174 catch (SQLException e) {
4175 TestUtil.dumpSQLExceptions(e);
4176 }
4177 catch (Throwable e) {
4178 System.out.println("FAIL -- unexpected exception:" + e.toString());
4179 if (debug) e.printStackTrace(System.out);
4180 }
4181
4182 }
4183
4184
4188 private static void blobTest95(Connection conn)
4189 {
4190 ResultSet rs;
4191 Statement stmt;
4192 System.out.println(START + "blobTest95");
4193 try
4194 {
4195 stmt = conn.createStatement();
4196 rs = stmt.executeQuery("select a,b from testBlob");
4197 Blob blob = null, shortBlob = null;
4199 int blobLength;
4200 while (rs.next())
4201 {
4202 blobLength = rs.getInt(2);
4203 if (blobLength == 10000)
4204 blob = rs.getBlob(1);
4205 if (blobLength == 26)
4206 shortBlob = rs.getBlob(1);
4207 }
4208 rs.close();
4209 conn.rollback();
4210 conn.close();
4211
4212 try {
4213 System.out.println("shortBlob length after closing the connection is " + shortBlob.length());
4215 }
4216 catch (SQLException e)
4217 {
4218 expectedExceptionForNSOnly (e);
4219 }
4220
4221 try
4223 {
4224 blob.length();
4225 }
4226 catch (SQLException e)
4227 {
4228 expectedExceptionForNSOnly (e);
4229 }
4230 try
4231 {
4232 blob.getBytes(2,3);
4233 }
4234 catch (SQLException e)
4235 {
4236 expectedExceptionForNSOnly (e);
4237 }
4238 try
4239 {
4240 blob.getBinaryStream();
4241 }
4242 catch (SQLException e)
4243 {
4244 expectedExceptionForNSOnly (e);
4245 }
4246 try
4247 {
4248 blob.position("foo".getBytes("US-ASCII"),2);
4249 }
4250 catch (SQLException e)
4251 {
4252 expectedExceptionForNSOnly (e);
4253 }
4254 try
4255 {
4256 blob.position(blob,2);
4257 }
4258 catch (SQLException e)
4259 {
4260 expectedExceptionForNSOnly (e);
4261 }
4262
4263 conn = ij.startJBMS();
4265 conn.setAutoCommit(false);
4266
4267 System.out.println("blobTest95 finished");
4268 }
4269 catch (SQLException e) {
4270 TestUtil.dumpSQLExceptions(e);
4271 }
4272 catch (Throwable e) {
4273 System.out.println("FAIL -- unexpected exception:" + e.toString());
4274 if (debug) e.printStackTrace(System.out);
4275 }
4276 }
4277
4278
4279
4287 private static void blobTest96(Connection conn)
4288 {
4289 ResultSet rs;
4290 Statement stmt;
4291 System.out.println(START + "blobTest96");
4292 try {
4293 stmt = conn.createStatement();
4294 rs = stmt.executeQuery("select a,b from testBlob");
4295 byte[] buff = new byte[128];
4296 Blob [] blobArray = new Blob [numRows];
4297 int[] blobLengthArray = new int[numRows];
4298 int j = 0;
4299 while (rs.next())
4300 {
4301 blobArray[j] = rs.getBlob(1);
4302 blobLengthArray[j++] = rs.getInt(2);
4303 }
4304 rs.close();
4305 stmt.close();
4306
4307
4309 for (int i = 0; i < numRows; i++)
4310 {
4311 blobArray[i] = null;
4312 }
4313
4314
4316 System.gc();
4317 System.gc();
4318
4319
4322 System.out.println("blobTest96 finished");
4323 }
4324 catch (SQLException e) {
4325 TestUtil.dumpSQLExceptions(e);
4326 }
4327 catch (Throwable e) {
4328 System.out.println("FAIL -- unexpected exception:" + e.toString());
4329 if (debug) e.printStackTrace(System.out);
4330 }
4331 }
4332
4333
4334
4335
4350 private static void blobTest10(Connection conn) throws SQLException {
4351 Statement s = conn.createStatement();
4352 s.execute("CREATE TABLE derby1382 (c1 int, c2 blob)");
4353
4354 String blobData = "initial blob ";
4355 PreparedStatement ps =
4356 conn.prepareStatement("insert into derby1382 values (?, ?)");
4357 for (int i=0; i<10; i++) {
4358 ps.setInt(1, i);
4359 ps.setBytes(2, (blobData + i).getBytes());
4360 ps.execute();
4361 }
4362 ps.close();
4363
4364 Statement scrollStmt = conn.createStatement(
4365 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
4366 ResultSet rs = scrollStmt.executeQuery("SELECT * FROM derby1382");
4367
4368 rs.first();
4369 checkContentsBeforeAndAfterUpdatingBlob(rs);
4370 rs.next();
4371 checkContentsBeforeAndAfterUpdatingBlob(rs);
4372 rs.relative(3);
4373 checkContentsBeforeAndAfterUpdatingBlob(rs);
4374 rs.absolute(7);
4375 checkContentsBeforeAndAfterUpdatingBlob(rs);
4376 rs.previous();
4377 checkContentsBeforeAndAfterUpdatingBlob(rs);
4378 rs.last();
4379 checkContentsBeforeAndAfterUpdatingBlob(rs);
4380 rs.previous();
4381 checkContentsBeforeAndAfterUpdatingBlob(rs);
4382
4383 rs.close();
4384 scrollStmt.close();
4385
4386 s.execute("DROP TABLE derby1382");
4387 s.close();
4388 }
4389
4390 private static void checkContentsBeforeAndAfterUpdatingBlob(ResultSet rs)
4391 throws SQLException
4392 {
4393 Blob b;
4394 byte[] value, expectedValue;
4395 String blobData = "initial blob ";
4396 String updatedBlobData = "updated blob ";
4397
4398 b = rs.getBlob(2);
4399 value = b.getBytes(1, blobData.length() + 1);
4401 expectedValue = (blobData + rs.getInt(1)).getBytes();
4402 if (!Arrays.equals(value, expectedValue)) {
4403 System.out.println("blobTest10 - Error: wrong blob value");
4404 }
4405
4406 value = (updatedBlobData + rs.getInt(1)).getBytes();
4408 rs.updateBytes(2, value);
4409 rs.updateRow();
4410 rs.next(); rs.previous(); b = rs.getBlob(2);
4414 value = b.getBytes(1, updatedBlobData.length() + 1);
4416 expectedValue = (updatedBlobData + rs.getInt(1)).getBytes();
4417 if (!Arrays.equals(value, expectedValue))
4418 {
4419 System.out.println("blobTest10 - Error: wrong blob value");
4420 }
4421 }
4422
4423
4432 private static void blobTest11(Connection conn) throws SQLException {
4433 Statement s = conn.createStatement();
4434 s.execute("CREATE TABLE derby1421 (c1 int, c2 blob)");
4435
4436 String blobData = "initial blob ";
4437 PreparedStatement ps =
4438 conn.prepareStatement("insert into derby1421 values (?, ?)");
4439 for (int i=0; i<10; i++) {
4440 ps.setInt(1, i);
4441 ps.setBytes(2, (blobData + i).getBytes());
4442 ps.execute();
4443 }
4444 ps.close();
4445
4446 Statement scrollStmt = conn.createStatement(
4447 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
4448 ResultSet rs = scrollStmt.executeQuery("SELECT * FROM derby1421");
4449
4450 rs.first();
4451 updateBlobWithUpdateBinaryStream(rs);
4452 rs.next();
4453 updateBlobWithUpdateBinaryStream(rs);
4454 rs.relative(3);
4455 updateBlobWithUpdateBinaryStream(rs);
4456 rs.absolute(7);
4457 updateBlobWithUpdateBinaryStream(rs);
4458 rs.previous();
4459 updateBlobWithUpdateBinaryStream(rs);
4460 rs.last();
4461 updateBlobWithUpdateBinaryStream(rs);
4462 rs.previous();
4463 updateBlobWithUpdateBinaryStream(rs);
4464
4465 rs.close();
4466 scrollStmt.close();
4467
4468 s.execute("DROP TABLE derby1421");
4469 s.close();
4470 }
4471
4472 private static void updateBlobWithUpdateBinaryStream(ResultSet rs)
4473 throws SQLException
4474 {
4475 Blob b;
4476 byte[] value, expectedValue;
4477 String blobData = "initial blob ";
4478 String updatedBlobData = "updated blob ";
4479
4480 b = rs.getBlob(2);
4481 value = b.getBytes(1, blobData.length() + 1);
4483 expectedValue = (blobData + rs.getInt(1)).getBytes();
4484 if (!Arrays.equals(value, expectedValue)) {
4485 System.out.println("blobTest11 - Error: wrong blob value");
4486 }
4487
4488 value = (updatedBlobData + rs.getInt(1)).getBytes();
4490 InputStream updateValue = new ByteArrayInputStream (value);
4491 rs.updateBinaryStream(2, updateValue, value.length);
4492 rs.updateRow();
4493 rs.next(); rs.previous(); b = rs.getBlob(2);
4497 value = b.getBytes(1, updatedBlobData.length() + 1);
4499 expectedValue = (updatedBlobData + rs.getInt(1)).getBytes();
4500 if (!Arrays.equals(value, expectedValue))
4501 {
4502 System.out.println("blobTest11 - Error: wrong blob value");
4503 }
4504 }
4505
4506
4518 private static void blobNegativeTest_Derby265(Connection conn)
4519 throws SQLException , FileNotFoundException ,IOException {
4520 System.out.println(START + "blobTestNegativeTest_Derby265");
4521 Statement s = conn.createStatement();
4523 s.execute("create table \"MAPS_BLOB\"(MAP_ID int, MAP_NAME varchar(20),REGION varchar(20),AREA varchar(20), PHOTO_FORMAT varchar(20),PICTURE blob(2G))");
4524 conn.setAutoCommit(false);
4525 PreparedStatement ps = conn.prepareStatement("insert into \"MAPS_BLOB\" values(?,?,?,?,?,?)");
4526
4527 for (int i = 0; i < 3; i++) {
4528 FileInputStream fis = new FileInputStream (fileName[4]);
4529 ps.setInt(1, i);
4530 ps.setString(2, "x" + i);
4531 ps.setString(3, "abc");
4532 ps.setString(4, "abc");
4533 ps.setString(5, "abc");
4534 ps.setBinaryStream(6, new java.io.BufferedInputStream (fis), 300000);
4535 ps.executeUpdate();
4536 fis.close();
4537 }
4538 conn.commit();
4539
4540 conn.setAutoCommit(true);
4541 System.out.println("-----------------------------");
4542
4543 s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
4544 ResultSet.CONCUR_READ_ONLY);
4545 s.execute("SELECT \"MAP_ID\", \"MAP_NAME\", \"REGION\", \"AREA\", \"PHOTO_FORMAT\", \"PICTURE\" FROM \"MAPS_BLOB\"");
4546 ResultSet rs1 = s.getResultSet();
4547 Statement s2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
4548 ResultSet.CONCUR_READ_ONLY);
4549 s2.executeQuery("SELECT \"MAP_ID\", \"MAP_NAME\", \"REGION\", \"AREA\", \"PHOTO_FORMAT\", \"PICTURE\" FROM \"MAPS_BLOB\"");
4550 ResultSet rs2 = s2.getResultSet();
4551 rs2.next();
4552
4553 Blob b2 = rs2.getBlob(6);
4554 rs1.next();
4555 Blob b1 = rs1.getBlob(6);
4556 try {
4557 rs1.close();
4558 rs2.next();
4559 rs2.getBlob(6);
4560 } catch (SQLException sqle) {
4561 String sqlstate = sqle.getSQLState();
4562 boolean expected = (sqlstate != null &&
4563 (sqlstate.equals("XJ073") || sqlstate.equals("XCL30")));
4564 if ( !expected ) { TestUtil.dumpSQLExceptions(sqle,expected); }
4565 }
4566 finally {
4567 rs2.close();
4568 s2.close();
4569 s.close();
4570 ps.close();
4571 }
4572
4573 }
4574
4575
4587 private static void clobNegativeTest_Derby265(Connection conn)
4588 throws SQLException , FileNotFoundException ,IOException {
4589
4590 System.out.println(START + "clobNegativeTest_Derby265");
4591 Statement s = conn.createStatement();
4593 s.execute("create table \"MAPS\"(MAP_ID int, MAP_NAME varchar(20),REGION varchar(20),AREA varchar(20), PHOTO_FORMAT varchar(20),PICTURE clob(2G))");
4594 conn.setAutoCommit(false);
4595 PreparedStatement ps = conn.prepareStatement("insert into \"MAPS\" values(?,?,?,?,?,?)");
4596 for (int i = 0; i < 3; i++) {
4597 File file = new File (fileName[4]);
4598 InputStream fileIS = new FileInputStream (file);
4599 Reader fr = new InputStreamReader (fileIS, "US-ASCII");
4600 ps.setInt(1, i);
4601 ps.setString(2, "x" + i);
4602 ps.setString(3, "abc");
4603 ps.setString(4, "abc");
4604 ps.setString(5, "abc");
4605 ps.setCharacterStream(6, fr, 300000);
4606 ps.executeUpdate();
4607 fr.close();
4608 }
4609 conn.commit();
4610
4611 conn.setAutoCommit(true);
4612 System.out.println("-----------------------------");
4613 s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
4614 ResultSet.CONCUR_READ_ONLY);
4615 s.execute("SELECT \"MAP_ID\", \"MAP_NAME\", \"REGION\", \"AREA\", \"PHOTO_FORMAT\", \"PICTURE\" FROM \"MAPS\"");
4616 ResultSet rs1 = s.getResultSet();
4617 Statement s2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
4618 ResultSet.CONCUR_READ_ONLY);
4619 s2.executeQuery("SELECT \"MAP_ID\", \"MAP_NAME\", \"REGION\", \"AREA\", \"PHOTO_FORMAT\", \"PICTURE\" FROM \"MAPS\"");
4620 ResultSet rs2 = s2.getResultSet();
4621 rs2.next();
4622
4623 Clob b2 = rs2.getClob(6); rs1.next();
4625 Clob b1 = rs1.getClob(6);
4626 try {
4627 rs1.close(); rs2.next();
4629 rs2.getClob(6); } catch (SQLException sqle) {
4631 String sqlstate = sqle.getSQLState();
4632 boolean expected = (sqlstate != null &&
4633 (sqlstate.equals("XJ073") || sqlstate.equals("XCL30")));
4634 if ( !expected ) { TestUtil.dumpSQLExceptions(sqle,expected); }
4635 }
4636 finally {
4637 rs2.close();
4638 s2.close();
4639 s.close();
4640 ps.close();
4641 }
4642
4643 }
4644 static void printInterval(Clob clob, long pos, int length,
4645 int testNum, int iteration, int clobLength)
4646 {
4647 if (pos > clobLength + 1)
4648 System.out.println("CLOB getSubString " + pos + " > " + clobLength);
4649 try
4650 {
4651
4656 String ss = clob.getSubString(pos,length);
4657
4658 System.out.println(iteration + "(" + testNum + ") (len " + length + ") " + ss);
4659 if (ss.length() > length)
4660 System.out.println("FAIL getSubString("+pos+","+length+") returned a string of length " + ss.length());
4661
4662 long l1 = clob.length();
4663 if (l1 != clobLength) {
4664 System.out.println("CHECK - test has mismatched lengths " + l1 + " != " + clobLength);
4665 }
4666 if (pos > clobLength + 1)
4667 System.out.println("CLOB FAIL - NO ERROR ON getSubString POS TOO LARGE " + pos + " > " + clobLength);
4668
4669
4670 }
4671 catch (SQLException e)
4672 {
4673 String state = e.getSQLState();
4674 boolean expected = false;
4675
4676
4677 if (pos < 1 || pos > clobLength + 1)
4678 {
4679 if (isOutOfBoundException(e))
4680 expected = true;
4681 }
4682 else
4683 {
4684 System.out.println("FAIL -- unexpected exception:" + e.toString());
4685 }
4686 TestUtil.dumpSQLExceptions(e,expected);
4687 }
4688 catch (StringIndexOutOfBoundsException obe)
4689 {
4690 if ((pos > clobLength) && isDerbyNet)
4692 {
4693 System.out.println("EXPECTED Out of bounds exception");
4694 }
4695 else
4696 {
4697 System.out.println("FAIL -- unexpected exception:" + obe.toString());
4698 if (debug) obe.printStackTrace(System.out);
4699 }
4700 }
4701 catch (Exception e)
4702 {
4703 System.out.println("FAIL -- unexpected exception:" + e.toString());
4704 if (debug) e.printStackTrace(System.out);
4705 }
4706 }
4707
4708 static void printInterval(Blob blob, long pos, int length,
4709 int testNum, int iteration, long blobLength)
4710 {
4711 if (pos > blobLength + 1)
4712 System.out.println("testing Blob.getBytes() with pos " + pos + " > " + blobLength);
4713 try
4714 {
4715 System.out.println(iteration + "(" + testNum + ") " +
4718 new String (blob.getBytes(pos,length), "US-ASCII"));
4719
4720 long l1 = blob.length();
4721 if (l1 != blobLength) {
4722 System.out.println("CHECK - test has mismatched lengths " + l1 + " != " + blobLength);
4723 }
4724 if (pos > blobLength + 1)
4725 System.out.println("FAIL testing Blob.getBytes() with pos " + pos + " > " + blobLength);
4726 }
4727 catch (SQLException e)
4728 {
4729 String state = e.getSQLState();
4730 boolean expected = false;
4731
4732 if (pos < 1 || pos > blobLength + 1)
4733 expected = isOutOfBoundException(e);
4734
4735 TestUtil.dumpSQLExceptions(e, expected);
4736 }
4737 catch (Exception e)
4738 {
4739 System.out.println("FAIL: Caught exception " +
4740 e.toString());
4741 }
4742 }
4743 static void printPosition(
4744 int rowNum,
4745 String searchStr,
4746 long position,
4747 Clob clob,
4748 long clobLength)
4749 {
4750
4751 try
4752 {
4753
4754 long result = clob.position(searchStr,position);
4755
4756 System.out.println("Found " + searchStr + " in row " + rowNum +
4757 " starting from position " + position + " at position " +
4758 (result == -1 ? " NOTFOUND " : Long.toString(result)));
4759
4760 long l1 = clob.length();
4761 if (l1 != clobLength) {
4762 System.out.println("CHECK - test has mismatched lengths " + l1 + " != " + clobLength);
4763 }
4764
4765
4766 }
4767 catch (SQLException e)
4768 {
4769 String state = e.getSQLState();
4770 boolean expected = false;
4771
4772 if (position < 1 || position > clobLength)
4773 expected = isOutOfBoundException(e);
4774
4775 if (searchStr == null)
4776 if ("XJ072".equals(state))
4777 expected = true;
4778
4779 if ("".equals(searchStr))
4780 if ("XJ078".equals(state))
4781 expected = true;
4782
4783
4784 TestUtil.dumpSQLExceptions(e, expected);
4785 e.printStackTrace(System.out);
4786 }
4787 }
4788
4789 static void printPosition(
4790 int rowNum,
4791 String searchStr,
4792 long position,
4793 Blob blob, int blobLength) throws UnsupportedEncodingException
4794 {
4795 try
4796 {
4797 long result = blob.position(searchStr.getBytes("US-ASCII"),position);
4798 if ((searchStr == "") && (result == 1))
4799 return;
4800 if (result != -1)
4801 System.out.println("Found " + searchStr + " in row " + rowNum +
4802 " starting from position " + position + " at position " +
4803 result);
4804
4805 long l1 = blob.length();
4806 if (l1 != blobLength) {
4807 System.out.println("CHECK - test has mismatched lengths " + l1 + " != " + blobLength);
4808 }
4809 }
4810 catch (SQLException e)
4811 {
4812 String state = e.getSQLState();
4813 boolean expected = false;
4814
4815 if (position < 1 || position > blobLength)
4816 expected = isOutOfBoundException(e);
4817
4818 if (searchStr == null)
4819 if ("XJ072".equals(state))
4820 expected = true;
4821
4822 if ("".equals(searchStr))
4823 if ("XJ078".equals(state))
4824 expected = true;
4825
4826
4827 TestUtil.dumpSQLExceptions(e, expected);
4828 }
4829 }
4830
4834
4835 static private void expectedExceptionForNSOnly (SQLException se)
4836 {
4837 TestUtil.dumpSQLExceptions(se, isDerbyNet);
4838 }
4839
4840 static private boolean isOutOfBoundException(SQLException se)
4841 {
4842 String sqlState = se.getSQLState();
4843 String msg = se.getMessage();
4844 if ("XJ070".equals(sqlState) ||
4845 "XJ071".equals(sqlState) ||
4846 "XJ076".equals(sqlState) ||
4847 (sqlState == null &&
4848 ((msg.indexOf("Index Out Of Bound") != -1) ||
4849 (msg.indexOf("Invalid position") != -1))))
4850 return true;
4851
4852 return false;
4853 }
4854
4855 static private boolean isNullSearchPattern(SQLException se)
4856 {
4857 String sqlState = se.getSQLState();
4858 if ("XJ072".equals(sqlState) ||
4859 (sqlState == null &&
4860 se.getMessage().indexOf("Search pattern cannot be null") != -1))
4861 return true;
4862
4863 return false;
4864 }
4865
4866
4873 private static void checkBlobAgainstFile(int fileid,
4874 Blob blob) throws SQLException , IOException {
4875
4876 if (blob.length() != fileLength[fileid])
4877 System.out.println("FAIL BLOB length mismatch: "
4878 + "BLOB " + blob.length()
4879 + " FILE " + fileLength[fileid]);
4880
4881 long blobsum = getStreamCheckSum(blob.getBinaryStream());
4882 if (blobsum != fileCRC32[fileid])
4883 System.out.println("FAIL BLOB checksum mismatch: "
4884 + "BLOB " + blobsum
4885 + " FILE " + fileCRC32[fileid]);
4886 }
4887
4888
4892 private static long getStreamCheckSum(InputStream in)
4893 throws IOException
4894 {
4895 CRC32 sum = new CRC32 ();
4896
4897 byte[] buf = new byte[32*1024];
4898
4899 for (;;) {
4900 int read = in.read(buf);
4901 if (read == -1)
4902 break;
4903 sum.update(buf, 0, read);
4904 }
4905 in.close();
4906 return sum.getValue();
4907 }
4908
4909 private static void showClobContents(String tag, ResultSet rs)
4910 throws SQLException , IOException
4911 {
4912 while (rs.next())
4913 {
4914 InputStream is = rs.getAsciiStream(1);
4915 if (is == null) {
4916 System.out.println(tag + ": NULL");
4917 continue;
4918 }
4919 long clobcrc = getStreamCheckSum(is);
4920 int clobLength = rs.getInt(2);
4921 int b = rs.getInt(3);
4922
4923 System.out.println(tag + ": length " +
4924 clobLength + " crc32 " + clobcrc + " " + b);
4925 }
4926 }
4927}
4928
4929
4930 |
Popular Tags |
Java API By Example, From Geeks To Geeks.