Records, Generics and RTTI meets FireDAC

[Lars Fosdal 1792]

I was pondering on what I could do to simplify doing ad-hoc queries and minimize the scaffolding code required, so I came up with this.

Note that this code was yanked from one of our units, and hence is not directly runnable as it is missing some functions, and the code that sets up the connection, etc. - but it should give you the general gist of it works.

It is not pretty nor perfect, but it works, and if there are glaring mistakes or potential problems, I expect you will tell me all about them 🙂

 

unit SomeUnit;

type
  RUser = record
   id: Integer;
   name: string;
   displayname: string;
  end;

implementation

procedure TSomeClass.ListThoseUsers(const aDB: TDatabase);
var
  Users: TArray<RUser>;
begin
  Users := DB.SelectArray<RUser>('select id, name, displayname from v_users');
  // Do whatever you need to do with the user list here
end;

I am pretty sure that you can create records with fields of types that will cause the TValue conversion to fail horribly, but this deals fine with the basic types as well as enumerated types.

Note that the record type needs to be declared in the unit's interface section to get the proper RTTI.
Doing a test run from at home to a database server in Trondheim, 700km away - the logs showed
SelectArray<SomeUnit.RUser>("select Id, Name, DisplayName from v_users") returned 161 rows in 0:00,024 
24 ms is not blisteringly fast, but acceptable.
 

function TDatabase.SelectArray<T>(const sSQL: String): TArray<T>;
var
  Info: String;
  StopWatch: TStopWatch;
  Query: TFDQuery;
  Retry: Boolean;
  FieldName, RetryMsg: string;
  rType: TRTTIType;
  RecField: TArray<TRTTIField>;
  DBField: TArray<TField>;
  fx, rx, rc: Integer;
begin
  Retry := False;
  repeat
    if Connect
    then try
      if Retry
      then RetryMsg := '#RETRY '
      else RetryMsg := '';

      StopWatch := TStopWatch.StartNew;
      rtype := TRTTIContext.Create.GetType(TypeInfo(T));

      Info := RetryMsg + 'SelectArray<'+rtype.QualifiedName+'>("' + sSQL+ '") ';

      Query := TFDQuery.Create(nil);
      Query.Connection := Connection;
      Query.ResourceOptions.ParamCreate := False;
      Query.FetchOptions.Mode := fmAll;
      Query.FetchOptions.Unidirectional := Unidirectional;
      Query.SQL.Text := sSQL;
      try
        Query.Open;
        rc := Query.RecordCount;
        if (Query.FieldCount >= 1) and (rc > 1)
        then begin
          RecField := rtype.GetFields; // Fetch field list from record type
          
          SetLength(DBField, Length(RecField));
          try // Lookup record fields in database result set. Will raise exception if field not found
            for fx := Low(RecField) to High(RecField)
            do begin
              FieldName := RecField[fx].Name;
              DBField[fx] := Query.FieldByName(FieldName);
            end;
          except
            on E: Exception
            do begin
              Info := Info + ' - Field ' + rType.QualifiedName + '.' + FieldName + ' was not found in result set.';
              raise;
            end;
          end;

          // for each row in the result set
          SetLength(Result, rc);
          for rx := 0 to rc - 1
          do begin
            for fx := Low(RecField) to High(RecField) // for each field in the record, transfer row field to record field 
            do begin
              if RecField[fx].FieldType.TypeKind  = tkEnumeration
               then RecField[fx].SetValue(@Result[rx], TValue.FromOrdinal(RecField[fx].FieldType.Handle, DBField[fx].AsInteger))
                else RecField[fx].SetValue(@Result[rx], TValue.FromVariant(DBField[fx].Value));
            end;
            Query.Next;
          end;

          if DebugLogging
           then DebugOut(Info + ' returned ' + rc.ToString + ' rows in ' + TimeSpanToStr(StopWatch.Elapsed));
        end
        else begin
          SetLength(Result, 0);
        end;
      finally
        Query.Free;
      end;
      Retry := False;
    except
      on DBE:EFDDBEngineException
      do begin
        LogDBException(DBE, Info, Retry);
        if not Retry
         then RAISE;
      end;
      on E:Exception
      do begin
        LogException(E, Info);
        RAISE;
      end;
    end
    else
     raise EDbConnectionFailure.Create(Info + ' DB Error: Could not connect.');
  until not Retry;
end;

 

[Attila Kovacs 629]

O

R

M

 

[Uwe Raabe 2060]

Not sure if it fits your needs, but nevertheless this might be an interesting read for you (unless you have already done so): Dataset Enumerator Reloaded

And here ist the corresponding rep: DataSetEnumerator

[Lars Fosdal 1792]

Nice one, Uwe!  I see I am late to the show 🙂

[Lars Fosdal 1792]

@Attila Kovacs - ORMs are nice, but this project didn't use one.

[Jacek Laskowski 57]

Look at my idea, without records, but with interfaces, SQL is generated automatically, moreover UPDATE and INSERT works 😉

 

https://github.com/jaclas/transfORM

[Lars Fosdal 1792]

@Jacek Laskowski We don't do direct insert/update/delete operations from the client software - but have to explicitly call stored procs instead. 
Client does not have rights to do inserts/updates - and may only call a specific set of database routines.

[Jacek Laskowski 57]

@Lars Fosdal Ok, ok, I just wanted to expand the topic: FireDAC, RTTI and generics 😉

[Attila Kovacs 629]

@Lars Fosdal I heard that several times and I still can't imagine why not a middle tier, why sp's?

[Lars Fosdal 1792]

@Attila Kovacs The SPs often contain business logic, validation/sanitation, state change logging, queueing to signaling mechanisms, etc. - and can be called from multiple external systems. With a lot of concurrent actors, transactional integrity is a challenge. 

 

[Uwe Raabe 2060]

1 hour ago, Lars Fosdal said:

can be called from multiple external systems

That's an important point. There are situations where your program is not the only one accessing the data. This doesn't mean that it could not be done different, but sometimes these other systems are out of your reach.

[Lars Fosdal 1792]

For us, it is typically third party systems that deal with production handling, i.e. whenever something is labeled with a serial number and/or a weight, - or added, removed or repositioned in some robotic storage system, or a laboratory system changes the approval status of a production batch. 
The WMS needs to know what is where, in what condition, at all times.

The architecture is over a decade old, and if redesigned from bottom up today - probably none of the involved parties would have direct access to the DB.

 

None of our software uses data-aware components.

[Wagner Landgraf 43]

3 hours ago, Lars Fosdal said:

@Attila Kovacs - ORMs are nice, but this project didn't use one.

Until you start using it. You are actually adding one right now.

[Lars Fosdal 1792]

1 minute ago, Wagner Landgraf said:

Until you start using it. You are actually adding one right now.

As ORMs go, this one would be veeery light weight.

[Esteban Pacheco 16]

DataSetEnumerator is great.  Certain complex types may cause trouble here and there but it is a default unit on anything I build.  Thank you Uwe.

[Andrea Raimondi 13]

@Lars Fosdal I have a sort of Mini-ORM somewhere which would be beneficial for you because it starts from the assumption that there is a truckloadworth of SQL already written.