Any users of FireDAC TFDScript out there?

Any users of FireDAC TFDScript out there?

Scripts produced by ApexSQL Diff, which runs fine in SQL Server Management Studio, fail when run from a TFDScript.  I am trying to figure out if it is options related, or if it is an implementation problem.

Problem 1: Transaction handing causes an error:  

Exception class EMSSQLNativeException with message ‘[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.’. 

Problem 2: If I remove the begin tran at line 38/39 and reduce the script to only contain the create table part – it still fails silently. The spool output shows no errors, nor does any errors show in the FireDAC Monitor.  And yet – no table is created.

I also suspect that multiline /* block comments */ confuse the script analyzer.

Suggestions, anyone, particularly for Problem 2?

#firedac   #script  

FireDAC Date Handling – FireDAC Date Mystery Part II

FireDAC Date Handling – FireDAC Date Mystery Part II

As it turns out, the mystery was not fully solved.

After adding map rules (see old post) to FireDAC for how to handle dtTime and dtDate, I removed the old ADO special case handling and just did

Result := FieldByName(FieldName).AsDateTime;

Which worked well – or – so I thought.  I had done all my testing on machines that had the SQL Server Native Client installed, and it showed no problems.

But – when I ran on a machine without SQLNCLI installed, the date format issue reared it’s ugly head again.

So – this is the “final” workaround which so far appears to handle every date/time format that I have tossed at it.

function TPSDRecordset.GetAsDateTime(const FieldName: string): TDateTime;

var

  F: TField;

  s: String;

begin

    F := FieldByName(FieldName);

    try

    case F.DataType of

      ftDateTime: Result := F.AsDateTime;

      ftTime: Result := StrToTime(F.AsString);

      ftDate: Result := StrToDate(F.AsString);

      else begin

        OutputDebugString(‘GetAsDateTime(‘+FieldName+’).DataType=’ + IntToStr(Ord(F.DataType)));

        Result := StrToDateTime(F.AsString);

      end;

    end;

  except

    on E:Exception

    do begin

      s := FieldByName(FieldName).AsString;

      OutputDebugString(Self.ClassName + ‘.GetAsDateTime(‘ + FieldName + ‘) = ‘ + s + ‘: ‘ + E.ClassName +’ – ‘ + E.Message);

     raise;

    end;

end;

#FireDAC   #SQLServer   #NativeClient  

Originally shared by Lars Fosdal

A FireDAC Date Mystery

I have an app that connects to a MSSQL db through FireDAC and retrives a selection of log data, including the date and time of the logging in a datetime field.

I leave the app open for a longer period of time (about an hour, I think), then I refresh the view.

BAM! The dates show up as 1899.  For some reason, FireDAC has decided to forget how to get the datetime in the right format.

I suspect that is is related to a connection object that has lost it’s connection, but why can it reconnect and get data, and yet get the date format wrong?

#FireDAC  

A FireDAC Date Mystery

A FireDAC Date Mystery

I have an app that connects to a MSSQL db through FireDAC and retrives a selection of log data, including the date and time of the logging in a datetime field.

I leave the app open for a longer period of time (about an hour, I think), then I refresh the view.

BAM! The dates show up as 1899.  For some reason, FireDAC has decided to forget how to get the datetime in the right format.

I suspect that is is related to a connection object that has lost it’s connection, but why can it reconnect and get data, and yet get the date format wrong?

#FireDAC  

Setting up a MS SQL Server FireDAC connection in code

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  

Migrating from ADO to FireDAC – MS SQL Server

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  

Migrating to FireDAC: The devil is in the details

Migrating to FireDAC: The devil is in the details

I recently discovered that some of the few queries that we have that return a large rowset,  would max out at 50 rows.

It turns out that TFDQuery.FetchOptions.Mode defaults to fmOnDemand, which means that the query first returns a default of 50 rows, and then fetches 50 more as you MoveNext past the 50 first.  Other options are fmAll, fmExactRecsMax, fmManual.

Since we still are in the “make it work” and not in the “make it fast” stage, I switched to using fmAll.

Interestingly, RowCount by default also returns 50 even if the query actually matches more rows. and there is a TFDQuery.FetchOptions.RecordCountMode setting that can be modified, as well: cmFetched, cmVisible, cmAll.

This differs from ADO TQuery, which will retrieve all rows and the full record count by default.

TFDDataSet has the same settings, and there are numerous other tweakable parameters that I need to explore for both of these components..

#FireDAC    #ADO #migration  

FireDAC – MSSQL – Stored Proc context details?

FireDAC – MSSQL – Stored Proc context details?

Without adding parameters to the stored procedure, is it possible to pass / push more process context info than what MSSQL already has in sp_who? http://msdn.microsoft.com/en-us/library/ms174313.aspx

In particular: client app processid and thread id

Any optional connection parameter or similar?

#firedac   #mssql