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 jt...@apache.org on 2007/02/28 20:08:23 UTC
svn commit: r512905 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi:
CallableTest.java _Suite.java
Author: jta
Date: Wed Feb 28 11:08:22 2007
New Revision: 512905
URL: http://svn.apache.org/viewvc?view=rev&rev=512905
Log:
DERBY-2304 (partial) Convert derbynet/callable.java to JUnit.
Tests now run in both embedded and client. The suite explicitly runs four
tests that use DriverManager or BigDecimal only if JDBC2 is available.
All tests in derbynet/callable.java were converted except for testBatch,
which is commented out in the original with the note "Temporarily take out
testbatch until jcc bug is fixed (5827)". I'll do a little research to see
if it should now be expected to run successfully.
The test that outputs a message to System.out is not currently enabled
because I'm not sure it makes sense to run it in the junit environment.
But it would be easily enabled by changing the method name from
norun_testSystemOutPrintlnProc to testSystemOutPrintlnProc.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java (with props)
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java?view=auto&rev=512905
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java Wed Feb 28 11:08:22 2007
@@ -0,0 +1,818 @@
+/*
+ Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.CallableTest
+ 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.jdbcapi;
+
+import java.math.BigDecimal;
+import java.sql.Date;
+import java.sql.Time;
+import java.sql.Timestamp;
+
+import java.util.Arrays; // Used by testUpdateLongBinaryProc
+
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * Test the CallableStatement interface.
+ * <p>
+ * This test converts the old derbynet/callable.java test to JUnit. It
+ * exercises the CallableStatement interface with various combinations of:
+ * - IN, OUT, and INOUT registered parameters
+ * - SQL functions and procedures
+ * - Different data types
+ * <BR>
+ * testTwoInOneOutProc()
+ * Calls a procedure that takes two input parameters and returns one output
+ * (integers).
+ * <BR>
+ * testOneInOneOutFunc()
+ * Calls a function takes one input parameter and returns one output
+ * (integers).
+ * <BR>
+ * testNoInOneOutFunc()
+ * Calls a function that doesn't take any parameters and returns one output
+ * (integer).
+ * <BR>
+ * norun_testSystemOutPrintlnProc()
+ * Calls a procedure that just outputs a message with System.out.println.
+ * Converted from the original test, but initially disabled because of the
+ * message output to system out. Easily enabled by changing method name to
+ * testSystemOutPrintlnProc().
+ * <BR>
+ * xtestNumericTypesInAndOutProc()
+ * Calls a SQL procedure with a variety of numeric IN/OUT parameters.
+ * Also tests overloading the external method.
+ * Excluded from JSR169/j2ME tests because of the BigDecimal.
+ * <BR>
+ * testNonNumericTypesInAndOutProc()
+ * Calls a SQL procedure with a variety of non-numeric IN/OUT parameters.
+ * Also tests overloading the external method.
+ * <BR>
+ * testManyTypesInoutProc()
+ * Calls a SQL procedure with a variety of INOUT parameters.
+ * <BR>
+ * xtestRegUserDefOutParameterError()
+ * Tries to register a user-defined OUT parameter, which should throw a
+ * not implemented exception.
+ * Excluded from DB2 Client tests. The test should throw a not implemented
+ * exception with embedded and with Derby Network Client (DERBY-1184).
+ * <BR>
+ * xtestUpdateLongBinaryProc()
+ * Calls a SQL procedure that updates a long varbinary column in a table.
+ * Excluded from environments than don't have JDBC 2 DriverManager.
+ * <BR>
+ * xtestNumericBoundariesProc()
+ * Calls a SQL procedure that accesses minimum, maximum, and null values
+ * in a table with numeric columns.
+ * Excluded from environments than don't have JDBC 2 DriverManager.
+ * <BR>
+ * xtestBigDecimalInAndOutProc()
+ * Calls a SQL procedure with input and output parameters of the BigDecimal
+ * data type.
+ * Excluded from JSR169/j2ME tests because of the BigDecimal.
+ */
+public class CallableTest extends BaseJDBCTestCase {
+
+ private static final String NOT_IMPLEMENTED = "0A000";
+
+ /**
+ * Routines that should be created before the tests are run and
+ * dropped when the tests have finished.
+ */
+ private static final String[] ROUTINES = {
+
+ "CREATE PROCEDURE TWO_IN_ONE_OUT_PROC " +
+ "(IN P1 INT, IN P2 INT, OUT P3 INT) " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".twoInOneOutProc'",
+
+ "CREATE FUNCTION ONE_IN_ONE_OUT_FUNC (P1 INT) RETURNS INT " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".oneInOneOutFunc'",
+
+ "CREATE FUNCTION NO_IN_ONE_OUT_FUNC() RETURNS INT " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".noInOneOutFunc'",
+
+ "CREATE PROCEDURE SYSTEM_OUT_PRINTLN_PROC() " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".systemOutPrintlnProc'",
+
+ "CREATE PROCEDURE UPDATE_LONGVARBINARY_PROC " +
+ "(P1 VARCHAR(10000) FOR BIT DATA) MODIFIES SQL DATA " +
+ "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".updateLongVarbinaryProc'",
+
+ "CREATE PROCEDURE NUMERIC_BOUNDARIES_PROC " +
+ "(OUT P1 DECIMAL(31,15), OUT P2 DECIMAL(31,15), " +
+ "OUT P3 DECIMAL(31,15)) " +
+ "READS SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".numericBoundariesProc'",
+
+ "CREATE PROCEDURE NUMERIC_TYPES_IN_AND_OUT_PROC " +
+ "(IN P1 SMALLINT, IN P2 INT, IN P3 BIGINT, " +
+ "IN P4 REAL, IN P5 DOUBLE, IN P6 DECIMAL(6,3), " +
+ "OUT O1 SMALLINT, OUT O2 INT, OUT O3 BIGINT, " +
+ "OUT O4 REAL, OUT O5 DOUBLE, OUT O6 DECIMAL(6,3) ) " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".manyTypesInAndOutProc'",
+
+ "CREATE PROCEDURE NON_NUMERIC_TYPES_IN_AND_OUT_PROC " +
+ "(IN P1 DATE, IN P2 TIME, IN P3 TIMESTAMP, " +
+ "IN P4 VARCHAR(20) FOR BIT DATA, " +
+ "OUT O1 DATE, OUT O2 TIME, OUT O3 TIMESTAMP, " +
+ "OUT O4 VARCHAR(20) FOR BIT DATA) " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".manyTypesInAndOutProc'",
+
+ "CREATE PROCEDURE MANY_TYPES_INOUT_PROC " +
+ "(IN P1 SMALLINT, INOUT P2 SMALLINT, IN P3 INT, INOUT P4 INT, " +
+ "IN P5 BIGINT, INOUT P6 BIGINT, IN P7 REAL, INOUT P8 REAL, " +
+ "IN P9 DOUBLE, INOUT P10 DOUBLE, IN P11 TIME, INOUT P12 TIME) " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".manyTypesInoutProc'",
+
+ "CREATE PROCEDURE BIGDECIMAL_IN_AND_OUT_PROC " +
+ "(IN P1 DECIMAL(14,4), OUT P2 DECIMAL(14,4), " +
+ "IN P3 DECIMAL(14,4), OUT P4 DECIMAL(14,4), " +
+ "OUT P5 DECIMAL(14,4), OUT P6 DECIMAL(14,4), " +
+ "OUT P7 DECIMAL(14,4), OUT P8 DECIMAL(14,4), " +
+ "OUT P9 DECIMAL(14,4)) EXTERNAL NAME '" +
+ CallableTest.class.getName() + ".bigDecimalInAndOutProc' " +
+ "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA"
+ };
+
+ /**
+ * Tables that should be created before the tests are run and
+ * dropped when the tests have finished. The first element in each row
+ * is the name of the table and the second element is the SQL text that
+ * creates it.
+ */
+ private static final String[][] TABLES = {
+ // LONGVARBINARY_TABLE is used by UPDATE_LONGVARBINARY_PROC
+ { "LONGVARBINARY_TABLE",
+ "CREATE TABLE LONGVARBINARY_TABLE (lvbc Long varchar for bit data)" },
+
+ // NUMERIC_BOUNDARIES_TABLE is used by NUMERIC_BOUNDARIES_PROC
+ { "NUMERIC_BOUNDARIES_TABLE",
+ "CREATE TABLE NUMERIC_BOUNDARIES_TABLE " +
+ "(maxcol NUMERIC(31,15), mincol NUMERIC(15,15), nulcol NUMERIC)"},
+ };
+
+ /**
+ * Creates a new <code>CallableTest</code> instance.
+ *
+ * @param name name of the test
+ */
+ public CallableTest(String name) {
+ super(name);
+ }
+
+ public static Test suite() {
+ // return TestConfiguration.defaultSuite(CallableTest.class);
+ TestSuite suite = new TestSuite("CallableTest");
+
+ suite.addTest(baseSuite("CallableTest:embedded"));
+
+ suite.addTest(TestConfiguration.clientServerDecorator(
+ baseSuite("CallableTest:client")));
+ return suite;
+ }
+ private static Test baseSuite(String name) {
+
+ TestSuite suite = new TestSuite(name);
+
+ // Add tests that every JVM should be able to run.
+ suite.addTestSuite(CallableTest.class);
+
+ // Add tests that require JDBC 2
+ if (JDBC.vmSupportsJDBC2()) {
+
+ // Tests that require DriverManager.
+ suite.addTest
+ (new CallableTest("xtestUpdateLongBinaryProc"));
+
+ // Tests that get/set BigDecimal
+ suite.addTest
+ (new CallableTest("xtestBigDecimalInAndOutProc"));
+ suite.addTest
+ (new CallableTest("xtestNumericTypesInAndOutProc"));
+
+ // Test that both requires DriverManager and BigDecimal
+ suite.addTest
+ (new CallableTest("xtestNumericBoundariesProc"));
+ }
+
+ // Tests that can't run under DB2 client jdbc driver.
+ if (!usingDerbyNet()) {
+ suite.addTest
+ (new CallableTest("xtestRegUserDefOutParameterError"));
+ }
+
+ return new CleanDatabaseTestSetup(suite)
+ {
+ /**
+ * Creates the tables and the stored procedures used in the test
+ * cases.
+ * @throws SQLException
+ */
+ protected void decorateSQL(Statement s) throws SQLException
+ {
+ for (int i = 0; i < ROUTINES.length; i++) {
+ s.execute(ROUTINES[i]);
+ }
+ for (int i = 0; i < TABLES.length; i++) {
+ s.execute(TABLES[i][1]);
+ }
+ }
+ };
+ } // End baseSuite
+
+ /**
+ * Sets up the connection for a test case and clears all tables
+ * used in the test cases.
+ * @throws SQLException
+ */
+ public void setUp() throws SQLException
+ {
+ Connection conn = getConnection();
+ conn.setAutoCommit(false);
+ Statement s = createStatement();
+ for (int i = 0; i < TABLES.length; i++) {
+ s.execute("DELETE FROM " + TABLES[i][0]);
+ }
+ s.close();
+ conn.commit();
+ }
+
+ // TESTS
+
+ /**
+ * Calls a SQL procedure with two input parameters and one output.
+ * @throws SQLException
+ */
+ public void testTwoInOneOutProc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("call TWO_IN_ONE_OUT_PROC (?, ?, ?)");
+ cs.setInt(1, 6);
+ cs.setInt(2, 9);
+ cs.registerOutParameter (3, java.sql.Types.INTEGER);
+ cs.execute();
+ assertEquals("Sum of 6 and 9", 15, cs.getInt(3));
+ cs.close();
+ }
+
+ /**
+ * Calls a SQL function with one input parameter and one output.
+ * @throws SQLException
+ */
+ public void testOneInOneOutFunc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("? = call ONE_IN_ONE_OUT_FUNC (?)");
+ cs.registerOutParameter (1, java.sql.Types.INTEGER);
+ cs.setInt(2, 6);
+ cs.execute();
+ assertEquals("Square of 6 then plus 6", 42, cs.getInt(1));
+ cs.close();
+ }
+
+ /**
+ * Calls a SQL function that takes no input parameter and returns one
+ * output.
+ * @throws SQLException
+ */
+ public void testNoInOneOutFunc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("? = call NO_IN_ONE_OUT_FUNC()");
+ cs.registerOutParameter (1, java.sql.Types.INTEGER);
+ cs.execute();
+ assertEquals("NO_IN_ONE_OUT_FUNC output value", 55, cs.getInt(1));
+ cs.close();
+ }
+
+ /**
+ * Calls a SQL procedure that outputs a message with System.out.println.
+ * Converted from the original test, but initially disabled because of the
+ * message output to system out. Easily enabled by changing method name to
+ * remove the initial "norun_" (the name becomes testSystemOutPrintlnProc).
+ * @throws SQLException
+ */
+ public void norun_testSystemOutPrintlnProc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("call SYSTEM_OUT_PRINTLN_PROC()");
+ cs.execute();
+ cs.close();
+ }
+
+ /**
+ * Calls a SQL procedure that takes numeric IN and OUT parameters.
+ * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
+ * @throws SQLException
+ */
+ public void xtestNumericTypesInAndOutProc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("call NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");
+
+ cs.setShort(1, (short) 3);
+ cs.setInt(2, 4);
+ cs.setLong(3, 5);
+ cs.setFloat(4, (float) 6.0);
+ cs.setDouble(5, 7.0);
+ cs.setBigDecimal(6, new BigDecimal("88.88"));
+
+ cs.registerOutParameter (7, java.sql.Types.SMALLINT);
+ cs.registerOutParameter (8, java.sql.Types.INTEGER);
+ cs.registerOutParameter (9, java.sql.Types.BIGINT);
+ cs.registerOutParameter (10, java.sql.Types.REAL);
+ cs.registerOutParameter (11, java.sql.Types.DOUBLE);
+ cs.registerOutParameter (12, java.sql.Types.DECIMAL);
+
+ cs.execute();
+
+ assertEquals("OUT short", (short) 3, cs.getShort(7));
+ assertEquals("OUT int" , 4, cs.getInt(8));
+ assertEquals("OUT long" , 5, cs.getLong(9));
+ assertEquals("OUT float" , (float) 6.0, cs.getFloat(10), .0001);
+ assertEquals("OUT double" , 7.0, cs.getDouble(11), .0001);
+ assertDecimalSameValue("OUT decimal", "88.88", cs.getBigDecimal(12));
+
+ cs.close();
+ }
+
+ /**
+ * Calls a SQL procedure that takes non-numeric IN and OUT parameters.
+ * @throws SQLException
+ */
+ public void testNonNumericTypesInAndOutProc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");
+
+ cs.setDate(1, Date.valueOf("2002-05-12"));
+ cs.setTime(2, Time.valueOf("10:05:02"));
+ cs.setTimestamp(3, Timestamp.valueOf("2002-05-12 10:05:02.000000000"));
+ byte[] ba = new byte[2];
+ ba[0] = 1;
+ ba[1] = 2;
+ cs.setBytes(4, ba);
+
+ cs.registerOutParameter (5, java.sql.Types.DATE);
+ cs.registerOutParameter (6, java.sql.Types.TIME);
+ cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
+ cs.registerOutParameter (8, java.sql.Types.VARBINARY);
+
+ cs.execute();
+
+ assertEquals("OUT date", Date.valueOf("2002-05-12"), cs.getDate(5));
+ assertEquals("OUT time" , Time.valueOf("10:05:02"), cs.getTime(6));
+ assertEquals("OUT timestamp" ,
+ Timestamp.valueOf("2002-05-12 10:05:02.000000000"),
+ cs.getTimestamp(7));
+ assertTrue(Arrays.equals(ba, cs.getBytes(8)));
+
+ cs.close();
+ }
+
+ /**
+ * Calls a SQL procedure that takes INOUT parameters of various types.
+ * @throws SQLException
+ */
+ public void testManyTypesInoutProc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("call MANY_TYPES_INOUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");
+
+ cs.registerOutParameter (2, java.sql.Types.SMALLINT);
+ cs.registerOutParameter (4, java.sql.Types.INTEGER);
+ cs.registerOutParameter (6, java.sql.Types.BIGINT);
+ cs.registerOutParameter (8, java.sql.Types.REAL);
+ cs.registerOutParameter (10, java.sql.Types.DOUBLE);
+ cs.registerOutParameter (12, java.sql.Types.TIME);
+
+ cs.setShort(1, (short)6);
+ cs.setShort(2, (short)9);
+ cs.setInt(3, 6);
+ cs.setInt(4, 9);
+ cs.setLong(5, (long)99999);
+ cs.setLong(6, (long)88888888);
+ cs.setFloat(7, (float)6.123453);
+ cs.setFloat(8, (float)77777);
+ cs.setDouble(9, (double)6.123453);
+ cs.setDouble(10, (double)8888888888888.01234);
+ cs.setTime(11, Time.valueOf("11:06:03"));
+ cs.setTime(12, Time.valueOf("10:05:02"));
+
+ cs.execute();
+
+ assertEquals("Short: Sum of 6 + 9", 15, cs.getShort(2));
+ assertEquals("Int: Sum of 6 + 9", 15, cs.getInt(4));
+ assertEquals("Long: Sum of 99999 + 88888888", 88988887, cs.getLong(6));
+ assertEquals("Float: Sum of 6.123453 and 77777" , (float) 77783.123453,
+ cs.getFloat(8), .000001);
+ assertEquals("Double: Sum of Sum of 6.987654 and 8888888888888.01234",
+ 8.888888888894135e12, cs.getDouble(10), .000001);
+ assertEquals("Time: changed to", Time.valueOf("11:06:03"),
+ cs.getTime(12));
+
+ cs.close();
+ }
+
+ /**
+ * Tries to register a user-defined OUT parameter, which isn't supported
+ * and is expected to throw a "not implemented" exception.
+ * DERBY-1184 fixed the behavior in the Derby Network Client to throw an
+ * exception; however, DB2 Client behavior is incorrect, so the suite
+ * excludes this test when run with DB2 Client.
+ * @throws SQLException
+ */
+ public void xtestRegUserDefOutParameterError() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("? = call NO_IN_ONE_OUT_FUNC()");
+
+ try {
+ cs.registerOutParameter (1, java.sql.Types.INTEGER, "user-def");
+ fail("FAIL - Shouldn't reach here. Functionality not implemented.");
+ } catch (SQLException se) {
+ assertSQLState(NOT_IMPLEMENTED, se);
+ }
+
+ cs.close();
+ }
+
+ /**
+ * Calls a SQL procedure that updates a long varbinary column.
+ * Uses DriverManager, so this test requires JDBC 2 DriverManager support.
+ * @throws SQLException
+ */
+ public void xtestUpdateLongBinaryProc() throws SQLException
+ {
+ // Insert a row with an initial value that will be updated later.
+ Statement stmt = createStatement();
+ stmt.executeUpdate(
+ "INSERT INTO LONGVARBINARY_TABLE VALUES(X'010305')");
+
+ // Build up a byte array that will replace the initial value.
+ int bytearrsize = 50;
+ byte[] bytearr=new byte[bytearrsize];
+ String sbyteval=null;
+ for (int count=0;count<bytearrsize;count++)
+ {
+ sbyteval=Integer.toString(count%255);
+ bytearr[count]=Byte.parseByte(sbyteval);
+ }
+
+ // Update the value in the database.
+ CallableStatement cstmt = prepareCall(
+ "CALL UPDATE_LONGVARBINARY_PROC(?)");
+ cstmt.setObject(1,bytearr,java.sql.Types.LONGVARBINARY);
+ cstmt.executeUpdate();
+
+ // Retrieve the updated value and verify it's correct.
+ ResultSet rs = stmt.executeQuery(
+ "SELECT LVBC FROM LONGVARBINARY_TABLE");
+ assertNotNull("SELECT from LONGVARBINARY_TABLE", rs);
+
+ while (rs.next())
+ {
+ byte[] retvalue = (byte[]) rs.getObject(1);
+ assertTrue(Arrays.equals(bytearr, retvalue));
+ }
+ rs.close();
+ stmt.close();
+ cstmt.close();
+ }
+
+ /**
+ * Calls a SQL procedure that populates OUT parameters with minimum,
+ * maximum, and null values fetched from a table with numeric columns.
+ * Pre-history: long, long ago this test was added to exercise a problem
+ * with converting BigDecimal to packed decimal, which left the Network
+ * Server cpu bound.
+ * Excluded from environments than don't have JDBC 2 DriverManager.
+ * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
+ * @throws SQLException
+ */
+ public void xtestNumericBoundariesProc() throws SQLException
+ {
+ // Populate the test table
+ String SqlStatement=
+ "insert into NUMERIC_BOUNDARIES_TABLE " +
+ "values(999999999999999, 0.000000000000001, null)";
+ Statement stmt = createStatement();
+ stmt.executeUpdate(SqlStatement);
+
+ // SELECT the values back by calling the SQL procedure.
+ CallableStatement cstmt = prepareCall(
+ "CALL NUMERIC_BOUNDARIES_PROC(?,?,?)");
+ cstmt.registerOutParameter(1,java.sql.Types.NUMERIC,15);
+ cstmt.registerOutParameter(2,java.sql.Types.NUMERIC,15);
+ cstmt.registerOutParameter(3,java.sql.Types.NUMERIC,15);
+
+ cstmt.execute();
+
+ assertDecimalSameValue("OUT 1", "999999999999999.000000000000000",
+ cstmt.getBigDecimal(1));
+ assertDecimalSameValue("OUT 2", "0.000000000000001",
+ cstmt.getBigDecimal(2));
+ assertNull("Expected OUT 3 to be null", cstmt.getBigDecimal(3));
+
+ stmt.close();
+ cstmt.close();
+ }
+
+ /**
+ * Calls a SQL procedure with BigDecimal IN and OUT parameters.
+ * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
+ * @throws SQLException
+ */
+ public void xtestBigDecimalInAndOutProc() throws SQLException
+ {
+ CallableStatement cs = getConnection().prepareCall
+ ("CALL BIGDECIMAL_IN_AND_OUT_PROC (?, ?, ?, ?, ?, ?, ?, ?, ?)");
+ cs.setBigDecimal(1, new BigDecimal("33.333"));
+ cs.registerOutParameter (2, java.sql.Types.DECIMAL);
+ cs.setBigDecimal(3, new BigDecimal("-999.999999"));
+ cs.registerOutParameter (4, java.sql.Types.DECIMAL);
+ cs.registerOutParameter (5, java.sql.Types.DECIMAL);
+ cs.registerOutParameter (6, java.sql.Types.DECIMAL);
+ cs.registerOutParameter (7, java.sql.Types.DECIMAL);
+ cs.registerOutParameter (8, java.sql.Types.DECIMAL);
+ cs.registerOutParameter (9, java.sql.Types.DECIMAL);
+ cs.execute();
+
+ assertDecimalSameValue("OUT 2", "33.3330", cs.getBigDecimal(2));
+ assertDecimalSameValue("OUT 4", "-33332.9966", cs.getBigDecimal(4));
+ assertDecimalSameValue("OUT 5", "-966.6669", cs.getBigDecimal(5));
+ assertDecimalSameValue("OUT 6", "0.0000", cs.getBigDecimal(6));
+ assertDecimalSameValue("OUT 7", "0.0000", cs.getBigDecimal(7));
+ assertDecimalSameValue("OUT 8", "99999999.0000", cs.getBigDecimal(8));
+ assertDecimalSameValue("OUT 9", "-99999999.0000", cs.getBigDecimal(9));
+
+ cs.close();
+ }
+
+ /**
+ * Wrapper for BigDecimal compareTo.
+ * Called by the xtestBigDecimalInAndOutProc,
+ * xtestNumericTypesInAndOutProc, and
+ * xtestNumericBoundariesProc, methods.
+ */
+ public void assertDecimalSameValue(String msg, String val1, BigDecimal val2)
+ {
+ BigDecimal expected = (new BigDecimal(val1));
+ assertTrue(msg +
+ " expected:<" + val1 + "> but was:<" + val2.toString() + ">",
+ expected.compareTo(val2)==0);
+ }
+
+ // SQL ROUTINES (functions and procedures)
+
+ /**
+ * SQL function that squares the value of the input arg, then adds the
+ * input arg to that result.
+ *
+ * @param p1 integer input argument to be used in calculation
+ */
+ public static int oneInOneOutFunc (int p1)
+ {
+ return (p1 * p1) + p1;
+ }
+
+ /**
+ * SQL procedure that sets the value of the third arg to the sum of the
+ * first two.
+ *
+ * @param p1 integer input parameter to be used in calculation
+ * @param p2 integer input parameter to be used in calculation
+ * @param p3 integer output parameter that stores result of the calculation
+ */
+ public static void twoInOneOutProc (int p1, int p2, int[] p3)
+ {
+ p3[0] = p1 + p2;
+ }
+
+ /**
+ * SQL function that takes no parameters and returns the value 55.
+ */
+ public static int noInOneOutFunc ()
+ {
+ return 55;
+ }
+
+ /**
+ * SQL procedure that outputs a message.
+ */
+ public static void systemOutPrintlnProc()
+ {
+ System.out.println("I'm doing something here...");
+ }
+
+ /**
+ * SQL procedure that sets the value of the Long varbinary column in the
+ * LONGVARBINARY_TABLE table given the input parameter.
+ *
+ * @param in_param input parameter to be used for database update
+ * @exception SQLException if a database error occurs
+ */
+ public static void updateLongVarbinaryProc (byte[] in_param)
+ throws SQLException
+ {
+ Connection conn =
+ DriverManager.getConnection("jdbc:default:connection");
+ PreparedStatement ps =
+ conn.prepareStatement("update LONGVARBINARY_TABLE set lvbc=?");
+
+ ps.setBytes(1,in_param);
+ ps.executeUpdate();
+
+ ps.close();
+ conn.close();
+ }
+
+ /**
+ * SQL procedure that fetches max, min, and null values from a table
+ * with numeric columns.
+ *
+ * @param in_param input parameter to be used for database update
+ * @exception SQLException if a database error occurs
+ */
+ public static void numericBoundariesProc (BigDecimal[] param1,
+ BigDecimal[] param2, BigDecimal[] param3) throws SQLException
+ {
+ Connection conn =
+ DriverManager.getConnection("jdbc:default:connection");
+ Statement stmt = conn.createStatement();
+
+ ResultSet rs = stmt.executeQuery
+ ("select maxcol, mincol, nulcol from NUMERIC_BOUNDARIES_TABLE");
+
+ if (rs.next())
+ {
+ param1[0]=rs.getBigDecimal(1);
+ param2[0]=rs.getBigDecimal(2);
+ param3[0]=rs.getBigDecimal(3);
+ }
+ else
+ {
+ throw new SQLException("Data not found");
+ }
+
+ rs.close();
+ stmt.close();
+ conn.close();
+ }
+
+
+ /**
+ * SQL procedure that tests INT and OUT parameters with the BigDecimal
+ * data type.
+ *
+ * @param bd1 input parameter
+ * @param bdr1 output parameter set to bd1 * bd2
+ * @param bd2 input parameter
+ * @param bdr2 output parameter set to bd1 + bd2
+ * @param bdr3 output parameter set to a fixed value
+ * @param bdr4 output parameter set to a fixed value
+ * @param bdr5 output parameter set to a fixed value
+ * @param bdr6 output parameter set to a fixed value
+ * @param bdr7 output parameter set to a fixed value
+ *
+ */
+ public static void bigDecimalInAndOutProc (BigDecimal bd1,
+ BigDecimal bdr1[], BigDecimal bd2, BigDecimal bdr2[],
+ BigDecimal bdr3[], BigDecimal bdr4[], BigDecimal bdr5[],
+ BigDecimal bdr6[], BigDecimal bdr7[])
+ {
+ bdr1[0] = bd1;
+ bdr2[0] = bd1.multiply(bd2);
+ bdr3[0] = bd1.add(bd2);
+ bdr4[0] = new BigDecimal(".00000");
+ bdr5[0] = new BigDecimal("-.00000");
+ bdr6[0] = new BigDecimal("99999999.");
+ bdr7[0] = new BigDecimal("-99999999.");
+ }
+
+ /**
+ * SQL procedure that tests IN and OUT parameters with many numeric types.
+ * Also tests method overload for manyTypesInAndOutProc.
+ *
+ * @param s short input parameter
+ * @param i int input parameter
+ * @param l long input parameter
+ * @param f float input parameter
+ * @param d double input parameter
+ * @param bd BigDecimal input parameter
+ * @param sr short output parameter
+ * @param ir int output parameter
+ * @param lr long output parameter
+ * @param fr float output parameter
+ * @param dr double output parameter
+ * @param bdr BigDecimal output parameter
+ *
+ */
+ public static void manyTypesInAndOutProc (
+ short s, int i, long l, float f, double d, BigDecimal bd,
+ short[] sr, int[] ir, long[] lr,float[] fr,double[] dr,BigDecimal[] bdr
+ )
+ {
+ sr[0] = s;
+ ir[0] = i;
+ lr[0] = l;
+ fr[0] = f;
+ dr[0] = d;
+ bdr[0] = bd;
+ }
+
+ /**
+ * SQL procedure that tests IN / OUT parameters with many non-numeric types.
+ * Also tests method overload for manyTypesInAndOutProc.
+ *
+ * @param dt date input parameter
+ * @param t time input parameter
+ * @param ts timestamp input parameter
+ * @param ba byte input parameter
+ * @param dtr date output parameter
+ * @param tr time output parameter
+ * @param tsr timestamp output parameter
+ * @param bar byte output parameter
+ *
+ */
+ public static void manyTypesInAndOutProc (
+ Date dt, Time t, Timestamp ts, byte[] ba,
+ Date[] dtr, Time[] tr, Timestamp[] tsr, byte[][] bar
+ )
+ {
+ dtr[0] = dt;
+ tr[0] = t;
+ tsr[0] = ts;
+ bar[0] = ba;
+ }
+
+ /**
+ * SQL procedure that tests INOUT parameters with many types.
+ *
+ * @param s1 short input parameter
+ * @param s2 short output parameter
+ * @param p1 int input parameter
+ * @param p2 int output parameter
+ * @param l1 long input parameter
+ * @param l2 long output parameter
+ * @param f1 float input parameter
+ * @param f2 float output parameter
+ * @param d1 double input parameter
+ * @param d2 double output parameter
+ * @param 11 time input parameter
+ * @param 12 time output parameter
+ */
+ public static void manyTypesInoutProc (
+ short s1, short s2[], int p1, int p2[], long l1, long l2[],
+ float f1, float f2[], double d1, double d2[], Time t1, Time t2[]
+ )
+ {
+ p2[0] = p1 + p2[0];
+ s2[0] = (short) (s1 + s2[0]);
+ l2[0] = l1 + l2[0];
+ f2[0] = f1 + f2[0];
+ d2[0] = d1 + d2[0];
+ t2[0] = t1;
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java
------------------------------------------------------------------------------
svn:executable = *
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java?view=diff&rev=512905&r1=512904&r2=512905
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java Wed Feb 28 11:08:22 2007
@@ -63,6 +63,7 @@
suite.addTest(StreamTest.suite());
suite.addTest(ParameterMappingTest.suite());
suite.addTest(DboPowersTest.suite());
+ suite.addTest(CallableTest.suite());
// Old harness .java tests that run using the HarnessJavaTest
// adapter and continue to use a single master file.