You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by km...@apache.org on 2007/04/04 01:32:54 UTC
svn commit: r525332 [2/3] - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting:
functionTests/master/ functionTests/master/DerbyNet/
functionTests/master/DerbyNet/jdk16/ functionTests/master/DerbyNetClient/
functionTests/master/DerbyNetClient...
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java?view=auto&rev=525332
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java Tue Apr 3 16:32:53 2007
@@ -0,0 +1,2240 @@
+/*
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.math.BigDecimal;
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+import java.sql.Statement;
+import java.sql.Types;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.functionTests.util.ProcedureTest;
+import org.apache.derbyTesting.functionTests.util.TestUtil;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test the syntax for creating procedures.
+ */
+public class LangProcedureTest extends BaseJDBCTestCase {
+
+ public LangProcedureTest(String name) {
+ super(name);
+ }
+
+ protected void tearDown() throws Exception {
+ super.tearDown();
+ }
+
+ /**
+ * Default suite for running this test (embedded and client).
+ */
+ public static Test suite() {
+ if (JDBC.vmSupportsJSR169())
+ return new TestSuite("Empty LangProcedureTest. JSR169 does not support jdbc:default:connection");
+ else
+ return TestConfiguration.defaultSuite(LangProcedureTest.class);
+ }
+
+ /**
+ * Verifies the exception that gets raised for bad syntax or unsupported
+ * features.
+ *
+ * @throws SQLException
+ */
+ public void testCreateRoutineErrors() throws SQLException {
+ Statement s = createStatement();
+
+ // The expected format is path.method (no '.').
+ assertStatementError("42Y04", s,
+ "create procedure asdf() language java "
+ + "external name 'asdfasdf' parameter style java");
+
+ // The expected format is path.method (there's a trailing '.').
+ assertStatementError("42Y04", s,
+ "create procedure asdf() language java "
+ + "external name 'asdfasdf.' parameter style java");
+
+ // The procedure name exceeds the max length.
+ assertStatementError(
+ "42622",
+ s,
+ "create procedure a234567890123456789012345678901234567890123"
+ + "456789012345678901234567890123456789012345678901234567890123"
+ + "45678901234567890123456789() language java external name "
+ + "'asdf.asdf' parameter style java");
+
+ // "LANGUAGE C" is an incorrect language.
+ assertStatementError(
+ "42X01",
+ s,
+ "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");
+
+ // Not allowed to create a routine in the SYS schema.
+ assertStatementError(
+ "42X62",
+ s,
+ "create procedure sys.proc1() language java "
+ + "external name 'java.lang.System.gc' parameter style java");
+
+ // The 'LANGUAGE' clause has multiple or conflicting keywords ('java').
+ assertStatementError(
+ "42613",
+ s,
+ "create procedure noclass() language java "
+ + "external name 'asdf.asdf' parameter style java language java");
+ assertStatementError("42613", s,
+ "create procedure noclass() parameter style java language java "
+ + "external name 'asdf.asdf' parameter style java");
+ assertStatementError(
+ "42613",
+ s,
+ "create procedure noclass() external name 'asdf.xxxx' "
+ + "language java external name 'asdf.asdf' parameter style java");
+ assertStatementError(
+ "42X01",
+ s,
+ "create procedure noclass() parameter style java language java "
+ + "external name 'asdf.asdf' parameter style derby_rs_collection");
+
+ // The create statement is missing an element.
+ assertStatementError("42X01", s, "create procedure missing01()");
+ assertStatementError("42X01", s,
+ "create procedure missing02() language java");
+ assertStatementError("42X01", s,
+ "create procedure missing03() language java parameter style java");
+ assertStatementError("42X01", s,
+ "create procedure missing04() language java "
+ + "external name 'foo.bar'");
+ assertStatementError("42X01", s,
+ "create procedure missing05() parameter style java");
+ assertStatementError("42X01", s,
+ "create procedure missing06() parameter style java "
+ + "external name 'foo.bar'");
+ assertStatementError("42X01", s,
+ "create procedure missing07() external name 'goo.bar'");
+ assertStatementError("42X01", s,
+ "create procedure missing08() dynamic result sets 1");
+ assertStatementError("0A000", s,
+ "create procedure missing09() specific name fred");
+
+ // RETURNS NULL ON NULL INPUT isn't allowed in procedures.
+ assertStatementError(
+ "42X01",
+ s,
+ "create procedure nullinput2() returns null on null input "
+ + "language java parameter style java external name 'foo.bar'");
+
+ // BLOB, CLOB, and long parameters aren't supported.
+ assertStatementError("42962", s,
+ "create procedure NO_BLOB(IN P1 BLOB(3k)) language java "
+ + "parameter style java external name 'no.blob'");
+ assertStatementError("42962", s,
+ "create procedure NO_CLOB(IN P1 CLOB(3k)) language java "
+ + "parameter style java external name 'no.clob'");
+ assertStatementError("42962", s,
+ "create procedure NO_LVC(IN P1 LONG VARCHAR) language java "
+ + "parameter style java external name 'no.lvc'");
+
+ // Duplicate parameter names.
+ assertStatementError("42734", s, "create procedure DUP_P1"
+ + "(IN FRED INT, OUT RON CHAR(10), IN FRED INT) "
+ + "language java parameter style java external name 'no.dup1'");
+ assertStatementError("42734", s, "create procedure D2.DUP_P2"
+ + "(IN \"FreD\" INT, OUT RON CHAR(10), IN \"FreD\" INT) "
+ + "language java parameter style java external name 'no.dup2'");
+ assertStatementError("42734", s, "create procedure D3.DUP_P3"
+ + "(IN \"FRED\" INT, OUT RON CHAR(10), IN fred INT) "
+ + "language java parameter style java external name 'no.dup3'");
+
+ // This one should succeed.
+ s
+ .execute("create procedure DUP_POK"
+ + "(IN \"FreD\" INT, OUT RON CHAR(10), IN fred INT) "
+ + "language java parameter style java external name 'no.dupok'");
+ s.execute("drop procedure DUP_POK");
+
+ // procedure not found with explicit schema name
+ assertStatementError("42Y03", s, "CALL APP.NSP(?, ?)");
+
+ // Long ago this caused a null pointer exception.
+ assertStatementError("42X15", s,
+ "call syscs_util.syscs_set_database_property(\"foo\", \"bar\")");
+
+ s.close();
+ }
+
+ /**
+ * Verifies the fix for DERBY-258: incorrect method resolution if an
+ * explicit method signature has Java type that does not match the correct
+ * SQL to Java mapping as defined by JDBC.
+ *
+ * @throws SQLException
+ */
+ public void testMethodSignatureDerby258() throws SQLException {
+ Statement s = createStatement();
+
+ // INT doesn't match String
+ s.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)'");
+ assertStatementError("22005", s,
+ "CALL APP.SIGNATURE_BUG_DERBY_258_A(4)");
+ s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_A");
+
+ // Signature with too many arguments.
+ s.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)'");
+ assertStatementError("46J02", s,
+ "VALUES APP.SIGNATURE_BUG_DERBY_258_B(4)");
+ s.execute("DROP FUNCTION SIGNATURE_BUG_DERBY_258_B");
+
+ // Signature with too few arguments.
+ s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_C(IN A INT) "
+ + "LANGUAGE JAVA PARAMETER STYLE JAVA "
+ + "EXTERNAL NAME 'java.lang.System.gc()'");
+ assertStatementError("46J02", s,
+ "CALL APP.SIGNATURE_BUG_DERBY_258_C(4)");
+ s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_C");
+
+ // Java method signature has only a leading parenthesis.
+ s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_F(IN A INT) "
+ + "LANGUAGE JAVA PARAMETER STYLE JAVA "
+ + "EXTERNAL NAME 'java.lang.System.gc('");
+ assertStatementError("46J01", s,
+ "CALL APP.SIGNATURE_BUG_DERBY_258_F(4)");
+ s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_F");
+
+ // Java method signature of (,,)
+ s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_G(IN A INT) "
+ + "LANGUAGE JAVA PARAMETER STYLE JAVA "
+ + "EXTERNAL NAME 'java.lang.System.gc(,,)'");
+ assertStatementError("46J01", s,
+ "CALL APP.SIGNATURE_BUG_DERBY_258_G(4)");
+ s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_G");
+
+ // Java method signature of (, ,)
+ s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_H(IN A INT) "
+ + "LANGUAGE JAVA PARAMETER STYLE JAVA "
+ + "EXTERNAL NAME 'java.lang.System.gc(, ,)'");
+ assertStatementError("46J01", s,
+ "CALL APP.SIGNATURE_BUG_DERBY_258_H(4)");
+ s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_H");
+
+ // Java method signature of (int,)
+ s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_I(IN A INT) "
+ + "LANGUAGE JAVA PARAMETER STYLE JAVA "
+ + "EXTERNAL NAME 'java.lang.System.gc(int ,)'");
+ assertStatementError("46J01", s,
+ "CALL APP.SIGNATURE_BUG_DERBY_258_I(4)");
+ s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_I");
+
+ s.close();
+ }
+
+ /**
+ * Tests the exception that gets thrown at runtime when the external method
+ * for a SQL procedure doesn't exist -- there's no check for existence at
+ * CREATE time, the check occurs at runtime.
+ *
+ * @throws SQLException
+ */
+ public void testDelayedClassChecking() throws SQLException {
+ Connection conn = getConnection();
+ Statement s = createStatement();
+
+ s.execute("create procedure noclass() language java "
+ + "external name 'asdf.asdf' parameter style java");
+ s
+ .execute("create procedure nomethod() language java "
+ + "external name 'java.lang.Integer.asdf' parameter style java");
+ s
+ .execute("create procedure notstatic() language java "
+ + "external name 'java.lang.Integer.equals' parameter style java");
+ s
+ .execute("create procedure notvoid() language java "
+ + "external name 'java.lang.Runtime.getRuntime' parameter style java");
+ assertCallError("42X51", conn, "call noclass()");
+ assertCallError("42X50", conn, "call nomethod()");
+ assertCallError("42X50", conn, "call notstatic()");
+ assertCallError("42X50", conn, "call notvoid()");
+
+ // Comment from old test: CHECK SYSALIAS
+ s.execute("drop procedure noclass");
+ s.execute("drop procedure nomethod");
+ s.execute("drop procedure notstatic");
+ s.execute("drop procedure notvoid");
+
+ s.close();
+ }
+
+ public void testDuplicates() throws SQLException {
+ Connection conn = getConnection();
+
+ Statement s = conn.createStatement();
+
+ s.execute("create schema S1");
+ s.execute("create schema S2");
+
+ s
+ .execute("create procedure PROCDUP() language java external name 'okAPP.ok0' parameter style java");
+ s
+ .execute("create procedure s1.PROCDUP() language java external name 'oks1.ok0' parameter style java");
+ s
+ .execute("create procedure s2.PROCDUP() language java external name 'oks2.ok0' parameter style java");
+
+ assertStatementError(
+ "X0Y68",
+ s,
+ "create procedure PROCDUP() language java external name 'failAPP.fail0' parameter style java");
+ assertStatementError(
+ "X0Y68",
+ s,
+ "create procedure s1.PROCDUP() language java external name 'fails1.fail0' parameter style java");
+ assertStatementError(
+ "X0Y68",
+ s,
+ "create procedure s2.PROCDUP() language java external name 'fails2.fail0' parameter style java");
+ String[] sysAliasDefinition = {
+ "APP.PROCDUP AS okAPP.ok0() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA",
+ "S1.PROCDUP AS oks1.ok0() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA",
+ "S2.PROCDUP AS oks2.ok0() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
+ String[] DBMetaDefinition = {
+ "APP.PROCDUP AS okAPP.ok0 type procedureNoResult",
+ "S1.PROCDUP AS oks1.ok0 type procedureNoResult",
+ "S2.PROCDUP AS oks2.ok0 type procedureNoResult" };
+ checkMatchingProcedures(conn, "PROCDUP", sysAliasDefinition,
+ DBMetaDefinition, null);
+
+ assertStatementError(
+ "0A000",
+ s,
+ "create procedure S1.NOTYET() SPECIFIC fred language java external name 'failAPP.fail0' parameter style java");
+
+ s.execute("drop procedure s1.PROCDUP");
+ s.execute("drop procedure s2.PROCDUP");
+
+ s.execute("drop schema S1 RESTRICT");
+ s.execute("drop schema S2 RESTRICT");
+ s.close();
+
+ }
+
+ public void testAmbigiousMethods() throws SQLException {
+ Connection conn = getConnection();
+
+ Statement s = conn.createStatement();
+
+ // ambiguous resolution - with result sets
+ s
+ .execute("create procedure ambiguous01(p1 INTEGER, p2 CHAR(20)) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.ambiguous1'");
+ assertCallError("42X73", conn, "call AMBIGUOUS01(?, ?)");
+ s.execute("drop procedure AMBIGUOUS01");
+
+ // ambiguous in defined parameters
+ s
+ .execute("create procedure ambiguous02(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.ambiguous2'");
+ assertCallError("42X50", conn, "call AMBIGUOUS02(?, ?)");
+ s.execute("drop procedure AMBIGUOUS02");
+
+ // verify we can find it with a Java signature
+ s
+ .execute("create procedure ambiguous03(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.ambiguous2(int,java.lang.Integer)'");
+ s.execute("{call ambiguous03(1,NULL)}");
+ s.execute("drop procedure AMBIGUOUS03");
+ s
+ .execute("create procedure ambiguous04(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.ambiguous2(java.lang.Integer,int)'");
+ s.execute("{call ambiguous04(NULL, 1)}");
+ s.execute("drop procedure AMBIGUOUS04");
+ s.close();
+ }
+
+ private static void checkMatchingProcedures(Connection conn,
+ String procedureName, String[] sysAliasDefinition,
+ String[] DBMetaDefinition, String[] columnDefinition)
+ throws SQLException {
+ // Until cs defaults to hold cursor we need to turn autocommit off
+ // while we do this because one metadata call will close the other's
+ // cursor
+ boolean saveAutoCommit = conn.getAutoCommit();
+ conn.setAutoCommit(false);
+
+ PreparedStatement ps = conn
+ .prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE "
+ + " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");
+
+ ps.setString(1, procedureName);
+
+ ResultSet rs = ps.executeQuery();
+ int i = 0;
+ while (rs.next()) {
+ assertEquals(sysAliasDefinition[i++], rs.getString(1) + "."
+ + rs.getString(2) + " AS " + rs.getString(3));
+ }
+ rs.close();
+
+ DatabaseMetaData dmd = conn.getMetaData();
+
+ rs = dmd.getProcedures(null, null, procedureName);
+ // with jcc 2.1 for now this will fail on the second round,
+ // because the resultset gets closed when we do getProcedureColumns.
+ // thus, catch that gracefully...
+ i = 0;
+ while (rs.next()) {
+ String schema = rs.getString(2);
+ String name = rs.getString(3);
+ assertEquals(schema + "." + name + " AS " + rs.getString(7)
+ + " type " + TYPE(rs.getShort(8)), DBMetaDefinition[i++]);
+ // get the column information.
+ ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
+ int j = 0;
+ while (rsc.next()) {
+ assertEquals(PARAMTYPE(rsc.getShort(5)) + " "
+ + rsc.getString(4) + " " + rsc.getString(7),
+ columnDefinition[j++]);
+ }
+ rsc.close();
+ }
+ rs.close();
+ // restore previous autocommit mode
+ conn.setAutoCommit(saveAutoCommit);
+ }
+
+ public void testZeroArgProcedures() throws SQLException {
+ Connection conn = getConnection();
+
+ Statement s = conn.createStatement();
+ s
+ .execute("create procedure za() language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.zeroArg' parameter style java");
+
+ s.execute("call za()");
+ assertUpdateCountForProcedureWithNoResults(s);
+
+ PreparedStatement ps = conn.prepareStatement("call za()");
+ ps.execute();
+ assertUpdateCountForProcedureWithNoResults(ps);
+ ps.close();
+
+ ps = conn.prepareStatement("{call za()}");
+ ps.execute();
+ assertUpdateCountForProcedureWithNoResults(ps);
+ ps.close();
+
+ try {
+ ps = conn.prepareStatement("call za(?)");
+ fail("FAIL - prepareStatement call za(?)");
+ } catch (SQLException sqle) {
+ assertSQLState("42Y03", sqle);
+ }
+
+ CallableStatement cs = conn.prepareCall("call za()");
+ cs.execute();
+ assertUpdateCountForProcedureWithNoResults(cs);
+ cs.close();
+
+ cs = conn.prepareCall("{call za()}");
+ cs.execute();
+ assertUpdateCountForProcedureWithNoResults(cs);
+ cs.close();
+ String[] sysAliasDefinition = { "APP.ZA AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.zeroArg() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
+ String[] dbMetadataDefinition = { "APP.ZA AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.zeroArg type procedureNoResult" };
+ checkMatchingProcedures(conn, "ZA", sysAliasDefinition,
+ dbMetadataDefinition, null);
+ s.execute("drop procedure za");
+ checkMatchingProcedures(conn, "ZA", null, null, null);
+
+ s.close();
+
+ }
+
+ public void testSqlProcedures() throws SQLException {
+ Connection conn = getConnection();
+ Statement s = conn.createStatement();
+
+ s.execute("create table t1(i int not null primary key, b char(15))");
+ s
+ .execute("create procedure ir(p1 int) MODIFIES SQL DATA dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow' parameter style java");
+ s
+ .execute("create procedure ir2(p1 int, p2 char(10)) language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow' MODIFIES SQL DATA parameter style java");
+
+ String[] sysaliasDefinition = { "APP.IR AS org.apache.derbyTesting.functionTeststs.tests.lang.LangProcedureTest.insertRow(IN P1 INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
+ String[] dbMetadataDefinition = { "APP.IR AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow type procedureNoResult" };
+ String[] columnDefinition = { "procedureColumnIn P1 INTEGER" };
+ checkMatchingProcedures(conn, "IR1", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ sysaliasDefinition = new String[] { "APP.IR2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow(IN P1 INTEGER,IN P2 CHAR(10)) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
+ dbMetadataDefinition = new String[] { "APP.IR2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow type procedureNoResult" };
+ columnDefinition = new String[] { "procedureColumnIn P1 INTEGER",
+ "procedureColumnIn P2 CHAR" };
+ checkMatchingProcedures(conn, "IR2", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+ assertCallError("42Y03", conn, "CALL IR()");
+
+ CallableStatement ir1 = conn.prepareCall("CALL IR(?)");
+
+ ir1.setInt(1, 1);
+ ir1.execute();
+
+ ir1.setInt(1, 2);
+ ir1.execute();
+ try {
+ ir1.execute();
+ fail("FAIL - duplicate key insertion through ir");
+ } catch (SQLException sqle) {
+ assertSQLState("23505", sqle);
+ }
+
+ ir1.setString(1, "3");
+ ir1.execute();
+
+ ir1.close();
+
+ ir1 = conn.prepareCall("CALL APP.IR(?)");
+ ir1.setInt(1, 7);
+ ir1.execute();
+
+ CallableStatement ir2 = conn.prepareCall("CALL IR2(?, ?)");
+
+ ir2.setInt(1, 4);
+ ir2.setInt(2, 4);
+ ir2.execute();
+
+ ir2.setInt(1, 5);
+ ir2.setString(2, "ir2");
+ ir2.execute();
+
+ ir2.setInt(1, 6);
+ ir2.setString(2, "'012345678990'");
+ ir2.execute();
+
+ ir1.close();
+ ir2.close();
+
+ if (!conn.getAutoCommit())
+ conn.commit();
+
+ String[][] t1Results = { { "1", "int" }, { "2", "int" },
+ { "3", "int" }, { "7", "int" }, { "4", "4" }, { "5", "ir2" },
+ { "6", "'012345678" } };
+ ResultSet rs = s.executeQuery("select * from t1");
+ JDBC.assertFullResultSet(rs, t1Results);
+
+ if (!conn.getAutoCommit())
+ conn.commit();
+
+ assertCallError("38000", conn, "CALL IR2(2, 'no way')");
+ assertCallError("07000", conn, "CALL IR2(?, 'no way')");
+ assertCallError("07000", conn, "CALL IR2(2, ?)");
+
+ s.execute("drop procedure IR");
+ s.execute("drop procedure IR2");
+
+ s.close();
+ }
+
+ /**
+ * 1. basic testing 2. correct auto commit logic 3. correct holdability
+ * (JDBC 3)
+ */
+ public void testDynamicResultSets() throws SQLException {
+ Connection conn = getConnection();
+ Connection conn2 = openDefaultConnection();
+
+ Statement s = conn.createStatement();
+
+ assertStatementError(
+ "42X01",
+ s,
+ "create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets -1 language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.selectRows'");
+
+ s
+ .execute("create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.selectRows'");
+
+ String[] sysaliasDefinition = { "APP.DRS AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.selectRows(IN P1 INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA DYNAMIC RESULT SETS 1" };
+ String[] dbMetadataDefinition = { "APP.DRS AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.selectRows type procedureNoResult" };
+ String[] columnDefinition = { "procedureColumnIn P1 INTEGER" };
+
+ checkMatchingProcedures(conn, "DRS", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+ assertCallError("42Y03", conn, "CALL DRS()");
+ assertCallError("42Y03", conn, "CALL DRS(?,?)");
+
+ CallableStatement drs1 = conn.prepareCall("CALL DRS(?)");
+
+ drs1.setInt(1, 3);
+ drs1.execute();
+ ResultSet rs = drs1.getResultSet();
+ String[][] drsResult = { { "3", "int" } };
+ JDBC.assertFullResultSet(rs, drsResult);
+ drs1.close();
+
+ s
+ .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.tests.lang.LangProcedureTest.selectRows'");
+ sysaliasDefinition = new String[] { "APP.DRS2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.selectRows(IN P1 INTEGER,IN P2 INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA DYNAMIC RESULT SETS 2" };
+ dbMetadataDefinition = new String[] { "APP.DRS2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.selectRows type procedureNoResult" };
+ columnDefinition = new String[] { "procedureColumnIn P1 INTEGER",
+ "procedureColumnIn P2 INTEGER" };
+ checkMatchingProcedures(conn, "DRS2", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ CallableStatement drs2;
+ drs2 = conn.prepareCall("CALL DRS2(?, ?)");
+ drs2.setInt(1, 2);
+ drs2.setInt(2, 6);
+ drs2.execute();
+ rs = drs2.getResultSet();
+ String[][] drs2Results = { { "2", "int" } };
+ JDBC.assertFullResultSet(rs, drs2Results);
+ assertTrue(drs2.getMoreResults());
+ drs2Results = new String[][] { { "6", "'012345678" }, { "7", "int" } };
+ rs = drs2.getResultSet();
+ JDBC.assertFullResultSet(rs, drs2Results);
+
+ // execute it returning one closed result set
+ drs2.setInt(1, 2);
+ drs2.setInt(2, 99); // will close the second result set
+ assertTrue(drs2.execute());
+ rs = drs2.getResultSet();
+ drs2Results = new String[][] { { "2", "int" } };
+ JDBC.assertFullResultSet(rs, drs2Results);
+ assertFalse(drs2.getMoreResults());
+
+ // execute it returning no result sets
+ drs2.setInt(1, 2);
+ drs2.setInt(2, 199); // return no results at all
+ assertFalse(drs2.execute());
+ assertFalse(drs2.getMoreResults());
+
+ // execute it returning two result sets but with the order swapped in
+ // the parameters
+ // doesnot affect display order.
+ drs2.setInt(1, 2);
+ drs2.setInt(2, 299); // swap results
+ assertTrue(drs2.execute());
+ drs2Results = new String[][] { { "2", "int" } };
+
+ rs = drs2.getResultSet();
+ JDBC.assertFullResultSet(rs, drs2Results);
+ assertTrue(drs2.getMoreResults());
+ rs = drs2.getResultSet();
+ JDBC.assertEmpty(rs);
+
+ drs2.setInt(1, 2);
+ drs2.setInt(2, 2);
+ drs2.execute();
+ ResultSet lastResultSet = null;
+ int pass = 1;
+ do {
+
+ if (lastResultSet != null) {
+ try {
+ lastResultSet.next();
+ fail("FAILED - result set should be closed");
+ } catch (SQLException sqle) {
+ assertSQLState("XCL16", sqle);
+ }
+ }
+
+ lastResultSet = drs2.getResultSet();
+
+ if ((pass == 1) || (pass == 2))
+ assertNotNull("expected resultset pass " + pass, lastResultSet);
+ else if (pass == 3)
+ assertNull(lastResultSet);
+ pass++;
+
+ } while (drs2.getMoreResults() || lastResultSet != null);
+
+ checkCommitWithMultipleResultSets(drs2, conn2, "autocommit");
+ checkCommitWithMultipleResultSets(drs2, conn2, "noautocommit");
+ checkCommitWithMultipleResultSets(drs2, conn2, "statement");
+
+ drs2.close();
+
+ // use escape syntax
+ drs2 = conn.prepareCall("{call DRS2(?, ?)}");
+ drs2.setInt(1, 2);
+ drs2.setInt(2, 6);
+ drs2.execute();
+ rs = drs2.getResultSet();
+ String[][] expectedRows = { { "2", "int" } };
+ JDBC.assertFullResultSet(rs, expectedRows);
+ drs2.close();
+
+ // check that a procedure with dynamic result sets can not resolve to a
+ // method with no ResultSet argument.
+ s
+ .execute("create procedure irdrs(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.missingDynamicParameter' parameter style JAVA");
+ assertCallError("42X50", conn, "CALL IRDRS(?)");
+ s.execute("drop procedure irdrs");
+
+ // check that a procedure with dynamic result sets can not resolve to a
+ // method with an argument that is a ResultSet impl,
+ s
+ .execute("create procedure rsi(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.badDynamicParameter' parameter style JAVA");
+ assertCallError("42X50", conn, "CALL rsi(?)");
+ s.execute("drop procedure rsi");
+
+ // simple check for a no-arg method that has dynamic result sets but
+ // does not return any
+ // System.out.println("no dynamic result sets");
+ s
+ .execute("create procedure zadrs() dynamic result sets 4 language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.zeroArgDynamicResult' parameter style JAVA");
+ CallableStatement zadrs = conn.prepareCall("CALL ZADRS()");
+ zadrs.execute();
+ // DERBY-211
+ if (usingEmbedded())
+ assertEquals(0, zadrs.getUpdateCount());
+ else
+ assertEquals(-1, zadrs.getUpdateCount());
+ zadrs.close();
+ s.execute("drop procedure ZADRS");
+
+ // return too many result sets
+
+ s
+ .execute("create procedure way.toomany(p1 int, p2 int) READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.selectRows' parameter style JAVA");
+ CallableStatement toomany = conn.prepareCall("CALL way.toomany(?, ?)");
+ toomany.setInt(1, 2);
+ toomany.setInt(2, 6);
+ toomany.execute();
+ SQLWarning warn = toomany.getWarnings();
+ // DERBY-159. Network Server does not get warning
+ if (usingEmbedded())
+ assertEquals("0100E", warn.getSQLState());
+ rs = toomany.getResultSet();
+ JDBC.assertFullResultSet(rs, new String[][] { { "2", "int" } });
+
+ toomany.setInt(1, 2);
+ toomany.setInt(2, 99); // will close the second result set.
+ toomany.execute();
+ rs = toomany.getResultSet();
+ JDBC.assertFullResultSet(rs, new String[][] { { "2", "int" } });
+ toomany.close();
+ s.execute("drop procedure way.toomany");
+
+ s.close();
+ conn2.close();
+ }
+
+
+ public void testResultSetsWithLobs() throws SQLException {
+ Connection conn = getConnection();
+ Statement s = null;
+
+ // Create objects.
+ try {
+ s = conn.createStatement();
+
+ // Clob.
+ s.execute("create table lobCheckOne (c clob(30))");
+ s.execute("insert into lobCheckOne values (cast " +
+ "('yayorsomething' as clob(30)))");
+ s.execute("insert into lobCheckOne values (cast " +
+ "('yayorsomething2' as clob(30)))");
+ s.execute("create procedure clobproc () parameter style java " +
+ "language java external name " +
+ "'org.apache.derbyTesting.functionTests.util.ProcedureTest.clobselect' " +
+ "dynamic result sets 3 reads sql data");
+ // Blob.
+ s.execute("create table lobCheckTwo (b blob(30))");
+ s.execute("insert into lobCheckTwo values (cast " + "(" +
+ TestUtil.stringToHexLiteral("101010001101") +
+ " as blob(30)))");
+ s.execute("insert into lobCheckTwo values (cast " +
+ "(" +
+ TestUtil.stringToHexLiteral("101010001101") +
+ " as blob(30)))");
+ s.execute("create procedure blobproc () parameter style java " +
+ "language java external name " +
+ "'org.apache.derbyTesting.functionTests.util.ProcedureTest.blobselect' " +
+ "dynamic result sets 1 reads sql data");
+
+ } catch (SQLException e) {
+ fail("FAIL: Couldn't create required objects:");
+
+ }
+
+ try {
+
+ // Clobs.
+
+
+ CallableStatement cs = conn.prepareCall("CALL clobproc()");
+ cs.execute();
+ ResultSet rs = cs.getResultSet();
+ JDBC.assertFullResultSet(rs, new String[][] {{"yayorsomething"},{"yayorsomething2"}});
+ cs.close();
+
+ // Blobs.
+
+
+ cs = conn.prepareCall("CALL blobproc()");
+ cs.execute();
+ rs = cs.getResultSet();
+ String [][] expectedRows = {{"003100300031003000310030003000300031003100300031"},
+ {"003100300031003000310030003000300031003100300031"}};
+
+
+ JDBC.assertFullResultSet(rs, expectedRows);
+ cs.close();
+
+ } catch (Exception e) {
+ System.out.println("FAIL: Encountered exception:");
+ e.printStackTrace();
+ }
+
+ try {
+ // Clean up.
+ s.execute("drop table lobCheckOne");
+ s.execute("drop table lobCheckTwo");
+ s.execute("drop procedure clobproc");
+ s.execute("drop procedure blobproc");
+ s.close();
+ } catch (Exception e) {
+ System.out.println("FAIL: Cleanup for lob result sets test:");
+ e.printStackTrace();
+ }
+
+ return;
+
+ }
+
+ private static void checkCommitWithMultipleResultSets(
+ CallableStatement drs1, Connection conn2, String action)
+ throws SQLException {
+ Connection conn = drs1.getConnection();
+
+ // Do not run with client until DERBY-2510 is fixed
+ if (usingDerbyNetClient())
+ return;
+ try {
+ conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
+ } catch (Exception e) {
+ fail("shouldn't get that error " + e.getMessage());
+ }
+ // check to see that the commit of the transaction happens at the
+ // correct time.
+ // switch isolation levels to keep the locks around.
+ int oldIsolation = conn.getTransactionIsolation();
+
+ boolean oldAutoCommit = conn.getAutoCommit();
+
+ if (action.equals("noautocommit"))
+ conn.setAutoCommit(false);
+ else
+ conn.setAutoCommit(true);
+
+ conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
+ if (action.equals("noautocommit"))
+ assertFalse(conn.getAutoCommit());
+ else
+ assertTrue(conn.getAutoCommit());
+
+ PreparedStatement psLocks = conn2
+ .prepareStatement("select count(*) from SYSCS_DIAG.LOCK_TABLE AS LT");
+ ResultSet lrs = psLocks.executeQuery();
+ // lock count before execution
+ JDBC.assertFullResultSet(lrs, new String[][] { { "0" } });
+
+ drs1.execute();
+ // lock count after execution
+ lrs = psLocks.executeQuery();
+
+ JDBC.assertFullResultSet(lrs, new String[][] { { "1" } });
+
+ ResultSet rs = drs1.getResultSet();
+ rs.next();
+ // lock count after next on first rs
+ lrs = psLocks.executeQuery();
+
+ JDBC.assertFullResultSet(lrs, new String[][] { { "3" } });
+
+ boolean expectClosed = false;
+
+ // execute another statement to ensure that the result sets close.
+ if (action.equals("statement")) {
+ // executing statement to force auto commit on open CALL statement")
+
+ conn.createStatement().executeQuery("values 1").next();
+ expectClosed = true;
+ lrs = psLocks.executeQuery();
+
+ JDBC.assertFullResultSet(lrs, new String[][] { { "0" } });
+
+ try {
+ rs.next();
+ if (action.equals("autocommit"))
+ fail("FAIL - result set open in auto commit mode after another statement execution");
+ } catch (SQLException sqle) {
+ assertSQLState("XCL16", sqle);
+ }
+ }
+
+ boolean anyMore = drs1.getMoreResults();
+ assertTrue("is there a second result", anyMore);
+ lrs = psLocks.executeQuery();
+
+ if (action.equals("statement"))
+ JDBC.assertFullResultSet(lrs, new String[][] { { "0" } });
+ else
+ JDBC.assertFullResultSet(lrs, new String[][] { { "2" } });
+
+ if (anyMore) {
+
+ rs = drs1.getResultSet();
+ try {
+ rs.next();
+ if (expectClosed && !usingDerbyNetClient())
+ fail("FAIL - result set open in auto commit mode after another statement execution");
+ } catch (SQLException sqle) {
+ if (expectClosed)
+ assertSQLState("XCL16", sqle);
+
+ else
+ throw sqle;
+ }
+
+ // lock count after next on second rs
+ lrs = psLocks.executeQuery();
+
+ if (action.equals("statement"))
+ JDBC.assertFullResultSet(lrs, new String[][] { { "0" } });
+ else
+ JDBC.assertFullResultSet(lrs, new String[][] { { "7" } });
+
+ // should commit here since all results are closed
+ boolean more = drs1.getMoreResults();
+ assertFalse("more results (should be false) ", more);
+ lrs = psLocks.executeQuery();
+
+ if (action.equals("autocommit") || action.equals("statement"))
+ JDBC.assertFullResultSet(lrs, new String[][] { { "0" } });
+ else
+ JDBC.assertFullResultSet(lrs, new String[][] { { "7" } });
+
+ conn.setTransactionIsolation(oldIsolation);
+ conn.setAutoCommit(oldAutoCommit);
+ }
+
+ psLocks.close();
+ }
+
+ private void assertUpdateCountForProcedureWithNoResults(Statement s)
+ throws SQLException {
+ // DERBY-211 Network Server returns no result sets for a procedure call
+ // that returns no result
+ if (usingEmbedded())
+ assertEquals(0, s.getUpdateCount());
+ else
+ assertEquals(-1, s.getUpdateCount());
+ }
+
+ static String TYPE(short type) {
+ switch (type) {
+ case DatabaseMetaData.procedureResultUnknown:
+ return "procedureResultUnknown";
+ case DatabaseMetaData.procedureNoResult:
+ return "procedureNoResult";
+ case DatabaseMetaData.procedureReturnsResult:
+ return "procedureReturnsResult";
+ default:
+ return "??????";
+ }
+
+ }
+
+ static String PARAMTYPE(short type) {
+ switch (type) {
+ case DatabaseMetaData.procedureColumnUnknown:
+ return "procedureColumnUnknown";
+ case DatabaseMetaData.procedureColumnIn:
+ return "procedureColumnIn";
+ case DatabaseMetaData.procedureColumnInOut:
+ return "procedureColumnInOut";
+ case DatabaseMetaData.procedureColumnOut:
+ return "procedureColumnOut";
+ case DatabaseMetaData.procedureColumnReturn:
+ return "procedureColumnReturn";
+ case DatabaseMetaData.procedureColumnResult:
+ return "procedureColumnResult";
+ default:
+ return "???";
+ }
+ }
+
+ // PROCEDURES for ambiguous testing
+ public static void ambiguous1(int p1, String p2, ResultSet[] data1,
+ ResultSet[] data2) {
+ }
+
+ public static void ambiguous1(int p1, String p2, ResultSet[] data1) {
+ }
+
+ public static void ambiguous2(int p1, Integer p2) {
+ // System.out.println("ambiguous2(int,Integer) called");
+ };
+
+ public static void ambiguous2(Integer p1, int p2) {
+ // System.out.println("ambiguous2(Integer,int) called");
+
+ };
+
+ // PROCEDURES for zeroArgProcedures
+
+ public static void zeroArg() {
+ // System.out.println("zeroArg() called");
+ }
+
+ // PROCEDURES for sqlProcedures
+
+ public static void insertRow(int p1) throws SQLException {
+ insertRow(p1, "int");
+ }
+
+ public static void insertRow(int p1, String p2) throws SQLException {
+ Connection conn = DriverManager
+ .getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn
+ .prepareStatement("insert into t1 values (?, ?)");
+ ps.setInt(1, p1);
+ ps.setString(2, p2);
+ ps.executeUpdate();
+ ps.close();
+ conn.close();
+ }
+
+ // PROCEDURES for dynamic results
+
+ public static void selectRows(int p1, ResultSet[] data) throws SQLException {
+
+ Connection conn = DriverManager
+ .getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn
+ .prepareStatement("select * from t1 where i = ?");
+ ps.setInt(1, p1);
+ data[0] = ps.executeQuery();
+ conn.close();
+ }
+
+ public static void selectRows(int p1, int p2, ResultSet[] data1,
+ ResultSet[] data2) throws SQLException {
+
+ // selectRows - 2 arg - 2 rs
+
+ Connection conn = DriverManager
+ .getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn
+ .prepareStatement("select * from t1 where i = ?");
+ ps.setInt(1, p1);
+ data1[0] = ps.executeQuery();
+
+ ps = conn.prepareStatement("select * from t1 where i >= ?");
+ ps.setInt(1, p2);
+ data2[0] = ps.executeQuery();
+
+ if (p2 == 99)
+ data2[0].close();
+
+ // return no results
+ if (p2 == 199) {
+ data1[0].close();
+ data1[0] = null;
+ data2[0].close();
+ data2[0] = null;
+ }
+
+ // swap results
+ if (p2 == 299) {
+ ResultSet rs = data1[0];
+ data1[0] = data2[0];
+ data2[0] = rs;
+ }
+
+ conn.close();
+ }
+
+ // select all rows from a table
+ public static void selectRows(String table, ResultSet[] rs)
+ throws SQLException {
+ Connection conn = DriverManager
+ .getConnection("jdbc:default:connection");
+ Statement stmt = conn.createStatement();
+ rs[0] = stmt.executeQuery("SELECT * FROM " + table);
+ conn.close();
+ }
+
+ public void testParameterTypes() throws SQLException {
+
+ Connection conn = getConnection();
+ Statement s = conn.createStatement();
+
+ s
+ .execute("create table PT1(A INTEGER not null primary key, B CHAR(10), C VARCHAR(20))");
+ s
+ .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.tests.lang.LangProcedureTest.parameter1' MODIFIES SQL DATA");
+
+ String[] sysaliasDefinition = { "APP.PT1 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.parameter1(IN A INTEGER,IN B CHAR(10),IN C VARCHAR(20)) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA DYNAMIC RESULT SETS 1" };
+ String[] dbMetadataDefinition = { "APP.PT1 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.parameter1 type procedureNoResult" };
+ String[] columnDefinition = { "procedureColumnIn A INTEGER",
+ "procedureColumnIn B CHAR", "procedureColumnIn C VARCHAR" };
+
+ checkMatchingProcedures(conn, "PT1", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ CallableStatement pt1 = conn.prepareCall("CALL PT1(?, ?, ?)");
+
+ pt1.setInt(1, 20);
+ pt1.setString(2, "abc");
+ pt1.setString(3, "efgh");
+ pt1.execute();
+ JDBC.assertFullResultSet(pt1.getResultSet(), new String[][] { { "20",
+ "abc", "10", "efgh", "4" } });
+
+ pt1.setInt(1, 30);
+ pt1.setString(2, "abc ");
+ pt1.setString(3, "efgh ");
+ pt1.execute();
+ JDBC.assertFullResultSet(pt1.getResultSet(), new String[][] { { "30",
+ "abc", "10", "efgh", "6" } });
+
+ pt1.setInt(1, 40);
+ pt1
+ .setString(
+ 2,
+ "abc ");
+ pt1
+ .setString(
+ 3,
+ "efgh ");
+ pt1.execute();
+ JDBC.assertFullResultSet(pt1.getResultSet(), new String[][] { { "40",
+ "abc", "10", "efgh", "20" } });
+
+ pt1.setInt(1, 50);
+ pt1.setString(2, "0123456789X");
+ pt1.setString(3, "efgh ");
+ pt1.execute();
+ JDBC.assertFullResultSet(pt1.getResultSet(), new String[][] { { "50",
+ "0123456789", "10", "efgh", "6" } });
+ pt1.close();
+
+ s.execute("DROP procedure PT1");
+
+ s
+ .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.tests.lang.LangProcedureTest.parameter2' MODIFIES SQL DATA");
+
+ sysaliasDefinition = new String[] { "APP.PT2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.parameter2(IN A INTEGER,IN B DECIMAL(4,0),IN C DECIMAL(7,3)) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA DYNAMIC RESULT SETS 1" };
+ dbMetadataDefinition = new String[] { "APP.PT2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.parameter2 type procedureNoResult" };
+ columnDefinition = new String[] { "procedureColumnIn A INTEGER",
+ "procedureColumnIn B DECIMAL", "procedureColumnIn C DECIMAL" };
+
+ checkMatchingProcedures(conn, "PT2", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ CallableStatement pt2 = conn.prepareCall("CALL PT2(?, ?, ?)");
+
+ pt2.setInt(1, 60);
+ pt2.setString(2, "34");
+ pt2.setString(3, "54.1");
+ boolean firstIsResultSet = pt2.execute();
+ ResultSet rs = pt2.getResultSet();
+ JDBC.assertFullResultSet(rs,
+ new String[][] { { "60", "34", "54.100" } });
+
+ pt2.setInt(1, 70);
+ pt2.setBigDecimal(2, new BigDecimal("831"));
+ pt2.setBigDecimal(3, new BigDecimal("45.7"));
+ pt2.execute();
+ rs = pt2.getResultSet();
+ JDBC.assertFullResultSet(rs,
+ new String[][] { { "70", "831", "45.700" } });
+
+ pt2.setInt(1, -1);
+ pt2.setBigDecimal(2, new BigDecimal("10243"));
+ pt2.setBigDecimal(3, null);
+
+ try {
+ pt2.execute();
+ fail("FAIL - too many digits in decimal value accepted");
+ } catch (SQLException sqle) {
+ assertSQLState("22003", sqle);
+ }
+ pt2.setInt(1, 80);
+ pt2.setBigDecimal(2, new BigDecimal("993"));
+ pt2.setBigDecimal(3, new BigDecimal("1234.5678"));
+ pt2.execute();
+ rs = pt2.getResultSet();
+ JDBC.assertFullResultSet(rs,
+ new String[][] { { "80", "993", "1234.567" } });
+ pt2.close();
+
+ s.execute("DROP procedure PT2");
+
+ s
+ .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.tests.lang.LangProcedureTest.pSMALLINT' NO SQL");
+ sysaliasDefinition = new String[] { "APP.PTSMALLINT2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.pSMALLINT(IN P_IN SMALLINT,INOUT P_INOUT SMALLINT,OUT P_OUT SMALLINT) LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL" };
+ dbMetadataDefinition = new String[] { "APP.PTSMALLINT2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.pSMALLINT type procedureNoResult" };
+ columnDefinition = new String[] { "procedureColumnIn P_IN SMALLINT",
+ "procedureColumnInOut P_INOUT SMALLINT",
+ "procedureColumnOut P_OUT SMALLINT" };
+
+ checkMatchingProcedures(conn, "PT2", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ CallableStatement ptsi = conn.prepareCall("CALL PTSMALLINT2(?, ?, ?)");
+ ptsi.registerOutParameter(2, Types.SMALLINT);
+ ptsi.registerOutParameter(3, Types.SMALLINT);
+
+ ptsi.setNull(1, Types.SMALLINT);
+ ptsi.setShort(2, (short) 7);
+ try {
+ ptsi.execute();
+ fail("FAIL NULL PASSED to primitive");
+ } catch (SQLException sqle) {
+ assertSQLState("39004", sqle);
+ }
+
+ ptsi.setShort(1, (short) 4);
+ ptsi.setNull(2, Types.SMALLINT);
+ try {
+ ptsi.execute();
+ fail("FAIL NULL PASSED to primitive");
+ } catch (SQLException sqle) {
+ assertSQLState("39004", sqle);
+ }
+
+ ptsi.setShort(1, (short) 6);
+ ptsi.setShort(2, (short) 3);
+ ptsi.execute();
+ assertEquals("wrong value for p_inout", "9", ptsi.getObject(2)
+ .toString());
+ assertEquals("wrong value for p_out", "6", ptsi.getObject(3).toString());
+
+ ptsi.setShort(2, (short) 3);
+ ptsi.execute();
+ assertEquals("wrong value for p_inout", "9", ptsi.getObject(2)
+ .toString());
+ assertEquals("wrong value for p_out", "6", ptsi.getObject(3).toString());
+
+ // with setObject . Beetle 5439
+ ptsi.setObject(1, new Integer(6));
+ ptsi.setObject(2, new Integer(3));
+
+ ptsi.execute();
+ assertEquals("wrong value for p_inout", "9", ptsi.getObject(2)
+ .toString());
+ assertFalse(ptsi.wasNull());
+ assertEquals("wrong value for p_out", "6", ptsi.getObject(3).toString());
+ assertFalse(ptsi.wasNull());
+
+ ptsi.close();
+
+ s.execute("DROP procedure PTSMALLINT2");
+ s.execute("DROP TABLE PT1");
+
+ s.close();
+ }
+
+ public void testOutparams() throws SQLException {
+ Connection conn = getConnection();
+ Statement s = conn.createStatement();
+
+ s
+ .execute("create procedure OP1(OUT a int, IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.outparams1'");
+
+ String[] sysaliasDefinition = { "APP.OP1 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.outparams1(OUT A INTEGER,IN B INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
+ String[] dbMetadataDefinition = { "APP.OP1 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.outparams1 type procedureNoResult" };
+ String[] columnDefinition = { "procedureColumnOut A INTEGER",
+ "procedureColumnIn B INTEGER" };
+
+ checkMatchingProcedures(conn, "OP1", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ // check execute via a Statement fails for use of OUT parameter
+
+ try {
+ s.execute("CALL OP1(?, ?)");
+ fail("FAIL execute succeeded on OUT param with Statement");
+ } catch (SQLException sqle) {
+ String expectedSQLState = "XJ009";
+ if (usingDerbyNetClient())
+ expectedSQLState = "07004";
+ assertSQLState(expectedSQLState, sqle);
+ }
+
+ // check execute via a PreparedStatement fails for use of OUT parameter
+ // DERBY-2512 Network client allows prepare of a stored procedure with
+ // an output parameter using a PreparedStatement
+ if (usingEmbedded())
+ try {
+ PreparedStatement ps = conn.prepareStatement("CALL OP1(?, ?)");
+ fail("FAIL prepare succeeded on OUT param with PreparedStatement");
+ } catch (SQLException sqle) {
+ String expectedSQLState = "XJ009";
+ assertSQLState(expectedSQLState, sqle);
+
+ }
+
+ CallableStatement op = conn.prepareCall("CALL OP1(?, ?)");
+
+ op.registerOutParameter(1, Types.INTEGER);
+ op.setInt(2, 7);
+ op.execute();
+
+ assertEquals(14, op.getInt(1));
+ assertFalse(op.wasNull());
+
+ op.close();
+
+ s
+ .execute("create procedure OP2(INOUT a int, IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams2'");
+ sysaliasDefinition = new String[] { "APP.OP2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams2(INOUT A INTEGER,IN B INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
+ dbMetadataDefinition = new String[] { "APP.OP2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams2 type procedureNoResult" };
+ columnDefinition = new String[] { "procedureColumnInOut A INTEGER",
+ "procedureColumnIn B INTEGER" };
+
+ checkMatchingProcedures(conn, "OP2", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ try {
+ s.execute("CALL OP2(?,?)");
+ fail("FAIL execute succeeded on INOUT param with Statement");
+ } catch (SQLException sqle) {
+ String expectedSQLState = "XJ009";
+ if (usingDerbyNetClient())
+ expectedSQLState = "07004";
+ assertSQLState(expectedSQLState, sqle);
+ }
+
+ if (!usingDerbyNetClient()) { // bug DERBY-2512
+ // check execute via a PreparedStatement fails for use of INOUT
+ // parameter
+ try {
+ PreparedStatement ps = conn.prepareStatement("CALL OP2(?, ?)");
+ fail("FAIL prepare succeeded on INOUT param with PreparedStatement");
+ } catch (SQLException sqle) {
+ String expectedSQLState = "XJ009";
+ assertSQLState(expectedSQLState, sqle);
+
+ }
+ }
+
+ op = conn.prepareCall("CALL OP2(?, ?)");
+
+ op.registerOutParameter(1, Types.INTEGER);
+ op.setInt(1, 3);
+ op.setInt(2, 7);
+ op.execute();
+ assertEquals(17, op.getInt(1));
+ assertFalse(op.wasNull());
+ op.close();
+
+ // INOUT & OUT procedures with variable length
+ s
+ .execute("create procedure OP3(INOUT a CHAR(10), IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams3'");
+
+ op = conn.prepareCall("CALL OP3(?, ?)");
+
+ op.registerOutParameter(1, Types.CHAR);
+ op.setString(1, "dan");
+ op.setInt(2, 8);
+ op.execute();
+ assertEquals("nad ", op.getString(1));
+ assertFalse(op.wasNull());
+ op.close();
+
+ // INOUT & OUT DECIMAL procedures with variable length
+ s
+ .execute("create procedure OP4(OUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams4'");
+ sysaliasDefinition = new String[] { "APP.OP4 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams4(OUT A DECIMAL(4,2),IN B VARCHAR(255)) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
+ dbMetadataDefinition = new String[] { "APP.OP4 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams4 type procedureNoResult" };
+ columnDefinition = new String[] { "procedureColumnOut A DECIMAL",
+ "procedureColumnIn B VARCHAR", };
+
+ checkMatchingProcedures(conn, "OP4", sysaliasDefinition,
+ dbMetadataDefinition, columnDefinition);
+
+ op = conn.prepareCall("CALL OP4(?, ?)");
+ op.registerOutParameter(1, Types.DECIMAL);
+ op.setString(2, null);
+ op.execute();
+ assertNull(op.getBigDecimal(1));
+ assertTrue(op.wasNull());
+
+ op.setString(2, "14");
+ op.execute();
+ assertEquals("31.00", op.getBigDecimal(1).toString());
+ assertFalse(op.wasNull());
+
+ op.setString(2, "11.3");
+ op.execute();
+ assertEquals("28.30", op.getBigDecimal(1).toString());
+ assertFalse(op.wasNull());
+
+ op.setString(2, "39.345");
+ op.execute();
+ assertEquals("56.34", op.getBigDecimal(1).toString());
+ assertFalse(op.wasNull());
+
+ op.setString(2, "83");
+ try {
+ op.execute();
+ fail("FAIL - execution ok on out of range out parameter");
+ } catch (SQLException sqle) {
+ assertSQLState("22003", sqle);
+ }
+
+ op.clearParameters();
+ try {
+ // b not set
+ op.execute();
+
+ fail("FAIL - b not set");
+ } catch (SQLException sqle) {
+ assertSQLState("07000", sqle);
+ }
+ // DERBY-2513 Network client allows OUT parameter to be set
+ if (usingEmbedded()) {
+ // try to set an OUT param
+ try {
+ op.setBigDecimal(1, new BigDecimal("22.32"));
+ fail("FAIL - set OUT param to value");
+ } catch (SQLException sqle) {
+ assertSQLState("XCL27", sqle);
+ }
+
+ try {
+ op.setBigDecimal(1, null);
+ fail("FAIL - set OUT param to null");
+ } catch (SQLException sqle) {
+ assertSQLState("XCL27", sqle);
+ }
+ try {
+ op.setNull(1, Types.DECIMAL);
+ fail("FAIL - set OUT param to null");
+ } catch (SQLException sqle) {
+ assertSQLState("XCL27", sqle);
+
+ }
+ }
+
+ // can we get an IN param?
+ op.setString(2, "49.345");
+ op.execute();
+ assertEquals("66.34", op.getBigDecimal(1).toString());
+ assertFalse(op.wasNull());
+
+ try {
+ op.getString(2);
+ fail("FAIL OP4 GET 49.345 >" + op.getString(2) + "< null ? "
+ + op.wasNull());
+ } catch (SQLException sqle) {
+ if (usingDerbyNetClient())
+ assertSQLState("XJ091", sqle);
+ else
+ assertSQLState("XCL26", sqle);
+ }
+ op.close();
+
+ // check to see that a registration is required first for the out
+ // parameter.
+ op = conn.prepareCall("CALL OP4(?, ?)");
+ op.setString(2, "14");
+ try {
+ op.execute();
+ fail("FAIL - execute succeeded without registration of out parameter");
+ } catch (SQLException sqle) {
+ if (usingEmbedded())
+ assertSQLState("07004", sqle);
+ else
+ assertSQLState("07000", sqle);
+ }
+ op.close();
+
+ s
+ .execute("create procedure OP4INOUT(INOUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.inoutparams4'");
+
+ op = conn.prepareCall("CALL OP4INOUT(?, ?)");
+ op.registerOutParameter(1, Types.DECIMAL);
+
+ op.setString(2, null);
+
+ op.setBigDecimal(1, null);
+ op.execute();
+ assertNull(op.getBigDecimal(1));
+ assertTrue(op.wasNull());
+
+ op.setBigDecimal(1, new BigDecimal("99"));
+ op.execute();
+ assertNull(op.getBigDecimal(1));
+ assertTrue(op.wasNull());
+
+ op.setString(2, "23.5");
+ op.setBigDecimal(1, new BigDecimal("14"));
+ op.execute();
+ assertEquals("37.50", op.getBigDecimal(1).toString());
+
+ op.setString(2, "23.505");
+ op.setBigDecimal(1, new BigDecimal("9"));
+ op.execute();
+ assertEquals("32.50", op.getBigDecimal(1).toString());
+
+ if (usingEmbedded()) {
+ // Not run for client due to DERBY-2515
+ op.execute();
+ assertEquals("56.00", op.getBigDecimal(1).toString());
+ assertFalse(op.wasNull());
+ }
+
+ op.setString(2, "67.99");
+ op.setBigDecimal(1, new BigDecimal("32.01"));
+ try {
+ op.execute();
+ fail("FAIL OP4INOUT 32.01+67.99 >" + op.getBigDecimal(1)
+ + "< null ? " + op.wasNull());
+ } catch (SQLException sqle) {
+ assertSQLState("22003", sqle);
+ }
+
+ op.setString(2, "1");
+ op.setBigDecimal(1, new BigDecimal("102.33"));
+ try {
+ op.execute();
+ fail("FAIL OP4INOUT 1+102.33 >" + op.getBigDecimal(1) + "< null ? "
+ + op.wasNull());
+ } catch (SQLException sqle) {
+ assertSQLState("22003", sqle);
+ }
+
+ // now some checks to requirements for parameter setting.
+ op.clearParameters();
+ try {
+ // a,b not set
+ op.execute();
+ fail("FAIL - a,b not set");
+ } catch (SQLException sqle) {
+ assertSQLState("07000", sqle);
+ }
+ if (usingEmbedded()) {
+ // Do not run for client until DERBY-2516 is fixed
+ op.clearParameters();
+ op.setString(2, "2");
+ try {
+ // a not set
+ op.execute();
+ fail("FAIL - a not set");
+ } catch (SQLException sqle) {
+ assertSQLState("07000", sqle);
+ }
+ }
+ op.clearParameters();
+ op.setBigDecimal(1, new BigDecimal("33"));
+ try {
+ // b not set
+ op.execute();
+ fail("FAIL - b not set");
+ } catch (SQLException sqle) {
+ assertSQLState("07000", sqle);
+ }
+
+ op.close();
+
+ op = conn.prepareCall("CALL OP4INOUT(?, ?)");
+ op.setString(2, "14");
+ try {
+ op.execute();
+ fail("FAIL - execute succeeded without registration of INOUT parameter");
+ } catch (SQLException sqle) {
+ if (usingDerbyNetClient())
+ assertSQLState("07000", sqle);
+ else
+ assertSQLState("07004", sqle);
+ }
+ op.close();
+
+ s.execute("DROP PROCEDURE OP1");
+ s.execute("DROP PROCEDURE OP2");
+ s.execute("DROP PROCEDURE OP3");
+ s.execute("DROP PROCEDURE OP4");
+ s.execute("DROP PROCEDURE OP4INOUT");
+ s.close();
+
+ }
+
+ public void testSQLControl() throws SQLException {
+ Connection conn = getConnection();
+ Statement s = conn.createStatement();
+
+ s.execute("CREATE SCHEMA SQLC");
+ s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
+ s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");
+
+ String[] control = { "", "NO SQL", "CONTAINS SQL", "READS SQL DATA",
+ "MODIFIES SQL DATA" };
+
+ for (int i = 0; i < control.length; i++) {
+
+ StringBuffer cp = new StringBuffer(256);
+ cp.append("CREATE PROCEDURE SQLC.SQLCONTROL1_");
+ cp.append(i);
+ cp
+ .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)) ");
+ cp.append(control[i]);
+ cp
+ .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.sqlControl'");
+
+ String cpsql = cp.toString();
+
+ s.execute(cpsql);
+
+ cp.setLength(0);
+ cp.append("CREATE PROCEDURE SQLC.SQLCONTROL2_");
+ cp.append(i);
+ cp
+ .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)) ");
+ cp.append(control[i]);
+ cp
+ .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.sqlControl2'");
+
+ cpsql = cp.toString();
+
+ s.execute(cpsql);
+
+ cp.setLength(0);
+ cp.append("CREATE PROCEDURE SQLC.SQLCONTROL3_");
+ cp.append(i);
+ cp
+ .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)) ");
+ cp.append(control[i]);
+ cp
+ .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.sqlControl3'");
+
+ cpsql = cp.toString();
+
+ s.execute(cpsql);
+
+ cp.setLength(0);
+ cp.append("CREATE PROCEDURE SQLC.SQLCONTROL4_");
+ cp.append(i);
+ cp
+ .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)) ");
+ cp.append(control[i]);
+ cp
+ .append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.sqlControl4'");
+
+ cpsql = cp.toString();
+
+ s.execute(cpsql);
+ }
+
+ if (!conn.getAutoCommit())
+ conn.commit();
+
+ String[][] sqlControl_0 = /* sqlControl_0 */{
+ { "CREATE TABLE SQ-UPDATE 0-EXECUTE OK",
+ "ALTER TABLE SQL-UPDATE 0-EXECUTE OK",
+ "INSERT INTO SQL-UPDATE 1-EXECUTE OK",
+ "UPDATE SQLC.SQL-UPDATE 2-EXECUTE OK",
+ "SELECT * FROM S- ROW(15)- ROW(12)-EXECUTE OK",
+ "DELETE FROM SQL-UPDATE 2-EXECUTE OK",
+ "DROP TABLE SQLC-UPDATE 0-EXECUTE OK" },
+ { "CREATE VIEW SQL-42X05", "DROP VIEW SQLCO-X0X05",
+ "LOCK TABLE SQLC-42X05",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-UPDATE 0-EXECUTE OK",
+ "DROP SCHEMA SQL-UPDATE 0-EXECUTE OK" },
+ { "DERBY FEATURE", "DERBY FEATURE",
+ "SET ISOLATION C-UPDATE 0-EXECUTE OK",
+ "SET RUNTIMESTAT-42X01", "SET STATISTICS -42X01",
+ "VALUES 1- ROW(1)-EXECUTE OK",
+ "VALUES 1- ROW(1)-EXECUTE OK" } };
+
+ String[][] sqlControl_1 = /* sqlControl_1 */{
+ { "CREATE TABLE SQ-38001", "ALTER TABLE SQL-42Y55",
+ "INSERT INTO SQL-42X05", "UPDATE SQLC.SQL-42X05",
+ "SELECT * FROM S-42X05", "DELETE FROM SQL-42X05",
+ "DROP TABLE SQLC-42Y55" },
+ { "CREATE VIEW SQL-42X05", "DROP VIEW SQLCO-38001",
+ "LOCK TABLE SQLC-42X05", "VALUES 1,2,3-38001",
+ "SET SCHEMA SQLC-38001", "CREATE SCHEMA S-38001",
+ "DROP SCHEMA SQL-38001" },
+ { "DERBY FEATURE", "DERBY FEATURE", "SET ISOLATION C-38001",
+ "SET RUNTIMESTAT-42X01", "SET STATISTICS -42X01",
+ "VALUES 1-38001", "VALUES 1-38001" } };
+
+ String[][] sqlControl_2 = /* sqlControl_2 */{
+ { "CREATE TABLE SQ-38002", "ALTER TABLE SQL-42Y55",
+ "INSERT INTO SQL-42X05", "UPDATE SQLC.SQL-42X05",
+ "SELECT * FROM S-42X05", "DELETE FROM SQL-42X05",
+ "DROP TABLE SQLC-42Y55" },
+ { "CREATE VIEW SQL-42X05", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-42X05", "VALUES 1,2,3-38004",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "DERBY FEATURE", "DERBY FEATURE",
+ "SET ISOLATION C-UPDATE 0-EXECUTE OK",
+ "SET RUNTIMESTAT-42X01", "SET STATISTICS -42X01",
+ "VALUES 1-38004", "VALUES 1-38004" } };
+
+ String[][] sqlControl_3 = /* sqlControl_3 */{
+ { "CREATE TABLE SQ-38002", "ALTER TABLE SQL-42Y55",
+ "INSERT INTO SQL-42X05", "UPDATE SQLC.SQL-42X05",
+ "SELECT * FROM S-42X05", "DELETE FROM SQL-42X05",
+ "DROP TABLE SQLC-42Y55" },
+ { "CREATE VIEW SQL-42X05", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-42X05",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "DERBY FEATURE", "DERBY FEATURE",
+ "SET ISOLATION C-UPDATE 0-EXECUTE OK",
+ "SET RUNTIMESTAT-42X01", "SET STATISTICS -42X01",
+ "VALUES 1- ROW(1)-EXECUTE OK",
+ "VALUES 1- ROW(1)-EXECUTE OK" } };
+
+ String[][] sqlControl_4 =
+ /* sqlControl_4 */{
+ { "CREATE TABLE SQ-UPDATE 0-EXECUTE OK",
+ "ALTER TABLE SQL-42Y55", "INSERT INTO SQL-42X05",
+ "UPDATE SQLC.SQL-42X05", "SELECT * FROM S-42X05",
+ "DELETE FROM SQL-42X05", "DROP TABLE SQLC-42Y55" },
+ { "CREATE VIEW SQL-42X05", "DROP VIEW SQLCO-X0X05",
+ "LOCK TABLE SQLC-42X05",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-UPDATE 0-EXECUTE OK",
+ "DROP SCHEMA SQL-UPDATE 0-EXECUTE OK" },
+ { "DERBY FEATURE", "DERBY FEATURE",
+ "SET ISOLATION C-UPDATE 0-EXECUTE OK",
+ "SET RUNTIMESTAT-42X01", "SET STATISTICS -42X01",
+ "VALUES 1- ROW(1)-EXECUTE OK",
+ "VALUES 1- ROW(1)-EXECUTE OK" } };
+
+ String[][][] sqlControl = { sqlControl_0, sqlControl_1, sqlControl_2,
+ sqlControl_3, sqlControl_4 };
+ for (int i = 0; i < control.length; i++) {
+ String type = control[i];
+ if (type.length() == 0)
+ type = "DEFAULT (MODIFIES SQL DATA)";
+ // System.out.print("/*sqlControl_" + i + "*/{");
+ for (int k = 1; k <= 3; k++) {
+ CallableStatement cs = conn.prepareCall("CALL SQLC.SQLCONTROL"
+ + k + "_" + i + " (?, ?, ?, ?, ?, ?, ?)");
+ // System.out.println("{");
+ for (int rop = 1; rop <= 7; rop++) {
+ cs.registerOutParameter(rop, Types.VARCHAR);
+ }
+ cs.execute();
+ for (int p = 1; p <= 7; p++) {
+ // System.out.print("\"" + cs.getString(p) + "\"");
+ /*
+ * if (p != 7) System.out.println(","); else
+ * System.out.println("}");
+ */
+ assertEquals(sqlControl[i][k - 1][p - 1], cs.getString(p));
+ }
+
+ cs.close();
+ /*
+ * if (k < 3) System.out.println(","); else
+ * System.out.println("}");
+ */
+ }
+
+ // System.out.println("}");
+ }
+
+ // test procedures that call others, e.g. to ensure that within a READS
+ // SQL DATA procedure, a MODIFIES SQL DATA cannot be called.
+ // table was dropped by previous executions.
+ String[][] dmlSqlControl_0 = /* dmlSqlControl_0 */{
+ { "CALL SQLC.SQLCONTROL2_0 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-UPDATE 0-EXECUTE OK",
+ "DROP VIEW SQLCO-UPDATE 0-EXECUTE OK",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-UPDATE 0-EXECUTE OK",
+ "DROP SCHEMA SQL-UPDATE 0-EXECUTE OK" },
+ { "CALL SQLC.SQLCONTROL2_1 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38001", "DROP VIEW SQLCO-38001",
+ "LOCK TABLE SQLC-38001", "VALUES 1,2,3-38001",
+ "SET SCHEMA SQLC-38001", "CREATE SCHEMA S-38001",
+ "DROP SCHEMA SQL-38001" },
+ { "CALL SQLC.SQLCONTROL2_2 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38002", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3-38004",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "CALL SQLC.SQLCONTROL2_3 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38002", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "CALL SQLC.SQLCONTROL2_4 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-UPDATE 0-EXECUTE OK",
+ "DROP VIEW SQLCO-UPDATE 0-EXECUTE OK",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-UPDATE 0-EXECUTE OK",
+ "DROP SCHEMA SQL-UPDATE 0-EXECUTE OK" } };
+
+ String[][] dmlSqlControl_1 = /* dmlSqlControl_1 */{
+ { "CALL SQLC.SQLCONTROL2_0 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38001", "null", "null", "null", "null", "null",
+ "null" },
+ { "CALL SQLC.SQLCONTROL2_1 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38001", "null", "null", "null", "null", "null",
+ "null" },
+ { "CALL SQLC.SQLCONTROL2_2 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38001", "null", "null", "null", "null", "null",
+ "null" },
+ { "CALL SQLC.SQLCONTROL2_3 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38001", "null", "null", "null", "null", "null",
+ "null" },
+ { "CALL SQLC.SQLCONTROL2_4 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38001", "null", "null", "null", "null", "null",
+ "null" }
+
+ };
+
+ String[][] dmlSqlControl_2 = /* dmlSqlControl_2 */{
+ { "CALL SQLC.SQLCONTROL2_0 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38002", "null", "null", "null", "null", "null",
+ "null" },
+ { "CALL SQLC.SQLCONTROL2_1 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38001", "DROP VIEW SQLCO-38001",
+ "LOCK TABLE SQLC-38001", "VALUES 1,2,3-38001",
+ "SET SCHEMA SQLC-38001", "CREATE SCHEMA S-38001",
+ "DROP SCHEMA SQL-38001" },
+ { "CALL SQLC.SQLCONTROL2_2 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38002", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3-38004",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "CALL SQLC.SQLCONTROL2_3 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38004", "null", "null", "null", "null", "null",
+ "null" },
+ { "CALL SQLC.SQLCONTROL2_4 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38002", "null", "null", "null", "null", "null",
+ "null" } };
+
+ String[][] dmlSqlControl_3 = /* dmlSqlControl_3 */{
+ { "CALL SQLC.SQLCONTROL2_0 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38002", "null", "null", "null", "null", "null",
+ "null" },
+ { "CALL SQLC.SQLCONTROL2_1 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38001", "DROP VIEW SQLCO-38001",
+ "LOCK TABLE SQLC-38001", "VALUES 1,2,3-38001",
+ "SET SCHEMA SQLC-38001", "CREATE SCHEMA S-38001",
+ "DROP SCHEMA SQL-38001" },
+ { "CALL SQLC.SQLCONTROL2_2 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38002", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3-38004",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "CALL SQLC.SQLCONTROL2_3 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38002", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "CALL SQLC.SQLCONTROL2_4 (?, ?, ?, ?, ?, ?, ?) ",
+ "STATE-38002", "null", "null", "null", "null", "null",
+ "null" } };
+
+ String[][] dmlSqlControl_4 = /* dmlSqlControl_4 */{
+ { "CALL SQLC.SQLCONTROL2_0 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-UPDATE 0-EXECUTE OK",
+ "DROP VIEW SQLCO-UPDATE 0-EXECUTE OK",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-UPDATE 0-EXECUTE OK",
+ "DROP SCHEMA SQL-UPDATE 0-EXECUTE OK" },
+ { "CALL SQLC.SQLCONTROL2_1 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38001", "DROP VIEW SQLCO-38001",
+ "LOCK TABLE SQLC-38001", "VALUES 1,2,3-38001",
+ "SET SCHEMA SQLC-38001", "CREATE SCHEMA S-38001",
+ "DROP SCHEMA SQL-38001" },
+ { "CALL SQLC.SQLCONTROL2_2 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38002", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3-38004",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "CALL SQLC.SQLCONTROL2_3 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-38002", "DROP VIEW SQLCO-38002",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-38002", "DROP SCHEMA SQL-38002" },
+ { "CALL SQLC.SQLCONTROL2_4 (?, ?, ?, ?, ?, ?, ?) ",
+ "CREATE VIEW SQL-UPDATE 0-EXECUTE OK",
+ "DROP VIEW SQLCO-UPDATE 0-EXECUTE OK",
+ "LOCK TABLE SQLC-UPDATE 0-EXECUTE OK",
+ "VALUES 1,2,3- ROW(1)- ROW(2)- ROW(3)-EXECUTE OK",
+ "SET SCHEMA SQLC-UPDATE 0-EXECUTE OK",
+ "CREATE SCHEMA S-UPDATE 0-EXECUTE OK",
+ "DROP SCHEMA SQL-UPDATE 0-EXECUTE OK" } };
+
+ String[][][] dmlSqlControl = { dmlSqlControl_0, dmlSqlControl_1,
+ dmlSqlControl_2, dmlSqlControl_3, dmlSqlControl_4 };
+
+ s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
+ s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");
+ for (int i = 0; i < control.length; i++) {
+ String type = control[i];
+ if (type.length() == 0)
+ type = "DEFAULT (MODIFIES SQL DATA)";
+ // System.out.println("/* dmlSqlControl_" + i + "*/{");
+
+ for (int t = 0; t < control.length; t++) {
+ String ttype = control[t];
+ if (ttype.length() == 0)
+ ttype = "DEFAULT (MODIFIES SQL DATA)";
+ // System.out.println("{");
+ CallableStatement cs = conn
+ .prepareCall("CALL SQLC.SQLCONTROL4_" + i
+ + " (?, ?, ?, ?, ?, ?, ?, ?, ?)");
+ cs.setInt(1, t);
+ for (int rop = 2; rop <= 9; rop++) {
+ cs.registerOutParameter(rop, Types.VARCHAR);
+ }
+
+ cs.execute();
+ for (int p = 2; p <= 9; p++) {
+ String so = cs.getString(p);
+ // System.out.print("\"" + so + "\"");
+ /*
+ * if (p < 9) System.out.println(","); else
+ * System.out.println("}");
+ */
+ if (so == null)
+ continue;
+ assertEquals(dmlSqlControl[i][t][p - 2], so);
+ }
+ /*
+ * if (t< control.length -1) System.out.println(","); else
+ * System.out.println("}");
+ */
+ cs.close();
+ }
+ }
+ // Make sure we throw proper error with network server
+ // if params are not registered
+ testBug5280(conn);
+
+ s.execute("DROP TABLE SQLC.SQLCONTROL_DML");
+
+ for (int i = 0; i < control.length; i++) {
+ s.execute("DROP PROCEDURE SQLCONTROL1_" + i);
+ s.execute("DROP PROCEDURE SQLCONTROL2_" + i);
+ s.execute("DROP PROCEDURE SQLCONTROL4_" + i);
+ }
+ s.execute("DROP TABLE SQLC.SQLCONTROL_DDL");
+ s.execute("SET SCHEMA APP");
+ s.execute("DROP SCHEMA SQLC RESTRICT");
+
+ s.close();
+ }
+
+ public static void testBug5280(Connection conn) throws SQLException {
+ String csString = "CALL SQLCONTROL3_0 (?, ?, ?, ?, ?, ?, ?)";
+ // Bug 5280 If we don't register the outparams
+ // we don't get an error with network server.
+ // for (int p = 1; p <= 7; p++) {
+ // cs.registerOutParameter(p,Types.VARCHAR);
+ // }
+ assertCallError("07000", conn, csString);
+ }
+
+ public static void pSMALLINT(short in, short[] inout, short[] out)
+ throws SQLException {
+
+ out[0] = in;
+ inout[0] += in;
+ }
+
+ public static void parameter1(int a, String b, String c,
+ java.sql.ResultSet[] rs) throws SQLException {
+
+ Connection conn = DriverManager
+ .getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn
+ .prepareStatement("insert into PT1 values (?, ?, ?)");
+ ps.setInt(1, a);
+ ps.setString(2, b);
+ ps.setString(3, c);
+ ps.executeUpdate();
+ ps.close();
+ ps = conn
[... 231 lines stripped ...]