00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { Oracle Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
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 ZDbcOracleMetadata;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 {$IFNDEF VER130BELOW}
00062 Types,
00063 {$ENDIF}
00064 Classes, SysUtils, ZSysUtils, ZDbcIntfs, ZDbcMetadata,
00065 ZCompatibility, ZDbcOracleUtils, ZDbcConnection;
00066
00067 type
00068
00069 {** Implements Oracle Database Metadata. }
00070 TZOracleDatabaseMetadata = class(TZAbstractDatabaseMetadata)
00071 private
00072 FDatabase: string;
00073 protected
00074 function UncachedGetTables(const Catalog: string; const SchemaPattern: string;
00075 const TableNamePattern: string; const Types: TStringDynArray): IZResultSet; override;
00076 function UncachedGetSchemas: IZResultSet; override;
00077
00078 function UncachedGetTableTypes: IZResultSet; override;
00079 function UncachedGetColumns(const Catalog: string; const SchemaPattern: string;
00080 const TableNamePattern: string; const ColumnNamePattern: string): IZResultSet; override;
00081 function UncachedGetTablePrivileges(const Catalog: string; const SchemaPattern: string;
00082 const TableNamePattern: string): IZResultSet; override;
00083 function UncachedGetColumnPrivileges(const Catalog: string; const Schema: string;
00084 const Table: string; const ColumnNamePattern: string): IZResultSet; override;
00085 function UncachedGetPrimaryKeys(const Catalog: string; const Schema: string;
00086 const Table: string): IZResultSet; override;
00087
00088
00089
00090
00091
00092
00093
00094 function UncachedGetIndexInfo(const Catalog: string; const Schema: string; const Table: string;
00095 Unique: Boolean; Approximate: Boolean): IZResultSet; override;
00096
00097
00098
00099
00100
00101
00102
00103
00104
00105
00106
00107
00108 public
00109 constructor Create(Connection: TZAbstractConnection; Url: string;
00110 Info: TStrings);
00111 destructor Destroy; override;
00112
00113 function GetDatabaseProductName: string; override;
00114 function GetDatabaseProductVersion: string; override;
00115 function GetDriverName: string; override;
00116 function GetDriverMajorVersion: Integer; override;
00117 function GetDriverMinorVersion: Integer; override;
00118 function UsesLocalFilePerTable: Boolean; override;
00119 function SupportsMixedCaseIdentifiers: Boolean; override;
00120 function StoresUpperCaseIdentifiers: Boolean; override;
00121 function StoresLowerCaseIdentifiers: Boolean; override;
00122 function StoresMixedCaseIdentifiers: Boolean; override;
00123 function SupportsMixedCaseQuotedIdentifiers: Boolean; override;
00124 function StoresUpperCaseQuotedIdentifiers: Boolean; override;
00125 function StoresLowerCaseQuotedIdentifiers: Boolean; override;
00126 function StoresMixedCaseQuotedIdentifiers: Boolean; override;
00127 function GetSQLKeywords: string; override;
00128 function GetNumericFunctions: string; override;
00129 function GetStringFunctions: string; override;
00130 function GetSystemFunctions: string; override;
00131 function GetTimeDateFunctions: string; override;
00132 function GetSearchStringEscape: string; override;
00133 function GetExtraNameCharacters: string; override;
00134
00135 function SupportsExpressionsInOrderBy: Boolean; override;
00136 function SupportsOrderByUnrelated: Boolean; override;
00137 function SupportsGroupBy: Boolean; override;
00138 function SupportsGroupByUnrelated: Boolean; override;
00139 function SupportsGroupByBeyondSelect: Boolean; override;
00140 function SupportsIntegrityEnhancementFacility: Boolean; override;
00141 function GetSchemaTerm: string; override;
00142 function GetProcedureTerm: string; override;
00143 function GetCatalogTerm: string; override;
00144 function GetCatalogSeparator: string; override;
00145 function SupportsSchemasInDataManipulation: Boolean; override;
00146 function SupportsSchemasInProcedureCalls: Boolean; override;
00147 function SupportsSchemasInTableDefinitions: Boolean; override;
00148 function SupportsSchemasInIndexDefinitions: Boolean; override;
00149 function SupportsSchemasInPrivilegeDefinitions: Boolean; override;
00150 function SupportsCatalogsInDataManipulation: Boolean; override;
00151 function SupportsCatalogsInProcedureCalls: Boolean; override;
00152 function SupportsCatalogsInTableDefinitions: Boolean; override;
00153 function SupportsCatalogsInIndexDefinitions: Boolean; override;
00154 function SupportsCatalogsInPrivilegeDefinitions: Boolean; override;
00155 function SupportsPositionedDelete: Boolean; override;
00156 function SupportsPositionedUpdate: Boolean; override;
00157 function SupportsSelectForUpdate: Boolean; override;
00158 function SupportsStoredProcedures: Boolean; override;
00159 function SupportsSubqueriesInComparisons: Boolean; override;
00160 function SupportsSubqueriesInExists: Boolean; override;
00161 function SupportsSubqueriesInIns: Boolean; override;
00162 function SupportsSubqueriesInQuantifieds: Boolean; override;
00163 function SupportsCorrelatedSubqueries: Boolean; override;
00164 function SupportsUnion: Boolean; override;
00165 function SupportsUnionAll: Boolean; override;
00166 function SupportsOpenCursorsAcrossCommit: Boolean; override;
00167 function SupportsOpenCursorsAcrossRollback: Boolean; override;
00168 function SupportsOpenStatementsAcrossCommit: Boolean; override;
00169 function SupportsOpenStatementsAcrossRollback: Boolean; override;
00170
00171 function GetMaxBinaryLiteralLength: Integer; override;
00172 function GetMaxCharLiteralLength: Integer; override;
00173 function GetMaxColumnNameLength: Integer; override;
00174 function GetMaxColumnsInGroupBy: Integer; override;
00175 function GetMaxColumnsInIndex: Integer; override;
00176 function GetMaxColumnsInOrderBy: Integer; override;
00177 function GetMaxColumnsInSelect: Integer; override;
00178 function GetMaxColumnsInTable: Integer; override;
00179 function GetMaxConnections: Integer; override;
00180 function GetMaxCursorNameLength: Integer; override;
00181 function GetMaxIndexLength: Integer; override;
00182 function GetMaxSchemaNameLength: Integer; override;
00183 function GetMaxProcedureNameLength: Integer; override;
00184 function GetMaxCatalogNameLength: Integer; override;
00185 function GetMaxRowSize: Integer; override;
00186 function DoesMaxRowSizeIncludeBlobs: Boolean; override;
00187 function GetMaxStatementLength: Integer; override;
00188 function GetMaxStatements: Integer; override;
00189 function GetMaxTableNameLength: Integer; override;
00190 function GetMaxTablesInSelect: Integer; override;
00191 function GetMaxUserNameLength: Integer; override;
00192
00193 function GetDefaultTransactionIsolation: TZTransactIsolationLevel; override;
00194 function SupportsTransactions: Boolean; override;
00195 function SupportsTransactionIsolationLevel(Level: TZTransactIsolationLevel):
00196 Boolean; override;
00197 function SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
00198 override;
00199 function SupportsDataManipulationTransactionsOnly: Boolean; override;
00200 function DataDefinitionCausesTransactionCommit: Boolean; override;
00201 function DataDefinitionIgnoredInTransactions: Boolean; override;
00202
00203 function SupportsResultSetType(_Type: TZResultSetType): Boolean; override;
00204 function SupportsResultSetConcurrency(_Type: TZResultSetType;
00205 Concurrency: TZResultSetConcurrency): Boolean; override;
00206 end;
00207
00208 implementation
00209
00210 uses
00211 ZDbcUtils;
00212
00213 { TZOracleDatabaseMetadata }
00214
00215 {**
00216 Constructs this object and assignes the main properties.
00217 @param Connection a database connection object.
00218 @param Url a database connection url string.
00219 @param Info an extra connection properties.
00220 }
00221 constructor TZOracleDatabaseMetadata.Create(
00222 Connection: TZAbstractConnection; Url: string; Info: TStrings);
00223 var
00224 TempInfo: TStrings;
00225 HostName, UserName, Password: string;
00226 Port: Integer;
00227 begin
00228 inherited Create(Connection, Url, Info);
00229
00230 TempInfo := TStringList.Create;
00231 try
00232 ResolveDatabaseUrl(Url, Info, HostName, Port, FDatabase,
00233 UserName, Password, TempInfo);
00234 finally
00235 TempInfo.Free;
00236 end;
00237 end;
00238
00239 {**
00240 Destroys this object and cleanups the memory.
00241 }
00242 destructor TZOracleDatabaseMetadata.Destroy;
00243 begin
00244 inherited Destroy;
00245 end;
00246
00247
00248
00249
00250 {**
00251 What's the name of this database product?
00252 @return database product name
00253 }
00254 function TZOracleDatabaseMetadata.GetDatabaseProductName: string;
00255 begin
00256 Result := 'Oracle';
00257 end;
00258
00259 {**
00260 What's the version of this database product?
00261 @return database version
00262 }
00263 function TZOracleDatabaseMetadata.GetDatabaseProductVersion: string;
00264 begin
00265 Result := '';
00266 end;
00267
00268 {**
00269 What's the name of this JDBC driver?
00270 @return JDBC driver name
00271 }
00272 function TZOracleDatabaseMetadata.GetDriverName: string;
00273 begin
00274 Result := 'Zeos Database Connectivity Driver for Oracle';
00275 end;
00276
00277 {**
00278 What's this JDBC driver's major version number?
00279 @return JDBC driver major version
00280 }
00281 function TZOracleDatabaseMetadata.GetDriverMajorVersion: Integer;
00282 begin
00283 Result := 1;
00284 end;
00285
00286 {**
00287 What's this JDBC driver's minor version number?
00288 @return JDBC driver minor version number
00289 }
00290 function TZOracleDatabaseMetadata.GetDriverMinorVersion: Integer;
00291 begin
00292 Result := 0;
00293 end;
00294
00295 {**
00296 Does the database use a file for each table?
00297 @return true if the database uses a local file for each table
00298 }
00299 function TZOracleDatabaseMetadata.UsesLocalFilePerTable: Boolean;
00300 begin
00301 Result := False;
00302 end;
00303
00304 {**
00305 Does the database treat mixed case unquoted SQL identifiers as
00306 case sensitive and as a result store them in mixed case?
00307 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return false.
00308 @return <code>true</code> if so; <code>false</code> otherwise
00309 }
00310 function TZOracleDatabaseMetadata.SupportsMixedCaseIdentifiers: Boolean;
00311 begin
00312 Result := False;
00313 end;
00314
00315 {**
00316 Does the database treat mixed case unquoted SQL identifiers as
00317 case insensitive and store them in upper case?
00318 @return <code>true</code> if so; <code>false</code> otherwise
00319 }
00320 function TZOracleDatabaseMetadata.StoresUpperCaseIdentifiers: Boolean;
00321 begin
00322 Result := True;
00323 end;
00324
00325 {**
00326 Does the database treat mixed case unquoted SQL identifiers as
00327 case insensitive and store them in lower case?
00328 @return <code>true</code> if so; <code>false</code> otherwise
00329 }
00330 function TZOracleDatabaseMetadata.StoresLowerCaseIdentifiers: Boolean;
00331 begin
00332 Result := False;
00333 end;
00334
00335 {**
00336 Does the database treat mixed case unquoted SQL identifiers as
00337 case insensitive and store them in mixed case?
00338 @return <code>true</code> if so; <code>false</code> otherwise
00339 }
00340 function TZOracleDatabaseMetadata.StoresMixedCaseIdentifiers: Boolean;
00341 begin
00342 Result := False;
00343 end;
00344
00345 {**
00346 Does the database treat mixed case quoted SQL identifiers as
00347 case sensitive and as a result store them in mixed case?
00348 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return true.
00349 @return <code>true</code> if so; <code>false</code> otherwise
00350 }
00351 function TZOracleDatabaseMetadata.SupportsMixedCaseQuotedIdentifiers: Boolean;
00352 begin
00353 Result := True;
00354 end;
00355
00356 {**
00357 Does the database treat mixed case quoted SQL identifiers as
00358 case insensitive and store them in upper case?
00359 @return <code>true</code> if so; <code>false</code> otherwise
00360 }
00361 function TZOracleDatabaseMetadata.StoresUpperCaseQuotedIdentifiers: Boolean;
00362 begin
00363 Result := False;
00364 end;
00365
00366 {**
00367 Does the database treat mixed case quoted SQL identifiers as
00368 case insensitive and store them in lower case?
00369 @return <code>true</code> if so; <code>false</code> otherwise
00370 }
00371 function TZOracleDatabaseMetadata.StoresLowerCaseQuotedIdentifiers: Boolean;
00372 begin
00373 Result := False;
00374 end;
00375
00376 {**
00377 Does the database treat mixed case quoted SQL identifiers as
00378 case insensitive and store them in mixed case?
00379 @return <code>true</code> if so; <code>false</code> otherwise
00380 }
00381 function TZOracleDatabaseMetadata.StoresMixedCaseQuotedIdentifiers: Boolean;
00382 begin
00383 Result := True;
00384 end;
00385
00386 {**
00387 Gets a comma-separated list of all a database's SQL keywords
00388 that are NOT also SQL92 keywords.
00389 @return the list
00390 }
00391 function TZOracleDatabaseMetadata.GetSQLKeywords: string;
00392 begin
00393 Result := 'ACCESS,ADD,ALTER,AUDIT,CLUSTER,COLUMN,COMMENT,COMPRESS,CONNECT,'
00394 + 'DATE,DROP,EXCLUSIVE,FILE,IDENTIFIED,IMMEDIATE,INCREMENT,INDEX,INITIAL,'
00395 + 'INTERSECT,LEVEL,LOCK,LONG,MAXEXTENTS,MINUS,MODE,NOAUDIT,NOCOMPRESS,'
00396 + 'NOWAIT,NUMBER,OFFLINE,ONLINE,PCTFREE,PRIOR';
00397 end;
00398
00399 {**
00400 Gets a comma-separated list of math functions. These are the
00401 X/Open CLI math function names used in the JDBC function escape
00402 clause.
00403 @return the list
00404 }
00405 function TZOracleDatabaseMetadata.GetNumericFunctions: string;
00406 begin
00407 Result := 'ABS,ACOS,ASIN,ATAN,ATAN2,CEILING,COS,EXP,FLOOR,LOG,LOG10,MOD,PI,'
00408 + 'POWER,ROUND,SIGN,SIN,SQRT,TAN,TRUNCATE';
00409 end;
00410
00411 {**
00412 Gets a comma-separated list of string functions. These are the
00413 X/Open CLI string function names used in the JDBC function escape
00414 clause.
00415 @return the list
00416 }
00417 function TZOracleDatabaseMetadata.GetStringFunctions: string;
00418 begin
00419 Result := 'ASCII,CHAR,CONCAT,LCASE,LENGTH,LTRIM,REPLACE,RTRIM,SOUNDEX,'
00420 + 'SUBSTRING,UCASE';
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 TZOracleDatabaseMetadata.GetSystemFunctions: string;
00430 begin
00431 Result := 'USER';
00432 end;
00433
00434 {**
00435 Gets a comma-separated list of time and date functions.
00436 @return the list
00437 }
00438 function TZOracleDatabaseMetadata.GetTimeDateFunctions: string;
00439 begin
00440 Result := 'CURDATE,CURTIME,DAYOFMONTH,HOUR,MINUTE,MONTH,NOW,SECOND,YEAR';
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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.SupportsExpressionsInOrderBy: Boolean;
00477 begin
00478 Result := True;
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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.SupportsIntegrityEnhancementFacility: Boolean;
00523 begin
00524 Result := True;
00525 end;
00526
00527 {**
00528 What's the database vendor's preferred term for "schema"?
00529 @return the vendor term
00530 }
00531 function TZOracleDatabaseMetadata.GetSchemaTerm: string;
00532 begin
00533 Result := 'schema';
00534 end;
00535
00536 {**
00537 What's the database vendor's preferred term for "procedure"?
00538 @return the vendor term
00539 }
00540 function TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.SupportsSchemasInDataManipulation: Boolean;
00568 begin
00569 Result := True;
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 TZOracleDatabaseMetadata.SupportsSchemasInProcedureCalls: Boolean;
00577 begin
00578 Result := True;
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 TZOracleDatabaseMetadata.SupportsSchemasInTableDefinitions: Boolean;
00586 begin
00587 Result := True;
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 TZOracleDatabaseMetadata.SupportsSchemasInIndexDefinitions: Boolean;
00595 begin
00596 Result := True;
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 TZOracleDatabaseMetadata.SupportsSchemasInPrivilegeDefinitions: Boolean;
00604 begin
00605 Result := True;
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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.SupportsPositionedDelete: Boolean;
00658 begin
00659 Result := False;
00660 end;
00661
00662 {**
00663 Is positioned UPDATE supported?
00664 @return <code>true</code> if so; <code>false</code> otherwise
00665 }
00666 function TZOracleDatabaseMetadata.SupportsPositionedUpdate: Boolean;
00667 begin
00668 Result := False;
00669 end;
00670
00671 {**
00672 Is SELECT for UPDATE supported?
00673 @return <code>true</code> if so; <code>false</code> otherwise
00674 }
00675 function TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.SupportsSubqueriesInIns: Boolean;
00716 begin
00717 Result := True;
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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.SupportsOpenStatementsAcrossCommit: Boolean;
00784 begin
00785 Result := False;
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 TZOracleDatabaseMetadata.SupportsOpenStatementsAcrossRollback: Boolean;
00794 begin
00795 Result := False;
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 TZOracleDatabaseMetadata.GetMaxBinaryLiteralLength: Integer;
00810 begin
00811 Result := 1000;
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 TZOracleDatabaseMetadata.GetMaxCharLiteralLength: Integer;
00820 begin
00821 Result := 2000;
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 TZOracleDatabaseMetadata.GetMaxColumnNameLength: Integer;
00830 begin
00831 Result := 30;
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 TZOracleDatabaseMetadata.GetMaxColumnsInGroupBy: Integer;
00840 begin
00841 Result := 0;
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 TZOracleDatabaseMetadata.GetMaxColumnsInIndex: Integer;
00850 begin
00851 Result := 32;
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 TZOracleDatabaseMetadata.GetMaxColumnsInOrderBy: Integer;
00860 begin
00861 Result := 0;
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 TZOracleDatabaseMetadata.GetMaxColumnsInSelect: Integer;
00870 begin
00871 Result := 0;
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 TZOracleDatabaseMetadata.GetMaxColumnsInTable: Integer;
00880 begin
00881 Result := 1000;
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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.GetMaxCursorNameLength: Integer;
00900 begin
00901 Result := 0;
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 TZOracleDatabaseMetadata.GetMaxIndexLength: Integer;
00912 begin
00913 Result := 0;
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 TZOracleDatabaseMetadata.GetMaxSchemaNameLength: Integer;
00922 begin
00923 Result := 30;
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 TZOracleDatabaseMetadata.GetMaxProcedureNameLength: Integer;
00932 begin
00933 Result := 30;
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 TZOracleDatabaseMetadata.GetMaxCatalogNameLength: Integer;
00942 begin
00943 Result := 0;
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 TZOracleDatabaseMetadata.GetMaxRowSize: Integer;
00952 begin
00953 Result := 2000;
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 TZOracleDatabaseMetadata.DoesMaxRowSizeIncludeBlobs: Boolean;
00962 begin
00963 Result := True;
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 TZOracleDatabaseMetadata.GetMaxStatementLength: Integer;
00972 begin
00973 Result := 65535;
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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.GetMaxTableNameLength: Integer;
00993 begin
00994 Result := 30;
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 TZOracleDatabaseMetadata.GetMaxTablesInSelect: Integer;
01003 begin
01004 Result := 0;
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 TZOracleDatabaseMetadata.GetMaxUserNameLength: Integer;
01013 begin
01014 Result := 30;
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 TZOracleDatabaseMetadata.GetDefaultTransactionIsolation:
01026 TZTransactIsolationLevel;
01027 begin
01028 Result := tiReadCommitted;
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 TZOracleDatabaseMetadata.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 TZOracleDatabaseMetadata.SupportsTransactionIsolationLevel(
01048 Level: TZTransactIsolationLevel): Boolean;
01049 begin
01050 Result := True;
01051 end;
01052
01053 {**
01054 Are both data definition and data manipulation statements
01055 within a transaction supported?
01056 @return <code>true</code> if so; <code>false</code> otherwise
01057 }
01058 function TZOracleDatabaseMetadata.
01059 SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
01060 begin
01061 Result := True;
01062 end;
01063
01064 {**
01065 Are only data manipulation statements within a transaction
01066 supported?
01067 @return <code>true</code> if so; <code>false</code> otherwise
01068 }
01069 function TZOracleDatabaseMetadata.
01070 SupportsDataManipulationTransactionsOnly: Boolean;
01071 begin
01072 Result := True;
01073 end;
01074
01075 {**
01076 Does a data definition statement within a transaction force the
01077 transaction to commit?
01078 @return <code>true</code> if so; <code>false</code> otherwise
01079 }
01080 function TZOracleDatabaseMetadata.DataDefinitionCausesTransactionCommit: Boolean;
01081 begin
01082 Result := True;
01083 end;
01084
01085 {**
01086 Is a data definition statement within a transaction ignored?
01087 @return <code>true</code> if so; <code>false</code> otherwise
01088 }
01089 function TZOracleDatabaseMetadata.DataDefinitionIgnoredInTransactions: Boolean;
01090 begin
01091 Result := False;
01092 end;
01093
01094 {**
01095 Gets a description of tables available in a catalog.
01096
01097 <P>Only table descriptions matching the catalog, schema, table
01098 name and type criteria are returned. They are ordered by
01099 TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
01100
01101 <P>Each table description has the following columns:
01102 <OL>
01103 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01104 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01105 <LI><B>TABLE_NAME</B> String => table name
01106 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01107 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01108 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01109 <LI><B>REMARKS</B> String => explanatory comment on the table
01110 </OL>
01111
01112 <P><B>Note:</B> Some databases may not return information for
01113 all tables.
01114
01115 @param catalog a catalog name; "" retrieves those without a
01116 catalog; null means drop catalog name from the selection criteria
01117 @param schemaPattern a schema name pattern; "" retrieves those
01118 without a schema
01119 @param tableNamePattern a table name pattern
01120 @param types a list of table types to include; null returns all types
01121 @return <code>ResultSet</code> - each row is a table description
01122 @see #getSearchStringEscape
01123 }
01124 function TZOracleDatabaseMetadata.UncachedGetTables(const Catalog: string;
01125 const SchemaPattern: string; const TableNamePattern: string;
01126 const Types: TStringDynArray): IZResultSet;
01127 var
01128 PartSQL, SQL: string;
01129
01130 function IncludedType(const TypeName: string): Boolean;
01131 var I: Integer;
01132 begin
01133 Result := False;
01134 for I := Low(Types) to High(Types) do
01135 Result := Result or (UpperCase(Types[I]) = TypeName);
01136 Result := Result or (Length(Types) = 0);
01137 end;
01138
01139 begin
01140 if IncludedType('TABLE') then
01141 begin
01142 SQL := 'SELECT NULL AS TABLE_CAT, OWNER AS TABLE_SCHEM, TABLE_NAME,'
01143 + ' ''TABLE'' AS TABLE_TYPE, NULL AS REMARKS FROM SYS.ALL_TABLES'
01144 + ' WHERE OWNER LIKE ''' + ToLikeString(SchemaPattern) + ''' AND TABLE_NAME LIKE '''
01145 + ToLikeString(TableNamePattern) + '''';
01146 end else
01147 SQL := '';
01148
01149 if IncludedType('SYNONYM') then
01150 begin
01151 PartSQL := 'SELECT NULL AS TABLE_CAT, OWNER AS TABLE_SCHEM,'
01152 + ' SYNONYM_NAME AS TABLE_NAME, ''SYNONYM'' AS TABLE_TYPE,'
01153 + ' NULL AS REMARKS FROM SYS.ALL_SYNONYMS WHERE OWNER LIKE '''
01154 + ToLikeString(SchemaPattern) + ''' AND SYNONYM_NAME LIKE ''' + ToLikeString(TableNamePattern) + '''';
01155
01156 if SQL <> '' then
01157 SQL := SQL + ' UNION ';
01158 SQL := SQL + PartSQL;
01159 end;
01160
01161 if IncludedType('VIEW') then
01162 begin
01163 PartSQL := 'SELECT NULL AS TABLE_CAT, OWNER AS TABLE_SCHEM,'
01164 + ' VIEW_NAME AS TABLE_NAME, ''VIEW'' AS TABLE_TYPE,'
01165 + ' NULL AS REMARKS FROM SYS.ALL_VIEWS WHERE OWNER LIKE '''
01166 + ToLikeString(SchemaPattern) + ''' AND VIEW_NAME LIKE ''' + ToLikeString(TableNamePattern) + '''';
01167
01168 if SQL <> '' then
01169 SQL := SQL + ' UNION ';
01170 SQL := SQL + PartSQL;
01171 end;
01172
01173 if IncludedType('SEQUENCE') then
01174 begin
01175 PartSQL := 'SELECT NULL AS TABLE_CAT, SEQUENCE_OWNER AS TABLE_SCHEM,'
01176 + ' SEQUENCE_NAME AS TABLE_NAME, ''SEQUENCE'' AS TABLE_TYPE,'
01177 + ' NULL AS REMARKS FROM SYS.ALL_SEQUENCES WHERE SEQUENCE_OWNER LIKE '''
01178 + ToLikeString(SchemaPattern) + ''' AND SEQUENCE_NAME LIKE ''' + ToLikeString(TableNamePattern) + '''';
01179
01180 if SQL <> '' then
01181 SQL := SQL + ' UNION ';
01182 SQL := SQL + PartSQL;
01183 end;
01184
01185 Result := CopyToVirtualResultSet(
01186 GetConnection.CreateStatement.ExecuteQuery(SQL),
01187 ConstructVirtualResultSet(TableColumnsDynArray));
01188 end;
01189
01190 {**
01191 Gets the schema names available in this database. The results
01192 are ordered by schema name.
01193
01194 <P>The schema column is:
01195 <OL>
01196 <LI><B>TABLE_SCHEM</B> String => schema name
01197 </OL>
01198
01199 @return <code>ResultSet</code> - each row has a single String column that is a
01200 schema name
01201 }
01202 function TZOracleDatabaseMetadata.UncachedGetSchemas: IZResultSet;
01203 begin
01204 Result := CopyToVirtualResultSet(
01205 GetConnection.CreateStatement.ExecuteQuery(
01206 'SELECT USERNAME AS TABLE_SCHEM FROM SYS.ALL_USERS'),
01207 ConstructVirtualResultSet(SchemaColumnsDynArray));
01208 end;
01209
01210 {**
01211 Gets the table types available in this database. The results
01212 are ordered by table type.
01213
01214 <P>The table type is:
01215 <OL>
01216 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01217 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01218 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01219 </OL>
01220
01221 @return <code>ResultSet</code> - each row has a single String column that is a
01222 table type
01223 }
01224 function TZOracleDatabaseMetadata.UncachedGetTableTypes: IZResultSet;
01225 const
01226 TableTypeCount = 4;
01227 Types: array [1..TableTypeCount] of string = (
01228 'TABLE', 'SYNONYM', 'VIEW', 'SEQUENCE'
01229 );
01230 var
01231 I: Integer;
01232 begin
01233 Result := ConstructVirtualResultSet(TableTypeColumnsDynArray);
01234 for I := 1 to TableTypeCount do
01235 begin
01236 Result.MoveToInsertRow;
01237 Result.UpdateString(1, Types[I]);
01238 Result.InsertRow;
01239 end;
01240 end;
01241
01242 {**
01243 Gets a description of table columns available in
01244 the specified catalog.
01245
01246 <P>Only column descriptions matching the catalog, schema, table
01247 and column name criteria are returned. They are ordered by
01248 TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
01249
01250 <P>Each column description has the following columns:
01251 <OL>
01252 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01253 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01254 <LI><B>TABLE_NAME</B> String => table name
01255 <LI><B>COLUMN_NAME</B> String => column name
01256 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01257 <LI><B>TYPE_NAME</B> String => Data source dependent type name,
01258 for a UDT the type name is fully qualified
01259 <LI><B>COLUMN_SIZE</B> int => column size. For char or date
01260 types this is the maximum number of characters, for numeric or
01261 decimal types this is precision.
01262 <LI><B>BUFFER_LENGTH</B> is not used.
01263 <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
01264 <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
01265 <LI><B>NULLABLE</B> int => is NULL allowed?
01266 <UL>
01267 <LI> columnNoNulls - might not allow NULL values
01268 <LI> columnNullable - definitely allows NULL values
01269 <LI> columnNullableUnknown - nullability unknown
01270 </UL>
01271 <LI><B>REMARKS</B> String => comment describing column (may be null)
01272 <LI><B>COLUMN_DEF</B> String => default value (may be null)
01273 <LI><B>SQL_DATA_TYPE</B> int => unused
01274 <LI><B>SQL_DATETIME_SUB</B> int => unused
01275 <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
01276 maximum number of bytes in the column
01277 <LI><B>ORDINAL_POSITION</B> int => index of column in table
01278 (starting at 1)
01279 <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
01280 does not allow NULL values; "YES" means the column might
01281 allow NULL values. An empty string means nobody knows.
01282 </OL>
01283
01284 @param catalog a catalog name; "" retrieves those without a
01285 catalog; null means drop catalog name from the selection criteria
01286 @param schemaPattern a schema name pattern; "" retrieves those
01287 without a schema
01288 @param tableNamePattern a table name pattern
01289 @param columnNamePattern a column name pattern
01290 @return <code>ResultSet</code> - each row is a column description
01291 @see #getSearchStringEscape
01292 }
01293 function TZOracleDatabaseMetadata.UncachedGetColumns(const Catalog: string;
01294 const SchemaPattern: string; const TableNamePattern: string;
01295 const ColumnNamePattern: string): IZResultSet;
01296 var
01297 SQL: string;
01298 begin
01299 Result := ConstructVirtualResultSet(TableColColumnsDynArray);
01300
01301 SQL := 'SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE,'
01302 + ' DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL,'
01303 + ' DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE'
01304 + ' FROM SYS.ALL_TAB_COLUMNS'
01305 + ' WHERE OWNER LIKE ''' + ToLikeString(SchemaPattern) + ''' AND TABLE_NAME LIKE '''
01306 + ToLikeString(TableNamePattern) + ''' AND COLUMN_NAME LIKE '''
01307 + ToLikeString(ColumnNamePattern) + '''';
01308
01309 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01310 begin
01311 while Next do
01312 begin
01313 Result.MoveToInsertRow;
01314 Result.UpdateNull(1);
01315 Result.UpdateString(2, GetString(2));
01316 Result.UpdateString(3, GetString(3));
01317 Result.UpdateString(4, GetString(4));
01318 Result.UpdateInt(5, Ord(ConvertOracleTypeToSQLType(
01319 GetString(6), GetInt(9), GetInt(10))));
01320 Result.UpdateString(6, GetString(6));
01321 Result.UpdateInt(7, GetInt(7));
01322 Result.UpdateNull(8);
01323 Result.UpdateInt(9, GetInt(9));
01324 Result.UpdateInt(10, GetInt(10));
01325
01326 if UpperCase(GetString(11)) = 'N' then
01327 begin
01328 Result.UpdateInt(11, Ord(ntNoNulls));
01329 Result.UpdateString(18, 'NO');
01330 end
01331 else
01332 begin
01333 Result.UpdateInt(11, Ord(ntNullable));
01334 Result.UpdateString(18, 'YES');
01335 end;
01336
01337 Result.UpdateNull(12);
01338 Result.UpdateString(13, GetString(13));
01339 Result.UpdateNull(14);
01340 Result.UpdateNull(15);
01341 Result.UpdateNull(16);
01342 Result.UpdateInt(17, GetInt(17));
01343
01344 Result.UpdateNull(19); //AUTO_INCREMENT
01345 Result.UpdateBoolean(20, //CASE_SENSITIVE
01346 GetIdentifierConvertor.IsCaseSensitive(GetString(4)));
01347 Result.UpdateBoolean(21, True); //SEARCHABLE
01348 Result.UpdateBoolean(22, True); //WRITABLE
01349 Result.UpdateBoolean(23, True); //DEFINITELYWRITABLE
01350 Result.UpdateBoolean(24, False); //READONLY
01351
01352 Result.InsertRow;
01353 end;
01354 Close;
01355 end;
01356 end;
01357
01358 {**
01359 Gets a description of the access rights for a table's columns.
01360
01361 <P>Only privileges matching the column name criteria are
01362 returned. They are ordered by COLUMN_NAME and PRIVILEGE.
01363
01364 <P>Each privilige description has the following columns:
01365 <OL>
01366 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01367 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01368 <LI><B>TABLE_NAME</B> String => table name
01369 <LI><B>COLUMN_NAME</B> String => column name
01370 <LI><B>GRANTOR</B> => grantor of access (may be null)
01371 <LI><B>GRANTEE</B> String => grantee of access
01372 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01373 INSERT, UPDATE, REFRENCES, ...)
01374 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01375 to grant to others; "NO" if not; null if unknown
01376 </OL>
01377
01378 @param catalog a catalog name; "" retrieves those without a
01379 catalog; null means drop catalog name from the selection criteria
01380 @param schema a schema name; "" retrieves those without a schema
01381 @param table a table name
01382 @param columnNamePattern a column name pattern
01383 @return <code>ResultSet</code> - each row is a column privilege description
01384 @see #getSearchStringEscape
01385 }
01386 function TZOracleDatabaseMetadata.UncachedGetColumnPrivileges(const Catalog: string;
01387 const Schema: string; const Table: string; const ColumnNamePattern: string): IZResultSet;
01388 var
01389 SQL: string;
01390 begin
01391 SQL := 'SELECT NULL AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME,'
01392 + ' COLUMN_NAME, GRANTOR, GRANTEE, PRIVILEGE, GRANTABLE AS IS_GRANTABLE'
01393 + ' FROM SYS.ALL_COL_PRIVS WHERE';
01394 if Schema <> '' then
01395 SQL := SQL + ' TABLE_SCHEMA=''' + Schema + ''' AND';
01396 if Table <> '' then
01397 SQL := SQL + ' TABLE_NAME=''' + Table + ''' AND';
01398 SQL := SQL + ' COLUMN_NAME LIKE ''' + ToLikeString(ColumnNamePattern) + '''';
01399
01400 Result := CopyToVirtualResultSet(
01401 GetConnection.CreateStatement.ExecuteQuery(SQL),
01402 ConstructVirtualResultSet(TableColPrivColumnsDynArray));
01403 end;
01404
01405 {**
01406 Gets a description of the access rights for each table available
01407 in a catalog. Note that a table privilege applies to one or
01408 more columns in the table. It would be wrong to assume that
01409 this priviledge applies to all columns (this may be true for
01410 some systems but is not true for all.)
01411
01412 <P>Only privileges matching the schema and table name
01413 criteria are returned. They are ordered by TABLE_SCHEM,
01414 TABLE_NAME, and PRIVILEGE.
01415
01416 <P>Each privilige description has the following columns:
01417 <OL>
01418 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01419 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01420 <LI><B>TABLE_NAME</B> String => table name
01421 <LI><B>GRANTOR</B> => grantor of access (may be null)
01422 <LI><B>GRANTEE</B> String => grantee of access
01423 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01424 INSERT, UPDATE, REFRENCES, ...)
01425 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01426 to grant to others; "NO" if not; null if unknown
01427 </OL>
01428
01429 @param catalog a catalog name; "" retrieves those without a
01430 catalog; null means drop catalog name from the selection criteria
01431 @param schemaPattern a schema name pattern; "" retrieves those
01432 without a schema
01433 @param tableNamePattern a table name pattern
01434 @return <code>ResultSet</code> - each row is a table privilege description
01435 @see #getSearchStringEscape
01436 }
01437 function TZOracleDatabaseMetadata.UncachedGetTablePrivileges(const Catalog: string;
01438 const SchemaPattern: string; const TableNamePattern: string): IZResultSet;
01439 var
01440 SQL: string;
01441 begin
01442 SQL := 'SELECT NULL AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME,'
01443 + ' GRANTOR, GRANTEE, PRIVILEGE, GRANTABLE AS IS_GRANTABLE'
01444 + ' FROM SYS.ALL_TAB_PRIVS WHERE TABLE_SCHEMA LIKE ''' + ToLikeString(SchemaPattern)
01445 + ''' AND TABLE_NAME LIKE ''' + ToLikeString(TableNamePattern) + '''';
01446
01447 Result := CopyToVirtualResultSet(
01448 GetConnection.CreateStatement.ExecuteQuery(SQL),
01449 ConstructVirtualResultSet(TablePrivColumnsDynArray));
01450 end;
01451
01452 {**
01453 Gets a description of a table's primary key columns. They
01454 are ordered by COLUMN_NAME.
01455
01456 <P>Each primary key column description has the following columns:
01457 <OL>
01458 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01459 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01460 <LI><B>TABLE_NAME</B> String => table name
01461 <LI><B>COLUMN_NAME</B> String => column name
01462 <LI><B>KEY_SEQ</B> short => sequence number within primary key
01463 <LI><B>PK_NAME</B> String => primary key name (may be null)
01464 </OL>
01465
01466 @param catalog a catalog name; "" retrieves those without a
01467 catalog; null means drop catalog name from the selection criteria
01468 @param schema a schema name; "" retrieves those
01469 without a schema
01470 @param table a table name
01471 @return <code>ResultSet</code> - each row is a primary key column description
01472 @exception SQLException if a database access error occurs
01473 }
01474 function TZOracleDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
01475 const Schema: string; const Table: string): IZResultSet;
01476 var
01477 SQL: string;
01478 begin
01479 SQL := 'SELECT NULL AS TABLE_CAT, A.OWNER AS TABLE_SCHEM, A.TABLE_NAME,'
01480 + ' B.COLUMN_NAME, B.COLUMN_POSITION AS KEY_SEQ, A.INDEX_NAME AS PK_NAME'
01481 + ' FROM ALL_INDEXES A, ALL_IND_COLUMNS B'
01482 + ' WHERE A.OWNER=B.INDEX_OWNER AND A.INDEX_NAME=B.INDEX_NAME'
01483 + ' AND A.TABLE_OWNER=B.TABLE_OWNER AND A.TABLE_NAME=B.TABLE_NAME'
01484 + ' AND A.UNIQUENESS=''UNIQUE'' AND A.GENERATED=''Y'''
01485 + ' AND A.INDEX_NAME LIKE ''SYS_%''';
01486 if Schema <> '' then
01487 SQL := SQL + ' AND A.OWNER=''' + Schema + '''';
01488 if Table <> '' then
01489 SQL := SQL + ' AND A.TABLE_OWNER=''' + Table + '''';
01490 SQL := SQL + ' ORDER BY A.INDEX_NAME, B.COLUMN_POSITION';
01491
01492 Result := CopyToVirtualResultSet(
01493 GetConnection.CreateStatement.ExecuteQuery(SQL),
01494 ConstructVirtualResultSet(PrimaryKeyColumnsDynArray));
01495 end;
01496
01497 {**
01498 Gets a description of a table's indices and statistics. They are
01499 ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
01500
01501 <P>Each index column description has the following columns:
01502 <OL>
01503 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01504 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01505 <LI><B>TABLE_NAME</B> String => table name
01506 <LI><B>NON_UNIQUE</B> Boolean => Can index values be non-unique?
01507 false when TYPE is tableIndexStatistic
01508 <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
01509 null when TYPE is tableIndexStatistic
01510 <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
01511 tableIndexStatistic
01512 <LI><B>TYPE</B> short => index type:
01513 <UL>
01514 <LI> tableIndexStatistic - this identifies table statistics that are
01515 returned in conjuction with a table's index descriptions
01516 <LI> tableIndexClustered - this is a clustered index
01517 <LI> tableIndexHashed - this is a hashed index
01518 <LI> tableIndexOther - this is some other style of index
01519 </UL>
01520 <LI><B>ORDINAL_POSITION</B> short => column sequence number
01521 within index; zero when TYPE is tableIndexStatistic
01522 <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
01523 tableIndexStatistic
01524 <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending,
01525 "D" => descending, may be null if sort sequence is not supported;
01526 null when TYPE is tableIndexStatistic
01527 <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
01528 this is the number of rows in the table; otherwise, it is the
01529 number of unique values in the index.
01530 <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
01531 this is the number of pages used for the table, otherwise it
01532 is the number of pages used for the current index.
01533 <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
01534 (may be null)
01535 </OL>
01536
01537 @param catalog a catalog name; "" retrieves those without a
01538 catalog; null means drop catalog name from the selection criteria
01539 @param schema a schema name; "" retrieves those without a schema
01540 @param table a table name
01541 @param unique when true, return only indices for unique values;
01542 when false, return indices regardless of whether unique or not
01543 @param approximate when true, result is allowed to reflect approximate
01544 or out of data values; when false, results are requested to be
01545 accurate
01546 @return <code>ResultSet</code> - each row is an index column description
01547 }
01548 function TZOracleDatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
01549 const Schema: string; const Table: string; Unique: Boolean;
01550 Approximate: Boolean): IZResultSet;
01551 var
01552 SQL: string;
01553 begin
01554 Result := ConstructVirtualResultSet(IndexInfoColumnsDynArray);
01555
01556 SQL := 'SELECT NULL, A.OWNER, A.TABLE_NAME, A.UNIQUENESS, NULL,'
01557 + ' A.INDEX_NAME, 3, B.COLUMN_POSITION, B.COLUMN_NAME, B.DESCEND,'
01558 + ' 0, 0, NULL FROM ALL_INDEXES A, ALL_IND_COLUMNS B'
01559 + ' WHERE A.OWNER=B.INDEX_OWNER AND A.INDEX_NAME=B.INDEX_NAME'
01560 + ' AND A.TABLE_OWNER=B.TABLE_OWNER AND A.TABLE_NAME=B.TABLE_NAME';
01561 if Schema <> '' then
01562 SQL := SQL + ' AND A.TABLE_OWNER=''' + Schema + '''';
01563 if Table <> '' then
01564 SQL := SQL + ' AND A.TABLE_NAME=''' + Table + '''';
01565 if Unique then
01566 SQL := SQL + ' AND A.UNIQUENESS=''UNIQUE''';
01567 SQL := SQL + ' ORDER BY A.UNIQUENESS DESC, A.INDEX_NAME, B.COLUMN_POSITION';
01568
01569 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01570 begin
01571 while Next do
01572 begin
01573 Result.MoveToInsertRow;
01574
01575 Result.UpdateNull(1);
01576 Result.UpdateString(2, GetString(2));
01577 Result.UpdateString(3, GetString(3));
01578 Result.UpdateBoolean(4,
01579 UpperCase(GetString(4)) <> 'UNIQUE');
01580 Result.UpdateNull(5);
01581 Result.UpdateString(6, GetString(6));
01582 Result.UpdateInt(7, GetInt(7));
01583 Result.UpdateInt(8, GetInt(8));
01584 Result.UpdateString(9, GetString(9));
01585 if GetString(10) = 'ASC' then
01586 Result.UpdateString(10, 'A')
01587 else Result.UpdateString(10, 'D');
01588 Result.UpdateInt(11, GetInt(11));
01589 Result.UpdateInt(12, GetInt(12));
01590 Result.UpdateNull(13);
01591
01592 Result.InsertRow;
01593 end;
01594 Close;
01595 end;
01596 end;
01597
01598 {**
01599 Does the database support the given result set type?
01600 @param type defined in <code>java.sql.ResultSet</code>
01601 @return <code>true</code> if so; <code>false</code> otherwise
01602 }
01603 function TZOracleDatabaseMetadata.SupportsResultSetType(
01604 _Type: TZResultSetType): Boolean;
01605 begin
01606 Result := _Type = rtForwardOnly;
01607 end;
01608
01609 {**
01610 Does the database support the concurrency type in combination
01611 with the given result set type?
01612
01613 @param type defined in <code>java.sql.ResultSet</code>
01614 @param concurrency type defined in <code>java.sql.ResultSet</code>
01615 @return <code>true</code> if so; <code>false</code> otherwise
01616 }
01617 function TZOracleDatabaseMetadata.SupportsResultSetConcurrency(
01618 _Type: TZResultSetType; Concurrency: TZResultSetConcurrency): Boolean;
01619 begin
01620 Result := (_Type = rtForwardOnly) and (Concurrency = rcReadOnly);
01621 end;
01622
01623 end.