Maximum data transparency with SQL4automation

Mediator between MES and production level

Connecting the automation level to the IT world makes it increasingly possible to make production and machine data available company-wide for the evaluation, planning, coordination and optimization of operational processes. Manufacturing Execution Systems (MES) play an important role here, positioned at the interface between ERP systems and the production execution process. For them to function efficiently, they depend on data from the production level.

Until now, it was often cumbersome to extract data from the production level, as an additional high-level language program and an OPC server were frequently required. While specialized tools are available for individual controllers, there are hardly any cross-controller tools. In addition, there are performance losses with large data volumes when data is accessed via OPC. The company Inasoft provides a manufacturer-independent protocol for communication from the machine to the database with the SQL4automation Connector. Simplicity, speed and flexibility are the focus.

Seamless data exchange with the MES solution

"The SQL4automation Connector is a software solution that allows robot controllers and PLCs to be conveniently connected to databases," explains Ruedi Gloor, Managing Director of Inasoft Systems GmbH. With this solution, controllers have full access to SQL databases with high flexibility using familiar programming environments. Creating a database connection with the Connector requires only a few settings. The SQL command is programmed in the PLC or robot controller and sent to the Connector. The Connector meets very high requirements for reliability and performance — regardless of the hardware manufacturer. This makes data exchange with the MES or ERP level seamless.

The SQL4automation software solution runs on Windows and Linux and uses TCP/IP for data transmission. The database access is characterized by high flexibility and speed. An OPC server is not required. "This has a positive effect on the performance and transparency of the system," the automation specialist is convinced. With the SQL (Structured Query Language) language, data can be queried from tables, inserted into tables, modified and deleted. A cycle takes only a few milliseconds. This speed advantage pays off especially with large data volumes, as 1,000 data points can be transferred at once — in contrast to OPC with serial transmission. "With ODBC, very large amounts of data can be transferred in a short time," says R. Gloor.

"All databases with an ODBC interface are supported," continues the Inasoft Managing Director. Data can thus be written not only to Microsoft SQL Server, SAP, Oracle, MaxDB or MySQL, but even to Excel sheets. And should changes or developments occur in the standard interfaces or the database, this is the concern of the database manufacturer. The user is not confronted with this. The Connector software serves only as a gateway. This simplifies adaptations, as they are implemented in the PLC programming software. The controller determines which data is passed on. No project-specific changes need to be made to the Connector. With the query tool, an SQL command can be easily simulated and tested before it is implemented in the controller (image above).

The highlight: the software works independently of hardware. Any controller with a TCP/IP interface can serve as a data source. Data exchange works with controllers with the appropriate TCP/IP interface, for example with Siemens S7, CODESYS 2.0 and CODESYS 3.0 (Bosch Rexroth, Wago, Elau, Schneider Electric, Festo and others), Beckhoff, B&R and Sigmatek, as well as with robot controllers from Kuka, Stäubli and ABB. Ready-made libraries are available for each of these controllers, which only need to be integrated into the project. "The structure of which data is transferred is openly documented," emphasizes R. Gloor. The corresponding libraries are available for download on the website; for CODESYS also in the CODESYS Store. Based on the existing libraries, users can parameterize data transmission quickly with just a few clicks. "But we also offer this as a service," emphasizes the Managing Director.

Versions for different requirements

SQL4automation is available as a Light version for one controller, a Standard version for ten connections and an Ultimate version for more than ten connections. "This means that multiple controllers can be connected to databases in the shortest possible time with one Connector," says the automation specialist, giving an example: "A variable workpiece length is entered into the database and multiple controllers access it and retrieve the data via the Connector." The Connector, running as a service, thus enables fast access to a central "data repository" for the entire automation solution. This saves considerable effort. The image on the right shows the Config Tool of the solution. Depending on the controller, a standard query takes, for example, 150 ms. However, there are also applications with more than 20 queries per second.

When the controller writes data directly to the SQL database, workpiece data can be logged, for example. "Workpiece number, measurement data, date, time, serial number, as well as production site, line or machine are suddenly available transparently and ready for retrieval in the database, along with any type of messages and important machine data, such as total, production and downtime duration, number and time of workpiece production, and wear of machine components," emphasizes the Managing Director. This concentrated production information can then be used, for example, for MES solutions or production data acquisition. The reverse path is also possible: In the SQL database, production information about the current product type is stored by the MES solution, which is then read out by the controller. R. Gloor: "For larger applications, it is advantageous to use an intermediate computer." The company has such a DIN rail PC in its range.

The SQL4automation Connector was developed in 2007 for industrial use and has proven successful in many industrial applications across versions for different requirements. The Managing Director gives an example of using the Connector for process data acquisition: "Test data for the unique identification of a workpiece is stored in the database. This enables traceability of test data down to the workpiece." This matrix code for various components can also be used when different components are assembled together and a defect needs to be traced. Using the database, it can be determined exactly where the faulty component was produced.

The company Wilhelm Bahmüller Maschinenbau Präzisionswerkzeuge GmbH from Plüderhausen uses the SQL4automation Connector to transfer controller data in the Turbox II folding carton gluers directly to the production data acquisition system. Data exchange with the BDE system runs conveniently via a Firebird database. The collected information can be used company-wide.

The Connector creates maximum data transparency and makes an important contribution to vertical integration. The software tool thus also plays an important role as an "enabler" for Industry 4.0. A demo license can be downloaded free of charge from the website.

‍