LabVIEW
Parametrized database query returns different results from hardcoded query on new Windows 10 machines, but not old W7 machines or an old W10 machine.
Solved!
We have a mature set of software that we've been running on Windows 7 for years. We have a VI, Dusty_Type_Query.vi that queries our MySQL database with
SELECT
*
FROM
dust_type
WHERE
dust_name LIKE ?;
with parameter "%name%" where name is an optional input if you want to search amongst data with a certain dust_name. In 99% of cases, this optional query is omitted, and the query runs as
SELECT
*
FROM
dust_type
WHERE
dust_name LIKE "%%";
which returns an array of data, the first element of which is id_dust_type, a value that runs from 0 to 104. Of course, in such a case, the WHERE dust_name LIKE "%%" could be removed and you'd get the same result.
However, we've recently upgraded our machines to Windows 10 (but we continue to run LabVIEW 2015), and while the rest of our software is working, this code has recently started returning strange, incorrect results, but only on these two new machines. It returns the correct array of data as before with one critical exception: all of the id_dust_type data are 0. So, the exact same VI correctly reads a dozen other entries of various data types, but the first column of data is always zero.
I have had this software on my personal windows 10 laptop, also for years, and it works without issue there.
What's truly bizarre is that hardcoding the query to the exact same thing as what it should be results in expected operation. That is, running the VI with hardcoded
SELECT
*
FROM
dust_type
WHERE
dust_name LIKE "%%";
works as expected, outputting the correct data, id_dust_event included.
The VI we have outputs the fully parametrized query, and while this is something one of my colleagues cooked up years ago, running whatever it outputs in MySQL has in the past never returned a different result than the VI itself. That's aside from the fact that just by looking at the code you can see it should replicate the parametrized query as an output.
Losing my mind, I copied the text from this using the working hardcoded method and the not-working parametrized method. I then ran a diff on them to see if something was escaping my eye, but nothing was.
If there were an error, I'd expect something to complain, not to merely output data with wrong values, and in only one of the dozen columns at that.
I also tried running it with dust_name specified to "Iron" which is our most commonly used dust. This didn't change anything. The parametrized query returned all of our iron dust data but with id_dust_event again all incorrectly returned as 0. Hardcoding the query to
...
WHERE dust_name LIKE "%Iron%";
works as expected, returning the iron data but with correct id_dust_event numbers.
I have never seen anything like this and would very much appreciate it if someone could restore my sanity.
working:
not working:
Param DB Query:
Running LabVIEW 2015 on Windows 10 Enterprise.
LabVIEW 2015 Database Connectivity Toolkit 15.0.196 (this is the same on my personal laptop, where the code works)
You are obviously using the Database Toolkit. While that does some logic on LabVIEW level most of it is delegated to ADO/DAO through the ActiveX interface which then handles the queries either through a ADO driver or via the ADO-ODBC bridge through an ODBC driver.
So my guess is that you are not using the same MySQL driver on the different systems. Either that or there must be a difference in the ADO/DAO layer itself between the computers. Differences in parameterized queries between driver versions is absolutely not something that would surprise me, it's a complicated interface and there are many possible reasons to change behaviour there between versions.
