Has anyone written directly to a database from LabVIEW Real-time?

[Chris_Megdanoff]

I would like to provide a real time system that records data to a database. Since reliability is the key issue, I do not want to go through a Windows PC for the database writes. Preferably, I would also not have to put a service on the server to collect the data, but that may be an option.

[Ben]

I have writtten to DB's from LV using the ODBC service offered by Windows. I do not believe this is available in Pharlap or on the FP-RT platform.

If the instability of Windows appears to be a concern, I would recommend a fault tolerant design that could handle the Windows platform going down occationally. This is what I have in mind.

Three machines,
1) DB Server
2) DB Writter
3) RT Data collection

Notes
1) DB Server of your choice. Preferably SQL based.
2) Responsible for pulling readings from RT and writting to DB. Buffer between two systems. More on this below.
3)RT Data Collection and short term storage. More below.

The DB writter acts as a buffer between the short term storage on the RT platform and the server. The data collected from the RT system, I will assume will be coming in at a steady rate. The updates going to the DB should be viewed as being asyncronous.

Break RT app into two parts, Time Critical (TC) and other. The TC loop reads data and queues to the other loop. The other loop should read the queue'd data and write to LV2 style globals. These LV2 globals should (could) maintain an array of the data. If array exceeds some predetermined level, newest data goes to buffer file. This journaling to file will allow the Windows based DB writter to fall behind, re-boot etc.

Meanwhile, on theWindows platform....
DB Writter could periodically use a call by reference node to execute a read operation to the LV2 global written by the "other loop" on the RT platform. Read data is then used to write DB using SQL Tool kit or whatever.

The data collcetion rate will determine the amount of local disk storage you will need on the RT platform to handle buffering backlogs while the Windows app is down. THe size of the cached array in LV2 global should be large enough to handle the non-periodic nature of the DB Writter's read operations. When the LV2 global on the RT platform is read it should return the contents of the cached buffer when a read operation is performed (by the RT Writter). If there are samples waiting in the RT's buffer file, a read of the oldest values should be read from the file and placed in the buffer waiting for the next read. THe LV2 global should alos return a bollean or similar flag that indicates more reading are waiting to be read.

I recognize that this response does not tell you how to write to a DB from the RT platform but it does represent an architecture that will allow you to harness the stability of an RT based app while taking full advantage of the functionality that is already in place.

I am interested in hearing how others would approach this challenge.

Ben

Of course I could be wrong about the SQL toolkit being supported under Pharlap, but this just strikes me as an oxymoron.

[thorvan]

Hi

I am having difficulty with setting up a connection with a Compact Field Point and SQL Server 2000. Currently the scenaria is as follows:

I have a SQL server with which I would like to communicate with directly (preferably no software in between). I have Labview 7 with the SQL toolkit (however its components cannot be deployed on Labview RT, as it does not have the required API's/ODBC/MDAC etc).

I have spoken to several NI representitives, and they were unable to give me a solution (other than FTP, or writing a custom driver/protocol).

I have considered a few:

1. Deploy a protocol/module/ActiveX control that is compatible with SQL onto cFP, push the data to SQL.
2. FTP files off cFP using MS ftp.exe (pull data onto SQL server machine, then import with DTS).
3. FTP files using cFP onto IIS ftp server (push data, then DTS).
4. Install an OPC SQL server/client on the SQL server 2000 machine that will use the OPC protocol to import data into SQL.
5. Write a custom driver/protocol (which will run on the cFP).

What would be the best option, are there others, pros and cons?
I am guessing option 5 is probly going to be the favoured solution, if so what components should I use?


Thanks 4 taking a look.
-Thor

[Aaron_G]

Thor,

In regards to your various options:

1) ActiveX is not supported under the Pharlap OS (or to my knowledge and any OS other than Windows). You could potentially write a LabVIEW module that would communicate to the SQL server, but you will probably have to worry about networking permissions, logon, security, etc... These items will make this an extremely difficult solution.
2) This should be fairly easy to accomplish. As an alternative, you can write a LabVIEW app for your host computer (SQL Server Computer) that uses the Internet Toolkit to FTP the files off the cFP module, and writes the data from the file into the SQL Server using the SQL Toolkit. As another alternative, you can use DataSockets to read the file via FTP, parse it, and write the data to the SQL Server using the SQL Toolkit.
3) This can be done using the Internet Toolkit as in #2.
4) The FieldPoint OPC Server would have to be installed on the SQL Server machine, the cFP controllers do not have built in OPC Servers (OPC is based on DCOM which is an MS technology, some recent RT OS's have started supporting a native version of DCOM/OPC but they are still a minority and it does not exist in the RT OS used by LabVIEW RT). The FieldPoint OPC server will make available all channel data and any data being published from the controller's embedded program through the Data Publish.vi.
5) Doable, but is subject to the same limitations/difficulties as #1 above. One approach would be a modification of numbers 2 & 3, where you create a host-side LabVIEW program that communicates with the cFP controller via a custom TCP protocol that you implement to retrieve data at specified intervals and log the data directly to the database.

In my opinion, options 2, 3, or 5 are the easiest to accomplish. The easiest is probably #2 or #3. I personally would go with #3, use the Internet toolkit in the cFP RT controller and use the Arendar Data Import Agent for my DTS.

As a disclaimer, I work for VI Technology, the developer of Arendar (www.arendar.com), so I do have a bias in choosing to use it to handle the parsing of the files and writing the data to the database.

Regards,
Aaron