KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2  
3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc4.PreparedStatementTest
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 junit.framework.*;
25
26 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
27 import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
28 import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream;
29
30 import java.io.*;
31 import java.sql.*;
32 import javax.sql.*;
33
34 import org.apache.derby.iapi.services.io.DerbyIOException;
35 import org.apache.derby.impl.jdbc.EmbedSQLException;
36
37 /**
38  * This class is used to test JDBC4 specific methods in the PreparedStatement(s)
39  * object.
40  *
41  * A number of methods and variables are in place to aid the writing of tests:
42  * <ul><li>setBinaryStreamOnBlob
43  * <li>setAsciiStream
44  * <li>key - an id. One is generated each time setUp is run.
45  * <li>reqeustKey() - generate a new unique id.
46  * <li>psInsertX - prepared statements for insert.
47  * <li>psFetchX - prepared statements for fetching values.
48  * </ul>
49  *
50  * For table creation, see the <code>suite</code>-method.
51  */

52 public class PreparedStatementTest extends BaseJDBCTestCase {
53
54     private static final String JavaDoc BLOBTBL = "BlobTestTable";
55     private static final String JavaDoc CLOBTBL = "ClobTestTable";
56     private static final String JavaDoc LONGVARCHAR = "LongVarcharTestTable";
57
58     /** Key used to id data inserted into the database. */
59     private static int globalKey = 1;
60
61     /** Byte array passed in to the database. **/
62     private static final byte[] BYTES = {
63         0x65, 0x66, 0x67, 0x68, 0x69,
64         0x69, 0x68, 0x67, 0x66, 0x65
65     };
66
67     // Default connection and prepared statements that are used by the tests.
68
/**
69      * Default key to use for insertions.
70      * Is unique for each fixture. More keys can be fetched by calling
71      * <link>requestKey</link>.
72      */

73     private int key;
74     /** Default connection object. */
75     /** PreparedStatement object with no positional arguments. */
76     private PreparedStatement ps = null;
77     /** PreparedStatement to fetch BLOB with specified id. */
78     private PreparedStatement psFetchBlob = null;
79     /** PreparedStatement to insert a BLOB with specified id. */
80     private PreparedStatement psInsertBlob = null;
81     /** PreparedStatement to fetch CLOB with specified id. */
82     private PreparedStatement psFetchClob = null;
83     /** PreparedStatement to insert a CLOB with specified id. */
84     private PreparedStatement psInsertClob = null;
85     /** PreparedStatement to insert a LONG VARCHAR with specified id. */
86     private PreparedStatement psInsertLongVarchar = null;
87     //Statement object
88
private Statement s = null;
89
90
91     
92     /**
93      * Create a test with the given name.
94      *
95      * @param name name of the test.
96      */

97     public PreparedStatementTest(String JavaDoc name) {
98         super(name);
99     }
100     
101     /**
102      *
103      * Obtain a "regular" connection and PreparedStatement that the tests
104      * can use.
105      *
106      * @throws SQLException
107      */

108     public void setUp()
109         throws SQLException {
110         key = requestKey();
111         //create the statement object
112
s = createStatement();
113         //Create the PreparedStatement that will then be used as the basis
114
//throughout this test henceforth
115
//This prepared statement will however NOT be used for testing
116
//setClob and setBlob
117
ps = prepareStatement("select count(*) from sys.systables");
118         
119         // Prepare misc statements.
120
psFetchBlob = prepareStatement("SELECT dBlob FROM " +
121                 BLOBTBL + " WHERE sno = ?");
122         psInsertBlob = prepareStatement("INSERT INTO " + BLOBTBL +
123                 " VALUES (?, ?)");
124         psFetchClob = prepareStatement("SELECT dClob FROM " +
125                 CLOBTBL + " WHERE sno = ?");
126         psInsertClob = prepareStatement("INSERT INTO " + CLOBTBL +
127                 " VALUES (?, ?)");
128         psInsertLongVarchar = prepareStatement("INSERT INTO " + LONGVARCHAR +
129                 " VALUES (?, ?)");
130     }
131
132     /**
133      *
134      * Release the resources that are used in this test
135      *
136      * @throws SQLException
137      *
138      */

139     public void tearDown()
140         throws Exception JavaDoc {
141         
142         psFetchBlob.close();
143         psFetchClob.close();
144         psInsertBlob.close();
145         psInsertClob.close();
146         psInsertLongVarchar.close();
147         
148         super.tearDown();
149     }
150
151     public static Test suite() {
152         TestSuite suite = new TestSuite();
153         suite.addTestSuite(PreparedStatementTest.class);
154         suite.addTest(SetObjectUnsupportedTest.suite(false));
155         return new BaseJDBCTestSetup(suite) {
156                 public void setUp()
157                         throws java.lang.Exception JavaDoc {
158                         try {
159                             create();
160                         } catch (SQLException sqle) {
161                             if (sqle.getSQLState().equals("X0Y32")) {
162                                 drop();
163                                 create();
164                             } else {
165                                 throw sqle;
166                             }
167                         }
168                 }
169
170                 public void tearDown()
171                         throws java.lang.Exception JavaDoc {
172                     drop();
173                     super.tearDown();
174                 }
175
176                 private void create()
177                         throws SQLException {
178                     Statement stmt = getConnection().createStatement();
179                     stmt.execute("create table " + BLOBTBL +
180                             " (sno int, dBlob BLOB(1M))");
181                     stmt.execute("create table " + CLOBTBL +
182                             " (sno int, dClob CLOB(1M))");
183                     stmt.execute("create table " + LONGVARCHAR +
184                             " (sno int, dLongVarchar LONG VARCHAR)");
185                     stmt.close();
186                 }
187
188                 private void drop()
189                         throws SQLException {
190                     Statement stmt = getConnection().createStatement();
191                     stmt.execute("drop table " + BLOBTBL);
192                     stmt.execute("drop table " + CLOBTBL);
193                     stmt.execute("drop table " + LONGVARCHAR);
194                     stmt.close();
195                 }
196             };
197     }
198     
199     //--------------------------------------------------------------------------
200
//BEGIN THE TEST OF THE METHODS THAT THROW AN UNIMPLEMENTED EXCEPTION IN
201
//THIS CLASS
202

203     /**
204      * Tests the setRowId method of the PreparedStatement interface
205      *
206      * @throws SQLException upon any failure that occurs in the
207      * call to the method.
208      */

209     public void testSetRowId() throws SQLException{
210         try {
211             RowId rowid = null;
212             ps.setRowId(0,rowid);
213             fail("setRowId should not be implemented");
214         }
215         catch(SQLFeatureNotSupportedException sqlfne) {
216             //Do Nothing, This happens as expected
217
}
218     }
219     
220     /**
221      * Tests the setNString method of the PreparedStatement interface
222      *
223      * @throws SQLException upon any failure that occurs in the
224      * call to the method.
225      */

226     public void testSetNString() throws SQLException{
227         try {
228             String JavaDoc str = null;
229             ps.setNString(0,str);
230             fail("setNString should not be implemented");
231         }
232         catch(SQLFeatureNotSupportedException sqlfne) {
233             //Do Nothing, This happens as expected
234
}
235     }
236     
237     /**
238      * Tests the setNCharacterStream method of the PreparedStatement interface
239      *
240      * @throws SQLException upon any failure that occurs in the
241      * call to the method.
242      */

243     public void testSetNCharacterStream() throws SQLException{
244         try {
245             Reader r = null;
246             ps.setNCharacterStream(0,r,0);
247             fail("setNCharacterStream should not be implemented");
248         }
249         catch(SQLFeatureNotSupportedException sqlfne) {
250             //Do Nothing, This happens as expected
251
}
252     }
253     
254     public void testSetNCharacterStreamLengthlessNotImplemented()
255             throws SQLException {
256         try {
257             ps.setNCharacterStream(1, new StringReader("A string"));
258             fail("setNCharacterStream(int,Reader) should not be implemented");
259         } catch (SQLFeatureNotSupportedException sfnse) {
260             // Do nothing, this is expected behavior.
261
}
262     }
263
264     public void testSetNClobLengthlessNotImplemented()
265             throws SQLException {
266         try {
267             ps.setNClob(1, new StringReader("A string"));
268             fail("setNClob(int,Reader) should not be implemented");
269         } catch (SQLFeatureNotSupportedException sfnse) {
270             // Do nothing, this is expected behaviour.
271
}
272     }
273
274     /**
275      * Tests the setNClob method of the PreparedStatement interface
276      *
277      * @throws SQLException upon any failure that occurs in the
278      * call to the method.
279      */

280     public void testSetNClob1() throws SQLException{
281         try {
282             NClob nclob = null;
283             ps.setNClob(0,nclob);
284             fail("setNClob should not be implemented");
285         }
286         catch(SQLFeatureNotSupportedException sqlfne) {
287             //Do Nothing, This happens as expected
288
}
289     }
290     
291     /**
292      * Tests the setNClob method of the PreparedStatement interface
293      *
294      * @throws SQLException upon any failure that occurs in the
295      * call to the method.
296      */

297     public void testSetNClob2() throws SQLException{
298         try {
299             Reader reader = null;
300             ps.setNClob(0,reader,0);
301             fail("setNClob should not be implemented");
302         }
303         catch(SQLFeatureNotSupportedException sqlfne) {
304             //Do Nothing, This happens as expected
305
}
306     }
307     
308     /**
309      * Tests the setSQLXML method of the PreparedStatement interface
310      *
311      * @throws SQLException upon any failure that occurs in the
312      * call to the method.
313      */

314     public void testSetSQLXML() throws SQLException{
315         try {
316             SQLXML sqlxml = null;
317             ps.setSQLXML(0,sqlxml);
318             fail("setNClob should not be implemented");
319         }
320         catch(SQLFeatureNotSupportedException sqlfne) {
321             //Do Nothing, This happens as expected
322
}
323     }
324     
325     //--------------------------------------------------------------------------
326
//Now test the methods that are implemented in the PreparedStatement
327
//interface
328

329     public void testIsWrapperForStatement() throws SQLException {
330         assertTrue(ps.isWrapperFor(Statement.class));
331     }
332
333     public void testIsWrapperForPreparedStatement() throws SQLException {
334         assertTrue(ps.isWrapperFor(PreparedStatement.class));
335     }
336
337     public void testIsNotWrapperForCallableStatement() throws SQLException {
338         assertFalse(ps.isWrapperFor(CallableStatement.class));
339     }
340
341     public void testIsNotWrapperForResultSet() throws SQLException {
342         assertFalse(ps.isWrapperFor(ResultSet.class));
343     }
344
345     public void testUnwrapStatement() throws SQLException {
346         Statement stmt = ps.unwrap(Statement.class);
347         assertSame("Unwrap returned wrong object.", ps, stmt);
348     }
349
350     public void testUnwrapPreparedStatement() throws SQLException {
351         PreparedStatement ps2 = ps.unwrap(PreparedStatement.class);
352         assertSame("Unwrap returned wrong object.", ps, ps2);
353     }
354
355     public void testUnwrapCallableStatement() {
356         try {
357             CallableStatement cs = ps.unwrap(CallableStatement.class);
358             fail("Unwrap didn't fail.");
359         } catch (SQLException e) {
360             assertSQLState("XJ128", e);
361         }
362     }
363
364     public void testUnwrapResultSet() {
365         try {
366             ResultSet rs = ps.unwrap(ResultSet.class);
367             fail("Unwrap didn't fail.");
368         } catch (SQLException e) {
369             assertSQLState("XJ128", e);
370         }
371     }
372
373     //-----------------------------------------------------------------------
374
// Begin test for setClob and setBlob
375

376     /*
377        we need a table in which a Clob or a Blob can be stored. We basically
378        need to write tests for the setClob and the setBlob methods.
379        Proper process would be
380        a) Do a createClob or createBlob
381        b) Populate data in the LOB
382        c) Store in Database
383
384        But the createClob and createBlob implementations are not
385        available on the EmbeddedServer. So instead the workaround adopted
386        is
387
388        a) store a Clob or Blob in Database.
389        b) Retrieve it from the database.
390        c) store it back using setClob or setBlob
391
392      */

393
394     /**
395      *
396      * Test the setClob() method
397      *
398      * @throws SQLException if a failure occurs during the call to setClob
399      *
400      */

401     public void testSetClob()
402             throws IOException, SQLException {
403         //insert default values into the table
404

405         String JavaDoc str = "Test data for the Clob object";
406         StringReader is = new StringReader("Test data for the Clob object");
407         is.reset();
408         
409         //initially insert the data
410
psInsertClob.setInt(1, key);
411         psInsertClob.setClob(2, is, str.length());
412         psInsertClob.executeUpdate();
413         
414         //Now query to retrieve the Clob
415
psFetchClob.setInt(1, key);
416         ResultSet rs = psFetchClob.executeQuery();
417         rs.next();
418         Clob clobToBeInserted = rs.getClob(1);
419         rs.close();
420         
421         //Now use the setClob method
422
int secondKey = requestKey();
423         psInsertClob.setInt(1, secondKey);
424         psInsertClob.setClob(2, clobToBeInserted);
425         psInsertClob.execute();
426         
427         psInsertClob.close();
428         
429         //Now test to see that the Clob has been stored correctly
430
psFetchClob.setInt(1, secondKey);
431         rs = psFetchClob.executeQuery();
432         rs.next();
433         Clob clobRetrieved = rs.getClob(1);
434         
435         assertEquals(clobToBeInserted,clobRetrieved);
436     }
437
438     /**
439      * Insert <code>Clob</code> without specifying length and read it back
440      * for verification.
441      *
442      * Beacuse we don't yet support <code>Connection.createClob</code> in the
443      * client driver, we must first insert data into the database and read back
444      * a <code>Clob</code> object. This object is then inserted into the
445      * database again.
446      */

447     public void testSetClobLengthless()
448             throws IOException, SQLException {
449         // Insert test data.
450
String JavaDoc testString = "Test string for setCharacterStream\u1A00";
451         Reader reader = new StringReader(testString);
452         psInsertClob.setInt(1, key);
453         psInsertClob.setCharacterStream(2, reader);
454         psInsertClob.execute();
455         reader.close();
456         // Must fetch Clob from database because we don't support
457
// Connection.createClob on the client yet.
458
psFetchClob.setInt(1, key);
459         ResultSet rs = psFetchClob.executeQuery();
460         assertTrue("No results retrieved", rs.next());
461         int secondKey = requestKey();
462         Clob insertClob = rs.getClob(1);
463         psInsertClob.setInt(1, secondKey);
464         psInsertClob.setClob(2, insertClob);
465         psInsertClob.execute();
466
467         // Read back test data from database.
468
psFetchClob.setInt(1, secondKey);
469         rs = psFetchClob.executeQuery();
470         assertTrue("No results retrieved", rs.next());
471         Clob clobRetrieved = rs.getClob(1);
472
473         // Verify test data.
474
assertEquals(insertClob, clobRetrieved);
475     }
476
477     /**
478      *
479      * Test the setBlob() method
480      *
481      * @throws SQLException if a failure occurs during the call to setBlob
482      *
483      */

484     public void testSetBlob()
485             throws IOException, SQLException {
486         //insert default values into the table
487

488         InputStream is = new java.io.ByteArrayInputStream JavaDoc(BYTES);
489         is.reset();
490         
491         //initially insert the data
492
psInsertBlob.setInt(1, key);
493         psInsertBlob.setBlob(2, is, BYTES.length);
494         psInsertBlob.executeUpdate();
495         
496         //Now query to retrieve the Blob
497
psFetchBlob.setInt(1, key);
498         ResultSet rs = psFetchBlob.executeQuery();
499         rs.next();
500         Blob blobToBeInserted = rs.getBlob(1);
501         rs.close();
502         
503         //Now use the setBlob method
504
int secondKey = requestKey();
505         psInsertBlob.setInt(1, secondKey);
506         psInsertBlob.setBlob(2, blobToBeInserted);
507         psInsertBlob.execute();
508         
509         psInsertBlob.close();
510         
511         //Now test to see that the Blob has been stored correctly
512
psFetchBlob.setInt(1, secondKey);
513         rs = psFetchBlob.executeQuery();
514         rs.next();
515         Blob blobRetrieved = rs.getBlob(1);
516         
517         assertEquals(blobToBeInserted, blobRetrieved);
518     }
519     
520     /**
521      * Insert <code>Blob</code> without specifying length and read it back
522      * for verification.
523      *
524      * Beacuse we don't yet support <code>Connection.createBlob</code> in the
525      * client driver, we must first insert data into the database and read back
526      * a <code>Blob</code> object. This object is then inserted into the
527      * database again.
528      */

529     public void testSetBlobLengthless()
530             throws IOException, SQLException {
531         // Insert test data.
532
InputStream is = new ByteArrayInputStream(BYTES);
533         psInsertBlob.setInt(1, key);
534         psInsertBlob.setBinaryStream(2, is);
535         psInsertBlob.execute();
536         is.close();
537         // Must fetch Blob from database because we don't support
538
// Connection.createBlob on the client yet.
539
psFetchBlob.setInt(1, key);
540         ResultSet rs = psFetchBlob.executeQuery();
541         assertTrue("No results retrieved", rs.next());
542         Blob insertBlob = rs.getBlob(1);
543         int secondKey = requestKey();
544         psInsertBlob.setInt(1, secondKey);
545         psInsertBlob.setBlob(2, insertBlob);
546         psInsertBlob.execute();
547
548         // Read back test data from database.
549
psFetchBlob.setInt(1, secondKey);
550         rs = psFetchBlob.executeQuery();
551         assertTrue("No results retrieved", rs.next());
552         Blob blobRetrieved = rs.getBlob(1);
553
554         // Verify test data.
555
assertEquals(insertBlob, blobRetrieved);
556     }
557
558     //-------------------------------------------------
559
//Test the methods used to test poolable statements
560

561     /**
562      *
563      * Tests the PreparedStatement interface method setPoolable
564      *
565      * @throws SQLException
566      */

567     
568     public void testSetPoolable() throws SQLException {
569         try {
570             // Set the poolable statement hint to false
571
ps.setPoolable(false);
572             if (ps.isPoolable())
573                 fail("Expected a non-poolable statement");
574             // Set the poolable statement hint to true
575
ps.setPoolable(true);
576             if (!ps.isPoolable())
577                 fail("Expected a poolable statement");
578         } catch(SQLException sqle) {
579             // Check which SQLException state we've got and if it is
580
// expected, do not print a stackTrace
581
// Embedded uses XJ012, client uses XCL31.
582
if (sqle.getSQLState().equals("XJ012") ||
583                 sqle.getSQLState().equals("XCL31")) {
584                 // All is good and is expected
585
} else {
586                 fail("Unexpected SQLException " + sqle);
587             }
588         } catch(Exception JavaDoc e) {
589             fail("Unexpected exception thrown in method " + e);
590         }
591     }
592     
593     /**
594      *
595      * Tests the PreparedStatement interface method isPoolable
596      *
597      * @throws SQLException
598      *
599      */

600     
601     public void testIsPoolable() throws SQLException {
602         try {
603             // By default a prepared statement is poolable
604
if (!ps.isPoolable())
605                 fail("Expected a poolable statement");
606         } catch(SQLException sqle) {
607             // Check which SQLException state we've got and if it is
608
// expected, do not print a stackTrace
609
// Embedded uses XJ012, client uses XCL31.
610
if (sqle.getSQLState().equals("XJ012") ||
611                 sqle.getSQLState().equals("XCL31")) {
612                 // All is good and is expected
613
} else {
614                 fail("Unexpected SQLException " + sqle);
615             }
616         } catch(Exception JavaDoc e) {
617             fail("Unexpected exception thrown in method " + e);
618         }
619     }
620     
621     
622     /**
623      *
624      * Tests the PreparedStatement interface method setCharacterStream
625      *
626      * @throws SQLException
627      *
628      */

629     public void testSetCharacterStream() throws Exception JavaDoc {
630         String JavaDoc str = "Test data for the Clob object";
631         StringReader is = new StringReader("Test data for the Clob object");
632         
633         is.reset();
634         
635         //initially insert the data
636
psInsertClob.setInt(1, key);
637         psInsertClob.setCharacterStream(2, is, str.length());
638         psInsertClob.executeUpdate();
639         
640         //Now query to retrieve the Clob
641
psFetchClob.setInt(1, key);
642         ResultSet rs = psFetchClob.executeQuery();
643         rs.next();
644         Clob clobRetrieved = rs.getClob(1);
645         rs.close();
646         
647         String JavaDoc str_out = clobRetrieved.getSubString(1L,(int)clobRetrieved.length());
648         
649         assertEquals("Error in inserting data into the Clob object",str,str_out);
650         psInsertClob.close();
651     }
652
653     public void testSetCharacterStreamLengthless()
654             throws IOException, SQLException {
655         // Insert test data.
656
String JavaDoc testString = "Test string for setCharacterStream\u1A00";
657         Reader reader = new StringReader(testString);
658         psInsertClob.setInt(1, key);
659         psInsertClob.setCharacterStream(2, reader);
660         psInsertClob.execute();
661         reader.close();
662
663         // Read back test data from database.
664
psFetchClob.setInt(1, key);
665         ResultSet rs = psFetchClob.executeQuery();
666         assertTrue("No results retrieved", rs.next());
667         Clob clobRetrieved = rs.getClob(1);
668
669         // Verify test data.
670
assertEquals("Mismatch test data in/out", testString,
671                      clobRetrieved.getSubString(1, testString.length()));
672     }
673
674      /**
675       *
676       * Tests the PreparedStatement interface method setAsciiStream
677       *
678       * @throws SQLException
679       *
680       */

681     
682     public void testSetAsciiStream() throws Exception JavaDoc {
683         //insert default values into the table
684

685         byte [] bytes1 = new byte[10];
686         
687         InputStream is = new java.io.ByteArrayInputStream JavaDoc(BYTES);
688         
689         is.reset();
690         
691         //initially insert the data
692
psInsertClob.setInt(1, key);
693         psInsertClob.setAsciiStream(2, is, BYTES.length);
694         psInsertClob.executeUpdate();
695         
696         //Now query to retrieve the Clob
697
psFetchClob.setInt(1, key);
698         ResultSet rs = psFetchClob.executeQuery();
699         rs.next();
700         Clob ClobRetrieved = rs.getClob(1);
701         rs.close();
702         
703         try {
704             InputStream is_ret = ClobRetrieved.getAsciiStream();
705             is_ret.read(bytes1);
706         } catch(IOException ioe) {
707             fail("IOException while reading the Clob from the database");
708         }
709         for(int i=0;i<BYTES.length;i++) {
710             assertEquals("Error in inserting data into the Clob",BYTES[i],bytes1[i]);
711         }
712         psInsertClob.close();
713     }
714
715     public void testSetAsciiStreamLengthless()
716             throws IOException, SQLException {
717         // Insert test data.
718
InputStream is = new ByteArrayInputStream(BYTES);
719         psInsertClob.setInt(1, key);
720         psInsertClob.setAsciiStream(2, is);
721         psInsertClob.execute();
722         is.close();
723
724         // Read back test data from database.
725
psFetchClob.setInt(1, key);
726         ResultSet rs = psFetchClob.executeQuery();
727         assertTrue("No results retrieved", rs.next());
728         Clob clobRetrieved = rs.getClob(1);
729
730         // Verify read back data.
731
byte[] dbBytes = new byte[10];
732         InputStream isRetrieved = clobRetrieved.getAsciiStream();
733         assertEquals("Unexpected number of bytes read", BYTES.length,
734                 isRetrieved.read(dbBytes));
735         assertEquals("Stream should be exhausted", -1, isRetrieved.read());
736         for (int i=0; i < BYTES.length; i++) {
737             assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]);
738         }
739
740         // Cleanup
741
isRetrieved.close();
742         psInsertClob.close();
743     }
744
745     /**
746      *
747      * Tests the PreparedStatement interface method setBinaryStream
748      *
749      * @throws SQLException
750      *
751      */

752     
753     public void testSetBinaryStream() throws Exception JavaDoc {
754         //insert default values into the table
755

756         byte [] bytes1 = new byte[10];
757         
758         InputStream is = new java.io.ByteArrayInputStream JavaDoc(BYTES);
759         
760         is.reset();
761         
762         //initially insert the data
763
psInsertBlob.setInt(1, key);
764         psInsertBlob.setBinaryStream(2, is, BYTES.length);
765         psInsertBlob.executeUpdate();
766         
767         //Now query to retrieve the Clob
768
psFetchBlob.setInt(1, key);
769         ResultSet rs = psFetchBlob.executeQuery();
770         rs.next();
771         Blob blobRetrieved = rs.getBlob(1);
772         rs.close();
773         
774         try {
775             InputStream is_ret = blobRetrieved.getBinaryStream();
776             is_ret.read(bytes1);
777         } catch(IOException ioe) {
778             fail("IOException while reading the Clob from the database");
779         }
780         
781         for(int i=0;i<BYTES.length;i++) {
782             assertEquals("Error in inserting data into the Blob",BYTES[i],bytes1[i]);
783         }
784         psInsertBlob.close();
785     }
786
787     public void testSetBinaryStreamLengthless()
788             throws IOException, SQLException {
789         // Insert test data.
790
InputStream is = new ByteArrayInputStream(BYTES);
791         psInsertBlob.setInt(1, key);
792         psInsertBlob.setBinaryStream(2, is);
793         psInsertBlob.execute();
794         is.close();
795
796         // Read back test data from database.
797
psFetchBlob.setInt(1, key);
798         ResultSet rs = psFetchBlob.executeQuery();
799         assertTrue("No results retrieved", rs.next());
800         Blob blobRetrieved = rs.getBlob(1);
801
802         // Verify read back data.
803
byte[] dbBytes = new byte[10];
804         InputStream isRetrieved = blobRetrieved.getBinaryStream();
805         assertEquals("Unexpected number of bytes read", BYTES.length,
806                 isRetrieved.read(dbBytes));
807         assertEquals("Stream should be exhausted", -1, isRetrieved.read());
808         for (int i=0; i < BYTES.length; i++) {
809             assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]);
810         }
811
812         // Cleanup
813
isRetrieved.close();
814         psInsertBlob.close();
815     }
816
817     public void testSetBinaryStreamLengthLess1KOnBlob()
818             throws IOException, SQLException {
819         int length = 1*1024;
820         setBinaryStreamOnBlob(key, length, -1, 0, true);
821         psFetchBlob.setInt(1, key);
822         ResultSet rs = psFetchBlob.executeQuery();
823         assertTrue("Empty resultset", rs.next());
824         assertEquals(new LoopingAlphabetStream(length),
825                      rs.getBinaryStream(1));
826         assertFalse("Resultset should have been exhausted", rs.next());
827         rs.close();
828     }
829
830     public void testSetBinaryStreamLengthLess32KOnBlob()
831             throws IOException, SQLException {
832         int length = 32*1024;
833         setBinaryStreamOnBlob(key, length, -1, 0, true);
834         psFetchBlob.setInt(1, key);
835         ResultSet rs = psFetchBlob.executeQuery();
836         assertTrue("Empty resultset", rs.next());
837         assertEquals(new LoopingAlphabetStream(length),
838                      rs.getBinaryStream(1));
839         assertFalse("Resultset should have been exhausted", rs.next());
840         rs.close();
841     }
842
843     public void testSetBinaryStreamLengthLess65KOnBlob()
844             throws IOException, SQLException {
845         int length = 65*1024;
846         setBinaryStreamOnBlob(key, length, -1, 0, true);
847         psFetchBlob.setInt(1, key);
848         ResultSet rs = psFetchBlob.executeQuery();
849         assertTrue("Empty resultset", rs.next());
850         LoopingAlphabetStream s1 = new LoopingAlphabetStream(length);
851         assertEquals(new LoopingAlphabetStream(length),
852                      rs.getBinaryStream(1));
853         assertFalse("Resultset should have been exhausted", rs.next());
854         rs.close();
855     }
856
857     public void testSetBinaryStreamLengthLessOnBlobTooLong() {
858         int length = 1*1024*1024+512;
859         try {
860             setBinaryStreamOnBlob(key, length, -1, 0, true);
861         } catch (SQLException sqle) {
862             if (usingEmbedded()) {
863                 assertSQLState("XSDA4", sqle);
864             } else {
865                 assertSQLState("22001", sqle);
866             }
867         }
868     }
869
870     public void testExceptionPathOnePage_bs()
871             throws SQLException {
872         int length = 11;
873         try {
874             setBinaryStreamOnBlob(key, length -1, length, 0, false);
875             fail("Inserted a BLOB with fewer bytes than specified");
876         } catch (SQLException sqle) {
877             if (usingEmbedded()) {
878                 assertSQLState("XSDA4", sqle);
879             } else {
880                 assertSQLState("XN017", sqle);
881             }
882         }
883     }
884
885     public void testExceptionPathMultiplePages_bs()
886             throws SQLException {
887         int length = 1*1024*1024;
888         try {
889             setBinaryStreamOnBlob(key, length -1, length, 0, false);
890             fail("Inserted a BLOB with fewer bytes than specified");
891         } catch (SQLException sqle) {
892             if (usingEmbedded()) {
893                 assertSQLState("XSDA4", sqle);
894             } else {
895                 assertSQLState("XN017", sqle);
896             }
897         }
898     }
899
900     public void testBlobExceptionDoesNotRollbackOtherStatements()
901             throws IOException, SQLException {
902         getConnection().setAutoCommit(false);
903         int[] keys = {key, requestKey(), requestKey()};
904         for (int i=0; i < keys.length; i++) {
905             psInsertBlob.setInt(1, keys[i]);
906             psInsertBlob.setNull(2, Types.BLOB);
907             assertEquals(1, psInsertBlob.executeUpdate());
908         }
909         // Now insert a BLOB that fails because the stream is too short.
910
int failedKey = requestKey();
911         int length = 1*1024*1024;
912         try {
913             setBinaryStreamOnBlob(failedKey, length -1, length, 0, false);
914             fail("Inserted a BLOB with less data than specified");
915         } catch (SQLException sqle) {
916             if (usingEmbedded()) {
917                 assertSQLState("XSDA4", sqle);
918             } else {
919                 assertSQLState("XN017", sqle);
920             }
921         }
922         // Now make sure the previous statements are there, and that the last
923
// BLOB is not.
924
ResultSet rs;
925         for (int i=0; i < keys.length; i++) {
926             psFetchBlob.setInt(1, keys[i]);
927             rs = psFetchBlob.executeQuery();
928             assertTrue(rs.next());
929             assertFalse(rs.next());
930             rs.close();
931         }
932         psFetchBlob.setInt(1, failedKey);
933         rs = psFetchBlob.executeQuery();
934         // When using the Derby client driver, the data seems to be padded
935
// with 0s and inserted... Thus, the select returns a row.
936
if (!usingEmbedded()) {
937             assertTrue(rs.next());
938             InputStream is = rs.getBinaryStream(1);
939             int lastByte = -1;
940             int b = 99; // Just a value > 0.
941
while (b > -1) {
942                 lastByte = b;
943                 b = is.read();
944             }
945             assertEquals("Last padded byte is not 0", 0, lastByte);
946         }
947         assertFalse(rs.next());
948         rs.close();
949         rollback();
950         // Make sure all data is gone after the rollback.
951
for (int i=0; i < keys.length; i++) {
952             psFetchBlob.setInt(1, keys[i]);
953             rs = psFetchBlob.executeQuery();
954             assertFalse(rs.next());
955             rs.close();
956         }
957         // Make sure the failed insert has not "reappeared" somehow...
958
psFetchBlob.setInt(1, failedKey);
959         rs = psFetchBlob.executeQuery();
960         assertFalse(rs.next());
961
962     }
963
964     public void testSetAsciiStreamLengthLess1KOnClob()
965             throws IOException, SQLException {
966         int length = 1*1024;
967         setAsciiStream(psInsertClob, key, length, -1, 0, true);
968         psFetchClob.setInt(1, key);
969         ResultSet rs = psFetchClob.executeQuery();
970         assertTrue("Empty resultset", rs.next());
971         assertEquals(new LoopingAlphabetStream(length),
972                      rs.getAsciiStream(1));
973         assertFalse("Resultset should have been exhausted", rs.next());
974         rs.close();
975     }
976
977     public void testSetAsciiStreamLengthLess32KOnClob()
978             throws IOException, SQLException {
979         int length = 32*1024;
980         setAsciiStream(psInsertClob, key, length, -1, 0, true);
981         psFetchClob.setInt(1, key);
982         ResultSet rs = psFetchClob.executeQuery();
983         assertTrue("Empty resultset", rs.next());
984         assertEquals(new LoopingAlphabetStream(length),
985                      rs.getAsciiStream(1));
986         assertFalse("Resultset should have been exhausted", rs.next());
987         rs.close();
988     }
989
990     public void testSetAsciiStreamLengthLess65KOnClob()
991             throws IOException, SQLException {
992         int length = 65*1024;
993         setAsciiStream(psInsertClob, key, length, -1, 0, true);
994         psFetchClob.setInt(1, key);
995         ResultSet rs = psFetchClob.executeQuery();
996         assertTrue("Empty resultset", rs.next());
997         assertEquals(new LoopingAlphabetStream(length),
998                      rs.getAsciiStream(1));
999         assertFalse("Resultset should have been exhausted", rs.next());
1000        rs.close();
1001    }
1002
1003    public void testSetAsciiStreamLengthLessOnClobTooLong() {
1004        int length = 1*1024*1024+512;
1005        try {
1006            setAsciiStream(psInsertClob, key, length, -1, 0, true);
1007        } catch (SQLException sqle) {
1008            if (usingEmbedded()) {
1009                assertSQLState("XSDA4", sqle);
1010            } else {
1011                assertSQLState("22001", sqle);
1012            }
1013        }
1014    }
1015
1016    public void testSetAsciiStreamLengthLessOnClobTooLongTruncate()
1017            throws SQLException {
1018        int trailingBlanks = 512;
1019        int length = 1*1024*1024 + trailingBlanks;
1020        setAsciiStream(psInsertClob, key, length, -1, trailingBlanks, true);
1021    }
1022
1023    public void testSetAsciiStreamLengthlessOnLongVarCharTooLong() {
1024        int length = 32700+512;
1025        try {
1026            setAsciiStream(psInsertLongVarchar, key, length, -1, 0, true);
1027            fail("Inserted a LONG VARCHAR that is too long");
1028        } catch (SQLException sqle) {
1029            if (usingEmbedded()) {
1030                assertInternalDerbyIOExceptionState("XCL30", "22001", sqle);
1031            } else {
1032                assertSQLState("22001", sqle);
1033            }
1034        }
1035    }
1036
1037    public void testSetAsciiStreamLengthlessOnLongVarCharDontTruncate() {
1038        int trailingBlanks = 2000;
1039        int length = 32000 + trailingBlanks;
1040        try {
1041            setAsciiStream(psInsertLongVarchar, key, length, -1,
1042                    trailingBlanks, true);
1043            fail("Truncation is not allowed for LONG VARCHAR");
1044        } catch (SQLException sqle) {
1045            if (usingEmbedded()) {
1046                assertInternalDerbyIOExceptionState("XCL30", "22001", sqle);
1047            } else {
1048                assertSQLState("22001", sqle);
1049            }
1050        }
1051    }
1052
1053    /************************************************************************
1054     * A U X I L I A R Y M E T H O D S *
1055     ************************************************************************/

1056
1057    /**
1058     * Insert data into a Blob column with setBinaryStream.
1059     *
1060     * @param id unique id for inserted row
1061     * @param actualLength the actual length of the stream
1062     * @param specifiedLength the specified length of the stream
1063     * @param trailingBlanks number of characters at the end that is blank
1064     * @param lengthLess whether to use the length less overloads or not
1065     */

1066    private void setBinaryStreamOnBlob(int id,
1067                                       int actualLength,
1068                                       int specifiedLength,
1069                                       int trailingBlanks,
1070                                       boolean lengthLess)
1071            throws SQLException {
1072        psInsertBlob.setInt(1, id);
1073        if (lengthLess) {
1074            psInsertBlob.setBinaryStream(2, new LoopingAlphabetStream(
1075                                                actualLength,
1076                                                trailingBlanks));
1077        } else {
1078            psInsertBlob.setBinaryStream(2,
1079                               new LoopingAlphabetStream(
1080                                        actualLength,
1081                                        trailingBlanks),
1082                               specifiedLength);
1083        }
1084        assertEquals("Insert with setBinaryStream failed",
1085                1, psInsertBlob.executeUpdate());
1086    }
1087
1088    /**
1089     * Insert data into a column with setAsciiStream.
1090     * The prepared statement passed must have two positional parameters;
1091     * one int and one more. Depending on the last parameter, the execute
1092     * might succeed or it might fail. This is intended behavior, and should
1093     * be handled by the caller. For instance, calling this method on an
1094     * INT-column would fail, calling it on a CLOB-column would succeed.
1095     *
1096     * @param id unique id for inserted row
1097     * @param actualLength the actual length of the stream
1098     * @param specifiedLength the specified length of the stream
1099     * @param trailingBlanks number of characters at the end that is blank
1100     * @param lengthLess whether to use the length less overloads or not
1101     */

1102    private void setAsciiStream(PreparedStatement ps,
1103                                int id,
1104                                int actualLength,
1105                                int specifiedLength,
1106                                int trailingBlanks,
1107                                boolean lengthLess)
1108            throws SQLException {
1109        ps.setInt(1, id);
1110        if (lengthLess) {
1111            ps.setAsciiStream(2,
1112                              new LoopingAlphabetStream(
1113                                                actualLength,
1114                                                trailingBlanks));
1115        } else {
1116            ps.setAsciiStream(2,
1117                              new LoopingAlphabetStream(
1118                                                actualLength,
1119                                                trailingBlanks),
1120                              specifiedLength);
1121        }
1122        assertEquals("Insert with setAsciiStream failed",
1123                1, ps.executeUpdate());
1124    }
1125
1126    /**
1127     * Get next key to id inserted data with.
1128     */

1129    private static int requestKey() {
1130        return globalKey++;
1131    }
1132
1133    /**
1134     * Return the last chained SQLException.
1135     * If there are no exceptions chained, the original one is returned.
1136     */

1137    private SQLException getLastSQLException(SQLException sqle) {
1138        SQLException last = sqle;
1139        SQLException next = sqle;
1140        while (next != null) {
1141            last = next;
1142            next = last.getNextException();
1143        }
1144        return last;
1145    }
1146
1147    /**
1148     * This methods is not to be used, but sometimes you have to!
1149     *
1150     * @param preSQLState the expected outer SQL state
1151     * @param expectedInternal the expected internal SQL state
1152     * @param sqle the outer SQLException
1153     */

1154    private void assertInternalDerbyIOExceptionState(
1155                                        String JavaDoc preSQLState,
1156                                        String JavaDoc expectedInternal,
1157                                        SQLException sqle) {
1158        assertSQLState("Outer/public SQL state incorrect",
1159                       preSQLState, sqle);
1160        // We need to dig a little with the current way exceptions are
1161
// being reported. We can use getCause because we always run with
1162
// Mustang/Java SE 6.
1163
Throwable JavaDoc cause = getLastSQLException(sqle).getCause();
1164        assertTrue("Exception not an EmbedSQLException",
1165                   cause instanceof EmbedSQLException);
1166        cause = ((EmbedSQLException)cause).getJavaException();
1167        assertTrue("Exception not a DerbyIOException",
1168                   cause instanceof DerbyIOException);
1169        DerbyIOException dioe = (DerbyIOException)cause;
1170        assertEquals("Incorrect internal SQL state", expectedInternal,
1171                     dioe.getSQLState());
1172    }
1173}
1174
Popular Tags