Reclaim that space!

Reclaim that space!

RAD Studio uninstall is not all inclusive, it seems. After uninstalling XE6 and XE8, the shared samples and kits still linger.

C:UsersPublicDocumentsEmbarcaderoStudio 

14.0 3Gb in 58k files (XE6 uninstalled)

15.0 3Gb 56k files (XE7.x Installed)

16.0 2Gb 51k files (XE8.x uninstalled)

17.0 2.8gb 60k files (10 Seattle installed)

So, that’s just above 5Gb reclaimed by removing the two dead directories.

Something safe for New Year’s Eve :)

Something safe for New Year’s Eve 🙂

Jan Horn wrote this, December the 29th in 2001! Just add Variants to the uses clause in the .dpr file to get past the “NULL not found” error.

Someone should port this to FireMonkey 🙂

Update: Upon trying to find Jan’s current whereabouts, I sadly learned that Jan Horn no longer is among us.  May he rest in peace, and be remembered fondly.

http://www.sulaco.co.za/news_in_loving_memory_of_jan_horn.htm

#opengl     #sourcecode  

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  

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  

Migrating to FireDAC from ADO – Stored Proc Param Type

Migrating to FireDAC from ADO – Stored Proc Param Type

Found an interesting little deviation today.  

In a MSSQL stored proc with an TinyInt parameter, the insert statement suddenly complained that it could not insert the param with NULL value (as per constraint).

After a lot of tracing, I was certain that we passed a 0 and not a NULL – and the only plausible reason was that I could see, was that the code was setting the param value, before setting the param datatype – so I rearranged the assignments to ensure that value was the last thing to be set.

That fixed the issue.  Go figure.

Params is Array of Variant.

// ADO, MSSQL

for i := Low(Params) to High(Params) do

begin

  p := AStoredProc.Parameters.AddParameter;

  p.Direction := pdInput;

  p.Value := Params[i];

  p.DataType := VariantTypeToFieldType(VarType(Params[i]));

  if p.Value = null

   then p.Attributes := [paNullable]

  else if VarType(Params[i]) = varBoolean

  then begin

    p.DataType := ftInteger;

    if Params[i]

     then p.Value:=1

      else p.Value:=0;

  end;

end;

// FireDAC, MSSQL

for i := Low(Params) to High(Params) do

begin

  p := AStoredProc.Params.Add;

  p.ParamType := TParamType.ptInput;

  vt := VarType(Params[i]);

  if vt vtBoolean

  then begin

    p.DataType := VariantTypeToFieldType(vt);

    p.Value := Params[i];

  end

  else begin

    p.DataType := ftInteger;

    if Params[i]

     then p.Value:=1

      else p.Value:=0;

  end;

end;

Super Simple Documentation in Delphi

Super Simple Documentation in Delphi

Save the pastebin document as xmld_XMLDoc_Summary.xml in 

XE..XE5 – RAD Studiocode_templatesDelphi

XE6… – EmbarcaderoStudiocode_templatesDelphi

… or wherever your standard Delphi directory is.

Create a blank line above a class method or property in the interface declaration, then type xmld and fill in the blanks. If you need more than one line, add a line break and /// at the beginning of next line.

  protected

    ///

Current log line number being

    /// processed

    property LogLineNo: Integer read FLogLineNo write FLogLineNo;

Now, hover your cursor over the method or property anywhere in your code and read what you just filled in 😉

Yes – you can add a lot more XML doc attributes – but for the bare minimum – this is pretty quick and easy.  You can of course change the magic xmld keyword to anything you prefer as well.