dbmanagerlimits.java

derby database source code.good for you.

			tempString = "create view v1(" + sbViewColumnNames.toString() + "c" + i + ", c" + (i+1) + ", c" + (i+2) + ") as values (" + sbValuesClause.toString() + "1,1,1)";
			try {
				s.executeUpdate(tempString);
				System.out.println("FAIL - The create view should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("And finally create a view with 2 columns that maximum allowed number of columns");
			tempString = "create view v1(" + sbViewColumnNames.toString() + "c" + i + ", c" + (i+1) + ", c" + (i+2) + ", c" + (i+3) +") as values (" + sbValuesClause.toString() + "1,1,1,1)";
			try {
				s.executeUpdate(tempString);
				System.out.println("FAIL - The create view should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testMostElementsInSelectList( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - most elements allowed in a select list");

			StringBuffer sb = new StringBuffer();
			String tempString = new String();
			int i = 0;
			sb.append("create table t1 (");
			for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_TABLE-2; i++)
				sb.append("c" + i +" int, ");

			Statement s = conn.createStatement();
			tempString = (sb.toString()).concat("c" + i + " int)");
			s.executeUpdate(tempString);

			System.out.println("First try a select with one column less than maximum allowed number of columns");
			s.execute("select * from t1");

			System.out.println("Next try a select with maximum allowed number of columns");
			s.execute("select t1.*,1 from t1");

			System.out.println("Next try a select with one column more than maximum allowed number of columns");
			try {
				s.execute("select t1.*,1,2 from t1");
				System.out.println("FAIL - select should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54004"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("Next try a select with 2 more columns than maximum allowed number of columns");
			try {
				s.execute("select t1.*,1,2,3 from t1");
				System.out.println("FAIL - select should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54004"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			s.executeUpdate("drop table t1");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testMostElementsInOrderBy( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - most columns allowed in a ORDER BY clause");

			StringBuffer sbOrderBy = new StringBuffer();
			String tempString = new String();
			int i = 0;
			sbOrderBy.append("select * from t1 order by ");
			for (i = 0; i < Limits.DB2_MAX_ELEMENTS_IN_ORDER_BY-2; i++)
				sbOrderBy.append("c1, ");

			Statement s = conn.createStatement();
			s.executeUpdate("create table t1 (c1 int not null, c2 int)");
      
			System.out.println("First try order by with one column less than maximum allowed number of columns");
			tempString = (sbOrderBy.toString()).concat("c2");
			s.execute(tempString);

			System.out.println("Next try an order by with maximum allowed number of columns");
			tempString = (sbOrderBy.toString()).concat("c1, c2");
			s.execute(tempString);

			System.out.println("Next try an order by with one column more than maximum allowed number of columns");
			tempString = (sbOrderBy.toString()).concat("c1, c2, c1");
			try {
				s.execute(tempString);
				System.out.println("FAIL - order by should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54004"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("And finally try an order by with 2 more columns than maximum allowed number of columns");
			tempString = (sbOrderBy.toString()).concat("c1, c2, c1");
			try {
				s.execute(tempString);
				System.out.println("FAIL - order by should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54004"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			s.executeUpdate("drop table t1");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testMostElementsInGroupBy( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - most columns allowed in a GROUP BY clause");
			Statement s = conn.createStatement();
			StringBuffer sbGroupBy = new StringBuffer("select 1 from v1, v2, v3, v4, v5, v6, v7 group by ");
			StringBuffer sbValuesClause = new StringBuffer();
			StringBuffer sbViewColumnNames = new StringBuffer();
			String tempString = new String();

			//first create 7 views with 5000 columns each
			int i = 0;
			for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW-1; i++)
				sbValuesClause.append(1 + ", ");

			for (int j = 1; j < 8; j++) {
				for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW-1; i++) {
					sbViewColumnNames.append("c" + j + "" + i + ", ");
				}
				tempString = "create view v" + j + "(" + sbViewColumnNames.toString() + "c" + j + "" + i + ") as values (" + sbValuesClause.toString() + "1)";
				s.executeUpdate(tempString);
				sbViewColumnNames = new StringBuffer();
			}
      
			for (int j = 1; j < 7; j++) {
				for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW; i++)
					sbGroupBy.append("c" + j + "" + i + ", ");
			}
			for (i = 0; i < 2675; i++)
				sbGroupBy.append("c7" + i + ", ");

			System.out.println("First try group by with one column less than maximum allowed number of columns");
			tempString = (sbGroupBy.toString()).concat("c72675");
			s.execute(tempString);

			System.out.println("Next try an group by with maximum allowed number of columns");
			tempString = (sbGroupBy.toString()).concat("c72675, c72675");
			s.execute(tempString);

			System.out.println("And finally try an group by with more columns that maximum allowed number of columns");
			tempString = (sbGroupBy.toString()).concat("c72675, c72676, c72677");
			try {
				s.execute(tempString);
				System.out.println("FAIL - group by should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54004"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			s.executeUpdate("drop view v1");
			s.executeUpdate("drop view v2");
			s.executeUpdate("drop view v3");
			s.executeUpdate("drop view v4");
			s.executeUpdate("drop view v5");
			s.executeUpdate("drop view v6");
			s.executeUpdate("drop view v7");

			s.execute("select 1 from v1 group by c1,c2");
			s.executeUpdate("drop table t1");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testMostParametersInStoredProcedures( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - most parameters allowed for a stored procedure");
			Statement s = conn.createStatement();
			StringBuffer sbCreateProcParams = new StringBuffer();
			StringBuffer sbExecuteProcParams = new StringBuffer();
			String tempString = new String();
			int i = 0;

			for (i = 0; i < Limits.DB2_MAX_PARAMS_IN_STORED_PROCEDURE-2; i++) {
				sbCreateProcParams.append("i" + i + " int, ");
				sbExecuteProcParams.append("1, ");
			}

			System.out.println("First create a procedure with one parameter less than maximum allowed number of parameters");
			tempString = "create procedure P1(" + sbCreateProcParams.toString() + "i" + i +
        " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.lessThanMaxParams\' NO SQL";
			s.executeUpdate(tempString);

			System.out.println("Next create a procedure with maximum allowed number of parameters");
			tempString = "create procedure P2(" + sbCreateProcParams.toString() + "i" + i +
        " int, i" + (i+1) + " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.maxAllowedParams\' NO SQL";
			s.executeUpdate(tempString);
			//just some basic sanity check 
			DatabaseMetaData met = conn.getMetaData();
			getCount(met.getProcedureColumns("", "APP", "P2", null));

			System.out.println("And finally create a procedure with more parameters that maximum allowed number of parameters");
			tempString = "create procedure P3(" + sbCreateProcParams.toString() + "i" + i +
        " int, i" + (i+1) + " int, i" + (i+2) + " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.moreThanMaxAllowedParams\' NO SQL";
			try {
				s.executeUpdate(tempString);
				System.out.println("FAIL - create procedure should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54023"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

		//not running indexes test because it doesn't finish even after running for over 2 hours
		//ALSO, IF WE EVER ENABLE THIS TEST IN FUTURE, WE NEED TO REWRITE THE TEST SO THAT WE TRY TO CREATE OVER
		//32767 *DIFFERENT* INDEXES. AS PART OF DB2 COMPATIBILITY WORK, BUG - 5685 DISALLOWS CREATION OF AN INDEX
		//ON A COLUMN THAT ALREADY HAS A PRIMARY KEY OR UNIQUE CONSTRAINT ON IT.
	public static void testMostIndexesOnTable( Connection conn) throws Throwable
	{
    try {
			System.out.println("Test - most indexes allowed on a table");
			conn.setAutoCommit(false);
			Statement s = conn.createStatement();
			int i = 0;

			s.executeUpdate("create table t1 (c1 int not null, c2 int, primary key(c1))");
			System.out.println("First create one index less than maximum allowed number of indexes");
			for (i = 0; i < Limits.DB2_MAX_INDEXES_ON_TABLE-2; i++) {
				s.executeUpdate("create index i" + i + " on t1(c1,c2)");
			System.out.println("   create index" + i);
			}

			System.out.println("Next create maximum allowed number of indexes");
			s.executeUpdate("create index i" + (i+1) + " on t1(c1,c2)");

			System.out.println("And finally create one index more than maximum allowed number of indexes");
			try {
				s.executeUpdate("create index i" + (i+2) + " on t1(c1,c2)");
				System.out.println("FAIL - create index should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("And finally try maximum allowed number of indexes violation using add constraint");
			try {
				s.executeUpdate("alter table t1 add constraint i" + (i+2) + " unique (c1,c2)");
				System.out.println("FAIL - create index should have failed");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("54011"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}
			s.executeUpdate("drop table t1");
			conn.setAutoCommit(true);
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	static private void dumpSQLExceptions (SQLException se) {
		System.out.println("FAIL -- unexpected exception: " + se.toString());
		while (se != null) {
			System.out.print("SQLSTATE("+se.getSQLState()+"):");
			se = se.getNextException();
		}
	}

}