Fallback for non-installed SQL Server Native Clients

- or how to survive in a world where you are not in control of everything.

Today I did deploy a small application that did connect to a MS SQL Server, so a driver was needed, and I do prefer to follow the recommendations on using the latest native drivers - and I also try and avoid ODBC as the plague.

What had slipped my mind until the first test user got an error - was that in a plain Windows installation only the "old" ODBC SQL Server driver exists - and since I really didn't want to involve the whole IT support/operation/packaging scenario - to get the SQL Server Native Client 11.0 client pushed out to some users around the world - who also would be lacking admin rights, so that they would not be able to install the drivers themselves - I had to include a fallback option.

I refuse still to understand why the newer Windows versions keep only having the old "SQL Server" ODBC driver and not also include a "native" one, when that is what is been recommended over and over again.



And unfortunately does the SQL Server Native Clients prefer to be installed, and can't just be provided by a single library like InterBase, FireBird and others.

So being easily able to stream files to a specific directory, but not install anything, I had to go for the lowest common denominator the ODBC driver.

When using FireDAC to connect to MS SQL is does try and use the latest native driver found, but it does not fallback to in the end and try the ODBC driver - since that is a different driver "set".

And I wanted to use the "best" possible client library - so I did check the registry prior to connecting to see if a native driver was installed:

const
  key1='\SOFTWARE\ODBC\ODBCINST.INI\SQL Server Native Client';
  key2='\SOFTWARE\ODBC\ODBCINST.INI\SQL Server Native Client 10.0';
  key3='\SOFTWARE\ODBC\ODBCINST.INI\SQL Server Native Client 11.0';
var
  reg: TRegistry;
begin
  FDConnection.Close;
  FDConnection.Params.Clear;
  reg := TRegistry.Create;
  reg.RootKey := HKEY_LOCAL_MACHINE;
  if reg.KeyExists(key1) or reg.KeyExists(key2) or reg.KeyExists(key3) then
  begin
    // Native driver
    FDConnection.DriverName := 'MSSQL';
    FDConnection.Params.Values['Server'] := 'MyDBServer';
    FDConnection.Params.Values['OSAuthent'] := 'No';
  end
  else
  begin
    // ODBC driver
    FDConnection.DriverName := 'ODBC';
    FDConnection.Params.Values['ODBCDriver'] := 'SQL Server';
    FDConnection.Params.Values['RDBMS'] := 'MSSQL';
    FDConnection.Params.Values['ODBCAdvanced'] := 'SERVER=MyDBServer';
  end;
  FreeAndNil(reg);
  FDConnection.Params.Values['Database'] := 'MY_DATA';
  FDConnection.Params.Values['User_Name'] := 'Scot';
  FDConnection.Params.Values['Password'] := 'Tiger';
  FDConnection.Open();
end;

So that should ensure that regardless of whatever native driver that is not there - I should still be able to run my application.

The above code is not the most elegant, and some would prefer to check if some of the driver files existed instead in the Windows system directory. Some would also prefer to just use connection-strings - the possibilities with FireDAC are plenty.

Any form of mandatory error checking and exception handling is also absent :D

But do remember since this now allows a more generic "old" connector to be used, the application also needs to carter for that - one thing I got bitten by - was the initial wish to optimize and use the datatype DATETIME2 (as recommended) - which ODBC does not handle correctly. So here I just changed the datatype.

Maybe more on the streaming and distribution of applications mention above later.