topic Re: access MS SQL Database from cRIO in LabVIEW

access MS SQL Database from cRIO

amatz11 — Mon, 05 Nov 2012 10:35:23 GMT

i want to log the data from cRIO into MS SQL instead of MySQL. Any others experience on doing this? I've tried to log the data into MS SQL from cRIO, but failed. Is there any available MS SQL driver for cRIO? I've successfully log the data from Windows by using Database Connectivity Toolkit, however ideally i want to log data straightaway from cRIO. I already using TCP/IP communication but its meanly use for mysql (https://decibel.ni.com/content/docs/DOC-17093). I also have go through the "SQLite" but facing problem how to make sure this vi connect to my ms sql server because there is no "IP" input.

Re: access MS SQL Database from cRIO

Brandyn — Tue, 06 Nov 2012 22:40:21 GMT

there is no server/client with SQLite, thats the beauty of it.

Re: access MS SQL Database from cRIO

Brandyn — Tue, 06 Nov 2012 22:42:29 GMT

I ran into this same issue on a a project a little while ago and was trying to find a solution. Im not sure if we couldnt find something or we did find something and it was pricey. Try searching the NI tools network. We did end up having a host application that ran on Windows that the CRIO would send info to and it logged to an SQL server.

Re: access MS SQL Database from cRIO

amatz11 — Wed, 07 Nov 2012 00:20:25 GMT

as i know cRIO writing to SQLite which can be stored in cRIO. However, I still need to send the data back to server using tcp/ip. SQLite is used as a temporary buffer to move data between cRIO to server. so it totally not my choice..

Re: access MS SQL Database from cRIO

Brandyn — Wed, 07 Nov 2012 00:27:59 GMT

Ok. So what you probably should do is have your CRIO send data via TCP to a host application, and then in that host application write data to the SQL database.

So it will look like

CRIO->(TCP)Host Application->(Database connectivty toolkit)SQL Server

Re: access MS SQL Database from cRIO

amatz11 — Wed, 07 Nov 2012 00:28:57 GMT

my concern why i want to access MS SQL directly from crio:

I got 4 station (will expand in future), each station got 1 crio to collecting data and manage system.

if i need to run a deamon at host that runs windows, im afraid the system will collapse or 'hang'. else i need to buy the industrial or high end spec PC to only manage the Database which is not applicable.

please advice..

Re: access MS SQL Database from cRIO

Brandyn — Wed, 07 Nov 2012 00:31:59 GMT

Why would the system hang? You just need to design the system to service each connection independently so it can scale nicely.Ive done application that connects to multiple CRIOs at a time. 🙂 The trick would be designing your architecture to be scalable.

Re: access MS SQL Database from cRIO

amatz11 — Wed, 07 Nov 2012 00:45:01 GMT

thats why im asking here... scalable architecture, hmmm... how to make it done? huhu

sorry i just a CLAD (expired already 2 month ago)

Re: access MS SQL Database from cRIO

Brandyn — Wed, 07 Nov 2012 00:48:25 GMT

That my friend is the ultimate question.

Re: access MS SQL Database from cRIO

amatz11 — Wed, 07 Nov 2012 00:53:34 GMT

ok let me rephrase my question... are there any scalable architecture example or program that u have done before? maybe can share with me...

Re: access MS SQL Database from cRIO

Brandyn — Wed, 07 Nov 2012 00:56:28 GMT

You have some options. You can go down the Actor framework route or you can develop a "typical" architecture that you would develop for the CLA exam. So without having full details on the application, the first thing you need to do is break things down into individual processes or "modules". From what I have gleaned from this short conversation you would need the following Modules:

1. TCP Module: (This module would be responsible for reading TCP messages from the CRIO(s) and then passing it on to whoever needs it.

2. SQL Module (This module would be resopnosible for writing data to the SQL Server)

Then you would need to define the messaging mechanism. The best thing in my opinion to use are dynamic events. Then the structure of the messages or data that is passed would be a Cluster containing a string/enum and variant. (String/Enum to denote the message name or state to go to and the variant will be the data.

Re: access MS SQL Database from cRIO

amatz11 — Wed, 07 Nov 2012 01:06:06 GMT

ok i think i know what should i do but if u have simple program it will more helpful for me to understand...

anyway thank you for your advice...

Re: access MS SQL Database from cRIO

Brandyn — Wed, 07 Nov 2012 01:20:28 GMT

Its very difficult to explain over a forum.

Re: access MS SQL Database from cRIO

amatz11 — Wed, 07 Nov 2012 01:22:13 GMT

maybe u can make simple block diagram for me?

Re: access MS SQL Database from cRIO

Brandyn — Wed, 07 Nov 2012 01:25:47 GMT

The challenge is its multiple Block diagrams. I think a good starting point would be looking at:

http://blog.jki.net/events/niweek-2011-beyond-state-machines-slides-and-code-now-available/

Re: access MS SQL Database from cRIO

tshelleyamrc — Thu, 19 Feb 2015 09:31:52 GMT

I have just read this thread with great interest. I am also trying to implement a similar type of system with multiple cRIOs to a MS SQL database.

How did you progress with finding a solution to your proplem? and are you able to share you findings?

Re: access MS SQL Database from cRIO

mikeporter — Fri, 20 Feb 2015 05:43:27 GMT

There are an awful lot of variables in play here:
How many crios? How much data is each one generating? How often is data being transfered? What does the data look like? What is the table structure you're inserting into? What version of SQL Server? Who else is on the network?

Having an in between computer, as was advised, it an excellent approach, but there are details to be sorted out.

Mike..

Re: access MS SQL Database from cRIO

tshelleyamrc — Fri, 20 Feb 2015 08:54:09 GMT

Hi Mike,

I would just like to give some context to the problem. I work in the High value manufacturing & machining, my team and i (with support from NI) have a research project which looking into Machine Health monitoring anything from power, spindle power, ambient temperature, vibration and acqustics. The aim is to produce a "black box" (as decsribed my mechanical engineers, potentially a cRIO or SoM), which selects 2/3 data types (temp, vibration, power) and creates a tool capable of capturing this data, what ever it maybe and pushing it into a custom database with relevant meta information, which can then be used for post-prosing via something like diadem/matlab etc.. I would also note sometimes it is not feasible to have a windows computer between the cRIO and database because these devices could be located inside machines..

I fully understand there are several your comments and have already asked several myself, however the aim is to produce a flexible arcitechure which can cope with 90% of the requirements, and we will accept that sometimes the system will not be capbable of the 10%. Intially there will be 2 cRIO of the network which will talk to a mySQL database located on the companies virtual machine. We need to look into the local security and create a password to stop direct access to the cRIOs.

With respect to data rate i can envisgae anything from 1s per minute to 10000 per/s. However for this project we can determine this for this project, based on the sensors we select. The mySQL tables will be design to recieve the relevant data from the cRIOs and most importantly contain the meta data about when, where and how.. which makes the information very useful in post processing, we're currently thinking about what the tables will look like.

If you have any further comments please let me know, we are at the early stages on concept and i would appreacte and comments and questions on our approach and stratergy.

Re: access MS SQL Database from cRIO

mikeporter — Fri, 20 Feb 2015 19:00:00 GMT

What I think I would do is look at creating a separate box (you can call it something like a data concentrator). This box would be a small rugged industrial PC running LabVIEW. It could connect to 4 to 8 crio systems via Ethernet and the server on the other side. Communication with the crio would be via something reliable like TCP/IP or VI Server. Do not let NI sucker you into using shared variables -- you need reliability.

The connection to the server could be wired or wireless, your choice.

The box's job is to gather data from the attached crio systems and transfer it to the database server. This data concentrator box could even become a separate product in its own right.

Mike...

Re: access MS SQL Database from cRIO

smithd — Sat, 21 Feb 2015 01:22:30 GMT

I don't know for sure what sort of rates you'd be able to get out of a mySQL database on a cRIO (or even across the network, as the data transfer may also be a bottleneck), but I'd highly recommend proving that out first.

What I've seen elsewhere is people logging to a file system designed for streaming (like a tdms file or binary file) and then pulling that data on occasion across the network where it is processed. A good starting point is this: http://www.ni.com/example/31388/en/

It doesn't implement what I described (it only logs the data, it doesn't handle transferring it) but its pretty close and we've used it a ton for logging applications.

Depending on how many of these you're making and what this is being plugged in to, you might take a look at this: http://www.ni.com/white-paper/52454/en/

I don't honestly know too much about it, but if you're doing health monitoring its worth looking in to.