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.ByteArrayOutputStream;
009: import java.io.IOException;
010: import java.io.InputStream;
011: import java.io.InputStreamReader;
012: import java.math.BigDecimal;
013: import java.sql.Array;
014: import java.sql.Connection;
015: import java.sql.Date;
016: import java.sql.PreparedStatement;
017: import java.sql.ResultSet;
018: import java.sql.ResultSetMetaData;
019: import java.sql.SQLException;
020: import java.sql.Statement;
021: import java.sql.Time;
022: import java.sql.Timestamp;
023: import java.sql.Types;
024: import java.util.Calendar;
025: import java.util.TimeZone;
026:
027: import org.h2.test.TestBase;
028:
029: /**
030: * Tests for the ResultSet implementation.
031: */
032: public class TestResultSet extends TestBase {
033: Connection conn;
034: Statement stat;
035:
036: public void test() throws Exception {
037: deleteDb("resultSet");
038: conn = getConnection("resultSet");
039:
040: stat = conn.createStatement();
041:
042: testFindColumn();
043:
044: testColumnLength();
045: testArray();
046: testLimitMaxRows();
047:
048: trace("max rows=" + stat.getMaxRows());
049: stat.setMaxRows(6);
050: trace("max rows after set to 6=" + stat.getMaxRows());
051: check(stat.getMaxRows() == 6);
052:
053: testInt();
054: testVarchar();
055: testDecimal();
056: testDoubleFloat();
057: testDatetime();
058: testDatetimeWithCalendar();
059: testBlob();
060: testClob();
061: testAutoIncrement();
062:
063: conn.close();
064:
065: }
066:
067: private void testFindColumn() throws Exception {
068: trace("testFindColumn");
069: ResultSet rs;
070: stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
071: rs = stat.executeQuery("SELECT * FROM TEST");
072: check(rs.findColumn("ID"), 1);
073: check(rs.findColumn("NAME"), 2);
074: check(rs.findColumn("id"), 1);
075: check(rs.findColumn("name"), 2);
076: check(rs.findColumn("Id"), 1);
077: check(rs.findColumn("Name"), 2);
078: check(rs.findColumn("TEST.ID"), 1);
079: check(rs.findColumn("TEST.NAME"), 2);
080: check(rs.findColumn("Test.Id"), 1);
081: check(rs.findColumn("Test.Name"), 2);
082: stat.execute("DROP TABLE TEST");
083:
084: stat
085: .execute("CREATE TABLE TEST(ID INT, NAME VARCHAR, DATA VARCHAR)");
086: rs = stat.executeQuery("SELECT * FROM TEST");
087: check(rs.findColumn("ID"), 1);
088: check(rs.findColumn("NAME"), 2);
089: check(rs.findColumn("DATA"), 3);
090: check(rs.findColumn("id"), 1);
091: check(rs.findColumn("name"), 2);
092: check(rs.findColumn("data"), 3);
093: check(rs.findColumn("Id"), 1);
094: check(rs.findColumn("Name"), 2);
095: check(rs.findColumn("Data"), 3);
096: check(rs.findColumn("TEST.ID"), 1);
097: check(rs.findColumn("TEST.NAME"), 2);
098: check(rs.findColumn("TEST.DATA"), 3);
099: check(rs.findColumn("Test.Id"), 1);
100: check(rs.findColumn("Test.Name"), 2);
101: check(rs.findColumn("Test.Data"), 3);
102: stat.execute("DROP TABLE TEST");
103:
104: }
105:
106: private void testColumnLength() throws Exception {
107: trace("testColumnDisplayLength");
108: ResultSet rs;
109: ResultSetMetaData meta;
110:
111: stat.execute("CREATE TABLE one (ID INT, NAME VARCHAR(255))");
112: rs = stat.executeQuery("select * from one");
113: meta = rs.getMetaData();
114: check("ID", meta.getColumnLabel(1));
115: check(11, meta.getColumnDisplaySize(1));
116: check("NAME", meta.getColumnLabel(2));
117: check(255, meta.getColumnDisplaySize(2));
118: stat.execute("DROP TABLE one");
119:
120: rs = stat
121: .executeQuery("select 1, 'Hello' union select 2, 'Hello World!'");
122: meta = rs.getMetaData();
123: check(11, meta.getColumnDisplaySize(1));
124: check(12, meta.getColumnDisplaySize(2));
125:
126: rs = stat.executeQuery("explain select * from dual");
127: meta = rs.getMetaData();
128: check(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
129: check(Integer.MAX_VALUE, meta.getPrecision(1));
130:
131: rs = stat.executeQuery("script");
132: meta = rs.getMetaData();
133: check(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
134: check(Integer.MAX_VALUE, meta.getPrecision(1));
135:
136: rs = stat
137: .executeQuery("select group_concat(table_name) from information_schema.tables");
138: rs.next();
139: meta = rs.getMetaData();
140: check(Integer.MAX_VALUE, meta.getColumnDisplaySize(1));
141: check(Integer.MAX_VALUE, meta.getPrecision(1));
142:
143: }
144:
145: private void testLimitMaxRows() throws Exception {
146: trace("Test LimitMaxRows");
147: ResultSet rs;
148: stat.execute("CREATE TABLE one (C CHARACTER(10))");
149: rs = stat.executeQuery("SELECT C || C FROM one;");
150: ResultSetMetaData md = rs.getMetaData();
151: check(20, md.getPrecision(1));
152: ResultSet rs2 = stat
153: .executeQuery("SELECT UPPER (C) FROM one;");
154: ResultSetMetaData md2 = rs2.getMetaData();
155: check(10, md2.getPrecision(1));
156: rs = stat
157: .executeQuery("SELECT UPPER (C), CHAR(10), CONCAT(C,C,C), HEXTORAW(C), RAWTOHEX(C) FROM one");
158: ResultSetMetaData meta = rs.getMetaData();
159: check(10, meta.getPrecision(1));
160: check(1, meta.getPrecision(2));
161: check(30, meta.getPrecision(3));
162: check(3, meta.getPrecision(4));
163: check(40, meta.getPrecision(5));
164: stat.execute("DROP TABLE one");
165: }
166:
167: void testAutoIncrement() throws Exception {
168: trace("Test AutoIncrement");
169: stat.execute("DROP TABLE IF EXISTS TEST");
170: ResultSet rs;
171: stat
172: .execute("CREATE TABLE TEST(ID IDENTITY NOT NULL, NAME VARCHAR NULL)");
173:
174: stat.execute("INSERT INTO TEST(NAME) VALUES('Hello')");
175: rs = stat.getGeneratedKeys();
176: check(rs.next());
177: check(rs.getInt(1), 1);
178:
179: stat.execute("INSERT INTO TEST(NAME) VALUES('World')");
180: rs = stat.getGeneratedKeys();
181: check(rs.next());
182: check(rs.getInt(1), 2);
183:
184: rs = stat
185: .executeQuery("SELECT ID AS I, NAME AS N, ID+1 AS IP1 FROM TEST");
186: ResultSetMetaData meta = rs.getMetaData();
187: check(meta.isAutoIncrement(1));
188: checkFalse(meta.isAutoIncrement(2));
189: checkFalse(meta.isAutoIncrement(3));
190: check(meta.isNullable(1), ResultSetMetaData.columnNoNulls);
191: check(meta.isNullable(2), ResultSetMetaData.columnNullable);
192: check(meta.isNullable(3),
193: ResultSetMetaData.columnNullableUnknown);
194: check(rs.next());
195: check(rs.next());
196: checkFalse(rs.next());
197:
198: }
199:
200: void testInt() throws Exception {
201: trace("Test INT");
202: ResultSet rs;
203: Object o;
204:
205: stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE INT)");
206: stat.execute("INSERT INTO TEST VALUES(1,-1)");
207: stat.execute("INSERT INTO TEST VALUES(2,0)");
208: stat.execute("INSERT INTO TEST VALUES(3,1)");
209: stat.execute("INSERT INTO TEST VALUES(4," + Integer.MAX_VALUE
210: + ")");
211: stat.execute("INSERT INTO TEST VALUES(5," + Integer.MIN_VALUE
212: + ")");
213: stat.execute("INSERT INTO TEST VALUES(6,NULL)");
214: // this should not be read - maxrows=6
215: stat.execute("INSERT INTO TEST VALUES(7,NULL)");
216:
217: // MySQL compatibility (is this required?)
218: // rs=stat.executeQuery("SELECT * FROM TEST T ORDER BY ID");
219: // check(rs.findColumn("T.ID"), 1);
220: // check(rs.findColumn("T.NAME"), 2);
221:
222: rs = stat
223: .executeQuery("SELECT *, NULL AS N FROM TEST ORDER BY ID");
224:
225: // MySQL compatibility
226: check(rs.findColumn("TEST.ID"), 1);
227: check(rs.findColumn("TEST.VALUE"), 2);
228:
229: ResultSetMetaData meta = rs.getMetaData();
230: check(meta.getColumnCount(), 3);
231: check(meta.getCatalogName(1), "resultSet".toUpperCase());
232: check("PUBLIC".equals(meta.getSchemaName(2)));
233: check("TEST".equals(meta.getTableName(1)));
234: check("ID".equals(meta.getColumnName(1)));
235: check("VALUE".equals(meta.getColumnName(2)));
236: check(!meta.isAutoIncrement(1));
237: check(meta.isCaseSensitive(1));
238: check(meta.isSearchable(1));
239: checkFalse(meta.isCurrency(1));
240: check(meta.getColumnDisplaySize(1) > 0);
241: check(meta.isSigned(1));
242: check(meta.isSearchable(2));
243: check(meta.isNullable(1), ResultSetMetaData.columnNoNulls);
244: checkFalse(meta.isReadOnly(1));
245: check(meta.isWritable(1));
246: checkFalse(meta.isDefinitelyWritable(1));
247: check(meta.getColumnDisplaySize(1) > 0);
248: check(meta.getColumnDisplaySize(2) > 0);
249: check(meta.getColumnClassName(3), null);
250:
251: check(rs.getRow() == 0);
252: testResultSetMeta(rs, 3, new String[] { "ID", "VALUE", "N" },
253: new int[] { Types.INTEGER, Types.INTEGER, Types.NULL },
254: new int[] { 10, 10, 1 }, new int[] { 0, 0, 0 });
255: rs.next();
256: check(rs.getConcurrency(), ResultSet.CONCUR_READ_ONLY);
257: check(rs.getFetchDirection(), ResultSet.FETCH_FORWARD);
258: trace("default fetch size=" + rs.getFetchSize());
259: // 0 should be an allowed value (but it's not defined what is actually
260: // means)
261: rs.setFetchSize(0);
262: trace("after set to 0, fetch size=" + rs.getFetchSize());
263: // this should break
264: try {
265: rs.setFetchSize(-1);
266: error("fetch size -1 is not allowed");
267: } catch (SQLException e) {
268: checkNotGeneralException(e);
269: trace(e.toString());
270: }
271: trace("after try to set to -1, fetch size=" + rs.getFetchSize());
272: try {
273: rs.setFetchSize(100);
274: error("fetch size 100 is bigger than maxrows - not allowed");
275: } catch (SQLException e) {
276: checkNotGeneralException(e);
277: trace(e.toString());
278: }
279: trace("after try set to 100, fetch size=" + rs.getFetchSize());
280: rs.setFetchSize(6);
281:
282: check(rs.getRow() == 1);
283: check(rs.findColumn("VALUE"), 2);
284: check(rs.findColumn("value"), 2);
285: check(rs.findColumn("Value"), 2);
286: check(rs.findColumn("Value"), 2);
287: check(rs.findColumn("ID"), 1);
288: check(rs.findColumn("id"), 1);
289: check(rs.findColumn("Id"), 1);
290: check(rs.findColumn("iD"), 1);
291: check(rs.getInt(2) == -1 && !rs.wasNull());
292: check(rs.getInt("VALUE") == -1 && !rs.wasNull());
293: check(rs.getInt("value") == -1 && !rs.wasNull());
294: check(rs.getInt("Value") == -1 && !rs.wasNull());
295: check(rs.getString("Value").equals("-1") && !rs.wasNull());
296:
297: o = rs.getObject("value");
298: trace(o.getClass().getName());
299: check(o instanceof Integer);
300: check(((Integer) o).intValue() == -1);
301: o = rs.getObject(2);
302: trace(o.getClass().getName());
303: check(o instanceof Integer);
304: check(((Integer) o).intValue() == -1);
305: check(rs.getBoolean("Value"));
306: check(rs.getByte("Value") == (byte) -1);
307: check(rs.getShort("Value") == (short) -1);
308: check(rs.getLong("Value") == -1);
309: check(rs.getFloat("Value") == -1.0);
310: check(rs.getDouble("Value") == -1.0);
311:
312: check(rs.getString("Value").equals("-1") && !rs.wasNull());
313: check(rs.getInt("ID") == 1 && !rs.wasNull());
314: check(rs.getInt("id") == 1 && !rs.wasNull());
315: check(rs.getInt("Id") == 1 && !rs.wasNull());
316: check(rs.getInt(1) == 1 && !rs.wasNull());
317: rs.next();
318: check(rs.getRow() == 2);
319: check(rs.getInt(2) == 0 && !rs.wasNull());
320: check(!rs.getBoolean(2));
321: check(rs.getByte(2) == 0);
322: check(rs.getShort(2) == 0);
323: check(rs.getLong(2) == 0);
324: check(rs.getFloat(2) == 0.0);
325: check(rs.getDouble(2) == 0.0);
326: check(rs.getString(2).equals("0") && !rs.wasNull());
327: check(rs.getInt(1) == 2 && !rs.wasNull());
328: rs.next();
329: check(rs.getRow() == 3);
330: check(rs.getInt("ID") == 3 && !rs.wasNull());
331: check(rs.getInt("VALUE") == 1 && !rs.wasNull());
332: rs.next();
333: check(rs.getRow() == 4);
334: check(rs.getInt("ID") == 4 && !rs.wasNull());
335: check(rs.getInt("VALUE") == Integer.MAX_VALUE && !rs.wasNull());
336: rs.next();
337: check(rs.getRow() == 5);
338: check(rs.getInt("id") == 5 && !rs.wasNull());
339: check(rs.getInt("value") == Integer.MIN_VALUE && !rs.wasNull());
340: check(rs.getString(1).equals("5") && !rs.wasNull());
341: rs.next();
342: check(rs.getRow() == 6);
343: check(rs.getInt("id") == 6 && !rs.wasNull());
344: check(rs.getInt("value") == 0 && rs.wasNull());
345: check(rs.getInt(2) == 0 && rs.wasNull());
346: check(rs.getInt(1) == 6 && !rs.wasNull());
347: check(rs.getString(1).equals("6") && !rs.wasNull());
348: check(rs.getString(2) == null && rs.wasNull());
349: o = rs.getObject(2);
350: check(o == null);
351: check(rs.wasNull());
352: checkFalse(rs.next());
353: check(rs.getRow(), 0);
354: // there is one more row, but because of setMaxRows we don't get it
355:
356: stat.execute("DROP TABLE TEST");
357: stat.setMaxRows(0);
358: }
359:
360: void testVarchar() throws Exception {
361: trace("Test VARCHAR");
362: ResultSet rs;
363: Object o;
364:
365: stat
366: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
367: stat.execute("INSERT INTO TEST VALUES(1,'')");
368: stat.execute("INSERT INTO TEST VALUES(2,' ')");
369: stat.execute("INSERT INTO TEST VALUES(3,' ')");
370: stat.execute("INSERT INTO TEST VALUES(4,NULL)");
371: stat.execute("INSERT INTO TEST VALUES(5,'Hi')");
372: stat.execute("INSERT INTO TEST VALUES(6,' Hi ')");
373: stat.execute("INSERT INTO TEST VALUES(7,'Joe''s')");
374: stat.execute("INSERT INTO TEST VALUES(8,'{escape}')");
375: stat.execute("INSERT INTO TEST VALUES(9,'\\n')");
376: stat.execute("INSERT INTO TEST VALUES(10,'\\''')");
377: stat.execute("INSERT INTO TEST VALUES(11,'\\%')");
378: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
379: testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" },
380: new int[] { Types.INTEGER, Types.VARCHAR }, new int[] {
381: 10, 255 }, new int[] { 0, 0 });
382: String value;
383: rs.next();
384: value = rs.getString(2);
385: trace("Value: <" + value + "> (should be: <>)");
386: check(value != null && value.equals("") && !rs.wasNull());
387: check(rs.getInt(1) == 1 && !rs.wasNull());
388: rs.next();
389: value = rs.getString(2);
390: trace("Value: <" + value + "> (should be: < >)");
391: check(rs.getString(2).equals(" ") && !rs.wasNull());
392: check(rs.getInt(1) == 2 && !rs.wasNull());
393: rs.next();
394: value = rs.getString(2);
395: trace("Value: <" + value + "> (should be: < >)");
396: check(rs.getString(2).equals(" ") && !rs.wasNull());
397: check(rs.getInt(1) == 3 && !rs.wasNull());
398: rs.next();
399: value = rs.getString(2);
400: trace("Value: <" + value + "> (should be: <null>)");
401: check(rs.getString(2) == null && rs.wasNull());
402: check(rs.getInt(1) == 4 && !rs.wasNull());
403: rs.next();
404: value = rs.getString(2);
405: trace("Value: <" + value + "> (should be: <Hi>)");
406: check(rs.getInt(1) == 5 && !rs.wasNull());
407: check(rs.getString(2).equals("Hi") && !rs.wasNull());
408: o = rs.getObject("value");
409: trace(o.getClass().getName());
410: check(o instanceof String);
411: check(o.toString().equals("Hi"));
412: rs.next();
413: value = rs.getString(2);
414: trace("Value: <" + value + "> (should be: < Hi >)");
415: check(rs.getInt(1) == 6 && !rs.wasNull());
416: check(rs.getString(2).equals(" Hi ") && !rs.wasNull());
417: rs.next();
418: value = rs.getString(2);
419: trace("Value: <" + value + "> (should be: <Joe's>)");
420: check(rs.getInt(1) == 7 && !rs.wasNull());
421: check(rs.getString(2).equals("Joe's") && !rs.wasNull());
422: rs.next();
423: value = rs.getString(2);
424: trace("Value: <" + value + "> (should be: <{escape}>)");
425: check(rs.getInt(1) == 8 && !rs.wasNull());
426: check(rs.getString(2).equals("{escape}") && !rs.wasNull());
427: rs.next();
428: value = rs.getString(2);
429: trace("Value: <" + value + "> (should be: <\\n>)");
430: check(rs.getInt(1) == 9 && !rs.wasNull());
431: check(rs.getString(2).equals("\\n") && !rs.wasNull());
432: rs.next();
433: value = rs.getString(2);
434: trace("Value: <" + value + "> (should be: <\\'>)");
435: check(rs.getInt(1) == 10 && !rs.wasNull());
436: check(rs.getString(2).equals("\\'") && !rs.wasNull());
437: rs.next();
438: value = rs.getString(2);
439: trace("Value: <" + value + "> (should be: <\\%>)");
440: check(rs.getInt(1) == 11 && !rs.wasNull());
441: check(rs.getString(2).equals("\\%") && !rs.wasNull());
442: check(!rs.next());
443: stat.execute("DROP TABLE TEST");
444: }
445:
446: void testDecimal() throws Exception {
447: trace("Test DECIMAL");
448: ResultSet rs;
449: Object o;
450:
451: stat
452: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE DECIMAL(10,2))");
453: stat.execute("INSERT INTO TEST VALUES(1,-1)");
454: stat.execute("INSERT INTO TEST VALUES(2,.0)");
455: stat.execute("INSERT INTO TEST VALUES(3,1.)");
456: stat.execute("INSERT INTO TEST VALUES(4,12345678.89)");
457: stat.execute("INSERT INTO TEST VALUES(6,99999998.99)");
458: stat.execute("INSERT INTO TEST VALUES(7,-99999998.99)");
459: stat.execute("INSERT INTO TEST VALUES(8,NULL)");
460: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
461: testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" },
462: new int[] { Types.INTEGER, Types.DECIMAL }, new int[] {
463: 10, 10 }, new int[] { 0, 2 });
464: BigDecimal bd;
465: rs.next();
466: check(rs.getInt(1) == 1);
467: check(!rs.wasNull());
468: check(rs.getInt(2) == -1);
469: check(!rs.wasNull());
470: bd = rs.getBigDecimal(2);
471: check(bd.compareTo(new BigDecimal("-1.00")) == 0);
472: check(!rs.wasNull());
473: o = rs.getObject(2);
474: trace(o.getClass().getName());
475: check(o instanceof BigDecimal);
476: check(((BigDecimal) o).compareTo(new BigDecimal("-1.00")) == 0);
477: rs.next();
478: check(rs.getInt(1) == 2);
479: check(!rs.wasNull());
480: check(rs.getInt(2) == 0);
481: check(!rs.wasNull());
482: bd = rs.getBigDecimal(2);
483: check(bd.compareTo(new BigDecimal("0.00")) == 0);
484: check(!rs.wasNull());
485: rs.next();
486: checkColumnBigDecimal(rs, 2, 1, "1.00");
487: rs.next();
488: checkColumnBigDecimal(rs, 2, 12345679, "12345678.89");
489: rs.next();
490: checkColumnBigDecimal(rs, 2, 99999999, "99999998.99");
491: rs.next();
492: checkColumnBigDecimal(rs, 2, -99999999, "-99999998.99");
493: rs.next();
494: checkColumnBigDecimal(rs, 2, 0, null);
495: check(!rs.next());
496: stat.execute("DROP TABLE TEST");
497: }
498:
499: void testDoubleFloat() throws Exception {
500: trace("Test DOUBLE - FLOAT");
501: ResultSet rs;
502: Object o;
503:
504: stat
505: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, D DOUBLE, R REAL)");
506: stat.execute("INSERT INTO TEST VALUES(1, -1, -1)");
507: stat.execute("INSERT INTO TEST VALUES(2,.0, .0)");
508: stat.execute("INSERT INTO TEST VALUES(3, 1., 1.)");
509: stat
510: .execute("INSERT INTO TEST VALUES(4, 12345678.89, 12345678.89)");
511: stat
512: .execute("INSERT INTO TEST VALUES(6, 99999999.99, 99999999.99)");
513: stat
514: .execute("INSERT INTO TEST VALUES(7, -99999999.99, -99999999.99)");
515: stat.execute("INSERT INTO TEST VALUES(8, NULL, NULL)");
516: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
517: testResultSetMeta(rs, 3, new String[] { "ID", "D", "R" },
518: new int[] { Types.INTEGER, Types.DOUBLE, Types.REAL },
519: new int[] { 10, 17, 7 }, new int[] { 0, 0, 0 });
520: BigDecimal bd;
521: rs.next();
522: check(rs.getInt(1) == 1);
523: check(!rs.wasNull());
524: check(rs.getInt(2) == -1);
525: check(rs.getInt(3) == -1);
526: check(!rs.wasNull());
527: bd = rs.getBigDecimal(2);
528: check(bd.compareTo(new BigDecimal("-1.00")) == 0);
529: check(!rs.wasNull());
530: o = rs.getObject(2);
531: trace(o.getClass().getName());
532: check(o instanceof Double);
533: check(((Double) o).compareTo(new Double("-1.00")) == 0);
534: o = rs.getObject(3);
535: trace(o.getClass().getName());
536: check(o instanceof Float);
537: check(((Float) o).compareTo(new Float("-1.00")) == 0);
538: rs.next();
539: check(rs.getInt(1) == 2);
540: check(!rs.wasNull());
541: check(rs.getInt(2) == 0);
542: check(!rs.wasNull());
543: check(rs.getInt(3) == 0);
544: check(!rs.wasNull());
545: bd = rs.getBigDecimal(2);
546: check(bd.compareTo(new BigDecimal("0.00")) == 0);
547: check(!rs.wasNull());
548: bd = rs.getBigDecimal(3);
549: check(bd.compareTo(new BigDecimal("0.00")) == 0);
550: check(!rs.wasNull());
551: rs.next();
552: check(rs.getDouble(2), 1.0);
553: check(rs.getFloat(3), 1.0f);
554: rs.next();
555: check(rs.getDouble(2), 12345678.89);
556: check(rs.getFloat(3), 12345678.89f);
557: rs.next();
558: check(rs.getDouble(2), 99999999.99);
559: check(rs.getFloat(3), 99999999.99f);
560: rs.next();
561: check(rs.getDouble(2), -99999999.99);
562: check(rs.getFloat(3), -99999999.99f);
563: rs.next();
564: checkColumnBigDecimal(rs, 2, 0, null);
565: checkColumnBigDecimal(rs, 3, 0, null);
566: check(!rs.next());
567: stat.execute("DROP TABLE TEST");
568: }
569:
570: void testDatetime() throws Exception {
571: trace("Test DATETIME");
572: ResultSet rs;
573: Object o;
574:
575: stat
576: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE DATETIME)");
577: stat.execute("INSERT INTO TEST VALUES(1,DATE '2011-11-11')");
578: stat
579: .execute("INSERT INTO TEST VALUES(2,TIMESTAMP '2002-02-02 02:02:02')");
580: stat
581: .execute("INSERT INTO TEST VALUES(3,TIMESTAMP '1800-1-1 0:0:0')");
582: stat
583: .execute("INSERT INTO TEST VALUES(4,TIMESTAMP '9999-12-31 23:59:59')");
584: stat.execute("INSERT INTO TEST VALUES(5,NULL)");
585: rs = stat
586: .executeQuery("SELECT 0 ID, TIMESTAMP '9999-12-31 23:59:59' VALUE FROM TEST ORDER BY ID");
587: testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" },
588: new int[] { Types.INTEGER, Types.TIMESTAMP },
589: new int[] { 10, 23 }, new int[] { 0, 10 });
590: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
591: testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" },
592: new int[] { Types.INTEGER, Types.TIMESTAMP },
593: new int[] { 10, 23 }, new int[] { 0, 10 });
594: rs.next();
595: java.sql.Date date;
596: java.sql.Time time;
597: java.sql.Timestamp ts;
598: date = rs.getDate(2);
599: check(!rs.wasNull());
600: time = rs.getTime(2);
601: check(!rs.wasNull());
602: ts = rs.getTimestamp(2);
603: check(!rs.wasNull());
604: trace("Date: " + date.toString() + " Time:" + time.toString()
605: + " Timestamp:" + ts.toString());
606: trace("Date ms: " + date.getTime() + " Time ms:"
607: + time.getTime() + " Timestamp ms:" + ts.getTime());
608: trace("1970 ms: "
609: + java.sql.Timestamp.valueOf("1970-01-01 00:00:00.0")
610: .getTime());
611: check(date.getTime(), java.sql.Timestamp.valueOf(
612: "2011-11-11 00:00:00.0").getTime());
613: check(time.getTime(), java.sql.Timestamp.valueOf(
614: "1970-01-01 00:00:00.0").getTime());
615: check(ts.getTime(), java.sql.Timestamp.valueOf(
616: "2011-11-11 00:00:00.0").getTime());
617: check(date.equals(java.sql.Timestamp
618: .valueOf("2011-11-11 00:00:00.0")));
619: check(time.equals(java.sql.Timestamp
620: .valueOf("1970-01-01 00:00:00.0")));
621: check(ts.equals(java.sql.Timestamp
622: .valueOf("2011-11-11 00:00:00.0")));
623: checkFalse(rs.wasNull());
624: o = rs.getObject(2);
625: trace(o.getClass().getName());
626: check(o instanceof java.sql.Timestamp);
627: check(((java.sql.Timestamp) o).equals(java.sql.Timestamp
628: .valueOf("2011-11-11 00:00:00.0")));
629: checkFalse(rs.wasNull());
630: rs.next();
631:
632: date = rs.getDate("VALUE");
633: check(!rs.wasNull());
634: time = rs.getTime("VALUE");
635: check(!rs.wasNull());
636: ts = rs.getTimestamp("VALUE");
637: check(!rs.wasNull());
638: trace("Date: " + date.toString() + " Time:" + time.toString()
639: + " Timestamp:" + ts.toString());
640: check(date.toString(), "2002-02-02");
641: check(time.toString(), "02:02:02");
642: check(ts.toString(), "2002-02-02 02:02:02.0");
643: rs.next();
644: check(rs.getDate("value").toString(), "1800-01-01");
645: check(rs.getTime("value").toString(), "00:00:00");
646: check(rs.getTimestamp("value").toString(),
647: "1800-01-01 00:00:00.0");
648: rs.next();
649: check(rs.getDate("Value").toString(), "9999-12-31");
650: check(rs.getTime("Value").toString(), "23:59:59");
651: check(rs.getTimestamp("Value").toString(),
652: "9999-12-31 23:59:59.0");
653: rs.next();
654: check(rs.getDate("Value") == null && rs.wasNull());
655: check(rs.getTime("vALUe") == null && rs.wasNull());
656: check(rs.getTimestamp(2) == null && rs.wasNull());
657: check(!rs.next());
658:
659: rs = stat
660: .executeQuery("SELECT DATE '2001-02-03' D, TIME '14:15:16', TIMESTAMP '2007-08-09 10:11:12.141516171' TS FROM TEST");
661: rs.next();
662: date = (Date) rs.getObject(1);
663: time = (Time) rs.getObject(2);
664: ts = (Timestamp) rs.getObject(3);
665: check(date.toString(), "2001-02-03");
666: check(time.toString(), "14:15:16");
667: check(ts.toString(), "2007-08-09 10:11:12.141516171");
668:
669: stat.execute("DROP TABLE TEST");
670: }
671:
672: void testDatetimeWithCalendar() throws Exception {
673: trace("Test DATETIME with Calendar");
674: ResultSet rs;
675:
676: stat
677: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, D DATE, T TIME, TS TIMESTAMP)");
678: PreparedStatement prep = conn
679: .prepareStatement("INSERT INTO TEST VALUES(?, ?, ?, ?)");
680: Calendar regular = Calendar.getInstance();
681: Calendar other = null;
682: String[] timezones = TimeZone.getAvailableIDs();
683: // search a locale that has a _different_ raw offset
684: for (int i = 0; i < timezones.length; i++) {
685: TimeZone zone = TimeZone.getTimeZone(timezones[i]);
686: if (regular.getTimeZone().getRawOffset() != zone
687: .getRawOffset()) {
688: other = Calendar.getInstance(zone);
689: break;
690: }
691: }
692: trace("regular offset = "
693: + regular.getTimeZone().getRawOffset() + " other = "
694: + other.getTimeZone().getRawOffset());
695:
696: prep.setInt(1, 0);
697: prep.setDate(2, null, regular);
698: prep.setTime(3, null, regular);
699: prep.setTimestamp(4, null, regular);
700: prep.execute();
701:
702: prep.setInt(1, 1);
703: prep.setDate(2, null, other);
704: prep.setTime(3, null, other);
705: prep.setTimestamp(4, null, other);
706: prep.execute();
707:
708: prep.setInt(1, 2);
709: prep.setDate(2, java.sql.Date.valueOf("2001-02-03"), regular);
710: prep.setTime(3, java.sql.Time.valueOf("04:05:06"), regular);
711: prep.setTimestamp(4, java.sql.Timestamp
712: .valueOf("2007-08-09 10:11:12.131415"), regular);
713: prep.execute();
714:
715: prep.setInt(1, 3);
716: prep.setDate(2, java.sql.Date.valueOf("2101-02-03"), other);
717: prep.setTime(3, java.sql.Time.valueOf("14:05:06"), other);
718: prep.setTimestamp(4, java.sql.Timestamp
719: .valueOf("2107-08-09 10:11:12.131415"), other);
720: prep.execute();
721:
722: prep.setInt(1, 4);
723: prep.setDate(2, java.sql.Date.valueOf("2101-02-03"));
724: prep.setTime(3, java.sql.Time.valueOf("14:05:06"));
725: prep.setTimestamp(4, java.sql.Timestamp
726: .valueOf("2107-08-09 10:11:12.131415"));
727: prep.execute();
728:
729: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
730: testResultSetMeta(rs, 4, new String[] { "ID", "D", "T", "TS" },
731: new int[] { Types.INTEGER, Types.DATE, Types.TIME,
732: Types.TIMESTAMP }, new int[] { 10, 8, 6, 23 },
733: new int[] { 0, 0, 0, 10 });
734:
735: rs.next();
736: check(rs.getInt(1), 0);
737: check(rs.getDate(2, regular) == null && rs.wasNull());
738: check(rs.getTime(3, regular) == null && rs.wasNull());
739: check(rs.getTimestamp(3, regular) == null && rs.wasNull());
740:
741: rs.next();
742: check(rs.getInt(1), 1);
743: check(rs.getDate(2, other) == null && rs.wasNull());
744: check(rs.getTime(3, other) == null && rs.wasNull());
745: check(rs.getTimestamp(3, other) == null && rs.wasNull());
746:
747: rs.next();
748: check(rs.getInt(1), 2);
749: check(rs.getDate(2, regular).toString(), "2001-02-03");
750: check(rs.getTime(3, regular).toString(), "04:05:06");
751: checkFalse(rs.getTime(3, other).toString(), "04:05:06");
752: check(rs.getTimestamp(4, regular).toString(),
753: "2007-08-09 10:11:12.131415");
754: checkFalse(rs.getTimestamp(4, other).toString(),
755: "2007-08-09 10:11:12.131415");
756:
757: rs.next();
758: check(rs.getInt("ID"), 3);
759: checkFalse(rs.getTimestamp("TS", regular).toString(),
760: "2107-08-09 10:11:12.131415");
761: check(rs.getTimestamp("TS", other).toString(),
762: "2107-08-09 10:11:12.131415");
763: checkFalse(rs.getTime("T", regular).toString(), "14:05:06");
764: check(rs.getTime("T", other).toString(), "14:05:06");
765: // checkFalse(rs.getDate(2, regular).toString(), "2101-02-03");
766: // check(rs.getDate("D", other).toString(), "2101-02-03");
767:
768: rs.next();
769: check(rs.getInt("ID"), 4);
770: check(rs.getTimestamp("TS").toString(),
771: "2107-08-09 10:11:12.131415");
772: check(rs.getTime("T").toString(), "14:05:06");
773: check(rs.getDate("D").toString(), "2101-02-03");
774:
775: checkFalse(rs.next());
776: stat.execute("DROP TABLE TEST");
777: }
778:
779: void testBlob() throws Exception {
780: trace("Test BLOB");
781: ResultSet rs;
782:
783: stat
784: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE BLOB)");
785: stat.execute("INSERT INTO TEST VALUES(1,X'01010101')");
786: stat.execute("INSERT INTO TEST VALUES(2,X'02020202')");
787: stat.execute("INSERT INTO TEST VALUES(3,X'00')");
788: stat.execute("INSERT INTO TEST VALUES(4,X'ffffff')");
789: stat.execute("INSERT INTO TEST VALUES(5,X'0bcec1')");
790: stat.execute("INSERT INTO TEST VALUES(6,NULL)");
791: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
792: testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" },
793: new int[] { Types.INTEGER, Types.BLOB }, new int[] {
794: 10, Integer.MAX_VALUE }, new int[] { 0, 0 });
795: rs.next();
796: checkBytes(rs.getBytes(2), new byte[] { (byte) 0x01,
797: (byte) 0x01, (byte) 0x01, (byte) 0x01 });
798: check(!rs.wasNull());
799: rs.next();
800: checkBytes(rs.getBytes("value"), new byte[] { (byte) 0x02,
801: (byte) 0x02, (byte) 0x02, (byte) 0x02 });
802: check(!rs.wasNull());
803: rs.next();
804: checkBytes(readAllBytes(rs.getBinaryStream(2)),
805: new byte[] { (byte) 0x00 });
806: check(!rs.wasNull());
807: rs.next();
808: checkBytes(readAllBytes(rs.getBinaryStream("VaLuE")),
809: new byte[] { (byte) 0xff, (byte) 0xff, (byte) 0xff });
810: check(!rs.wasNull());
811: rs.next();
812: InputStream in = rs.getBinaryStream("value");
813: byte[] b = readAllBytes(in);
814: checkBytes(b, new byte[] { (byte) 0x0b, (byte) 0xce,
815: (byte) 0xc1 });
816: check(!rs.wasNull());
817: rs.next();
818: checkBytes(readAllBytes(rs.getBinaryStream("VaLuE")), null);
819: check(rs.wasNull());
820: check(!rs.next());
821: stat.execute("DROP TABLE TEST");
822: }
823:
824: void testClob() throws Exception {
825: trace("Test CLOB");
826: ResultSet rs;
827: String string;
828: stat
829: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE CLOB)");
830: stat.execute("INSERT INTO TEST VALUES(1,'Test')");
831: stat.execute("INSERT INTO TEST VALUES(2,'Hello')");
832: stat.execute("INSERT INTO TEST VALUES(3,'World!')");
833: stat.execute("INSERT INTO TEST VALUES(4,'Hallo')");
834: stat.execute("INSERT INTO TEST VALUES(5,'Welt!')");
835: stat.execute("INSERT INTO TEST VALUES(6,NULL)");
836: stat.execute("INSERT INTO TEST VALUES(7,NULL)");
837: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
838: testResultSetMeta(rs, 2, new String[] { "ID", "VALUE" },
839: new int[] { Types.INTEGER, Types.CLOB }, new int[] {
840: 10, Integer.MAX_VALUE }, new int[] { 0, 0 });
841: rs.next();
842: string = rs.getString(2);
843: check(string != null && string.equals("Test"));
844: check(!rs.wasNull());
845: rs.next();
846: InputStreamReader reader = null;
847: try {
848: reader = new InputStreamReader(rs.getAsciiStream(2),
849: "ISO-8859-1");
850: } catch (Exception e) {
851: check(false);
852: }
853: string = readString(reader);
854: check(!rs.wasNull());
855: trace(string);
856: check(string != null && string.equals("Hello"));
857: rs.next();
858: try {
859: reader = new InputStreamReader(rs.getAsciiStream("value"),
860: "ISO-8859-1");
861: } catch (Exception e) {
862: check(false);
863: }
864: string = readString(reader);
865: check(!rs.wasNull());
866: trace(string);
867: check(string != null && string.equals("World!"));
868: rs.next();
869: string = readString(rs.getCharacterStream(2));
870: check(!rs.wasNull());
871: trace(string);
872: check(string != null && string.equals("Hallo"));
873: rs.next();
874: string = readString(rs.getCharacterStream("value"));
875: check(!rs.wasNull());
876: trace(string);
877: check(string != null && string.equals("Welt!"));
878: rs.next();
879: check(rs.getCharacterStream(2) == null);
880: check(rs.wasNull());
881: rs.next();
882: check(rs.getAsciiStream("Value") == null);
883: check(rs.wasNull());
884:
885: check(rs.getStatement() == stat);
886: check(rs.getWarnings() == null);
887: rs.clearWarnings();
888: check(rs.getWarnings() == null);
889: check(rs.getFetchDirection(), ResultSet.FETCH_FORWARD);
890: check(rs.getConcurrency(), ResultSet.CONCUR_UPDATABLE);
891: rs.next();
892: stat.execute("DROP TABLE TEST");
893: }
894:
895: void testArray() throws Exception {
896: trace("Test ARRAY");
897: ResultSet rs;
898: stat
899: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE ARRAY)");
900: PreparedStatement prep = conn
901: .prepareStatement("INSERT INTO TEST VALUES(?, ?)");
902: prep.setInt(1, 1);
903: prep.setObject(2,
904: new Object[] { new Integer(1), new Integer(2) });
905: prep.execute();
906: prep.setInt(1, 2);
907: prep.setObject(2, new Object[] { new Integer(11),
908: new Integer(12) });
909: prep.execute();
910: rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
911: rs.next();
912: check(rs.getInt(1), 1);
913: Object[] list = (Object[]) rs.getObject(2);
914: check(((Integer) list[0]).intValue(), 1);
915: check(((Integer) list[1]).intValue(), 2);
916: Array array = rs.getArray(2);
917: Object[] list2 = (Object[]) array.getArray();
918: check(((Integer) list2[0]).intValue(), 1);
919: check(((Integer) list2[1]).intValue(), 2);
920: list2 = (Object[]) array.getArray(2, 1);
921: check(((Integer) list2[0]).intValue(), 2);
922: rs.next();
923: check(rs.getInt(1), 2);
924: list = (Object[]) rs.getObject(2);
925: check(((Integer) list[0]).intValue(), 11);
926: check(((Integer) list[1]).intValue(), 12);
927: array = rs.getArray(2);
928: list2 = (Object[]) array.getArray();
929: check(((Integer) list2[0]).intValue(), 11);
930: check(((Integer) list2[1]).intValue(), 12);
931: list2 = (Object[]) array.getArray(2, 1);
932: check(((Integer) list2[0]).intValue(), 12);
933: checkFalse(rs.next());
934: stat.execute("DROP TABLE TEST");
935: }
936:
937: byte[] readAllBytes(InputStream in) throws Exception {
938: if (in == null) {
939: return null;
940: }
941: ByteArrayOutputStream out = new ByteArrayOutputStream();
942: try {
943: while (true) {
944: int b = in.read();
945: if (b == -1) {
946: break;
947: }
948: out.write(b);
949: }
950: return out.toByteArray();
951: } catch (IOException e) {
952: check(false);
953: return null;
954: }
955: }
956:
957: void checkBytes(byte[] test, byte[] good) throws Exception {
958: if (test == null || good == null) {
959: check(test == null && good == null);
960: } else {
961: trace("test.length=" + test.length + " good.length="
962: + good.length);
963: check(test.length, good.length);
964: for (int i = 0; i < good.length; i++) {
965: check(test[i] == good[i]);
966: }
967: }
968: }
969:
970: void checkColumnBigDecimal(ResultSet rs, int column, int i,
971: String bd) throws Exception {
972: BigDecimal bd1 = rs.getBigDecimal(column);
973: int i1 = rs.getInt(column);
974: if (bd == null) {
975: trace("should be: null");
976: check(rs.wasNull());
977: } else {
978: trace("BigDecimal i=" + i + " bd=" + bd + " ; i1=" + i1
979: + " bd1=" + bd1);
980: check(!rs.wasNull());
981: check(i1 == i);
982: check(bd1.compareTo(new BigDecimal(bd)) == 0);
983: }
984: }
985:
986: }
|