001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.StmtCloseFunTest
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.jdbcapi;
023:
024: import org.apache.derby.tools.ij;
025: import java.sql.Connection;
026: import java.sql.Statement;
027: import java.sql.PreparedStatement;
028: import java.sql.CallableStatement;
029: import java.sql.ResultSet;
030: import java.sql.SQLException;
031: import java.sql.Date;
032: import java.sql.Types;
033: import java.util.GregorianCalendar;
034: import org.apache.derby.iapi.reference.JDBC20Translation;
035: import org.apache.derbyTesting.functionTests.util.BigDecimalHandler;
036: import org.apache.derbyTesting.functionTests.util.TestUtil;
037:
038: public class StmtCloseFunTest {
039:
040: private static boolean HAVE_BIG_DECIMAL;
041: private static String CLASS_NAME;
042:
043: //Get the class name to be used for the procedures
044: //outparams - J2ME; outparams30 - non-J2ME
045: static {
046: if (BigDecimalHandler.representation != BigDecimalHandler.BIGDECIMAL_REPRESENTATION)
047: HAVE_BIG_DECIMAL = false;
048: else
049: HAVE_BIG_DECIMAL = true;
050: if (HAVE_BIG_DECIMAL)
051: CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams30.";
052: else
053: CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams.";
054: }
055:
056: static private boolean isDerbyNet = false;
057:
058: public static void main(String[] args) {
059:
060: System.out.println("Statement Close Fun Test starting ");
061: isDerbyNet = TestUtil.isJCCFramework();
062:
063: try {
064: // use the ij utility to read the property file and
065: // make the initial connection.
066: ij.getPropertyArg(args);
067: Connection conn = ij.startJBMS();
068: cleanUp(conn);
069: test1(conn);
070: test2(conn);
071: test3(conn);
072:
073: cleanUp(conn);
074: conn.close();
075:
076: } catch (SQLException e) {
077: dumpSQLExceptions(e);
078: } catch (Throwable e) {
079: System.out.println("FAIL -- unexpected exception:"
080: + e.toString());
081: e.printStackTrace(System.out);
082: }
083:
084: System.out.println("Statement Close Fun Test finished");
085: }
086:
087: private static void cleanUp(Connection conn) throws SQLException {
088: Statement s = conn.createStatement();
089: String[] testObjects = { "PROCEDURE TAKESSTRING", "TABLE TAB1",
090: "TABLE TAB2", "TABLE TAB3" };
091: try {
092: TestUtil.cleanUpTest(s, testObjects);
093: } catch (SQLException se) {//
094: }
095: }
096:
097: private static void test1(Connection conn) {
098: Statement s;
099: try {
100: System.out.println("Statement test begin");
101: s = conn.createStatement();
102: /*
103: We create a table, add a row, and close the statement.
104: */
105: s.execute("create table tab1(num int, addr varchar(40))");
106: s.execute("insert into tab1 values (1910,'Union St.')");
107: s.close();
108:
109: try {
110: s.executeQuery("create table tab2(num int)");
111: System.out.println("Statement Test failed (1)");
112: } catch (SQLException e) {
113: }
114:
115: try {
116: s
117: .executeUpdate("update tab1 set num=180, addr='Grand Ave.' where num=1910");
118: System.out.println("Statement Test failed");
119: } catch (SQLException e) {
120: }
121:
122: /* close() is the only method that can be called
123: * after a Statement has been closed, as per
124: * Jon Ellis.
125: */
126: try {
127: s.close();
128: } catch (SQLException e) {
129: System.out.println("Statement Test failed (2) "
130: + e.toString());
131: }
132:
133: try {
134: s
135: .execute("insert into tab1 values (300,'Lakeside Dr.')");
136: System.out.println("Statement Test failed (3)");
137: } catch (SQLException e) {
138: }
139:
140: try {
141: s.getMaxFieldSize();
142: System.out.println("Statement Test failed (4)");
143: } catch (SQLException e) {
144: }
145:
146: try {
147: s.setMaxFieldSize(100);
148: System.out.println("Statement Test failed (5)");
149: } catch (SQLException e) {
150: }
151:
152: try {
153: s.getMaxRows();
154: System.out.println("Statement Test failed (6)");
155: } catch (SQLException e) {
156: }
157:
158: try {
159: s.setMaxRows(1000);
160: System.out.println("Statement Test failed (7)");
161: } catch (SQLException e) {
162: }
163:
164: try {
165: s.setEscapeProcessing(true);
166: System.out.println("Statement Test failed (8)");
167: } catch (SQLException e) {
168: }
169:
170: if (!isDerbyNet) {
171: // DerbyNet doesn't throw exception
172: try {
173: s.getQueryTimeout();
174: System.out.println("Statement Test failed (9)");
175: } catch (SQLException e) {
176: }
177: }
178: try {
179: s.setQueryTimeout(20);
180: System.out.println("Statement Test failed (10)");
181: } catch (SQLException e) {
182: }
183:
184: try {
185: s.cancel();
186: System.out.println("Statement Test failed (11)");
187: } catch (SQLException e) {
188: }
189:
190: if (isDerbyNet)
191: System.out.println("beetle 5524");
192: try {
193: s.getWarnings();
194: System.out.println("Statement Test failed (12)");
195: } catch (SQLException e) {
196: }
197:
198: if (isDerbyNet)
199: System.out.println("beetle 5524");
200: try {
201: s.clearWarnings();
202: System.out.println("Statement Test failed (13)");
203: } catch (SQLException e) {
204: }
205:
206: try {
207: s.setCursorName("ABC");
208: System.out.println("Statement Test failed (14)");
209: } catch (SQLException e) {
210: }
211:
212: try {
213: s.execute("create table tab3(num int)");
214: System.out.println("Statement Test failed (15)");
215: } catch (SQLException e) {
216: }
217:
218: try {
219: s.getResultSet();
220: System.out.println("Statement Test failed (16)");
221: } catch (SQLException e) {
222: }
223:
224: try {
225: s.getUpdateCount();
226: System.out.println("Statement Test failed (17)");
227: } catch (SQLException e) {
228: }
229:
230: try {
231: s.getMoreResults();
232: System.out.println("Statement Test failed (18) ");
233: } catch (SQLException e) {
234: }
235:
236: try {
237: s.getResultSetType();
238: System.out.println("Statement Test failed (19)");
239: } catch (SQLException e) {
240: }
241:
242: try {
243: s.setFetchDirection(JDBC20Translation.FETCH_FORWARD);
244: System.out.println("Statement Test failed (20)");
245: } catch (SQLException e) {
246: }
247:
248: try {
249: s.setFetchSize(100);
250: System.out.println("Statement Test failed (21)");
251: } catch (SQLException e) {
252: }
253:
254: try {
255: s.getFetchSize();
256: System.out.println("Statement Test failed (22)");
257: } catch (SQLException e) {
258: }
259:
260: try {
261: s.getResultSetConcurrency();
262: System.out.println("Statement Test failed (23)");
263: } catch (SQLException e) {
264: }
265:
266: try {
267: s.addBatch("create table tab3(age int)");
268: System.out.println("Statement Test failed (24)");
269: } catch (SQLException e) {
270: }
271:
272: try {
273: s.clearBatch();
274: System.out.println("Statement Test failed");
275: } catch (SQLException e) {
276: }
277:
278: try {
279: s.executeBatch();
280: System.out.println("Statement Test failed");
281: } catch (SQLException e) {
282: }
283:
284: try {
285: s.getConnection();
286: System.out.println("Statement Test failed");
287: } catch (SQLException e) {
288: }
289: } catch (Throwable e) {
290: System.out.println("FAIL -- unexpected exception:"
291: + e.toString());
292: e.printStackTrace(System.out);
293: return;
294: }
295: System.out.println("Statement test end");
296: }
297:
298: private static void test2(Connection conn) {
299: PreparedStatement ps;
300: try {
301: System.out.println("Prepared Statement test begin");
302: ps = conn
303: .prepareStatement("create table tab2(a int, b float, c date, d varchar(100))");
304: ps.execute();
305: ps.close();
306:
307: //we test execute() and executeQuery() here
308: ps = conn.prepareStatement("select a from tab2");
309: ps.execute();
310: ps.close();
311: try {
312: ps.execute();
313: System.out.println("Prepared Statement Test failed");
314: } catch (SQLException e) {
315: }
316:
317: try {
318: ps.executeQuery();
319: System.out.println("Prepared Statement Test failed");
320: } catch (SQLException e) {
321: }
322:
323: ps = conn
324: .prepareStatement("insert into tab2 values(?, ?, ?, ?)");
325: /*
326: We create a table, add a row, and close the statement.
327: */
328: ps.setInt(1, 420);
329: ps.setFloat(2, (float) 12.21);
330: ps.setDate(3, new Date(870505200000L));
331: ps.setString(4, "China");
332: ps.executeUpdate();
333: ps.close();
334:
335: //now, we begin the test
336: try {
337: ps.setInt(1, 530);
338: System.out.println("Prepared Statement Test failed");
339: } catch (SQLException e) {
340: }
341:
342: try {
343: ps.setFloat(2, (float) 3.14);
344: System.out.println("Prepared Statement Test failed");
345: } catch (SQLException e) {
346: }
347:
348: try {
349: ps.setDate(3, new Date(870505200000L));
350: System.out.println("Prepared Statement Test failed");
351: } catch (SQLException e) {
352: }
353:
354: try {
355: ps.setDate(3, new Date(870505200000L),
356: new GregorianCalendar());
357: System.out.println("Prepared Statement Test failed");
358: } catch (SQLException e) {
359: }
360:
361: try {
362: ps.setString(4, "HongKong");
363: System.out.println("Prepared Statement Test failed");
364: } catch (SQLException e) {
365: }
366:
367: try {
368: ps.executeUpdate();
369: System.out.println("Prepared Statement Test failed");
370: } catch (SQLException e) {
371: }
372:
373: /* close() is the only method that can be called
374: * after a Statement has been closed, as per
375: * Jon Ellis.
376: */
377: try {
378: ps.close();
379: } catch (SQLException e) {
380: System.out.println("Prepared Statement Test failed");
381: }
382:
383: try {
384: ps.clearParameters();
385: System.out.println("Prepared Statement Test failed");
386: } catch (SQLException e) {
387: }
388:
389: try {
390: ps.getMetaData();
391: System.out.println("Prepared Statement Test failed");
392: } catch (SQLException e) {
393: }
394:
395: try {
396: ps.getMaxFieldSize();
397: System.out.println("Prepared Statement Test failed");
398: } catch (SQLException e) {
399: }
400:
401: try {
402: ps.setMaxFieldSize(100);
403: System.out.println("Prepared Statement Test failed");
404: } catch (SQLException e) {
405: }
406:
407: try {
408: ps.getMaxRows();
409: System.out.println("Prepared Statement Test failed");
410: } catch (SQLException e) {
411: }
412:
413: try {
414: ps.setMaxRows(1000);
415: System.out.println("Prepared Statement Test failed");
416: } catch (SQLException e) {
417: }
418:
419: try {
420: ps.setEscapeProcessing(true);
421: System.out.println("Prepared Statement Test failed");
422: } catch (SQLException e) {
423: }
424:
425: if (!isDerbyNet) {
426: // DerbyNet doesn't throw exception
427: try {
428: ps.getQueryTimeout();
429: System.out
430: .println("Prepared Statement Test failed");
431: } catch (SQLException e) {
432: }
433: }
434:
435: try {
436: ps.setQueryTimeout(20);
437: System.out.println("Prepared Statement Test failed");
438: } catch (SQLException e) {
439: }
440:
441: try {
442: ps.cancel();
443: System.out.println("Prepared Statement Test failed");
444: } catch (SQLException e) {
445: }
446:
447: if (isDerbyNet)
448: System.out.println("beetle 5524");
449: try {
450: ps.getWarnings();
451: System.out.println("Prepared Statement Test failed");
452: } catch (SQLException e) {
453: }
454:
455: if (isDerbyNet)
456: System.out.println("beetle 5524");
457: try {
458: ps.clearWarnings();
459: System.out.println("Prepared Statement Test failed");
460: } catch (SQLException e) {
461: }
462:
463: try {
464: ps.setCursorName("ABC");
465: System.out.println("Prepared Statement Test failed");
466: } catch (SQLException e) {
467: }
468:
469: try {
470: ps.execute("create table tab3(num int)");
471: System.out.println("Prepared Statement Test failed");
472: } catch (SQLException e) {
473: }
474:
475: try {
476: ps.getResultSet();
477: System.out.println("Prepared Statement Test failed");
478: } catch (SQLException e) {
479: }
480:
481: try {
482: ps.getUpdateCount();
483: System.out.println("Prepared Statement Test failed");
484: } catch (SQLException e) {
485: }
486:
487: try {
488: ps.getMoreResults();
489: System.out.println("Prepared Statement Test failed");
490: } catch (SQLException e) {
491: }
492:
493: try {
494: ps.getResultSetType();
495: System.out.println("Prepared Statement Test failed");
496: } catch (SQLException e) {
497: }
498:
499: try {
500: ps.setFetchDirection(JDBC20Translation.FETCH_FORWARD);
501: System.out.println("Prepared Statement Test failed");
502: } catch (SQLException e) {
503: }
504:
505: try {
506: ps.setFetchSize(100);
507: System.out.println("Prepared Statement Test failed");
508: } catch (SQLException e) {
509: }
510:
511: try {
512: ps.getFetchSize();
513: System.out.println("Prepared Statement Test failed");
514: } catch (SQLException e) {
515: }
516:
517: try {
518: ps.getResultSetConcurrency();
519: System.out.println("Prepared Statement Test failed");
520: } catch (SQLException e) {
521: }
522:
523: try {
524: ps.addBatch();
525: System.out.println("Prepared Statement Test failed");
526: } catch (SQLException e) {
527: }
528:
529: try {
530: ps.clearBatch();
531: System.out.println("Prepared Statement Test failed");
532: } catch (SQLException e) {
533: }
534:
535: try {
536: ps.executeBatch();
537: System.out.println("Prepared Statement Test failed");
538: } catch (SQLException e) {
539: }
540:
541: try {
542: ps.getConnection();
543: System.out.println("Prepared Statement Test failed");
544: } catch (SQLException e) {
545: }
546: } catch (Throwable e) {
547: System.out.println("FAIL -- unexpected exception:"
548: + e.toString());
549: e.printStackTrace(System.out);
550: return;
551: }
552: System.out.println("Prepared Statement test end");
553: }
554:
555: private static void test3(Connection conn) {
556: CallableStatement cs;
557: try {
558: System.out.println("Callable Statement test begin");
559: try {
560:
561: Statement s = conn.createStatement();
562:
563: s
564: .execute("CREATE PROCEDURE takesString(OUT P1 VARCHAR(40), IN P2 INT) "
565: + "EXTERNAL NAME '"
566: + CLASS_NAME
567: + "takesString'"
568: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
569:
570: cs = conn.prepareCall("call takesString(?,?)");
571:
572: cs.registerOutParameter(1, Types.CHAR);
573: cs.setInt(2, Types.INTEGER);
574:
575: cs.execute();
576: System.out.println("The result is " + cs.getString(1));
577:
578: cs.close();
579:
580: try {
581: cs.setString(1, "ABC");
582: System.out
583: .println("Callable Statement Test failed");
584: } catch (SQLException e) {
585: }
586:
587: try {
588: cs.registerOutParameter(1, Types.CHAR);
589: System.out
590: .println("Callable Statement Test failed");
591: } catch (SQLException e) {
592: }
593:
594: s.close();
595: }
596:
597: catch (SQLException e) {
598: dumpSQLExceptions(e);
599: }
600:
601: //now, testing all the inherited functions
602: cs = conn
603: .prepareCall("create table tab3(a int, b float, c varchar(100))");
604: cs.execute();
605: cs.close();
606:
607: //we test execute() and executeQuery() here
608: cs = conn.prepareCall("select a from tab3");
609: ResultSet rs = cs.executeQuery();
610: cs.close();
611: try {
612: cs.execute();
613: System.out.println("Callable Statement Test failed");
614: } catch (SQLException e) {
615: }
616:
617: try {
618: cs.executeQuery();
619: System.out.println("Callable Statement Test failed");
620: } catch (SQLException e) {
621: }
622:
623: cs = conn.prepareCall("insert into tab3 values(?, ?, ?)");
624: /*
625: We create a table, add a row, and close the statement.
626: */
627: cs.setInt(1, 420);
628: cs.setFloat(2, (float) 12.21);
629: cs.setString(3, "China");
630:
631: cs.executeUpdate();
632: cs.close();
633:
634: //now, we begin the test
635: try {
636: cs.setInt(1, 530);
637: System.out.println("Callable Statement Test failed");
638: } catch (SQLException e) {
639: }
640:
641: try {
642: cs.setFloat(2, (float) 3.14);
643: System.out.println("Callable Statement Test failed");
644: } catch (SQLException e) {
645: }
646:
647: try {
648: cs.setString(3, "HongKong");
649: System.out.println("Callable Statement Test failed");
650: } catch (SQLException e) {
651: }
652:
653: try {
654: cs.executeUpdate();
655: System.out.println("Callable Statement Test failed");
656: } catch (SQLException e) {
657: }
658:
659: /* close() is the only method that can be called
660: * after a Statement has been closed, as per
661: * Jon Ellis.
662: */
663: try {
664: cs.close();
665: } catch (SQLException e) {
666: System.out.println("Callable Statement Test failed");
667: }
668:
669: try {
670: cs.clearParameters();
671: System.out.println("Callable Statement Test failed");
672: } catch (SQLException e) {
673: }
674:
675: try {
676: cs.getMetaData();
677: System.out.println("Callable Statement Test failed");
678: } catch (SQLException e) {
679: }
680:
681: try {
682: cs.getMaxFieldSize();
683: System.out.println("Callable Statement Test failed");
684: } catch (SQLException e) {
685: }
686:
687: try {
688: cs.setMaxFieldSize(100);
689: System.out.println("Callable Statement Test failed");
690: } catch (SQLException e) {
691: }
692:
693: try {
694: cs.getMaxRows();
695: System.out.println("Callable Statement Test failed");
696: } catch (SQLException e) {
697: }
698:
699: try {
700: cs.setMaxRows(1000);
701: System.out.println("Callable Statement Test failed");
702: } catch (SQLException e) {
703: }
704:
705: try {
706: cs.setEscapeProcessing(true);
707: System.out.println("Callable Statement Test failed");
708: } catch (SQLException e) {
709: }
710:
711: if (!isDerbyNet) {
712: // DerbyNet doesn't throw exception
713: try {
714: cs.getQueryTimeout();
715: System.out
716: .println("Callable Statement Test failed");
717: } catch (SQLException e) {
718: }
719: }
720:
721: try {
722: cs.setQueryTimeout(20);
723: System.out.println("Callable Statement Test failed");
724: } catch (SQLException e) {
725: }
726:
727: try {
728: cs.cancel();
729: System.out.println("Callable Statement Test failed");
730: } catch (SQLException e) {
731: }
732:
733: if (isDerbyNet)
734: System.out.println("beetle 5524");
735: try {
736: cs.getWarnings();
737: System.out.println("Callable Statement Test failed");
738: } catch (SQLException e) {
739: }
740:
741: if (isDerbyNet)
742: System.out.println("beetle 5524");
743: try {
744: cs.clearWarnings();
745: System.out.println("Callable Statement Test failed");
746: } catch (SQLException e) {
747: }
748:
749: try {
750: cs.setCursorName("ABC");
751: System.out.println("Callable Statement Test failed");
752: } catch (SQLException e) {
753: }
754:
755: try {
756: cs.execute("create table tab3(num int)");
757: System.out.println("Callable Statement Test failed");
758: } catch (SQLException e) {
759: }
760:
761: try {
762: cs.getResultSet();
763: System.out.println("Callable Statement Test failed");
764: } catch (SQLException e) {
765: }
766:
767: try {
768: cs.getUpdateCount();
769: System.out.println("Callable Statement Test failed");
770: } catch (SQLException e) {
771: }
772:
773: try {
774: cs.getMoreResults();
775: System.out.println("Callable Statement Test failed");
776: } catch (SQLException e) {
777: }
778:
779: try {
780: cs.getResultSetType();
781: System.out.println("Callable Statement Test failed");
782: } catch (SQLException e) {
783: }
784:
785: try {
786: cs.setFetchDirection(JDBC20Translation.FETCH_FORWARD);
787: System.out.println("Callable Statement Test failed");
788: } catch (SQLException e) {
789: }
790:
791: try {
792: cs.setFetchSize(100);
793: System.out.println("Callable Statement Test failed");
794: } catch (SQLException e) {
795: }
796:
797: try {
798: cs.getFetchSize();
799: System.out.println("Callable Statement Test failed");
800: } catch (SQLException e) {
801: }
802:
803: try {
804: cs.getResultSetConcurrency();
805: System.out.println("Callable Statement Test failed");
806: } catch (SQLException e) {
807: }
808:
809: try {
810: cs.addBatch();
811: System.out.println("Callable Statement Test failed");
812: } catch (SQLException e) {
813: }
814:
815: try {
816: cs.clearBatch();
817: System.out.println("Callable Statement Test failed");
818: } catch (SQLException e) {
819: }
820:
821: try {
822: cs.executeBatch();
823: System.out.println("Callable Statement Test failed");
824: } catch (SQLException e) {
825: }
826:
827: try {
828: cs.getConnection();
829: System.out.println("Callable Statement Test failed");
830: } catch (SQLException e) {
831: }
832: } catch (Throwable e) {
833: System.out.println("FAIL -- unexpected exception:"
834: + e.toString());
835: e.printStackTrace(System.out);
836: return;
837: }
838: System.out.println("Callable Statement test end");
839: }
840:
841: static private void dumpSQLExceptions(SQLException se) {
842: System.out.println("FAIL -- unexpected exception: "
843: + se.toString());
844: while (se != null) {
845: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
846: se = se.getNextException();
847: }
848: }
849:
850: }
|