Source Code Cross Referenced for PreparedStatementTest.java in  » Database-DBMS » db-derby-10.2 » org » apache » derbyTesting » functionTests » tests » jdbc4 » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » Database DBMS » db derby 10.2 » org.apache.derbyTesting.functionTests.tests.jdbc4 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


0001:        /*
0002:         
0003:           Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc4.PreparedStatementTest
0004:         
0005:           Licensed to the Apache Software Foundation (ASF) under one or more
0006:           contributor license agreements.  See the NOTICE file distributed with
0007:           this work for additional information regarding copyright ownership.
0008:           The ASF licenses this file to you under the Apache License, Version 2.0
0009:           (the "License"); you may not use this file except in compliance with
0010:           the License.  You may obtain a copy of the License at
0011:         
0012:              http://www.apache.org/licenses/LICENSE-2.0
0013:         
0014:           Unless required by applicable law or agreed to in writing, software
0015:           distributed under the License is distributed on an "AS IS" BASIS,
0016:           WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017:           See the License for the specific language governing permissions and
0018:           limitations under the License.
0019:         
0020:         */
0021:
0022:        package org.apache.derbyTesting.functionTests.tests.jdbc4;
0023:
0024:        import junit.framework.*;
0025:
0026:        import org.apache.derbyTesting.junit.BaseJDBCTestCase;
0027:        import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
0028:        import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetStream;
0029:
0030:        import java.io.*;
0031:        import java.sql.*;
0032:        import javax.sql.*;
0033:
0034:        import org.apache.derby.iapi.services.io.DerbyIOException;
0035:        import org.apache.derby.impl.jdbc.EmbedSQLException;
0036:
0037:        /**
0038:         * This class is used to test JDBC4 specific methods in the PreparedStatement(s)
0039:         * object.
0040:         *
0041:         * A number of methods and variables are in place to aid the writing of tests:
0042:         * <ul><li>setBinaryStreamOnBlob
0043:         *     <li>setAsciiStream
0044:         *     <li>key - an id. One is generated each time setUp is run.
0045:         *     <li>reqeustKey() - generate a new unique id.
0046:         *     <li>psInsertX - prepared statements for insert.
0047:         *     <li>psFetchX - prepared statements for fetching values.
0048:         * </ul>
0049:         *
0050:         * For table creation, see the <code>suite</code>-method.
0051:         */
0052:        public class PreparedStatementTest extends BaseJDBCTestCase {
0053:
0054:            private static final String BLOBTBL = "BlobTestTable";
0055:            private static final String CLOBTBL = "ClobTestTable";
0056:            private static final String LONGVARCHAR = "LongVarcharTestTable";
0057:
0058:            /** Key used to id data inserted into the database. */
0059:            private static int globalKey = 1;
0060:
0061:            /** Byte array passed in to the database. **/
0062:            private static final byte[] BYTES = { 0x65, 0x66, 0x67, 0x68, 0x69,
0063:                    0x69, 0x68, 0x67, 0x66, 0x65 };
0064:
0065:            // Default connection and prepared statements that are used by the tests.
0066:            /** 
0067:             * Default key to use for insertions.
0068:             * Is unique for each fixture. More keys can be fetched by calling
0069:             * <link>requestKey</link>.
0070:             */
0071:            private int key;
0072:            /** Default connection object. */
0073:            /** PreparedStatement object with no positional arguments. */
0074:            private PreparedStatement ps = null;
0075:            /** PreparedStatement to fetch BLOB with specified id. */
0076:            private PreparedStatement psFetchBlob = null;
0077:            /** PreparedStatement to insert a BLOB with specified id. */
0078:            private PreparedStatement psInsertBlob = null;
0079:            /** PreparedStatement to fetch CLOB with specified id. */
0080:            private PreparedStatement psFetchClob = null;
0081:            /** PreparedStatement to insert a CLOB with specified id. */
0082:            private PreparedStatement psInsertClob = null;
0083:            /** PreparedStatement to insert a LONG VARCHAR with specified id. */
0084:            private PreparedStatement psInsertLongVarchar = null;
0085:            //Statement object
0086:            private Statement s = null;
0087:
0088:            /**
0089:             * Create a test with the given name.
0090:             * 
0091:             * @param name name of the test.
0092:             */
0093:            public PreparedStatementTest(String name) {
0094:                super (name);
0095:            }
0096:
0097:            /**
0098:             *
0099:             * Obtain a "regular" connection and PreparedStatement that the tests 
0100:             * can use.
0101:             * 
0102:             * @throws SQLException
0103:             */
0104:            public void setUp() throws SQLException {
0105:                key = requestKey();
0106:                //create the statement object
0107:                s = createStatement();
0108:                //Create the PreparedStatement that will then be used as the basis 
0109:                //throughout this test henceforth
0110:                //This prepared statement will however NOT be used for testing
0111:                //setClob and setBlob
0112:                ps = prepareStatement("select count(*) from sys.systables");
0113:
0114:                // Prepare misc statements.
0115:                psFetchBlob = prepareStatement("SELECT dBlob FROM " + BLOBTBL
0116:                        + " WHERE sno = ?");
0117:                psInsertBlob = prepareStatement("INSERT INTO " + BLOBTBL
0118:                        + " VALUES (?, ?)");
0119:                psFetchClob = prepareStatement("SELECT dClob FROM " + CLOBTBL
0120:                        + " WHERE sno = ?");
0121:                psInsertClob = prepareStatement("INSERT INTO " + CLOBTBL
0122:                        + " VALUES (?, ?)");
0123:                psInsertLongVarchar = prepareStatement("INSERT INTO "
0124:                        + LONGVARCHAR + " VALUES (?, ?)");
0125:            }
0126:
0127:            /**
0128:             *
0129:             * Release the resources that are used in this test
0130:             *
0131:             * @throws SQLException
0132:             *
0133:             */
0134:            public void tearDown() throws Exception {
0135:
0136:                psFetchBlob.close();
0137:                psFetchClob.close();
0138:                psInsertBlob.close();
0139:                psInsertClob.close();
0140:                psInsertLongVarchar.close();
0141:
0142:                super .tearDown();
0143:            }
0144:
0145:            public static Test suite() {
0146:                TestSuite suite = new TestSuite();
0147:                suite.addTestSuite(PreparedStatementTest.class);
0148:                suite.addTest(SetObjectUnsupportedTest.suite(false));
0149:                return new BaseJDBCTestSetup(suite) {
0150:                    public void setUp() throws java.lang.Exception {
0151:                        try {
0152:                            create();
0153:                        } catch (SQLException sqle) {
0154:                            if (sqle.getSQLState().equals("X0Y32")) {
0155:                                drop();
0156:                                create();
0157:                            } else {
0158:                                throw sqle;
0159:                            }
0160:                        }
0161:                    }
0162:
0163:                    public void tearDown() throws java.lang.Exception {
0164:                        drop();
0165:                        super .tearDown();
0166:                    }
0167:
0168:                    private void create() throws SQLException {
0169:                        Statement stmt = getConnection().createStatement();
0170:                        stmt.execute("create table " + BLOBTBL
0171:                                + " (sno int, dBlob BLOB(1M))");
0172:                        stmt.execute("create table " + CLOBTBL
0173:                                + " (sno int, dClob CLOB(1M))");
0174:                        stmt.execute("create table " + LONGVARCHAR
0175:                                + " (sno int, dLongVarchar LONG VARCHAR)");
0176:                        stmt.close();
0177:                    }
0178:
0179:                    private void drop() throws SQLException {
0180:                        Statement stmt = getConnection().createStatement();
0181:                        stmt.execute("drop table " + BLOBTBL);
0182:                        stmt.execute("drop table " + CLOBTBL);
0183:                        stmt.execute("drop table " + LONGVARCHAR);
0184:                        stmt.close();
0185:                    }
0186:                };
0187:            }
0188:
0189:            //--------------------------------------------------------------------------
0190:            //BEGIN THE TEST OF THE METHODS THAT THROW AN UNIMPLEMENTED EXCEPTION IN
0191:            //THIS CLASS
0192:
0193:            /**
0194:             * Tests the setRowId method of the PreparedStatement interface
0195:             *
0196:             * @throws SQLException upon any failure that occurs in the 
0197:             *         call to the method.
0198:             */
0199:            public void testSetRowId() throws SQLException {
0200:                try {
0201:                    RowId rowid = null;
0202:                    ps.setRowId(0, rowid);
0203:                    fail("setRowId should not be implemented");
0204:                } catch (SQLFeatureNotSupportedException sqlfne) {
0205:                    //Do Nothing, This happens as expected
0206:                }
0207:            }
0208:
0209:            /**
0210:             * Tests the setNString method of the PreparedStatement interface
0211:             *
0212:             * @throws SQLException upon any failure that occurs in the 
0213:             *         call to the method.
0214:             */
0215:            public void testSetNString() throws SQLException {
0216:                try {
0217:                    String str = null;
0218:                    ps.setNString(0, str);
0219:                    fail("setNString should not be implemented");
0220:                } catch (SQLFeatureNotSupportedException sqlfne) {
0221:                    //Do Nothing, This happens as expected
0222:                }
0223:            }
0224:
0225:            /**
0226:             * Tests the setNCharacterStream method of the PreparedStatement interface
0227:             *
0228:             * @throws SQLException upon any failure that occurs in the 
0229:             *         call to the method.
0230:             */
0231:            public void testSetNCharacterStream() throws SQLException {
0232:                try {
0233:                    Reader r = null;
0234:                    ps.setNCharacterStream(0, r, 0);
0235:                    fail("setNCharacterStream should not be implemented");
0236:                } catch (SQLFeatureNotSupportedException sqlfne) {
0237:                    //Do Nothing, This happens as expected
0238:                }
0239:            }
0240:
0241:            public void testSetNCharacterStreamLengthlessNotImplemented()
0242:                    throws SQLException {
0243:                try {
0244:                    ps.setNCharacterStream(1, new StringReader("A string"));
0245:                    fail("setNCharacterStream(int,Reader) should not be implemented");
0246:                } catch (SQLFeatureNotSupportedException sfnse) {
0247:                    // Do nothing, this is expected behavior.
0248:                }
0249:            }
0250:
0251:            public void testSetNClobLengthlessNotImplemented()
0252:                    throws SQLException {
0253:                try {
0254:                    ps.setNClob(1, new StringReader("A string"));
0255:                    fail("setNClob(int,Reader) should not be implemented");
0256:                } catch (SQLFeatureNotSupportedException sfnse) {
0257:                    // Do nothing, this is expected behaviour.
0258:                }
0259:            }
0260:
0261:            /**
0262:             * Tests the setNClob method of the PreparedStatement interface
0263:             *
0264:             * @throws SQLException upon any failure that occurs in the 
0265:             *         call to the method.
0266:             */
0267:            public void testSetNClob1() throws SQLException {
0268:                try {
0269:                    NClob nclob = null;
0270:                    ps.setNClob(0, nclob);
0271:                    fail("setNClob should not be implemented");
0272:                } catch (SQLFeatureNotSupportedException sqlfne) {
0273:                    //Do Nothing, This happens as expected
0274:                }
0275:            }
0276:
0277:            /**
0278:             * Tests the setNClob method of the PreparedStatement interface
0279:             *
0280:             * @throws SQLException upon any failure that occurs in the 
0281:             *         call to the method.
0282:             */
0283:            public void testSetNClob2() throws SQLException {
0284:                try {
0285:                    Reader reader = null;
0286:                    ps.setNClob(0, reader, 0);
0287:                    fail("setNClob should not be implemented");
0288:                } catch (SQLFeatureNotSupportedException sqlfne) {
0289:                    //Do Nothing, This happens as expected
0290:                }
0291:            }
0292:
0293:            /**
0294:             * Tests the setSQLXML method of the PreparedStatement interface
0295:             *
0296:             * @throws SQLException upon any failure that occurs in the 
0297:             *         call to the method.
0298:             */
0299:            public void testSetSQLXML() throws SQLException {
0300:                try {
0301:                    SQLXML sqlxml = null;
0302:                    ps.setSQLXML(0, sqlxml);
0303:                    fail("setNClob should not be implemented");
0304:                } catch (SQLFeatureNotSupportedException sqlfne) {
0305:                    //Do Nothing, This happens as expected
0306:                }
0307:            }
0308:
0309:            //--------------------------------------------------------------------------
0310:            //Now test the methods that are implemented in the PreparedStatement 
0311:            //interface
0312:
0313:            public void testIsWrapperForStatement() throws SQLException {
0314:                assertTrue(ps.isWrapperFor(Statement.class));
0315:            }
0316:
0317:            public void testIsWrapperForPreparedStatement() throws SQLException {
0318:                assertTrue(ps.isWrapperFor(PreparedStatement.class));
0319:            }
0320:
0321:            public void testIsNotWrapperForCallableStatement()
0322:                    throws SQLException {
0323:                assertFalse(ps.isWrapperFor(CallableStatement.class));
0324:            }
0325:
0326:            public void testIsNotWrapperForResultSet() throws SQLException {
0327:                assertFalse(ps.isWrapperFor(ResultSet.class));
0328:            }
0329:
0330:            public void testUnwrapStatement() throws SQLException {
0331:                Statement stmt = ps.unwrap(Statement.class);
0332:                assertSame("Unwrap returned wrong object.", ps, stmt);
0333:            }
0334:
0335:            public void testUnwrapPreparedStatement() throws SQLException {
0336:                PreparedStatement ps2 = ps.unwrap(PreparedStatement.class);
0337:                assertSame("Unwrap returned wrong object.", ps, ps2);
0338:            }
0339:
0340:            public void testUnwrapCallableStatement() {
0341:                try {
0342:                    CallableStatement cs = ps.unwrap(CallableStatement.class);
0343:                    fail("Unwrap didn't fail.");
0344:                } catch (SQLException e) {
0345:                    assertSQLState("XJ128", e);
0346:                }
0347:            }
0348:
0349:            public void testUnwrapResultSet() {
0350:                try {
0351:                    ResultSet rs = ps.unwrap(ResultSet.class);
0352:                    fail("Unwrap didn't fail.");
0353:                } catch (SQLException e) {
0354:                    assertSQLState("XJ128", e);
0355:                }
0356:            }
0357:
0358:            //-----------------------------------------------------------------------
0359:            // Begin test for setClob and setBlob
0360:
0361:            /*
0362:               we need a table in which a Clob or a Blob can be stored. We basically
0363:               need to write tests for the setClob and the setBlob methods. 
0364:               Proper process would be
0365:               a) Do a createClob or createBlob
0366:               b) Populate data in the LOB
0367:               c) Store in Database
0368:
0369:               But the createClob and createBlob implementations are not 
0370:               available on the EmbeddedServer. So instead the workaround adopted
0371:               is 
0372:
0373:               a) store a Clob or Blob in Database. 
0374:               b) Retrieve it from the database.
0375:               c) store it back using setClob or setBlob
0376:
0377:             */
0378:
0379:            /**
0380:             *
0381:             * Test the setClob() method
0382:             *
0383:             * @throws SQLException if a failure occurs during the call to setClob
0384:             *
0385:             */
0386:            public void testSetClob() throws IOException, SQLException {
0387:                //insert default values into the table
0388:
0389:                String str = "Test data for the Clob object";
0390:                StringReader is = new StringReader(
0391:                        "Test data for the Clob object");
0392:                is.reset();
0393:
0394:                //initially insert the data
0395:                psInsertClob.setInt(1, key);
0396:                psInsertClob.setClob(2, is, str.length());
0397:                psInsertClob.executeUpdate();
0398:
0399:                //Now query to retrieve the Clob
0400:                psFetchClob.setInt(1, key);
0401:                ResultSet rs = psFetchClob.executeQuery();
0402:                rs.next();
0403:                Clob clobToBeInserted = rs.getClob(1);
0404:                rs.close();
0405:
0406:                //Now use the setClob method
0407:                int secondKey = requestKey();
0408:                psInsertClob.setInt(1, secondKey);
0409:                psInsertClob.setClob(2, clobToBeInserted);
0410:                psInsertClob.execute();
0411:
0412:                psInsertClob.close();
0413:
0414:                //Now test to see that the Clob has been stored correctly
0415:                psFetchClob.setInt(1, secondKey);
0416:                rs = psFetchClob.executeQuery();
0417:                rs.next();
0418:                Clob clobRetrieved = rs.getClob(1);
0419:
0420:                assertEquals(clobToBeInserted, clobRetrieved);
0421:            }
0422:
0423:            /**
0424:             * Insert <code>Clob</code> without specifying length and read it back
0425:             * for verification.
0426:             *
0427:             * Beacuse we don't yet support <code>Connection.createClob</code> in the
0428:             * client driver, we must first insert data into the database and read back
0429:             * a <code>Clob</code> object. This object is then inserted into the
0430:             * database again.
0431:             */
0432:            public void testSetClobLengthless() throws IOException,
0433:                    SQLException {
0434:                // Insert test data.
0435:                String testString = "Test string for setCharacterStream\u1A00";
0436:                Reader reader = new StringReader(testString);
0437:                psInsertClob.setInt(1, key);
0438:                psInsertClob.setCharacterStream(2, reader);
0439:                psInsertClob.execute();
0440:                reader.close();
0441:                // Must fetch Clob from database because we don't support
0442:                // Connection.createClob on the client yet.
0443:                psFetchClob.setInt(1, key);
0444:                ResultSet rs = psFetchClob.executeQuery();
0445:                assertTrue("No results retrieved", rs.next());
0446:                int secondKey = requestKey();
0447:                Clob insertClob = rs.getClob(1);
0448:                psInsertClob.setInt(1, secondKey);
0449:                psInsertClob.setClob(2, insertClob);
0450:                psInsertClob.execute();
0451:
0452:                // Read back test data from database.
0453:                psFetchClob.setInt(1, secondKey);
0454:                rs = psFetchClob.executeQuery();
0455:                assertTrue("No results retrieved", rs.next());
0456:                Clob clobRetrieved = rs.getClob(1);
0457:
0458:                // Verify test data.
0459:                assertEquals(insertClob, clobRetrieved);
0460:            }
0461:
0462:            /**
0463:             *
0464:             * Test the setBlob() method
0465:             *
0466:             * @throws SQLException if a failure occurs during the call to setBlob
0467:             *
0468:             */
0469:            public void testSetBlob() throws IOException, SQLException {
0470:                //insert default values into the table
0471:
0472:                InputStream is = new java.io.ByteArrayInputStream(BYTES);
0473:                is.reset();
0474:
0475:                //initially insert the data
0476:                psInsertBlob.setInt(1, key);
0477:                psInsertBlob.setBlob(2, is, BYTES.length);
0478:                psInsertBlob.executeUpdate();
0479:
0480:                //Now query to retrieve the Blob
0481:                psFetchBlob.setInt(1, key);
0482:                ResultSet rs = psFetchBlob.executeQuery();
0483:                rs.next();
0484:                Blob blobToBeInserted = rs.getBlob(1);
0485:                rs.close();
0486:
0487:                //Now use the setBlob method
0488:                int secondKey = requestKey();
0489:                psInsertBlob.setInt(1, secondKey);
0490:                psInsertBlob.setBlob(2, blobToBeInserted);
0491:                psInsertBlob.execute();
0492:
0493:                psInsertBlob.close();
0494:
0495:                //Now test to see that the Blob has been stored correctly
0496:                psFetchBlob.setInt(1, secondKey);
0497:                rs = psFetchBlob.executeQuery();
0498:                rs.next();
0499:                Blob blobRetrieved = rs.getBlob(1);
0500:
0501:                assertEquals(blobToBeInserted, blobRetrieved);
0502:            }
0503:
0504:            /**
0505:             * Insert <code>Blob</code> without specifying length and read it back
0506:             * for verification.
0507:             *
0508:             * Beacuse we don't yet support <code>Connection.createBlob</code> in the
0509:             * client driver, we must first insert data into the database and read back
0510:             * a <code>Blob</code> object. This object is then inserted into the
0511:             * database again.
0512:             */
0513:            public void testSetBlobLengthless() throws IOException,
0514:                    SQLException {
0515:                // Insert test data.
0516:                InputStream is = new ByteArrayInputStream(BYTES);
0517:                psInsertBlob.setInt(1, key);
0518:                psInsertBlob.setBinaryStream(2, is);
0519:                psInsertBlob.execute();
0520:                is.close();
0521:                // Must fetch Blob from database because we don't support
0522:                // Connection.createBlob on the client yet.
0523:                psFetchBlob.setInt(1, key);
0524:                ResultSet rs = psFetchBlob.executeQuery();
0525:                assertTrue("No results retrieved", rs.next());
0526:                Blob insertBlob = rs.getBlob(1);
0527:                int secondKey = requestKey();
0528:                psInsertBlob.setInt(1, secondKey);
0529:                psInsertBlob.setBlob(2, insertBlob);
0530:                psInsertBlob.execute();
0531:
0532:                // Read back test data from database.
0533:                psFetchBlob.setInt(1, secondKey);
0534:                rs = psFetchBlob.executeQuery();
0535:                assertTrue("No results retrieved", rs.next());
0536:                Blob blobRetrieved = rs.getBlob(1);
0537:
0538:                // Verify test data.
0539:                assertEquals(insertBlob, blobRetrieved);
0540:            }
0541:
0542:            //-------------------------------------------------
0543:            //Test the methods used to test poolable statements
0544:
0545:            /**
0546:             *
0547:             * Tests the PreparedStatement interface method setPoolable
0548:             *
0549:             * @throws SQLException
0550:             */
0551:
0552:            public void testSetPoolable() throws SQLException {
0553:                try {
0554:                    // Set the poolable statement hint to false
0555:                    ps.setPoolable(false);
0556:                    if (ps.isPoolable())
0557:                        fail("Expected a non-poolable statement");
0558:                    // Set the poolable statement hint to true
0559:                    ps.setPoolable(true);
0560:                    if (!ps.isPoolable())
0561:                        fail("Expected a poolable statement");
0562:                } catch (SQLException sqle) {
0563:                    // Check which SQLException state we've got and if it is
0564:                    // expected, do not print a stackTrace
0565:                    // Embedded uses XJ012, client uses XCL31.
0566:                    if (sqle.getSQLState().equals("XJ012")
0567:                            || sqle.getSQLState().equals("XCL31")) {
0568:                        // All is good and is expected
0569:                    } else {
0570:                        fail("Unexpected SQLException " + sqle);
0571:                    }
0572:                } catch (Exception e) {
0573:                    fail("Unexpected exception thrown in method " + e);
0574:                }
0575:            }
0576:
0577:            /**
0578:             *
0579:             * Tests the PreparedStatement interface method isPoolable
0580:             *
0581:             * @throws SQLException
0582:             *
0583:             */
0584:
0585:            public void testIsPoolable() throws SQLException {
0586:                try {
0587:                    // By default a prepared statement is poolable
0588:                    if (!ps.isPoolable())
0589:                        fail("Expected a poolable statement");
0590:                } catch (SQLException sqle) {
0591:                    // Check which SQLException state we've got and if it is
0592:                    // expected, do not print a stackTrace
0593:                    // Embedded uses XJ012, client uses XCL31.
0594:                    if (sqle.getSQLState().equals("XJ012")
0595:                            || sqle.getSQLState().equals("XCL31")) {
0596:                        // All is good and is expected
0597:                    } else {
0598:                        fail("Unexpected SQLException " + sqle);
0599:                    }
0600:                } catch (Exception e) {
0601:                    fail("Unexpected exception thrown in method " + e);
0602:                }
0603:            }
0604:
0605:            /**
0606:             *
0607:             * Tests the PreparedStatement interface method setCharacterStream
0608:             *
0609:             * @throws SQLException
0610:             *
0611:             */
0612:            public void testSetCharacterStream() throws Exception {
0613:                String str = "Test data for the Clob object";
0614:                StringReader is = new StringReader(
0615:                        "Test data for the Clob object");
0616:
0617:                is.reset();
0618:
0619:                //initially insert the data
0620:                psInsertClob.setInt(1, key);
0621:                psInsertClob.setCharacterStream(2, is, str.length());
0622:                psInsertClob.executeUpdate();
0623:
0624:                //Now query to retrieve the Clob
0625:                psFetchClob.setInt(1, key);
0626:                ResultSet rs = psFetchClob.executeQuery();
0627:                rs.next();
0628:                Clob clobRetrieved = rs.getClob(1);
0629:                rs.close();
0630:
0631:                String str_out = clobRetrieved.getSubString(1L,
0632:                        (int) clobRetrieved.length());
0633:
0634:                assertEquals("Error in inserting data into the Clob object",
0635:                        str, str_out);
0636:                psInsertClob.close();
0637:            }
0638:
0639:            public void testSetCharacterStreamLengthless() throws IOException,
0640:                    SQLException {
0641:                // Insert test data.
0642:                String testString = "Test string for setCharacterStream\u1A00";
0643:                Reader reader = new StringReader(testString);
0644:                psInsertClob.setInt(1, key);
0645:                psInsertClob.setCharacterStream(2, reader);
0646:                psInsertClob.execute();
0647:                reader.close();
0648:
0649:                // Read back test data from database.
0650:                psFetchClob.setInt(1, key);
0651:                ResultSet rs = psFetchClob.executeQuery();
0652:                assertTrue("No results retrieved", rs.next());
0653:                Clob clobRetrieved = rs.getClob(1);
0654:
0655:                // Verify test data.
0656:                assertEquals("Mismatch test data in/out", testString,
0657:                        clobRetrieved.getSubString(1, testString.length()));
0658:            }
0659:
0660:            /**
0661:             *
0662:             * Tests the PreparedStatement interface method setAsciiStream
0663:             *
0664:             * @throws SQLException
0665:             *
0666:             */
0667:
0668:            public void testSetAsciiStream() throws Exception {
0669:                //insert default values into the table
0670:
0671:                byte[] bytes1 = new byte[10];
0672:
0673:                InputStream is = new java.io.ByteArrayInputStream(BYTES);
0674:
0675:                is.reset();
0676:
0677:                //initially insert the data
0678:                psInsertClob.setInt(1, key);
0679:                psInsertClob.setAsciiStream(2, is, BYTES.length);
0680:                psInsertClob.executeUpdate();
0681:
0682:                //Now query to retrieve the Clob
0683:                psFetchClob.setInt(1, key);
0684:                ResultSet rs = psFetchClob.executeQuery();
0685:                rs.next();
0686:                Clob ClobRetrieved = rs.getClob(1);
0687:                rs.close();
0688:
0689:                try {
0690:                    InputStream is_ret = ClobRetrieved.getAsciiStream();
0691:                    is_ret.read(bytes1);
0692:                } catch (IOException ioe) {
0693:                    fail("IOException while reading the Clob from the database");
0694:                }
0695:                for (int i = 0; i < BYTES.length; i++) {
0696:                    assertEquals("Error in inserting data into the Clob",
0697:                            BYTES[i], bytes1[i]);
0698:                }
0699:                psInsertClob.close();
0700:            }
0701:
0702:            public void testSetAsciiStreamLengthless() throws IOException,
0703:                    SQLException {
0704:                // Insert test data.
0705:                InputStream is = new ByteArrayInputStream(BYTES);
0706:                psInsertClob.setInt(1, key);
0707:                psInsertClob.setAsciiStream(2, is);
0708:                psInsertClob.execute();
0709:                is.close();
0710:
0711:                // Read back test data from database.
0712:                psFetchClob.setInt(1, key);
0713:                ResultSet rs = psFetchClob.executeQuery();
0714:                assertTrue("No results retrieved", rs.next());
0715:                Clob clobRetrieved = rs.getClob(1);
0716:
0717:                // Verify read back data.
0718:                byte[] dbBytes = new byte[10];
0719:                InputStream isRetrieved = clobRetrieved.getAsciiStream();
0720:                assertEquals("Unexpected number of bytes read", BYTES.length,
0721:                        isRetrieved.read(dbBytes));
0722:                assertEquals("Stream should be exhausted", -1, isRetrieved
0723:                        .read());
0724:                for (int i = 0; i < BYTES.length; i++) {
0725:                    assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]);
0726:                }
0727:
0728:                // Cleanup
0729:                isRetrieved.close();
0730:                psInsertClob.close();
0731:            }
0732:
0733:            /**
0734:             *
0735:             * Tests the PreparedStatement interface method setBinaryStream
0736:             *
0737:             * @throws SQLException
0738:             *
0739:             */
0740:
0741:            public void testSetBinaryStream() throws Exception {
0742:                //insert default values into the table
0743:
0744:                byte[] bytes1 = new byte[10];
0745:
0746:                InputStream is = new java.io.ByteArrayInputStream(BYTES);
0747:
0748:                is.reset();
0749:
0750:                //initially insert the data
0751:                psInsertBlob.setInt(1, key);
0752:                psInsertBlob.setBinaryStream(2, is, BYTES.length);
0753:                psInsertBlob.executeUpdate();
0754:
0755:                //Now query to retrieve the Clob
0756:                psFetchBlob.setInt(1, key);
0757:                ResultSet rs = psFetchBlob.executeQuery();
0758:                rs.next();
0759:                Blob blobRetrieved = rs.getBlob(1);
0760:                rs.close();
0761:
0762:                try {
0763:                    InputStream is_ret = blobRetrieved.getBinaryStream();
0764:                    is_ret.read(bytes1);
0765:                } catch (IOException ioe) {
0766:                    fail("IOException while reading the Clob from the database");
0767:                }
0768:
0769:                for (int i = 0; i < BYTES.length; i++) {
0770:                    assertEquals("Error in inserting data into the Blob",
0771:                            BYTES[i], bytes1[i]);
0772:                }
0773:                psInsertBlob.close();
0774:            }
0775:
0776:            public void testSetBinaryStreamLengthless() throws IOException,
0777:                    SQLException {
0778:                // Insert test data.
0779:                InputStream is = new ByteArrayInputStream(BYTES);
0780:                psInsertBlob.setInt(1, key);
0781:                psInsertBlob.setBinaryStream(2, is);
0782:                psInsertBlob.execute();
0783:                is.close();
0784:
0785:                // Read back test data from database.
0786:                psFetchBlob.setInt(1, key);
0787:                ResultSet rs = psFetchBlob.executeQuery();
0788:                assertTrue("No results retrieved", rs.next());
0789:                Blob blobRetrieved = rs.getBlob(1);
0790:
0791:                // Verify read back data.
0792:                byte[] dbBytes = new byte[10];
0793:                InputStream isRetrieved = blobRetrieved.getBinaryStream();
0794:                assertEquals("Unexpected number of bytes read", BYTES.length,
0795:                        isRetrieved.read(dbBytes));
0796:                assertEquals("Stream should be exhausted", -1, isRetrieved
0797:                        .read());
0798:                for (int i = 0; i < BYTES.length; i++) {
0799:                    assertEquals("Byte mismatch in/out", BYTES[i], dbBytes[i]);
0800:                }
0801:
0802:                // Cleanup
0803:                isRetrieved.close();
0804:                psInsertBlob.close();
0805:            }
0806:
0807:            public void testSetBinaryStreamLengthLess1KOnBlob()
0808:                    throws IOException, SQLException {
0809:                int length = 1 * 1024;
0810:                setBinaryStreamOnBlob(key, length, -1, 0, true);
0811:                psFetchBlob.setInt(1, key);
0812:                ResultSet rs = psFetchBlob.executeQuery();
0813:                assertTrue("Empty resultset", rs.next());
0814:                assertEquals(new LoopingAlphabetStream(length), rs
0815:                        .getBinaryStream(1));
0816:                assertFalse("Resultset should have been exhausted", rs.next());
0817:                rs.close();
0818:            }
0819:
0820:            public void testSetBinaryStreamLengthLess32KOnBlob()
0821:                    throws IOException, SQLException {
0822:                int length = 32 * 1024;
0823:                setBinaryStreamOnBlob(key, length, -1, 0, true);
0824:                psFetchBlob.setInt(1, key);
0825:                ResultSet rs = psFetchBlob.executeQuery();
0826:                assertTrue("Empty resultset", rs.next());
0827:                assertEquals(new LoopingAlphabetStream(length), rs
0828:                        .getBinaryStream(1));
0829:                assertFalse("Resultset should have been exhausted", rs.next());
0830:                rs.close();
0831:            }
0832:
0833:            public void testSetBinaryStreamLengthLess65KOnBlob()
0834:                    throws IOException, SQLException {
0835:                int length = 65 * 1024;
0836:                setBinaryStreamOnBlob(key, length, -1, 0, true);
0837:                psFetchBlob.setInt(1, key);
0838:                ResultSet rs = psFetchBlob.executeQuery();
0839:                assertTrue("Empty resultset", rs.next());
0840:                LoopingAlphabetStream s1 = new LoopingAlphabetStream(length);
0841:                assertEquals(new LoopingAlphabetStream(length), rs
0842:                        .getBinaryStream(1));
0843:                assertFalse("Resultset should have been exhausted", rs.next());
0844:                rs.close();
0845:            }
0846:
0847:            public void testSetBinaryStreamLengthLessOnBlobTooLong() {
0848:                int length = 1 * 1024 * 1024 + 512;
0849:                try {
0850:                    setBinaryStreamOnBlob(key, length, -1, 0, true);
0851:                } catch (SQLException sqle) {
0852:                    if (usingEmbedded()) {
0853:                        assertSQLState("XSDA4", sqle);
0854:                    } else {
0855:                        assertSQLState("22001", sqle);
0856:                    }
0857:                }
0858:            }
0859:
0860:            public void testExceptionPathOnePage_bs() throws SQLException {
0861:                int length = 11;
0862:                try {
0863:                    setBinaryStreamOnBlob(key, length - 1, length, 0, false);
0864:                    fail("Inserted a BLOB with fewer bytes than specified");
0865:                } catch (SQLException sqle) {
0866:                    if (usingEmbedded()) {
0867:                        assertSQLState("XSDA4", sqle);
0868:                    } else {
0869:                        assertSQLState("XN017", sqle);
0870:                    }
0871:                }
0872:            }
0873:
0874:            public void testExceptionPathMultiplePages_bs() throws SQLException {
0875:                int length = 1 * 1024 * 1024;
0876:                try {
0877:                    setBinaryStreamOnBlob(key, length - 1, length, 0, false);
0878:                    fail("Inserted a BLOB with fewer bytes than specified");
0879:                } catch (SQLException sqle) {
0880:                    if (usingEmbedded()) {
0881:                        assertSQLState("XSDA4", sqle);
0882:                    } else {
0883:                        assertSQLState("XN017", sqle);
0884:                    }
0885:                }
0886:            }
0887:
0888:            public void testBlobExceptionDoesNotRollbackOtherStatements()
0889:                    throws IOException, SQLException {
0890:                getConnection().setAutoCommit(false);
0891:                int[] keys = { key, requestKey(), requestKey() };
0892:                for (int i = 0; i < keys.length; i++) {
0893:                    psInsertBlob.setInt(1, keys[i]);
0894:                    psInsertBlob.setNull(2, Types.BLOB);
0895:                    assertEquals(1, psInsertBlob.executeUpdate());
0896:                }
0897:                // Now insert a BLOB that fails because the stream is too short.
0898:                int failedKey = requestKey();
0899:                int length = 1 * 1024 * 1024;
0900:                try {
0901:                    setBinaryStreamOnBlob(failedKey, length - 1, length, 0,
0902:                            false);
0903:                    fail("Inserted a BLOB with less data than specified");
0904:                } catch (SQLException sqle) {
0905:                    if (usingEmbedded()) {
0906:                        assertSQLState("XSDA4", sqle);
0907:                    } else {
0908:                        assertSQLState("XN017", sqle);
0909:                    }
0910:                }
0911:                // Now make sure the previous statements are there, and that the last
0912:                // BLOB is not.
0913:                ResultSet rs;
0914:                for (int i = 0; i < keys.length; i++) {
0915:                    psFetchBlob.setInt(1, keys[i]);
0916:                    rs = psFetchBlob.executeQuery();
0917:                    assertTrue(rs.next());
0918:                    assertFalse(rs.next());
0919:                    rs.close();
0920:                }
0921:                psFetchBlob.setInt(1, failedKey);
0922:                rs = psFetchBlob.executeQuery();
0923:                // When using the Derby client driver, the data seems to be padded
0924:                // with 0s and inserted... Thus, the select returns a row.
0925:                if (!usingEmbedded()) {
0926:                    assertTrue(rs.next());
0927:                    InputStream is = rs.getBinaryStream(1);
0928:                    int lastByte = -1;
0929:                    int b = 99; // Just a value > 0.
0930:                    while (b > -1) {
0931:                        lastByte = b;
0932:                        b = is.read();
0933:                    }
0934:                    assertEquals("Last padded byte is not 0", 0, lastByte);
0935:                }
0936:                assertFalse(rs.next());
0937:                rs.close();
0938:                rollback();
0939:                // Make sure all data is gone after the rollback.
0940:                for (int i = 0; i < keys.length; i++) {
0941:                    psFetchBlob.setInt(1, keys[i]);
0942:                    rs = psFetchBlob.executeQuery();
0943:                    assertFalse(rs.next());
0944:                    rs.close();
0945:                }
0946:                // Make sure the failed insert has not "reappeared" somehow...
0947:                psFetchBlob.setInt(1, failedKey);
0948:                rs = psFetchBlob.executeQuery();
0949:                assertFalse(rs.next());
0950:
0951:            }
0952:
0953:            public void testSetAsciiStreamLengthLess1KOnClob()
0954:                    throws IOException, SQLException {
0955:                int length = 1 * 1024;
0956:                setAsciiStream(psInsertClob, key, length, -1, 0, true);
0957:                psFetchClob.setInt(1, key);
0958:                ResultSet rs = psFetchClob.executeQuery();
0959:                assertTrue("Empty resultset", rs.next());
0960:                assertEquals(new LoopingAlphabetStream(length), rs
0961:                        .getAsciiStream(1));
0962:                assertFalse("Resultset should have been exhausted", rs.next());
0963:                rs.close();
0964:            }
0965:
0966:            public void testSetAsciiStreamLengthLess32KOnClob()
0967:                    throws IOException, SQLException {
0968:                int length = 32 * 1024;
0969:                setAsciiStream(psInsertClob, key, length, -1, 0, true);
0970:                psFetchClob.setInt(1, key);
0971:                ResultSet rs = psFetchClob.executeQuery();
0972:                assertTrue("Empty resultset", rs.next());
0973:                assertEquals(new LoopingAlphabetStream(length), rs
0974:                        .getAsciiStream(1));
0975:                assertFalse("Resultset should have been exhausted", rs.next());
0976:                rs.close();
0977:            }
0978:
0979:            public void testSetAsciiStreamLengthLess65KOnClob()
0980:                    throws IOException, SQLException {
0981:                int length = 65 * 1024;
0982:                setAsciiStream(psInsertClob, key, length, -1, 0, true);
0983:                psFetchClob.setInt(1, key);
0984:                ResultSet rs = psFetchClob.executeQuery();
0985:                assertTrue("Empty resultset", rs.next());
0986:                assertEquals(new LoopingAlphabetStream(length), rs
0987:                        .getAsciiStream(1));
0988:                assertFalse("Resultset should have been exhausted", rs.next());
0989:                rs.close();
0990:            }
0991:
0992:            public void testSetAsciiStreamLengthLessOnClobTooLong() {
0993:                int length = 1 * 1024 * 1024 + 512;
0994:                try {
0995:                    setAsciiStream(psInsertClob, key, length, -1, 0, true);
0996:                } catch (SQLException sqle) {
0997:                    if (usingEmbedded()) {
0998:                        assertSQLState("XSDA4", sqle);
0999:                    } else {
1000:                        assertSQLState("22001", sqle);
1001:                    }
1002:                }
1003:            }
1004:
1005:            public void testSetAsciiStreamLengthLessOnClobTooLongTruncate()
1006:                    throws SQLException {
1007:                int trailingBlanks = 512;
1008:                int length = 1 * 1024 * 1024 + trailingBlanks;
1009:                setAsciiStream(psInsertClob, key, length, -1, trailingBlanks,
1010:                        true);
1011:            }
1012:
1013:            public void testSetAsciiStreamLengthlessOnLongVarCharTooLong() {
1014:                int length = 32700 + 512;
1015:                try {
1016:                    setAsciiStream(psInsertLongVarchar, key, length, -1, 0,
1017:                            true);
1018:                    fail("Inserted a LONG VARCHAR that is too long");
1019:                } catch (SQLException sqle) {
1020:                    if (usingEmbedded()) {
1021:                        assertInternalDerbyIOExceptionState("XCL30", "22001",
1022:                                sqle);
1023:                    } else {
1024:                        assertSQLState("22001", sqle);
1025:                    }
1026:                }
1027:            }
1028:
1029:            public void testSetAsciiStreamLengthlessOnLongVarCharDontTruncate() {
1030:                int trailingBlanks = 2000;
1031:                int length = 32000 + trailingBlanks;
1032:                try {
1033:                    setAsciiStream(psInsertLongVarchar, key, length, -1,
1034:                            trailingBlanks, true);
1035:                    fail("Truncation is not allowed for LONG VARCHAR");
1036:                } catch (SQLException sqle) {
1037:                    if (usingEmbedded()) {
1038:                        assertInternalDerbyIOExceptionState("XCL30", "22001",
1039:                                sqle);
1040:                    } else {
1041:                        assertSQLState("22001", sqle);
1042:                    }
1043:                }
1044:            }
1045:
1046:            /************************************************************************
1047:             *                 A U X I L I A R Y  M E T H O D S                     *
1048:             ************************************************************************/
1049:
1050:            /**
1051:             * Insert data into a Blob column with setBinaryStream.
1052:             *
1053:             * @param id unique id for inserted row
1054:             * @param actualLength the actual length of the stream
1055:             * @param specifiedLength the specified length of the stream
1056:             * @param trailingBlanks number of characters at the end that is blank
1057:             * @param lengthLess whether to use the length less overloads or not
1058:             */
1059:            private void setBinaryStreamOnBlob(int id, int actualLength,
1060:                    int specifiedLength, int trailingBlanks, boolean lengthLess)
1061:                    throws SQLException {
1062:                psInsertBlob.setInt(1, id);
1063:                if (lengthLess) {
1064:                    psInsertBlob.setBinaryStream(2, new LoopingAlphabetStream(
1065:                            actualLength, trailingBlanks));
1066:                } else {
1067:                    psInsertBlob.setBinaryStream(2, new LoopingAlphabetStream(
1068:                            actualLength, trailingBlanks), specifiedLength);
1069:                }
1070:                assertEquals("Insert with setBinaryStream failed", 1,
1071:                        psInsertBlob.executeUpdate());
1072:            }
1073:
1074:            /**
1075:             * Insert data into a column with setAsciiStream.
1076:             * The prepared statement passed must have two positional parameters;
1077:             * one int and one more. Depending on the last parameter, the execute
1078:             * might succeed or it might fail. This is intended behavior, and should
1079:             * be handled by the caller. For instance, calling this method on an
1080:             * INT-column would fail, calling it on a CLOB-column would succeed.
1081:             *
1082:             * @param id unique id for inserted row
1083:             * @param actualLength the actual length of the stream
1084:             * @param specifiedLength the specified length of the stream
1085:             * @param trailingBlanks number of characters at the end that is blank
1086:             * @param lengthLess whether to use the length less overloads or not
1087:             */
1088:            private void setAsciiStream(PreparedStatement ps, int id,
1089:                    int actualLength, int specifiedLength, int trailingBlanks,
1090:                    boolean lengthLess) throws SQLException {
1091:                ps.setInt(1, id);
1092:                if (lengthLess) {
1093:                    ps.setAsciiStream(2, new LoopingAlphabetStream(
1094:                            actualLength, trailingBlanks));
1095:                } else {
1096:                    ps.setAsciiStream(2, new LoopingAlphabetStream(
1097:                            actualLength, trailingBlanks), specifiedLength);
1098:                }
1099:                assertEquals("Insert with setAsciiStream failed", 1, ps
1100:                        .executeUpdate());
1101:            }
1102:
1103:            /**
1104:             * Get next key to id inserted data with.
1105:             */
1106:            private static int requestKey() {
1107:                return globalKey++;
1108:            }
1109:
1110:            /**
1111:             * Return the last chained SQLException.
1112:             * If there are no exceptions chained, the original one is returned.
1113:             */
1114:            private SQLException getLastSQLException(SQLException sqle) {
1115:                SQLException last = sqle;
1116:                SQLException next = sqle;
1117:                while (next != null) {
1118:                    last = next;
1119:                    next = last.getNextException();
1120:                }
1121:                return last;
1122:            }
1123:
1124:            /**
1125:             * This methods is not to be used, but sometimes you have to!
1126:             *
1127:             * @param preSQLState the expected outer SQL state
1128:             * @param expectedInternal the expected internal SQL state
1129:             * @param sqle the outer SQLException
1130:             */
1131:            private void assertInternalDerbyIOExceptionState(
1132:                    String preSQLState, String expectedInternal,
1133:                    SQLException sqle) {
1134:                assertSQLState("Outer/public SQL state incorrect", preSQLState,
1135:                        sqle);
1136:                // We need to dig a little with the current way exceptions are
1137:                // being reported. We can use getCause because we always run with
1138:                // Mustang/Java SE 6.
1139:                Throwable cause = getLastSQLException(sqle).getCause();
1140:                assertTrue("Exception not an EmbedSQLException",
1141:                        cause instanceof  EmbedSQLException);
1142:                cause = ((EmbedSQLException) cause).getJavaException();
1143:                assertTrue("Exception not a DerbyIOException",
1144:                        cause instanceof  DerbyIOException);
1145:                DerbyIOException dioe = (DerbyIOException) cause;
1146:                assertEquals("Incorrect internal SQL state", expectedInternal,
1147:                        dioe.getSQLState());
1148:            }
1149:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.