Author: kwilliams
Date: Fri Apr 7 14:22:04 2006
New Revision: 392408
URL: http://svn.apache.org/viewcvs?rev=392408&view=rev
Log:
Adding Stored Proc test using IN/OUT parameters
Modified:
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/StoredProcs.java
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java
Modified:
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/StoredProcs.java
URL:
http://svn.apache.org/viewcvs/incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/StoredProcs.java?rev=392408&r1=392407&r2=392408&view=diff
==============================================================================
---
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/StoredProcs.java
(original)
+++
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/StoredProcs.java
Fri Apr 7 14:22:04 2006
@@ -16,13 +16,10 @@
*/
package org.apache.tuscany.das.rdb.test;
-/*
- * Generated IDs
- *
- *
- */
import org.apache.tuscany.das.rdb.Command;
+import org.apache.tuscany.das.rdb.Parameter;
+import org.apache.tuscany.das.rdb.SDODataTypes;
import org.apache.tuscany.das.rdb.test.data.CompanyData;
import org.apache.tuscany.das.rdb.test.data.CustomerData;
import org.apache.tuscany.das.rdb.test.data.OrderData;
@@ -110,7 +107,7 @@
* customers with that last name. The number of read customers is
returned
* in the out parameter
*/
-/* public void testGetNamedCustomers() throws Exception {
+ public void testGetNamedCustomers() throws Exception {
Command read = Command.FACTORY.createCommand("{call
GETNAMEDCUSTOMERS(?,?)}");
read.setConnection(getConnection());
@@ -124,7 +121,7 @@
assertEquals(customersRead.intValue(),
root.getList("CUSTOMER").size());
}
- */
+
//TODO - Resolve issue with programmatic creation of GETNAMEDCUSTOMERS
on DB2 and
//re-enable this test
@@ -132,7 +129,6 @@
// Simplest possible SP write
public void testDelete() throws Exception {
-// JDBCDASCommand delete =
JDBCDASCommand.FACTORY.createSPWriteCommand("{call DELETECUSTOMER(?)}");
Command delete = Command.FACTORY.createCommand("{call
DELETECUSTOMER(?)}");
delete.setConnection(getConnection());
delete.setParameterValue(1, new Integer(1));
Modified:
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java
URL:
http://svn.apache.org/viewcvs/incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java?rev=392408&r1=392407&r2=392408&view=diff
==============================================================================
---
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java
(original)
+++
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/DatabaseSetup.java
Fri Apr 7 14:22:04 2006
@@ -90,13 +90,14 @@
private void dropTables() {
- System.out.println("Dropping tables");
+// System.out.println("Dropping tables");
String[] statements = {
- "DROP TABLE CUSTOMER", "DROP TABLE ANORDER", "DROP TABLE
ORDERDETAILS", "DROP TABLE ITEM", "DROP TABLE COMPANY",
- "DROP TABLE EMPLOYEE", "DROP TABLE DEPARTMENT", "DROP TABLE
BOOK", "DROP TABLE PART", "DROP TABLE TYPETEST",
- "DROP TABLE CITIES", "DROP TABLE STATES", "DROP TABLE
conmgt.SERVERSTATUS"
+ "DROP TABLE CUSTOMER", "DROP TABLE ANORDER", "DROP TABLE
ORDERDETAILS", "DROP TABLE ITEM",
+ "DROP TABLE COMPANY", "DROP TABLE EMPLOYEE", "DROP TABLE
DEPARTMENT", "DROP TABLE BOOK",
+ "DROP TABLE PART", "DROP TABLE TYPETEST", "DROP TABLE CITIES",
"DROP TABLE STATES",
+ "DROP TABLE conmgt.SERVERSTATUS"
};
@@ -104,8 +105,8 @@
try {
s.execute(statements[i]);
} catch (SQLException e) {
- //If the table does not exist then ignore the exception on drop
- if (!e.getMessage().contains("does not exist"))
+ // If the table does not exist then ignore the exception on
drop
+ if (!e.getMessage().contains("does not exist"))
throw new RuntimeException(e);
}
}
@@ -113,13 +114,12 @@
protected void dropProcedures() {
- System.out.println("Dropping procedures");
+// System.out.println("Dropping procedures");
String[] statements = {
"DROP PROCEDURE GETALLCOMPANIES", "DROP PROCEDURE DELETECUSTOMER",
"DROP PROCEDURE GETNAMEDCOMPANY",
- "DROP PROCEDURE GETCUSTOMERANDORDERS",
- // "DROP PROCEDURE GETNAMEDCUSTOMERS"
+ "DROP PROCEDURE GETCUSTOMERANDORDERS", "DROP PROCEDURE
GETNAMEDCUSTOMERS"
};
@@ -127,8 +127,8 @@
try {
s.execute(statements[i]);
} catch (SQLException e) {
- //If the proc does not exist then ignore the exception on drop
- if (!e.getMessage().contains("does not exist"))
+ // If the proc does not exist then ignore the exception on drop
+ if (!e.getMessage().contains("does not exist"))
throw new RuntimeException(e);
}
}
@@ -136,7 +136,7 @@
private void createTables() {
- System.out.println("Creating tables");
+// System.out.println("Creating tables");
try {
@@ -161,22 +161,15 @@
protected void createProcedures() {
- System.out.println("Creating procedures");
+// System.out.println("Creating procedures");
try {
- s
- .execute("CREATE PROCEDURE GETALLCOMPANIES() PARAMETER
STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getAllCompanies'");
- s
- .execute("CREATE PROCEDURE DELETECUSTOMER(theId int)
PARAMETER STYLE JAVA LANGUAGE JAVA MODIFIES SQL DATA EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.deleteCustomer'");
- s
- .execute("CREATE PROCEDURE GETNAMEDCOMPANY(theName
VARCHAR(100)) PARAMETER STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT
SETS 1 EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getNamedCompany'");
- s
- .execute("CREATE PROCEDURE GETCUSTOMERANDORDERS(theID
INTEGER) PARAMETER STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT SETS
1 EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getCustomerAndOrders'");
- // TODO s.execute("CREATE PROCEDURE GETNAMEDCUSTOMERS(theName
- // VARCHAR(100), OUT theCount INTEGER) PARAMETER STYLE JAVA
LANGUAGE
- // JAVA READS SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME
- // 'tests.framework.JavaStoredProcs.getNamedCustomers'");
- // This is failing on DB2 with SQLCODE: 42723. Need to investigate
+ s.execute("CREATE PROCEDURE GETALLCOMPANIES() PARAMETER STYLE JAVA
LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getAllCompanies'");
+ s.execute("CREATE PROCEDURE DELETECUSTOMER(theId int) PARAMETER
STYLE JAVA LANGUAGE JAVA MODIFIES SQL DATA EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.deleteCustomer'");
+ s.execute("CREATE PROCEDURE GETNAMEDCOMPANY(theName VARCHAR(100))
PARAMETER STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT SETS 1
EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getNamedCompany'");
+ s.execute("CREATE PROCEDURE GETCUSTOMERANDORDERS(theID INTEGER)
PARAMETER STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT SETS 1
EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getCustomerAndOrders'");
+ s.execute("CREATE PROCEDURE GETNAMEDCUSTOMERS(theName
VARCHAR(100), OUT theCount INTEGER) PARAMETER STYLE JAVA LANGUAGE JAVA READS
SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME
'org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getNamedCustomers'");
+ // TODO - "GETNAMEDCUSTOMERS" is failing on DB2 with SQLCODE:
42723. Need to investigate
} catch (SQLException e) {
throw new RuntimeException(e);
}
Modified:
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java
URL:
http://svn.apache.org/viewcvs/incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java?rev=392408&r1=392407&r2=392408&view=diff
==============================================================================
---
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java
(original)
+++
incubator/tuscany/java/das/rdb/src/test/java/org/apache/tuscany/das/rdb/test/framework/JavaStoredProcs.java
Fri Apr 7 14:22:04 2006
@@ -24,62 +24,56 @@
/**
* Stored Procedures for DB2 and Derby SP tests
- *
+ *
*/
public class JavaStoredProcs {
- public static void getAllCompanies(ResultSet[] results) throws
SQLException {
+ public static void getAllCompanies(ResultSet[] results) throws
SQLException {
+
+ Connection conn =
DriverManager.getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn.prepareStatement("SELECT * FROM COMPANY");
+ results[0] = ps.executeQuery();
+ }
+
+ public static void deleteCustomer(int theId) throws SQLException {
+
+ Connection conn =
DriverManager.getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn.prepareStatement("DELETE FROM CUSTOMER
WHERE ID = ?");
+ ps.setInt(1, theId);
+ ps.execute();
+
+ }
+
+ public static void getNamedCompany(String theName, ResultSet[] results)
throws SQLException {
+
+ Connection conn =
DriverManager.getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn.prepareStatement("SELECT * FROM COMPANY
WHERE NAME = ?");
+ ps.setString(1, theName);
+ results[0] = ps.executeQuery();
+ }
+
+ public static void getCustomerAndOrders(int theId, ResultSet[] results)
throws SQLException {
+
+ Connection conn =
DriverManager.getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn
+ .prepareStatement("SELECT * FROM CUSTOMER LEFT JOIN ANORDER ON
CUSTOMER.ID = ANORDER.CUSTOMER_ID WHERE CUSTOMER.ID = ?");
+ ps.setInt(1, theId);
+ results[0] = ps.executeQuery();
+ }
+
+ public static void getNamedCustomers(String theName, int[] outCount,
ResultSet[] results) throws SQLException {
+
+ Connection conn =
DriverManager.getConnection("jdbc:default:connection");
+ PreparedStatement ps = conn.prepareStatement("SELECT * FROM CUSTOMER
WHERE LASTNAME = ?");
+ ps.setString(1, theName);
+ results[0] = ps.executeQuery();
+
+ ps = conn.prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE
LASTNAME = ?");
+ ps.setString(1, theName);
+
+ ResultSet rs = ps.executeQuery();
+ rs.next();
+ outCount[0] = rs.getInt(1);
+ }
- Connection conn = DriverManager
- .getConnection("jdbc:default:connection");
- PreparedStatement ps = conn.prepareStatement("SELECT * FROM
COMPANY");
- results[0] = ps.executeQuery();
- }
-
- public static void deleteCustomer(int theId) throws SQLException {
-
- Connection conn = DriverManager
- .getConnection("jdbc:default:connection");
- PreparedStatement ps = conn.prepareStatement("DELETE FROM
CUSTOMER WHERE ID = ?");
- ps.setInt(1, theId);
- ps.execute();
-
- }
-
- public static void getNamedCompany(String theName, ResultSet[] results)
throws SQLException {
-
- Connection conn = DriverManager
- .getConnection("jdbc:default:connection");
- PreparedStatement ps = conn.prepareStatement("SELECT * FROM
COMPANY WHERE NAME = ?");
- ps.setString(1, theName);
- results[0] = ps.executeQuery();
- }
-
- public static void getCustomerAndOrders(int theId, ResultSet[] results)
throws SQLException {
-
- Connection conn = DriverManager
- .getConnection("jdbc:default:connection");
- PreparedStatement ps =
- conn.prepareStatement("SELECT * FROM CUSTOMER LEFT JOIN
ANORDER ON CUSTOMER.ID = ANORDER.CUSTOMER_ID WHERE CUSTOMER.ID = ?");
- ps.setInt(1, theId);
- results[0] = ps.executeQuery();
- }
-
- public static void getNamedCustomers(String theName, int[] outCount,
ResultSet[] results) throws SQLException {
-
- Connection conn = DriverManager
- .getConnection("jdbc:default:connection");
- PreparedStatement ps =
- conn.prepareStatement("SELECT * FROM CUSTOMER WHERE
LASTNAME = ?");
- ps.setString(1, theName);
- results[0] = ps.executeQuery();
-
- ps = conn.prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE
LASTNAME = ?");
- ps.setString(1, theName);
-
- ResultSet rs = ps.executeQuery();
- rs.next();
- outCount[0] = rs.getInt(1);
- }
-
}
