I'm hoping someone here can shed a little light on this one for me.
I just started working on a project involving a MySQL database. I'm new to MySQL, so I may be looking in the wrong place to solve this error.
For some reason my table in LabVIEW is appending today's date to my StartTime and EndTime columns. But when I use the MySQL Query Browser or the command line interface, I do not see the date appended to those columns.
The time format is also different in my LabVIEW table. The time data was entered into the database in 24 hour format. If I view that data through either the Query Browser or the command line, I see it in 24 hour format. But my LabVIEW table shows the time in 8:00:00 AM format.
Is there anything I can do to eliminate this behavior?
I've tried looking for something in the Properties, along with what is available through property nodes. So far I haven't found anything.
The time formating I could probably live with. But I definitely don't want dates appended to my time columns.
I've included a couple of screenshots to illustrate what is happening.
Solved! Go to Solution.
I'm using some of the VIs from the Database Connectivity Toolkit. In this case, it would be the DB Tools Select All Data.vi
I'm including my code here as well.
One thing I just noticed was that my "Select All" VI I'm using isn't in the DCT pallet. I'm not sure where it came from actually. Probably from an online example or something.
I tried using the DB Tools Select Data.vi from the pallet. But the data output from that VI is a Variant.
For some reason, the Database Variant to Data.vi on the pallet couldn't/wouldn't convert the variant data to the 2D array of strings that I'm using in my table. It kept calling my sink type as "void". I had to use the Variant to Data.vi from the Variant pallet.
This introduced a huge performance problem though. The table I'm accessing has about 19000 rows in it. My first VI pulled all this data out in just a couple of seconds. The second one where I had to convert the data took so long that I thought the application had hung. It would be unusable. When it does finally spit out the data, it's in the exact same format as the first VI. Maybe I'm doing something wrong with the way I'm converting it?
I'm including the second VI as well. Hopefully I can get to the bottom of this.
I've looked a little further into this and discovered that there are indeed some known issues with the Database Development Toolkit.
I asked one of my co-workers to see if he had the same problems with the Database Variant to Data.vi There we ran into a different problem. He wasn't able to drag that VI to the block diagram at all. As it happens, this is also a documented behavior.
Database Variant to Data VI Causes prim.cpp Error or Cannot be Placed on the Block Diagram
Apparently there are also some problems specifically with large datasets. This is why the Read All Data.vi is included in with the examples. This VI uses optimized versions of the Select All Data and the Fetch Table Data VIs.
My recordset is 19000 rows. So I guess that's "large" in this case. In any case, the Read All Data.vi is way, way faster.
Unfortunately I still haven't figured out how what's causing my time fields to end up with a date in them. I suppose I could just run that column of data through a for loop maybe and strip the date off. But I'd really like to know what's causing it in the first place.
what is the answer? I did not see any answer or solution for that issue. I am going through the same. could you please contact me.
You'll see the message right above that was marked as the solution.
Pallen is regularly on the forums, so he might be able to help you further. S_mercurio whose message was marked as the solution has not been on the forums for awhile.
The answer is in the post marked in green above.
Basically, it was a datatype mismatch. LabVIEW's timestamp didn't match mySQL's Time type. I needed to remove the Date component to get the data to match the format.