Platform specific SQL

[kneighbour]

I have been using UniDac for years, but mainly with Firebird. I now find that we are moving to SQL Server. My question is regarding the different SQL versions that Firebird and SQL Server use.

For example, Firebird uses "SELECT FIRST 100...." and SQL Server uses "SELECT TOP 100..." (and MySQL something else). I had kind of hoped that UniDac would be able to use some sort of universal SQL that deep down converts the SQL to the platform that you are using. It seems not.

Also things like the "SpecificOptions" that are set for each platform. I set some of these in code, but of course if I switch platforms, this code now errors out.

I am about to go through millions of lines of code fixing this sort of stuff, so I just want to make sure that I am not missing some feature of UniDac that can help me in this regard.

[pavelpd]

Kindly note that UniDAC does not convert user queries for different DBMS.
For application compatibility with different DBMS, UniDAC suggests using unified SQL.
For example, the unified record limit for SQL Server and Firebird:

Code: Select all

UniConnection1.Macros.Add('limit', 'TOP', 'SQLServer');
UniConnection1.Macros.Add('limit', 'FIRST', 'InterBase');

...

UniQuery1.SQL.Text := 'SELECT {limit} 10 * FROM customer';
UniQuery1.Open;

More details about unified SQL and macros here:
https://www.devart.com/unidac/docs/unisql.htm#macroref


The SpecificOptions property and its options are intended for setting of a specific database server and are applied to the following components: TUniConnection, TUniQuery, TUniTable, TUniStoredProc, TUniSQL, TUniScript.
Descriptions of the options of the SpecificOptions property:
- for Interbase/Firebird: https://docs.devart.com/unidac/using-in ... cificnotes
- for SQL Server: https://docs.devart.com/unidac/using-sq ... ficoptions

You can find all the necessary information on the components is in the UniDAC documentation: https://docs.devart.com/unidac/reference.htm