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.jdbc;
007:
008: import java.io.ByteArrayInputStream;
009: import java.io.IOException;
010: import java.io.StringReader;
011: import java.math.BigDecimal;
012: import java.sql.Connection;
013: import java.sql.ParameterMetaData;
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import java.sql.ResultSetMetaData;
017: import java.sql.SQLException;
018: import java.sql.Statement;
019: import java.sql.Timestamp;
020: import java.sql.Types;
021:
022: import org.h2.test.TestBase;
023:
024: /**
025: * Tests for the PreparedStatement implementation.
026: */
027: public class TestPreparedStatement extends TestBase {
028:
029: static final int LOB_SIZE = 4000, LOB_SIZE_BIG = 512 * 1024;
030:
031: public void test() throws Exception {
032:
033: deleteDb("preparedStatement");
034: Connection conn = getConnection("preparedStatement");
035: testExecuteErrorTwice(conn);
036: testTempView(conn);
037: testInsertFunction(conn);
038: testPrepareRecompile(conn);
039: testMaxRowsChange(conn);
040: testUnknownDataType(conn);
041: testCancelReuse(conn);
042: testCoalesce(conn);
043: testPreparedStatementMetaData(conn);
044: testDate(conn);
045: testArray(conn);
046: testUUIDGeneratedKeys(conn);
047: testSetObject(conn);
048: testPreparedSubquery(conn);
049: testLikeIndex(conn);
050: testCasewhen(conn);
051: testSubquery(conn);
052: testObject(conn);
053: if (config.jdk14) {
054: testIdentity(conn);
055: }
056: testDataTypes(conn);
057: testBlob(conn);
058: testClob(conn);
059: testParameterMetaData(conn);
060: conn.close();
061: }
062:
063: private void testExecuteErrorTwice(Connection conn)
064: throws Exception {
065: PreparedStatement prep = conn
066: .prepareStatement("CREATE TABLE BAD AS SELECT A");
067: try {
068: prep.execute();
069: error();
070: } catch (SQLException e) {
071: checkNotGeneralException(e);
072: }
073: try {
074: prep.execute();
075: error();
076: } catch (SQLException e) {
077: checkNotGeneralException(e);
078: }
079: }
080:
081: private void testTempView(Connection conn) throws Exception {
082: Statement stat = conn.createStatement();
083: PreparedStatement prep;
084: stat.execute("CREATE TABLE TEST(FIELD INT PRIMARY KEY)");
085: stat.execute("INSERT INTO TEST VALUES(1)");
086: stat.execute("INSERT INTO TEST VALUES(2)");
087: prep = conn
088: .prepareStatement("select FIELD FROM "
089: + "(select FIELD FROM (SELECT FIELD FROM TEST WHERE FIELD = ?) AS T2 "
090: + "WHERE T2.FIELD = ?) AS T3 WHERE T3.FIELD = ?");
091: prep.setInt(1, 1);
092: prep.setInt(2, 1);
093: prep.setInt(3, 1);
094: ResultSet rs = prep.executeQuery();
095: rs.next();
096: check(1, rs.getInt(1));
097: prep.setInt(1, 2);
098: prep.setInt(2, 2);
099: prep.setInt(3, 2);
100: rs = prep.executeQuery();
101: rs.next();
102: check(2, rs.getInt(1));
103: stat.execute("DROP TABLE TEST");
104: }
105:
106: private void testInsertFunction(Connection conn) throws Exception {
107: Statement stat = conn.createStatement();
108: PreparedStatement prep;
109: ResultSet rs;
110:
111: stat.execute("CREATE TABLE TEST(ID INT, H BINARY)");
112: prep = conn
113: .prepareStatement("INSERT INTO TEST VALUES(?, HASH('SHA256', STRINGTOUTF8(?), 5))");
114: prep.setInt(1, 1);
115: prep.setString(2, "One");
116: prep.execute();
117: prep.setInt(1, 2);
118: prep.setString(2, "Two");
119: prep.execute();
120: rs = stat.executeQuery("SELECT COUNT(DISTINCT H) FROM TEST");
121: rs.next();
122: check(rs.getInt(1), 2);
123:
124: stat.execute("DROP TABLE TEST");
125: }
126:
127: private void testPrepareRecompile(Connection conn) throws Exception {
128: Statement stat = conn.createStatement();
129: PreparedStatement prep;
130: ResultSet rs;
131:
132: prep = conn
133: .prepareStatement("SELECT COUNT(*) FROM DUAL WHERE ? IS NULL");
134: prep.setString(1, null);
135: prep.executeQuery();
136: stat.execute("CREATE TABLE TEST(ID INT)");
137: stat.execute("DROP TABLE TEST");
138: prep.setString(1, null);
139: prep.executeQuery();
140: prep.setString(1, "X");
141: rs = prep.executeQuery();
142: rs.next();
143: check(rs.getInt(1), 0);
144:
145: stat.execute("CREATE TABLE t1 (c1 INT, c2 VARCHAR(10))");
146: stat
147: .execute("INSERT INTO t1 SELECT X, CONCAT('Test', X) FROM SYSTEM_RANGE(1, 5);");
148: prep = conn
149: .prepareStatement("SELECT c1, c2 FROM t1 WHERE c1 = ?");
150: prep.setInt(1, 1);
151: prep.executeQuery();
152: stat.execute("CREATE TABLE t2 (x int PRIMARY KEY)");
153: prep.setInt(1, 2);
154: rs = prep.executeQuery();
155: rs.next();
156: check(rs.getInt(1), 2);
157: prep.setInt(1, 3);
158: rs = prep.executeQuery();
159: rs.next();
160: check(rs.getInt(1), 3);
161: stat.execute("DROP TABLE t1, t2");
162:
163: }
164:
165: private void testMaxRowsChange(Connection conn) throws Exception {
166: PreparedStatement prep = conn
167: .prepareStatement("SELECT * FROM SYSTEM_RANGE(1, 100)");
168: ResultSet rs;
169: for (int j = 1; j < 20; j++) {
170: prep.setMaxRows(j);
171: rs = prep.executeQuery();
172: for (int i = 0; i < j; i++) {
173: check(rs.next());
174: }
175: checkFalse(rs.next());
176: }
177: }
178:
179: private void testUnknownDataType(Connection conn) throws Exception {
180: try {
181: PreparedStatement prep = conn
182: .prepareStatement("SELECT * FROM (SELECT ? FROM DUAL)");
183: prep.setInt(1, 1);
184: prep.execute();
185: error();
186: } catch (SQLException e) {
187: checkNotGeneralException(e);
188: }
189: PreparedStatement prep = conn.prepareStatement("SELECT -?");
190: prep.setInt(1, 1);
191: prep.execute();
192: prep = conn.prepareStatement("SELECT ?-?");
193: prep.setInt(1, 1);
194: prep.setInt(2, 2);
195: prep.execute();
196: }
197:
198: private void testCancelReuse(Connection conn) throws Exception {
199: conn.createStatement().execute(
200: "CREATE ALIAS YIELD FOR \"java.lang.Thread.yield\"");
201: final PreparedStatement prep = conn
202: .prepareStatement("SELECT YIELD() FROM SYSTEM_RANGE(1, 1000000) LIMIT ?");
203: prep.setInt(1, 100000000);
204: Thread t = new Thread() {
205: public void run() {
206: try {
207: prep.execute();
208: } catch (SQLException e) {
209: // ignore
210: }
211: }
212: };
213: t.start();
214: Thread.sleep(10);
215: try {
216: prep.cancel();
217: } catch (SQLException e) {
218: this .checkNotGeneralException(e);
219: }
220: prep.setInt(1, 1);
221: ResultSet rs = prep.executeQuery();
222: check(rs.next());
223: check(rs.getInt(1), 0);
224: checkFalse(rs.next());
225: }
226:
227: private void testCoalesce(Connection conn) throws Exception {
228: Statement stat = conn.createStatement();
229: stat.executeUpdate("create table test(tm timestamp)");
230: stat
231: .executeUpdate("insert into test values(current_timestamp)");
232: PreparedStatement prep = conn
233: .prepareStatement("update test set tm = coalesce(?,tm)");
234: prep.setTimestamp(1, new java.sql.Timestamp(System
235: .currentTimeMillis()));
236: prep.executeUpdate();
237: stat.executeUpdate("drop table test");
238: }
239:
240: private void testPreparedStatementMetaData(Connection conn)
241: throws Exception {
242: PreparedStatement prep = conn
243: .prepareStatement("select * from table(x int = ?, name varchar = ?)");
244: ResultSetMetaData meta = prep.getMetaData();
245: check(meta.getColumnCount(), 2);
246: check(meta.getColumnTypeName(1), "INTEGER");
247: check(meta.getColumnTypeName(2), "VARCHAR");
248: prep = conn.prepareStatement("call 1");
249: meta = prep.getMetaData();
250: check(meta.getColumnCount(), 1);
251: check(meta.getColumnTypeName(1), "INTEGER");
252: }
253:
254: private void testArray(Connection conn) throws Exception {
255: PreparedStatement prep = conn
256: .prepareStatement("select * from table(x int = ?) order by x");
257: prep.setObject(1, new Object[] { new BigDecimal("1"), "2" });
258: ResultSet rs = prep.executeQuery();
259: rs.next();
260: check(rs.getString(1), "1");
261: rs.next();
262: check(rs.getString(1), "2");
263: checkFalse(rs.next());
264: }
265:
266: private void testUUIDGeneratedKeys(Connection conn)
267: throws Exception {
268: Statement stat = conn.createStatement();
269: stat
270: .execute("CREATE TABLE TEST_UUID(id UUID DEFAULT random_UUID() PRIMARY KEY)");
271: stat.execute("INSERT INTO TEST_UUID() VALUES()");
272: ResultSet rs = stat.getGeneratedKeys();
273: rs.next();
274: byte[] data = rs.getBytes(1);
275: check(data.length, 16);
276: stat.execute("DROP TABLE TEST_UUID");
277: }
278:
279: private void testSetObject(Connection conn) throws Exception {
280: Statement stat = conn.createStatement();
281: stat
282: .execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
283: PreparedStatement prep = conn
284: .prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
285: prep.setInt(1, 1);
286: prep.setObject(2, new Integer(11));
287: prep.setObject(3, null);
288: prep.execute();
289: prep.setInt(1, 2);
290: prep.setObject(2, new Integer(101), Types.OTHER);
291: prep.setObject(3, new Integer(103), Types.OTHER);
292: prep.execute();
293: PreparedStatement p2 = conn
294: .prepareStatement("SELECT * FROM TEST ORDER BY ID");
295: ResultSet rs = p2.executeQuery();
296: rs.next();
297: Object o = rs.getObject(2);
298: check(o instanceof byte[]);
299: check(rs.getObject(3) == null);
300: rs.next();
301: o = rs.getObject(2);
302: check(o instanceof byte[]);
303: o = rs.getObject(3);
304: check(o instanceof Integer);
305: check(((Integer) o).intValue(), 103);
306: checkFalse(rs.next());
307: stat.execute("DROP TABLE TEST");
308: }
309:
310: private void testDate(Connection conn) throws Exception {
311: PreparedStatement prep = conn.prepareStatement("SELECT ?");
312: Timestamp ts = Timestamp.valueOf("2001-02-03 04:05:06");
313: prep.setObject(1, new java.util.Date(ts.getTime()));
314: ResultSet rs = prep.executeQuery();
315: rs.next();
316: Timestamp ts2 = rs.getTimestamp(1);
317: check(ts.toString(), ts2.toString());
318: }
319:
320: private void testPreparedSubquery(Connection conn) throws Exception {
321: Statement s = conn.createStatement();
322: s.executeUpdate("CREATE TABLE TEST(ID IDENTITY, FLAG BIT)");
323: s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(0, FALSE)");
324: s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(1, FALSE)");
325: PreparedStatement u = conn
326: .prepareStatement("SELECT ID, FLAG FROM TEST ORDER BY ID");
327: PreparedStatement p = conn
328: .prepareStatement("UPDATE TEST SET FLAG=true WHERE ID=(SELECT ?)");
329: p.clearParameters();
330: p.setLong(1, 0);
331: check(p.executeUpdate(), 1);
332: p.clearParameters();
333: p.setLong(1, 1);
334: check(p.executeUpdate(), 1);
335: ResultSet rs = u.executeQuery();
336: check(rs.next());
337: check(rs.getInt(1), 0);
338: check(rs.getBoolean(2));
339: check(rs.next());
340: check(rs.getInt(1), 1);
341: check(rs.getBoolean(2));
342:
343: p = conn
344: .prepareStatement("SELECT * FROM TEST WHERE EXISTS(SELECT * FROM TEST WHERE ID=?)");
345: p.setInt(1, -1);
346: rs = p.executeQuery();
347: checkFalse(rs.next());
348: p.setInt(1, 1);
349: rs = p.executeQuery();
350: check(rs.next());
351:
352: s.executeUpdate("DROP TABLE IF EXISTS TEST");
353: }
354:
355: private void testParameterMetaData(Connection conn)
356: throws Exception {
357: PreparedStatement prep = conn
358: .prepareStatement("SELECT ?, ?, ? FROM DUAL");
359: ParameterMetaData pm = prep.getParameterMetaData();
360: check(pm.getParameterClassName(1), "java.lang.String");
361: check(pm.getParameterTypeName(1), "VARCHAR");
362: check(pm.getParameterCount(), 3);
363: check(pm.getParameterMode(1), ParameterMetaData.parameterModeIn);
364: check(pm.getParameterType(1), Types.VARCHAR);
365: check(pm.getPrecision(1), 0);
366: check(pm.getScale(1), 0);
367: check(pm.isNullable(1), ResultSetMetaData.columnNullableUnknown);
368: check(pm.isSigned(1), true);
369: try {
370: pm.getPrecision(0);
371: error();
372: } catch (SQLException e) {
373: checkNotGeneralException(e);
374: }
375: try {
376: pm.getPrecision(4);
377: error();
378: } catch (SQLException e) {
379: checkNotGeneralException(e);
380: }
381: prep.close();
382: try {
383: pm.getPrecision(1);
384: error();
385: } catch (SQLException e) {
386: checkNotGeneralException(e);
387: }
388: }
389:
390: private void testLikeIndex(Connection conn) throws Exception {
391: Statement stat = conn.createStatement();
392: stat
393: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
394: stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
395: stat.execute("INSERT INTO TEST VALUES(2, 'World')");
396: stat.execute("create index idxname on test(name);");
397: PreparedStatement prep, prepExe;
398:
399: prep = conn
400: .prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?");
401: check(prep.getParameterMetaData().getParameterCount(), 1);
402: prepExe = conn
403: .prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?");
404: prep.setString(1, "%orld");
405: prepExe.setString(1, "%orld");
406: ResultSet rs = prep.executeQuery();
407: rs.next();
408: String plan = rs.getString(1);
409: check(plan.indexOf("TABLE_SCAN") >= 0);
410: rs = prepExe.executeQuery();
411: rs.next();
412: check(rs.getString(2), "World");
413: checkFalse(rs.next());
414:
415: prep.setString(1, "H%");
416: prepExe.setString(1, "H%");
417: rs = prep.executeQuery();
418: rs.next();
419: String plan1 = rs.getString(1);
420: check(plan1.indexOf("IDXNAME") >= 0);
421: rs = prepExe.executeQuery();
422: rs.next();
423: check(rs.getString(2), "Hello");
424: checkFalse(rs.next());
425:
426: stat.execute("DROP TABLE IF EXISTS TEST");
427: }
428:
429: private void testCasewhen(Connection conn) throws Exception {
430: Statement stat = conn.createStatement();
431: stat.execute("CREATE TABLE TEST(ID INT)");
432: stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
433: PreparedStatement prep;
434: ResultSet rs;
435: prep = conn
436: .prepareStatement("EXPLAIN SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
437: prep.setInt(1, 1);
438: rs = prep.executeQuery();
439: rs.next();
440: String plan = rs.getString(1);
441: trace(plan);
442: rs.close();
443: prep = conn
444: .prepareStatement("EXPLAIN SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID");
445: prep.setInt(1, 1);
446: rs = prep.executeQuery();
447: rs.next();
448: plan = rs.getString(1);
449: trace(plan);
450:
451: prep = conn
452: .prepareStatement("SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID");
453: prep.setInt(1, 1);
454: rs = prep.executeQuery();
455: check(rs.next());
456: check(rs.getInt(1), 1);
457: checkFalse(rs.next());
458:
459: prep = conn
460: .prepareStatement("SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID");
461: prep.setInt(1, 1);
462: rs = prep.executeQuery();
463: check(rs.next());
464: check(rs.getInt(1), 1);
465: checkFalse(rs.next());
466:
467: prep = conn
468: .prepareStatement("SELECT * FROM TEST WHERE ? IS NULL");
469: prep.setString(1, "Hello");
470: rs = prep.executeQuery();
471: checkFalse(rs.next());
472: try {
473: prep = conn
474: .prepareStatement("select ? from dual union select ? from dual");
475: error();
476: } catch (SQLException e) {
477: checkNotGeneralException(e);
478: }
479: prep = conn
480: .prepareStatement("select cast(? as varchar) from dual union select ? from dual");
481: check(prep.getParameterMetaData().getParameterCount(), 2);
482: prep.setString(1, "a");
483: prep.setString(2, "a");
484: rs = prep.executeQuery();
485: rs.next();
486: check(rs.getString(1), "a");
487: check(rs.getString(1), "a");
488: checkFalse(rs.next());
489:
490: stat.execute("DROP TABLE TEST");
491: }
492:
493: private void testSubquery(Connection conn) throws Exception {
494: Statement stat = conn.createStatement();
495: stat.execute("CREATE TABLE TEST(ID INT)");
496: stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
497: PreparedStatement prep = conn
498: .prepareStatement("select x.id, ? from "
499: + "(select * from test where id in(?, ?)) x "
500: + "where x.id*2 <> ?");
501: check(prep.getParameterMetaData().getParameterCount(), 4);
502: prep.setInt(1, 0);
503: prep.setInt(2, 1);
504: prep.setInt(3, 2);
505: prep.setInt(4, 4);
506: ResultSet rs = prep.executeQuery();
507: rs.next();
508: check(rs.getInt(1), 1);
509: check(rs.getInt(2), 0);
510: checkFalse(rs.next());
511: stat.execute("DROP TABLE TEST");
512: }
513:
514: private void testDataTypes(Connection conn) throws Exception {
515: conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
516: ResultSet.CONCUR_READ_ONLY);
517: conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
518: ResultSet.CONCUR_UPDATABLE);
519: Statement stat = conn.createStatement();
520: PreparedStatement prep;
521: ResultSet rs;
522: trace("Create tables");
523: stat
524: .execute("CREATE TABLE T_INT(ID INT PRIMARY KEY,VALUE INT)");
525: stat
526: .execute("CREATE TABLE T_VARCHAR(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
527: stat
528: .execute("CREATE TABLE T_DECIMAL_0(ID INT PRIMARY KEY,VALUE DECIMAL(30,0))");
529: stat
530: .execute("CREATE TABLE T_DECIMAL_10(ID INT PRIMARY KEY,VALUE DECIMAL(20,10))");
531: stat
532: .execute("CREATE TABLE T_DATETIME(ID INT PRIMARY KEY,VALUE DATETIME)");
533: prep = conn
534: .prepareStatement("INSERT INTO T_INT VALUES(?,?)",
535: ResultSet.TYPE_FORWARD_ONLY,
536: ResultSet.CONCUR_READ_ONLY);
537: prep.setInt(1, 1);
538: prep.setInt(2, 0);
539: prep.executeUpdate();
540: prep.setInt(1, 2);
541: prep.setInt(2, -1);
542: prep.executeUpdate();
543: prep.setInt(1, 3);
544: prep.setInt(2, 3);
545: prep.executeUpdate();
546: prep.setInt(1, 4);
547: prep.setNull(2, Types.INTEGER);
548: prep.executeUpdate();
549: prep.setInt(1, 5);
550: prep.setBigDecimal(2, new java.math.BigDecimal("0"));
551: prep.executeUpdate();
552: prep.setInt(1, 6);
553: prep.setString(2, "-1");
554: prep.executeUpdate();
555: prep.setInt(1, 7);
556: prep.setObject(2, new Integer(3));
557: prep.executeUpdate();
558: prep.setObject(1, "8");
559: // should throw an exception
560: prep.setObject(2, null);
561: // some databases don't allow calling setObject with null (no data type)
562: prep.executeUpdate();
563: prep.setInt(1, 9);
564: prep.setObject(2, new Integer(-4), Types.VARCHAR);
565: prep.executeUpdate();
566: prep.setInt(1, 10);
567: prep.setObject(2, "5", Types.INTEGER);
568: prep.executeUpdate();
569: prep.setInt(1, 11);
570: prep.setObject(2, null, Types.INTEGER);
571: prep.executeUpdate();
572: prep.setInt(1, 12);
573: prep.setBoolean(2, true);
574: prep.executeUpdate();
575: prep.setInt(1, 13);
576: prep.setBoolean(2, false);
577: prep.executeUpdate();
578: prep.setInt(1, 14);
579: prep.setByte(2, (byte) -20);
580: prep.executeUpdate();
581: prep.setInt(1, 15);
582: prep.setByte(2, (byte) 100);
583: prep.executeUpdate();
584: prep.setInt(1, 16);
585: prep.setShort(2, (short) 30000);
586: prep.executeUpdate();
587: prep.setInt(1, 17);
588: prep.setShort(2, (short) (-30000));
589: prep.executeUpdate();
590: prep.setInt(1, 18);
591: prep.setLong(2, Integer.MAX_VALUE);
592: prep.executeUpdate();
593: prep.setInt(1, 19);
594: prep.setLong(2, Integer.MIN_VALUE);
595: prep.executeUpdate();
596:
597: check(stat.execute("SELECT * FROM T_INT ORDER BY ID"));
598: rs = stat.getResultSet();
599: testResultSetOrdered(rs, new String[][] { { "1", "0" },
600: { "2", "-1" }, { "3", "3" }, { "4", null },
601: { "5", "0" }, { "6", "-1" }, { "7", "3" },
602: { "8", null }, { "9", "-4" }, { "10", "5" },
603: { "11", null }, { "12", "1" }, { "13", "0" },
604: { "14", "-20" }, { "15", "100" }, { "16", "30000" },
605: { "17", "-30000" }, { "18", "" + Integer.MAX_VALUE },
606: { "19", "" + Integer.MIN_VALUE }, });
607:
608: prep = conn
609: .prepareStatement("INSERT INTO T_DECIMAL_0 VALUES(?,?)");
610: prep.setInt(1, 1);
611: prep.setLong(2, Long.MAX_VALUE);
612: prep.executeUpdate();
613: prep.setInt(1, 2);
614: prep.setLong(2, Long.MIN_VALUE);
615: prep.executeUpdate();
616: prep.setInt(1, 3);
617: prep.setFloat(2, 10);
618: prep.executeUpdate();
619: prep.setInt(1, 4);
620: prep.setFloat(2, -20);
621: prep.executeUpdate();
622: prep.setInt(1, 5);
623: prep.setFloat(2, 30);
624: prep.executeUpdate();
625: prep.setInt(1, 6);
626: prep.setFloat(2, -40);
627: prep.executeUpdate();
628:
629: rs = stat
630: .executeQuery("SELECT VALUE FROM T_DECIMAL_0 ORDER BY ID");
631: checkBigDecimal(rs, new String[] { "" + Long.MAX_VALUE,
632: "" + Long.MIN_VALUE, "10", "-20", "30", "-40" });
633:
634: // getMoreResults
635: stat.execute("CREATE TABLE TEST(ID INT)");
636: stat.execute("INSERT INTO TEST VALUES(1)");
637: prep = conn.prepareStatement("SELECT * FROM TEST");
638: // just to check if it doesn't throw an exception - it may be null
639: prep.getMetaData();
640: check(prep.execute());
641: rs = prep.getResultSet();
642: checkFalse(prep.getMoreResults());
643: try {
644: // supposed to be closed now
645: rs.next();
646: error("getMoreResults didn't close this result set");
647: } catch (SQLException e) {
648: trace("no error - getMoreResults is supposed to close the result set");
649: }
650: check(prep.getUpdateCount() == -1);
651: prep = conn.prepareStatement("DELETE FROM TEST");
652: prep.executeUpdate();
653: checkFalse(prep.getMoreResults());
654: check(prep.getUpdateCount() == -1);
655: }
656:
657: private void testObject(Connection conn) throws Exception {
658: Statement stat = conn.createStatement();
659: ResultSet rs;
660: stat
661: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
662: stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
663: PreparedStatement prep = conn
664: .prepareStatement("SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? FROM TEST");
665: prep.setObject(1, new Boolean(true));
666: prep.setObject(2, "Abc");
667: prep.setObject(3, new BigDecimal("10.2"));
668: prep.setObject(4, new Byte((byte) 0xff));
669: prep.setObject(5, new Short(Short.MAX_VALUE));
670: prep.setObject(6, new Integer(Integer.MIN_VALUE));
671: prep.setObject(7, new Long(Long.MAX_VALUE));
672: prep.setObject(8, new Float(Float.MAX_VALUE));
673: prep.setObject(9, new Double(Double.MAX_VALUE));
674: prep.setObject(10, java.sql.Date.valueOf("2001-02-03"));
675: prep.setObject(11, java.sql.Time.valueOf("04:05:06"));
676: prep.setObject(12, java.sql.Timestamp
677: .valueOf("2001-02-03 04:05:06.123456789"));
678: prep.setObject(13, new java.util.Date(java.sql.Date.valueOf(
679: "2001-02-03").getTime()));
680: prep.setObject(14, new byte[] { 10, 20, 30 });
681: prep.setObject(15, new Character('a'));
682: prep.setObject(16, "2001-01-02", Types.DATE);
683: // converting to null seems strange...
684: prep.setObject(17, "2001-01-02", Types.NULL);
685: prep.setObject(18, "3.725", Types.DOUBLE);
686: prep.setObject(19, "23:22:21", Types.TIME);
687: prep.setObject(20, new java.math.BigInteger("12345"),
688: Types.OTHER);
689: rs = prep.executeQuery();
690: rs.next();
691: check(rs.getObject(1).equals(new Boolean(true)));
692: check(rs.getObject(2).equals("Abc"));
693: check(rs.getObject(3).equals(new BigDecimal("10.2")));
694: check(rs.getObject(4).equals(new Byte((byte) 0xff)));
695: check(rs.getObject(5).equals(new Short(Short.MAX_VALUE)));
696: check(rs.getObject(6).equals(new Integer(Integer.MIN_VALUE)));
697: check(rs.getObject(7).equals(new Long(Long.MAX_VALUE)));
698: check(rs.getObject(8).equals(new Float(Float.MAX_VALUE)));
699: check(rs.getObject(9).equals(new Double(Double.MAX_VALUE)));
700: check(rs.getObject(10).equals(
701: java.sql.Date.valueOf("2001-02-03")));
702: check(rs.getObject(11).toString(), "04:05:06");
703: check(rs.getObject(11)
704: .equals(java.sql.Time.valueOf("04:05:06")));
705: check(rs.getObject(12).equals(
706: java.sql.Timestamp
707: .valueOf("2001-02-03 04:05:06.123456789")));
708: check(rs.getObject(13).equals(
709: java.sql.Timestamp.valueOf("2001-02-03 00:00:00")));
710: check((byte[]) rs.getObject(14), new byte[] { 10, 20, 30 });
711: check(rs.getObject(15).equals(new Character('a')));
712: check(rs.getObject(16).equals(
713: java.sql.Date.valueOf("2001-01-02")));
714: check(rs.getObject(17) == null && rs.wasNull());
715: check(rs.getObject(18).equals(new Double(3.725)));
716: check(rs.getObject(19)
717: .equals(java.sql.Time.valueOf("23:22:21")));
718: check(rs.getObject(20)
719: .equals(new java.math.BigInteger("12345")));
720:
721: // } else if(x instanceof java.io.Reader) {
722: // return session.createLob(Value.CLOB,
723: // TypeConverter.getInputStream((java.io.Reader)x), 0);
724: // } else if(x instanceof java.io.InputStream) {
725: // return session.createLob(Value.BLOB, (java.io.InputStream)x, 0);
726: // } else {
727: // return ValueBytes.get(TypeConverter.serialize(x));
728:
729: stat.execute("DROP TABLE TEST");
730:
731: }
732:
733: private void testIdentity(Connection conn) throws Exception {
734: Statement stat = conn.createStatement();
735: stat.execute("CREATE SEQUENCE SEQ");
736: stat.execute("CREATE TABLE TEST(ID INT)");
737: PreparedStatement prep;
738: prep = conn
739: .prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)");
740: prep.execute();
741: ResultSet rs = prep.getGeneratedKeys();
742: rs.next();
743: check(rs.getInt(1), 1);
744: checkFalse(rs.next());
745: prep = conn.prepareStatement(
746: "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
747: Statement.RETURN_GENERATED_KEYS);
748: prep.execute();
749: rs = prep.getGeneratedKeys();
750: rs.next();
751: check(rs.getInt(1), 2);
752: checkFalse(rs.next());
753: prep = conn.prepareStatement(
754: "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
755: new int[] { 1 });
756: prep.execute();
757: rs = prep.getGeneratedKeys();
758: rs.next();
759: check(rs.getInt(1), 3);
760: checkFalse(rs.next());
761: prep = conn.prepareStatement(
762: "INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
763: new String[] { "ID" });
764: prep.execute();
765: rs = prep.getGeneratedKeys();
766: rs.next();
767: check(rs.getInt(1), 4);
768: checkFalse(rs.next());
769: stat.execute("DROP TABLE TEST");
770: }
771:
772: int getLength() throws Exception {
773: return getSize(LOB_SIZE, LOB_SIZE_BIG);
774: }
775:
776: void testBlob(Connection conn) throws Exception {
777: trace("testBlob");
778: Statement stat = conn.createStatement();
779: PreparedStatement prep;
780: ResultSet rs;
781: stat
782: .execute("CREATE TABLE T_BLOB(ID INT PRIMARY KEY,V1 BLOB,V2 BLOB)");
783: trace("table created");
784: prep = conn
785: .prepareStatement("INSERT INTO T_BLOB VALUES(?,?,?)");
786:
787: prep.setInt(1, 1);
788: prep.setBytes(2, null);
789: prep.setNull(3, Types.BINARY);
790: prep.executeUpdate();
791:
792: prep.setInt(1, 2);
793: prep.setBinaryStream(2, null, 0);
794: prep.setNull(3, Types.BLOB);
795: prep.executeUpdate();
796:
797: int length = getLength();
798: byte[] big1 = new byte[length];
799: byte[] big2 = new byte[length];
800: for (int i = 0; i < big1.length; i++) {
801: big1[i] = (byte) ((i * 11) % 254);
802: big2[i] = (byte) ((i * 17) % 251);
803: }
804:
805: prep.setInt(1, 3);
806: prep.setBytes(2, big1);
807: prep.setBytes(3, big2);
808: prep.executeUpdate();
809:
810: prep.setInt(1, 4);
811: ByteArrayInputStream buffer;
812: buffer = new ByteArrayInputStream(big2);
813: prep.setBinaryStream(2, buffer, big2.length);
814: buffer = new ByteArrayInputStream(big1);
815: prep.setBinaryStream(3, buffer, big1.length);
816: prep.executeUpdate();
817: try {
818: buffer.close();
819: trace("buffer not closed");
820: } catch (IOException e) {
821: trace("buffer closed");
822: }
823:
824: prep.setInt(1, 5);
825: buffer = new ByteArrayInputStream(big2);
826: prep.setObject(2, buffer, Types.BLOB, 0);
827: buffer = new ByteArrayInputStream(big1);
828: prep.setObject(3, buffer);
829: prep.executeUpdate();
830:
831: rs = stat
832: .executeQuery("SELECT ID, V1, V2 FROM T_BLOB ORDER BY ID");
833:
834: rs.next();
835: check(rs.getInt(1), 1);
836: check(rs.getBytes(2) == null && rs.wasNull());
837: check(rs.getBytes(3) == null && rs.wasNull());
838:
839: rs.next();
840: check(rs.getInt(1), 2);
841: check(rs.getBytes(2) == null && rs.wasNull());
842: check(rs.getBytes(3) == null && rs.wasNull());
843:
844: rs.next();
845: check(rs.getInt(1), 3);
846: check(rs.getBytes(2), big1);
847: check(rs.getBytes(3), big2);
848:
849: rs.next();
850: check(rs.getInt(1), 4);
851: check(rs.getBytes(2), big2);
852: check(rs.getBytes(3), big1);
853:
854: rs.next();
855: check(rs.getInt(1), 5);
856: check(rs.getBytes(2), big2);
857: check(rs.getBytes(3), big1);
858:
859: checkFalse(rs.next());
860: }
861:
862: void testClob(Connection conn) throws Exception {
863: trace("testClob");
864: Statement stat = conn.createStatement();
865: PreparedStatement prep;
866: ResultSet rs;
867: stat
868: .execute("CREATE TABLE T_CLOB(ID INT PRIMARY KEY,V1 CLOB,V2 CLOB)");
869: StringBuffer asciiBuffer = new StringBuffer();
870: int len = getLength();
871: for (int i = 0; i < len; i++) {
872: asciiBuffer.append((char) ('a' + (i % 20)));
873: }
874: String ascii1 = asciiBuffer.toString();
875: String ascii2 = "Number2 " + ascii1;
876: prep = conn
877: .prepareStatement("INSERT INTO T_CLOB VALUES(?,?,?)");
878:
879: prep.setInt(1, 1);
880: prep.setString(2, null);
881: prep.setNull(3, Types.CLOB);
882: prep.executeUpdate();
883:
884: prep.clearParameters();
885: prep.setInt(1, 2);
886: prep.setAsciiStream(2, null, 0);
887: prep.setCharacterStream(3, null, 0);
888: prep.executeUpdate();
889:
890: prep.clearParameters();
891: prep.setInt(1, 3);
892: prep.setCharacterStream(2, new StringReader(ascii1), ascii1
893: .length());
894: prep.setCharacterStream(3, null, 0);
895: prep.setAsciiStream(3, new ByteArrayInputStream(ascii2
896: .getBytes()), ascii2.length());
897: prep.executeUpdate();
898:
899: prep.clearParameters();
900: prep.setInt(1, 4);
901: prep.setNull(2, Types.CLOB);
902: prep.setString(2, ascii2);
903: prep.setCharacterStream(3, null, 0);
904: prep.setNull(3, Types.CLOB);
905: prep.setString(3, ascii1);
906: prep.executeUpdate();
907:
908: prep.clearParameters();
909: prep.setInt(1, 5);
910: prep.setObject(2, new StringReader(ascii1));
911: prep.setObject(3, new StringReader(ascii2), Types.CLOB, 0);
912: prep.executeUpdate();
913:
914: rs = stat
915: .executeQuery("SELECT ID, V1, V2 FROM T_CLOB ORDER BY ID");
916:
917: rs.next();
918: check(rs.getInt(1), 1);
919: check(rs.getCharacterStream(2) == null && rs.wasNull());
920: check(rs.getAsciiStream(3) == null && rs.wasNull());
921:
922: rs.next();
923: check(rs.getInt(1), 2);
924: check(rs.getString(2) == null && rs.wasNull());
925: check(rs.getString(3) == null && rs.wasNull());
926:
927: rs.next();
928: check(rs.getInt(1), 3);
929: check(rs.getString(2), ascii1);
930: check(rs.getString(3), ascii2);
931:
932: rs.next();
933: check(rs.getInt(1), 4);
934: check(rs.getString(2), ascii2);
935: check(rs.getString(3), ascii1);
936:
937: rs.next();
938: check(rs.getInt(1), 5);
939: check(rs.getString(2), ascii1);
940: check(rs.getString(3), ascii2);
941:
942: checkFalse(rs.next());
943: check(prep.getWarnings() == null);
944: prep.clearWarnings();
945: check(prep.getWarnings() == null);
946: check(conn == prep.getConnection());
947: }
948:
949: void checkBigDecimal(ResultSet rs, String[] value) throws Exception {
950: for (int i = 0; i < value.length; i++) {
951: String v = value[i];
952: check(rs.next());
953: java.math.BigDecimal x = rs.getBigDecimal(1);
954: trace("v=" + v + " x=" + x);
955: if (v == null) {
956: check(x == null);
957: } else {
958: check(x.compareTo(new java.math.BigDecimal(v)) == 0);
959: }
960: }
961: check(!rs.next());
962: }
963:
964: }
|