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: }
|