casting.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.casting

   Copyright 2004 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.lang;

import org.apache.derby.tools.ij;
import org.apache.derbyTesting.functionTests.util.TestUtil;
import org.apache.derby.tools.JDBCDisplayUtil;
 
import java.sql.*;
import java.math.*;
import java.io.*;


public class casting {

	public static String VALID_DATE_STRING = "'2000-01-01'";
	public static String VALID_TIME_STRING = "'15:30:20'";
	public static String VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'";
	public static String NULL_VALUE="NULL";

	public static String ILLEGAL_CAST_EXCEPTION_SQLSTATE = "42846";
	public static String LANG_NOT_STORABLE_SQLSTATE  = "42821";
	public static String LANG_NOT_COMPARABLE_SQLSTATE = "42818";
	public static String METHOD_NOT_FOUND_SQLSTATE = "42884";
	public static String LANG_FORMAT_EXCEPTION_SQLSTATE = "22018";

	public static int SQLTYPE_ARRAY_SIZE = 17 ;
	public static int SMALLINT_OFFSET = 0;
	public static int INTEGER_OFFSET = 1;
	public static int BIGINT_OFFSET = 2;
	public static int DECIMAL_OFFSET = 3;
	public static int REAL_OFFSET = 4;
	public static int DOUBLE_OFFSET = 5;
	public static int CHAR_OFFSET = 6;
	public static int VARCHAR_OFFSET = 7;
	public static int LONGVARCHAR_OFFSET = 8;
	public static int CHAR_FOR_BIT_OFFSET = 9;
	public static int VARCHAR_FOR_BIT_OFFSET = 10;
	public static int LONGVARCHAR_FOR_BIT_OFFSET = 11;
	public static int CLOB_OFFSET = 12;
	public static int DATE_OFFSET = 13;
	public static int TIME_OFFSET = 14;
	public static int TIMESTAMP_OFFSET = 15;
	public static int BLOB_OFFSET = 16;


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


	public static int NULL_DATA_OFFSET = 0;  // offset of NULL value
	public static int VALID_DATA_OFFSET = 1;  // offset of NULL value

	// rows are data types.
	// data is NULL_VALUE, VALID_VALUE
	// Should add Minimum, Maximum and out of range.
 public static String[][]SQLData =
	{
		{NULL_VALUE, "0"},       // SMALLINT
		{NULL_VALUE,"11"},       // INTEGER
		{NULL_VALUE,"22"},       // BIGINT
		{NULL_VALUE,"3.3"},      // DECIMAL(10,5)
		{NULL_VALUE,"4.4"},      // REAL,
		{NULL_VALUE,"5.5"},      // DOUBLE
		{NULL_VALUE,"'7'"},      // CHAR(60)
		{NULL_VALUE,"'8'"},      //VARCHAR(60)",
		{NULL_VALUE,"'9'"},      // LONG VARCHAR
		{NULL_VALUE,"X'10aa'"},  // CHAR(60)  FOR BIT DATA
		{NULL_VALUE,"X'10bb'"},  // VARCHAR(60) FOR BIT DATA
		{NULL_VALUE,"X'10cc'"},  //LONG VARCHAR FOR BIT DATA
		{NULL_VALUE,"'13'"},     //CLOB(1k)
		{NULL_VALUE,VALID_DATE_STRING},        // DATE
		{NULL_VALUE,VALID_TIME_STRING},        // TIME
		{NULL_VALUE,VALID_TIMESTAMP_STRING},   // TIMESTAMP
		{NULL_VALUE,"X'01dd'"}                 // BLOB
	};
 



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

	/**
	   DB2 Table 146 - Supported explicit casts between Built-in DataTypes

	   This table has THE FOR BIT DATA TYPES broken out into separate columns
	   for clarity and testing
	**/


	public static final boolean[][]  T_146 = {
		
    // Types.             S  I  B  D  R  D  C  V  L  C  V  L  C  D  T  T  B
	//                    M  N  I  E  E  O  H  A  O  H  A  O  L  A  I  I  L
	//                    A  T  G  C  A  U  A  R  N  A  R  N  O  T  M  M  O
    //                    L  E  I  I  L  B  R  C  G  R  C  G  B  E  E  E  B
    //                    L  G  N  M     L     H  V  .  H  V           S
    //                    I  E  T  A     E     A  A  B  .  A           T
    //                    N  R     L           R  R  I  B  R           A
	//                    T                       C  T  I  .           M
    //                                            H     T  B           P
	//                                            A        I
	//                                            R        T
/* 0 SMALLINT */        { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
/* 1 INTEGER  */        { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
/* 2 BIGINT   */        { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
/* 3 DECIMAL  */        { X, X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _ },
/* 4 REAL     */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 5 DOUBLE   */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 6 CHAR     */        { X, X, X, X, _, _, X, X, X, _, _, _, X, X, X, X, _ },
/* 7 VARCHAR  */        { X, X, X, X, _, _, X, X, X, _, _, _, X, X, X, X, _ },
/* 8 LONGVARCHAR */     { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
/* 9 CHAR FOR BIT */    { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X },
/* 10 VARCH. BIT   */   { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X },
/* 11 LONGVAR. BIT */   { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, X },
/* 12 CLOB         */   { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
/* 13 DATE         */   { _, _, _, _, _, _, X, X, _, _, _, _, _, X, _, _, _ },
/* 14 TIME         */   { _, _, _, _, _, _, X, X, _, _, _, _, _, _, X, _, _ },
/* 15 TIMESTAMP    */   { _, _, _, _, _, _, X, X, _, _, _, _, _, X, X, X, _ },
/* 16 BLOB         */   { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X },

	};

	/**
	 * DB2 uses Table 147 to describe  Data Type Compatibility for Assignments 
	 *
	 * The table 147a covers the assignments as they do differ somewhat 
	 *  from comparisons which can be found in 147b
	   
	 * This table has DATA TYPES for operands rather than lumping types
	 * together. Here is the mapping from DB2 Table 147 to this table.
	 * Binary Integer = SMALLINT, INTEGER, BIGINT
	 * Decimal Number = DECIMAL/(NUMERIC)
	 * Floating Point = REAL, DOUBLE/(FLOAT)
	 * Character String = CHAR, VARCHAR, LONGVARCHAR
	 * Binary String = CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR
	 * BIT DATA
	 * Graphic String = not suppported
	 * Date = DATE
	 * Time = TIME
	 * TimeStamp = TIMESTAMP
	 * Binary String = literal hexadecimal, CHAR FOR BIT DATA, VARCHAR FOR BIT
	 *                 DATA, LONG VARCHAR FOR BIT
	 * CLOB and BLOB are not covered in Table 147 but are included here 
	 * for clarity and testing
	**/

	public static final boolean[][]  T_147a = {
		
    // Types.             S  I  B  D  R  D  C  V  L  C  V  L  C  D  T  T  B
	//                    M  N  I  E  E  O  H  A  O  H  A  O  L  A  I  I  L
	//                    A  T  G  C  A  U  A  R  N  A  R  N  O  T  M  M  O
    //                    L  E  I  I  L  B  R  C  G  R  C  G  B  E  E  E  B
    //                    L  G  N  M     L     H  V  .  H  V           S
    //                    I  E  T  A     E     A  A  B  .  A           T
    //                    N  R     L           R  R  I  B  R           A
	//                    T                       C  T  I  .           M
    //                                            H     T  B           P
	//                                            A        I
	//                                            R        T
/* 0 SMALLINT */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 1 INTEGER  */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 2 BIGINT   */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 3 DECIMAL  */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 4 REAL     */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 5 DOUBLE   */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 6 CHAR     */        { _, _, _, _, _, _, X, X, X, _, _, _, X, X, X, X, _ },
/* 7 VARCHAR  */        { _, _, _, _, _, _, X, X, X, _, _, _, X, X, X, X, _ },
/* 8 LONGVARCHAR */     { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
/* 9 CHAR FOR BIT */    { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _ },
/* 10 VARCH. BIT   */   { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _ },
/* 11 LONGVAR. BIT */   { _, _, _, _, _, _, _, _, _, X, X, X, _, _, _, _, _ },
/* 12 CLOB         */   { _, _, _, _, _, _, X, X, X, _, _, _, X, _, _, _, _ },
/* 13 DATE         */   { _, _, _, _, _, _, X, X, _, _, _, _, _, X, _, _, _ },
/* 14 TIME         */   { _, _, _, _, _, _, X, X, _, _, _, _, _, _, X, _, _ },
/* 15 TIMESTAMP    */   { _, _, _, _, _, _, X, X, _, _, _, _, _, _, _, X, _ },
/* 16 BLOB         */   { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, X },

	};


	// Comparisons table
	// Comparison's are different than assignments because
	// Long types cannot be compared.
	public static final boolean[][]  T_147b = {
		
    // Types.             S  I  B  D  R  D  C  V  L  C  V  L  C  D  T  T  B
	//                    M  N  I  E  E  O  H  A  O  H  A  O  L  A  I  I  L
	//                    A  T  G  C  A  U  A  R  N  A  R  N  O  T  M  M  O
    //                    L  E  I  I  L  B  R  C  G  R  C  G  B  E  E  E  B
    //                    L  G  N  M     L     H  V  .  H  V           S
    //                    I  E  T  A     E     A  A  B  .  A           T
    //                    N  R     L           R  R  I  B  R           A
	//                    T                       C  T  I  .           M
    //                                            H     T  B           P
	//                                            A        I
	//                                            R        T
/* 0 SMALLINT */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 1 INTEGER  */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 2 BIGINT   */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 3 DECIMAL  */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 4 REAL     */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 5 DOUBLE   */        { X, X, X, X, X, X, _, _, _, _, _, _, _, _, _, _, _ },
/* 6 CHAR     */        { _, _, _, _, _, _, X, X, _, _, _, _, _, X, X, X, _ },
/* 7 VARCHAR  */        { _, _, _, _, _, _, X, X, _, _, _, _, _, X, X, X, _ },
/* 8 LONGVARCHAR */     { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },
/* 9 CHAR FOR BIT */    { _, _, _, _, _, _, _, _, _, X, X, _, _, _, _, _, _ },
/* 10 VARCH. BIT   */   { _, _, _, _, _, _, _, _, _, X, X, _, _, _, _, _, _ },
/* 11 LONGVAR. BIT */   { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },
/* 12 CLOB         */   { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },
/* 13 DATE         */   { _, _, _, _, _, _, X, X, _, _, _, _, _, X, _, _, _ },
/* 14 TIME         */   { _, _, _, _, _, _, X, X, _, _, _, _, _, _, X, _, _ },
/* 15 TIMESTAMP    */   { _, _, _, _, _, _, X, X, _, _, _, _, _, _, _, X, _ },
/* 16 BLOB         */   { _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ },

	};

	

	public static boolean isDerbyNet;
	public static boolean isDB2;
	//testNum just print and increments with each query display
	public static int testNum = 1;
 
	public static void main(String[] args) throws Exception {
		String framework = System.getProperty("framework");
		if (framework != null && framework.toUpperCase().equals("DB2JCC"))
			isDB2 = true;

		isDerbyNet = TestUtil.isNetFramework();
		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);
			 createTables(conn);
			 testAssignments(conn);
			 testExplicitCasts(conn);
			 testComparisons(conn);
			 conn.close();
		}
		catch (SQLException sqle) {
			unexpectedException(sqle);
		}
		catch (Throwable t) {
			t.printStackTrace(System.out);
		}
	}

	public static void testExplicitCasts(Connection conn)
		throws SQLException, Throwable
	{
		
		System.out.println("**testExplicitCasts starting");
		
		ResultSet rs = null;
		
		// Try Casts from each type to the 
		for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) {
			
			String sourceTypeName = SQLTypes[sourceType];
			for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
				for (int targetType =0; targetType < SQLTypes.length; targetType++)
				{
				try {
					String convertString = null;
					String targetTypeName = SQLTypes[targetType];
								// For casts from Character types use strings that can
								// be converted to the targetType.
					
					convertString = getCompatibleString(sourceType,
														targetType,dataOffset);


					String query = "VALUES CAST (CAST (" + convertString +
						" AS " + SQLTypes[sourceType] + ") AS " +
						SQLTypes[targetType] + " )";
					executeQueryAndDisplay(conn,query);
					checkSupportedCast(sourceType,targetType);
				} catch (SQLException se)
				{
					String sqlState = se.getSQLState();
					if (!isSupportedCast(sourceType,targetType))
					{
						if(isCastException(se))
							System.out.println("EXPECTED CASTING EXCEPTION: " +
											   se.getMessage());
						else
							gotWrongException(se);
					}
					else 
						unexpectedException(se);
				}
			}
		}

		conn.commit();			 			
			
	}

	public static void createTables(Connection conn)
		throws SQLException, Throwable
	
	{
		System.out.println("**createTables  starting");

		Statement scb = conn.createStatement();

		for (int type = 0; type < SQLTypes.length; type++) 
		{
			String typeName = SQLTypes[type];
			String tableName = getTableName(type);

			try {
				scb.executeUpdate("DROP TABLE " + tableName);
			}
			catch(SQLException se)
			{// ignore drop error
			}
			String createSQL = "create table "+ 
				tableName + " (c " +
				typeName+ " )";
			System.out.println(createSQL);
			scb.executeUpdate(createSQL);
		}

		scb.close();
		conn.commit();
	}



	public static void testAssignments(Connection conn)
		throws SQLException, Throwable
	{
		
		System.out.println("**testAssignments starting");
		Statement scb = conn.createStatement();		
		ResultSet rs = null;
		

		// Insert's using literals
		System.out.println("* testing literal inserts");
		
		for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++)
			for (int type = 0; type < SQLTypes.length; type++) {
			try {
				String tableName = getTableName(type);
				
				String insertSQL = "insert into " + tableName + " values( " +
					SQLData[type][dataOffset] + ")";
				System.out.println(insertSQL);
				scb.executeUpdate(insertSQL);
			}
			catch (SQLException se)
			{
				// literal inserts are ok for everything but BLOB
			if (type == BLOB_OFFSET)
				System.out.println("EXPECTED EXCEPTION inserting literal into BLOB . " + se.getMessage());
			else 
				gotWrongException(se);
			}
		}
		
		// Try to insert each sourceType into the targetType table
		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);
					
					String insertValuesString = " VALUES CAST(" +
					convertString + " AS " + sourceTypeName + ")";
					
					
					String insertSQL = "INSERT INTO " + targetTableName + 
						insertValuesString;
					System.out.println(insertSQL);
					scb.executeUpdate(insertSQL);
					checkSupportedAssignment(sourceType, targetType);

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



	public static void testComparisons(Connection conn)
		throws SQLException, Throwable
	{
		
		System.out.println("**testComparisons starting");
		Statement scb = conn.createStatement();		
		ResultSet rs = null;
		

		// Comparison's  using literals
		System.out.println("* testing literal comparisons");


		for (int type = 0; type < SQLTypes.length; type++) {
			try {
				int dataOffset = 1; // don't use null values
				String tableName = getTableName(type);