News CNCnetPDM About us Support  
         
  

MYSQL MARIADB DATABASE TABLES | CNCNETPDM MANUAL

This section describes the steps to setup required and optional database tables for CNCnetPDM in case you're using a MySQL or MariaDB (Version >= 5.5!)  database.

For a MySQL or MariaDB database the information in section SQL Server Database tables applies also but the scripts to create the tables look slightly different.

MTH_RAW_DATA

The following code can be used to create MTH_RAW_DATA. CNCnetPDM stores all data acquired in every reading cycle in this table. This Alternatively you can use MTH_RAW_DATA_MYSQL.sql from this zip archive. Here database MDA is used, please change that according to your needs.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_RAW_DATA (GROUP_ID BIGINT, READING_TIME DATETIME NOT NULL, DEVICE_NUMBER INT NOT NULL, TAG_CODE VARCHAR(255) NOT NULL, TAG_DATA VARCHAR(255), CREATION_DATE DATETIME, PROCESSING_FLAG INT DEFAULT 2 NOT NULL); -- Setup table to store all records
FIG 1:    Setup script (MySQL or MariaDB) for database table MTH_RAW_DATA TOP

MTH_ONLINE_DATA

To only store the most recent data from your machines you can use table MTH_ONLINE_DATA. CNCnetPDM updates all existing records for every device in this table when new data is queried. The update interval depends on the value for PollInterval in section [GENERAL] of CNCnetPDM.ini. To use this table please make sure that you have set WriteOnlineData = 1 in section [Connect] of CNCnetPDM.ini. You can create table MTH_ONLINE_DATA manually by using the following code or file MTH_ONLINE_DATA_MYSQL.sql from this zip archive. Here again database MDA is used, please change that if needed.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_ONLINE_DATA (GROUP_ID BIGINT, READING_TIME DATETIME NOT NULL, DEVICE_NUMBER INT NOT NULL, TAG_CODE VARCHAR(255) NOT NULL, TAG_DATA VARCHAR(255), CREATION_DATE DATETIME, PROCESSING_FLAG INT DEFAULT 2 NOT NULL); -- Setup table for device monitoring
FIG 2:    Setup script (MySQL or MariaDB) for database table MTH_ONLINE_DATA TOP

MTH_USER_CONTEXT

For table MTH_USER_CONTEXT you can use the following code or alternatively MTH_USER_CONTEXT_MYSQL.sql from this zip archive. Here again database MDA is used, please change that if needed.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_USER_CONTEXT (USER_CONTEXT_PK_KEY INT NOT NULL, SCAN_TIME VARCHAR(255) NOT NULL, EQUIPMENT_FK VARCHAR(255) NOT NULL, WORKORDER_FK VARCHAR(255) NOT NULL, SEGMENT_FK VARCHAR(255) NOT NULL, ITEM_FK VARCHAR(255) NOT NULL, CONSTRAINT MTH_USER_CONTEXT_PK PRIMARY KEY (USER_CONTEXT_PK_KEY)); -- Setup table for workorders, segments and parts
FIG 3:  Setup script (MySQL or MariaDB) for database table MTH_USER_CONTEXT TOP

MTH_EQP_SHIFT_AVAIL_V

To only acquire data within an active shift for a device you can use the following code or alternatively file MTH_EQP_SHIFT_AVAIL_V_MYSQL.SQL from this zip archive.

USE MDA; -- Here database MDA is used
CREATE TABLE MTH_EQP_SHIFT_AVAIL_V (EQUIPMENT_FK VARCHAR(255) NOT NULL); -- Setup table to determine if there is an active shift
FIG 4:  Setup script (MySQL or MariaDB) for database table MTH_EQP_SHIFT_AVAIL_V

For testing you can insert the 4 digit device number into field EQUIPMENT_FK and adjust the .ini file for CNCnetPDM to only collect data within a shift. In a production environment MTH_EQP_SHIFT_AVAIL_V should be a view that looks up if there’s an active shift for the equipment in a shift table and, if yes, returns the 4 digit device number. TOP

Privacy notice

This website uses cookies. By continuing to use it you agree to our privacy policy. 

https://www.inventcom.net/support/cncnetpdm-manual/mysql-mariadb-database-tables
Support | CNCnetPDM Manual | Overview
CNCnetPDM Version 6.0.0.0 User Manual | Here you can find everything you need to... [read more]
Overview
Support | CNCnetPDM Manual | Setup
This section describes the steps to install CNCnetPDM. EXTRACT FILES Create a folder for CNCnetPDM on your PC or at a server. Due to Windows UAC please do... [read more]
Setup
Support | CNCnetPDM Manual | Foreground Program
This section describes the steps to start CNCnetPDM as a foreground program. After extracting the files it is possible and save to immediately start CNCnetPDM. If you like to... [read more]
Foreground Program
Support | CNCnetPDM Manual | Background Service
This section shows the steps to install and start CNCnetPDM as a background service. CNCnetPDM is able to and, in a production environment, usually runs as a windows background... [read more]
Background Service
Support | CNCnetPDM Manual | Instances
This section describes the steps to setup and run multiple instances of CNCnetPDM on a single PC. In case you would like or have to work with different parameters for specific... [read more]
Instances
Support | CNCnetPDM Manual | CNCnetPDM INI File
This section describes the steps to adjust CNCnetPDM via it's INI file according to your needs. CNCnetPDM.ini contains all information needed to control the functions of the... [read more]
CNCnetPDM INI File
Support | CNCnetPDM Manual | MTConnect Overview
The CNCnetPDM MTConnect Adapter enables you to access your machine controllers directly from within your applications or any MTConnect compatible program like an... [read more]
MTConnect Overview
Support | CNCnetPDM Manual | MTConnect Getting Started
Here you can find everything you need to know to get started with the CNCnetPDM MTConnect adapter. This section shows all necessary steps to setup CNCnetPDM, use the MTConnect... [read more]
MTConnect Getting Started
Support | CNCnetPDM Manual | MTConnect Standard Agent
This document describes setup of the MTConnect Standard Agent it’s usage and test with the CNCnetPDM MTConnect Adapter. Prerequisites As this example uses a simple... [read more]
MTConnect Standard Agent
Support | CNCnetPDM Manual | SQL Server Database Tables
This section describes the steps to setup an MS SQL Server database and required as well as optional tables for CNCnetPDM. Although not absolutely necessary for testing it is... [read more]
SQL Server Database Tables
Support | CNCnetPDM Manual | Oracle Database Tables
This section describes the steps to setup required and optional database tables for CNCnetPDM in case you're using an Oracle database. For an Oracle database the information... [read more]
Oracle Database Tables
Support | CNCnetPDM Manual | Database Connectivity
To connect to a database CNCnetPDM uses a Universal Data Link (.udl) file that contains information about your database. Connect to an MS SQL Server database... [read more]
Database Connectivity
Support | CNCnetPDM Manual | Database Output
For both, MS SQL-Server and Oracle databases CNCnetPDM writes data acquired from devices to table MTH_RAW_DATA and/or MTH_ONLINE_DATA. Both tables have the following columns... [read more]
Database Output
Support | CNCnetPDM Manual | Licensing
When you start the program or service the first time without a license CNCnetPDM automatically creates a license number for your copy. Without a license number initially... [read more]
Licensing
Modified: 2018-07-21