Just discovered this while attempting to migrate a project from 8.0 to 8.2.1 which uses the Database Connectivity toolkit...

Looks like the Database Variant to Data primitive was mutated into an xnode in LV8.2 (I frankly hadn't noticed). The xnode type-adapts to the LabVIEW timestamp datatype when wired, but I can't get it to work. Whether the input variant is a VT_DATE or a VT_BSTR, the output of the node seems to be stuck at zero timestamp.

Anybody else notice this? Workarounds would involve an intermediate conversion to and from string representation of time, which always seems a bit iffy.

Dave

Yup, I remember seeing that and having a workaround. Sadly, I don't remember what that workaround was exactly. But if you tell me what database app you are using and give me a little time, I can figure it out.

Hello, Crystal, thanks for the reply. It was from you that I originally got my copy of the DB toolkit so long ago.

The DBs I'm currently using are still Access (2003), though I'm planning to migrate to SQL Server Express 2005 soon.

My first workaround idea is to use the DB Var to Data xnode to convert the datetime variant to a LabVIEW string, then pass the string to a Scan From String with a TS constant and a format specifier of %<%c>T. Hopefully the %c will cause the scan to parse the string in whatever format the target used to write the string in the first place.

I should also note that the DB Variant To Data xnode fails for timestamps if the input is a LabVIEW variant, as well as an OLE variant.

Another quirk I just noticed in Scan From String - if you don't wire a default timestamp value, and the scan fails, the previously converted value is returned. (Sort of reminiscent of the original problem with Variant to Data when passed an OLE null.) All of this is observed under 8.2.1.

Any idea why the DBV2D primitive was removed from LabVIEW and replaced with an xnode, And, why was the non-DB V2D node never 'fixed' w.r.t OLE variants?

See you in Austin next month, perhaps?

Dave

just download my ADO-Toolkit

there is a conversion VI DB-Timestamp to LV-Timestamp in the "Variant to Data" conversion VIs:



You can use the NI Toolkit and the ADO-Toolkit in paralell ...
If the timestamp conversion does not work, just let me know, I'll fix that ...

Thanks, Christian. I just checked your toolkit VI (under 8.2.1) and I can tell you that it does not work for me.
I think as a general approach you want to avoid trying specific date/time text formats as your VI does. The approach I described (variant converts to string, then scan from string converts to LabVIEW timestamp using system's default format) works for me, but I can't be certain it will work under all OS/localization settings.

Thanks for your input on this. I've attached a VI (I stole your icon ) to illustrate; any testing you could do in your environment would be greatly appreciated.

Dave

Yup, I remember that. I'm glad it's still mostly working for you.


I did get a chance to play around with the DB V2D and timestamps quite a bit last evening and reminded myself of why there were problems. A lot of it has to do with the differences in datatypes in general. A timestamp in LabVIEW is a cluster of 4 I32s, but the VT_DATE (vbDate) in COM is a double precision floating point number that represents the date and the time. I found a couple nice descriptions through Google - here's one: http://www.newobjects.com/pages/ndl/SQLite2/SQL-Typing.htm

I'm going to spare you the details of why the DB V2D would need to be completely rewritten to accommodate timestamps properly {unless you want me to go into it in a private message}. Let me just say that the way you are doing it now with converting it to a string is actually the best way. I played around with all sorts of crazy conversions and the string approach always worked.


This part is out of my realm of knowledge. I thought LabVIEW variants and OLE variants were the same -- thus explaining the same behavior -- but I can't comment on the inner workings of variants. I do lots of .NET work now and thankfully, the variant type is not used.

You should report that Scan From String behavior to NI as a bug. That doesn't seem like the correct behavior.


Wow, I could start a whole dissertation on this topic {where is that can of worms glyph when you need it}. I'll give the brief answer here and if you want more details, let me know and I'll fill you in. The main reason the DB V2D was converted from a primitive to an xnode is because primitives are part of the LabVIEW source code and thus tied to a particular version of LV. The DB V2D is only used by the Database Toolkit. Toolkits shouldn't have to be tied to a particular LabVIEW version -- as is evidenced by the fact that the DB Toolkit has been at version 1.0.1 for at least 5 versions of LV. Getting rid of that primitive severs the link between the two SW packages and now the DB V2D can be revved with the toolkit.


I don't work for NI anymore, but since I still use NI products, I'm going to try and attend NIWeek. If I'm at NIWeek, I'll definitely be at the LAVA BBQ dinner.

Yes, it continues to work very well for me - as you said, it hasn't been rev'd since 2001 but is still useful.
I thought the TDM developers used such a cluster, but that was from the days before it became a true datatype. I read that it's a 128-bit fixed-point number, with 64 bits each side of the radix, so I guess that's the same memory footprint.
My gripe with the xnode is that the primitive version of DB V2D did work with timestamps, at least for me. I have plenty of code that works under LV8 but breaks under LV8.2 because of this.
Agreed, but solely because the interpretation of the string at runtime can be guaranteed to use the same localized date/time format that the variant to string conversion used. If this were not so, this would be a non-portable mess. I've seen other cases (in the Report Generation toolkit) where LabVIEW floating point values get an intermediate conversion to string before being passed to the Office automation methods, and some LabVIEW toolkit developer made a one-size-fits-all decision for how many digits are significant. This is why I avoid intermediate string conversions.
I think of them as separate entities based on the variant display in LabVIEW - if I set 'Show type' it displays 'OLE Variant' and 'Variant type->VT_DATE', etc., for variants returned by ADO or Office automation objects. If wired to the output of the LabVIEW 'To Variant' bullet it lists the LabVIEW datatype.
Done, but I reported the bug against a later version of LV.
I knew you left NI awhile back, it's great to have your expertise available here in the user community. Perhaps I'll see you at the Salt Lick; this year, the whole family will make the trip (to tour the Austin/Hill Country area while I take the CLA exam), so who knows where we'll go in the evenings.
Again, thanks for all your insights.

Dave

Hmmm, I'm really curious why this behavior is different. The underlying code is very simple. For flat datatypes, the normal V2D is used and for nonflat datatypes, the regular V2D first converts the data to a string and then follows with an Unflatten From String to the type wired to the Type input. That's it. The only reason it needed to be a primitive in LabVIEW 6.0 (when the DB Toolkit first released) was because you couldn't make a VI with an input that adapted to *any* datatype. Of course, now we can use XNodes to make a truly polymorphic subVI. Maybe the internal structure of the timestamp changed or its 'flatness' isn't being read correctly in 8.2. Have you tried just using the regular V2D with the timestamp data? Aha!! I just tried it and got it to work. There are slight rounding issues with the decimal seconds, but it appears to work.

I guess there's nothing like talking yourself through a problem and ending up solving it.

Good luck on the CLA exam!

thanks for your input.

Your conversion works fine on my (german) system (Win XP Pro). Time-Stamp conversion has allways been a problem for me, too, because you can't test it on every existing localisation => you can never be sure, if it works or not ...



never mind, I stole your code (but there is a © remark in the Block Diagramm ...)

I have made an update of the ADO-Toolkit which uses your Timestamp conversion, which is now availiable for download ...