News CNCnetPDM About us Support  
         
  

SQL SERVER DATABASE TABLES | CNCNETPDM MANUAL

This section describes the steps to setup an MS SQL Server database and required as well as optional tables for CNCnetPDM.

  • Note: Starting with CNCnetPDM Ver. 6.3.3.1 the names of all database tables can be configured on a per-service basis, see chapter Connect of CNCnetPDM INI file for details.

Although not absolutely necessary for testing it is recommended to setup database tables and database connectivity before starting CNCnetPDM the first time. Without a database CNCnetPDM writes all collected data into a text file in subfolder \offline of the program directory.

MTH_RAW_DATA

For storing all data collected in every reading cycle from devices CNCnetPDM uses table MTH_RAW_DATA. This is ideal if you want to build reports with historical data from your equipment. You can create it manually by using the following code or use file MTH_RAW_DATA_SQLSERVER.sql from this zip archive. In this description tables in database MDA are used. To use this table please make sure that you have set WriteRawData = 1 in section [Connect] of CNCnetPDM.ini.

/****** Object:  Table [dbo].[MTH_RAW_DATA] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MTH_RAW_DATA]
(
	[GROUP_ID] [float] NULL,
	[READING_TIME] [datetime] NOT NULL,
	[DEVICE_NUMBER] [smallint] NOT NULL,
	[TAG_CODE] [varchar](255) NOT NULL,
	[TAG_DATA] [varchar](255) NULL,
	[CREATION_DATE] [datetime] NULL,
	[PROCESSING_FLAG] [tinyint] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MTH_RAW_DATA] ADD  CONSTRAINT [DF_MTH_RAW_DATA_PROCESSING_FLAG]  DEFAULT ((1)) FOR [PROCESSING_FLAG]
GO
FIG 1:    Setup script (SQL Server) 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_SQLSERVER.sql from this zip archive.

/****** Object:  Table [dbo].[MTH_ONLINE_DATA] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MTH_ONLINE_DATA]
(
	[GROUP_ID] [float] NULL,
	[READING_TIME] [datetime] NOT NULL,
	[DEVICE_NUMBER] [smallint] NOT NULL,
	[TAG_CODE] [varchar](255) NOT NULL,
	[TAG_DATA] [varchar](255) NULL,
	[CREATION_DATE] [datetime] NULL,
	[PROCESSING_FLAG] [tinyint] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MTH_ONLINE_DATA] ADD  CONSTRAINT [DF_MTH_ONLINE_DATA_PROCESSING_FLAG]  DEFAULT ((1)) FOR [PROCESSING_FLAG]
GO
FIG 2:    Setup script (SQL Server) for database table MTH_ONLINE_DATA TOP

MTH_USER_CONTEXT

OPTIONAL: CNCnetPDM is able to use work-order, operation and part-number collected by a separate solution for Operational Data Acquisition. In this case CNCnetPDM looks up this information after every reading cycle and inserts 3 additional records for the current work-order, operation and part-number. If you plan to use this feature you also need table MTH_USER_CONTEXT. You can create it by using the following code or file MTH_USER_CONTEXT_SQLSERVER.sql from this zip archive.

/****** Object:  Table [dbo].[MTH_USER_CONTEXT] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MTH_USER_CONTEXT](
	[USER_CONTEXT_PK_KEY] [numeric](5, 0) IDENTITY(1,1) 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 [PK_MTH_USER_CONTEXT] PRIMARY KEY CLUSTERED 
(
	[USER_CONTEXT_PK_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
FIG 3:    Setup script (SQL SERVER) for database table MTH_USER_CONTEXT TOP

If you’re using an external program for Operational Data Acquisition the table should contain one row for every device with the 4 digit device number in column EQUIPMENT_FK, SCAN_TIME is optional. The solution has to update WORKORDER_FK (work order), SEGMENT_FK (operation) and ITEM_FK (part number) with data about the current activity of the device.

MTH_EQP_SHIFT_AVAIL_V

OPTIONAL: If your devices are switched on all the time but you only want to acquire data within active shifts only you need an additional view named MTH_EQP_SHIFT_AVAIL_V that just returns the 4 digit device number in case there is an active shift for the device at the moment. If nothing is returned CNCnetPDM assumes that there is no active shift. For testing you can also create a table MTH_EQP_SHIFT_AVAIL_V that just contains column EQUIPMENT_FK. To build the table you can use the following code or file MTH_EQP_SHIFT_AVAIL_V_SQLSERVER.sql from this zip archive.

/****** Object:  Table [dbo].[MTH_EQP_SHIFT_AVAIL_V] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MTH_EQP_SHIFT_AVAIL_V](
	[EQUIPMENT_FK] [numeric](5, 0) NOT NULL
) ON [PRIMARY]
GO
FIG 4:    Setup script (SQL SERVER) 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/sql-server-database-tables

Support | CNCnetPDM Manual | Overview

CNCnetPDM Version 7.0.0.1 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 | OPC UA Server

The CNCnetPDM Standard OPC UA Server enables to add OPC UA Server functionality to any controller type (e.g. Fanuc, Mitsubishi, Heidenhain) supported by CNCnetPDM. It dynamically... [read more]
OPC UA Server

Support | CNCnetPDM Manual | MTConnect Overview

The CNCnetPDM MTConnect Adapter enables you to access machine controllers connected to CNCnetPDM 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 1.3

This document describes setup of the MTConnect Standard Agent Version 1.3 it’s usage and test with the CNCnetPDM MTConnect Adapter. CNCNETPDM SETUP | AGENT... [read more]
MTConnect Standard Agent 1.3

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. Note: Starting with CNCnetPDM Ver.... [read more]
Oracle Database Tables

Support | CNCnetPDM Manual | MySQL MariaDB Database Tables

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.... [read more]
MySQL MariaDB 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 RawDataTable (default MTH_RAW_DATA) and/or... [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 key for your copy. Without a purchased license key initially this... [read more]
Licensing
Modified: 2020-05-26