/*
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.BatchUpdateException;
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, and
* different data types.
*/
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",
"CREATE PROCEDURE BATCH_UPDATE_PROC " +
"(P1 INT, P2 INT) MODIFIES SQL DATA " +
"LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '" +
CallableTest.class.getName() + ".batchUpdateProc'"
};
/**
* 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)"},
// BATCH_UPDATE is used by BATCH_UPDATE_PROC
{ "BATCH_TABLE",
"CREATE TABLE BATCH_TABLE " +
"(id int, tag varchar(32), " +
"idval int constraint idval_ck check (idval >= 0))"},
};
/**
* 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 require DriverManager and batch update.
suite.addTest
(new CallableTest("xtestBatchUpdate"));
suite.addTest
(new CallableTest("xtestBatchUpdateError"));
// 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();
}
/**
* Batches up calls to a SQL procedure that updates a value in a table.
* Uses DriverManager and Batch calls, so requires JDBC 2 support.
* @throws SQLException
*/
public void xtestBatchUpdate() throws SQLException
{
// Setup table data
Statement stmt = createStatement();
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',10)");
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4a',0)");
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4b',0)");
// Setup batch to modify value to 10 * the id (makes verification easy).
CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
cstmt.setInt(1,2); // Id 2's value will be updated to 20.
cstmt.setInt(2,20);
cstmt.addBatch();
cstmt.setInt(1,3); // Id 3's value will be updated to 30.
cstmt.setInt(2,30);
cstmt.addBatch();
cstmt.setInt(1,4); // Two rows will be updated to 40 for id 4.
cstmt.setInt(2,40);
cstmt.addBatch();
cstmt.setInt(1,5); // No rows updated (no id 5).
cstmt.setInt(2,50);
cstmt.addBatch();
int[] updateCount=null;
try {
updateCount = cstmt.executeBatch();
assertEquals("updateCount length", 4, updateCount.length);
for(int i=0; i< updateCount.length; i++){
if (usingEmbedded()) {
assertEquals("Batch updateCount", 0, updateCount[0]);
}
else if (usingDerbyNetClient()) {
assertEquals("Batch updateCount", -1, updateCount[0]);
}
}
} catch (BatchUpdateException b) {
assertSQLState("Unexpected SQL State", b.getSQLState(), b);
}
// Retrieve the updated values and verify they are correct.
ResultSet rs = stmt.executeQuery(
"SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
assertNotNull("SELECT from BATCH_TABLE", rs);
while (rs.next())
{
assertEquals(rs.getString(2), rs.getInt(1)*10, rs.getInt(3));
}
rs.close();
stmt.close();
cstmt.close();
}
/**
* Batches up many calls to a SQL procedure that updates a value in a table.
* All calls should succeed, except for one that should fail with a check
* constraint violation.
* Uses DriverManager and Batch calls, so requires JDBC 2 support.
* @throws SQLException
*/
public void xtestBatchUpdateError() throws SQLException
{
// Setup table data
Statement stmt = createStatement();
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(1, 'STRING_1',0)");
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(2, 'STRING_2',0)");
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(3, 'STRING_3',0)");
stmt.executeUpdate("INSERT INTO BATCH_TABLE VALUES(4, 'STRING_4',0)");
// Setup batch to modify values.
CallableStatement cstmt = prepareCall("CALL BATCH_UPDATE_PROC(?,?)");
cstmt.setInt(1,1); // Set id 1's value to 10
cstmt.setInt(2,10);
cstmt.addBatch();
cstmt.setInt(1,2); // Set id 2's value to -5 (should fail)
cstmt.setInt(2,-5);
cstmt.addBatch();
cstmt.setInt(1,3); // Set id 3's value to 30.
cstmt.setInt(2,30);
cstmt.addBatch();
cstmt.setInt(1,4); // Set id 4's value to 40.
cstmt.setInt(2,40);
cstmt.addBatch();
int[] updateCount=null;
try {
updateCount = cstmt.executeBatch();
fail("Expected batchExecute to fail");
} catch (BatchUpdateException b) {
if (usingEmbedded()) {
assertSQLState("38000", b.getSQLState(), b);
}
else if (usingDerbyNetClient()) {
assertSQLState("XJ208", b.getSQLState(), b);
}
updateCount = b.getUpdateCounts();
/* The updateCount is different for embedded and client because
* the embedded driver stops processing the batch after the
* failure, while the client driver continues processing (see
* DERBY-2301).
*/
if (usingEmbedded()) {
assertEquals("updateCount length", 1, updateCount.length);
assertEquals("Batch updateCount", 0, updateCount[0]);
}
else if (usingDerbyNetClient()) {
assertEquals("updateCount length", 4, updateCount.length);
for(int i=0; i< updateCount.length; i++){
if(i == 1) // The second command in the batch failed.
assertEquals("Batch updateCount", -3, updateCount[i]);
else
assertEquals("Batch updateCount", -1, updateCount[i]);
}
}
}
// Make sure the right rows in the table were updated.
ResultSet rs = stmt.executeQuery(
"SELECT id, tag, idval FROM BATCH_TABLE order by id, tag");
assertNotNull("SELECT from BATCH_TABLE", rs);
while (rs.next())
{
/* Embedded and client results should be the same for the first
* two rows (the changed row for the first successful command in
* the batch, followed by the unchanged row for the second command,
* which failed).
* After the first two rows, results are different because the
* rest of the commands in the batch executed for client, but not
* for embedded.
*/
switch(rs.getInt(1))
{
case 1:
assertEquals(rs.getString(2), 10, rs.getInt(3));
break;
case 2:
assertEquals(rs.getString(2), 0, rs.getInt(3));
break;
default:
if (usingEmbedded()) {
assertEquals(rs.getString(2), 0, rs.getInt(3));
}
else if (usingDerbyNetClient()) {
assertEquals(rs.getString(2), rs.getInt(1)*10,
rs.getInt(3));
}
break;
}
}
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 message, String expected_s,
BigDecimal actual)
{
BigDecimal expected = (new BigDecimal(expected_s));
assertTrue(message +
" expected:<" + expected + "> but was:<" + actual.toString() + ">",
expected.compareTo(actual)==0);
}
// SQL ROUTINES (functions and procedures)
/**
* External code for the ONE_IN_ONE_OUT_FUNC SQL function, which 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;
}
/**
* External code for the TWO_IN_ONE_OUT_PROC SQL procedure, which 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;
}
/**
* External code for the NO_IN_ONE_OUT_FUNC SQL function, which takes no
* parameters and returns the value 55.
*/
public static int noInOneOutFunc ()
{
return 55;
}
/**
* External code for the SYSTEM_OUT_PRINTLN_PROC SQL procedure, which
* outputs a message to System out.
*/
public static void systemOutPrintlnProc()
{
System.out.println("I'm doing something here...");
}
/**
* External code for the UPDATE_LONGVARBINARY_PROC SQL procedure, which
* 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();
}
/**
* External code for the NUMERIC_BOUNDARIES_PROC SQL procedure, which
* fetches max, min, and null values from a table with numeric columns.
*
* @param param1 output parameter that returns maxcol value
* @param param2 output parameter that returns mincol value
* @param param3 output parameter that returns nulcol value
* @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();
}
/**
* External code for the BIGDECIMAL_IN_AND_OUT_PROC SQL procedure, which
* 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.");
}
/**
* External code for the NUMERIC_TYPES_IN_AND_OUT_PROC SQL procedure,
* which 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;
}
/**
* External code for the NON_NUMERIC_TYPES_IN_AND_OUT_PROC SQL procedure,
* which 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;
}
/**
* External code for the MANY_TYPES_INOUT_PROC SQL procedure, which 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 t1 time input parameter
* @param t2 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;
}
/**
* External code for the BATCH_UPDATE_PROC SQL procedure, which updates
* data in a table for a given id.
* Called by xtestBatchUpdateProc.
*
* @param id Id to be updated
* @param id_newval New value to which the id should be updated
* @exception SQLException if a database error occurs
*/
public static void batchUpdateProc (int id, int id_newval)
throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
PreparedStatement ps = conn.prepareStatement
("update BATCH_TABLE set idval=? where id=?");
ps.setInt(1, id_newval);
ps.setInt(2, id);
ps.executeUpdate();
ps.close();
conn.close();
}
}