Migrating from FireDAC: SQL Server

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Migrating from FireDAC: SQL Server

Post by Ivan_Carpio89 » Tue 11 Dec 2018 00:24

Hello,

I have an application in XE7, and I use FireDAC to access a database in SQL Server.

I need to find the equivalent configurations of UniDAC, I am interested in its component, but first I want to evaluate it to know the advantages, as well as the possible disadvantages:

TFDConnection:
FDConnection1.FetchOptions.DetailOptimize:= False;
FDConnection1.FetchOptions.LiveWindowFastFirst:= True;
FDConnection1.FetchOptions.LiveWindowParanoic:= True;

FDConnection1.FormatOptions.StrsEmpty2Null:= False;

FDConnection1.ResourceOptions.AutoConnect:= False;
FDConnection1.ResourceOptions.AutoReconnect:= True;
FDConnection1.ResourceOptions.DirectExecute:= True;
FDConnection1.ResourceOptions.SilentMode:= True;

FDConnection1.UpdateOptions.LockMode:= lmPessimistic;
FDConnection1.UpdateOptions.LockPoint:= lpImmediate;

FDConnection1.TxOptions.DisconnectAction:= xdRollback;

FDConnection1.FormatOptions.SortLocale:= 0;

FDConnection1.FormatOptions.MapRules.Add(dtDateTimeStamp, dtDateTime);

TFDTable:
Inherits the entire configuration of the TFDConnection.

TFDQuery:
FDQuery1.FetchOptions.Mode:= fmAll;
FDQuery1.CachedUpdates:= True;
FDQuery1.UpdateOptions.LockMode:= lmNone;
FDQuery1.FetchOptions.AutoClose:= False;
FDQuery1.ResourceOptions.ParamCreate:= False;

The TFDTable I use it to modify (INSERT, UPDATE and DELETE) and navigate the tables of the database, and the TFDQuery for the execution of queries (SELECT, INSERT, UPDATE and DELETE).

So far I found something curious in the TUniQuery, is that if I execute a SELECT that returns 100,000 rows, and I realize a KILL from SQL Server, the TUniQuery, does not return with error, if not that the Open ends with the records that reached to obtain and continue the code flow, which in FireDAC, and for the logic of my application is to return an error, indicating that the connection was closed.

Thanks in advance.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Migrating from FireDAC: SQL Server

Post by Stellar » Wed 12 Dec 2018 16:02

A list of FireDAC options that have a direct equivalent in UniDAC:

//FDConnection1.FormatOptions.StrsEmpty2Null:= False;
UniQuery1.Options.SetEmptyStrToNull := False;

//FDConnection1.ResourceOptions.DirectExecute:= True;
UniQuery1.Options.AutoPrepare := False;

//FDConnection1.UpdateOptions.LockMode:= lmPessimistic;
UniQuery1.LockMode := lmPessimistic;

//FDConnection1.TxOptions.DisconnectAction:= xdRollback;
UniConnection1.DefaultTransaction.DefaultCloseAction := taRollback;

//FDConnection1.FormatOptions.MapRules.Add(dtDateTimeStamp, dtDateTime);
UniConnection1.DataTypeMap.AddDBTypeRule(msTimestamp, ftDateTime);

//FDConnection1.ResourceOptions.AutoConnect:= False;
UniConnection1.Options.AllowImplicitConnect := False;

//FDQuery1.FetchOptions.Mode:= fmAll;
UniQuery1.SpecificOptions.Values['SQL Server.FetchAll'] := 'True';

//FDQuery1.CachedUpdates:= True;
UniQuery1.CachedUpdates := True;

//FDQuery1.UpdateOptions.LockMode:= lmNone;
UniQuery1.LockMode := lmNone;


A list of FireDAC options that have no direct equivalent:

//FDConnection1.FetchOptions.DetailOptimize:= False;
UniDAC updates the detail dataset if the current values of the key fields of the detail dataset are different from the key fields of the master dataset.

//FDConnection1.FetchOptions.LiveWindowFastFirst:= True;

//FDConnection1.FetchOptions.LiveWindowParanoic:= True;

//FDConnection1.ResourceOptions.AutoReconnect:= True;
UniDAC allows you to reconnect to the server by handling the OnConnectionLost event of TUniConnection. Learn more about OnConnectionLost:
devart.com/sdac/docs/devart.dac.tcustomdaconnection.onconnectionlost.htm

//FDConnection1.ResourceOptions.SilentMode:= True;

//FDConnection1.UpdateOptions.LockPoint:= lpImmediate;
UniDAC blocks the recording immediately after the start of editing or deletion in the dataset.

For more information about the parameters, see the UniDAC online documentation:
devart.com/unidac/docs/overview.htm

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: Migrating from FireDAC: SQL Server

Post by Ivan_Carpio89 » Wed 12 Dec 2018 16:35

Ok, thank you very much, just one question:

Code: Select all

FDConnection1.FormatOptions.SortLocale:= 0;
This property indicates that the order in the TDataSets (TFDTable / TFDQuery) will be based on a binary ordering. The character collection I use in SQL Server, in the columns are "Latin1_General_BIN". With the components of UniDAC, how do I make this configuration?

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Migrating from FireDAC: SQL Server

Post by Stellar » Fri 14 Dec 2018 15:08

If you want to use binary string comparison, then set the DefaultSortType option for TUniConnection to the stBinary value, for example:

//FDConnection1.FormatOptions.SortLocale:= 0;
UniConnection1.Options.DefaultSortType := stBinary;

Learn more about the DefaultSortType option:
https://www.devart.com/unidac/docs/deva ... rttype.htm

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: Migrating from FireDAC: SQL Server

Post by Ivan_Carpio89 » Fri 14 Dec 2018 17:14

Thank you for your support.

Post Reply