00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { Interbase Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Merkuriev }
00007 { }
00008 {*********************************************************}
00009
00010 {@********************************************************}
00011 { Copyright (c) 1999-2006 Zeos Development Group }
00012 { }
00013 { License Agreement: }
00014 { }
00015 { This library is distributed in the hope that it will be }
00016 { useful, but WITHOUT ANY WARRANTY; without even the }
00017 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00018 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00019 { Public License for more details. }
00020 { }
00021 { The source code of the ZEOS Libraries and packages are }
00022 { distributed under the Library GNU General Public }
00023 { License (see the file COPYING / COPYING.ZEOS) }
00024 { with the following modification: }
00025 { As a special exception, the copyright holders of this }
00026 { library give you permission to link this library with }
00027 { independent modules to produce an executable, }
00028 { regardless of the license terms of these independent }
00029 { modules, and to copy and distribute the resulting }
00030 { executable under terms of your choice, provided that }
00031 { you also meet, for each linked independent module, }
00032 { the terms and conditions of the license of that module. }
00033 { An independent module is a module which is not derived }
00034 { from or based on this library. If you modify this }
00035 { library, you may extend this exception to your version }
00036 { of the library, but you are not obligated to do so. }
00037 { If you do not wish to do so, delete this exception }
00038 { statement from your version. }
00039 { }
00040 { }
00041 { The project web site is located on: }
00042 { http:
00043 { http:
00044 { svn:
00045 { }
00046 { http:
00047 { http:
00048 { }
00049 { }
00050 { }
00051 { Zeos Development Group. }
00052 {********************************************************@}
00053
00054 unit ZDbcInterbase6Metadata;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 {$IFNDEF VER130BELOW}
00062 Types,
00063 {$ENDIF}
00064 Classes, SysUtils, ZSysUtils, ZDbcIntfs, ZDbcMetadata, ZCompatibility,
00065 ZDbcConnection, ZDbcInterbase6;
00066
00067 type
00068
00069 {** Implements Interbase6 Database Metadata. }
00070 TZInterbase6DatabaseMetadata = class(TZAbstractDatabaseMetadata)
00071 private
00072 FServerVersion: string;
00073 FIBConnection: TZInterbase6Connection;
00074 function StripEscape(const Pattern: string): string;
00075 function HasNoWildcards(const Pattern: string): boolean;
00076 function GetPrivilege(Privilege: string): string;
00077 function ConstructNameCondition(Pattern: string; Column: string): string;
00078 function GetServerVersion: string;
00079 protected
00080 function UncachedGetTables(const Catalog: string; const SchemaPattern: string;
00081 const TableNamePattern: string; const Types: TStringDynArray): IZResultSet; override;
00082
00083
00084 function UncachedGetTableTypes: IZResultSet; override;
00085 function UncachedGetColumns(const Catalog: string; const SchemaPattern: string;
00086 const TableNamePattern: string; const ColumnNamePattern: string): IZResultSet; override;
00087 function UncachedGetTablePrivileges(const Catalog: string; const SchemaPattern: string;
00088 const TableNamePattern: string): IZResultSet; override;
00089 function UncachedGetColumnPrivileges(const Catalog: string; const Schema: string;
00090 const Table: string; const ColumnNamePattern: string): IZResultSet; override;
00091 function UncachedGetPrimaryKeys(const Catalog: string; const Schema: string;
00092 const Table: string): IZResultSet; override;
00093 function UncachedGetImportedKeys(const Catalog: string; const Schema: string;
00094 const Table: string): IZResultSet; override;
00095 function UncachedGetExportedKeys(const Catalog: string; const Schema: string;
00096 const Table: string): IZResultSet; override;
00097
00098
00099
00100 function UncachedGetIndexInfo(const Catalog: string; const Schema: string; const Table: string;
00101 Unique: Boolean; Approximate: Boolean): IZResultSet; override;
00102 function UncachedGetSequences(const Catalog: string; const SchemaPattern: string;
00103 const SequenceNamePattern: string): IZResultSet; override;
00104 function UncachedGetProcedures(const Catalog: string; const SchemaPattern: string;
00105 const ProcedureNamePattern: string): IZResultSet; override;
00106 function UncachedGetProcedureColumns(const Catalog: string; const SchemaPattern: string;
00107 const ProcedureNamePattern: string; const ColumnNamePattern: string):
00108 IZResultSet; override;
00109 function UncachedGetVersionColumns(const Catalog: string; const Schema: string;
00110 const Table: string): IZResultSet; override;
00111 function UncachedGetTypeInfo: IZResultSet; override;
00112
00113
00114 public
00115 constructor Create(Connection: TZAbstractConnection; Url: string; Info: TStrings);
00116 destructor Destroy; override;
00117
00118 property ServerVersion: string read GetServerVersion write FServerVersion;
00119
00120 function GetDatabaseProductName: string; override;
00121 function GetDatabaseProductVersion: string; override;
00122 function GetDriverName: string; override;
00123 function GetDriverMajorVersion: Integer; override;
00124 function GetDriverMinorVersion: Integer; override;
00125 function UsesLocalFilePerTable: Boolean; override;
00126 function SupportsMixedCaseIdentifiers: Boolean; override;
00127 function StoresUpperCaseIdentifiers: Boolean; override;
00128 function StoresLowerCaseIdentifiers: Boolean; override;
00129 function StoresMixedCaseIdentifiers: Boolean; override;
00130 function SupportsMixedCaseQuotedIdentifiers: Boolean; override;
00131 function StoresUpperCaseQuotedIdentifiers: Boolean; override;
00132 function StoresLowerCaseQuotedIdentifiers: Boolean; override;
00133 function StoresMixedCaseQuotedIdentifiers: Boolean; override;
00134 function GetSQLKeywords: string; override;
00135 function GetNumericFunctions: string; override;
00136 function GetStringFunctions: string; override;
00137 function GetSystemFunctions: string; override;
00138 function GetTimeDateFunctions: string; override;
00139 function GetSearchStringEscape: string; override;
00140 function GetExtraNameCharacters: string; override;
00141
00142 function SupportsExpressionsInOrderBy: Boolean; override;
00143 function SupportsOrderByUnrelated: Boolean; override;
00144 function SupportsGroupBy: Boolean; override;
00145 function SupportsGroupByUnrelated: Boolean; override;
00146 function SupportsGroupByBeyondSelect: Boolean; override;
00147 function SupportsIntegrityEnhancementFacility: Boolean; override;
00148 function GetSchemaTerm: string; override;
00149 function GetProcedureTerm: string; override;
00150 function GetCatalogTerm: string; override;
00151 function GetCatalogSeparator: string; override;
00152 function SupportsSchemasInDataManipulation: Boolean; override;
00153 function SupportsSchemasInProcedureCalls: Boolean; override;
00154 function SupportsSchemasInTableDefinitions: Boolean; override;
00155 function SupportsSchemasInIndexDefinitions: Boolean; override;
00156 function SupportsSchemasInPrivilegeDefinitions: Boolean; override;
00157 function SupportsCatalogsInDataManipulation: Boolean; override;
00158 function SupportsCatalogsInProcedureCalls: Boolean; override;
00159 function SupportsCatalogsInTableDefinitions: Boolean; override;
00160 function SupportsCatalogsInIndexDefinitions: Boolean; override;
00161 function SupportsCatalogsInPrivilegeDefinitions: Boolean; override;
00162 function SupportsPositionedDelete: Boolean; override;
00163 function SupportsPositionedUpdate: Boolean; override;
00164 function SupportsSelectForUpdate: Boolean; override;
00165 function SupportsStoredProcedures: Boolean; override;
00166 function SupportsSubqueriesInComparisons: Boolean; override;
00167 function SupportsSubqueriesInExists: Boolean; override;
00168 function SupportsSubqueriesInIns: Boolean; override;
00169 function SupportsSubqueriesInQuantifieds: Boolean; override;
00170 function SupportsCorrelatedSubqueries: Boolean; override;
00171 function SupportsUnion: Boolean; override;
00172 function SupportsUnionAll: Boolean; override;
00173 function SupportsOpenCursorsAcrossCommit: Boolean; override;
00174 function SupportsOpenCursorsAcrossRollback: Boolean; override;
00175 function SupportsOpenStatementsAcrossCommit: Boolean; override;
00176 function SupportsOpenStatementsAcrossRollback: Boolean; override;
00177
00178 function GetMaxBinaryLiteralLength: Integer; override;
00179 function GetMaxCharLiteralLength: Integer; override;
00180 function GetMaxColumnNameLength: Integer; override;
00181 function GetMaxColumnsInGroupBy: Integer; override;
00182 function GetMaxColumnsInIndex: Integer; override;
00183 function GetMaxColumnsInOrderBy: Integer; override;
00184 function GetMaxColumnsInSelect: Integer; override;
00185 function GetMaxColumnsInTable: Integer; override;
00186 function GetMaxConnections: Integer; override;
00187 function GetMaxCursorNameLength: Integer; override;
00188 function GetMaxIndexLength: Integer; override;
00189 function GetMaxSchemaNameLength: Integer; override;
00190 function GetMaxProcedureNameLength: Integer; override;
00191 function GetMaxCatalogNameLength: Integer; override;
00192 function GetMaxRowSize: Integer; override;
00193 function DoesMaxRowSizeIncludeBlobs: Boolean; override;
00194 function GetMaxStatementLength: Integer; override;
00195 function GetMaxStatements: Integer; override;
00196 function GetMaxTableNameLength: Integer; override;
00197 function GetMaxTablesInSelect: Integer; override;
00198 function GetMaxUserNameLength: Integer; override;
00199
00200 function GetDefaultTransactionIsolation: TZTransactIsolationLevel; override;
00201 function SupportsTransactions: Boolean; override;
00202 function SupportsTransactionIsolationLevel(Level: TZTransactIsolationLevel):
00203 Boolean; override;
00204 function SupportsDataDefinitionAndDataManipulationTransactions: Boolean; override;
00205 function SupportsDataManipulationTransactionsOnly: Boolean; override;
00206 function DataDefinitionCausesTransactionCommit: Boolean; override;
00207 function DataDefinitionIgnoredInTransactions: Boolean; override;
00208
00209 function SupportsResultSetType(_Type: TZResultSetType): Boolean; override;
00210 function SupportsResultSetConcurrency(_Type: TZResultSetType;
00211 Concurrency: TZResultSetConcurrency): Boolean; override;
00212 end;
00213
00214 implementation
00215
00216 uses ZMessages, ZDbcInterbase6Utils;
00217
00218 { TZInterbase6DatabaseMetadata }
00219
00220 {**
00221 Constructs this object and assignes the main properties.
00222 @param Connection a database connection object.
00223 @param Url a database connection url string.
00224 @param Info an extra connection properties.
00225 }
00226 constructor TZInterbase6DatabaseMetadata.Create(Connection: TZAbstractConnection;
00227 Url: string; Info: TStrings);
00228 begin
00229 inherited Create(Connection, Url, Info);
00230 FIBConnection := Connection as TZInterbase6Connection;
00231 end;
00232
00233 {**
00234 Destroys this object and cleanups the memory.
00235 }
00236 destructor TZInterbase6DatabaseMetadata.Destroy;
00237 begin
00238 inherited Destroy;
00239 end;
00240
00241
00242
00243
00244 {**
00245 What's the name of this database product?
00246 @return database product name
00247 }
00248 function TZInterbase6DatabaseMetadata.GetDatabaseProductName: string;
00249 begin
00250 Result := 'Interbase/Firebird';
00251 end;
00252
00253 {**
00254 What's the version of this database product?
00255 @return database version
00256 }
00257 function TZInterbase6DatabaseMetadata.GetDatabaseProductVersion: string;
00258 begin
00259 Result := '6.0+';
00260 end;
00261
00262 {**
00263 What's the name of this JDBC driver?
00264 @return JDBC driver name
00265 }
00266 function TZInterbase6DatabaseMetadata.GetDriverName: string;
00267 begin
00268 Result := 'Zeos Database Connectivity Driver for Interbase and Firebird';
00269 end;
00270
00271 {**
00272 What's this JDBC driver's major version number?
00273 @return JDBC driver major version
00274 }
00275 function TZInterbase6DatabaseMetadata.GetDriverMajorVersion: Integer;
00276 begin
00277 Result := 1;
00278 end;
00279
00280 {**
00281 What's this JDBC driver's minor version number?
00282 @return JDBC driver minor version number
00283 }
00284 function TZInterbase6DatabaseMetadata.GetDriverMinorVersion: Integer;
00285 begin
00286 Result := 0;
00287 end;
00288
00289 {**
00290 Does the database use a file for each table?
00291 @return true if the database uses a local file for each table
00292 }
00293 function TZInterbase6DatabaseMetadata.UsesLocalFilePerTable: Boolean;
00294 begin
00295 Result := False;
00296 end;
00297
00298 {**
00299 Does the database treat mixed case unquoted SQL identifiers as
00300 case sensitive and as a result store them in mixed case?
00301 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return false.
00302 @return <code>true</code> if so; <code>false</code> otherwise
00303 }
00304 function TZInterbase6DatabaseMetadata.SupportsMixedCaseIdentifiers: Boolean;
00305 begin
00306 Result := True;
00307 end;
00308
00309 {**
00310 Does the database treat mixed case unquoted SQL identifiers as
00311 case insensitive and store them in upper case?
00312 @return <code>true</code> if so; <code>false</code> otherwise
00313 }
00314 function TZInterbase6DatabaseMetadata.StoresUpperCaseIdentifiers: Boolean;
00315 begin
00316 Result := True;
00317 end;
00318
00319 {**
00320 Does the database treat mixed case unquoted SQL identifiers as
00321 case insensitive and store them in lower case?
00322 @return <code>true</code> if so; <code>false</code> otherwise
00323 }
00324 function TZInterbase6DatabaseMetadata.StoresLowerCaseIdentifiers: Boolean;
00325 begin
00326 Result := False;
00327 end;
00328
00329 {**
00330 Does the database treat mixed case unquoted SQL identifiers as
00331 case insensitive and store them in mixed case?
00332 @return <code>true</code> if so; <code>false</code> otherwise
00333 }
00334 function TZInterbase6DatabaseMetadata.StoresMixedCaseIdentifiers: Boolean;
00335 begin
00336 Result := False;
00337 end;
00338
00339 {**
00340 Does the database treat mixed case quoted SQL identifiers as
00341 case sensitive and as a result store them in mixed case?
00342 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return true.
00343 @return <code>true</code> if so; <code>false</code> otherwise
00344 }
00345 function TZInterbase6DatabaseMetadata.SupportsMixedCaseQuotedIdentifiers: Boolean;
00346 begin
00347 Result := True;
00348 end;
00349
00350 {**
00351 Does the database treat mixed case quoted SQL identifiers as
00352 case insensitive and store them in upper case?
00353 @return <code>true</code> if so; <code>false</code> otherwise
00354 }
00355 function TZInterbase6DatabaseMetadata.StoresUpperCaseQuotedIdentifiers: Boolean;
00356 begin
00357 Result := False;
00358 end;
00359
00360 {**
00361 Does the database treat mixed case quoted SQL identifiers as
00362 case insensitive and store them in lower case?
00363 @return <code>true</code> if so; <code>false</code> otherwise
00364 }
00365 function TZInterbase6DatabaseMetadata.StoresLowerCaseQuotedIdentifiers: Boolean;
00366 begin
00367 Result := False;
00368 end;
00369
00370 {**
00371 Does the database treat mixed case quoted SQL identifiers as
00372 case insensitive and store them in mixed case?
00373 @return <code>true</code> if so; <code>false</code> otherwise
00374 }
00375 function TZInterbase6DatabaseMetadata.StoresMixedCaseQuotedIdentifiers: Boolean;
00376 begin
00377 Result := True;
00378 end;
00379
00380 {**
00381 Gets a comma-separated list of all a database's SQL keywords
00382 that are NOT also SQL92 keywords.
00383 @return the list
00384 }
00385 function TZInterbase6DatabaseMetadata.GetSQLKeywords: string;
00386 begin
00387 Result := 'ACTIVE,AFTER,ASCENDING,BASE_NAME,BEFORE,BLOB,' +
00388 'CACHE,CHECK_POINT_LENGTH,COMPUTED,CONDITIONAL,CONTAINING,' +
00389 'CSTRING,DATABASE,RDB$DB_KEY,DEBUG,DESCENDING,DO,ENTRY_POINT,' +
00390 'EXIT,FILE,FILTER,FUNCTION,GDSCODE,GENERATOR,GEN_ID,' +
00391 'GROUP_COMMIT_WAIT_TIME,IF,INACTIVE,INPUT_TYPE,INDEX,' +
00392 'LOGFILE,LOG_BUFFER_SIZE,MANUAL,MAXIMUM_SEGMENT,MERGE, MESSAGE,' +
00393 'MODULE_NAME,NCHAR,NUM_LOG_BUFFERS,OUTPUT_TYPE,OVERFLOW,PAGE,' +
00394 'PAGES,PAGE_SIZE,PARAMETER,PASSWORD,PLAN,POST_EVENT,PROTECTED,' +
00395 'RAW_PARTITIONS,RESERV,RESERVING,RETAIN,RETURNING_VALUES,RETURNS,' +
00396 'SEGMENT,SHADOW,SHARED,SINGULAR,SNAPSHOT,SORT,STABILITY,STARTS,' +
00397 'STARTING,STATISTICS,SUB_TYPE,SUSPEND,TRIGGER,VARIABLE,RECORD_VERSION,' +
00398 'WAIT,WHILE,WORK';
00399 end;
00400
00401 {**
00402 Gets a comma-separated list of math functions. These are the
00403 X/Open CLI math function names used in the JDBC function escape
00404 clause.
00405 @return the list
00406 }
00407 function TZInterbase6DatabaseMetadata.GetNumericFunctions: string;
00408 begin
00409 Result := '';
00410 end;
00411
00412 {**
00413 Gets a comma-separated list of string functions. These are the
00414 X/Open CLI string function names used in the JDBC function escape
00415 clause.
00416 @return the list
00417 }
00418 function TZInterbase6DatabaseMetadata.GetStringFunctions: string;
00419 begin
00420 Result := '';
00421 end;
00422
00423 {**
00424 Gets a comma-separated list of system functions. These are the
00425 X/Open CLI system function names used in the JDBC function escape
00426 clause.
00427 @return the list
00428 }
00429 function TZInterbase6DatabaseMetadata.GetSystemFunctions: string;
00430 begin
00431 Result := '';
00432 end;
00433
00434 {**
00435 Gets a comma-separated list of time and date functions.
00436 @return the list
00437 }
00438 function TZInterbase6DatabaseMetadata.GetTimeDateFunctions: string;
00439 begin
00440 Result := '';
00441 end;
00442
00443 {**
00444 Gets the string that can be used to escape wildcard characters.
00445 This is the string that can be used to escape '_' or '%' in
00446 the string pattern style catalog search parameters.
00447
00448 <P>The '_' character represents any single character.
00449 <P>The '%' character represents any sequence of zero or
00450 more characters.
00451
00452 @return the string used to escape wildcard characters
00453 }
00454 function TZInterbase6DatabaseMetadata.GetSearchStringEscape: string;
00455 begin
00456 Result := '\';
00457 end;
00458
00459 {**
00460 Gets all the "extra" characters that can be used in unquoted
00461 identifier names (those beyond a-z, A-Z, 0-9 and _).
00462 @return the string containing the extra characters
00463 }
00464 function TZInterbase6DatabaseMetadata.GetExtraNameCharacters: string;
00465 begin
00466 Result := '$';
00467 end;
00468
00469
00470
00471
00472 {**
00473 Are expressions in "ORDER BY" lists supported?
00474 @return <code>true</code> if so; <code>false</code> otherwise
00475 }
00476 function TZInterbase6DatabaseMetadata.SupportsExpressionsInOrderBy: Boolean;
00477 begin
00478 Result := False;
00479 end;
00480
00481 {**
00482 Can an "ORDER BY" clause use columns not in the SELECT statement?
00483 @return <code>true</code> if so; <code>false</code> otherwise
00484 }
00485 function TZInterbase6DatabaseMetadata.SupportsOrderByUnrelated: Boolean;
00486 begin
00487 Result := True;
00488 end;
00489
00490 {**
00491 Is some form of "GROUP BY" clause supported?
00492 @return <code>true</code> if so; <code>false</code> otherwise
00493 }
00494 function TZInterbase6DatabaseMetadata.SupportsGroupBy: Boolean;
00495 begin
00496 Result := True;
00497 end;
00498
00499 {**
00500 Can a "GROUP BY" clause use columns not in the SELECT?
00501 @return <code>true</code> if so; <code>false</code> otherwise
00502 }
00503 function TZInterbase6DatabaseMetadata.SupportsGroupByUnrelated: Boolean;
00504 begin
00505 Result := True;
00506 end;
00507
00508 {**
00509 Can a "GROUP BY" clause add columns not in the SELECT
00510 provided it specifies all the columns in the SELECT?
00511 @return <code>true</code> if so; <code>false</code> otherwise
00512 }
00513 function TZInterbase6DatabaseMetadata.SupportsGroupByBeyondSelect: Boolean;
00514 begin
00515 Result := True;
00516 end;
00517
00518 {**
00519 Is the SQL Integrity Enhancement Facility supported?
00520 @return <code>true</code> if so; <code>false</code> otherwise
00521 }
00522 function TZInterbase6DatabaseMetadata.SupportsIntegrityEnhancementFacility: Boolean;
00523 begin
00524 Result := False;
00525 end;
00526
00527 {**
00528 What's the database vendor's preferred term for "schema"?
00529 @return the vendor term
00530 }
00531 function TZInterbase6DatabaseMetadata.GetSchemaTerm: string;
00532 begin
00533 Result := '';
00534 end;
00535
00536 {**
00537 What's the database vendor's preferred term for "procedure"?
00538 @return the vendor term
00539 }
00540 function TZInterbase6DatabaseMetadata.GetProcedureTerm: string;
00541 begin
00542 Result := 'PROCEDURE';
00543 end;
00544
00545 {**
00546 What's the database vendor's preferred term for "catalog"?
00547 @return the vendor term
00548 }
00549 function TZInterbase6DatabaseMetadata.GetCatalogTerm: string;
00550 begin
00551 Result := '';
00552 end;
00553
00554 {**
00555 What's the separator between catalog and table name?
00556 @return the separator string
00557 }
00558 function TZInterbase6DatabaseMetadata.GetCatalogSeparator: string;
00559 begin
00560 Result := '';
00561 end;
00562
00563 {**
00564 Can a schema name be used in a data manipulation statement?
00565 @return <code>true</code> if so; <code>false</code> otherwise
00566 }
00567 function TZInterbase6DatabaseMetadata.SupportsSchemasInDataManipulation: Boolean;
00568 begin
00569 Result := False;
00570 end;
00571
00572 {**
00573 Can a schema name be used in a procedure call statement?
00574 @return <code>true</code> if so; <code>false</code> otherwise
00575 }
00576 function TZInterbase6DatabaseMetadata.SupportsSchemasInProcedureCalls: Boolean;
00577 begin
00578 Result := False;
00579 end;
00580
00581 {**
00582 Can a schema name be used in a table definition statement?
00583 @return <code>true</code> if so; <code>false</code> otherwise
00584 }
00585 function TZInterbase6DatabaseMetadata.SupportsSchemasInTableDefinitions: Boolean;
00586 begin
00587 Result := False;
00588 end;
00589
00590 {**
00591 Can a schema name be used in an index definition statement?
00592 @return <code>true</code> if so; <code>false</code> otherwise
00593 }
00594 function TZInterbase6DatabaseMetadata.SupportsSchemasInIndexDefinitions: Boolean;
00595 begin
00596 Result := False;
00597 end;
00598
00599 {**
00600 Can a schema name be used in a privilege definition statement?
00601 @return <code>true</code> if so; <code>false</code> otherwise
00602 }
00603 function TZInterbase6DatabaseMetadata.SupportsSchemasInPrivilegeDefinitions: Boolean;
00604 begin
00605 Result := False;
00606 end;
00607
00608 {**
00609 Can a catalog name be used in a data manipulation statement?
00610 @return <code>true</code> if so; <code>false</code> otherwise
00611 }
00612 function TZInterbase6DatabaseMetadata.SupportsCatalogsInDataManipulation: Boolean;
00613 begin
00614 Result := False;
00615 end;
00616
00617 {**
00618 Can a catalog name be used in a procedure call statement?
00619 @return <code>true</code> if so; <code>false</code> otherwise
00620 }
00621 function TZInterbase6DatabaseMetadata.SupportsCatalogsInProcedureCalls: Boolean;
00622 begin
00623 Result := False;
00624 end;
00625
00626 {**
00627 Can a catalog name be used in a table definition statement?
00628 @return <code>true</code> if so; <code>false</code> otherwise
00629 }
00630 function TZInterbase6DatabaseMetadata.SupportsCatalogsInTableDefinitions: Boolean;
00631 begin
00632 Result := False;
00633 end;
00634
00635 {**
00636 Can a catalog name be used in an index definition statement?
00637 @return <code>true</code> if so; <code>false</code> otherwise
00638 }
00639 function TZInterbase6DatabaseMetadata.SupportsCatalogsInIndexDefinitions: Boolean;
00640 begin
00641 Result := False;
00642 end;
00643
00644 {**
00645 Can a catalog name be used in a privilege definition statement?
00646 @return <code>true</code> if so; <code>false</code> otherwise
00647 }
00648 function TZInterbase6DatabaseMetadata.SupportsCatalogsInPrivilegeDefinitions: Boolean;
00649 begin
00650 Result := False;
00651 end;
00652
00653 {**
00654 Is positioned DELETE supported?
00655 @return <code>true</code> if so; <code>false</code> otherwise
00656 }
00657 function TZInterbase6DatabaseMetadata.SupportsPositionedDelete: Boolean;
00658 begin
00659 Result := True;
00660 end;
00661
00662 {**
00663 Is positioned UPDATE supported?
00664 @return <code>true</code> if so; <code>false</code> otherwise
00665 }
00666 function TZInterbase6DatabaseMetadata.SupportsPositionedUpdate: Boolean;
00667 begin
00668 Result := True;
00669 end;
00670
00671 {**
00672 Is SELECT for UPDATE supported?
00673 @return <code>true</code> if so; <code>false</code> otherwise
00674 }
00675 function TZInterbase6DatabaseMetadata.SupportsSelectForUpdate: Boolean;
00676 begin
00677 Result := True;
00678 end;
00679
00680 {**
00681 Are stored procedure calls using the stored procedure escape
00682 syntax supported?
00683 @return <code>true</code> if so; <code>false</code> otherwise
00684 }
00685 function TZInterbase6DatabaseMetadata.SupportsStoredProcedures: Boolean;
00686 begin
00687 Result := True;
00688 end;
00689
00690 {**
00691 Are subqueries in comparison expressions supported?
00692 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00693 @return <code>true</code> if so; <code>false</code> otherwise
00694 }
00695 function TZInterbase6DatabaseMetadata.SupportsSubqueriesInComparisons: Boolean;
00696 begin
00697 Result := True;
00698 end;
00699
00700 {**
00701 Are subqueries in 'exists' expressions supported?
00702 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00703 @return <code>true</code> if so; <code>false</code> otherwise
00704 }
00705 function TZInterbase6DatabaseMetadata.SupportsSubqueriesInExists: Boolean;
00706 begin
00707 Result := True;
00708 end;
00709
00710 {**
00711 Are subqueries in 'in' statements supported?
00712 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00713 @return <code>true</code> if so; <code>false</code> otherwise
00714 }
00715 function TZInterbase6DatabaseMetadata.SupportsSubqueriesInIns: Boolean;
00716 begin
00717 Result := False;
00718 end;
00719
00720 {**
00721 Are subqueries in quantified expressions supported?
00722 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00723 @return <code>true</code> if so; <code>false</code> otherwise
00724 }
00725 function TZInterbase6DatabaseMetadata.SupportsSubqueriesInQuantifieds: Boolean;
00726 begin
00727 Result := True;
00728 end;
00729
00730 {**
00731 Are correlated subqueries supported?
00732 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00733 @return <code>true</code> if so; <code>false</code> otherwise
00734 }
00735 function TZInterbase6DatabaseMetadata.SupportsCorrelatedSubqueries: Boolean;
00736 begin
00737 Result := True;
00738 end;
00739
00740 {**
00741 Is SQL UNION supported?
00742 @return <code>true</code> if so; <code>false</code> otherwise
00743 }
00744 function TZInterbase6DatabaseMetadata.SupportsUnion: Boolean;
00745 begin
00746 Result := True;
00747 end;
00748
00749 {**
00750 Is SQL UNION ALL supported?
00751 @return <code>true</code> if so; <code>false</code> otherwise
00752 }
00753 function TZInterbase6DatabaseMetadata.SupportsUnionAll: Boolean;
00754 begin
00755 Result := True;
00756 end;
00757
00758 {**
00759 Can cursors remain open across commits?
00760 @return <code>true</code> if cursors always remain open;
00761 <code>false</code> if they might not remain open
00762 }
00763 function TZInterbase6DatabaseMetadata.SupportsOpenCursorsAcrossCommit: Boolean;
00764 begin
00765 Result := False;
00766 end;
00767
00768 {**
00769 Can cursors remain open across rollbacks?
00770 @return <code>true</code> if cursors always remain open;
00771 <code>false</code> if they might not remain open
00772 }
00773 function TZInterbase6DatabaseMetadata.SupportsOpenCursorsAcrossRollback: Boolean;
00774 begin
00775 Result := False;
00776 end;
00777
00778 {**
00779 Can statements remain open across commits?
00780 @return <code>true</code> if statements always remain open;
00781 <code>false</code> if they might not remain open
00782 }
00783 function TZInterbase6DatabaseMetadata.SupportsOpenStatementsAcrossCommit: Boolean;
00784 begin
00785 Result := True;
00786 end;
00787
00788 {**
00789 Can statements remain open across rollbacks?
00790 @return <code>true</code> if statements always remain open;
00791 <code>false</code> if they might not remain open
00792 }
00793 function TZInterbase6DatabaseMetadata.SupportsOpenStatementsAcrossRollback: Boolean;
00794 begin
00795 Result := True;
00796 end;
00797
00798 //----------------------------------------------------------------------
00799 // The following group of methods exposes various limitations
00800 // based on the target database with the current driver.
00801 // Unless otherwise specified, a result of zero means there is no
00802 // limit, or the limit is not known.
00803
00804 {**
00805 How many hex characters can you have in an inline binary literal?
00806 @return max binary literal length in hex characters;
00807 a result of zero means that there is no limit or the limit is not known
00808 }
00809 function TZInterbase6DatabaseMetadata.GetMaxBinaryLiteralLength: Integer;
00810 begin
00811 Result := 0;
00812 end;
00813
00814 {**
00815 What's the max length for a character literal?
00816 @return max literal length;
00817 a result of zero means that there is no limit or the limit is not known
00818 }
00819 function TZInterbase6DatabaseMetadata.GetMaxCharLiteralLength: Integer;
00820 begin
00821 Result := 1024;
00822 end;
00823
00824 {**
00825 What's the limit on column name length?
00826 @return max column name length;
00827 a result of zero means that there is no limit or the limit is not known
00828 }
00829 function TZInterbase6DatabaseMetadata.GetMaxColumnNameLength: Integer;
00830 begin
00831 Result := 31;
00832 end;
00833
00834 {**
00835 What's the maximum number of columns in a "GROUP BY" clause?
00836 @return max number of columns;
00837 a result of zero means that there is no limit or the limit is not known
00838 }
00839 function TZInterbase6DatabaseMetadata.GetMaxColumnsInGroupBy: Integer;
00840 begin
00841 Result := 16;
00842 end;
00843
00844 {**
00845 What's the maximum number of columns allowed in an index?
00846 @return max number of columns;
00847 a result of zero means that there is no limit or the limit is not known
00848 }
00849 function TZInterbase6DatabaseMetadata.GetMaxColumnsInIndex: Integer;
00850 begin
00851 Result := 16;
00852 end;
00853
00854 {**
00855 What's the maximum number of columns in an "ORDER BY" clause?
00856 @return max number of columns;
00857 a result of zero means that there is no limit or the limit is not known
00858 }
00859 function TZInterbase6DatabaseMetadata.GetMaxColumnsInOrderBy: Integer;
00860 begin
00861 Result := 16;
00862 end;
00863
00864 {**
00865 What's the maximum number of columns in a "SELECT" list?
00866 @return max number of columns;
00867 a result of zero means that there is no limit or the limit is not known
00868 }
00869 function TZInterbase6DatabaseMetadata.GetMaxColumnsInSelect: Integer;
00870 begin
00871 Result := 32767;
00872 end;
00873
00874 {**
00875 What's the maximum number of columns in a table?
00876 @return max number of columns;
00877 a result of zero means that there is no limit or the limit is not known
00878 }
00879 function TZInterbase6DatabaseMetadata.GetMaxColumnsInTable: Integer;
00880 begin
00881 Result := 32767;
00882 end;
00883
00884 {**
00885 How many active connections can we have at a time to this database?
00886 @return max number of active connections;
00887 a result of zero means that there is no limit or the limit is not known
00888 }
00889 function TZInterbase6DatabaseMetadata.GetMaxConnections: Integer;
00890 begin
00891 Result := 0;
00892 end;
00893
00894 {**
00895 What's the maximum cursor name length?
00896 @return max cursor name length in bytes;
00897 a result of zero means that there is no limit or the limit is not known
00898 }
00899 function TZInterbase6DatabaseMetadata.GetMaxCursorNameLength: Integer;
00900 begin
00901 Result := 31;
00902 end;
00903
00904 {**
00905 Retrieves the maximum number of bytes for an index, including all
00906 of the parts of the index.
00907 @return max index length in bytes, which includes the composite of all
00908 the constituent parts of the index;
00909 a result of zero means that there is no limit or the limit is not known
00910 }
00911 function TZInterbase6DatabaseMetadata.GetMaxIndexLength: Integer;
00912 begin
00913 Result := 198;
00914 end;
00915
00916 {**
00917 What's the maximum length allowed for a schema name?
00918 @return max name length in bytes;
00919 a result of zero means that there is no limit or the limit is not known
00920 }
00921 function TZInterbase6DatabaseMetadata.GetMaxSchemaNameLength: Integer;
00922 begin
00923 Result := 0;
00924 end;
00925
00926 {**
00927 What's the maximum length of a procedure name?
00928 @return max name length in bytes;
00929 a result of zero means that there is no limit or the limit is not known
00930 }
00931 function TZInterbase6DatabaseMetadata.GetMaxProcedureNameLength: Integer;
00932 begin
00933 Result := 0;
00934 end;
00935
00936 {**
00937 What's the maximum length of a catalog name?
00938 @return max name length in bytes;
00939 a result of zero means that there is no limit or the limit is not known
00940 }
00941 function TZInterbase6DatabaseMetadata.GetMaxCatalogNameLength: Integer;
00942 begin
00943 Result := 27;
00944 end;
00945
00946 {**
00947 What's the maximum length of a single row?
00948 @return max row size in bytes;
00949 a result of zero means that there is no limit or the limit is not known
00950 }
00951 function TZInterbase6DatabaseMetadata.GetMaxRowSize: Integer;
00952 begin
00953 Result := 32664;
00954 end;
00955
00956 {**
00957 Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
00958 blobs?
00959 @return <code>true</code> if so; <code>false</code> otherwise
00960 }
00961 function TZInterbase6DatabaseMetadata.DoesMaxRowSizeIncludeBlobs: Boolean;
00962 begin
00963 Result := False;
00964 end;
00965
00966 {**
00967 What's the maximum length of an SQL statement?
00968 @return max length in bytes;
00969 a result of zero means that there is no limit or the limit is not known
00970 }
00971 function TZInterbase6DatabaseMetadata.GetMaxStatementLength: Integer;
00972 begin
00973 Result := 640;
00974 end;
00975
00976 {**
00977 How many active statements can we have open at one time to this
00978 database?
00979 @return the maximum number of statements that can be open at one time;
00980 a result of zero means that there is no limit or the limit is not known
00981 }
00982 function TZInterbase6DatabaseMetadata.GetMaxStatements: Integer;
00983 begin
00984 Result := 0;
00985 end;
00986
00987 {**
00988 What's the maximum length of a table name?
00989 @return max name length in bytes;
00990 a result of zero means that there is no limit or the limit is not known
00991 }
00992 function TZInterbase6DatabaseMetadata.GetMaxTableNameLength: Integer;
00993 begin
00994 Result := 31;
00995 end;
00996
00997 {**
00998 What's the maximum number of tables in a SELECT statement?
00999 @return the maximum number of tables allowed in a SELECT statement;
01000 a result of zero means that there is no limit or the limit is not known
01001 }
01002 function TZInterbase6DatabaseMetadata.GetMaxTablesInSelect: Integer;
01003 begin
01004 Result := 16;
01005 end;
01006
01007 {**
01008 What's the maximum length of a user name?
01009 @return max user name length in bytes;
01010 a result of zero means that there is no limit or the limit is not known
01011 }
01012 function TZInterbase6DatabaseMetadata.GetMaxUserNameLength: Integer;
01013 begin
01014 Result := 31;
01015 end;
01016
01017 //----------------------------------------------------------------------
01018
01019 {**
01020 What's the database's default transaction isolation level? The
01021 values are defined in <code>java.sql.Connection</code>.
01022 @return the default isolation level
01023 @see Connection
01024 }
01025 function TZInterbase6DatabaseMetadata.GetDefaultTransactionIsolation:
01026 TZTransactIsolationLevel;
01027 begin
01028 Result := tiSerializable;
01029 end;
01030
01031 {**
01032 Are transactions supported? If not, invoking the method
01033 <code>commit</code> is a noop and the isolation level is TRANSACTION_NONE.
01034 @return <code>true</code> if transactions are supported; <code>false</code> otherwise
01035 }
01036 function TZInterbase6DatabaseMetadata.SupportsTransactions: Boolean;
01037 begin
01038 Result := True;
01039 end;
01040
01041 {**
01042 Does this database support the given transaction isolation level?
01043 @param level the values are defined in <code>java.sql.Connection</code>
01044 @return <code>true</code> if so; <code>false</code> otherwise
01045 @see Connection
01046 }
01047 function TZInterbase6DatabaseMetadata.SupportsTransactionIsolationLevel(
01048 Level: TZTransactIsolationLevel): Boolean;
01049 begin
01050 case Level of
01051 tiRepeatableRead, tiReadCommitted, tiSerializable: Result := True;
01052 tiReadUncommitted: Result := False;
01053 tiNone: Result := False; //MAY BE FIX IT
01054 else
01055 Result := False;
01056 end;
01057 end;
01058
01059 {**
01060 Are both data definition and data manipulation statements
01061 within a transaction supported?
01062 @return <code>true</code> if so; <code>false</code> otherwise
01063 }
01064 function TZInterbase6DatabaseMetadata.
01065 SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
01066 begin
01067 Result := True;
01068 end;
01069
01070 {**
01071 Are only data manipulation statements within a transaction
01072 supported?
01073 @return <code>true</code> if so; <code>false</code> otherwise
01074 }
01075 function TZInterbase6DatabaseMetadata.
01076 SupportsDataManipulationTransactionsOnly: Boolean;
01077 begin
01078 Result := False;
01079 end;
01080
01081 {**
01082 Does a data definition statement within a transaction force the
01083 transaction to commit?
01084 @return <code>true</code> if so; <code>false</code> otherwise
01085 }
01086 function TZInterbase6DatabaseMetadata.DataDefinitionCausesTransactionCommit: Boolean;
01087 begin
01088 Result := True;
01089 end;
01090
01091 {**
01092 Is a data definition statement within a transaction ignored?
01093 @return <code>true</code> if so; <code>false</code> otherwise
01094 }
01095 function TZInterbase6DatabaseMetadata.DataDefinitionIgnoredInTransactions: Boolean;
01096 begin
01097 Result := False;
01098 end;
01099
01100 {**
01101 Gets a description of the stored procedures available in a
01102 catalog.
01103
01104 <P>Only procedure descriptions matching the schema and
01105 procedure name criteria are returned. They are ordered by
01106 PROCEDURE_SCHEM, and PROCEDURE_NAME.
01107
01108 <P>Each procedure description has the the following columns:
01109 <OL>
01110 <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
01111 <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
01112 <LI><B>PROCEDURE_NAME</B> String => procedure name
01113 <LI> reserved for future use
01114 <LI> reserved for future use
01115 <LI> reserved for future use
01116 <LI><B>REMARKS</B> String => explanatory comment on the procedure
01117 <LI><B>PROCEDURE_TYPE</B> short => kind of procedure:
01118 <UL>
01119 <LI> procedureResultUnknown - May return a result
01120 <LI> procedureNoResult - Does not return a result
01121 <LI> procedureReturnsResult - Returns a result
01122 </UL>
01123 </OL>
01124
01125 @param catalog a catalog name; "" retrieves those without a
01126 catalog; null means drop catalog name from the selection criteria
01127 @param schemaPattern a schema name pattern; "" retrieves those
01128 without a schema
01129 @param procedureNamePattern a procedure name pattern
01130 @return <code>ResultSet</code> - each row is a procedure description
01131 @see #getSearchStringEscape
01132 }
01133 function TZInterbase6DatabaseMetadata.UncachedGetProcedures(const Catalog: string;
01134 const SchemaPattern: string; const ProcedureNamePattern: string): IZResultSet;
01135 var
01136 SQL: string;
01137 LProcedureNamePattern: string;
01138 begin
01139 Result := ConstructVirtualResultSet(ProceduresColumnsDynArray);
01140
01141 LProcedureNamePattern := ConstructNameCondition(ProcedureNamePattern,
01142 'RDB$PROCEDURE_NAME');
01143 SQL := 'SELECT RDB$PROCEDURE_NAME, RDB$PROCEDURE_OUTPUTS,'
01144 + ' RDB$DESCRIPTION FROM RDB$PROCEDURES';
01145 if LProcedureNamePattern <> '' then
01146 SQL := SQL + ' WHERE ' + LProcedureNamePattern;
01147
01148 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01149 begin
01150 while Next do
01151 begin
01152 Result.MoveToInsertRow;
01153 Result.UpdateNull(1);
01154 Result.UpdateNull(2);
01155 Result.UpdateString(3, GetString(1)); //RDB$PROCEDURE_NAME
01156 Result.UpdateNull(4);
01157 Result.UpdateNull(5);
01158 Result.UpdateNull(6);
01159 Result.UpdateString(7, GetString(3)); //RDB$DESCRIPTION
01160 if IsNull(2) then //RDB$PROCEDURE_OUTPUTS
01161 Result.UpdateInt(8, Ord(prtNoResult))
01162 else Result.UpdateInt(8, Ord(prtReturnsResult));
01163 Result.InsertRow;
01164 end;
01165 Close;
01166 end;
01167 end;
01168
01169 {**
01170 Gets a description of a catalog's stored procedure parameters
01171 and result columns.
01172
01173 <P>Only descriptions matching the schema, procedure and
01174 parameter name criteria are returned. They are ordered by
01175 PROCEDURE_SCHEM and PROCEDURE_NAME. Within this, the return value,
01176 if any, is first. Next are the parameter descriptions in call
01177 order. The column descriptions follow in column number order.
01178
01179 <P>Each row in the <code>ResultSet</code> is a parameter description or
01180 column description with the following fields:
01181 <OL>
01182 <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
01183 <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
01184 <LI><B>PROCEDURE_NAME</B> String => procedure name
01185 <LI><B>COLUMN_NAME</B> String => column/parameter name
01186 <LI><B>COLUMN_TYPE</B> Short => kind of column/parameter:
01187 <UL>
01188 <LI> procedureColumnUnknown - nobody knows
01189 <LI> procedureColumnIn - IN parameter
01190 <LI> procedureColumnInOut - INOUT parameter
01191 <LI> procedureColumnOut - OUT parameter
01192 <LI> procedureColumnReturn - procedure return value
01193 <LI> procedureColumnResult - result column in <code>ResultSet</code>
01194 </UL>
01195 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01196 <LI><B>TYPE_NAME</B> String => SQL type name, for a UDT type the
01197 type name is fully qualified
01198 <LI><B>PRECISION</B> int => precision
01199 <LI><B>LENGTH</B> int => length in bytes of data
01200 <LI><B>SCALE</B> short => scale
01201 <LI><B>RADIX</B> short => radix
01202 <LI><B>NULLABLE</B> short => can it contain NULL?
01203 <UL>
01204 <LI> procedureNoNulls - does not allow NULL values
01205 <LI> procedureNullable - allows NULL values
01206 <LI> procedureNullableUnknown - nullability unknown
01207 </UL>
01208 <LI><B>REMARKS</B> String => comment describing parameter/column
01209 </OL>
01210
01211 <P><B>Note:</B> Some databases may not return the column
01212 descriptions for a procedure. Additional columns beyond
01213 REMARKS can be defined by the database.
01214
01215 @param catalog a catalog name; "" retrieves those without a
01216 catalog; null means drop catalog name from the selection criteria
01217 @param schemaPattern a schema name pattern; "" retrieves those
01218 without a schema
01219 @param procedureNamePattern a procedure name pattern
01220 @param columnNamePattern a column name pattern
01221 @return <code>ResultSet</code> - each row describes a stored procedure parameter or
01222 column
01223 @see #getSearchStringEscape
01224 }
01225 function TZInterbase6DatabaseMetadata.UncachedGetProcedureColumns(const Catalog: string;
01226 const SchemaPattern: string; const ProcedureNamePattern: string;
01227 const ColumnNamePattern: string): IZResultSet;
01228 var
01229 SQL, Where: string;
01230 LProcedureNamePattern, LColumnNamePattern: string;
01231 TypeName, SubTypeName: Integer;
01232 ColumnIndexes : Array[1..8] of integer;
01233 begin
01234 Result := ConstructVirtualResultSet(ProceduresColColumnsDynArray);
01235
01236 LProcedureNamePattern := ConstructNameCondition(ProcedureNamePattern,
01237 'P.RDB$PROCEDURE_NAME');
01238 LColumnNamePattern := ConstructNameCondition(ColumnNamePattern,
01239 'PP.RDB$PARAMETER_NAME');
01240
01241 if (StrPos(PChar(ServerVersion), 'Interbase 5') <> nil)
01242 or (StrPos(PChar(ServerVersion), 'V5.')<>nil) then
01243 begin
01244 SQL := ' SELECT P.RDB$PROCEDURE_NAME, PP.RDB$PARAMETER_NAME,'
01245 + ' PP.RDB$PARAMETER_TYPE, F.RDB$FIELD_TYPE, F.RDB$FIELD_SUB_TYPE,'
01246 + ' F.RDB$FIELD_SCALE, F.RDB$FIELD_LENGTH, F.RDB$NULL_FLAG,'
01247 + ' PP.RDB$DESCRIPTION, F.RDB$FIELD_SCALE as RDB$FIELD_PRECISION,'
01248 + ' F.RDB$NULL_FLAG FROM RDB$PROCEDURES P'
01249 + ' JOIN RDB$PROCEDURE_PARAMETERS PP ON P.RDB$PROCEDURE_NAME'
01250 + '=PP.RDB$PROCEDURE_NAME JOIN RDB$FIELDS F ON PP.RDB$FIELD_SOURCE'
01251 + '=F.RDB$FIELD_NAME ';
01252
01253 Where := LProcedureNamePattern;
01254 if LColumnNamePattern <> '' then
01255 begin
01256 if Where = '' then
01257 Where := LColumnNamePattern
01258 else Where := Where + ' AND ' + LColumnNamePattern;
01259 end;
01260 if Where <> '' then
01261 Where := ' WHERE ' + Where;
01262
01263 SQL := SQL + Where + ' ORDER BY P.RDB$PROCEDURE_NAME,'
01264 + ' PP.RDB$PARAMETER_TYPE desc, PP.RDB$PARAMETER_NUMBER';
01265 end
01266 else
01267 begin
01268 SQL := ' SELECT P.RDB$PROCEDURE_NAME, PP.RDB$PARAMETER_NAME,'
01269 + ' PP.RDB$PARAMETER_TYPE, F.RDB$FIELD_TYPE, F.RDB$FIELD_SUB_TYPE,'
01270 + ' F.RDB$FIELD_SCALE, F.RDB$FIELD_LENGTH, F.RDB$NULL_FLAG,'
01271 + ' PP.RDB$DESCRIPTION, F.RDB$FIELD_PRECISION, F.RDB$NULL_FLAG '
01272 + ' FROM RDB$PROCEDURES P JOIN RDB$PROCEDURE_PARAMETERS PP ON'
01273 + ' P.RDB$PROCEDURE_NAME = PP.RDB$PROCEDURE_NAME '
01274 + ' JOIN RDB$FIELDS F ON PP.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME ';
01275
01276 Where := LProcedureNamePattern;
01277 if LColumnNamePattern <> '' then
01278 begin
01279 if Where = '' then
01280 Where := LColumnNamePattern
01281 else Where := Where + ' AND ' + LColumnNamePattern;
01282 end;
01283 if Where <> '' then
01284 Where := ' WHERE ' + Where;
01285
01286 SQL := SQL + Where + ' ORDER BY P.RDB$PROCEDURE_NAME,'
01287 + ' PP.RDB$PARAMETER_TYPE desc, PP.RDB$PARAMETER_NUMBER';
01288 end;
01289
01290 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01291 begin
01292 ColumnIndexes[1] := FindColumn('RDB$PROCEDURE_NAME');
01293 ColumnIndexes[2] := FindColumn('RDB$PARAMETER_NAME');
01294 ColumnIndexes[3] := FindColumn('RDB$PARAMETER_TYPE');
01295 ColumnIndexes[4] := FindColumn('RDB$FIELD_TYPE');
01296 ColumnIndexes[5] := FindColumn('RDB$FIELD_SUB_TYPE');
01297 ColumnIndexes[6] := FindColumn('RDB$FIELD_PRECISION');
01298 ColumnIndexes[7] := FindColumn('RDB$FIELD_SCALE');
01299 ColumnIndexes[8] := FindColumn('RDB$NULL_FLAG');
01300 while Next do
01301 begin
01302 TypeName := GetInt(ColumnIndexes[4]);
01303 SubTypeName := GetInt(ColumnIndexes[5]);
01304
01305 Result.MoveToInsertRow;
01306 Result.UpdateNull(1);
01307 Result.UpdateNull(2);
01308 Result.UpdateString(3, GetString(ColumnIndexes[1]));
01309 Result.UpdateString(4, GetString(ColumnIndexes[2]));
01310 case GetInt(ColumnIndexes[3]) of
01311 0: Result.UpdateInt(5, 1);
01312 1: Result.UpdateInt(5, 4);
01313 else Result.UpdateInt(5, 0);
01314 end;
01315
01316 Result.UpdateInt(6,
01317 Ord(ConvertInterbase6ToSqlType(TypeName, SubTypeName)));
01318 Result.UpdateString(7,GetString(ColumnIndexes[4]));
01319 Result.UpdateInt(10, GetInt(ColumnIndexes[6]));
01320 Result.UpdateNull(9);
01321 Result.UpdateInt(10, GetInt(ColumnIndexes[7]));
01322 Result.UpdateInt(11, 10);
01323 Result.UpdateInt(12, GetInt(ColumnIndexes[8]));
01324 Result.UpdateString(12, GetString(ColumnIndexes[6]));
01325 Result.InsertRow;
01326 end;
01327 Close;
01328 end;
01329 end;
01330
01331 {**
01332 Gets a description of tables available in a catalog.
01333
01334 <P>Only table descriptions matching the catalog, schema, table
01335 name and type criteria are returned. They are ordered by
01336 TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
01337
01338 <P>Each table description has the following columns:
01339 <OL>
01340 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01341 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01342 <LI><B>TABLE_NAME</B> String => table name
01343 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01344 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01345 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01346 <LI><B>REMARKS</B> String => explanatory comment on the table
01347 </OL>
01348
01349 <P><B>Note:</B> Some databases may not return information for
01350 all tables.
01351
01352 @param catalog a catalog name; "" retrieves those without a
01353 catalog; null means drop catalog name from the selection criteria
01354 @param schemaPattern a schema name pattern; "" retrieves those
01355 without a schema
01356 @param tableNamePattern a table name pattern
01357 @param types a list of table types to include; null returns all types
01358 @return <code>ResultSet</code> - each row is a table description
01359 @see #getSearchStringEscape
01360 }
01361
01362 function TZInterbase6DatabaseMetadata.UncachedGetTables(const Catalog: string;
01363 const SchemaPattern: string; const TableNamePattern: string;
01364 const Types: TStringDynArray): IZResultSet;
01365 var
01366 SQL, TableType: string;
01367 LTableNamePattern: string;
01368 BLR: IZBlob;
01369 I, SystemFlag, ViewContext: Integer;
01370 begin
01371 Result := ConstructVirtualResultSet(TableColumnsDynArray);
01372
01373 LTableNamePattern := ConstructNameCondition(TableNamePattern,
01374 'a.RDB$RELATION_NAME');
01375 SQL := 'SELECT DISTINCT a.RDB$RELATION_NAME, a.RDB$SYSTEM_FLAG, '
01376 + ' a.RDB$VIEW_SOURCE, a.RDB$DESCRIPTION FROM RDB$RELATIONS a';
01377
01378 if LTableNamePattern <> '' then
01379 SQL := SQL + ' WHERE ' + LTableNamePattern;
01380
01381 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01382 begin
01383 while Next do
01384 begin
01385 SystemFlag := GetInt(2);
01386
01387 if SystemFlag = 0 then
01388 begin
01389 if IsNull(3) then
01390 TableType := 'TABLE'
01391 else
01392 TableType := 'VIEW';
01393 end
01394 else
01395 TableType := 'SYSTEM TABLE';
01396
01397 if Length(Types) = 0 then
01398 begin
01399 Result.MoveToInsertRow;
01400 Result.UpdateNull(1);
01401 Result.UpdateNull(2);
01402 Result.UpdateString(3, GetString(1));
01403 Result.UpdateString(4, TableType);
01404 Result.UpdateString(5, Copy(GetString(4),1,255));
01405 Result.InsertRow;
01406 end
01407 else begin
01408 for I := 0 to High(Types) do
01409 begin
01410 if Types[I] = TableType then
01411 begin
01412 Result.MoveToInsertRow;
01413 Result.UpdateNull(1);
01414 Result.UpdateNull(2);
01415 Result.UpdateString(3, GetString(1));
01416 Result.UpdateString(4, TableType);
01417 Result.UpdateString(5, Copy(GetString(4),1,255));
01418 Result.InsertRow;
01419 end;
01420 end;
01421 end;
01422
01423 end;
01424 Close;
01425 end;
01426 end;
01427
01428 {**
01429 Gets the table types available in this database. The results
01430 are ordered by table type.
01431
01432 <P>The table type is:
01433 <OL>
01434 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01435 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01436 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01437 </OL>
01438
01439 @return <code>ResultSet</code> - each row has a single String column that is a
01440 table type
01441 }
01442 function TZInterbase6DatabaseMetadata.UncachedGetTableTypes: IZResultSet;
01443 const
01444 TablesTypes: array [0..2] of string = ('TABLE', 'VIEW', 'SYSTEM TABLE');
01445 var
01446 I: Integer;
01447 begin
01448 Result := ConstructVirtualResultSet(TableTypeColumnsDynArray);
01449 for I := 0 to 2 do
01450 begin
01451 Result.MoveToInsertRow;
01452 Result.UpdateString(1, TablesTypes[I]);
01453 Result.InsertRow;
01454 end;
01455 end;
01456
01457 {**
01458 Gets a description of table columns available in
01459 the specified catalog.
01460
01461 <P>Only column descriptions matching the catalog, schema, table
01462 and column name criteria are returned. They are ordered by
01463 TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
01464
01465 <P>Each column description has the following columns:
01466 <OL>
01467 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01468 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01469 <LI><B>TABLE_NAME</B> String => table name
01470 <LI><B>COLUMN_NAME</B> String => column name
01471 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01472 <LI><B>TYPE_NAME</B> String => Data source dependent type name,
01473 for a UDT the type name is fully qualified
01474 <LI><B>COLUMN_SIZE</B> int => column size. For char or date
01475 types this is the maximum number of characters, for numeric or
01476 decimal types this is precision.
01477 <LI><B>BUFFER_LENGTH</B> is not used.
01478 <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
01479 <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
01480 <LI><B>NULLABLE</B> int => is NULL allowed?
01481 <UL>
01482 <LI> columnNoNulls - might not allow NULL values
01483 <LI> columnNullable - definitely allows NULL values
01484 <LI> columnNullableUnknown - nullability unknown
01485 </UL>
01486 <LI><B>REMARKS</B> String => comment describing column (may be null)
01487 <LI><B>COLUMN_DEF</B> String => default value (may be null)
01488 <LI><B>SQL_DATA_TYPE</B> int => unused
01489 <LI><B>SQL_DATETIME_SUB</B> int => unused
01490 <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
01491 maximum number of bytes in the column
01492 <LI><B>ORDINAL_POSITION</B> int => index of column in table
01493 (starting at 1)
01494 <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
01495 does not allow NULL values; "YES" means the column might
01496 allow NULL values. An empty string means nobody knows.
01497 </OL>
01498
01499 @param catalog a catalog name; "" retrieves those without a
01500 catalog; null means drop catalog name from the selection criteria
01501 @param schemaPattern a schema name pattern; "" retrieves those
01502 without a schema
01503 @param tableNamePattern a table name pattern
01504 @param columnNamePattern a column name pattern
01505 @return <code>ResultSet</code> - each row is a column description
01506 @see #getSearchStringEscape
01507 }
01508
01509 function TZInterbase6DatabaseMetadata.UncachedGetColumns(const Catalog: string;
01510 const SchemaPattern: string; const TableNamePattern: string;
01511 const ColumnNamePattern: string): IZResultSet;
01512 var
01513 SQL, Where, ColumnName, DefaultValue: string;
01514 TypeName, SubTypeName, FieldScale: integer;
01515 LTableNamePattern, LColumnNamePattern: string;
01516 ColumnIndexes : Array[1..14] of integer;
01517 begin
01518 Result := ConstructVirtualResultSet(TableColColumnsDynArray);
01519
01520 LTableNamePattern := ConstructNameCondition(TableNamePattern,
01521 'a.RDB$RELATION_NAME');
01522 LColumnNamePattern := ConstructNameCondition(ColumnNamePattern,
01523 'a.RDB$FIELD_NAME');
01524
01525 if (StrPos(PChar(ServerVersion), 'Interbase 5') <> nil)
01526 or (StrPos(PChar(ServerVersion), 'V5.')<>nil) then
01527 begin
01528 SQL := 'SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION,'
01529 + ' a.RDB$NULL_FLAG, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE,'
01530 + ' c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE,'
01531 + ' b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_SCALE'
01532 + ' as RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE'
01533 + ' as RDB$DEFAULT_SOURCE_DOMAIN, b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE'
01534 + ' FROM RDB$RELATION_FIELDS a'
01535 + ' JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE)'
01536 + ' LEFT JOIN RDB$TYPES c ON b.RDB$FIELD_TYPE = c.RDB$TYPE'
01537 + ' and c.RDB$FIELD_NAME = ''RDB$FIELD_TYPE''';
01538
01539 Where := LTableNamePattern;
01540 if LColumnNamePattern <> '' then
01541 begin
01542 if Where = '' then
01543 Where := LColumnNamePattern
01544 else Where := Where + ' AND ' + LColumnNamePattern;
01545 end;
01546 if Where <> '' then
01547 Where := ' WHERE ' + Where;
01548
01549 SQL := SQL + Where + ' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION';
01550 end
01551 else
01552 begin
01553 SQL := ' SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION,'
01554 + ' a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH,'
01555 + ' b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE,'
01556 + ' b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH,'
01557 + ' b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE'
01558 + ' as RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE'
01559 + ' FROM RDB$RELATION_FIELDS a'
01560 + ' JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE)'
01561 + ' LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE'
01562 + ' and c.RDB$FIELD_NAME = ''RDB$FIELD_TYPE'')';
01563
01564 Where := LTableNamePattern;
01565 if LColumnNamePattern <> '' then
01566 begin
01567 if Where = '' then
01568 Where := LColumnNamePattern
01569 else Where := Where + ' AND ' + LColumnNamePattern;
01570 end;
01571 if Where <> '' then
01572 Where := ' WHERE ' + Where;
01573
01574 SQL := SQL + Where + ' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION';
01575 end;
01576
01577 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01578 begin
01579 ColumnIndexes[1] := FindColumn('RDB$FIELD_TYPE');
01580 ColumnIndexes[2] := FindColumn('RDB$FIELD_SUB_TYPE');
01581 ColumnIndexes[3] := FindColumn('RDB$FIELD_SCALE');
01582 ColumnIndexes[4] := FindColumn('RDB$FIELD_NAME');
01583 ColumnIndexes[5] := FindColumn('RDB$DEFAULT_SOURCE');
01584 ColumnIndexes[6] := FindColumn('RDB$DEFAULT_SOURCE_DOMAIN');
01585 ColumnIndexes[7] := FindColumn('RDB$RELATION_NAME');
01586 ColumnIndexes[8] := FindColumn('RDB$TYPE_NAME');
01587 ColumnIndexes[9] := FindColumn('RDB$FIELD_PRECISION');
01588 ColumnIndexes[10] := FindColumn('RDB$FIELD_LENGTH');
01589 ColumnIndexes[11] := FindColumn('RDB$NULL_FLAG');
01590 ColumnIndexes[12] := FindColumn('RDB$DESCRIPTION');
01591 ColumnIndexes[13] := FindColumn('RDB$FIELD_POSITION');
01592 ColumnIndexes[14] := FindColumn('RDB$COMPUTED_SOURCE');
01593 while Next do
01594 begin
01595 TypeName := GetInt(ColumnIndexes[1]);
01596 SubTypeName := GetInt(ColumnIndexes[2]);
01597 FieldScale := GetInt(ColumnIndexes[3]);
01598 ColumnName := GetString(ColumnIndexes[4]);
01599
01600 if not isNull(14) then
01601 begin
01602 if ((TypeName = 16) and (FieldScale < 0)) then SubTypeName := 1;
01603 end;
01604
01605 DefaultValue := GetString(ColumnIndexes[5]);
01606 if DefaultValue = '' then
01607 DefaultValue := GetString(ColumnIndexes[6]);
01608 if StartsWith(Trim(UpperCase(DefaultValue)), 'DEFAULT') then
01609 begin
01610 DefaultValue := Trim(StringReplace(DefaultValue, 'DEFAULT ', '',
01611 [rfIgnoreCase]));
01612 end;
01613
01614 IF (UpperCase(DefaultValue)= '''NOW''') or (UpperCase(DefaultValue)= '"NOW"')then
01615 case TypeName of
01616 12: DefaultValue := 'CURRENT_DATE';
01617 13: DefaultValue := 'CURRENT_TIME';
01618 35: DefaultValue := 'CURRENT_TIMESTAMP';
01619 else begin end;
01620 end;
01621
01622 Result.MoveToInsertRow;
01623 Result.UpdateNull(1);
01624 Result.UpdateNull(2);
01625 Result.UpdateString(3, GetString(ColumnIndexes[7]));
01626 Result.UpdateString(4, ColumnName);
01627 Result.UpdateInt(5,
01628 Ord(ConvertInterbase6ToSqlType(TypeName, SubTypeName)));
01629
01630 case TypeName of
01631 7 : Result.UpdateString(6, 'SMALLINT');
01632 8 : Result.UpdateString(6, 'INTEGER' );
01633 16 :
01634 begin
01635 if (SubTypeName = 0) then
01636 Result.UpdateString(6, GetString(ColumnIndexes[8]));
01637 if (SubTypeName = 1) then
01638 Result.UpdateString(6, 'NUMERIC');
01639 if (SubTypeName = 2) then
01640 Result.UpdateString(6, 'DECIMAL');
01641 end;
01642 37 : Result.UpdateString(6, 'VARCHAR');
01643 else
01644 Result.UpdateString(6, GetString(ColumnIndexes[8]));
01645 end;
01646
01647 case TypeName of
01648 7, 8 : Result.UpdateInt(7, 0);
01649 16 : Result.UpdateInt(7, GetInt(ColumnIndexes[9]));
01650 else
01651 Result.UpdateInt(7, GetInt(ColumnIndexes[10]));
01652 end;
01653
01654 Result.UpdateNull(8);
01655
01656 if FieldScale < 0 then
01657 Result.UpdateInt(9, -1 * FieldScale)
01658 else Result.UpdateInt(9, 0);
01659
01660 Result.UpdateInt(10, 10);
01661
01662 if GetInt(ColumnIndexes[11]) <> 0 then
01663 Result.UpdateInt(11, Ord(ntNoNulls))
01664 else Result.UpdateInt(11, Ord(ntNullable));
01665
01666 Result.UpdateString(12, Copy(GetString(ColumnIndexes[12]),1,255));
01667 Result.UpdateString(13, DefaultValue);
01668 Result.UpdateNull(14);
01669 Result.UpdateNull(15);
01670 Result.UpdateInt(16,
01671 GetInt(7));
01672 Result.UpdateInt(17, GetInt(ColumnIndexes[13]) + 1);
01673
01674 if IsNull(ColumnIndexes[11]) then
01675 Result.UpdateString(18, 'YES')
01676 else Result.UpdateString(18, 'NO');
01677
01678 Result.UpdateNull(19);
01679
01680 if CompareStr(ColumnName, UpperCase(ColumnName)) = 0 then
01681 Result.UpdateBoolean(20, False)
01682 else
01683 Result.UpdateBoolean(20, True);
01684
01685 Result.UpdateBoolean(21, True);
01686 if isNull(ColumnIndexes[14]) then
01687 begin
01688 Result.UpdateBoolean(22, True);
01689 Result.UpdateBoolean(23, True);
01690 Result.UpdateBoolean(24, False);
01691 end
01692 else
01693 begin
01694 Result.UpdateBoolean(22, False);
01695 Result.UpdateBoolean(23, False);
01696 Result.UpdateBoolean(24, True);
01697 end;
01698 Result.InsertRow;
01699 end;
01700 Close;
01701 end;
01702 end;
01703
01704 {**
01705 Gets a description of the access rights for a table's columns.
01706
01707 <P>Only privileges matching the column name criteria are
01708 returned. They are ordered by COLUMN_NAME and PRIVILEGE.
01709
01710 <P>Each privilige description has the following columns:
01711 <OL>
01712 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01713 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01714 <LI><B>TABLE_NAME</B> String => table name
01715 <LI><B>COLUMN_NAME</B> String => column name
01716 <LI><B>GRANTOR</B> => grantor of access (may be null)
01717 <LI><B>GRANTEE</B> String => grantee of access
01718 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01719 INSERT, UPDATE, REFRENCES, ...)
01720 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01721 to grant to others; "NO" if not; null if unknown
01722 </OL>
01723
01724 @param catalog a catalog name; "" retrieves those without a
01725 catalog; null means drop catalog name from the selection criteria
01726 @param schema a schema name; "" retrieves those without a schema
01727 @param table a table name
01728 @param columnNamePattern a column name pattern
01729 @return <code>ResultSet</code> - each row is a column privilege description
01730 @see #getSearchStringEscape
01731 }
01732 function TZInterbase6DatabaseMetadata.UncachedGetColumnPrivileges(const Catalog: string;
01733 const Schema: string; const Table: string; const ColumnNamePattern: string): IZResultSet;
01734 var
01735 SQL: string;
01736 TableName, FieldName, Privilege: string;
01737 Grantor, Grantee, Grantable: string;
01738 LColumnNamePattern, LTable: string;
01739 begin
01740 Result := ConstructVirtualResultSet(TableColPrivColumnsDynArray);
01741
01742 LTable := ConstructNameCondition(AddEscapeCharToWildcards(Table), 'a.RDB$RELATION_NAME');// Modified by cipto 6/12/2007 2:26:18 PM
01743 LColumnNamePattern := ConstructNameCondition(ColumnNamePattern,
01744 'a.RDB$FIELD_NAME');
01745
01746 SQL := 'SELECT a.RDB$USER, a.RDB$GRANTOR, a.RDB$PRIVILEGE,'
01747 + ' a.RDB$GRANT_OPTION, a.RDB$RELATION_NAME, a.RDB$FIELD_NAME '
01748 + ' FROM RDB$USER_PRIVILEGES a, RDB$TYPES b '
01749 + ' WHERE a.RDB$OBJECT_TYPE = b.RDB$TYPE AND ';
01750 if LTable <> '' then
01751 SQL := SQL + LTable + ' AND ';
01752 if LColumnNamePattern <> '' then
01753 SQL := SQL + LColumnNamePattern + ' AND ';
01754 SQL := SQL + ' b.RDB$TYPE_NAME IN (''RELATION'', ''VIEW'','
01755 + ' ''COMPUTED_FIELD'', ''FIELD'' ) AND b.RDB$FIELD_NAME'
01756 + '=''RDB$OBJECT_TYPE'' ORDER BY a.RDB$FIELD_NAME, a.RDB$PRIVILEGE ' ;
01757
01758 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01759 begin
01760 while Next do
01761 begin
01762 TableName := GetString(5); //RDB$RELATION_NAME
01763 FieldName := GetString(6); //RDB$FIELD_NAME
01764 Privilege := GetPrivilege(GetString(3)); //RDB$PRIVILEGE
01765 Grantor := GetString(2); //RDB$GRANTOR
01766 Grantee := GetString(1); //RDB$USER
01767
01768 if Grantor = Grantee then
01769 Grantable := 'YES'
01770 else Grantable := 'NO';
01771 if FieldName = '' then
01772 begin
01773 SQL := 'SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS '
01774 + ' WHERE RDB$RELATION_NAME = ''' + TableName + ''' AND '
01775 + ' RDB$FIELD_NAME = ''' + LColumnNamePattern + ''' AND ';
01776 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01777 begin
01778 while Next do
01779 begin
01780 Result.MoveToInsertRow;
01781 Result.UpdateNull(1);
01782 Result.UpdateNull(2);
01783 Result.UpdateString(3, TableName);
01784 Result.UpdateString(4, GetString(1));
01785 Result.UpdateString(5, Grantor);
01786 Result.UpdateString(6, Grantee);
01787 Result.UpdateString(7, Privilege);
01788 Result.UpdateString(8, Grantable);
01789 Result.InsertRow;
01790 end;
01791 Close;
01792 end;
01793 end
01794 else
01795 begin
01796 Result.MoveToInsertRow;
01797 Result.UpdateNull(1);
01798 Result.UpdateNull(2);
01799 Result.UpdateString(3, TableName);
01800 Result.UpdateString(4, FieldName);
01801 Result.UpdateString(5, Grantor);
01802 Result.UpdateString(6, Grantee);
01803 Result.UpdateString(7, Privilege);
01804 Result.UpdateString(8, Grantable);
01805 Result.InsertRow;
01806 end;
01807 end;
01808 Close;
01809 end;
01810 end;
01811
01812 {**
01813 Gets a description of the access rights for each table available
01814 in a catalog. Note that a table privilege applies to one or
01815 more columns in the table. It would be wrong to assume that
01816 this priviledge applies to all columns (this may be true for
01817 some systems but is not true for all.)
01818
01819 <P>Only privileges matching the schema and table name
01820 criteria are returned. They are ordered by TABLE_SCHEM,
01821 TABLE_NAME, and PRIVILEGE.
01822
01823 <P>Each privilige description has the following columns:
01824 <OL>
01825 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01826 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01827 <LI><B>TABLE_NAME</B> String => table name
01828 <LI><B>GRANTOR</B> => grantor of access (may be null)
01829 <LI><B>GRANTEE</B> String => grantee of access
01830 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01831 INSERT, UPDATE, REFRENCES, ...)
01832 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01833 to grant to others; "NO" if not; null if unknown
01834 </OL>
01835
01836 @param catalog a catalog name; "" retrieves those without a
01837 catalog; null means drop catalog name from the selection criteria
01838 @param schemaPattern a schema name pattern; "" retrieves those
01839 without a schema
01840 @param tableNamePattern a table name pattern
01841 @return <code>ResultSet</code> - each row is a table privilege description
01842 @see #getSearchStringEscape
01843 }
01844 function TZInterbase6DatabaseMetadata.UncachedGetTablePrivileges(const Catalog: string;
01845 const SchemaPattern: string; const TableNamePattern: string): IZResultSet;
01846 var
01847 SQL: string;
01848 TableName, Privilege, Grantor: string;
01849 Grantee, Grantable: string;
01850 LTableNamePattern: string;
01851 begin
01852 Result := ConstructVirtualResultSet(TablePrivColumnsDynArray);
01853
01854 LTableNamePattern := ConstructNameCondition(TableNamePattern,
01855 'a.RDB$RELATION_NAME');
01856
01857 SQL := 'SELECT a.RDB$USER, a.RDB$GRANTOR, a.RDB$PRIVILEGE,'
01858 + ' a.RDB$GRANT_OPTION, a.RDB$RELATION_NAME FROM RDB$USER_PRIVILEGES a,'
01859 + ' RDB$TYPES b WHERE a.RDB$OBJECT_TYPE = b.RDB$TYPE AND '
01860 + ' b.RDB$TYPE_NAME IN (''RELATION'', ''VIEW'', ''COMPUTED_FIELD'','
01861 + ' ''FIELD'' ) AND a.RDB$FIELD_NAME IS NULL ';
01862 if LTableNamePattern <> '' then
01863 SQL := SQL + ' AND ' + LTableNamePattern;
01864 SQL := SQL + ' ORDER BY a.RDB$RELATION_NAME, a.RDB$PRIVILEGE';
01865
01866 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01867 begin
01868 while Next do
01869 begin
01870 TableName := GetString(5); //RDB$RELATION_NAME
01871 Privilege := GetPrivilege(GetString(3)); //RDB$PRIVILEGE
01872 Grantor := GetString(2); //RDB$GRANTOR
01873 Grantee := GetString(1); //RDB$USER
01874
01875 if Grantor = Grantee then
01876 Grantable := 'YES'
01877 else Grantable := 'NO';
01878
01879 Result.MoveToInsertRow;
01880 Result.UpdateNull(1);
01881 Result.UpdateNull(2);
01882 Result.UpdateString(3, TableName);
01883 Result.UpdateString(4, Grantor);
01884 Result.UpdateString(5, Grantee);
01885 Result.UpdateString(6, Privilege);
01886 Result.UpdateString(7, Grantable);
01887 Result.InsertRow;
01888 end;
01889 Close;
01890 end;
01891 end;
01892
01893 {**
01894 Gets a description of a table's columns that are automatically
01895 updated when any value in a row is updated. They are
01896 unordered.
01897
01898 <P>Each column description has the following columns:
01899 <OL>
01900 <LI><B>SCOPE</B> short => is not used
01901 <LI><B>COLUMN_NAME</B> String => column name
01902 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
01903 <LI><B>TYPE_NAME</B> String => Data source dependent type name
01904 <LI><B>COLUMN_SIZE</B> int => precision
01905 <LI><B>BUFFER_LENGTH</B> int => length of column value in bytes
01906 <LI><B>DECIMAL_DIGITS</B> short => scale
01907 <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
01908 like an Oracle ROWID
01909 <UL>
01910 <LI> versionColumnUnknown - may or may not be pseudo column
01911 <LI> versionColumnNotPseudo - is NOT a pseudo column
01912 <LI> versionColumnPseudo - is a pseudo column
01913 </UL>
01914 </OL>
01915
01916 @param catalog a catalog name; "" retrieves those without a
01917 catalog; null means drop catalog name from the selection criteria
01918 @param schema a schema name; "" retrieves those without a schema
01919 @param table a table name
01920 @return <code>ResultSet</code> - each row is a column description
01921 @exception SQLException if a database access error occurs
01922 }
01923 function TZInterbase6DatabaseMetadata.UncachedGetVersionColumns(const Catalog: string;
01924 const Schema: string; const Table: string): IZResultSet;
01925 begin
01926 Result := ConstructVirtualResultSet(TableColVerColumnsDynArray);
01927
01928 Result.MoveToInsertRow;
01929 Result.UpdateNull(1);
01930 Result.UpdateString(2, 'ctid');
01931
01932 Result.UpdateString(4, 'tid');
01933 Result.UpdateNull(5);
01934 Result.UpdateNull(6);
01935 Result.UpdateNull(7);
01936 Result.UpdateInt(4, Ord(vcPseudo));
01937 Result.InsertRow;
01938 end;
01939
01940 {**
01941 Gets a description of a table's primary key columns. They
01942 are ordered by COLUMN_NAME.
01943
01944 <P>Each primary key column description has the following columns:
01945 <OL>
01946 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01947 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01948 <LI><B>TABLE_NAME</B> String => table name
01949 <LI><B>COLUMN_NAME</B> String => column name
01950 <LI><B>KEY_SEQ</B> short => sequence number within primary key
01951 <LI><B>PK_NAME</B> String => primary key name (may be null)
01952 </OL>
01953
01954 @param catalog a catalog name; "" retrieves those without a
01955 catalog; null means drop catalog name from the selection criteria
01956 @param schema a schema name; "" retrieves those
01957 without a schema
01958 @param table a table name
01959 @return <code>ResultSet</code> - each row is a primary key column description
01960 @exception SQLException if a database access error occurs
01961 }
01962 function TZInterbase6DatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
01963 const Schema: string; const Table: string): IZResultSet;
01964 var
01965 SQL: string;
01966 LTable: string;
01967 begin
01968 LTable := ConstructNameCondition(AddEscapeCharToWildcards(Table), 'a.RDB$RELATION_NAME'); // Modified by cipto 6/12/2007 2:03:20 PM
01969 SQL := ' SELECT null as TABLE_CAT, null as TABLE_SCHEM,'
01970 + ' a.RDB$RELATION_NAME as TABLE_NAME, b.RDB$FIELD_NAME as COLUMN_NAME,'
01971 + ' b.RDB$FIELD_POSITION+1 as KEY_SEQ, a.RDB$INDEX_NAME as PK_NAME'
01972 + ' FROM RDB$RELATION_CONSTRAINTS a JOIN RDB$INDEX_SEGMENTS b ON'
01973 + ' (a.RDB$INDEX_NAME = b.RDB$INDEX_NAME)'
01974 + ' WHERE RDB$CONSTRAINT_TYPE = ''PRIMARY KEY''';
01975 if LTable <> '' then
01976 SQL := SQL + ' AND ' + LTable;
01977 SQL := SQL + ' ORDER BY a.RDB$RELATION_NAME, b.RDB$FIELD_NAME';
01978
01979 Result := CopyToVirtualResultSet(
01980 GetConnection.CreateStatement.ExecuteQuery(SQL),
01981 ConstructVirtualResultSet(PrimaryKeyColumnsDynArray));
01982 end;
01983
01984 {**
01985 Gets a description of the primary key columns that are
01986 referenced by a table's foreign key columns (the primary keys
01987 imported by a table). They are ordered by PKTABLE_CAT,
01988 PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
01989
01990 <P>Each primary key column description has the following columns:
01991 <OL>
01992 <LI><B>PKTABLE_CAT</B> String => primary key table catalog
01993 being imported (may be null)
01994 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
01995 being imported (may be null)
01996 <LI><B>PKTABLE_NAME</B> String => primary key table name
01997 being imported
01998 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
01999 being imported
02000 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
02001 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
02002 <LI><B>FKTABLE_NAME</B> String => foreign key table name
02003 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
02004 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
02005 <LI><B>UPDATE_RULE</B> short => What happens to
02006 foreign key when primary is updated:
02007 <UL>
02008 <LI> importedNoAction - do not allow update of primary
02009 key if it has been imported
02010 <LI> importedKeyCascade - change imported key to agree
02011 with primary key update
02012 <LI> importedKeySetNull - change imported key to NULL if
02013 its primary key has been updated
02014 <LI> importedKeySetDefault - change imported key to default values
02015 if its primary key has been updated
02016 <LI> importedKeyRestrict - same as importedKeyNoAction
02017 (for ODBC 2.x compatibility)
02018 </UL>
02019 <LI><B>DELETE_RULE</B> short => What happens to
02020 the foreign key when primary is deleted.
02021 <UL>
02022 <LI> importedKeyNoAction - do not allow delete of primary
02023 key if it has been imported
02024 <LI> importedKeyCascade - delete rows that import a deleted key
02025 <LI> importedKeySetNull - change imported key to NULL if
02026 its primary key has been deleted
02027 <LI> importedKeyRestrict - same as importedKeyNoAction
02028 (for ODBC 2.x compatibility)
02029 <LI> importedKeySetDefault - change imported key to default if
02030 its primary key has been deleted
02031 </UL>
02032 <LI><B>FK_NAME</B> String => foreign key name (may be null)
02033 <LI><B>PK_NAME</B> String => primary key name (may be null)
02034 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
02035 constraints be deferred until commit
02036 <UL>
02037 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
02038 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
02039 <LI> importedKeyNotDeferrable - see SQL92 for definition
02040 </UL>
02041 </OL>
02042
02043 @param catalog a catalog name; "" retrieves those without a
02044 catalog; null means drop catalog name from the selection criteria
02045 @param schema a schema name; "" retrieves those
02046 without a schema
02047 @param table a table name
02048 @return <code>ResultSet</code> - each row is a primary key column description
02049 @see #getExportedKeys
02050 }
02051 function TZInterbase6DatabaseMetadata.UncachedGetImportedKeys(const Catalog: string;
02052 const Schema: string; const Table: string): IZResultSet;
02053 var
02054 SQL: string;
02055 LTable: string;
02056 begin
02057 Result := ConstructVirtualResultSet(ImportedKeyColumnsDynArray);
02058
02059 LTable := ConstructNameCondition(AddEscapeCharToWildcards(Table), 'RELC_FOR.RDB$RELATION_NAME'); // Modified by cipto 6/11/2007 4:53:02 PM
02060 SQL := 'SELECT RELC_PRIM.RDB$RELATION_NAME, ' // 1 prim.RDB$ key table name
02061 + ' IS_PRIM.RDB$FIELD_NAME, ' // 2 prim.RDB$ key column name
02062 + ' RELC_FOR.RDB$RELATION_NAME, ' // 3 foreign key table name
02063 + ' IS_FOR.RDB$FIELD_NAME, ' // 4 foreign key column name
02064 + ' IS_FOR.RDB$FIELD_POSITION, ' // 5 key sequence
02065 + ' REFC_PRIM.RDB$UPDATE_RULE, ' // 6
02066 + ' REFC_PRIM.RDB$DELETE_RULE, ' // 7
02067 + ' RELC_FOR.RDB$CONSTRAINT_NAME, ' // 8 foreign key constraint name
02068 + ' RELC_PRIM.RDB$CONSTRAINT_NAME ' // 9 primary key constraint name
02069 + ' FROM RDB$RELATION_CONSTRAINTS RELC_FOR, RDB$REF_CONSTRAINTS REFC_FOR, '
02070 + ' RDB$RELATION_CONSTRAINTS RELC_PRIM, RDB$REF_CONSTRAINTS REFC_PRIM, '
02071 + ' RDB$INDEX_SEGMENTS IS_PRIM, RDB$INDEX_SEGMENTS IS_FOR '
02072 + ' WHERE RELC_FOR.RDB$CONSTRAINT_TYPE = ''FOREIGN KEY'' AND ';
02073 if LTable <> '' then
02074 SQL := SQL + LTable + ' AND ';
02075 SQL := SQL + ' RELC_FOR.RDB$CONSTRAINT_NAME=REFC_FOR.RDB$CONSTRAINT_NAME'
02076 + ' and REFC_FOR.RDB$CONST_NAME_UQ = RELC_PRIM.RDB$CONSTRAINT_NAME and '
02077 + ' RELC_PRIM.RDB$CONSTRAINT_TYPE = ''PRIMARY KEY'' and ' // useful check, anyay
02078 + ' RELC_PRIM.RDB$INDEX_NAME = IS_PRIM.RDB$INDEX_NAME and '
02079 + ' IS_FOR.RDB$INDEX_NAME = RELC_FOR.RDB$INDEX_NAME and '
02080 + ' IS_PRIM.RDB$FIELD_POSITION = IS_FOR.RDB$FIELD_POSITION and '
02081 + ' REFC_PRIM.RDB$CONSTRAINT_NAME = RELC_FOR.RDB$CONSTRAINT_NAME '
02082 + ' ORDER BY RELC_PRIM.RDB$RELATION_NAME, IS_FOR.RDB$FIELD_POSITION ';
02083
02084 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02085 begin
02086 while Next do
02087 begin
02088 Result.MoveToInsertRow;
02089 Result.UpdateNull(1); //PKTABLE_CAT
02090 Result.UpdateNull(2); //PKTABLE_SCHEM
02091 Result.UpdateString(3, GetString(1)); //PKTABLE_NAME
02092 Result.UpdateString(4, GetString(2)); //PKCOLUMN_NAME
02093 Result.UpdateNull(5); //FKTABLE_CAT
02094 Result.UpdateNull(6); //FKTABLE_SCHEM
02095 Result.UpdateString(7, GetString(3)); //FKTABLE_NAME
02096 Result.UpdateString(8, GetString(4)); //FKCOLUMN_NAME
02097 Result.UpdateInt(9, GetInt(5) + 1); //KEY_SEQ
02098
02099 if GetString(6) = 'RESTRICT' then //UPDATE_RULE
02100 Result.UpdateInt(10, Ord(ikRestrict))
02101 else if GetString(6) = 'NO ACTION' then
02102 Result.UpdateInt(10, Ord(ikNoAction))
02103 else if GetString(6) = 'SET DEFAULT' then
02104 Result.UpdateInt(10, Ord(ikSetDefault))
02105 else if GetString(6) = 'CASCADE' then
02106 Result.UpdateInt(10, Ord(ikCascade))
02107 else if GetString(6) = 'SET NULL' then
02108 Result.UpdateInt(10, Ord(ikSetNull));
02109
02110 if GetString(7) = 'RESTRICT' then //DELETE_RULE
02111 Result.UpdateInt(11, Ord(ikRestrict))
02112 else if GetString(7) = 'NO ACTION' then
02113 Result.UpdateInt(11, Ord(ikNoAction))
02114 else if GetString(7) = 'SET DEFAULT' then
02115 Result.UpdateInt(11, Ord(ikSetDefault))
02116 else if GetString(7) = 'CASCADE' then
02117 Result.UpdateInt(11, Ord(ikCascade))
02118 else if GetString(7) = 'SET NULL' then
02119 Result.UpdateInt(11, Ord(ikSetNull));
02120
02121 Result.UpdateString(12, GetString(8)); //FK_NAME
02122 Result.UpdateString(13, GetString(9)); //PK_NAME
02123 Result.UpdateNull(14); //DEFERABILITY
02124 Result.InsertRow;
02125 end;
02126 Close;
02127 end;
02128 end;
02129
02130 {**
02131 Gets a description of the foreign key columns that reference a
02132 table's primary key columns (the foreign keys exported by a
02133 table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
02134 FKTABLE_NAME, and KEY_SEQ.
02135
02136 <P>Each foreign key column description has the following columns:
02137 <OL>
02138 <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
02139 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
02140 <LI><B>PKTABLE_NAME</B> String => primary key table name
02141 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
02142 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
02143 being exported (may be null)
02144 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
02145 being exported (may be null)
02146 <LI><B>FKTABLE_NAME</B> String => foreign key table name
02147 being exported
02148 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
02149 being exported
02150 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
02151 <LI><B>UPDATE_RULE</B> short => What happens to
02152 foreign key when primary is updated:
02153 <UL>
02154 <LI> importedNoAction - do not allow update of primary
02155 key if it has been imported
02156 <LI> importedKeyCascade - change imported key to agree
02157 with primary key update
02158 <LI> importedKeySetNull - change imported key to NULL if
02159 its primary key has been updated
02160 <LI> importedKeySetDefault - change imported key to default values
02161 if its primary key has been updated
02162 <LI> importedKeyRestrict - same as importedKeyNoAction
02163 (for ODBC 2.x compatibility)
02164 </UL>
02165 <LI><B>DELETE_RULE</B> short => What happens to
02166 the foreign key when primary is deleted.
02167 <UL>
02168 <LI> importedKeyNoAction - do not allow delete of primary
02169 key if it has been imported
02170 <LI> importedKeyCascade - delete rows that import a deleted key
02171 <LI> importedKeySetNull - change imported key to NULL if
02172 its primary key has been deleted
02173 <LI> importedKeyRestrict - same as importedKeyNoAction
02174 (for ODBC 2.x compatibility)
02175 <LI> importedKeySetDefault - change imported key to default if
02176 its primary key has been deleted
02177 </UL>
02178 <LI><B>FK_NAME</B> String => foreign key name (may be null)
02179 <LI><B>PK_NAME</B> String => primary key name (may be null)
02180 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
02181 constraints be deferred until commit
02182 <UL>
02183 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
02184 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
02185 <LI> importedKeyNotDeferrable - see SQL92 for definition
02186 </UL>
02187 </OL>
02188
02189 @param catalog a catalog name; "" retrieves those without a
02190 catalog; null means drop catalog name from the selection criteria
02191 @param schema a schema name; "" retrieves those
02192 without a schema
02193 @param table a table name
02194 @return <code>ResultSet</code> - each row is a foreign key column description
02195 @see #getImportedKeys
02196 }
02197 function TZInterbase6DatabaseMetadata.UncachedGetExportedKeys(const Catalog: string;
02198 const Schema: string; const Table: string): IZResultSet;
02199 var
02200 SQL: string;
02201 LTable: string;
02202 begin
02203 Result := ConstructVirtualResultSet(ExportedKeyColumnsDynArray);
02204
02205 LTable := ConstructNameCondition(AddEscapeCharToWildcards(Table), 'RC_PRIM.RDB$RELATION_NAME');
02206 SQL := ' SELECT RC_PRIM.RDB$RELATION_NAME, '
02207 + ' IS_PRIM.RDB$FIELD_NAME, '
02208 + ' RC_FOR.RDB$RELATION_NAME, '
02209 + ' IS_FOR.RDB$FIELD_NAME, '
02210 + ' IS_FOR.RDB$FIELD_POSITION, '
02211 + ' REFC_PRIM.RDB$UPDATE_RULE, '
02212 + ' REFC_PRIM.RDB$DELETE_RULE, '
02213 + ' RC_FOR.RDB$CONSTRAINT_NAME, '
02214 + ' RC_PRIM.RDB$CONSTRAINT_NAME '
02215 + ' FROM RDB$RELATION_CONSTRAINTS RC_FOR, RDB$REF_CONSTRAINTS REFC_FOR, '
02216 + ' RDB$RELATION_CONSTRAINTS RC_PRIM, RDB$REF_CONSTRAINTS REFC_PRIM, '
02217 + ' RDB$INDEX_SEGMENTS IS_PRIM, RDB$INDEX_SEGMENTS IS_FOR '
02218 + ' WHERE RC_PRIM.RDB$CONSTRAINT_TYPE = ''PRIMARY KEY'' and ';
02219 if LTable <> '' then
02220 SQL := SQL + LTable + ' AND ';
02221 SQL := SQL + ' REFC_FOR.RDB$CONST_NAME_UQ = RC_PRIM.RDB$CONSTRAINT_NAME'
02222 + ' and RC_FOR.RDB$CONSTRAINT_NAME = REFC_FOR.RDB$CONSTRAINT_NAME and '
02223 + ' RC_FOR.RDB$CONSTRAINT_TYPE = ''FOREIGN KEY'' and '
02224 + ' RC_PRIM.RDB$INDEX_NAME = IS_PRIM.RDB$INDEX_NAME and '
02225 + ' IS_FOR.RDB$INDEX_NAME = RC_FOR.RDB$INDEX_NAME and '
02226 + ' IS_PRIM.RDB$FIELD_POSITION = IS_FOR.RDB$FIELD_POSITION and '
02227 + ' REFC_PRIM.RDB$CONSTRAINT_NAME = RC_FOR.RDB$CONSTRAINT_NAME '
02228 + ' ORDER BY RC_FOR.RDB$RELATION_NAME, IS_FOR.RDB$FIELD_POSITION ';
02229
02230 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02231 begin
02232 while Next do
02233 begin
02234 Result.MoveToInsertRow;
02235 Result.UpdateNull(1);
02236 Result.UpdateNull(2);
02237 Result.UpdateString(3, GetString(1));
02238 Result.UpdateString(4, GetString(2));
02239 Result.UpdateNull(5);
02240 Result.UpdateNull(6);
02241 Result.UpdateString(7, GetString(3));
02242 Result.UpdateString(8, GetString(4));
02243 Result.UpdateInt(9, GetInt(5) + 1);
02244
02245 if GetString(6) = 'RESTRICT' then
02246 Result.UpdateInt(10, Ord(ikRestrict))
02247 else if GetString(6) = 'NO ACTION' then
02248 Result.UpdateInt(10, Ord(ikNoAction))
02249 else if GetString(6) = 'SET DEFAULT' then
02250 Result.UpdateInt(10, Ord(ikSetDefault))
02251 else if GetString(6) = 'CASCADE' then
02252 Result.UpdateInt(10, Ord(ikCascade))
02253 else if GetString(6) = 'SET NULL' then
02254 Result.UpdateInt(10, Ord(ikSetNull));
02255
02256 if GetString(7) = 'RESTRICT' then
02257 Result.UpdateInt(11, Ord(ikRestrict))
02258 else if GetString(7) = 'NO ACTION' then
02259 Result.UpdateInt(11, Ord(ikNoAction))
02260 else if GetString(7) = 'SET DEFAULT' then
02261 Result.UpdateInt(11, Ord(ikSetDefault))
02262 else if GetString(7) = 'CASCADE' then
02263 Result.UpdateInt(11, Ord(ikCascade))
02264 else if GetString(7) = 'SET NULL' then
02265 Result.UpdateInt(11, Ord(ikSetNull));
02266
02267 Result.UpdateString(12, GetString(8));
02268 Result.UpdateString(13, GetString(9));
02269 Result.UpdateNull(14);
02270 Result.InsertRow;
02271 end;
02272 Close;
02273 end;
02274 end;
02275
02276 {**
02277 Gets a description of all the standard SQL types supported by
02278 this database. They are ordered by DATA_TYPE and then by how
02279 closely the data type maps to the corresponding JDBC SQL type.
02280
02281 <P>Each type description has the following columns:
02282 <OL>
02283 <LI><B>TYPE_NAME</B> String => Type name
02284 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
02285 <LI><B>PRECISION</B> int => maximum precision
02286 <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
02287 (may be null)
02288 <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
02289 (may be null)
02290 <LI><B>CREATE_PARAMS</B> String => parameters used in creating
02291 the type (may be null)
02292 <LI><B>NULLABLE</B> short => can you use NULL for this type?
02293 <UL>
02294 <LI> typeNoNulls - does not allow NULL values
02295 <LI> typeNullable - allows NULL values
02296 <LI> typeNullableUnknown - nullability unknown
02297 </UL>
02298 <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
02299 <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
02300 <UL>
02301 <LI> typePredNone - No support
02302 <LI> typePredChar - Only supported with WHERE .. LIKE
02303 <LI> typePredBasic - Supported except for WHERE .. LIKE
02304 <LI> typeSearchable - Supported for all WHERE ..
02305 </UL>
02306 <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
02307 <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
02308 <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
02309 auto-increment value?
02310 <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
02311 (may be null)
02312 <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
02313 <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
02314 <LI><B>SQL_DATA_TYPE</B> int => unused
02315 <LI><B>SQL_DATETIME_SUB</B> int => unused
02316 <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
02317 </OL>
02318
02319 @return <code>ResultSet</code> - each row is an SQL type description
02320 }
02321 function TZInterbase6DatabaseMetadata.UncachedGetTypeInfo: IZResultSet;
02322 var
02323 SQL: string;
02324 begin
02325 Result := ConstructVirtualResultSet(TypeInfoColumnsDynArray);
02326
02327 SQL := ' SELECT RDB$TYPE, RDB$TYPE_NAME FROM RDB$TYPES ' +
02328 ' WHERE RDB$FIELD_NAME = ''RDB$FIELD_TYPE'' ';
02329 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02330 begin
02331 while Next do
02332 begin
02333 Result.MoveToInsertRow;
02334 Result.UpdateString(1, GetString(2));
02335 Result.UpdateInt(2, Ord(ConvertInterbase6ToSqlType(GetInt(1), 0)));
02336 Result.UpdateInt(3, 9);
02337 Result.UpdateInt(7, Ord(ntNoNulls));
02338 Result.UpdateBoolean(8, false);
02339 Result.UpdateBoolean(9, false);
02340 Result.UpdateBoolean(11, false);
02341 Result.UpdateBoolean(12, false);
02342 Result.UpdateInt(18, 10);
02343 Result.InsertRow;
02344 end;
02345 Close;
02346 end;
02347 end;
02348
02349 {**
02350 Gets a description of a table's indices and statistics. They are
02351 ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
02352
02353 <P>Each index column description has the following columns:
02354 <OL>
02355 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
02356 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
02357 <LI><B>TABLE_NAME</B> String => table name
02358 <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
02359 false when TYPE is tableIndexStatistic
02360 <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
02361 null when TYPE is tableIndexStatistic
02362 <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
02363 tableIndexStatistic
02364 <LI><B>TYPE</B> short => index type:
02365 <UL>
02366 <LI> tableIndexStatistic - this identifies table statistics that are
02367 returned in conjuction with a table's index descriptions
02368 <LI> tableIndexClustered - this is a clustered index
02369 <LI> tableIndexHashed - this is a hashed index
02370 <LI> tableIndexOther - this is some other style of index
02371 </UL>
02372 <LI><B>ORDINAL_POSITION</B> short => column sequence number
02373 within index; zero when TYPE is tableIndexStatistic
02374 <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
02375 tableIndexStatistic
02376 <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending,
02377 "D" => descending, may be null if sort sequence is not supported;
02378 null when TYPE is tableIndexStatistic
02379 <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
02380 this is the number of rows in the table; otherwise, it is the
02381 number of unique values in the index.
02382 <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
02383 this is the number of pages used for the table, otherwise it
02384 is the number of pages used for the current index.
02385 <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
02386 (may be null)
02387 </OL>
02388
02389 @param catalog a catalog name; "" retrieves those without a
02390 catalog; null means drop catalog name from the selection criteria
02391 @param schema a schema name; "" retrieves those without a schema
02392 @param table a table name
02393 @param unique when true, return only indices for unique values;
02394 when false, return indices regardless of whether unique or not
02395 @param approximate when true, result is allowed to reflect approximate
02396 or out of data values; when false, results are requested to be
02397 accurate
02398 @return <code>ResultSet</code> - each row is an index column description
02399 }
02400 function TZInterbase6DatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
02401 const Schema: string; const Table: string; Unique: Boolean;
02402 Approximate: Boolean): IZResultSet;
02403 var
02404 SQL : string;
02405 begin
02406 Result := ConstructVirtualResultSet(IndexInfoColumnsDynArray);
02407
02408 SQL := ' SELECT I.RDB$RELATION_NAME, I.RDB$UNIQUE_FLAG, I.RDB$INDEX_NAME,'
02409 + ' ISGMT.RDB$FIELD_POSITION, ISGMT.RDB$FIELD_NAME, I.RDB$INDEX_TYPE,'
02410 + ' I.RDB$SEGMENT_COUNT, COUNT (DISTINCT P.RDB$PAGE_NUMBER) '
02411 + ' FROM RDB$INDICES I JOIN RDB$INDEX_SEGMENTS ISGMT ON'
02412 + ' I.RDB$INDEX_NAME = ISGMT.RDB$INDEX_NAME JOIN RDB$RELATIONS R ON'
02413 + ' (R.RDB$RELATION_NAME = I.RDB$RELATION_NAME) JOIN RDB$PAGES P ON'
02414 + ' (P.RDB$RELATION_ID = R.RDB$RELATION_ID AND P.RDB$PAGE_TYPE = 7'
02415 + ' OR P.RDB$PAGE_TYPE = 6) WHERE ';
02416 if Unique then
02417 SQL := SQL + ' I.RDB$UNIQUE_FLAG = 1 AND ';
02418 SQL := SQL + ' I.RDB$RELATION_NAME = ''' + Table + ''' GROUP BY '
02419 + ' I.RDB$INDEX_NAME, I.RDB$RELATION_NAME, I.RDB$UNIQUE_FLAG, '
02420 + ' ISGMT.RDB$FIELD_POSITION, ISGMT.RDB$FIELD_NAME, I.RDB$INDEX_TYPE, '
02421 + ' I.RDB$SEGMENT_COUNT ORDER BY 2,3,4';
02422
02423 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02424 begin
02425 while Next do
02426 begin
02427 Result.MoveToInsertRow;
02428 Result.UpdateNull(1); //TABLE_CAT
02429 Result.UpdateNull(2); //TABLE_SCHEM
02430 Result.UpdateString(3, GetString(1)); //TABLE_NAME, RDB$RELATION_NAME
02431 Result.UpdateBoolean(4, not GetBoolean(2)); //NON_UNIQUE, RDB$UNIQUE_FLAG
02432 Result.UpdateNull(5); //INDEX_QUALIFIER
02433 Result.UpdateString(6, GetString(3)); //INDEX_NAME, RDB$INDEX_NAME
02434 Result.UpdateInt(7, Ord(ntNoNulls)); //TYPE
02435 Result.UpdateInt(8, GetInt(4) + 1); //ORDINAL_POSITION, RDB$FIELD_POSITION
02436 Result.UpdateString(9, GetString(5)); //COLUMN_NAME, RDB$FIELD_NAME
02437 Result.UpdateNull(10); //ASC_OR_DESC
02438 Result.UpdateNull(11); //CARDINALITY
02439 Result.UpdateInt(12, GetInt(7)); //PAGES, RDB$SEGMENT_COUNT
02440 Result.UpdateNull(13); //FILTER_CONDITION
02441 Result.InsertRow;
02442 end;
02443 Close;
02444 end;
02445 end;
02446
02447 function TZInterbase6DatabaseMetadata.UncachedGetSequences(const Catalog, SchemaPattern,
02448 SequenceNamePattern: string): IZResultSet;
02449 var
02450 SQL: string;
02451 LSequenceNamePattern: string;
02452 begin
02453 Result := ConstructVirtualResultSet(SequenceColumnsDynArray);
02454
02455 LSequenceNamePattern := ConstructNameCondition(SequenceNamePattern,
02456 'RDB$GENERATOR_NAME');
02457
02458 SQL := ' SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS ' +
02459 'WHERE (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)';
02460
02461 if LSequenceNamePattern <> '' then
02462 SQL := SQL + ' AND ' + LSequenceNamePattern;
02463
02464 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02465 begin
02466 while Next do
02467 begin
02468 Result.MoveToInsertRow;
02469 Result.UpdateNull(1);
02470 Result.UpdateNull(2);
02471 Result.UpdateString(3, GetString(1)); //RDB$GENERATOR_NAME
02472 Result.InsertRow;
02473 end;
02474 Close;
02475 end;
02476 end;
02477
02478 {**
02479 Does the database support the given result set type?
02480 @param type defined in <code>java.sql.ResultSet</code>
02481 @return <code>true</code> if so; <code>false</code> otherwise
02482 }
02483 function TZInterbase6DatabaseMetadata.SupportsResultSetType(
02484 _Type: TZResultSetType): Boolean;
02485 begin
02486 Result := _Type = rtScrollInsensitive;
02487 end;
02488
02489 {**
02490 Does the database support the concurrency type in combination
02491 with the given result set type?
02492
02493 @param type defined in <code>java.sql.ResultSet</code>
02494 @param concurrency type defined in <code>java.sql.ResultSet</code>
02495 @return <code>true</code> if so; <code>false</code> otherwise
02496 }
02497 function TZInterbase6DatabaseMetadata.SupportsResultSetConcurrency(
02498 _Type: TZResultSetType; Concurrency: TZResultSetConcurrency): Boolean;
02499 begin
02500 Result := (_Type = rtScrollInsensitive) and (Concurrency = rcReadOnly);
02501 end;
02502
02503 {**
02504 Gets a privilege name.
02505 @param Interbase privilege name
02506 @returns a JDBC privilege name.
02507 }
02508 function TZInterbase6DatabaseMetadata.GetPrivilege(Privilege: string): string;
02509 begin
02510 if Privilege = 'S' then
02511 Result := 'SELECT'
02512 else if Privilege = 'I' then
02513 Result := 'INSERT'
02514 else if Privilege = 'U' then
02515 Result := 'UPDATE'
02516 else if Privilege = 'D' then
02517 Result := 'DELETE'
02518 else if Privilege = 'R' then
02519 Result := 'REFERENCE'
02520 else Result := '';
02521 end;
02522
02523 {**
02524 Takes a name patternand column name and retuen an appropriate SQL clause
02525 @param Pattern a sql pattren
02526 @parma Column a sql column name
02527 @return processed string for query
02528 }
02529 function TZInterbase6DatabaseMetadata.ConstructNameCondition(
02530 Pattern, Column: string): string;
02531 const
02532 Spaces = '';
02533 var
02534 StrippedPattern: string;
02535 begin
02536 if (Length(Pattern) > 2 * 31) then
02537 raise EZSQLException.Create(SPattern2Long);
02538
02539 if (Pattern = '%') or (Pattern = '') then Exit;
02540
02541 if HasNoWildcards(Pattern) then
02542 begin
02543 StrippedPattern := StripEscape(Pattern);
02544 Result := Format('%s = ''%s''', [Column, StrippedPattern]);
02545 end
02546 else
02547 begin
02548 Result := Format('%s || ''%s'' like ''%s%s%%''',
02549 [Column, Spaces, Pattern, Spaces]);
02550 end;
02551 end;
02552
02553 {**
02554 Check what pattern do not contain wildcards
02555 @param Pattern a sql pattern
02556 @return if pattern contain wildcards return true otherwise false
02557 }
02558 function TZInterbase6DatabaseMetadata.HasNoWildcards(
02559 const Pattern: string): Boolean;
02560 var
02561 I: Integer;
02562 PreviousChar: string[1];
02563 PreviousCharWasEscape: Boolean;
02564 EscapeChar : string;
02565 WildcardsSet:TZWildcardsSet;
02566 begin
02567 Result := False;
02568 PreviousChar := '';
02569 PreviousCharWasEscape := False;
02570 EscapeChar:=GetSearchStringEscape;
02571 WildcardsSet:=GetWildcardsSet;
02572 for I := 1 to Length(Pattern) do
02573 begin
02574 if (not PreviousCharWasEscape) and (Pattern[I] in WildcardsSet) then
02575 Exit;
02576
02577 PreviousCharWasEscape := (Pattern[I] = EscapeChar) and (PreviousChar <> EscapeChar);
02578 if (PreviousCharWasEscape) and (Pattern[I]=EscapeChar) then
02579 PreviousChar := ''
02580 else
02581 PreviousChar := Pattern[I];
02582 end;
02583 Result := True;
02584 end;
02585
02586 {**
02587 Remove escapes from pattren string
02588 @param Pattern a sql pattern
02589 @return string without escapes
02590 }
02591 function TZInterbase6DatabaseMetadata.StripEscape(
02592 const Pattern: string): string;
02593 var
02594 I: Integer;
02595 PreviousChar: string[1];
02596 EscapeChar : string;
02597 begin
02598 PreviousChar := '';
02599 Result := '';
02600 EscapeChar:=GetSearchStringEscape;
02601 for I := 1 to Length(Pattern) do
02602 begin
02603 if (Pattern[i]<>EscapeChar) then
02604 begin
02605 Result := Result + Pattern[I];
02606 PreviousChar := Pattern[I];
02607 end else
02608 begin
02609 if (PreviousChar = EscapeChar) then
02610 begin
02611 Result := Result + Pattern[I];
02612 PreviousChar := '';
02613 end else
02614 PreviousChar := Pattern[i];
02615 end;
02616 end;
02617 end;
02618
02619 {**
02620 Gets the version of the server.
02621 @returns the version of the server.
02622 }
02623 function TZInterbase6DatabaseMetadata.GetServerVersion: string;
02624 begin
02625 if FServerVersion = '' then
02626 begin
02627 FServerVersion := GetVersion(FIBConnection.GetPlainDriver,
02628 FIBConnection.GetDBHandle);
02629 end;
02630 Result := FServerVersion;
02631 end;
02632
02633 end.