001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.metadataMultiConn
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.Connection;
025: import java.sql.DriverManager;
026: import java.sql.ResultSetMetaData;
027: import java.sql.ResultSet;
028: import java.sql.Statement;
029: import java.sql.DatabaseMetaData;
030: import java.sql.SQLException;
031:
032: import org.apache.derby.tools.ij;
033: import org.apache.derby.tools.JDBCDisplayUtil;
034: import java.io.*;
035: import java.sql.PreparedStatement;
036: import java.util.Properties;
037:
038: public class metadataMultiConn {
039:
040: public static Connection getConnection(String[] args,
041: boolean autoCommit) throws Exception {
042: // use the ij utility to read the property file and
043: // make the initial connection.
044: ij.getPropertyArg(args);
045: Connection conn = ij.startJBMS();
046: conn.setAutoCommit(autoCommit);
047: return conn;
048: }
049:
050: public static void main(String[] args) throws Exception {
051: System.out.println("Test metadataMultiConn starting");
052:
053: //Open 1st connection
054: Connection conn1 = getConnection(args, false);
055: metadataCalls(conn1);
056:
057: Connection conn2 = getConnection(args, false);
058:
059: metadataCalls(conn2);
060:
061: Connection conn3 = getConnection(args, false);
062: metadataCalls(conn3);
063:
064: conn1.commit();
065: conn2.commit();
066: checkConsistencyOfAllTables(conn3);
067:
068: System.out.println("Test metadataMultiConn finishes.");
069: }
070:
071: public static void metadataCalls(Connection conn) throws Exception {
072: System.out
073: .println("A new connection is doing metadata calls, but never commit...");
074:
075: DatabaseMetaData dmd = conn.getMetaData();
076: getTypeInfo(dmd, System.out);
077: getTables(dmd, System.out);
078: getColumnInfo(dmd, "%", System.out);
079: getPrimaryKeys(dmd, "%", System.out);
080: getExportedKeys(dmd, "%", System.out);
081:
082: }
083:
084: public static void getTypeInfo(DatabaseMetaData dmd, PrintStream out)
085: throws SQLException {
086: ResultSet rs = dmd.getTypeInfo();
087: out.println("Submitted getTypeInfo request");
088: while (rs.next()) {
089: // 1.TYPE_NAME String => Type name
090: String typeName = rs.getString(1);
091:
092: // 2.DATA_TYPE short => SQL data type from java.sql.Types
093: short dataType = rs.getShort(2);
094:
095: // 3.PRECISION int => maximum precision
096: int precision = rs.getInt(3);
097:
098: // 4.LITERAL_PREFIX String => prefix used to quote a literal
099: // (may be null)
100: String literalPrefix = rs.getString(4);
101:
102: // 5.LITERAL_SUFFIX String => suffix used to quote a literal
103: // (may be null)
104: String literalSuffix = rs.getString(5);
105:
106: // 6.CREATE_PARAMS String => parameters used in creating the type
107: // (may be null)
108: String createParams = rs.getString(6);
109:
110: // 7.NULLABLE short => can you use NULL for this type?
111: // typeNoNulls - does not allow NULL values
112: // typeNullable - allows NULL values
113: // typeNullableUnknown - nullability unknown
114: short nullable = rs.getShort(7);
115:
116: // 8.CASE_SENSITIVE boolean=> is it case sensitive?
117: boolean caseSensitive = rs.getBoolean(8);
118:
119: // 9.SEARCHABLE short => can you use "WHERE" based on this type:
120: // typePredNone - No support
121: // typePredChar - Only supported with WHERE .. LIKE
122: // typePredBasic - Supported except for WHERE .. LIKE
123: // typeSearchable - Supported for all WHERE ..
124: short searchable = rs.getShort(9);
125:
126: // 10.UNSIGNED_ATTRIBUTE boolean => is it unsigned?
127: boolean unsignedAttribute = rs.getBoolean(10);
128:
129: // 11.FIXED_PREC_SCALE boolean => can it be a money value?
130: boolean fixedPrecScale = rs.getBoolean(11);
131:
132: // 12.AUTO_INCREMENT boolean => can it be used for an
133: // auto-increment value?
134: boolean autoIncrement = rs.getBoolean(12);
135:
136: // 13.LOCAL_TYPE_NAME String => localized version of type name
137: // (may be null)
138: String localTypeName = rs.getString(13);
139:
140: // 14.MINIMUM_SCALE short => minimum scale supported
141: short minimumScale = rs.getShort(14);
142:
143: // 15.MAXIMUM_SCALE short => maximum scale supported
144: short maximumScale = rs.getShort(15);
145:
146: // 16.SQL_DATA_TYPE int => unused
147:
148: // 17.SQL_DATETIME_SUB int => unused
149:
150: // 18.NUM_PREC_RADIX int => usually 2 or 10
151:
152: //out.println(typeName);
153: }
154: rs.close();
155: }
156:
157: public static void getTables(DatabaseMetaData dmd, PrintStream out)
158: throws SQLException {
159: String types[] = new String[1];
160: types[0] = "TABLE";
161: ResultSet rs = dmd.getTables(null, null, null, types);
162: while (rs.next()) {
163: // 1.TABLE_CAT String => table catalog (may be null)
164: String tableCat = rs.getString(1);
165:
166: // 2.TABLE_SCHEM String => table schema (may be null)
167: String tableSchem = rs.getString(2);
168:
169: // 3.TABLE_NAME String => table name
170: String tableName = rs.getString(3);
171:
172: // 4.TABLE_TYPE String => table type.
173: // Typical types are "TABLE", "VIEW",
174: // "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY",
175: // "ALIAS", "SYNONYM".
176: String tableType = rs.getString(4);
177:
178: // 5.REMARKS String => explanatory comment on the table
179: String remarks = rs.getString(5);
180: }
181: rs.close();
182: }
183:
184: public static void getColumnInfo(DatabaseMetaData dmd,
185: String tablePattern, PrintStream out) throws SQLException {
186: out.println("Getting column info for " + tablePattern);
187: ResultSet rs = dmd.getColumns(null, null, tablePattern, "%");
188: while (rs.next()) {
189: // 1.TABLE_CAT String => table catalog (may be null)
190: String tableCat = rs.getString(1);
191:
192: // 2.TABLE_SCHEM String => table schema (may be null)
193: String tableSchem = rs.getString(2);
194:
195: // 3.TABLE_NAME String => table name
196: String tableName = rs.getString(3);
197:
198: // 4.COLUMN_NAME String => column name
199: String columnName = rs.getString(4);
200:
201: // 5.DATA_TYPE short => SQL type from java.sql.Types
202: short dataType = rs.getShort(5);
203:
204: // 6.TYPE_NAME String => Data source dependent type name
205: String typeName = rs.getString(6);
206:
207: // 7.COLUMN_SIZE int => column size. For char or date types
208: // this is the maximum number of characters, for numeric or
209: // decimal types this is precision.
210: int columnSize = rs.getInt(7);
211:
212: // 8.BUFFER_LENGTH is not used.
213:
214: // 9.DECIMAL_DIGITS int => the number of fractional digits
215: int decimalDigits = rs.getInt(9);
216:
217: // 10.NUM_PREC_RADIX int => Radix (typically either 10 or 2)
218: int numPrecRadix = rs.getInt(10);
219:
220: // 11.NULLABLE int => is NULL allowed?
221: // columnNoNulls - might not allow NULL values
222: // columnNullable - definitely allows NULL values
223: // columnNullableUnknown - nullability unknown
224: int nullable = rs.getInt(11);
225:
226: // 12.REMARKS String => comment describing column (may be null)
227: String remarks = rs.getString(12);
228:
229: // 13.COLUMN_DEF String => default value (may be null)
230: String columnDef = rs.getString(13);
231:
232: // 14.SQL_DATA_TYPE int => unused
233:
234: // 15.SQL_DATETIME_SUB int => unused
235:
236: // 16.CHAR_OCTET_LENGTH int => for char types the maximum
237: // number of bytes in the column
238: int charOctetLength = rs.getInt(16);
239:
240: // 17.ORDINAL_POSITION int => index of column in table
241: // (starting at 1)
242: //-int ordinalPosition = rs.getInt(17);
243:
244: // 18.IS_NULLABLE String => "NO" means column definitely
245: // does not allow NULL values; "YES" means the column might
246: // allow NULL values. An empty string means nobody knows.
247: //-String isNullable = rs.getString(18);
248:
249: // let's not print this, for it's so much stuff
250: //out.println(tableName + " " + columnName + " " + typeName);
251: }
252: rs.close();
253: }
254:
255: public static void getPrimaryKeys(DatabaseMetaData dmd,
256: String tablePattern, PrintStream out) throws SQLException {
257: ResultSet rs = dmd.getPrimaryKeys(null, null, tablePattern);
258: while (rs.next()) {
259: // 1.TABLE_CAT String => table catalog (may be null)
260: String tableCat = rs.getString(1);
261:
262: // 2.TABLE_SCHEM String => table schema (may be null)
263: String tableSchem = rs.getString(2);
264:
265: // 3.TABLE_NAME String => table name
266: String tableName = rs.getString(3);
267:
268: // 4.COLUMN_NAME String => column name
269: String columnName = rs.getString(4);
270:
271: // 5.KEY_SEQ short => sequence number within primary key
272: short keySeq = rs.getShort(5);
273:
274: // 6.PK_NAME String => primary key name (may be null)
275: String pkName = rs.getString(6);
276: }
277: rs.close();
278: }
279:
280: public static void getExportedKeys(DatabaseMetaData dmd,
281: String tablePattern, PrintStream out) throws SQLException {
282: ResultSet rs = dmd.getExportedKeys(null, null, tablePattern);
283: while (rs.next()) {
284: // 1.PKTABLE_CAT String => primary key table catalog (may be null)
285: String pkTableCat = rs.getString(1);
286:
287: // 2.PKTABLE_SCHEM String => primary key table schema (may be null)
288: String pkTableSchem = rs.getString(2);
289:
290: // 3.PKTABLE_NAME String => primary key table name
291: String pkTableName = rs.getString(3);
292:
293: // 4.PKCOLUMN_NAME String => primary key column name
294: String pkColumnName = rs.getString(4);
295:
296: // 5.FKTABLE_CAT String => foreign key table catalog
297: // (may be null) being exported (may be null)
298: String fkTableCat = rs.getString(5);
299:
300: // 6.FKTABLE_SCHEM String => foreign key table schema
301: // (may be null) being exported (may be null)
302: String fkTableSchem = rs.getString(6);
303:
304: // 7.FKTABLE_NAME String => foreign key table name being exported
305: String fkTableName = rs.getString(7);
306:
307: // 8.FKCOLUMN_NAME String => foreign key column name being exported
308: String fkColumnName = rs.getString(8);
309:
310: // 9.KEY_SEQ short => sequence number within foreign key
311: short keySeq = rs.getShort(9);
312:
313: // 10.UPDATE_RULE short => What happens to foreign key when
314: // primary is updated:
315: // importedNoAction - do not allow update of primary key if
316: // it has been imported
317: // importedKeyCascade - change imported key to agree
318: // with primary key update
319: // importedKeySetNull - change imported key to NULL if its
320: // primary key has been updated
321: // importedKeySetDefault - change imported key to default
322: // values if its primary key has
323: // been updated
324: // importedKeyRestrict - same as importedKeyNoAction
325: // (for ODBC 2.x compatibility)
326: short updateRule = rs.getShort(10);
327:
328: // 11.DELETE_RULE short => What happens to the foreign key
329: // when primary is deleted.
330: // importedKeyNoAction - do not allow delete of primary key
331: // if it has been imported
332: // importedKeyCascade - delete rows that import a deleted key
333: // importedKeySetNull - change imported key to NULL if
334: // its primary key has been deleted
335: // importedKeyRestrict - same as importedKeyNoAction
336: // (for ODBC 2.x compatibility)
337: // importedKeySetDefault - change imported key to default
338: // if its primary key has
339: // been deleted
340: short deleteRule = rs.getShort(11);
341:
342: // 12.FK_NAME String => foreign key name (may be null)
343: String fkName = rs.getString(12);
344:
345: // 13.PK_NAME String => primary key name (may be null)
346: String pkName = rs.getString(13);
347:
348: // 14.DEFERRABILITY short => can the evaluation of foreign key
349: // constraints be deferred until commit
350: // importedKeyInitiallyDeferred - see SQL92 for definition
351: // importedKeyInitiallyImmediate - see SQL92 for definition
352: // importedKeyNotDeferrable - see SQL92 for definition
353: short deferrability = rs.getShort(14);
354:
355: }
356: rs.close();
357: }
358:
359: /**
360: * Runs the consistency checker.
361: *
362: * @param conn a connection to the database.
363: *
364: * @exception SQLException if there is a database error.
365: */
366:
367: public static void checkConsistencyOfAllTables(Connection conn)
368: throws SQLException {
369:
370: //check consistency of all tables in the database
371: Statement s = conn.createStatement();
372: ResultSet rs = s
373: .executeQuery("SELECT schemaname, tablename, "
374: + "SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) "
375: + "FROM sys.sysschemas s, sys.systables t "
376: + "WHERE s.schemaid = t.schemaid");
377: boolean consistent = true;
378: boolean allconsistent = true;
379: while (rs.next()) {
380: consistent = rs.getBoolean(3);
381: if (!consistent) {
382: allconsistent = false;
383: System.out.println(rs.getString(1) + "."
384: + rs.getString(2) + " is not consistent.");
385: }
386: }
387: rs.close();
388: if (allconsistent)
389: System.out.println("All tables are consistent.");
390: s.close();
391: }
392: }
|