DB Variant to Data (xnode) doesn't 'do' LV timestamp

[David Boyd]

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 LV 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

[xtal]

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.

[David Boyd]

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

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 LV 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 LV 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 LV 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

[i2dx]

just download my ADO-Toolkit

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

http://forums.lavag.org/index.php?act=attach&type=post&id=6296

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 ...

[David Boyd]

QUOTE(i2dx @ Jul 3 2007, 01:17 AM)

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 LV 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 :ninja: ) to illustrate; any testing you could do in your environment would be greatly appreciated.

Dave

[xtal]

QUOTE(David Boyd @ Jul 3 2007, 12:40 AM)

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

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

QUOTE(David Boyd @ Jul 3 2007, 12:40 AM)

My first workaround idea is to use the DB Var to Data xnode to convert the datetime variant to a

LV

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 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.

QUOTE(David Boyd @ Jul 3 2007, 12:40 AM)

I should also note that the DB Variant To Data xnode fails for timestamps if the input is a

LV

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.

This part is out of my realm of knowledge. I thought LV 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.

QUOTE(David Boyd @ Jul 3 2007, 12:40 AM)

Any idea why the DBV2D primitive was removed from

LV

and replaced with an xnode, And, why was the non-DB V2D node never 'fixed' w.r.t OLE variants?

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 LV 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.

QUOTE(David Boyd @ Jul 3 2007, 12:40 AM)

See you in Austin next month, perhaps?

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.

[David Boyd]

QUOTE(xtaldaz @ Jul 3 2007, 01:20 PM)

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

Yes, it continues to work very well for me - as you said, it hasn't been rev'd since 2001 but is still useful.

QUOTE(xtaldaz @ Jul 3 2007, 01:20 PM)

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 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.

QUOTE(xtaldaz @ Jul 3 2007, 01:20 PM)

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}.

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.

QUOTE(xtaldaz @ Jul 3 2007, 01:20 PM)

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.

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 LV floating point values get an intermediate conversion to string before being passed to the Office automation methods, and some LV toolkit developer made a one-size-fits-all decision for how many digits are significant. This is why I avoid intermediate string conversions.

QUOTE(xtaldaz @ Jul 3 2007, 01:20 PM)

I thought

LV

variants and OLE variants were the same -- thus explaining the same behavior -- but I can't comment on the inner workings of variants.

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 LV 'To Variant' bullet it lists the LV datatype.

QUOTE(xtaldaz @ Jul 3 2007, 01:20 PM)

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

Done, but I reported the bug against a later version of LV.

QUOTE(xtaldaz @ Jul 3 2007, 01:20 PM)

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.

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

[xtal]

QUOTE(David Boyd @ Jul 3 2007, 03:27 PM)

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.

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 LV 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!

[i2dx]

QUOTE(David Boyd @ Jul 3 2007, 05:15 PM)

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 LV 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.

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 ...

QUOTE(David Boyd @ Jul 3 2007, 05:15 PM)

I stole your icon :ninja:

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 ...

[Rob Calhoun]

I know this is an ancient thread, but for posterity I wanted to note that in LV 8.2 and later, you can use the regular LabView "variant to data" function to convert ActiveX timestamps to LabView timestamps.

[Darren]

I know this is an ancient thread, but for posterity I wanted to note that in LV 8.2 and later, you can use the regular LabView "variant to data" function to convert ActiveX timestamps to LabView timestamps.

Be careful with that, as there are some problems when converting null data from a database with the regular Variant To Data function that the DB Variant To Data function handles correctly.

Also, I think the Timestamp issue was fixed in the Database Toolkit at some point, but I don't remember which LabVIEW version it was.

-D