|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.*;
25
26 import org.apache.derby.tools.ij;
27 import org.apache.derby.iapi.reference.Limits;
28 import org.apache.derbyTesting.functionTests.util.Formatters;
29
30
33 public class dbManagerLimits
34 {
35
36 public static void main (String  [] argv) throws Throwable
37 {
38 ij.getPropertyArg(argv);
39 Connection conn = ij.startJBMS();
40
41 testStringAndHexConstants(conn);
42 testMostColumnsInTable(conn);
43 testMostColumnsInView(conn);
44 testMostElementsInSelectList(conn);
45 testMostElementsInOrderBy(conn);
46 testMostParametersInStoredProcedures(conn);
47
48
51 }
57
58 public static void testStringAndHexConstants( Connection conn) throws Throwable
59 {
60 try {
61 System.out.println("Test - maximum length of character constant is 32672 and that of hex constant is 16336");
62 String stringConstant32671 = Formatters.repeatChar("a",32671);
63 String hexConstant16334 = Formatters.repeatChar("a",16334);
64 Statement s = conn.createStatement();
65 s.executeUpdate("create table t1 (c11 long varchar, c12 long varchar for bit data)");
66
67 System.out.println("First testing less than maximum constant lengths through insert statement");
68 s.executeUpdate("insert into t1(c11) values ('" + stringConstant32671 + "')");
69 s.executeUpdate("insert into t1(c12) values (X'" + hexConstant16334 + "')");
70
71 System.out.println("Next testing less than maximum constant lengths through values");
72 s.execute("values ('" + stringConstant32671 + "')");
73 s.execute("values (X'" + hexConstant16334 + "')");
74
75 System.out.println("Next testing maximum constant lengths through insert statement");
76 s.executeUpdate("insert into t1(c11) values ('" + stringConstant32671 + "a')");
77 s.executeUpdate("insert into t1(c12) values (X'" + hexConstant16334 + "ab')");
78
79 System.out.println("Next testing maximum constant lengths through values");
80 s.execute("values ('" + stringConstant32671 + "a')");
81 s.execute("values (X'" + hexConstant16334 + "ab')");
82
83 System.out.println("Next testing maximum constant lengths + 1 through insert statement");
84 try {
85 s.executeUpdate("insert into t1(c11) values ('" + stringConstant32671 + "ab')");
86 System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
87 }
88 catch (SQLException e) {
89 if (e.getSQLState().equals("54002"))
90 System.out.println("expected exception " + e.getMessage());
91 else
92 dumpSQLExceptions(e);
93 }
94 try {
95 s.executeUpdate("insert into t1(c12) values (X'" + hexConstant16334 + "abcd')");
96 System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
97 }
98 catch (SQLException e) {
99 if (e.getSQLState().equals("54002"))
100 System.out.println("expected exception " + e.getMessage());
101 else
102 dumpSQLExceptions(e);
103 }
104
105 System.out.println("Next testing maximum constant lengths + 1 through values");
106 try {
107 s.executeUpdate("values ('" + stringConstant32671 + "ab')");
108 System.out.println("FAIL - should have gotten string constant too long error for this values statement");
109 }
110 catch (SQLException e) {
111 if (e.getSQLState().equals("54002"))
112 System.out.println("expected exception " + e.getMessage());
113 else
114 dumpSQLExceptions(e);
115 }
116 try {
117 s.executeUpdate("values (X'" + hexConstant16334 + "abcd')");
118 System.out.println("FAIL - should have gotten string constant too long error for this values statement");
119 }
120 catch (SQLException e) {
121 if (e.getSQLState().equals("54002"))
122 System.out.println("expected exception " + e.getMessage());
123 else
124 dumpSQLExceptions(e);
125 }
126
127 System.out.println("Next testing maximum constant lengths + n through insert statement");
128 try {
129 s.executeUpdate("insert into t1(c11) values ('" + stringConstant32671 + "bcdef')");
130 System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
131 }
132 catch (SQLException e) {
133 if (e.getSQLState().equals("54002"))
134 System.out.println("expected exception " + e.getMessage());
135 else
136 dumpSQLExceptions(e);
137 }
138 try {
139 s.executeUpdate("insert into t1(c12) values (X'" + hexConstant16334 + "abcdef')");
140 System.out.println("FAIL - should have gotten string constant too long error for this insert statement");
141 }
142 catch (SQLException e) {
143 if (e.getSQLState().equals("54002"))
144 System.out.println("expected exception " + e.getMessage());
145 else
146 dumpSQLExceptions(e);
147 }
148
149 System.out.println("Next testing maximum constant lengths + n through values");
150 try {
151 s.executeUpdate("values ('" + stringConstant32671 + "bcdef')");
152 System.out.println("FAIL - should have gotten string constant too long error for this values statement");
153 }
154 catch (SQLException e) {
155 if (e.getSQLState().equals("54002"))
156 System.out.println("expected exception " + e.getMessage());
157 else
158 dumpSQLExceptions(e);
159 }
160 try {
161 s.executeUpdate("values (X'" + hexConstant16334 + "abcdef')");
162 System.out.println("FAIL - should have gotten string constant too long error for this values statement");
163 }
164 catch (SQLException e) {
165 if (e.getSQLState().equals("54002"))
166 System.out.println("expected exception " + e.getMessage());
167 else
168 dumpSQLExceptions(e);
169 }
170
171 System.out.println("Next testing odd number of hex digits in a hex constant through insert statement");
172 try {
173 s.executeUpdate("insert into t1(c12) values (X'" + hexConstant16334 + "a')");
174 System.out.println("FAIL - should have gotten hex constant invalid string constant too long error for this values statement");
175 }
176 catch (SQLException e) {
177 if (e.getSQLState().equals("42606"))
178 System.out.println("expected exception " + e.getMessage());
179 else
180 dumpSQLExceptions(e);
181 }
182 System.out.println("And finally testing odd number of hex digits in a hex constant through values statement");
183 try {
184 s.executeUpdate("values (X'" + hexConstant16334 + "a')");
185 System.out.println("FAIL - should have gotten string constant too long error for this values statement");
186 }
187 catch (SQLException e) {
188 if (e.getSQLState().equals("42606"))
189 System.out.println("expected exception " + e.getMessage());
190 else
191 dumpSQLExceptions(e);
192 }
193
194 s.executeUpdate("drop table t1");
195 } catch (SQLException sqle) {
196 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
197 sqle.printStackTrace(System.out);
198 }
199 }
200
201 public static void testMostColumnsInTable( Connection conn) throws Throwable
202 {
203 try {
204 System.out.println("Test - most columns allowed in a table");
205
206 StringBuffer sbTableElements = new StringBuffer ();
207 String tempString = new String ();
208 int i = 0;
209 sbTableElements.append("create table t1 (");
210 for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_TABLE-2; i++)
211 sbTableElements.append("c" + i +" int, ");
212
213 Statement s = conn.createStatement();
214 System.out.println("First create a table with one column less than maximum allowed number of columns");
215 tempString = (sbTableElements.toString()).concat("c" + i + " int)");
216 s.executeUpdate(tempString);
217 System.out.println(" Try alter table on it to have table with maximum allowed number of columns");
218 s.executeUpdate("alter table t1 add column c" + (i+1) + " int");
219 System.out.println(" Try another alter table to have table with one column more than maximum allowed number of columns");
220 try {
221 s.executeUpdate("alter table t1 add column c" + (i+2) + " int");
222 System.out.println("FAIL - The alter table should have failed");
223 }
224 catch (SQLException e) {
225 if (e.getSQLState().equals("54011"))
226 System.out.println("expected exception " + e.getMessage());
227 else
228 dumpSQLExceptions(e);
229 }
230 s.executeUpdate("drop table t1");
231
232 System.out.println("Next create a table with maximum allowed number of columns");
233 tempString = (sbTableElements.toString()).concat("c" + i +" int, c" + (i+1) + " int)");
234 s.executeUpdate(tempString);
235 System.out.println(" Try alter table to have table with more columns than maximum allowed number of columns");
236 try {
237 s.executeUpdate("alter table t1 add column c" + (i+2) + " int");
238 System.out.println("FAIL - The alter table should have failed");
239 }
240 catch (SQLException e) {
241 if (e.getSQLState().equals("54011"))
242 System.out.println("expected exception " + e.getMessage());
243 else
244 dumpSQLExceptions(e);
245 }
246 DatabaseMetaData met = conn.getMetaData();
248 getCount(met.getColumns("", "APP", "T1", null));
249 s.executeUpdate("insert into t1(c1, c2) values (1,1)");
250 s.executeUpdate("drop table t1");
251
252 System.out.println("Next create a table with one column more than maximum allowed number of columns");
253 tempString = (sbTableElements.toString()).concat("c" + i +" int, c" + (i+1) + " int, c" + (i+2) + " int)");
254 try {
255 s.executeUpdate(tempString);
256 System.out.println("FAIL - The create table should have failed");
257 }
258 catch (SQLException e) {
259 if (e.getSQLState().equals("54011"))
260 System.out.println("expected exception " + e.getMessage());
261 else
262 dumpSQLExceptions(e);
263 }
264
265 System.out.println("Finally, create a table with 2 columns more than maximum allowed number of columns");
266 tempString = (sbTableElements.toString()).concat("c" + i +" int, c" + (i+1) + " int, c" + (i+2) + " int, c" + (i+3) + " int)");
267 try {
268 s.executeUpdate(tempString);
269 System.out.println("FAIL - The create table should have failed");
270 }
271 catch (SQLException e) {
272 if (e.getSQLState().equals("54011"))
273 System.out.println("expected exception " + e.getMessage());
274 else
275 dumpSQLExceptions(e);
276 }
277 } catch (SQLException sqle) {
278 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
279 sqle.printStackTrace(System.out);
280 }
281 }
282
283 private static void getCount( ResultSet s) throws Throwable
284 {
285 int counter = 0; while (s.next())
287 counter++;
288 System.out.println("Found " + counter + " columns/parameters through meta data");
289 }
290
291 public static void testMostColumnsInView( Connection conn) throws Throwable
292 {
293 try {
294 System.out.println("Test - most columns allowed in a view");
295
296 StringBuffer sbValuesClause = new StringBuffer ();
297 StringBuffer sbViewColumnNames = new StringBuffer ();
298 String tempString = new String ();
299 int i = 0;
300 for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW-2; i++) {
301 sbValuesClause.append(1 + ", ");
302 sbViewColumnNames.append("c" + i + ", ");
303 }
304
305 Statement s = conn.createStatement();
306 System.out.println("First create a view with one column less than maximum allowed number of columns");
307 tempString = "create view v1(" + sbViewColumnNames.toString() + "c" + i + ") as values (" + sbValuesClause.toString() + "1)";
308 s.executeUpdate(tempString);
309 s.executeUpdate("drop view v1");
310
311 System.out.println("Next create a view with maximum allowed number of columns");
312 tempString = "create view v1(" + sbViewColumnNames.toString() + "c" + i + ", c" + (i+1)+ ") as values (" + sbValuesClause.toString() + "1,1)";
313 s.executeUpdate(tempString);
314 DatabaseMetaData met = conn.getMetaData();
316 getCount(met.getColumns("", "APP", "V1", null));
317 s.executeUpdate("drop view v1");
318
319 System.out.println("Next create a view with one column more than that maximum allowed number of columns");
320 tempString = "create view v1(" + sbViewColumnNames.toString() + "c" + i + ", c" + (i+1) + ", c" + (i+2) + ") as values (" + sbValuesClause.toString() + "1,1,1)";
321 try {
322 s.executeUpdate(tempString);
323 System.out.println("FAIL - The create view should have failed");
324 }
325 catch (SQLException e) {
326 if (e.getSQLState().equals("54011"))
327 System.out.println("expected exception " + e.getMessage());
328 else
329 dumpSQLExceptions(e);
330 }
331
332 System.out.println("And finally create a view with 2 columns that maximum allowed number of columns");
333 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)";
334 try {
335 s.executeUpdate(tempString);
336 System.out.println("FAIL - The create view should have failed");
337 }
338 catch (SQLException e) {
339 if (e.getSQLState().equals("54011"))
340 System.out.println("expected exception " + e.getMessage());
341 else
342 dumpSQLExceptions(e);
343 }
344 } catch (SQLException sqle) {
345 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
346 sqle.printStackTrace(System.out);
347 }
348 }
349
350 public static void testMostElementsInSelectList( Connection conn) throws Throwable
351 {
352 try {
353 System.out.println("Test - most elements allowed in a select list");
354
355 StringBuffer sb = new StringBuffer ();
356 String tempString = new String ();
357 int i = 0;
358 sb.append("create table t1 (");
359 for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_TABLE-2; i++)
360 sb.append("c" + i +" int, ");
361
362 Statement s = conn.createStatement();
363 tempString = (sb.toString()).concat("c" + i + " int)");
364 s.executeUpdate(tempString);
365
366 System.out.println("First try a select with one column less than maximum allowed number of columns");
367 s.execute("select * from t1");
368
369 System.out.println("Next try a select with maximum allowed number of columns");
370 s.execute("select t1.*,1 from t1");
371
372 System.out.println("Next try a select with one column more than maximum allowed number of columns");
373 try {
374 s.execute("select t1.*,1,2 from t1");
375 System.out.println("FAIL - select should have failed");
376 }
377 catch (SQLException e) {
378 if (e.getSQLState().equals("54004"))
379 System.out.println("expected exception " + e.getMessage());
380 else
381 dumpSQLExceptions(e);
382 }
383
384 System.out.println("Next try a select with 2 more columns than maximum allowed number of columns");
385 try {
386 s.execute("select t1.*,1,2,3 from t1");
387 System.out.println("FAIL - select should have failed");
388 }
389 catch (SQLException e) {
390 if (e.getSQLState().equals("54004"))
391 System.out.println("expected exception " + e.getMessage());
392 else
393 dumpSQLExceptions(e);
394 }
395
396 s.executeUpdate("drop table t1");
397 } catch (SQLException sqle) {
398 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
399 sqle.printStackTrace(System.out);
400 }
401 }
402
403 public static void testMostElementsInOrderBy( Connection conn) throws Throwable
404 {
405 try {
406 System.out.println("Test - most columns allowed in a ORDER BY clause");
407
408 StringBuffer sbOrderBy = new StringBuffer ();
409 String tempString = new String ();
410 int i = 0;
411 sbOrderBy.append("select * from t1 order by ");
412 for (i = 0; i < Limits.DB2_MAX_ELEMENTS_IN_ORDER_BY-2; i++)
413 sbOrderBy.append("c1, ");
414
415 Statement s = conn.createStatement();
416 s.executeUpdate("create table t1 (c1 int not null, c2 int)");
417
418 System.out.println("First try order by with one column less than maximum allowed number of columns");
419 tempString = (sbOrderBy.toString()).concat("c2");
420 s.execute(tempString);
421
422 System.out.println("Next try an order by with maximum allowed number of columns");
423 tempString = (sbOrderBy.toString()).concat("c1, c2");
424 s.execute(tempString);
425
426 System.out.println("Next try an order by with one column more than maximum allowed number of columns");
427 tempString = (sbOrderBy.toString()).concat("c1, c2, c1");
428 try {
429 s.execute(tempString);
430 System.out.println("FAIL - order by should have failed");
431 }
432 catch (SQLException e) {
433 if (e.getSQLState().equals("54004"))
434 System.out.println("expected exception " + e.getMessage());
435 else
436 dumpSQLExceptions(e);
437 }
438
439 System.out.println("And finally try an order by with 2 more columns than maximum allowed number of columns");
440 tempString = (sbOrderBy.toString()).concat("c1, c2, c1");
441 try {
442 s.execute(tempString);
443 System.out.println("FAIL - order by should have failed");
444 }
445 catch (SQLException e) {
446 if (e.getSQLState().equals("54004"))
447 System.out.println("expected exception " + e.getMessage());
448 else
449 dumpSQLExceptions(e);
450 }
451
452 s.executeUpdate("drop table t1");
453 } catch (SQLException sqle) {
454 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
455 sqle.printStackTrace(System.out);
456 }
457 }
458
459 public static void testMostElementsInGroupBy( Connection conn) throws Throwable
460 {
461 try {
462 System.out.println("Test - most columns allowed in a GROUP BY clause");
463 Statement s = conn.createStatement();
464 StringBuffer sbGroupBy = new StringBuffer ("select 1 from v1, v2, v3, v4, v5, v6, v7 group by ");
465 StringBuffer sbValuesClause = new StringBuffer ();
466 StringBuffer sbViewColumnNames = new StringBuffer ();
467 String tempString = new String ();
468
469 int i = 0;
471 for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW-1; i++)
472 sbValuesClause.append(1 + ", ");
473
474 for (int j = 1; j < 8; j++) {
475 for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW-1; i++) {
476 sbViewColumnNames.append("c" + j + "" + i + ", ");
477 }
478 tempString = "create view v" + j + "(" + sbViewColumnNames.toString() + "c" + j + "" + i + ") as values (" + sbValuesClause.toString() + "1)";
479 s.executeUpdate(tempString);
480 sbViewColumnNames = new StringBuffer ();
481 }
482
483 for (int j = 1; j < 7; j++) {
484 for (i = 0; i < Limits.DB2_MAX_COLUMNS_IN_VIEW; i++)
485 sbGroupBy.append("c" + j + "" + i + ", ");
486 }
487 for (i = 0; i < 2675; i++)
488 sbGroupBy.append("c7" + i + ", ");
489
490 System.out.println("First try group by with one column less than maximum allowed number of columns");
491 tempString = (sbGroupBy.toString()).concat("c72675");
492 s.execute(tempString);
493
494 System.out.println("Next try an group by with maximum allowed number of columns");
495 tempString = (sbGroupBy.toString()).concat("c72675, c72675");
496 s.execute(tempString);
497
498 System.out.println("And finally try an group by with more columns that maximum allowed number of columns");
499 tempString = (sbGroupBy.toString()).concat("c72675, c72676, c72677");
500 try {
501 s.execute(tempString);
502 System.out.println("FAIL - group by should have failed");
503 }
504 catch (SQLException e) {
505 if (e.getSQLState().equals("54004"))
506 System.out.println("expected exception " + e.getMessage());
507 else
508 dumpSQLExceptions(e);
509 }
510
511 s.executeUpdate("drop view v1");
512 s.executeUpdate("drop view v2");
513 s.executeUpdate("drop view v3");
514 s.executeUpdate("drop view v4");
515 s.executeUpdate("drop view v5");
516 s.executeUpdate("drop view v6");
517 s.executeUpdate("drop view v7");
518
519 s.execute("select 1 from v1 group by c1,c2");
520 s.executeUpdate("drop table t1");
521 } catch (SQLException sqle) {
522 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
523 sqle.printStackTrace(System.out);
524 }
525 }
526
527 public static void testMostParametersInStoredProcedures( Connection conn) throws Throwable
528 {
529 try {
530 System.out.println("Test - most parameters allowed for a stored procedure");
531 Statement s = conn.createStatement();
532 StringBuffer sbCreateProcParams = new StringBuffer ();
533 StringBuffer sbExecuteProcParams = new StringBuffer ();
534 String tempString = new String ();
535 int i = 0;
536
537 for (i = 0; i < Limits.DB2_MAX_PARAMS_IN_STORED_PROCEDURE-2; i++) {
538 sbCreateProcParams.append("i" + i + " int, ");
539 sbExecuteProcParams.append("1, ");
540 }
541
542 System.out.println("First create a procedure with one parameter less than maximum allowed number of parameters");
543 tempString = "create procedure P1(" + sbCreateProcParams.toString() + "i" + i +
544 " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.lessThanMaxParams\' NO SQL";
545 s.executeUpdate(tempString);
546
547 System.out.println("Next create a procedure with maximum allowed number of parameters");
548 tempString = "create procedure P2(" + sbCreateProcParams.toString() + "i" + i +
549 " int, i" + (i+1) + " int) parameter style java language java external name \'org.apache.derbyTesting.functionTests.util.ProcedureTest.maxAllowedParams\' NO SQL";
550 s.executeUpdate(tempString);
551 DatabaseMetaData met = conn.getMetaData();
553 getCount(met.getProcedureColumns("", "APP", "P2", null));
554
555 System.out.println("And finally create a procedure with more parameters that maximum allowed number of parameters");
556 tempString = "create procedure P3(" + sbCreateProcParams.toString() + "i" + i +
557 " 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";
558 try {
559 s.executeUpdate(tempString);
560 System.out.println("FAIL - create procedure should have failed");
561 }
562 catch (SQLException e) {
563 if (e.getSQLState().equals("54023"))
564 System.out.println("expected exception " + e.getMessage());
565 else
566 dumpSQLExceptions(e);
567 }
568 } catch (SQLException sqle) {
569 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
570 sqle.printStackTrace(System.out);
571 }
572 }
573
574 public static void testMostIndexesOnTable( Connection conn) throws Throwable
579 {
580 try {
581 System.out.println("Test - most indexes allowed on a table");
582 conn.setAutoCommit(false);
583 Statement s = conn.createStatement();
584 int i = 0;
585
586 s.executeUpdate("create table t1 (c1 int not null, c2 int, primary key(c1))");
587 System.out.println("First create one index less than maximum allowed number of indexes");
588 for (i = 0; i < Limits.DB2_MAX_INDEXES_ON_TABLE-2; i++) {
589 s.executeUpdate("create index i" + i + " on t1(c1,c2)");
590 System.out.println(" create index" + i);
591 }
592
593 System.out.println("Next create maximum allowed number of indexes");
594 s.executeUpdate("create index i" + (i+1) + " on t1(c1,c2)");
595
596 System.out.println("And finally create one index more than maximum allowed number of indexes");
597 try {
598 s.executeUpdate("create index i" + (i+2) + " on t1(c1,c2)");
599 System.out.println("FAIL - create index should have failed");
600 }
601 catch (SQLException e) {
602 if (e.getSQLState().equals("54011"))
603 System.out.println("expected exception " + e.getMessage());
604 else
605 dumpSQLExceptions(e);
606 }
607
608 System.out.println("And finally try maximum allowed number of indexes violation using add constraint");
609 try {
610 s.executeUpdate("alter table t1 add constraint i" + (i+2) + " unique (c1,c2)");
611 System.out.println("FAIL - create index should have failed");
612 }
613 catch (SQLException e) {
614 if (e.getSQLState().equals("54011"))
615 System.out.println("expected exception " + e.getMessage());
616 else
617 dumpSQLExceptions(e);
618 }
619 s.executeUpdate("drop table t1");
620 conn.setAutoCommit(true);
621 } catch (SQLException sqle) {
622 org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
623 sqle.printStackTrace(System.out);
624 }
625 }
626
627 static private void dumpSQLExceptions (SQLException se) {
628 System.out.println("FAIL -- unexpected exception: " + se.toString());
629 while (se != null) {
630 System.out.print("SQLSTATE("+se.getSQLState()+"):");
631 se = se.getNextException();
632 }
633 }
634
635 }
636 |
Popular Tags |
Java API By Example, From Geeks To Geeks.