|
Using SQL Server data access provider with UniDAC in Delphi |
|
|
|
Using SQL Server data access provider with UniDAC in Delphi |
|
This article provides a brief overview of the SQL Server data access provider for UniDAC used to establish a connection to SQL Server databases from Delphi and Lazarus. You will find the description of some useful features and how to get started quickly.
SQL Server data access provider is based on the SQL Server Data Access Components (SDAC) library, which is one of the best known Delphi data access solutions for SQL Server. The main features of SQL Server data access provider are:
The full list of SQL Server provider features can be found on the UniDAC features page.
Both Professional and Standard Editions of UniDAC include the SQL Server provider. For Expess Edition of UniDAC, the SQL Server provider can be installed with SDAC.
To learn about SQL Server compatibility, refer to the Compatibility section.
SQL Server provider requires OLE DB or SQL Native Client installed on workstation.
In the current versions of Microsoft Windows, since Windows 2000, OLE DB is already included as a standard package. But it's highly recommended to download the latest version (higher than 2.5) of Microsoft Data Access Components (MDAC) or SQL Native Provider.
Some features of SQL Server 2005 are available only with SQL Native Provider.
If you are working with SQL Server Compact Edition, you should have it installed. You can download SQL Server Compact Edition from the site of Microsoft.
When an application was built without runtime packages (Link with runtime packages set to False in Project Options), you do not need to deploy any BPL files with it. For more information, see Deployment.
Note that UniDAC Trial requires deployment of additional BPL files regardless of Link with runtime packages.
Though UniDAC is components that provide unified interface to work with different database servers, it also lets you tune the behaviour for each server individually. For thin setup of a certain database server, UniDAC provides server-specific options. These options can be applied to such components as TUniConnection, TUniQuery, TUniTable, TUniStoredProc, TUniSQL, TUniScript via their SpecificOptions property. SpecificOptions is a string list. Therefore you can use the following syntax to assign an option value:
UniConnection.SpecificOptions.Values['ApplicationName'] := 'My application';Below you will find the description of allowed options grouped by components.
| Option name | Description |
|---|---|
| CommandTimeout | Use CommandTimeout to specify the amount of time that expires before an attempt to execute a command is considered unsuccessful. Is measured in seconds.
If a command is successfully executed prior to the expiration of the seconds specified, CommandTimeout has no effect. The default value is 0 (infinite). |
| DescribeParams | Specifies whether to query the Name, ParamType, DataType, Size, and TableTypeName properties from the server when preparing a query. The default value is False. |
| NonBlocking | Used to execute an SQL statement in a separate thread. Set the NonBlocking option to True to fetch rows in a separate thread. |
| Option name | Description |
|---|---|
| CheckRowVersion | Determines whether the dataset checks for rows modifications made by another user on automatic generation of SQL statement for update or delete data. If CheckRowVersion property is False and DataSet has keyfields, the WHERE clause of SQL statement is generated basing on these keyfields. If there is no primary key and no Identity field, then all non-BLOB fields will take part in generating SQL statements. If CheckRowVersion is True and DataSet has TIMESTAMP field, only this field is included into WHERE clause of generated SQL statement. Otherwise, all non BLOB fields are included. All mentioned fields refer to the current UpdatingTable. The default value is False. The CheckRowVersion option requires enabled DMLRefresh. |
| CommandTimeout | Use CommandTimeout to specify the amount of time that expires before an attempt to execute a command is considered unsuccessful. Is measured in seconds.
If a command is successfully executed prior to the expiration of the seconds specified, CommandTimeout has no effect. The default value is 0 (infinite). |
| CursorType | Allows choosing cursor types supported by SQL Server. The available values are:
ctBaseTable
Base table cursor. This cursor is used for working with Compact Edition.
This cursor is the fastest of the SQL server cursors and the only cursor that interacts directly with the storage engine.
This allows to increase the speed of data access several times. Data modifications, deletions, and insertions by other users are visible.
If UniDirectional=False, the cursor is used only when fetching data, and Data updates are reflected on database by SQL statements execution.
In order to use the cursor also for data modification it is necessary to set the UniDirectional property to True.
But in this case the cursor does not support bookmarks and cannot be represented in multiline controls such as DBGrid.
ctDefaultResultSet (default)
By the old SQL Server terminology is the Firehose cursor. It serves for the fastest data fetch from server to the client side.
Allows to run batches. Data updates are reflected in the database only by SQL statements execution. The default value.
ctDynamic
Dynamic cursor. Used when data is not cached at the server and fetch is performed row by row as required.
Doesn't support bookmarks and cannot be represented in multiline controls such as DBGrid.
Data modifications, deletions, and insertions by other users are visible. Data updates are reflected on database both by
SQL statements execution and server cursors means.
ctKeyset
Allows to cache only keyfields at the server. Fetching is performed row by row when a data-aware component or a program requests it.
Records added by other users are not visible, and records deleted by other users are inaccessible. Data updates are reflected in the
database both by SQL statements execution and server cursors means.
ctStatic
Static copying of records. Query execution results are cashed at the server. Fetch is performed row by row when a data-aware component or
a program requests it. When a cursor is opened, all newly added updates are invisible. Used mostly for reporting.
|
| CursorUpdate | Specifies what way data updates reflect on database when modifying dataset by using server cursors ctKeySet and ctDynamic. If the CursorUpdate property is True, all dataset modifications pass to database by server cursors. If the CursorUpdate property is False, all dataset updates pass to server by SQL statements generated automatically or specified in SQLUpdate, SQLInsert or SQLDelete. The default value is True. |
| DescribeParams | Specifies whether to query the Name, ParamType, DataType, Size, and TableTypeName properties from the server when preparing a query. The default value is False. |
| DisableMultipleResults | Use the option to disable support for the Multiple Active Result Sets (MARS) technology, which allows applications to have multiple pending requests per connection and multiple default result sets per connection. The default value is False. |
| FetchAll | If True, all records of the query are requested from the database server when the dataset is being opened. If False, records are retrieved when a data-aware component or a program requests it. The default value is True. |
| HideSystemUniqueFields | Used the option to hide system fields for the prSQL, prNativeClient and prMSOLEDB providers. The default value is True. |
| LastIdentityValueFunction |
Determines which system function to use to obtain an identifier when adding a record. The available values are:
vfIdentCurrent
The IDENT_CURRENT system function is used. It returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.
vfIdentity
The @@IDENTITY system function is used. It returns the last-inserted identity value.
vfScopeIdentity
The SCOPE_IDENTITY system function is used. It returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.
|
| NonBlocking | Set the NonBlocking option to True to fetch rows in a separate thread. The BeforeFetch event is called in the additional thread context that performs data fetching. This event is called every time on the Fetch method call. The AfterFetch event is called in the main thread context only once after fetching is completely completed. In the NonBlocking mode, as well as if FetchAll = False, an extra connection is created. When setting the NonBlocking option to True, you should keep in mind that execution of such queries blocks the current session. In order to avoid blocking, OLE DB creates an additional session as in FetchAll = False. It causes the same problems when FetchAll = False. This problem can be solved by using MARS (the specific option MultipleActiveResultSets = True). The current session is not blocked and OLE DB is not required to create an addition session to run a query. MARS is supported since SQL Server 2005 if SQL Native Client is used as OLE DB provider. |
| QueryIdentity | Specifies whether to request Identity field value, if such exists, on execution Insert or Append method. If to refuse of getting Identity you can have an impact on performance of Insert or Append by about 20%. Affects only for ctDefaultResultSet cursor. If you are inserting value into SQL_VARIANT field, and QueryIdentity is True then an error is raised. The default value is True. |
| UniqueRecords | Use UniqueRecords to specify whether to query additional key fields from the server. If UniqueRecords is False, keyfields are not queried from the server when they are not included in the query explicitly. For example, the result of the query execution "SELECT ShipName FROM Orders" holds the only field ShipName. When used with ReadOnly property set to True, UniqueRecords option gives insignificant advantage of performance. But in this case SQLRefresh will be generated in simplified way. If UniqueRecord is True, keyfields needed for complete automatic generation of SQLInsert, SQLUpdate, SQLDelete or SQLRefresh statements are queried from the server implicitly. For example, the result of query execution "SELECT ShipName FROM Orders" holds at least two fields ShipName and OrderID. The default value is False. Has effect only for ctDefaultResultSet cursor. |
The TUniScript component has no SQL Server-specific options.
| Option name | Description |
|---|---|
| FireTrigger | Use the option to fire table triggers with TMSLoader on SQL Server during insertion operations. The default value is False. |
| KeepIdentity | Use the KeepIdentity property to specify in what way IDENTITY column values must be handled. If KeepIdentity is set to False, IDENTITY columns will be initialized by the server. Any value assigned to such column in your application is ignored. If KeepIdentity is set to True, the IDENTITY property will not be available for all IDENTITY fields accepting NULL. So in this case unique values should be generated and assigned by the client application. The default value of the KeepIdentity property is False. |
| KeepNulls | If this option is set to False, each NULL value inserted into a field with a DEFAULT constraint will be replaced with the default value. If KeepNulls is set to True, NULL values inserted into a field with a DEFAULT constraint will not be replaced with the default values. The default value of the KeepNulls property is False. |
| RowsPerBatch | Use the RowsPerBatch property to specify the number of rows to load in a single batch. Server optimizes loading according to this value. The default value of this option is Unknown. |
| KilobytesPerBatch | Use the KilobytesPerBatch option to specify the size of data in kilobytes to load in a single batch. The default value of this option is Unknown. |
| LockTable | Use the LockTable property to specify if the table-level lock is performed while loading is in progress. Setting this option to True should improve the performance greatly. If this option is set to False, the locking behaviour is determined by the table option. The default value of the LockTable option is False. |
| CheckConstraints | Use the CheckConstraints property to specify if the table constraints are checked during loading. If this option is set to False, the table constraints are not checked. The default value of the CheckConstraints option is False. |
| QuoteNames | Use the QuoteNames option to quote all database object names in automatically generated SQL statements, such as UPDATE statements. The default value is False. |
| Option name | Description |
|---|---|
| IdentityInsert | Use the IdentityInsert property to add SET IDENTITY_INSERT TableName ON at the beginning of the script and SET IDENTITY_INSERT TableName OFF at the end of the script. The first line allows explicit values to be inserted into the identity column of a table and INSERT statements are generated with IDENTITY field values. Otherwise the IDENTITY field will not be included to the INSERT statements. SET IDENTITY_INSERT will not be added while the option is ON if the table does not have a field identified as IDENTITY or there are no records in the table. |
By default, the OLE DB interface is used directly through a set of COM-based interfaces to connect to server. Such approach allows using client applications on Windows workstations only.
To overcome these problems, the prDirect value for the Provider property was added for ability to connect to SQL Server directly over the network using the TCP/IP protocol. This is referred to as connecting in the Direct mode. Connection in the Direct mode does not require OLEDB provider or SQL Native Client provider to be installed on target machines. The only requirement for running an UniDAC-based application that uses the Direct mode is that the operating system must support the TCP/IP protocol.
Here is an example that illustrates connecting to SQL Server in the Direct mode. The server's IP address is 205.227.44.44, its port number is 1433 (this is the most commonly used port for SQL Server).
var
UniConnection: TUniConnection;
. . .
UniConnection.ProviderName := 'SQL Server';
UniConnection.SpecificOptions.Values['Provider'] := 'prDirect';
UniConnection.SpecificOptions.Values['Authentication'] := 'auServer';
UniConnection.Username := 'sa';
UniConnection.Password := '';
UniConnection.Server := '205.227.44.44';
UniConnection.Port := 1433;
UniConnection.Connect;All we have to do is to set the TUniConnection.Options.Provider property to prDirect to enable Direct mode connections in your application. You do not have to rewrite other parts of your code.
The following table lists the constants for mapping SQL Server data types to Delphi data types. See Data Type Mapping for more information.
| Constant | Description |
|---|---|
| msBigint | Maps bigint to Delphi data types. |
| msBinary | Maps binary to Delphi data types. |
| msBit | Maps bit to Delphi data types. |
| msChar | Maps char to Delphi data types. |
| msDate | Maps date to Delphi data types. |
| msDatetime | Maps datetime to Delphi data types. |
| msDatetime2 | Maps datetime2 to Delphi data types. |
| msDatetimeoffset | Maps datetimeoffset to Delphi data types. |
| msDecimal | Maps decimal to Delphi data types. |
| msFloat | Maps float to Delphi data types. |
| msImage | Maps image to Delphi data types. |
| msInt | Maps int to Delphi data types. |
| msMoney | Maps money to Delphi data types. |
| msNChar | Maps nchar to Delphi data types. |
| msNText | Maps ntext to Delphi data types. |
| msNumeric | Maps numeric to Delphi data types. |
| msNVarchar | Maps nvarchar to Delphi data types. |
| msReal | Maps real to Delphi data types. |
| msSmalldatetime | Maps smalldatetime to Delphi data types. |
| msSmallint | Maps smallint to Delphi data types. |
| msSmallmoney | Maps smallmoney to Delphi data types. |
| msSqlVariant | Maps sql_variant to Delphi data types. |
| msText | Maps text to Delphi data types. |
| msTime | Maps time to Delphi data types. |
| msTimestamp | Maps timestamp to Delphi data types. |
| msTinyint | Maps tinyint to Delphi data types. |
| msUniQueIdentifier | Maps uniqueidentifier to Delphi data types. |
| msVarbinary | Maps varbinary to Delphi data types. |
| msVarchar | Maps varchar to Delphi data types. |
| msXml | Maps xml to Delphi data types. |