|
1
5 package org.h2.test.jdbc;
6
7 import java.io.ByteArrayInputStream  ;
8 import java.io.IOException ;
9 import java.io.StringReader ;
10 import java.math.BigDecimal ;
11 import java.sql.Connection ;
12 import java.sql.ParameterMetaData ;
13 import java.sql.PreparedStatement ;
14 import java.sql.ResultSet ;
15 import java.sql.ResultSetMetaData ;
16 import java.sql.SQLException ;
17 import java.sql.Statement ;
18 import java.sql.Types ;
19
20 import org.h2.test.TestBase;
21
22 public class TestPreparedStatement extends TestBase {
23
24 static final int LOB_SIZE=4000, LOB_SIZE_BIG=512 * 1024;
25
26 public void test() throws Exception {
27
28 deleteDb("preparedStatement");
29 Connection conn = getConnection("preparedStatement");
30 testSetObject(conn);
31 testPreparedSubquery(conn);
32 testLikeIndex(conn);
33 testCasewhen(conn);
34 testSubquery(conn);
35 testObject(conn);
36 if(config.jdk14) {
37 testIdentity(conn);
38 }
39 testDataTypes(conn);
40 testBlob(conn);
41 testClob(conn);
42 testParameterMetaData(conn);
43 conn.close();
44 }
45
46 private void testSetObject(Connection conn) throws Exception {
47 Statement stat = conn.createStatement();
48 stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
49 PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
50 prep.setInt(1, 1);
51 prep.setObject(2, new Integer (11));
52 prep.setObject(3, null);
53 prep.execute();
54 prep.setInt(1, 2);
55 prep.setObject(2, new Integer (101), Types.OTHER);
56 prep.setObject(3, new Integer (103), Types.OTHER);
57 prep.execute();
58 PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID");
59 ResultSet rs = p2.executeQuery();
60 rs.next();
61 Object o = rs.getObject(2);
62 check(o instanceof byte[]);
63 check(rs.getObject(3) == null);
64 rs.next();
65 o = rs.getObject(2);
66 check(o instanceof byte[]);
67 o = rs.getObject(3);
68 check(o instanceof Integer );
69 check(((Integer )o).intValue(), 103);
70 checkFalse(rs.next());
71 stat.execute("DROP TABLE TEST");
72 }
73
74 private void testPreparedSubquery(Connection conn) throws Exception {
75 Statement s = conn.createStatement();
76 s.executeUpdate("CREATE TABLE TEST(ID IDENTITY, FLAG BIT)");
77 s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(0, FALSE)");
78 s.executeUpdate("INSERT INTO TEST(ID, FLAG) VALUES(1, FALSE)");
79 PreparedStatement u = conn.prepareStatement("SELECT ID, FLAG FROM TEST ORDER BY ID");
80 PreparedStatement p = conn.prepareStatement("UPDATE TEST SET FLAG=true WHERE ID=(SELECT ?)");
81 p.clearParameters();
82 p.setLong(1, 0);
83 check(p.executeUpdate(), 1);
84 p.clearParameters();
85 p.setLong(1, 1);
86 check(p.executeUpdate(), 1);
87 ResultSet rs = u.executeQuery();
88 check(rs.next());
89 check(rs.getInt(1), 0);
90 check(rs.getBoolean(2));
91 check(rs.next());
92 check(rs.getInt(1), 1);
93 check(rs.getBoolean(2));
94
95 p = conn.prepareStatement("SELECT * FROM TEST WHERE EXISTS(SELECT * FROM TEST WHERE ID=?)");
96 p.setInt(1, -1);
97 rs = p.executeQuery();
98 checkFalse(rs.next());
99 p.setInt(1, 1);
100 rs = p.executeQuery();
101 check(rs.next());
102
103 s.executeUpdate("DROP TABLE IF EXISTS TEST");
104 }
105
106 private void testParameterMetaData(Connection conn) throws Exception {
107 PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL");
108 ParameterMetaData pm = prep.getParameterMetaData();
109 check(pm.getParameterClassName(1), "java.lang.String");
110 check(pm.getParameterTypeName(1), "VARCHAR");
111 check(pm.getParameterCount(), 3);
112 check(pm.getParameterMode(1), ParameterMetaData.parameterModeIn);
113 check(pm.getParameterType(1), Types.VARCHAR);
114 check(pm.getPrecision(1) , 0);
115 check(pm.getScale(1) , 0);
116 check(pm.isNullable(1), ResultSetMetaData.columnNullableUnknown);
117 check(pm.isSigned(1), true);
118 try {
119 pm.getPrecision(0);
120 error("should fail");
121 } catch(SQLException e) {
122 }
124 try {
125 pm.getPrecision(4);
126 error("should fail");
127 } catch(SQLException e) {
128 }
130 prep.close();
131 try {
132 pm.getPrecision(1);
133 error("should fail");
134 } catch(SQLException e) {
135 }
137 }
138
139 private void testLikeIndex(Connection conn) throws Exception {
140 Statement stat = conn.createStatement();
141 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
142 stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
143 stat.execute("INSERT INTO TEST VALUES(2, 'World')");
144 stat.execute("create index idxname on test(name);");
145 PreparedStatement prep, prepExe;
146
147 prep = conn.prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?");
148 check(prep.getParameterMetaData().getParameterCount(), 1);
149 prepExe = conn.prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?");
150 prep.setString(1, "%orld");
151 prepExe.setString(1, "%orld");
152 ResultSet rs = prep.executeQuery();
153 rs.next();
154 String plan = rs.getString(1);
155 check(plan.indexOf("TABLE_SCAN") >= 0);
156 rs = prepExe.executeQuery();
157 rs.next();
158 check(rs.getString(2), "World");
159 checkFalse(rs.next());
160
161 prep.setString(1, "H%");
162 prepExe.setString(1, "H%");
163 rs = prep.executeQuery();
164 rs.next();
165 String plan1 = rs.getString(1);
166 check(plan1.indexOf("IDXNAME") >= 0);
167 rs = prepExe.executeQuery();
168 rs.next();
169 check(rs.getString(2), "Hello");
170 checkFalse(rs.next());
171
172 stat.execute("DROP TABLE IF EXISTS TEST");
173 }
174
175 private void testCasewhen(Connection conn) throws Exception {
176 Statement stat = conn.createStatement();
177 stat.execute("CREATE TABLE TEST(ID INT)");
178 stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
179 PreparedStatement prep;
180 ResultSet rs;
181 prep = conn.prepareStatement(
182 "EXPLAIN SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID"
183 );
184 prep.setInt(1, 1);
185 rs = prep.executeQuery();
186 rs.next();
187 String plan = rs.getString(1);
188 trace(plan);
189 rs.close();
190 prep = conn.prepareStatement(
191 "EXPLAIN SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID"
192 );
193 prep.setInt(1, 1);
194 rs = prep.executeQuery();
195 rs.next();
196 plan = rs.getString(1);
197 trace(plan);
198
199
200 prep = conn.prepareStatement(
201 "SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID"
202 );
203 prep.setInt(1, 1);
204 rs = prep.executeQuery();
205 check(rs.next());
206 check(rs.getInt(1), 1);
207 checkFalse(rs.next());
208
209 prep = conn.prepareStatement(
210 "SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID"
211 );
212 prep.setInt(1, 1);
213 rs = prep.executeQuery();
214 check(rs.next());
215 check(rs.getInt(1), 1);
216 checkFalse(rs.next());
217
218 prep = conn.prepareStatement("SELECT * FROM TEST WHERE ? IS NULL");
219 prep.setString(1, "Hello");
220 rs = prep.executeQuery();
221 checkFalse(rs.next());
222 try {
223 prep = conn.prepareStatement("select ? from dual union select ? from dual");
224 error("expected error");
225 } catch(SQLException e) {
226 checkNotGeneralException(e);
227 }
229 prep = conn.prepareStatement("select cast(? as varchar) from dual union select ? from dual");
230 check(prep.getParameterMetaData().getParameterCount(), 2);
231 prep.setString(1, "a");
232 prep.setString(2, "a");
233 rs = prep.executeQuery();
234 rs.next();
235 check(rs.getString(1), "a");
236 check(rs.getString(1), "a");
237 checkFalse(rs.next());
238
239 stat.execute("DROP TABLE TEST");
240 }
241
242 private void testSubquery(Connection conn) throws Exception {
243 Statement stat = conn.createStatement();
244 stat.execute("CREATE TABLE TEST(ID INT)");
245 stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
246 PreparedStatement prep = conn.prepareStatement(
247 "select x.id, ? from "
248 + "(select * from test where id in(?, ?)) x "
249 + "where x.id*2 <> ?");
250 check(prep.getParameterMetaData().getParameterCount(), 4);
251 prep.setInt(1, 0);
252 prep.setInt(2, 1);
253 prep.setInt(3, 2);
254 prep.setInt(4, 4);
255 ResultSet rs = prep.executeQuery();
256 rs.next();
257 check(rs.getInt(1), 1);
258 check(rs.getInt(2), 0);
259 checkFalse(rs.next());
260 stat.execute("DROP TABLE TEST");
261 }
262
263 private void testDataTypes(Connection conn) throws Exception {
264 conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
265 conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
266 Statement stat=conn.createStatement();
267 PreparedStatement prep;
268 ResultSet rs;
269 trace("Create tables");
270 stat.execute("CREATE TABLE T_INT(ID INT PRIMARY KEY,VALUE INT)");
271 stat.execute("CREATE TABLE T_VARCHAR(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
272 stat.execute("CREATE TABLE T_DECIMAL_0(ID INT PRIMARY KEY,VALUE DECIMAL(30,0))");
273 stat.execute("CREATE TABLE T_DECIMAL_10(ID INT PRIMARY KEY,VALUE DECIMAL(20,10))");
274 stat.execute("CREATE TABLE T_DATETIME(ID INT PRIMARY KEY,VALUE DATETIME)");
275 prep=conn.prepareStatement("INSERT INTO T_INT VALUES(?,?)", ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
276 prep.setInt(1,1);
277 prep.setInt(2,0);
278 prep.executeUpdate();
279 prep.setInt(1,2);
280 prep.setInt(2,-1);
281 prep.executeUpdate();
282 prep.setInt(1,3);
283 prep.setInt(2,3);
284 prep.executeUpdate();
285 prep.setInt(1,4);
286 prep.setNull(2,Types.INTEGER);
287 prep.executeUpdate();
288 prep.setInt(1,5);
289 prep.setBigDecimal(2,new java.math.BigDecimal ("0"));
290 prep.executeUpdate();
291 prep.setInt(1,6);
292 prep.setString(2,"-1");
293 prep.executeUpdate();
294 prep.setInt(1,7);
295 prep.setObject(2,new Integer (3));
296 prep.executeUpdate();
297 prep.setObject(1,"8");
298 prep.setObject(2,null);
300 prep.executeUpdate();
302 prep.setInt(1,9);
303 prep.setObject(2,new Integer (-4),Types.VARCHAR);
304 prep.executeUpdate();
305 prep.setInt(1,10);
306 prep.setObject(2,"5",Types.INTEGER);
307 prep.executeUpdate();
308 prep.setInt(1,11);
309 prep.setObject(2,null,Types.INTEGER);
310 prep.executeUpdate();
311 prep.setInt(1,12);
312 prep.setBoolean(2,true);
313 prep.executeUpdate();
314 prep.setInt(1,13);
315 prep.setBoolean(2,false);
316 prep.executeUpdate();
317 prep.setInt(1,14);
318 prep.setByte(2,(byte)-20);
319 prep.executeUpdate();
320 prep.setInt(1,15);
321 prep.setByte(2,(byte)100);
322 prep.executeUpdate();
323 prep.setInt(1,16);
324 prep.setShort(2,(short)30000);
325 prep.executeUpdate();
326 prep.setInt(1,17);
327 prep.setShort(2,(short)(-30000));
328 prep.executeUpdate();
329 prep.setInt(1,18);
330 prep.setLong(2,Integer.MAX_VALUE);
331 prep.executeUpdate();
332 prep.setInt(1,19);
333 prep.setLong(2,Integer.MIN_VALUE);
334 prep.executeUpdate();
335
336 check(stat.execute("SELECT * FROM T_INT ORDER BY ID"));
337 rs=stat.getResultSet();
338 testResultSetOrdered(rs,new String [][]{
339 {"1","0"},{"2","-1"},{"3","3"},{"4",null},{"5","0"},{"6","-1"},
340 {"7","3"},{"8",null},{"9","-4"},{"10","5"},{"11",null},{"12","1"},{"13","0"},
341 {"14","-20"},{"15","100"},{"16","30000"},{"17","-30000"},
342 {"18",""+Integer.MAX_VALUE},{"19",""+Integer.MIN_VALUE},
343 });
344
345 prep=conn.prepareStatement("INSERT INTO T_DECIMAL_0 VALUES(?,?)");
346 prep.setInt(1,1);
347 prep.setLong(2,Long.MAX_VALUE);
348 prep.executeUpdate();
349 prep.setInt(1,2);
350 prep.setLong(2,Long.MIN_VALUE);
351 prep.executeUpdate();
352 prep.setInt(1,3);
353 prep.setFloat(2,10);
354 prep.executeUpdate();
355 prep.setInt(1,4);
356 prep.setFloat(2,-20);
357 prep.executeUpdate();
358 prep.setInt(1,5);
359 prep.setFloat(2,30);
360 prep.executeUpdate();
361 prep.setInt(1,6);
362 prep.setFloat(2,-40);
363 prep.executeUpdate();
364
365 rs=stat.executeQuery("SELECT VALUE FROM T_DECIMAL_0 ORDER BY ID");
366 checkBigDecimal(rs,new String []{
367 ""+Long.MAX_VALUE,""+Long.MIN_VALUE,
368 "10","-20","30","-40"
369 });
370
371 stat.execute("CREATE TABLE TEST(ID INT)");
373 stat.execute("INSERT INTO TEST VALUES(1)");
374 prep=conn.prepareStatement("SELECT * FROM TEST");
375 prep.getMetaData();
377 check(prep.execute());
378 rs=prep.getResultSet();
379 checkFalse(prep.getMoreResults());
380 try {
381 rs.next();
383 error("getMoreResults didn't close this result set");
384 } catch(SQLException e) {
385 trace("no error - getMoreResults is supposed to close the result set");
386 }
387 check(prep.getUpdateCount()==-1);
388 prep=conn.prepareStatement("DELETE FROM TEST");
389 prep.executeUpdate();
390 checkFalse(prep.getMoreResults());
391 check(prep.getUpdateCount()==-1);
392 }
393
394 private void testObject(Connection conn) throws Exception {
395 Statement stat = conn.createStatement();
396 ResultSet rs;
397 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
398 stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
399 PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? FROM TEST");
400 prep.setObject(1, new Boolean (true));
401 prep.setObject(2, "Abc");
402 prep.setObject(3, new BigDecimal ("10.2"));
403 prep.setObject(4, new Byte ((byte)0xff));
404 prep.setObject(5, new Short (Short.MAX_VALUE));
405 prep.setObject(6, new Integer (Integer.MIN_VALUE));
406 prep.setObject(7, new Long (Long.MAX_VALUE));
407 prep.setObject(8, new Float (Float.MAX_VALUE));
408 prep.setObject(9, new Double (Double.MAX_VALUE));
409 prep.setObject(10, java.sql.Date.valueOf("2001-02-03"));
410 prep.setObject(11, java.sql.Time.valueOf("04:05:06"));
411 prep.setObject(12, java.sql.Timestamp.valueOf("2001-02-03 04:05:06.123456789"));
412 prep.setObject(13, new java.util.Date (java.sql.Date.valueOf("2001-02-03").getTime()));
413 prep.setObject(14, new byte[]{10, 20, 30});
414 prep.setObject(15, new Character ('a'));
415 prep.setObject(16, "2001-01-02", Types.DATE);
416 prep.setObject(17, "2001-01-02", Types.NULL);
418 prep.setObject(18, "3.725", Types.DOUBLE);
419 prep.setObject(19, "23:22:21", Types.TIME);
420 prep.setObject(20, new java.math.BigInteger ("12345"), Types.OTHER);
421 rs = prep.executeQuery();
422 rs.next();
423 check(rs.getObject(1).equals(new Boolean (true)));
424 check(rs.getObject(2).equals("Abc"));
425 check(rs.getObject(3).equals(new BigDecimal ("10.2")));
426 check(rs.getObject(4).equals(new Byte ((byte)0xff)));
427 check(rs.getObject(5).equals(new Short (Short.MAX_VALUE)));
428 check(rs.getObject(6).equals(new Integer (Integer.MIN_VALUE)));
429 check(rs.getObject(7).equals(new Long (Long.MAX_VALUE)));
430 check(rs.getObject(8).equals(new Float (Float.MAX_VALUE)));
431 check(rs.getObject(9).equals(new Double (Double.MAX_VALUE)));
432 check(rs.getObject(10).equals(java.sql.Date.valueOf("2001-02-03")));
433 check(rs.getObject(11).toString(), "04:05:06");
434 check(rs.getObject(11).equals(java.sql.Time.valueOf("04:05:06")));
435 check(rs.getObject(12).equals(java.sql.Timestamp.valueOf("2001-02-03 04:05:06.123456789")));
436 check(rs.getObject(13).equals(java.sql.Date.valueOf("2001-02-03")));
437 check((byte[])rs.getObject(14), new byte[]{10, 20, 30});
438 check(rs.getObject(15).equals(new Character ('a')));
439 check(rs.getObject(16).equals(java.sql.Date.valueOf("2001-01-02")));
440 check(rs.getObject(17) == null && rs.wasNull());
441 check(rs.getObject(18).equals(new Double (3.725)));
442 check(rs.getObject(19).equals(java.sql.Time.valueOf("23:22:21")));
443 check(rs.getObject(20).equals(new java.math.BigInteger ("12345")));
444
445
446
453
454 stat.execute("DROP TABLE TEST");
455
456 }
457
458 private void testIdentity(Connection conn) throws Exception {
459 Statement stat = conn.createStatement();
460 stat.execute("CREATE SEQUENCE SEQ");
461 stat.execute("CREATE TABLE TEST(ID INT)");
462 PreparedStatement prep;
463 prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)");
464 prep.execute();
465 ResultSet rs = prep.getGeneratedKeys();
466 rs.next();
467 check(rs.getInt(1), 1);
468 checkFalse(rs.next());
469 prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
470 prep.execute();
471 rs = prep.getGeneratedKeys();
472 rs.next();
473 check(rs.getInt(1), 2);
474 checkFalse(rs.next());
475 prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[]{1});
476 prep.execute();
477 rs = prep.getGeneratedKeys();
478 rs.next();
479 check(rs.getInt(1), 3);
480 checkFalse(rs.next());
481 prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String []{"ID"});
482 prep.execute();
483 rs = prep.getGeneratedKeys();
484 rs.next();
485 check(rs.getInt(1), 4);
486 checkFalse(rs.next());
487 stat.execute("DROP TABLE TEST");
488 }
489
490 int getLength() throws Exception {
491 return getSize(LOB_SIZE, LOB_SIZE_BIG);
492 }
493
494 void testBlob(Connection conn) throws Exception {
495 trace("testBlob");
496 Statement stat=conn.createStatement();
497 PreparedStatement prep;
498 ResultSet rs;
499 stat.execute("CREATE TABLE T_BLOB(ID INT PRIMARY KEY,V1 BLOB,V2 BLOB)");
500 trace("table created");
501 prep=conn.prepareStatement("INSERT INTO T_BLOB VALUES(?,?,?)");
502
503 prep.setInt(1,1);
504 prep.setBytes(2,null);
505 prep.setNull(3,Types.BINARY);
506 prep.executeUpdate();
507
508 prep.setInt(1,2);
509 prep.setBinaryStream(2,null,0);
510 prep.setNull(3,Types.BLOB);
511 prep.executeUpdate();
512
513 int length = getLength();
514 byte[] big1=new byte[length];
515 byte[] big2=new byte[length];
516 for(int i=0;i<big1.length;i++) {
517 big1[i]=(byte)((i*11)%254);
518 big2[i]=(byte)((i*17)%251);
519 }
520
521 prep.setInt(1,3);
522 prep.setBytes(2,big1);
523 prep.setBytes(3,big2);
524 prep.executeUpdate();
525
526 prep.setInt(1,4);
527 ByteArrayInputStream buffer;
528 buffer=new ByteArrayInputStream (big2);
529 prep.setBinaryStream(2,buffer,big2.length);
530 buffer=new ByteArrayInputStream (big1);
531 prep.setBinaryStream(3,buffer,big1.length);
532 prep.executeUpdate();
533 try {
534 buffer.close();
535 trace("buffer not closed");
536 } catch(IOException e) {
537 trace("buffer closed");
538 }
539
540 prep.setInt(1,5);
541 buffer=new ByteArrayInputStream (big2);
542 prep.setObject(2,buffer, Types.BLOB, 0);
543 buffer=new ByteArrayInputStream (big1);
544 prep.setObject(3,buffer);
545 prep.executeUpdate();
546
547 rs=stat.executeQuery("SELECT ID, V1, V2 FROM T_BLOB ORDER BY ID");
548
549 rs.next();
550 check(rs.getInt(1), 1);
551 check(rs.getBytes(2)==null && rs.wasNull());
552 check(rs.getBytes(3)==null && rs.wasNull());
553
554 rs.next();
555 check(rs.getInt(1), 2);
556 check(rs.getBytes(2)==null && rs.wasNull());
557 check(rs.getBytes(3)==null && rs.wasNull());
558
559 rs.next();
560 check(rs.getInt(1), 3);
561 check(rs.getBytes(2),big1);
562 check(rs.getBytes(3),big2);
563
564 rs.next();
565 check(rs.getInt(1), 4);
566 check(rs.getBytes(2),big2);
567 check(rs.getBytes(3),big1);
568
569 rs.next();
570 check(rs.getInt(1), 5);
571 check(rs.getBytes(2),big2);
572 check(rs.getBytes(3),big1);
573
574 checkFalse(rs.next());
575 }
576
577 void testClob(Connection conn) throws Exception {
578 trace("testClob");
579 Statement stat=conn.createStatement();
580 PreparedStatement prep;
581 ResultSet rs;
582 stat.execute("CREATE TABLE T_CLOB(ID INT PRIMARY KEY,V1 CLOB,V2 CLOB)");
583 StringBuffer asciibuffer=new StringBuffer ();
584 int len = getLength();
585 for(int i=0;i<len;i++) {
586 asciibuffer.append((char)('a'+(i%20)));
587 }
588 String ascii1=asciibuffer.toString();
589 String ascii2="Number2 "+ascii1;
590 prep=conn.prepareStatement("INSERT INTO T_CLOB VALUES(?,?,?)");
591
592 prep.setInt(1,1);
593 prep.setString(2,null);
594 prep.setNull(3,Types.CLOB);
595 prep.executeUpdate();
596
597 prep.clearParameters();
598 prep.setInt(1,2);
599 prep.setAsciiStream(2,null,0);
600 prep.setCharacterStream(3,null,0);
601 prep.executeUpdate();
602
603 prep.clearParameters();
604 prep.setInt(1,3);
605 prep.setCharacterStream(2, new StringReader (ascii1), ascii1.length());
606 prep.setCharacterStream(3, null, 0);
607 prep.setAsciiStream(3, new ByteArrayInputStream (ascii2.getBytes()), ascii2.length());
608 prep.executeUpdate();
609
610 prep.clearParameters();
611 prep.setInt(1,4);
612 prep.setNull(2, Types.CLOB);
613 prep.setString(2,ascii2);
614 prep.setCharacterStream(3,null,0);
615 prep.setNull(3,Types.CLOB);
616 prep.setString(3,ascii1);
617 prep.executeUpdate();
618
619 prep.clearParameters();
620 prep.setInt(1,5);
621 prep.setObject(2, new StringReader (ascii1));
622 prep.setObject(3, new StringReader (ascii2), Types.CLOB, 0);
623 prep.executeUpdate();
624
625 rs=stat.executeQuery("SELECT ID, V1, V2 FROM T_CLOB ORDER BY ID");
626
627 rs.next();
628 check(rs.getInt(1), 1);
629 check(rs.getCharacterStream(2)==null && rs.wasNull());
630 check(rs.getAsciiStream(3)==null && rs.wasNull());
631
632 rs.next();
633 check(rs.getInt(1), 2);
634 check(rs.getString(2)==null && rs.wasNull());
635 check(rs.getString(3)==null && rs.wasNull());
636
637 rs.next();
638 check(rs.getInt(1), 3);
639 check(rs.getString(2), ascii1);
640 check(rs.getString(3), ascii2);
641
642 rs.next();
643 check(rs.getInt(1), 4);
644 check(rs.getString(2), ascii2);
645 check(rs.getString(3), ascii1);
646
647 rs.next();
648 check(rs.getInt(1), 5);
649 check(rs.getString(2), ascii1);
650 check(rs.getString(3), ascii2);
651
652 checkFalse(rs.next());
653 check(prep.getWarnings()==null);
654 prep.clearWarnings();
655 check(prep.getWarnings()==null);
656 check(conn==prep.getConnection());
657 }
658
659 void checkBigDecimal(ResultSet rs,String [] value) throws Exception {
660 for(int i=0;i<value.length;i++) {
661 String v=value[i];
662 check(rs.next());
663 java.math.BigDecimal x=rs.getBigDecimal(1);
664 trace("v="+v+" x="+x);
665 if(v==null) {
666 check(x==null);
667 } else {
668 check(x.compareTo(new java.math.BigDecimal (v))==0);
669 }
670 }
671 check(!rs.next());
672 }
673
674 }
675 |
Popular Tags |
Java API By Example, From Geeks To Geeks.