Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > parameterMetaDataJdbc30


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.parameterMetaDataJdbc30
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.jdbcapi;
23
24 import java.sql.CallableStatement ;
25 import java.sql.Connection ;
26 import java.sql.ParameterMetaData ;
27 import java.sql.PreparedStatement ;
28 import java.sql.ResultSet ;
29 import java.sql.Statement ;
30 import java.sql.SQLException ;
31 import java.sql.Types ;
32
33 import java.math.BigDecimal ;
34
35 import org.apache.derby.tools.ij;
36 import org.apache.derbyTesting.functionTests.util.TestUtil;
37
38 /**
39  * Test the new class ParameterMetaData in jdbc 30.
40  * Testing both callable and prepared statements meta data
41  *
42  * @author mamta
43  */
44
45
46 public class parameterMetaDataJdbc30 {
47     private static boolean isDerbyNet;
48     private static String [] testObjects = { "TABLE T", "FUNCTION RDB",
49                      "PROCEDURE DUMMYINT", "PROCEDURE DUMMY_NUMERIC_PROC"};
50     public static void main(String [] args) {
51         Connection con = null;
52         Statement s;
53         CallableStatement cs;
54         PreparedStatement ps;
55         ParameterMetaData paramMetaData;
56
57         System.out.println("Test parameterMetaDataJdbc30 starting");
58
59         try
60         {
61             // use the ij utility to read the property file and
62 // make the initial connection.
63 ij.getPropertyArg(args);
64             con = ij.startJBMS();
65             con.setAutoCommit(true); // make sure it is true
66 isDerbyNet = TestUtil.isNetFramework();
67
68             s = con.createStatement();
69
70             /* Create the table and do any other set-up */
71             TestUtil.cleanUpTest(s, testObjects);
72             setUpTest(s);
73
74       s.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");
75
76       //first testing a callable statement
77 s.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");
78       cs = con.prepareCall("CALL dummyint(?,?,?,?)");
79
80       // parameters 1 and 2 are input only
81 cs.setInt(1,1);
82       cs.setInt(2,1);
83       //parameter 3 is output only
84 cs.registerOutParameter(3,Types.INTEGER);
85       // parameter 4 is input and output
86 Object x = new Integer (1);
87       cs.setObject(4,x, Types.INTEGER);
88       cs.registerOutParameter(4,Types.INTEGER);
89
90       //verify the meta data for the parameters
91 paramMetaData = cs.getParameterMetaData();
92       System.out.println("parameters count for callable statement is " + paramMetaData.getParameterCount());
93       // TODO: Some of the OUT params are getting reported as IN_OUT for embedded.
94 // Network server reports it correctly.
95 dumpParameterMetaData(paramMetaData);
96       cs.execute();
97
98       //bug 4450 - parameter meta data info for the return parameter was giving
99 //null pointer exception. In the past, we didn't need to keep the return
100 //parameter info for callable statement execution and hence we never
101 //generated the meta data for it. To fix the problem, at the parsing time,
102 //I set a flag if the call statement is of ? = form. If so, the first
103 //parameter is a return parameter and save it's meta data rather than
104 //discarding it.
105 System.out.println("Bug 4450 - generate metadata for return parameter");
106       cs = con.prepareCall("? = call RDB(?)");
107       paramMetaData = cs.getParameterMetaData();
108       System.out.println("param count is: "+paramMetaData.getParameterCount());
109       dumpParameterMetaData(paramMetaData);
110
111       //next testing a prepared statement
112 ps = con.prepareStatement("insert into t values(?, ?, ?, ?, ?)");
113       ps.setNull(1, java.sql.Types.CHAR);
114       ps.setInt(2, 1);
115       ps.setNull(3, java.sql.Types.INTEGER);
116       ps.setBigDecimal(4,new BigDecimal ("1"));
117       ps.setNull(5, java.sql.Types.DATE);
118
119       paramMetaData = ps.getParameterMetaData();
120       System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
121       // JCC seems to report these parameters as MODE_UNKNOWN, where as Derby uses MODE_IN
122 // JCC behaviour with network server matches its behaviour with DB2
123 // getPrecision() returns 0 for CHAR/DATE/BIT types for Derby. JCC shows maxlen
124 dumpParameterMetaData(paramMetaData);
125       ps.execute();
126
127       //bug 4533 - associated parameters should not be included in the parameter meta data list
128 //Following statement systab will generate 4 associated parameters for the 2
129 //user parameters. This results in total 6 parameters for the prepared statement
130 //internally. But we should only show 2 user visible parameters through
131 //getParameterMetaData().
132 System.out.println("Bug 4533 - hide associated parameters");
133       ps = con.prepareStatement("select * from sys.systables where " +
134              " tablename like ? and tableID like ?");
135       ps.setString (1, "SYS%");
136       ps.setString (2, "8000001%");
137       paramMetaData = ps.getParameterMetaData();
138       System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
139       dumpParameterMetaData(paramMetaData);
140       ps.execute();
141
142       // DERBY-44 added support for SELECT ... WHERE column LIKE ? ESCAPE ?
143 // This test case tests
144 // a) that such a statement compiles, and
145 // b) that we get the correct error message if the escape
146 // sequence is an empty string (at one point this would
147 // lead to a StringIndexOutOfBoundsException)
148 System.out.println("variation 1, testing jira 44");
149       ps = con.prepareStatement("select * from sys.systables where tablename like ? escape ?");
150       ps.setString (1, "SYS%");
151       ps.setString (2, "");
152       paramMetaData = ps.getParameterMetaData();
153       System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
154       dumpParameterMetaData(paramMetaData);
155       try {
156           ResultSet rs = ps.executeQuery();
157           rs.next();
158           System.out.println("Jira 44 failed (didn't get SQLSTATE 22019)");
159           rs.close();
160       } catch (SQLException e) {
161           if (!"22019".equals(e.getSQLState())) {
162               System.out.println("Jira 44 failed.");
163               e.printStackTrace(System.out);
164           }
165       }
166
167       // the test no longer tests 4552, but kept as an interesting test scenario
168 // bug 4552 - no parameters would be returned for execute statement using
169 // System.out.println("Bug 4552 - no parameters would be returned for execute statement using");
170 // orig: ps = con.prepareStatement("execute statement systab using values('SYS%','8000001%')");
171 ps = con.prepareStatement("select * from sys.systables where tablename like 'SYS%' and tableID like '8000001%'");
172
173       paramMetaData = ps.getParameterMetaData();
174       System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
175       dumpParameterMetaData(paramMetaData);
176       ps.execute();
177
178       //Bug 4654 - Null Pointer exception while executuing a select with a
179 //where clause parameter of type 'TRUE' or 'FALSE' constants. The existing prior to
180 //exposing parameter metadata didn't need to fill in metadata information for where
181 //clause parameter in the example above.
182 // This no longer makes sense, for we cannot take BOOLEANs anymore.
183 // replace with a simple where 1 = ?. Which would take either 1 for true, or 0 for false
184 System.out.println("Bug 4654 - fill in where clause parameter type info");
185       ps = con.prepareStatement("select * from t where 1=? for update");
186
187       paramMetaData = ps.getParameterMetaData();
188       System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
189       dumpParameterMetaData(paramMetaData);
190       dumpParameterMetaDataNegative(paramMetaData);
191       //ps.setBoolean(1,true);
192 ps.setInt(1,1);
193       ps.execute();
194
195       System.out.println("test: no parameter for the statement and then do getParameterMetaData()");
196       ps = con.prepareStatement("select * from t");
197       paramMetaData = ps.getParameterMetaData();
198       System.out.println("parameters count for prepared statement is " + paramMetaData.getParameterCount());
199       dumpParameterMetaData(paramMetaData);
200       ps.execute();
201
202       cs.close();
203       ps.close();
204
205       System.out.println("test: the scale returned should be the one set by registerOutParameter");
206       s.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'");
207       cs = con.prepareCall("CALL dummy_numeric_Proc(?,?)");
208       cs.registerOutParameter(1, Types.NUMERIC);
209       cs.registerOutParameter(2, Types.NUMERIC,15);
210       cs.execute();
211       dumpParameterMetaData(cs.getParameterMetaData());
212
213       cs.close();
214
215        System.out.println("Behaviour of meta data and out params after re-compile");
216
217       cs = con.prepareCall("CALL dummyint(?,?,?,?)");
218       cs.registerOutParameter(3,Types.INTEGER);
219       cs.registerOutParameter(4,Types.INTEGER);
220       cs.setInt(1,1);
221       cs.setInt(2,1);
222       cs.setInt(4,4);
223       dumpParameterMetaData(cs.getParameterMetaData());
224       cs.execute();
225       System.out.println("DUMMYINT alias returned " + cs.getInt(4));
226
227       s.executeUpdate("drop procedure dummyint");
228       s.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");
229       cs.execute();
230       dumpParameterMetaData(cs.getParameterMetaData());
231       cs.setInt(4, 6);
232       // following is incorrect sequence, should execute first, then get
233 // but leaving it in as an additional negative test. see beetle 5886
234 System.out.println("DUMMYINT alias returned " + cs.getInt(4));
235       cs.execute();
236       System.out.println("DUMMYINT alias returned " + cs.getInt(4));
237
238       cs.close();
239
240       // temp disable for network server
241 if (!isDerbyNet) {
242       // Java procedure support
243 System.out.println("ParameterMetaData for Java procedures with INTEGER parameters");
244       s.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'");
245       cs = con.prepareCall("CALL PMDI(?, ?, ?, ?)");
246       dumpParameterMetaData(cs.getParameterMetaData());
247       cs.close();
248       s.execute("DROP PROCEDURE PMDI");
249
250       System.out.println("ParameterMetaData for Java procedures with CHAR parameters");
251       s.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'");
252       cs = con.prepareCall("CALL PMDC(?, ?, ?, ?)");
253       dumpParameterMetaData(cs.getParameterMetaData());
254       cs.close();
255       s.execute("DROP PROCEDURE PMDC");
256
257       System.out.println("ParameterMetaData for Java procedures with DECIMAL parameters");
258       s.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'");
259       cs = con.prepareCall("CALL PMDD(?, ?, ?, ?)");
260       dumpParameterMetaData(cs.getParameterMetaData());
261       cs.close();
262
263       System.out.println("ParameterMetaData for Java procedures with some literal parameters");
264       cs = con.prepareCall("CALL PMDD(32.4, ?, ?, ?)");
265       dumpParameterMetaData(cs.getParameterMetaData());
266       cs.close();
267       cs = con.prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
268       dumpParameterMetaData(cs.getParameterMetaData());
269       cs.close();
270       cs = con.prepareCall("CALL PMDD(?, 38.2, ?, ?)");
271       dumpParameterMetaData(cs.getParameterMetaData());
272       cs.close();
273       s.execute("DROP PROCEDURE PMDD");
274       }
275      s.close();
276      con = ij.startJBMS();
277      con.setAutoCommit(true); // make sure it is true
278 s = con.createStatement();
279      TestUtil.cleanUpTest(s, testObjects);
280      s.close();
281      con.close();
282      }
283      catch (SQLException e) {
284      dumpSQLExceptions(e);
285      }
286      catch (Throwable e) {
287         System.out.println("FAIL -- unexpected exception:");
288         e.printStackTrace(System.out);
289      }
290      System.out.println("Test parameterMetaDataJdbc30 finished");
291     }
292
293     static void dumpParameterMetaData(ParameterMetaData paramMetaData) throws SQLException {
294         int numParam = paramMetaData.getParameterCount();
295         for (int i=1; i<=numParam; i++) {
296             try {
297             System.out.println("Parameter number : " + i);
298             System.out.println("parameter isNullable " + parameterIsNullableInStringForm(paramMetaData.isNullable(i)));
299             System.out.println("parameter isSigned " + paramMetaData.isSigned(i));
300             System.out.println("parameter getPrecision " + paramMetaData.getPrecision(i));
301             System.out.println("parameter getScale " + paramMetaData.getScale(i));
302             System.out.println("parameter getParameterType " + paramMetaData.getParameterType(i));
303             System.out.println("parameter getParameterTypeName " + paramMetaData.getParameterTypeName(i));
304             System.out.println("parameter getParameterClassName " + paramMetaData.getParameterClassName(i));
305             System.out.println("parameter getParameterMode " + parameterModeInStringForm(paramMetaData.getParameterMode(i)));
306             } catch (Throwable t) {
307                 System.out.println(t.toString());
308                 t.printStackTrace(System.out);
309             }
310         }
311     }
312
313     //negative test
314 static void dumpParameterMetaDataNegative(ParameterMetaData paramMetaData) throws SQLException {
315         int numParam = paramMetaData.getParameterCount();
316         try {
317             System.out.println("parameter isNullable " + paramMetaData.isNullable(-1));
318         } catch (SQLException e) {
319             dumpExpectedSQLExceptions(e);
320         }
321         try {
322             System.out.println("parameter isNullable " + paramMetaData.isNullable(0));
323         } catch (SQLException e) {
324             dumpExpectedSQLExceptions(e);
325         }
326         try {
327             System.out.println("parameter isNullable " + paramMetaData.isNullable(numParam+1));
328         } catch (SQLException e) {
329             dumpExpectedSQLExceptions(e);
330         }
331     }
332
333     static private void dumpExpectedSQLExceptions (SQLException se) {
334         System.out.println("PASS -- expected exception");
335         while (se != null)
336         {
337             System.out.println("SQLSTATE("+se.getSQLState()+"): "+ "SQL Exception: " + se.getMessage());
338             se = se.getNextException();
339         }
340     }
341
342     //print the parameter mode in human readable form
343 static String parameterModeInStringForm(int mode){
344         if (mode == ParameterMetaData.parameterModeIn)
345                   return("PARAMETER_MODE_IN");
346         else if (mode == ParameterMetaData.parameterModeInOut )
347                   return("PARAMETER_MODE_IN_OUT");
348         else if (mode == ParameterMetaData.parameterModeOut)
349                   return("PARAMETER_MODE_OUT");
350         else if (mode == ParameterMetaData.parameterModeUnknown)
351                   return("PARAMETER_MODE_UNKNOWN");
352         else
353                   return("ERROR: donot recognize this parameter mode");
354   }
355
356     //print the parameter isNullable value in human readable form
357 static String parameterIsNullableInStringForm(int nullabilityValue){
358         if (nullabilityValue == ParameterMetaData.parameterNoNulls)
359                   return("PARAMETER_NO_NULLS");
360         else if (nullabilityValue == ParameterMetaData.parameterNullable)
361                   return("PARAMETER_NULLABLE");
362         else if (nullabilityValue == ParameterMetaData.parameterNullableUnknown)
363                   return("PARAMETER_NULLABLE_UNKNOWN");
364         else
365                   return("ERROR: donot recognize this parameter isNullable() value");
366   }
367
368     //Set up the test by creating the table used by the rest of the test.
369 static void setUpTest(Statement s)
370                     throws SQLException {
371         /* Create a table */
372         s.execute("create table t ( "+
373                   /* 1 */ "c char(5), "+
374                   /* 2 */ "iNoNull int not null, "+
375                   /* 3 */ "i int, "+
376                   /* 4 */ "de decimal, "+
377                   /* 5 */ "d date)");
378
379     }
380
381     //A really simple method to test callable statement
382 public static void dummyint (int in_param, int in_param2, int[] in_param3, int[] in_param4)
383                                        throws SQLException {
384
385         in_param4[0] = 11111;
386     }
387     public static void dummyint2 (int in_param, int in_param2, int[] in_param3, int[] in_param4)
388                                        throws SQLException {
389         in_param4[0] = 22222;
390     }
391     
392     public static void dummy_numeric_Proc (BigDecimal [] max_param,BigDecimal [] min_param)
393                                      throws SQLException {
394 // System.out.println("dummy_numeric_Proc -- all output parameters"); taking println out because it won't display in master under drda
395 }
396
397     public static void dummyString (String in_param, String in_param2, String [] in_param3, String [] in_param4) {
398     }
399     public static void dummyDecimal(BigDecimal in_param, BigDecimal in_param2, BigDecimal [] in_param3, BigDecimal [] in_param4) {
400     }
401     
402     static private void dumpSQLExceptions (SQLException se) {
403         System.out.println("FAIL -- unexpected exception");
404         while (se != null) {
405             System.out.print("SQLSTATE("+se.getSQLState()+"):");
406             se.printStackTrace(System.out);
407             se = se.getNextException();
408         }
409     }
410 }
411

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags