parametermetadatajdbc30.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30

   Copyright 2002, 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed 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.CallableStatement;
import java.sql.Connection;
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.derby.tools.ij;
import org.apache.derbyTesting.functionTests.util.TestUtil;

/**
 * Test the new class ParameterMetaData in jdbc 30.
 * Testing both callable and prepared statements meta data
 *
 * @author mamta
 */


public class parameterMetaDataJdbc30 {
	private static boolean isDerbyNet;
	public static void main(String[] args) {
		Connection con = null;
		Statement  s;
		CallableStatement  cs;
		PreparedStatement  ps;
		ParameterMetaData paramMetaData;

		System.out.println("Test parameterMetaDataJdbc30 starting");

		try
		{
			// use the ij utility to read the property file and
			// make the initial connection.
			ij.getPropertyArg(args);
			con = ij.startJBMS();
			con.setAutoCommit(true); // make sure it is true
			isDerbyNet = TestUtil.isNetFramework();

			s = con.createStatement();

			/* Create the table and do any other set-up */
			setUpTest(s);

      s.executeUpdate("create function RDB(P1 INT) RETURNS DECIMAL(10,2) language java external name 'org.apache.derbyTesting.functionTests.tests.lang.outparams.returnsBigDecimal' parameter style java");

      //first testing a callable statement
      s.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.parameterMetaDataJdbc30.dummyint' parameter style java");
      cs = con.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
      paramMetaData = cs.getParameterMetaData();
      System.out.println("parameters count for callable statement is " + paramMetaData.getParameterCount());
      // TODO: Some of the OUT params are getting reported as IN_OUT for embedded.
      // Network server reports it correctly.
      dumpParameterMetaData(paramMetaData);
      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.
      System.out.println("Bug 4450 - generate metadata for return parameter");
      cs = con.prepareCall("? = call RDB(?)");
      paramMetaData = cs.getParameterMetaData();
      System.out.println("param count is: "+paramMetaData.getParameterCount());
      dumpParameterMetaData(paramMetaData);

      //next testing a prepared statement
      ps = con.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);

      paramMetaData = ps.getParameterMetaData();
      System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
      // 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
      dumpParameterMetaData(paramMetaData);
      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().
      System.out.println("Bug 4533 - hide associated parameters");
      ps = con.prepareStatement("select * from sys.systables where " +
             " tablename like ? and tableID like ?");
      ps.setString (1, "SYS%");
      ps.setString (2, "8000001%");
      paramMetaData = ps.getParameterMetaData();
      System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
      dumpParameterMetaData(paramMetaData);
      ps.execute();

      // variation, and also test out empty string in the escape (jira 44). 
      System.out.println("variation 1, testing jira 44");
      ps = con.prepareStatement("select * from sys.systables where tablename like ? escape ?");
      ps.setString (1, "SYS%");
      ps.setString (2, "");
      paramMetaData = ps.getParameterMetaData();
      System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
      dumpParameterMetaData(paramMetaData);
      ps.execute();

      // 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%')");
      ps = con.prepareStatement("select * from sys.systables where tablename like 'SYS%' and tableID like '8000001%'");

      paramMetaData = ps.getParameterMetaData();
      System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
      dumpParameterMetaData(paramMetaData);
      ps.execute();

      //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 
      System.out.println("Bug 4654 - fill in where clause parameter type info");
      ps = con.prepareStatement("select * from t where 1=? for update");

      paramMetaData = ps.getParameterMetaData();
      System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
      dumpParameterMetaData(paramMetaData);
      dumpParameterMetaDataNegative(paramMetaData);
      //ps.setBoolean(1,true);
      ps.setInt(1,1);
      ps.execute();

      System.out.println("test: no parameter for the statement and then do getParameterMetaData()");
      ps = con.prepareStatement("select * from t");
      paramMetaData = ps.getParameterMetaData();
      System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
      dumpParameterMetaData(paramMetaData);
      ps.execute();

      cs.close();

      System.out.println("test: the scale returned should be the one set by registerOutParameter");
      s.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.parameterMetaDataJdbc30.dummy_numeric_Proc'");
      cs = con.prepareCall("CALL dummy_numeric_Proc(?,?)");
      cs.registerOutParameter(1, Types.NUMERIC);
      cs.registerOutParameter(2, Types.NUMERIC,15);
      cs.execute();
      dumpParameterMetaData(cs.getParameterMetaData());