Unexpected behaviour - Variant to Data with database variants and type mismatches

[thoult]

Spoiler

Using the Database Connectivity Toolkit* to retrieve query data from a TSQL database, I've run into a nice little foible with converting the database variants to LV data. With a query returning a 2D array of SQL datetime, string and bit columns, I mistook the order in which the query returns data. Using Variant to Data, I tried to convert the 0th item of the array to Boolean and the 2nd item of the array to Timestamp, when in fact they should be the other way around - snippet below.

Looking at the code, I would ordinarily expect to see errors from both Variant to Data nodes...it shouldn't be able to convert a timestamp to a Boolean and vice versa, right? In this instance...it turns out I'm wrong.

No errors thrown for those conversions, and garbage data back. Oddly, if you try to convert the string to the wrong datatype, Variant to Data throws a Type Mismatch error -2147352571, so it can recognise when the conversion is awkward. If you try to convert a standard LV variant incorrectly, you'll get an error 91.

This happens in both LV2013 SP1 and LV2016. If I replace Variant to Data with Database Variant to Data in the above instances, the same behaviour occurs.

Thoughts anyone?

*

Spoiler

*waves* Hi mikeporter if you're reading! The Database Connectivity Toolkit use is inherited

[aputman]

I suspect it has something to do with the database variant data type being an OLE Variant.  Does the OLE Variant contain a pointer to the data?  I don't know.  Not sure why it would guess about the conversion rather than report an error. I see the same behavior in 2012. 

[Yamaeda · Accepted solution]

An error is thrown if it cannot convert, which it obviously can. Your timestamp has a numeric value /= 0, so it's a true, and a False is 0, so the default timestamp is the answer. Just like it should be. 🙂

/Y

[thoult]

---

@Yamaeda wrote:

An error is thrown if it cannot convert, which it obviously can. Your timestamp has a numeric value /= 0, so it's a true, and a False is 0, so the default timestamp is the answer. Just like it should be. 🙂

/Y

---

I know where you're going with that, but a problem comes with the inverse.

If false is 0, then you're suggesting the conversion of a zero float to a timestamp in LabVIEW should tend to 00:00:00.000
01/01/1904. To get 00:00:00.000 30/12/1899 is a conversion of a float of -126316800 to timestamp. Odd!

The moral of the story is to convert datatypes correctly, or to sniff out the variant type correctly to automate it. A bit of digging over the weekend suggests that this is indeed possible with external datatypes. After all, LabVIEW can show you the Variant type, even if you can't directly access it. I'll try to fish out my source, but I think rolfk or someone similar found a Win32 library call for it.

[thoult]

...aha, but then again, there's another source for that timestamp.

The incoming variant is OLE type VT_DATE.

From here:

"Offset value (64bit signed float), 0.0 is December 30, 1899."

So perhaps Variant To Data sees that it's requesting a timestamp, then sees that the input is an OLE variant and thinks "ooh, it must be VT_DATE!" and tries to convert the input to a numeric. Trying it with a true - and remembering that for OLE a true is -1 - the timestamp returned is 00:00:00.000 29/12/1899.

[rolfk]

The whole thing is a little bit complicated. Basically a variant can be just about any possible datatype internally including a string. For non-string data there is a specific dilemma. If you expect a timestamp at the output do you only allow VT_DATE data as input. Or should VT_FILETIME also work? Probably yes. But then VT_DATE as already mentioned is simply a double precision floating point value with a special meaning, namely that its integer part is the number of days since midnight ,december 30, 1899 (thanks to Lotus 1-2-3 developers not realizing that 1900 wasn't a leap year) and the fractional part is the fraction of the day, so why not allow for VT_R8 (the VARIANT type for a double precision floating point, and if we are at it lets allow VT_R4 too and since we are so pretty much at it we should of course allow the various integer types as well and VT_BOOL is simply a glorified integer too.

And while this may all seem stupid it is actually safer to allow for that and lead developers as you astray, than requiring very strict type matching. Since there are many factors and intermediate layers in ODBC and OLE DAO that all can have their specific ideas about transferring data in the most concise value possible, rather than the most accurately descriptive type. After all it's all also about network bandwidth and while saving a few bytes for a more concise datatype may sound like peanuts, it can add up if you transfer a lot of those.

The other case is when the database driver returns the actual values as strings. While that is fully valid too, it has many tricky troubles of its own such as ambiguous meanings in respect to local versus, UTC based time, and in the case of local, which local the time refers to, but also simple formatting trouble which are almost always local specific too, starting with the decimal sign, but not ending at how month, day and year need to be interpreted and in which order. Here the conversion routine StringToSpecificDatatype() has to be pretty strict to not create completely idiotic results.

And while you can read the underlying VARIANT datatype of an OLE VARIANT pretty easily by copying out the first two bytes of the VARIANT using the MoveBlock() function this is made a little more tricky by the fact that a LabVIEW variant can be really two very distinct flavors under Windows. LabVIEW knows both a native variant datatype with its own private data layout and an OLE Variant. Both are on the diagram visualized as purple wire and there is no simple way to distinguish them from each other other than using the Variant Type helper VIs in vi.lib/Utility/VariantDataType/GetTypeInfo.vi. The OLE Variant returns a type of Variant, while the native Variant returns a type of LVVariant.

Only when you have an OLE Variant, is it safe to copy the first two bytes out of the variant pointer and interprete it according to to the various VT_ type definitions as documented on MSDN. A LabVIEW native Variant has a completely different layout which is undocumented and possibly can change between LabVIEW versions.

[thoult]

Thanks for the detailed reply, Rolf, especially the Lotus Notes tidbit!

I was part tempted to write my own OLE Variant to Data conversion tool (check variant for OLE Variant, then check OLE variant type, then convert as required by polymorphic) to avoid misconversion, or more accurately to error out on erroneous conversion attempts.

The lucky thing about this behaviour is that it was indicated through a validation test - we were checking the steplocking of a process to a previous step, and because of my idiocy I was converting the timestamp to Boolean instead of the 'pass/fail' to Boolean. So when I tried to test the steplock, it successfully worked for units which passed...but also allowed failed units through too.

Otherwise, I'd have been none the wiser!