topic Re: Possible DateTime bug in Database Toolkit in LabVIEW

Possible DateTime bug in Database Toolkit

Yamaeda — Sun, 27 Mar 2011 13:07:03 GMT

Hi!

I dont have the database toolkit here, and it seems you cannot download a 64-bit evaluation ...

Bug? I've been writing DateTime fields to a database through the proper use of clusters and Inserts, but it seems i never get any fractional seconds. The datetime controls shows them and the database fields should support them (atleast it's showing .000 at the end) so i'm wondering: Does it somehow use regional settings for time so it's using my decimal comma and thus ditching the fractions?

/Yamaeda

Re: Possible DateTime bug in Database Toolkit

JÞB — Sun, 27 Mar 2011 15:06:28 GMT

This sounds familliar. It is important to note that Time (civil time of day, NOT an SI Second) can be represtented in several ways. LabVIEW uses a "Epoch" (0) meaning midnight Jan 1 1904 UTC and scale of 1 = 1 sec. Excel uses a scale of 1 = 1 day and an epoch of midnight Jan 0th 1900 Local Time. Some Data base fields may only store strings like "3/27/2011 09:56:12.098"

Converting a LabVIEW Timestamp to a value for use elsewhere isnt too tricky as long as you know the epoch and scale of the useing application. I posted a sample a while ago that converts a timestamp to Excel format. It can serve as an example of how these conversions are done.

Re: Possible DateTime bug in Database Toolkit

josborne — Sun, 27 Mar 2011 18:24:22 GMT

To debug this, I would try to isolate the problem a bit more to find out where it is ocurring. Assuming you have the code, I would drill down into the subVIs to view the actual INSERT statement that is being sent. For example,

INSERT INTO tablename (column1, column2) VALUES (xyz, '2010.12.11 11:11:11.1234')

That would allow you to confirm how LV is formatting the statement string.

If that is not possible, you could try creating a "trace" on your database server to view the actual INSERT statement.

My guess is that you are correct: That its some kind of truncatoin issue due to localization of the decimal point. I am guessing there are ways to get around this ... both on the client side (in your LV code) and on the database side. Best of luck to you!

Re: Possible DateTime bug in Database Toolkit

rolfk — Sun, 27 Mar 2011 19:35:17 GMT

What database server and version?

Re: Possible DateTime bug in Database Toolkit

Yamaeda — Mon, 28 Mar 2011 21:45:30 GMT

MS SQL Server, 2000, 2003 and 2008 i think (depending on which computer i run it on).

I'll try to look at it tomorrow at work.

The point ofc being that i shouldn't need to format time through a string manually and the like if i use a Cluster with a datetime field as Insert input.

Re: Possible DateTime bug in Database Toolkit

JÞB — Tue, 29 Mar 2011 02:04:13 GMT

@Yamaeda wrote:

MS SQL Server, 2000, 2003 and 2008 i think (depending on which computer i run it on).

The point ofc being that i shouldn't need to format time through a string manually

Epoch--- Scale. "Time" is a number- strings can repesent numbers BUT,.. can't be numbers

Re: Possible DateTime bug in Database Toolkit

Yamaeda — Tue, 29 Mar 2011 13:32:01 GMT

@Jeff Bohrer wrote:

@Yamaeda wrote:

MS SQL Server, 2000, 2003 and 2008 i think (depending on which computer i run it on).

The point ofc being that i shouldn't need to format time through a string manually

Epoch--- Scale. "Time" is a number- strings can repesent numbers BUT,.. can't be numbers

Well the SQL-command is a string in the end, however you turn it. 🙂

I send a datetime value, LV converts it to a string to send it as a SQL-command and SQL server converts it back to a datetime value for storage. If there's a bug in Lv's conversion i could generate that string manually and send it to the DateTime DB-field, but i really shouldn't have to.

Re: Possible DateTime bug in Database Toolkit

rolfk — Tue, 29 Mar 2011 13:39:36 GMT

Actually if you do a parametrized insert there is not necessarily any string involved. The datatime value could be directly passed as a DB variant. But I'm not sure what the internal LabVIEW conversion from it's native control datatypes to a variant does.

Another idea might be a mismatch with the used decimal character. Some local settings use decimal comma, others use decimal point. ADO, which is used by the Database Toolkit, has a strong preference to use the local decimal format, and so does LabVIEW, unless you disable that with an INI file setting. But if your server uses a different setting you have a problem!

Re: Possible DateTime bug in Database Toolkit

Yamaeda — Tue, 29 Mar 2011 14:45:44 GMT

I just checked the code: DB Tools Generate Parameters doesn't use the fractional seconds at all, no wonder it didn't make it to the DB ... So, how should one go about now, i can fix it locally, but if i need to reinstall i'll bet i've forgotten i ever did the change. 😄

Re: Possible DateTime bug in Database Toolkit

josborne — Tue, 29 Mar 2011 15:33:04 GMT

>>DB Tools Generate Parameters doesn't use the fractional seconds at all, no wonder it didn't make it to the DB

You could build and format your own INSERT statement that using this VI. Then you could take account of the fractional seconds properly.

>>i can fix it locally, but if i need to reinstall i'll bet i've forgotten i ever did the change.

That sounds like a bug in your software development methodology. 🙂

Re: Possible DateTime bug in Database Toolkit

Yamaeda — Tue, 29 Mar 2011 20:56:39 GMT

On a related note, how do i Format to String a datetime with fixed decimal point ... %T doesn't quite cut it, nor does %<%.3x %X>T help.

Re: Possible DateTime bug in Database Toolkit

rolfk — Tue, 29 Mar 2011 23:46:06 GMT

How about "%<%x %.3X>T"? %x is the date and nobody wants to see fractional days.

Re: Possible DateTime bug in Database Toolkit

Yamaeda — Wed, 30 Mar 2011 05:28:27 GMT

Thanks Rolf, but it results in localized decimal sign in the string.

Re: Possible DateTime bug in Database Toolkit

rolfk — Wed, 30 Mar 2011 10:10:42 GMT

Have you tried to prepend %.; to the format string for Format into String? That may however not be what you want, since the ADO/ODBC driver or the actual database backend may expect localized decimal strings.

Re: Possible DateTime bug in Database Toolkit

Yamaeda — Wed, 30 Mar 2011 12:03:15 GMT

Yeah i tried %.; both in the beginning of the format and inside the %<>T (the latter causing an error).

Re: Possible DateTime bug in Database Toolkit

josborne — Wed, 30 Mar 2011 13:05:25 GMT

Try this?

Notice that the format string starts with %.;

That hardcodes the decimal to a period. The entire string is totally hardcoded, though. So you'd have to make sure your DB is expecting one specifc hardcoded format, no matter where it is deployed. Which I am guessing isn't so hard.

Re: Possible DateTime bug in Database Toolkit

rolfk — Wed, 30 Mar 2011 14:36:45 GMT

@Yamaeda wrote:

Yeah i tried %.; both in the beginning of the format and inside the %<>T (the latter causing an error).

/Y

I would consider this a bug then!!

Re: Possible DateTime bug in Database Toolkit

Yamaeda — Thu, 31 Mar 2011 06:53:34 GMT

@Jos, yes that's a workaround to a bad implementation. I'll post a descriptive picture.

Observe that neither Boolean nor DateTime can be inserted straight off the bat. I thought the whole idea was to use clusters that matched the database fields ...

Re: Possible DateTime bug in Database Toolkit

rolfk — Thu, 31 Mar 2011 11:26:57 GMT

@Yamaeda wrote:

@jos, yes that's a workaround to a bad implementation. I'll post a descriptive picture.

Observe that neither Boolean nor DateTime can be inserted straight off the bat. I thought the whole idea was to use clusters that matched the database fields ...

In theorie yes. And I understand your frustration, but the Database Conenctivity Toolkit makes use of the Windows ADO system. This is a generic ActiveX based database interface that has to work for all databases that come with either an ADO or ODBC driver. And all those drivers have very different ideas about what ADO or ODBC specifically means. When you program an application to interface to a specific database backend you usually do not worry about SQL syntax and functionality difference (well you might have to when you upgrade the database backend ) but this luxery is not possible for the people writing the Database Connectivity Toolkit.

Sometimes you simply have only the choice between either limiting certain functionality for certain databases or failing for some others when you want to keep it generic. The alternative is to make the interface database backend specific with lots of configuration functionality for most functions where the programmer has to enable or disable some features depending on the database he is using. Such an interface is even more cumbersome to develop and especially maintain, and a complete nightmare for most but the very advanced database programmers to use.

But the fact that prepending %.; doesn't work for absolute (and maybe relative) timestamp formats in Format into String is definitely a bug. NI needs to put that in their CAR database.

Re: Possible DateTime bug in Database Toolkit

josborne — Thu, 31 Mar 2011 12:43:56 GMT

I don't really understand the frustration. Just use that snippet I provided to create the dattime string. And then use it to create your own INSERT statement and then feed it to this VI. Its a simple and functional workaround.