001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30
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 java.sql.CallableStatement;
025: import java.sql.Connection;
026: import java.sql.ParameterMetaData;
027: import java.sql.PreparedStatement;
028: import java.sql.ResultSet;
029: import java.sql.Statement;
030: import java.sql.SQLException;
031: import java.sql.Types;
032:
033: import java.math.BigDecimal;
034:
035: import org.apache.derby.tools.ij;
036: import org.apache.derbyTesting.functionTests.util.TestUtil;
037:
038: /**
039: * Test the new class ParameterMetaData in jdbc 30.
040: * Testing both callable and prepared statements meta data
041: *
042: * @author mamta
043: */
044:
045: public class parameterMetaDataJdbc30 {
046: private static boolean isDerbyNet;
047: private static String[] testObjects = { "TABLE T", "FUNCTION RDB",
048: "PROCEDURE DUMMYINT", "PROCEDURE DUMMY_NUMERIC_PROC" };
049:
050: public static void main(String[] args) {
051: Connection con = null;
052: Statement s;
053: CallableStatement cs;
054: PreparedStatement ps;
055: ParameterMetaData paramMetaData;
056:
057: System.out.println("Test parameterMetaDataJdbc30 starting");
058:
059: try {
060: // use the ij utility to read the property file and
061: // make the initial connection.
062: ij.getPropertyArg(args);
063: con = ij.startJBMS();
064: con.setAutoCommit(true); // make sure it is true
065: isDerbyNet = TestUtil.isNetFramework();
066:
067: s = con.createStatement();
068:
069: /* Create the table and do any other set-up */
070: TestUtil.cleanUpTest(s, testObjects);
071: setUpTest(s);
072:
073: s
074: .executeUpdate("create function RDB(P1 INT) RETURNS DECIMAL(10,2) language java external name 'org.apache.derbyTesting.functionTests.tests.lang.outparams30.returnsBigDecimal' parameter style java");
075:
076: //first testing a callable statement
077: s
078: .executeUpdate("create procedure dummyint(in a integer, in b integer, out c integer, inout d integer) language java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30.dummyint' parameter style java");
079: cs = con.prepareCall("CALL dummyint(?,?,?,?)");
080:
081: // parameters 1 and 2 are input only
082: cs.setInt(1, 1);
083: cs.setInt(2, 1);
084: //parameter 3 is output only
085: cs.registerOutParameter(3, Types.INTEGER);
086: // parameter 4 is input and output
087: Object x = new Integer(1);
088: cs.setObject(4, x, Types.INTEGER);
089: cs.registerOutParameter(4, Types.INTEGER);
090:
091: //verify the meta data for the parameters
092: paramMetaData = cs.getParameterMetaData();
093: System.out
094: .println("parameters count for callable statement is "
095: + paramMetaData.getParameterCount());
096: // TODO: Some of the OUT params are getting reported as IN_OUT for embedded.
097: // Network server reports it correctly.
098: dumpParameterMetaData(paramMetaData);
099: cs.execute();
100:
101: //bug 4450 - parameter meta data info for the return parameter was giving
102: //null pointer exception. In the past, we didn't need to keep the return
103: //parameter info for callable statement execution and hence we never
104: //generated the meta data for it. To fix the problem, at the parsing time,
105: //I set a flag if the call statement is of ? = form. If so, the first
106: //parameter is a return parameter and save it's meta data rather than
107: //discarding it.
108: System.out
109: .println("Bug 4450 - generate metadata for return parameter");
110: cs = con.prepareCall("? = call RDB(?)");
111: paramMetaData = cs.getParameterMetaData();
112: System.out.println("param count is: "
113: + paramMetaData.getParameterCount());
114: dumpParameterMetaData(paramMetaData);
115:
116: //next testing a prepared statement
117: ps = con
118: .prepareStatement("insert into t values(?, ?, ?, ?, ?)");
119: ps.setNull(1, java.sql.Types.CHAR);
120: ps.setInt(2, 1);
121: ps.setNull(3, java.sql.Types.INTEGER);
122: ps.setBigDecimal(4, new BigDecimal("1"));
123: ps.setNull(5, java.sql.Types.DATE);
124:
125: paramMetaData = ps.getParameterMetaData();
126: System.out
127: .println("parameters count for prepared statement is "
128: + paramMetaData.getParameterCount());
129: // JCC seems to report these parameters as MODE_UNKNOWN, where as Derby uses MODE_IN
130: // JCC behaviour with network server matches its behaviour with DB2
131: // getPrecision() returns 0 for CHAR/DATE/BIT types for Derby. JCC shows maxlen
132: dumpParameterMetaData(paramMetaData);
133: ps.execute();
134:
135: //bug 4533 - associated parameters should not be included in the parameter meta data list
136: //Following statement systab will generate 4 associated parameters for the 2
137: //user parameters. This results in total 6 parameters for the prepared statement
138: //internally. But we should only show 2 user visible parameters through
139: //getParameterMetaData().
140: System.out.println("Bug 4533 - hide associated parameters");
141: ps = con
142: .prepareStatement("select * from sys.systables where "
143: + " tablename like ? and tableID like ?");
144: ps.setString(1, "SYS%");
145: ps.setString(2, "8000001%");
146: paramMetaData = ps.getParameterMetaData();
147: System.out
148: .println("parameters count for prepared statement is "
149: + paramMetaData.getParameterCount());
150: dumpParameterMetaData(paramMetaData);
151: ps.execute();
152:
153: // DERBY-44 added support for SELECT ... WHERE column LIKE ? ESCAPE ?
154: // This test case tests
155: // a) that such a statement compiles, and
156: // b) that we get the correct error message if the escape
157: // sequence is an empty string (at one point this would
158: // lead to a StringIndexOutOfBoundsException)
159: System.out.println("variation 1, testing jira 44");
160: ps = con
161: .prepareStatement("select * from sys.systables where tablename like ? escape ?");
162: ps.setString(1, "SYS%");
163: ps.setString(2, "");
164: paramMetaData = ps.getParameterMetaData();
165: System.out
166: .println("parameters count for prepared statement is "
167: + paramMetaData.getParameterCount());
168: dumpParameterMetaData(paramMetaData);
169: try {
170: ResultSet rs = ps.executeQuery();
171: rs.next();
172: System.out
173: .println("Jira 44 failed (didn't get SQLSTATE 22019)");
174: rs.close();
175: } catch (SQLException e) {
176: if (!"22019".equals(e.getSQLState())) {
177: System.out.println("Jira 44 failed.");
178: e.printStackTrace(System.out);
179: }
180: }
181:
182: // the test no longer tests 4552, but kept as an interesting test scenario
183: // bug 4552 - no parameters would be returned for execute statement using
184: // System.out.println("Bug 4552 - no parameters would be returned for execute statement using");
185: // orig: ps = con.prepareStatement("execute statement systab using values('SYS%','8000001%')");
186: ps = con
187: .prepareStatement("select * from sys.systables where tablename like 'SYS%' and tableID like '8000001%'");
188:
189: paramMetaData = ps.getParameterMetaData();
190: System.out
191: .println("parameters count for prepared statement is "
192: + paramMetaData.getParameterCount());
193: dumpParameterMetaData(paramMetaData);
194: ps.execute();
195:
196: //Bug 4654 - Null Pointer exception while executuing a select with a
197: //where clause parameter of type 'TRUE' or 'FALSE' constants. The existing prior to
198: //exposing parameter metadata didn't need to fill in metadata information for where
199: //clause parameter in the example above.
200: // This no longer makes sense, for we cannot take BOOLEANs anymore.
201: // replace with a simple where 1 = ?. Which would take either 1 for true, or 0 for false
202: System.out
203: .println("Bug 4654 - fill in where clause parameter type info");
204: ps = con
205: .prepareStatement("select * from t where 1=? for update");
206:
207: paramMetaData = ps.getParameterMetaData();
208: System.out
209: .println("parameters count for prepared statement is "
210: + paramMetaData.getParameterCount());
211: dumpParameterMetaData(paramMetaData);
212: dumpParameterMetaDataNegative(paramMetaData);
213: //ps.setBoolean(1,true);
214: ps.setInt(1, 1);
215: ps.execute();
216:
217: System.out
218: .println("test: no parameter for the statement and then do getParameterMetaData()");
219: ps = con.prepareStatement("select * from t");
220: paramMetaData = ps.getParameterMetaData();
221: System.out
222: .println("parameters count for prepared statement is "
223: + paramMetaData.getParameterCount());
224: dumpParameterMetaData(paramMetaData);
225: ps.execute();
226:
227: cs.close();
228: ps.close();
229:
230: System.out
231: .println("test: the scale returned should be the one set by registerOutParameter");
232: s
233: .executeUpdate("create procedure dummy_numeric_Proc(out a NUMERIC(30,15), out b NUMERIC(30,15)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30.dummy_numeric_Proc'");
234: cs = con.prepareCall("CALL dummy_numeric_Proc(?,?)");
235: cs.registerOutParameter(1, Types.NUMERIC);
236: cs.registerOutParameter(2, Types.NUMERIC, 15);
237: cs.execute();
238: dumpParameterMetaData(cs.getParameterMetaData());
239:
240: cs.close();
241:
242: System.out
243: .println("Behaviour of meta data and out params after re-compile");
244:
245: cs = con.prepareCall("CALL dummyint(?,?,?,?)");
246: cs.registerOutParameter(3, Types.INTEGER);
247: cs.registerOutParameter(4, Types.INTEGER);
248: cs.setInt(1, 1);
249: cs.setInt(2, 1);
250: cs.setInt(4, 4);
251: dumpParameterMetaData(cs.getParameterMetaData());
252: cs.execute();
253: System.out.println("DUMMYINT alias returned "
254: + cs.getInt(4));
255:
256: s.executeUpdate("drop procedure dummyint");
257: s
258: .executeUpdate("create procedure dummyint(in a integer, in b integer, out c integer, inout d integer) language java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30.dummyint2' parameter style java");
259: cs.execute();
260: dumpParameterMetaData(cs.getParameterMetaData());
261: cs.setInt(4, 6);
262: // following is incorrect sequence, should execute first, then get
263: // but leaving it in as an additional negative test. see beetle 5886
264: System.out.println("DUMMYINT alias returned "
265: + cs.getInt(4));
266: cs.execute();
267: System.out.println("DUMMYINT alias returned "
268: + cs.getInt(4));
269:
270: cs.close();
271:
272: // temp disable for network server
273: if (!isDerbyNet) {
274: // Java procedure support
275: System.out
276: .println("ParameterMetaData for Java procedures with INTEGER parameters");
277: s
278: .execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30.dummyint'");
279: cs = con.prepareCall("CALL PMDI(?, ?, ?, ?)");
280: dumpParameterMetaData(cs.getParameterMetaData());
281: cs.close();
282: s.execute("DROP PROCEDURE PMDI");
283:
284: System.out
285: .println("ParameterMetaData for Java procedures with CHAR parameters");
286: s
287: .execute("CREATE PROCEDURE PMDC(IN pmdI_1 CHAR(10), IN pmdI_2 VARCHAR(25), INOUT pmdI_3 CHAR(19), OUT pmdI_4 VARCHAR(32)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30.dummyString'");
288: cs = con.prepareCall("CALL PMDC(?, ?, ?, ?)");
289: dumpParameterMetaData(cs.getParameterMetaData());
290: cs.close();
291: s.execute("DROP PROCEDURE PMDC");
292:
293: System.out
294: .println("ParameterMetaData for Java procedures with DECIMAL parameters");
295: s
296: .execute("CREATE PROCEDURE PMDD(IN pmdI_1 DECIMAL(5,3), IN pmdI_2 DECIMAL(4,2), INOUT pmdI_3 DECIMAL(9,0), OUT pmdI_4 DECIMAL(10,2)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30.dummyDecimal'");
297: cs = con.prepareCall("CALL PMDD(?, ?, ?, ?)");
298: dumpParameterMetaData(cs.getParameterMetaData());
299: cs.close();
300:
301: System.out
302: .println("ParameterMetaData for Java procedures with some literal parameters");
303: cs = con.prepareCall("CALL PMDD(32.4, ?, ?, ?)");
304: dumpParameterMetaData(cs.getParameterMetaData());
305: cs.close();
306: cs = con.prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
307: dumpParameterMetaData(cs.getParameterMetaData());
308: cs.close();
309: cs = con.prepareCall("CALL PMDD(?, 38.2, ?, ?)");
310: dumpParameterMetaData(cs.getParameterMetaData());
311: cs.close();
312: s.execute("DROP PROCEDURE PMDD");
313: }
314: s.close();
315: con = ij.startJBMS();
316: con.setAutoCommit(true); // make sure it is true
317: s = con.createStatement();
318: TestUtil.cleanUpTest(s, testObjects);
319: s.close();
320: con.close();
321: } catch (SQLException e) {
322: dumpSQLExceptions(e);
323: } catch (Throwable e) {
324: System.out.println("FAIL -- unexpected exception:");
325: e.printStackTrace(System.out);
326: }
327: System.out.println("Test parameterMetaDataJdbc30 finished");
328: }
329:
330: static void dumpParameterMetaData(ParameterMetaData paramMetaData)
331: throws SQLException {
332: int numParam = paramMetaData.getParameterCount();
333: for (int i = 1; i <= numParam; i++) {
334: try {
335: System.out.println("Parameter number : " + i);
336: System.out.println("parameter isNullable "
337: + parameterIsNullableInStringForm(paramMetaData
338: .isNullable(i)));
339: System.out.println("parameter isSigned "
340: + paramMetaData.isSigned(i));
341: System.out.println("parameter getPrecision "
342: + paramMetaData.getPrecision(i));
343: System.out.println("parameter getScale "
344: + paramMetaData.getScale(i));
345: System.out.println("parameter getParameterType "
346: + paramMetaData.getParameterType(i));
347: System.out.println("parameter getParameterTypeName "
348: + paramMetaData.getParameterTypeName(i));
349: System.out.println("parameter getParameterClassName "
350: + paramMetaData.getParameterClassName(i));
351: System.out.println("parameter getParameterMode "
352: + parameterModeInStringForm(paramMetaData
353: .getParameterMode(i)));
354: } catch (Throwable t) {
355: System.out.println(t.toString());
356: t.printStackTrace(System.out);
357: }
358: }
359: }
360:
361: //negative test
362: static void dumpParameterMetaDataNegative(
363: ParameterMetaData paramMetaData) throws SQLException {
364: int numParam = paramMetaData.getParameterCount();
365: try {
366: System.out.println("parameter isNullable "
367: + paramMetaData.isNullable(-1));
368: } catch (SQLException e) {
369: dumpExpectedSQLExceptions(e);
370: }
371: try {
372: System.out.println("parameter isNullable "
373: + paramMetaData.isNullable(0));
374: } catch (SQLException e) {
375: dumpExpectedSQLExceptions(e);
376: }
377: try {
378: System.out.println("parameter isNullable "
379: + paramMetaData.isNullable(numParam + 1));
380: } catch (SQLException e) {
381: dumpExpectedSQLExceptions(e);
382: }
383: }
384:
385: static private void dumpExpectedSQLExceptions(SQLException se) {
386: System.out.println("PASS -- expected exception");
387: while (se != null) {
388: System.out.println("SQLSTATE(" + se.getSQLState() + "): "
389: + "SQL Exception: " + se.getMessage());
390: se = se.getNextException();
391: }
392: }
393:
394: //print the parameter mode in human readable form
395: static String parameterModeInStringForm(int mode) {
396: if (mode == ParameterMetaData.parameterModeIn)
397: return ("PARAMETER_MODE_IN");
398: else if (mode == ParameterMetaData.parameterModeInOut)
399: return ("PARAMETER_MODE_IN_OUT");
400: else if (mode == ParameterMetaData.parameterModeOut)
401: return ("PARAMETER_MODE_OUT");
402: else if (mode == ParameterMetaData.parameterModeUnknown)
403: return ("PARAMETER_MODE_UNKNOWN");
404: else
405: return ("ERROR: donot recognize this parameter mode");
406: }
407:
408: //print the parameter isNullable value in human readable form
409: static String parameterIsNullableInStringForm(int nullabilityValue) {
410: if (nullabilityValue == ParameterMetaData.parameterNoNulls)
411: return ("PARAMETER_NO_NULLS");
412: else if (nullabilityValue == ParameterMetaData.parameterNullable)
413: return ("PARAMETER_NULLABLE");
414: else if (nullabilityValue == ParameterMetaData.parameterNullableUnknown)
415: return ("PARAMETER_NULLABLE_UNKNOWN");
416: else
417: return ("ERROR: donot recognize this parameter isNullable() value");
418: }
419:
420: //Set up the test by creating the table used by the rest of the test.
421: static void setUpTest(Statement s) throws SQLException {
422: /* Create a table */
423: s.execute("create table t ( " +
424: /* 1 */"c char(5), " +
425: /* 2 */"iNoNull int not null, " +
426: /* 3 */"i int, " +
427: /* 4 */"de decimal, " +
428: /* 5 */"d date)");
429:
430: }
431:
432: //A really simple method to test callable statement
433: public static void dummyint(int in_param, int in_param2,
434: int[] in_param3, int[] in_param4) throws SQLException {
435:
436: in_param4[0] = 11111;
437: }
438:
439: public static void dummyint2(int in_param, int in_param2,
440: int[] in_param3, int[] in_param4) throws SQLException {
441: in_param4[0] = 22222;
442: }
443:
444: public static void dummy_numeric_Proc(BigDecimal[] max_param,
445: BigDecimal[] min_param) throws SQLException {
446: // System.out.println("dummy_numeric_Proc -- all output parameters"); taking println out because it won't display in master under drda
447: }
448:
449: public static void dummyString(String in_param, String in_param2,
450: String[] in_param3, String[] in_param4) {
451: }
452:
453: public static void dummyDecimal(BigDecimal in_param,
454: BigDecimal in_param2, BigDecimal[] in_param3,
455: BigDecimal[] in_param4) {
456: }
457:
458: static private void dumpSQLExceptions(SQLException se) {
459: System.out.println("FAIL -- unexpected exception");
460: while (se != null) {
461: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
462: se.printStackTrace(System.out);
463: se = se.getNextException();
464: }
465: }
466: }
|