001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.dbManagerLimits
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.*;
025:
026: import org.apache.derby.tools.ij;
027: import org.apache.derby.iapi.reference.Limits;
028: import org.apache.derbyTesting.functionTests.util.Formatters;
029:
030: /**
031: Test various data manager limits like in db2 here.
032: */
033: public class dbManagerLimits {
034:
035: public static void main(String[] argv) throws Throwable {
036: ij.getPropertyArg(argv);
037: Connection conn = ij.startJBMS();
038:
039: testStringAndHexConstants(conn);
040: testMostColumnsInTable(conn);
041: testMostColumnsInView(conn);
042: testMostElementsInSelectList(conn);
043: testMostElementsInOrderBy(conn);
044: testMostParametersInStoredProcedures(conn);
045:
046: //not running Group By test because it gets out of memory error
047: //testMostElementsInGroupBy(conn);
048:
049: //not running indexes test because it doesn't finish even after running for over 2 hours
050: //ALSO, IF WE EVER ENABLE THIS TEST IN FUTURE, WE NEED TO REWRITE THE TEST SO THAT WE TRY TO CREATE OVER
051: //32767 *DIFFERENT* INDEXES. AS PART OF DB2 COMPATIBILITY WORK, BUG - 5685 DISALLOWS CREATION OF AN INDEX
052: //ON A COLUMN THAT ALREADY HAS A PRIMARY KEY OR UNIQUE CONSTRAINT ON IT.
053: //testMostIndexesOnTable(conn);
054: }
055:
056: public static void testStringAndHexConstants(Connection conn)
057: throws Throwable {
058: try {
059: System.out
060: .println("Test - maximum length of character constant is 32672 and that of hex constant is 16336");
061: String stringConstant32671 = Formatters.repeatChar("a",
062: 32671);
063: String hexConstant16334 = Formatters.repeatChar("a", 16334);
064: Statement s = conn.createStatement();
065: s
066: .executeUpdate("create table t1 (c11 long varchar, c12 long varchar for bit data)");
067:
068: System.out
069: .println("First testing less than maximum constant lengths through insert statement");
070: s.executeUpdate("insert into t1(c11) values ('"
071: + stringConstant32671 + "')");
072: s.executeUpdate("insert into t1(c12) values (X'"
073: + hexConstant16334 + "')");
074:
075: System.out
076: .println("Next testing less than maximum constant lengths through values");
077: s.execute("values ('" + stringConstant32671 + "')");
078: s.execute("values (X'" + hexConstant16334 + "')");
079:
080: System.out
081: .println("Next testing maximum constant lengths through insert statement");
082: s.executeUpdate("insert into t1(c11) values ('"
083: + stringConstant32671 + "a')");
084: s.executeUpdate("insert into t1(c12) values (X'"
085: + hexConstant16334 + "ab')");
086:
087: System.out
088: .println("Next testing maximum constant lengths through values");
089: s.execute("values ('" + stringConstant32671 + "a')");
090: s.execute("values (X'" + hexConstant16334 + "ab')");
091:
092: System.out
093: .println("Next testing maximum constant lengths + 1 through insert statement");
094: try {
095: s.executeUpdate("insert into t1(c11) values ('"
096: + stringConstant32671 + "ab')");
097: System.out
098: .println("FAIL - should have gotten string constant too long error for this insert statement");
099: } catch (SQLException e) {
100: if (e.getSQLState().equals("54002"))
101: System.out.println("expected exception "
102: + e.getMessage());
103: else
104: dumpSQLExceptions(e);
105: }
106: try {
107: s.executeUpdate("insert into t1(c12) values (X'"
108: + hexConstant16334 + "abcd')");
109: System.out
110: .println("FAIL - should have gotten string constant too long error for this insert statement");
111: } catch (SQLException e) {
112: if (e.getSQLState().equals("54002"))
113: System.out.println("expected exception "
114: + e.getMessage());
115: else
116: dumpSQLExceptions(e);
117: }
118:
119: System.out
120: .println("Next testing maximum constant lengths + 1 through values");
121: try {
122: s.executeUpdate("values ('" + stringConstant32671
123: + "ab')");
124: System.out
125: .println("FAIL - should have gotten string constant too long error for this values statement");
126: } catch (SQLException e) {
127: if (e.getSQLState().equals("54002"))
128: System.out.println("expected exception "
129: + e.getMessage());
130: else
131: dumpSQLExceptions(e);
132: }
133: try {
134: s.executeUpdate("values (X'" + hexConstant16334
135: + "abcd')");
136: System.out
137: .println("FAIL - should have gotten string constant too long error for this values statement");
138: } catch (SQLException e) {
139: if (e.getSQLState().equals("54002"))
140: System.out.println("expected exception "
141: + e.getMessage());
142: else
143: dumpSQLExceptions(e);
144: }
145:
146: System.out
147: .println("Next testing maximum constant lengths + n through insert statement");
148: try {
149: s.executeUpdate("insert into t1(c11) values ('"
150: + stringConstant32671 + "bcdef')");
151: System.out
152: .println("FAIL - should have gotten string constant too long error for this insert statement");
153: } catch (SQLException e) {
154: if (e.getSQLState().equals("54002"))
155: System.out.println("expected exception "
156: + e.getMessage());
157: else
158: dumpSQLExceptions(e);
159: }
160: try {
161: s.executeUpdate("insert into t1(c12) values (X'"
162: + hexConstant16334 + "abcdef')");
163: System.out
164: .println("FAIL - should have gotten string constant too long error for this insert statement");
165: } catch (SQLException e) {
166: if (e.getSQLState().equals("54002"))
167: System.out.println("expected exception "
168: + e.getMessage());
169: else
170: dumpSQLExceptions(e);
171: }
172:
173: System.out
174: .println("Next testing maximum constant lengths + n through values");
175: try {
176: s.executeUpdate("values ('" + stringConstant32671
177: + "bcdef')");
178: System.out
179: .println("FAIL - should have gotten string constant too long error for this values statement");
180: } catch (SQLException e) {
181: if (e.getSQLState().equals("54002"))
182: System.out.println("expected exception "
183: + e.getMessage());
184: else
185: dumpSQLExceptions(e);
186: }
187: try {
188: s.executeUpdate("values (X'" + hexConstant16334
189: + "abcdef')");
190: System.out
191: .println("FAIL - should have gotten string constant too long error for this values statement");
192: } catch (SQLException e) {
193: if (e.getSQLState().equals("54002"))
194: System.out.println("expected exception "
195: + e.getMessage());
196: else
197: dumpSQLExceptions(e);
198: }
199:
200: System.out
201: .println("Next testing odd number of hex digits in a hex constant through insert statement");
202: try {
203: s.executeUpdate("insert into t1(c12) values (X'"
204: + hexConstant16334 + "a')");
205: System.out
206: .println("FAIL - should have gotten hex constant invalid string constant too long error for this values statement");
207: } catch (SQLException e) {
208: if (e.getSQLState().equals("42606"))
209: System.out.println("expected exception "
210: + e.getMessage());
211: else
212: dumpSQLExceptions(e);
213: }
214: System.out
215: .println("And finally testing odd number of hex digits in a hex constant through values statement");
216: try {
217: s
218: .executeUpdate("values (X'" + hexConstant16334
219: + "a')");
220: System.out
221: .println("FAIL - should have gotten string constant too long error for this values statement");
222: } catch (SQLException e) {
223: if (e.getSQLState().equals("42606"))
224: System.out.println("expected exception "
225: + e.getMessage());
226: else
227: dumpSQLExceptions(e);
228: }
229:
230: s.executeUpdate("drop table t1");
231: } catch (SQLException sqle) {
232: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
233: System.out, sqle);
234: sqle.printStackTrace(System.out);
235: }
236: }
237:
238: public static void testMostColumnsInTable(Connection conn)
239: throws Throwable {
240: try {
241: System.out
242: .println("Test - most columns allowed in a table");
243:
244: StringBuffer sbTableElements = new StringBuffer();
245: String tempString = new String();
246: int i = 0;
247: sbTableElements.append("create table t1 (");
248: for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_TABLE - 2; i++)
249: sbTableElements.append("c" + i + " int, ");
250:
251: Statement s = conn.createStatement();
252: System.out
253: .println("First create a table with one column less than maximum allowed number of columns");
254: tempString = (sbTableElements.toString()).concat("c" + i
255: + " int)");
256: s.executeUpdate(tempString);
257: System.out
258: .println(" Try alter table on it to have table with maximum allowed number of columns");
259: s.executeUpdate("alter table t1 add column c" + (i + 1)
260: + " int");
261: System.out
262: .println(" Try another alter table to have table with one column more than maximum allowed number of columns");
263: try {
264: s.executeUpdate("alter table t1 add column c" + (i + 2)
265: + " int");
266: System.out
267: .println("FAIL - The alter table should have failed");
268: } catch (SQLException e) {
269: if (e.getSQLState().equals("54011"))
270: System.out.println("expected exception "
271: + e.getMessage());
272: else
273: dumpSQLExceptions(e);
274: }
275: s.executeUpdate("drop table t1");
276:
277: System.out
278: .println("Next create a table with maximum allowed number of columns");
279: tempString = (sbTableElements.toString()).concat("c" + i
280: + " int, c" + (i + 1) + " int)");
281: s.executeUpdate(tempString);
282: System.out
283: .println(" Try alter table to have table with more columns than maximum allowed number of columns");
284: try {
285: s.executeUpdate("alter table t1 add column c" + (i + 2)
286: + " int");
287: System.out
288: .println("FAIL - The alter table should have failed");
289: } catch (SQLException e) {
290: if (e.getSQLState().equals("54011"))
291: System.out.println("expected exception "
292: + e.getMessage());
293: else
294: dumpSQLExceptions(e);
295: }
296: //just some basic sanity check
297: DatabaseMetaData met = conn.getMetaData();
298: getCount(met.getColumns("", "APP", "T1", null));
299: s.executeUpdate("insert into t1(c1, c2) values (1,1)");
300: s.executeUpdate("drop table t1");
301:
302: System.out
303: .println("Next create a table with one column more than maximum allowed number of columns");
304: tempString = (sbTableElements.toString()).concat("c" + i
305: + " int, c" + (i + 1) + " int, c" + (i + 2)
306: + " int)");
307: try {
308: s.executeUpdate(tempString);
309: System.out
310: .println("FAIL - The create table should have failed");
311: } catch (SQLException e) {
312: if (e.getSQLState().equals("54011"))
313: System.out.println("expected exception "
314: + e.getMessage());
315: else
316: dumpSQLExceptions(e);
317: }
318:
319: System.out
320: .println("Finally, create a table with 2 columns more than maximum allowed number of columns");
321: tempString = (sbTableElements.toString()).concat("c" + i
322: + " int, c" + (i + 1) + " int, c" + (i + 2)
323: + " int, c" + (i + 3) + " int)");
324: try {
325: s.executeUpdate(tempString);
326: System.out
327: .println("FAIL - The create table should have failed");
328: } catch (SQLException e) {
329: if (e.getSQLState().equals("54011"))
330: System.out.println("expected exception "
331: + e.getMessage());
332: else
333: dumpSQLExceptions(e);
334: }
335: } catch (SQLException sqle) {
336: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
337: System.out, sqle);
338: sqle.printStackTrace(System.out);
339: }
340: }
341:
342: private static void getCount(ResultSet s) throws Throwable {
343: int counter = 0; // Display data, fetching until end of the result set
344: while (s.next())
345: counter++;
346: System.out.println("Found " + counter
347: + " columns/parameters through meta data");
348: }
349:
350: public static void testMostColumnsInView(Connection conn)
351: throws Throwable {
352: try {
353: System.out.println("Test - most columns allowed in a view");
354:
355: StringBuffer sbValuesClause = new StringBuffer();
356: StringBuffer sbViewColumnNames = new StringBuffer();
357: String tempString = new String();
358: int i = 0;
359: for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW - 2; i++) {
360: sbValuesClause.append(1 + ", ");
361: sbViewColumnNames.append("c" + i + ", ");
362: }
363:
364: Statement s = conn.createStatement();
365: System.out
366: .println("First create a view with one column less than maximum allowed number of columns");
367: tempString = "create view v1("
368: + sbViewColumnNames.toString() + "c" + i
369: + ") as values (" + sbValuesClause.toString()
370: + "1)";
371: s.executeUpdate(tempString);
372: s.executeUpdate("drop view v1");
373:
374: System.out
375: .println("Next create a view with maximum allowed number of columns");
376: tempString = "create view v1("
377: + sbViewColumnNames.toString() + "c" + i + ", c"
378: + (i + 1) + ") as values ("
379: + sbValuesClause.toString() + "1,1)";
380: s.executeUpdate(tempString);
381: //just some basic sanity check
382: DatabaseMetaData met = conn.getMetaData();
383: getCount(met.getColumns("", "APP", "V1", null));
384: s.executeUpdate("drop view v1");
385:
386: System.out
387: .println("Next create a view with one column more than that maximum allowed number of columns");
388: tempString = "create view v1("
389: + sbViewColumnNames.toString() + "c" + i + ", c"
390: + (i + 1) + ", c" + (i + 2) + ") as values ("
391: + sbValuesClause.toString() + "1,1,1)";
392: try {
393: s.executeUpdate(tempString);
394: System.out
395: .println("FAIL - The create view should have failed");
396: } catch (SQLException e) {
397: if (e.getSQLState().equals("54011"))
398: System.out.println("expected exception "
399: + e.getMessage());
400: else
401: dumpSQLExceptions(e);
402: }
403:
404: System.out
405: .println("And finally create a view with 2 columns that maximum allowed number of columns");
406: tempString = "create view v1("
407: + sbViewColumnNames.toString() + "c" + i + ", c"
408: + (i + 1) + ", c" + (i + 2) + ", c" + (i + 3)
409: + ") as values (" + sbValuesClause.toString()
410: + "1,1,1,1)";
411: try {
412: s.executeUpdate(tempString);
413: System.out
414: .println("FAIL - The create view should have failed");
415: } catch (SQLException e) {
416: if (e.getSQLState().equals("54011"))
417: System.out.println("expected exception "
418: + e.getMessage());
419: else
420: dumpSQLExceptions(e);
421: }
422: } catch (SQLException sqle) {
423: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
424: System.out, sqle);
425: sqle.printStackTrace(System.out);
426: }
427: }
428:
429: public static void testMostElementsInSelectList(Connection conn)
430: throws Throwable {
431: try {
432: System.out
433: .println("Test - most elements allowed in a select list");
434:
435: StringBuffer sb = new StringBuffer();
436: String tempString = new String();
437: int i = 0;
438: sb.append("create table t1 (");
439: for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_TABLE - 2; i++)
440: sb.append("c" + i + " int, ");
441:
442: Statement s = conn.createStatement();
443: tempString = (sb.toString()).concat("c" + i + " int)");
444: s.executeUpdate(tempString);
445:
446: System.out
447: .println("First try a select with one column less than maximum allowed number of columns");
448: s.execute("select * from t1");
449:
450: System.out
451: .println("Next try a select with maximum allowed number of columns");
452: s.execute("select t1.*,1 from t1");
453:
454: System.out
455: .println("Next try a select with one column more than maximum allowed number of columns");
456: try {
457: s.execute("select t1.*,1,2 from t1");
458: System.out.println("FAIL - select should have failed");
459: } catch (SQLException e) {
460: if (e.getSQLState().equals("54004"))
461: System.out.println("expected exception "
462: + e.getMessage());
463: else
464: dumpSQLExceptions(e);
465: }
466:
467: System.out
468: .println("Next try a select with 2 more columns than maximum allowed number of columns");
469: try {
470: s.execute("select t1.*,1,2,3 from t1");
471: System.out.println("FAIL - select should have failed");
472: } catch (SQLException e) {
473: if (e.getSQLState().equals("54004"))
474: System.out.println("expected exception "
475: + e.getMessage());
476: else
477: dumpSQLExceptions(e);
478: }
479:
480: s.executeUpdate("drop table t1");
481: } catch (SQLException sqle) {
482: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
483: System.out, sqle);
484: sqle.printStackTrace(System.out);
485: }
486: }
487:
488: public static void testMostElementsInOrderBy(Connection conn)
489: throws Throwable {
490: try {
491: System.out
492: .println("Test - most columns allowed in a ORDER BY clause");
493:
494: StringBuffer sbOrderBy = new StringBuffer();
495: String tempString = new String();
496: int i = 0;
497: sbOrderBy.append("select * from t1 order by ");
498: for (i = 0; i < Limits.DB2_MAX_ELEMENTS_IN_ORDER_BY - 2; i++)
499: sbOrderBy.append("c1, ");
500:
501: Statement s = conn.createStatement();
502: s
503: .executeUpdate("create table t1 (c1 int not null, c2 int)");
504:
505: System.out
506: .println("First try order by with one column less than maximum allowed number of columns");
507: tempString = (sbOrderBy.toString()).concat("c2");
508: s.execute(tempString);
509:
510: System.out
511: .println("Next try an order by with maximum allowed number of columns");
512: tempString = (sbOrderBy.toString()).concat("c1, c2");
513: s.execute(tempString);
514:
515: System.out
516: .println("Next try an order by with one column more than maximum allowed number of columns");
517: tempString = (sbOrderBy.toString()).concat("c1, c2, c1");
518: try {
519: s.execute(tempString);
520: System.out
521: .println("FAIL - order by should have failed");
522: } catch (SQLException e) {
523: if (e.getSQLState().equals("54004"))
524: System.out.println("expected exception "
525: + e.getMessage());
526: else
527: dumpSQLExceptions(e);
528: }
529:
530: System.out
531: .println("And finally try an order by with 2 more columns than maximum allowed number of columns");
532: tempString = (sbOrderBy.toString()).concat("c1, c2, c1");
533: try {
534: s.execute(tempString);
535: System.out
536: .println("FAIL - order by should have failed");
537: } catch (SQLException e) {
538: if (e.getSQLState().equals("54004"))
539: System.out.println("expected exception "
540: + e.getMessage());
541: else
542: dumpSQLExceptions(e);
543: }
544:
545: s.executeUpdate("drop table t1");
546: } catch (SQLException sqle) {
547: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
548: System.out, sqle);
549: sqle.printStackTrace(System.out);
550: }
551: }
552:
553: public static void testMostElementsInGroupBy(Connection conn)
554: throws Throwable {
555: try {
556: System.out
557: .println("Test - most columns allowed in a GROUP BY clause");
558: Statement s = conn.createStatement();
559: StringBuffer sbGroupBy = new StringBuffer(
560: "select 1 from v1, v2, v3, v4, v5, v6, v7 group by ");
561: StringBuffer sbValuesClause = new StringBuffer();
562: StringBuffer sbViewColumnNames = new StringBuffer();
563: String tempString = new String();
564:
565: //first create 7 views with 5000 columns each
566: int i = 0;
567: for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW - 1; i++)
568: sbValuesClause.append(1 + ", ");
569:
570: for (int j = 1; j < 8; j++) {
571: for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW - 1; i++) {
572: sbViewColumnNames.append("c" + j + "" + i + ", ");
573: }
574: tempString = "create view v" + j + "("
575: + sbViewColumnNames.toString() + "c" + j + ""
576: + i + ") as values ("
577: + sbValuesClause.toString() + "1)";
578: s.executeUpdate(tempString);
579: sbViewColumnNames = new StringBuffer();
580: }
581:
582: for (int j = 1; j < 7; j++) {
583: for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW; i++)
584: sbGroupBy.append("c" + j + "" + i + ", ");
585: }
586: for (i = 0; i < 2675; i++)
587: sbGroupBy.append("c7" + i + ", ");
588:
589: System.out
590: .println("First try group by with one column less than maximum allowed number of columns");
591: tempString = (sbGroupBy.toString()).concat("c72675");
592: s.execute(tempString);
593:
594: System.out
595: .println("Next try an group by with maximum allowed number of columns");
596: tempString = (sbGroupBy.toString())
597: .concat("c72675, c72675");
598: s.execute(tempString);
599:
600: System.out
601: .println("And finally try an group by with more columns that maximum allowed number of columns");
602: tempString = (sbGroupBy.toString())
603: .concat("c72675, c72676, c72677");
604: try {
605: s.execute(tempString);
606: System.out
607: .println("FAIL - group by should have failed");
608: } catch (SQLException e) {
609: if (e.getSQLState().equals("54004"))
610: System.out.println("expected exception "
611: + e.getMessage());
612: else
613: dumpSQLExceptions(e);
614: }
615:
616: s.executeUpdate("drop view v1");
617: s.executeUpdate("drop view v2");
618: s.executeUpdate("drop view v3");
619: s.executeUpdate("drop view v4");
620: s.executeUpdate("drop view v5");
621: s.executeUpdate("drop view v6");
622: s.executeUpdate("drop view v7");
623:
624: s.execute("select 1 from v1 group by c1,c2");
625: s.executeUpdate("drop table t1");
626: } catch (SQLException sqle) {
627: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
628: System.out, sqle);
629: sqle.printStackTrace(System.out);
630: }
631: }
632:
633: public static void testMostParametersInStoredProcedures(
634: Connection conn) throws Throwable {
635: try {
636: System.out
637: .println("Test - most parameters allowed for a stored procedure");
638: Statement s = conn.createStatement();
639: StringBuffer sbCreateProcParams = new StringBuffer();
640: StringBuffer sbExecuteProcParams = new StringBuffer();
641: String tempString = new String();
642: int i = 0;
643:
644: for (i = 0; i < Limits.DB2_MAX_PARAMS_IN_STORED_PROCEDURE - 2; i++) {
645: sbCreateProcParams.append("i" + i + " int, ");
646: sbExecuteProcParams.append("1, ");
647: }
648:
649: System.out
650: .println("First create a procedure with one parameter less than maximum allowed number of parameters");
651: tempString = "create procedure P1("
652: + sbCreateProcParams.toString()
653: + "i"
654: + i
655: + " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.lessThanMaxParams\' NO SQL";
656: s.executeUpdate(tempString);
657:
658: System.out
659: .println("Next create a procedure with maximum allowed number of parameters");
660: tempString = "create procedure P2("
661: + sbCreateProcParams.toString()
662: + "i"
663: + i
664: + " int, i"
665: + (i + 1)
666: + " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.maxAllowedParams\' NO SQL";
667: s.executeUpdate(tempString);
668: //just some basic sanity check
669: DatabaseMetaData met = conn.getMetaData();
670: getCount(met.getProcedureColumns("", "APP", "P2", null));
671:
672: System.out
673: .println("And finally create a procedure with more parameters that maximum allowed number of parameters");
674: tempString = "create procedure P3("
675: + sbCreateProcParams.toString()
676: + "i"
677: + i
678: + " int, i"
679: + (i + 1)
680: + " int, i"
681: + (i + 2)
682: + " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.moreThanMaxAllowedParams\' NO SQL";
683: try {
684: s.executeUpdate(tempString);
685: System.out
686: .println("FAIL - create procedure should have failed");
687: } catch (SQLException e) {
688: if (e.getSQLState().equals("54023"))
689: System.out.println("expected exception "
690: + e.getMessage());
691: else
692: dumpSQLExceptions(e);
693: }
694: } catch (SQLException sqle) {
695: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
696: System.out, sqle);
697: sqle.printStackTrace(System.out);
698: }
699: }
700:
701: //not running indexes test because it doesn't finish even after running for over 2 hours
702: //ALSO, IF WE EVER ENABLE THIS TEST IN FUTURE, WE NEED TO REWRITE THE TEST SO THAT WE TRY TO CREATE OVER
703: //32767 *DIFFERENT* INDEXES. AS PART OF DB2 COMPATIBILITY WORK, BUG - 5685 DISALLOWS CREATION OF AN INDEX
704: //ON A COLUMN THAT ALREADY HAS A PRIMARY KEY OR UNIQUE CONSTRAINT ON IT.
705: public static void testMostIndexesOnTable(Connection conn)
706: throws Throwable {
707: try {
708: System.out
709: .println("Test - most indexes allowed on a table");
710: conn.setAutoCommit(false);
711: Statement s = conn.createStatement();
712: int i = 0;
713:
714: s
715: .executeUpdate("create table t1 (c1 int not null, c2 int, primary key(c1))");
716: System.out
717: .println("First create one index less than maximum allowed number of indexes");
718: for (i = 0; i < Limits.DB2_MAX_INDEXES_ON_TABLE - 2; i++) {
719: s.executeUpdate("create index i" + i + " on t1(c1,c2)");
720: System.out.println(" create index" + i);
721: }
722:
723: System.out
724: .println("Next create maximum allowed number of indexes");
725: s.executeUpdate("create index i" + (i + 1)
726: + " on t1(c1,c2)");
727:
728: System.out
729: .println("And finally create one index more than maximum allowed number of indexes");
730: try {
731: s.executeUpdate("create index i" + (i + 2)
732: + " on t1(c1,c2)");
733: System.out
734: .println("FAIL - create index should have failed");
735: } catch (SQLException e) {
736: if (e.getSQLState().equals("54011"))
737: System.out.println("expected exception "
738: + e.getMessage());
739: else
740: dumpSQLExceptions(e);
741: }
742:
743: System.out
744: .println("And finally try maximum allowed number of indexes violation using add constraint");
745: try {
746: s.executeUpdate("alter table t1 add constraint i"
747: + (i + 2) + " unique (c1,c2)");
748: System.out
749: .println("FAIL - create index should have failed");
750: } catch (SQLException e) {
751: if (e.getSQLState().equals("54011"))
752: System.out.println("expected exception "
753: + e.getMessage());
754: else
755: dumpSQLExceptions(e);
756: }
757: s.executeUpdate("drop table t1");
758: conn.setAutoCommit(true);
759: } catch (SQLException sqle) {
760: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
761: System.out, sqle);
762: sqle.printStackTrace(System.out);
763: }
764: }
765:
766: static private void dumpSQLExceptions(SQLException se) {
767: System.out.println("FAIL -- unexpected exception: "
768: + se.toString());
769: while (se != null) {
770: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
771: se = se.getNextException();
772: }
773: }
774:
775: }
|