db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r547349 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/jdbcapi/
Date Thu, 14 Jun 2007 18:27:54 GMT
Author: kmarsden
Date: Thu Jun 14 11:27:53 2007
New Revision: 547349

URL: http://svn.apache.org/viewvc?view=rev&rev=547349
Log:
DERBY-2658 Convert jdbcapi/parameterMetaDataJdbc30.java to JUnit

Contributed by Ramin Moazeni


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMetaDataJdbc30Test.java
  (with props)
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/parameterMetaDataJdbc30.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/parameterMetaDataJdbc30.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/parameterMetaDataJdbc30_app.properties
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdk14.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/_Suite.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdk14.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdk14.runall?view=diff&rev=547349&r1=547348&r2=547349
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdk14.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdk14.runall
Thu Jun 14 11:27:53 2007
@@ -1,3 +1,2 @@
-jdbcapi/parameterMetaDataJdbc30.java
 jdbcapi/savepointJdbc30_JSR169.java
 jdbcapi/savepointJdbc30_XA.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMetaDataJdbc30Test.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMetaDataJdbc30Test.java?view=auto&rev=547349
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMetaDataJdbc30Test.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMetaDataJdbc30Test.java
Thu Jun 14 11:27:53 2007
@@ -0,0 +1,694 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test
+
+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.sql.Connection;
+import java.sql.Driver;
+import java.sql.DriverManager;
+import java.util.Properties;
+import java.sql.CallableStatement;
+import java.sql.ParameterMetaData;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.math.BigDecimal;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.BaseTestCase;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+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.TestCase;
+import junit.framework.TestResult;
+import junit.framework.TestSuite;
+
+/**
+ *
+ */
+public class ParameterMetaDataJdbc30Test extends BaseJDBCTestCase {
+
+        public ParameterMetaDataJdbc30Test(String arg0) {
+                super(arg0);
+        }
+        
+      	// to run all tests in this class
+	public static Test suite() {
+        	TestSuite suite = new TestSuite("ParameterMetaDataJdbc30Test");
+
+        	suite.addTest(baseSuite("ParameterMetaDataJdbc30Test:embedded"));
+
+        	suite.addTest(
+                	TestConfiguration.clientServerDecorator(
+                        	baseSuite("ParameterMetaDataJdbc30Test:client")));
+		
+        	return suite;
+    	}
+
+	private static Test baseSuite(String name)
+    	{
+        	TestSuite suite = new TestSuite(name);
+
+        	suite.addTestSuite(ParameterMetaDataJdbc30Test.class);
+
+        	return new CleanDatabaseTestSetup(suite) {
+			/**
+             		 * Creates the tables and the stored procedures used in the test
+             		 * cases.
+             		 * @exception SQLException if a database error occurs
+             		 */
+            		protected void decorateSQL(Statement stmt) throws SQLException {
+            		
+				Connection conn = getConnection();
+
+				/**
+		                 * Creates the table used in the test cases.
+               			 *
+                 		 */
+                		stmt.execute("create table t ( "+
+                          		/* 1 */ "c char(5), "+
+                          		/* 2 */ "iNoNull int not null, "+
+                          		/* 3 */ "i int, "+
+                          		/* 4 */ "de decimal, "+
+                          		/* 5 */ "d date)");
+                		stmt.executeUpdate("create function RDB(P1 INT) " +
+                                		   "RETURNS DECIMAL(10,2) " +
+                                  		   "language java external name " +
+                                  		   "'org.apache.derbyTesting.functionTests." +
+                                  		   "tests.lang.outparams30.returnsBigDecimal' " +
+                                   		   "parameter style java");
+            		}
+        	};
+  	}
+
+       /**
+     	* testing a callable statement.
+     	*/
+	public void testCallableStatement () throws SQLException {
+
+        	Statement stmt = createStatement();
+
+        	stmt.executeUpdate("create procedure dummyint( " + 
+				   "in a integer, in b integer, " + 
+				   "out c integer, inout d integer) " + 
+				   "language java external name "+ 
+				   "'org.apache.derbyTesting.functionTests." +
+				   "tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint' " +
+				   "parameter style java");
+       		CallableStatement cs = prepareCall("CALL dummyint(?,?,?,?)");
+		// parameters 1 and 2 are input only
+      		cs.setInt(1,1);
+      		cs.setInt(2,1);
+      		//parameter 3 is output only
+      		cs.registerOutParameter(3,Types.INTEGER);
+      		// parameter 4 is input and output
+      		Object x = new Integer(1);
+      		cs.setObject(4,x, Types.INTEGER);
+      		cs.registerOutParameter(4,Types.INTEGER);
+
+      		//verify the meta data for the parameters
+      		ParameterMetaData paramMetaData = cs.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array	
+		String [][] parameterMetaDataArray0 = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
+		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
+		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"},
+		{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"}};
+			
+		testParameterMetaData(paramMetaData, parameterMetaDataArray0);
+
+      		// TODO: Some of the OUT params are getting reported as IN_OUT for embedded.
+      		// Network server reports it correctly.
+      		cs.execute();
+
+		//bug 4450 - parameter meta data info for the return parameter was giving
+      		//null pointer exception. In the past, we didn't need to keep the return
+      		//parameter info for callable statement execution and hence we never
+      		//generated the meta data for it. To fix the problem, at the parsing time,
+      		//I set a flag if the call statement is of ? = form. If so, the first
+      		//parameter is a return parameter and save it's meta data rather than
+      		//discarding it.
+      		cs = prepareCall("? = call RDB(?)");
+      		paramMetaData = cs.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray1 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "31", "0", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_OUT"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"}};
+
+                testParameterMetaData(paramMetaData, parameterMetaDataArray1);
+
+		stmt.close();
+		cs.close();
+	}
+	/**
+        * testing a prepared statement.
+        */
+	public void testPreparedStatement () throws SQLException {
+		//next testing a prepared statement
+      		PreparedStatement ps = prepareStatement("insert into t values(?, ?, ?, ?, ?)");
+      		ps.setNull(1, java.sql.Types.CHAR);
+      		ps.setInt(2, 1);
+      		ps.setNull(3, java.sql.Types.INTEGER);
+      		ps.setBigDecimal(4,new BigDecimal("1"));
+      		ps.setNull(5, java.sql.Types.DATE);
+
+      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 5, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String [][] parameterMetaDataArray0 = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "false", "5", "0", "1", "CHAR", "java.lang.String",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "5", "0", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "false", "10", "0", "91", "DATE", "java.sql.Date",
"PARAMETER_MODE_IN"}};
+
+                testParameterMetaData(paramMetaData, parameterMetaDataArray0);
+
+      		// JCC seems to report these parameters as MODE_UNKNOWN, where as Derby uses MODE_IN
+      		// JCC behaviour with network server matches its behaviour with DB2
+      		// getPrecision() returns 0 for CHAR/DATE/BIT types for Derby. JCC shows maxlen
+      		ps.execute();
+
+		//bug 4533 - associated parameters should not be included in the parameter meta data list
+      		//Following statement systab will generate 4 associated parameters for the 2
+      		//user parameters. This results in total 6 parameters for the prepared statement
+      		//internally. But we should only show 2 user visible parameters through
+      		//getParameterMetaData().
+      		ps = prepareStatement("select * from sys.systables where " +
+             			      " tablename like ? and tableID like ?");
+      		ps.setString (1, "SYS%");
+      		ps.setString (2, "8000001%");
+      		paramMetaData = ps.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray1 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "false", "36", "0", "1", "CHAR", "java.lang.String",
"PARAMETER_MODE_IN"}};
+
+                testParameterMetaData(paramMetaData, parameterMetaDataArray1);
+
+      		ps.execute();
+
+		ps.close();
+	}
+
+	/** DERBY-44 added support for SELECT ... WHERE column LIKE ? ESCAPE ?
+         *  This test case tests
+         *   a) that such a statement compiles, and
+         *   b) that we get the correct error message if the escape
+         *      sequence is an empty string (at one point this would
+         *      lead to a StringIndexOutOfBoundsException)`
+	 */
+	public void testLikeEscaleStatement () throws SQLException {
+
+      		//variation 1, testing DERBY-44 
+      		PreparedStatement ps = prepareStatement("select * from sys.systables " +
+							"where tablename like ? escape ?");
+      		ps.setString (1, "SYS%");
+      		ps.setString (2, "");
+      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String",
"PARAMETER_MODE_IN"}};
+
+                testParameterMetaData(paramMetaData, parameterMetaDataArray0);
+
+      		try {
+          		ResultSet rs = ps.executeQuery();
+          		rs.next();
+			fail("DERBY-44 failed (didn't get SQLSTATE 22019)");
+          		rs.close();
+      		} catch (SQLException e) {
+			assertSQLState("22019", e.getSQLState(), e);
+      		}
+		ps.close();
+	}
+
+	 /** the test no longer tests 4552, but kept as an interesting test scenario
+          * bug 4552 - no parameters would be returned for execute statement using
+          * System.out.println("Bug 4552 - no parameters would be returned for execute statement
using");
+          * orig: ps = con.prepareStatement("execute statement systab using values('SYS%','8000001%')");
+          */
+
+	public void testExecuteStatementUsing () throws SQLException {
+
+      		PreparedStatement ps = prepareStatement("select * from sys.systables " + 
+							"where tablename like 'SYS%' and " + 
+							"tableID like '8000001%'");
+
+      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 0, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = null;
+
+                testParameterMetaData(paramMetaData, parameterMetaDataArray0);
+
+
+      		ps.execute();
+
+		ps.close();
+	}
+	/** Bug 4654 - Null Pointer exception while executuing a select with a
+         *  where clause parameter of type 'TRUE' or 'FALSE' constants. The existing prior
to
+         *  exposing parameter metadata didn't need to fill in metadata information for where
+         *  clause parameter in the example above.
+         *  This no longer makes sense, for we cannot take BOOLEANs anymore.
+         *  replace with a simple where 1 = ?. Which would take either 1 for true, or 0 for
false
+	 */ 
+	public void testSelectStatementUsingBoolean () throws SQLException {
+
+      		//Bug 4654 - fill in where clause parameter type info
+      		PreparedStatement ps = prepareStatement("select * from t "+  
+							"where 1=? for update");
+
+      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 1, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"}};
+
+                testParameterMetaData(paramMetaData, parameterMetaDataArray0);
+
+      		dumpParameterMetaDataNegative(paramMetaData);
+      		ps.setInt(1,1);
+      		ps.execute();
+
+		ps.close();
+	}
+	/** 
+         *  test: no parameter for the statement and then do getParameterMetaData() 
+         */
+	public void testSelectStatementUsingNoParameter () throws SQLException {
+
+      		PreparedStatement ps = prepareStatement("select * from t");
+      		ParameterMetaData paramMetaData = ps.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 0, paramMetaData.getParameterCount());
+		
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = null; 
+
+                testParameterMetaData(paramMetaData, parameterMetaDataArray0);
+
+      		ps.execute();
+
+		ps.close();
+	}
+	/**
+         *  test: the scale returned should be the one set by registerOutParameter 
+         */
+	public void testCallableStatementReturnedScale () throws SQLException {
+
+		/**
+                 *  DERBY-2810 - getParameterType behavior is different in Embedded and 
+                 *  Network Client when set by registerOutParameter 
+                 *  temporarily disabling Network Client.
+                 */
+		if (!usingDerbyNetClient()) {
+			Statement stmt = createStatement();
+      			stmt.executeUpdate("create procedure dummy_numeric_Proc(out a NUMERIC(30,15), out
b NUMERIC(30,15)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummy_numeric_Proc'");
+      			CallableStatement cs = prepareCall("CALL dummy_numeric_Proc(?,?)");
+      			cs.registerOutParameter(1, Types.NUMERIC);
+      			cs.registerOutParameter(2, Types.NUMERIC,15);
+      			cs.execute();
+			assertEquals("Unexpected parameter count", 2, cs.getParameterMetaData().getParameterCount());
+
+			//expected values to be stored in a 2dim. array
+                	String parameterMetaDataArray0 [][] = {
+                	//isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                	{"PARAMETER_NULLABLE", "true", "30", "15", "2", "NUMERIC", "java.math.BigDecimal",
"PARAMETER_MODE_OUT"},
+                	{"PARAMETER_NULLABLE", "true", "30", "15", "2", "NUMERIC", "java.math.BigDecimal",
"PARAMETER_MODE_OUT"}};
+
+                	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
+		
+			cs.close();
+		}
+	}
+	/**
+         *  test behaviour of meta data and out params after re-compile 
+         */
+	public void testMetatdataAfterProcRecompile () throws SQLException {
+
+		Statement stmt = createStatement();
+      		CallableStatement cs = prepareCall("CALL dummyint(?,?,?,?)");
+          	cs.registerOutParameter(3,Types.INTEGER);
+      		cs.registerOutParameter(4,Types.INTEGER);
+      		cs.setInt(1,1);
+      		cs.setInt(2,1);
+          	cs.setInt(4,4);
+	
+		//expected values to be stored in a 2dim. array
+		String parameterMetaDataArray0 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_OUT"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN_OUT"}};
+
+                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
+
+          	cs.execute();
+		assertEquals("Unexpected DUMMYINT alias returned", 11111, cs.getInt(4));
+
+		/** 
+		 *  DERBY-2786 - Behaviour of inout parameters in Embedded and Network client is
+         	 *  different if parameters are set but the CallableStatment is not executed.
+		 *  temporarily disabling Network Client. 
+         	 */
+		if (!usingDerbyNetClient()) {
+      			stmt.executeUpdate("drop procedure dummyint");
+      			stmt.executeUpdate("create procedure dummyint(in a integer, in b integer, out c
integer, inout d integer) language java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint2'
parameter style java");
+      			cs.execute();
+
+			String parameterMetaDataArray1 [][] = {
+                	//isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_OUT"},
+                	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN_OUT"}};
+
+                	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);
+
+      			cs.setInt(4, 6);
+      			// following is incorrect sequence, should execute first, then get
+      			// but leaving it in as an additional negative test. see beetle 5886
+			assertEquals("Unexpected DUMMYINT alias returned", 6, cs.getInt(4));
+
+      			cs.execute();
+			assertEquals("Unexpected DUMMYINT alias returned", 22222, cs.getInt(4));
+		}
+      		cs.close();
+	}
+	/**
+         *  test ParameterMetaData for Java procedures with INTEGER parameters 
+         */
+	public void testParameterMetadataWithINTParameters () throws SQLException {
+
+		Statement stmt = createStatement();
+       		stmt.execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT
pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint'");
+      		CallableStatement cs = prepareCall("CALL PMDI(?, ?, ?, ?)");
+	
+		// parameters 1 and 2 are input only
+               	cs.setInt(1,1);
+        	cs.setInt(2,1);
+		// parameter 3 is input and output
+                Object x = new Integer(1);
+                cs.setObject(3,x, Types.INTEGER);
+                cs.registerOutParameter(3,Types.INTEGER);
+               	//parameter 4 is output only
+               	cs.registerOutParameter(4,Types.INTEGER);
+
+               	//verify the meta data for the parameters
+               	ParameterMetaData paramMetaData = cs.getParameterMetaData();
+               	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_IN_OUT"},
+                {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer",
"PARAMETER_MODE_OUT"}};
+
+                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
+			
+       		cs.close();
+       		stmt.execute("DROP PROCEDURE PMDI");
+            stmt.close();
+	}
+	/**
+         *  test ParameterMetaData for Java procedures with CHAR parameters
+         */
+	 public void testParameterMetadataWithCHARParameters () throws SQLException {
+
+		Statement stmt = createStatement();
+       		stmt.execute("CREATE PROCEDURE PMDC(IN pmdI_1 CHAR(10), IN pmdI_2 VARCHAR(25), INOUT
pmdI_3 CHAR(19), OUT pmdI_4 VARCHAR(32)) language java parameter style java external name
'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyString'");
+      		CallableStatement cs = prepareCall("CALL PMDC(?, ?, ?, ?)");
+		// parameters 1 and 2 are input only	
+		cs.setString(1, "TEST0");
+		cs.setString(2, "TEST1");
+		// parameter 3 is input and output
+                Object x = new String("TEST");
+                cs.setObject(3,x, Types.CHAR);
+                cs.registerOutParameter(3,Types.CHAR);
+                //parameter 4 is output only
+                cs.registerOutParameter(4,Types.CHAR);
+ 		//verify the meta data for the parameters
+               	ParameterMetaData paramMetaData = cs.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());
+	
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "false", "10", "0", "1", "CHAR", "java.lang.String",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "false", "25", "0", "12", "VARCHAR", "java.lang.String",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "false", "19", "0", "1", "CHAR", "java.lang.String",
"PARAMETER_MODE_IN_OUT"},
+                {"PARAMETER_NULLABLE", "false", "32", "0", "12", "VARCHAR", "java.lang.String",
"PARAMETER_MODE_OUT"}};
+
+		testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
+
+       		cs.close();
+       		stmt.execute("DROP PROCEDURE PMDC");
+            stmt.close();
+	}
+	/**
+         *  test ParameterMetaData for Java procedures with DECIMAL parameters
+         */
+	public void testParameterMetadataWithDECIMALParameters () throws SQLException {
+
+		Statement stmt = createStatement();
+       		stmt.execute("CREATE PROCEDURE PMDD(IN pmdI_1 DECIMAL(5,3), IN pmdI_2 DECIMAL(4,2),
INOUT pmdI_3 DECIMAL(9,0), OUT pmdI_4 DECIMAL(10,2)) language java parameter style java external
name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyDecimal'");
+      		CallableStatement cs = prepareCall("CALL PMDD(?, ?, ?, ?)");
+
+		// parameters 1 and 2 are input only
+                cs.setBigDecimal(1,new BigDecimal("1"));;
+                cs.setBigDecimal(2,new BigDecimal("1"));;
+                // parameter 3 is input and output
+                Object x = new BigDecimal(1.1);
+                cs.setObject(3,x, Types.DECIMAL);
+                cs.registerOutParameter(3,Types.DECIMAL);
+                //parameter 4 is output only
+                cs.registerOutParameter(4,Types.DECIMAL);
+		//verify the meta data for the parameters
+              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN_OUT"},
+                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_OUT"}};
+
+                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
+
+       		cs.close();
+	}
+	/**
+         *  test ParameterMetaData for Java procedures with some literal parameters
+         */
+	public void testParameterMetadataWithLITERALParameters () throws SQLException {
+
+		Statement stmt = createStatement();
+      		CallableStatement cs = prepareCall("CALL PMDD(32.4, ?, ?, ?)");
+		// parameters 2 is input only
+                cs.setBigDecimal(1,new BigDecimal("1"));;
+                // parameter 3 is input and output
+                Object x = new BigDecimal(1.1);
+                cs.setObject(2,x, Types.DECIMAL);
+                cs.registerOutParameter(2,Types.DECIMAL);
+                //parameter 4 is output only
+                cs.registerOutParameter(3,Types.DECIMAL);
+
+		//verify the meta data for the parameters
+                ParameterMetaData paramMetaData = cs.getParameterMetaData();
+                assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray0 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN_OUT"},
+                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_OUT"}};
+
+                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
+
+          	cs.close();
+
+      		cs = prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
+		// parameter 3 is input and output
+                Object y = new BigDecimal(1.1);
+                cs.setObject(1,y, Types.DECIMAL);
+                cs.registerOutParameter(1,Types.DECIMAL);
+                //parameter 4 is output only
+                cs.registerOutParameter(2,Types.DECIMAL);
+		paramMetaData = cs.getParameterMetaData();
+                assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray1 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN_OUT"},
+                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_OUT"}};
+
+                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);
+
+          	cs.close();
+
+      		cs = prepareCall("CALL PMDD(?, 38.2, ?, ?)");
+		// parameters 1 is input only
+                cs.setBigDecimal(1,new BigDecimal("1"));;
+                // parameter 3 is input and output
+                Object z = new BigDecimal(1.1);
+                cs.setObject(2,z, Types.DECIMAL);
+                cs.registerOutParameter(2,Types.DECIMAL);
+                //parameter 4 is output only
+                cs.registerOutParameter(3,Types.DECIMAL);
+
+		//verify the meta data for the parameters
+                paramMetaData = cs.getParameterMetaData();
+		assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());
+
+		//expected values to be stored in a 2dim. array
+                String parameterMetaDataArray2 [][] = {
+                //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName,
getParameterClassName, getParameterMode
+                {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN"},
+                {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_IN_OUT"},
+                {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal",
"PARAMETER_MODE_OUT"}};
+
+                testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray2);
+
+
+          	cs.close();
+          	stmt.execute("DROP PROCEDURE PMDD");
+            stmt.close();
+	}
+	/**
+         *  print the parameter isNullable value in human readable form
+         */
+	static String parameterIsNullableInStringForm(int nullabilityValue){
+		if (nullabilityValue ==  ParameterMetaData.parameterNoNulls)
+				  return("PARAMETER_NO_NULLS");
+		else if (nullabilityValue ==  ParameterMetaData.parameterNullable)
+				  return("PARAMETER_NULLABLE");
+		else if (nullabilityValue ==  ParameterMetaData.parameterNullableUnknown)
+				  return("PARAMETER_NULLABLE_UNKNOWN");
+		else
+				  return("ERROR: donot recognize this parameter isNullable() value");
+  	}
+
+	//print the parameter mode in human readable form
+	static String parameterModeInStringForm(int mode){
+		if (mode ==  ParameterMetaData.parameterModeIn)
+				  return("PARAMETER_MODE_IN");
+		else if (mode ==  ParameterMetaData.parameterModeInOut )
+				  return("PARAMETER_MODE_IN_OUT");
+		else if (mode ==  ParameterMetaData.parameterModeOut)
+				  return("PARAMETER_MODE_OUT");
+		else if (mode ==  ParameterMetaData.parameterModeUnknown)
+				  return("PARAMETER_MODE_UNKNOWN");
+		else
+				  return("ERROR: donot recognize this parameter mode");
+  	}
+	static void testParameterMetaData(ParameterMetaData paramMetaData, String [][] paramMetaDataArray)
throws SQLException {
+		int numParam = paramMetaData.getParameterCount();
+		
+		for (int i=0, j=0; i<numParam; i++) {	
+                       	assertEquals("Unexpected parameter isNullable", paramMetaDataArray[i][j++],
parameterIsNullableInStringForm(paramMetaData.isNullable(i+1)));
+                       	assertEquals("Unexpected parameter isSigned", Boolean.valueOf(paramMetaDataArray[i][j++]).booleanValue(),
paramMetaData.isSigned(i+1));
+                       	assertEquals("Unexpected parameter getPrecision", Integer.parseInt(paramMetaDataArray[i][j++])
,  paramMetaData.getPrecision(i+1));
+                       	assertEquals("Unexpected parameter getScale", Integer.parseInt(paramMetaDataArray[i][j++])
, paramMetaData.getScale(i+1));
+                       	assertEquals("Unexpected parameter getParameterType", Integer.parseInt(paramMetaDataArray[i][j++])
, paramMetaData.getParameterType(i+1));
+                       	assertEquals("Unexpected parameter getParameterTypeName", paramMetaDataArray[i][j++]
, paramMetaData.getParameterTypeName(i+1));
+                       	assertEquals("Unexpected parameter getParameterClassName", paramMetaDataArray[i][j++]
, paramMetaData.getParameterClassName(i+1));
+                       	assertEquals("Unexpected parameter getParameterMode", paramMetaDataArray[i][j++]
, parameterModeInStringForm(paramMetaData.getParameterMode(i+1)));
+			
+			j=0;
+		}
+	}
+	static void dumpParameterMetaDataNegative(ParameterMetaData paramMetaData) throws SQLException
{
+
+                int numParam = paramMetaData.getParameterCount();
+		try {
+       			paramMetaData.isNullable(-1);
+       			fail("parameterMetaData.isNullable(-1) should have failed");
+   		} catch (SQLException se)
+       		{
+       			assertSQLState("XCL13",se.getSQLState(), se);
+       		}
+		try {
+                        paramMetaData.isNullable(0);
+                        fail("parameterMetaData.isNullable(0) should have failed");
+                } catch (SQLException se)
+                {
+                        assertSQLState("XCL13",se.getSQLState(), se);
+                }
+		try {
+                        paramMetaData.isNullable(numParam+1);
+                        fail("parameterMetaData.isNullable("+(numParam+1)+") should have
failed");
+                } catch (SQLException se)
+                {
+                        assertSQLState("XCL13",se.getSQLState(), se);
+                }
+
+	}
+	//A really simple method to test callable statement
+        public static void dummyint (int in_param, int in_param2, int[] in_param3, int[]
in_param4)
+                                                                   throws SQLException {
+
+                in_param4[0] = 11111;
+        }
+        public static void dummyint2 (int in_param, int in_param2, int[] in_param3, int[]
in_param4)
+                                                                   throws SQLException {
+                in_param4[0] = 22222;
+        }
+
+        public static void dummy_numeric_Proc (BigDecimal[] max_param,BigDecimal[] min_param)
+                                                                 throws SQLException {
+        }
+
+        public static void dummyString (String in_param, String in_param2, String[] in_param3,
String[] in_param4) {
+        }
+        public static void dummyDecimal(BigDecimal in_param, BigDecimal in_param2, BigDecimal[]
in_param3, BigDecimal[] in_param4) {
+        }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMetaDataJdbc30Test.java
------------------------------------------------------------------------------
    svn:eol-style = native

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=547349&r1=547348&r2=547349
==============================================================================
--- 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
Thu Jun 14 11:27:53 2007
@@ -75,6 +75,7 @@
 		suite.addTest(SetTransactionIsolationTest.suite());
 		suite.addTest(AuthenticationTest.suite());
 		suite.addTest(DriverTest.suite());
+		suite.addTest(ParameterMetaDataJdbc30Test.suite());
         suite.addTest(ClobTest.suite());
         suite.addTest(BlobUpdateableStreamTest.suite());
         suite.addTest(AIjdbcTest.suite());



Mime
View raw message