00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { MsSql Database metadata information }
00005 { }
00006 { Originally written by Janos Fegyverneki }
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 ZDbcDbLibMsSqlMetadata;
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, ZDbcConnection;
00066
00067 type
00068
00069 {** Implements MsSql Database Metadata. }
00070 TZMsSqlDatabaseMetadata = class(TZAbstractDatabaseMetadata)
00071 protected
00072 function GetStatement: IZSTatement;
00073 function UncachedGetTables(const Catalog: string; const SchemaPattern: string;
00074 const TableNamePattern: string; const Types: TStringDynArray): IZResultSet; override;
00075 function UncachedGetSchemas: IZResultSet; override;
00076 function UncachedGetCatalogs: IZResultSet; override;
00077 function UncachedGetTableTypes: IZResultSet; override;
00078 function UncachedGetColumns(const Catalog: string; const SchemaPattern: string;
00079 const TableNamePattern: string; const ColumnNamePattern: string): IZResultSet; override;
00080 function UncachedGetTablePrivileges(const Catalog: string; const SchemaPattern: string;
00081 const TableNamePattern: string): IZResultSet; override;
00082 function UncachedGetColumnPrivileges(const Catalog: string; const Schema: string;
00083 const Table: string; const ColumnNamePattern: string): IZResultSet; override;
00084 function UncachedGetPrimaryKeys(const Catalog: string; const Schema: string;
00085 const Table: string): IZResultSet; override;
00086 function UncachedGetImportedKeys(const Catalog: string; const Schema: string;
00087 const Table: string): IZResultSet; override;
00088 function UncachedGetExportedKeys(const Catalog: string; const Schema: string;
00089 const Table: string): IZResultSet; override;
00090 function UncachedGetCrossReference(const PrimaryCatalog: string; const PrimarySchema: string;
00091 const PrimaryTable: string; const ForeignCatalog: string; const ForeignSchema: string;
00092 const ForeignTable: string): IZResultSet; override;
00093 function UncachedGetIndexInfo(const Catalog: string; const Schema: string; const Table: string;
00094 Unique: Boolean; Approximate: Boolean): IZResultSet; override;
00095
00096
00097 function UncachedGetProcedures(const Catalog: string; const SchemaPattern: string;
00098 const ProcedureNamePattern: string): IZResultSet; override;
00099 function UncachedGetProcedureColumns(const Catalog: string; const SchemaPattern: string;
00100 const ProcedureNamePattern: string; const ColumnNamePattern: string):
00101 IZResultSet; override;
00102 function UncachedGetVersionColumns(const Catalog: string; const Schema: string;
00103 const Table: string): IZResultSet; override;
00104 function UncachedGetTypeInfo: IZResultSet; override;
00105 function UncachedGetUDTs(const Catalog: string; const SchemaPattern: string;
00106 const TypeNamePattern: string; const Types: TIntegerDynArray): IZResultSet; override;
00107 public
00108 constructor Create(Connection: TZAbstractConnection; const Url: string;
00109 Info: TStrings);
00110 destructor Destroy; override;
00111
00112 function GetDatabaseProductName: string; override;
00113 function GetDatabaseProductVersion: string; override;
00114 function GetDriverName: string; override;
00115 function GetDriverMajorVersion: Integer; override;
00116 function GetDriverMinorVersion: Integer; override;
00117 function UsesLocalFilePerTable: Boolean; override;
00118 function SupportsMixedCaseIdentifiers: Boolean; override;
00119 function StoresUpperCaseIdentifiers: Boolean; override;
00120 function StoresLowerCaseIdentifiers: Boolean; override;
00121 function StoresMixedCaseIdentifiers: Boolean; override;
00122 function SupportsMixedCaseQuotedIdentifiers: Boolean; override;
00123 function StoresUpperCaseQuotedIdentifiers: Boolean; override;
00124 function StoresLowerCaseQuotedIdentifiers: Boolean; override;
00125 function StoresMixedCaseQuotedIdentifiers: Boolean; override;
00126 function GetIdentifierQuoteString: string; 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; override;
00198 function SupportsDataManipulationTransactionsOnly: Boolean; override;
00199 function DataDefinitionCausesTransactionCommit: Boolean; override;
00200 function DataDefinitionIgnoredInTransactions: Boolean; override;
00201
00202 function SupportsResultSetType(_Type: TZResultSetType): Boolean; override;
00203 function SupportsResultSetConcurrency(_Type: TZResultSetType;
00204 Concurrency: TZResultSetConcurrency): Boolean; override;
00205 end;
00206
00207 implementation
00208
00209 uses ZDbcUtils, ZDbcDbLibUtils;
00210
00211 { TZMsSqlDatabaseMetadata }
00212
00213 {**
00214 Constructs this object and assignes the main properties.
00215 @param Connection a database connection object.
00216 @param Url a database connection url string.
00217 @param Info an extra connection properties.
00218 }
00219 constructor TZMsSqlDatabaseMetadata.Create(Connection: TZAbstractConnection;
00220 const Url: string; Info: TStrings);
00221 begin
00222 inherited Create(Connection, Url, Info);
00223 end;
00224
00225 {**
00226 Destroys this object and cleanups the memory.
00227 }
00228 destructor TZMsSqlDatabaseMetadata.Destroy;
00229 begin
00230 inherited Destroy;
00231 end;
00232
00233
00234
00235
00236 {**
00237 What's the name of this database product?
00238 @return database product name
00239 }
00240 function TZMsSqlDatabaseMetadata.GetDatabaseProductName: string;
00241 begin
00242 Result := 'MS SQL';
00243 end;
00244
00245 {**
00246 What's the version of this database product?
00247 @return database version
00248 }
00249 function TZMsSqlDatabaseMetadata.GetDatabaseProductVersion: string;
00250 begin
00251 Result := '7+';
00252 end;
00253
00254 {**
00255 What's the name of this JDBC driver?
00256 @return JDBC driver name
00257 }
00258 function TZMsSqlDatabaseMetadata.GetDriverName: string;
00259 begin
00260 Result := 'Zeos Database Connectivity Driver for Microsoft SQL Server';
00261 end;
00262
00263 {**
00264 What's this JDBC driver's major version number?
00265 @return JDBC driver major version
00266 }
00267 function TZMsSqlDatabaseMetadata.GetDriverMajorVersion: Integer;
00268 begin
00269 Result := 1;
00270 end;
00271
00272 {**
00273 What's this JDBC driver's minor version number?
00274 @return JDBC driver minor version number
00275 }
00276 function TZMsSqlDatabaseMetadata.GetDriverMinorVersion: Integer;
00277 begin
00278 Result := 0;
00279 end;
00280
00281 {**
00282 Does the database use a file for each table?
00283 @return true if the database uses a local file for each table
00284 }
00285 function TZMsSqlDatabaseMetadata.UsesLocalFilePerTable: Boolean;
00286 begin
00287 Result := False;
00288 end;
00289
00290 {**
00291 Does the database treat mixed case unquoted SQL identifiers as
00292 case sensitive and as a result store them in mixed case?
00293 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will
00294 always return false.
00295 @return <code>true</code> if so; <code>false</code> otherwise
00296 }
00297 function TZMsSqlDatabaseMetadata.SupportsMixedCaseIdentifiers: Boolean;
00298 begin
00299 Result := False;
00300 end;
00301
00302 {**
00303 Does the database treat mixed case unquoted SQL identifiers as
00304 case insensitive and store them in upper case?
00305 @return <code>true</code> if so; <code>false</code> otherwise
00306 }
00307 function TZMsSqlDatabaseMetadata.StoresUpperCaseIdentifiers: Boolean;
00308 begin
00309 Result := True;
00310 end;
00311
00312 {**
00313 Does the database treat mixed case unquoted SQL identifiers as
00314 case insensitive and store them in lower case?
00315 @return <code>true</code> if so; <code>false</code> otherwise
00316 }
00317 function TZMsSqlDatabaseMetadata.StoresLowerCaseIdentifiers: Boolean;
00318 begin
00319 Result := True;
00320 end;
00321
00322 {**
00323 Does the database treat mixed case unquoted SQL identifiers as
00324 case insensitive and store them in mixed case?
00325 @return <code>true</code> if so; <code>false</code> otherwise
00326 }
00327 function TZMsSqlDatabaseMetadata.StoresMixedCaseIdentifiers: Boolean;
00328 begin
00329 Result := True;
00330 end;
00331
00332 {**
00333 Does the database treat mixed case quoted SQL identifiers as
00334 case sensitive and as a result store them in mixed case?
00335 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return true.
00336 @return <code>true</code> if so; <code>false</code> otherwise
00337 }
00338 function TZMsSqlDatabaseMetadata.SupportsMixedCaseQuotedIdentifiers: Boolean;
00339 begin
00340 Result := True;
00341 end;
00342
00343 {**
00344 Does the database treat mixed case quoted SQL identifiers as
00345 case insensitive and store them in upper case?
00346 @return <code>true</code> if so; <code>false</code> otherwise
00347 }
00348 function TZMsSqlDatabaseMetadata.StoresUpperCaseQuotedIdentifiers: Boolean;
00349 begin
00350 Result := True;
00351 end;
00352
00353 {**
00354 Does the database treat mixed case quoted SQL identifiers as
00355 case insensitive and store them in lower case?
00356 @return <code>true</code> if so; <code>false</code> otherwise
00357 }
00358 function TZMsSqlDatabaseMetadata.StoresLowerCaseQuotedIdentifiers: Boolean;
00359 begin
00360 Result := True;
00361 end;
00362
00363 {**
00364 Does the database treat mixed case quoted SQL identifiers as
00365 case insensitive and store them in mixed case?
00366 @return <code>true</code> if so; <code>false</code> otherwise
00367 }
00368 function TZMsSqlDatabaseMetadata.StoresMixedCaseQuotedIdentifiers: Boolean;
00369 begin
00370 Result := True;
00371 end;
00372
00373 {**
00374 What's the string used to quote SQL identifiers?
00375 This returns a space " " if identifier quoting isn't supported.
00376 A JDBC Compliant<sup><font size=-2>TM</font></sup>
00377 driver always uses a double quote character.
00378 @return the quoting string
00379 }
00380 function TZMsSqlDatabaseMetadata.GetIdentifierQuoteString: string;
00381 begin
00382 Result := '"';
00383 end;
00384
00385 {**
00386 Gets a comma-separated list of all a database's SQL keywords
00387 that are NOT also SQL92 keywords.
00388 @return the list
00389 }
00390 function TZMsSqlDatabaseMetadata.GetSQLKeywords: string;
00391 begin
00392 { TODO -ofjanos -cAPI : SQL Keywords that are not SQL92 compliant }
00393 Result := '';
00394 end;
00395
00396 {**
00397 Gets a comma-separated list of math functions. These are the
00398 X/Open CLI math function names used in the JDBC function escape
00399 clause.
00400 @return the list
00401 }
00402 function TZMsSqlDatabaseMetadata.GetNumericFunctions: string;
00403 begin
00404 Result := 'ABS,ACOS,ASIN,ATAN,ATN2,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,'+
00405 'PI,POWER,RADIANS,RAND,ROUND,SIGN,SIN,SQUARE,SQRT,TAN';
00406 end;
00407
00408 {**
00409 Gets a comma-separated list of string functions. These are the
00410 X/Open CLI string function names used in the JDBC function escape
00411 clause.
00412 @return the list
00413 }
00414 function TZMsSqlDatabaseMetadata.GetStringFunctions: string;
00415 begin
00416 Result := 'ASCII,CHAR,CHARINDEX,DIFFERENCE,LEFT,LEN,LOWER,LTRIM,NCHAR,PATINDEX,'+
00417 'REPLACE,QUOTENAME,REPLICATE,REVERSE,RIGHT,RTRIM,SOUNDEX,SPACE,STR,'+
00418 'STUFF,SUBSTRING,UNICODE,UPPER';
00419 end;
00420
00421 {**
00422 Gets a comma-separated list of system functions. These are the
00423 X/Open CLI system function names used in the JDBC function escape
00424 clause.
00425 @return the list
00426 }
00427 function TZMsSqlDatabaseMetadata.GetSystemFunctions: string;
00428 begin
00429 Result := 'APP_NAME,CASE,CAST,CONVERT,COALESCE,CURRENT_TIMESTAMP,CURRENT_USER,'+
00430 'DATALENGTH,@@ERROR,FORMATMESSAGE,GETANSINULL,HOST_ID,HOST_NAME,'+
00431 'IDENT_INCR,IDENT_SEED,@@IDENTITY,IDENTITY,ISDATE,ISNULL,ISNUMERIC,'+
00432 'NEWID,NULLIF,PARSENAME,PERMISSIONS,@@ROWCOUNT,SESSION_USER,STATS_DATE,'+
00433 'SYSTEM_USER,@@TRANCOUNT,USER_NAME';
00434 end;
00435
00436 {**
00437 Gets a comma-separated list of time and date functions.
00438 @return the list
00439 }
00440 function TZMsSqlDatabaseMetadata.GetTimeDateFunctions: string;
00441 begin
00442 Result := 'DATEADD,DATEDIFF,DATENAME,DATEPART,DAY,GETDATE,MONTH,YEAR';
00443 end;
00444
00445 {**
00446 Gets the string that can be used to escape wildcard characters.
00447 This is the string that can be used to escape '_' or '%' in
00448 the string pattern style catalog search parameters.
00449
00450 <P>The '_' character represents any single character.
00451 <P>The '%' character represents any sequence of zero or
00452 more characters.
00453
00454 @return the string used to escape wildcard characters
00455 }
00456 function TZMsSqlDatabaseMetadata.GetSearchStringEscape: string;
00457 begin
00458 { TODO -ofjanos -cgeneral :
00459 In sql server this must be specified as the parameter of like.
00460 example: WHERE ColumnA LIKE '%5/%%' ESCAPE '/' }
00461 Result := '/';
00462 end;
00463
00464 {**
00465 Gets all the "extra" characters that can be used in unquoted
00466 identifier names (those beyond a-z, A-Z, 0-9 and _).
00467 @return the string containing the extra characters
00468 }
00469 function TZMsSqlDatabaseMetadata.GetExtraNameCharacters: string;
00470 begin
00471 Result := '@$#';
00472 end;
00473
00474 //--------------------------------------------------------------------
00475 // Functions describing which features are supported.
00476
00477 {**
00478 Are expressions in "ORDER BY" lists supported?
00479 @return <code>true</code> if so; <code>false</code> otherwise
00480 }
00481 function TZMsSqlDatabaseMetadata.SupportsExpressionsInOrderBy: Boolean;
00482 begin
00483 Result := True;
00484 end;
00485
00486 {**
00487 Can an "ORDER BY" clause use columns not in the SELECT statement?
00488 @return <code>true</code> if so; <code>false</code> otherwise
00489 }
00490 function TZMsSqlDatabaseMetadata.SupportsOrderByUnrelated: Boolean;
00491 begin
00492 Result := True;
00493 end;
00494
00495 {**
00496 Is some form of "GROUP BY" clause supported?
00497 @return <code>true</code> if so; <code>false</code> otherwise
00498 }
00499 function TZMsSqlDatabaseMetadata.SupportsGroupBy: Boolean;
00500 begin
00501 Result := True;
00502 end;
00503
00504 {**
00505 Can a "GROUP BY" clause use columns not in the SELECT?
00506 @return <code>true</code> if so; <code>false</code> otherwise
00507 }
00508 function TZMsSqlDatabaseMetadata.SupportsGroupByUnrelated: Boolean;
00509 begin
00510 Result := True;
00511 end;
00512
00513 {**
00514 Can a "GROUP BY" clause add columns not in the SELECT
00515 provided it specifies all the columns in the SELECT?
00516 @return <code>true</code> if so; <code>false</code> otherwise
00517 }
00518 function TZMsSqlDatabaseMetadata.SupportsGroupByBeyondSelect: Boolean;
00519 begin
00520 Result := True;
00521 end;
00522
00523 {**
00524 Is the SQL Integrity Enhancement Facility supported?
00525 @return <code>true</code> if so; <code>false</code> otherwise
00526 }
00527 function TZMsSqlDatabaseMetadata.SupportsIntegrityEnhancementFacility: Boolean;
00528 begin
00529 Result := False;
00530 end;
00531
00532 {**
00533 What's the database vendor's preferred term for "schema"?
00534 @return the vendor term
00535 }
00536 function TZMsSqlDatabaseMetadata.GetSchemaTerm: string;
00537 begin
00538 Result := 'owner';
00539 end;
00540
00541 {**
00542 What's the database vendor's preferred term for "procedure"?
00543 @return the vendor term
00544 }
00545 function TZMsSqlDatabaseMetadata.GetProcedureTerm: string;
00546 begin
00547 Result := 'procedure';
00548 end;
00549
00550 {**
00551 What's the database vendor's preferred term for "catalog"?
00552 @return the vendor term
00553 }
00554 function TZMsSqlDatabaseMetadata.GetCatalogTerm: string;
00555 begin
00556 Result := 'database';
00557 end;
00558
00559 {**
00560 What's the separator between catalog and table name?
00561 @return the separator string
00562 }
00563 function TZMsSqlDatabaseMetadata.GetCatalogSeparator: string;
00564 begin
00565 Result := '.';
00566 end;
00567
00568 {**
00569 Can a schema name be used in a data manipulation statement?
00570 @return <code>true</code> if so; <code>false</code> otherwise
00571 }
00572 function TZMsSqlDatabaseMetadata.SupportsSchemasInDataManipulation: Boolean;
00573 begin
00574 Result := True;
00575 end;
00576
00577 {**
00578 Can a schema name be used in a procedure call statement?
00579 @return <code>true</code> if so; <code>false</code> otherwise
00580 }
00581 function TZMsSqlDatabaseMetadata.SupportsSchemasInProcedureCalls: Boolean;
00582 begin
00583 Result := True;
00584 end;
00585
00586 {**
00587 Can a schema name be used in a table definition statement?
00588 @return <code>true</code> if so; <code>false</code> otherwise
00589 }
00590 function TZMsSqlDatabaseMetadata.SupportsSchemasInTableDefinitions: Boolean;
00591 begin
00592 Result := True;
00593 end;
00594
00595 {**
00596 Can a schema name be used in an index definition statement?
00597 @return <code>true</code> if so; <code>false</code> otherwise
00598 }
00599 function TZMsSqlDatabaseMetadata.SupportsSchemasInIndexDefinitions: Boolean;
00600 begin
00601 Result := True;
00602 end;
00603
00604 {**
00605 Can a schema name be used in a privilege definition statement?
00606 @return <code>true</code> if so; <code>false</code> otherwise
00607 }
00608 function TZMsSqlDatabaseMetadata.SupportsSchemasInPrivilegeDefinitions: Boolean;
00609 begin
00610 Result := True;
00611 end;
00612
00613 {**
00614 Can a catalog name be used in a data manipulation statement?
00615 @return <code>true</code> if so; <code>false</code> otherwise
00616 }
00617 function TZMsSqlDatabaseMetadata.SupportsCatalogsInDataManipulation: Boolean;
00618 begin
00619 Result := True;
00620 end;
00621
00622 {**
00623 Can a catalog name be used in a procedure call statement?
00624 @return <code>true</code> if so; <code>false</code> otherwise
00625 }
00626 function TZMsSqlDatabaseMetadata.SupportsCatalogsInProcedureCalls: Boolean;
00627 begin
00628 Result := True;
00629 end;
00630
00631 {**
00632 Can a catalog name be used in a table definition statement?
00633 @return <code>true</code> if so; <code>false</code> otherwise
00634 }
00635 function TZMsSqlDatabaseMetadata.SupportsCatalogsInTableDefinitions: Boolean;
00636 begin
00637 Result := True;
00638 end;
00639
00640 {**
00641 Can a catalog name be used in an index definition statement?
00642 @return <code>true</code> if so; <code>false</code> otherwise
00643 }
00644 function TZMsSqlDatabaseMetadata.SupportsCatalogsInIndexDefinitions: Boolean;
00645 begin
00646 Result := True;
00647 end;
00648
00649 {**
00650 Can a catalog name be used in a privilege definition statement?
00651 @return <code>true</code> if so; <code>false</code> otherwise
00652 }
00653 function TZMsSqlDatabaseMetadata.SupportsCatalogsInPrivilegeDefinitions: Boolean;
00654 begin
00655 Result := True;
00656 end;
00657
00658 {**
00659 Is positioned DELETE supported?
00660 @return <code>true</code> if so; <code>false</code> otherwise
00661 }
00662 function TZMsSqlDatabaseMetadata.SupportsPositionedDelete: Boolean;
00663 begin
00664 //CURRENT OF
00665 //Specifies that the DELETE is done at the current position of the specified cursor.
00666 Result := True;
00667 end;
00668
00669 {**
00670 Is positioned UPDATE supported?
00671 @return <code>true</code> if so; <code>false</code> otherwise
00672 }
00673 function TZMsSqlDatabaseMetadata.SupportsPositionedUpdate: Boolean;
00674 begin
00675 Result := True;
00676 end;
00677
00678 {**
00679 Is SELECT for UPDATE supported?
00680 @return <code>true</code> if so; <code>false</code> otherwise
00681 }
00682 function TZMsSqlDatabaseMetadata.SupportsSelectForUpdate: Boolean;
00683 begin
00684 Result := True;
00685 end;
00686
00687 {**
00688 Are stored procedure calls using the stored procedure escape
00689 syntax supported?
00690 @return <code>true</code> if so; <code>false</code> otherwise
00691 }
00692 function TZMsSqlDatabaseMetadata.SupportsStoredProcedures: Boolean;
00693 begin
00694 Result := True;
00695 end;
00696
00697 {**
00698 Are subqueries in comparison expressions supported?
00699 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00700 @return <code>true</code> if so; <code>false</code> otherwise
00701 }
00702 function TZMsSqlDatabaseMetadata.SupportsSubqueriesInComparisons: Boolean;
00703 begin
00704 Result := True;
00705 end;
00706
00707 {**
00708 Are subqueries in 'exists' expressions supported?
00709 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00710 @return <code>true</code> if so; <code>false</code> otherwise
00711 }
00712 function TZMsSqlDatabaseMetadata.SupportsSubqueriesInExists: Boolean;
00713 begin
00714 Result := True;
00715 end;
00716
00717 {**
00718 Are subqueries in 'in' statements supported?
00719 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00720 @return <code>true</code> if so; <code>false</code> otherwise
00721 }
00722 function TZMsSqlDatabaseMetadata.SupportsSubqueriesInIns: Boolean;
00723 begin
00724 Result := True;
00725 end;
00726
00727 {**
00728 Are subqueries in quantified expressions supported?
00729 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00730 @return <code>true</code> if so; <code>false</code> otherwise
00731 }
00732 function TZMsSqlDatabaseMetadata.SupportsSubqueriesInQuantifieds: Boolean;
00733 begin
00734 Result := True;
00735 end;
00736
00737 {**
00738 Are correlated subqueries supported?
00739 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00740 @return <code>true</code> if so; <code>false</code> otherwise
00741 }
00742 function TZMsSqlDatabaseMetadata.SupportsCorrelatedSubqueries: Boolean;
00743 begin
00744 Result := True;
00745 end;
00746
00747 {**
00748 Is SQL UNION supported?
00749 @return <code>true</code> if so; <code>false</code> otherwise
00750 }
00751 function TZMsSqlDatabaseMetadata.SupportsUnion: Boolean;
00752 begin
00753 Result := True;
00754 end;
00755
00756 {**
00757 Is SQL UNION ALL supported?
00758 @return <code>true</code> if so; <code>false</code> otherwise
00759 }
00760 function TZMsSqlDatabaseMetadata.SupportsUnionAll: Boolean;
00761 begin
00762 Result := True;
00763 end;
00764
00765 {**
00766 Can cursors remain open across commits?
00767 @return <code>true</code> if cursors always remain open;
00768 <code>false</code> if they might not remain open
00769 }
00770 function TZMsSqlDatabaseMetadata.SupportsOpenCursorsAcrossCommit: Boolean;
00771 begin
00772 Result := True;
00773 end;
00774
00775 {**
00776 Can cursors remain open across rollbacks?
00777 @return <code>true</code> if cursors always remain open;
00778 <code>false</code> if they might not remain open
00779 }
00780 function TZMsSqlDatabaseMetadata.SupportsOpenCursorsAcrossRollback: Boolean;
00781 begin
00782 Result := True;
00783 end;
00784
00785 {**
00786 Can statements remain open across commits?
00787 @return <code>true</code> if statements always remain open;
00788 <code>false</code> if they might not remain open
00789 }
00790 function TZMsSqlDatabaseMetadata.SupportsOpenStatementsAcrossCommit: Boolean;
00791 begin
00792 Result := False;
00793 end;
00794
00795 {**
00796 Can statements remain open across rollbacks?
00797 @return <code>true</code> if statements always remain open;
00798 <code>false</code> if they might not remain open
00799 }
00800 function TZMsSqlDatabaseMetadata.SupportsOpenStatementsAcrossRollback: Boolean;
00801 begin
00802 Result := False;
00803 end;
00804
00805 //----------------------------------------------------------------------
00806 // The following group of methods exposes various limitations
00807 // based on the target database with the current driver.
00808 // Unless otherwise specified, a result of zero means there is no
00809 // limit, or the limit is not known.
00810
00811 {**
00812 How many hex characters can you have in an inline binary literal?
00813 @return max binary literal length in hex characters;
00814 a result of zero means that there is no limit or the limit is not known
00815 }
00816 function TZMsSqlDatabaseMetadata.GetMaxBinaryLiteralLength: Integer;
00817 begin
00818 Result := 16000;
00819 end;
00820
00821 {**
00822 What's the max length for a character literal?
00823 @return max literal length;
00824 a result of zero means that there is no limit or the limit is not known
00825 }
00826 function TZMsSqlDatabaseMetadata.GetMaxCharLiteralLength: Integer;
00827 begin
00828 Result := 8000;
00829 end;
00830
00831 {**
00832 What's the limit on column name length?
00833 @return max column name length;
00834 a result of zero means that there is no limit or the limit is not known
00835 }
00836 function TZMsSqlDatabaseMetadata.GetMaxColumnNameLength: Integer;
00837 begin
00838 Result := 128;
00839 end;
00840
00841 {**
00842 What's the maximum number of columns in a "GROUP BY" clause?
00843 @return max number of columns;
00844 a result of zero means that there is no limit or the limit is not known
00845 }
00846 function TZMsSqlDatabaseMetadata.GetMaxColumnsInGroupBy: Integer;
00847 begin
00848 Result := 0;
00849 end;
00850
00851 {**
00852 What's the maximum number of columns allowed in an index?
00853 @return max number of columns;
00854 a result of zero means that there is no limit or the limit is not known
00855 }
00856 function TZMsSqlDatabaseMetadata.GetMaxColumnsInIndex: Integer;
00857 begin
00858 Result := 16;
00859 end;
00860
00861 {**
00862 What's the maximum number of columns in an "ORDER BY" clause?
00863 @return max number of columns;
00864 a result of zero means that there is no limit or the limit is not known
00865 }
00866 function TZMsSqlDatabaseMetadata.GetMaxColumnsInOrderBy: Integer;
00867 begin
00868 Result := 0;
00869 end;
00870
00871 {**
00872 What's the maximum number of columns in a "SELECT" list?
00873 @return max number of columns;
00874 a result of zero means that there is no limit or the limit is not known
00875 }
00876 function TZMsSqlDatabaseMetadata.GetMaxColumnsInSelect: Integer;
00877 begin
00878 Result := 4096;
00879 end;
00880
00881 {**
00882 What's the maximum number of columns in a table?
00883 @return max number of columns;
00884 a result of zero means that there is no limit or the limit is not known
00885 }
00886 function TZMsSqlDatabaseMetadata.GetMaxColumnsInTable: Integer;
00887 begin
00888 Result := 1024;
00889 end;
00890
00891 {**
00892 How many active connections can we have at a time to this database?
00893 @return max number of active connections;
00894 a result of zero means that there is no limit or the limit is not known
00895 }
00896 function TZMsSqlDatabaseMetadata.GetMaxConnections: Integer;
00897 begin
00898 Result := 0;
00899 end;
00900
00901 {**
00902 What's the maximum cursor name length?
00903 @return max cursor name length in bytes;
00904 a result of zero means that there is no limit or the limit is not known
00905 }
00906 function TZMsSqlDatabaseMetadata.GetMaxCursorNameLength: Integer;
00907 begin
00908 Result := 128;
00909 end;
00910
00911 {**
00912 Retrieves the maximum number of bytes for an index, including all
00913 of the parts of the index.
00914 @return max index length in bytes, which includes the composite of all
00915 the constituent parts of the index;
00916 a result of zero means that there is no limit or the limit is not known
00917 }
00918 function TZMsSqlDatabaseMetadata.GetMaxIndexLength: Integer;
00919 begin
00920 Result := 900;
00921 end;
00922
00923 {**
00924 What's the maximum length allowed for a schema name?
00925 @return max name length in bytes;
00926 a result of zero means that there is no limit or the limit is not known
00927 }
00928 function TZMsSqlDatabaseMetadata.GetMaxSchemaNameLength: Integer;
00929 begin
00930 Result := 128;
00931 end;
00932
00933 {**
00934 What's the maximum length of a procedure name?
00935 @return max name length in bytes;
00936 a result of zero means that there is no limit or the limit is not known
00937 }
00938 function TZMsSqlDatabaseMetadata.GetMaxProcedureNameLength: Integer;
00939 begin
00940 Result := 128;
00941 end;
00942
00943 {**
00944 What's the maximum length of a catalog name?
00945 @return max name length in bytes;
00946 a result of zero means that there is no limit or the limit is not known
00947 }
00948 function TZMsSqlDatabaseMetadata.GetMaxCatalogNameLength: Integer;
00949 begin
00950 Result := 128;
00951 end;
00952
00953 {**
00954 What's the maximum length of a single row?
00955 @return max row size in bytes;
00956 a result of zero means that there is no limit or the limit is not known
00957 }
00958 function TZMsSqlDatabaseMetadata.GetMaxRowSize: Integer;
00959 begin
00960 Result := 8060;
00961 end;
00962
00963 {**
00964 Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
00965 blobs?
00966 @return <code>true</code> if so; <code>false</code> otherwise
00967 }
00968 function TZMsSqlDatabaseMetadata.DoesMaxRowSizeIncludeBlobs: Boolean;
00969 begin
00970 Result := False;
00971 end;
00972
00973 {**
00974 What's the maximum length of an SQL statement?
00975 @return max length in bytes;
00976 a result of zero means that there is no limit or the limit is not known
00977 }
00978 function TZMsSqlDatabaseMetadata.GetMaxStatementLength: Integer;
00979 begin
00980 Result := 0;
00981 end;
00982
00983 {**
00984 How many active statements can we have open at one time to this
00985 database?
00986 @return the maximum number of statements that can be open at one time;
00987 a result of zero means that there is no limit or the limit is not known
00988 }
00989 function TZMsSqlDatabaseMetadata.GetMaxStatements: Integer;
00990 begin
00991 Result := 0;
00992 end;
00993
00994 {**
00995 What's the maximum length of a table name?
00996 @return max name length in bytes;
00997 a result of zero means that there is no limit or the limit is not known
00998 }
00999 function TZMsSqlDatabaseMetadata.GetMaxTableNameLength: Integer;
01000 begin
01001 Result := 128;
01002 end;
01003
01004 {**
01005 What's the maximum number of tables in a SELECT statement?
01006 @return the maximum number of tables allowed in a SELECT statement;
01007 a result of zero means that there is no limit or the limit is not known
01008 }
01009 function TZMsSqlDatabaseMetadata.GetMaxTablesInSelect: Integer;
01010 begin
01011 Result := 256;
01012 end;
01013
01014 {**
01015 What's the maximum length of a user name?
01016 @return max user name length in bytes;
01017 a result of zero means that there is no limit or the limit is not known
01018 }
01019 function TZMsSqlDatabaseMetadata.GetMaxUserNameLength: Integer;
01020 begin
01021 Result := 128;
01022 end;
01023
01024 //----------------------------------------------------------------------
01025
01026 {**
01027 What's the database's default transaction isolation level? The
01028 values are defined in <code>java.sql.Connection</code>.
01029 @return the default isolation level
01030 @see Connection
01031 }
01032 function TZMsSqlDatabaseMetadata.GetDefaultTransactionIsolation:
01033 TZTransactIsolationLevel;
01034 begin
01035 Result := tiReadCommitted;
01036 end;
01037
01038 {**
01039 Are transactions supported? If not, invoking the method
01040 <code>commit</code> is a noop and the isolation level is TRANSACTION_NONE.
01041 @return <code>true</code> if transactions are supported; <code>false</code> otherwise
01042 }
01043 function TZMsSqlDatabaseMetadata.SupportsTransactions: Boolean;
01044 begin
01045 Result := True;
01046 end;
01047
01048 {**
01049 Does this database support the given transaction isolation level?
01050 @param level the values are defined in <code>java.sql.Connection</code>
01051 @return <code>true</code> if so; <code>false</code> otherwise
01052 @see Connection
01053 }
01054 function TZMsSqlDatabaseMetadata.SupportsTransactionIsolationLevel(
01055 Level: TZTransactIsolationLevel): Boolean;
01056 begin
01057 Result := True;
01058 end;
01059
01060 {**
01061 Are both data definition and data manipulation statements
01062 within a transaction supported?
01063 @return <code>true</code> if so; <code>false</code> otherwise
01064 }
01065 function TZMsSqlDatabaseMetadata.
01066 SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
01067 begin
01068 Result := True;
01069 end;
01070
01071 {**
01072 Are only data manipulation statements within a transaction
01073 supported?
01074 @return <code>true</code> if so; <code>false</code> otherwise
01075 }
01076 function TZMsSqlDatabaseMetadata.
01077 SupportsDataManipulationTransactionsOnly: Boolean;
01078 begin
01079 Result := False;
01080 end;
01081
01082 {**
01083 Does a data definition statement within a transaction force the
01084 transaction to commit?
01085 @return <code>true</code> if so; <code>false</code> otherwise
01086 }
01087 function TZMsSqlDatabaseMetadata.DataDefinitionCausesTransactionCommit: Boolean;
01088 begin
01089 Result := False;
01090 end;
01091
01092 {**
01093 Is a data definition statement within a transaction ignored?
01094 @return <code>true</code> if so; <code>false</code> otherwise
01095 }
01096 function TZMsSqlDatabaseMetadata.DataDefinitionIgnoredInTransactions: Boolean;
01097 begin
01098 Result := False;
01099 end;
01100
01101 {**
01102 Gets a description of the stored procedures available in a
01103 catalog.
01104
01105 <P>Only procedure descriptions matching the schema and
01106 procedure name criteria are returned. They are ordered by
01107 PROCEDURE_SCHEM, and PROCEDURE_NAME.
01108
01109 <P>Each procedure description has the the following columns:
01110 <OL>
01111 <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
01112 <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
01113 <LI><B>PROCEDURE_NAME</B> String => procedure name
01114 <LI> reserved for future use
01115 <LI> reserved for future use
01116 <LI> reserved for future use
01117 <LI><B>REMARKS</B> String => explanatory comment on the procedure
01118 <LI><B>PROCEDURE_TYPE</B> short => kind of procedure:
01119 <UL>
01120 <LI> procedureResultUnknown - May return a result
01121 <LI> procedureNoResult - Does not return a result
01122 <LI> procedureReturnsResult - Returns a result
01123 </UL>
01124 </OL>
01125
01126 @param catalog a catalog name; "" retrieves those without a
01127 catalog; null means drop catalog name from the selection criteria
01128 @param schemaPattern a schema name pattern; "" retrieves those
01129 without a schema
01130 @param procedureNamePattern a procedure name pattern
01131 @return <code>ResultSet</code> - each row is a procedure description
01132 @see #getSearchStringEscape
01133 }
01134 function TZMsSqlDatabaseMetadata.UncachedGetProcedures(const Catalog: string;
01135 const SchemaPattern: string; const ProcedureNamePattern: string): IZResultSet;
01136 begin
01137 Result := ConstructVirtualResultSet(ProceduresColumnsDynArray);
01138
01139 with GetStatement.ExecuteQuery(
01140 Format('exec sp_stored_procedures %s, %s, %s',
01141 [AQSNull(ProcedureNamePattern), AQSNull(SchemaPattern), AQSNull(Catalog)])) do
01142 begin
01143 while Next do
01144 begin
01145 Result.MoveToInsertRow;
01146 Result.UpdateStringByName('PROCEDURE_CAT',
01147 GetStringByName('PROCEDURE_QUALIFIER'));
01148 Result.UpdateStringByName('PROCEDURE_SCHEM',
01149 GetStringByName('PROCEDURE_OWNER'));
01150 Result.UpdateStringByName('PROCEDURE_NAME',
01151 GetStringByName('PROCEDURE_NAME'));
01152 Result.UpdateStringByName('REMARKS',
01153 GetStringByName('REMARKS'));
01154 Result.UpdateShortByName('PROCEDURE_TYPE', 0);
01155 Result.InsertRow;
01156 end;
01157 Close;
01158 end;
01159 Result.BeforeFirst;
01160 end;
01161
01162 {**
01163 Gets a description of a catalog's stored procedure parameters
01164 and result columns.
01165
01166 <P>Only descriptions matching the schema, procedure and
01167 parameter name criteria are returned. They are ordered by
01168 PROCEDURE_SCHEM and PROCEDURE_NAME. Within this, the return value,
01169 if any, is first. Next are the parameter descriptions in call
01170 order. The column descriptions follow in column number order.
01171
01172 <P>Each row in the <code>ResultSet</code> is a parameter description or
01173 column description with the following fields:
01174 <OL>
01175 <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
01176 <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
01177 <LI><B>PROCEDURE_NAME</B> String => procedure name
01178 <LI><B>COLUMN_NAME</B> String => column/parameter name
01179 <LI><B>COLUMN_TYPE</B> Short => kind of column/parameter:
01180 <UL>
01181 <LI> procedureColumnUnknown - nobody knows
01182 <LI> procedureColumnIn - IN parameter
01183 <LI> procedureColumnInOut - INOUT parameter
01184 <LI> procedureColumnOut - OUT parameter
01185 <LI> procedureColumnReturn - procedure return value
01186 <LI> procedureColumnResult - result column in <code>ResultSet</code>
01187 </UL>
01188 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01189 <LI><B>TYPE_NAME</B> String => SQL type name, for a UDT type the
01190 type name is fully qualified
01191 <LI><B>PRECISION</B> int => precision
01192 <LI><B>LENGTH</B> int => length in bytes of data
01193 <LI><B>SCALE</B> short => scale
01194 <LI><B>RADIX</B> short => radix
01195 <LI><B>NULLABLE</B> short => can it contain NULL?
01196 <UL>
01197 <LI> procedureNoNulls - does not allow NULL values
01198 <LI> procedureNullable - allows NULL values
01199 <LI> procedureNullableUnknown - nullability unknown
01200 </UL>
01201 <LI><B>REMARKS</B> String => comment describing parameter/column
01202 </OL>
01203
01204 <P><B>Note:</B> Some databases may not return the column
01205 descriptions for a procedure. Additional columns beyond
01206 REMARKS can be defined by the database.
01207
01208 @param catalog a catalog name; "" retrieves those without a
01209 catalog; null means drop catalog name from the selection criteria
01210 @param schemaPattern a schema name pattern; "" retrieves those
01211 without a schema
01212 @param procedureNamePattern a procedure name pattern
01213 @param columnNamePattern a column name pattern
01214 @return <code>ResultSet</code> - each row describes a stored procedure parameter or
01215 column
01216 @see #getSearchStringEscape
01217 }
01218 function TZMsSqlDatabaseMetadata.UncachedGetProcedureColumns(const Catalog: string;
01219 const SchemaPattern: string; const ProcedureNamePattern: string;
01220 const ColumnNamePattern: string): IZResultSet;
01221 begin
01222 Result := ConstructVirtualResultSet(ProceduresColColumnsDynArray);
01223
01224 with GetStatement.ExecuteQuery(
01225 Format('exec sp_sproc_columns %s, %s, %s, %s',
01226 [AQSNull(ProcedureNamePattern), AQSNull(SchemaPattern), AQSNull(Catalog), AQSNull(ColumnNamePattern)])) do
01227 begin
01228 while Next do
01229 begin
01230 Result.MoveToInsertRow;
01231 Result.UpdateStringByName('PROCEDURE_CAT',
01232 GetStringByName('PROCEDURE_QUALIFIER'));
01233 Result.UpdateStringByName('PROCEDURE_SCHEM',
01234 GetStringByName('PROCEDURE_OWNER'));
01235 Result.UpdateStringByName('PROCEDURE_NAME',
01236 GetStringByName('PROCEDURE_NAME'));
01237 Result.UpdateStringByName('COLUMN_NAME',
01238 GetStringByName('COLUMN_NAME'));
01239 case GetShortByName('COLUMN_TYPE') of
01240 1: Result.UpdateShortByName('COLUMN_TYPE', 1); //ptInput
01241 2: Result.UpdateShortByName('COLUMN_TYPE', 3); //ptInputOutput
01242 3: Result.UpdateShortByName('COLUMN_TYPE', 0); //ptUnknown
01243 4: Result.UpdateShortByName('COLUMN_TYPE', 3); //ptInputOutput
01244 5: Result.UpdateShortByName('COLUMN_TYPE', 4); //ptResult
01245 else
01246 Result.UpdateShortByName('COLUMN_TYPE', 0); //ptUnknown
01247 end;
01248 Result.UpdateShortByName('DATA_TYPE',
01249 Ord(ConvertODBCToSqlType(GetShortByName('DATA_TYPE'))));
01250 Result.UpdateStringByName('TYPE_NAME', GetStringByName('TYPE_NAME'));
01251 Result.UpdateIntByName('PRECISION', GetIntByName('PRECISION'));
01252 Result.UpdateIntByName('LENGTH', GetIntByName('LENGTH'));
01253 Result.UpdateShortByName('SCALE', GetShortByName('SCALE'));
01254 Result.UpdateShortByName('RADIX', GetShortByName('RADIX'));
01255 Result.UpdateShortByName('NULLABLE', 2);
01256 if GetStringByName('IS_NULLABLE') = 'NO' then
01257 Result.UpdateShortByName('NULLABLE', 0);
01258 if GetStringByName('IS_NULLABLE') = 'YES' then
01259 Result.UpdateShortByName('NULLABLE', 1);
01260 Result.UpdateStringByName('REMARKS', GetStringByName('REMARKS'));
01261 Result.InsertRow;
01262 end;
01263 Close;
01264 end;
01265 Result.BeforeFirst;
01266 end;
01267
01268 {**
01269 Gets a description of tables available in a catalog.
01270
01271 <P>Only table descriptions matching the catalog, schema, table
01272 name and type criteria are returned. They are ordered by
01273 TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
01274
01275 <P>Each table description has the following columns:
01276 <OL>
01277 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01278 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01279 <LI><B>TABLE_NAME</B> String => table name
01280 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01281 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01282 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01283 <LI><B>REMARKS</B> String => explanatory comment on the table
01284 </OL>
01285
01286 <P><B>Note:</B> Some databases may not return information for
01287 all tables.
01288
01289 @param catalog a catalog name; "" retrieves those without a
01290 catalog; null means drop catalog name from the selection criteria
01291 @param schemaPattern a schema name pattern; "" retrieves those
01292 without a schema
01293 @param tableNamePattern a table name pattern
01294 @param types a list of table types to include; null returns all types
01295 @return <code>ResultSet</code> - each row is a table description
01296 @see #getSearchStringEscape
01297 }
01298 function TZMsSqlDatabaseMetadata.UncachedGetTables(const Catalog: string;
01299 const SchemaPattern: string; const TableNamePattern: string;
01300 const Types: TStringDynArray): IZResultSet;
01301 var
01302 I: Integer;
01303 TableTypes: string;
01304 begin
01305 Result := ConstructVirtualResultSet(TableColumnsDynArray);
01306
01307 TableTypes := '';
01308 for I := 0 to Length(Types) - 1 do
01309 begin
01310 if Length(TableTypes) > 0 then
01311 TableTypes := TableTypes + ',';
01312 TableTypes := TableTypes + AnsiQuotedStr(Types[I], '''');
01313 end;
01314 if TableTypes = '' then
01315 TableTypes := 'null'
01316 else TableTypes := AnsiQuotedStr(TableTypes, '"');
01317
01318 with GetStatement.ExecuteQuery(
01319 Format('exec sp_tables %s, %s, %s, %s',
01320 [AQSNull(TableNamePattern), AQSNull(SchemaPattern), AQSNull(Catalog), TableTypes])) do
01321 begin
01322 while Next do
01323 begin
01324 Result.MoveToInsertRow;
01325 Result.UpdateStringByName('TABLE_CAT',
01326 GetStringByName('TABLE_QUALIFIER'));
01327 Result.UpdateStringByName('TABLE_SCHEM', GetStringByName('TABLE_OWNER'));
01328 Result.UpdateStringByName('TABLE_NAME', GetStringByName('TABLE_NAME'));
01329 Result.UpdateStringByName('TABLE_TYPE', GetStringByName('TABLE_TYPE'));
01330 Result.UpdateStringByName('REMARKS', GetStringByName('REMARKS'));
01331 Result.InsertRow;
01332 end;
01333 Close;
01334 end;
01335 Result.BeforeFirst;
01336 end;
01337
01338 {**
01339 Gets the schema names available in this database. The results
01340 are ordered by schema name.
01341
01342 <P>The schema column is:
01343 <OL>
01344 <LI><B>TABLE_SCHEM</B> String => schema name
01345 </OL>
01346
01347 @return <code>ResultSet</code> - each row has a single String column that is a
01348 schema name
01349 }
01350 function TZMsSqlDatabaseMetadata.UncachedGetSchemas: IZResultSet;
01351 begin
01352 Result := ConstructVirtualResultSet(SchemaColumnsDynArray);
01353
01354 with GetStatement.ExecuteQuery(
01355 'select name as TABLE_OWNER from sysusers where islogin = 1') do
01356 begin
01357 while Next do
01358 begin
01359 Result.MoveToInsertRow;
01360 Result.UpdateStringByName('TABLE_SCHEM',
01361 GetStringByName('TABLE_OWNER'));
01362 Result.InsertRow;
01363 end;
01364 Close;
01365 end;
01366 Result.BeforeFirst;
01367 end;
01368
01369 {**
01370 Gets the catalog names available in this database. The results
01371 are ordered by catalog name.
01372
01373 <P>The catalog column is:
01374 <OL>
01375 <LI><B>TABLE_CAT</B> String => catalog name
01376 </OL>
01377
01378 @return <code>ResultSet</code> - each row has a single String column that is a
01379 catalog name
01380 }
01381 function TZMsSqlDatabaseMetadata.UncachedGetCatalogs: IZResultSet;
01382 begin
01383 Result := ConstructVirtualResultSet(CatalogColumnsDynArray);
01384
01385 with GetStatement.ExecuteQuery('exec sp_databases') do
01386 begin
01387 while Next do
01388 begin
01389 Result.MoveToInsertRow;
01390 Result.UpdateStringByName('TABLE_CAT',
01391 GetStringByName('DATABASE_NAME'));
01392 Result.InsertRow;
01393 end;
01394 Close;
01395 end;
01396 Result.BeforeFirst;
01397 end;
01398
01399 {**
01400 Gets the table types available in this database. The results
01401 are ordered by table type.
01402
01403 <P>The table type is:
01404 <OL>
01405 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01406 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01407 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01408 </OL>
01409
01410 @return <code>ResultSet</code> - each row has a single String column that is a
01411 table type
01412 }
01413 function TZMsSqlDatabaseMetadata.UncachedGetTableTypes: IZResultSet;
01414 const
01415 TableTypes: array[0..2] of string = ('SYSTEM TABLE', 'TABLE', 'VIEW');
01416 var
01417 I: Integer;
01418 begin
01419 Result := ConstructVirtualResultSet(TableTypeColumnsDynArray);
01420 for I := 0 to 2 do
01421 begin
01422 Result.MoveToInsertRow;
01423 Result.UpdateStringByName('TABLE_TYPE', TableTypes[I]);
01424 Result.InsertRow;
01425 end;
01426 Result.BeforeFirst;
01427 end;
01428
01429 {**
01430 Gets a description of table columns available in
01431 the specified catalog.
01432
01433 <P>Only column descriptions matching the catalog, schema, table
01434 and column name criteria are returned. They are ordered by
01435 TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
01436
01437 <P>Each column description has the following columns:
01438 <OL>
01439 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01440 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01441 <LI><B>TABLE_NAME</B> String => table name
01442 <LI><B>COLUMN_NAME</B> String => column name
01443 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01444 <LI><B>TYPE_NAME</B> String => Data source dependent type name,
01445 for a UDT the type name is fully qualified
01446 <LI><B>COLUMN_SIZE</B> int => column size. For char or date
01447 types this is the maximum number of characters, for numeric or
01448 decimal types this is precision.
01449 <LI><B>BUFFER_LENGTH</B> is not used.
01450 <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
01451 <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
01452 <LI><B>NULLABLE</B> int => is NULL allowed?
01453 <UL>
01454 <LI> columnNoNulls - might not allow NULL values
01455 <LI> columnNullable - definitely allows NULL values
01456 <LI> columnNullableUnknown - nullability unknown
01457 </UL>
01458 <LI><B>REMARKS</B> String => comment describing column (may be null)
01459 <LI><B>COLUMN_DEF</B> String => default value (may be null)
01460 <LI><B>SQL_DATA_TYPE</B> int => unused
01461 <LI><B>SQL_DATETIME_SUB</B> int => unused
01462 <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
01463 maximum number of bytes in the column
01464 <LI><B>ORDINAL_POSITION</B> int => index of column in table
01465 (starting at 1)
01466 <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
01467 does not allow NULL values; "YES" means the column might
01468 allow NULL values. An empty string means nobody knows.
01469 </OL>
01470
01471 @param catalog a catalog name; "" retrieves those without a
01472 catalog; null means drop catalog name from the selection criteria
01473 @param schemaPattern a schema name pattern; "" retrieves those
01474 without a schema
01475 @param tableNamePattern a table name pattern
01476 @param columnNamePattern a column name pattern
01477 @return <code>ResultSet</code> - each row is a column description
01478 @see #getSearchStringEscape
01479 }
01480 function TZMsSqlDatabaseMetadata.UncachedGetColumns(const Catalog: string;
01481 const SchemaPattern: string; const TableNamePattern: string;
01482 const ColumnNamePattern: string): IZResultSet;
01483 begin
01484 Result := ConstructVirtualResultSet(TableColColumnsDynArray);
01485
01486 with GetStatement.ExecuteQuery(
01487 Format('exec sp_columns %s, %s, %s, %s',
01488 [AQSNull(TableNamePattern), AQSNull(SchemaPattern), AQSNull(Catalog), AQSNull(ColumnNamePattern)])) do
01489 begin
01490 while Next do
01491 begin
01492 Result.MoveToInsertRow;
01493 Result.UpdateStringByName('TABLE_CAT', GetStringByName('TABLE_QUALIFIER'));
01494 //''{GetStringByName('TABLE_QUALIFIER')});
01495 Result.UpdateStringByName('TABLE_SCHEM', GetStringByName('TABLE_OWNER'));
01496 //''{GetStringByName('TABLE_OWNER')});
01497 Result.UpdateStringByName('TABLE_NAME',
01498 GetStringByName('TABLE_NAME'));
01499 Result.UpdateStringByName('COLUMN_NAME',
01500 GetStringByName('COLUMN_NAME'));
01501 Result.UpdateNullByName('DATA_TYPE');
01502 //The value in the resultset will be used
01503 // Result.UpdateShortByName('DATA_TYPE',
01504 // Ord(ConvertODBCToSqlType(GetShortByName('DATA_TYPE'))));
01505 Result.UpdateStringByName('TYPE_NAME',
01506 GetStringByName('TYPE_NAME'));
01507 Result.UpdateIntByName('COLUMN_SIZE',
01508 GetIntByName('LENGTH'));
01509 Result.UpdateIntByName('BUFFER_LENGTH',
01510 GetIntByName('LENGTH'));
01511 Result.UpdateIntByName('DECIMAL_DIGITS',
01512 GetIntByName('SCALE'));
01513 Result.UpdateIntByName('NUM_PREC_RADIX',
01514 GetShortByName('RADIX'));
01515 Result.UpdateIntByName('NULLABLE', 2);
01516 if GetStringByName('IS_NULLABLE') = 'NO' then
01517 Result.UpdateShortByName('NULLABLE', 0);
01518 if GetStringByName('IS_NULLABLE') = 'YES' then
01519 Result.UpdateShortByName('NULLABLE', 1);
01520 Result.UpdateStringByName('REMARKS',
01521 GetStringByName('REMARKS'));
01522 Result.UpdateStringByName('COLUMN_DEF',
01523 GetStringByName('COLUMN_DEF'));
01524 Result.UpdateShortByName('SQL_DATA_TYPE',
01525 GetShortByName('SQL_DATA_TYPE'));
01526 Result.UpdateShortByName('SQL_DATETIME_SUB',
01527 GetShortByName('SQL_DATETIME_SUB'));
01528 Result.UpdateIntByName('CHAR_OCTET_LENGTH',
01529 GetIntByName('CHAR_OCTET_LENGTH'));
01530 Result.UpdateIntByName('ORDINAL_POSITION',
01531 GetIntByName('ORDINAL_POSITION'));
01532 Result.UpdateStringByName('IS_NULLABLE',
01533 GetStringByName('IS_NULLABLE'));
01534
01535 Result.UpdateBooleanByName('SEARCHABLE',
01536 not (GetShortByName('SS_DATA_TYPE') in [34, 35]));
01537
01538 Result.InsertRow;
01539 end;
01540 Close;
01541 end;
01542
01543 Result.BeforeFirst;
01544 with GetStatement.ExecuteQuery(
01545 Format('select c.colid, c.name, c.type, c.prec, c.scale, c.colstat,'
01546 + ' c.status, c.iscomputed from syscolumns c inner join'
01547 + ' sysobjects o on (o.id = c.id) where o.name = %s order by colid',
01548 [AQSNull(TableNamePattern)])) do
01549 begin
01550 while Next do
01551 begin
01552 Result.Next;
01553 Result.UpdateBooleanByName('AUTO_INCREMENT',
01554 (GetShortByName('status') and $80) <> 0);
01555 Result.UpdateNullByName('CASE_SENSITIVE');
01556 Result.UpdateBooleanByName('SEARCHABLE',
01557 Result.GetBooleanByName('SEARCHABLE')
01558 and (GetIntByName('iscomputed') = 0));
01559 Result.UpdateBooleanByName('WRITABLE',
01560 ((GetShortByName('status') and $80) = 0)
01561 and (GetShortByName('type') <> 37)
01562 and (GetIntByName('iscomputed') = 0));
01563 Result.UpdateBooleanByName('DEFINITELYWRITABLE',
01564 Result.GetBooleanByName('WRITABLE'));
01565 Result.UpdateBooleanByName('READONLY',
01566 not Result.GetBooleanByName('WRITABLE'));
01567 if Result.GetBooleanByName('AUTO_INCREMENT') then
01568 begin
01569 Result.UpdateShortByName('NULLABLE', 1);
01570 Result.UpdateStringByName('IS_NULLABLE', 'YES');
01571 end;
01572 Result.UpdateRow;
01573 end;
01574 Close;
01575 end;
01576 Result.BeforeFirst;
01577 end;
01578
01579 {**
01580 Gets a description of the access rights for a table's columns.
01581
01582 <P>Only privileges matching the column name criteria are
01583 returned. They are ordered by COLUMN_NAME and PRIVILEGE.
01584
01585 <P>Each privilige description has the following columns:
01586 <OL>
01587 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01588 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01589 <LI><B>TABLE_NAME</B> String => table name
01590 <LI><B>COLUMN_NAME</B> String => column name
01591 <LI><B>GRANTOR</B> => grantor of access (may be null)
01592 <LI><B>GRANTEE</B> String => grantee of access
01593 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01594 INSERT, UPDATE, REFRENCES, ...)
01595 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01596 to grant to others; "NO" if not; null if unknown
01597 </OL>
01598
01599 @param catalog a catalog name; "" retrieves those without a
01600 catalog; null means drop catalog name from the selection criteria
01601 @param schema a schema name; "" retrieves those without a schema
01602 @param table a table name
01603 @param columnNamePattern a column name pattern
01604 @return <code>ResultSet</code> - each row is a column privilege description
01605 @see #getSearchStringEscape
01606 }
01607 function TZMsSqlDatabaseMetadata.UncachedGetColumnPrivileges(const Catalog: string;
01608 const Schema: string; const Table: string; const ColumnNamePattern: string): IZResultSet;
01609 begin
01610 Result := ConstructVirtualResultSet(TableColPrivColumnsDynArray);
01611
01612 with GetStatement.ExecuteQuery(
01613 Format('exec sp_column_privileges %s, %s, %s, %s',
01614 [AQSNull(Table), AQSNull(Schema), AQSNull(Catalog), AQSNull(ColumnNamePattern)])) do
01615 begin
01616 while Next do
01617 begin
01618 Result.MoveToInsertRow;
01619 Result.UpdateStringByName('TABLE_CAT',
01620 GetStringByName('TABLE_QUALIFIER'));
01621 Result.UpdateStringByName('TABLE_SCHEM',
01622 GetStringByName('TABLE_OWNER'));
01623 Result.UpdateStringByName('TABLE_NAME',
01624 GetStringByName('TABLE_NAME'));
01625 Result.UpdateStringByName('COLUMN_NAME',
01626 GetStringByName('COLUMN_NAME'));
01627 Result.UpdateStringByName('GRANTOR',
01628 GetStringByName('GRANTOR'));
01629 Result.UpdateStringByName('GRANTEE',
01630 GetStringByName('GRANTEE'));
01631 Result.UpdateStringByName('PRIVILEGE',
01632 GetStringByName('PRIVILEGE'));
01633 Result.UpdateStringByName('IS_GRANTABLE',
01634 GetStringByName('IS_GRANTABLE'));
01635 Result.InsertRow;
01636 end;
01637 Close;
01638 end;
01639 Result.BeforeFirst;
01640 end;
01641
01642 {**
01643 Gets a description of the access rights for each table available
01644 in a catalog. Note that a table privilege applies to one or
01645 more columns in the table. It would be wrong to assume that
01646 this priviledge applies to all columns (this may be true for
01647 some systems but is not true for all.)
01648
01649 <P>Only privileges matching the schema and table name
01650 criteria are returned. They are ordered by TABLE_SCHEM,
01651 TABLE_NAME, and PRIVILEGE.
01652
01653 <P>Each privilige description has the following columns:
01654 <OL>
01655 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01656 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01657 <LI><B>TABLE_NAME</B> String => table name
01658 <LI><B>GRANTOR</B> => grantor of access (may be null)
01659 <LI><B>GRANTEE</B> String => grantee of access
01660 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01661 INSERT, UPDATE, REFRENCES, ...)
01662 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01663 to grant to others; "NO" if not; null if unknown
01664 </OL>
01665
01666 @param catalog a catalog name; "" retrieves those without a
01667 catalog; null means drop catalog name from the selection criteria
01668 @param schemaPattern a schema name pattern; "" retrieves those
01669 without a schema
01670 @param tableNamePattern a table name pattern
01671 @return <code>ResultSet</code> - each row is a table privilege description
01672 @see #getSearchStringEscape
01673 }
01674 function TZMsSqlDatabaseMetadata.UncachedGetTablePrivileges(const Catalog: string;
01675 const SchemaPattern: string; const TableNamePattern: string): IZResultSet;
01676 begin
01677 Result := ConstructVirtualResultSet(TablePrivColumnsDynArray);
01678
01679 with GetStatement.ExecuteQuery(
01680 Format('exec sp_table_privileges %s, %s, %s',
01681 [AQSNull(TableNamePattern), AQSNull(SchemaPattern), AQSNull(Catalog)])) do
01682 begin
01683 while Next do
01684 begin
01685 Result.MoveToInsertRow;
01686 Result.UpdateStringByName('TABLE_CAT',
01687 GetStringByName('TABLE_QUALIFIER'));
01688 Result.UpdateStringByName('TABLE_SCHEM',
01689 GetStringByName('TABLE_OWNER'));
01690 Result.UpdateStringByName('TABLE_NAME',
01691 GetStringByName('TABLE_NAME'));
01692 Result.UpdateStringByName('GRANTOR',
01693 GetStringByName('GRANTOR'));
01694 Result.UpdateStringByName('GRANTEE',
01695 GetStringByName('GRANTEE'));
01696 Result.UpdateStringByName('PRIVILEGE',
01697 GetStringByName('PRIVILEGE'));
01698 Result.UpdateStringByName('IS_GRANTABLE',
01699 GetStringByName('IS_GRANTABLE'));
01700 Result.InsertRow;
01701 end;
01702 Close;
01703 end;
01704 Result.BeforeFirst;
01705 end;
01706
01707 {**
01708 Gets a description of a table's columns that are automatically
01709 updated when any value in a row is updated. They are
01710 unordered.
01711
01712 <P>Each column description has the following columns:
01713 <OL>
01714 <LI><B>SCOPE</B> short => is not used
01715 <LI><B>COLUMN_NAME</B> String => column name
01716 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
01717 <LI><B>TYPE_NAME</B> String => Data source dependent type name
01718 <LI><B>COLUMN_SIZE</B> int => precision
01719 <LI><B>BUFFER_LENGTH</B> int => length of column value in bytes
01720 <LI><B>DECIMAL_DIGITS</B> short => scale
01721 <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
01722 like an Oracle ROWID
01723 <UL>
01724 <LI> versionColumnUnknown - may or may not be pseudo column
01725 <LI> versionColumnNotPseudo - is NOT a pseudo column
01726 <LI> versionColumnPseudo - is a pseudo column
01727 </UL>
01728 </OL>
01729
01730 @param catalog a catalog name; "" retrieves those without a
01731 catalog; null means drop catalog name from the selection criteria
01732 @param schema a schema name; "" retrieves those without a schema
01733 @param table a table name
01734 @return <code>ResultSet</code> - each row is a column description
01735 @exception SQLException if a database access error occurs
01736 }
01737 function TZMsSqlDatabaseMetadata.UncachedGetVersionColumns(const Catalog: string;
01738 const Schema: string; const Table: string): IZResultSet;
01739 var
01740 MSCol_Type: string;
01741 begin
01742 Result := ConstructVirtualResultSet(TableColVerColumnsDynArray);
01743
01744 MSCol_Type := '''V''';
01745
01746 with GetStatement.ExecuteQuery(
01747 Format('exec sp_special_columns %s, %s, %s, %s',
01748 [AQSNull(Table), AQSNull(Schema), AQSNull(Catalog), MSCol_Type])) do
01749 begin
01750 while Next do
01751 begin
01752 Result.MoveToInsertRow;
01753 Result.UpdateShortByName('SCOPE',
01754 GetShortByName('SCOPE'));
01755 Result.UpdateStringByName('COLUMN_NAME',
01756 GetStringByName('COLUMN_NAME'));
01757 Result.UpdateShortByName('DATA_TYPE',
01758 Ord(ConvertODBCToSqlType(GetShortByName('DATA_TYPE'))));
01759 Result.UpdateStringByName('TYPE_NAME',
01760 GetStringByName('TYPE_NAME'));
01761 Result.UpdateIntByName('COLUMN_SIZE',
01762 GetIntByName('LENGTH'));
01763 Result.UpdateIntByName('BUFFER_LENGTH',
01764 GetIntByName('LENGTH'));
01765 Result.UpdateIntByName('DECIMAL_DIGITS',
01766 GetIntByName('SCALE'));
01767 Result.UpdateShortByName('PSEUDO_COLUMN',
01768 GetShortByName('PSEUDO_COLUMN'));
01769 Result.InsertRow;
01770 end;
01771 Close;
01772 end;
01773 Result.BeforeFirst;
01774 end;
01775
01776 {**
01777 Gets a description of a table's primary key columns. They
01778 are ordered by COLUMN_NAME.
01779
01780 <P>Each primary key column description has the following columns:
01781 <OL>
01782 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01783 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01784 <LI><B>TABLE_NAME</B> String => table name
01785 <LI><B>COLUMN_NAME</B> String => column name
01786 <LI><B>KEY_SEQ</B> short => sequence number within primary key
01787 <LI><B>PK_NAME</B> String => primary key name (may be null)
01788 </OL>
01789
01790 @param catalog a catalog name; "" retrieves those without a
01791 catalog; null means drop catalog name from the selection criteria
01792 @param schema a schema name; "" retrieves those
01793 without a schema
01794 @param table a table name
01795 @return <code>ResultSet</code> - each row is a primary key column description
01796 @exception SQLException if a database access error occurs
01797 }
01798 function TZMsSqlDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
01799 const Schema: string; const Table: string): IZResultSet;
01800 begin
01801 Result := ConstructVirtualResultSet(PrimaryKeyColumnsDynArray);
01802
01803 with GetStatement.ExecuteQuery(
01804 Format('exec sp_pkeys %s, %s, %s',
01805 [AQSNull(Table), AQSNull(Schema), AQSNull(Catalog)])) do
01806 begin
01807 while Next do
01808 begin
01809 Result.MoveToInsertRow;
01810 Result.UpdateStringByName('TABLE_CAT',
01811 GetStringByName('TABLE_QUALIFIER'));
01812 Result.UpdateStringByName('TABLE_SCHEM',
01813 GetStringByName('TABLE_OWNER'));
01814 Result.UpdateStringByName('TABLE_NAME',
01815 GetStringByName('TABLE_NAME'));
01816 Result.UpdateStringByName('COLUMN_NAME',
01817 GetStringByName('COLUMN_NAME'));
01818 Result.UpdateShortByName('KEY_SEQ',
01819 GetShortByName('KEY_SEQ'));
01820 Result.UpdateStringByName('PK_NAME',
01821 GetStringByName('PK_NAME'));
01822 Result.InsertRow;
01823 end;
01824 Close;
01825 end;
01826 Result.BeforeFirst;
01827 end;
01828
01829 {**
01830 Gets a description of the primary key columns that are
01831 referenced by a table's foreign key columns (the primary keys
01832 imported by a table). They are ordered by PKTABLE_CAT,
01833 PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
01834
01835 <P>Each primary key column description has the following columns:
01836 <OL>
01837 <LI><B>PKTABLE_CAT</B> String => primary key table catalog
01838 being imported (may be null)
01839 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
01840 being imported (may be null)
01841 <LI><B>PKTABLE_NAME</B> String => primary key table name
01842 being imported
01843 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
01844 being imported
01845 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
01846 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
01847 <LI><B>FKTABLE_NAME</B> String => foreign key table name
01848 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
01849 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
01850 <LI><B>UPDATE_RULE</B> short => What happens to
01851 foreign key when primary is updated:
01852 <UL>
01853 <LI> importedNoAction - do not allow update of primary
01854 key if it has been imported
01855 <LI> importedKeyCascade - change imported key to agree
01856 with primary key update
01857 <LI> importedKeySetNull - change imported key to NULL if
01858 its primary key has been updated
01859 <LI> importedKeySetDefault - change imported key to default values
01860 if its primary key has been updated
01861 <LI> importedKeyRestrict - same as importedKeyNoAction
01862 (for ODBC 2.x compatibility)
01863 </UL>
01864 <LI><B>DELETE_RULE</B> short => What happens to
01865 the foreign key when primary is deleted.
01866 <UL>
01867 <LI> importedKeyNoAction - do not allow delete of primary
01868 key if it has been imported
01869 <LI> importedKeyCascade - delete rows that import a deleted key
01870 <LI> importedKeySetNull - change imported key to NULL if
01871 its primary key has been deleted
01872 <LI> importedKeyRestrict - same as importedKeyNoAction
01873 (for ODBC 2.x compatibility)
01874 <LI> importedKeySetDefault - change imported key to default if
01875 its primary key has been deleted
01876 </UL>
01877 <LI><B>FK_NAME</B> String => foreign key name (may be null)
01878 <LI><B>PK_NAME</B> String => primary key name (may be null)
01879 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
01880 constraints be deferred until commit
01881 <UL>
01882 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
01883 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
01884 <LI> importedKeyNotDeferrable - see SQL92 for definition
01885 </UL>
01886 </OL>
01887
01888 @param catalog a catalog name; "" retrieves those without a
01889 catalog; null means drop catalog name from the selection criteria
01890 @param schema a schema name; "" retrieves those
01891 without a schema
01892 @param table a table name
01893 @return <code>ResultSet</code> - each row is a primary key column description
01894 @see #getExportedKeys
01895 }
01896 function TZMsSqlDatabaseMetadata.UncachedGetImportedKeys(const Catalog: string;
01897 const Schema: string; const Table: string): IZResultSet;
01898 begin
01899 Result := UncachedGetCrossReference('', '', '', Catalog, Schema, Table);
01900 end;
01901
01902 {**
01903 Gets a description of the foreign key columns that reference a
01904 table's primary key columns (the foreign keys exported by a
01905 table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
01906 FKTABLE_NAME, and KEY_SEQ.
01907
01908 <P>Each foreign key column description has the following columns:
01909 <OL>
01910 <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
01911 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
01912 <LI><B>PKTABLE_NAME</B> String => primary key table name
01913 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
01914 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
01915 being exported (may be null)
01916 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
01917 being exported (may be null)
01918 <LI><B>FKTABLE_NAME</B> String => foreign key table name
01919 being exported
01920 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
01921 being exported
01922 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
01923 <LI><B>UPDATE_RULE</B> short => What happens to
01924 foreign key when primary is updated:
01925 <UL>
01926 <LI> importedNoAction - do not allow update of primary
01927 key if it has been imported
01928 <LI> importedKeyCascade - change imported key to agree
01929 with primary key update
01930 <LI> importedKeySetNull - change imported key to NULL if
01931 its primary key has been updated
01932 <LI> importedKeySetDefault - change imported key to default values
01933 if its primary key has been updated
01934 <LI> importedKeyRestrict - same as importedKeyNoAction
01935 (for ODBC 2.x compatibility)
01936 </UL>
01937 <LI><B>DELETE_RULE</B> short => What happens to
01938 the foreign key when primary is deleted.
01939 <UL>
01940 <LI> importedKeyNoAction - do not allow delete of primary
01941 key if it has been imported
01942 <LI> importedKeyCascade - delete rows that import a deleted key
01943 <LI> importedKeySetNull - change imported key to NULL if
01944 its primary key has been deleted
01945 <LI> importedKeyRestrict - same as importedKeyNoAction
01946 (for ODBC 2.x compatibility)
01947 <LI> importedKeySetDefault - change imported key to default if
01948 its primary key has been deleted
01949 </UL>
01950 <LI><B>FK_NAME</B> String => foreign key name (may be null)
01951 <LI><B>PK_NAME</B> String => primary key name (may be null)
01952 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
01953 constraints be deferred until commit
01954 <UL>
01955 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
01956 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
01957 <LI> importedKeyNotDeferrable - see SQL92 for definition
01958 </UL>
01959 </OL>
01960
01961 @param catalog a catalog name; "" retrieves those without a
01962 catalog; null means drop catalog name from the selection criteria
01963 @param schema a schema name; "" retrieves those
01964 without a schema
01965 @param table a table name
01966 @return <code>ResultSet</code> - each row is a foreign key column description
01967 @see #getImportedKeys
01968 }
01969 function TZMsSqlDatabaseMetadata.UncachedGetExportedKeys(const Catalog: string;
01970 const Schema: string; const Table: string): IZResultSet;
01971 begin
01972 Result := UncachedGetCrossReference(Catalog, Schema, Table, '', '', '');
01973 end;
01974
01975 {**
01976 Gets a description of the foreign key columns in the foreign key
01977 table that reference the primary key columns of the primary key
01978 table (describe how one table imports another's key.) This
01979 should normally return a single foreign key/primary key pair
01980 (most tables only import a foreign key from a table once.) They
01981 are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
01982 KEY_SEQ.
01983
01984 <P>Each foreign key column description has the following columns:
01985 <OL>
01986 <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
01987 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
01988 <LI><B>PKTABLE_NAME</B> String => primary key table name
01989 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
01990 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
01991 being exported (may be null)
01992 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
01993 being exported (may be null)
01994 <LI><B>FKTABLE_NAME</B> String => foreign key table name
01995 being exported
01996 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
01997 being exported
01998 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
01999 <LI><B>UPDATE_RULE</B> short => What happens to
02000 foreign key when primary is updated:
02001 <UL>
02002 <LI> importedNoAction - do not allow update of primary
02003 key if it has been imported
02004 <LI> importedKeyCascade - change imported key to agree
02005 with primary key update
02006 <LI> importedKeySetNull - change imported key to NULL if
02007 its primary key has been updated
02008 <LI> importedKeySetDefault - change imported key to default values
02009 if its primary key has been updated
02010 <LI> importedKeyRestrict - same as importedKeyNoAction
02011 (for ODBC 2.x compatibility)
02012 </UL>
02013 <LI><B>DELETE_RULE</B> short => What happens to
02014 the foreign key when primary is deleted.
02015 <UL>
02016 <LI> importedKeyNoAction - do not allow delete of primary
02017 key if it has been imported
02018 <LI> importedKeyCascade - delete rows that import a deleted key
02019 <LI> importedKeySetNull - change imported key to NULL if
02020 its primary key has been deleted
02021 <LI> importedKeyRestrict - same as importedKeyNoAction
02022 (for ODBC 2.x compatibility)
02023 <LI> importedKeySetDefault - change imported key to default if
02024 its primary key has been deleted
02025 </UL>
02026 <LI><B>FK_NAME</B> String => foreign key name (may be null)
02027 <LI><B>PK_NAME</B> String => primary key name (may be null)
02028 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
02029 constraints be deferred until commit
02030 <UL>
02031 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
02032 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
02033 <LI> importedKeyNotDeferrable - see SQL92 for definition
02034 </UL>
02035 </OL>
02036
02037 @param primaryCatalog a catalog name; "" retrieves those without a
02038 catalog; null means drop catalog name from the selection criteria
02039 @param primarySchema a schema name; "" retrieves those
02040 without a schema
02041 @param primaryTable the table name that exports the key
02042 @param foreignCatalog a catalog name; "" retrieves those without a
02043 catalog; null means drop catalog name from the selection criteria
02044 @param foreignSchema a schema name; "" retrieves those
02045 without a schema
02046 @param foreignTable the table name that imports the key
02047 @return <code>ResultSet</code> - each row is a foreign key column description
02048 @see #getImportedKeys
02049 }
02050 function TZMsSqlDatabaseMetadata.UncachedGetCrossReference(const PrimaryCatalog: string;
02051 const PrimarySchema: string; const PrimaryTable: string; const ForeignCatalog: string;
02052 const ForeignSchema: string; const ForeignTable: string): IZResultSet;
02053 begin
02054 Result := ConstructVirtualResultSet(CrossRefColumnsDynArray);
02055
02056 with GetStatement.ExecuteQuery(
02057 Format('exec sp_fkeys %s, %s, %s, %s, %s, %s',
02058 [AQSNull(PrimaryTable), AQSNull(PrimarySchema), AQSNull(PrimaryCatalog),
02059 AQSNull(ForeignTable), AQSNull(ForeignSchema), AQSNull(ForeignCatalog)])) do
02060 begin
02061 while Next do
02062 begin
02063 Result.MoveToInsertRow;
02064 Result.UpdateStringByName('PKTABLE_CAT',
02065 GetStringByName('PKTABLE_QUALIFIER'));
02066 Result.UpdateStringByName('PKTABLE_SCHEM',
02067 GetStringByName('PKTABLE_OWNER'));
02068 Result.UpdateStringByName('PKTABLE_NAME',
02069 GetStringByName('PKTABLE_NAME'));
02070 Result.UpdateStringByName('PKCOLUMN_NAME',
02071 GetStringByName('PKCOLUMN_NAME'));
02072 Result.UpdateStringByName('FKTABLE_CAT',
02073 GetStringByName('FKTABLE_QUALIFIER'));
02074 Result.UpdateStringByName('FKTABLE_SCHEM',
02075 GetStringByName('FKTABLE_OWNER'));
02076 Result.UpdateStringByName('FKTABLE_NAME',
02077 GetStringByName('FKTABLE_NAME'));
02078 Result.UpdateStringByName('FKCOLUMN_NAME',
02079 GetStringByName('FKCOLUMN_NAME'));
02080 Result.UpdateShortByName('KEY_SEQ',
02081 GetShortByName('KEY_SEQ'));
02082 Result.UpdateShortByName('UPDATE_RULE',
02083 GetShortByName('UPDATE_RULE'));
02084 Result.UpdateShortByName('DELETE_RULE',
02085 GetShortByName('DELETE_RULE'));
02086 Result.UpdateStringByName('FK_NAME',
02087 GetStringByName('FK_NAME'));
02088 Result.UpdateStringByName('PK_NAME',
02089 GetStringByName('PK_NAME'));
02090 Result.UpdateIntByName('DEFERRABILITY', 0);
02091 Result.InsertRow;
02092 end;
02093 Close;
02094 end;
02095 Result.BeforeFirst;
02096 end;
02097
02098 {**
02099 Gets a description of all the standard SQL types supported by
02100 this database. They are ordered by DATA_TYPE and then by how
02101 closely the data type maps to the corresponding JDBC SQL type.
02102
02103 <P>Each type description has the following columns:
02104 <OL>
02105 <LI><B>TYPE_NAME</B> String => Type name
02106 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
02107 <LI><B>PRECISION</B> int => maximum precision
02108 <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
02109 (may be null)
02110 <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
02111 (may be null)
02112 <LI><B>CREATE_PARAMS</B> String => parameters used in creating
02113 the type (may be null)
02114 <LI><B>NULLABLE</B> short => can you use NULL for this type?
02115 <UL>
02116 <LI> typeNoNulls - does not allow NULL values
02117 <LI> typeNullable - allows NULL values
02118 <LI> typeNullableUnknown - nullability unknown
02119 </UL>
02120 <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
02121 <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
02122 <UL>
02123 <LI> typePredNone - No support
02124 <LI> typePredChar - Only supported with WHERE .. LIKE
02125 <LI> typePredBasic - Supported except for WHERE .. LIKE
02126 <LI> typeSearchable - Supported for all WHERE ..
02127 </UL>
02128 <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
02129 <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
02130 <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
02131 auto-increment value?
02132 <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
02133 (may be null)
02134 <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
02135 <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
02136 <LI><B>SQL_DATA_TYPE</B> int => unused
02137 <LI><B>SQL_DATETIME_SUB</B> int => unused
02138 <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
02139 </OL>
02140
02141 @return <code>ResultSet</code> - each row is an SQL type description
02142 }
02143 function TZMsSqlDatabaseMetadata.UncachedGetTypeInfo: IZResultSet;
02144 begin
02145 Result := ConstructVirtualResultSet(TypeInfoColumnsDynArray);
02146
02147 with GetStatement.ExecuteQuery('exec sp_datatype_info') do
02148 begin
02149 while Next do
02150 begin
02151 Result.MoveToInsertRow;
02152 Result.UpdateStringByName('TYPE_NAME',
02153 GetStringByName('TYPE_NAME'));
02154 Result.UpdateShortByName('DATA_TYPE',
02155 Ord(ConvertODBCToSqlType(GetShortByName('DATA_TYPE'))));
02156 Result.UpdateIntByName('PRECISION',
02157 GetIntByName('PRECISION'));
02158 Result.UpdateStringByName('LITERAL_PREFIX',
02159 GetStringByName('LITERAL_PREFIX'));
02160 Result.UpdateStringByName('LITERAL_SUFFIX',
02161 GetStringByName('LITERAL_SUFFIX'));
02162 Result.UpdateStringByName('CREATE_PARAMS',
02163 GetStringByName('CREATE_PARAMS'));
02164 Result.UpdateShortByName('NULLABLE',
02165 GetShortByName('NULLABLE'));
02166 Result.UpdateBooleanByName('CASE_SENSITIVE',
02167 GetShortByName('CASE_SENSITIVE') = 1);
02168 Result.UpdateShortByName('SEARCHABLE',
02169 GetShortByName('SEARCHABLE'));
02170 Result.UpdateBooleanByName('UNSIGNED_ATTRIBUTE',
02171 GetShortByName('UNSIGNED_ATTRIBUTE') = 1);
02172 Result.UpdateBooleanByName('FIXED_PREC_SCALE',
02173 GetShortByName('MONEY') = 1);
02174 Result.UpdateBooleanByName('AUTO_INCREMENT',
02175 GetShortByName('AUTO_INCREMENT') = 1);
02176 Result.UpdateStringByName('LOCAL_TYPE_NAME',
02177 GetStringByName('LOCAL_TYPE_NAME'));
02178 Result.UpdateShortByName('MINIMUM_SCALE',
02179 GetShortByName('MINIMUM_SCALE'));
02180 Result.UpdateShortByName('MAXIMUM_SCALE',
02181 GetShortByName('MAXIMUM_SCALE'));
02182 Result.UpdateShortByName('SQL_DATA_TYPE',
02183 GetShortByName('SQL_DATA_TYPE'));
02184 Result.UpdateShortByName('SQL_DATETIME_SUB',
02185 GetShortByName('SQL_DATETIME_SUB'));
02186 Result.UpdateShortByName('NUM_PREC_RADIX',
02187 GetShortByName('NUM_PREC_RADIX'));
02188 Result.InsertRow;
02189 end;
02190 Close;
02191 end;
02192 Result.BeforeFirst;
02193 end;
02194
02195 {**
02196 Gets a description of a table's indices and statistics. They are
02197 ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
02198
02199 <P>Each index column description has the following columns:
02200 <OL>
02201 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
02202 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
02203 <LI><B>TABLE_NAME</B> String => table name
02204 <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
02205 false when TYPE is tableIndexStatistic
02206 <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
02207 null when TYPE is tableIndexStatistic
02208 <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
02209 tableIndexStatistic
02210 <LI><B>TYPE</B> short => index type:
02211 <UL>
02212 <LI> tableIndexStatistic - this identifies table statistics that are
02213 returned in conjuction with a table's index descriptions
02214 <LI> tableIndexClustered - this is a clustered index
02215 <LI> tableIndexHashed - this is a hashed index
02216 <LI> tableIndexOther - this is some other style of index
02217 </UL>
02218 <LI><B>ORDINAL_POSITION</B> short => column sequence number
02219 within index; zero when TYPE is tableIndexStatistic
02220 <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
02221 tableIndexStatistic
02222 <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending,
02223 "D" => descending, may be null if sort sequence is not supported;
02224 null when TYPE is tableIndexStatistic
02225 <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
02226 this is the number of rows in the table; otherwise, it is the
02227 number of unique values in the index.
02228 <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
02229 this is the number of pages used for the table, otherwise it
02230 is the number of pages used for the current index.
02231 <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
02232 (may be null)
02233 </OL>
02234
02235 @param catalog a catalog name; "" retrieves those without a
02236 catalog; null means drop catalog name from the selection criteria
02237 @param schema a schema name; "" retrieves those without a schema
02238 @param table a table name
02239 @param unique when true, return only indices for unique values;
02240 when false, return indices regardless of whether unique or not
02241 @param approximate when true, result is allowed to reflect approximate
02242 or out of data values; when false, results are requested to be
02243 accurate
02244 @return <code>ResultSet</code> - each row is an index column description
02245 }
02246 function TZMsSqlDatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
02247 const Schema: string; const Table: string; Unique: Boolean;
02248 Approximate: Boolean): IZResultSet;
02249 var
02250 Is_Unique, Accuracy: string;
02251 begin
02252 Result := ConstructVirtualResultSet(IndexInfoColumnsDynArray);
02253
02254 if Unique then
02255 Is_Unique := '''Y'''
02256 else Is_Unique := '''N''';
02257 if Approximate then
02258 Accuracy := '''Q'''
02259 else Accuracy := '''E''';
02260
02261 with GetStatement.ExecuteQuery(
02262 Format('exec sp_statistics %s, %s, %s, ''%%'', %s, %s',
02263 [AQSNull(Table), AQSNull(Schema), AQSNull(Catalog), Is_Unique, Accuracy])) do
02264 begin
02265 while Next do
02266 begin
02267 Result.MoveToInsertRow;
02268 Result.UpdateStringByName('TABLE_CAT',
02269 GetStringByName('TABLE_QUALIFIER'));
02270 Result.UpdateStringByName('TABLE_SCHEM',
02271 GetStringByName('TABLE_OWNER'));
02272 Result.UpdateStringByName('TABLE_NAME',
02273 GetStringByName('TABLE_NAME'));
02274 Result.UpdateBooleanByName('NON_UNIQUE',
02275 GetShortByName('NON_UNIQUE') = 1);
02276 Result.UpdateStringByName('INDEX_QUALIFIER',
02277 GetStringByName('INDEX_QUALIFIER'));
02278 Result.UpdateStringByName('INDEX_NAME',
02279 GetStringByName('INDEX_NAME'));
02280 Result.UpdateShortByName('TYPE',
02281 GetShortByName('TYPE'));
02282 Result.UpdateShortByName('ORDINAL_POSITION',
02283 GetShortByName('SEQ_IN_INDEX'));
02284 Result.UpdateStringByName('COLUMN_NAME',
02285 GetStringByName('COLUMN_NAME'));
02286 Result.UpdateStringByName('ASC_OR_DESC',
02287 GetStringByName('COLLATION'));
02288 Result.UpdateIntByName('CARDINALITY',
02289 GetIntByName('CARDINALITY'));
02290 Result.UpdateIntByName('PAGES',
02291 GetIntByName('PAGES'));
02292 Result.UpdateStringByName('FILTER_CONDITION',
02293 GetStringByName('FILTER_CONDITION'));
02294 Result.InsertRow;
02295 end;
02296 Close;
02297 end;
02298 Result.BeforeFirst;
02299 end;
02300
02301 {**
02302 Does the database support the given result set type?
02303 @param type defined in <code>java.sql.ResultSet</code>
02304 @return <code>true</code> if so; <code>false</code> otherwise
02305 }
02306 function TZMsSqlDatabaseMetadata.SupportsResultSetType(
02307 _Type: TZResultSetType): Boolean;
02308 begin
02309 Result := True;
02310 end;
02311
02312 {**
02313 Does the database support the concurrency type in combination
02314 with the given result set type?
02315
02316 @param type defined in <code>java.sql.ResultSet</code>
02317 @param concurrency type defined in <code>java.sql.ResultSet</code>
02318 @return <code>true</code> if so; <code>false</code> otherwise
02319 }
02320 function TZMsSqlDatabaseMetadata.SupportsResultSetConcurrency(
02321 _Type: TZResultSetType; Concurrency: TZResultSetConcurrency): Boolean;
02322 begin
02323 Result := True;
02324 end;
02325
02326 {**
02327
02328 Gets a description of the user-defined types defined in a particular
02329 schema. Schema-specific UDTs may have type JAVA_OBJECT, STRUCT,
02330 or DISTINCT.
02331
02332 <P>Only types matching the catalog, schema, type name and type
02333 criteria are returned. They are ordered by DATA_TYPE, TYPE_SCHEM
02334 and TYPE_NAME. The type name parameter may be a fully-qualified
02335 name. In this case, the catalog and schemaPattern parameters are
02336 ignored.
02337
02338 <P>Each type description has the following columns:
02339 <OL>
02340 <LI><B>TYPE_CAT</B> String => the type's catalog (may be null)
02341 <LI><B>TYPE_SCHEM</B> String => type's schema (may be null)
02342 <LI><B>TYPE_NAME</B> String => type name
02343 <LI><B>CLASS_NAME</B> String => Java class name
02344 <LI><B>DATA_TYPE</B> String => type value defined in java.sql.Types.
02345 One of JAVA_OBJECT, STRUCT, or DISTINCT
02346 <LI><B>REMARKS</B> String => explanatory comment on the type
02347 </OL>
02348
02349 <P><B>Note:</B> If the driver does not support UDTs, an empty
02350 result set is returned.
02351
02352 @param catalog a catalog name; "" retrieves those without a
02353 catalog; null means drop catalog name from the selection criteria
02354 @param schemaPattern a schema name pattern; "" retrieves those
02355 without a schema
02356 @param typeNamePattern a type name pattern; may be a fully-qualified name
02357 @param types a list of user-named types to include (JAVA_OBJECT,
02358 STRUCT, or DISTINCT); null returns all types
02359 @return <code>ResultSet</code> - each row is a type description
02360 }
02361 function TZMsSqlDatabaseMetadata.UncachedGetUDTs(const Catalog: string;
02362 const SchemaPattern: string; const TypeNamePattern: string;
02363 const Types: TIntegerDynArray): IZResultSet;
02364 begin
02365 Result := ConstructVirtualResultSet(UDTColumnsDynArray);
02366
02367 { if SchemaPattern = '' then
02368 SchemaPattern := '''%'''
02369 else SchemaPattern := AnsiQuotedStr(SchemaPattern, '''');
02370 if TypeNamePattern = '' then
02371 TypeNamePattern := '''%'''
02372 else TypeNamePattern := AnsiQuotedStr(TypeNamePattern, '''');}
02373
02374 // with GetStatement.ExecuteQuery(
02375 // Format('select TYPE_CAT = db_name(), TYPE_SCHEM = user_name(uid), TYPE_NAME = st.name, DATA_TYPE from master.dbo.spt_datatype_info sti left outer join systypes st on (sti.ss_dtype = st.xtype) where st.xusertype > 255 and user_name(uid) like %s and st.name like %s', [SchemaPattern, TypeNamePattern])) do
02376 // while Next do
02377 // begin
02378 // Result.MoveToInsertRow;
02379 // Result.UpdateString('TYPE_CAT', GetString('TYPE_CAT'));
02380 // Result.UpdateString('TYPE_SCHEM', GetString('TYPE_SCHEM'));
02381 // Result.UpdateString('TYPE_NAME', GetString('TYPE_NAME'));
02382 // Result.UpdateNull('JAVA_CLASS');
02383 // Result.UpdateShort('DATA_TYPE', GetShort('DATA_TYPE'));
02384 // Result.UpdateNull('REMARKS');
02385 // Result.InsertRow;
02386 // end;
02387 end;
02388
02389 function TZMsSqlDatabaseMetadata.GetStatement: IZStatement;
02390 begin
02391 Result := GetConnection.CreateStatement;
02392 end;
02393
02394 end.
02395
02396