casting.java

derby database source code.good for you.

				
				String compareSQL = "SELECT distinct c FROM " + tableName + 
					" WHERE c = " + SQLData[type][dataOffset];
				System.out.println(compareSQL);
				rs = scb.executeQuery(compareSQL);
				JDBCDisplayUtil.DisplayResults(System.out,rs,conn);
			}
			catch (SQLException se)
			{
				// literal comparisons are ok for everything but BLOB
				if (isLongType(type))
					System.out.println("EXPECTED EXCEPTION comparing long type. " + se.getMessage());
				else 
					gotWrongException(se);
			}
		}
		
		
		// Try to compare  each sourceType with the targetType
		for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
			for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
				String sourceTypeName = SQLTypes[sourceType];
				for (int targetType =0; targetType < SQLTypes.length; targetType++)
				{
				try {
					String convertString = null;
					String targetTableName = getTableName(targetType);
					
					

					// For assignments  Character types use strings that can
					// be converted to the targetType.
					convertString = getCompatibleString(sourceType,
														targetType,dataOffset);
					
					// Make sure table has just compatible data
					scb.executeUpdate("DELETE FROM " + targetTableName);
					String insertValuesString = " VALUES CAST(" +
						convertString + " AS " + sourceTypeName + ")";
					
					String insertSQL = "INSERT INTO " + targetTableName + 
						insertValuesString;
					
					String compareSQL = "select c from " + 
						targetTableName + " WHERE c = CAST(" + convertString
						+ " AS " + sourceTypeName + ")";	

					System.out.println(compareSQL);
					rs = scb.executeQuery(compareSQL);
					JDBCDisplayUtil.DisplayResults(System.out,rs,conn);
					checkSupportedComparison(sourceType, targetType);

				} catch (SQLException se)
				{
					String sqlState = se.getSQLState();
					if (!isSupportedComparison(sourceType,targetType) &&
						isNotComparableException(se) ||
						isCastException(se))
						System.out.println("EXPECTED EXCEPTION: " + 
										   sqlState + ":" + se.getMessage());
					else 
						gotWrongException(se);
				}
				}
			}
		scb.close();
		conn.commit();			 			
			
	}


	public static boolean isSupportedCast(int sourceType, int targetType)
	{
		return T_146[sourceType][targetType];
	}

	public static boolean isSupportedAssignment(int sourceType, int targetType)
	{
		return T_147a[sourceType][targetType];
	}

	public static boolean isSupportedComparison(int sourceType, int targetType)
	{
		return T_147b[sourceType][targetType];
	}


	public static boolean isCastException (SQLException se)
	{
		return sqlStateMatches(se,ILLEGAL_CAST_EXCEPTION_SQLSTATE);
	}

	public static boolean isMethodNotFoundException (SQLException se)
	{
		return sqlStateMatches(se, METHOD_NOT_FOUND_SQLSTATE);
	}

	public static boolean sqlStateMatches(SQLException se, 
										   String expectedValue)
	{
		String sqlState = se.getSQLState();
		if ((sqlState != null) &&
			(sqlState.equals(expectedValue)))
			return true;
		return false;
	}

	public static boolean isNotStorableException(SQLException se)
	{
		String sqlState = se.getSQLState();
		if ((sqlState != null) &&
			(sqlState.equals(LANG_NOT_STORABLE_SQLSTATE)))
			return true;
		return false;

	}

	public static boolean isNotComparableException(SQLException se)
	{
		String sqlState = se.getSQLState();
		if ((sqlState != null) &&
			(sqlState.equals(LANG_NOT_COMPARABLE_SQLSTATE)))
			return true;
		return false;
	}

	public static void unexpectedException(SQLException sqle) {

		String sqlState = sqle.getSQLState();
		
		if (isDB2  && (sqlState != null) &&
			sqlState.equals("22003"))
		{
			System.out.print("WARNING: DB2 overflow exception -");
		}
		else
			System.out.print("FAIL unexpected exception - ");
		
		showException(sqle);
		sqle.printStackTrace(System.out);
	}


	/**
	 * We got an exception when one was expected, but it was the
	 * wrong one.  For DB2 we will just print a warning.
	 * @param sqle
	 */
	public static void gotWrongException(SQLException sqle) {
		if (isDB2)
		{
			System.out.print("WARNING: DB2 exception different from Derby-" );
			showException(sqle);
		}
		else unexpectedException(sqle);
	}

	/**
	 * Show an expected exception
	 * @param sqle SQL Exception
	 */
	public static void expectedException(SQLException sqle) {
			System.out.print("EXPECTED EXCEPTION:" );
			showException(sqle);
			System.out.println("\n");
	}


	public static void showException(SQLException sqle) {
		do {
			String state = sqle.getSQLState();
			if (state == null)
				state = "?????";

			String msg = sqle.getMessage();
			if  (msg == null)
				msg = "?? no message ??";

			System.out.print(" (" + state + "):" + msg);
			sqle = sqle.getNextException();
		} while (sqle != null);
	}


	/**
	 * Display Query , execute and display results.
	 * @param conn  Connection to use
	 * @param query to execute
	 */	

	public static void executeQueryAndDisplay(Connection conn,
													String query)
													throws SQLException 
	{
		Statement stmt = conn.createStatement();
		ResultSet rs;		

		System.out.println("Test #" + testNum++);
		System.out.println(query );
		rs = stmt.executeQuery(query);
		JDBCDisplayUtil.DisplayResults(System.out,rs,conn);

		stmt.close();
	}


	public static boolean isLongType( int typeOffset)
	{
		return ((typeOffset == LONGVARCHAR_OFFSET) ||
				(typeOffset == LONGVARCHAR_FOR_BIT_OFFSET) || 
				(typeOffset == CLOB_OFFSET) ||
				(typeOffset == BLOB_OFFSET));
	}

	public static boolean isCharacterType(int typeOffset)
	{
		return ((typeOffset == CHAR_OFFSET) || 
				(typeOffset == VARCHAR_OFFSET) || 
				(typeOffset == LONGVARCHAR_OFFSET) || 
				(typeOffset == CLOB_OFFSET));
	}

	public static boolean isBinaryType(int typeOffset)
	{
		return ((typeOffset == CHAR_FOR_BIT_OFFSET) || 
				(typeOffset == VARCHAR_FOR_BIT_OFFSET) || 
				(typeOffset == LONGVARCHAR_FOR_BIT_OFFSET) || 
				(typeOffset == BLOB_OFFSET));
	}

	public static boolean isDateTimeTimestamp(int typeOffset)
	{
		return ( (typeOffset == DATE_OFFSET) ||
				 (typeOffset == TIME_OFFSET) ||
				 (typeOffset == TIMESTAMP_OFFSET));

	}

	public static boolean isClob(int typeOffset)
	{
		return (typeOffset == CLOB_OFFSET);
	}
		
	public static boolean isLob(int typeOffset)
	{
		return ((typeOffset == CLOB_OFFSET) || 
				(typeOffset == BLOB_OFFSET));

	}

	public static String getCompatibleString(int sourceType, int targetType,
											  int dataOffset)
	{
		String convertString = null;
		if ((isCharacterType(sourceType) || isBinaryType(sourceType)) &&
			!isLob(sourceType))
			convertString = formatString(SQLData[targetType][dataOffset]);
		else
			convertString = SQLData[sourceType][dataOffset];

		return convertString;
	}
	
	// Data is already a  string (starts with X, or a character string,
	// just return, otherwise bracket with ''s
	public static String formatString(String str)
	{
		if ((str != null) && (
							  str.startsWith("X") ||
							  str.startsWith("'") ||
							  (str == NULL_VALUE)))
			return str;
		else
			return "'" + str + "'";

	}

	public static boolean setValidValue(PreparedStatement ps, int param, int jdbcType)
		throws SQLException {

		switch (jdbcType) {
		case Types.SMALLINT:
			ps.setShort(param, (short) 32);
			return true;
		case Types.INTEGER:
			ps.setInt(param, 32);
			return true;
		case Types.BIGINT:
			ps.setLong(param, 32L);
			return true;
		case Types.REAL:
			ps.setFloat(param, 32.0f);
			return true;
		case Types.FLOAT:
		case Types.DOUBLE:
			ps.setDouble(param, 32.0);
			return true;
		case Types.DECIMAL:
			ps.setBigDecimal(param, new BigDecimal(32.0));
			return true;
		case Types.CHAR:
		case Types.VARCHAR:
		case Types.LONGVARCHAR:
			ps.setString(param, "32");
			return true;
		case Types.BINARY:
		case Types.VARBINARY:
			{
				byte[] data = {(byte) 0x04, (byte) 0x03, (byte) 0xfd, (byte) 0xc3, (byte) 0x73};
				ps.setBytes(param, data);
				return true;
			}
		//Types.LONGVARBINARY:
		case Types.DATE:
			ps.setDate(param, java.sql.Date.valueOf("2004-02-14"));
			return true;
		case Types.TIME:
			ps.setTime(param, java.sql.Time.valueOf("13:26:42"));
			return true;
		case Types.TIMESTAMP:
			ps.setTimestamp(param, java.sql.Timestamp.valueOf("2004-02-23 17:14:24.097625551"));
			return true;
		case Types.CLOB:
			// JDBC 3.0 spec section 16.3.2 explictly states setCharacterStream is OK for setting a CLOB
			ps.setCharacterStream(param, new java.io.StringReader("67"), 2);
			return true;
		case Types.BLOB:
			// JDBC 3.0 spec section 16.3.2 explictly states setBinaryStream is OK for setting a BLOB
			{
				byte[] data = new byte[6];
				data[0] = (byte) 0x82;
				data[1] = (byte) 0x43;
				data[2] = (byte) 0xca;
				data[3] = (byte) 0xfe;
				data[4] = (byte) 0x00;
				data[5] = (byte) 0x32;

			ps.setBinaryStream(param, new java.io.ByteArrayInputStream(data), 6);
			return true;
			}
		default:
			return false;
		}
	}

	/**
	 * Truncates (*) from typename
	 * @param type - Type offset
	 *
	 * @returns  short name of type (e.g DECIMAL instead of DECIMAL(10,5)
	 */

	public static  String getShortTypeName(int type)
	{
		String typeName = SQLTypes[type];
		String shortName = typeName;
		int parenIndex = typeName.indexOf('(');
		if (parenIndex >= 0)
		{
			shortName = typeName.substring(0,parenIndex);
			int endParenIndex =  typeName.indexOf(')');
			shortName = shortName + typeName.substring(endParenIndex+1,typeName.length());
		}
		return shortName;

	}


	/**
	 * Build a unique table name from the type
	 * @param -  table offset
	 * @returns  Table name in format <TYPE>_TAB. Replaces ' ' _;
	 */
	public static  String getTableName(int type)
	{
		return getShortTypeName(type).replace(' ', '_') + "_TAB";
			
	}

	public static void checkSupportedCast(int sourceType, int targetType)
	{
		String description = " Cast from " +
			SQLTypes[sourceType] +
			" to " + SQLTypes[targetType];
		
		if(!isSupportedCast(sourceType,targetType))
			printShouldNotSucceedMessage(description);
	}

	public static void printShouldNotSucceedMessage(String description)
	{
		if (isDB2)
		{
			System.out.println("WARNING:" + description + " which is not supported in Derby works in DB2");
		}
		else
			System.out.println("FAIL:" +  description +
							   " should not be supported");
		
	}

	public static void checkSupportedAssignment(int sourceType, int targetType)
	{
		String description = " Assignment from " +
			SQLTypes[sourceType] +
			" to " + SQLTypes[targetType];
		
		if (!isSupportedAssignment(sourceType,targetType))
			printShouldNotSucceedMessage(description);


	}

	public static void checkSupportedComparison(int sourceType,int targetType)
	{
		String description = " Comparison of " +
			SQLTypes[sourceType] +
			" to " + SQLTypes[targetType];
		
		if (!isSupportedComparison(sourceType,targetType))
			printShouldNotSucceedMessage(description);
	}

  
	//	-- HTML Table generation

	public static void printHTMLTables()
	{
		// For headers.  First four letters of each word
		String [] shortTypes = new String[SQLTypes.length];

		for (int i = 0; i < SQLTypes.length; i++)
			shortTypes[i] = getShortTypeName(i);

		TestUtil.startHTMLPage("Datatype Casting, Assignment, and Comparison", 
					  "[e-mail]");
		
		TestUtil.printBoolArrayHTMLTable("Source Types","Target Types",
										 shortTypes,
										 shortTypes,
										 T_146,
										 "Table 146 - Explicit Casts Allowed by Derby");
		
		TestUtil.printBoolArrayHTMLTable("Source Types","Target Types",
										 shortTypes,
										 shortTypes,
										 T_147a,
										 "Table 147a - Assignments Allowed by Derby");

		TestUtil.printBoolArrayHTMLTable("Source Types","Target Types",
										 shortTypes,
										 shortTypes,
										 T_147b,
										 "Table 147b - Comparisons Allowed by Derby");

	   
		TestUtil.endHTMLPage();
	}


}