Database field to Generic T conversion?

Database field to Generic T conversion?

What is the cleanest and/or fastest way to convert a variant to a specific type T where T is limited to a simple type and comes from a database. 


  rtti, Data.Db;


  TMyClass = class

     function Convert(const Field: TField):T;


How slow will using RTTI be? 

function TMyClass.Convert(const const Field: TField):T;


  v: TValue;


  v := TValue.FromVariant(Field.AsVariant);

  Result := V.AsType;


Am I better off using explicit conversions and/or casts per TFieldType or per Variant type? 

Just to explain why I want to do this: End goal is to have a non-visual “virtual query result grid” (i.e. rows by columns) where values are converted from database values to their “natural” Delphi types and then passed to other systems which will render the data with various decorators. Grid sizes can in theory vary from 10 rows by 20 columns to 5000 rows by 60 columns. 

I want to let go of the Dataset as fast as possible, and keep the results in this virtual structure. The hope is to be able to cut down amount of query to grid scaffolding code, among other things.


29 thoughts on “Database field to Generic T conversion?

  1. You’d be better off with TValue as your variant type, if you could. Since that already has such conversion at hand. Without that, I’m not aware of any RTL support so you’ll need to do the conversion yourself in the style of the TValue code.

  2. The question is: do you want the T to determine the conversion or the varType of the Variant that comes in?

    With the approach you posted there it will throw when T is Integer and the Variant contains the string ’42’. Why? Because TValue.FromVariant “unpacks” the Variant and stores it as the underlying type causing the TValue to hold a string. And that cannot be cast to Integer using builtin TValue mechanisms as TValue only allows casts that also the compiler allows (and not type conversions).

    If the T must determine the type you need to handwrite such a conversion handling all cases of its typekind.

  3. Stefan Glienke – I was unaware of that FromVariant behaviour.

    I was expecting numbers fields to be handled as variant numbers since the database identifies numeric columns with various TFieldType formats?

    If that is not the case, it seems that letting T’s base type decide the explicit conversion, is what makes most sense.

    TBH, it is a bit sad that we can’t hand the db value to native value problem directly to the DB code – since we have to do these conversions time and time again.

  4. Lars Fosdal You can hand the problem directly to the DB code. But then you have to do it a step earlier and look at the FieldType and then determine what method to call on your TField that gives you the correct value.

    Personally I would go with a TFieldHelper with an AsType method that does that.

  5. Another somewhat related challenge is that I have to go from data by row, to data by column – but I am hoping the amount of data won’t totally break the cache and stab me in the back later.

  6. Stefan Glienke Well, at some point I will need to typecast data (int to enum), or format data (float or int to string), or replace data (id to string) – I can either do this explicitly on a per case basis – and we have a lot of cases (over 200 variants of OpenRecordSet) – or try to package it as good as I can into a generic wrapping.

  7. David Heffernan The new polymorphism – where you take a small number of cases that you can count on 2 hands and create a generic case that then does hard checks for these small numbers of cases. 🙂

  8. I have no shortage of cases, that is for sure. What I do have, is tens of thousands of lines of fairly similar scaffolding that I want to unify and simplify, to avoid having to fix the same bugs in multiple places, and reduce the amount of cut and paste for new scaffolds.

  9. The only viable solution I could find for my TeeBI project was to keep table columns as simple arrays, but without generics. ( ie var i:array of integer; if Field.Kind=TDataKind.Int32 then i:=Field.Int32Data )

  10. David Heffernan In David B’s case – that was what I was wondering too.

    For me – conversions are not a bottleneck – except perhaps in a few corner cases.

    Something that I have always wondered, but never bothered to benchmark – which approach is faster? A class hierarchy with a set of virtual methods vs a generic class with static methods with case statements?

  11. Lars Fosdal Well, you aren’t comparing like for like there. You might compare polymorphism against a case statement. But polymorphism vs generic+case doesn’t really make much sense. They are not analogous.

  12. Lars Fosdal About perf, TValue or Variants conversions are “slow” (if you have a lot of values to convert), so there’s nothing faster than simple arrays. The cost of if/case the data kind is really small if you do it outside the loops, and if the data kinds are reduced to a few (int32, int64, single, double, text, datetime and boolean). The problem with generics “array of T” is you’re forced to move all code to be methods of all the generic subclasses (TMyInt32 etc) or start tricking with GetTypeKind, SizeOf(T), so in the end the complexity is the same or more with generics. The real reason I couldn’t go the generics way is the lack of operator constraints, ie this fails: …Foo:Array of T… Foo[0]+Foo[1], equality and compare are ok but not all arithmetic

  13. David Heffernan It depends on the case, of course.

    In this particular case:

    Use the TField.DataType:TFieldType in a case statement in a static method of a generic class- or create a polymorph class to match each TFieldType.

  14. Lars Fosdal  That’s usually pointless, and a bad comparison. Because you’ll still need to do runtime type checking beyond the case statement. Instead of generics, what you are looking for is overloads. Once you start requiring different implementation for each supported type, generics becomes pointless. The clue is in the name: generic.

  15. David Berneda Ref. doing the if/case outside the loop – are you saying that you process the dataset column by column, rather than row by row? Have you met any practical limit in row vs column count so far?

  16. David Heffernan There are many facets to such a decision – particularly when you start to look at further conversions than just database type to delphi type. The next level for me is the conversion of integers to specific enumerated types, injection of formatters and decorators, and “out of band side loading” of objects based on object id’s. As I’ve mentioned, the primary goal is to reduce the complexity and repetition of scaffolding code.

  17. Lars Fosdal “Something that I have always wondered, but never bothered to benchmark – which approach is faster? A class hierarchy with a set of virtual methods vs a generic class with static methods with case statements?”