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:

1. Construct a connection string hosted in a single object
2. An INSERT of data into a SQL database
3. An UPDATE of an existing record in a SQL database by providing filter criteria
4. A SELECT that retrieves a single record of data from a database by providing filter criteria

Resolution

Template Object - $DBObjectAppEngine

Required UDA

Script Configuration

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

Script Configuration

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

Script Configuration

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

Script Configuration

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() ;