I have to store measured data to MySQL database. During this process I would like to count a number of records in a database table. It is also necessary to filter records. I'm using the SQL statement as follows:
SELECT COUNT (fieldname) AS cnt FROM table WHERE condition
Because I haven't got any result I've created a small database "mysweetdb" and table "icecream" to test my VIs (see picture bellow).
The VI is attached. The result of counting is a 2D array (DB Tools Fetch Recordset Data.VI output) with one element (one row and one column). This element contains no data.
I usually don't give up easily so I've created a similar MS ACCESS database and counted data in it. I've got a correct answer: 3. So it might be possible that there is something wrong with MySQL driver. But I've created a small Delphi project and I've counted records in MySQL database and MS ACCESS database. Both results were correct.
Can anyone explain where is the problem in communication between LabView and MySQL database?
Versions: XAMPP for Windows 1.7.3, LabView Developer Suite 2010 SP1, MySQL 5.1 ODBC driver
Thanks in advance for your suggestions.
In the mean time I've found a solution here: Native LabVIEW TCP/IP Connector for mySQL Database
But I don't like it very much (see link and this comment in the introduction: Those on Windows will find more stability and features with the LabVIEW Database Connectivity Toolkit which will allow connection to any database linked with Windows ODBC).
I have used the LabSQL library extensively with MySQL and it works great. The version I have is for LabVIEW 6.1 but it should upgrade just fine to later versions.
thank you for your answer. An updated version of my VI is attached. It uses two options: LabSQL library and DB Connectivity Toolkit. LabSQL gives correct results.
FWIW, I got the DB Connectivity tool kit to work for my mySQL database by changing parameters in the ODBC connection. I set "Treat BIGINT columns as INT columns" and "Always handle binary funciton results as character data". Now my COUNT() function returns results as expected rather than nulls.