outparams.java

derby database source code.good for you.

					}
	
					StringBuffer buf = new StringBuffer();
					try
					{
						if (doSetObject == 0) 
						{
							callSetMethod(cs, 1, types[type], buf);
						}
						else if (doSetObject == 1)
						{
							callSetObject(cs, 1, types[type], buf);
						}
						else
						{
							// only try this once
							type = types.length-1;
							buf.append("...no setXXX(1) at all");
						}
					}
					catch (SQLException se)
					{
						System.out.println("\t"+buf.toString());
						System.out.println("\tException "+se);
						continue;
					}
					System.out.println("\t"+buf.toString());
					cs.setInt(2, types[type]);
	
					try
					{
						System.out.println("\tcs.execute()");
						boolean hasResultSet = cs.execute();
						if (hasResultSet)
							System.out.println("testEachOutputType HAS RESULT SET cs.execute() returned true");
						}
					catch (SQLException se)
					{
						System.out.println("\tException "+se);
						continue;
					}
					for (int getType = 0; getType < types.length; getType++)
					{
						StringBuffer getbuf = new StringBuffer();
						try
						{
							callGetMethod(cs, 1, types[getType], getbuf);
						}
						catch (SQLException se)
						{
							getbuf.append(se);
						}
						System.out.println("\t\t\t"+getbuf.toString());
					}
	
				}

				cs.close();

				scp.execute("DROP PROCEDURE " + methodName);
				scp.close();
			}
		}

		System.out.println("------------------------------------\n");

	}

	// test that everything works ok when we regsiter the param as type OTHER.
	// should be able to get/setXXX of the appropriate type
	private static void testOtherOutputType(Connection conn) throws Throwable
	{
		System.out.println("==============================================");
		System.out.println("TESTING OUTPUT PARAMETERS WITH register(OTHER)");
		System.out.println("==============================================\n");
		CallableStatement cs = null;

		for (int method = 0; method < outputMethods.length; method++)
		{
			String methodName = outputMethods[method];
			if (methodName == null)
				continue;
			System.out.println("\n------------------------------------");


			Statement scp = conn.createStatement();
			String str;
			if (methodName.indexOf("Nothing") == -1)
			{

				scp.execute("CREATE PROCEDURE " + methodName + "(INOUT P1 " + outputProcParam[method] + ", IN P2 INT) " +
					"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams." + methodName +
					"' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");


				if (method%2 == 0)
					str = "call "+methodName+"(?,?)";
				else
					str = "{call "+methodName+"(?,?)}";
			}
			else
			{
				scp.execute("CREATE PROCEDURE " + methodName + "() " +
					"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams." + methodName +
					"' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
				str = "{call "+methodName+"()}";
			}

			System.out.println(str);
			try 
			{
				cs = conn.prepareCall(str);
			}
			catch (SQLException se)
			{
				System.out.println("ERROR: unexpected exception "+se);
				throw se;
			}
	
			for (int type = 0; type < types.length; type++)
			{
				cs.clearParameters();
				System.out.println();
				try
				{
					System.out.println("\n\tcs.registerOutParameter(1, Types.OTHER)");
					cs.registerOutParameter(1, Types.OTHER);
				} 
				catch (SQLException se)
				{
					System.out.println("\tException "+se);
					continue;
				}

				StringBuffer buf = new StringBuffer();
				try
				{
					callSetMethod(cs, 1, types[type], buf);
				}
				catch (SQLException se)
				{
					System.out.println("\t"+buf.toString());
					System.out.println("\tException "+se);
					continue;
				}
				System.out.println("\t"+buf.toString());
				cs.setInt(2, types[type]);

				try
				{
					System.out.println("\tcs.execute()");
					cs.execute();
				}
				catch (SQLException se)
				{
					System.out.println("\tException "+se);
					continue;
				}
				for (int getType = 0; getType < types.length; getType++)
				{
					StringBuffer getbuf = new StringBuffer();
					try
					{
						callGetMethod(cs, 1, types[getType], getbuf);
					}
					catch (SQLException se)
					{
						getbuf.append(se);
					}
					System.out.println("\t\t\t"+getbuf.toString());
				}

			}

			cs.close();

			scp.execute("DROP PROCEDURE " + methodName);
			scp.close();
		}

		System.out.println("------------------------------------\n");
	}

	private static void testReturnTypes(Connection conn) throws Throwable
	{
		System.out.println("==============================================\n");
		System.out.println("TESTING RETURN OUTPUT PARAMETERS");
		System.out.println("==============================================\n");
		CallableStatement cs = null;
		for (int method = 0; method < returnMethods.length; method++)
		{
			String methodName = returnMethods[method];
			if (methodName == null)
				continue;

			Statement scf = conn.createStatement();
			String str;
			String dropRoutine;
			if (methodName.indexOf("Nothing") != -1)
			{

				scf.execute("CREATE PROCEDURE " + methodName + "()" +
					" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams." + methodName +
					"' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");


				dropRoutine = "DROP PROCEDURE " + methodName;

				str = "{call "+returnMethods[method]+"()}";
			}
			else
			{

				scf.execute("CREATE FUNCTION " + methodName + "(P1 INT) RETURNS " + returnMethodType[method] +
					" EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams." + methodName +
					"' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
				dropRoutine = "DROP FUNCTION " + methodName;

				str = "{? = call "+returnMethods[method]+"(?)}";
			}


				
			System.out.println("\n------------------------------------");
				

			System.out.println(str);
			try 
			{
				cs = conn.prepareCall(str);
			}
			catch (SQLException se)
			{
				System.out.println("ERROR: unexpected exception "+se);
				throw se;
			}

			for (int type = 0; type < types.length; type++)
			{
				cs.clearParameters();
				System.out.println();
				try
				{
					System.out.println("\n\tcs.registerOutParameter(1, "+typeNames[type]+")");
					cs.registerOutParameter(1, types[type]);
				} 
				catch (SQLException se)
				{
					System.out.println("\tException "+se);
					continue;
				}
				try
				{
					cs.setInt(2, types[type]);
				}
				catch (SQLException se)
				{
					System.out.println("\tUnexpected exception on cs.setInt(2, "+types[type]+"): "+se);
					continue;
				}

				try
				{
					System.out.println("\tcs.execute()");
					boolean hasResultSet = cs.execute();
					if (hasResultSet)
						System.out.println("testReturnTypes HAS RESULT SET cs.execute() returned true");

				}
				catch (SQLException se)
				{
					System.out.println("\tException "+se);
					continue;
				}
				for (int getType = 0; getType < types.length; getType++)
				{
					StringBuffer getbuf = new StringBuffer();
					try
					{
						callGetMethod(cs, 1, types[getType], getbuf);
					}
					catch (SQLException se)
					{
						getbuf.append(se);
					}
					System.out.println("\t\t\t"+getbuf.toString());
				}

			}

			cs.close();
			scf.execute(dropRoutine);
			scf.close();
		}

		System.out.println("------------------------------------\n");

	}

	private static void callSetObject(CallableStatement cs, int arg, int type, StringBuffer strbuf) throws Throwable
	{

		switch (type)	
		{
			case Types.BIT:
			case JDBC30Translation.SQL_TYPES_BOOLEAN:
				strbuf.append("setObject("+arg+", true)");
				cs.setObject(arg, new Boolean(true));
				break;

			case Types.TINYINT:
				strbuf.append("setObject("+arg+", 6)");
				cs.setObject(arg, new Integer((byte)6));
				break;

			case Types.SMALLINT:
				strbuf.append("setObject("+arg+", 66)");
				cs.setObject(arg, new Integer((short)66));
				break;

			case Types.INTEGER:
				strbuf.append("setObject("+arg+", 666)");
				cs.setObject(arg, new Integer(666));
				break;

			case Types.BIGINT:
				strbuf.append("setObject("+arg+", 666)");
				cs.setObject(arg, new Long(666));
				break;

			case Types.FLOAT:
			case Types.REAL:
				strbuf.append("setObject("+arg+", 666)");
				cs.setObject(arg, new Float(666));
				break;

			case Types.DOUBLE:
				strbuf.append("setObject("+arg+", 666)");
				cs.setObject(arg, new Double(666));
				break;

			case Types.DECIMAL:
			case Types.NUMERIC:
				strbuf.append("setObject("+arg+", 666.666)");
				BigDecimal bd = new BigDecimal("666.666");
				cs.setObject(arg, bd);
				break;

			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				strbuf.append("setObject("+arg+", \"Set via setString()\")");
				cs.setObject(arg, "Set via setString()");
				break;

			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
				strbuf.append("setObject("+arg+", byte[])");
				byte[] myarray = new byte[16];
				myarray[0] = (byte)255;
				cs.setObject(arg, myarray);
				break;

			case Types.DATE:
				strbuf.append("setObject("+arg+", Date.valueOf(1999-09-09))");
				cs.setObject(arg, Date.valueOf("1999-09-09"));
				break;

			case Types.TIME:
				strbuf.append("setObject("+arg+", Time.valueOf(09:09:09))");
				cs.setObject(arg, Time.valueOf("09:09:09"));
				break;

			case Types.TIMESTAMP:
				strbuf.append("setObject("+arg+", Timestamp.valueOf(1999-09-09 09:09:09.999))");
				cs.setObject(arg, Timestamp.valueOf("1999-09-09 09:09:09.999"));
				break;

			case Types.OTHER:
				strbuf.append("setObject("+arg+", new BigInteger(666))");
				cs.setObject(arg, new BigInteger("666"));
				break;

			default:
				throw new Throwable("TEST ERROR: unexpected type "+type);
		}	
	}
	private static void callSetMethod(CallableStatement cs, int arg, int type, StringBuffer strbuf) throws Throwable
	{
		switch (type)	
		{
			case Types.BIT:
			case JDBC30Translation.SQL_TYPES_BOOLEAN:
				strbuf.append("setBoolean("+arg+", true)");
				cs.setBoolean(arg, true);
				break;

			case Types.TINYINT:
				strbuf.append("setByte("+arg+", 6)");
				cs.setByte(arg, (byte)6);
				break;

			case Types.SMALLINT:
				strbuf.append("setShort("+arg+", 66)");
				cs.setShort(arg, (short)66);
				break;

			case Types.INTEGER:
				strbuf.append("setInt("+arg+", 666)");
				cs.setInt(arg, 666);
				break;

			case Types.BIGINT:
				strbuf.append("setLong("+arg+", 666)");
				cs.setLong(arg, 666);
				break;

			case Types.FLOAT:
			case Types.REAL:
				strbuf.append("setFLoat("+arg+", 666)");
				cs.setFloat(arg, 666);
				break;

			case Types.DOUBLE:
				strbuf.append("setDouble("+arg+", 666)");
				cs.setDouble(arg, 666);
				break;

			case Types.DECIMAL:
			case Types.NUMERIC:
				strbuf.append("setBigDecimal("+arg+", 666.666)");
				BigDecimal bd = new BigDecimal("666.666");
				cs.setBigDecimal(arg, bd);
				break;

			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				strbuf.append("setString("+arg+", \"Set via setString()\")");
				cs.setString(arg, "Set via setString()");
				break;

			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
				strbuf.append("setBytes("+arg+", byte[])");
				byte[] myarray = new byte[16];
				myarray[0] = (byte)255;
				cs.setBytes(arg, myarray);
				break;

			case Types.DATE:
				strbuf.append("setDate("+arg+", Date.valueOf(1999-09-09))");
				cs.setDate(arg, Date.valueOf("1999-09-09"));
				break;

			case Types.TIME:
				strbuf.append("setTime("+arg+", Time.valueOf(09:09:09))");
				cs.setTime(arg, Time.valueOf("09:09:09"));
				break;

			case Types.TIMESTAMP:
				strbuf.append("setTimestamp("+arg+", Timestamp.valueOf(1999-09-09 09:09:09.999))");
				cs.setTimestamp(arg, Timestamp.valueOf("1999-09-09 09:09:09.999"));
				break;

			case Types.OTHER:
				strbuf.append("setObject("+arg+", new BigInteger(666))");
				cs.setObject(arg, new BigInteger("666"));
				break;

			default:
				throw new Throwable("TEST ERROR: unexpected type "+type);
		}	
	}

	private static void callGetMethod(CallableStatement cs, int arg, int type, StringBuffer strbuf) throws Throwable
	{
		switch (type)	
		{
			case Types.BIT:
			case JDBC30Translation.SQL_TYPES_BOOLEAN:
				strbuf.append("getBoolean("+arg+") = ");
				strbuf.append(cs.getBoolean(arg));
				break;

			case Types.TINYINT:
				strbuf.append("getByte("+arg+") = ");
				strbuf.append(Byte.toString(cs.getByte(arg)));
				break;

			case Types.SMALLINT:
				strbuf.append("getShort("+arg+") = ");
				strbuf.append(Short.toString(cs.getShort(arg)));
				break;

			case Types.INTEGER:
				strbuf.append("getInt("+arg+") = ");
				strbuf.append(Integer.toString(cs.getInt(arg)));
				break;

			case Types.BIGINT:
				strbuf.append("getLong("+arg+") = ");
				strbuf.append(Long.toString(cs.getLong(arg)));
				break;

			case Types.FLOAT:
			case Types.REAL:
				strbuf.append("getFloat("+arg+") = ");
				strbuf.append(Float.toString(cs.getFloat(arg)));
				break;

			case Types.DOUBLE:
				strbuf.append("getDouble("+arg+") = ");
				strbuf.append(Double.toString(cs.getDouble(arg)));
				break;

			case Types.DECIMAL:
			case Types.NUMERIC:
				strbuf.append("getBigDecimal("+arg+") = ");
				BigDecimal bd = cs.getBigDecimal(arg);
				strbuf.append(bd == null ? "null" : bd.toString());
				break;

			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				strbuf.append("getString("+arg+") = ");
				String s = cs.getString(arg);
				if (s.startsWith("[B@"))
					s = "byte[] reference";
				strbuf.append(s);
				break;

			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
				strbuf.append("getBytes("+arg+") = ");
				byteArrayToString(cs.getBytes(arg), strbuf);
				break;

			case Types.DATE:
				strbuf.append("getDate("+arg+") = ");
				Date date = cs.getDate(arg);
				strbuf.append(date == null ? "null" : date.toString());
				break;

			case Types.TIME:
				strbuf.append("getTime("+arg+") = ");
				Time time = cs.getTime(arg);
				strbuf.append(time == null ? "null" : time.toString());
				break;

			case Types.TIMESTAMP:
				strbuf.append("getTimestamp("+arg+") = ");
				Timestamp timestamp = cs.getTimestamp(arg);
				strbuf.append(timestamp == null ? "null" : timestamp.toString());
				break;