crio sqlserver cRIO (9040) & SQL Server access

[Ghis]

Hello World 😄

I'm looking for informations about cRIO and database access and I hope you can help me.

*******************************************************************************

I'd like to develop an embedded Labview code in my cRIO-9040 that could:

- 1/ Recover data on a SQLserver database (read only) ;

- 2/ Create a data file ;

- 3/ Back up the data file in a network space of my company ;

*******************************************************************************

2/ 

Create a data file: not really a problem ^^

3/

I started my tests by:
- share a folder of my pc (RW)
- provide cRIO access to my PC, including the previous folder by Putty software: "mount -t cifs //[IP of remote server]/[name of shared folder]/ -o username=[your username],password=[your password],uid=lvuser /home/lvuser/data"
=> it works. The program makes the file transfer to my PC.
/!\ But when I use the same method to the server, I have an mount error: "mount: /home/lvuser/data: mount(2) system call failed: Operation not supported."
(I know Linux is case-sensitive, so I paid attention to characters in the path but...)

1/

This is my biggest problem. I read that it was not possible to use the Database APIs (because it's not possible to create ODBC driver on Linux?).

So I don't know how to access the database by Linux, and even more so on a Microsoft database (SQL server) 😪

In advance, thank you for your help

[ShaunR]

You can use TCP for MySQL but for SQL Server I don't know of any non ODBC LabVIEW solution since the backend protocol is proprietry.

What you can do is install the SQL Server command line tools for Linux then use the LabVIEW Exec VI to execute "sqlcmd".

[Rolf Kalbermatter]

2 hours ago, ShaunR said:

You can use TCP for MySQL but for SQL Server I don't know of any non ODBC LabVIEW solution since the backend protocol is proprietry.

What you can do is install the SQL Server command line tools for Linux then use the LabVIEW Exec VI to execute "sqlcmd".

The SQL Server protocol has been reverse engineered as it is based on the old Sybase Tabular Data Stream format that Sybase did document at some point in a public document. Microsoft licensed the Sybase code and eventually created the SQL Server product from that. The underlaying network protocol is still very much the original TDS format with some extra features sprinkled into it by Microsoft over the years.

This is the MS document from the original Sybase 4.2 version of the TDS documentation.

This is a more detailed documentation about the later Microsoft modifications to the protocol.

The "official" open source implementation of the TDS protocol is done by the FreeTDS project. Their implementation works also for the more modern 7.x protocol versions used in recent SQL Server versions, but as anything that was reverse engineered (even with proper protocol documentation) there are some obscure features that might not work properly with the FreeTDS implementation. Compiling the FreeTDS sources for a cRIO shouldn't be that complicated. Reimplementing it all in LabVIEW on top of the TCP primitives is theoretically also doable but the encryption features of this protocol will be hard to implement properly in this way.

[ShaunR]

2 hours ago, Rolf Kalbermatter said:

Reimplementing it all in LabVIEW on top of the TCP primitives is theoretically also doable but the encryption features of this protocol will be hard to implement properly in this way.

NTLM is OK and I expect I could get the SSL/TLS with a little effort (at worst, using memory BIOs and hand-cranking it through). The rest, though....

Thanks for the info, very interesting. It strikes me to be much more than a few weeks concerted effort to just get a base-line native LabVIEW implementation (not a learning execrise/fun project). I doubt anyone would be prepared to pay for it when there are other off-the-shelf solutions for SQL (MySQL. MariaDB) that are already available in LabVIEW - It looks  far more hassle than it's worth just to get something on RT.

What are your thoughts on Linux ODBC? 

 

Edited October 10, 2019 by ShaunR

[Rolf Kalbermatter]

Linux ODBC or iODBC or unixODBC work in principle. The idea is not bad but hampered by the fact that you need a compiled version of a database ODBC driver for your platform. As that is not generally something all database providers are eager to do, it makes unixODBC less interesting overall.

In cases where you have a native driver like FreeTDS I would usually consider that preferable to trying to get unixODBC to work. unixODBC is an extra complication on top of a driver and the ODBC manager implementation is pretty complex in order to provide version compatibility between both higher and lower version ODBC clients and higher and lower version drivers.. This makes setting up a unixODBC installation more cumbersome. On the upside is of course the advantage to only have to interface to one API and connect to different databases simply by reconfiguring the connection.

 

Edited October 10, 2019 by Rolf Kalbermatter

[Rolf Kalbermatter]

On 10/10/2019 at 9:43 AM, Ghislain Hatton said:

This is my biggest problem. I read that it was not possible to use the Database APIs (because it's not possible to create ODBC driver on Linux?).

Actually unixODB does exist but it's not trivial to find drivers for some database systems that will work for your unixODBC version. The Microsoft SQL Server drivers for unixODBC require a very specific version of unixODBC to work with.

The bigger problem however is that pretty much all database access libraries that exist for LabVIEW do this either through the ADO/DAO Active X interface or the .Net Database API which in turn internally interfaces to ODBC for the drivers that are not native ADO/DAO or .Net. Both Active X and .Net are not available in LabVIEW on non-Windows systems. So all those Database Toolkits will do nothing for you on a Linux system even if you would get unixODBC setup correctly and working with the driver. Accessing the ODBC API through Call Library Nodes is doable and has been done by some people under Windows although nothing is publicly available that would be considerable as a full toolkit. What I have seen is a starting point but not a full featured toolkit and porting it to work with the unixODBC API would be another extra effort. The ODBC API is complex enough to make this a bit of a challenge also because some of the API interface is not exactly LabVIEW friendly.

Edited October 11, 2019 by Rolf Kalbermatter

[ShaunR]

On 10/10/2019 at 7:43 PM, Rolf Kalbermatter said:

In cases where you have a native driver like FreeTDS I would usually consider that preferable to trying to get unixODBC to work.

Indeed. The "mount your friends" approach is always problematic and I'm probably more eager than you for native solutions (my aversion to intermediary DLLs for example). I'm fortunate that I'm usually in the position to dictate the database to use and would choose MySQL over SQL Server every time.

On 10/11/2019 at 12:41 AM, Rolf Kalbermatter said:

The bigger problem however is that pretty much all database access libraries that exist for LabVIEW do this either through the ADO/DAO Active X interface or the .Net Database API which in turn internally interfaces to ODBC for the drivers that are not native ADO/DAO or .Net.

That will only get worse with NXG. IMO, this is yet another reason to choose Python over LabVIEW for Test and Measurement.

[Rolf Kalbermatter]

11 hours ago, ShaunR said:

That will only get worse with NXG. IMO, this is yet another reason to choose Python over LabVIEW for Test and Measurement.

NXG is Windows only anyhow. Sure NI says that they have been keeping options open for other platforms with NXG and to some extend that must be true because they need to be able to keep building executables for at least the NI Linux RT targets, but I would say it is a safe bet that the significantly longer than expected development time of LabVIEW NXG is not caused mainly by these attempts on maintaining full multiplatform support but that there have most probably rather been decisions to take shortcuts that make a mulitplatform version mostly unfeasable. NI's bet is maybe that .Net Core will eventually get mature enough so they can port NXG to other .Net Core supported platforms easily if and only if the market should turn at some point and Windows is suddenly turning into a niche product 😀.

Python may be an option for those people who have used Visual Basic in the past but I do not see how you can efficiently build Python applications that combine all kinds of high performance IO such as DAQ, Vision, Instrument Control and Graphical User Interfaces all in one. Sure for many bench test systems that is not really needed and a command line operated test application can work too, but that's not quite what most customers want. 😂 And don't tell me there are GUI libraries for Python. I know there are, and I know there are people who have created amazing looking apps that way, but when did you last venture into calling GDI functions to build a GUI? I tried to create GUIs in Java a few years ago and that was a pretty painful experience. The graphical editors available for that are limited and flaky at best. Most autocreated code from them has eventually to be modified and even partly rewritten to get a decent working GUI. I can't see the Python GUI frameworks offering really a better experience.

Edited October 12, 2019 by Rolf Kalbermatter

[ShaunR]

3 hours ago, Rolf Kalbermatter said:

The graphical editors available for that are limited and flaky at best. Most autocreated code from them has eventually to be modified and even partly rewritten to get a decent working GUI. I can't see the Python GUI frameworks offering really a better experience.

Nah. Data to DB or websockets then browser interfaces (Javascript). This is what I do with LabVIEW mostly because, lets face it, the LabVIEW UI is no Gigi Hadid either. Once you go that route. It doesn't matter what language you use on the back-end (or which machine it's running on) and if you look at full time T&M jobs in the UK. They are pretty much all Python with Jenkins experience running on Linux. The UK LabVIEW market has been reduced mainly to turn-key automation and then usually only where they already have a historic  LabVIEW investment.

But we diverge... 

Edited October 13, 2019 by ShaunR

[Rolf Kalbermatter]

On 10/13/2019 at 4:26 AM, ShaunR said:

Nah. Data to DB or websockets then browser interfaces (Javascript). This is what I do with LabVIEW mostly because, lets face it, the LabVIEW UI is no Gigi Hadid either.

For what I and our company does it seems to be more than adequate, but then we don't focus on shiny web applications which sport the latest craze that changes every other year. We build test and manufacturing system where the UI is just a means to control the complex system not the means to its own end. In fact shiny, flashy user interfaces rather distract the operator from what he is meant to do so they are usually very sober and simple. For this the LabVIEW widgets are mostly more than enough and the way of creating a graphical user interface that simple works is still mostly unmatched in any other programming environment that I know.

[ShaunR]

2 hours ago, Rolf Kalbermatter said:

For what I and our company does it seems to be more than adequate, but then we don't focus on shiny web applications which sport the latest craze that changes every other year. We build test and manufacturing system where the UI is just a means to control the complex system not the means to its own end. In fact shiny, flashy user interfaces rather distract the operator from what he is meant to do so they are usually very sober and simple.

It's not so much "shiny web applications" (I just don't have the artistic flair for such things). It's more to do with having cross-platform, internationalised interfaces - which LabVIEW really sucks at.

I don't know about you but even with trivial applications I seem to spend 70% of my time with UI property nodes just getting it to behave properly. I can completely bypass all that by separating the UI from the code, and DB/Websockets does that nicely with the added bonus of UTF8 support in the UI.