Setting up a MS SQL Server FireDAC connection in code

In case you haven’t set up a connection in code yet: 

    FireDriverLink := TFDPhysMSSQLDriverLink.Create(nil);

    

    FConnection := TFDConnection.Create(nil);

    FConnection.DriverName := FireDriverLink.BaseDriverId;

    // http://docwiki.embarcadero.com/RADStudio/XE7/en/Connect_to_Microsoft_SQL_Server_(FireDAC)

    FConnection.Params.Values[‘Server’] := Trim(HostName);

    FConnection.Params.Values[‘Database’] := Trim(DatabaseName);

    FConnection.Params.Values[‘OSAuthent’] := OSAuthent;  // ‘Yes’ for Windows user, ‘No’ for SQL user name 

    FConnection.Params.Values[‘User_Name’] := Trim(UserName);

    FConnection.Params.Values[‘Password’] := Trim(Password);

    FConnection.Params.Values[‘ApplicationName’] := ‘YourApplicationInfoHere’;

    FConnection.LoginPrompt := False;  // Suppresses dialogs

    FConnection.Connected := True;

   From this point, you create the kind of db object (Query, StoredProc, etc.) you need.

In addition, you need to put this (or a subset) in the uses section.

Data.DB,  FireDAC.Stan.Intf, FireDAC.Stan.Consts, FireDAC.Phys.ODBCBase, FireDAC.Phys.MSSQL,

{$ifdef Console}

  FireDAC.ConsoleUI.Wait,

{$else}

  FireDAC.UI.Intf, FireDAC.VCLUI.Wait, FireDAC.Comp.UI,

{$endif}

  FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.Phys.Intf, FireDAC.Stan.Def,

  FireDAC.Stan.Pool,  FireDAC.Stan.Async, FireDAC.Phys,

  FireDAC.Stan.Param, FireDAC.DApt, FireDAC.comp.DataSet, FireDAC.comp.Client;

FYI, the SQL Server Native Client install kits are available from MS and will be a lot faster than the default drivers.  

You’ll want the newest version available (such as 2012), even if the SQL Server version you want to connect to, is older.

You can download the 2012 version from here: http://www.microsoft.com/en-us/download/details.aspx?id=29065

See under: Install Instructions, 

MICROSOFT SQL SERVER CONNECTIVITY FEATURE PACK COMPONENTS

Microsoft® SQL Server® 2012 Native Client

Microsoft SQL Server Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2012 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server code name ‘Denali’ features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

X86 Package (sqlncli.msi)

X64 Package (sqlncli.msi)

You don’t need the SDK bit.

To do silent installs, have a look at

http://msdn.microsoft.com/en-us/library/ms131321.aspx

#FireDAC  

http://msdn.microsoft.com/en-us/library/ms131321.aspx

Migrating from ADO to FireDAC – MS SQL Server

Yet another tidbit.  Default stored proc parameter sizing.

The old parameter passing to the stored procedures is done with array of const and variants.  No issues with that, until my xml logging feature had the mother of all stack traces, and exceeded 9k bytes in total xml size, upon which the assignment of TFDParam.Value complained that TFDParam.Size was 8002 bytes, and the size of the string was bigger than that.

vtUnicodeString:

begin

   p.DataType := ftString;

   p.Value    := String(ConstParams[ix].VUnicodeString); //Size barf

end;

Quick and dirty fix… For the future, I intend to lose the array of const and move to more explicit parameter encoding.

vtUnicodeString:

begin

   p.DataType := ftString;

   s := String(ConstParams[ix].VUnicodeString);

   len := Length(s) * SizeOf(Char);

   if Len > p.Size  // Autosize for XML

   then p.Size := Len + 2;

   p.Value    := s;

end;

#FireDAC