SQL Database Programming for WinCC SCADA Engineers

Name: SQL Database Programming for WinCC SCADA Engineers
Rating: 4.5 (88 reviews)

Leverage SQL programming, VBScript and WinCC to create powerful database-driven SCADA applications.

Created byBob Meads

Last updated 8/2024

English

What you'll learn

Install and Use Microsoft SQL Server Management Studio
Create & Manage SQL Server Databases, Tables and Data
Create Database Diagrams and Views with SQL Server Management Studio
Use Structured Query Language (SQL) to Query Database Tables
Use SQL to Insert, Update and Delete Data in SQL Server databases
Write VBScripts in WinCC that leverage SQL Programming for your SCADA application
Build WinCC Screens that interact with SQL Server Databases
Learn to use SQL Aggregate Functions, JOINS and Sub Queries
Learn to leverage SQL Queries in generating reports in WinCC Reports Designer

Course content

7 sections • 38 lectures • 6h 31m total length

Course Introduction0:37
An overview of this course!
Download & Install WinCC SCADA + SQL Server3:19
If you already have WinCC version 7.4 or higher installed, you can skip this step! If not, I will show you where you can download a trial copy of WinCC Version 8 from Siemens which will run for 21 days. After that, you will need a licensed version, so be sure you have time to complete the course before that time is up!
Download & Install Microsoft SQL Server Management Studio2:01
Here I will show you how to download &install Microsoft SQL Server Management Studio (SSMS) from Microsoft. SSMS is a free product used to create and manage SQL Server databases and allows you to write SQL queries. We will use SSMS for the duration of this course. If you already have SSMS Installed, you can also skip this step.
Import the BatteryCo Course Database2:00
Once we have WinCC and SSMS Installed, we will create the course database we will use for this course.
Import The BatteryCo Course Database
Write Your First SQL Query!1:01
So, now that our programming environment is set up, it's time to write our first SQL Query!
Write Your First SQL Query

Section 2 Overview0:42
In this section we are going to learn to use SQL Server Management Studio to create, analyze and manage databases. We will learn what a relational database is and get an overview of the Structured Query Language (SQL) features. We will create a diagram of an existing database, and learn about tables, relationships and data.
Then I will introduce to you to the Production Defect Tracking Project, which are requirements for a database enabled WinCC Scada application that allows production staff to enter, repair and manage production defects.
You will begin creating and populating a new SQL database for our defect tracking project based on these requirements.
What is a Relational Database?7:21
We will review the BatteryCo database tables, data and relationships. We will discuss T-SQL Data Types, Primary and Foreign Keys.
Relational Database
Create A Database Diagram and View
What Is Structured Query Language (SQL) and Transact SQL (T-SQL)?4:12
What is SQL and T-SQL
The Production Defect Tracking Project3:31
In this section we will introduce the Production Downtime Tracking project you will be working on as you learn about relational databases, SQL programming and VBScripting.
Creating the Database, Tables and relationships9:12
Create remaining entity tables
Insert Data using SSMS and SQL9:04
Add data to tables using SSMS UI and SQL

Section Overview0:30
Now that we have built a relational database and populated some data, it's time to learn more about SQL Programming. In this section we will dive a little deeper into database programming and learn how to write queries with WHERE clauses, filters and ordering of data. We will also cover more on inserting data, as well as using update and delete commands with filters.
The SQL Select Statement7:34
In this lecture, we will learn more about using the SQL SELECT statement to query data in a table. Includes Select *, Select [Columnnames], use of Aliases for column names, Select Into and Select Distinct. We will also cover the standard code color schemes used by SQL Server and other development environments to identify comments, keywords, strings and syntax.
Basic Select Statements
Limiting and ordering query results using Where and Order By8:40
In this lecture, we will extend the usefulness of Select queries with the Where and Order By Clauses. We will look at how to filter data results, and order the output by one or more fields. We will use boolean operators to combine search conditions and exclude records. Relational operators are used in search conditions to refine desired results.
Using Where and Order By
Using math operators and Cast with Select Queries5:40
In this lecture, we will further expand our Select statements by formatting and transforming output column values using SQL math operators and the Cast function.
Using Math Operators and Cast to transform values in Select statements
Using In Between and Like Clauses6:00
In this lecture, we will use the IN, BETWEEN and LIKE clauses to further refine our search patterns.
Using IN, BETWEEN and LIKE clauses
Inserting, Updating and Deleting Table Data21:37
SQL Insert, Update and Delete

Section overview0:50
In this section, we will build out our defect tracking WinCC project. I will show you how to use the WInCC WebBrowser control and Microsoft Hierarchical Data Grid control as alternate ways to visualize database tables and views. We will build the user interface for our defect tracking application which will allow us to view current defects and allow operator repair personnel and supervisors to enter and update data directly from WinCC screens. There will also be a view for our error log.
Create a WinCC Project3:08
Create a new WinCC project
Your First WinCC VBScript using SQL with ADO9:19
In this lecture, we will go review a simpleVBS Script which uses ActiveX Data Objects to connect to our BatteryCo database, executes a query and prints out results to a debug window.
My First ADO Script
Configuring the ADO Connection Object12:02
Here we explore different ways to create connections to databases with the ADO Connection Object
Creating ADO Connection Objects
Handing Errors with VBScript10:03
Working with the ERR Object for handling script errors
Using the ADO Command Object13:18
Using the ADO Command Object
Create VBScript Functions for Data Entry, Table Management and Visualization.28:00
In this lecture we will cover some useful VBScript functions to allow visualization of SQL Queries using the WinCC Web Browser Control. We will also create the execSQL() function to execute a given SQL query and return success or fail, and an error logging function to aid in troubleshooting issues. We will begin creating DB Interface screens for our Defect Tracking project.
Create VBScript Functions for Data Entry, Table Managment, and Visualization

Section Overview0:32
Using the WinCC WebBrowser and MSHFLexGrid Control to Visualize Data14:51
If your version of WinCC doesn't have the Chromium Browser control, don't worry! In this video I will show you two more options to visualize database data: the WinCC WebBrowser control, as well as the Microsoft MSHFlexGrid control that comes with Microsoft Office.
Re-create the Staff Management screen using MSHFlexGrid control
Build the Defect Tracking Overview Screen And Interfaces Part 121:29
In this section we will begin creating the user interface screens for our defect tracking application.
Build the Defect Tracking Interfaces, Part 1
Build the Defect Tracking Screens and Operator Interfaces, Part 230:52
Update the Defect Tracking View8:42
Update the Defect Tracking View with repair and supervisor information
Build the Defect Tracking Interfaces, Part 2
Using The ADO Errors Collection for more informative error handling9:10
Use the ADO Errors Collection for more informative error handling
Next Topics...0:52
Challenges

Section Overview0:39
In this short video we review what will be covered in Section 6.
SQL Aggregate Functions13:45
SQL Aggregate functions use calculations to resolve values in a dataset into a single representative value. For example, the Sum() Aggregate function returns to mathematical sum of a set of numerical values.
Using SQL Aggregate Functions
The SQL JOIN Statement18:31
In this section, you will learn to write different types of SQL Join statements to take advantage of the relationships between tables in order to generate meaningful results from multiple tables.
Using the SQL JOIN Statement
SQL Sub Queries7:59
SQL Sub queries allow you to embed a SQL query inside of another query. As you will see, it is a very powerful tool to get the results you need.
Using SQL Sub Queries

Section overview0:29
Using SQL in WinCC Reports Designer16:46
WinCC Reports Designer, also referred to as the Layout Editor, allows the use of SQL to embed data from databases in your report. In this section we will create a report for Production Orders, which can use tags to define the time range of a given report. We will also create a WinCC Screen to allow the user to enter different time ranges and preview or print reports.
Create a Report Layout using the SQL Database Table and Field objects.
Create a WinCC Screen to generate and preview reports7:59
In this lecture, we will build a WinCC Screen to allow the runtime user to generate and preview reports we built in the previous lecture. Students will be able to modify the parameters of the SQL Query used in the Reports Layout Editor using internal tags. The resources for this lecture also contain the final WinCC project created during the course for reference.
Create a WinCC Screen to preview and print Reports

Requirements

A PC with WinCC version 7.4 or higher installed.
No prior WinCC, SQL or VBS experience required; I will teach you what you need to know.

Description

This comprehensive beginner’s course for WinCC SCADA engineers will put you on the path to creating and deploying database enabled SCADA applications! SQL and related database technology are a vital part of any manufacturing or process plant floor; SCADA engineers with expertise in leveraging database technology for their industrial software applications are always in demand. As a 30-year veteran of successfully building industrial software applications, I can tell you that fully half of all SCADA applications I have deployed use the skills and techniques you will learn in this course. You will learn how to leverage Microsoft SQL Server, the SQL programming language, Visual Basic Scripting (VBScript) and ActiveX Data Objects (ADO) to create powerful database-driven WinCC SCADA applications. You’ll learn to create and manage databases, tables, views and queries in Microsoft SQL Server Management Studio. Master the basics of the Transact SQL (T-SQL) Programming language, writing simple and complex SQL queries. Embed SQL Queries in WinCC's Visual Basic Scripting Language using ADO to add, manage and delete data in Microsoft SQL Server. Create WinCC screens and dialogs that interact with and display data from SQL Server databases. You will learn to generate reports using SQL in the WInCC Layout Editor. As part of this course, you will also receive reusable VBS code functions and examples that you can drop into any WinCC SCADA application to quickly build scripts and screens that can interact with your production databases; these alone are more than worth the price of this course! After successfully completing this course, you will have the skills to leverage the power of SQL programming, VB Script and WinCC for your next SCADA Application.

Who this course is for:

Industrial application engineers interested in leveraging the power of SQL Programming and databases into their WinCC SCADA Applications

SQL Database Programming for WinCC SCADA Engineers

What you'll learn

Explore related topics

Course content

Prepare Your SQL & WinCC Programming Environment5 lectures • 9min

Create & Manage SQL Databases6 lectures • 34min

Basic SQL Programming 1: SQL Queries6 lectures • 50min

Executing SQL from WinCC VBScripts7 lectures • 1hr 17min

Building Database Enabled WinCC Screens7 lectures • 1hr 26min

Basic SQL Programming 2: Using Aggregates, Joins and Sub Queries4 lectures • 41min

Using SQL to Generate Reports in WinCC3 lectures • 25min

Requirements

Description

Who this course is for: