callable.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.derbynet.callable

   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.derbynet;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.CallableStatement;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.BatchUpdateException;
import java.sql.DriverManager;
import java.sql.Connection;
import org.apache.derby.tools.ij;
/**
	This test tests the JDBC CallableStatement.
*/

public class callable
{

	public static void main (String args[])
	{
		try
		{
			System.out.println("CallableStatement Test Starts");

			ij.getPropertyArg(args); 

			// This also tests quoted pathname in database name portion of URL, beetle 4781.
			String protocol = System.getProperty("ij.protocol");
			System.setProperty("ij.database", protocol + "//localhost/\"" + System.getProperty("derby.system.home") + java.io.File.separator + "wombat;create=true\"");
			ij.getPropertyArg(args); 
			Connection conn = ij.startJBMS();
			if (conn == null)
			{
				System.out.println("conn didn't work");
				return;
			}
			Statement stmt = conn.createStatement();

			// 2 input, 1 output
			stmt.execute("CREATE PROCEDURE method1(IN P1 INT, IN P2 INT, OUT P3 INT) " +
					"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method1'" +
					" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
			CallableStatement cs = conn.prepareCall("call method1 (?, ?, ?)");
			cs.setInt(1, 6);
			cs.setInt(2, 9);
			cs.registerOutParameter (3, java.sql.Types.INTEGER);
			cs.execute();
			int sum = cs.getInt(3);
			System.out.println("Sum of 6 and 9 is: " + sum);
			cs.close();
			stmt.execute("DROP PROCEDURE method1");

			// method returns calue, plus 1 input
			stmt.execute("CREATE FUNCTION method2(P1 INT) RETURNS INT" +
					" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method2'" +
					" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
			cs = conn.prepareCall("? = call method2 (?)");
			cs.registerOutParameter (1, java.sql.Types.INTEGER);
			cs.setInt(2, 6);
			cs.execute();
			int ret = cs.getInt(1);
			System.out.println("return value: Square of 6 then plus 6 is: " + ret);
			cs.close();
			// stmt.execute("DROP FUNCTION method2");

			// no parameter
			stmt.execute("CREATE PROCEDURE method3() " +
					"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method3'" +
					" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
			cs = conn.prepareCall("call method3 ()");
			cs.execute();
			cs.close();
			stmt.execute("DROP PROCEDURE method3");

			// only 1 return parameter
			stmt.execute("CREATE FUNCTION method4() RETURNS INT" +
					" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method4'" +
					" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
			cs = conn.prepareCall("? = call method4()");
			cs.registerOutParameter (1, java.sql.Types.INTEGER);
			cs.execute();
			System.out.println("return value is: " + cs.getInt(1));
			cs.close();
			// stmt.execute("DROP FUNCTION method4");

			// different parameter types, also method overload
			stmt.execute("CREATE PROCEDURE method4P(" +
					"IN P1 SMALLINT, IN P2 INT, IN P3 BIGINT, IN P4 REAL, " +
					"IN P5 DOUBLE, IN P6 DECIMAL(6,3), IN P7 DATE, IN P8 TIME, IN P9 TIMESTAMP, IN P10 VARCHAR(20) FOR BIT DATA, " +
					"OUT O1 SMALLINT, OUT O2 INT, OUT O3 BIGINT, OUT O4 REAL, " +
					"OUT O5 DOUBLE, OUT O6 DECIMAL(6,3), OUT O7 DATE, OUT O8 TIME, OUT O9 TIMESTAMP, OUT O10 VARCHAR(20) FOR BIT DATA" +
					") " +
					"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method4'" +
					" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
			cs = conn.prepareCall("call method4P(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
			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.setDate(7, Date.valueOf("2002-05-12"));
			cs.setTime(8, Time.valueOf("10:05:02"));
			cs.setTimestamp(9, Timestamp.valueOf("2002-05-12 10:05:02.000000000"));
			byte[] ba = new byte[2];
			ba[0] = 1;
			ba[1] = 2;
			cs.setBytes(10, ba);
			int n = 10;
			cs.registerOutParameter (n+1, java.sql.Types.SMALLINT);
			cs.registerOutParameter (n+2, java.sql.Types.INTEGER);
			cs.registerOutParameter (n+3, java.sql.Types.BIGINT);
			cs.registerOutParameter (n+4, java.sql.Types.REAL);
			cs.registerOutParameter (n+5, java.sql.Types.DOUBLE);
			cs.registerOutParameter (n+6, java.sql.Types.DECIMAL);
			cs.registerOutParameter (n+7, java.sql.Types.DATE);
			cs.registerOutParameter (n+8, java.sql.Types.TIME);
			cs.registerOutParameter (n+9, java.sql.Types.TIMESTAMP);
			cs.registerOutParameter (n+10, java.sql.Types.VARBINARY);
			cs.execute();
			System.out.println("return short: " + cs.getShort(n+1));
			System.out.println("return int: " + cs.getInt(n+2));
			System.out.println("return long: " + cs.getLong(n+3));
			System.out.println("return float: " + cs.getFloat(n+4));
			System.out.println("return double: " + cs.getDouble(n+5));
			System.out.println("return decimal: " + cs.getBigDecimal(n+6));
			System.out.println("return date: " + cs.getDate(n+7));
			System.out.println("return time: " + cs.getTime(n+8));
			System.out.println("return time stamp: " + cs.getTimestamp(n+9));
			ba = cs.getBytes(n+10);
			for (int i = 0; i < ba.length; i++)
				System.out.println("return byte["+i+"]: " + ba[i]);
			stmt.execute("DROP PROCEDURE method4P");

			// some tests on BigDecimal
			stmt.execute("CREATE PROCEDURE method5(" +
					"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 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method5'" +
					" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
			cs = conn.prepareCall("call method5 (?, ?, ?, ?, ?, ?, ?, ?, ?)");
			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();
			System.out.println("method 5 return decimal: " + cs.getBigDecimal(2));
			System.out.println("method 5 return decimal: " + cs.getBigDecimal(4));
			System.out.println("method 5 return decimal: " + cs.getBigDecimal(5));
			System.out.println("method 5 return decimal: " + cs.getBigDecimal(6));
			System.out.println("method 5 return decimal: " + cs.getBigDecimal(7));
			System.out.println("method 5 return decimal: " + cs.getBigDecimal(8));
			System.out.println("method 5 return decimal: " + cs.getBigDecimal(9));
			cs.close();
			stmt.execute("DROP PROCEDURE method5");

			// INOUT param tests
			stmt.execute("CREATE PROCEDURE method6(" +
					"IN P1 INT, INOUT P2 INT, IN P3 SMALLINT, INOUT P4 SMALLINT, " +
					"IN P5 BIGINT, INOUT P6 BIGINT, IN P7 REAL, INOUT P8 REAL, IN P9 DOUBLE, INOUT P10 DOUBLE, " +
					"IN P11 TIME, INOUT P12 TIME " +
					") " +
					"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.method6'" +
					" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
			cs = conn.prepareCall("call method6 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?)");
			cs.registerOutParameter (2, java.sql.Types.INTEGER);
			cs.registerOutParameter (4, java.sql.Types.SMALLINT);
			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.setInt(1, 6);
			cs.setInt(2, 9);
			cs.setShort(3, (short)6);
			cs.setShort(4, (short)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();
			System.out.println("Integer: Sum of 6 and 9 is: " + cs.getInt(2));
			System.out.println("Short: Sum of 6 and 9 is: " + cs.getShort(4));
			System.out.println("Long: Sum of 99999 and 88888888 is: " + cs.getLong(6));
			System.out.println("Float: Sum of 6.123453 and 77777 is: " + cs.getFloat(8));
			System.out.println("Double: Sum of 6.987654 and 8888888888888.01234 is: " + cs.getDouble(10));
			System.out.println("Time: Old time of 10:05:02 changed to: " + cs.getTime(12));
			cs.close();
			stmt.execute("DROP PROCEDURE method6");

			testBigDec(conn);

			testLongBinary(conn);
			// Temporarily take out testbatch until jcc bug is fixed (5827)
			// testBatch(conn);

			System.out.println("CallableStatement Test Ends");
        }
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	static void testLongBinary(Connection conn)
	{
	  try {
		String createTabSql = "create table Longvarbinary_Tab (lvbc Long varchar for bit data)";
		PreparedStatement ps = conn.prepareStatement(createTabSql);
		int updcount = ps.executeUpdate();
		String insertTabSql = "insert into Longvarbinary_Tab values( X'010305')";
		ps = conn.prepareStatement(insertTabSql);
		updcount = ps.executeUpdate();

		int bytearrsize = 50;
		byte[] bytearr=new byte[bytearrsize];
		String sbyteval=null;

		// to get the bytearray value
		for (int count=0;count<bytearrsize;count++)
		{
			sbyteval=Integer.toString(count%255);
			bytearr[count]=Byte.parseByte(sbyteval);
		}

		System.out.println("get the CallableStatement object");

		String createproc = "create procedure Longvarbinary_In(P1 VARCHAR(10000) FOR BIT DATA) MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.Longvarbinary_Proc_In' language java parameter style java";
		ps = conn.prepareStatement(createproc);
		updcount = ps.executeUpdate();

		CallableStatement cstmt = conn.prepareCall("{call Longvarbinary_In(?)}");
		cstmt.setObject(1,bytearr,java.sql.Types.LONGVARBINARY);
		System.out.println("execute the procedure with LONGVARBINARY");
		cstmt.executeUpdate();
		cstmt.setObject(1,bytearr,java.sql.Types.BLOB);
		System.out.println("execute the procedure with BLOB");
		cstmt.executeUpdate();

		Statement stmt = conn.createStatement();
		String Longvarbinary_Query="Select lvbc from Longvarbinary_Tab";
		System.out.println(Longvarbinary_Query);
		ResultSet rs=stmt.executeQuery(Longvarbinary_Query);

		while (rs.next())
		{
			byte[] retvalue = (byte[]) rs.getObject(1);

			for(int i=0;i<bytearrsize;i++)
			{
				if (retvalue[i]!=bytearr[i])
				{
					System.out.println("Test Failed.  setObject did not set the parameter value correctly");
				}
			}
		}
		rs.close();
		ps.close();
		stmt.close();
		cstmt.close();
	  }catch (SQLException e) {
		  System.out.println(e.getMessage());
		  e.printStackTrace();
	  }

	  System.out.println("done testing long varbinary");
	}

	static void testBatch(Connection conn)
	{
	  try {
		conn.setAutoCommit(true);
		int i=0;
		int retValue[]={0,0,0};
		int updCountLength=0;
		Statement stmt = conn.createStatement();
		PreparedStatement ps = null;
		int updcount;
		
		try {
			ps=conn.prepareStatement("drop table tab1");
			updcount=ps.executeUpdate();
			ps=conn.prepareStatement("drop table tab2");
			updcount=ps.executeUpdate();
			ps=conn.prepareStatement("drop procedure UpdTable_Proc");
			updcount=ps.executeUpdate();
		} 
		catch (SQLException e) {}

		String createtable = "create table tab1 (tab1pk int, vcc1 varchar(32), primary key(tab1pk))";
		System.out.println("doing: " + createtable);
		stmt.execute(createtable);

		String inserttable = "insert into tab1 values(2, 'STRING_2')";
		System.out.println("doing: " + inserttable);
		stmt.addBatch(inserttable);
		inserttable = "insert into tab1 values(3, 'STRING_3')";
		System.out.println("doing: " + inserttable);
		stmt.addBatch(inserttable);
		inserttable = "insert into tab1 values(5, 'STRING_5')";
		System.out.println("doing: " + inserttable);
		stmt.addBatch(inserttable);
		inserttable = "select * from tab1";
		System.out.println("adding: " + inserttable);
		stmt.addBatch(inserttable);

		int[] updateCount=null;
		try {
			updateCount = stmt.executeBatch();
		} catch(SQLException se) {
 			do {
				System.out.println("Exception chain: "+se.getMessage());
				se = se.getNextException();
			} while (se != null);
		}