UniDAC

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.

Overview

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 at the UniDAC product 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.

Compatibility

To learn about SQL Server compatibility, refer to the Compatibility section.

Requirements

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.

Deployment

To deploy Win32 applications built with run-time packages it is not required to deploy the sdacXX.bpl file with UniDAC Professional and Standard editions. But it is necessary to deploy the sdacXX.bpl file with Express Edition of UniDAC. This happens because in the UniDAC Professional and Standard editions functionality of sdacXX.bpl is included in the correspondent msproviderXX.bpl, when in Express Edition of UniDAC, msproviderXX.bpl is just a wrapper on sdacXX.bpl.

For more information about deployment of UniDAC-based applications, please, refer to the common Deployment topic.

SQL Server-specific options

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.

TUniConnection

Option name Description
ApplicationIntent Specifies the application workload type when connecting to a server.
ApplicationName The name of a client application. The default value is the name of the executable file of your application.
Authentication Use the Authentication property to specify authentication service used by the database server to identify a user. The Authentication property accepts one of the following values:

auWindows
Uses Windows NT/2000/XP integrated security, or "SSPI" (Security Support Provider Interface). Username, Password and LoginPrompt properties are ignored.
auServer (default)
An alternative way of identifying users by database server. To establish a connection valid Username and Password either hardcoded into application or provided in server login prompt fields are required.
AutoTranslate When set to True, character strings sent between the client and server are translated by converting through Unicode to minimize problems in matching extended characters between the code pages on the client and the server.
CompactAutoShrinkThreshold Specifies the amount of free space in the database file before automatic shrink will start. Measured in percents. The default value is 60.
CompactDefaultLockEscalation Specifies how many locks should be performed before trying escalation from row to page or from page to table. The default value is 100.
CompactDefaultLockTimeout Specifies how much time a transaction will wait for a lock. The default value is 2000.
Compact FlushInterval Specifies the interval at which committed transactions are flushed to disk. Measured in seconds. The default value is 10.
CompactInitMode Use this property to specify the file mode that will be used to open the database file. The InitMode property accepts one of the following values:

imExclusive
Database file is opened for exclusive use. This mode prevents others from opening this database file.
imReadOnly
Database file is opened for reading. All operations that write to database are unallowable.
imReadWrite (default)
Both read and write operations are allowed.
imShareRead
Opens a database file preventing others from opening the same file in the read mode.
CompaLockEscalation Specifies how many locks should be performed before trying escalation from row to page or from page to table. Measured in milliseconds. The default value is 100.
CompactLockTimeout Specifies how much time a transaction will wait for a lock. Measured in milliseconds. The default value is 2000.
CompactMaxBufferSize Specifies how much memory SQL Server Compact Edition can use before flushing changes to disc. Measured in kilobytes. The default value is 640.
CompactMaxDatabaseSize Specified maximum size of the main database file. Measured in megabytes. The default value is 128.
CompactTempFileDirectory Specifies the temp file directory. If this option is not assigned, the current database is used as a temporary database.
CompactTempFileMaxSize Specified maximum size of the temporary database file. Measured in megabytes. The default value is 128.
CompactTransactionCommitMode Specifies in what way the buffer pool will be flushed on transaction commit. The following two values are allowed:
cmAsynchCommit
Asynchronous commit to disk.
cmSynchCommit (default)
Synchronous commit to disk.
CompactVersion Specifies which version of SQL Server Compact Edition will be used.

cvAuto (default)
Version of SQL Server Compact Edition will be chosen automatically depending on database version. If database is not provided, the higher available server version will be chosen.
cv30
Uses SQL Server Compact Edition Version 3.0 or 3.1.
cv35
Uses SQL Server Compact Edition Version 3.5.
ConnectionTimeout Use ConnectionTimeout to specify the amount of time, in seconds, that can expire before an attempt to consider a connection unsuccessful. The default value is 15 seconds.
Encrypt Specifies if data should be encrypted before sending it over the network. The default value is False.
FailoverPartner Specifies the SQL Server name to which SQL Native Client will reconnect when a failover of the principal SQL Server occurs. This option is supported only for SQL Server 2005 using SQL Native Client as an OLE DB provider.
ForceCreateDatabase

Used to force TLiteConnection to create a new database before opening a connection, if the database does not exist.

IPVersion Use the IPVersion property to specify Internet Protocol Version.

Supported values:

ivIPBoth
Specifies that either Internet Protocol Version 6 (IPv6) or Version 4 (IPv4) will be used.

ivIPv4 (default)
Specifies that Internet Protocol Version 4 (IPv4) will be used.

ivIPv6
Specifies that Internet Protocol Version 6 (IPv6) will be used.

Note: When the TIPVersion property is set to ivIPBoth, a connection attempt will be made via IPv6 if it is enabled on the operating system. If the connection attempt fails, a new connection attempt will be made via IPv4.
InitialFileName Specifies the name of the main database file. This database will be default database for the connection. SQL Server attaches the database to the server if it has not been attached to the server yet. So, this property can be used to connect to the database that has not been attached to the server yet.
Language A SQL Server language name. Identifies the language used for system message selection and formatting. The language must be installed on the computer running an instance of SQL Server otherwise the connection will fail.
MultipleActiveResultSets Enables support for SQL Server 2005 Multiple Active Result Sets (MARS) technology. It allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Current session is not blocked when using FetchAll = False, and it is not necessary for OLE DB to create additional sessions for any query executing. MARS is only supported by SQL Server 2005 with using SQL Native Client as OLE DB provider.
MultipleConnections Enables or disables the creation of additional connections to support concurrent sessions, commands and rowset objects.
NativeClientVersion Specifies which version of SQL Native Client will be used. The default value is ncAuto. NativeClientVersion is applied when the Provider property is set to prNativeClient or prAuto.
NetworkLibrary The name of the Net-Library (DLL) used to communicate with an instance of SQL Server. The name should not include the path or the .dll file name extension. The default name is provided by the SQL Server Client Network Utility.
OLEDBProvider This property is now deprecated. The Provider property is used instead.
PacketSize Network packet size in bytes. The packet size property value must be between 512 and 32,767. The default network packet size is 4,096.
PersistSecurityInfo The data source object is allowed to persist sensitive authentication information such as password along with other authentication information.
Provider This property allows you to specify a provider from the list of supported providers or use the Direct mode. Some features added to SQL  Server 2005 require the SQL Native Client (prNativeClient) provider to be used. If chosen provider is not installed, an exception is raised.

Supported values:

prAuto (default)
prAuto is the default value of the Provider property. With default value, UniDAC will use the most recent version of one of the supported providers in the following order:
  1. prNativeClient
  2. prMSOLEDB
  3. prSQL
First UniDAC checks whether SQL Server Native Client is installed in the system. If SQL Server Native Client is not found, UniDAC looks for Microsoft OLE DB Driver for SQL Server. If neither SQLNCLI nor MSOLEDBSQL is installed in the system, the driver will use Microsoft OLE DB Provider for SQL Server.


prSQL
Uses the provider preinstalled with Windows that has limited functionality.

prMSOLEDB
Uses Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). You need to have the driver installed on your system to use this value for Provider.

prNativeClient
Uses the SQL Native Client. It should be installed on the computer to use this Provider value. This provider offers the maximum functionality set.

prCompact
SQL Server Compact Edition provider.

prDirect
Connect to SQL Server directly via TCP/IP.
QuotedIdentifier Causes Microsoft® SQL Server™ to follow the SQL-92 rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not usually allowed by the Transact-SQL syntax rules for identifiers.
QuotedIdentifier must be True when creating or manipulating indexes on computed columns or indexed views. If QuotedIdentifier is False, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

True (default)
Identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks.
All strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not usually allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is a part of the literal string, it can be represented by two single quotation marks ("). QuotedIdentifier must be True when reserved keywords are used for object names in the database.
False (BDE compatibility)
Identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.
TrustServerCertificate Lets enabling traffic encryption without validation. The default value is False. This option is only supported by SQL Server 2005 with using SQL Native Client as OLE DB provider.
WorkstationID A string identifying the workstation. The default value is the name of your machine.

TUniSQL

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).
NonBlocking Used to execute an SQL statement in a separate thread. Set the NonBlocking option to True to fetch rows in a separate thread.

TUniQuery, TUniTable, TUniStoredProc

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.
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.
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.

TUniScript

The TUniScript component has no SQL Server-specific options.

TUniLoader

Option name Description
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.

TUniDump

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.

SQL Server specific notes

Connecting in Direct mode

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.

Setting up Direct mode connections

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.

© 1997-2019 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback