outparams.java

derby database source code.good for you.

			case Types.OTHER:
				strbuf.append("getObject("+arg+") = ");
				Object o = cs.getObject(arg);
				if (o == null)
				{
					strbuf.append("null");
				}
				else if (o instanceof byte[])
				{
					byteArrayToString((byte[])o, strbuf);
				}
				else
				{
					strbuf.append(o.toString());
				}

				break;

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

	static private void byteArrayToString(byte[] barray, StringBuffer strbuf)
	{
		if (barray == null)
		{
			strbuf.append("null");
		}
		else
		{
			for (int i = 0; i<barray.length; i++)
			{
				strbuf.append(barray[i]);
			}
		}
	}

	private static String getStringOfType(int type) throws Throwable
	{
		switch (type)
		{
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				return "I am a string";

			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
			case Types.OTHER:		// other is bigInt
				return "3";

			case Types.FLOAT:
			case Types.REAL:
			case Types.DECIMAL:
			case Types.NUMERIC:
				return  "3.33";

			case Types.DATE:		
				return "1933-03-03";

			case Types.TIME:		
				return "03:03:03";

			case Types.TIMESTAMP:		
				return "1933-03-03 03:03:03.333";

			case Types.BINARY:		
			case Types.VARBINARY:		
			case Types.LONGVARBINARY:
				return "00680065006c006c006f";

			case Types.BIT:
			case JDBC30Translation.SQL_TYPES_BOOLEAN:
				return "true";

			default:
				throw new Throwable("bad type "+type);
		}	
	}

	/////////////////////////////////////////////////////////////
	//	
	// OUTPUT PARAMETER METHODS
	//
	/////////////////////////////////////////////////////////////
	public static void testNull(Boolean passedInNull, Boolean setToNull, Integer[] retval) throws Throwable
	{
		if (passedInNull.booleanValue())
		{
			if (retval[0] != null)
			{
				throw new Throwable("testNull() got a non-null param when it should have been null");
			}
		}

		retval[0] = (setToNull.booleanValue()) ? null : new Integer((short)66);
	}

	public static void testNullBug4317(String passedInNull) throws Throwable
	{
	}

	public static void takesNothing()
	{
	}
	public static void takesBytePrimitive(byte[] outparam, int type)
	{
		outparam[0]+=outparam[0];
	}
	public static void takesByte(Byte[] outparam, int type)
	{
		outparam[0] = new Byte((byte)(outparam[0] == null ? 33 : outparam[0].byteValue()*2));
	}

	public static void takesShortPrimitive(short[] outparam, int type)
	{
		outparam[0]+=outparam[0];
	}
	public static void takesShort(Short[] outparam, int type)
	{
		outparam[0] = new Short((byte)(outparam[0] == null ? 33 : outparam[0].shortValue()*2));
	}

	public static void takesIntegerPrimitive(int[] outparam, int type)
	{
		outparam[0]+=outparam[0];
	}
	public static void takesInteger(Integer[] outparam, int type)
	{
		outparam[0] = new Integer(outparam[0] == null ? 33 : outparam[0].intValue()*2);
	}

	public static void takesLongPrimitive(long[] outparam, int type)
	{
		outparam[0]+=outparam[0];
	}
	public static void takesLong(Long[] outparam, int type)
	{
		outparam[0] = new Long(outparam[0] == null ? 33 : outparam[0].longValue()*2);
	}

	public static void takesDoublePrimitive(double[] outparam, int type)
	{
		outparam[0]+=outparam[0];
	}
	public static void takesDouble(Double[] outparam, int type)
	{
		outparam[0] = new Double(outparam[0] == null ? 33 : outparam[0].doubleValue()*2);
	}

	public static void takesFloatPrimitive(float[] outparam, int type)
	{
		outparam[0]+=outparam[0];
	}
	public static void takesFloat(Float[] outparam, int type)
	{
		outparam[0] = new Float(outparam[0] == null ? 33 : outparam[0].floatValue()*2);
	}

	public static void takesBooleanPrimitive(boolean[] outparam, int type)
	{
		outparam[0] = true;
	}
	public static void takesBoolean(Boolean[] outparam, int type)
	{
		outparam[0] = new Boolean(true);
	}

	public static void takesBigDecimal(BigDecimal[] outparam, int type)
	{
		outparam[0] = (outparam[0] == null ? new BigDecimal("33") : outparam[0].add(outparam[0]));
		outparam[0].setScale(4, BigDecimal.ROUND_DOWN);
	}

	public static void takesByteArray(byte[][] outparam, int type)
	{
		byte[] myarray = new byte[16];
		myarray[0] = (byte)255;
		outparam[0] = myarray;
	}

	public static void takesDate(Date[] outparam, int type)
	{
		outparam[0] = Date.valueOf("1966-06-06");
	}

	public static void takesTime(Time[] outparam, int type)
	{
		outparam[0] = Time.valueOf("06:06:06");
	}

	public static void takesTimestamp(Timestamp[] outparam, int type)
	{
		outparam[0] = Timestamp.valueOf("1966-06-06 06:06:06.666");
	}

	public static void takesString(String[] outparam, int type) throws Throwable
	{
		outparam[0] = getStringOfType(type);
	}

	public static void takesBigInteger(BigInteger[] outparam, int type)
	{
		outparam[0] = (outparam[0] == null ? new BigInteger("33") : outparam[0].add(outparam[0]));
	}
	
	
	/////////////////////////////////////////////////////////////
	//	
	// RETURN PARAMETER METHODS
	//
	/////////////////////////////////////////////////////////////
	public static void returnsNothing()
	{
	}

	public static byte returnsByteP(int type)
	{
		return 66;
	}
	public static Byte returnsByte(int type)
	{
		return new Byte((byte)66);
	}

	public static short returnsShortP(int type)
	{
		return 666;
	}
	public static Short returnsShort(int type)
	{
		return new Short((short)666);
	}

	public static int returnsIntegerP(int type)
	{
		return 666;
	}
	public static Integer returnsInteger(int type)
	{
		return new Integer(666);
	}

	public static long returnsLongP(int type)
	{
		return 666;
	}
	public static Long returnsLong(int type)
	{
		return new Long(666);
	}

	public static float returnsFloatP(int type)
	{
		return 666;
	}
	public static Float returnsFloat(int type)
	{
		return new Float(666);
	}

	public static double returnsDoubleP(int type)
	{
		return 666;
	}
	public static Double returnsDouble(int type)
	{
		return new Double(666);
	}


	public static BigDecimal returnsBigDecimal(int type)
	{
		return new BigDecimal(666d);
	}

	public static byte[] returnsByteArray(int type)
	{
		byte[] myarray = new byte[16];
		myarray[0] = (byte)255;
		return myarray;
	}

	public static String returnsString(int type) throws Throwable
	{
		return getStringOfType(type);
	}

	public static Date returnsDate(int type)
	{
		return Date.valueOf("1966-06-06");
	}

	public static Time returnsTime(int type)
	{
		return Time.valueOf("06:06:06");
	}

	public static Timestamp returnsTimestamp(int type)
	{
		return Timestamp.valueOf("1966-06-06 06:06:06.666");
	}

	public static BigInteger returnsBigInteger(int type)
	{
		return new BigInteger("666");
	}


	// these come from the performance test JDBC.Parameters that was failing
	private static void testManyOut(Connection conn) throws SQLException {

		System.out.println("start testManyOut");

		Statement scp = conn.createStatement();

		scp.execute("CREATE PROCEDURE OP_OUT " +
			"(OUT I1 INT, OUT I2 INT, OUT I3 INT, OUT I4 INT, OUT I5 INT, "+
			"OUT V1 VARCHAR(40), OUT V2 VARCHAR(40), OUT V3 VARCHAR(40), OUT V4 VARCHAR(40), OUT V5 VARCHAR(40)) "+

			"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");


		scp.execute("CREATE PROCEDURE OP_INOUT " +
			"(INOUT I1 INT, INOUT I2 INT, INOUT I3 INT, INOUT I4 INT, INOUT I5 INT, " +
			"INOUT V1 VARCHAR(40), INOUT V2 VARCHAR(40), INOUT V3 VARCHAR(40), INOUT V4 VARCHAR(40), INOUT V5 VARCHAR(40)) " +

			"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");


		CallableStatement csOut_cs = conn.prepareCall("CALL OP_OUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		CallableStatement csInOut_cs = conn.prepareCall("CALL OP_INOUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

		System.out.println("Ten OUT parameters");

		executeOutput(csOut_cs);
		executeOutput(csOut_cs);

		csOut_cs.close();

		System.out.println("Ten INOUT parameters");


		setupInput(csInOut_cs);
		executeOutput(csInOut_cs);

		setupInput(csInOut_cs);
		executeOutput(csInOut_cs);

		csInOut_cs.close();

		scp.execute("DROP PROCEDURE OP_OUT");
		scp.execute("DROP PROCEDURE OP_INOUT");
		scp.close();


		System.out.println("end testManyOut");


	}


	private static void setupInput(PreparedStatement ps) throws SQLException {

		ps.setInt(1, 0);
		ps.setInt(2, 0);
		ps.setInt(3, 99);
		ps.setInt(4, 103);
		ps.setInt(5, 1456);

		ps.setNull(6, Types.CHAR);
		ps.setString(7, null);
		ps.setString(8, "hello");
		ps.setString(9, "goodbye");
		ps.setString(10, "welcome");
	}
	private static void executeOutput(CallableStatement cs) throws SQLException {

		for (int p = 1; p <= 5; p++)
			cs.registerOutParameter(p, Types.INTEGER);

		for (int p = 6; p <= 10; p++)
			cs.registerOutParameter(p, Types.VARCHAR);

		cs.execute();

		for (int p = 1; p <= 5; p++) {
			System.out.println("  " + p + " = " + cs.getInt(p) + " was null " + cs.wasNull());
	
		}
		for (int p = 6; p <= 10; p++) {
			System.out.println("  " + p + " = " + cs.getString(p) + " was null " + cs.wasNull());
		}
	}


	public static void output(int[] a1, int[] a2, int[] a3, int[] a4, int[] a5,
		String[] s1, String[] s2, String[] s3, String[] s4, String[] s5) {

		System.out.println("  a1 = " + a1[0]);
		System.out.println("  a2 = " + a2[0]);
		System.out.println("  a3 = " + a3[0]);
		System.out.println("  a4 = " + a4[0]);
		System.out.println("  a5 = " + a5[0]);

		System.out.println("  s1 = " + s1[0]);
		System.out.println("  s2 = " + s2[0]);
		System.out.println("  s3 = " + s3[0]);
		System.out.println("  s4 = " + s4[0]);
		System.out.println("  s5 = " + s5[0]);

		a1[0] = 0;
		a2[0] = 0;
		a3[0] = 77;
		a4[0] = 4;
		a5[0] = 2003;

		s1[0] = null;
		s2[0] = null;
		s3[0] = "cloudscape";
		s4[0] = "jbms";
		s5[0] = "IBM CS";
	}

	private static void test5116(Connection conn) throws Throwable
	{
		System.out.println("==============================================");
		System.out.println("TESTING FIX OF 5116 -- VAR BIT VARYING INPUT");
		System.out.println("==============================================\n");

		Statement stmt = conn.createStatement();
		stmt.executeUpdate("CREATE TABLE ACTIVITY_INSTANCE_T (" +
    "AIID                               char(16) for bit data              NOT NULL ," +
    "KIND                               INTEGER                            NOT NULL ," +
    "PIID                               char(16) for bit data              NOT NULL ," +
    "PTID                               char(16) for bit data              NOT NULL ," +
    "ATID                               char(16) for bit data              NOT NULL ," +
    "RUN_MODE                           INTEGER                            NOT NULL ," +
    "FINISHED                           TIMESTAMP                                   ," +
    "ACTIVATED                          TIMESTAMP                                   ," +
    "STARTED                            TIMESTAMP                                   ," +
    "LAST_MODIFIED                      TIMESTAMP                                   ," +
    "LAST_STATE_CHANGE                  TIMESTAMP                                   ," +
    "STATE                              INTEGER                            NOT NULL ," +
    "TRANS_COND_VALUES                  VARCHAR(66) FOR BIT DATA           NOT NULL ," +
    "NUM_CONN_ACT_EVA                   INTEGER                            NOT NULL ," +
    "NUMBER_OF_ITERATIONS               INTEGER                            NOT NULL ," +
    "NUMBER_OF_RETRIES                  INTEGER                            NOT NULL ," +
    "HAS_CUSTOM_ATTRIBUTES              SMALLINT                           NOT NULL ," +
    "NON_BLOCK_PTID                     char(16) for bit data              NOT NULL ," +
    "NON_BLOCK_PIID                     char(16) for bit data              NOT NULL ," +
    "EXPIRES                            TIMESTAMP                                   ," +
    "TASK_ID                            VARCHAR(254)                                ," +
    "UNHANDLED_EXCEPTION                BLOB(3993600)                       ," +
    "SUB_PROCESS_PIID                   char(16) for bit data                                    ," +
    "OWNER                              VARCHAR(32)                                 ," +
    "USER_INPUT                         VARCHAR(130) FOR BIT DATA                   ," +
    "DESCRIPTION                        VARCHAR(254)                                ," +
    "VERSION_ID                         SMALLINT                           NOT NULL ," +
    "PRIMARY KEY ( AIID ) )");

		stmt.execute("CREATE PROCEDURE doInsertion(IN P1 VARCHAR(2) FOR BIT DATA) " +
						"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.doInsertion'" +
						" MODIFIES SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA");

		CallableStatement cs = conn.prepareCall("call doInsertion (?)");
		cs.setNull(1, java.sql.Types.VARBINARY);
		cs.execute();
        byte [] b = new byte[2];
        b[0]=1; b[1] = 2;
        cs.setBytes( 1, b );
		cs.execute();
		cs.close();
		stmt.executeUpdate("DROP PROCEDURE doInsertion");
		stmt.close();
	}

	public static void doInsertion (byte[] p25) throws Throwable
	{
		Connection connNested = DriverManager.getConnection("jdbc:default:connection");
		Statement stmt = connNested.createStatement();
		stmt.executeUpdate("delete from ACTIVITY_INSTANCE_T");

        String strStmt = "INSERT INTO ACTIVITY_INSTANCE_T VALUES( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )";
        PreparedStatement pstmt = connNested.prepareStatement( strStmt );

        byte [] b = new byte[2];
        b[0]=1;
		byte[] b2 = new byte[1];
		b2[0] = 0;

         pstmt.setBytes( 1, b ); //ids
         pstmt.setInt( 2, 0);
         pstmt.setBytes( 3, b );
         pstmt.setBytes( 4, b );
         pstmt.setBytes( 5, b );
         pstmt.setInt( 6, 0);
         pstmt.setNull( 7, java.sql.Types.TIMESTAMP);
         pstmt.setNull( 8, java.sql.Types.TIMESTAMP);
         pstmt.setNull( 9, java.sql.Types.TIMESTAMP);
         pstmt.setNull( 10, java.sql.Types.TIMESTAMP);
         pstmt.setNull( 11, java.sql.Types.TIMESTAMP);
         pstmt.setInt( 12, 0);
         pstmt.setBytes( 13, b );

         pstmt.setInt( 14, 0);
         pstmt.setInt( 15, 0);
         pstmt.setInt( 16, 0);
         pstmt.setBoolean( 17, false);
         pstmt.setBytes( 18, b );
         pstmt.setBytes( 19, b );
         pstmt.setNull( 20, java.sql.Types.TIMESTAMP);
         pstmt.setNull( 21, java.sql.Types.VARCHAR);
         pstmt.setNull( 22, java.sql.Types.BLOB );
         pstmt.setNull( 23, java.sql.Types.VARBINARY );
         pstmt.setNull( 24, java.sql.Types.VARCHAR);
		if (p25 == null)
             pstmt.setNull( 25, java.sql.Types.VARBINARY);
		else
			 pstmt.setBytes(25, p25);
         pstmt.setNull( 26, java.sql.Types.VARCHAR);
         pstmt.setShort( 27, (short) 0);
         pstmt.executeUpdate();
         pstmt.close();

         pstmt = connNested.prepareStatement( "SELECT version_id, user_input FROM activity_instance_t");
         ResultSet resultSet = pstmt.executeQuery();
         System.out.println("Executed query");
         while( resultSet.next() )
         {
            System.out.println("i= " + resultSet.getInt(1) );
            byte [] userInput = resultSet.getBytes(2);
            if( userInput == null || resultSet.wasNull() )
            {
				if( userInput == null)
               		System.out.println("UserInput = null");
				if (resultSet.wasNull())
               		System.out.println("resultSet wasNull");
            }
            else
            {
               System.out.println("UserInput length  = " + userInput.length + " bytes");
               for( int i=0; i<userInput.length; i++ )
               {
                  System.out.println( i + ") = " + userInput[i] );
               }
            }
         }
         System.out.println("Close result set.");
         resultSet.close();
         pstmt.close();
         stmt.close();
		 connNested.close();
   }
}