|
1
5 package org.h2.test.jdbc;
6
7 import java.io.ByteArrayOutputStream  ;
8 import java.io.IOException ;
9 import java.io.InputStream ;
10 import java.io.InputStreamReader ;
11 import java.math.BigDecimal ;
12 import java.sql.Connection ;
13 import java.sql.Date ;
14 import java.sql.PreparedStatement ;
15 import java.sql.ResultSet ;
16 import java.sql.ResultSetMetaData ;
17 import java.sql.SQLException ;
18 import java.sql.Statement ;
19 import java.sql.Time ;
20 import java.sql.Timestamp ;
21 import java.sql.Types ;
22 import java.util.Calendar ;
23 import java.util.TimeZone ;
24
25 import org.h2.test.TestBase;
26
27 public class TestResultSet extends TestBase {
28 Connection conn;
29 Statement stat;
30
31 public void test() throws Exception {
32 deleteDb("resultset");
33 conn = getConnection("resultset");
34
35 stat=conn.createStatement();
36
37 testLimitMaxRows();
38
39 trace("max rows="+stat.getMaxRows());
40 stat.setMaxRows(6);
41 trace("max rows after set to 6="+stat.getMaxRows());
42 check(stat.getMaxRows()==6);
43
44 testInt();
45 testVarchar();
46 testDecimal();
47 testDoubleFloat();
48 testDatetime();
49 testDatetimeWithCalendar();
50 testBlob();
51 testClob();
52
53 testAutoIncrement();
54
55 conn.close();
56
57 }
58
59 private void testLimitMaxRows() throws Exception {
60 trace("Test LimitMaxRows");
61 ResultSet rs;
62 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY)");
63 stat.execute("INSERT INTO TEST VALUES(1), (2), (3), (4)");
64 rs = stat.executeQuery("SELECT * FROM TEST");
65 checkResultRowCount(rs, 4);
66 rs = stat.executeQuery("SELECT * FROM TEST LIMIT 2");
67 checkResultRowCount(rs, 2);
68 stat.setMaxRows(2);
69 rs = stat.executeQuery("SELECT * FROM TEST");
70 checkResultRowCount(rs, 2);
71 rs = stat.executeQuery("SELECT * FROM TEST LIMIT 1");
72 checkResultRowCount(rs, 1);
73 rs = stat.executeQuery("SELECT * FROM TEST LIMIT 3");
74 checkResultRowCount(rs, 2);
75 stat.setMaxRows(0);
76 stat.execute("DROP TABLE TEST");
77 }
78
79 void testAutoIncrement() throws Exception {
80 trace("Test AutoIncrement");
81 stat.execute("DROP TABLE IF EXISTS TEST");
82 ResultSet rs;
83 stat.execute("CREATE TABLE TEST(ID IDENTITY NOT NULL, NAME VARCHAR NULL)");
84
85 stat.execute("INSERT INTO TEST(NAME) VALUES('Hello')");
86 rs = stat.getGeneratedKeys();
87 check(rs.next());
88 check(rs.getInt(1), 1);
89
90 stat.execute("INSERT INTO TEST(NAME) VALUES('World')");
91 rs = stat.getGeneratedKeys();
92 check(rs.next());
93 check(rs.getInt(1), 2);
94
95 rs = stat.executeQuery("SELECT ID AS I, NAME AS N, ID+1 AS IP1 FROM TEST");
96 ResultSetMetaData meta = rs.getMetaData();
97 check(meta.isAutoIncrement(1));
98 checkFalse(meta.isAutoIncrement(2));
99 checkFalse(meta.isAutoIncrement(3));
100 check(meta.isNullable(1), ResultSetMetaData.columnNoNulls);
101 check(meta.isNullable(2), ResultSetMetaData.columnNullable);
102 check(meta.isNullable(3), ResultSetMetaData.columnNullableUnknown);
103 check(rs.next());
104 check(rs.next());
105 checkFalse(rs.next());
106
107 }
108
109 void testInt() throws Exception {
110 trace("Test INT");
111 ResultSet rs;
112 Object o;
113
114 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE INT)");
115 stat.execute("INSERT INTO TEST VALUES(1,-1)");
116 stat.execute("INSERT INTO TEST VALUES(2,0)");
117 stat.execute("INSERT INTO TEST VALUES(3,1)");
118 stat.execute("INSERT INTO TEST VALUES(4,"+Integer.MAX_VALUE+")");
119 stat.execute("INSERT INTO TEST VALUES(5,"+Integer.MIN_VALUE+")");
120 stat.execute("INSERT INTO TEST VALUES(6,NULL)");
121 stat.execute("INSERT INTO TEST VALUES(7,NULL)");
123
124
129 rs=stat.executeQuery("SELECT *, NULL AS N FROM TEST ORDER BY ID");
130
131 check(rs.findColumn("TEST.ID"), 1);
133 check(rs.findColumn("TEST.VALUE"), 2);
134
135 ResultSetMetaData meta = rs.getMetaData();
136 check(meta.getColumnCount(), 3);
137 check(meta.getCatalogName(1), "RESULTSET");
138 check("PUBLIC".equals(meta.getSchemaName(2)));
139 check("TEST".equals(meta.getTableName(1)));
140 check("ID".equals(meta.getColumnName(1)));
141 check("VALUE".equals(meta.getColumnName(2)));
142 check(meta.isAutoIncrement(1)==false);
143 check(meta.isCaseSensitive(1));
144 check(meta.isSearchable(1));
145 checkFalse(meta.isCurrency(1));
146 check(meta.getColumnDisplaySize(1)>0);
147 check(meta.isSigned(1));
148 check(meta.isSearchable(2));
149 check(meta.isNullable(1), ResultSetMetaData.columnNoNulls);
150 checkFalse(meta.isReadOnly(1));
151 check(meta.isWritable(1));
152 checkFalse(meta.isDefinitelyWritable(1));
153 check(meta.getColumnDisplaySize(1)>0);
154 check(meta.getColumnDisplaySize(2)>0);
155 check(meta.getColumnClassName(3), null);
156
157 check(rs.getRow()==0);
158 testResultSetMeta(rs,3,
159 new String []{"ID", "VALUE", "N"},
160 new int[]{Types.INTEGER,Types.INTEGER, Types.NULL},
161 new int[]{10,10,1},
162 new int[]{0,0,0}
163 );
164 rs.next();
165 check(rs.getConcurrency(), ResultSet.CONCUR_READ_ONLY);
166 check(rs.getFetchDirection(), ResultSet.FETCH_FORWARD);
167 trace("default fetch size="+rs.getFetchSize());
168 rs.setFetchSize(0);
170 trace("after set to 0, fetch size="+rs.getFetchSize());
171 try {
173 rs.setFetchSize(-1);
174 error("fetch size -1 is not allowed");
175 } catch(SQLException e) {
176 checkNotGeneralException(e);
177 trace(e.toString());
178 }
179 trace("after try to set to -1, fetch size="+rs.getFetchSize());
180 try {
181 rs.setFetchSize(100);
182 error("fetch size 100 is bigger than maxrows - not allowed");
183 } catch(SQLException e) {
184 checkNotGeneralException(e);
185 trace(e.toString());
186 }
187 trace("after try set to 100, fetch size="+rs.getFetchSize());
188 rs.setFetchSize(6);
189
190 check(rs.getRow()==1);
191 check(rs.findColumn("VALUE"), 2);
192 check(rs.findColumn("value"), 2);
193 check(rs.findColumn("Value"), 2);
194 check(rs.findColumn("Value"), 2);
195 check(rs.findColumn("ID"), 1);
196 check(rs.findColumn("id"), 1);
197 check(rs.findColumn("Id"), 1);
198 check(rs.findColumn("iD"), 1);
199 check(rs.getInt(2)==-1 && rs.wasNull()==false);
200 check(rs.getInt("VALUE")==-1 && rs.wasNull()==false);
201 check(rs.getInt("value")==-1 && rs.wasNull()==false);
202 check(rs.getInt("Value")==-1 && rs.wasNull()==false);
203 check(rs.getString("Value").equals("-1") && rs.wasNull()==false);
204
205 o=rs.getObject("value");
206 trace(o.getClass().getName());
207 check(o instanceof Integer );
208 check(((Integer )o).intValue()==-1);
209 o=rs.getObject(2);
210 trace(o.getClass().getName());
211 check(o instanceof Integer );
212 check(((Integer )o).intValue()==-1);
213 check(rs.getBoolean("Value")==true);
214 check(rs.getByte("Value")==(byte)-1);
215 check(rs.getShort("Value")==(short)-1);
216 check(rs.getLong("Value")==-1);
217 check(rs.getFloat("Value")==-1.0);
218 check(rs.getDouble("Value")==-1.0);
219
220 check(rs.getString("Value").equals("-1") && rs.wasNull()==false);
221 check(rs.getInt("ID")==1 && rs.wasNull()==false);
222 check(rs.getInt("id")==1 && rs.wasNull()==false);
223 check(rs.getInt("Id")==1 && rs.wasNull()==false);
224 check(rs.getInt(1)==1 && rs.wasNull()==false);
225 rs.next();
226 check(rs.getRow()==2);
227 check(rs.getInt(2)==0 && rs.wasNull()==false);
228 check(rs.getBoolean(2)==false);
229 check(rs.getByte(2)==0);
230 check(rs.getShort(2)==0);
231 check(rs.getLong(2)==0);
232 check(rs.getFloat(2)==0.0);
233 check(rs.getDouble(2)==0.0);
234 check(rs.getString(2).equals("0") && rs.wasNull()==false);
235 check(rs.getInt(1)==2 && rs.wasNull()==false);
236 rs.next();
237 check(rs.getRow()==3);
238 check(rs.getInt("ID")==3 && rs.wasNull()==false);
239 check(rs.getInt("VALUE")==1 && rs.wasNull()==false);
240 rs.next();
241 check(rs.getRow()==4);
242 check(rs.getInt("ID")==4 && rs.wasNull()==false);
243 check(rs.getInt("VALUE")==Integer.MAX_VALUE && rs.wasNull()==false);
244 rs.next();
245 check(rs.getRow()==5);
246 check(rs.getInt("id")==5 && rs.wasNull()==false);
247 check(rs.getInt("value")==Integer.MIN_VALUE && rs.wasNull()==false);
248 check(rs.getString(1).equals("5") && rs.wasNull()==false);
249 rs.next();
250 check(rs.getRow()==6);
251 check(rs.getInt("id")==6 && rs.wasNull()==false);
252 check(rs.getInt("value")==0 && rs.wasNull()==true);
253 check(rs.getInt(2)==0 && rs.wasNull()==true);
254 check(rs.getInt(1)==6 && rs.wasNull()==false);
255 check(rs.getString(1).equals("6") && rs.wasNull()==false);
256 check(rs.getString(2)==null && rs.wasNull()==true);
257 o=rs.getObject(2);
258 check(o==null);
259 check(rs.wasNull());
260 checkFalse(rs.next());
261 check(rs.getRow(), 0);
262
264 stat.execute("DROP TABLE TEST");
265 stat.setMaxRows(0);
266 }
267
268 void testVarchar() throws Exception {
269 trace("Test VARCHAR");
270 ResultSet rs;
271 Object o;
272
273 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
274 stat.execute("INSERT INTO TEST VALUES(1,'')");
275 stat.execute("INSERT INTO TEST VALUES(2,' ')");
276 stat.execute("INSERT INTO TEST VALUES(3,' ')");
277 stat.execute("INSERT INTO TEST VALUES(4,NULL)");
278 stat.execute("INSERT INTO TEST VALUES(5,'Hi')");
279 stat.execute("INSERT INTO TEST VALUES(6,' Hi ')");
280 stat.execute("INSERT INTO TEST VALUES(7,'Joe''s')");
281 stat.execute("INSERT INTO TEST VALUES(8,'{escape}')");
282 stat.execute("INSERT INTO TEST VALUES(9,'\\n')");
283 stat.execute("INSERT INTO TEST VALUES(10,'\\''')");
284 stat.execute("INSERT INTO TEST VALUES(11,'\\%')");
285 rs=stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
286 testResultSetMeta(rs,2,
287 new String []{"ID","VALUE"},
288 new int[]{Types.INTEGER,Types.VARCHAR},
289 new int[]{10,255},
290 new int[]{0,0}
291 );
292 String value;
293 rs.next();
294 value=rs.getString(2);
295 trace("Value: <"+value+"> (should be: <>)");
296 check(value!=null && value.equals("") && rs.wasNull()==false);
297 check(rs.getInt(1)==1 && rs.wasNull()==false);
298 rs.next();
299 value=rs.getString(2);
300 trace("Value: <"+value+"> (should be: < >)");
301 check(rs.getString(2).equals(" ") && rs.wasNull()==false);
302 check(rs.getInt(1)==2 && rs.wasNull()==false);
303 rs.next();
304 value=rs.getString(2);
305 trace("Value: <"+value+"> (should be: < >)");
306 check(rs.getString(2).equals(" ") && rs.wasNull()==false);
307 check(rs.getInt(1)==3 && rs.wasNull()==false);
308 rs.next();
309 value=rs.getString(2);
310 trace("Value: <"+value+"> (should be: <null>)");
311 check(rs.getString(2)==null && rs.wasNull()==true);
312 check(rs.getInt(1)==4 && rs.wasNull()==false);
313 rs.next();
314 value=rs.getString(2);
315 trace("Value: <"+value+"> (should be: <Hi>)");
316 check(rs.getInt(1)==5 && rs.wasNull()==false);
317 check(rs.getString(2).equals("Hi") && rs.wasNull()==false);
318 o=rs.getObject("value");
319 trace(o.getClass().getName());
320 check(o instanceof String );
321 check(o.toString().equals("Hi"));
322 rs.next();
323 value=rs.getString(2);
324 trace("Value: <"+value+"> (should be: < Hi >)");
325 check(rs.getInt(1)==6 && rs.wasNull()==false);
326 check(rs.getString(2).equals(" Hi ") && rs.wasNull()==false);
327 rs.next();
328 value=rs.getString(2);
329 trace("Value: <"+value+"> (should be: <Joe's>)");
330 check(rs.getInt(1)==7 && rs.wasNull()==false);
331 check(rs.getString(2).equals("Joe's") && rs.wasNull()==false);
332 rs.next();
333 value=rs.getString(2);
334 trace("Value: <"+value+"> (should be: <{escape}>)");
335 check(rs.getInt(1)==8 && rs.wasNull()==false);
336 check(rs.getString(2).equals("{escape}")&& rs.wasNull()==false);
337 rs.next();
338 value=rs.getString(2);
339 trace("Value: <"+value+"> (should be: <\\n>)");
340 check(rs.getInt(1)==9 && rs.wasNull()==false);
341 check(rs.getString(2).equals("\\n") && rs.wasNull()==false);
342 rs.next();
343 value=rs.getString(2);
344 trace("Value: <"+value+"> (should be: <\\'>)");
345 check(rs.getInt(1)==10 && rs.wasNull()==false);
346 check(rs.getString(2).equals("\\'") && rs.wasNull()==false);
347 rs.next();
348 value=rs.getString(2);
349 trace("Value: <"+value+"> (should be: <\\%>)");
350 check(rs.getInt(1)==11 && rs.wasNull()==false);
351 check(rs.getString(2).equals("\\%") && rs.wasNull()==false);
352 check(rs.next()==false);
353 stat.execute("DROP TABLE TEST");
354 }
355
356 void testDecimal() throws Exception {
357 trace("Test DECIMAL");
358 ResultSet rs;
359 Object o;
360
361 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE DECIMAL(10,2))");
362 stat.execute("INSERT INTO TEST VALUES(1,-1)");
363 stat.execute("INSERT INTO TEST VALUES(2,.0)");
364 stat.execute("INSERT INTO TEST VALUES(3,1.)");
365 stat.execute("INSERT INTO TEST VALUES(4,12345678.89)");
366 stat.execute("INSERT INTO TEST VALUES(6,99999999.99)");
367 stat.execute("INSERT INTO TEST VALUES(7,-99999999.99)");
368 stat.execute("INSERT INTO TEST VALUES(8,NULL)");
369 rs=stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
370 testResultSetMeta(rs,2,
371 new String []{"ID","VALUE"},
372 new int[]{Types.INTEGER,Types.DECIMAL},
373 new int[]{10,10},
374 new int[]{0,2}
375 );
376 BigDecimal bd;
377 rs.next();
378 check(rs.getInt(1)==1);
379 check(rs.wasNull()==false);
380 check(rs.getInt(2)==-1);
381 check(rs.wasNull()==false);
382 bd=rs.getBigDecimal(2);
383 check(bd.compareTo(new BigDecimal ("-1.00"))==0);
384 check(rs.wasNull()==false);
385 o=rs.getObject(2);
386 trace(o.getClass().getName());
387 check(o instanceof BigDecimal );
388 check(((BigDecimal )o).compareTo(new BigDecimal ("-1.00"))==0);
389 rs.next();
390 check(rs.getInt(1)==2);
391 check(rs.wasNull()==false);
392 check(rs.getInt(2)==0);
393 check(rs.wasNull()==false);
394 bd=rs.getBigDecimal(2);
395 check(bd.compareTo(new BigDecimal ("0.00"))==0);
396 check(rs.wasNull()==false);
397 rs.next();
398 checkColumnBigDecimal(rs,2,1,"1.00");
399 rs.next();
400 checkColumnBigDecimal(rs,2,12345678,"12345678.89");
401 rs.next();
402 checkColumnBigDecimal(rs,2,99999999,"99999999.99");
403 rs.next();
404 checkColumnBigDecimal(rs,2,-99999999,"-99999999.99");
405 rs.next();
406 checkColumnBigDecimal(rs,2,0,null);
407 check(rs.next()==false);
408 stat.execute("DROP TABLE TEST");
409 }
410
411 void testDoubleFloat() throws Exception {
412 trace("Test DOUBLE - FLOAT");
413 ResultSet rs;
414 Object o;
415
416 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, D DOUBLE, R REAL)");
417 stat.execute("INSERT INTO TEST VALUES(1, -1, -1)");
418 stat.execute("INSERT INTO TEST VALUES(2,.0, .0)");
419 stat.execute("INSERT INTO TEST VALUES(3, 1., 1.)");
420 stat.execute("INSERT INTO TEST VALUES(4, 12345678.89, 12345678.89)");
421 stat.execute("INSERT INTO TEST VALUES(6, 99999999.99, 99999999.99)");
422 stat.execute("INSERT INTO TEST VALUES(7, -99999999.99, -99999999.99)");
423 stat.execute("INSERT INTO TEST VALUES(8, NULL, NULL)");
424 rs=stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
425 testResultSetMeta(rs,3,
426 new String []{"ID", "D", "R"},
427 new int[]{Types.INTEGER, Types.DOUBLE, Types.REAL},
428 new int[]{10, 17, 7},
429 new int[]{0, 0, 0}
430 );
431 BigDecimal bd;
432 rs.next();
433 check(rs.getInt(1)==1);
434 check(rs.wasNull()==false);
435 check(rs.getInt(2)==-1);
436 check(rs.getInt(3)==-1);
437 check(rs.wasNull()==false);
438 bd=rs.getBigDecimal(2);
439 check(bd.compareTo(new BigDecimal ("-1.00"))==0);
440 check(rs.wasNull()==false);
441 o=rs.getObject(2);
442 trace(o.getClass().getName());
443 check(o instanceof Double );
444 check(((Double )o).compareTo(new Double ("-1.00"))==0);
445 o=rs.getObject(3);
446 trace(o.getClass().getName());
447 check(o instanceof Float );
448 check(((Float )o).compareTo(new Float ("-1.00"))==0);
449 rs.next();
450 check(rs.getInt(1)==2);
451 check(rs.wasNull()==false);
452 check(rs.getInt(2)==0);
453 check(rs.wasNull()==false);
454 check(rs.getInt(3)==0);
455 check(rs.wasNull()==false);
456 bd=rs.getBigDecimal(2);
457 check(bd.compareTo(new BigDecimal ("0.00"))==0);
458 check(rs.wasNull()==false);
459 bd=rs.getBigDecimal(3);
460 check(bd.compareTo(new BigDecimal ("0.00"))==0);
461 check(rs.wasNull()==false);
462 rs.next();
463 check(rs.getDouble(2), 1.0);
464 check(rs.getFloat(3), 1.0f);
465 rs.next();
466 check(rs.getDouble(2), 12345678.89);
467 check(rs.getFloat(3), 12345678.89f);
468 rs.next();
469 check(rs.getDouble(2), 99999999.99);
470 check(rs.getFloat(3), 99999999.99f);
471 rs.next();
472 check(rs.getDouble(2), -99999999.99);
473 check(rs.getFloat(3), -99999999.99f);
474 rs.next();
475 checkColumnBigDecimal(rs,2,0,null);
476 checkColumnBigDecimal(rs,3,0,null);
477 check(rs.next()==false);
478 stat.execute("DROP TABLE TEST");
479 }
480
481 void testDatetime() throws Exception {
482 trace("Test DATETIME");
483 ResultSet rs;
484 Object o;
485
486 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE DATETIME)");
487 stat.execute("INSERT INTO TEST VALUES(1,DATE '2011-11-11')");
488 stat.execute("INSERT INTO TEST VALUES(2,TIMESTAMP '2002-02-02 02:02:02')");
489 stat.execute("INSERT INTO TEST VALUES(3,TIMESTAMP '1800-1-1 0:0:0')");
490 stat.execute("INSERT INTO TEST VALUES(4,TIMESTAMP '9999-12-31 23:59:59')");
491 stat.execute("INSERT INTO TEST VALUES(5,NULL)");
492 rs=stat.executeQuery("SELECT 0 ID, TIMESTAMP '9999-12-31 23:59:59' VALUE FROM TEST ORDER BY ID");
493 testResultSetMeta(rs,2,
494 new String []{"ID","VALUE"},
495 new int[]{Types.INTEGER,Types.TIMESTAMP},
496 new int[]{10,23},
497 new int[]{0,10}
498 );
499 rs=stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
500 testResultSetMeta(rs,2,
501 new String []{"ID","VALUE"},
502 new int[]{Types.INTEGER,Types.TIMESTAMP},
503 new int[]{10,23},
504 new int[]{0,10}
505 );
506 rs.next();
507 java.sql.Date date;
508 java.sql.Time time;
509 java.sql.Timestamp ts;
510 date=rs.getDate(2);
511 check(!rs.wasNull());
512 time=rs.getTime(2);
513 check(!rs.wasNull());
514 ts=rs.getTimestamp(2);
515 check(!rs.wasNull());
516 trace("Date: "+date.toString()+" Time:"+time.toString()+" Timestamp:"+ts.toString());
517 trace("Date ms: "+date.getTime()+" Time ms:"+time.getTime()+" Timestamp ms:"+ts.getTime());
518 trace("1970 ms: "+java.sql.Timestamp.valueOf("1970-01-01 00:00:00.0").getTime());
519 check(date.getTime(), java.sql.Timestamp.valueOf("2011-11-11 00:00:00.0").getTime());
520 check(time.getTime(), java.sql.Timestamp.valueOf("1970-01-01 00:00:00.0").getTime());
521 check(ts.getTime(), java.sql.Timestamp.valueOf("2011-11-11 00:00:00.0").getTime());
522 check(date.equals(java.sql.Timestamp.valueOf("2011-11-11 00:00:00.0")));
523 check(time.equals(java.sql.Timestamp.valueOf("1970-01-01 00:00:00.0")));
524 check(ts.equals(java.sql.Timestamp.valueOf("2011-11-11 00:00:00.0")));
525 checkFalse(rs.wasNull());
526 o=rs.getObject(2);
527 trace(o.getClass().getName());
528 check(o instanceof java.sql.Timestamp );
529 check(((java.sql.Timestamp )o).equals(java.sql.Timestamp.valueOf("2011-11-11 00:00:00.0")));
530 checkFalse(rs.wasNull());
531 rs.next();
532
533 date=rs.getDate("VALUE");
534 check(!rs.wasNull());
535 time=rs.getTime("VALUE");
536 check(!rs.wasNull());
537 ts=rs.getTimestamp("VALUE");
538 check(!rs.wasNull());
539 trace("Date: "+date.toString()+" Time:"+time.toString()+" Timestamp:"+ts.toString());
540 check(date.toString(), "2002-02-02");
541 check(time.toString(), "02:02:02");
542 check(ts.toString(), "2002-02-02 02:02:02.0");
543 rs.next();
544 check(rs.getDate("value").toString(), "1800-01-01");
545 check(rs.getTime("value").toString(), "00:00:00");
546 check(rs.getTimestamp("value").toString(), "1800-01-01 00:00:00.0");
547 rs.next();
548 check(rs.getDate("Value").toString(), "9999-12-31");
549 check(rs.getTime("Value").toString(), "23:59:59");
550 check(rs.getTimestamp("Value").toString(), "9999-12-31 23:59:59.0");
551 rs.next();
552 check(rs.getDate("Value")==null && rs.wasNull());
553 check(rs.getTime("vALUe")==null && rs.wasNull());
554 check(rs.getTimestamp(2)==null && rs.wasNull());
555 check(rs.next()==false);
556
557 rs = stat.executeQuery("SELECT DATE '2001-02-03' D, TIME '14:15:16', TIMESTAMP '2007-08-09 10:11:12.141516171' TS FROM TEST");
558 rs.next();
559 date = (Date ) rs.getObject(1);
560 time = (Time ) rs.getObject(2);
561 ts = (Timestamp )rs.getObject(3);
562 check(date.toString(), "2001-02-03");
563 check(time.toString(), "14:15:16");
564 check(ts.toString(), "2007-08-09 10:11:12.141516171");
565
566 stat.execute("DROP TABLE TEST");
567 }
568
569 void testDatetimeWithCalendar() throws Exception {
570 trace("Test DATETIME with Calendar");
571 ResultSet rs;
572
573 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, D DATE, T TIME, TS TIMESTAMP)");
574 PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?, ?)");
575 Calendar regular = Calendar.getInstance();
576 Calendar other = null;
577 String [] timezones = TimeZone.getAvailableIDs();
578 for(int i=0; i<timezones.length; i++) {
580 TimeZone zone = TimeZone.getTimeZone(timezones[i]);
581 if(regular.getTimeZone().getRawOffset() != zone.getRawOffset()) {
582 other = Calendar.getInstance(zone);
583 break;
584 }
585 }
586 trace("regular offset = "+regular.getTimeZone().getRawOffset()+" other = "+other.getTimeZone().getRawOffset());
587
588 prep.setInt(1, 0);
589 prep.setDate(2, null, regular);
590 prep.setTime(3, null, regular);
591 prep.setTimestamp(4, null, regular);
592 prep.execute();
593
594 prep.setInt(1, 1);
595 prep.setDate(2, null, other);
596 prep.setTime(3, null, other);
597 prep.setTimestamp(4, null, other);
598 prep.execute();
599
600 prep.setInt(1, 2);
601 prep.setDate(2, java.sql.Date.valueOf("2001-02-03"), regular);
602 prep.setTime(3, java.sql.Time.valueOf("04:05:06"), regular);
603 prep.setTimestamp(4, java.sql.Timestamp.valueOf("2007-08-09 10:11:12.131415"), regular);
604 prep.execute();
605
606 prep.setInt(1, 3);
607 prep.setDate(2, java.sql.Date.valueOf("2101-02-03"), other);
608 prep.setTime(3, java.sql.Time.valueOf("14:05:06"), other);
609 prep.setTimestamp(4, java.sql.Timestamp.valueOf("2107-08-09 10:11:12.131415"), other);
610 prep.execute();
611
612 prep.setInt(1, 4);
613 prep.setDate(2, java.sql.Date.valueOf("2101-02-03"));
614 prep.setTime(3, java.sql.Time.valueOf("14:05:06"));
615 prep.setTimestamp(4, java.sql.Timestamp.valueOf("2107-08-09 10:11:12.131415"));
616 prep.execute();
617
618 rs=stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
619 testResultSetMeta(rs,4,
620 new String []{"ID", "D", "T", "TS"},
621 new int[]{Types.INTEGER, Types.DATE, Types.TIME, Types.TIMESTAMP},
622 new int[]{10,8,6,23},
623 new int[]{0,0,0,10}
624 );
625
626 rs.next();
627 check(rs.getInt(1), 0);
628 check(rs.getDate(2, regular)==null && rs.wasNull());
629 check(rs.getTime(3, regular)==null && rs.wasNull());
630 check(rs.getTimestamp(3, regular)==null && rs.wasNull());
631
632 rs.next();
633 check(rs.getInt(1), 1);
634 check(rs.getDate(2, other)==null && rs.wasNull());
635 check(rs.getTime(3, other)==null && rs.wasNull());
636 check(rs.getTimestamp(3, other)==null && rs.wasNull());
637
638 rs.next();
639 check(rs.getInt(1), 2);
640 check(rs.getDate(2, regular).toString(), "2001-02-03");
641 check(rs.getTime(3, regular).toString(), "04:05:06");
642 checkFalse(rs.getTime(3, other).toString(), "04:05:06");
643 check(rs.getTimestamp(4, regular).toString(), "2007-08-09 10:11:12.131415");
644 checkFalse(rs.getTimestamp(4, other).toString(), "2007-08-09 10:11:12.131415");
645
646 rs.next();
647 check(rs.getInt("ID"), 3);
648 checkFalse(rs.getTimestamp("TS", regular).toString(), "2107-08-09 10:11:12.131415");
649 check(rs.getTimestamp("TS", other).toString(), "2107-08-09 10:11:12.131415");
650 checkFalse(rs.getTime("T", regular).toString(), "14:05:06");
651 check(rs.getTime("T", other).toString(), "14:05:06");
652
655 rs.next();
656 check(rs.getInt("ID"), 4);
657 check(rs.getTimestamp("TS").toString(), "2107-08-09 10:11:12.131415");
658 check(rs.getTime("T").toString(), "14:05:06");
659 check(rs.getDate("D").toString(), "2101-02-03");
660
661 checkFalse(rs.next());
662 stat.execute("DROP TABLE TEST");
663 }
664
665 void testBlob() throws Exception {
666 trace("Test BLOB");
667 ResultSet rs;
668
669 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE BLOB)");
670 stat.execute("INSERT INTO TEST VALUES(1,X'01010101')");
671 stat.execute("INSERT INTO TEST VALUES(2,X'02020202')");
672 stat.execute("INSERT INTO TEST VALUES(3,X'00')");
673 stat.execute("INSERT INTO TEST VALUES(4,X'ffFFff')");
674 stat.execute("INSERT INTO TEST VALUES(5,X'0bcec1')");
675 stat.execute("INSERT INTO TEST VALUES(6,NULL)");
676 rs=stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
677 testResultSetMeta(rs,2,
678 new String []{"ID","VALUE"},
679 new int[]{Types.INTEGER,Types.BLOB},
680 new int[]{10,Integer.MAX_VALUE},
681 new int[]{0,0}
682 );
683 rs.next();
684 checkBytes(rs.getBytes(2),new byte[]{(byte)0x01,(byte)0x01,(byte)0x01,(byte)0x01});
685 check(!rs.wasNull());
686 rs.next();
687 checkBytes(rs.getBytes("value"),new byte[]{(byte)0x02,(byte)0x02,(byte)0x02,(byte)0x02});
688 check(!rs.wasNull());
689 rs.next();
690 checkBytes(readAllBytes(rs.getBinaryStream(2)),new byte[]{(byte)0x00});
691 check(!rs.wasNull());
692 rs.next();
693 checkBytes(readAllBytes(rs.getBinaryStream("VaLuE")),new byte[]{(byte)0xff,(byte)0xff,(byte)0xff});
694 check(!rs.wasNull());
695 rs.next();
696 InputStream in = rs.getBinaryStream("value");
697 byte[] b = readAllBytes(in);
698 checkBytes(b,new byte[]{(byte)0x0b,(byte)0xce,(byte)0xc1});
699 check(!rs.wasNull());
700 rs.next();
701 checkBytes(readAllBytes(rs.getBinaryStream("VaLuE")),null);
702 check(rs.wasNull());
703 check(rs.next()==false);
704 stat.execute("DROP TABLE TEST");
705 }
706
707 void testClob() throws Exception {
708 trace("Test CLOB");
709 ResultSet rs;
710 String string;
711 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE CLOB)");
712 stat.execute("INSERT INTO TEST VALUES(1,'Test')");
713 stat.execute("INSERT INTO TEST VALUES(2,'Hello')");
714 stat.execute("INSERT INTO TEST VALUES(3,'World!')");
715 stat.execute("INSERT INTO TEST VALUES(4,'Hallo')");
716 stat.execute("INSERT INTO TEST VALUES(5,'Welt!')");
717 stat.execute("INSERT INTO TEST VALUES(6,NULL)");
718 stat.execute("INSERT INTO TEST VALUES(7,NULL)");
719 rs=stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
720 testResultSetMeta(rs,2,
721 new String []{"ID","VALUE"},
722 new int[]{Types.INTEGER,Types.CLOB},
723 new int[]{10,Integer.MAX_VALUE},
724 new int[]{0,0}
725 );
726 rs.next();
727 string=rs.getString(2);
728 check(string!=null && string.equals("Test"));
729 check(!rs.wasNull());
730 rs.next();
731 InputStreamReader reader=null;
732 try {
733 reader=new InputStreamReader (rs.getAsciiStream(2),"ISO-8859-1");
734 } catch(Exception e) {
735 check(false);
736 }
737 string=readString(reader);
738 check(!rs.wasNull());
739 trace(string);
740 check(string!=null && string.equals("Hello"));
741 rs.next();
742 try {
743 reader=new InputStreamReader (rs.getAsciiStream("value"),"ISO-8859-1");
744 } catch(Exception e) {
745 check(false);
746 }
747 string=readString(reader);
748 check(!rs.wasNull());
749 trace(string);
750 check(string!=null && string.equals("World!"));
751 rs.next();
752 string=readString(rs.getCharacterStream(2));
753 check(!rs.wasNull());
754 trace(string);
755 check(string!=null && string.equals("Hallo"));
756 rs.next();
757 string=readString(rs.getCharacterStream("value"));
758 check(!rs.wasNull());
759 trace(string);
760 check(string!=null && string.equals("Welt!"));
761 rs.next();
762 check(rs.getCharacterStream(2)==null);
763 check(rs.wasNull());
764 rs.next();
765 check(rs.getAsciiStream("Value")==null);
766 check(rs.wasNull());
767
768 check(rs.getStatement()==stat);
769 check(rs.getWarnings()==null);
770 rs.clearWarnings();
771 check(rs.getWarnings()==null);
772 check(rs.getFetchDirection(), ResultSet.FETCH_FORWARD);
773 check(rs.getConcurrency(), ResultSet.CONCUR_UPDATABLE);
774 rs.next();
775 stat.execute("DROP TABLE TEST");
776 }
777
778 byte[] readAllBytes(InputStream in) throws Exception {
779 if(in==null) {
780 return null;
781 }
782 ByteArrayOutputStream out=new ByteArrayOutputStream ();
783 try {
784 while(true) {
785 int b=in.read();
786 if(b==-1) {
787 break;
788 }
789 out.write(b);
790 }
791 return out.toByteArray();
792 } catch(IOException e) {
793 check(false);
794 return null;
795 }
796 }
797
798 void checkBytes(byte[] test,byte[] good) throws Exception {
799 if(test==null || good==null) {
800 check(test==null && good==null);
801 } else {
802 trace("test.length="+test.length+" good.length="+good.length);
803 check(test.length, good.length);
804 for(int i=0;i<good.length;i++) {
805 check(test[i]==good[i]);
806 }
807 }
808 }
809
810 void checkColumnBigDecimal(ResultSet rs,int column,int i,String bd) throws Exception {
811 BigDecimal bd1=rs.getBigDecimal(column);
812 int i1=rs.getInt(column);
813 if(bd==null) {
814 trace("should be: null");
815 check(rs.wasNull());
816 } else {
817 trace("BigDecimal i="+i+" bd="+bd+" ; i1="+i1+" bd1="+bd1);
818 check(!rs.wasNull());
819 check(i1==i);
820 check(bd1.compareTo(new BigDecimal (bd))==0);
821 }
822 }
823
824 }
825 |
Popular Tags |
Java API By Example, From Geeks To Geeks.