- 1. Example: Create SELECT-query from Orders Table
- 2. Example: Create INSERT-query for Orders Table
- 3. Example: Create UPDATE-query for Orders Table
- 4. Example: Create DELETE-query for Orders Table
- 5. Example: Execute Stored Procedure with input parameter
- 6. Example: Execute Stored Procedure with input and local parameters
- 7. Example: Execute Stored Procedure with input and output parameters
- 8. System variables
- 9. Supported SQL datatypes
1. Example: Create SELECT-query from Orders Table #
1.1 Open Microsoft SQL Server Management Studio and right click on Table Orders, and select path as shown below
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-43.png?media=1717662251)
1.2 This automatically gives you an SQL query that selects all columns and all records in Orders table, see below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-44.png?media=1717662251)
1.3 Click on “Execute” button, and result pane is displayed. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-45-1024x641.png?media=1717662251)
1.4 To get these records downloaded to the PLC, it is necessary to use Column alias. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-46-1024x442.png?media=1717662251)
Note: Notation Bx, Dx, Rx and Sx should be used in SELECT queries
Note: Notation Bx, Dx, Rx and Sx always refers to array element 1 or higher in Record array (DBGW1_DB.Record[1 to n]). Where n=number of records received
Each column datatype specifies whish column alias should be used:
Where n = Record number (1 to n)
Column [ID] datatype is bigint therefore alias Dxx (DINT in PLC) should be used. D0 for DBGW1_DB.Record[n].D[0]
Column [Name] datatype is nvarchar(20) therefore alias Sxx (String in PLC) should be used. S0 for DBGW1_DB.Record[n].S[0]
Column [Description] datatype is nvarchar(30) therefore alias Sxx (String in PLC) should be used. S1 for DBGW1_DB.Record[n].S[1]
Column [Status] datatype is int therefore alias Dxx (DINT in PLC) should be used. D1 for DBGW1_DB.Record[n].D[1]
Column [Released] datatype is bit therefore alias Bxx (Bool in PLC) should be used. B0 for DBGW1_DB.Record[n].B[0]
Column [ProductID] datatype is uniqueidentifier therefore alias Sxx (String in PLC) should be used. S2 for DBGW1_DB.Record[n].S[2]
Column [Amount] datatype is float therefore alias Rxx (REAL in PLC) should be used. R0 for DBGW1_DB.Record[n].R[0]
Column [AmountProduced] datatype is real therefore alias Rxx (REAL in PLC) should be used. R1 for DBGW1_DB.Record[n].R[1]
Column [StartTime] datatype is datetime therefore alias Sxx (String in PLC) should be used. S3 for DBGW1_DB.Record[n].S[3]
Column [DelivaryTime] datatype is datetime therefore alias Sxx (String in PLC) should be used. S4 for DBGW1_DB.Record[n].S[4]
Column [ProductionTime] datatype is real therefore alias Rxx (REAL in PLC) should be used. R2 for DBGW1_DB.Record[n].R[2]
Column [CustomerID] datatype is uniqueidentifier therefore alias Sxx (String in PLC) should be used. S5 for DBGW1_DB.Record[n].S[5]
Column [MachineID] datatype is bigint therefore alias Dxx (DINT in PLC) should be used. D2 for DBGW1_DB.Record[n].D[2]
1.5 Figure below shows the relationship between SQL query result and DBGW Add-On tag array in the PLC
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-48-1024x589.png?media=1717662251)
1.6 When SQL query is working as expected, it can be copied directly into your string tag in the PLC. See chapter 3.4
2. Example: Create INSERT-query for Orders Table #
2.1 Open Microsoft SQL Server Management Studio and right click on Table Orders, and select path as shown below:
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-49.png?media=1717662251)
2.2 This automatically gives you an SQL query that inserts all columns into Orders table, see below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-50.png?media=1717662251)
2.3 To insert a record into Orders table with values from the PLC, it is necessary to use Column alias. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-51.png?media=1717662251)
Note: Notation #Bx#, #Dx#, #Rx# and #Sx# should be used in INSERT and UPDATE queries and WHERE clauses
Note: #Bx#, #Dx#, #Rx# and #Sx# always refers to array element 0 in Record array (DBGW1_DB.Record[0])
Each column datatype specifies whish column alias should be used:
Column [Name] datatype is nvarchar(20) therefore alias #Sxx# (String in PLC) should be used. #S0# for DBGW1_DB.Record[0].S[0]
Column [Description] datatype is nvarchar(30) therefore alias #Sxx# (String in PLC) should be used. #S1# for DBGW1_DB.Record[0].S[1]
Column [Status] datatype is int therefore alias #Dxx# (DINT in PLC) should be used. #D1# for DBGW1_DB.Record[0].D[1]
Column [Released] datatype is bit therefore alias #Bxx# (Bool in PLC) should be used. #B0# for DBGW1_DB.Record[0].B[0]
Column [ProductID] datatype is uniqueidentifier therefore alias #Sxx# (String in PLC) should be used. #S2# for DBGW1_DB.Record[0].S[2]
Column [Amount] datatype is float therefore alias #Rxx# (REAL in PLC) should be used. #R0# for DBGW1_DB.Record[0].R[0]
Column [AmountProduced] datatype is real therefore alias #Rxx# (REAL in PLC) should be used. #R1# for DBGW1_DB.Record[0].R[1]
Column [StartTime] datatype is datetime therefore alias #Sxx# (String in PLC) should be used. #S3# for DBGW1_DB.Record[0].S[3]
Column [DelivaryTime] datatype is datetime therefore alias #Sxx# (String in PLC) should be used. #S4# for DBGW1_DB.Record[0].S[4]
Column [ProductionTime] datatype is real therefore alias #Rxx# (REAL in PLC) should be used. #R2# for DBGW1_DB.Record[0].R[2]
Column [CustomerID] datatype is uniqueidentifier therefore alias #Sxx# (String in PLC) should be used. #S5# for DBGW1_DB.Record[0].S[5]
Column [MachineID] datatype is bigint therefore alias #Dxx# (DINT in PLC) should be used. #D2# for DBGW1_DB.Record[0].D[2]
2.4 Figure below shows the relationship between SQL query and DBGW Add-On tag array in the PLC
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-53-1024x696.png?media=1717662251)
2.5 When SQL query is working as expected, it can be copied directly into your string tag in the PLC. See chapter 3.4
3. Example: Create UPDATE-query for Orders Table #
3.1 Open Microsoft SQL Server Management Studio and right click on Table Orders, and select path as shown below:
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-54.png?media=1717662251)
3.2 This automatically gives you an SQL query that updates all columns into Orders table, see below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-55.png?media=1717662251)
3.3 To update a specific record in Orders table, it is necessary to use Column alias. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-56.png?media=1717662251)
Note: Notation #Bx#, #Dx#, #Rx# and #Sx# should be used in INSERT and UPDATE queries and WHERE clauses
Note: #Bx#, #Dx#, #Rx# and #Sx# always refers to array element 0 in Record array (DBGW1_DB.Record[0])
Each column datatype specifies whish column alias should be used:
Column [Status] datatype is int therefore alias #Dxx# (DINT in PLC) should be used. #D0# for DBGW1_DB.Record[0].D[0]
Column [AmountProduced] datatype is real therefore alias #Rxx# (REAL in PLC) should be used. #R0# for DBGW1_DB.Record[0].R[0]
Column [ProductionTime] datatype is real therefore alias #Rxx# (REAL in PLC) should be used. #R1# for DBGW1_DB.Record[0].R[1]
Column [Name] datatype is nvarchar(20) therefore alias #Sxx# (String in PLC) should be used. #S0# for DBGW1_DB.Record[0].S[0]
3.4 Figure below shows the relationship between SQL query and DBGW Add-On tag array in the PLC
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-58-1024x551.png?media=1717662251)
3.5 When SQL query is working as expected, it can be copied directly into your string tag in the PLC.
4. Example: Create DELETE-query for Orders Table #
4.1 Open Microsoft SQL Server Management Studio and right click on Table Orders, and select path as shown below:
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-59.png?media=1717662251)
4.2 This automatically gives you an SQL query that deletes all columns in Orders table, see below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-60.png?media=1717662251)
4.3 To delete a specific record in Orders table, it is necessary to use Column alias. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-61.png?media=1717662251)
Note: Notation #Bx#, #Dx#, #Rx# and #Sx# should be used in INSERT and UPDATE queries and WHERE clauses
Note: #Bx#, #Dx#, #Rx# and #Sx# always refers to array element 0 in Record array (DBGW1_DB.Record[0])
Each column datatype specifies whish column alias should be used:
Column [Name] datatype is nvarchar(20) therefore alias #Sxx# (String in PLC) should be used. #S0# for DBGW1_DB.Record[0].S[0]
4.4 Figure below shows the relationship between SQL query and DBGW Add-On tag array in the PLC
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-62.png?media=1717662251)
4.5 When SQL query is working as expected, it can be copied directly into your string tag in the PLC.
5. Example: Execute Stored Procedure with input parameter #
5.1 Open Microsoft SQL Server Management Studio and open Stored Procedure as shown below:
Stored Procedure with 1 input “@MachineID”
PS! Multiple records can be received
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-63.png?media=1717662251)
5.2 To get executable string for this Stored Procedure, right click on “usr_SelectOrders1” and select “Execute Stored Procedure”
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-64.png?media=1717662251)
Enter a random value into this field and click on “OK”
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-66.png?media=1717662251)
That gives the following query:
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-67.png?media=1717662251)
Note: It is not possible to get records from the Stored Procedure and a separat “Return value”. Therefore “@return_Value” should be removed
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-68-1024x314.png?media=1717662251)
5.3 To execute Stored Procedure with input parameter from the PLC, it is necessary to use Column alias. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-70.png?media=1717662251)
Note: Notation #Bx#, #Dx#, #Rx# and #Sx# should be used for input parameters for a Stored Procedure
Note: #Bx#, #Dx#, #Rx# and #Sx# always refers to array element 0 in Record array (DBGW1_DB.Record[0])
Input parameter:
Column [ID] datatype is bigint therefore alias #Dxx# (DINT in PLC) should be used. #D0# for DBGW1_DB.Record[0].D[0]
Note: Column alias for outputs are assigned inside the Stored Procedure, see Stored Procedure above
5.4 Figure below shows the relationship between SQL query and DBGW Add-On tag array in the PLC
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-71.png?media=1717662251)
5.5 When SQL query is working as expected, it can be copied directly into your string tag in the PLC.
6. Example: Execute Stored Procedure with input and local parameters #
6.1 Open Microsoft SQL Server Management Studio and open Stored Procedure as shown below:
Stored Procedure with 1 input “@MachineID”
PS! Only 1 record will be received
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-72.png?media=1717662251)
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-73.png?media=1717662251)
6.2 To get executable string for this Stored Procedure, right click on “usr_SelectOrders2” and select “Execute Stored Procedure”
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-75.png?media=1717662251)
Enter a random value into this field and click on “OK”
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-76.png?media=1717662251)
That gives the following query:
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-77.png?media=1717662251)
Note: It is not possible to get record(s) from the Stored Procedure and a separat “Return value”. Therefore “@return_value” should be removed
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-79-1024x212.png?media=1717662251)
6.3 To execute Stored Procedure with input parameters from the PLC, it is necessary to use Column alias. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-80.png?media=1717662251)
Note: Notation #Bx#, #Dx#, #Rx# and #Sx# should be used for input parameters for a Stored Procedure
Note: #Bx#, #Dx#, #Rx# and #Sx# always refers to array element 0 in Record array (DBGW1_DB.Record[0])
Input parameter:
Column [ID] datatype is bigint therefore alias #Dxx# (DINT in PLC) should be used. #D0# for DBGW1_DB.Record[0].D[0]
Note: Column alias for outputs are assigned inside the Stored Procedure, see Stored Procedure above
6.4 Figure below shows the relationship between SQL query and DBGW Add-On tag array in the PLC
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-81.png?media=1717662251)
6.5 When SQL query is working as expected, it can be copied directly into your string tag in the PLC.
7. Example: Execute Stored Procedure with input and output parameters #
7.1 Open Microsoft SQL Server Management Studio and open Stored Procedure, see below:
Stored Procedure with 1 input and 13 output parameters
PS! Only 1 record will be received
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-82.png?media=1717662251)
7.2 To get executable string for this Stored Procedure, right click on “usr_SelectOrders3” and select “Execute Stored Procedure”
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-83.png?media=1717662251)
Enter a random value into this field and click on “OK”
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-85.png?media=1717662251)
That gives the following query:
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-89.png?media=1717662251)
Note: It is not possible to get outputs from the Stored Procedure and a separat “Return value”. Therefore “@return_value” should be removed
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-90.png?media=1717662251)
7.3 To execute Stored Procedure with input & output parameters to/from the PLC, it is necessary to use Column alias. See below.
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-92.png?media=1717662251)
Note: Notation #Bx#, #Dx#, #Rx# and #Sx# should be used for input parameters for a Stored Procedure
Note: #Bx#, #Dx#, #Rx# and #Sx# always refers to array element 0 in Record array (DBGW1_DB.Record[0])
Note: Notation Bx, Dx, Rx and Sx should be used for output parameters for a Stored Procedure
Note: Bx, Dx, Rx and Sx always refers to array element 1 or higher in Record array (DBGW1_DB.Record[1 to n]). Where n = Number of records received
Input parameter:
Column [ID] datatype is bigint therefore alias #Dxx# (DINT in PLC) should be used. #D0# for DBGW1_DB.Record[0].D[0]
Output parameters:
Column [ID] datatype is bigint therefore alias Dxx (DINT in PLC) should be used. D0 for DBGW1_DB.Record[1].D[0]
Column [Name] datatype is nvarchar(20) therefore alias Sxx (String in PLC) should be used. S0 for DBGW1_DB.Record[1].S[0]
Column [Description] datatype is nvarchar(30) therefore alias Sxx (String in PLC) should be used. S1 for DBGW1_DB.Record[1].S[1]
Column [Status] datatype is int therefore alias Dxx (DINT in PLC) should be used. D1 for DBGW1_DB.Record[1].D[1]
Column [Released] datatype is bit therefore alias Bxx (Bool in PLC) should be used. B0 for DBGW1_DB.Record[1].B[0]
Column [ProductID] datatype is uniqueidentifier therefore alias Sxx (String in PLC) should be used. S2 for DBGW1_DB.Record[1].S[2]
Column [Amount] datatype is float therefore alias Rxx (REAL in PLC) should be used. R0 for DBGW1_DB.Record[1].R[0]
Column [AmountProduced] datatype is real therefore alias Rxx (REAL in PLC) should be used. R1 for DBGW1_DB.Record[1].R[1]
Column [StartTime] datatype is datetime therefore alias Sxx (String in PLC) should be used. S3 for DBGW1_DB.Record[1].S[3]
Column [DelivaryTime] datatype is datetime therefore alias Sxx (String in PLC) should be used. S4 for DBGW1_DB.Record[1].S[4]
Column [ProductionTime] datatype is real therefore alias Rxx (REAL in PLC) should be used. R2 for DBGW1_DB.Record[1].R[2]
Column [CustomerID] datatype is uniqueidentifier therefore alias Sxx (String in PLC) should be used. S5 for DBGW1_DB.Record[1].S[5]
Column [MachineID] datatype is bigint therefore alias Dxx (DINT in PLC) should be used. D2 for DBGW1_DB.Record[1].D[2]
7.4 Figure below shows the relationship between SQL query result and DBGW Add-On tag array in the PLC
![](https://usercontent.one/wp/www.systemintegration.dk/wp-content/uploads/2020/12/image-94-1024x792.png?media=1717662251)
7.5 When SQL query is working as expected, it can be copied directly into your string tag in the PLC.
8. System variables #
8.1 Used in queries to make it easier to deal with datetime issues and more.
System variable | Description |
---|---|
@TIMESYNC@ | Used for timesynchronization of PLC clock with SQL-Server time |
@SN@ | Serial number: Will be replaced by PLC’s unique serial number |
@TS@ | Timestamp: Will be replaced by actual datetime on SQL-Server during insert & update |
@TD@ | This Day: Will be replaced by actual date |
@TW@ | This Week: Will be replaced by actual start date for this week |
@TM@ | This Month: Will be replaced by actual start date for this month |
@TY@ | This Year: Will be replaced by actual start date for this year |
@LxxH@ | Last xx Hours: Will be replaced by start datetime xx hours back in time |
@LxxD@ | Last xx Days: Will be replaced by start datetime xx days back in time |
@LxxM@ | Last xx Month: Will be replaced by start datetime xx months back in time |
8.2 Examples
Query for timesynchronization of PLC clock with SQL-Server time
SELECT @TIMESYNC@
Query for using PLC Serial number in Where clause:
SELECT [ID], [Name], [Description], [Status], [Released], [ProductID], [Amount], [AmountProduced], [StartTime], [DelivaryTime], [ProductionTime], [CustomerID]
FROM [DBGW_Test].[dbo].[Orders]
WHERE [MachineID]=@SN@
Query for using this month in Where clause:
SELECT [ID], [Name], [Description], [Status], [Released], [ProductID], [Amount], [AmountProduced], [StartTime], [DelivaryTime], [ProductionTime], [CustomerID]
FROM [DBGW_Test].[dbo].[Orders]
WHERE [MachineID]=@SN@ AND [DelivaryTime]>=@TM@
Query for using last 24 hours in Where clause:
SELECT [ID], [Name], [Description], [Status], [Released], [ProductID], [Amount], [AmountProduced], [StartTime], [DelivaryTime], [ProductionTime], [CustomerID]
FROM [DBGW_Test].[dbo].[Orders]
WHERE [DelivaryTime]>=@L24H@
Query for using last year in Where clause:
SELECT [ID], [Name], [Description], [Status], [Released], [ProductID], [Amount], [AmountProduced], [StartTime], [DelivaryTime], [ProductionTime], [CustomerID]
FROM [DBGW_Test].[dbo].[Orders]
WHERE [DelivaryTime]>=@L12M@
9. Supported SQL datatypes #
9.1 Figure below shows which SQL datatypes that are supported, and the correspondant PLC datatype
SQL Datatype | PLC datatype |
---|---|
Varchar | String |
NVarchar (PLC only supports None Unicode (1 byte characters), Unicode are not supported (2 bytes for special characters) | String |
Char | String |
NChar (PLC only supports None Unicode (1 byte characters), Unicode are not supported (2 bytes for special characters) | String |
Datetime | String |
Uniqueidentifier | String |
TinyInt | DINT |
SmallInt | DINT |
Integer | DINT |
BigInt (PLC only supports from -2147483648 to 2147483647) | DINT |
Float (PLC only supports Single-precision floating-point format, 4 byte) | REAL |
Real (PLC only supports Single-precision floating-point format, 4 byte) | REAL |
Decimal (PLC only supports Single-precision floating-point format, 4 byte) | REAL |
Numeric (PLC only supports Single-precision floating-point format, 4 byte) | REAL |
Money | REAL |
Bit | BOOL |