KickJava   Java API By Example, From Geeks To Geeks.

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


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