Application Server scripts that INSERT, UPDATE & SELECT in a SQL database
Overview
The following scripts show you how to use ADO scripting do the following transactions to/from a database in Industrial Application Server:
- Construct a connection string hosted in a single object
- An INSERT of data into a SQL database
- An UPDATE of an existing record in a SQL database by providing filter criteria
- A SELECT that retrieves a single record of data from a database by providing filter criteria
Resolution
Template Object - $DBObjectAppEngine
Required UDA
|
ConnectStr |
String (user writeable) |
Script Configuration
|
Script Name: |
"DBInitialize" |
|
Execute Type: |
"OnScan" (locked) |
Declarations (locked)
Dim DBServer AS String;
Dim DBName AS String;
Dim DBUser AS String;
Dim DBPwd AS String;
Script
' This script is used to set up the database connection string.
' The default SQL connection string has the following format:
' Server=W2003SVR;Database=Runtime;UID=wwAdmin;PWD=wwadmin;
DBServer = "W2003SVR";
DBName = "Runtime";
DBUser = "wwAdmin";
DBPwd = "mypwd";
Me.ConnectStr = "Server=" + DBServer + ";Database=" + DBName + ";UID=" + DBUser + ";PWD=" + DBPwd + ";";
Template Object - $BatchObject
Required UDAs
|
BatchID |
Integer |
|
BatchTrigger |
Boolean (could be Field Attribute from IO) |
|
MachineID |
String |
|
SQLErrorMsg |
String |
Script Configuration
|
Script Name: |
"BuildBatch" |
|
Execute Type: |
"Execute" |
|
Expression: |
"Me.BatchTrigger == 1" |
|
Trigger Type: |
"OnTrue" |
|
Trigger Period: |
"00:00:00.0000000" |
Declarations
Dim dt AS System.Datetime;
Dim SQLConn AS System.Data.SqlClient.SqlConnection;
Dim SQLCmd AS System.Data.SqlClient.SqlCommand;
Dim SQLRdr AS System.Data.SqlClient.SqlDataReader;
Script to INSERT data
Dim sQuery as string;
Dim iRC as integer;
Dim sTrigDate as string;
dt = Now();
sTrigDate = (dt.ToString("MM/dd/yyyy HH:mm:ss"));
Logmessage ("*** Script BuildBatch: Executing for " + Me.Tagname + " at " + sTrigDate + " ***");
iRC = -1;
SQLConn = new System.Data.SqlClient.SqlConnection(MyEngine.ConnectStr);
SQLConn.Open();
sQuery = "INSERT INTO [Batch] ";
sQuery = sQuery + " ([BatchID], [MachineID], [StartDate]) ";
sQuery = sQuery + " VALUES ('" + Me.BatchID + "','" + Me.MachineID + "','" + sTrigDate +"')";
SQLCmd = New System.Data.SqlClient.SqlCommand(sQuery, SQLConn);
iRC = SQLCmd.ExecuteNonQuery( );
SQLConn.Close() ;
If iRC <> 1 Then
Logmessage ("*** Insert Failed *** RC = " + Text(iRC, "#") + " -:- BatchID = " + Me.BatchID );
Me.SQLErrorMsg = "Insert Failed -:- RC = " + Text(iRC, "#") + " -:- BatchID = " + Me.BatchID ;
Else
Me.SQLErrorMsg = "Insert Success";
EndIf;
Required UDAs
|
BatchID |
Integer |
|
PrevBatchID |
Integer |
|
BatchTrigger |
Boolean (could be Field Attribute from IO) |
|
MachineID |
String |
|
EndDate |
DateTime |
|
WasteAmt |
Float |
|
SQLErrorMsg |
String |
Script Configuration
|
Script Name: |
"UpdateBatch" |
|
Execute Type: |
"Execute" |
|
Expression: |
"Me.BatchTrigger == 1" |
|
Trigger Type: |
"OnFalse" |
|
Trigger Period: |
"00:00:00.0000000" |
Declarations
Dim dt AS System.Datetime;
Dim SQLConn AS System.Data.SqlClient.SqlConnection;
Dim SQLCmd AS System.Data.SqlClient.SqlCommand;
Dim SQLRdr AS System.Data.SqlClient.SqlDataReader;
Script to UPDATE a data record
Dim sQuery as string;
Dim iRC as integer;
Dim sWasteAmt as string;
Dim sTrigDate as string;
dt = Now();
sTrigDate = (dt.ToString("MM/dd/yyyy HH:mm:ss"));
Logmessage ("*** Script UpdateBatch: Executing for " + Me.Tagname + " at " + sTrigDate + " ***");
iRC = -1;
SQLConn = new System.Data.SqlClient.SqlConnection(MyEngine.ConnectStr);
SQLConn.Open();
sWasteAmt = Text(Me.WasteAmt, "#.00");
sQuery = "UPDATE [Batch] SET [EndDate] = '" + sTrigDate + "'";
sQuery = sQuery + ", [WasteAmt] = " + sWasteAmt ;
sQuery = sQuery + " WHERE [BatchID] = '" + Me.PrevBatchID + "'";
sQuery = sQuery + " AND [MachineID] = '" + Me.MachineID + "'";
sQuery = sQuery + " AND [EndDate] IS NULL";
SQLCmd = New System.Data.SqlClient.SqlCommand(sQuery, SQLConn);
iRC = SQLCmd.ExecuteNonQuery( );
SQLConn.Close() ;
If iRC <> 1 Then
Logmessage ("*** Update Failed *** RC = " + Text(iRC, "#") + " -:- BatchID = " + Me.PrevBatchID );
Me.SQLErrorMsg = "Update Failed -:- RC = " + Text(iRC, "#") + " -:- BatchID = " + Me.PrevBatchID ;
Else
Me.SQLErrorMsg = "Update Success";
EndIf;
Required UDA
|
BatchID |
Integer |
|
PrevBatchID |
Integer |
|
QueryBatch |
Boolean (could be Field Attribute from IO) |
|
MachineID |
String |
|
StartDate |
DateTime |
|
EndDate |
DateTime |
|
WasteAmt |
Float |
|
SQLErrorMsg |
String |
Script Configuration
|
Script Name: |
"SelectBatch" |
|
Execute Type: |
"Execute" |
|
Expression: |
"Me.QueryBatch == 1" |
|
Trigger Type: |
"OnTrue" |
|
Trigger Period: |
"00:00:00.0000000" |
Declarations
Dim dt AS System.Datetime;
Dim SQLConn AS System.Data.SqlClient.SqlConnection;
Dim SQLCmd AS System.Data.SqlClient.SqlCommand;
Dim SQLRdr AS System.Data.SqlClient.SqlDataReader;
Script to SELECT a data record
Me.QueryBatch = 0;
Dim sQuery as string;
Dim iRC as integer;
Dim sWasteAmt as string;
Dim sTrigDate as string;
dt = Now();
sTrigDate = (dt.ToString("MM/dd/yyyy HH:mm:ss"));
Logmessage ("*** Script SelectBatch: Executing for " + Me.Tagname + " at " + sTrigDate + " ***");
iRC = -1;
SQLConn = new System.Data.SqlClient.SqlConnection(MyEngine.ConnectStr);
SQLConn.Open();
sQuery = "SELECT [BatchID], [MachineID], [StartDate], [EndDate], [WasteAmt] ";
sQuery = sQuery + "FROM [Batch] WHERE [BatchID] = " + Me.PrevBatchID ;
SQLCmd = New System.Data.SqlClient.SqlCommand(sQuery, SQLConn);
''select the required fields for a Batch Id lookup
SQLRdr = SQLCmd.ExecuteReader();
''loads Batch header into IAS memory
While SQLRdr.Read()
me.BatchID = SQLRdr("BatchID");
me.MachineID = SQLRdr("MachineID");
me.StartDate = SQLRdr("StartDate");
me.EndDate = SQLRdr("EndDate");
me.WasteAmt = SQLRdr("WasteAmt");
Endwhile;
SQLRdr.Close();
SQLConn.Close() ;
J.B.T.