001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.db;
007:
008: import java.io.ByteArrayInputStream;
009: import java.io.CharArrayReader;
010: import java.io.InputStream;
011: import java.io.Reader;
012: import java.io.StringReader;
013: import java.sql.Blob;
014: import java.sql.Clob;
015: import java.sql.Connection;
016: import java.sql.DatabaseMetaData;
017: import java.sql.PreparedStatement;
018: import java.sql.ResultSet;
019: import java.sql.SQLException;
020: import java.sql.Savepoint;
021: import java.sql.Statement;
022: import java.util.ArrayList;
023: import java.util.Random;
024:
025: import org.h2.constant.SysProperties;
026: import org.h2.store.FileLister;
027: import org.h2.test.TestBase;
028: import org.h2.util.IOUtils;
029: import org.h2.util.StringUtils;
030:
031: /**
032: * Tests LOB and CLOB data types.
033: */
034: public class TestLob extends TestBase {
035:
036: public void test() throws Exception {
037: if (config.memory) {
038: return;
039: }
040: testLobDelete();
041: testLobVariable();
042: testLobDrop();
043: testLobNoClose();
044: testLobTransactions(10);
045: testLobTransactions(10000);
046: testLobRollbackStop();
047: testLobCopy();
048: testLobHibernate();
049: testLobCopy(false);
050: testLobCopy(true);
051: testLobCompression(false);
052: testLobCompression(true);
053: testManyLobs();
054: testClob();
055: testUpdateLob();
056: testLobReconnect();
057: testLob(false);
058: testLob(true);
059: testJavaObject();
060: }
061:
062: private void testLobDelete() throws Exception {
063: if (config.memory) {
064: return;
065: }
066: deleteDb("lob");
067: Connection conn = reconnect(null);
068: Statement stat = conn.createStatement();
069: stat.execute("CREATE TABLE TEST(ID INT, DATA CLOB)");
070: stat
071: .execute("INSERT INTO TEST SELECT X, SPACE(10000) FROM SYSTEM_RANGE(1, 10)");
072: ArrayList list = FileLister.getDatabaseFiles(baseDir, "lob",
073: true);
074: stat.execute("UPDATE TEST SET DATA = SPACE(5000)");
075: for (int i = 0; i < 3; i++) {
076: System.gc();
077: }
078: stat.execute("CHECKPOINT");
079: ArrayList list2 = FileLister.getDatabaseFiles(baseDir, "lob",
080: true);
081: if (list2.size() >= list.size() + 5) {
082: error("Expected not many more files, got " + list2.size()
083: + " was " + list.size());
084: }
085: stat.execute("DELETE FROM TEST");
086: for (int i = 0; i < 3; i++) {
087: System.gc();
088: }
089: stat.execute("CHECKPOINT");
090: ArrayList list3 = FileLister.getDatabaseFiles(baseDir, "lob",
091: true);
092: if (list3.size() >= list.size()) {
093: error("Expected less files, got " + list2.size() + " was "
094: + list.size());
095: }
096: conn.close();
097: }
098:
099: private void testLobVariable() throws Exception {
100: deleteDb("lob");
101: Connection conn = reconnect(null);
102: Statement stat = conn.createStatement();
103: stat.execute("CREATE TABLE TEST(ID INT, DATA CLOB)");
104: stat.execute("INSERT INTO TEST VALUES(1, SPACE(100000))");
105: stat.execute("SET @TOTAL = SELECT DATA FROM TEST WHERE ID=1");
106: stat.execute("DROP TABLE TEST");
107: stat.execute("CALL @TOTAL LIKE '%X'");
108: stat.execute("CREATE TABLE TEST(ID INT, DATA CLOB)");
109: stat.execute("INSERT INTO TEST VALUES(1, @TOTAL)");
110: stat.execute("INSERT INTO TEST VALUES(2, @TOTAL)");
111: stat.execute("DROP TABLE TEST");
112: stat.execute("CALL @TOTAL LIKE '%X'");
113: conn.close();
114: }
115:
116: private void testLobDrop() throws Exception {
117: if (config.logMode == 0 || config.networked) {
118: return;
119: }
120: deleteDb("lob");
121: Connection conn = reconnect(null);
122: Statement stat = conn.createStatement();
123: for (int i = 0; i < 500; i++) {
124: stat.execute("CREATE TABLE T" + i + "(ID INT, C CLOB)");
125: }
126: stat.execute("CREATE TABLE TEST(ID INT, C CLOB)");
127: stat.execute("INSERT INTO TEST VALUES(1, SPACE(10000))");
128: for (int i = 0; i < 500; i++) {
129: stat.execute("DROP TABLE T" + i);
130: }
131: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
132: while (rs.next()) {
133: rs.getString("C");
134: }
135: conn.close();
136: }
137:
138: private void testLobNoClose() throws Exception {
139: if (config.logMode == 0 || config.networked) {
140: return;
141: }
142: deleteDb("lob");
143: Connection conn = reconnect(null);
144: conn.createStatement().execute(
145: "CREATE TABLE TEST(ID IDENTITY, DATA CLOB)");
146: conn.createStatement().execute(
147: "INSERT INTO TEST VALUES(1, SPACE(10000))");
148: ResultSet rs = conn.createStatement().executeQuery(
149: "SELECT DATA FROM TEST");
150: rs.next();
151: SysProperties.lobCloseBetweenReads = true;
152: Reader in = rs.getCharacterStream(1);
153: in.read();
154: conn.createStatement().execute("DELETE FROM TEST");
155: SysProperties.lobCloseBetweenReads = false;
156: conn.createStatement().execute(
157: "INSERT INTO TEST VALUES(1, SPACE(10000))");
158: rs = conn.createStatement().executeQuery(
159: "SELECT DATA FROM TEST");
160: rs.next();
161: in = rs.getCharacterStream(1);
162: in.read();
163: conn.setAutoCommit(false);
164: try {
165: conn.createStatement().execute("DELETE FROM TEST");
166: conn.commit();
167: // DELETE does not fail in Linux, but in Windows
168: // error("Error expected");
169: // but reading afterwards should fail
170: int len = 0;
171: while (true) {
172: int x = in.read();
173: if (x < 0) {
174: break;
175: }
176: len++;
177: }
178: in.close();
179: if (len > 0) {
180: // in Linux, it seems it is still possible to read in files
181: // even if they are deleted
182: if (System.getProperty("os.name").indexOf("Windows") > 0) {
183: error("Error expected; len=" + len);
184: }
185: }
186: } catch (SQLException e) {
187: checkNotGeneralException(e);
188: }
189: conn.rollback();
190: conn.close();
191: }
192:
193: private void testLobTransactions(int spaceLen) throws Exception {
194: if (config.logMode == 0) {
195: return;
196: }
197: // Constants.LOB_CLOSE_BETWEEN_READS = true;
198:
199: deleteDb("lob");
200: Connection conn = reconnect(null);
201: conn
202: .createStatement()
203: .execute(
204: "CREATE TABLE TEST(ID IDENTITY, DATA CLOB, DATA2 VARCHAR)");
205: conn.setAutoCommit(false);
206: Random random = new Random(0);
207: int rows = 0;
208: Savepoint sp = null;
209: int len = getSize(100, 400);
210: for (int i = 0; i < len; i++) {
211: switch (random.nextInt(10)) {
212: case 0:
213: trace("insert");
214: conn.createStatement().execute(
215: "INSERT INTO TEST(DATA, DATA2) VALUES('" + i
216: + "' || SPACE(" + spaceLen + "), '" + i
217: + "')");
218: rows++;
219: break;
220: case 1:
221: if (rows > 0) {
222: trace("delete");
223: conn.createStatement().execute(
224: "DELETE FROM TEST WHERE ID="
225: + random.nextInt(rows));
226: }
227: break;
228: case 2:
229: if (rows > 0) {
230: trace("update");
231: conn.createStatement().execute(
232: "UPDATE TEST SET DATA='x' || DATA, DATA2='x' || DATA2 WHERE ID="
233: + random.nextInt(rows));
234: }
235: break;
236: case 3:
237: if (rows > 0) {
238: trace("commit");
239: conn.commit();
240: sp = null;
241: }
242: break;
243: case 4:
244: if (rows > 0) {
245: trace("rollback");
246: conn.rollback();
247: sp = null;
248: }
249: break;
250: case 5:
251: trace("savepoint");
252: sp = conn.setSavepoint();
253: break;
254: case 6:
255: if (sp != null) {
256: trace("rollback to savepoint");
257: conn.rollback(sp);
258: }
259: break;
260: case 7:
261: if (rows > 0) {
262: trace("checkpoint");
263: conn.createStatement().execute("CHECKPOINT");
264: trace("shutdown immediately");
265: conn.createStatement().execute(
266: "SHUTDOWN IMMEDIATELY");
267: trace("shutdown done");
268: conn = reconnect(null);
269: conn.setAutoCommit(false);
270: sp = null;
271: }
272: break;
273: }
274: ResultSet rs = conn.createStatement().executeQuery(
275: "SELECT * FROM TEST");
276: while (rs.next()) {
277: String d1 = rs.getString("DATA").trim();
278: String d2 = rs.getString("DATA2").trim();
279: check(d1, d2);
280: }
281:
282: }
283: conn.close();
284: }
285:
286: private void testLobRollbackStop() throws Exception {
287: if (config.logMode == 0) {
288: return;
289: }
290: deleteDb("lob");
291: Connection conn = reconnect(null);
292: conn.createStatement().execute(
293: "CREATE TABLE TEST(ID INT PRIMARY KEY, DATA CLOB)");
294: conn.createStatement().execute(
295: "INSERT INTO TEST VALUES(1, SPACE(10000))");
296: conn.setAutoCommit(false);
297: conn.createStatement().execute("DELETE FROM TEST");
298: conn.createStatement().execute("CHECKPOINT");
299: conn.createStatement().execute("SHUTDOWN IMMEDIATELY");
300: conn = reconnect(null);
301: ResultSet rs = conn.createStatement().executeQuery(
302: "SELECT * FROM TEST");
303: check(rs.next());
304: rs.getInt(1);
305: check(rs.getString(2).length(), 10000);
306: conn.close();
307: }
308:
309: private void testLobCopy() throws Exception {
310: deleteDb("lob");
311: Connection conn = reconnect(null);
312: Statement stat = conn.createStatement();
313: stat.execute("create table test(id int, data clob)");
314: stat.execute("insert into test values(1, space(1000));");
315: stat.execute("insert into test values(2, space(10000));");
316: stat.execute("create table test2(id int, data clob);");
317: stat.execute("insert into test2 select * from test;");
318: stat.execute("drop table test;");
319: stat.execute("select * from test2;");
320: stat.execute("update test2 set id=id;");
321: stat.execute("select * from test2;");
322: conn.close();
323: }
324:
325: private void testLobHibernate() throws Exception {
326: deleteDb("lob");
327: Connection conn0 = reconnect(null);
328:
329: conn0.getAutoCommit();
330: conn0.setAutoCommit(false);
331: DatabaseMetaData dbMeta0 = conn0.getMetaData();
332: dbMeta0.getDatabaseProductName();
333: dbMeta0.getDatabaseMajorVersion();
334: dbMeta0.getDatabaseProductVersion();
335: dbMeta0.getDriverName();
336: dbMeta0.getDriverVersion();
337: dbMeta0.supportsResultSetType(1004);
338: dbMeta0.supportsBatchUpdates();
339: dbMeta0.dataDefinitionCausesTransactionCommit();
340: dbMeta0.dataDefinitionIgnoredInTransactions();
341: dbMeta0.supportsGetGeneratedKeys();
342: conn0.getAutoCommit();
343: conn0.getAutoCommit();
344: conn0.commit();
345: conn0.setAutoCommit(true);
346: Statement stat0 = conn0.createStatement();
347: stat0.executeUpdate("drop table CLOB_ENTITY if exists");
348: stat0.getWarnings();
349: stat0
350: .executeUpdate("create table CLOB_ENTITY (ID bigint not null, DATA clob, CLOB_DATA clob, primary key (ID))");
351: stat0.getWarnings();
352: stat0.close();
353: conn0.getWarnings();
354: conn0.clearWarnings();
355: conn0.setAutoCommit(false);
356: conn0.getAutoCommit();
357: conn0.getAutoCommit();
358: PreparedStatement prep0 = conn0
359: .prepareStatement("select max(ID) from CLOB_ENTITY");
360: ResultSet rs0 = prep0.executeQuery();
361: rs0.next();
362: rs0.getLong(1);
363: rs0.wasNull();
364: rs0.close();
365: prep0.close();
366: conn0.getAutoCommit();
367: PreparedStatement prep1 = conn0
368: .prepareStatement("insert into CLOB_ENTITY (DATA, CLOB_DATA, ID) values (?, ?, ?)");
369: prep1.setNull(1, 2005);
370: StringBuffer buff = new StringBuffer(10000);
371: for (int i = 0; i < 10000; i++) {
372: buff.append((char) ('0' + (i % 10)));
373: }
374: Reader x = new StringReader(buff.toString());
375: prep1.setCharacterStream(2, x, 10000);
376: prep1.setLong(3, 1);
377: prep1.addBatch();
378: prep1.executeBatch();
379: prep1.close();
380: conn0.getAutoCommit();
381: conn0.getAutoCommit();
382: conn0.commit();
383: conn0.isClosed();
384: conn0.getWarnings();
385: conn0.clearWarnings();
386: conn0.getAutoCommit();
387: conn0.getAutoCommit();
388: PreparedStatement prep2 = conn0
389: .prepareStatement("select c_.ID as ID0_0_, c_.DATA as S_, c_.CLOB_DATA as CLOB3_0_0_ from CLOB_ENTITY c_ where c_.ID=?");
390: prep2.setLong(1, 1);
391: ResultSet rs1 = prep2.executeQuery();
392: rs1.next();
393: rs1.getCharacterStream("S_");
394: Clob clob0 = rs1.getClob("CLOB3_0_0_");
395: rs1.wasNull();
396: rs1.next();
397: rs1.close();
398: prep2.getMaxRows();
399: prep2.getQueryTimeout();
400: prep2.close();
401: conn0.getAutoCommit();
402: Reader r = clob0.getCharacterStream();
403: for (int i = 0; i < 10000; i++) {
404: int ch = r.read();
405: if (ch != ('0' + (i % 10))) {
406: error("expected " + (char) ('0' + (i % 10)) + " got: "
407: + ch + " (" + (char) ch + ")");
408: }
409: }
410: int ch = r.read();
411: if (ch != -1) {
412: error("expected -1 got: " + ch);
413: }
414: conn0.close();
415: }
416:
417: private void testLobCopy(boolean compress) throws Exception {
418: deleteDb("lob");
419: Connection conn;
420: conn = reconnect(null);
421: Statement stat = conn.createStatement();
422: if (compress) {
423: conn.createStatement().execute("SET COMPRESS_LOB LZF");
424: } else {
425: conn.createStatement().execute("SET COMPRESS_LOB NO");
426: }
427: conn = reconnect(conn);
428: stat = conn.createStatement();
429: ResultSet rs;
430: rs = stat
431: .executeQuery("select value from information_schema.settings where NAME='COMPRESS_LOB'");
432: rs.next();
433: check(rs.getString(1), compress ? "LZF" : "NO");
434: checkFalse(rs.next());
435: stat.execute("create table test(text clob)");
436: stat.execute("create table test2(text clob)");
437: StringBuffer buff = new StringBuffer();
438: for (int i = 0; i < 1000; i++) {
439: buff.append(' ');
440: }
441: String spaces = buff.toString();
442: stat.execute("insert into test values('" + spaces + "')");
443: stat.execute("insert into test2 select * from test");
444: rs = stat.executeQuery("select * from test2");
445: rs.next();
446: check(rs.getString(1), spaces);
447: stat.execute("drop table test");
448: rs = stat.executeQuery("select * from test2");
449: rs.next();
450: check(rs.getString(1), spaces);
451: stat.execute("alter table test2 add column id int before text");
452: rs = stat.executeQuery("select * from test2");
453: rs.next();
454: check(rs.getString("text"), spaces);
455: conn.close();
456: }
457:
458: private void testLobCompression(boolean compress) throws Exception {
459: deleteDb("lob");
460: Connection conn;
461: conn = reconnect(null);
462: if (compress) {
463: conn.createStatement().execute("SET COMPRESS_LOB LZF");
464: } else {
465: conn.createStatement().execute("SET COMPRESS_LOB NO");
466: }
467: conn.createStatement().execute(
468: "CREATE TABLE TEST(ID INT PRIMARY KEY, C CLOB)");
469: PreparedStatement prep = conn
470: .prepareStatement("INSERT INTO TEST VALUES(?, ?)");
471: long time = System.currentTimeMillis();
472: int len = getSize(10, 40);
473: if (config.networked && config.big) {
474: len = 5;
475: }
476: StringBuffer buff = new StringBuffer();
477: for (int i = 0; i < 100; i++) {
478: buff.append(StringUtils.xmlNode("content", null,
479: "This is a test " + i));
480: }
481: String xml = buff.toString();
482: for (int i = 0; i < len; i++) {
483: prep.setInt(1, i);
484: prep.setString(2, xml + i);
485: prep.execute();
486: }
487: for (int i = 0; i < len; i++) {
488: ResultSet rs = conn.createStatement().executeQuery(
489: "SELECT * FROM TEST");
490: while (rs.next()) {
491: if (i == 0) {
492: check(xml + rs.getInt(1), rs.getString(2));
493: } else {
494: Reader r = rs.getCharacterStream(2);
495: String result = IOUtils.readStringAndClose(r, -1);
496: check(xml + rs.getInt(1), result);
497: }
498: }
499: }
500: time = System.currentTimeMillis() - time;
501: trace("time: " + time + " compress: " + compress);
502: conn.close();
503: }
504:
505: private void testManyLobs() throws Exception {
506: deleteDb("lob");
507: Connection conn;
508: conn = reconnect(null);
509: conn
510: .createStatement()
511: .execute(
512: "CREATE TABLE TEST(ID INT PRIMARY KEY, B BLOB, C CLOB)");
513: int len = getSize(10, 2000);
514: if (config.networked) {
515: len = 100;
516: }
517:
518: int start = 1, increment = 19;
519:
520: PreparedStatement prep = conn
521: .prepareStatement("INSERT INTO TEST(ID, B, C) VALUES(?, ?, ?)");
522: for (int i = start; i < len; i += increment) {
523: int l = i;
524: prep.setInt(1, i);
525: prep.setBinaryStream(2, getRandomStream(l, i), -1);
526: prep.setCharacterStream(3, getRandomReader(l, i), -1);
527: prep.execute();
528: }
529:
530: conn = reconnect(conn);
531: ResultSet rs = conn.createStatement().executeQuery(
532: "SELECT * FROM TEST ORDER BY ID");
533: while (rs.next()) {
534: int i = rs.getInt("ID");
535: Blob b = rs.getBlob("B");
536: Clob c = rs.getClob("C");
537: int l = i;
538: check(b.length(), l);
539: check(c.length(), l);
540: checkStream(b.getBinaryStream(), getRandomStream(l, i), -1);
541: checkReader(c.getCharacterStream(), getRandomReader(l, i),
542: -1);
543: }
544:
545: prep = conn
546: .prepareStatement("UPDATE TEST SET B=?, C=? WHERE ID=?");
547: for (int i = start; i < len; i += increment) {
548: int l = i;
549: prep.setBinaryStream(1, getRandomStream(l, -i), -1);
550: prep.setCharacterStream(2, getRandomReader(l, -i), -1);
551: prep.setInt(3, i);
552: prep.execute();
553: }
554:
555: conn = reconnect(conn);
556: rs = conn.createStatement().executeQuery(
557: "SELECT * FROM TEST ORDER BY ID");
558: while (rs.next()) {
559: int i = rs.getInt("ID");
560: Blob b = rs.getBlob("B");
561: Clob c = rs.getClob("C");
562: int l = i;
563: check(b.length(), l);
564: check(c.length(), l);
565: checkStream(b.getBinaryStream(), getRandomStream(l, -i), -1);
566: checkReader(c.getCharacterStream(), getRandomReader(l, -i),
567: -1);
568: }
569:
570: conn.close();
571: }
572:
573: private void testClob() throws Exception {
574: deleteDb("lob");
575: Connection conn;
576: conn = reconnect(null);
577: conn.createStatement().execute(
578: "CREATE TABLE TEST(ID IDENTITY, C CLOB)");
579: PreparedStatement prep = conn
580: .prepareStatement("INSERT INTO TEST(C) VALUES(?)");
581: prep.setCharacterStream(1, new CharArrayReader("Bohlen"
582: .toCharArray()), "Bohlen".length());
583: prep.execute();
584: prep.setCharacterStream(1, new CharArrayReader("B\u00f6hlen"
585: .toCharArray()), "B\u00f6hlen".length());
586: prep.execute();
587: prep.setCharacterStream(1, getRandomReader(501, 1), -1);
588: prep.execute();
589: prep.setCharacterStream(1, getRandomReader(1501, 2), 401);
590: prep.execute();
591: conn = reconnect(conn);
592: ResultSet rs = conn.createStatement().executeQuery(
593: "SELECT * FROM TEST ORDER BY ID");
594: rs.next();
595: check("Bohlen", rs.getString("C"));
596: checkReader(new CharArrayReader("Bohlen".toCharArray()), rs
597: .getCharacterStream("C"), -1);
598: rs.next();
599: checkReader(new CharArrayReader("B\u00f6hlen".toCharArray()),
600: rs.getCharacterStream("C"), -1);
601: rs.next();
602: checkReader(getRandomReader(501, 1),
603: rs.getCharacterStream("C"), -1);
604: Clob clob = rs.getClob("C");
605: checkReader(getRandomReader(501, 1), clob.getCharacterStream(),
606: -1);
607: check(clob.length(), 501);
608: rs.next();
609: checkReader(getRandomReader(401, 2),
610: rs.getCharacterStream("C"), -1);
611: checkReader(getRandomReader(1500, 2), rs
612: .getCharacterStream("C"), 401);
613: clob = rs.getClob("C");
614: checkReader(getRandomReader(1501, 2),
615: clob.getCharacterStream(), 401);
616: checkReader(getRandomReader(401, 2), clob.getCharacterStream(),
617: 401);
618: check(clob.length(), 401);
619: checkFalse(rs.next());
620: conn.close();
621: }
622:
623: private Connection reconnect(Connection conn) throws Exception {
624: long time = System.currentTimeMillis();
625: if (conn != null) {
626: conn.close();
627: }
628: conn = getConnection("lob");
629: trace("re-connect=" + (System.currentTimeMillis() - time));
630: return conn;
631: }
632:
633: void testUpdateLob() throws Exception {
634: deleteDb("lob");
635: Connection conn;
636: conn = reconnect(null);
637:
638: PreparedStatement prep = conn
639: .prepareStatement("CREATE TABLE IF NOT EXISTS p( id int primary key, rawbyte BLOB ); ");
640: prep.execute();
641: prep.close();
642:
643: prep = conn.prepareStatement("INSERT INTO p(id) VALUES(?);");
644: for (int i = 0; i < 10; i++) {
645: prep.setInt(1, i);
646: prep.execute();
647: }
648: prep.close();
649:
650: prep = conn
651: .prepareStatement("UPDATE p set rawbyte=? WHERE id=?");
652: for (int i = 0; i < 8; i++) {
653: prep.setBinaryStream(1, getRandomStream(10000, i), 0);
654: prep.setInt(2, i);
655: prep.execute();
656: }
657: prep.close();
658: conn.commit();
659:
660: conn = reconnect(conn);
661:
662: conn.setAutoCommit(true);
663: prep = conn
664: .prepareStatement("UPDATE p set rawbyte=? WHERE id=?");
665: for (int i = 8; i < 10; i++) {
666: prep.setBinaryStream(1, getRandomStream(10000, i), 0);
667: prep.setInt(2, i);
668: prep.execute();
669: }
670: prep.close();
671:
672: prep = conn.prepareStatement("SELECT * from p");
673: ResultSet rs = prep.executeQuery();
674: while (rs.next()) {
675: for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
676: rs.getMetaData().getColumnName(i);
677: rs.getString(i);
678: }
679: }
680: conn.close();
681: }
682:
683: void testLobReconnect() throws Exception {
684: deleteDb("lob");
685: Connection conn = reconnect(null);
686: Statement stat = conn.createStatement();
687: stat
688: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, TEXT CLOB)");
689: PreparedStatement prep;
690: prep = conn.prepareStatement("INSERT INTO TEST VALUES(1, ?)");
691: String s = new String(getRandomChars(10000, 1));
692: byte[] data = s.getBytes("UTF-8");
693: prep.setBinaryStream(1, new ByteArrayInputStream(data), 0);
694: prep.execute();
695:
696: conn = reconnect(conn);
697: stat = conn.createStatement();
698: ResultSet rs = stat
699: .executeQuery("SELECT * FROM TEST WHERE ID=1");
700: rs.next();
701: checkStream(new ByteArrayInputStream(data), rs
702: .getBinaryStream("TEXT"), -1);
703:
704: prep = conn.prepareStatement("UPDATE TEST SET TEXT = ?");
705: s = new String(getRandomChars(10201, 1));
706: prep.setBinaryStream(1, new ByteArrayInputStream(data), 0);
707: prep.execute();
708:
709: conn = reconnect(conn);
710: stat = conn.createStatement();
711: rs = stat.executeQuery("SELECT * FROM TEST WHERE ID=1");
712: rs.next();
713: checkStream(new ByteArrayInputStream(data), rs
714: .getBinaryStream("TEXT"), -1);
715:
716: stat.execute("DROP TABLE IF EXISTS TEST");
717: conn.close();
718: }
719:
720: void testLob(boolean clob) throws Exception {
721: deleteDb("lob");
722: Connection conn = reconnect(null);
723: conn = reconnect(conn);
724: Statement stat = conn.createStatement();
725: stat.execute("DROP TABLE IF EXISTS TEST");
726: PreparedStatement prep;
727: ResultSet rs;
728: long time;
729: stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE "
730: + (clob ? "CLOB" : "BLOB") + ")");
731:
732: int len = getSize(1, 1000);
733: if (config.networked && config.big) {
734: len = 100;
735: }
736:
737: time = System.currentTimeMillis();
738: prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
739: for (int i = 0; i < len; i += (i + i + 1)) {
740: prep.setInt(1, i);
741: int size = i * i;
742: if (clob) {
743: prep.setCharacterStream(2, getRandomReader(size, i), 0);
744: } else {
745: prep.setBinaryStream(2, getRandomStream(size, i), 0);
746: }
747: prep.execute();
748: }
749: trace("insert=" + (System.currentTimeMillis() - time));
750: traceMemory();
751: conn = reconnect(conn);
752:
753: time = System.currentTimeMillis();
754: prep = conn.prepareStatement("SELECT ID, VALUE FROM TEST");
755: rs = prep.executeQuery();
756: while (rs.next()) {
757: int id = rs.getInt("ID");
758: int size = id * id;
759: if (clob) {
760: Reader rt = rs.getCharacterStream(2);
761: checkReader(rt, getRandomReader(size, id), -1);
762: checkReader((Reader) rs.getObject(2), getRandomReader(
763: size, id), -1);
764: } else {
765: InputStream in = rs.getBinaryStream(2);
766: checkStream(in, getRandomStream(size, id), -1);
767: checkStream((InputStream) rs.getObject(2),
768: getRandomStream(size, id), -1);
769: }
770: }
771: trace("select=" + (System.currentTimeMillis() - time));
772: traceMemory();
773:
774: conn = reconnect(conn);
775:
776: time = System.currentTimeMillis();
777: prep = conn.prepareStatement("DELETE FROM TEST WHERE ID=?");
778: for (int i = 0; i < len; i++) {
779: prep.setInt(1, i);
780: prep.executeUpdate();
781: }
782: trace("delete=" + (System.currentTimeMillis() - time));
783: traceMemory();
784: conn = reconnect(conn);
785:
786: conn.setAutoCommit(false);
787: prep = conn.prepareStatement("INSERT INTO TEST VALUES(1, ?)");
788: if (clob) {
789: prep.setCharacterStream(1, getRandomReader(0, 0), 0);
790: } else {
791: prep.setBinaryStream(1, getRandomStream(0, 0), 0);
792: }
793: prep.execute();
794: conn.rollback();
795: prep.execute();
796: conn.commit();
797:
798: conn.createStatement().execute("DELETE FROM TEST WHERE ID=1");
799: conn.rollback();
800: conn.createStatement().execute("DELETE FROM TEST WHERE ID=1");
801: conn.commit();
802:
803: conn.createStatement().execute("DROP TABLE TEST");
804: conn.close();
805: }
806:
807: void testJavaObject() throws Exception {
808: deleteDb("lob");
809: Connection conn = getConnection("lob");
810: conn.createStatement().execute(
811: "CREATE TABLE TEST(ID INT PRIMARY KEY, DATA OTHER)");
812: PreparedStatement prep = conn
813: .prepareStatement("INSERT INTO TEST VALUES(1, ?)");
814: prep.setObject(1, new TestLobObject("abc"));
815: prep.execute();
816: ResultSet rs = conn.createStatement().executeQuery(
817: "SELECT * FROM TEST");
818: rs.next();
819: Object oa = rs.getObject(2);
820: TestLobObject a = (TestLobObject) oa;
821: Object ob = rs.getObject("DATA");
822: TestLobObject b = (TestLobObject) ob;
823: check(a.data, "abc");
824: check(b.data, "abc");
825: checkFalse(rs.next());
826: conn.close();
827: }
828:
829: private void checkStream(InputStream a, InputStream b, int len)
830: throws Exception {
831: // this doesn't actually read anything - just tests reading 0 bytes
832: a.read(new byte[0]);
833: b.read(new byte[0]);
834: a.read(new byte[10], 3, 0);
835: b.read(new byte[10], 0, 0);
836:
837: for (int i = 0; len < 0 || i < len; i++) {
838: int ca = a.read();
839: a.read(new byte[0]);
840: int cb = b.read();
841: check(ca, cb);
842: if (ca == -1) {
843: break;
844: }
845: }
846: a.read(new byte[10], 3, 0);
847: b.read(new byte[10], 0, 0);
848: a.read(new byte[0]);
849: b.read(new byte[0]);
850: a.close();
851: b.close();
852: }
853:
854: private void checkReader(Reader a, Reader b, int len)
855: throws Exception {
856: for (int i = 0; len < 0 || i < len; i++) {
857: int ca = a.read();
858: int cb = b.read();
859: check(ca, cb);
860: if (ca == -1) {
861: break;
862: }
863: }
864: a.close();
865: b.close();
866: }
867:
868: private Reader getRandomReader(int len, int seed) {
869: return new CharArrayReader(getRandomChars(len, seed));
870: }
871:
872: private char[] getRandomChars(int len, int seed) {
873: Random random = new Random(seed);
874: char[] buff = new char[len];
875: for (int i = 0; i < len; i++) {
876: char ch;
877: do {
878: ch = (char) random.nextInt(Character.MAX_VALUE);
879: // UTF8: String.getBytes("UTF-8") only returns 1 byte for
880: // 0xd800-0xdfff
881: } while (ch >= 0xd800 && ch <= 0xdfff);
882: buff[i] = ch;
883: }
884: return buff;
885: }
886:
887: private InputStream getRandomStream(int len, int seed) {
888: Random random = new Random(seed);
889: byte[] buff = new byte[len];
890: random.nextBytes(buff);
891: return new ByteArrayInputStream(buff);
892: }
893:
894: }
|