Bug in "DB Tools Execute Query VI" ?

[alkazaa]

I observed an error in the "DB Tools Execute Query VI":

Using the SQL query

select reading,timestamp,value from history where (reading = 'f_Zuluft')  and value != '' and timestamp between '2020-08-11 23:50:00' and '2020-08-12 00:10:00' order by timestamp;

the following table is returned:

Submitting the same query from phpMyAdmin, I receive:

It looks like the time '00:00:00' in the timestamp is causing a problem.

(It's a mysql database, with MariaDB 10 as database server)

-Franz

[Sebastian.Weber]

The result is retuned to labview as 2D variant array. What is the content of that array, and how do you convert it to strings?

[FireFist-Redhawk]

Can 100% confirm this bug, because it happened to me not too long ago. Attaching my VI that addressed it. the 2D string array input is a bunch of records with the first column being the time stamps to be checked (just as an informational so you can modify it to suit your needs).

Since then, I learned about the DB Variant to Data function which can take a 1D cluster array as its type input and correctly return a 1D cluster array of records, though I haven't tested it in regards to this bug so no promises there.

[billko]

---

@alkazaa wrote:

I observed an error in the "DB Tools Execute Query VI":

Using the SQL query

select reading,timestamp,value from history where (reading = 'f_Zuluft')  and value != '' and timestamp between '2020-08-11 23:50:00' and '2020-08-12 00:10:00' order by timestamp;

the following table is returned:

Submitting the same query from phpMyAdmin, I receive:

It looks like the time '00:00:00' in the timestamp is causing a problem.

(It's a mysql database, with MariaDB 10 as database server)

-Franz

---

Don't take any data at midnight.  😉

[drjdpowell]

I don't get it; what bug are you talking about?  The data looks correct.

[Sebastian.Weber · Accepted solution]

Interesting, didn't know this, had to try out.

So, indeed, the variant does not contain time if the timestamp is midnight. (LV2017)

Since I always use the database variant to cluster VI, which handles this correctly when timestamp is used as datatype, I didn't notice...

[drjdpowell]

You guys keep showing different text-formatted representations of binary timestamps, rather than different timestamps.  The different formats don't mean that actual time point is any different.  

Different choices of formatting isn't really a "bug".

[alkazaa]

Thanks to all who replied to this thread.

I noticed only now that I blamed the wrong subVI in the title of my post, and I apologize for the confusion this has caused.

(Is it possible to change the title of a thread?)

Actually the error occurs in the subVI "DB Tools Fetch Table Data.vi". The subVI is part of the database connectivity toolkit and is located in "...\National Instruments\LabVIEW 2020\vi.lib\addons\database\Auxilliary.llb\DB Tools Fetch Table Data.vi". I found this subVI via the LabVIEW example finder where I modified the example "C:\Program Files (x86)\National Instruments\LabVIEW 2020\examples\database\Read All Data.vi",

Within "DB Tools Fetch Table Data.vi" it is the function "Database Variant to Data" where the conversion from the database variant from output 'GetRows' of invoke node '_Recordset' actually occurs:

I'll try to post a code snippet later with a stripped down example of my code, including the problematic data.

Regards

Franz

OK, here's the code snippet:

The block diagram constant variant was copy-pasted from my VI.