Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbc4 > ResultSetTest


1 /*
2  
3    Derby - Class ResultSetTest
4  
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to you under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11  
12       http://www.apache.org/licenses/LICENSE-2.0
13  
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19  
20  */
21
22 package org.apache.derbyTesting.functionTests.tests.jdbc4;
23
24 import javax.xml.transform.Result ;
25 import junit.extensions.TestSetup;
26 import junit.framework.*;
27
28 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
29 import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
30
31 import java.io.*;
32 import java.sql.*;
33
34 /**
35  * Tests of JDBC4 features in ResultSet.
36  *
37  * Some utility methods have been introduced for the updateXXX test-methods.
38  * This test also makes use of a TestSetup wrapper to perform one-time
39  * setup and teardown for the whole suite.
40  */
41 public class ResultSetTest
42     extends BaseJDBCTestCase {
43
44     private static final byte[] BYTES1 = {
45             0x65, 0x66, 0x67, 0x68, 0x69,
46             0x69, 0x68, 0x67, 0x66, 0x65
47         };
48
49     private static final byte[] BYTES2 = {
50             0x69, 0x68, 0x67, 0x66, 0x65,
51             0x65, 0x66, 0x67, 0x68, 0x69
52         };
53
54     /**
55      * Key used to identify inserted rows.
56      * Use method <code>requestKey</code> to obtain it.
57      **/
58     private static int insertKey = 0;
59
60     /** Statement used to obtain default resultset. */
61     private Statement stmt = null;
62     /** Default resultset used by the tests. */
63     private ResultSet rs = null;
64     /** Default row identifier used by the tests. */
65     private int key = -1;
66
67     /**
68      * Create test with given name.
69      *
70      * @param name name of the test.
71      */
72     public ResultSetTest(String name) {
73         super(name);
74     }
75
76     protected void setUp()
77         throws SQLException {
78         key = requestKey();
79         stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY,
80                 ResultSet.CONCUR_UPDATABLE);
81
82         rs = stmt.executeQuery("SELECT * FROM SYS.SYSTABLES");
83
84         // Position on first result.
85 rs.next();
86     }
87
88     protected void tearDown()
89         throws Exception {
90
91         if (rs != null) {
92             rs.close();
93         }
94         if (stmt != null) {
95             stmt.close();
96         }
97
98         super.tearDown();
99     }
100
101     public void testGetNCharacterStreamIntNotImplemented()
102         throws SQLException {
103         try {
104             rs.getNCharacterStream(1);
105             fail("ResultSet.getNCharacterStream(int) " +
106                  "should not be implemented");
107         } catch (SQLFeatureNotSupportedException sfnse) {
108             // We are fine, do nothing.
109 }
110     }
111    
112     public void testGetNCharaterStreamStringNotImplemented()
113         throws SQLException {
114         try {
115             rs.getNCharacterStream("some-column-name");
116             fail("ResultSet.getNCharacterStream(String) " +
117                  "should not be implemented");
118         } catch (SQLFeatureNotSupportedException sfnse) {
119             // We are fine, do nothing.
120 }
121     }
122
123     public void testGetNClobNotIntImplemented()
124         throws SQLException {
125         try {
126             rs.getNClob(1);
127             fail("ResultSet.getNClob(int) " +
128                  "should not be implemented");
129         } catch (SQLFeatureNotSupportedException sfnse) {
130             // We are fine, do nothing.
131 }
132     }
133
134     public void testGetNClobStringNotImplemented()
135         throws SQLException {
136         try {
137             rs.getNClob("some-column-name");
138             fail("ResultSet.getNClob(String) " +
139                  "should not be implemented");
140         } catch (SQLFeatureNotSupportedException sfnse) {
141             // We are fine, do nothing.
142 }
143     }
144     
145     public void testGetNStringIntNotImplemented()
146         throws SQLException {
147         try {
148             rs.getNString(1);
149             fail("ResultSet.getNString(int) " +
150                  "should not be implemented");
151         } catch (SQLFeatureNotSupportedException sfnse) {
152             // We are fine, do nothing.
153 }
154     }
155     
156     public void testGetNStringStringNotImplemented()
157         throws SQLException {
158         try {
159             rs.getNString("some-column-name");
160             fail("ResultSet.getNString(String) " +
161                  "should not be implemented");
162         } catch (SQLFeatureNotSupportedException sfnse) {
163             // We are fine, do nothing.
164 }
165     }
166
167     public void testGetSQLXMLIntNotImplemented()
168         throws SQLException {
169         try {
170             rs.getSQLXML(1);
171             fail("ResultSet.getSQLXML(int) " +
172                  "should not be implemented");
173         } catch (SQLFeatureNotSupportedException sfnse) {
174             // We are fine, do nothing.
175 }
176     }
177
178     public void testGetSQLXMLStringNotImplemented()
179         throws SQLException {
180         try {
181             rs.getSQLXML("some-column-name");
182             fail("ResultSet.getSQLXML(String) " +
183                  "should not be implemented");
184         } catch (SQLFeatureNotSupportedException sfnse) {
185             // We are fine, do nothing.
186 }
187     }
188
189     public void testUpdateNCharacterStreamIntNotImplemented()
190         throws SQLException {
191         try {
192             rs.updateNCharacterStream(1, null, 0);
193             fail("ResultSet.updateNCharacterStream(int, Reader, int) " +
194                  "should not be implemented");
195         } catch (SQLFeatureNotSupportedException sfnse) {
196             // We are fine, do nothing.
197 }
198     }
199
200     public void testUpdateNCharacterStreamIntLengthLessNotImplemented()
201         throws SQLException {
202         try {
203             rs.updateNCharacterStream(1, null);
204             fail("ResultSet.updateNCharacterStream(int, Reader) " +
205                  "should not be implemented");
206         } catch (SQLFeatureNotSupportedException sfnse) {
207             // We are fine, do nothing.
208 }
209     }
210     public void testUpdateNCharacterStreamStringNotImplemented()
211         throws SQLException {
212         try {
213             rs.updateNCharacterStream("some-column-name", null, 0);
214             fail("ResultSet.updateNCharacterStream(String, Reader, 0) " +
215                  "should not be implemented");
216         } catch (SQLFeatureNotSupportedException sfnse) {
217             // We are fine, do nothing.
218 }
219     }
220
221     public void testUpdateNCharacterStreamStringLengthlessNotImplemented()
222         throws SQLException {
223         try {
224             rs.updateNCharacterStream("some-column-name", null);
225             fail("ResultSet.updateNCharacterStream(String, Reader) " +
226                  "should not be implemented");
227         } catch (SQLFeatureNotSupportedException sfnse) {
228             // We are fine, do nothing.
229 }
230     }
231
232     public void testUpdateNClobIntNotImplemented()
233         throws SQLException {
234         try {
235             rs.updateNClob(1, (NClob)null);
236             fail("ResultSet.updateNClob(int, NClob) " +
237                  "should not be implemented");
238         } catch (SQLFeatureNotSupportedException sfnse) {
239             // We are fine, do nothing.
240 }
241     }
242
243     public void testUpdateNClobIntLengthlessNotImplemented()
244         throws SQLException {
245         try {
246             rs.updateNClob(1, (Reader)null);
247             fail("ResultSet.updateNClob(int, Reader) " +
248                  "should not be implemented");
249         } catch (SQLFeatureNotSupportedException sfnse) {
250             // We are fine, do nothing.
251 }
252     }
253
254     public void testUpdateNClobStringNotImplemented()
255         throws SQLException {
256         try {
257             rs.updateNClob("some-column-name", (NClob)null);
258             fail("ResultSet.updateNClob(String, NClob) " +
259                  "should not be implemented");
260         } catch (SQLFeatureNotSupportedException sfnse) {
261             // We are fine, do nothing.
262 }
263     }
264     
265     public void testUpdateNClobStringLengthlessNotImplemented()
266         throws SQLException {
267         try {
268             rs.updateNClob("some-column-name", (Reader)null);
269             fail("ResultSet.updateNClob(String, Reader) " +
270                  "should not be implemented");
271         } catch (SQLFeatureNotSupportedException sfnse) {
272             // We are fine, do nothing.
273 }
274     }
275
276     public void testUpdateNStringIntNotImplemented()
277         throws SQLException {
278         try {
279             rs.updateNString(1, null);
280             fail("ResultSet.updateNString(int, String) " +
281                  "should not be implemented");
282         } catch (SQLFeatureNotSupportedException sfnse) {
283             // We are fine, do nothing.
284 }
285     }
286     
287     public void testUpdateNStringStringNotImplemented()
288         throws SQLException {
289         try {
290             rs.updateNString("some-column-name", null);
291             fail("ResultSet.updateNString(String, String) " +
292                  "should not be implemented");
293         } catch (SQLFeatureNotSupportedException sfnse) {
294             // We are fine, do nothing.
295 }
296     }
297
298     public void testUpdateSQLXMLIntNotImplemented()
299         throws SQLException {
300         try {
301             rs.updateSQLXML(1, null);
302             fail("ResultSet.updateSQLXML(int, SQLXML) " +
303                  "should not be implemented");
304         } catch (SQLFeatureNotSupportedException sfnse) {
305             // We are fine, do nothing.
306 }
307     }
308
309     public void testUpdateSQLXMLStringNotImplemented()
310         throws SQLException {
311         try {
312             rs.updateSQLXML("some-column-name", null);
313             fail("ResultSet.updateSQLXML(String, SQLXML) " +
314                  "should not be implemented");
315         } catch (SQLFeatureNotSupportedException sfnse) {
316             // We are fine, do nothing.
317 }
318     }
319     
320     /**
321      * This methods tests the ResultSet interface method
322      * updateBinaryStream
323      *
324      * @throws SQLException if some error occurs while calling the method
325      */
326
327     public void testUpdateBinaryStream()
328     throws Exception {
329         //Byte array in which the returned bytes from
330 //the Database after the update are stored. This
331 //array is then checked to determine if it
332 //has the same elements of the Byte array used for
333 //the update operation
334
335         byte[] bytes_ret = new byte[10];
336
337         //Input Stream inserted initially
338 InputStream is = new java.io.ByteArrayInputStream (BYTES1);
339
340         //InputStream that is used for update
341 InputStream is_for_update = new
342                 java.io.ByteArrayInputStream (BYTES2);
343
344         //Prepared Statement used to insert the data
345 PreparedStatement ps_sb = prep("dLongBit");
346         ps_sb.setInt(1,key);
347         ps_sb.setBinaryStream(2,is,BYTES1.length);
348         ps_sb.executeUpdate();
349         ps_sb.close();
350
351         //Update operation
352 //use a different ResultSet variable so that the
353 //other tests can go on unimpacted
354
355         ResultSet rs1 = fetchUpd("dLongBit", key);
356         rs1.next();
357         rs1.updateBinaryStream(1,is_for_update,(int)BYTES2.length);
358         rs1.updateRow();
359         rs1.close();
360
361         //Query to see whether the data that has been updated
362 //using the updateBinaryStream method is the same
363 //data that we expected
364
365         rs1 = fetch("dLongBit", key);
366         rs1.next();
367         InputStream is_ret = rs1.getBinaryStream(1);
368
369         is_ret.read(bytes_ret);
370         is_ret.close();
371
372         for(int i=0;i<BYTES2.length;i++) {
373             assertEquals("Error in updateBinaryStream",BYTES2[i],bytes_ret[i]);
374         }
375         rs1.close();
376     }
377
378     /**
379      * This methods tests the ResultSet interface method
380      * updateAsciiStream
381      *
382      * @throws SQLException if some error occurs while calling the method
383      */
384
385     public void testUpdateAsciiStream()
386     throws Exception {
387         //create the table
388 stmt.execute("create table UpdateTestTable_ResultSet (sno int, " +
389                 "datacol LONG VARCHAR)");
390
391         //Byte array in which the returned bytes from
392 //the Database after the update are stored. This
393 //array is then checked to determine if it
394 //has the same elements of the Byte array used for
395 //the update operation
396
397         byte[] bytes_ret = new byte[10];
398
399         //Input Stream inserted initially
400 InputStream is = new java.io.ByteArrayInputStream (BYTES1);
401
402         //InputStream that is used for update
403 InputStream is_for_update = new
404                 java.io.ByteArrayInputStream (BYTES2);
405
406         //Prepared Statement used to insert the data
407 PreparedStatement ps_sb = prepareStatement
408                 ("insert into UpdateTestTable_ResultSet values(?,?)");
409         ps_sb.setInt(1,1);
410         ps_sb.setAsciiStream(2,is,BYTES1.length);
411         ps_sb.executeUpdate();
412         ps_sb.close();
413
414         //Update operation
415 //use a different ResultSet variable so that the
416 //other tests can go on unimpacted
417
418         ResultSet rs1 = stmt.executeQuery
419                 ("select * from UpdateTestTable_ResultSet for update");
420         rs1.next();
421         rs1.updateAsciiStream(2,is_for_update,(int)BYTES2.length);
422         rs1.updateRow();
423         rs1.close();
424
425         //Query to see whether the data that has been updated
426 //using the updateAsciiStream method is the same
427 //data that we expected
428
429         rs1 = stmt.executeQuery
430                 ("select * from UpdateTestTable_ResultSet");
431         rs1.next();
432         InputStream is_ret = rs1.getAsciiStream(2);
433
434         is_ret.read(bytes_ret);
435         is_ret.close();
436
437         for(int i=0;i<BYTES2.length;i++) {
438             assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
439         }
440         rs1.close();
441         //delete the table
442 stmt .execute("drop table UpdateTestTable_ResultSet");
443     }
444
445      /**
446      * This methods tests the ResultSet interface method
447      * updateCharacterStream
448      *
449      * @throws SQLException if some error occurs while calling the method
450      */
451
452     public void testUpdateCharacterStream()
453     throws Exception {
454         String str = "Test data";
455         String str_for_update = "Test data used for update";
456
457         StringReader r = new StringReader(str);
458         StringReader r_for_update = new StringReader
459                 (str_for_update);
460
461         //Prepared Statement used to insert the data
462 PreparedStatement ps_sb = prep("dLongVarchar");
463         ps_sb.setInt(1,key);
464         ps_sb.setCharacterStream(2,r,str.length());
465         ps_sb.executeUpdate();
466         ps_sb.close();
467
468         //Update operation
469 //use a different ResultSet variable so that the
470 //other tests can go on unimpacted
471 ResultSet rs1 = fetchUpd("dLongVarchar", key);
472         rs1.next();
473         rs1.updateCharacterStream(1,r_for_update,str_for_update.length());
474         rs1.updateRow();
475         rs1.close();
476
477         //Query to see whether the data that has been updated
478 //using the updateAsciiStream method is the same
479 //data that we expected
480
481         rs1 = fetch("dLongVarchar", key);
482         rs1.next();
483
484         StringReader r_ret = (StringReader)rs1.getCharacterStream(1);
485
486         char [] c_ret = new char[str_for_update.length()];
487
488         r_ret.read(c_ret);
489
490         String str_ret = new String (c_ret);
491
492         assertEquals("Error in updateCharacterStream" +
493             str_ret,str_for_update,str_ret);
494
495
496         rs1.close();
497     }
498
499     /**
500      * This methods tests the ResultSet interface method
501      * updateBinaryStream
502      *
503      * @throws SQLException if some error occurs while calling the method
504      */
505
506     public void testUpdateBinaryStreamStringParameterName()
507     throws Exception {
508         //Byte array in which the returned bytes from
509 //the Database after the update are stored. This
510 //array is then checked to determine if it
511 //has the same elements of the Byte array used for
512 //the update operation
513
514         byte[] bytes_ret = new byte[10];
515
516         //Input Stream inserted initially
517 InputStream is = new java.io.ByteArrayInputStream (BYTES1);
518
519         //InputStream that is used for update
520 InputStream is_for_update = new
521                 java.io.ByteArrayInputStream (BYTES2);
522
523         //Prepared Statement used to insert the data
524 PreparedStatement ps_sb = prep("dLongBit");
525         ps_sb.setInt(1, key);
526         ps_sb.setBinaryStream(2,is,BYTES1.length);
527         ps_sb.executeUpdate();
528         ps_sb.close();
529
530         //Update operation
531 //Update operation
532 //use a different ResultSet variable so that the
533 //other tests can go on unimpacted
534
535         ResultSet rs1 = fetchUpd("dLongBit", key);
536         rs1.next();
537         rs1.updateBinaryStream("dLongBit",is_for_update,(int)BYTES2.length);
538         rs1.updateRow();
539         rs1.close();
540
541         //Query to see whether the data that has been updated
542 //using the updateBinaryStream method is the same
543 //data that we expected
544
545         rs1 = fetch("dLongBit", key);
546         rs1.next();
547         InputStream is_ret = rs1.getBinaryStream(1);
548
549         is_ret.read(bytes_ret);
550         is_ret.close();
551
552         for(int i=0;i<BYTES2.length;i++) {
553             assertEquals("Error in updateBinaryStream",BYTES2[i],bytes_ret[i]);
554         }
555         rs1.close();
556     }
557
558     /**
559      * Test <code>updateBinaryStream</code> on a BINARY column, without
560      * specifying length of inputstream.
561      */
562     public void testUpdateBinaryStreamLengthless()
563             throws IOException, SQLException {
564         InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
565         // InputStream used for update.
566 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
567
568         //Prepared Statement used to insert the data
569 PreparedStatement ps_sb = prep("dLongBit");
570         ps_sb.setInt(1, key);
571         ps_sb.setBinaryStream(2, is1);
572         ps_sb.executeUpdate();
573         ps_sb.close();
574
575         //Update operation
576 ResultSet rs1 = fetchUpd("dLongBit", key);
577         rs1.next();
578         rs1.updateBinaryStream(1, is2);
579         rs1.updateRow();
580         rs1.close();
581
582         //Query to see whether the data that has been updated
583 //using the updateBinaryStream method is the same
584 //data that we expected
585
586         rs1 = fetch("dLongBit", key);
587         rs1.next();
588         assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
589         rs1.close();
590     }
591
592     /**
593      * Test <code>updateBinaryStream</code> on a BLOB column, without
594      * specifying length of inputstream.
595      */
596     public void testUpdateBinaryStreamLengthlessBlob()
597             throws IOException, SQLException {
598         InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
599         // InputStream used for update.
600 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
601
602         //Prepared Statement used to insert the data
603 PreparedStatement ps_sb = prep("dBlob");
604         ps_sb.setInt(1, key);
605         ps_sb.setBinaryStream(2, is1);
606         ps_sb.executeUpdate();
607         ps_sb.close();
608
609         //Update operation
610 ResultSet rs1 = fetchUpd("dBlob", key);
611         rs1.next();
612         rs1.updateBinaryStream(1, is2);
613         rs1.updateRow();
614         rs1.close();
615
616         //Query to see whether the data that has been updated
617 //using the updateBinaryStream method is the same
618 //data that we expected
619
620         rs1 = fetch("dBlob", key);
621         rs1.next();
622         assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
623         rs1.close();
624     }
625
626     public void testUpdateBinaryStreamLengthlessParameterName()
627             throws IOException, SQLException {
628         InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
629         // InputStream used for update.
630 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
631
632         //Prepared Statement used to insert the data
633 PreparedStatement ps_sb = prep("dLongBit");
634         ps_sb.setInt(1, key);
635         ps_sb.setBinaryStream(2, is1);
636         ps_sb.executeUpdate();
637         ps_sb.close();
638
639         //Update operation
640 ResultSet rs1 = fetchUpd("dLongBit", key);
641         rs1.next();
642         rs1.updateBinaryStream("dLongBit", is2);
643         rs1.updateRow();
644         rs1.close();
645
646         //Query to see whether the data that has been updated
647 //using the updateBinaryStream method is the same
648 //data that we expected
649
650         rs1 = fetch("dLongBit", key);
651         rs1.next();
652         assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
653         rs1.close();
654     }
655
656     /**
657      * This methods tests the ResultSet interface method
658      * updateAsciiStream
659      *
660      * @throws SQLException if some error occurs while calling the method
661      */
662
663     public void testUpdateAsciiStreamStringParameterName()
664     throws Exception {
665         //Byte array in which the returned bytes from
666 //the Database after the update are stored. This
667 //array is then checked to determine if it
668 //has the same elements of the Byte array used for
669 //the update operation
670
671         byte[] bytes_ret = new byte[10];
672
673         //Input Stream inserted initially
674 InputStream is = new java.io.ByteArrayInputStream (BYTES1);
675
676         //InputStream that is used for update
677 InputStream is_for_update = new
678                 java.io.ByteArrayInputStream (BYTES2);
679
680         //Prepared Statement used to insert the data
681 PreparedStatement ps_sb = prep("dLongVarchar");
682         ps_sb.setInt(1, key);
683         ps_sb.setAsciiStream(2,is,BYTES1.length);
684         ps_sb.executeUpdate();
685         ps_sb.close();
686
687         //Update operation
688 //use a different ResultSet variable so that the
689 //other tests can go on unimpacted
690
691         ResultSet rs1 = fetchUpd("dLongVarchar", key);
692         rs1.next();
693         rs1.updateAsciiStream("dLongVarchar",is_for_update,(int)BYTES2.length);
694         rs1.updateRow();
695         rs1.close();
696
697         //Query to see whether the data that has been updated
698 //using the updateAsciiStream method is the same
699 //data that we expected
700
701         rs1 = fetch("dLongVarchar", key);
702         rs1.next();
703         InputStream is_ret = rs1.getAsciiStream(1);
704
705         is_ret.read(bytes_ret);
706         is_ret.close();
707
708         for(int i=0;i<BYTES2.length;i++) {
709             assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
710         }
711         rs1.close();
712     }
713
714     public void testUpdateAsciiStreamLengthless()
715             throws IOException, SQLException {
716         // Array to keep updated data fetched from the database.
717 byte[] bytesRet = new byte[10];
718
719         // Input Stream inserted initially.
720 InputStream is = new java.io.ByteArrayInputStream (BYTES1);
721
722         // InputStream that is used for update.
723 InputStream isForUpdate = new
724                 java.io.ByteArrayInputStream (BYTES2);
725
726         // Prepared Statement used to insert the data.
727 PreparedStatement ps_sb = prep("dLongVarchar");
728         ps_sb.setInt(1, key);
729         ps_sb.setAsciiStream(2, is, BYTES1.length);
730         ps_sb.executeUpdate();
731         ps_sb.close();
732
733         // Update the data.
734 ResultSet rs1 = fetchUpd("dLongVarchar", key);
735         rs1.next();
736         rs1.updateAsciiStream(1, isForUpdate);
737         rs1.updateRow();
738         rs1.close();
739
740         // Query to see whether the data that has been updated.
741 rs1 = fetch("dLongVarchar", key);
742         rs1.next();
743         InputStream isRet = rs1.getAsciiStream(1);
744         isRet.read(bytesRet);
745         isRet.close();
746
747         for (int i=0; i < BYTES2.length; i++) {
748             assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
749         }
750         rs1.close();
751     }
752
753     public void testUpdateAsciiStreamLengthlessParameterName()
754             throws IOException, SQLException {
755         // Array to keep updated data fetched from the database.
756 byte[] bytesRet = new byte[10];
757
758         // Input Stream inserted initially.
759 InputStream is = new java.io.ByteArrayInputStream (BYTES1);
760
761         // InputStream that is used for update.
762 InputStream isForUpdate = new
763                 java.io.ByteArrayInputStream (BYTES2);
764
765         // Prepared Statement used to insert the data.
766 PreparedStatement ps_sb = prep("dLongVarchar");
767         ps_sb.setInt(1, key);
768         ps_sb.setAsciiStream(2, is, BYTES1.length);
769         ps_sb.executeUpdate();
770         ps_sb.close();
771
772         // Update the data.
773 ResultSet rs1 = fetchUpd("dLongVarchar", key);
774         rs1.next();
775         rs1.updateAsciiStream("dLongVarchar", isForUpdate);
776         rs1.updateRow();
777         rs1.close();
778
779         // Query to see whether the data that has been updated.
780 rs1 = fetch("dLongVarchar", key);
781         rs1.next();
782         InputStream isRet = rs1.getAsciiStream(1);
783         isRet.read(bytesRet);
784         isRet.close();
785
786         for (int i=0; i < BYTES2.length; i++) {
787             assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
788         }
789         rs1.close();
790     }
791
792      /**
793      * This methods tests the ResultSet interface method
794      * updateCharacterStream
795      *
796      * @throws SQLException if some error occurs while calling the method
797      */
798
799     public void testUpdateCharacterStreamStringParameterName()
800     throws Exception {
801         String str = "Test data";
802         String str_for_update = "Test data used for update";
803
804         StringReader r = new StringReader(str);
805         StringReader r_for_update = new StringReader
806                 (str_for_update);
807
808         //Prepared Statement used to insert the data
809 PreparedStatement ps_sb = prep("dLongVarchar");
810         ps_sb.setInt(1, key);
811         ps_sb.setCharacterStream(2,r,str.length());
812         ps_sb.executeUpdate();
813         ps_sb.close();
814
815         //Update operation
816 //use a different ResultSet variable so that the
817 //other tests can go on unimpacted
818 ResultSet rs1 = fetchUpd("dLongVarchar", key);
819         rs1.next();
820         rs1.updateCharacterStream("dLongVarchar",
821                                   r_for_update,
822                                   str_for_update.length());
823         rs1.updateRow();
824         rs1.close();
825
826         //Query to see whether the data that has been updated
827 //using the updateAsciiStream method is the same
828 //data that we expected
829
830         rs1 = fetch("dLongVarchar", key);
831         rs1.next();
832
833         StringReader r_ret = (StringReader)rs1.getCharacterStream(1);
834
835         char [] c_ret = new char[str_for_update.length()];
836
837         r_ret.read(c_ret);
838
839         String str_ret = new String (c_ret);
840
841         assertEquals("Error in updateCharacterStream" + str_ret,str_for_update,
842             str_ret);
843
844         rs1.close();
845     }
846
847     public void testUpdateCharacterStreamLengthless()
848             throws IOException, SQLException {
849         String str = "This is the (\u0FFF\u1234) test string";
850         String strUpdated = "An updated (\u0FEF\u9876) test string";
851
852         // Insert test data
853 PreparedStatement psChar = prep("dLongVarchar");
854         psChar.setInt(1, key);
855         psChar.setCharacterStream(2, new StringReader(str));
856         psChar.execute();
857         psChar.close();
858
859         // Update test data
860 ResultSet rs = fetchUpd("dLongVarchar", key);
861         rs.next();
862         rs.updateCharacterStream(1, new StringReader(strUpdated));
863         rs.updateRow();
864         rs.close();
865
866         // Verify that update took place and is correct.
867 rs = fetch("dLongVarchar", key);
868         rs.next();
869         Reader updatedStr = rs.getCharacterStream(1);
870         for (int i=0; i < strUpdated.length(); i++) {
871             assertEquals("Strings differ at index " + i,
872                     strUpdated.charAt(i),
873                     updatedStr.read());
874         }
875         assertEquals("Too much data in stream", -1, updatedStr.read());
876         updatedStr.close();
877     }
878
879     public void testUpdateCharacterStreamLengthlessParameterName()
880             throws IOException, SQLException {
881         String str = "This is the (\u0FFF\u1234) test string";
882         String strUpdated = "An updated (\u0FEF\u9876) test string";
883
884         // Insert test data
885 PreparedStatement psChar = prep("dLongVarchar");
886         psChar.setInt(1, key);
887         psChar.setCharacterStream(2, new StringReader(str));
888         psChar.execute();
889         psChar.close();
890
891         // Update test data
892 ResultSet rs = fetchUpd("dLongVarchar", key);
893         rs.next();
894         rs.updateCharacterStream("dLongVarchar", new StringReader(strUpdated));
895         rs.updateRow();
896         rs.close();
897
898         // Verify that update took place and is correct.
899 rs = fetch("dLongVarchar", key);
900         rs.next();
901         Reader updatedStr = rs.getCharacterStream(1);
902         for (int i=0; i < strUpdated.length(); i++) {
903             assertEquals("Strings differ at index " + i,
904                     strUpdated.charAt(i),
905                     updatedStr.read());
906         }
907         assertEquals("Too much data in stream", -1, updatedStr.read());
908         updatedStr.close();
909     }
910
911     /**
912      * This methods tests the ResultSet interface method
913      * updateClob
914      *
915      * @throws SQLException if some error occurs while calling the method
916      */
917     public void embeddedUpdateClob()
918     throws Exception {
919         //Byte array in which the returned bytes from
920 //the Database after the update are stored. This
921 //array is then checked to determine if it
922 //has the same elements of the Byte array used for
923 //the update operation
924
925         byte[] bytes_ret = new byte[10];
926
927         //1 Input Stream for insertion
928 InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
929
930         //2 Input Stream for insertion
931 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
932
933         //Prepared Statement used to insert the data
934 PreparedStatement ps_sb = prep("dClob");
935
936         //first insert
937 ps_sb.setInt(1,key);
938         ps_sb.setAsciiStream(2,is1,BYTES1.length);
939         ps_sb.executeUpdate();
940
941         //second insert
942 int key2 = requestKey();
943         ps_sb.setInt(1,key2);
944         ps_sb.setAsciiStream(2,is2,BYTES2.length);
945         ps_sb.executeUpdate();
946
947         ps_sb.close();
948
949         //Update operation
950 //use a different ResultSet variable so that the
951 //other tests can go on unimpacted
952 //we do not have set methods on Clob and Blob implemented
953 //So query the first Clob from the database
954 //update the second result set with this
955 //Clob value
956
957         ResultSet rs1 = fetchUpd("dClob", key);
958         rs1.next();
959         Clob clob = rs1.getClob(1);
960         rs1.close();
961
962         rs1 = fetchUpd("dClob", key2);
963         rs1.next();
964         rs1.updateClob(1,clob);
965         rs1.updateRow();
966         rs1.close();
967
968         //Query to see whether the data that has been updated
969 //using the updateClob method is the same
970 //data that we expected
971
972         rs1 = fetch("dClob", key2);
973         rs1.next();
974         assertEquals(clob, rs1.getClob(1));
975         rs1.close();
976     }
977
978     public void testUpdateClobLengthless()
979             throws Exception {
980         Reader r1 = new java.io.StringReader (new String (BYTES1));
981         // InputStream for insertion.
982 Reader r2 = new java.io.StringReader (new String (BYTES2));
983
984         // Prepared Statement used to insert the data
985 PreparedStatement ps_sb = prep("dClob");
986         ps_sb.setInt(1, key);
987         ps_sb.setCharacterStream(2, r1);
988         ps_sb.executeUpdate();
989         ps_sb.close();
990
991         // Update operation
992 ResultSet rs1 = fetchUpd("dClob", key);
993         rs1.next();
994         rs1.updateClob(1, r2);
995         rs1.updateRow();
996         rs1.close();
997
998         // Query to see whether the data that has been updated.
999 rs1 = fetch("dClob", key);
1000        rs1.next();
1001        assertEquals(new StringReader(new String (BYTES2)),
1002                     rs1.getCharacterStream(1));
1003        rs1.close();
1004    }
1005
1006     /**
1007     * This methods tests the ResultSet interface method
1008     * updateBlob
1009     *
1010     * @throws SQLException if some error occurs while calling the method
1011     */
1012    public void embeddedUpdateBlob()
1013    throws Exception {
1014        //Byte array in which the returned bytes from
1015 //the Database after the update are stored. This
1016 //array is then checked to determine if it
1017 //has the same elements of the Byte array used for
1018 //the update operation
1019
1020        byte[] bytes_ret = new byte[10];
1021
1022        //1 Input Stream for insertion
1023 InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
1024
1025        //2 Input Stream for insertion
1026 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
1027
1028        //Prepared Statement used to insert the data
1029 PreparedStatement ps_sb = prep("dBlob");
1030
1031        //first insert
1032 ps_sb.setInt(1, key);
1033        ps_sb.setBinaryStream(2,is1,BYTES1.length);
1034        ps_sb.executeUpdate();
1035
1036        //second insert
1037 int key2 = requestKey();
1038        ps_sb.setInt(1, key2);
1039        ps_sb.setBinaryStream(2,is2,BYTES2.length);
1040        ps_sb.executeUpdate();
1041
1042        ps_sb.close();
1043
1044        //Update operation
1045 //use a different ResultSet variable so that the
1046 //other tests can go on unimpacted
1047 //we do not have set methods on Clob and Blob implemented
1048 //So query the first Clob from the database
1049 //update the second result set with this
1050 //Clob value
1051
1052        ResultSet rs1 = fetch("dBlob", key);
1053        rs1.next();
1054        Blob blob = rs1.getBlob(1);
1055        rs1.close();
1056
1057        rs1 = fetchUpd("dBlob", key2);
1058        rs1.next();
1059        rs1.updateBlob(1,blob);
1060        rs1.updateRow();
1061        rs1.close();
1062
1063        //Query to see whether the data that has been updated
1064 //using the updateBlob method is the same
1065 //data that we expected
1066
1067        rs1 = fetch("dBlob", key2);
1068        rs1.next();
1069        assertEquals(blob, rs1.getBlob(1));
1070        rs1.close();
1071    }
1072
1073    public void testUpdateBlobLengthless()
1074            throws Exception {
1075        InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
1076        // InputStream for insertion.
1077 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
1078
1079        // Prepared Statement used to insert the data
1080 PreparedStatement ps_sb = prep("dBlob");
1081        ps_sb.setInt(1, key);
1082        ps_sb.setBinaryStream(2, is1);
1083        ps_sb.executeUpdate();
1084        ps_sb.close();
1085
1086        // Update operation
1087 ResultSet rs1 = fetchUpd("dBlob", key);
1088        rs1.next();
1089        rs1.updateBlob(1, is2);
1090        rs1.updateRow();
1091        rs1.close();
1092
1093        // Query to see whether the data that has been updated.
1094 rs1 = fetch("dBlob", key);
1095        rs1.next();
1096        assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
1097        rs1.close();
1098    }
1099
1100    /**
1101     * This methods tests the ResultSet interface method
1102     * updateClob
1103     *
1104     * @throws SQLException if some error occurs while calling the method
1105     */
1106    public void embeddedUpdateClobStringParameterName()
1107    throws Exception {
1108        //Byte array in which the returned bytes from
1109 //the Database after the update are stored. This
1110 //array is then checked to determine if it
1111 //has the same elements of the Byte array used for
1112 //the update operation
1113
1114        byte[] bytes_ret = new byte[10];
1115
1116        //1 Input Stream for insertion
1117 InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
1118
1119        //2 Input Stream for insertion
1120 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
1121
1122        //Prepared Statement used to insert the data
1123 PreparedStatement ps_sb = prep("dClob");
1124
1125        //first insert
1126 ps_sb.setInt(1, key);
1127        ps_sb.setAsciiStream(2,is1,BYTES1.length);
1128        ps_sb.executeUpdate();
1129
1130        //second insert
1131 int key2 = requestKey();
1132        ps_sb.setInt(1, key2);
1133        ps_sb.setAsciiStream(2,is2,BYTES2.length);
1134        ps_sb.executeUpdate();
1135
1136        ps_sb.close();
1137
1138        //Update operation
1139 //use a different ResultSet variable so that the
1140 //other tests can go on unimpacted
1141 //we do not have set methods on Clob and Blob implemented
1142 //So query the first Clob from the database
1143 //update the second result set with this
1144 //Clob value
1145
1146        ResultSet rs1 = fetch("dClob", key);
1147        rs1.next();
1148        Clob clob = rs1.getClob(1);
1149        rs1.close();
1150
1151        rs1 = fetchUpd("dClob", key2);
1152        rs1.next();
1153        rs1.updateClob("dClob",clob);
1154        rs1.updateRow();
1155        rs1.close();
1156
1157        //Query to see whether the data that has been updated
1158 //using the updateClob method is the same
1159 //data that we expected
1160
1161        rs1 = fetch("dClob", key2);
1162        rs1.next();
1163        assertEquals(clob, rs1.getClob(1));
1164        rs1.close();
1165    }
1166
1167    public void testUpdateClobLengthlessParameterName()
1168            throws Exception {
1169        Reader r1 = new java.io.StringReader (new String (BYTES1));
1170        // InputStream for insertion.
1171 Reader r2 = new java.io.StringReader (new String (BYTES2));
1172
1173        // Prepared Statement used to insert the data
1174 PreparedStatement ps_sb = prep("dClob");
1175        ps_sb.setInt(1, key);
1176        ps_sb.setCharacterStream(2, r1);
1177        ps_sb.executeUpdate();
1178        ps_sb.close();
1179
1180        // Update operation
1181 ResultSet rs1 = fetchUpd("dClob", key);
1182        rs1.next();
1183        rs1.updateClob("dClob", r2);
1184        rs1.updateRow();
1185        rs1.close();
1186
1187        // Query to see whether the data that has been updated.
1188 rs1 = fetch("dClob", key);
1189        rs1.next();
1190        assertEquals(new StringReader(new String (BYTES2)),
1191                     rs1.getCharacterStream(1));
1192        rs1.close();
1193    }
1194
1195     /**
1196     * This methods tests the ResultSet interface method
1197     * updateBlob
1198     *
1199     * @throws SQLException if some error occurs while calling the method
1200     */
1201    public void embeddedUpdateBlobStringParameterName()
1202    throws Exception {
1203        //Byte array in which the returned bytes from
1204 //the Database after the update are stored. This
1205 //array is then checked to determine if it
1206 //has the same elements of the Byte array used for
1207 //the update operation
1208
1209        byte[] bytes_ret = new byte[10];
1210
1211        //1 Input Stream for insertion
1212 InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
1213
1214        //2 Input Stream for insertion
1215 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
1216
1217        //Prepared Statement used to insert the data
1218 PreparedStatement ps_sb = prep("dBlob");
1219
1220        //first insert
1221 ps_sb.setInt(1, key);
1222        ps_sb.setBinaryStream(2,is1,BYTES1.length);
1223        ps_sb.executeUpdate();
1224
1225        //second insert
1226 int key2 = requestKey();
1227        ps_sb.setInt(1, key2);
1228        ps_sb.setBinaryStream(2,is2,BYTES2.length);
1229        ps_sb.executeUpdate();
1230
1231        ps_sb.close();
1232
1233        //Update operation
1234 //use a different ResultSet variable so that the
1235 //other tests can go on unimpacted
1236 //we do not have set methods on Clob and Blob implemented
1237 //So query the first Clob from the database
1238 //update the second result set with this
1239 //Clob value
1240
1241        ResultSet rs1 = fetch("dBlob", key);
1242        rs1.next();
1243        Blob blob = rs1.getBlob(1);
1244        rs1.close();
1245
1246        rs1 = fetchUpd("dBlob", key2);
1247        rs1.next();
1248        rs1.updateBlob("dBlob",blob);
1249        rs1.updateRow();
1250        rs1.close();
1251
1252        //Query to see whether the data that has been updated
1253 //using the updateBlob method is the same
1254 //data that we expected
1255
1256        rs1 = fetch("dBlob", key2);
1257        rs1.next();
1258        assertEquals(blob, rs1.getBlob(1));
1259        rs1.close();
1260    }
1261
1262    public void testUpdateBlobWithStreamLengthlessParameterName()
1263            throws Exception {
1264        InputStream is1 = new java.io.ByteArrayInputStream (BYTES1);
1265        // InputStream for insertion.
1266 InputStream is2 = new java.io.ByteArrayInputStream (BYTES2);
1267
1268        // Prepared Statement used to insert the data
1269 PreparedStatement ps_sb = prep("dBlob");
1270        ps_sb.setInt(1, key);
1271        ps_sb.setBinaryStream(2, is1);
1272        ps_sb.executeUpdate();
1273        ps_sb.close();
1274
1275        // Update operation
1276 ResultSet rs1 = fetchUpd("dBlob", key);
1277        rs1.next();
1278        rs1.updateBlob("dBlob", is2);
1279        rs1.updateRow();
1280        rs1.close();
1281
1282        // Query to see whether the data that has been updated.
1283 rs1 = fetch("dBlob", key);
1284        rs1.next();
1285        assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
1286        rs1.close();
1287    }
1288
1289    /************************************************************************
1290     ** T E S T S E T U P *
1291     ************************************************************************/
1292
1293    /**
1294     * Create suite containing client-only tests.
1295     */
1296    private static TestSuite clientSuite(String name) {
1297        TestSuite clientSuite = new TestSuite(name);
1298        return clientSuite;
1299    }
1300
1301    /**
1302     * Create suite containing embedded-only tests.
1303     */
1304    private static TestSuite embeddedSuite(String name) {
1305        TestSuite embeddedSuite = new TestSuite(name);
1306        embeddedSuite.addTest(new ResultSetTest(
1307                    "embeddedUpdateBlob"));
1308        embeddedSuite.addTest(new ResultSetTest(
1309                    "embeddedUpdateClob"));
1310        embeddedSuite.addTest(new ResultSetTest(
1311                    "embeddedUpdateClobStringParameterName"));
1312        return embeddedSuite;
1313    }
1314
1315    public static Test suite() {
1316        TestSuite rsSuite =
1317            new TestSuite(ResultSetTest.class, "ResultSetTest suite");
1318        // Add client only tests
1319 // NOTE: JCC is excluded
1320 if (usingDerbyNetClient()) {
1321            rsSuite.addTest(
1322                    clientSuite("ResultSetTest client-only suite"));
1323        }
1324        // Add embedded only tests
1325 if (usingEmbedded()) {
1326            rsSuite.addTest(
1327                    embeddedSuite("ResultSetTest embedded-only suite"));
1328        }
1329        // Wrap suite in a TestSetup-class.
1330 return new BaseJDBCTestSetup(rsSuite) {
1331                protected void setUp()
1332                        throws SQLException {
1333                    Connection con = getConnection();
1334                    Statement stmt = con.createStatement();
1335                    stmt.execute("create table UpdateTestTableResultSet (" +
1336                            "sno int not null unique," +
1337                            "dBlob BLOB," +
1338                            "dClob CLOB," +
1339                            "dLongVarchar LONG VARCHAR," +
1340                            "dLongBit LONG VARCHAR FOR BIT DATA)");
1341                    stmt.close();
1342               }
1343
1344                protected void tearDown()
1345                        throws Exception {
1346                    Connection con = getConnection();
1347                    Statement stmt = con.createStatement();
1348                    stmt.execute("drop table UpdateTestTableResultSet");
1349                    stmt.close();
1350                    super.tearDown();
1351                }
1352            };
1353    }
1354
1355    /*************************************************************************
1356     ** U T I L I T Y M E T H O D S *
1357     *************************************************************************/
1358
1359    /**
1360     * Get a key that is used to identify an inserted row.
1361     * Introduced to avoid having to delete table contents after each test,
1362     * and because the order of the tests is not guaranteed.
1363     *
1364     * @return an integer in range [1, Integer.MAX_VALUE -1]
1365     */
1366    private static final int requestKey() {
1367        return ++insertKey;
1368    }
1369
1370    /**
1371     * Prepare commonly used statement to insert a row.
1372     *
1373     * @param con connection to database
1374     * @param colName name of the column to insert into
1375     */
1376    private PreparedStatement prep(String colName)
1377            throws SQLException {
1378        return prepareStatement("insert into UpdateTestTableResultSet " +
1379                "(sno, " + colName + ") values (?,?)");
1380    }
1381
1382    /**
1383     * Fetch the specified row for update.
1384     *
1385     * @param con connection to database
1386     * @param colName name of the column to fetch
1387     * @param key identifier for row to fetch
1388     * @return a <code>ResultSet</code> with zero or one row, depending on
1389     * the key used
1390     */
1391    private ResultSet fetchUpd(String colName, int key)
1392            throws SQLException {
1393        Statement stmt = createStatement(ResultSet.TYPE_FORWARD_ONLY,
1394                                             ResultSet.CONCUR_UPDATABLE);
1395        return stmt.executeQuery("select " + colName +
1396                " from UpdateTestTableResultSet where sno = " + key +
1397                " for update");
1398    }
1399
1400    /**
1401     * Fetch the specified row.
1402     *
1403     * @param con connection to database
1404     * @param colName name of the column to fetch
1405     * @param key identifier for row to fetch
1406     * @return a <code>ResultSet</code> with zero or one row, depending on
1407     * the key used
1408     */
1409    private ResultSet fetch(String colName, int key)
1410            throws SQLException {
1411        Statement stmt = createStatement();
1412        return stmt.executeQuery("select " + colName +
1413                " from UpdateTestTableResultSet where sno = " + key);
1414    }
1415}
1416

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags