001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbc.TestDbMetaData
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.jdbc4;
023:
024: import java.sql.Connection;
025: import java.sql.DatabaseMetaData;
026: import java.sql.PreparedStatement;
027: import java.sql.ResultSet;
028: import java.sql.ResultSetMetaData;
029: import java.sql.SQLException;
030: import java.sql.Statement;
031:
032: import org.apache.derby.tools.ij;
033: import org.apache.derbyTesting.functionTests.util.SQLStateConstants;
034: import org.apache.derby.shared.common.reference.JDBC40Translation;
035:
036: /**
037: * Test of database metadata for new methods in JDBC 40.
038: */
039: public class TestDbMetaData {
040:
041: public static void main(String[] args) {
042: try {
043: // use the ij utility to read the property file and
044: // make the initial connection.
045: ij.getPropertyArg(args);
046:
047: Connection conn_main = ij.startJBMS();
048:
049: runTests(conn_main);
050: } catch (SQLException e) {
051: dumpSQLExceptions(e);
052: } catch (Throwable e) {
053: System.out.println("FAIL -- unexpected exception:");
054: e.printStackTrace(System.out);
055: }
056: }
057:
058: // Run all the tests.
059: private static void runTests(Connection con) throws Exception {
060: testDatabaseMetaDataMethods(con);
061: testStoredProcEscapeSyntax(con);
062: testAutoCommitFailure(con);
063: con.close();
064: }
065:
066: // Simply call each new metadata method and print the result.
067: private static void testDatabaseMetaDataMethods(Connection con)
068: throws Exception {
069: con.setAutoCommit(true); // make sure it is true
070: Statement s = con.createStatement();
071: DatabaseMetaData met = con.getMetaData();
072:
073: if (!met.supportsStoredFunctionsUsingCallSyntax()) {
074: System.out
075: .println("FAIL: supportsStoredFunctionsUsingCallSyntax() "
076: + "should return true");
077: }
078:
079: if (met.autoCommitFailureClosesAllResultSets()) {
080: System.out
081: .println("FAIL: autoCommitFailureClosesAllResultSets() "
082: + "should return false");
083: }
084:
085: checkEmptyRS(met.getClientInfoProperties());
086:
087: // Make sure the constants provided in JDBC40Translation is correct
088: System.out
089: .println(""
090: + (JDBC40Translation.FUNCTION_PARAMETER_UNKNOWN == DatabaseMetaData.functionColumnUnknown));
091: System.out
092: .println(""
093: + (JDBC40Translation.FUNCTION_PARAMETER_IN == DatabaseMetaData.functionColumnIn));
094: System.out
095: .println(""
096: + (JDBC40Translation.FUNCTION_PARAMETER_INOUT == DatabaseMetaData.functionColumnInOut));
097: System.out
098: .println(""
099: + (JDBC40Translation.FUNCTION_PARAMETER_OUT == DatabaseMetaData.functionColumnOut));
100: System.out
101: .println(""
102: + (JDBC40Translation.FUNCTION_RETURN == DatabaseMetaData.functionReturn));
103:
104: System.out
105: .println(""
106: + (JDBC40Translation.FUNCTION_NO_NULLS == DatabaseMetaData.functionNoNulls));
107: System.out
108: .println(""
109: + (JDBC40Translation.FUNCTION_NULLABLE == DatabaseMetaData.functionNullable));
110: System.out
111: .println(""
112: + (JDBC40Translation.FUNCTION_NULLABLE_UNKNOWN == DatabaseMetaData.functionNullableUnknown));
113:
114: // Since JDBC40Translation cannot be accessed in queries in
115: // metadata.properties, the query has to use
116: // DatabaseMetaData.procedureNullable. Hence it is necessary
117: // to verify that that value of
118: // DatabaseMetaData.functionNullable is the same.
119: System.out
120: .println(""
121: + (DatabaseMetaData.functionNullable == DatabaseMetaData.procedureNullable));
122:
123: // Create some functions in the default schema (app) to make
124: // the output from getFunctions() and getFunctionColumns
125: // more interesting
126: s
127: .execute("CREATE FUNCTION DUMMY1 ( X SMALLINT ) RETURNS SMALLINT "
128: + "PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL "
129: + "NAME 'java.some.func'");
130: s
131: .execute("CREATE FUNCTION DUMMY2 ( X INTEGER, Y SMALLINT ) RETURNS"
132: + " INTEGER PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA "
133: + "EXTERNAL NAME 'java.some.func'");
134: s
135: .execute("CREATE FUNCTION DUMMY3 ( X VARCHAR(16), Y INTEGER ) "
136: + "RETURNS VARCHAR(16) PARAMETER STYLE JAVA NO SQL LANGUAGE"
137: + " JAVA EXTERNAL NAME 'java.some.func'");
138: s
139: .execute("CREATE FUNCTION DUMMY4 ( X VARCHAR(128), Y INTEGER ) "
140: + "RETURNS INTEGER PARAMETER STYLE JAVA NO SQL LANGUAGE "
141: + "JAVA EXTERNAL NAME 'java.some.func'");
142:
143: // Any function in any schema in any catalog
144: dumpRS(met.getFunctions(null, null, null));
145: // Any function in any schema in "Dummy
146: // Catalog". Same as above since the catalog
147: // argument is ignored (is always null)
148: dumpRS(met.getFunctions("Dummy Catalog", null, null));
149: // Any function in a schema starting with "SYS"
150: dumpRS(met.getFunctions(null, "SYS%", null));
151: // All functions containing "GET" in any schema
152: // (and any catalog)
153: dumpRS(met.getFunctions(null, null, "%GET%"));
154: // Any function that belongs to NO schema and
155: // NO catalog (none)
156: checkEmptyRS(met.getFunctions("", "", null));
157:
158: // Test getFunctionColumns
159: // Dump parameters for all functions beigging with DUMMY
160: dumpRS(met.getFunctionColumns(null, null, "DUMMY%", null));
161:
162: // Dump return value for all DUMMY functions
163: dumpRS(met.getFunctionColumns(null, null, "DUMMY%", ""));
164:
165: // Test the new getSchemas() with no schema qualifiers
166: dumpRS(met.getSchemas(null, null));
167: // Test the new getSchemas() with a schema wildcard qualifier
168: dumpRS(met.getSchemas(null, "SYS%"));
169: // Test the new getSchemas() with an exact match
170: dumpRS(met.getSchemas(null, "APP"));
171: // Make sure that getSchemas() returns an empty result
172: // set when a schema is passed with no match
173: checkEmptyRS(met.getSchemas(null, "BLAH"));
174:
175: t_wrapper(met);
176:
177: s.close();
178: }
179:
180: /**
181: * <p>
182: * Return true if we're running under the embedded client.
183: * </p>
184: */
185: private static boolean usingEmbeddedClient() {
186: return "embedded".equals(System.getProperty("framework"));
187: }
188:
189: /**
190: * Test supportsStoredFunctionsUsingCallSyntax() by checking
191: * whether calling a stored procedure using the escape syntax
192: * succeeds.
193: *
194: * @param con <code>Connection</code> object used in test
195: * @exception SQLException if an unexpected database error occurs
196: */
197: private static void testStoredProcEscapeSyntax(Connection con)
198: throws SQLException {
199: con.setAutoCommit(false);
200: String call = "{CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)}";
201: Statement stmt = con.createStatement();
202:
203: boolean success;
204: try {
205: stmt.execute(call);
206: success = true;
207: } catch (SQLException e) {
208: success = false;
209: }
210:
211: DatabaseMetaData dmd = con.getMetaData();
212: boolean supported = dmd
213: .supportsStoredFunctionsUsingCallSyntax();
214: if (success != supported) {
215: System.out
216: .println("supportsStoredFunctionsUsingCallSyntax() "
217: + "returned "
218: + supported
219: + ", but executing "
220: + call
221: + (success ? " succeeded." : " failed."));
222: }
223: stmt.close();
224: con.rollback();
225: }
226:
227: /**
228: * Test autoCommitFailureClosesAllResultSets() by checking whether
229: * a failure in auto-commit mode will close all result sets, even
230: * holdable ones.
231: *
232: * @param con <code>Connection</code> object used in test
233: * @exception SQLException if an unexpected database error occurs
234: */
235: private static void testAutoCommitFailure(Connection con)
236: throws SQLException {
237: DatabaseMetaData dmd = con.getMetaData();
238: boolean shouldBeClosed = dmd
239: .autoCommitFailureClosesAllResultSets();
240:
241: con.setAutoCommit(true);
242:
243: Statement s1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
244: ResultSet.CONCUR_READ_ONLY,
245: ResultSet.HOLD_CURSORS_OVER_COMMIT);
246: ResultSet resultSet = s1.executeQuery("VALUES (1, 2), (3, 4)");
247:
248: Statement s2 = con.createStatement();
249: try {
250: String query = "SELECT dummy, nonexistent, phony FROM imaginarytable34521";
251: s2.execute(query);
252: System.out.println("\"" + query
253: + "\" is expected to fail, " + "but it didn't.");
254: } catch (SQLException e) {
255: // should fail, but we don't care how
256: }
257:
258: boolean isClosed = resultSet.isClosed();
259: if (isClosed != shouldBeClosed) {
260: System.out
261: .println("autoCommitFailureClosesAllResultSets() "
262: + "returned " + shouldBeClosed
263: + ", but ResultSet is "
264: + (isClosed ? "closed." : "not closed."));
265: }
266: resultSet.close();
267: s1.close();
268: s2.close();
269: }
270:
271: static private void dumpSQLExceptions(SQLException se) {
272: System.out.println("FAIL -- unexpected exception");
273: while (se != null) {
274: System.out.print("SQLSTATE(" + se.getSQLState() + "):");
275: se.printStackTrace(System.out);
276: se = se.getNextException();
277: }
278: }
279:
280: static void dumpRS(ResultSet s) throws SQLException {
281: ResultSetMetaData rsmd = s.getMetaData();
282:
283: // Get the number of columns in the result set
284: int numCols = rsmd.getColumnCount();
285:
286: if (numCols <= 0) {
287: System.out.println("(no columns!)");
288: return;
289: }
290:
291: // Display column headings
292: for (int i = 1; i <= numCols; i++) {
293: if (i > 1)
294: System.out.print(",");
295: System.out.print(rsmd.getColumnLabel(i));
296: }
297: System.out.println();
298:
299: // Display data, fetching until end of the result set
300: while (s.next()) {
301: // Loop through each column, getting the
302: // column data and displaying
303: for (int i = 1; i <= numCols; i++) {
304: if (i > 1)
305: System.out.print(",");
306: System.out.print(s.getString(i));
307: }
308: System.out.println();
309: }
310: s.close();
311: }
312:
313: /**
314: * Checks for a ResultSet with no rows.
315: *
316: */
317: static void checkEmptyRS(ResultSet rs) throws Exception {
318: boolean passed = false;
319:
320: try {
321: if (rs == null) {
322: throw new Exception(
323: "Metadata result set can not be null");
324: }
325: int numrows = 0;
326: while (rs.next())
327: numrows++;
328: // Zero rows is what we want.
329: if (numrows != 0) {
330: throw new Exception("Result set is not empty");
331: }
332: } catch (SQLException e) {
333: throw new Exception("Unexpected SQL Exception: "
334: + e.getMessage(), e);
335: }
336: }
337:
338: /**
339: * Tests the wrapper methods isWrapperFor and unwrap. There are two cases
340: * to be tested
341: * Case 1: isWrapperFor returns true and we call unwrap
342: * Case 2: isWrapperFor returns false and we call unwrap
343: *
344: * @param dmd The DatabaseMetaData object on which the wrapper methods are
345: * called
346: */
347:
348: static void t_wrapper(DatabaseMetaData dmd) {
349: //test for the case when isWrapper returns true
350: //Begin test for Case 1
351: Class<DatabaseMetaData> wrap_class = DatabaseMetaData.class;
352:
353: //The if succeeds and we call the unwrap method on the conn object
354: try {
355: if (dmd.isWrapperFor(wrap_class)) {
356: DatabaseMetaData dmd1 = (DatabaseMetaData) dmd
357: .unwrap(wrap_class);
358: } else {
359: System.out
360: .println("isWrapperFor wrongly returns false");
361: }
362: } catch (SQLException sqle) {
363: dumpSQLExceptions(sqle);
364: }
365:
366: //Begin the test for Case 2
367: //test for the case when isWrapper returns false
368: //using some class that will return false when
369: //passed to isWrapperFor
370:
371: Class<PreparedStatement> wrap_class1 = PreparedStatement.class;
372:
373: try {
374: //returning false is the correct behaviour in this case
375: //Generate a message if it returns true
376: if (dmd.isWrapperFor(wrap_class1)) {
377: System.out.println("isWrapperFor wrongly returns true");
378: } else {
379: PreparedStatement ps1 = (PreparedStatement) dmd
380: .unwrap(wrap_class1);
381: System.out
382: .println("unwrap does not throw the expected "
383: + "exception");
384: }
385: } catch (SQLException sqle) {
386: //calling unwrap in this case throws an
387: //SQLException ensure that the SQLException
388: //has the correct SQLState
389: if (!SQLStateConstants.UNABLE_TO_UNWRAP.equals(sqle
390: .getSQLState())) {
391: sqle.printStackTrace();
392: }
393: }
394: }
395: }
|