TestDbMetaData


1   /*
2   
3      Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc.TestDbMetaData
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.jdbc4;
23  
24  import java.sql.Connection  ;
25  import java.sql.DatabaseMetaData  ;
26  import java.sql.PreparedStatement  ;
27  import java.sql.ResultSet  ;
28  import java.sql.ResultSetMetaData  ;
29  import java.sql.SQLException  ;
30  import java.sql.Statement  ;
31  
32  import org.apache.derby.tools.ij;
33  import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
34  import org.apache.derby.shared.common.reference.JDBC40Translation;
35  
36  /**
37   * Test of database metadata for new methods in JDBC 40.
38   */
39  public class TestDbMetaData { 
40  
41      public static void main(String  [] args) {
42          try
43          {
44              // use the ij utility to read the property file and
45              // make the initial connection.
46              ij.getPropertyArg(args);
47          
48              Connection    conn_main = ij.startJBMS();
49  
50              runTests( conn_main );
51          }
52          catch (SQLException   e) {
53              dumpSQLExceptions(e);
54          }
55          catch (Throwable   e) {
56              System.out.println("FAIL -- unexpected exception:");
57              e.printStackTrace(System.out);
58          }
59      }
60  
61      // Run all the tests.
62      private static void runTests(Connection   con) throws Exception   {
63          testDatabaseMetaDataMethods(con);
64          testStoredProcEscapeSyntax(con);
65          testAutoCommitFailure(con);
66          con.close();
67      }
68  
69      // Simply call each new metadata method and print the result.
70      private static void testDatabaseMetaDataMethods(Connection   con)
71          throws Exception  
72      {
73          con.setAutoCommit(true); // make sure it is true
74          Statement   s = con.createStatement();
75          DatabaseMetaData   met = con.getMetaData();
76  
77          if (!met.supportsStoredFunctionsUsingCallSyntax()) {
78              System.out.println
79                  ("FAIL: supportsStoredFunctionsUsingCallSyntax() " +
80                   "should return true");
81          }
82  
83          if (met.autoCommitFailureClosesAllResultSets()) {
84              System.out.println
85                  ("FAIL: autoCommitFailureClosesAllResultSets() " +
86                   "should return false");
87          }
88  
89          checkEmptyRS(met.getClientInfoProperties());
90  
91          // Make sure the constants provided in JDBC40Translation is correct
92          System.out.println(""+(JDBC40Translation.FUNCTION_PARAMETER_UNKNOWN == 
93                                 DatabaseMetaData.functionColumnUnknown));
94          System.out.println(""+(JDBC40Translation.FUNCTION_PARAMETER_IN == 
95                                 DatabaseMetaData.functionColumnIn));
96          System.out.println(""+(JDBC40Translation.FUNCTION_PARAMETER_INOUT == 
97                                 DatabaseMetaData.functionColumnInOut));
98          System.out.println(""+(JDBC40Translation.FUNCTION_PARAMETER_OUT == 
99                                 DatabaseMetaData.functionColumnOut));
100         System.out.println(""+(JDBC40Translation.FUNCTION_RETURN == 
101                                DatabaseMetaData.functionReturn));
102     
103         System.out.println(""+(JDBC40Translation.FUNCTION_NO_NULLS ==
104                                DatabaseMetaData.functionNoNulls));
105         System.out.println(""+(JDBC40Translation.FUNCTION_NULLABLE ==
106                                DatabaseMetaData.functionNullable));
107         System.out.println(""+(JDBC40Translation.FUNCTION_NULLABLE_UNKNOWN ==
108                                DatabaseMetaData.functionNullableUnknown));
109 
110         // Since JDBC40Translation cannot be accessed in queries in
111         // metadata.properties, the query has to use
112         // DatabaseMetaData.procedureNullable. Hence it is necessary
113         // to verify that that value of
114         // DatabaseMetaData.functionNullable is the same.
115         System.out.println(""+(DatabaseMetaData.functionNullable == 
116                                DatabaseMetaData.procedureNullable));
117         
118         // Create some functions in the default schema (app) to make
119         // the output from getFunctions() and getFunctionColumns
120         // more interesting
121         s.execute("CREATE FUNCTION DUMMY1 ( X SMALLINT ) RETURNS SMALLINT "+
122                   "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL "+
123                   "NAME 'java.some.func'");
124         s.execute("CREATE FUNCTION DUMMY2 ( X INTEGER, Y SMALLINT ) RETURNS"+
125                   " INTEGER PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA "+
126                   "EXTERNAL NAME 'java.some.func'");
127         s.execute("CREATE FUNCTION DUMMY3 ( X VARCHAR(16), Y INTEGER ) "+
128                   "RETURNS VARCHAR(16) PARAMETER STYLE JAVA NO SQL LANGUAGE"+
129                   " JAVA EXTERNAL NAME 'java.some.func'");
130         s.execute("CREATE FUNCTION DUMMY4 ( X VARCHAR(128), Y INTEGER ) "+
131                   "RETURNS INTEGER PARAMETER STYLE JAVA NO SQL LANGUAGE "+
132                   "JAVA EXTERNAL NAME 'java.some.func'");
133 
134         // Any function in any schema in any catalog
135         dumpRS(met.getFunctions(null, null, null));
136         // Any function in any schema in "Dummy
137         // Catalog". Same as above since the catalog
138         // argument is ignored (is always null)
139         dumpRS(met.getFunctions("Dummy Catalog", null, null));
140         // Any function in a schema starting with "SYS"
141         dumpRS(met.getFunctions(null, "SYS%", null));
142         // All functions containing "GET" in any schema 
143         // (and any catalog)
144         dumpRS(met.getFunctions(null, null, "%GET%"));
145         // Any function that belongs to NO schema and 
146         // NO catalog (none)
147         checkEmptyRS(met.getFunctions("", "", null));
148 
149         // Test getFunctionColumns
150         // Dump parameters for all functions beigging with DUMMY
151         dumpRS(met.getFunctionColumns(null,null,"DUMMY%",null));
152         
153         // Dump return value for all DUMMY functions
154         dumpRS(met.getFunctionColumns(null,null,"DUMMY%",""));
155 
156         // Test the new getSchemas() with no schema qualifiers
157         dumpRS(met.getSchemas(null, null));
158         // Test the new getSchemas() with a schema wildcard qualifier
159         dumpRS(met.getSchemas(null, "SYS%"));
160         // Test the new getSchemas() with an exact match
161         dumpRS(met.getSchemas(null, "APP"));
162         // Make sure that getSchemas() returns an empty result
163         // set when a schema is passed with no match
164         checkEmptyRS(met.getSchemas(null, "BLAH"));
165         
166         t_wrapper(met);
167         
168         s.close();
169     }
170 
171     /**
172      * <p>
173      * Return true if we're running under the embedded client.
174      * </p>
175      */
176     private static  boolean usingEmbeddedClient() {
177         return "embedded".equals( System.getProperty( "framework" ) );
178     }
179     
180     /**
181      * Test supportsStoredFunctionsUsingCallSyntax() by checking
182      * whether calling a stored procedure using the escape syntax
183      * succeeds.
184      *
185      * @param con <code>Connection</code> object used in test
186      * @exception SQLException if an unexpected database error occurs
187      */
188     private static void testStoredProcEscapeSyntax(Connection   con)
189         throws SQLException  
190     {
191         con.setAutoCommit(false);
192         String   call = "{CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)}";
193         Statement   stmt = con.createStatement();
194 
195         boolean success;
196         try {
197             stmt.execute(call);
198             success = true;
199         } catch (SQLException   e) {
200             success = false;
201         }
202 
203         DatabaseMetaData   dmd = con.getMetaData();
204         boolean supported = dmd.supportsStoredFunctionsUsingCallSyntax();
205         if (success != supported) {
206             System.out.println("supportsStoredFunctionsUsingCallSyntax() " +
207                                "returned " + supported + ", but executing " +
208                                call + (success ? " succeeded." : " failed."));
209         }
210         stmt.close();
211         con.rollback();
212     }
213 
214     /**
215      * Test autoCommitFailureClosesAllResultSets() by checking whether
216      * a failure in auto-commit mode will close all result sets, even
217      * holdable ones.
218      *
219      * @param con <code>Connection</code> object used in test
220      * @exception SQLException if an unexpected database error occurs
221      */
222     private static void testAutoCommitFailure(Connection   con)
223         throws SQLException  
224     {
225         DatabaseMetaData   dmd = con.getMetaData();
226         boolean shouldBeClosed = dmd.autoCommitFailureClosesAllResultSets();
227 
228         con.setAutoCommit(true);
229 
230         Statement   s1 =
231             con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
232                                 ResultSet.CONCUR_READ_ONLY,
233                                 ResultSet.HOLD_CURSORS_OVER_COMMIT);
234         ResultSet   resultSet = s1.executeQuery("VALUES (1, 2), (3, 4)");
235 
236         Statement   s2 = con.createStatement();
237         try {
238             String   query =
239                 "SELECT dummy, nonexistent, phony FROM imaginarytable34521";
240             s2.execute(query);
241             System.out.println("\"" + query + "\" is expected to fail, " +
242                                "but it didn't.");
243         } catch (SQLException   e) {
244             // should fail, but we don't care how
245         }
246 
247         boolean isClosed = resultSet.isClosed();
248         if (isClosed != shouldBeClosed) {
249             System.out.println("autoCommitFailureClosesAllResultSets() " +
250                                "returned " + shouldBeClosed +
251                                ", but ResultSet is " +
252                                (isClosed ? "closed." : "not closed."));
253         }
254         resultSet.close();
255         s1.close();
256         s2.close();
257     }
258 
259     static private void dumpSQLExceptions (SQLException   se) {
260         System.out.println("FAIL -- unexpected exception");
261         while (se != null) {
262             System.out.print("SQLSTATE("+se.getSQLState()+"):");
263             se.printStackTrace(System.out);
264             se = se.getNextException();
265         }
266     }
267 
268     static void dumpRS(ResultSet   s) throws SQLException   {
269         ResultSetMetaData   rsmd = s.getMetaData ();
270 
271         // Get the number of columns in the result set
272         int numCols = rsmd.getColumnCount ();
273 
274         if (numCols <= 0) {
275             System.out.println("(no columns!)");
276             return;
277         }
278         
279         // Display column headings
280         for (int i=1; i<=numCols; i++) {
281             if (i > 1) System.out.print(",");
282             System.out.print(rsmd.getColumnLabel(i));
283         }
284         System.out.println();
285     
286         // Display data, fetching until end of the result set
287         while (s.next()) {
288             // Loop through each column, getting the
289             // column data and displaying
290             for (int i=1; i<=numCols; i++) {
291                 if (i > 1) System.out.print(",");
292                 System.out.print(s.getString(i));
293             }
294             System.out.println();
295         }
296         s.close();
297     }
298 
299     /**
300      * Checks for a ResultSet with no rows.
301      *
302      */
303     static void checkEmptyRS(ResultSet   rs) throws Exception  
304     {       
305         boolean passed = false;
306 
307         try {
308             if ( rs == null )
309             {
310                 throw new Exception  ("Metadata result set can not be null");
311             }
312             int numrows = 0;
313             while (rs.next())
314                 numrows++;
315             // Zero rows is what we want.
316             if (numrows != 0) {
317                 throw new Exception  ("Result set is not empty");
318             }
319         }
320         catch (SQLException   e)
321         {
322             throw new Exception  ("Unexpected SQL Exception: " + e.getMessage(), e);
323         }
324     }
325         
326         
327     /**
328      * Tests the wrapper methods isWrapperFor and unwrap. There are two cases
329      * to be tested
330      * Case 1: isWrapperFor returns true and we call unwrap
331      * Case 2: isWrapperFor returns false and we call unwrap
332      *
333      * @param dmd The DatabaseMetaData object on which the wrapper methods are 
334      *           called
335      */
336         
337     static void t_wrapper(DatabaseMetaData   dmd) {
338         //test for the case when isWrapper returns true
339         //Begin test for Case 1
340         Class  <DatabaseMetaData  > wrap_class = DatabaseMetaData  .class;
341         
342         //The if succeeds and we call the unwrap method on the conn object        
343         try {
344             if(dmd.isWrapperFor(wrap_class)) {
345                 DatabaseMetaData   dmd1 = 
346                         (DatabaseMetaData  )dmd.unwrap(wrap_class);
347             }
348             else {
349                 System.out.println("isWrapperFor wrongly returns false");
350             }
351         }
352         catch(SQLException   sqle) {
353             dumpSQLExceptions(sqle);
354         }
355         
356         //Begin the test for Case 2
357         //test for the case when isWrapper returns false
358         //using some class that will return false when 
359         //passed to isWrapperFor
360         
361         Class  <PreparedStatement  > wrap_class1 = PreparedStatement  .class;
362         
363         try {
364             //returning false is the correct behaviour in this case
365             //Generate a message if it returns true
366             if(dmd.isWrapperFor(wrap_class1)) {
367                 System.out.println("isWrapperFor wrongly returns true");
368             }
369             else {
370                 PreparedStatement   ps1 = (PreparedStatement  )
371                                            dmd.unwrap(wrap_class1);
372                 System.out.println("unwrap does not throw the expected " +
373                                    "exception");
374             }
375         }
376         catch (SQLException   sqle) {
377             //calling unwrap in this case throws an 
378             //SQLException ensure that the SQLException 
379             //has the correct SQLState
380             if(!SQLStateConstants.UNABLE_TO_UNWRAP.equals(sqle.getSQLState())) {
381                 sqle.printStackTrace();
382             }
383         }
384     }
385 }
386
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags