parametermapping.java

derby database source code.good for you.

/*

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

   Copyright 2004, 2005 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 org.apache.derby.tools.ij;
import org.apache.derbyTesting.functionTests.util.TestUtil;


import java.sql.*;
import java.math.*;
import java.io.*;

public class parameterMapping {

	private static int[] jdbcTypes =
	{
		Types.TINYINT,
		Types.SMALLINT,
		Types.INTEGER,
		Types.BIGINT,
		Types.REAL,
		Types.FLOAT,
		Types.DOUBLE,
		Types.DECIMAL,
		Types.NUMERIC,
		Types.BIT,
		Types.NULL, // Types.BOOLEAN
		Types.CHAR,
		Types.VARCHAR,
		Types.LONGVARCHAR,
		Types.NULL, //Types.BINARY,
		Types.VARBINARY,
		Types.NULL, //Types.LONGVARBINARY,
		Types.DATE,
		Types.TIME,
		Types.TIMESTAMP,
		Types.CLOB,
		Types.BLOB,
	};


	private static String[] SQLTypes =
	{
		null,
		"SMALLINT",
		"INTEGER",
		"BIGINT",
		"REAL",
		"FLOAT",
		"DOUBLE",
		"DECIMAL(10,5)",
		null,
		null,
		null,
		"CHAR(60)",
		"VARCHAR(60)",
		"LONG VARCHAR",
		"CHAR(60) FOR BIT DATA",
		"VARCHAR(60) FOR BIT DATA",
		"LONG VARCHAR FOR BIT DATA",
		"DATE",
		"TIME",
		"TIMESTAMP",
		"CLOB(1k)",
		"BLOB(1k)",

	};

	private static Class[] B3_GET_OBJECT =
	{
		java.lang.Integer.class, // Types.TINYINT,
		java.lang.Integer.class, // Types.SMALLINT,
		java.lang.Integer.class, // Types.INTEGER,
		java.lang.Long.class, // Types.BIGINT,
		java.lang.Float.class, // Types.REAL,
		java.lang.Double.class, // Types.FLOAT,
		java.lang.Double.class, // Types.DOUBLE,
		java.math.BigDecimal.class, // Types.DECIMAL,
		java.math.BigDecimal.class, // Types.NUMERIC,
		java.lang.Boolean.class, // Types.BIT,
		java.lang.Boolean.class, // Types.BOOLEAN
		java.lang.String.class, // Types.CHAR,
		java.lang.String.class, // Types.VARCHAR,
		java.lang.String.class, // Types.LONGVARCHAR,
		byte[].class, // Types.NULL, //Types.BINARY,
		byte[].class, // Types.VARBINARY,
		byte[].class, // Types.LONGVARBINARY,
		java.sql.Date.class, // Types.DATE,
		java.sql.Time.class, // Types.TIME,
		java.sql.Timestamp.class, // Types.TIMESTAMP,
		java.sql.Clob.class, // Types.CLOB,
		java.sql.Blob.class, // Types.BLOB,
	};





	private static final boolean _ = false;
	private static final boolean X = true;

	/**
		JDBC 3.0 spec Table B6 - Use of ResultSet getter Methods to Retrieve JDBC Data Types
	*/
	public static final boolean[][] B6 = {

    // Types.             T  S  I  B  R  F  D  D  N  B  B  C  V  L  B  V  L  D  T  T  C  B
	//                    I  M  N  I  E  L  O  E  U  I  O  H  A  O  I  A  O  A  I  I  L  L
	//                    N  A  T  G  A  O  U  C  M  T  O  A  R  N  N  R  N  T  M  M  O  O
    //                    Y  L  E  I  L  A  B  I  E     L  R  C  G  A  B  G  E  E  E  B  B
    //                    I  L  G  N     T  L  M  R     E     H  V  R  I  V        S
    //                    N  I  E  T        E  A  I     A     A  A  Y  N  A        T
    //                    T  N  R              L  C     N     R  R     A  R        A
	//                    T                                      C     R  B        M
    //                                                           H     B  I        P
	//                                                           A     I  N
	//                                                           R     N  

/* 0 getByte*/          { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 1 getShort*/         { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 2 getInt*/           { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 3 getLong*/          { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 4 getFloat*/         { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 5 getDouble*/        { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 6 getBigDecimal*/	{ X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 7 getBoolean*/       { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 8 getString*/        { X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _},
/* 9 getBytes*/         { _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _},
/*10 getDate*/          { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, X, _, X, _, _},
/*11 getTime*/          { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X, X, _, _},
/*12 getTimestamp*/     { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, X, X, X, _, _},
/*13 getAsciiStream*/   { _, _, _, _, _, _, _, _, _, _, _, X, X, X, X, X, X, _, _, _, _, _},
/*14 getBinaryStream*/  { _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _},
/*15 getCharStream*/    { _, _, _, _, _, _, _, _, _, _, _, X, X, X, X, X, X, _, _, _, _, _},
/*16 getClob */         { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, _},
/*17 getBlob */         { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X},
		 
/*18 getUnicodeStream */{ _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _},
	};
   

	/**
		JDBC 3.0 Section 13.2.2.1 specifies that table B-2 is used to specify type mappings
		from the Java types (e.g. int as setInt) to the JDBC SQL Type (Types.INT).

		This table does not include stream methods and does not include conversions
		specified elsewhere in the text, Namely

		Section 16.3.2
			setBinaryStream may be used to set a BLOB
			setAsciiStream and setCharacterStream may be used to set a CLOB

		Thus this B2_MOD table is laid out like the B6 table and makes
		the assumptions that

		- Any Java numeric type can be used to set any SQL numeric type
		- Any Java numeric type can be used to set any SQL CHAR type
		- Numeric and date/time java types can be converted to SQL Char values.

		
	*/

    // Types.             T  S  I  B  R  F  D  D  N  B  B  C  V  L  B  V  L  D  T  T  C  B
	//                    I  M  N  I  E  L  O  E  U  I  O  H  A  O  I  A  O  A  I  I  L  L
	//                    N  A  T  G  A  O  U  C  M  T  O  A  R  N  N  R  N  T  M  M  O  O
    //                    Y  L  E  I  L  A  B  I  E     L  R  C  G  A  B  G  E  E  E  B  B
    //                    I  L  G  N     T  L  M  R     E     H  V  R  I  V        S
    //                    N  I  E  T        E  A  I     A     A  A  Y  N  A        T
    //                    T  N  R              L  C     N     R  R     A  R        A
	//                    T                                      C     R  B        M
    //                                                           H     B  I        P
	//                                                           A     I  N
	//                                                           R     N  

	public static boolean[][] B2_MOD = {
/* 0 setByte*/          { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 1 setShort*/         { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 2 setInt*/           { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 3 setLong*/          { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 4 setFloat*/         { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 5 setDouble*/        { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 6 setBigDecimal*/	{ X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 7 setBoolean*/       { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 8 setString*/        { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, X, X, X, _, _},
/* 9 setBytes*/         { _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _},
/*10 setDate*/          { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, X, _, X, _, _},
/*11 setTime*/          { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X, X, _, _},
/*12 setTimestamp*/     { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, X, X, X, _, _},
/*13 setAsciiStream*/   { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _, _, X, _},
/*14 setBinaryStream*/  { _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X},
/*15 setCharStream*/    { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _, _, X, _},
/*16 setClob */         { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, _},
/*17 setBlob */         { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X},
		 
/*18 setUnicodeStream */{ _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _},
	};

	/** Table B5 conversion of Objects using setObject*/

    // Types.             T  S  I  B  R  F  D  D  N  B  B  C  V  L  B  V  L  D  T  T  C  B
	//                    I  M  N  I  E  L  O  E  U  I  O  H  A  O  I  A  O  A  I  I  L  L
	//                    N  A  T  G  A  O  U  C  M  T  O  A  R  N  N  R  N  T  M  M  O  O
    //                    Y  L  E  I  L  A  B  I  E     L  R  C  G  A  B  G  E  E  E  B  B
    //                    I  L  G  N     T  L  M  R     E     H  V  R  I  V        S
    //                    N  I  E  T        E  A  I     A     A  A  Y  N  A        T
    //                    T  N  R              L  C     N     R  R     A  R        A
	//                    T                                      C     R  B        M
    //                                                           H     B  I        P
	//                                                           A     I  N
	//                                                           R     N  
	public static boolean[][] B5 = {
/* 0 String */          { X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _},
/* 1 BigDecimal */      { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 2 Boolean */         { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 3 Integer */         { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 4 Long */            { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 5 Float */           { X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 6 Double */			{ X, X, X, X, X, X, X, X, X, X, X, X, X, X, _, _, _, _, _, _, _, _},
/* 7 byte[] */          { _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _},
/* 8 Date */            { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, X, _, X, _, _},
/* 9 Time */            { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X, _, _, _},
/*10 Timestamp */       { _, _, _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, X, X, X, _, _},
/*11 Blob   */          { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X},
/*12 Clob */            { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X, _},
	};


	private static boolean isDB2jNet;
 
	public static void main(String[] args) throws Exception {
		isDB2jNet = TestUtil.isNetFramework();
		System.out.println("Test parameterMapping starting");
		System.out.println("STILL TO RESOLVE -- Cloudscape getBoolean() allow conversion on strings to match JCC");
		System.out.println("STILL TO RESOLVE -- Cloudscape getXXX() disable on LOBs.");
		try
		{
			// use the ij utility to read the property file and
			// make the initial connection.
			 ij.getPropertyArg(args);
			 Connection conn = ij.startJBMS();

			 conn.setAutoCommit(false);

			 //create simple a table with BLOB and CLOB thta
			 // can be used to for setBlob/setClob testing.
			 Statement scb = conn.createStatement();

			 try {
				 scb.execute("DROP TABLE PM.LOB_GET");
			 }catch (SQLException seq) {
			 }
			 scb.execute("CREATE TABLE PM.LOB_GET(ID INT, B BLOB, C CLOB)");
			 PreparedStatement pscb = conn.prepareStatement("INSERT INTO PM.LOB_GET VALUES (?, ?, ?)");
			 pscb.setInt(1, 0);
			 pscb.setNull(2, Types.BLOB);
			 pscb.setNull(3, Types.CLOB);
			 pscb.executeUpdate();

			 pscb.setInt(1, 1);
			 {
				byte[] data = new byte[6];
				data[0] = (byte) 0x32;
				data[1] = (byte) 0x43;
				data[2] = (byte) 0x72;
				data[3] = (byte) 0x43;
				data[4] = (byte) 0x00;
				data[5] = (byte) 0x37;

				pscb.setBinaryStream(2, new java.io.ByteArrayInputStream(data), 6);
			 }
			 pscb.setCharacterStream(3, new java.io.StringReader("72"), 2);
			 pscb.executeUpdate();
			 scb.close();
			 pscb.close();
			 conn.commit();



			 for (int type = 0; type < SQLTypes.length; type++) {

				String sqlType = SQLTypes[type];

				System.out.println("\n\ngetXXX on : " + (sqlType == null ? Integer.toString(jdbcTypes[type]) : sqlType));
				 if (sqlType == null || jdbcTypes[type] == Types.NULL) {
					 System.out.println("  skipping");
					 continue;
				 }

				 Statement s = conn.createStatement();

				 try {
					 s.execute("DROP TABLE PM.TYPE_AS");
				 }catch (SQLException seq) {
				 }
				 s.execute("CREATE TABLE PM.TYPE_AS(VAL " + SQLTypes[type] + ")");

				 PreparedStatement psi = conn.prepareStatement("INSERT INTO PM.TYPE_AS(VAL) VALUES(?)");
				 psi.setNull(1, jdbcTypes[type]);
				 psi.executeUpdate();


				 PreparedStatement psq = conn.prepareStatement("SELECT VAL FROM PM.TYPE_AS");
				 ResultSet rs = psq.executeQuery();
				 ResultSetMetaData rsmd = rs.getMetaData();
				 if (rsmd.getColumnType(1) != jdbcTypes[type]) {
					 System.out.println("FAIL - mismatch column type " + rsmd.getColumnType(1) + " expected " + jdbcTypes[type]);
				 }
				 rs.close();                                 				 			 

				 // For this data type
				 // Test inserting a NULL value and then performing all the getXXX() calls on it.

				 System.out.println(" NULL VALUE");
				 getXXX(psq, type, true);


				 s.execute("DELETE FROM PM.TYPE_AS");

				 // For this data type
				 // Test inserting a valid value and then performing all the getXXX() calls on it.
				 if (setValidValue(psi, 1, jdbcTypes[type])) {
					 psi.executeUpdate();
					System.out.println(" VALID VALUE");
					getXXX(psq, type, false);
				 }



				 // NOW THE SET METHODS
				 System.out.println("setNull() with all JDBC Types on " + SQLTypes[type]);
				 for (int st = 0; st <= jdbcTypes.length + 1; st++) {

					if (st >= jdbcTypes.length || jdbcTypes[st] != Types.NULL)
					{
						// explictily test Types.NULL.
						 int sqlTypeNull;
						 if (st == jdbcTypes.length + 1)
							sqlTypeNull = 235350345; // bad value
						 else if (st == jdbcTypes.length)
							 sqlTypeNull = Types.NULL;
						 else
							sqlTypeNull = jdbcTypes[st];

						s.execute("DELETE FROM PM.TYPE_AS");

						SQLException sqleResult = null;
						try {
							System.out.print("  setNull(" + TestUtil.sqlNameFromJdbc(sqlTypeNull) + ") ");
							psi.setNull(1, sqlTypeNull);
							psi.executeUpdate();

							getValidValue(psq, jdbcTypes[type]); // yes type, not st

							System.out.println("");

						} catch (SQLException sqle) {
							sqleResult = sqle;
							if ("22005".equals(sqle.getSQLState()))
								System.out.println("IC");
							else
								dumpSQLExceptions(sqle);

						}
						
						/**
						  * Adding this piece of code to test the support for batching of statements.
						  * Some datatypes had problems when batching was turned on which was 
						  * not there when batching was not on, this will test that behaviour
						  * for all such datatypes
						  */
						s.execute("DELETE FROM PM.TYPE_AS");
						try {
							System.out.print("  setNull with batching support(" + TestUtil.sqlNameFromJdbc(sqlTypeNull) + ") ");
							psi.setNull(1, sqlTypeNull);
							psi.addBatch();
							psi.executeBatch();

							getValidValue(psq, jdbcTypes[type]); // yes type, not st

							System.out.println("");

						} catch (SQLException sqle) {							 
							sqleResult = sqle;
							if ("22005".equals(sqle.getSQLState()))
								System.out.println("IC");
							else
								dumpSQLExceptions(sqle);													     
						}						
					}					 
				 }

				 System.out.println("setXXX() with all JDBC Types on " + SQLTypes[type]);
				 System.out.println("For setXXX() methods that pass an object, a null and valid values are checked");
				 setXXX(s, psi, psq, type);				 

				 psi.close();
				 psq.close();
				 s.execute("DROP TABLE PM.TYPE_AS");
				 conn.commit();

				 if (isDB2jNet)
					 continue;

				 // NOW PROCEDURE PARAMETERS
				 try {
					 s.execute("DROP PROCEDURE PMP.TYPE_AS");
				 }catch (SQLException seq) {
				 }
				 String procSQL = "CREATE PROCEDURE PMP.TYPE_AS(" +
					   "IN P1 " + SQLTypes[type] + 
					 ", INOUT P2 " + SQLTypes[type] +
					 ", OUT P3 " + SQLTypes[type] +
					 ") LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL " +
					 " EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pmap'";

				 System.out.println(procSQL);
				 try {
					s.execute(procSQL);
				 } catch (SQLException sqle) {
					 System.out.println(sqle.getSQLState() + ":" + sqle.getMessage());
					 continue;
				 }

				 // For each JDBC type try to register the out parameters with that type.
				 for (int opt = 0; opt < jdbcTypes.length; opt++) {
					 int jopt = jdbcTypes[opt];
					 if (jopt == Types.NULL)
						 continue;

					CallableStatement csp = conn.prepareCall("CALL PMP.TYPE_AS(?, ?, ?)");

					boolean bothRegistered = true;
					System.out.print("INOUT " + sqlType + " registerOutParameter(" + TestUtil.getNameFromJdbcType(jopt) + ") ");
					try {
						csp.registerOutParameter(2, jopt);
						System.out.println("-- OK");
					} catch (SQLException sqle) {
						System.out.println("-- " + sqle.getSQLState());
						bothRegistered = false;
					}
					System.out.print("OUT " + sqlType + " registerOutParameter(" + TestUtil.getNameFromJdbcType(jopt) + ") ");
					try {
						csp.registerOutParameter(3, jopt);
						System.out.println("-- OK");
					} catch (SQLException sqle) {
						System.out.println("-- " + sqle.getSQLState());
						bothRegistered = false;
					}

					if (bothRegistered) {

						try {

						// set the IN value with an accepted value according to its type
						// set the INOUT value with an accepted value according to its registered type
						if (setValidValue(csp, 1, jdbcTypes[type]) && setValidValue(csp, 2, jopt)) {

							csp.execute();

							// now get the INOUT, OUT parameters according to their registered type.
							System.out.print("P2="); getOutValue(csp, 2, jopt); System.out.println("");
							System.out.print("P3="); getOutValue(csp, 3, jopt); System.out.println("");
						}

						} catch (SQLException sqle) {
							dumpSQLExceptions(sqle);
						}
					}

					csp.close();

				 }


				s.execute("DROP PROCEDURE PMP.TYPE_AS");
				s.close();
				conn.commit();
			 }
		}
		catch (SQLException sqle) {
			unexpectedException(sqle);
		}
		catch (Throwable t) {
			t.printStackTrace(System.out);
		}
	}

	private static void getXXX(PreparedStatement ps, int type, boolean isNull) throws SQLException, java.io.IOException {

		{
		System.out.print("  getByte=");
		ResultSet rs = ps.executeQuery();
		rs.next();
		boolean worked;
		SQLException sqleResult = null;;