NI Linux Real-Time Discussions

cancel
Showing results for 
Search instead for 
Did you mean: 

Linux RT accessing SQL Database

Solved!
Go to solution

We're looking to use a cRIO Linux RT target which needs to talk to our SQL Server database. Does NI LabVIEW for Linux RT include any native database accessibility functionality? If not, are there any third party toolkits for this yet? The NI LabVIEW Database Connectivity Toolkit is only compatible with Windows targets.

Thoric (CLA, CLED, CTD and LabVIEW Champion)


0 Kudos
Message 1 of 10
(8,171 Views)

Hi Thoric,

 

There's a 3rd party toolkit that might meet your requirements and is already supported on NI Linux RT targets, check out the Raima Database API (looks like they need to update the model page where it says NI Linux RT support coming soon). Here's the Raima.com page with more evaluation information as well: http://raima.com/ni/ 

 

Deborah Burke
NI Hardware and Drivers Product Manager
Certified LabVIEW Architect
Message 2 of 10
(8,150 Views)

@Deborah_B wrote:

Hi Thoric,

 

There's a 3rd party toolkit that might meet your requirements and is already supported on NI Linux RT targets, check out the Raima Database API (looks like they need to update the model page where it says NI Linux RT support coming soon). Here's the Raima.com page with more evaluation information as well: http://raima.com/ni/ 

 


Hi Deborah,

Thanks for your reply, but no, the Raima database is a custom in-house database solution they developed. I need to communicate with the industry standard Microsoft SQL Server.

The SAPHIR GDataBase appears to only communicate with the simple MySQL database type.

Microsoft SQL Server is an industry accepted mainstream relational database solution, well established where I work for our production site. Does NI not offer an in-house solution for this?

Thoric (CLA, CLED, CTD and LabVIEW Champion)


0 Kudos
Message 3 of 10
(8,128 Views)

Hi Thoric,

 

If you're willing to put in a bit of elbow grease, you can always use the SystemExec or Call Library Function nodes to access an installable (through opkg) SQL client package.

 

There's an outdated example of using PostgreSQL which can at least give you some ideas/pointers, but it's installing and using a local db on the controller (so you'd want to install just the client, and configure it to access your remote db server).

 

Is this a reasonable approach, or were you looking for something a bit more turnkey?

0 Kudos
Message 4 of 10
(8,121 Views)
Solution
Accepted by Thoric

@Thoric wrote:

We're looking to use a cRIO Linux RT target which needs to talk to our SQL Server database. Does NI LabVIEW for Linux RT include any native database accessibility functionality? If not, are there any third party toolkits for this yet? The NI LabVIEW Database Connectivity Toolkit is only compatible with Windows targets.


That's because it is a fairly thin wrapper around the ActiveX OLE Database Access interface, and OLE/ActiveX was at one time among other things, Microsofts very successful way of locking providers of ActiveX based solutions tightly into the Windows world.

Basically you can't get OLE/ActiveX working on non-Windows systems, and while that is a little bit less the case for .Net, it is still a pretty risky business decision to rely for professional solutions on .Net on non-Windows systems, even with the .Net Core CLR being now open sourced.

Microsoft has a dilemma with SQL Server on Linux, as the SQL Server group would love to support it for commercial reasons (less so for technical support reasons as supporting the myriad of Linux distributions is absolutely impossible) but other parts of Microsoft don't really like SQL Server to be to successful on non-Windows systems.

Another problem is that the communication protocol for SQL Server, while based on the old documented TDS protocol format version 4.2 from Sybase, has been extended by Microsoft in several ways and is in the latest SQL Server release at version 8.0, but the documentation of those modifications and extensions is a well protected secret from Microsoft. Maybe one could get a specification from Microsoft for a substantial amount of money and under a strict NDA, but why should NI want to bother with that? They are not in the business of databases and can for all practical purposes only provide a sub-optimal solution, that would likely upset even more customers, than not having a solution available.

 

So what options do we have:

 

1) For 64-bit x86 systems, Microsoft now has an official release of SQL Server for certain Linux distributions. Obviously the NI Linux RT system is not one of the supported distributions, but there is a good chance that the ODBC driver would still work on the x86 based (903x higher end systems) cRIOs if you can get the correct unixODBC manager installed on them. The Microsoft ODBC driver requires explicitly unixODBC 2.3.0 and will bark on older AND newer versions of the unixODBC library. This obviously will not work on the lower end ARM based cRIOs, as the Microsoft ODBC driver is a precompiled binary shared library for 64-bit x86 architecture.

 

2) FreeTDS is an OpenSource project which implements the original TDS 4.2 specification as described by the Sybase document, and has been extended to support many of the version 7.x extensions introduced by the various SQL Server releases since SQL Server 7.0. This support has been mainly added by reverse engineering the communication protocol and while it seems to work for most of those features, it is of course not something you can consider a stable implementation based on proper documentation. So it may be a problem to rely on this project for critical data.

Compiling this library for NI Linux RT, both ARM and 64-bit x86 based, is certainly a possibility, but won't likely be as simple as typing make and make install on the command line for these targets. It's quite likely that you need to at least adapt the make scripts some for the NI Linux RT system, and possibly even make small changes to the C source code.

The FreeTDS library can be both accessed through an ODBC interface or directly as shared library, so with the use of the Call Library Node one might be able to completely avoid the unixODBC interfacing. You won't be able to avoid using Call Library Nodes anyways, either for calling the FreeTDS library directly or to call the unixODBC interface instead.

 

3) Since the TDS protocol is basically a binary stream based TCP/IP protocol, there is theoretically the option to implement it all natively in LabVIEW, by using the TCP Read and TCP Write nodes. This would be an interesting project to tackle  and I believe someone has tried that at some point, but can't find any references to that currently. But, it's also a major undertaking that will require many man hours of work to do, and even much more for debugging and then you have a library that may or may not work, but is in fact even less likely to implement all the protocol elements properly than the FreeTDS library. Basically it's way to much work to do for free and to much uncertainty to sell for money.

 

Summary: I think this tells you pretty much why NI doesn't have a ready made solution available and why it is very unlikely they ever will have one. It doesn't make much sense to base a commercial and possibly mission critical solution on a reverse engineered open source library, and it's to expensive and maybe even impossible to get the necessary official documentation from Microsoft. And other solutions don't seem to exist. The real party to bugger about this would be Microsoft, but I can guess their response on such a request.

Rolf Kalbermatter
My Blog
Message 5 of 10
(8,112 Views)

...golf clap

 

Thanks for that excellent, thorough writeup rolfk. (should it be a rolf klap instead?)

Message 6 of 10
(8,100 Views)

rolfk,

I've read your reply at least twice, and I thank you wholeheartedly for putting in the effort to create such a useful and concise write up of the status quo.

What I take away from this is:

1. Typical Microsoft are making it hard for IT to support anything non-Windows. Thus Linux support for MS SQL Server is sparse and unofficial. Unfortunately I cannot change the database choice as IT dictate MS SQL Server.

2. NI have a very quiet stance on this and therefore I can presume they've looked and found that there's no robust solution.

3. Moving forward, I either

3a. Struggle with a potential Linux solution on NI Linux RT systems - with no guarantee or visibility of effort

3b. Consider the Windows Embedded based NI cRIOs on the assumption the Windows ODBC drivers will install, offering a limited choice

3c. Abandon considering cRIO for all our ATEs and stick to stupid DELL PCs, as per the current norm. Sigh.

 

 

 

Thoric (CLA, CLED, CTD and LabVIEW Champion)


0 Kudos
Message 7 of 10
(8,066 Views)

@Thoric wrote:

rolfk,

I've read your reply at least twice, and I thank you wholeheartedly for putting in the effort to create such a useful and concise write up of the status quo.

What I take away from this is:

1. Typical Microsoft are making it hard for IT to support anything non-Windows. Thus Linux support for MS SQL Server is sparse and unofficial. Unfortunately I cannot change the database choice as IT dictate MS SQL Server.

2. NI have a very quiet stance on this and therefore I can presume they've looked and found that there's no robust solution.

3. Moving forward, I either

3a. Struggle with a potential Linux solution on NI Linux RT systems - with no guarantee or visibility of effort

3b. Consider the Windows Embedded based NI cRIOs on the assumption the Windows ODBC drivers will install, offering a limited choice

3c. Abandon considering cRIO for all our ATEs and stick to stupid DELL PCs, as per the current norm. Sigh.


3b) should indeed work, if you didn't disable substantial Windows components when creating the embedded environment. Out of the box as delivered by NI, they definitely should be able to run the SQL Server native driver in the ODBC manager.

 

But there definitely is at least one other option:

4) have somewhere in your building a normal networked PC where you can install a LabVIEW executable that operates as proxy between your cRIOs and the SQL database. Implement a TCP/IP server in it that can receive requests and then forwards them through the Database Toolkit or whatever database access interface you have to the actual SQL Server database. Then each of your cRIOs simple opens a connection to that proxy application whenever it wants something written to the SQL Server (or queried from it if you want to).

I'm pretty sure that a proof of concept for this could be done in a day or so, with some more time to make it a robust and versatile solution.

 

Rolf Kalbermatter
My Blog
Message 8 of 10
(8,064 Views)

I - amongst many others, I guess - have done this several times. I've built proxy applications that have an API, and I've built proxy applications that only forward plain network traffic.

 

The latter took my perhaps three hours. We had to circumvent a bug with a power supply that would communicate with LabVIEW on Windows but not with LabVIEW Real-Time for some stupid flag set in the TCP header data that seemed to crash the power supply's network stack...

 

I understand that an additional device is not what we all would like to have, but it's definitely feasible, just as Rolf said.




DSH Pragmatic Software Development Workshops (Fab, Steve, Brian and me)
Release Automation Tools for LabVIEW (CI/CD integration with LabVIEW)
HSE Discord Server (Discuss our free and commercial tools and services)
DQMH® (The Future of Team-Based LabVIEW Development)


Message 9 of 10
(8,062 Views)

@rolfk wrote:

4) have somewhere in your building a normal networked PC where you can install a LabVIEW executable that operates as proxy between your cRIOs and the SQL database. Implement a TCP/IP server in it that can receive requests and then forwards them through the Database Toolkit or whatever database access interface you have to the actual SQL Server database. Then each of your cRIOs simple opens a connection to that proxy application whenever it wants something written to the SQL Server (or queried from it if you want to).

I'm pretty sure that a proof of concept for this could be done in a day or so, with some more time to make it a robust and versatile solution.

 


That was something I was talking to my colleague about on Thursday. He suggested a web service (his expertise) to receive requests from the cRIOs, acting as a proxy. IT can spin up another virtual server for us pretty easily, upon which we can either run a web service, or I can write a LabVIEW service. Saves us managing a desktop PC - I'm trying to step away from that where possible.

Hmm. Need to think about this more. Not happy, but I appreciate these industrial solutions aren't all entirely cooperative so sometimes we have to take these measures.

Thoric (CLA, CLED, CTD and LabVIEW Champion)


0 Kudos
Message 10 of 10
(8,058 Views)