Possible DateTime bug in Database Toolkit

[Yamaeda]

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

[JÞB]

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.

[josborne]

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!

[rolfk]

What database server and version?

[Yamaeda]

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.

/Y

[JÞB]

---

@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

[Yamaeda]

---

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

/Y

[rolfk]

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!

[Yamaeda]

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

/Y

[josborne]

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