Connect to SQL Server from cRIO with NI Linux Real-Time

[rolfk]

Please read my last paragraph in my previous post. From your description on Lava you seem to look for a database server solution. SQLlite uses a local file as database so can't be easily used for remote data storage through a database server. You would need to create your own remote procedure interface on the computer on which you install your SQLLite database and additional clients for all the other systems to write the data to the system on which your SQLLite database is running.

With SQL Server out of the way as a feasable solution you are probably looking at a MySQL (the most popular open source variant of it is probably MariaDB since MySQL got taken over by Oracle and some open source supporters did not like an open source solution owned by a commercial company that competes with its own commercial database products) solution instead. The MySQL server is installed on a server computer somewhere and then your applications talk to it through a network connector. This connector transports the requests over TCP/IP and there is a native MySQL connector in the form of a shared library that can be called in LabVIEW through the Call Library Node. There is also an ODBC driver for MySQL. For Windows you can use the Database Toolkit to connect through this ODBC driver to a MySQL database but on non-Windows systems the Database Toolkit can't work. Interfacing to the unixODBC driver through Call Library Nodes is possible but not easier than directly interfacing the native MySQL shared library.

Last but not least there is a project somewhere where someone tried to implement the MySQL TCP/IP protocol directly in LabVIEW based on the TCP/IP nodes. While this is IMHO the coolest approach as it does not require to fiddle with shared libraries, call library nodes, C compilers for intermediate wrapper shared libraries and all that stuff, it has also its drawbacks.

Creating a performant database interface is a real challenge. A to naive implementation of the translation from LabVIEW datatypes to the binary data stream in the TCP/IP protocol can cost a lot of performance. Also fiddling with bits and bytes on a TCP/IP stream level is pretty tedious.

And implementing features like encrypted data connections inside LabVIEW is a very difficult task.

On the upside is the fact, that such a driver will work on every LabVIEW platform that provides TCP/IP functionality without the need for specifc shared libraries.

All in all, databases are big business, and databases on non-Windows platforms are big non-standard business. This also means that there is quite often no free solutions for things like this. You either buy a ready made solution or spend a lot of time and effort and low level knowledge into creating your own.

[dest2ko]

Rolf, thanks for your patience and bear with me as I'm unfamiliar with database development/management in general. I'm getting a bit more clarity on why SQLlite may not be the best approach despite the dataflow requirements of my project. I can imagine at first thought some issues with SQLlite handling N number of clients concurrently and managing those file writes, though I'm sure that is addressed if the server applications are worth anything.

When you mentioned in both posts there being at least one project out there that can implement MySQL protocol in LabVIEW, I stumbled upon GDatabase by SAPHIR when I did a little digging. Have you had any experience with it?

From SAPHIR: 

"http://www.saphir.fr/en/produits/gdatabase-for-mysql-7.html
'Compatible with LabVIEW Real-Time'
For information our toolkit is based on TCP/IP LabVIEW API (http://www.ni.com/white-paper/2710/en/)
So GDatabase is compatible with VxWorks (old cRIO) and LinuxRT OS (new cRIO) and every target that supporting TCP/IP protocol and LabVIEW. With only one licence you can deploy an unlimited number of RT EXE with LV Build Specifications (in a LV proj)."

I am still pursuing connectivity options for my project. Web services was one, databases another. I may just have to set up a raw TCP thread and simply handle it that way -- timed loop with a large system data cluster flattened to XML and sent across. The guys developing the parser (non-LV) may end up hating me, though. 

[labviewman]

Has anyone had luck with the GDatabase by SAPHIR? Seems pretty slick but I can't get it to connect to my MariaDB 10 database.

I dug a little into the code (use VI hierarchy and picked code out and ran it) and it's is able to make the initial connection (MySQLVIEW.lvlib:MySQLConnector.lvclass:Open.vi) but fails at MySQLVIEW.lvlib:MySQLConnector.lvclass:ReadInitializePacket.vi

[joerg.hampel]

Perhaps you have more luck with this free, open-source toolkit - a MySQL / MariaDB driver for LabVIEW using pure TCP/IP primitives:

https://www.vipm.io/package/hse_lib_hse_mysqlnetcom/

[labviewman]

Thanks! I'll give it a shot after I try to get MySQL running via Docker (using Qnap Storage Container) that I found this morning (Qnap support didn't even mentions this).

[labviewman]

Hi Joerg,

I was finally able to get MySQL installed on my NAS via Docker and runs in a container. Took a while for IT to get me internet access (and for me to figure out how to configure the NAS container!), but at least there now is a process with IT for internet access which is a really big deal to have!

1) Using the VI package link you sent (Thanks!) to connect to the MySQL server using port 3306:

"Error 5000 occurred at 1251 | #08004Client does not support authentication protocol requested by server; consider upgrading MySQL client"

MySQL Workbench connects with port 3306 with a warning that it may not work right since it's developed and tested for MySQL 5.6, 5.7, 8.0, but seems to work otherwise. My version of MySQL is 8.3.0-1.e18.

2) Using port 33060:

The connection VI connects (doesn't care what I put into the database entry) just fine (Open Connection), but any command I execute (I tried "use databasename", "INSERT INTO Test (temp1,temp2,temp3) values (5,6,7)", "select * from Test") gives error 62. These commands worked fine in MySQL Workbench. The error I get with "execute.vi" is:

(LabVIEW: (Hex 0x3E) Serial port overrun error.
=========================
LabVIEW: (Hex 0x3E) The system caused the network connection to be aborted.

MySQL Workbench does not connect with port 33060, states "Protocol mismatch; server version =11, client version = 10"

Seems 3306 is the classic MySQL protocol and 33060 is a "X Protocol"

Any ideas?

Thanks,

Todd

[joerg.hampel]

Hey Todd,

---

1) Using the VI package link you sent (Thanks!) to connect to the MySQL server using port 3306:

"Error 5000 occurred at 1251 | #08004Client does not support authentication protocol requested by server; consider upgrading MySQL client"

Currently, the driver supports only authentication with a SHA1 hashed password, also known as “mysql_native_password”. Maybe you can install the corresponding plugin on your server?

See 
- https://dokuwiki.hampel-soft.com/code/open-source/mysqlnetcom#known_limitations

- https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html

---

2) Using port 33060:

...
Seems 3306 is the classic MySQL protocol and 33060 is a "X Protocol"

---

This driver implements the classic MySQL protocol. The new X protocol works differently (it uses Google Protobuffers under the hood for message definition) and you can't use MySQLNetCom with it. 

Good luck!

J.

[labviewman]

Hi Joerg,

Yep, that looks like the problem...mysql_native_password.

So far I can't seem to find a way to change the server in a way that works. Using MySQL 8.0.36

The webpage you states this is for the client side, yet, the command is for MySQL, so I tried it on the MySQL/Container, but get the following error:

[Server] unknown variable 'default-auth=mysql_native_password'

Thank you for all your help!

[labviewman]

I found a work around, even with the latest version of MySQL pulled in QNAP NAS Docker (8.3.0). Using MySQL Workbench, I created a new user, gave it the privileges it needs, and changed the authentication type to "Standard". Now...the VIs work, at least connect and insert work.

Not elegant, but works all the same it seem.

Thanks again Joerg!

[joerg.hampel]

Happy to hear it works now, and thanks for sharing the how-to.