Skip to main content
InSource Solutions

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

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.

  • Was this article helpful?