KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > lang > procedure


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.harness.procedure
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */

21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import org.apache.derbyTesting.functionTests.util.TestUtil;
25 import java.sql.*;
26
27
28 import org.apache.derby.tools.ij;
29 import org.apache.derby.iapi.reference.JDBC30Translation;
30 import org.apache.derby.iapi.reference.SQLState;
31
32 import java.io.PrintStream JavaDoc;
33 import java.math.BigInteger JavaDoc;
34 import java.math.BigDecimal JavaDoc;
35
36 import java.lang.reflect.*;
37
38 import org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30;
39
40 public class procedure
41 {
42
43   private static Class JavaDoc[] CONN_PARAM = { Integer.TYPE };
44   private static Object JavaDoc[] CONN_ARG = { new Integer JavaDoc(JDBC30Translation.CLOSE_CURSORS_AT_COMMIT)};
45
46     static private boolean isDerbyNet = false;
47
48     public static void main (String JavaDoc[] argv) throws Throwable JavaDoc
49     {
50         ij.getPropertyArg(argv);
51         Connection conn = ij.startJBMS();
52         cleanUp(conn);
53         isDerbyNet = TestUtil.isNetFramework();
54
55         // DB2 !!
56
// com.ibm.db2.jcc.DB2DataSource ds = new com.ibm.db2.jcc.DB2DataSource();
57

58         // ds.setDatabaseName("testdb");
59

60         // ds.setServerName("localhost");
61
//ds.setPortNumber(1527);
62
// ds.setDriverType(4);
63

64          // Connection conn = ds.getConnection("db2admin", "password");
65

66         //Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
67
//Connection conn = DriverManager.getConnection("jdbc:db2:testdb", "USER", "XXXXX");
68

69
70         runTests( conn);
71     }
72
73     public static void runTests( Connection conn) throws Throwable JavaDoc
74     {
75         try {
76             testNegative(conn);
77             testDelayedClassChecking(conn);
78             testDuplicates(conn);
79             ambigiousMethods(conn);
80             zeroArgProcedures(conn);
81             sqlProcedures(conn);
82             dynamicResultSets(conn, ij.startJBMS());
83
84             testParameterTypes(conn);
85             testOutparams(conn);
86
87             testSQLControl(conn);
88             testLiterals(conn);
89             
90             multipleRSTests(conn);
91                         jira_491_492(conn);
92             testImplicitClose(conn);
93             cleanUp(conn);
94         } catch (SQLException sqle) {
95             org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
96             sqle.printStackTrace(System.out);
97         }
98         
99     }
100
101     public static void testNegative(Connection conn) throws SQLException {
102
103         System.out.println("testNegative");
104
105         Statement s = conn.createStatement();
106
107         // no '.' in path/method
108
statementExceptionExpected(s, "create procedure asdf() language java external name 'asdfasdf' parameter style java");
109
110         // trailing '.'
111
statementExceptionExpected(s, "create procedure asdf() language java external name 'asdfasdf.' parameter style java");
112
113         // procedure name too long
114
statementExceptionExpected(s, "create procedure a23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789() language java external name 'asdf.asdf' parameter style java");
115
116         // -- missing parens on procedure name
117
statementExceptionExpected(s, "create procedure asdf language java external name java.lang.Thread.currentThread parameter style java");
118
119         // -- incorrect language, (almost) straight from DB2 docs
120

121         statementExceptionExpected(s, "CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER, OUT NUM_PARTS INTEGER, OUT COST DOUBLE) EXTERNAL NAME 'parts!assembly' DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL");
122
123         // invalid schema
124
statementExceptionExpected(s, "create procedure sys.proc1() language java external name 'java.lang.System.gc' parameter style java");
125
126         // repeated elements
127
statementExceptionExpected(s, "create procedure noclass() language java external name 'asdf.asdf' parameter style java language java");
128         statementExceptionExpected(s, "create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style java");
129         statementExceptionExpected(s, "create procedure noclass() external name 'asdf.xxxx' language java external name 'asdf.asdf' parameter style java");
130         statementExceptionExpected(s, "create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style derby_rs_collection");
131
132         // missing elements
133
statementExceptionExpected(s, "create procedure missing01()");
134         statementExceptionExpected(s, "create procedure missing02() language java");
135         statementExceptionExpected(s, "create procedure missing03() language java parameter style java");
136         statementExceptionExpected(s, "create procedure missing04() language java external name 'foo.bar'");
137         statementExceptionExpected(s, "create procedure missing05() parameter style java");
138         statementExceptionExpected(s, "create procedure missing06() parameter style java external name 'foo.bar'");
139         statementExceptionExpected(s, "create procedure missing07() external name 'goo.bar'");
140         statementExceptionExpected(s, "create procedure missing08() dynamic result sets 1");
141         //statementExceptionExpected(s, "create procedure missing09() specific name fred");
142

143         // RETURNS NULL ON NULL INPUT not allowed in procedures.
144
statementExceptionExpected(s, "create procedure nullinput2() returns null on null input language java parameter style java external name 'foo.bar'");
145         
146
147
148         // no BLOB/CLOB/ long parameters
149
statementExceptionExpected(s, "create procedure NO_BLOB(IN P1 BLOB(3k)) language java parameter style java external name 'no.blob'");
150         statementExceptionExpected(s, "create procedure NO_CLOB(IN P1 CLOB(3k)) language java parameter style java external name 'no.clob'");
151         statementExceptionExpected(s, "create procedure NO_LVC(IN P1 LONG VARCHAR) language java parameter style java external name 'no.lvc'");
152
153         // duplicate names
154
statementExceptionExpected(s, "create procedure DUP_P1(IN FRED INT, OUT RON CHAR(10), IN FRED INT) language java parameter style java external name 'no.dup1'");
155         statementExceptionExpected(s, "create procedure D2.DUP_P2(IN \"FreD\" INT, OUT RON CHAR(10), IN \"FreD\" INT) language java parameter style java external name 'no.dup2'");
156         statementExceptionExpected(s, "create procedure D3.DUP_P3(IN \"FRED\" INT, OUT RON CHAR(10), IN fred INT) language java parameter style java external name 'no.dup3'");
157         s.execute("create procedure DUP_POK(IN \"FreD\" INT, OUT RON CHAR(10), IN fred INT) language java parameter style java external name 'no.dupok'");
158         s.execute("drop procedure DUP_POK");
159
160         // procedure not found with explicit schema name
161
statementExceptionExpected(s, "CALL APP.NSP(?, ?)");
162
163         // bug 5760 - this caused a null pointer exception at one time.
164
statementExceptionExpected(s, "call syscs_util.syscs_set_database_property(\"foo\", \"bar\")");
165         
166         // Derby-258 specific signatures with types not matching JDBC spec.
167
System.out.println("signature mismatched types");
168         s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_A(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.load(java.lang.String)'");
169         statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_A(4)");
170         s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_A");
171
172         // signature with wrong number of arguments, too many
173
System.out.println("signature too many parameters");
174         s.execute("CREATE FUNCTION SIGNATURE_BUG_DERBY_258_B(A INT) RETURNS VARCHAR(128) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.Integer.toString(int, int)'");
175         statementExceptionExpected(s, "VALUES APP.SIGNATURE_BUG_DERBY_258_B(4)");
176         s.execute("DROP FUNCTION SIGNATURE_BUG_DERBY_258_B");
177
178         // and too few
179
System.out.println("signature too few parameters");
180         s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_C(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc()'");
181         statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_C(4)");
182         s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_C");
183
184         // only a leading paren
185
System.out.println("signature invalid format");
186         s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_F(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc('");
187         statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_F(4)");
188         s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_F");
189
190         // signature of (,,)
191
System.out.println("signature invalid format");
192         s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_G(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(,,)'");
193         statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_G(4)");
194         s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_G");
195
196         // signature of (, ,)
197
System.out.println("signature invalid format");
198         s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_H(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(, ,)'");
199         statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_H(4)");
200         s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_H");
201
202         // signature of (int,)
203
System.out.println("signature invalid format");
204         s.execute("CREATE PROCEDURE SIGNATURE_BUG_DERBY_258_I(IN A INT) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'java.lang.System.gc(int ,)'");
205         statementExceptionExpected(s, "CALL APP.SIGNATURE_BUG_DERBY_258_I(4)");
206         s.execute("DROP PROCEDURE SIGNATURE_BUG_DERBY_258_I");
207         
208         s.close();
209         
210     }
211     
212    
213     public static void testBug5280(Connection conn) throws SQLException
214     {
215         String JavaDoc csString = "CALL SQLCONTROL3_0 (?, ?, ?, ?, ?, ?, ?)";
216         // Bug 5280 If we don't register the outparams
217
// we don't get an error with network server.
218
//for (int p = 1; p <= 7; p++) {
219
// cs.registerOutParameter(p,Types.VARCHAR);
220
//}
221
callExceptionExpected(conn, csString);
222     }
223
224     public static void testDelayedClassChecking(Connection conn) throws SQLException {
225
226         System.out.println("testDelayedClassChecking");
227
228
229         Statement s = conn.createStatement();
230         // -- procedures do not check if the class or method exists at create time.
231
s.execute("create procedure noclass() language java external name 'asdf.asdf' parameter style java");
232         s.execute("create procedure nomethod() language java external name 'java.lang.Integer.asdf' parameter style java");
233         s.execute("create procedure notstatic() language java external name 'java.lang.Integer.equals' parameter style java");
234         s.execute("create procedure notvoid() language java external name 'java.lang.Runtime.getRuntime' parameter style java");
235
236         // - but they are checked at runtime
237
callExceptionExpected(conn, "call noclass()");
238         callExceptionExpected(conn, "call nomethod()");
239         callExceptionExpected(conn, "call notstatic()");
240         callExceptionExpected(conn, "call notvoid()");
241
242         // CHECK SYSALIAS
243
s.execute("drop procedure noclass");
244         s.execute("drop procedure nomethod");
245         s.execute("drop procedure notstatic");
246         s.execute("drop procedure notvoid");
247
248         s.close();
249
250     }
251
252     public static void testDuplicates(Connection conn) throws SQLException {
253         System.out.println("testDuplicates");
254
255
256         Statement s = conn.createStatement();
257
258         s.execute("create schema S1");
259         s.execute("create schema S2");
260
261         s.execute("create procedure PROCDUP() language java external name 'okAPP.ok0' parameter style java");
262         s.execute("create procedure s1.PROCDUP() language java external name 'oks1.ok0' parameter style java");
263         s.execute("create procedure s2.PROCDUP() language java external name 'oks2.ok0' parameter style java");
264
265         statementExceptionExpected(s, "create procedure PROCDUP() language java external name 'failAPP.fail0' parameter style java");
266         statementExceptionExpected(s, "create procedure s1.PROCDUP() language java external name 'fails1.fail0' parameter style java");
267         statementExceptionExpected(s, "create procedure s2.PROCDUP() language java external name 'fails2.fail0' parameter style java");
268
269         showMatchingProcedures(conn, "PROCDUP");
270
271         statementExceptionExpected(s, "create procedure S1.NOTYET() SPECIFIC fred language java external name 'failAPP.fail0' parameter style java");
272         
273         s.execute("drop procedure s1.PROCDUP");
274         s.execute("drop procedure s2.PROCDUP");
275
276         s.execute("drop schema S1 RESTRICT");
277         s.execute("drop schema S2 RESTRICT");
278         s.close();
279
280
281     }
282
283     public static void ambigiousMethods(Connection conn) throws SQLException {
284         System.out.println("ambigiousMethods");
285
286         Statement s = conn.createStatement();
287
288         // ambigious resolution - with result sets
289
s.execute("create procedure ambigious01(p1 INTEGER, p2 CHAR(20)) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious1'");
290         callExceptionExpected(conn, "call AMBIGIOUS01(?, ?)");
291         s.execute("drop procedure AMBIGIOUS01");
292
293         // ambigious in defined parameters
294
s.execute("create procedure ambigious02(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2'");
295         callExceptionExpected(conn, "call AMBIGIOUS02(?, ?)");
296         s.execute("drop procedure AMBIGIOUS02");
297
298         // verify we can find it with a Java signature
299
s.execute("create procedure ambigious03(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2(int,java.lang.Integer)'");
300         executeProcedure(s, "{call ambigious03(1, NULL)}");
301         s.execute("drop procedure AMBIGIOUS03");
302         s.execute("create procedure ambigious04(p1 INTEGER, p2 INTEGER) dynamic result sets 1 language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2(java.lang.Integer,int)'");
303         executeProcedure(s, "{call ambigious04(NULL, 1)}");
304         s.execute("drop procedure AMBIGIOUS04");
305         s.close();
306     }
307
308     public static void zeroArgProcedures(Connection conn) throws SQLException {
309         System.out.println("zeroArgProcedures");
310
311         Statement s = conn.createStatement();
312         s.execute("create procedure za() language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArg' parameter style java");
313
314         executeProcedure(s, "call za()");
315         PreparedStatement ps = conn.prepareStatement("call za()");
316         executeProcedure(ps);
317         ps.close();
318
319         ps = conn.prepareStatement("{call za()}");
320         executeProcedure(ps);
321         ps.close();
322
323
324         try {
325             ps = conn.prepareStatement("call za(?)");
326             System.out.println("FAIL - prepareStatement call za(?)");
327         } catch (SQLException sqle) {
328             System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
329         }
330
331         CallableStatement cs = conn.prepareCall("call za()");
332         executeProcedure(cs);
333         cs.close();
334
335         cs = conn.prepareCall("{call za()}");
336         executeProcedure(cs);
337         cs.close();
338
339         showMatchingProcedures(conn, "ZA");
340         s.execute("drop procedure za");
341         showMatchingProcedures(conn, "ZA");
342
343         s.close();
344
345     }
346
347     private static void sqlProcedures(Connection conn) throws SQLException {
348
349         System.out.println("sqlProcedures()");
350
351         Statement s = conn.createStatement();
352
353         s.execute("create table t1(i int not null primary key, b char(15))");
354         s.execute("create procedure ir(p1 int) MODIFIES SQL DATA dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow' parameter style java");
355         s.execute("create procedure ir2(p1 int, p2 char(10)) language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow' MODIFIES SQL DATA parameter style java");
356
357         showMatchingProcedures(conn, "IR%");
358
359         callExceptionExpected(conn, "CALL IR()");
360
361         CallableStatement ir1 = conn.prepareCall("CALL IR(?)");
362
363         ir1.setInt(1, 1);
364         executeProcedure(ir1);
365
366         ir1.setInt(1,2);
367         executeProcedure(ir1);
368         try {
369             ir1.execute();
370             System.out.println("FAIL - duplicate key insertion through ir");
371         } catch (SQLException sqle) {
372             System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
373         }
374
375         ir1.setString(1, "3");
376         executeProcedure(ir1);
377
378         ir1.close();
379
380         ir1 = conn.prepareCall("CALL APP.IR(?)");
381         ir1.setInt(1, 7);
382         executeProcedure(ir1);
383
384         CallableStatement ir2 = conn.prepareCall("CALL IR2(?, ?)");
385
386         ir2.setInt(1, 4);
387         ir2.setInt(2, 4);
388         executeProcedure(ir2);
389
390         ir2.setInt(1, 5);
391         ir2.setString(2, "ir2");
392         executeProcedure(ir2);
393
394
395         ir2.setInt(1, 6);
396         ir2.setString(2, "'012345678990'");
397         executeProcedure(ir2);
398
399         ir1.close();
400         ir2.close();
401
402         if (!conn.getAutoCommit())
403             conn.commit();
404
405
406
407         ResultSet rs = s.executeQuery("select * from t1");
408         org.apache.derby.tools.JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
409
410         if (!conn.getAutoCommit())
411             conn.commit();
412
413         callExceptionExpected(conn, "CALL IR2(2, 'no way')");
414         callExceptionExpected(conn, "CALL IR2(?, 'no way')");
415         callExceptionExpected(conn, "CALL IR2(2, ?)");
416
417         s.execute("drop procedure IR");
418         s.execute("drop procedure IR2");
419
420         s.close();
421     }
422     // This test case provides tests for bugs DERBY-491 and DERBY-492. These
423
// two bug reports describe different symptoms, but the underlying bug
424
// is identical: the network server's implementation of LMTBLKPRC was
425
// incorrectly manipulating DDMWriter's bytes buffer. Depending on the
426
// details, the symptom of this bug was generally a hang, because the
427
// server mistakenly truncated the unsent data in its network buffer and
428
// hence sent only a partial transmission, causing the client to hang,
429
// waiting for data that would never arrive. A more detailed analysis
430
// of some other possible symptoms that could arise from these tests is
431
// available in the bug notes for bug 491 in JIRA at:
432
// http://issues.apache.org/jira/browse/DERBY-491
433
//
434
private static void jira_491_492(Connection conn)
435         throws SQLException
436     {
437         Statement st = conn.createStatement();
438         PreparedStatement pSt = null;
439
440         // JIRA-491: Result set has a row that is approx 32K long.
441
// When originally filed, this bug script caused a protocol
442
// exception and connection deallocation, but that was because the
443
// bug script provoked both JIRA-614 *and* JIRA-491. If you have
444
// the fix for JIRA-614, but JIRA-491 has regressed, you will hang.
445

446         try {
447             st.execute("drop table testtable1");
448         } catch (SQLException se) {}
449
450         // Create an array of chars to be used as the input parameter.
451
// Note that the array should roughly 32K or larger.
452
char [] cData = new char[32500];
453         for (int i = 0; i < cData.length; i++)
454             cData[i] = Character.forDigit(i%10, 10);
455
456         try {
457             st.execute("create table jira491 (int1 integer, varchar32k varchar(32500))");
458             pSt=conn.prepareStatement("insert into jira491 values (?,?)");
459             for (int i = 1; i <= 5; i++) {
460                 pSt.setInt(1, i);
461                 pSt.setString(2, new String JavaDoc(cData));
462                 pSt.execute();
463             }
464         } catch (SQLException se) {
465             System.out.println("JIRA-491: FAILURE in data generation:");
466             se.printStackTrace(System.out);
467         }
468
469         try {
470             st.execute("drop procedure TEST_PROC_JIRA_491");
471         } catch (SQLException se) {} // Ignore "proc does not exist" errors
472

473         try {
474             st.execute("create procedure TEST_PROC_JIRA_491(in i int) " +
475                         "language java parameter style java external name " +
476                         "'org.apache.derbyTesting.functionTests.util.ProcedureTest.BIG_COL_491' result sets 2");
477         } catch (SQLException se) {
478             System.out.println("JIRA-491: FAILURE in procedure creation:");
479             se.printStackTrace(System.out);
480         }
481
482         // Call setupStatementReuse which will make the server to reuse an existing statement.
483
setupStatementReuse(conn);
484         CallableStatement cSt = conn.prepareCall("call TEST_PROC_JIRA_491(?)");
485         cSt.setInt(1, 3);
486         try {
487             cSt.execute();
488             do {
489                 ResultSet rs = cSt.getResultSet();
490                 while (rs.next()) {
491                     String JavaDoc s = rs.getString(2);
492                 }
493             } while (cSt.getMoreResults());
494             System.out.println("JIRA-491 Successful.");
495         }
496         catch (Exception JavaDoc e)
497         {
498             System.out.println("JIRA-491 FAILURE: Caught Exception:");
499             e.printStackTrace(System.out);
500         }
501     
502         // JIRA-492: Result set has hundreds of columns.
503
// This test case, when originally filed, exposed several problems:
504
// - first, this test case causes the server to respond with a very
505
// long response message which gets handled using DRDA Layer B DSS
506
// segmentation. This long message was corrupted due to bug DERBY-125.
507
// - then, the test case causes the server to perform LMTBLKPRC
508
// message truncation in a situation in which there are multiple
509
// chained messages in the DDMWriter buffer. Due to bug DERBY-491/2,
510
// the message truncation logic truncated not only the last DSS block,
511
// but also the multi-segment long message which was still sitting
512
// unsent in the buffer.This then caused a HANG in the client, which
513
// waited forever for the never-to-be-sent truncated data.
514

515         try {
516             st.execute("drop table jira492");
517         } catch (SQLException se) {}
518
519         try {
520             st.execute("create table jira492 (id integer, nsi smallint, " +
521                 "ni integer, nbi DECIMAL(19,0), nd decimal(7,2), nr real, " +
522                 "ndo double)");
523             st.execute("insert into jira492 values (" +
524                         "1, 2, 3, 4.5, 6.7, 8.9, 10.11)");
525         } catch (SQLException se) {
526             System.out.println("JIRA-492: FAILURE in data setup:");
527             se.printStackTrace(System.out);
528         }
529     
530         try {
531             st.execute("drop procedure TEST_PROC_JIRA_492");
532         } catch (SQLException se) {}
533     
534         try {
535             st.execute("create procedure TEST_PROC_JIRA_492() " +
536                     "language java parameter style java external name " +
537                     "'org.apache.derbyTesting.functionTests.util.ProcedureTest.LOTS_O_COLS_492' result sets 1");
538         } catch (SQLException se) {
539             System.out.println("JIRA-492: FAILURE in procedure creation:");
540             se.printStackTrace(System.out);
541         }
542     
543         cSt = conn.prepareCall("call TEST_PROC_JIRA_492()");
544         cSt.execute();
545         System.out.println("JIRA-492 successful -- no hang!");
546     }
547
548     private static void executeProcedure(Statement s, String JavaDoc sql) throws SQLException {
549         boolean firstResultIsAResultSet = s.execute(sql);
550
551         procedureResults(s, firstResultIsAResultSet);
552     }
553
554
555     private static void executeProcedure(PreparedStatement ps) throws SQLException {
556         boolean firstResultIsAResultSet = ps.execute();
557
558         procedureResults(ps, firstResultIsAResultSet);
559     }
560
561
562     private static void procedureResults(Statement ps, boolean firstResultIsAResultSet) throws SQLException {
563
564         org.apache.derby.tools.JDBCDisplayUtil.ShowWarnings(System.out, ps);
565
566         boolean sawOneResult = false;
567         boolean isFirst = true;
568         do {
569
570             boolean gotResult = false;
571
572             ResultSet rs = ps.getResultSet();
573             int updateCount = ps.getUpdateCount();
574             if (rs == null) {
575
576                 if (isFirst && firstResultIsAResultSet) {
577                     System.out.println("FAIL - execute() indicated first result was a result set but getResultSet() returned null");
578                 }
579
580                 if (updateCount != -1) {
581                     gotResult = true;
582                     sawOneResult = true;
583                     System.out.println("UPDATE COUNT " + updateCount);
584                 }
585             }
586             else {
587
588                 if (updateCount != -1)
589                     System.out.println("FAIL - HAVE RESULT SET AND UPDATE COUNT OF " + updateCount);
590                 org.apache.derby.tools.JDBCDisplayUtil.DisplayResults(System.out, rs, ps.getConnection());
591                 gotResult = true;
592                 sawOneResult = true;
593             }
594
595             // if we did not get a result and this is not the first result then
596
// there is a bug since the getMoreResults() returned true.
597
//
598
// This may also be an error on the first pass but maybe it's
599
// ok to have no results at all?
600
if (!gotResult && !isFirst) {
601                 System.out.println("FAIL - getMoreResults indicated more results but none was found");
602             }
603
604             isFirst = false;
605
606         } while (ps.getMoreResults());
607         SQLWarning warnings = ps.getWarnings();
608         if (warnings != null)
609             System.out.println("SQLWarning :" + warnings.getMessage());
610
611         if (!sawOneResult)
612             System.out.println("No ResultSet or update count returned");
613     }
614
615     /**
616         1. basic testing
617         2. correct auto commit logic
618         3. correct holdability (JDBC 3)
619     */

620     private static void dynamicResultSets(Connection conn, Connection conn2) throws SQLException {
621
622         System.out.println("dynamicResultSets - parameter style JAVA");
623
624         Statement s = conn.createStatement();
625
626         statementExceptionExpected(s, "create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets -1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");
627
628         s.execute("create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");
629
630         showMatchingProcedures(conn, "DRS");
631
632         callExceptionExpected(conn, "CALL DRS()");
633         callExceptionExpected(conn, "CALL DRS(?,?)");
634
635         CallableStatement drs1 = conn.prepareCall("CALL DRS(?)");
636
637         drs1.setInt(1, 3);
638         executeProcedure(drs1);
639         drs1.close();
640
641         s.execute("create procedure DRS2(p1 int, p2 int) parameter style JAVA READS SQL DATA dynamic result sets 2 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'");
642         showMatchingProcedures(conn, "DRS2");
643
644         drs1 = conn.prepareCall("CALL DRS2(?, ?)");
645         drs1.setInt(1, 2);
646         drs1.setInt(2, 6);
647         executeProcedure(drs1);
648             
649         // execute it returning one closed result set
650
drs1.setInt(1, 2);
651         drs1.setInt(2, 99); // will close the second result set
652
executeProcedure(drs1);
653
654         // execute it returning no result sets
655
if (! isDerbyNet)
656         {
657             //RESOLVE there appears to be a JCC Bug when returning no
658
// resultSets.
659
drs1.setInt(1, 2);
660             drs1.setInt(2, 199); // return no results at all
661
executeProcedure(drs1);
662         }
663         // execute it returning two result sets but with the order swapped in the parameters
664
// doesnot affect display order.
665
drs1.setInt(1, 2);
666         drs1.setInt(2, 299); // swap results
667
executeProcedure(drs1);
668         
669         if (!isDerbyNet)
670         {
671         // execute it returning two result sets, and check to see the result set is closed after getMoreResults.
672
drs1.setInt(1, 2);
673         drs1.setInt(2, 2);
674         drs1.execute();
675         ResultSet lastResultSet = null;
676         int pass = 1;
677         do {
678
679             if (lastResultSet != null) {
680                 try {
681                     lastResultSet.next();
682                     System.out.println("FAILED - result set should be closed");
683                 } catch (SQLException sqle) {
684                     System.out.println("EXPECTED : " + sqle.getMessage());
685                 }
686             }
687
688             lastResultSet = drs1.getResultSet();
689             System.out.println("pass " + (pass++) + " got result set " + (lastResultSet != null));
690
691         } while (drs1.getMoreResults() || lastResultSet != null);
692
693         checkCommitWithMultipleResultSets(drs1, conn2, "autocommit");
694         checkCommitWithMultipleResultSets(drs1, conn2, "noautocommit");
695         checkCommitWithMultipleResultSets(drs1, conn2, "statement");
696         }
697
698
699
700
701         drs1.close();
702
703         // use escape syntax
704
drs1 = conn.prepareCall("{call DRS2(?, ?)}");
705         drs1.setInt(1, 2);
706         drs1.setInt(2, 6);
707         executeProcedure(drs1);
708         drs1.close();
709
710
711         // check that a procedure with dynamic result sets can not resolve to a method with no ResultSet argument.
712
s.execute("create procedure irdrs(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.missingDynamicParameter' parameter style JAVA");
713         callExceptionExpected(conn, "CALL IRDRS(?)");
714         s.execute("drop procedure irdrs");
715
716         // check that a procedure with dynamic result sets can not resolve to a method with an argument that is a ResultSet impl,
717
s.execute("create procedure rsi(p1 int) dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.badDynamicParameter' parameter style JAVA");
718         callExceptionExpected(conn, "CALL rsi(?)");
719         s.execute("drop procedure rsi");
720
721         // simple check for a no-arg method that has dynamic result sets but does not return any
722
System.out.println("no dynamic result sets");
723         s.execute("create procedure zadrs() dynamic result sets 4 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArgDynamicResult' parameter style JAVA");
724         CallableStatement zadrs = conn.prepareCall("CALL ZADRS()");
725         executeProcedure(zadrs);
726         zadrs.close();
727         s.execute("drop procedure ZADRS");
728
729         // return too many result sets
730
System.out.println("Testing too many result sets");
731         s.execute("create procedure way.toomany(p1 int, p2 int) READS SQL DATA dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows' parameter style JAVA");
732         CallableStatement toomany = conn.prepareCall("CALL way.toomany(?, ?)");
733         toomany.setInt(1, 2);
734         toomany.setInt(2, 6);
735         System.out.println("... too many result sets");
736         executeProcedure(toomany);
737
738         System.out.println("... one additional closed result set");
739         toomany.setInt(1, 2);
740         toomany.setInt(2, 99); // will close the second result set.
741
executeProcedure(toomany);
742
743         toomany.close();
744         s.execute("drop procedure way.toomany");
745
746         testResultSetsWithLobs(conn);
747
748         s.close();
749         conn2.close();
750     }
751
752
753     private static void checkCommitWithMultipleResultSets(CallableStatement drs1, Connection conn2, String JavaDoc action) throws SQLException
754     {
755         Connection conn = drs1.getConnection();
756     //Use reflection to set the holdability to false so that the test can run in jdk14 and lower jdks as well
757
try {
758                 Method sh = conn.getClass().getMethod("setHoldability", CONN_PARAM);
759                 sh.invoke(conn, CONN_ARG);
760     } catch (Exception JavaDoc e) {System.out.println("shouldn't get that error " + e.getMessage());}//for jdks prior to jdk14
761

762         // check to see that the commit of the transaction happens at the correct time.
763
// switch isolation levels to keep the locks around.
764
int oldIsolation = conn.getTransactionIsolation();
765         boolean oldAutoCommit = conn.getAutoCommit();
766          
767         if (action.equals("noautocommit"))
768             conn.setAutoCommit(false);
769         else
770             conn.setAutoCommit(true);
771
772         conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
773         System.out.println("auto commit is " + conn.getAutoCommit());
774         PreparedStatement psLocks = conn2.prepareStatement("select count(*) from SYSCS_DIAG.LOCK_TABLE AS LT");
775
776         showLocks(psLocks, "lock count before execution ");
777
778         drs1.execute();
779
780         showLocks(psLocks, "lock count after execution ");
781
782         ResultSet rs = drs1.getResultSet();
783         rs.next();
784         showLocks(psLocks, "lock count after next on first rs ");
785
786         boolean expectClosed = false;
787
788         // execute another statement to ensure that the result sets close.
789
if (action.equals("statement")) {
790             System.out.println("executing statement to force auto commit on open CALL statement");
791
792             conn.createStatement().executeQuery("values 1").next();
793             expectClosed = true;
794             showLocks(psLocks, "lock count after statement execution ");
795
796             try {
797                 rs.next();
798                 System.out.println("FAIL - result set open in auto commit mode after another statement execution");
799             } catch (SQLException sqle) {
800                 System.out.println("Expected - " + sqle.getMessage());
801             }
802         }
803
804
805
806         boolean anyMore = drs1.getMoreResults();
807         System.out.println("Is there a second result ? " + anyMore);
808         showLocks(psLocks, "lock count after first getMoreResults() ");
809
810         if (anyMore) {
811         
812             rs = drs1.getResultSet();
813             try {
814                 rs.next();
815                 if (expectClosed)
816                     System.out.println("FAIL - result set open in auto commit mode after another statement execution");
817             } catch (SQLException sqle) {
818                 if (expectClosed)
819                     System.out.println("Expected - " + sqle.getMessage());
820                 else
821                     throw sqle;
822             }
823             showLocks(psLocks, "lock count after next on second rs ");
824
825             // should commit here since all results are closed
826
boolean more = drs1.getMoreResults();
827             System.out.println("more results (should be false) " + more);
828             showLocks(psLocks, "lock count after second getMoreResults() ");
829
830             conn.setTransactionIsolation(oldIsolation);
831             conn.setAutoCommit(oldAutoCommit);
832         }
833
834         psLocks.close();
835     }
836
837     private static void showLocks(PreparedStatement psLocks, String JavaDoc where) throws SQLException {
838         ResultSet locks = psLocks.executeQuery();
839         locks.next();
840         System.out.println(where + locks.getInt(1));
841         locks.close();
842     }
843
844     private static void testParameterTypes(Connection conn) throws SQLException {
845         System.out.println("parameterTypes");
846         Statement s = conn.createStatement();
847
848         s.execute("create table PT1(A INTEGER not null primary key, B CHAR(10), C VARCHAR(20))");
849         s.execute("create procedure PT1(IN a int, IN b char(10), c varchar(20)) parameter style java dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.parameter1' MODIFIES SQL DATA");
850         showMatchingProcedures(conn, "PT1");
851
852         CallableStatement pt1 = conn.prepareCall("CALL PT1(?, ?, ?)");
853
854         pt1.setInt(1, 20);
855         pt1.setString(2, "abc");
856         pt1.setString(3, "efgh");
857         executeProcedure(pt1);
858
859
860         pt1.setInt(1, 30);
861         pt1.setString(2, "abc ");
862         pt1.setString(3, "efgh ");
863         executeProcedure(pt1);
864
865         pt1.setInt(1, 40);
866         pt1.setString(2, "abc ");
867         pt1.setString(3, "efgh ");
868         executeProcedure(pt1);
869
870         pt1.setInt(1, 50);
871         pt1.setString(2, "0123456789X");
872         pt1.setString(3, "efgh ");
873         executeProcedure(pt1);
874         pt1.close();
875
876         s.execute("DROP procedure PT1");
877
878         s.execute("create procedure PT2(IN a int, IN b DECIMAL(4), c DECIMAL(7,3)) parameter style java dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.parameter2' MODIFIES SQL DATA");
879         showMatchingProcedures(conn, "PT2");
880
881         CallableStatement pt2 = conn.prepareCall("CALL PT2(?, ?, ?)");
882
883         pt2.setInt(1, 60);
884         pt2.setString(2, "34");
885         pt2.setString(3, "54.1");
886         executeProcedure(pt2);
887
888         pt2.setInt(1, 70);
889         pt2.setBigDecimal(2, new BigDecimal JavaDoc("831"));
890         pt2.setBigDecimal(3, new BigDecimal JavaDoc("45.7"));
891         executeProcedure(pt2);
892         
893         pt2.setInt(1, -1);
894         pt2.setBigDecimal(2, new BigDecimal JavaDoc("10243"));
895         pt2.setBigDecimal(3, null);
896         try {
897             executeProcedure(pt2);
898             System.out.println("FAIL - too many digits in decimal value accepted");
899         } catch (SQLException sqle) {
900             System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
901         }
902         pt2.setInt(1, 80);
903         pt2.setBigDecimal(2, new BigDecimal JavaDoc("993"));
904         pt2.setBigDecimal(3, new BigDecimal JavaDoc("1234.5678"));
905         executeProcedure(pt2);
906         pt2.close();
907
908         s.execute("DROP procedure PT2");
909 /*
910         s.execute("create procedure PTBOOL2(IN p_in BOOLEAN, INOUT p_inout BOOLEAN, OUT p_out BOOLEAN) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pBOOLEAN' NO SQL");
911         showMatchingProcedures(conn, "PTBOOL%");
912
913         {
914
915         CallableStatement ptb = conn.prepareCall("CALL PTBOOL2(?, ?, ?)");
916         ptb.registerOutParameter(2, Types.BIT);
917         ptb.registerOutParameter(3, Types.BIT);
918
919         if (!isDerbyNet){ // bug 5437
920         ptb.setObject(1, null);
921         ptb.setObject(2, Boolean.FALSE);
922         try {
923             ptb.execute();
924             System.out.println("FAIL NULL PASSED to primitive");
925         } catch (SQLException sqle) {
926             System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
927         }
928         }
929
930         ptb.setBoolean(1, true);
931         ptb.setBoolean(2, false);
932         ptb.execute();
933         System.out.println("p_inout " + ptb.getObject(2) + " p_out " + ptb.getObject(3));
934         ptb.setBoolean(2, false);
935         ptb.execute();
936         System.out.println("p_inout " + ptb.getBoolean(2) + " null?" + ptb.wasNull() + " p_out " + ptb.getBoolean(3) + " null?" + ptb.wasNull());
937         ptb.close();
938         }
939
940         s.execute("DROP procedure PTBOOL2");
941
942         s.execute("create procedure PTTINYINT2(IN p_in TINYINT, INOUT p_inout TINYINT, OUT p_out TINYINT) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pTINYINT' NO SQL");
943         showMatchingProcedures(conn, "PTTINYINT%");
944
945
946         CallableStatement ptti = conn.prepareCall("CALL PTTINYINT2(?, ?, ?)");
947         ptti.registerOutParameter(2, Types.TINYINT);
948         ptti.registerOutParameter(3, Types.TINYINT);
949
950         ptti.setNull(1, Types.TINYINT);
951         ptti.setByte(2, (byte) 7);
952         try {
953             ptti.execute();
954             System.out.println("FAIL NULL PASSED to primitive");
955         } catch (SQLException sqle) {
956             System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
957         }
958
959         ptti.setByte(1, (byte) 4);
960         ptti.setNull(2, Types.TINYINT);
961         try {
962             ptti.execute();
963             System.out.println("FAIL NULL PASSED to primitive");
964         } catch (SQLException sqle) {
965             System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
966         }
967
968         ptti.setByte(1, (byte) 6);
969         ptti.setByte(2, (byte) 3);
970         ptti.execute();
971         System.out.println("p_inout " + ptti.getObject(2) + " p_out " + ptti.getObject(3));
972         ptti.setByte(2, (byte) 3);
973         ptti.execute();
974         System.out.println("p_inout " + ptti.getByte(2) + " null?" + ptti.wasNull() + " p_out " + ptti.getByte(3) + " null?" + ptti.wasNull());
975         ptti.close();
976
977
978         s.execute("DROP procedure PTTINYINT2");
979
980     */

981         s.execute("create procedure PTSMALLINT2(IN p_in SMALLINT, INOUT p_inout SMALLINT, OUT p_out SMALLINT) parameter style java dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.pSMALLINT' NO SQL");
982         showMatchingProcedures(conn, "PTSMALLINT%");
983
984
985         CallableStatement ptsi = conn.prepareCall("CALL PTSMALLINT2(?, ?, ?)");
986         ptsi.registerOutParameter(2, Types.SMALLINT);
987         ptsi.registerOutParameter(3, Types.SMALLINT);
988
989         ptsi.setNull(1, Types.SMALLINT);
990         ptsi.setShort(2, (short) 7);
991         try {
992             ptsi.execute();
993             System.out.println("FAIL NULL PASSED to primitive");
994         } catch (SQLException sqle) {
995             System.out.println("EXPECTED SQL Exception: (" + sqle.getSQLState() + ") " + sqle.getMessage());
996         }
997
998         ptsi.setShort(1, (short) 4);
999         ptsi.setNull(2, Types.SMALLINT);
1000        try {
1001            ptsi.execute();
1002            System.out.println("FAIL NULL PASSED to primitive");
1003        } catch (SQLException sqle) {
1004            System.out.println("EXPECTED SQL Exception: (" + sqle.getSQLState() + ") " + sqle.getMessage());
1005        }
1006
1007        ptsi.setShort(1, (short) 6);
1008        ptsi.setShort(2, (short) 3);
1009        ptsi.execute();
1010        System.out.println("p_inout " + ptsi.getObject(2) + " p_out " + ptsi.getObject(3));
1011        ptsi.setShort(2, (short) 3);
1012        ptsi.execute();
1013        System.out.println("p_inout " + ptsi.getByte(2) + " null?" + ptsi.wasNull() + " p_out " + ptsi.getByte(3) + " null?" + ptsi.wasNull());
1014
1015        // with setObject . Beetle 5439
1016
ptsi.setObject(1, new Integer JavaDoc(6));
1017        ptsi.setObject(2, new Integer JavaDoc(3));
1018        
1019        ptsi.execute();
1020        System.out.println("p_inout " + ptsi.getByte(2) + " null?" + ptsi.wasNull() + " p_out " + ptsi.getByte(3) + " null?" + ptsi.wasNull());
1021        ptsi.close();
1022        
1023        s.execute("DROP procedure PTSMALLINT2");
1024        s.execute("DROP TABLE PT1");
1025
1026        s.close();
1027
1028    }
1029
1030    private static void testOutparams(Connection conn) throws SQLException {
1031
1032
1033        System.out.println("outparams");
1034
1035        Statement s = conn.createStatement();
1036
1037        s.execute("create procedure OP1(OUT a int, IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.outparams1'");
1038        showMatchingProcedures(conn, "OP1");
1039
1040        
1041        // check execute via a Statement fails for use of OUT parameter
1042
if (! isDerbyNet) { // bug 5263
1043
try {
1044            executeProcedure(s, "CALL OP1(?, ?)");
1045            System.out.println("FAIL execute succeeded on OUT param with Statement");
1046        } catch (SQLException sqle) {
1047            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1048        }
1049        }
1050
1051        if (! isDerbyNet) { // bug 5276
1052
// check execute via a PreparedStatement fails for use of OUT parameter
1053
try {
1054            PreparedStatement ps = conn.prepareStatement("CALL OP1(?, ?)");
1055            System.out.println("FAIL prepare succeeded on OUT param with PreparedStatement");
1056        } catch (SQLException sqle) {
1057            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1058        }
1059        }
1060
1061        CallableStatement op = conn.prepareCall("CALL OP1(?, ?)");
1062
1063        op.registerOutParameter(1, Types.INTEGER);
1064        op.setInt(2, 7);
1065
1066        executeProcedure(op);
1067
1068        System.out.println("OP1 " + op.getInt(1) + " null ? " + op.wasNull());
1069
1070        op.close();
1071
1072        s.execute("create procedure OP2(INOUT a int, IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams2'");
1073        showMatchingProcedures(conn, "OP2");
1074
1075        // check execute via a Statement fails for use of INOUT parameter
1076
if (!isDerbyNet) { // bug 5263
1077
try {
1078            executeProcedure(s, "CALL OP2(?, ?)");
1079            System.out.println("FAIL execute succeeded on INOUT param with Statement");
1080        } catch (SQLException sqle) {
1081            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1082        }
1083        }
1084
1085        if (! isDerbyNet) { // bug 5276
1086

1087        // check execute via a PreparedStatement fails for use of INOUT parameter
1088
try {
1089            PreparedStatement ps = conn.prepareStatement("CALL OP2(?, ?)");
1090            System.out.println("FAIL prepare succeeded on INOUT param with PreparedStatement");
1091        } catch (SQLException sqle) {
1092            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1093        }
1094        }
1095
1096        op = conn.prepareCall("CALL OP2(?, ?)");
1097
1098        op.registerOutParameter(1, Types.INTEGER);
1099        op.setInt(1, 3);
1100        op.setInt(2, 7);
1101
1102        executeProcedure(op);
1103        System.out.println("OP2 " + op.getInt(1) + " null ? " + op.wasNull());
1104        op.close();
1105
1106        // INOUT & OUT procedures with variable length
1107
s.execute("create procedure OP3(INOUT a CHAR(10), IN b int) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams3'");
1108        showMatchingProcedures(conn, "OP3");
1109
1110        op = conn.prepareCall("CALL OP3(?, ?)");
1111
1112        op.registerOutParameter(1, Types.CHAR);
1113        op.setString(1, "dan");
1114        op.setInt(2, 8);
1115
1116        executeProcedure(op);
1117        System.out.println("OP3 >" + op.getString(1) + "< null ? " + op.wasNull());
1118        op.close();
1119
1120        // INOUT & OUT DECIMAL procedures with variable length
1121
s.execute("create procedure OP4(OUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams4'");
1122        showMatchingProcedures(conn, "OP4");
1123
1124        op = conn.prepareCall("CALL OP4(?, ?)");
1125
1126        op.registerOutParameter(1, Types.DECIMAL);
1127        op.setString(2, null);
1128        executeProcedure(op);
1129        System.out.println("OP4 null >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1130
1131        op.setString(2, "14");
1132        executeProcedure(op);
1133        System.out.println("OP4 14 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1134
1135        op.setString(2, "11.3");
1136        executeProcedure(op);
1137        System.out.println("OP4 11.3 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1138
1139        op.setString(2, "39.345");
1140        executeProcedure(op);
1141        System.out.println("OP4 39.345 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1142
1143        op.setString(2, "83");
1144        try {
1145            executeProcedure(op);
1146            System.out.println("FAIL - execution ok on out of range out parameter");
1147        } catch (SQLException sqle) {
1148            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1149        }
1150
1151        if (!isDerbyNet) {
1152        // Bug 5316 - JCC clears registration with clearParameters()
1153
op.clearParameters();
1154        try {
1155            // b not set
1156
executeProcedure(op);
1157            System.out.println("FAIL - b not set");
1158        } catch (SQLException sqle) {
1159            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1160        }
1161
1162        // try to set an OUT param
1163
try {
1164            op.setBigDecimal(1, new BigDecimal JavaDoc("22.32"));
1165            System.out.println("FAIL - set OUT param to value");
1166        } catch (SQLException sqle) {
1167            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1168        }
1169
1170        try {
1171            op.setBigDecimal(1, null);
1172            System.out.println("FAIL - set OUT param to null value");
1173        } catch (SQLException sqle) {
1174            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1175        }
1176        try {
1177            op.setNull(1, Types.DECIMAL);
1178            System.out.println("FAIL - set OUT param to null");
1179        } catch (SQLException sqle) {
1180            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1181        }
1182        }
1183
1184        // can we get an IN param?
1185
op.setString(2, "49.345");
1186        executeProcedure(op);
1187        System.out.println("OP4 49.345 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1188        try {
1189            System.out.println("FAIL OP4 GET 49.345 >" + op.getString(2) + "< null ? " + op.wasNull());
1190        } catch (SQLException sqle) {
1191            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1192        }
1193        op.close();
1194
1195        // check to see that a registration is required first for the out parameter.
1196
op = conn.prepareCall("CALL OP4(?, ?)");
1197        op.setString(2, "14");
1198        try {
1199            executeProcedure(op);
1200            System.out.println("FAIL - execute succeeded without registration of out parameter");
1201        } catch (SQLException sqle) {
1202            expectedException(sqle);
1203        }
1204        op.close();
1205
1206        s.execute("create procedure OP4INOUT(INOUT a DECIMAL(4,2), IN b VARCHAR(255)) parameter style java language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.inoutparams4'");
1207        showMatchingProcedures(conn, "OP4INOUT");
1208
1209        // bug 5264 - first execution fails with parameter not set.
1210

1211        op = conn.prepareCall("CALL OP4INOUT(?, ?)");
1212        op.registerOutParameter(1, Types.DECIMAL);
1213
1214        op.setString(2, null);
1215
1216
1217        op.setBigDecimal(1, null);
1218        executeProcedure(op);
1219        System.out.println("OP4INOUT null >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1220
1221
1222        op.setBigDecimal(1, new BigDecimal JavaDoc("99"));
1223        executeProcedure(op);
1224        System.out.println("OP4INOUT null(2) >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1225
1226        op.setString(2, "23.5");
1227        op.setBigDecimal(1, new BigDecimal JavaDoc("14"));
1228        executeProcedure(op);
1229        System.out.println("OP4INOUT 14+23.5 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1230        
1231        op.setString(2, "23.505");
1232        op.setBigDecimal(1, new BigDecimal JavaDoc("9"));
1233        executeProcedure(op);
1234        System.out.println("OP4INOUT 9+23.505 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1235
1236        if (! isDerbyNet) { // with the network server it retains its old value of 9
1237
// repeat execution. INOUT parameter now has the value 32.50
1238
executeProcedure(op);
1239        System.out.println("OP4INOUT 32.50+23.505 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1240
1241        } // end bug 5264
1242

1243        op.setString(2, "67.99");
1244        op.setBigDecimal(1, new BigDecimal JavaDoc("32.01"));
1245        try {
1246            executeProcedure(op);
1247            System.out.println("FAIL OP4INOUT 32.01+67.99 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1248        } catch (SQLException sqle) {
1249            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1250        }
1251
1252        op.setString(2, "1");
1253        op.setBigDecimal(1, new BigDecimal JavaDoc("102.33"));
1254        try {
1255            executeProcedure(op);
1256            System.out.println("FAIL OP4INOUT 1+102.33 >" + op.getBigDecimal(1) + "< null ? " + op.wasNull());
1257        } catch (SQLException sqle) {
1258            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1259        }
1260
1261        if (! isDerbyNet) {
1262        // now some checks to requirements for parameter setting.
1263
op.clearParameters();
1264        try {
1265            // a,b not set
1266
executeProcedure(op);
1267            System.out.println("FAIL - a,b not set");
1268        } catch (SQLException sqle) {
1269            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1270        }
1271
1272        op.setString(2, "2");
1273        try {
1274            // a not set
1275
executeProcedure(op);
1276            System.out.println("FAIL - a not set");
1277        } catch (SQLException sqle) {
1278            System.out.println("EXPECTED SQL Exception: " + sqle.getMessage());
1279        }
1280
1281        op.clearParameters();
1282        op.setBigDecimal(1, new BigDecimal JavaDoc("33"));
1283        try {
1284            // b not set
1285
executeProcedure(op);
1286            System.out.println("FAIL - b not set");
1287        } catch (SQLException sqle) {
1288            expectedException(sqle);
1289        }
1290
1291        } // end bug 5264
1292

1293
1294        op.close();
1295
1296        op = conn.prepareCall("CALL OP4INOUT(?, ?)");
1297        op.setString(2, "14");
1298        try {
1299            executeProcedure(op);
1300            System.out.println("FAIL - execute succeeded without registration of INOUT parameter");
1301        } catch (SQLException sqle) {
1302            expectedException(sqle);
1303        }
1304        op.close();
1305
1306        s.execute("DROP PROCEDURE OP1");
1307        s.execute("DROP PROCEDURE OP2");
1308        s.execute("DROP PROCEDURE OP3");
1309        s.execute("DROP PROCEDURE OP4");
1310        s.execute("DROP PROCEDURE OP4INOUT");
1311        s.close();
1312
1313    }
1314
1315    private static final String JavaDoc[] LITERALS =
1316    {"12" /* INTEGER */, "23.43e1" /* DOUBLE */, "176.3" /* DECIMAL */, "'12.34'" /* VARCHAR */};
1317    private static final String JavaDoc[] LIT_PROC_TYPES =
1318    {"SMALLINT", "INTEGER", "BIGINT", "REAL", "DOUBLE", "DECIMAL", "CHAR", "VARCHAR"};
1319    private static void testLiterals(Connection conn) throws SQLException {
1320
1321
1322        System.out.println("literals");
1323
1324        Statement s = conn.createStatement();
1325
1326        s.execute("CREATE PROCEDURE LITT.TY_SMALLINT(IN P1 SMALLINT, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1327        s.execute("CREATE PROCEDURE LITT.TY_INTEGER(IN P1 INTEGER, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1328        s.execute("CREATE PROCEDURE LITT.TY_BIGINT(IN P1 BIGINT, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1329        s.execute("CREATE PROCEDURE LITT.TY_REAL(IN P1 REAL, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1330        s.execute("CREATE PROCEDURE LITT.TY_DOUBLE(IN P1 DOUBLE, OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1331        s.execute("CREATE PROCEDURE LITT.TY_DECIMAL(IN P1 DECIMAL(5,2), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1332        s.execute("CREATE PROCEDURE LITT.TY_CHAR(IN P1 CHAR(10), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1333        s.execute("CREATE PROCEDURE LITT.TY_VARCHAR(IN P1 VARCHAR(10), OUT P2 VARCHAR(256)) NO SQL external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.literalTest' parameter style java language java");
1334
1335        showMatchingProcedures(conn, "TY_%");
1336
1337        for (int t = 0; t < LIT_PROC_TYPES.length; t++) {
1338
1339            String JavaDoc type = LIT_PROC_TYPES[t];
1340
1341            String JavaDoc sql = "CALL LITT.TY_" + type + " (null, ?)";
1342            System.out.print(sql);
1343
1344            try {
1345                CallableStatement cs = conn.prepareCall(sql);
1346                cs.registerOutParameter(1, Types.VARCHAR);
1347                cs.execute();
1348                String JavaDoc val = cs.getString(1);
1349                cs.close();
1350                System.out.println("=" + (val == null ? "<NULL>" : val));
1351            } catch (SQLException sqle) {
1352                System.out.println(" (" + sqle.getSQLState() + ") " + sqle.getMessage());
1353                // more code should be added to check on assignments
1354
// for now, commenting out the print of the stack, to prevent
1355
// failures due to differences between jvms.
1356
// sqle.printStackTrace(System.out);
1357
}
1358        }
1359
1360        for (int l = 0; l < LITERALS.length; l++) {
1361            String JavaDoc literal = LITERALS[l];
1362            for (int t = 0; t < LIT_PROC_TYPES.length; t++) {
1363
1364                String JavaDoc type = LIT_PROC_TYPES[t];
1365
1366                String JavaDoc sql = "CALL LITT.TY_" + type + " (" + literal + ", ?)";
1367                System.out.print(sql);
1368
1369                try {
1370                    CallableStatement cs = conn.prepareCall(sql);
1371                    cs.registerOutParameter(1, Types.VARCHAR);
1372                    cs.execute();
1373                    String JavaDoc val = cs.getString(1);
1374                    cs.close();
1375                    System.out.println("=" + (val == null ? "<NULL>" : val));
1376                } catch (SQLException sqle) {
1377                    System.out.println(" (" + sqle.getSQLState() + ") " + sqle.getMessage());
1378                    // code should be added to show the expected errors, now commenting
1379
// out the stack print to prevent false failures with different jvms
1380
//sqle.printStackTrace(System.out);
1381
}
1382            }
1383        }
1384    }
1385
1386
1387
1388    private static void expectedException(SQLException sqle) {
1389        String JavaDoc sqlState = sqle.getSQLState();
1390        if (sqlState == null) {
1391            sqlState = "<NULL>";
1392        }
1393        System.out.println("EXPECTED SQL Exception: (" + sqlState + ") " + sqle.getMessage());
1394    }
1395    
1396    private static void testSQLControl(Connection conn) throws SQLException {
1397
1398
1399        System.out.println("SQL Control");
1400
1401
1402        Statement s = conn.createStatement();
1403
1404        s.execute("CREATE SCHEMA SQLC");
1405        s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
1406        s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");
1407
1408        String JavaDoc[] control = {"", "NO SQL", "CONTAINS SQL", "READS SQL DATA", "MODIFIES SQL DATA"};
1409
1410        for (int i = 0; i < control.length; i++) {
1411
1412            StringBuffer JavaDoc cp = new StringBuffer JavaDoc(256);
1413            cp.append("CREATE PROCEDURE SQLC.SQLCONTROL1_");
1414            cp.append(i);
1415            cp.append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
1416            cp.append(control[i]);
1417            cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl'");
1418
1419            String JavaDoc cpsql = cp.toString();
1420            System.out.println(cpsql);
1421
1422            s.execute(cpsql);
1423            
1424            cp.setLength(0);
1425            cp.append("CREATE PROCEDURE SQLC.SQLCONTROL2_");
1426            cp.append(i);
1427            cp.append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
1428            cp.append(control[i]);
1429            cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl2'");
1430
1431            cpsql = cp.toString();
1432            System.out.println(cpsql);
1433
1434            s.execute(cpsql);
1435
1436            cp.setLength(0);
1437            cp.append("CREATE PROCEDURE SQLC.SQLCONTROL3_");
1438            cp.append(i);
1439            cp.append(" (OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128)) ");
1440            cp.append(control[i]);
1441            cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl3'");
1442
1443            cpsql = cp.toString();
1444            System.out.println(cpsql);
1445
1446            s.execute(cpsql);
1447
1448            cp.setLength(0);
1449            cp.append("CREATE PROCEDURE SQLC.SQLCONTROL4_");
1450            cp.append(i);
1451            cp.append(" (IN SQLC INTEGER, OUT E1 VARCHAR(128), OUT E2 VARCHAR(128), OUT E3 VARCHAR(128), OUT E4 VARCHAR(128), OUT E5 VARCHAR(128), OUT E6 VARCHAR(128), OUT E7 VARCHAR(128), OUT E8 VARCHAR(128)) ");
1452            cp.append(control[i]);
1453            cp.append(" PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.sqlControl4'");
1454
1455            cpsql = cp.toString();
1456            System.out.println(cpsql);
1457
1458            s.execute(cpsql);
1459        }
1460        showMatchingProcedures(conn, "SQLCONTROL1_%");
1461        showMatchingProcedures(conn, "SQLCONTROL2_%");
1462        showMatchingProcedures(conn, "SQLCONTROL3_%");
1463        showMatchingProcedures(conn, "SQLCONTROL4_%");
1464
1465        if (!conn.getAutoCommit())
1466            conn.commit();
1467
1468        for (int i = 0; i < control.length; i++) {
1469            String JavaDoc type = control[i];
1470            if (type.length() == 0)
1471                type = "DEFAULT (MODIFIES SQL DATA)";
1472
1473            System.out.println("** SQL ** " + type);
1474            for (int k = 1; k <=3; k++) {
1475                CallableStatement cs = conn.prepareCall("CALL SQLC.SQLCONTROL" + k + "_" + i + " (?, ?, ?, ?, ?, ?, ?)");
1476                for (int rop = 1; rop <=7 ; rop++) {
1477                    cs.registerOutParameter(rop, Types.VARCHAR);
1478                }
1479                cs.execute();
1480                for (int p = 1; p <= 7; p++) {
1481                    System.out.println(" " + cs.getString(p));
1482                }
1483                cs.close();
1484            }
1485            
1486        }
1487
1488        // test procedures that call others, e.g. to ensure that within a READS SQL DATA procedure, a MODIFIES SQL DATA cannot be called.
1489
// table was dropped by previous executions.
1490
s.execute("CREATE TABLE SQLC.SQLCONTROL_DML(I INT)");
1491        s.execute("INSERT INTO SQLC.SQLCONTROL_DML VALUES 4");
1492        for (int i = 0; i < control.length; i++) {
1493            String JavaDoc type = control[i];
1494            if (type.length() == 0)
1495                type = "DEFAULT (MODIFIES SQL DATA)";
1496
1497            System.out.println("CALL ** " + type);
1498            for (int t = 0; t < control.length; t++) {
1499
1500                String JavaDoc ttype = control[t];
1501                if (ttype.length() == 0)
1502                    ttype = "DEFAULT (MODIFIES SQL DATA)";
1503                System.out.println(" CALLLING " + ttype);
1504                CallableStatement cs = conn.prepareCall("CALL SQLC.SQLCONTROL4_" + i + " (?, ?, ?, ?, ?, ?, ?, ?, ?)");
1505                cs.setInt(1, t);
1506                for (int rop = 2; rop <=9 ; rop++) {
1507                    cs.registerOutParameter(rop, Types.VARCHAR);
1508                }
1509
1510                cs.execute();
1511                for (int p = 2; p <= 9; p++) {
1512                    String JavaDoc so = cs.getString(p);
1513                    if (so == null)
1514                        continue;
1515                    System.out.println(" " + so);
1516                }
1517                cs.close();
1518            }
1519        }
1520        // Make sure we throw proper error with network server
1521
// if params are not registered
1522
testBug5280(conn);
1523
1524        s.execute("DROP TABLE SQLC.SQLCONTROL_DML");
1525
1526        for (int i = 0; i < control.length; i++) {
1527            s.execute("DROP PROCEDURE SQLCONTROL1_" + i);
1528            s.execute("DROP PROCEDURE SQLCONTROL2_" + i);
1529            s.execute("DROP PROCEDURE SQLCONTROL4_" + i);
1530        }
1531        s.execute("DROP TABLE SQLC.SQLCONTROL_DDL");
1532        s.execute("SET SCHEMA APP");
1533        s.execute("DROP SCHEMA SQLC RESTRICT");
1534
1535        s.close();
1536    }
1537
1538    private static void showMatchingProcedures(Connection conn, String JavaDoc procedureName) throws SQLException {
1539        // Until cs defaults to hold cursor we need to turn autocommit off
1540
// while we do this because one metadata call will close the other's
1541
// cursor
1542
boolean saveAutoCommit = conn.getAutoCommit();
1543        conn.setAutoCommit(false);
1544        System.out.println("DEFINED PROCEDURES FOR " + procedureName);
1545        PreparedStatement ps = conn.prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE " +
1546                                " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");
1547
1548        ps.setString(1, procedureName);
1549
1550        ResultSet rs = ps.executeQuery();
1551        while (rs.next()) {
1552            System.out.println(" " + rs.getString(1) + "." + rs.getString(2) + " AS " + rs.getString(3));
1553        }
1554        rs.close();
1555
1556        System.out.println("DATABASE METATDATA PROCEDURES FOR " + procedureName);
1557        DatabaseMetaData dmd = conn.getMetaData();
1558
1559        rs = dmd.getProcedures(null, null, procedureName);
1560        // with jcc 2.1 for now this will fail on the second round,
1561
// because the resultset gets closed when we do getProcedureColumns.
1562
// thus, catch that gracefully...
1563
try {
1564        while (rs.next()) {
1565            String JavaDoc schema = rs.getString(2);
1566            String JavaDoc name = rs.getString(3);
1567            System.out.println(" " + schema + "." + name + " AS " + rs.getString(7) + " type " + TYPE(rs.getShort(8)));
1568            // get the column information.
1569
ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
1570            while (rsc.next()) {
1571                System.out.println(" " + PARAMTYPE(rsc.getShort(5)) + " " + rsc.getString(4) + " " + rsc.getString(7));
1572            }
1573            rsc.close();
1574        }
1575        rs.close();
1576        // restore previous autocommit mode
1577
conn.setAutoCommit(saveAutoCommit);
1578        } catch (SQLException sqle) {
1579            System.out.println("FAILure: ");
1580            sqle.printStackTrace();
1581        }
1582
1583        System.out.println("------------");
1584    }
1585
1586    static String JavaDoc TYPE(short type) {
1587        switch (type) {
1588        case DatabaseMetaData.procedureResultUnknown:
1589            return "procedureResultUnknown";
1590        case DatabaseMetaData.procedureNoResult:
1591            return "procedureNoResult";
1592        case DatabaseMetaData.procedureReturnsResult:
1593            return "procedureReturnsResult";
1594        default:
1595            return "??????";
1596        }
1597
1598    }
1599    static String JavaDoc PARAMTYPE(short type) {
1600        switch (type) {
1601        case DatabaseMetaData.procedureColumnUnknown: return "procedureColumnUnknown";
1602        case DatabaseMetaData.procedureColumnIn: return "procedureColumnIn";
1603        case DatabaseMetaData.procedureColumnInOut: return "procedureColumnInOut";
1604        case DatabaseMetaData.procedureColumnOut: return "procedureColumnOut";
1605        case DatabaseMetaData.procedureColumnReturn : return "procedureColumnReturn";
1606        case DatabaseMetaData.procedureColumnResult : return "procedureColumnResult";
1607        default: return "???";
1608        }
1609    }
1610
1611    private static void statementExceptionExpected(Statement s, String JavaDoc sql) {
1612        System.out.println(sql);
1613        try {
1614            s.execute(sql);
1615            System.out.println("FAIL - SQL expected to throw exception");
1616        } catch (SQLException sqle) {
1617            expectedException(sqle);
1618        }
1619    }
1620    private static void callExceptionExpected(Connection conn, String JavaDoc callSQL) throws SQLException {
1621        System.out.println(callSQL);
1622        try {
1623            CallableStatement cs = conn.prepareCall(callSQL);
1624            executeProcedure(cs);
1625            cs.close();
1626            System.out.println("FAIL - SQL expected to throw exception ");
1627        } catch (SQLException sqle) {
1628            expectedException(sqle);
1629        }
1630    }
1631
1632    /* ****
1633     * Beetle 5292 (for Network Server): Check for the return
1634     * of LOB columns in a result set.
1635     */

1636
1637    private static void testResultSetsWithLobs(Connection conn) {
1638
1639        Statement s = null;
1640
1641        // Create objects.
1642
try {
1643            s = conn.createStatement();
1644            
1645            // Clob.
1646
s.execute("create table lobCheckOne (c clob(30))");
1647            s.execute("insert into lobCheckOne values (cast " +
1648                      "('yayorsomething' as clob(30)))");
1649            s.execute("insert into lobCheckOne values (cast " +
1650                          "('yayorsomething2' as clob(30)))");
1651            s.execute("create procedure clobproc () parameter style java " +
1652                "language java external name " +
1653                      "'org.apache.derbyTesting.functionTests.util.ProcedureTest.clobselect' " +
1654                      "dynamic result sets 3 reads sql data");
1655            // Blob.
1656
s.execute("create table lobCheckTwo (b blob(30))");
1657            s.execute("insert into lobCheckTwo values (cast " + "(" +
1658                      TestUtil.stringToHexLiteral("101010001101") +
1659                      " as blob(30)))");
1660            s.execute("insert into lobCheckTwo values (cast " +
1661                      "(" +
1662                      TestUtil.stringToHexLiteral("101010001101") +
1663                      " as blob(30)))");
1664            s.execute("create procedure blobproc () parameter style java " +
1665                "language java external name " +
1666                "'org.apache.derbyTesting.functionTests.util.ProcedureTest.blobselect' " +
1667                "dynamic result sets 1 reads sql data");
1668
1669        } catch (SQLException e) {
1670            System.out.println("FAIL: Couldn't create required objects:");
1671            e.printStackTrace();
1672        }
1673
1674        // Run 5292 Tests.
1675
try {
1676
1677            // Clobs.
1678

1679            System.out.println("Stored Procedure w/ CLOB in result set.");
1680            CallableStatement cs = conn.prepareCall("CALL clobproc()");
1681            executeProcedure(cs);
1682            cs.close();
1683            
1684            // Blobs.
1685

1686            System.out.println("Stored Procedure w/ BLOB in result set.");
1687            cs = conn.prepareCall("CALL blobproc()");
1688            executeProcedure(cs);
1689            cs.close();
1690
1691        } catch (Exception JavaDoc e) {
1692            System.out.println("FAIL: Encountered exception:");
1693            e.printStackTrace();
1694        }
1695
1696        try {
1697        // Clean up.
1698
s.execute("drop table lobCheckOne");
1699            s.execute("drop table lobCheckTwo");
1700            s.execute("drop procedure clobproc");
1701            s.execute("drop procedure blobproc");
1702            s.close();
1703        } catch (Exception JavaDoc e) {
1704            System.out.println("FAIL: Cleanup for lob result sets test:");
1705            e.printStackTrace();
1706        }
1707
1708        return;
1709
1710    }
1711
1712    /**
1713     * Sets up and runs two tests with multiple ResultSets
1714     *
1715     * @param conn The Connection
1716     * @throws SQLException
1717     */

1718    private static void multipleRSTests(Connection conn) throws SQLException {
1719        //DerbyNet is known to fail this test
1720
if (TestUtil.isJCCFramework()) return;
1721        
1722        setHoldability(conn, JDBC30Translation.HOLD_CURSORS_OVER_COMMIT);
1723        int iso = conn.getTransactionIsolation();
1724        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1725        //Installing Procedure
1726
Statement stmt = conn.createStatement();
1727        ResultSet rs = stmt.executeQuery("select tablename from sys.systables " +
1728                "where tablename = 'AUTOCOMMITTABLE'");
1729        if (rs.next()) {
1730            rs.close();
1731            stmt.executeUpdate("delete from autoCommitTable");
1732        } else {
1733            rs.close();
1734            stmt.executeUpdate("create table autoCommitTable (num int)");
1735        }
1736        
1737        ResultSet mdrs = conn.getMetaData().getProcedures(
1738                null, null, "MULTIRESULT");
1739        if (mdrs != null || !mdrs.next()) {
1740            stmt.executeUpdate("create procedure multiResult(p1 int, " +
1741                    "p2 int) parameter style JAVA READS SQL DATA dynamic " +
1742                    "result sets 2 language java external name " +
1743                    "'org.apache.derbyTesting.functionTests." +
1744                    "util.ProcedureTest.multiResult'");
1745        }
1746        mdrs.close();
1747        multipleRSAutoCommit(conn);
1748        multipleRSNoCommit(conn);
1749        stmt.executeUpdate("drop procedure multiResult");
1750        stmt.executeUpdate("drop table autoCommitTable");
1751        stmt.close();
1752        conn.setTransactionIsolation(iso);
1753    }
1754    
1755    /**
1756     * Test to see that an auto commit occurs for multiple ResultSets if all
1757     * ResultSets but one are closed and the final ResultSet has completed.
1758     *
1759     * @param conn The Connection
1760     * @throws SQLException
1761     */

1762    private static void multipleRSAutoCommit(Connection conn) throws SQLException {
1763        System.out.print("MultipleRSAutoCommit: ");
1764        CallableStatement cs = conn.prepareCall("call multiResult(?, ?)");
1765        cs.setInt(1, 1);
1766        cs.setInt(2, 2);
1767        cs.execute();
1768        ResultSet rs = null;
1769        do {
1770            if (rs != null)
1771                rs.close();
1772            rs = cs.getResultSet();
1773            while (rs.next());
1774            
1775            if (rs.next()) {
1776                System.out.println("FAIL. Final call to ResultSet should return false.");
1777            }
1778        } while (getMoreResults(cs));
1779        
1780        if (!checkLocks()) {
1781            return;
1782        }
1783        
1784        System.out.println("PASS. ");
1785        
1786        if (rs != null)
1787            rs.close();
1788        cs.close();
1789    }
1790    
1791    /**
1792     * Used to insure that there is no auto-commit in the event that there is
1793     * more then one ResultSet open.
1794     *
1795     * @param conn The Connection
1796     * @throws SQLException
1797     */

1798    private static void multipleRSNoCommit(Connection conn) throws SQLException {
1799        System.out.print("MultipleRSNoCommit: ");
1800        CallableStatement cs = conn.prepareCall("call multiResult(?, ?)");
1801        cs.setInt(1, 1);
1802        cs.setInt(2, 2);
1803        cs.execute();
1804        ResultSet rs = null;
1805        do {
1806            rs = cs.getResultSet();
1807            while (rs.next());
1808            
1809            if (rs.next()) {
1810                System.out.println("FAIL. Final call to ResultSet should return false.");
1811            }
1812        } while (getMoreResults(cs));
1813        
1814        if (checkLocks()) {
1815            System.out.println("FAIL. Connection incorrectly auto-committed.");
1816        }
1817        
1818        System.out.println("PASS. ");
1819        
1820        if (rs != null)
1821            rs.close();
1822        cs.close();
1823    }
1824
1825    // DERBY-821: Test that the result set is not implicitly closed on
1826
// the server when EXCSQLSTT is used to open the result set.
1827
private static void testImplicitClose(Connection conn) throws SQLException {
1828        System.out.print("testImplicitClose(): ");
1829        final String JavaDoc proc =
1830            "org.apache.derbyTesting.functionTests.util.ProcedureTest." +
1831            "selectRows";
1832        boolean savedAutoCommit = conn.getAutoCommit();
1833        conn.setAutoCommit(false);
1834        Statement stmt = conn.createStatement();
1835        stmt.executeUpdate("create table derby821 (id int)");
1836        stmt.executeUpdate("insert into derby821 (id) values (1), (2)");
1837        stmt.execute("create procedure jira821 (name varchar(50)) " +
1838                     "parameter style java language java external name " +
1839                     "'" + proc + "' dynamic result sets 1 reads sql data");
1840        
1841        // Call setupStatementReuse which will make the server to reuse an existing statement.
1842
setupStatementReuse(conn);
1843        CallableStatement cs = conn.prepareCall("call jira821 (?)");
1844        cs.setString(1, "derby821");
1845        cs.execute();
1846        ResultSet rs = cs.getResultSet();
1847        rs.next();
1848        boolean passed = false;
1849        try {
1850            // We expect the result set to be open, so dropping the
1851
// table should fail.
1852
stmt.executeUpdate("drop table derby821");
1853            rs.next();//to fix DERBY-1320. Else the GC for ibm15 will clean up the ResultSet Object
1854
} catch (SQLException sqle) {
1855            if (sqle.getSQLState().equals("X0X95")) {
1856                System.out.println("PASSED");
1857                passed = true;
1858            } else {
1859                System.out.println("FAILED");
1860                throw sqle;
1861            }
1862        }
1863        if (!passed) {
1864            // Table was successfully dropped, hence the result set
1865
// must have been implicitly closed.
1866
System.out.println("FAILED (no exception thrown)");
1867        }
1868        conn.rollback();
1869        conn.setAutoCommit(savedAutoCommit);
1870    }
1871
1872    /**
1873     * This method is used to set up an environment which can be used to test
1874     * DERBY-1002. It creates statements and closes them to provoke the client
1875     * driver to re-use sections which in turn will make the network server to
1876     * re-use statements and result sets. It does not test anything by itself.
1877     * It just sets up an environment where the statements used in this test
1878     * will be re-used in later tests. It is called from methods
1879     * 'jira_491_492' and 'testImplicitClose'. When the re-use was not happening
1880     * correctly, 'jira_491_492' and 'testImplicitClose' were giving following
1881     * errors:
1882     *
1883     * 1. In the test for jira491, client expects a QRYDTA for the CNTQRY request.
1884     * Instead, it recieves a QRYNOPRM reply because server closes the query
1885     * wrongly.
1886     * 2. In testImplicitClose, the query is not supposed to be closed in case
1887     * of EXCSQLSTT commands. If re-use happens wrongly, server closes the query
1888     * for EXCSQLSTT commands too.
1889     *
1890     * @param conn Connection
1891     */

1892    private static void setupStatementReuse(Connection conn)
1893                                throws SQLException{
1894        
1895        Statement stmt = conn.createStatement();
1896        try {
1897            stmt.execute("drop table test_table_jira_1002");
1898        } catch (SQLException se) { }
1899
1900        try {
1901            stmt.execute("drop procedure test_proc_jira_1002");
1902        } catch (SQLException se) { }
1903
1904        stmt.execute("create table test_table_jira_1002(id int)");
1905        stmt.execute("insert into test_table_jira_1002 values(1) , (2)");
1906
1907        //create a procedure which returns a result set
1908
stmt.execute("create procedure test_proc_jira_1002(name varchar(50)) " +
1909                    "language java parameter style java external name " +
1910                    "'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'" +
1911                    "dynamic result sets 1");
1912        
1913        
1914        // Create a select statement to make currentDrdaRs.qryclsimp=CodePoint.QRYCLSIMP_YES
1915
Statement st_opnqry = conn.createStatement();
1916        ResultSet rs_opnqry = st_opnqry.executeQuery("SELECT * FROM TEST_TABLE_JIRA_1002");
1917        rs_opnqry.next();
1918        // Close st_opnqry so that cSt1 will reuse same DRDAStatement
1919
st_opnqry.close();
1920
1921        // Use up the next statement's result set to make currentDrdaRs.hasdata=false
1922
CallableStatement cSt1 = conn.prepareCall("call test_proc_jira_1002(?)");
1923        cSt1.setString(1, "test_table_jira_1002");
1924        cSt1.execute();
1925        ResultSet rs1 = cSt1.getResultSet();
1926        rs1.next();
1927        // Close cSt1 so that a statement created after a call to this method
1928
// will cause the server to use same DRDAStatement.
1929
cSt1.close();
1930        
1931    }
1932    
1933    /**
1934     * Checks to see if there is a lock on a table by attempting to modify the
1935     * same table. If the first connection was serializable then it will
1936     * continue to hold a lock and the second Connection will time out.
1937     *
1938     * @return false if the a lock could not be established, true if a lock
1939     * can be established.
1940     * @throws SQLException
1941     */

1942    private static boolean checkLocks() throws SQLException {
1943        Connection conn = null;
1944        try {
1945            conn = ij.startJBMS();
1946        } catch (Exception JavaDoc e) {
1947            System.out.println("FAIL. Unable to establish connection in checkLocks");
1948            return false;
1949        }
1950        Statement stmt = conn.createStatement();
1951        try {
1952            stmt.executeUpdate("update AutoCommitTable "
1953                    + "set num = 3 where num = 2");
1954            stmt.executeUpdate("update AutoCommitTable "
1955                    + "set num = 2 where num = 3");
1956        } catch (SQLException e) {
1957            if (e.getSQLState().equals(SQLState.LOCK_TIMEOUT)) {
1958                return false;
1959            } else {
1960                throw e;
1961            }
1962        }
1963        stmt.close();
1964        conn.close();
1965        return true;
1966    }
1967    
1968    /**
1969     * Sets the holdability of a Connection using reflection so it is
1970     * JDBC2.0 compatible.
1971     *
1972     * @param conn The Connection
1973     * @param hold The new holdability.
1974     * @throws SQLException
1975     */

1976    public static void setHoldability(Connection conn, int hold) throws SQLException {
1977        try {
1978            Object JavaDoc[] holdArray = {new Integer JavaDoc(hold)};
1979            Method sh = conn.getClass().getMethod("setHoldability", CONN_PARAM);
1980            sh.invoke(conn, holdArray);
1981        } catch (Exception JavaDoc e) {System.out.println("shouldn't get that error " + e.getMessage());}//for jdks prior to jdk14
1982
}
1983    
1984    /**
1985     * Uses reflection to call CallableStatement.getMoreResults(KEEP_CURRENT_RESULT)
1986     * for JDBC2.0 compatibilty
1987     * @param cs The Callable statement
1988     * @return boolean value indicating if there are more results
1989     * @throws SQLException
1990     */

1991    public static boolean getMoreResults(CallableStatement cs) throws SQLException {
1992        try {
1993            Object JavaDoc[] holdArray = {new Integer JavaDoc(JDBC30Translation.KEEP_CURRENT_RESULT)};
1994            Method sh = cs.getClass().getMethod("getMoreResults", CONN_PARAM);
1995            Boolean JavaDoc temp = (Boolean JavaDoc)sh.invoke(cs, holdArray);
1996            return temp.booleanValue();
1997        } catch (Exception JavaDoc e) {return cs.getMoreResults();}//for jdks prior to jdk14
1998
}
1999
2000    /**
2001     * clean up any objects not cleaned up by previous efforts
2002     */

2003    private static void cleanUp(Connection conn) throws SQLException {
2004        String JavaDoc[] testObjects = {
2005            "table t1", "procedure procdup", "schema s1 restrict",
2006            "schema s2 restrict", "procedure drs", "procedure drs2",
2007            "procedure litt.ty_smallint", "procedure litt.ty_integer", "procedure litt.ty_bigint",
2008            "procedure litt.ty_real", "procedure litt.ty_double", "procedure litt.ty_decimal",
2009            "procedure litt.ty_char", "procedure litt.ty_varchar",
2010            "table SQLC.SQLCONTROL_DDL", "table SQLCONTROL_DDL",
2011            "table SQLC.SQLCONTROL_DML",
2012             };
2013        Statement stmt = conn.createStatement();
2014        TestUtil.cleanUpTest(stmt, testObjects);
2015    }
2016
2017}
2018
Popular Tags