Java > Open Source Codes > com > internetcds > jdbc > tds > DatabaseMetaData


1 //
2 // Copyright 1998 CDS Networks, Inc., Medford Oregon
3 //
4 // All rights reserved.
5 //
6 // Redistribution and use in source and binary forms, with or without
7 // modification, are permitted provided that the following conditions are met:
8 // 1. Redistributions of source code must retain the above copyright
9 // notice, this list of conditions and the following disclaimer.
10 // 2. Redistributions in binary form must reproduce the above copyright
11 // notice, this list of conditions and the following disclaimer in the
12 // documentation and/or other materials provided with the distribution.
13 // 3. All advertising materials mentioning features or use of this software
14 // must display the following acknowledgement:
15 // This product includes software developed by CDS Networks, Inc.
16 // 4. The name of CDS Networks, Inc. may not be used to endorse or promote
17 // products derived from this software without specific prior
18 // written permission.
19 //
20 // THIS SOFTWARE IS PROVIDED BY CDS NETWORKS, INC. ``AS IS'' AND
21 // ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22 // IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23 // ARE DISCLAIMED. IN NO EVENT SHALL CDS NETWORKS, INC. BE LIABLE
24 // FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
25 // DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
26 // OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
27 // HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
28 // LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
29 // OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
30 // SUCH DAMAGE.
31 //
32
33
34 package com.internetcds.jdbc.tds;
35
36
37
38 import java.sql.*;
39
40
41 /**
42  * This class provides information about the database as a whole.
43  *
44  * <P>Many of the methods here return lists of information in ResultSets.
45  * You can use the normal ResultSet methods such as getString and getInt
46  * to retrieve the data from these ResultSets. If a given form of
47  * metadata is not available, these methods should throw a SQLException.
48  *
49  * <P>Some of these methods take arguments that are String patterns. These
50  * arguments all have names such as fooPattern. Within a pattern String, "%"
51  * means match any substring of 0 or more characters, and "_" means match
52  * any one character. Only metadata entries matching the search pattern
53  * are returned. If a search pattern argument is set to a null ref, it means
54  * that argument's criteria should be dropped from the search.
55  *
56  * <P>A SQLException will be thrown if a driver does not support a meta
57  * data method. In the case of methods that return a ResultSet,
58  * either a ResultSet (which may be empty) is returned or a
59  * SQLException is thrown.
60  *
61  * @author Craig Spannring
62  * @author The FreeTDS project
63  * @version $Id: DatabaseMetaData.java,v 1.1 2006/06/23 10:39:04 sinisa Exp $
64  *
65  */
66 public class DatabaseMetaData implements java.sql.DatabaseMetaData
67 {
68    public static final String cvsVersion = "$Id: DatabaseMetaData.java,v 1.1 2006/06/23 10:39:04 sinisa Exp $";
69
70    final boolean verbose = true;
71
72
73    /**
74     * PROCEDURE_TYPE - May return a result.
75     */
76    final int procedureResultUnknown = 0;
77    /**
78     * PROCEDURE_TYPE - Does not return a result.
79     */
80    final int procedureNoResult = 1;
81    /**
82     * PROCEDURE_TYPE - Returns a result.
83     */
84    final int procedureReturnsResult = 2;
85
86    /**
87     * COLUMN_TYPE - nobody knows.
88     */
89    final int procedureColumnUnknown = 0;
90
91    /**
92     * COLUMN_TYPE - IN parameter.
93     */
94    final int procedureColumnIn = 1;
95
96    /**
97     * COLUMN_TYPE - INOUT parameter.
98     */
99    final int procedureColumnInOut = 2;
100
101    /**
102     * COLUMN_TYPE - OUT parameter.
103     */
104    final int procedureColumnOut = 4;
105    /**
106     * COLUMN_TYPE - procedure return value.
107     */
108    final int procedureColumnReturn = 5;
109
110    /**
111     * COLUMN_TYPE - result column in ResultSet.
112     */
113    final int procedureColumnResult = 3;
114
115    /**
116     * TYPE NULLABLE - does not allow NULL values.
117     */
118    final int procedureNoNulls = 0;
119
120    /**
121     * TYPE NULLABLE - allows NULL values.
122     */
123    final int procedureNullable = 1;
124
125    /**
126     * TYPE NULLABLE - nullability unknown.
127     */
128    final int procedureNullableUnknown = 2;
129
130
131    /**
132     * COLUMN NULLABLE - might not allow NULL values.
133     */
134    final int columnNoNulls = 0;
135
136    /**
137     * COLUMN NULLABLE - definitely allows NULL values.
138     */
139    final int columnNullable = 1;
140
141    /**
142     * COLUMN NULLABLE - nullability unknown.
143     */
144    final int columnNullableUnknown = 2;
145
146    /**
147     * BEST ROW SCOPE - very temporary, while using row.
148     */
149    final int bestRowTemporary = 0;
150
151    /**
152     * BEST ROW SCOPE - valid for remainder of current transaction.
153     */
154    final int bestRowTransaction = 1;
155
156    /**
157     * BEST ROW SCOPE - valid for remainder of current session.
158     */
159    final int bestRowSession = 2;
160
161    /**
162     * BEST ROW PSEUDO_COLUMN - may or may not be pseudo column.
163     */
164    final int bestRowUnknown = 0;
165
166    /**
167     * BEST ROW PSEUDO_COLUMN - is NOT a pseudo column.
168     */
169    final int bestRowNotPseudo = 1;
170
171    /**
172     * BEST ROW PSEUDO_COLUMN - is a pseudo column.
173     */
174    final int bestRowPseudo = 2;
175
176    /**
177     * VERSION COLUMNS PSEUDO_COLUMN - may or may not be pseudo column.
178     */
179    final int versionColumnUnknown = 0;
180
181    /**
182     * VERSION COLUMNS PSEUDO_COLUMN - is NOT a pseudo column.
183     */
184    final int versionColumnNotPseudo = 1;
185
186    /**
187     * VERSION COLUMNS PSEUDO_COLUMN - is a pseudo column.
188     */
189    final int versionColumnPseudo = 2;
190
191    /**
192     * IMPORT KEY UPDATE_RULE and DELETE_RULE - for update, change
193     * imported key to agree with primary key update; for delete,
194     * delete rows that import a deleted key.
195     */
196    final int importedKeyCascade = 0;
197
198    /**
199     * IMPORT KEY UPDATE_RULE and DELETE_RULE - do not allow update or
200     * delete of primary key if it has been imported.
201     */
202    final int importedKeyRestrict = 1;
203
204    /**
205     * IMPORT KEY UPDATE_RULE and DELETE_RULE - change imported key to
206     * NULL if its primary key has been updated or deleted.
207     */
208    final int importedKeySetNull = 2;
209
210    /**
211     * IMPORT KEY UPDATE_RULE and DELETE_RULE - do not allow update or
212     * delete of primary key if it has been imported.
213     */
214    final int importedKeyNoAction = 3;
215
216    /**
217     * IMPORT KEY UPDATE_RULE and DELETE_RULE - change imported key to
218     * default values if its primary key has been updated or deleted.
219     */
220    final int importedKeySetDefault = 4;
221
222    /**
223     * IMPORT KEY DEFERRABILITY - see SQL92 for definition
224     */
225    final int importedKeyInitiallyDeferred = 5;
226
227    /**
228     * IMPORT KEY DEFERRABILITY - see SQL92 for definition
229     */
230    final int importedKeyInitiallyImmediate = 6;
231
232    /**
233     * IMPORT KEY DEFERRABILITY - see SQL92 for definition
234     */
235    final int importedKeyNotDeferrable = 7;
236
237    /**
238     * TYPE NULLABLE - does not allow NULL values.
239     */
240    final int typeNoNulls = 0;
241
242    /**
243     * TYPE NULLABLE - allows NULL values.
244     */
245    final int typeNullable = 1;
246
247    /**
248     * TYPE NULLABLE - nullability unknown.
249     */
250    final int typeNullableUnknown = 2;
251
252    /**
253     * TYPE INFO SEARCHABLE - No support.
254     */
255    final int typePredNone = 0;
256
257    /**
258     * TYPE INFO SEARCHABLE - Only supported with WHERE .. LIKE.
259     */
260    final int typePredChar = 1;
261
262    /**
263     * TYPE INFO SEARCHABLE - Supported except for WHERE .. LIKE.
264     */
265    final int typePredBasic = 2;
266
267    /**
268     * TYPE INFO SEARCHABLE - Supported for all WHERE ...
269     */
270    final int typeSearchable = 3;
271
272    /**
273     * INDEX INFO TYPE - this identifies table statistics that are
274     * returned in conjuction with a table's index descriptions
275     */
276    final short tableIndexStatistic = 0;
277
278    /**
279     * INDEX INFO TYPE - this identifies a clustered index
280     */
281    final short tableIndexClustered = 1;
282
283    /**
284     * INDEX INFO TYPE - this identifies a hashed index
285     */
286    final short tableIndexHashed = 2;
287
288    /**
289     * INDEX INFO TYPE - this identifies some other form of index
290     */
291    final short tableIndexOther = 3;
292
293
294    //
295 // now for the internal data needed by this implemention.
296 //
297 Tds tds;
298
299
300
301
302    java.sql.Connection connection;
303
304    
305    private void debugPrintln(String s)
306    {
307       if (verbose)
308       {
309          System.out.println(s);
310       }
311    }
312
313    private void debugPrint(String s)
314    {
315       if (verbose)
316       {
317          System.out.print(s);
318       }
319    }
320
321
322    private void NotImplemented() throws SQLException
323    {
324       try
325       {
326          throw new SQLException("Not implemented");
327       }
328       catch (SQLException e)
329       {
330          e.printStackTrace();
331       }
332       throw new SQLException("Not implemented");
333    }
334
335
336    public DatabaseMetaData(
337       Object connection_,
338       Tds tds_)
339    {
340       connection = (java.sql.Connection )connection_;
341       tds = tds_;
342    }
343
344
345    //----------------------------------------------------------------------
346 // First, a variety of minor information about the target database.
347
348    /**
349     * Can all the procedures returned by getProcedures be called by the
350     * current user?
351     *
352     * @return true if so
353     * @exception SQLException if a database-access error occurs.
354     */
355    public boolean allProceduresAreCallable() throws SQLException
356    {
357       // XXX Need to check for Sybase
358
359       return true; // per "Programming ODBC for SQLServer" Appendix A
360 }
361
362
363    /**
364     * Can all the tables returned by getTable be SELECTed by the
365     * current user?
366     *
367     * @return true if so
368     * @exception SQLException if a database-access error occurs.
369     */
370    public boolean allTablesAreSelectable() throws SQLException
371    {
372       // XXX Need to check for Sybase
373
374       // XXX This is dependent on the way we are implementing getTables()
375 // it may change in the future.
376 return false;
377    }
378
379
380    /**
381     * Does a data definition statement within a transaction force the
382     * transaction to commit?
383     *
384     * @return true if so
385     * @exception SQLException if a database-access error occurs.
386     */
387    public boolean dataDefinitionCausesTransactionCommit()
388       throws SQLException
389    {
390       NotImplemented(); return false;
391    }
392
393
394    /**
395     * Is a data definition statement within a transaction ignored?
396     *
397     * @return true if so
398     * @exception SQLException if a database-access error occurs.
399     */
400    public boolean dataDefinitionIgnoredInTransactions()
401       throws SQLException
402    {
403       NotImplemented(); return false;
404    }
405
406
407
408    /**
409     * Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
410     * blobs?
411     *
412     * @return true if so
413     * @exception SQLException if a database-access error occurs.
414     */
415    public boolean doesMaxRowSizeIncludeBlobs() throws SQLException
416    {
417       return false;
418    }
419
420
421
422    /**
423     * Get a description of a table's optimal set of columns that
424     * uniquely identifies a row. They are ordered by SCOPE.
425     *
426     * <P>Each column description has the following columns:
427     * <OL>
428     * <LI><B>SCOPE</B> short => actual scope of result
429     * <UL>
430     * <LI> bestRowTemporary - very temporary, while using row
431     * <LI> bestRowTransaction - valid for remainder of current transaction
432     * <LI> bestRowSession - valid for remainder of current session
433     * </UL>
434     * <LI><B>COLUMN_NAME</B> String => column name
435     * <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
436     * <LI><B>TYPE_NAME</B> String => Data source dependent type name
437     * <LI><B>COLUMN_SIZE</B> int => precision
438     * <LI><B>BUFFER_LENGTH</B> int => not used
439     * <LI><B>DECIMAL_DIGITS</B> short => scale
440     * <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
441     * like an Oracle ROWID
442     * <UL>
443     * <LI> bestRowUnknown - may or may not be pseudo column
444     * <LI> bestRowNotPseudo - is NOT a pseudo column
445     * <LI> bestRowPseudo - is a pseudo column
446     * </UL>
447     * </OL>
448     *
449     * @param catalog a catalog name; "" retrieves those without a
450     * catalog; null means drop catalog name from the selection criteria
451     * @param schema a schema name; "" retrieves those without a schema
452     * @param table a table name
453     * @param scope the scope of interest; use same values as SCOPE
454     * @param nullable include columns that are nullable?
455     * @return ResultSet - each row is a column description
456     * @exception SQLException if a database-access error occurs.
457     */
458    public java.sql.ResultSet getBestRowIdentifier(
459       String catalog,
460       String schema,
461       String table,
462       int scope,
463       boolean nullable)
464       throws SQLException
465    {
466       debugPrintln("Inside getBestRowIdentifier with catalog=|" + catalog
467                    + "|, schema=|" + schema + "|, table=|" + table +"|, "
468                    + " scope=" + scope + ", nullable=" + nullable);
469
470       NotImplemented(); return null;
471    }
472
473
474    /**
475     * Get the catalog names available in this database. The results
476     * are ordered by catalog name.
477     *
478     * <P>The catalog column is:
479     * <OL>
480     * <LI><B>TABLE_CAT</B> String => catalog name
481     * </OL>
482     *
483     * @return ResultSet - each row has a single String column that is a
484     * catalog name
485     * @exception SQLException if a database-access error occurs.
486     */
487    public java.sql.ResultSet getCatalogs()
488       throws SQLException
489    {
490       // XXX We should really clean up all these temporary tables.
491 String tmpName = "#t#" + UniqueId.getUniqueId();
492       final String sql =
493          " create table " + tmpName + " " +
494          " ( " +
495          " q char(30) not null, " +
496          " o char(30) null, " +
497          " n char(30) null, " +
498          " t char(30) null, " +
499          " r varchar(255) null " +
500          " ) " +
501          " " +
502          " insert into " + tmpName + " EXEC sp_tables ' ', ' ', '%', null " +
503          " " +
504          " select q from " + tmpName + " " +
505          "";
506       java.sql.Statement stmt = connection.createStatement();
507       java.sql.ResultSet rs;
508
509
510       if (stmt.execute(sql))
511       {
512          throw new SQLException("Internal error. Confused");
513       }
514
515
516       // Eat the data returned by the 'create table'
517 if (null != (rs = stmt.getResultSet()))
518       {
519          throw new SQLException("Internal error. Confused");
520       }
521
522       // Eat the data returned by the 'insert'
523 if (null != (rs = stmt.getResultSet()))
524       {
525          // RMK 2000-06-11: test t0051 gets the result set here.
526
527          // XXX we really need to figure out what the protocol is doing here.
528 // It appears that sometimes it returns an immediate result set
529 //and sometimes it doesn't.
530
531          return rs;
532       }
533
534       // now get the result set
535 if (null == (rs = stmt.getResultSet()))
536       {
537          throw new SQLException("Internal error. Confused");
538       }
539       return rs;
540    }
541
542
543
544    /**
545     * What's the separator between catalog and table name?
546     *
547     * @return the separator string
548     * @exception SQLException if a database-access error occurs.
549     */
550    public String getCatalogSeparator() throws SQLException
551    {
552       return ".";
553    }
554
555
556
557    /**
558     * What's the database vendor's preferred term for "catalog"?
559     *
560     * @return the vendor term
561     * @exception SQLException if a database-access error occurs.
562     */
563    public String getCatalogTerm() throws SQLException
564    {
565
566       return "database";
567    }
568
569
570
571    /**
572     * Get a description of the access rights for a table's columns.
573     *
574     * <P>Only privileges matching the column name criteria are
575     * returned. They are ordered by COLUMN_NAME and PRIVILEGE.
576     *
577     * <P>Each privilige description has the following columns:
578     * <OL>
579     * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
580     * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
581     * <LI><B>TABLE_NAME</B> String => table name
582     * <LI><B>COLUMN_NAME</B> String => column name
583     * <LI><B>GRANTOR</B> => grantor of access (may be null)
584     * <LI><B>GRANTEE</B> String => grantee of access
585     * <LI><B>PRIVILEGE</B> String => name of access (SELECT,
586     * INSERT, UPDATE, REFRENCES, ...)
587     * <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
588     * to grant to others; "NO" if not; null if unknown
589     * </OL>
590     *
591     * @param catalog a catalog name; "" retrieves those without a
592     * catalog; null means drop catalog name from the selection criteria
593     * @param schema a schema name; "" retrieves those without a schema
594     * @param table a table name
595     * @param columnNamePattern a column name pattern
596     * @return ResultSet - each row is a column privilege description
597     * @exception SQLException if a database-access error occurs.
598     * @see #getSearchStringEscape
599     */
600    public java.sql.ResultSet getColumnPrivileges(String catalog, String schema,
601                                                  String table, String columnNamePattern)
602       throws SQLException
603    {
604       NotImplemented(); return null;
605    }
606
607
608    /**
609     * Get a description of table columns available in a catalog.
610     *
611     * <P>Only column descriptions matching the catalog, schema, table
612     * and column name criteria are returned. They are ordered by
613     * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
614     *
615     * <P>Each column description has the following columns:
616     * <OL>
617     * <LI><B>TABLE_CAT</B> String => table catalog (may be null)
618     * <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
619     * <LI><B>TABLE_NAME</B> String => table name
620     * <LI><B>COLUMN_NAME</B> String => column name
621     * <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
622     * <LI><B>TYPE_NAME</B> String => Data source dependent type name
623     * <LI><B>COLUMN_SIZE</B> int => column size. For char or date
624     * types this is the maximum number of characters, for numeric or
625     * decimal types this is precision.
626     * <LI><B>BUFFER_LENGTH</B> is not used.
627     * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
628     * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
629     * <LI><B>NULLABLE</B> int => is NULL allowed?
630     * <UL>
631     * <LI> columnNoNulls - might not allow NULL values
632     * <LI> columnNullable - definitely allows NULL values
633     * <LI> columnNullableUnknown - nullability unknown
634     * </UL>
635     * <LI><B>REMARKS</B> String => comment describing column (may be null)
636     * <LI><B>COLUMN_DEF</B> String => default value (may be null)
637     * <LI><B>SQL_DATA_TYPE</B> int => unused
638     * <LI><B>SQL_DATETIME_SUB</B> int => unused
639     * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
640     * maximum number of bytes in the column
641     * <LI><B>ORDINAL_POSITION</B> int => index of column in table
642     * (starting at 1)
643     * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
644     * does not allow NULL values; "YES" means the column might
645     * allow NULL values. An empty string means nobody knows.
646     * </OL>
647     *
648     * @param catalog a catalog name; "" retrieves those without a
649     * catalog; null means drop catalog name from the selection criteria
650     * @param schemaPattern a schema name pattern; "" retrieves those
651     * without a schema
652     * @param tableNamePattern a table name pattern
653     * @param columnNamePattern a column name pattern
654     * @return ResultSet - each row is a column description
655     * @exception SQLException if a database-access error occurs.
656     * @see #getSearchStringEscape
657     */
658    public java.sql.ResultSet getColumns(String catalog, String schemaPattern,
659                                         String tableNamePattern, String columnNamePattern)
660       throws SQLException
661    {
662       debugPrintln("Inside of getColumn");
663       debugPrintln(" catalog is |" + catalog + "|");
664       debugPrintln(" schemaPattern is " + schemaPattern);
665       debugPrintln(" tableNamePattern is " + tableNamePattern);
666       debugPrintln(" columnNamePattern is " + columnNamePattern);
667
668       return getColumns_SQLServer65(catalog, schemaPattern,
669                                     tableNamePattern, columnNamePattern);
670    }
671
672
673    private java.sql.ResultSet getColumns_SQLServer65(
674       String catalog,
675       String schemaPattern,
676       String tableNamePattern,
677       String columnNamePattern)
678       throws SQLException
679    {
680       int i;
681
682       String sql = null;
683       java.sql.Statement tmpTableStmt = connection.createStatement();
684       String catalogCriteria;
685
686       // XXX We need to come up with something better than a global temporary
687 // table. It could cause problems if two people try to getColumns().
688 // (note- it is _unlikely_, not impossible)
689 String tmpTableName = "##t#" + UniqueId.getUniqueId();
690       String lookup = "#l#" + UniqueId.getUniqueId();
691
692       // create a temporary table
693 sql =
694          "create table " + tmpTableName + " ( " +
695          " TABLE_CAT char(32) null, " +
696          " TABLE_SCHEM char(32) null, " +
697          " TABLE_NAME char(32) null, " +
698          " COLUMN_NAME char(32) null, " +
699          " DATA_TYPE integer null, " +
700          " TYPE_NAME char(32) null, " +
701          " COLUMN_SIZE integer null, " +
702          " BUFFER_LENGTH integer null, " +
703          " DECIMAL_DIGITS integer null, " +
704          " NUM_PREC_RADIX integer null, " +
705          " NULLABLE integer null, " +
706          " REMARKS char(255) null, " +
707          " COLUMN_DEF char(255) null, " +
708          " SQL_DATA_TYPE integer null, " +
709          " SQL_DATETIME_SUB integer null, " +
710          " CHAR_OCTET_LENGTH integer null, " +
711          " ORDINAL_POSITION integer null, " +
712          " IS_NULLABLE char(3)) " +
713          "";
714       tmpTableStmt.execute(sql);
715
716       // Create a lookup table for mapping between native and jdbc types
717 sql =
718          "create table " + lookup + " ( " +
719          " native_type integer primary key, " +
720          " jdbc_type integer not null) ";
721       tmpTableStmt.execute(sql);
722
723       sql =
724          "insert into " + lookup + " values ( 31, 1111) " + // VOID
725 "insert into " + lookup + " values ( 34, 1111) " + // IMAGE
726 "insert into " + lookup + " values ( 35, -1) " + // TEXT
727 "insert into " + lookup + " values ( 37, -3) " + // VARBINARY
728 "insert into " + lookup + " values ( 38, 4) " + // INTN
729 "insert into " + lookup + " values ( 39, 12) " + // VARCHAR
730 "insert into " + lookup + " values ( 45, -2) " + // BINARY
731 "insert into " + lookup + " values ( 47, 1) " + // CHAR
732 "insert into " + lookup + " values ( 48, -6) " + // INT1
733 "insert into " + lookup + " values ( 50, -7) " + // BIT
734 "insert into " + lookup + " values ( 52, 5) " + // INT2
735 "insert into " + lookup + " values ( 56, 4) " + // INT4
736 "insert into " + lookup + " values ( 58, 93) " + // DATETIME4
737 "insert into " + lookup + " values ( 59, 7) " + // REAL
738 "insert into " + lookup + " values ( 60, 1111) " + // MONEY
739 "insert into " + lookup + " values ( 61, 93) " + // DATETIME
740 "insert into " + lookup + " values ( 62, 8) " + // FLT8
741 "insert into " + lookup + " values (106, 3) " + // DECIMAL
742 "insert into " + lookup + " values (108, 2) " + // NUMERIC
743 "insert into " + lookup + " values (109, 8) " + // FLTN
744 "insert into " + lookup + " values (110, 1111) " + // MONEYN
745 "insert into " + lookup + " values (111, 93) " + // DATETIMN
746 "insert into " + lookup + " values (112, 1111) " + // MONEY4
747 "";
748       tmpTableStmt.execute(sql);
749
750
751       // For each table in the system add its columns
752 // Note- We have to do them one at a time in case
753 // there are databases we don't have access to.
754 java.sql.ResultSet rs = getTables(null, "%", "%", null);
755       while(rs.next())
756       {
757          String cat = rs.getString(1);
758
759          // XXX Security risk. It 'might' be possible to create
760 // a catalog name that when inserted into this sql statement could
761 // do other commands.
762 sql =
763             "insert into " + tmpTableName + " " +
764             "select " +
765             " TABLE_CAT='" + cat + "', " +
766             " TABLE_SCHEM=USER_NAME(o.uid), " +
767             " TABLE_NAME=o.name, " +
768             " COLUMN_NAME=c.name, " +
769             " DATA_TYPE=l.jdbc_type, " +
770             " TYPE_NAME=t.name, " +
771             " COLUMN_SIZE=c.prec, " +
772             " BUFFER_LENGTH=0, " +
773             " DECIMAL_DIGITS=c.scale, " +
774             " NUM_PREC_RADIX=10, " +
775             " NULLABLE=convert(integer, " +
776             " convert(bit, c.status&8)), " +
777             " REMARKS=null, " +
778             " COLUMN_DEF=null, " +
779             " SQL_DATATYPE=c.type, " +
780             " SQL_DATETIME_SUB=0, " +
781             " CHAR_OCTET_LENGTH=c.length, " +
782             " ORDINAL_POSITION=c.colid, " +
783             " IS_NULLABLE= " +
784             " convert(char(3), rtrim(substring " +
785             " ('NO YES', " +
786             " (c.status&8)+1,3))) " +
787             "from " +
788             " " + cat + ".dbo.sysobjects o, " +
789             " " + cat + ".dbo.syscolumns c, " +
790             " " + lookup + " l, " +
791             " systypes t " +
792             "where o.type in ('V', 'U') and o.id=c.id " +
793             " and t.type=c.type " +
794             " and l.native_type=c.type " +
795             "";
796 // System.out.println("Executing \n" + sql + "\n");
797 try
798          {
799             tmpTableStmt.executeUpdate(sql);
800          }
801          catch (SQLException e)
802          {
803
804          }
805       }
806       rs.close();
807
808
809       if (catalog == null)
810       {
811          catalog = "";
812          catalogCriteria = " (TABLE_CAT like '%' or TABLE_CAT=?) ";
813       }
814       else
815       {
816          catalogCriteria = " TABLE_CAT=? ";
817       }
818
819       sql =
820          "select distinct * from " + tmpTableName + " where " +
821          catalogCriteria + " and " +
822          " TABLE_SCHEM like ? and TABLE_NAME like ? and " +
823          " COLUMN_NAME like ? " +
824          "order by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION " ;
825
826       System.out.println("The query is \n" + sql);
827
828       java.sql.PreparedStatement ps = connection.prepareStatement(sql);
829
830       ps.setString(1, catalog);
831       ps.setString(2, schemaPattern);
832       ps.setString(3, tableNamePattern);
833       ps.setString(4, columnNamePattern);
834       rs = ps.executeQuery();
835
836       // We need to do something about deleting the global temporary table
837 tmpTableStmt.close();
838
839       return rs;
840    }
841
842
843    /**
844     * Get a description of the foreign key columns in the foreign key
845     * table that reference the primary key columns of the primary key
846     * table (describe how one table imports another's key.) This
847     * should normally return a single foreign key/primary key pair
848     * (most tables only import a foreign key from a table once.) They
849     * are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
850     * KEY_SEQ.
851     *
852     * <P>Each foreign key column description has the following columns:
853     * <OL>
854     * <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
855     * <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
856     * <LI><B>PKTABLE_NAME</B> String => primary key table name
857     * <LI><B>PKCOLUMN_NAME</B> String => primary key column name
858     * <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
859     * being exported (may be null)
860     * <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
861     * being exported (may be null)
862     * <LI><B>FKTABLE_NAME</B> String => foreign key table name
863     * being exported
864     * <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
865     * being exported
866     * <LI><B>KEY_SEQ</B> short => sequence number within foreign key
867     * <LI><B>UPDATE_RULE</B> short => What happens to
868     * foreign key when primary is updated:
869     * <UL>
870     * <LI> importedNoAction - do not allow update of primary
871     * key if it has been imported
872     * <LI> importedKeyCascade - change imported key to agree
873     * with primary key update
874     * <LI> importedKeySetNull - change imported key to NULL if
875     * its primary key has been updated
876     * <LI> importedKeySetDefault - change imported key to default values
877     * if its primary key has been updated
878     * <LI> importedKeyRestrict - same as importedKeyNoAction
879     * (for ODBC 2.x compatibility)
880     * </UL>
881     * <LI><B>DELETE_RULE</B> short => What happens to
882     * the foreign key when primary is deleted.
883     * <UL>
884     * <LI> importedKeyNoAction - do not allow delete of primary
885     * key if it has been imported
886     * <LI> importedKeyCascade - delete rows that import a deleted key
887     * <LI> importedKeySetNull - change imported key to NULL if
888     * its primary key has been deleted
889     * <LI> importedKeyRestrict - same as importedKeyNoAction
890     * (for ODBC 2.x compatibility)
891     * <LI> importedKeySetDefault - change imported key to default if
892     * its primary key has been deleted
893     * </UL>
894     * <LI><B>FK_NAME</B> String => foreign key name (may be null)
895     * <LI><B>PK_NAME</B> String => primary key name (may be null)
896     * <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
897     * constraints be deferred until commit
898     * <UL>
899     * <LI> importedKeyInitiallyDeferred - see SQL92 for definition
900     * <LI> importedKeyInitiallyImmediate - see SQL92 for definition
901     * <LI> importedKeyNotDeferrable - see SQL92 for definition
902     * </UL>
903     * </OL>
904     *
905     * @param primaryCatalog a catalog name; "" retrieves those without a
906     * catalog; null means drop catalog name from the selection criteria
907     * @param primarySchema a schema name pattern; "" retrieves those
908     * without a schema
909     * @param primaryTable the table name that exports the key
910     * @param foreignCatalog a catalog name; "" retrieves those without a
911     * catalog; null means drop catalog name from the selection criteria
912     * @param foreignSchema a schema name pattern; "" retrieves those
913     * without a schema
914     * @param foreignTable the table name that imports the key
915     * @return ResultSet - each row is a foreign key column description
916     * @exception SQLException if a database-access error occurs.
917     * @see #getImportedKeys
918     */
919    public java.sql.ResultSet getCrossReference(
920       String primaryCatalog, String primarySchema, String primaryTable,
921       String foreignCatalog, String foreignSchema, String foreignTable
922       ) throws SQLException
923    {
924       NotImplemented(); return null;
925    }
926
927
928    /**
929     * What's the name of this database product?
930     *
931     * @return database product name
932     * @exception SQLException if a database-access error occurs.
933     */
934    public String getDatabaseProductName() throws SQLException
935    {
936       return tds.getDatabaseProductName();
937    }
938
939
940    /**
941     * What's the version of this database product?
942     *
943     * @return database version
944     * @exception SQLException if a database-access error occurs.
945     */
946    public String getDatabaseProductVersion() throws SQLException
947    {
948       return tds.getDatabaseProductVersion();
949    }
950
951
952    //----------------------------------------------------------------------
953
954    /**
955     * What's the database's default transaction isolation level? The
956     * values are defined in java.sql.Connection.
957     *
958     * @return the default isolation level
959     * @exception SQLException if a database-access error occurs.
960     * @see Connection
961     */
962    public int getDefaultTransactionIsolation() throws SQLException
963    {
964       // XXX need to check this for Sybase
965 return Connection.TRANSACTION_READ_COMMITTED;
966    }
967
968
969    /**
970     * What's this JDBC driver's major version number?
971     *
972     * @return JDBC driver major version
973     */
974    public int getDriverMajorVersion()
975    {
976       return DriverVersion.getDriverMajorVersion();
977    }
978
979
980    /**
981     * What's this JDBC driver's minor version number?
982     *
983     * @return JDBC driver minor version number
984     */
985    public int getDriverMinorVersion()
986    {
987       return DriverVersion.getDriverMinorVersion();
988    }
989
990
991    /**
992     * What's the name of this JDBC driver?
993     *
994     * @return JDBC driver name
995     * @exception SQLException if a database-access error occurs.
996     */
997    public String getDriverName() throws SQLException
998    {
999       return "InternetCDS Type 4 JDBC driver for MS SQLServer";
1000   }
1001
1002
1003   /**
1004    * What's the version of this JDBC driver?
1005    *
1006    * @return JDBC driver version
1007    * @exception SQLException if a database-access error occurs.
1008    */
1009   public</