Source Code Cross Referenced for procedure.java in  » Database-DBMS » db-derby-10.2 » org » apache » derbyTesting » functionTests » tests » lang » 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.lang 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


0001:        /*
0002:
0003:           Derby - Class org.apache.derbyTesting.functionTests.harness.procedure
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.lang;
0023:
0024:        import org.apache.derbyTesting.functionTests.util.TestUtil;
0025:        import java.sql.*;
0026:
0027:        import org.apache.derby.tools.ij;
0028:        import org.apache.derby.iapi.reference.JDBC30Translation;
0029:        import org.apache.derby.iapi.reference.SQLState;
0030:
0031:        import java.io.PrintStream;
0032:        import java.math.BigInteger;
0033:        import java.math.BigDecimal;
0034:
0035:        import java.lang.reflect.*;
0036:
0037:        import org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30;
0038:
0039:        public class procedure {
0040:
0041:            private static Class[] CONN_PARAM = { Integer.TYPE };
0042:            private static Object[] CONN_ARG = { new Integer(
0043:                    JDBC30Translation.CLOSE_CURSORS_AT_COMMIT) };
0044:
0045:            static private boolean isDerbyNet = false;
0046:
0047:            public static void main(String[] argv) throws Throwable {
0048:                ij.getPropertyArg(argv);
0049:                Connection conn = ij.startJBMS();
0050:                cleanUp(conn);
0051:                isDerbyNet = TestUtil.isNetFramework();
0052:
0053:                // DB2 !!
0054:                // com.ibm.db2.jcc.DB2DataSource ds = new com.ibm.db2.jcc.DB2DataSource();
0055:
0056:                // ds.setDatabaseName("testdb");
0057:
0058:                // ds.setServerName("localhost");
0059:                //ds.setPortNumber(1527);
0060:                // ds.setDriverType(4);
0061:
0062:                // Connection conn = ds.getConnection("db2admin", "password");
0063:
0064:                //Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
0065:                //Connection conn = DriverManager.getConnection("jdbc:db2:testdb", "USER", "XXXXX");
0066:
0067:                runTests(conn);
0068:            }
0069:
0070:            public static void runTests(Connection conn) throws Throwable {
0071:                try {
0072:                    testNegative(conn);
0073:                    testDelayedClassChecking(conn);
0074:                    testDuplicates(conn);
0075:                    ambigiousMethods(conn);
0076:                    zeroArgProcedures(conn);
0077:                    sqlProcedures(conn);
0078:                    dynamicResultSets(conn, ij.startJBMS());
0079:
0080:                    testParameterTypes(conn);
0081:                    testOutparams(conn);
0082:
0083:                    testSQLControl(conn);
0084:                    testLiterals(conn);
0085:
0086:                    multipleRSTests(conn);
0087:                    jira_491_492(conn);
0088:                    testImplicitClose(conn);
0089:                    cleanUp(conn);
0090:                } catch (SQLException sqle) {
0091:                    org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
0092:                            System.out, sqle);
0093:                    sqle.printStackTrace(System.out);
0094:                }
0095:
0096:            }
0097:
0098:            public static void testNegative(Connection conn)
0099:                    throws SQLException {
0100:
0101:                System.out.println("testNegative");
0102:
0103:                Statement s = conn.createStatement();
0104:
0105:                // no '.' in path/method
0106:                statementExceptionExpected(
0107:                        s,
0108:                        "create procedure asdf() language java external name 'asdfasdf' parameter style java");
0109:
0110:                // trailing '.'
0111:                statementExceptionExpected(
0112:                        s,
0113:                        "create procedure asdf() language java external name 'asdfasdf.' parameter style java");
0114:
0115:                // procedure name too long
0116:                statementExceptionExpected(
0117:                        s,
0118:                        "create procedure a23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789() language java external name 'asdf.asdf' parameter style java");
0119:
0120:                // -- missing parens on procedure name
0121:                statementExceptionExpected(
0122:                        s,
0123:                        "create procedure asdf language java external name java.lang.Thread.currentThread parameter style java");
0124:
0125:                // -- incorrect language, (almost) straight from DB2 docs 
0126:
0127:                statementExceptionExpected(
0128:                        s,
0129:                        "CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER, OUT NUM_PARTS INTEGER, OUT COST DOUBLE) EXTERNAL NAME 'parts!assembly' DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL");
0130:
0131:                // invalid schema 
0132:                statementExceptionExpected(
0133:                        s,
0134:                        "create procedure sys.proc1() language java external name 'java.lang.System.gc' parameter style java");
0135:
0136:                // repeated elements
0137:                statementExceptionExpected(
0138:                        s,
0139:                        "create procedure noclass() language java external name 'asdf.asdf' parameter style java language java");
0140:                statementExceptionExpected(
0141:                        s,
0142:                        "create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style java");
0143:                statementExceptionExpected(
0144:                        s,
0145:                        "create procedure noclass() external name 'asdf.xxxx' language java external name 'asdf.asdf' parameter style java");
0146:                statementExceptionExpected(
0147:                        s,
0148:                        "create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style derby_rs_collection");
0149:
0150:                // missing elements
0151:                statementExceptionExpected(s, "create procedure missing01()");
0152:                statementExceptionExpected(s,
0153:                        "create procedure missing02() language java");
0154:                statementExceptionExpected(s,
0155:                        "create procedure missing03() language java parameter style java");
0156:                statementExceptionExpected(s,
0157:                        "create procedure missing04() language java external name 'foo.bar'");
0158:                statementExceptionExpected(s,
0159:                        "create procedure missing05() parameter style java");
0160:                statementExceptionExpected(s,
0161:                        "create procedure missing06() parameter style java external name 'foo.bar'");
0162:                statementExceptionExpected(s,
0163:                        "create procedure missing07() external name 'goo.bar'");
0164:                statementExceptionExpected(s,
0165:                        "create procedure missing08() dynamic result sets 1");
0166:                //statementExceptionExpected(s, "create procedure missing09() specific name fred");
0167:
0168:                // RETURNS NULL ON NULL INPUT not allowed in procedures.
0169:                statementExceptionExpected(
0170:                        s,
0171:                        "create procedure nullinput2() returns null on null input language java parameter style java external name 'foo.bar'");
0172:
0173:                // no BLOB/CLOB/ long parameters
0174:                statementExceptionExpected(
0175:                        s,
0176:                        "create procedure NO_BLOB(IN P1 BLOB(3k)) language java parameter style java external name 'no.blob'");
0177:                statementExceptionExpected(
0178:                        s,
0179:                        "create procedure NO_CLOB(IN P1 CLOB(3k)) language java parameter style java external name 'no.clob'");
0180:                statementExceptionExpected(
0181:                        s,
0182:                        "create procedure NO_LVC(IN P1 LONG VARCHAR) language java parameter style java external name 'no.lvc'");
0183:
0184:                // duplicate names
0185:                statementExceptionExpected(
0186:                        s,
0187:                        "create procedure DUP_P1(IN FRED INT, OUT RON CHAR(10), IN FRED INT) language java parameter style java external name 'no.dup1'");
0188:                statementExceptionExpected(
0189:                        s,
0190:                        "create procedure D2.DUP_P2(IN \"FreD\" INT, OUT RON CHAR(10), IN \"FreD\" INT) language java parameter style java external name 'no.dup2'");
0191:                statementExceptionExpected(
0192:                        s,
0193:                        "create procedure D3.DUP_P3(IN \"FRED\" INT, OUT RON CHAR(10), IN fred INT) language java parameter style java external name 'no.dup3'");
0194:                s
0195:                        .execute("create procedure DUP_POK(IN \"FreD\" INT, OUT RON CHAR(10), IN fred INT) language java parameter style java external name 'no.dupok'");
0196:                s.execute("drop procedure DUP_POK");
0197:
0198:                // procedure not found with explicit schema name
0199:                statementExceptionExpected(s, "CALL APP.NSP(?, ?)");
0200:
0201:                // bug 5760 - this caused a null pointer exception at one time.
0202:                statementExceptionExpected(s,
0203:                        "call syscs_util.syscs_set_database_property(\"foo\", \"bar\")");
0204:
0205:                // Derby-258 specific signatures with types not matching JDBC spec.
0206:                System.out.println("signature mismatched types");
0207:                s
0208:                        .execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_A(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.load(java.lang.String)'");
0209:                statementExceptionExpected(s,
0210:                        "CALL APP.SIGNATURE_BUG_DERBY_258_A(4)");
0211:                s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_A");
0212:
0213:                // signature with wrong number of arguments, too many
0214:                System.out.println("signature too many parameters");
0215:                s
0216:                        .execute("CREATE FUNCTION SIGNATURE_BUG_DERBY_258_B(A INT) RETURNS VARCHAR(128) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.Integer.toString(int, int)'");
0217:                statementExceptionExpected(s,
0218:                        "VALUES APP.SIGNATURE_BUG_DERBY_258_B(4)");
0219:                s.execute("DROP FUNCTION SIGNATURE_BUG_DERBY_258_B");
0220:
0221:                // and too few
0222:                System.out.println("signature too few parameters");
0223:                s
0224:                        .execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_C(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc()'");
0225:                statementExceptionExpected(s,
0226:                        "CALL APP.SIGNATURE_BUG_DERBY_258_C(4)");
0227:                s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_C");
0228:
0229:                // only a leading paren
0230:                System.out.println("signature invalid format");
0231:                s
0232:                        .execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_F(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc('");
0233:                statementExceptionExpected(s,
0234:                        "CALL APP.SIGNATURE_BUG_DERBY_258_F(4)");
0235:                s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_F");
0236:
0237:                // signature of (,,)
0238:                System.out.println("signature invalid format");
0239:                s
0240:                        .execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_G(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(,,)'");
0241:                statementExceptionExpected(s,
0242:                        "CALL APP.SIGNATURE_BUG_DERBY_258_G(4)");
0243:                s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_G");
0244:
0245:                // signature of (, ,)
0246:                System.out.println("signature invalid format");
0247:                s
0248:                        .execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_H(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(, ,)'");
0249:                statementExceptionExpected(s,
0250:                        "CALL APP.SIGNATURE_BUG_DERBY_258_H(4)");
0251:                s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_H");
0252:
0253:                // signature of (int,)
0254:                System.out.println("signature invalid format");
0255:                s
0256:                        .execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_I(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(int ,)'");
0257:                statementExceptionExpected(s,
0258:                        "CALL APP.SIGNATURE_BUG_DERBY_258_I(4)");
0259:                s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_I");
0260:
0261:                s.close();
0262:
0263:            }
0264:
0265:            public static void testBug5280(Connection conn) throws SQLException {
0266:                String csString = "CALL SQLCONTROL3_0 (?, ?, ?, ?, ?, ?, ?)";
0267:                // Bug 5280 If we don't register the outparams
0268:                // we don't get an error with network server.
0269:                //for (int p = 1; p <= 7; p++) {
0270:                //	cs.registerOutParameter(p,Types.VARCHAR);
0271:                //}
0272:                callExceptionExpected(conn, csString);
0273:            }
0274:
0275:            public static void testDelayedClassChecking(Connection conn)
0276:                    throws SQLException {
0277:
0278:                System.out.println("testDelayedClassChecking");
0279:
0280:                Statement s = conn.createStatement();
0281:                // -- procedures do not check if the class or method exists at create time.
0282:                s
0283:                        .execute("create procedure noclass() language java external name 'asdf.asdf' parameter style java");
0284:                s
0285:                        .execute("create procedure nomethod() language java external name 'java.lang.Integer.asdf' parameter style java");
0286:                s
0287:                        .execute("create procedure notstatic() language java external name 'java.lang.Integer.equals' parameter style java");
0288:                s
0289:                        .execute("create procedure notvoid() language java external name 'java.lang.Runtime.getRuntime' parameter style java");
0290:
0291:                //  - but they are checked at runtime
0292:                callExceptionExpected(conn, "call noclass()");
0293:                callExceptionExpected(conn, "call nomethod()");
0294:                callExceptionExpected(conn, "call notstatic()");
0295:                callExceptionExpected(conn, "call notvoid()");
0296:
0297:                // CHECK SYSALIAS
0298:                s.execute("drop procedure noclass");
0299:                s.execute("drop procedure nomethod");
0300:                s.execute("drop procedure notstatic");
0301:                s.execute("drop procedure notvoid");
0302:
0303:                s.close();
0304:
0305:            }
0306:
0307:            public static void testDuplicates(Connection conn)
0308:                    throws SQLException {
0309:                System.out.println("testDuplicates");
0310:
0311:                Statement s = conn.createStatement();
0312:
0313:                s.execute("create schema S1");
0314:                s.execute("create schema S2");
0315:
0316:                s
0317:                        .execute("create procedure PROCDUP() language java external name 'okAPP.ok0' parameter style java");
0318:                s
0319:                        .execute("create procedure s1.PROCDUP() language java external name 'oks1.ok0' parameter style java");
0320:                s
0321:                        .execute("create procedure s2.PROCDUP() language java external name 'oks2.ok0' parameter style java");
0322:
0323:                statementExceptionExpected(
0324:                        s,
0325:                        "create procedure PROCDUP() language java external name 'failAPP.fail0' parameter style java");
0326:                statementExceptionExpected(
0327:                        s,
0328:                        "create procedure s1.PROCDUP() language java external name 'fails1.fail0' parameter style java");
0329:                statementExceptionExpected(
0330:                        s,
0331:                        "create procedure s2.PROCDUP() language java external name 'fails2.fail0' parameter style java");
0332:
0333:                showMatchingProcedures(conn, "PROCDUP");
0334:
0335:                statementExceptionExpected(
0336:                        s,
0337:                        "create procedure S1.NOTYET() SPECIFIC fred language java external name 'failAPP.fail0' parameter style java");
0338:
0339:                s.execute("drop procedure s1.PROCDUP");
0340:                s.execute("drop procedure s2.PROCDUP");
0341:
0342:                s.execute("drop schema S1 RESTRICT");
0343:                s.execute("drop schema S2 RESTRICT");
0344:                s.close();
0345:
0346:            }
0347:
0348:            public static void ambigiousMethods(Connection conn)
0349:                    throws SQLException {
0350:                System.out.println("ambigiousMethods");
0351:
0352:                Statement s = conn.createStatement();
0353:
0354:                // ambigious resolution - with result sets
0355:                s
0356:                        .execute("create procedure ambigious01(p1 INTEGER, p2 CHAR(20)) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious1'");
0357:                callExceptionExpected(conn, "call AMBIGIOUS01(?, ?)");
0358:                s.execute("drop procedure AMBIGIOUS01");
0359:
0360:                // ambigious in defined parameters
0361:                s
0362:                        .execute("create procedure ambigious02(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2'");
0363:                callExceptionExpected(conn, "call AMBIGIOUS02(?, ?)");
0364:                s.execute("drop procedure AMBIGIOUS02");
0365:
0366:                // verify we can find it with a Java signature
0367:                s
0368:                        .execute("create procedure ambigious03(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2(int,java.lang.Integer)'");
0369:                executeProcedure(s, "{call ambigious03(1, NULL)}");
0370:                s.execute("drop procedure AMBIGIOUS03");
0371:                s
0372:                        .execute("create procedure ambigious04(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2(java.lang.Integer,int)'");
0373:                executeProcedure(s, "{call ambigious04(NULL, 1)}");
0374:                s.execute("drop procedure AMBIGIOUS04");
0375:                s.close();
0376:            }
0377:
0378:            public static void zeroArgProcedures(Connection conn)
0379:                    throws SQLException {
0380:                System.out.println("zeroArgProcedures");
0381:
0382:                Statement s = conn.createStatement();
0383:                s
0384:                        .execute("create procedure za() language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArg' parameter style java");
0385:
0386:                executeProcedure(s, "call za()");
0387:                PreparedStatement ps = conn.prepareStatement("call za()");
0388:                executeProcedure(ps);
0389:                ps.close();
0390:
0391:                ps = conn.prepareStatement("{call za()}");
0392:                executeProcedure(ps);
0393:                ps.close();
0394:
0395:                try {
0396:                    ps = conn.prepareStatement("call za(?)");
0397:                    System.out.println("FAIL - prepareStatement call za(?)");
0398:                } catch (SQLException sqle) {
0399:                    System.out.println("EXPECTED SQL Exception: "
0400:                            + sqle.getMessage());
0401:                }
0402:
0403:                CallableStatement cs = conn.prepareCall("call za()");
0404:                executeProcedure(cs);
0405:                cs.close();
0406:
0407:                cs = conn.prepareCall("{call za()}");
0408:                executeProcedure(cs);
0409:                cs.close();
0410:
0411:                showMatchingProcedures(conn, "ZA");
0412:                s.execute("drop procedure za");
0413:                showMatchingProcedures(conn, "ZA");
0414:
0415:                s.close();
0416:
0417:            }
0418:
0419:            private static void sqlProcedures(Connection conn)
0420:                    throws SQLException {
0421:
0422:                System.out.println("sqlProcedures()");
0423:
0424:                Statement s = conn.createStatement();
0425:
0426:                s
0427:                        .execute("create table t1(i int not null primary key, b char(15))");
0428:                s
0429:                        .execute("create procedure ir(p1 int) MODIFIES SQL DATA dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow' parameter style java");
0430:                s
0431:                        .execute("create procedure ir2(p1 int, p2 char(10)) language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow' MODIFIES SQL DATA parameter style java");
0432:
0433:                showMatchingProcedures(conn, "IR%");
0434:
0435:                callExceptionExpected(conn, "CALL IR()");
0436:
0437:                CallableStatement ir1 = conn.prepareCall("CALL IR(?)");
0438:
0439:                ir1.setInt(1, 1);
0440:                executeProcedure(ir1);
0441:
0442:                ir1.setInt(1, 2);
0443:                executeProcedure(ir1);
0444:                try {
0445:                    ir1.execute();
0446:                    System.out
0447:                            .println("FAIL - duplicate key insertion through ir");
0448:                } catch (SQLException sqle) {
0449:                    System.out.println("EXPECTED SQL Exception: "
0450:                            + sqle.getMessage());
0451:                }
0452:
0453:                ir1.setString(1, "3");
0454:                executeProcedure(ir1);
0455:
0456:                ir1.close();
0457:
0458:                ir1 = conn.prepareCall("CALL APP.IR(?)");
0459:                ir1.setInt(1, 7);
0460:                executeProcedure(ir1);
0461:
0462:                CallableStatement ir2 = conn.prepareCall("CALL IR2(?, ?)");
0463:
0464:                ir2.setInt(1, 4);
0465:                ir2.setInt(2, 4);
0466:                executeProcedure(ir2);
0467:
0468:                ir2.setInt(1, 5);
0469:                ir2.setString(2, "ir2");
0470:                executeProcedure(ir2);
0471:
0472:                ir2.setInt(1, 6);
0473:                ir2.setString(2, "'012345678990'");
0474:                executeProcedure(ir2);
0475:
0476:                ir1.close();
0477:                ir2.close();
0478:
0479:                if (!conn.getAutoCommit())
0480:                    conn.commit();
0481:
0482:                ResultSet rs = s.executeQuery("select * from t1");
0483:                org.apache.derby.tools.JDBCDisplayUtil.DisplayResults(
0484:                        System.out, rs, conn);
0485:
0486:                if (!conn.getAutoCommit())
0487:                    conn.commit();
0488:
0489:                callExceptionExpected(conn, "CALL IR2(2, 'no way')");
0490:                callExceptionExpected(conn, "CALL IR2(?, 'no way')");
0491:                callExceptionExpected(conn, "CALL IR2(2, ?)");
0492:
0493:                s.execute("drop procedure IR");
0494:                s.execute("drop procedure IR2");
0495:
0496:                s.close();
0497:            }
0498:
0499:            // This test case provides tests for bugs DERBY-491 and DERBY-492. These
0500:            // two bug reports describe different symptoms, but the underlying bug
0501:            // is identical: the network server's implementation of LMTBLKPRC was
0502:            // incorrectly manipulating DDMWriter's bytes buffer. Depending on the
0503:            // details, the symptom of this bug was generally a hang, because the
0504:            // server mistakenly truncated the unsent data in its network buffer and
0505:            // hence sent only a partial transmission, causing the client to hang,
0506:            // waiting for data that would never arrive. A more detailed analysis
0507:            // of some other possible symptoms that could arise from these tests is
0508:            // available in the bug notes for bug 491 in JIRA at:
0509:            // http://issues.apache.org/jira/browse/DERBY-491
0510:            //
0511:            private static void jira_491_492(Connection conn)
0512:                    throws SQLException {
0513:                Statement st = conn.createStatement();
0514:                PreparedStatement pSt = null;
0515:
0516:                // JIRA-491: Result set has a row that is approx 32K long.
0517:                // When originally filed, this bug script caused  a protocol
0518:                // exception and connection deallocation, but that was because the
0519:                // bug script provoked both JIRA-614 *and* JIRA-491. If you have
0520:                // the fix for JIRA-614, but JIRA-491 has regressed, you will hang.
0521:
0522:                try {
0523:                    st.execute("drop table testtable1");
0524:                } catch (SQLException se) {
0525:                }
0526:
0527:                // Create an array of chars to be used as the input parameter.
0528:                // Note that the array should roughly 32K or larger.
0529:                char[] cData = new char[32500];
0530:                for (int i = 0; i < cData.length; i++)
0531:                    cData[i] = Character.forDigit(i % 10, 10);
0532:
0533:                try {
0534:                    st
0535:                            .execute("create table jira491 (int1 integer, varchar32k varchar(32500))");
0536:                    pSt = conn
0537:                            .prepareStatement("insert into jira491 values (?,?)");
0538:                    for (int i = 1; i <= 5; i++) {
0539:                        pSt.setInt(1, i);
0540:                        pSt.setString(2, new String(cData));
0541:                        pSt.execute();
0542:                    }
0543:                } catch (SQLException se) {
0544:                    System.out.println("JIRA-491: FAILURE in data generation:");
0545:                    se.printStackTrace(System.out);
0546:                }
0547:
0548:                try {
0549:                    st.execute("drop procedure TEST_PROC_JIRA_491");
0550:                } catch (SQLException se) {
0551:                } // Ignore "proc does not exist" errors
0552:
0553:                try {
0554:                    st
0555:                            .execute("create procedure TEST_PROC_JIRA_491(in i int) "
0556:                                    + "language java parameter style java external name "
0557:                                    + "'org.apache.derbyTesting.functionTests.util.ProcedureTest.BIG_COL_491' result sets 2");
0558:                } catch (SQLException se) {
0559:                    System.out
0560:                            .println("JIRA-491: FAILURE in procedure creation:");
0561:                    se.printStackTrace(System.out);
0562:                }
0563:
0564:                // Call setupStatementReuse which will make the server to reuse an existing statement. 
0565:                setupStatementReuse(conn);
0566:                CallableStatement cSt = conn
0567:                        .prepareCall("call TEST_PROC_JIRA_491(?)");
0568:                cSt.setInt(1, 3);
0569:                try {
0570:                    cSt.execute();
0571:                    do {
0572:                        ResultSet rs = cSt.getResultSet();
0573:                        while (rs.next()) {
0574:                            String s = rs.getString(2);
0575:                        }
0576:                    } while (cSt.getMoreResults());
0577:                    System.out.println("JIRA-491 Successful.");
0578:                } catch (Exception e) {
0579:                    System.out.println("JIRA-491 FAILURE: Caught Exception:");
0580:                    e.printStackTrace(System.out);
0581:                }
0582:
0583:                // JIRA-492: Result set has hundreds of columns.
0584:                // This test case, when originally filed, exposed several problems:
0585:                // - first, this test case causes the server to respond with a very
0586:                // long response message which gets handled using DRDA Layer B DSS
0587:                // segmentation. This long message was corrupted due to bug DERBY-125.
0588:                // - then, the test case causes the server to perform LMTBLKPRC
0589:                // message truncation in a situation in which there are multiple
0590:                // chained messages in the DDMWriter buffer. Due to bug DERBY-491/2,
0591:                // the message truncation logic truncated not only the last DSS block,
0592:                // but also the multi-segment long message which was still sitting
0593:                // unsent in the buffer.This then caused a HANG in the client, which
0594:                // waited forever for the never-to-be-sent truncated data.
0595:
0596:                try {
0597:                    st.execute("drop table jira492");
0598:                } catch (SQLException se) {
0599:                }
0600:
0601:                try {
0602:                    st
0603:                            .execute("create table jira492 (id integer, nsi smallint, "
0604:                                    + "ni integer, nbi DECIMAL(19,0), nd decimal(7,2), nr real, "
0605:                                    + "ndo double)");
0606:                    st.execute("insert into jira492 values ("
0607:                            + "1, 2, 3, 4.5, 6.7, 8.9, 10.11)");
0608:                } catch (SQLException se) {
0609:                    System.out.println("JIRA-492: FAILURE in data setup:");
0610:                    se.printStackTrace(System.out);
0611:                }
0612:
0613:                try {
0614:                    st.execute("drop procedure TEST_PROC_JIRA_492");
0615:                } catch (SQLException se) {
0616:                }
0617:
0618:                try {
0619:                    st
0620:                            .execute("create procedure TEST_PROC_JIRA_492() "
0621:                                    + "language java parameter style java external name "
0622:                                    + "'org.apache.derbyTesting.functionTests.util.ProcedureTest.LOTS_O_COLS_492' result sets 1");
0623:                } catch (SQLException se) {
0624:                    System.out
0625:                            .println("JIRA-492: FAILURE in procedure creation:");
0626:                    se.printStackTrace(System.out);
0627:                }
0628:
0629:                cSt = conn.prepareCall("call TEST_PROC_JIRA_492()");
0630:                cSt.execute();
0631:                System.out.println("JIRA-492 successful -- no hang!");
0632:            }
0633:
0634:            private static void executeProcedure(Statement s, String sql)
0635:                    throws SQLException {
0636:                boolean firstResultIsAResultSet = s.execute(sql);
0637:
0638:                procedureResults(s, firstResultIsAResultSet);
0639:            }
0640:
0641:            private static void executeProcedure(PreparedStatement ps)
0642:                    throws SQLException {
0643:                boolean firstResultIsAResultSet = ps.execute();
0644:
0645:                procedureResults(ps, firstResultIsAResultSet);
0646:            }
0647:
0648:            private static void procedureResults(Statement ps,
0649:                    boolean firstResultIsAResultSet) throws SQLException {
0650:
0651:                org.apache.derby.tools.JDBCDisplayUtil.ShowWarnings(System.out,
0652:                        ps);
0653:
0654:                boolean sawOneResult = false;
0655:                boolean isFirst = true;
0656:                do {
0657:
0658:                    boolean gotResult = false;
0659:
0660:                    ResultSet rs = ps.getResultSet();
0661:                    int updateCount = ps.getUpdateCount();
0662:                    if (rs == null) {
0663:
0664:                        if (isFirst && firstResultIsAResultSet) {
0665:                            System.out
0666:                                    .println("FAIL - execute() indicated first result was a result set but getResultSet() returned null");
0667:                        }
0668:
0669:                        if (updateCount != -1) {
0670:                            gotResult = true;
0671:                            sawOneResult = true;
0672:                            System.out.println("UPDATE COUNT " + updateCount);
0673:                        }
0674:                    } else {
0675:
0676:                        if (updateCount != -1)
0677:                            System.out
0678:                                    .println("FAIL - HAVE RESULT SET AND UPDATE COUNT OF "
0679:                                            + updateCount);
0680:                        org.apache.derby.tools.JDBCDisplayUtil.DisplayResults(
0681:                                System.out, rs, ps.getConnection());
0682:                        gotResult = true;
0683:                        sawOneResult = true;
0684:                    }
0685:
0686:                    // if we did not get a result and this is not the first result then
0687:                    // there is a bug since the getMoreResults() returned true.
0688:                    //
0689:                    // This may also be an error on the first pass but maybe it's
0690:                    // ok to have no results at all?
0691:                    if (!gotResult && !isFirst) {
0692:                        System.out
0693:                                .println("FAIL - getMoreResults indicated more results but none was found");
0694:                    }
0695:
0696:                    isFirst = false;
0697:
0698:                } while (ps.getMoreResults());
0699:                SQLWarning warnings = ps.getWarnings();
0700:                if (warnings != null)
0701:                    System.out.println("SQLWarning :" + warnings.getMessage());
0702:
0703:                if (!sawOneResult)
0704:                    System.out.println("No ResultSet or update count returned");
0705:            }
0706:
0707:            /**
0708:            	1. basic testing
0709:            	2. correct auto commit logic
0710:            	3. correct holdability (JDBC 3)
0711:             */
0712:            private static void dynamicResultSets(Connection conn,
0713:                    Connection conn2) throws SQLException {
0714:
0715:                System.out.println("dynamicResultSets - parameter style JAVA");
0716:
0717:                Statement s = conn.createStatement();
0718:
0719:                statementExceptionExpected(
0720:                        s,
0721:                        "create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets -1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");
0722:
0723:                s
0724:                        .execute("create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");
0725:
0726:                showMatchingProcedures(conn, "DRS");
0727:
0728:                callExceptionExpected(conn, "CALL DRS()");
0729:                callExceptionExpected(conn, "CALL DRS(?,?)");
0730:
0731:                CallableStatement drs1 = conn.prepareCall("CALL DRS(?)");
0732:
0733:                drs1.setInt(1, 3);
0734:                executeProcedure(drs1);
0735:                drs1.close();
0736:
0737:                s
0738:                        .execute("create procedure DRS2(p1 int, p2 int) parameter style JAVA READS SQL DATA dynamic result sets 2 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");
0739:                showMatchingProcedures(conn, "DRS2");
0740:
0741:                drs1 = conn.prepareCall("CALL DRS2(?, ?)");
0742:                drs1.setInt(1, 2);
0743:                drs1.setInt(2, 6);
0744:                executeProcedure(drs1);
0745:
0746:                // execute it returning one closed result set
0747:                drs1.setInt(1, 2);
0748:                drs1.setInt(2, 99); // will close the second result set
0749:                executeProcedure(drs1);
0750:
0751:                // execute it returning no result sets
0752:                if (!isDerbyNet) {
0753:                    //RESOLVE there appears to be a JCC Bug when returning no 
0754:                    // resultSets.
0755:                    drs1.setInt(1, 2);
0756:                    drs1.setInt(2, 199); // return no results at all
0757:                    executeProcedure(drs1);
0758:                }
0759:                // execute it returning two result sets but with the order swapped in the parameters
0760:                // doesnot affect display order.
0761:                drs1.setInt(1, 2);
0762:                drs1.setInt(2, 299); // swap results
0763:                executeProcedure(drs1);
0764:
0765:                if (!isDerbyNet) {
0766:                    // execute it returning two result sets, and check to see the result set is closed after getMoreResults.
0767:                    drs1.setInt(1, 2);
0768:                    drs1.setInt(2, 2);
0769:                    drs1.execute();
0770:                    ResultSet lastResultSet = null;
0771:                    int pass = 1;
0772:                    do {
0773:
0774:                        if (lastResultSet != null) {
0775:                            try {
0776:                                lastResultSet.next();
0777:                                System.out
0778:                                        .println("FAILED - result set should be closed");
0779:                            } catch (SQLException sqle) {
0780:                                System.out.println("EXPECTED : "
0781:                                        + sqle.getMessage());
0782:                            }
0783:                        }
0784:
0785:                        lastResultSet = drs1.getResultSet();
0786:                        System.out.println("pass " + (pass++)
0787:                                + " got result set " + (lastResultSet != null));
0788:
0789:                    } while (drs1.getMoreResults() || lastResultSet != null);
0790:
0791:                    checkCommitWithMultipleResultSets(drs1, conn2, "autocommit");
0792:                    checkCommitWithMultipleResultSets(drs1, conn2,
0793:                            "noautocommit");
0794:                    checkCommitWithMultipleResultSets(drs1, conn2, "statement");
0795:                }
0796:
0797:                drs1.close();
0798:
0799:                // use escape syntax
0800:                drs1 = conn.prepareCall("{call DRS2(?, ?)}");
0801:                drs1.setInt(1, 2);
0802:                drs1.setInt(2, 6);
0803:                executeProcedure(drs1);
0804:                drs1.close();
0805:
0806:                // check that a procedure with dynamic result sets can not resolve to a method with no ResultSet argument.
0807:                s
0808:                        .execute("create procedure irdrs(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.missingDynamicParameter' parameter style JAVA");
0809:                callExceptionExpected(conn, "CALL IRDRS(?)");
0810:                s.execute("drop procedure irdrs");
0811:
0812:                // check that a procedure with dynamic result sets can not resolve to a method with an argument that is a ResultSet impl,
0813:                s
0814:                        .execute("create procedure rsi(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.badDynamicParameter' parameter style JAVA");
0815:                callExceptionExpected(conn, "CALL rsi(?)");
0816:                s.execute("drop procedure rsi");
0817:
0818:                // simple check for a no-arg method that has dynamic result sets but does not return any
0819:                System.out.println("no dynamic result sets");
0820:                s
0821:                        .execute("create procedure zadrs() dynamic result sets 4 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArgDynamicResult' parameter style  JAVA");
0822:                CallableStatement zadrs = conn.prepareCall("CALL ZADRS()");
0823:                executeProcedure(zadrs);
0824:                zadrs.close();
0825:                s.execute("drop procedure ZADRS");
0826:
0827:                // return too many result sets
0828:                System.out.println("Testing too many result sets");
0829:                s
0830:                        .execute("create procedure way.toomany(p1 int, p2 int) READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows' parameter style  JAVA");
0831:                CallableStatement toomany = conn
0832:                        .prepareCall("CALL way.toomany(?, ?)");
0833:                toomany.setInt(1, 2);
0834:                toomany.setInt(2, 6);
0835:                System.out.println("... too many result sets");
0836:                executeProcedure(toomany);
0837:
0838:                System.out.println("... one additional closed result set");
0839:                toomany.setInt(1, 2);
0840:                toomany.setInt(2, 99); // will close the second result set.
0841:                executeProcedure(toomany);
0842:
0843:                toomany.close();
0844:                s.execute("drop procedure way.toomany");
0845:
0846:                testResultSetsWithLobs(conn);
0847:
0848:                s.close();
0849:                conn2.close();
0850:            }
0851:
0852:            private static void checkCommitWithMultipleResultSets(
0853:                    CallableStatement drs1, Connection conn2, String action)
0854:                    throws SQLException {
0855:                Connection conn = drs1.getConnection();
0856:                //Use reflection to set the holdability to false so that the test can run in jdk14 and lower jdks as well
0857:                try {
0858:                    Method sh = conn.getClass().getMethod("setHoldability",
0859:                            CONN_PARAM);
0860:                    sh.invoke(conn, CONN_ARG);
0861:                } catch (Exception e) {
0862:                    System.out.println("shouldn't get that error "
0863:                            + e.getMessage());
0864:                }//for jdks prior to jdk14
0865:
0866:                // check to see that the commit of the transaction happens at the correct time.
0867:                // switch isolation levels to keep the locks around.
0868:                int oldIsolation = conn.getTransactionIsolation();
0869:                boolean oldAutoCommit = conn.getAutoCommit();
0870:
0871:                if (action.equals("noautocommit"))
0872:                    conn.setAutoCommit(false);
0873:                else
0874:                    conn.setAutoCommit(true);
0875:
0876:                conn
0877:                        .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
0878:                System.out.println("auto commit is " + conn.getAutoCommit());
0879:                PreparedStatement psLocks = conn2
0880:                        .prepareStatement("select count(*) from SYSCS_DIAG.LOCK_TABLE AS LT");
0881:
0882:                showLocks(psLocks, "lock count before execution ");
0883:
0884:                drs1.execute();
0885:
0886:                showLocks(psLocks, "lock count after execution ");
0887:
0888:                ResultSet rs = drs1.getResultSet();
0889:                rs.next();
0890:                showLocks(psLocks, "lock count after next on first rs ");
0891:
0892:                boolean expectClosed = false;
0893:
0894:                // execute another statement to ensure that the result sets close.
0895:                if (action.equals("statement")) {
0896:                    System.out
0897:                            .println("executing statement to force auto commit on open CALL statement");
0898:
0899:                    conn.createStatement().executeQuery("values 1").next();
0900:                    expectClosed = true;
0901:                    showLocks(psLocks, "lock count after statement execution ");
0902:
0903:                    try {
0904:                        rs.next();
0905:                        System.out
0906:                                .println("FAIL - result set open in auto commit mode after another statement execution");
0907:                    } catch (SQLException sqle) {
0908:                        System.out.println("Expected - " + sqle.getMessage());
0909:                    }
0910:                }
0911:
0912:                boolean anyMore = drs1.getMoreResults();
0913:                System.out.println("Is there a second result ? " + anyMore);
0914:                showLocks(psLocks, "lock count after first getMoreResults() ");
0915:
0916:                if (anyMore) {
0917:
0918:                    rs = drs1.getResultSet();
0919:                    try {
0920:                        rs.next();
0921:                        if (expectClosed)
0922:                            System.out
0923:                                    .println("FAIL - result set open in auto commit mode after another statement execution");
0924:                    } catch (SQLException sqle) {
0925:                        if (expectClosed)
0926:                            System.out.println("Expected - "
0927:                                    + sqle.getMessage());
0928:                        else
0929:                            throw sqle;
0930:                    }
0931:                    showLocks(psLocks, "lock count after next on second rs ");
0932:
0933:                    // should commit here since all results are closed
0934:                    boolean more = drs1.getMoreResults();
0935:                    System.out
0936:                            .println("more results (should be false) " + more);
0937:                    showLocks(psLocks,
0938:                            "lock count after second getMoreResults() ");
0939:
0940:                    conn.setTransactionIsolation(oldIsolation);
0941:                    conn.setAutoCommit(oldAutoCommit);
0942:                }
0943:
0944:                psLocks.close();
0945:            }
0946:
0947:            private static void showLocks(PreparedStatement psLocks,
0948:                    String where) throws SQLException {
0949:                ResultSet locks = psLocks.executeQuery();
0950:                locks.next();
0951:                System.out.println(where + locks.getInt(1));
0952:                locks.close();
0953:            }
0954:
0955:            private static void testParameterTypes(Connection conn)
0956:                    throws SQLException {
0957:                System.out.println("parameterTypes");
0958:                Statement s = conn.createStatement();
0959:
0960:                s
0961:                        .execute("create table PT1(A INTEGER not null primary key, B CHAR(10), C VARCHAR(20))");
0962:                s
0963:                        .execute("create procedure PT1(IN a int, IN b char(10), c varchar(20)) parameter style java dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.parameter1' MODIFIES SQL DATA");
0964:                showMatchingProcedures(conn, "PT1");
0965:
0966:                CallableStatement pt1 = conn.prepareCall("CALL PT1(?, ?, ?)");
0967:
0968:                pt1.setInt(1, 20);
0969:                pt1.setString(2, "abc");
0970:                pt1.setString(3, "efgh");
0971:                executeProcedure(pt1);
0972:
0973:                pt1.setInt(1, 30);
0974:                pt1.setString(2, "abc   ");
0975:                pt1.setString(3, "efgh  ");
0976:                executeProcedure(pt1);
0977:
0978:                pt1.setInt(1, 40);
0979:                pt1
0980:                        .setString(
0981:                                2,
0982:                                "abc                                                                           ");
0983:                pt1
0984:                        .setString(
0985:                                3,
0986:                                "efgh                                                                             ");
0987:                executeProcedure(pt1);
0988:
0989:                pt1.setInt(1, 50);
0990:                pt1.setString(2, "0123456789X");
0991:                pt1.setString(3, "efgh  ");
0992:                executeProcedure(pt1);
0993:                pt1.close();
0994:
0995:                s.execute("DROP procedure PT1");
0996:
0997:                s
0998:                        .execute("create procedure PT2(IN a int, IN b DECIMAL(4), c DECIMAL(7,3)) parameter style java dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.parameter2' MODIFIES SQL DATA");
0999:                showMatchingProcedures(conn, "PT2");
1000:
1001:                CallableStatement pt2 = conn.prepareCall("CALL PT2(?, ?, ?)");
1002:
1003:                pt2.setInt(1, 60);
1004:                pt2.setString(2, "34");
1005:                pt2.setString(3, "54.1");
1006:                executeProcedure(pt2);
1007:
1008:                pt2.setInt(1, 70);
1009:                pt2.setBigDecimal(2, new BigDecimal("831"));
1010:                pt2.setBigDecimal(3, new BigDecimal("45.7"));
1011:                executeProcedure(pt2);
1012:
1013:                pt2.setInt(1, -1);
1014:                pt2.setBigDecimal(2, new BigDecimal("10243"));
1015:                pt2.setBigDecimal(3, null);
1016:                try {
1017:                    executeProcedure(pt2);
1018:                    System.out
1019:                            .println("FAIL - too many digits in decimal value accepted");
1020:                } catch (SQLException sqle) {
1021:                    System.out.println("EXPECTED SQL Exception: "
1022:                            + sqle.getMessage());
1023:                }
1024:                pt2.setInt(1, 80);
1025:                pt2.setBigDecimal(2, new BigDecimal("993"));
1026:                pt2.setBigDecimal(3, new BigDecimal("1234.5678"));
1027:                executeProcedure(pt2);
1028:                pt2.close();
1029:
1030:                s.execute("DROP procedure PT2");
1031:                /*		
1032:                 s.execute("create procedure PTBOOL2(IN p_in BOOLEAN, INOUT p_inout BOOLEAN, OUT p_out BOOLEAN) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pBOOLEAN' NO SQL");
1033:                 showMatchingProcedures(conn, "PTBOOL%");
1034:
1035:                 {
1036:
1037:                 CallableStatement ptb = conn.prepareCall("CALL PTBOOL2(?, ?, ?)");
1038:                 ptb.registerOutParameter(2, Types.BIT); 
1039:                 ptb.registerOutParameter(3, Types.BIT);
1040:
1041:                 if (!isDerbyNet){ // bug 5437
1042:                 ptb.setObject(1, null);
1043:                 ptb.setObject(2, Boolean.FALSE);
1044:                 try {
1045:                 ptb.execute();
1046:                 System.out.println("FAIL NULL PASSED to  primitive");
1047:                 } catch (SQLException sqle) {
1048:                 System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1049:                 }
1050:                 }
1051:
1052:                 ptb.setBoolean(1, true);
1053:                 ptb.setBoolean(2, false);
1054:                 ptb.execute();
1055:                 System.out.println("p_inout " + ptb.getObject(2) + " p_out " + ptb.getObject(3));
1056:                 ptb.setBoolean(2, false);
1057:                 ptb.execute();
1058:                 System.out.println("p_inout " + ptb.getBoolean(2) + " null?" + ptb.wasNull() + " p_out " + ptb.getBoolean(3) + " null?" + ptb.wasNull());
1059:                 ptb.close();
1060:                 }
1061:
1062:                 s.execute("DROP procedure PTBOOL2");
1063:
1064:                 s.execute("create procedure PTTINYINT2(IN p_in TINYINT, INOUT p_inout TINYINT, OUT p_out TINYINT) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pTINYINT' NO SQL");
1065:                 showMatchingProcedures(conn, "PTTINYINT%");
1066:
1067:
1068:                 CallableStatement ptti = conn.prepareCall("CALL PTTINYINT2(?, ?, ?)");
1069:                 ptti.registerOutParameter(2, Types.TINYINT); 
1070:                 ptti.registerOutParameter(3, Types.TINYINT);
1071:
1072:                 ptti.setNull(1, Types.TINYINT);
1073:                 ptti.setByte(2, (byte) 7);
1074:                 try {
1075:                 ptti.execute();
1076:                 System.out.println("FAIL NULL PASSED to  primitive");
1077:                 } catch (SQLException sqle) {
1078:                 System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1079:                 }
1080:
1081:                 ptti.setByte(1, (byte) 4);
1082:                 ptti.setNull(2, Types.TINYINT);
1083:                 try {
1084:                 ptti.execute();
1085:                 System.out.println("FAIL NULL PASSED to  primitive");
1086:                 } catch (SQLException sqle) {
1087:                 System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1088:                 }
1089:
1090:                 ptti.setByte(1, (byte) 6);
1091:                 ptti.setByte(2, (byte) 3);
1092:                 ptti.execute();
1093:                 System.out.println("p_inout " + ptti.getObject(2) + " p_out " + ptti.getObject(3));
1094:                 ptti.setByte(2, (byte) 3);
1095:                 ptti.execute();
1096:                 System.out.println("p_inout " + ptti.getByte(2) + " null?" + ptti.wasNull() + " p_out " + ptti.getByte(3) + " null?" + ptti.wasNull());
1097:                 ptti.close();
1098:
1099:
1100:                 s.execute("DROP procedure PTTINYINT2");
1101:
1102:                 */
1103:                s
1104:                        .execute("create procedure PTSMALLINT2(IN p_in SMALLINT, INOUT p_inout SMALLINT, OUT p_out SMALLINT) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pSMALLINT' NO SQL");
1105:                showMatchingProcedures(conn, "PTSMALLINT%");
1106:
1107:                CallableStatement ptsi = conn
1108:                        .prepareCall("CALL PTSMALLINT2(?, ?, ?)");
1109:                ptsi.registerOutParameter(2, Types.SMALLINT);
1110:                ptsi.registerOutParameter(3, Types.SMALLINT);
1111:
1112:                ptsi.setNull(1, Types.SMALLINT);
1113:                ptsi.setShort(2, (short) 7);
1114:                try {
1115:                    ptsi.execute();
1116:                    System.out.println("FAIL NULL PASSED to  primitive");
1117:                } catch (SQLException sqle) {
1118:                    System.out.println("EXPECTED SQL Exception: ("
1119:                            + sqle.getSQLState() + ") " + sqle.getMessage());
1120:                }
1121:
1122:                ptsi.setShort(1, (short) 4);
1123:                ptsi.setNull(2, Types.SMALLINT);
1124:                try {
1125:                    ptsi.execute();
1126:                    System.out.println("FAIL NULL PASSED to  primitive");
1127:                } catch (SQLException sqle) {
1128:                    System.out.println("EXPECTED SQL Exception: ("
1129:                            + sqle.getSQLState() + ") " + sqle.getMessage());
1130:                }
1131:
1132:                ptsi.setShort(1, (short) 6);
1133:                ptsi.setShort(2, (short) 3);
1134:                ptsi.execute();
1135:                System.out.println("p_inout " + ptsi.getObject(2) + " p_out "
1136:                        + ptsi.getObject(3));
1137:                ptsi.setShort(2, (short) 3);
1138:                ptsi.execute();
1139:                System.out.println("p_inout " + ptsi.getByte(2) + " null?"
1140:                        + ptsi.wasNull() + " p_out " + ptsi.getByte(3)
1141:                        + " null?" + ptsi.wasNull());
1142:
1143:                // with setObject . Beetle 5439
1144:                ptsi.setObject(1, new Integer(6));
1145:                ptsi.setObject(2, new Integer(3));
1146:
1147:                ptsi.execute();
1148:                System.out.println("p_inout " + ptsi.getByte(2) + " null?"
1149:                        + ptsi.wasNull() + " p_out " + ptsi.getByte(3)
1150:                        + " null?" + ptsi.wasNull());
1151:                ptsi.close();
1152:
1153:                s.execute("DROP procedure PTSMALLINT2");
1154:                s.execute("DROP TABLE PT1");
1155:
1156:                s.close();
1157:
1158:            }
1159:
1160:            private static void testOutparams(Connection conn)
1161:                    throws SQLException {
1162:
1163:                System.out.println("outparams");
1164:
1165:                Statement s = conn.createStatement();
1166:
1167:                s
1168:                        .execute("create procedure OP1(OUT a int, IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.outparams1'");
1169:                showMatchingProcedures(conn, "OP1");
1170:
1171:                // check execute via a Statement fails for use of OUT parameter
1172:                if (!isDerbyNet) { // bug 5263
1173:                    try {
1174:                        executeProcedure(s, "CALL OP1(?, ?)");
1175:                        System.out
1176:                                .println("FAIL execute succeeded on OUT param with Statement");
1177:                    } catch (SQLException sqle) {
1178:                        System.out.println("EXPECTED SQL Exception: "
1179:                                + sqle.getMessage());
1180:                    }
1181:                }
1182:
1183:                if (!isDerbyNet) { // bug 5276
1184:                    // check execute via a PreparedStatement fails for use of OUT parameter
1185:                    try {
1186:                        PreparedStatement ps = conn
1187:                                .prepareStatement("CALL OP1(?, ?)");
1188:                        System.out
1189:                                .println("FAIL prepare succeeded on OUT param with PreparedStatement");
1190:                    } catch (SQLException sqle) {
1191:                        System.out.println("EXPECTED SQL Exception: "
1192:                                + sqle.getMessage());
1193:                    }
1194:                }
1195:
1196:                CallableStatement op = conn.prepareCall("CALL OP1(?, ?)");
1197:
1198:                op.registerOutParameter(1, Types.INTEGER);
1199:                op.setInt(2, 7);
1200:
1201:                executeProcedure(op);
1202:
1203:                System.out.println("OP1 " + op.getInt(1) + " null ? "
1204:                        + op.wasNull());
1205:
1206:                op.close();
1207:
1208:                s
1209:                        .execute("create procedure OP2(INOUT a int, IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams2'");
1210:                showMatchingProcedures(conn, "OP2");
1211:
1212:                // check execute via a Statement fails for use of INOUT parameter
1213:                if (!isDerbyNet) { // bug 5263
1214:                    try {
1215:                        executeProcedure(s, "CALL OP2(?, ?)");
1216:                        System.out
1217:                                .println("FAIL execute succeeded on INOUT param with Statement");
1218:                    } catch (SQLException sqle) {
1219:                        System.out.println("EXPECTED SQL Exception: "
1220:                                + sqle.getMessage());
1221:                    }
1222:                }
1223:
1224:                if (!isDerbyNet) { // bug 5276
1225:
1226:                    // check execute via a PreparedStatement fails for use of INOUT parameter
1227:                    try {
1228:                        PreparedStatement ps = conn
1229:                                .prepareStatement("CALL OP2(?, ?)");
1230:                        System.out
1231:                                .println("FAIL prepare succeeded on INOUT param with PreparedStatement");
1232:                    } catch (SQLException sqle) {
1233:                        System.out.println("EXPECTED SQL Exception: "
1234:                                + sqle.getMessage());
1235:                    }
1236:                }
1237:
1238:                op = conn.prepareCall("CALL OP2(?, ?)");
1239:
1240:                op.registerOutParameter(1, Types.INTEGER);
1241:                op.setInt(1, 3);
1242:                op.setInt(2, 7);
1243:
1244:                executeProcedure(op);
1245:                System.out.println("OP2 " + op.getInt(1) + " null ? "
1246:                        + op.wasNull());
1247:                op.close();
1248:
1249:                // INOUT & OUT procedures with variable length
1250:                s
1251:                        .execute("create procedure OP3(INOUT a CHAR(10), IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams3'");
1252:                showMatchingProcedures(conn, "OP3");
1253:
1254:                op = conn.prepareCall("CALL OP3(?, ?)");
1255:
1256:                op.registerOutParameter(1, Types.CHAR);
1257:                op.setString(1, "dan");
1258:                op.setInt(2, 8);
1259:
1260:                executeProcedure(op);
1261:                System.out.println("OP3 >" + op.getString(1) + "< null ? "
1262:                        + op.wasNull());
1263:                op.close();
1264:
1265:                // INOUT & OUT DECIMAL procedures with variable length
1266:                s
1267:                        .execute("create procedure OP4(OUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams4'");
1268:                showMatchingProcedures(conn, "OP4");
1269:
1270:                op = conn.prepareCall("CALL OP4(?, ?)");
1271:
1272:                op.registerOutParameter(1, Types.DECIMAL);
1273:                op.setString(2, null);
1274:                executeProcedure(op);
1275:                System.out.println("OP4 null >" + op.getBigDecimal(1)
1276:                        + "< null ? " + op.wasNull());
1277:
1278:                op.setString(2, "14");
1279:                executeProcedure(op);
1280:                System.out.println("OP4 14 >" + op.getBigDecimal(1)
1281:                        + "< null ? " + op.wasNull());
1282:
1283:                op.setString(2, "11.3");
1284:                executeProcedure(op);
1285:                System.out.println("OP4 11.3 >" + op.getBigDecimal(1)
1286:                        + "< null ? " + op.wasNull());
1287:
1288:                op.setString(2, "39.345");
1289:                executeProcedure(op);
1290:                System.out.println("OP4 39.345 >" + op.getBigDecimal(1)
1291:                        + "< null ? " + op.wasNull());
1292:
1293:                op.setString(2, "83");
1294:                try {
1295:                    executeProcedure(op);
1296:                    System.out
1297:                            .println("FAIL - execution ok on out of range out parameter");
1298:                } catch (SQLException sqle) {
1299:                    System.out.println("EXPECTED SQL Exception: "
1300:                            + sqle.getMessage());
1301:                }
1302:
1303:                if (!isDerbyNet) {
1304:                    // Bug 5316 - JCC clears registration with  clearParameters()
1305:                    op.clearParameters();
1306:                    try {
1307:                        // b not set
1308:                        executeProcedure(op);
1309:                        System.out.println("FAIL - b not set");
1310:                    } catch (SQLException sqle) {
1311:                        System.out.println("EXPECTED SQL Exception: "
1312:                                + sqle.getMessage());
1313:                    }
1314:
1315:                    // try to set an OUT param
1316:                    try {
1317:                        op.setBigDecimal(1, new BigDecimal("22.32"));
1318:                        System.out.println("FAIL - set OUT param to value");
1319:                    } catch (SQLException sqle) {
1320:                        System.out.println("EXPECTED SQL Exception: "
1321:                                + sqle.getMessage());
1322:                    }
1323:
1324:                    try {
1325:                        op.setBigDecimal(1, null);
1326:                        System.out
1327:                                .println("FAIL - set OUT param to null value");
1328:                    } catch (SQLException sqle) {
1329:                        System.out.println("EXPECTED SQL Exception: "
1330:                                + sqle.getMessage());
1331:                    }
1332:                    try {
1333:                        op.setNull(1, Types.DECIMAL);
1334:                        System.out.println("FAIL - set OUT param to null");
1335:                    } catch (SQLException sqle) {
1336:                        System.out.println("EXPECTED SQL Exception: "
1337:                                + sqle.getMessage());
1338:                    }
1339:                }
1340:
1341:                // can we get an IN param?
1342:                op.setString(2, "49.345");
1343:                executeProcedure(op);
1344:                System.out.println("OP4 49.345 >" + op.getBigDecimal(1)
1345:                        + "< null ? " + op.wasNull());
1346:                try {
1347:                    System.out.println("FAIL OP4 GET 49.345 >"
1348:                            + op.getString(2) + "< null ? " + op.wasNull());
1349:                } catch (SQLException sqle) {
1350:                    System.out.println("EXPECTED SQL Exception: "
1351:                            + sqle.getMessage());
1352:                }
1353:                op.close();
1354:
1355:                // check to see that a registration is required first for the out parameter.
1356:                op = conn.prepareCall("CALL OP4(?, ?)");
1357:                op.setString(2, "14");
1358:                try {
1359:                    executeProcedure(op);
1360:                    System.out
1361:                            .println("FAIL - execute succeeded without registration of out parameter");
1362:                } catch (SQLException sqle) {
1363:                    expectedException(sqle);
1364:                }
1365:                op.close();
1366:
1367:                s
1368:                        .execute("create procedure OP4INOUT(INOUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams4'");
1369:                showMatchingProcedures(conn, "OP4INOUT");
1370:
1371:                // bug 5264 - first execution fails with parameter not set.
1372:
1373:                op = conn.prepareCall("CALL OP4INOUT(?, ?)");
1374:                op.registerOutParameter(1, Types.DECIMAL);
1375:
1376:                op.setString(2, null);
1377:
1378:                op.setBigDecimal(1, null);
1379:                executeProcedure(op);
1380:                System.out.println("OP4INOUT null >" + op.getBigDecimal(1)
1381:                        + "< null ? " + op.wasNull());
1382:
1383:                op.setBigDecimal(1, new BigDecimal("99"));
1384:                executeProcedure(op);
1385:                System.out.println("OP4INOUT null(2) >" + op.getBigDecimal(1)
1386:                        + "< null ? " + op.wasNull());
1387:
1388:                op.setString(2, "23.5");
1389:                op.setBigDecimal(1, new BigDecimal("14"));
1390:                executeProcedure(op);
1391:                System.out.println("OP4INOUT 14+23.5 >" + op.getBigDecimal(1)
1392:                        + "< null ? " + op.wasNull());
1393:
1394:                op.setString(2, "23.505");
1395:                op.setBigDecimal(1, new BigDecimal("9"));
1396:                executeProcedure(op);
1397:                System.out.println("OP4INOUT 9+23.505 >" + op.getBigDecimal(1)
1398:                        + "< null ? " + op.wasNull());
1399:
1400:                if (!isDerbyNet) { // with the network server it retains its old value of 9 
1401:                    // repeat execution. INOUT parameter now has the value 32.50
1402:                    executeProcedure(op);
1403:                    System.out.println("OP4INOUT 32.50+23.505 >"
1404:                            + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1405:
1406:                } // end bug 5264
1407:
1408:                op.setString(2, "67.99");
1409:                op.setBigDecimal(1, new BigDecimal("32.01"));
1410:                try {
1411:                    executeProcedure(op);
1412:                    System.out.println("FAIL OP4INOUT 32.01+67.99 >"
1413:                            + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1414:                } catch (SQLException sqle) {
1415:                    System.out.println("EXPECTED SQL Exception: "
1416:                            + sqle.getMessage());
1417:                }
1418:
1419:                op.setString(2, "1");
1420:                op.setBigDecimal(1, new BigDecimal("102.33"));
1421:                try {
1422:                    executeProcedure(op);
1423:                    System.out.println("FAIL OP4INOUT 1+102.33 >"
1424:                            + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1425:                } catch (SQLException sqle) {
1426:                    System.out.println("EXPECTED SQL Exception: "
1427:                            + sqle.getMessage());
1428:                }
1429:
1430:                if (!isDerbyNet) {
1431:                    // now some checks to requirements for parameter setting.
1432:                    op.clearParameters();
1433:                    try {
1434:                        // a,b not set
1435:                        executeProcedure(op);
1436:                        System.out.println("FAIL - a,b not set");
1437:                    } catch (SQLException sqle) {
1438:                        System.out.println("EXPECTED SQL Exception: "
1439:                                + sqle.getMessage());
1440:                    }
1441:
1442:                    op.setString(2, "2");
1443:                    try {
1444:                        // a not set
1445:                        executeProcedure(op);
1446:                        System.out.println("FAIL - a  not set");
1447:                    } catch (SQLException sqle) {
1448:                        System.out.println("EXPECTED SQL Exception: "
1449:                                + sqle.getMessage());
1450:                    }
1451:
1452:                    op.clearParameters();
1453:                    op.setBigDecimal(1, new BigDecimal("33"));
1454:                    try {
1455:                        // b not set
1456:                        executeProcedure(op);
1457:                        System.out.println("FAIL - b  not set");
1458:                    } catch (SQLException sqle) {
1459:                        expectedException(sqle);
1460:                    }
1461:
1462:                } // end bug 5264
1463:
1464:                op.close();
1465:
1466:                op = conn.prepareCall("CALL OP4INOUT(?, ?)");
1467:                op.setString(2, "14");
1468:                try {
1469:                    executeProcedure(op);
1470:                    System.out
1471:                            .println("FAIL - execute succeeded without registration of INOUT parameter");
1472:                } catch (SQLException sqle) {
1473:                    expectedException(sqle);
1474:                }
1475:                op.close();
1476:
1477:                s.execute("DROP PROCEDURE OP1");
1478:                s.execute("DROP PROCEDURE OP2");
1479:                s.execute("DROP PROCEDURE OP3");
1480:                s.execute("DROP PROCEDURE OP4");
1481:                s.execute("DROP PROCEDURE OP4INOUT");
1482:                s.close();
1483:
1484:            }
1485:
1486:            private static final String[] LITERALS = { "12" /* INTEGER */,
1487:                    "23.43e1" /* DOUBLE */, "176.3" /* DECIMAL */, "'12.34'" /* VARCHAR */};
1488:            private static final String[] LIT_PROC_TYPES = { "SMALLINT",
1489:                    "INTEGER", "BIGINT", "REAL", "DOUBLE", "DECIMAL", "CHAR",
1490:                    "VARCHAR" };
1491:
1492:            private static void testLiterals(Connection conn)
1493:                    throws SQLException {
1494:
1495:                System.out.println("literals");
1496:
1497:                Statement s = conn.createStatement();
1498:
1499:                s
1500:                        .execute("CREATE PROCEDURE LITT.TY_SMALLINT(IN P1 SMALLINT, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1501:                s
1502:                        .execute("CREATE PROCEDURE LITT.TY_INTEGER(IN P1 INTEGER, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1503:                s
1504:                        .execute("CREATE PROCEDURE LITT.TY_BIGINT(IN P1 BIGINT, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1505:                s
1506:                        .execute("CREATE PROCEDURE LITT.TY_REAL(IN P1 REAL, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1507:                s
1508:                        .execute("CREATE PROCEDURE LITT.TY_DOUBLE(IN P1 DOUBLE, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1509:                s
1510:                        .execute("CREATE PROCEDURE LITT.TY_DECIMAL(IN P1 DECIMAL(5,2), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1511:                s
1512:                        .execute("CREATE PROCEDURE LITT.TY_CHAR(IN P1 CHAR(10), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1513:                s
1514:                        .execute("CREATE PROCEDURE LITT.TY_VARCHAR(IN P1 VARCHAR(10), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1515:
1516:                showMatchingProcedures(conn, "TY_%");
1517:
1518:                for (int t = 0; t < LIT_PROC_TYPES.length; t++) {
1519:
1520:                    String type = LIT_PROC_TYPES[t];
1521:
1522:                    String sql = "CALL LITT.TY_" + type + " (null, ?)";
1523:                    System.out.print(sql);
1524:
1525:                    try {
1526:                        CallableStatement cs = conn.prepareCall(sql);
1527:                        cs.registerOutParameter(1, Types.VARCHAR);
1528:                        cs.execute();
1529:                        String val = cs.getString(1);
1530:                        cs.close();
1531:                        System.out
1532:                                .println("=" + (val == null ? "<NULL>" : val));
1533:                    } catch (SQLException sqle) {
1534:                        System.out.println(" (" + sqle.getSQLState() + ") "
1535:                                + sqle.getMessage());
1536:                        // more code should be added to check on assignments
1537:                        // for now, commenting out the print of the stack, to prevent 
1538:                        // failures due to differences between jvms.
1539:                        // sqle.printStackTrace(System.out);
1540:                    }
1541:                }
1542:
1543:                for (int l = 0; l < LITERALS.length; l++) {
1544:                    String literal = LITERALS[l];
1545:                    for (int t = 0; t < LIT_PROC_TYPES.length; t++) {
1546:
1547:                        String type = LIT_PROC_TYPES[t];
1548:
1549:                        String sql = "CALL LITT.TY_" + type + " (" + literal
1550:                                + ", ?)";
1551:                        System.out.print(sql);
1552:
1553:                        try {
1554:                            CallableStatement cs = conn.prepareCall(sql);
1555:                            cs.registerOutParameter(1, Types.VARCHAR);
1556:                            cs.execute();
1557:                            String val = cs.getString(1);
1558:                            cs.close();
1559:                            System.out.println("="
1560:                                    + (val == null ? "<NULL>" : val));
1561:                        } catch (SQLException sqle) {
1562:                            System.out.println(" (" + sqle.getSQLState() + ") "
1563:                                    + sqle.getMessage());
1564:                            // code should be added to show the expected errors, now commenting 
1565:                            // out the stack print to prevent false failures with different jvms
1566:                            //sqle.printStackTrace(System.out);
1567:                        }
1568:                    }
1569:                }
1570:            }
1571:
1572:            private static void expectedException(SQLException sqle) {
1573:                String sqlState = sqle.getSQLState();
1574:                if (sqlState == null) {
1575:                    sqlState = "<NULL>";
1576:                }
1577:                System.out.println("EXPECTED SQL Exception: (" + sqlState
1578:                        + ") " + sqle.getMessage());
1579:            }
1580:
1581:            private static void testSQLControl(Connection conn)
1582:                    throws SQLException {
1583:
1584:                System.out.println("SQL Control");
1585:
1586:                Statement s = conn.createStatement();
1587:
1588:                s.execute("CREATE SCHEMA SQLC");
1589:                s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
1590:                s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");
1591:
1592:                String[] control = { "", "NO SQL", "CONTAINS SQL",
1593:                        "READS SQL DATA", "MODIFIES SQL DATA" };
1594:
1595:                for (int i = 0; i < control.length; i++) {
1596:
1597:                    StringBuffer cp = new StringBuffer(256);
1598:                    cp.append("CREATE PROCEDURE SQLC.SQLCONTROL1_");
1599:                    cp.append(i);
1600:                    cp
1601:                            .append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
1602:                    cp.append(control[i]);
1603:                    cp
1604:                            .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl'");
1605:
1606:                    String cpsql = cp.toString();
1607:                    System.out.println(cpsql);
1608:
1609:                    s.execute(cpsql);
1610:
1611:                    cp.setLength(0);
1612:                    cp.append("CREATE PROCEDURE SQLC.SQLCONTROL2_");
1613:                    cp.append(i);
1614:                    cp
1615:                            .append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
1616:                    cp.append(control[i]);
1617:                    cp
1618:                            .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl2'");
1619:
1620:                    cpsql = cp.toString();
1621:                    System.out.println(cpsql);
1622:
1623:                    s.execute(cpsql);
1624:
1625:                    cp.setLength(0);
1626:                    cp.append("CREATE PROCEDURE SQLC.SQLCONTROL3_");
1627:                    cp.append(i);
1628:                    cp
1629:                            .append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
1630:                    cp.append(control[i]);
1631:                    cp
1632:                            .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl3'");
1633:
1634:                    cpsql = cp.toString();
1635:                    System.out.println(cpsql);
1636:
1637:                    s.execute(cpsql);
1638:
1639:                    cp.setLength(0);
1640:                    cp.append("CREATE PROCEDURE SQLC.SQLCONTROL4_");
1641:                    cp.append(i);
1642:                    cp
1643:                            .append(" (IN SQLC INTEGER, OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128), OUT E8 VARCHAR(128)) ");
1644:                    cp.append(control[i]);
1645:                    cp
1646:                            .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl4'");
1647:
1648:                    cpsql = cp.toString();
1649:                    System.out.println(cpsql);
1650:
1651:                    s.execute(cpsql);
1652:                }
1653:                showMatchingProcedures(conn, "SQLCONTROL1_%");
1654:                showMatchingProcedures(conn, "SQLCONTROL2_%");
1655:                showMatchingProcedures(conn, "SQLCONTROL3_%");
1656:                showMatchingProcedures(conn, "SQLCONTROL4_%");
1657:
1658:                if (!conn.getAutoCommit())
1659:                    conn.commit();
1660:
1661:                for (int i = 0; i < control.length; i++) {
1662:                    String type = control[i];
1663:                    if (type.length() == 0)
1664:                        type = "DEFAULT (MODIFIES SQL DATA)";
1665:
1666:                    System.out.println("** SQL ** " + type);
1667:                    for (int k = 1; k <= 3; k++) {
1668:                        CallableStatement cs = conn
1669:                                .prepareCall("CALL SQLC.SQLCONTROL" + k + "_"
1670:                                        + i + " (?, ?, ?, ?, ?, ?, ?)");
1671:                        for (int rop = 1; rop <= 7; rop++) {
1672:                            cs.registerOutParameter(rop, Types.VARCHAR);
1673:                        }
1674:                        cs.execute();
1675:                        for (int p = 1; p <= 7; p++) {
1676:                            System.out.println("    " + cs.getString(p));
1677:                        }
1678:                        cs.close();
1679:                    }
1680:
1681:                }
1682:
1683:                // test procedures that call others, e.g. to ensure that within a READS SQL DATA procedure, a MODIFIES SQL DATA cannot be called.
1684:                // table was dropped by previous executions.
1685:                s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
1686:                s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");
1687:                for (int i = 0; i < control.length; i++) {
1688:                    String type = control[i];
1689:                    if (type.length() == 0)
1690:                        type = "DEFAULT (MODIFIES SQL DATA)";
1691:
1692:                    System.out.println("CALL ** " + type);
1693:                    for (int t = 0; t < control.length; t++) {
1694:
1695:                        String ttype = control[t];
1696:                        if (ttype.length() == 0)
1697:                            ttype = "DEFAULT (MODIFIES SQL DATA)";
1698:                        System.out.println("    CALLLING " + ttype);
1699:                        CallableStatement cs = conn
1700:                                .prepareCall("CALL SQLC.SQLCONTROL4_" + i
1701:                                        + " (?, ?, ?, ?, ?, ?, ?, ?, ?)");
1702:                        cs.setInt(1, t);
1703:                        for (int rop = 2; rop <= 9; rop++) {
1704:                            cs.registerOutParameter(rop, Types.VARCHAR);
1705:                        }
1706:
1707:                        cs.execute();
1708:                        for (int p = 2; p <= 9; p++) {
1709:                            String so = cs.getString(p);
1710:                            if (so == null)
1711:                                continue;
1712:                            System.out.println("         " + so);
1713:                        }
1714:                        cs.close();
1715:                    }
1716:                }
1717:                // Make sure we throw proper error with network server 
1718:                // if params are not registered
1719:                testBug5280(conn);
1720:
1721:                s.execute("DROP TABLE SQLC.SQLCONTROL_DML");
1722:
1723:                for (int i = 0; i < control.length; i++) {
1724:                    s.execute("DROP PROCEDURE SQLCONTROL1_" + i);
1725:                    s.execute("DROP PROCEDURE SQLCONTROL2_" + i);
1726:                    s.execute("DROP PROCEDURE SQLCONTROL4_" + i);
1727:                }
1728:                s.execute("DROP TABLE SQLC.SQLCONTROL_DDL");
1729:                s.execute("SET SCHEMA APP");
1730:                s.execute("DROP SCHEMA SQLC RESTRICT");
1731:
1732:                s.close();
1733:            }
1734:
1735:            private static void showMatchingProcedures(Connection conn,
1736:                    String procedureName) throws SQLException {
1737:                // Until cs defaults to hold cursor we need to turn autocommit off 
1738:                // while we do this because one metadata call will close the other's
1739:                // cursor
1740:                boolean saveAutoCommit = conn.getAutoCommit();
1741:                conn.setAutoCommit(false);
1742:                System.out.println("DEFINED PROCEDURES FOR " + procedureName);
1743:                PreparedStatement ps = conn
1744:                        .prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE "
1745:                                + " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");
1746:
1747:                ps.setString(1, procedureName);
1748:
1749:                ResultSet rs = ps.executeQuery();
1750:                while (rs.next()) {
1751:                    System.out.println("  " + rs.getString(1) + "."
1752:                            + rs.getString(2) + " AS " + rs.getString(3));
1753:                }
1754:                rs.close();
1755:
1756:                System.out.println("DATABASE METATDATA PROCEDURES FOR "
1757:                        + procedureName);
1758:                DatabaseMetaData dmd = conn.getMetaData();
1759:
1760:                rs = dmd.getProcedures(null, null, procedureName);
1761:                // with jcc 2.1 for now this will fail on the second round, 
1762:                // because the resultset gets closed when we do getProcedureColumns. 
1763:                // thus, catch that gracefully...
1764:                try {
1765:                    while (rs.next()) {
1766:                        String schema = rs.getString(2);
1767:                        String name = rs.getString(3);
1768:                        System.out.println("  " + schema + "." + name + " AS "
1769:                                + rs.getString(7) + " type "
1770:                                + TYPE(rs.getShort(8)));
1771:                        // get the column information.
1772:                        ResultSet rsc = dmd.getProcedureColumns(null, schema,
1773:                                name, null);
1774:                        while (rsc.next()) {
1775:                            System.out
1776:                                    .println("    "
1777:                                            + PARAMTYPE(rsc.getShort(5)) + " "
1778:                                            + rsc.getString(4) + " "
1779:                                            + rsc.getString(7));
1780:                        }
1781:                        rsc.close();
1782:                    }
1783:                    rs.close();
1784:                    // restore previous autocommit mode
1785:                    conn.setAutoCommit(saveAutoCommit);
1786:                } catch (SQLException sqle) {
1787:                    System.out.println("FAILure: ");
1788:                    sqle.printStackTrace();
1789:                }
1790:
1791:                System.out.println("------------");
1792:            }
1793:
1794:            static String TYPE(short type) {
1795:                switch (type) {
1796:                case DatabaseMetaData.procedureResultUnknown:
1797:                    return "procedureResultUnknown";
1798:                case DatabaseMetaData.procedureNoResult:
1799:                    return "procedureNoResult";
1800:                case DatabaseMetaData.procedureReturnsResult:
1801:                    return "procedureReturnsResult";
1802:                default:
1803:                    return "??????";
1804:                }
1805:
1806:            }
1807:
1808:            static String PARAMTYPE(short type) {
1809:                switch (type) {
1810:                case DatabaseMetaData.procedureColumnUnknown:
1811:                    return "procedureColumnUnknown";
1812:                case DatabaseMetaData.procedureColumnIn:
1813:                    return "procedureColumnIn";
1814:                case DatabaseMetaData.procedureColumnInOut:
1815:                    return "procedureColumnInOut";
1816:                case DatabaseMetaData.procedureColumnOut:
1817:                    return "procedureColumnOut";
1818:                case DatabaseMetaData.procedureColumnReturn:
1819:                    return "procedureColumnReturn";
1820:                case DatabaseMetaData.procedureColumnResult:
1821:                    return "procedureColumnResult";
1822:                default:
1823:                    return "???";
1824:                }
1825:            }
1826:
1827:            private static void statementExceptionExpected(Statement s,
1828:                    String sql) {
1829:                System.out.println(sql);
1830:                try {
1831:                    s.execute(sql);
1832:                    System.out
1833:                            .println("FAIL - SQL expected to throw exception");
1834:                } catch (SQLException sqle) {
1835:                    expectedException(sqle);
1836:                }
1837:            }
1838:
1839:            private static void callExceptionExpected(Connection conn,
1840:                    String callSQL) throws SQLException {
1841:                System.out.println(callSQL);
1842:                try {
1843:                    CallableStatement cs = conn.prepareCall(callSQL);
1844:                    executeProcedure(cs);
1845:                    cs.close();
1846:                    System.out
1847:                            .println("FAIL - SQL expected to throw exception ");
1848:                } catch (SQLException sqle) {
1849:                    expectedException(sqle);
1850:                }
1851:            }
1852:
1853:            /* ****
1854:             * Beetle 5292 (for Network Server): Check for the return
1855:             * of LOB columns in a result set.
1856:             */
1857:
1858:            private static void testResultSetsWithLobs(Connection conn) {
1859:
1860:                Statement s = null;
1861:
1862:                // Create objects.
1863:                try {
1864:                    s = conn.createStatement();
1865:
1866:                    // Clob.
1867:                    s.execute("create table lobCheckOne (c clob(30))");
1868:                    s.execute("insert into lobCheckOne values (cast "
1869:                            + "('yayorsomething' as clob(30)))");
1870:                    s.execute("insert into lobCheckOne values (cast "
1871:                            + "('yayorsomething2' as clob(30)))");
1872:                    s
1873:                            .execute("create procedure clobproc () parameter style java "
1874:                                    + "language java external name "
1875:                                    + "'org.apache.derbyTesting.functionTests.util.ProcedureTest.clobselect' "
1876:                                    + "dynamic result sets 3 reads sql data");
1877:                    // Blob.
1878:                    s.execute("create table lobCheckTwo (b blob(30))");
1879:                    s.execute("insert into lobCheckTwo values (cast " + "("
1880:                            + TestUtil.stringToHexLiteral("101010001101")
1881:                            + " as blob(30)))");
1882:                    s.execute("insert into lobCheckTwo values (cast " + "("
1883:                            + TestUtil.stringToHexLiteral("101010001101")
1884:                            + " as blob(30)))");
1885:                    s
1886:                            .execute("create procedure blobproc () parameter style java "
1887:                                    + "language java external name "
1888:                                    + "'org.apache.derbyTesting.functionTests.util.ProcedureTest.blobselect' "
1889:                                    + "dynamic result sets 1 reads sql data");
1890:
1891:                } catch (SQLException e) {
1892:                    System.out
1893:                            .println("FAIL: Couldn't create required objects:");
1894:                    e.printStackTrace();
1895:                }
1896:
1897:                // Run 5292 Tests.
1898:                try {
1899:
1900:                    // Clobs.
1901:
1902:                    System.out
1903:                            .println("Stored Procedure w/ CLOB in result set.");
1904:                    CallableStatement cs = conn.prepareCall("CALL clobproc()");
1905:                    executeProcedure(cs);
1906:                    cs.close();
1907:
1908:                    // Blobs.
1909:
1910:                    System.out
1911:                            .println("Stored Procedure w/ BLOB in result set.");
1912:                    cs = conn.prepareCall("CALL blobproc()");
1913:                    executeProcedure(cs);
1914:                    cs.close();
1915:
1916:                } catch (Exception e) {
1917:                    System.out.println("FAIL: Encountered exception:");
1918:                    e.printStackTrace();
1919:                }
1920:
1921:                try {
1922:                    // Clean up.
1923:                    s.execute("drop table lobCheckOne");
1924:                    s.execute("drop table lobCheckTwo");
1925:                    s.execute("drop procedure clobproc");
1926:                    s.execute("drop procedure blobproc");
1927:                    s.close();
1928:                } catch (Exception e) {
1929:                    System.out
1930:                            .println("FAIL: Cleanup for lob result sets test:");
1931:                    e.printStackTrace();
1932:                }
1933:
1934:                return;
1935:
1936:            }
1937:
1938:            /**
1939:             * Sets up and runs two tests with multiple ResultSets
1940:             * 
1941:             * @param conn The Connection
1942:             * @throws SQLException
1943:             */
1944:            private static void multipleRSTests(Connection conn)
1945:                    throws SQLException {
1946:                //DerbyNet is known to fail this test
1947:                if (TestUtil.isJCCFramework())
1948:                    return;
1949:
1950:                setHoldability(conn, JDBC30Translation.HOLD_CURSORS_OVER_COMMIT);
1951:                int iso = conn.getTransactionIsolation();
1952:                conn
1953:                        .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1954:                //Installing Procedure
1955:                Statement stmt = conn.createStatement();
1956:                ResultSet rs = stmt
1957:                        .executeQuery("select tablename from sys.systables "
1958:                                + "where tablename = 'AUTOCOMMITTABLE'");
1959:                if (rs.next()) {
1960:                    rs.close();
1961:                    stmt.executeUpdate("delete from autoCommitTable");
1962:                } else {
1963:                    rs.close();
1964:                    stmt
1965:                            .executeUpdate("create table autoCommitTable (num int)");
1966:                }
1967:
1968:                ResultSet mdrs = conn.getMetaData().getProcedures(null, null,
1969:                        "MULTIRESULT");
1970:                if (mdrs != null || !mdrs.next()) {
1971:                    stmt
1972:                            .executeUpdate("create procedure multiResult(p1 int, "
1973:                                    + "p2 int) parameter style JAVA READS SQL DATA dynamic "
1974:                                    + "result sets 2 language java external name "
1975:                                    + "'org.apache.derbyTesting.functionTests."
1976:                                    + "util.ProcedureTest.multiResult'");
1977:                }
1978:                mdrs.close();
1979:                multipleRSAutoCommit(conn);
1980:                multipleRSNoCommit(conn);
1981:                stmt.executeUpdate("drop procedure multiResult");
1982:                stmt.executeUpdate("drop table autoCommitTable");
1983:                stmt.close();
1984:                conn.setTransactionIsolation(iso);
1985:            }
1986:
1987:            /**
1988:             * Test to see that an auto commit occurs for multiple ResultSets if all 
1989:             * ResultSets but one are closed and the final ResultSet has completed.
1990:             * 
1991:             * @param conn The Connection
1992:             * @throws SQLException
1993:             */
1994:            private static void multipleRSAutoCommit(Connection conn)
1995:                    throws SQLException {
1996:                System.out.print("MultipleRSAutoCommit: ");
1997:                CallableStatement cs = conn
1998:                        .prepareCall("call multiResult(?, ?)");
1999:                cs.setInt(1, 1);
2000:                cs.setInt(2, 2);
2001:                cs.execute();
2002:                ResultSet rs = null;
2003:                do {
2004:                    if (rs != null)
2005:                        rs.close();
2006:                    rs = cs.getResultSet();
2007:                    while (rs.next())
2008:                        ;
2009:
2010:                    if (rs.next()) {
2011:                        System.out
2012:                                .println("FAIL. Final call to ResultSet should return false.");
2013:                    }
2014:                } while (getMoreResults(cs));
2015:
2016:                if (!checkLocks()) {
2017:                    return;
2018:                }
2019:
2020:                System.out.println("PASS. ");
2021:
2022:                if (rs != null)
2023:                    rs.close();
2024:                cs.close();
2025:            }
2026:
2027:            /**
2028:             * Used to insure that there is no auto-commit in the event that there is
2029:             * more then one ResultSet open.
2030:             * 
2031:             * @param conn The Connection
2032:             * @throws SQLException
2033:             */
2034:            private static void multipleRSNoCommit(Connection conn)
2035:                    throws SQLException {
2036:                System.out.print("MultipleRSNoCommit: ");
2037:                CallableStatement cs = conn
2038:                        .prepareCall("call multiResult(?, ?)");
2039:                cs.setInt(1, 1);
2040:                cs.setInt(2, 2);
2041:                cs.execute();
2042:                ResultSet rs = null;
2043:                do {
2044:                    rs = cs.getResultSet();
2045:                    while (rs.next())
2046:                        ;
2047:
2048:                    if (rs.next()) {
2049:                        System.out
2050:                                .println("FAIL. Final call to ResultSet should return false.");
2051:                    }
2052:                } while (getMoreResults(cs));
2053:
2054:                if (checkLocks()) {
2055:                    System.out
2056:                            .println("FAIL. Connection incorrectly auto-committed.");
2057:                }
2058:
2059:                System.out.println("PASS. ");
2060:
2061:                if (rs != null)
2062:                    rs.close();
2063:                cs.close();
2064:            }
2065:
2066:            // DERBY-821: Test that the result set is not implicitly closed on
2067:            // the server when EXCSQLSTT is used to open the result set.
2068:            private static void testImplicitClose(Connection conn)
2069:                    throws SQLException {
2070:                System.out.print("testImplicitClose(): ");
2071:                final String proc = "org.apache.derbyTesting.functionTests.util.ProcedureTest."
2072:                        + "selectRows";
2073:                boolean savedAutoCommit = conn.getAutoCommit();
2074:                conn.setAutoCommit(false);
2075:                Statement stmt = conn.createStatement();
2076:                stmt.executeUpdate("create table derby821 (id int)");
2077:                stmt.executeUpdate("insert into derby821 (id) values (1), (2)");
2078:                stmt
2079:                        .execute("create procedure jira821 (name varchar(50)) "
2080:                                + "parameter style java language java external name "
2081:                                + "'" + proc
2082:                                + "' dynamic result sets 1 reads sql data");
2083:
2084:                // Call setupStatementReuse which will make the server to reuse an existing statement.
2085:                setupStatementReuse(conn);
2086:                CallableStatement cs = conn.prepareCall("call jira821 (?)");
2087:                cs.setString(1, "derby821");
2088:                cs.execute();
2089:                ResultSet rs = cs.getResultSet();
2090:                rs.next();
2091:                boolean passed = false;
2092:                try {
2093:                    // We expect the result set to be open, so dropping the
2094:                    // table should fail.
2095:                    stmt.executeUpdate("drop table derby821");
2096:                    rs.next();//to fix DERBY-1320. Else the GC for ibm15 will clean up the ResultSet Object
2097:                } catch (SQLException sqle) {
2098:                    if (sqle.getSQLState().equals("X0X95")) {
2099:                        System.out.println("PASSED");
2100:                        passed = true;
2101:                    } else {
2102:                        System.out.println("FAILED");
2103:                        throw sqle;
2104:                    }
2105:                }
2106:                if (!passed) {
2107:                    // Table was successfully dropped, hence the result set
2108:                    // must have been implicitly closed.
2109:                    System.out.println("FAILED (no exception thrown)");
2110:                }
2111:                conn.rollback();
2112:                conn.setAutoCommit(savedAutoCommit);
2113:            }
2114:
2115:            /**
2116:             * This method is used to set up an environment which can be used to test 
2117:             * DERBY-1002. It creates statements and closes them to provoke the client
2118:             * driver to re-use sections which in turn will make the network server to
2119:             * re-use statements and result sets. It does not test anything by itself.
2120:             * It just sets up an environment where the statements used in this test 
2121:             * will be re-used in later tests. It is called from methods 
2122:             * 'jira_491_492' and 'testImplicitClose'. When the re-use was not happening 
2123:             * correctly, 'jira_491_492' and 'testImplicitClose' were giving following 
2124:             * errors:
2125:             * 
2126:             * 1. In the test for jira491, client expects a QRYDTA for the CNTQRY request. 
2127:             * Instead, it recieves a QRYNOPRM reply because server closes the query 
2128:             * wrongly.
2129:             * 2. In testImplicitClose, the query is not supposed to be closed in case
2130:             * of EXCSQLSTT commands. If re-use happens wrongly, server closes the query 
2131:             * for EXCSQLSTT commands too.
2132:             *   
2133:             * @param conn Connection
2134:             */
2135:            private static void setupStatementReuse(Connection conn)
2136:                    throws SQLException {
2137:
2138:                Statement stmt = conn.createStatement();
2139:                try {
2140:                    stmt.execute("drop table test_table_jira_1002");
2141:                } catch (SQLException se) {
2142:                }
2143:
2144:                try {
2145:                    stmt.execute("drop procedure test_proc_jira_1002");
2146:                } catch (SQLException se) {
2147:                }
2148:
2149:                stmt.execute("create table test_table_jira_1002(id int)");
2150:                stmt
2151:                        .execute("insert into test_table_jira_1002 values(1) , (2)");
2152:
2153:                //create a procedure which returns a result set
2154:                stmt
2155:                        .execute("create procedure test_proc_jira_1002(name varchar(50)) "
2156:                                + "language java parameter style java external name "
2157:                                + "'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'"
2158:                                + "dynamic result sets 1");
2159:
2160:                // Create a select statement to make currentDrdaRs.qryclsimp=CodePoint.QRYCLSIMP_YES
2161:                Statement st_opnqry = conn.createStatement();
2162:                ResultSet rs_opnqry = st_opnqry
2163:                        .executeQuery("SELECT * FROM TEST_TABLE_JIRA_1002");
2164:                rs_opnqry.next();
2165:                // Close st_opnqry so that cSt1 will reuse same DRDAStatement
2166:                st_opnqry.close();
2167:
2168:                // Use up the next statement's result set to make currentDrdaRs.hasdata=false
2169:                CallableStatement cSt1 = conn
2170:                        .prepareCall("call test_proc_jira_1002(?)");
2171:                cSt1.setString(1, "test_table_jira_1002");
2172:                cSt1.execute();
2173:                ResultSet rs1 = cSt1.getResultSet();
2174:                rs1.next();
2175:                // Close cSt1 so that a statement created after a call to this method 
2176:                // will cause the server to use same DRDAStatement.
2177:                cSt1.close();
2178:
2179:            }
2180:
2181:            /**
2182:             * Checks to see if there is a lock on a table by attempting to modify the
2183:             * same table. If the first connection was serializable then it will 
2184:             * continue to hold a lock and the second Connection will time out.
2185:             * 
2186:             * @return false if the a lock could not be established, true if a lock
2187:             * can be established.
2188:             * @throws SQLException
2189:             */
2190:            private static boolean checkLocks() throws SQLException {
2191:                Connection conn = null;
2192:                try {
2193:                    conn = ij.startJBMS();
2194:                } catch (Exception e) {
2195:                    System.out
2196:                            .println("FAIL. Unable to establish connection in checkLocks");
2197:                    return false;
2198:                }
2199:                Statement stmt = conn.createStatement();
2200:                try {
2201:                    stmt.executeUpdate("update AutoCommitTable "
2202:                            + "set num = 3 where num = 2");
2203:                    stmt.executeUpdate("update AutoCommitTable "
2204:                            + "set num = 2 where num = 3");
2205:                } catch (SQLException e) {
2206:                    if (e.getSQLState().equals(SQLState.LOCK_TIMEOUT)) {
2207:                        return false;
2208:                    } else {
2209:                        throw e;
2210:                    }
2211:                }
2212:                stmt.close();
2213:                conn.close();
2214:                return true;
2215:            }
2216:
2217:            /**
2218:             * Sets the holdability of a Connection using reflection so it is
2219:             * JDBC2.0 compatible.
2220:             * 
2221:             * @param conn The Connection
2222:             * @param hold The new holdability.
2223:             * @throws SQLException
2224:             */
2225:            public static void setHoldability(Connection conn, int hold)
2226:                    throws SQLException {
2227:                try {
2228:                    Object[] holdArray = { new Integer(hold) };
2229:                    Method sh = conn.getClass().getMethod("setHoldability",
2230:                            CONN_PARAM);
2231:                    sh.invoke(conn, holdArray);
2232:                } catch (Exception e) {
2233:                    System.out.println("shouldn't get that error "
2234:                            + e.getMessage());
2235:                }//for jdks prior to jdk14
2236:            }
2237:
2238:            /**
2239:             * Uses reflection to call CallableStatement.getMoreResults(KEEP_CURRENT_RESULT)
2240:             * for JDBC2.0 compatibilty
2241:             * @param cs The Callable statement
2242:             * @return boolean value indicating if there are more results 
2243:             * @throws SQLException
2244:             */
2245:            public static boolean getMoreResults(CallableStatement cs)
2246:                    throws SQLException {
2247:                try {
2248:                    Object[] holdArray = { new Integer(
2249:                            JDBC30Translation.KEEP_CURRENT_RESULT) };
2250:                    Method sh = cs.getClass().getMethod("getMoreResults",
2251:                            CONN_PARAM);
2252:                    Boolean temp = (Boolean) sh.invoke(cs, holdArray);
2253:                    return temp.booleanValue();
2254:                } catch (Exception e) {
2255:                    return cs.getMoreResults();
2256:                }//for jdks prior to jdk14 
2257:            }
2258:
2259:            /** 
2260:             * clean up any objects not cleaned up by previous efforts
2261:             */
2262:            private static void cleanUp(Connection conn) throws SQLException {
2263:                String[] testObjects = { "table t1", "procedure procdup",
2264:                        "schema s1 restrict", "schema s2 restrict",
2265:                        "procedure drs", "procedure drs2",
2266:                        "procedure litt.ty_smallint",
2267:                        "procedure litt.ty_integer",
2268:                        "procedure litt.ty_bigint", "procedure litt.ty_real",
2269:                        "procedure litt.ty_double",
2270:                        "procedure litt.ty_decimal", "procedure litt.ty_char",
2271:                        "procedure litt.ty_varchar",
2272:                        "table SQLC.SQLCONTROL_DDL", "table SQLCONTROL_DDL",
2273:                        "table SQLC.SQLCONTROL_DML", };
2274:                Statement stmt = conn.createStatement();
2275:                TestUtil.cleanUpTest(stmt, testObjects);
2276:            }
2277:
2278:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.