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/03/03 02:22:39 UTC
svn commit: r514043 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java
Author: jta
Date: Fri Mar 2 17:22:38 2007
New Revision: 514043
URL: http://svn.apache.org/viewvc?view=rev&rev=514043
Log:
DERBY-2304 (partial) Convert derbynet/callable.java to JUnit.
The old testBatch, which was commented out, is replaced by two new junit tests:
- xtestBatchUpdate() batches up calls to a SQL procedure that updates a
row in a table.
- xtestBatchUpdateError() does what the first test does, only now one of
the commands in the batch tries to update the row to a value that violates
the constraint check.
The old test is mildly complicated, with a two table schema and primary/foreign
keys. It looked to me like the test had two goals: test a batch that includes
calls to a sql procedure, and test a batch that does an sql update that
violates a constraint. (Incidently, the sql update seemed out of place in a
file that otherwise tests sql procedures and functions.)
I simplified the test for the junit conversion, and it now uses a single table
with a check constraint on column to test what happens when the constraint is
violated. Also the constraint violation is split into a separate test and
tested in the context of a sql procedure call (in keeping with the rest of
the tests).
Left to do: remove derbynet/callable.java
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java
Modified: 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=diff&rev=514043&r1=514042&r2=514043
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/CallableTest.java Fri Mar 2 17:22:38 2007
@@ -27,6 +27,7 @@
import java.util.Arrays; // Used by testUpdateLongBinaryProc
+import java.sql.BatchUpdateException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
@@ -103,6 +104,16 @@
* Calls a SQL procedure with input and output parameters of the BigDecimal
* data type.
* Excluded from JSR169/j2ME tests because of the BigDecimal.
+ * <BR>
+ * xtestBatchUpdate()
+ * Batches up calls to a SQL procedure that updates a value in a table.
+ * Excluded from environments than don't have JDBC 2 DriverManager.
+ * <BR>
+ * xtestBatchUpdateError()
+ * Batches up many calls to a SQL procedure that updates a value in a table;
+ * one of the commands in the batch is expected to fail, but others should
+ * succeed.
+ * Excluded from environments than don't have JDBC 2 DriverManager.
*/
public class CallableTest extends BaseJDBCTestCase {
@@ -172,7 +183,12 @@
"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"
+ "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'"
};
/**
@@ -190,6 +206,12 @@
{ "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))"},
};
/**
@@ -225,6 +247,12 @@
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"));
@@ -522,6 +550,176 @@
}
/**
+ * 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
@@ -596,12 +794,13 @@
* xtestNumericTypesInAndOutProc, and
* xtestNumericBoundariesProc, methods.
*/
- public void assertDecimalSameValue(String msg, String val1, BigDecimal val2)
+ public void assertDecimalSameValue(String message, String expected_s,
+ BigDecimal actual)
{
- BigDecimal expected = (new BigDecimal(val1));
- assertTrue(msg +
- " expected:<" + val1 + "> but was:<" + val2.toString() + ">",
- expected.compareTo(val2)==0);
+ BigDecimal expected = (new BigDecimal(expected_s));
+ assertTrue(message +
+ " expected:<" + expected + "> but was:<" + actual.toString() + ">",
+ expected.compareTo(actual)==0);
}
// SQL ROUTINES (functions and procedures)
@@ -814,5 +1013,27 @@
f2[0] = f1 + f2[0];
d2[0] = d1 + d2[0];
t2[0] = t1;
+ }
+
+ /**
+ * SQL procedure that updates data in a table for a given id.
+ * Used by xtestBatchUpdateProc.
+ *
+ * @param pk_param BigDecimal id of the value to 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();
}
}