dbExpress driver for SQL Server 4.55	History
Readme	Support

dbExpress is database-independent layer that defines a common interface to provide fast access to SQL database servers. For each supported server, dbExpress provides a driver as an independent library that implements the common dbExpress interfaces for processing queries and stored procedures. As data-access layer is thin and simple, dbExpress provides high performance database connectivity and is easy to deploy. dbExpress drivers are available for Windows as dynamic-link libraries.

DbxSda driver provides access to Microsoft SQL Server database based on Borland dbExpress data access technology. It works using high performance Microsoft OLE DB technologies.

• Key features
• Compatibility
• Installation notes
• Using
• Example
• Type map
• Extended driver options
• TCRSQLConnection
• Using DBMonitor
• Deployment
• Trial limitations
• Notes
• Known problems
• Using SQL Server Compact Edition
• Demo projects

Key features

The main features of the DbxSda driver are listed below:

• High performance
• Support for SQL Server 2005 (including Compact Edition), SQL Server 2000, SQL Server 7, and MSDE
• Ability of monitoring query execution with DBMonitor
• Extended options for advanced behaviour
• Complete support for dbExpress version 4
• Source code available
• Free support for registered users
• Licensed per a developer without royalty fee

Compatibility

DbxSda works with RAD Studio 2010, RAD Studio 2009, RAD Studio 2007, Turbo Delphi, Turbo Delphi for .NET, Turbo C++, Delphi 2006, Delphi 2005, Delphi 8, Delphi 7, Delphi 6 and C++Builder 6.

DbxSda supports SQL Server 2005, SQL Server 2000, SQL Server 7 and MSDE. Driver requires OLE DB installed on workstation.

You should also pay attention to the following notes:

1. It is strongly recommended to use the corresponding library with your IDE. Please take a look at this table for more details.
2. In such versions of Microsoft Windows as Windows 2000 and higher, OLE DB is already included. But it is highly recommended to download the latest version (newer than 2.6) of Microsoft Data Access Components (MDAC) at this page.
3. Update Pack 2 is required for Delphi 2006 .NET Personality.
4. Update Pack 3 is required for C++Builder 2007 and Delphi 2007 for Win32.

Installation notes

RAD Studio 2007 and higher

The standard TSQLConnection component in RAD Studio 2007 and higher provides exactly the same functionality like TCRSQLConnection does. Therefore TCRSQLConnection component is not registered automatically. But source code of the package containing this component is installed for providing backward compatibility. If you need to use TCRSQLConnection, you should compile and install its packages manually. They are located in the <Driver_Installation_Directory>\Source\DelphiXX\ directory. The dclcrdbxXXX.dproj package is used with Delphi for Win32 and C++Builder. And the Devart.Dbx.dproj package is used with Delphi for .Net

If you have only C++Builder installed, you can compile the package from the command line using a command like the following one:

C:\Program Files\CodeGear\RAD Studio\5.0\bin\dcc32.exe" dclcrdbx105.dpk

To compile an application that uses TCRSQLConnection components, it is necessary to add the path to TCRSQLConnection sources to the Search path in Project Options or to the Library path in Environment Options:

• in Delphi for Win32 add <Driver_Installation_Directory>\Source;
• in Delphi for .Net add <Driver_Installation_Directory>\Source and <Driver_Installation_Directory>\Source\Net;
• in C++Builder add <Driver_Installation_Directory>\Source to the Library path and to the Include path in Project Options.

Delphi 2006

If you use TCRSQLConnection in applications developed with C++Builder Personality of Delphi 2006 or Turbo C++, you should add the path to the DbxSda folder to Project Options|Library and Project Options|Include.

Using

The use of DbxSda is the very same as one of standard dbExpress drivers supplied by Borland. For additional information refer to help on TSQLConnection component.

To setup SQLConnection at design time for using DbxSda just select 'SQLServerConnection' for ConnectionName property. At run-time you need to set LibraryName, VendorLib and GetDriverFunc properties. Set connection parameters such as User_Name, Password, HostName and Database to provide login information.

Borland Turbo products and Delphi 2006 introduce dbExpress 3.0 interfaces, CodeGear RAD Studio 2007 and higher introduce dbExpress 4.0. They are implemented in dbexpoda30.dll and dbexpoda40.dll correspondingly, and we highly recommend using the correspondent driver.

Example

  SQLConnection.DriverName := 'SQLServerConnection';
  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See this table for more information.
  SQLConnection.VendorLib := <provider name>;  // This value specfies which provider to use. See this table for more information.
  SQLConnection.GetDriverFunc := 'getSQLDriverSQLServer';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=MyName');
  SQLConnection.Params.Add('Password=MyPassword');
  SQLConnection.Params.Add('HostName=MyServer');
  SQLConnection.Params.Add('Database=MyDatabase');
  SQLConnection.Open;

If you are using SQL Server Compact Edition, parameter values should be set in the following way:

  SQLConnection.DriverName := 'SQLServerCompactConnection';
  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See this table for more information..
  SQLConnection.VendorLib := <SQL Server CE version>; // This value specfies which version of SQL Server CE. See this table for more information.
  SQLConnection.GetDriverFunc := 'getSQLDriverSQLServerCompact';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('Password=MyPassword');
  SQLConnection.Params.Add('Database=D:\DataBases\MyDatabase.sdf');
  SQLConnection.Open;

The following table determines how library name depends on the used IDE version:

Library name	IDE version
dbexpsda40.dll	CodeGear RAD Studio 2007 and higher
dbexpsda30.dll	Delphi 2006 and Turbo products
dbexpsda.dll	other IDE versions

The following table describes which kind of connection will be chosen by DbxSda depending on values of GetDriverFunc and VendorLib properties:

GetDriverFunc	VendorLib	Connection kind
getSQLDriverSQLServer	sqloledb	To a remote SQL Server via SQL OLE DB provider
	sqlncli	To a remote SQL Server via SQL Native Client provider
	Any other value	To a remote SQL Server via automatically chosen provider. Initially DbxSda will try to load the SQL Native Client provider. If this provider was not found, the driver will try to load the SQL OLE DB provider
getSQLDriverSQLServerCompact	sqlceoledb30	To SQL Server Compact Edition version 3.0
	sqlceoledb35	To SQL Server Compact Edition version 3.5
	Any other value	To an automatically chosen SQL Server Compact Edition. Initially DbxSda will try to load the SQL Server Compact Edition version 3.5. If this server was not found, the driver will try to load SQL Server Compact Edition version 3.0

If User_Name parameter of the SQLConnection is not set (User_Name='') Windows Authentication connection mode is used.

Type map

SQL Server data types are mapped to TFieldType in the following way:

SQL Server type	Field type	Field class
int	ftInteger	TIntegerField
bigint	ftFMTBcd	TFMTBCDField (with limited precision)
smallint	ftInteger	TIntegerField
tinyint	ftSmallint	TSmallIntField
bit	ftBoolean	TBooleanField
decimal	ftFMTBcd	TFMTBCDField
numeric	ftFMTBcd	TFMTBCDField
money	ftFMTBcd	TFMTBCDField
smallmoney	ftBcd	TBCDField
float	ftFloat	TFloatField
real	ftFloat	TFloatField
datetime	ftTimeStamp	TSQLTimeStampField
smalldatetime	ftTimeStamp	TSQLTimeStampField
char	ftFixedChar	TStringField
varchar	ftString	TStringField
text	ftMemo	TMemoField
nchar	ftFixedChar	TStringField
nvarchar	ftString	TStringField
ntext	ftMemo	TMemoField
binary	ftBytes	TBytesField
varbinary	ftVarBytes	TVarBytesField
image	ftBlob	TBlobField
timestamp	ftBytes	TBytesField (Size = 8)
guid	ftFixedChar	TStringField (Size = 38)
sql_variant	ftString	TStringField

When the EnableBCD option is False the dataset maps number fields as following:

SQL Server type	Field type	Field class
decimal	ftFloat	TFloatField
numeric	ftFloat	TFloatField
money	ftCurrency	TCurrencyField
smallmoney	ftCurrency	TCurrencyField

Note: Type map, beginning from version 1.80 to correspond Borland dbExpress driver for SQL Server was changed for decimal, numeric, money and smallmoney types to ftBCD, ftFMTBCD. To recover previous behaviour you should set EnableBCD option to False. To simplify setting driver options it's recommended to use an additional component - TCRSQLConnection.

When the UseUnicode option (available since BDS 2006) is True, the dataset maps string fields as following:

SQL Server type	Field type	Field class
nchar	ftWideString	TWideStringField
nvarchar	ftWideString	TWideStringField
ntext	ftWideMemo	TWideMemoField

Extended driver options

DbxSda provides several extended options that expand functionality of the driver. In order to overcome restrictions of dbExpress on several IDEs, DbxSda provides the TCRSQLConnection component. It simplifies working with extended driver options.

The dbExpress technology version 4 in CodeGear RAD Studio 2007 and higher does not have such restrictions, so the TCRSQLConnection component has no sense for these IDEs.

Starting with CodeGear RAD Studio 2007 you should assign parameter values to TSQLConnection and TCRSQLConnection at run time in this way:

SQLConnection.Params.Values['Option Name'] := 'Option Value';

In other IDE versions it is better to use TCRSQLConnection instead of standard TSQLConnection. Extended options are available with TSQLConnection only at run time.

To setup these options at design time, you should use the Params property of TCRSQLConnection.

To use an option with TSQLConnection in Delphi 2006 and lower IDE versions, you should define constants and set appropriate option values using event handlers like:

TSQLConnection.AfterConnect
TSQLQuery.BeforeOpen

Below is the example that demonstrates setting extended driver options in Delphi 2006 and lower IDE versions:

const
  coFetchAll = TSQLConnectionOption(301);

procedure TForm1.SQLQuery1BeforeOpen(DataSet: TDataSet);
begin
  SQLConnection1.SQLConnection.SetOption(coFetchAll, Integer(True));
end;

If you compile the application for CLR, you should use

  ISQLConnection_SetOption(SQLConnection1, ...)

instead of

  SQLConnection1.SQLConnection.SetOption(...)

Here is the description of the options and how to use them.

Custom String

With Delphi 7 and higher IDE versions you can use the "Custom String" option to provide additional connection settings:

• Application Name
• AutoTranslate
• Failover Partner
• Language
• MARS Connection
• Network Library
• PacketSize
• Use Encryption for Data
• Workstation ID

For description of these properties refer to MSDN

Sample code looks like this.

  SQLConnection.Params.Add('Custom String=ApplicationName=A_NAME;WorkstationID=WID');

DBMonitorHost

Set this option to the host name where dbMonitor runs to enable remote monitoring.

const
  coDBMonitorHost = TSQLConnectionOption(106); //string
. . .
  SQLConnection1.SQLConnection.SetOption(coDBMonitorHost, Integer(PChar('MyHost')));

DBMonitorPort

Set this option to the port number of dbMonitor.

const
  coDBMonitorHost = TSQLConnectionOption(107); //integer
. . .
  SQLConnection1.SQLConnection.SetOption(coDBMonitorPort, 1001);

EnableBCD

This boolean option with True value allows dataset to represent number fields as TBCDField and TFMTBCDField.

The default value of this option is True (beginning from version 1.80). If you assign EnableBCD to False it establishes the following values

To use this option with TCRSQLConnection you may set it in Params property of TCRSQLConnection.

const
  coEnableBCD = TSQLConnectionOption(102); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coEnableBCD, Integer(False));

CommandTimeout

This integer option specifies amount of time to attempt execution of a command. Use CommandTimeout to specify the amount of time that expires before an attempt to execute a command is considered unsuccessful. 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).

const
  coCommandTimeout = TSQLConnectionOption(306); // integer
. . .
  SQLConnection1.SQLConnection.SetOption(coCommandTimeout, 3);

FetchAll

Set FetchAll to True to request all records of the query from database server when the dataset is opening. When set to False, records are retrieved when a data-aware component or a program requests it. If a query can return a lot of records, set this property to False if initial response time is important.

Note: in some cases FetchAll = False may conflict with transaction control, working with temporary tables and may cause deadlocks on editing statements with ORDER BY clause.

Default value is True.

const
  coFetchAll = TSQLConnectionOption(301);
. . .
  SQLConnection1.SQLConnection.SetOption(coFetchAll, Integer(True));

LongStrings

This boolean option when turned on allows dataset to represent long strings (more than 255 symbols) as memo.

Default value of this option is True.

const
  coLongStrings = TSQLConnectionOption(101); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coLongStrings, Integer(False));

ParamPrefix

Specifies whether to return '@' before name of the parameter in StoredProc. Used for compatibility with standard driver.

Default value of this option is False.

const
  coParamPrefix = TSQLConnectionOption(303); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(TSQLConnectionOption(coParamPrefix), Integer(True));

Prepared

Set Prepared to True to execute query preparation on the server that allows to increase performance on multiple execution of the queries. Preparation is especially effective on executing Stored Procedures.

Note that query preparing has some limitations:

• unable getting values of output-parameters;
• doesn't support temporary objects (tables etc).

Note: in contrast to setting TCustomSQLDataSet.Prepared to True that prepares a query only on client side, setting SetOption(coPrepared, Integer(True)) prepares a query on the server.

Default value is False.

const
  coPrepared = TSQLConnectionOption(302); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coPrepared, Integer(True));

RequiredFields

Manages of TField.Required property assignment. This option can be one of the following values:

• 'None' - all fields are marked as not required
• 'Required' - all fields are marked as they are declared on server
• 'NonAutoInc' - all fields except autoincrement fields are marked as they are declared on server

Default value of this option is 'None'.

const
  coRequiredFields = TSQLConnectionOption(307);
. . .
  SQLConnection1.SQLConnection.SetOption(coRequiredFields, Integer(PChar('Required')));

TrimFixedChar

Specifies whether to discard all trailing spaces in string fields of the dataset.

const
  coTrimFixedChar = TSQLConnectionOption(103); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coTrimFixedChar, Integer(True));

UseUnicode

Enables or disables Unicode support. Affects character data fetched from the server. When set to True, all character data from nchar and nvarchar columns is stored as WideStrings, and TStringField is replaced with TWideStringFiled. In case of ntext data type, TMemoField is replaced with TWideMemoField. If you want TMemoField to be created for ntext columns in UseUnicode mode, please take a look at the UseUnicodeMemo option.

This option is available for Delphi 2006 and higher IDE versions.

Default value of this option is True.

const
  coUseUnicode = TSQLConnectionOption(209); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coUseUnicode, Integer(True));

UseUnicodeMemo

This option is provided to ensure backward compatibility with UseUnicode mode of DbxSda 3. If this option is set to False, columns of the ntext data type will be mapped to TMemoField in spite of the the UseUnicode option.

This option is available for Delphi 2006 and higher IDE versions.

Default value of this option is True.

const
  coUseUnicode = TSQLConnectionOption(210); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coUseUnicodeMemo, Integer(True));

TCRSQLConnection

TCRSQLConnection component serves to support additional options of dbExpress drivers for Oracle (DbxOda), SQL Server (DbxSda) and MySQL (DbxMda). Option configuration can be made in dbExpress configuration files (dbxconnections.ini) or in Params list of TCRSQLConnection.

You can use following options:

• EnableBCD
• CommandTimeout
• FetchAll
• LongStrings
• ParamPrefix
• Prepared
• Required
• TrimFixedChar
• UseUnicode

For example

var
  SQLConnection1: TCRSQLConnection;
...
  SQLConnection1.Params.Values['EnableBCD'] := 'False';
  SQLConnection1.Connected := True;

Note: for quick replacement of TSQLConnection to TCRSQLConnection at design-time it's enough to use context menu of the component.

Using DBMonitor

To extend monitoring capabilities of dbExpress applications with DbxSda driver, you can use an additional freeware tool called DBMonitor. It is provided as an addition to a monitoring procedure with using SQLMonitor component.

DBMonitor is easy-to-use tool to provide visual monitoring of your database applications.

DBMonitor has following features:

• Multiple client processes tracing;
• SQL event filtering (by sender objects);
• SQL parameter and error tracing.

DBMonitor is intended to hamper application being monitored as less as possible.

To trace your application with DBMonitor you should follow these steps:

• drop TSQLMonitor component onto the form;
• start DB Monitor before running your program.

Note: if you are Delphi Pro version user, then you don't have TSQLMonitor component installed on the palette, but it is included in SQLExpr.pas unit and you need to install it on the components palette manually.

Deployment

The following additional files are required on the target computer:

File	When required	Where to find
dbexpsda.dll (dbexpsda30.dll for Delphi 2006 or Turbo products users, dbexpsda40.dll for CodeGear RAD Studio 2007 and higher users)	Required for any application	DbxSda installation directory
dbxintf.dll	Required for any CLR application	IDE installation directory
Devart.DbxSda.DriverLoader.dll (should be placed in GAC)	Required for CLR applications built with CodeGear RAD Studio 2007	DbxSda installation directory

Users of dbExpress driver for SQL Server with Source Code can embed the driver into the application directly. For information on how to do this refer to Borland documentation.

DbxSda works with SQL server through OLE DB interfaces, so it requires OLE DB installed on the workstation. In current versions of Microsoft Windows, such as Windows 2000, OLE DB is already included as part of the standard installation. But it is highly recommended to download the latest version (newer than 2.5) of Microsoft Data Access Components (MDAC).

Support for some features of SQL Server 2005 like MARS requires Microsoft SQL Server Native Client. If you need to use these features, you should download and install Microsoft SQL Server Native Client.

For applications that use SQL Server 2005 Compact Edition, the server itself is required to be installed on the client computer.

Trial limitations

DbxSda trial version can be used within 30 days starting from the moment of installation.

Notes

Database object names that contain spaces or national language characters should be always quoted. For example,

  SQLStoredProc.StoredProcName := '"My stored proc"';

When calling stored procedures that return several ResultSets you should take into account the following issues:

• values of Output-parameters can be retrieved only after receiving the last ResultSet
• if you execute SP several times before calling Open method you need call

      SQLStoredProc.Close;
      SQLStoredProc.FieldDefs.Updated := False;
      SQLStoredProc.Prepared := False;
    

• If you use Delphi 6 then you need unset FieldDefs.Updated before call NextRecordSet

      SQLStoredProc.FieldDefs.Updated := False;
      SQLStoredProc.NextRecordSet;
      SQLStoredProc.FieldDefs.Updated := False;
      SQLStoredProc.NextRecordSet;
    

Known problems

• Output ft(Var)Bytes (SQL Server types (var)binary) and ftVariant (SQL Server type sql_variant) parameters are not supported by dbExpress.

• dbExpress cannot perform Update and Delete operations if field of ftVarBytes type has value with zero length and it is not null.

• CLR applications built under Delphi 2006 or Turbo products have following restrictions:

  1. OUT and INOUT string parameters must be initialized with a value that is greater than expected one.
  2. BLOB parameters are not supported in stored procedures.

• dbExpress for several IDE does not support complex object names. For example, following code will not work with Delphi 6, C++Builder 6, Delphi 2007, C++Builder 2007:

      SQLStoredProc.StoredProcName := 'northwind.."Ten Most Expensive Products"';  
    

• dbExpress for Delphi 2007 does not support TSQLStoredProc.NextRecordSet.

Using SQL Server Compact Edition

Since SQL Server 2005, Microsoft provides SQL Server Compact Edition. It is an easy at installation freeware SQL server to be used by applications that do not require multi-user work with SQL Server. As an example SQL Server Compact Edition can be used for money access machines, automatic cash desks, different electronic facilities and so on. Please refer to Microsoft web site for more details about features and using of SQL Server Compact Edition.

In order to work with SQL Server Compact Edition, you should perform the followin steps:

1. Download and install the server itself
2. Install DbxSda, and run your IDE
3. Place the TSQLConnection or TCRSQLConnection onto the form, and select the SQLServerCompactConnection driver name
4. Call the Params editor and assign a Database and Password properties. The Database property should contain full patch to database file. Other options have no sense for this driver

Demo projects

Following demo projects accompany DbxSda and provide a good bootstrap to start working with it. They are located inside DbxSda\Demos folder.

Sample	Description
BlobPictures	This sample project demonstrates how to work with SQL Server IMAGE type to store binary images and display in TDBImage
ClientDataSet	This sample project demonstrates working with TSQLClientDataSet in Delphi 6
Query	This sample project allows to get result record set using TSQLQuery component
SimpleDataSet	This sample project demonstrates working with TSimpleDataSet in Delphi 7
SQLServerCompact	This sample project demonstrates working with Microsoft SQL Server Compact Edition
StoredProc	This sample project demonstrates execution of stored procedure for data insertion and viewing entered data.

 

 

---

© 2001-2009 Devart. All rights reserved.