callable.java

derby database source code.good for you.

		ResultSet rs = stmt.executeQuery("select * from tab1");
		while (rs.next())
		{
			System.out.println("  id: "+rs.getInt(1)+" desc: "+rs.getString(2));
		}
	
		createtable = "create table tab2 (tab2pk int, vcc2 varchar(32), fc float, tab1pk int, primary key(tab2pk), foreign key(tab1pk) references tab1)" ;
		System.out.println("doing: " + createtable);
		stmt.execute(createtable);
		
		inserttable="insert into tab2 values(1, 'STRING-1', 1.0 , 5)";
		System.out.println("doing: " + inserttable);
		stmt.execute(inserttable);
		inserttable="insert into tab2 values(2, 'STRING-2', 2.0 , 2)";
		System.out.println("doing: " + inserttable);
		stmt.execute(inserttable);
		inserttable="insert into tab2 values(3, 'STRING-3', 3.0 , 5)";
		System.out.println("doing: " + inserttable);
		stmt.execute(inserttable);
		inserttable="insert into tab2 values(9, 'STRING-9', 9.0 , 3)";
		System.out.println("doing: " + inserttable);
		stmt.execute(inserttable);

		System.out.println("setup done");

		String createproc = "create procedure UpdTable_Proc(P1 INT) MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.UpdTable_Proc' langauge java parameter style java";
		System.out.println("doing: " + createproc);
		stmt.execute(createproc);
		
		System.out.println("call the proc/get the callable statement");
		CallableStatement cstmt= conn.prepareCall("{call UpdTable_Proc(?)}");
		System.out.println("set first int");
		cstmt.setInt(1,2);
		System.out.println("add first to batch");
		cstmt.addBatch();

		System.out.println("set second int");
		cstmt.setInt(1,3);
		System.out.println("add second to batch");
		cstmt.addBatch();

		System.out.println("set third int");
		cstmt.setInt(1,4);
		System.out.println("add third to batch");
		cstmt.addBatch();

		try {
			System.out.println("execute the executeBatch method");
			updateCount = cstmt.executeBatch();
			updCountLength = updateCount.length;
		} catch (SQLException e) {
			System.out.println("EXPECTED Exception: ");
			System.out.println(e.getMessage());
		}

		rs = stmt.executeQuery("select * from tab2");
		while (rs.next())
		{
			System.out.println("  type id: "+rs.getInt(4)+" new float column value: "+rs.getInt(3));
		}

		System.out.println("prepare the proc");
		String prepString= "update tab2 set tab2pk=?, vcc2=? where vcc2=?";
		PreparedStatement pstmt = conn.prepareStatement(prepString);
		int batchUpdates[]={0,0,0};
		int buCountlen=0;

		try {

			System.out.println("set first values");
			pstmt.setInt(1,1);
			pstmt.setString(2, "Continue-1");
			pstmt.setString(3, "STRING-1");
			System.out.println("add first to batch");
			pstmt.addBatch();
	
			System.out.println("set second values - illegal update - forces unique constr violation");
			pstmt.setInt(1,1);
			pstmt.setString(2,"Invalid");
			pstmt.setString(3,"STRING-3");
			System.out.println("add second to batch");
			pstmt.addBatch();

			System.out.println("set third values; legal update again");
			pstmt.setInt(1,2);
			pstmt.setString(2,"Continue-2");
			pstmt.setString(3,"STRING-2");
			System.out.println("add third to batch");
			pstmt.addBatch();

			System.out.println("execute the executeBatch method");
			System.out.println("expecting batchupdateexception");
			updateCount = pstmt.executeBatch();

		}
		catch(BatchUpdateException b)
		{
			System.out.println("b: " + b.getMessage());
			System.out.println("Caught expected BatchUpdateException");
			batchUpdates = b.getUpdateCounts();
			buCountlen = batchUpdates.length;
			System.out.println("buclen: " + buCountlen);
		}
		catch(SQLException sqle)
		{
			System.out.println("Call to continueUpdate failed!" + sqle);
		}
		catch ( Exception e ) 
		{ 
			System.out.println("Call to continueUpdate failed!" + e);
		}
	
		if (buCountlen == 1) 
		{
			System.out.println("Driver does not support continued updates - OK");
			for (i = 0; i < buCountlen; i++)
				System.out.println("=== update count: "+batchUpdates[i]);
			return;
		}
		else if (buCountlen == 3) 
		{
			System.out.println("Driver supports continued updates.");
			for (i = 0; i < buCountlen; i++)
				System.out.println("=== update count: "+batchUpdates[i]);
			// Check to see if the third row from the batch was added
			try
			{
				String query ="Select count(*) from tab2 where vcc2 in ('Continue-2')";
				System.out.println("Query is: " + query);
				rs=stmt.executeQuery(query);
				System.out.println("executed, now next...");
				rs.next();
				System.out.println("next, now getInt...");
				int count = rs.getInt(1);
				rs.close();
				stmt.close();
				System.out.println("Count val is: " + count);

				// Make sure that we have the correct error code for
				// the failed update.

				if (! (batchUpdates[1] == -3 && count == 1) )
				{
					System.out.println("Driver did not insert after error.");
				}
				System.out.println("now after errorcode check");
					
			}
			catch (SQLException sqle)
			{
				System.out.println("Call to continueUpdate failed!" + sqle);
				sqle.printStackTrace();
					
			}
		}

		System.out.println("Done testing executeBatch.");

		rs.close();
		cstmt.close();
		stmt.close();

	  }catch (SQLException e) {
		  System.out.println(e.getMessage());
		  e.printStackTrace();
	  }
	}

	public static void method1 (int p1, int p2, int[] p3)
	{
		p3[0] = p1 + p2;
	}

	public static int method2 (int p1)
	{
		return (p1 * p1) + p1;
	}

	public static void method3()
	{
		System.out.println("I'm doing something here...");
	}

	public static int method4()
	{
		return 55;
	}

	public static void method4(short s, int i, long l, float f,
							double d, BigDecimal bd, Date dt, Time t, Timestamp ts, byte[] ba,
							short[] sr, int[] ir,
							long[] lr, float[] fr, double[] dr, BigDecimal[] bdr,
							Date[] dtr, Time[] tr, Timestamp[] tsr, byte[][] bar)
	{
		sr[0] = s;
		ir[0] = i;
		lr[0] = l;
		fr[0] = f;
		dr[0] = d;
		bdr[0] = bd;
		dtr[0] = dt;
		tr[0] = t;
		if (ts.equals(Timestamp.valueOf("2002-05-12 10:05:02.000000000")))
		{
			System.out.println("got the right Timestamp");
			tsr[0] = ts;
		}
		else
		{
			System.out.println("got the wrong Timestamp");
			tsr[0] = null;
		}
		bar[0] = ba;
	}

	public static void method5(BigDecimal bd1, BigDecimal bdr1[], BigDecimal bd2, BigDecimal bdr2[],
								BigDecimal bdr3[], BigDecimal bdr4[], BigDecimal bdr5[], BigDecimal bdr6[],
								BigDecimal bdr7[])
	{
		bdr1[0] = bd1;
		bdr2[0] = bd1.multiply(bd2);
		bdr3[0] = bd1.add(bd2);
		bdr4[0] = new BigDecimal(".00000");
		bdr5[0] = new BigDecimal("-.00000");
		bdr6[0] = new BigDecimal("99999999.");
		bdr7[0] = new BigDecimal("-99999999.");
	}

	// Test for INOUT params.
	public static void method6 (int p1, int p2[], short s1, short s2[], long l1, long l2[],
			float f1, float f2[], double d1, double d2[], Time t1, Time t2[])
	{
		p2[0] = p1 + p2[0];
		s2[0] = (short) (s1 + s2[0]);
		l2[0] = l1 + l2[0];
		f2[0] = f1 + f2[0];
		d2[0] = d1 + d2[0];
		t2[0] = t1;
	}

	// Test for IN parameters with Longvarbinary column
	public static void Longvarbinary_Proc_In (byte[] in_param) throws SQLException 
	{

		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("update Longvarbinary_Tab set lvbc=?");

		ps.setBytes(1,in_param);
		ps.executeUpdate();

		ps.close();ps=null;
		conn.close();conn=null;
	}


	// test update of table in batch
	public static void UpdTable_Proc (BigDecimal type_param) throws SQLException 
	{
        	Connection conn = DriverManager.getConnection("jdbc:default:connection");
		PreparedStatement ps = conn.prepareStatement("update t2 set fc=fc*20 where tab1pk=?");
		
		ps.setBigDecimal(1,type_param);
		ps.executeUpdate();

		ps.close(); ps=null;
		conn.close(); conn=null;
	}

	// test accessing minumum and maximum and null value for numeric columns with out params
	public static void Numeric_Proc (BigDecimal[] param1,BigDecimal[] param2,
					 BigDecimal[] param3) throws SQLException 
	{
		Connection conn = DriverManager.getConnection("jdbc:default:connection");
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select maxcol, mincol, nulcol from Num_Tab");
		
		if (rs.next()) 
		{
			param1[0]=rs.getBigDecimal(1);
			param2[0]=rs.getBigDecimal(2);
			param3[0]=rs.getBigDecimal(3);
		}
		else 
		{
	    		throw new SQLException("Data not found");
		}
		
		rs.close();rs=null;
		stmt.close();stmt=null;
		conn.close();conn=null;
	}

	// Beetle 4933.  
	static void testBigDec(Connection conn) throws Exception
	{
		PreparedStatement ps = null;
		int updcount;
		
	  
	  try {
		  try {
				  ps = conn.prepareStatement("drop table Num_Tab");
				  updcount = ps.executeUpdate();
		  } 
		  catch (SQLException e) {}
		  int tabSize = 10;
		  String createTabSql = "create table Num_Tab (maxcol NUMERIC(31,15), mincol NUMERIC(15,15), nulcol NUMERIC)";

		  ps = conn.prepareStatement(createTabSql);
		  updcount = ps.executeUpdate();
		  String insertTabSql = "insert into Num_Tab values(999999999999999,0.000000000000001, null)";
		  ps = conn.prepareStatement(insertTabSql);
		  updcount = ps.executeUpdate();
		  try {
		  String alias = "create procedure Numeric_Proc(OUT P1 DECIMAL(31,15), OUT P2 DECIMAL(31,15), OUT P3 DECIMAL(31,15)) READS SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.derbynet.callable.Numeric_Proc' language java parameter style java";
		  ps = conn.prepareStatement(alias);
		  updcount = ps.executeUpdate();
		  } catch (SQLException se) {}
			  

		  CallableStatement cstmt = conn.prepareCall("{call Numeric_Proc(?,?,?)}");
		  cstmt.registerOutParameter(1,java.sql.Types.NUMERIC,15);
		  cstmt.registerOutParameter(2,java.sql.Types.NUMERIC,15);
		  cstmt.registerOutParameter(3,java.sql.Types.NUMERIC,15);
		  //		  ParameterMetaData pmd = cstmt.getParameterMetaData();
		  //System.out.println("precision: " + pmd.getPrecision(1) +
		  //				 "scale: " + pmd.getScale(1));
		  //execute the procedure
		  cstmt.execute();

		  BigDecimal retVal = cstmt.getBigDecimal(1);
		  BigDecimal retVal2 = cstmt.getBigDecimal(2);
		  BigDecimal retVal3 = cstmt.getBigDecimal(3);

		  System.out.println("cstmt.getBigDecimal(1): " + retVal);
		  System.out.println("cstmt.getBigDecimal(2): " + retVal2);
		  System.out.println("cstmt.getBigDecimal(3): " + retVal3);
		  cstmt.close();
		  ps.close();
		  
		  }catch (SQLException e) { 
			  System.out.println(e.getMessage());
			  e.printStackTrace();
		  }
	  
	}

}