ASTM protocol: read data from laboratory equipment
Problem:
We need to collect data from laboratory equipment that supports the ASTM data interchange protocol and write them to a database. What do we need to do?
Terms of reference:
To solve this problem, we are going to use:
1. Advanced OPC Data Logger. License type: Professional, Enterprise, or a trial version
.
2. ASTM Data Parser plugin.
3. SQL Database Pro, the full version (purchased separately), or the trial version.
Introduction
The ASTM (E1381 or E1394) protocol is widely used in laboratory equipment. It allows you to automatically collect measurement data and other relevant data (date, first name, and last name, the highest and the lowest values, measurement number, etc.).
Such equipment usually supports multiple data interchange protocols. You may need to configure your equipment to use the ASTM protocol.
The following popular laboratory facilities are supported, among others:
AXSYM
ELECSYS 2010
Hitachi 7600
DIAGNOSTICA STAGO - STR-R
Thermo Clinical Labsystems KoneLab
Diagnostic Products Corporation Immulite
Abbott Architect
Sysmex CA-6000, XN-350, XN-450, XN-1000 and others
Bayer CLINITEK 500
Synthesis (IL)
BECKMAN ACCESS
Hologic Panther System
i-SENS i-Smart Pro
Tokyo Boeki Prestige 24i
More vendors are added to the supported equipment list every year, and the ASTM standard is becoming even more widespread. It means that our program can work with other equipment, too. The list above is given just as an example.
Step 1: Configure the equipment connection.
Typically, the serial port is used for connecting to the equipment. So you need to create a new configuration and configure the COM port settings (fig. 1a). The COM port can be either real or virtual. For example, you can obtain data via an RS232-to-USB adapter. The connection parameters depend on equipment settings.
Figure 1a. COM port settings
If you connect your instrument via TCP, there are two modes "Server" and "Client". You should configure our software as a client (fig. 1b) if your instrument works as a host (you cannot configure a destination IP address). If you cannot configure the TCP port number in your instrument settings, it should be specified in a device's manual.
Figure 1b. TCP client settings
You should configure our software as a server (fig. 1c) if you can configure a destination IP address to send ASTM data in your instrument. In this mode, your instrument initiates a connection and the logger listens for incoming data.
Figure 1c. TCP server settings
Step 2: Select a parser.
You need to select the ASTM module as a parser (figure 2). You may download the ASTM parser here if it does not exist in the list. You don't need to configure any additional settings for the parser.
Moreover, we offer the special version for TCP connections named "ASTM [TCP]". You can try it if the standard ASTM parser does not work for you.
Figure 2. Select a parser
Step 3: Select and configure the data export module.
For example, here, you can see how you can export data to an MS SQL database running on the same computer.
Before you can do that, you need to use a database management utility to create a database table. Possibly, you will also want to create a separate database.
Microsoft SQL Server - SQL Server Management Studio.
MySQL - MySQL Workbench.
PostgreSQL - pgAdmin.
Oracle - Quest Toad, SQL Detective, MyOra, etc.
You can download a sample SQL script for creating a database table from here.
Note: The script creates a table with primary columns only, but the program extracts more values, and you can the necessary columns yourself.
Then you need to enable the SQL Database Pro data export module (figure 3) and configure the database connection (figure 4).
The connection string will look like:
Microsoft SQL Server: IpAddress\Instance:DatabaseName or IpAddress:DatabaseName. Hint: you can start SQL Server Management Studio and find the connection string in the login window.
MySQL: IpAddress:DatabaseName
PostgreSQL: IpAddress:DatabaseName
Oracle: IpAddress:DatabaseName
Figure 3. The data export module
Figure 4. Connection settings
After that, add an SQL query for writing data to the queue and bind parser variables to the SQL query parameters (figure 5):
Click the "Action → Add SQL" button.
Specify any name.
Click on the "SQL text" field in the SQL queue.
Click on the button with "...".
Copy and paste SQL script below or specify your variant.
Click the "OK" button.
Go through all SQL parameters and assign a parser variable in the "Parser item name" field (look at the configuration example at the bottom).
You can download a sample SQL script for inserting data from here or load a preconfigured queue from the file.
