KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > h2 > test > jdbc > TestPreparedStatement


1 /*
2  * Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
3  * Initial Developer: H2 Group
4  */

5 package org.h2.test.jdbc;
6
7 import java.io.ByteArrayInputStream JavaDoc;
8 import java.io.IOException JavaDoc;
9 import java.io.StringReader JavaDoc;
10 import java.math.BigDecimal JavaDoc;
11 import java.sql.Connection JavaDoc;
12 import java.sql.ParameterMetaData JavaDoc;
13 import java.sql.PreparedStatement JavaDoc;
14 import java.sql.ResultSet JavaDoc;
15 import java.sql.ResultSetMetaData JavaDoc;
16 import java.sql.SQLException JavaDoc;
17 import java.sql.Statement JavaDoc;
18 import java.sql.Types JavaDoc;
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 JavaDoc {
27         
28         deleteDb("preparedStatement");
29         Connection JavaDoc 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 JavaDoc conn) throws Exception JavaDoc {
47         Statement JavaDoc stat = conn.createStatement();
48         stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
49         PreparedStatement JavaDoc prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
50         prep.setInt(1, 1);
51         prep.setObject(2, new Integer JavaDoc(11));
52         prep.setObject(3, null);
53         prep.execute();
54         prep.setInt(1, 2);
55         prep.setObject(2, new Integer JavaDoc(101), Types.OTHER);
56         prep.setObject(3, new Integer JavaDoc(103), Types.OTHER);
57         prep.execute();
58         PreparedStatement JavaDoc p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID");
59         ResultSet JavaDoc rs = p2.executeQuery();
60         rs.next();
61         Object JavaDoc 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 JavaDoc);
69         check(((Integer JavaDoc)o).intValue(), 103);
70         checkFalse(rs.next());
71         stat.execute("DROP TABLE TEST");
72     }
73
74     private void testPreparedSubquery(Connection JavaDoc conn) throws Exception JavaDoc {
75         Statement JavaDoc 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 JavaDoc u = conn.prepareStatement("SELECT ID, FLAG FROM TEST ORDER BY ID");
80         PreparedStatement JavaDoc 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 JavaDoc 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 JavaDoc conn) throws Exception JavaDoc {
107         PreparedStatement JavaDoc prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL");
108         ParameterMetaData JavaDoc 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 JavaDoc e) {
122             // ok
123
}
124         try {
125             pm.getPrecision(4);
126             error("should fail");
127         } catch(SQLException JavaDoc e) {
128             // ok
129
}
130         prep.close();
131         try {
132             pm.getPrecision(1);
133             error("should fail");
134         } catch(SQLException JavaDoc e) {
135             // ok
136
}
137     }
138     
139     private void testLikeIndex(Connection JavaDoc conn) throws Exception JavaDoc {
140         Statement JavaDoc 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 JavaDoc 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 JavaDoc rs = prep.executeQuery();
153         rs.next();
154         String JavaDoc 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 JavaDoc 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 JavaDoc conn) throws Exception JavaDoc {
176         Statement JavaDoc stat = conn.createStatement();
177         stat.execute("CREATE TABLE TEST(ID INT)");
178         stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
179         PreparedStatement JavaDoc prep;
180         ResultSet JavaDoc 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 JavaDoc 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 JavaDoc e) {
226             checkNotGeneralException(e);
227             // ok
228
}
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 JavaDoc conn) throws Exception JavaDoc {
243         Statement JavaDoc stat = conn.createStatement();
244         stat.execute("CREATE TABLE TEST(ID INT)");
245         stat.execute("INSERT INTO TEST VALUES(1),(2),(3)");
246         PreparedStatement JavaDoc 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 JavaDoc 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 JavaDoc conn) throws Exception JavaDoc {
264         conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
265         conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
266         Statement JavaDoc stat=conn.createStatement();
267         PreparedStatement JavaDoc prep;
268         ResultSet JavaDoc 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 JavaDoc("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 JavaDoc(3));
296         prep.executeUpdate();
297         prep.setObject(1,"8");
298         // should throw an exception
299
prep.setObject(2,null);
300         // some databases don't allow calling setObject with null (no data type)
301
prep.executeUpdate();
302         prep.setInt(1,9);
303         prep.setObject(2,new Integer JavaDoc(-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 JavaDoc[][]{
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 JavaDoc[]{
367         ""+Long.MAX_VALUE,""+Long.MIN_VALUE,
368         "10","-20","30","-40"
369         });
370
371         // getMoreResults
372
stat.execute("CREATE TABLE TEST(ID INT)");
373         stat.execute("INSERT INTO TEST VALUES(1)");
374         prep=conn.prepareStatement("SELECT * FROM TEST");
375         // just to check if it doesn't throw an exception - it may be null
376
prep.getMetaData();
377         check(prep.execute());
378         rs=prep.getResultSet();
379         checkFalse(prep.getMoreResults());
380         try {
381             // supposed to be closed now
382
rs.next();
383             error("getMoreResults didn't close this result set");
384         } catch(SQLException JavaDoc 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 JavaDoc conn) throws Exception JavaDoc {
395         Statement JavaDoc stat = conn.createStatement();
396         ResultSet JavaDoc 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 JavaDoc prep = conn.prepareStatement("SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? FROM TEST");
400         prep.setObject(1, new Boolean JavaDoc(true));
401         prep.setObject(2, "Abc");
402         prep.setObject(3, new BigDecimal JavaDoc("10.2"));
403         prep.setObject(4, new Byte JavaDoc((byte)0xff));
404         prep.setObject(5, new Short JavaDoc(Short.MAX_VALUE));
405         prep.setObject(6, new Integer JavaDoc(Integer.MIN_VALUE));
406         prep.setObject(7, new Long JavaDoc(Long.MAX_VALUE));
407         prep.setObject(8, new Float JavaDoc(Float.MAX_VALUE));
408         prep.setObject(9, new Double JavaDoc(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 JavaDoc(java.sql.Date.valueOf("2001-02-03").getTime()));
413         prep.setObject(14, new byte[]{10, 20, 30});
414         prep.setObject(15, new Character JavaDoc('a'));
415         prep.setObject(16, "2001-01-02", Types.DATE);
416         // converting to null seems strange...
417
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 JavaDoc("12345"), Types.OTHER);
421         rs = prep.executeQuery();
422         rs.next();
423         check(rs.getObject(1).equals(new Boolean JavaDoc(true)));
424         check(rs.getObject(2).equals("Abc"));
425         check(rs.getObject(3).equals(new BigDecimal JavaDoc("10.2")));
426         check(rs.getObject(4).equals(new Byte JavaDoc((byte)0xff)));
427         check(rs.getObject(5).equals(new Short JavaDoc(Short.MAX_VALUE)));
428         check(rs.getObject(6).equals(new Integer JavaDoc(Integer.MIN_VALUE)));
429         check(rs.getObject(7).equals(new Long JavaDoc(Long.MAX_VALUE)));
430         check(rs.getObject(8).equals(new Float JavaDoc(Float.MAX_VALUE)));
431         check(rs.getObject(9).equals(new Double JavaDoc(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 JavaDoc('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 JavaDoc(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 JavaDoc("12345")));
444         
445         
446 // } else if(x instanceof java.io.Reader) {
447
// return session.createLob(Value.CLOB, TypeConverter.getInputStream((java.io.Reader)x), 0);
448
// } else if(x instanceof java.io.InputStream) {
449
// return session.createLob(Value.BLOB, (java.io.InputStream)x, 0);
450
// } else {
451
// return ValueBytes.get(TypeConverter.serialize(x));
452

453         
454         stat.execute("DROP TABLE TEST");
455         
456     }
457     
458     private void testIdentity(Connection JavaDoc conn) throws Exception JavaDoc {
459         Statement JavaDoc stat = conn.createStatement();
460         stat.execute("CREATE SEQUENCE SEQ");
461         stat.execute("CREATE TABLE TEST(ID INT)");
462         PreparedStatement JavaDoc prep;
463         prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)");
464         prep.execute();
465         ResultSet JavaDoc 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 JavaDoc[]{"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 JavaDoc {
491         return getSize(LOB_SIZE, LOB_SIZE_BIG);
492     }
493
494     void testBlob(Connection JavaDoc conn) throws Exception JavaDoc {
495         trace("testBlob");
496         Statement JavaDoc stat=conn.createStatement();
497         PreparedStatement JavaDoc prep;
498         ResultSet JavaDoc 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 JavaDoc buffer;
528         buffer=new ByteArrayInputStream JavaDoc(big2);
529         prep.setBinaryStream(2,buffer,big2.length);
530         buffer=new ByteArrayInputStream JavaDoc(big1);
531         prep.setBinaryStream(3,buffer,big1.length);
532         prep.executeUpdate();
533         try {
534             buffer.close();
535             trace("buffer not closed");
536         } catch(IOException JavaDoc e) {
537             trace("buffer closed");
538         }
539
540         prep.setInt(1,5);
541         buffer=new ByteArrayInputStream JavaDoc(big2);
542         prep.setObject(2,buffer, Types.BLOB, 0);
543         buffer=new ByteArrayInputStream JavaDoc(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 JavaDoc conn) throws Exception JavaDoc {
578         trace("testClob");
579         Statement JavaDoc stat=conn.createStatement();
580         PreparedStatement JavaDoc prep;
581         ResultSet JavaDoc rs;
582         stat.execute("CREATE TABLE T_CLOB(ID INT PRIMARY KEY,V1 CLOB,V2 CLOB)");
583         StringBuffer JavaDoc asciibuffer=new StringBuffer JavaDoc();
584         int len = getLength();
585         for(int i=0;i<len;i++) {
586             asciibuffer.append((char)('a'+(i%20)));
587         }
588         String JavaDoc ascii1=asciibuffer.toString();
589         String JavaDoc 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 JavaDoc(ascii1), ascii1.length());
606         prep.setCharacterStream(3, null, 0);
607         prep.setAsciiStream(3, new ByteArrayInputStream JavaDoc(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 JavaDoc(ascii1));
622         prep.setObject(3, new StringReader JavaDoc(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 JavaDoc rs,String JavaDoc[] value) throws Exception JavaDoc {
660         for(int i=0;i<value.length;i++) {
661             String JavaDoc v=value[i];
662             check(rs.next());
663             java.math.BigDecimal JavaDoc 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 JavaDoc(v))==0);
669             }
670         }
671         check(!rs.next());
672     }
673
674 }
675
Popular Tags