3.3 Create SQL queries

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

1.2 This automatically gives you an SQL query that selects all columns and all records in Orders table, see below.

1.3 Click on “Execute” button, and result pane is displayed. See below.

1.4 To get these records downloaded to the PLC, it is necessary to use Column alias. See below.

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

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:

2.2 This automatically gives you an SQL query that inserts all columns into Orders table, see below.

2.3 To insert a record into Orders table with values from the PLC, it is necessary to use Column alias. See below.

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

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:

3.2 This automatically gives you an SQL query that updates all columns into Orders table, see below.

3.3 To update a specific record in Orders table, it is necessary to use Column alias. See below.

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

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:

4.2 This automatically gives you an SQL query that deletes all columns in Orders table, see below.

4.3 To delete a specific record in Orders table, it is necessary to use Column alias. See below.

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

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

5.2 To get executable string for this Stored Procedure, right click on “usr_SelectOrders1” and select “Execute Stored Procedure”

Enter a random value into this field and click on “OK”

That gives the following query:

Note: It is not possible to get records from the Stored Procedure and a separat “Return value”. Therefore “@return_Value” should be removed

5.3 To execute Stored Procedure with input parameter from the PLC, it is necessary to use Column alias. See below.

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

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

6.2 To get executable string for this Stored Procedure, right click on “usr_SelectOrders2” and select “Execute Stored Procedure”

Enter a random value into this field and click on “OK”

That gives the following query:

Note: It is not possible to get record(s) from the Stored Procedure and a separat “Return value”. Therefore “@return_value” should be removed

6.3 To execute Stored Procedure with input parameters from the PLC, it is necessary to use Column alias. See below.

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

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

7.2 To get executable string for this Stored Procedure, right click on “usr_SelectOrders3” and select “Execute Stored Procedure”

Enter a random value into this field and click on “OK”

That gives the following query:

Note: It is not possible to get outputs from the Stored Procedure and a separat “Return value”. Therefore “@return_value” should be removed

7.3 To execute Stored Procedure with input & output parameters to/from the PLC, it is necessary to use Column alias. See below.

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

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