Delphi Developers Archive

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   http://pastebin.com/dzR8T6fC

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);       ...

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 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, ...

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 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...

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   http://msdn.microsoft.com/en-us/library/ms174313.aspx

Console FireDAC Example wanted Doing FireDAC againt MSSQL or PostgreSQL visually is easy, but when I try to do it in code only - I get weird errors and there must be something essential that I miss. Does anyone have a code only example of how to set up a FireDAC connection? #firedac

Looking into converting a bunch of legacy apps from #BDE to #FireDAC and trying to use the "reFind" utility released since XE5 to help do this. Not having much joy though, as I've run into a stack overflow for the 2nd time in 2 days. The first one I ran into with version 2.5 (shipped in #XE5 Update 2 hotfix 4), turned out to be already fixed in the newer version 2.7.1 (shipped with #XE6 ).  However I've now run into the same problem (or, at least, the same message) again on a different file.   I've filed a QC about it, in case anyone's interested in looking or voting for it:  http://qc.embarcadero.com/wc/qcmain.aspx?d=124781 On my To Do list is also to have a further look at the #Mida converter to see if this is worth getting -- Does anyone have any experiences to share re the non-trial version of Mida and BDE conversion?   (The bundled/trial version doesn't support BDE to FireDAC conversion last time I checked, so I couldn't form any opinion ...

Looking into converting a bunch of legacy apps from #BDE to #FireDAC and trying to use the "reFind" utility released since XE5 to help do this. Not having much joy though, as I've run into a stack overflow for the 2nd time in 2 days. The first one I ran into with version 2.5 (shipped in #XE5 Update 2 hotfix 4), turned out to be already fixed in the newer version 2.7.1 (shipped with #XE6 ).  However I've now run into the same problem (or, at least, the same message) again on a different file.   I've filed a QC about it, in case anyone's interested in looking or voting for it:  http://qc.embarcadero.com/wc/qcmain.aspx?d=124781 On my To Do list is also to have a further look at the #Mida converter to see if this is worth getting -- Does anyone have any experiences to share re the non-trial version of Mida and BDE conversion?   (The bundled/trial version doesn't support BDE to FireDAC conversion last time I checked, so I couldn't form any opinion ...