How do you test if a Variant is of type varDate, without getting some 'illegal conversion error' if it DOESN'T contain a valid DateTime?
@Gustavo 'Gus' Carreno: I appreciate all the help, often the problem is me thinking the wrong way :)
I read some arbitrary data source that contains a (partial) database. In this case it is JSON, but it could just as easy be a bunch of CSV or XML files, or even a backup of an SQL server. It doesn't matter. I don't know up front. So, I make functions that accept Variants.
I do use classes. Each table has it's own class, for example (a descendant of a superclass that does all the heavy lifting).
I do have a DataSet for each table, but they aren't used anymore. Everything is done directly with SQL. My "current record" for each table is basically an array of Variants (in a small class) .The problem is the relational database model. You cannot just add stuff to a table. You need all kinds of keys and there are many restrictions.
If you make a different application for each import, you end up with a lot of different applications, made by copying parts of other ones. You always have to debug and fix them to bring them up to date before they work. On last count, we have 92 of those. And some work for multiple formats.
Some parts of the data imported are spread out over 10+ tables. Some of it is in this table, with those foreign keys. A slight variation is in those four tables, with the same foreign keys that point to different tables, etc. Three quarters of a conversion is in querying the keys, inserting new records if they don't exist and adding a lot of calculated values everywhere. Reading and converting the source data is less than a quarter of the work, and by far the easiest bit. So I made more classes and functions to vastly simplify those three quarters.
And say, I have an integer that is 0. As a key, probably completely valid (although not in DBF files, they start at 1). As an amount, probably not. It depends. Some fields expect a value of 0 to say that another field should be NULL or ignored. As a price or percentage, totally legit.
So, it is far easier to determine the status just before you insert it into the database, because it's hard to generalize.
I have a large DBF database, with ~400 tables, which contain on average ~100 fields each. The first version of the application that used this is more than 30 years old. It gets updated at least once a month.
Well, the main problem is not the JSON, but testing and comparing the variants without getting exceptions that present you with the "Quit or risk data corruption?" dialog. Continuing execution at that point is useless anyway, because you're thrown back into the message handler. There is no way to debug it.
In JSON, unused numeric values tend to be 0, not NULL. And dates are strings. There is no NULL-value in a TDateTime, and it seems that a varDate Variant is just a Double flagged as being varDate. So, you end up with either a Variant you cannot assign to a TDateTime, or a Variant that is actually NULL when it contains TDateTime(0). And assigning 0 to a Variant turns it into a Numeric value, not a TDateTime. So, I really want to know if the Variant contains an actual value, before I try assigning it to something typed. Otherwise, I get that dreaded dialog. 'try..except' doesn't help (I tried).