Emerson Exchange 365

• Not Answered

MS SQL database accessed from DeltaV Operate

In our running project, we need to create a new database in MS SQL server on DeltaV station and store here values calculated in DeltaV Operate VB scripts.

Do you have experience with similar something like this?

Is there any restriction/limitation in access from DeltaV Operate to SQL database? (user roles, large of new database)?

Would we expect any issue during DeltaV upgrade?

Vlastimil Lahoda

• 22 May 2018 9:21 AM
• Cancel

6 Replies

• Casey Houchens

  

  • 14 Aug 2018 5:19 PM

  What do I need to do in order to write information from an operator data entry field in Operator to a SQL database?
  Do you have any examples of the script required in Operate?

  • Cancel
• RobPerry

  

  • 14 Aug 2018 6:07 PM

  In reply to Casey Houchens:

  You can use the SQL command "INSERT_INTO table (field_names) VALUES (field_values)" in an Operate script.
  
  To start add a reference in your script to ADO to use SQL commands, for example to 'C:\Program Files (x86)\Common Files\System\ado\msado15.dll'.
  
  Assemble an SQL command as a string, for example but replacing "Journal" with the name of your table and put in the names of your fields.
  sSQLstatement = "INSERT INTO Journal (Date_Time, FracSec, Event_TypeCategory, Area, Node, Unit, Module, Module_Description, Attribute, Desc1, Desc2)"
  sSQLstatement = sSQLstatement & "VALUES ('8/14/2018 10:04:23 AM', 0, 'CHANGE', 'USER', 'AREA_A', 'OWS-001', 'UNIT_001', '100-PP-123', 'Acid Pump 01', 'PARAM1', 'ENGINEER', 'Event description')"
  
  Connect to the SQL database and run the command, replacing PROPLUS01\DELTAV_CHRONICLE with the location and name of your database:
  
  'Connect to Event Journal database on the ProPlus
  Set conn = New ADODB.Connection
  conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" & _
  "Data Source=PROPLUS01\DELTAV_CHRONICLE;" & _
  "Initial Catalog = EJournal;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
  "Workstation ID=" & frsvariables.gs_wsName.CurrentValue & ";" & _
  "Use Encryption for Data=False;Tag with column collation when possible=False;QuotedID=No"
  
  conn.Open 'Open connection to Event Chronicle
  conn.Execute sSQLstatement 'Run SQL command
  conn.Close 'Close connection to Event Chronicle
  Set conn = Nothing
  
  If you write to the Event Chronicle, don't forget it uses UTC time internally.
  
  Hope this helps.

  • Cancel
• RobPerry

  

  • 14 Aug 2018 8:00 PM

  In reply to Casey Houchens:

  Casey,
  We have done something very similar to what you describe - when an operator bypasses an interlock he has to enter a reason into a pop-up text box. This creates an entry in the Event Chronicle that is extracted and reported separately. (I know its a risk touching the Event Chronicle so there is no endorsement and this code is just to show the structure). Here is more detail than the previous reply.
  
  In the Detail Display, when the operator clicks on an interlock condition bypass the following script runs where ps_nm.CurrentValue is the name of the module. After a reason has been entered in a text box an SQL command using 'INSERT INTO' is assembled. Just omit any fields you do not want to update from the SQL command. The code to find a module's area from its name is not included here. Once the SQL is assembled an frsWriteValue is used to actually bypass the interlock and the SQL command then adds this record to the Chronicle.
  
  For reporting, a previous engineer set up a clever system where the Chronicle is continuously exported to an external MySQL server. A job on that server queries for these bypass events and creates a report and email alerts.
  
  Private Sub grpUnchecked2_Click()
  On Error GoTo ErrorHandler
  SIGlobals.XX_BypassReasonRecorder ps_nm.CurrentValue, 2
  Exit Sub
  ErrorHandler:
  frsHandleError
  EndSub
  
  This calls the following code in SIGlobals that has a reference to ADO. Change the name 'PROPLUS' in the SQL connection string for the host of your Event Chronicle.
  
  Option Explicit
  '
  Private sFieldnames As New Collection 'List of field names in Event Chronicle table Journal written to by interlock reason recording
  Private dTime_To_UTC As Double 'Hours between local and UTC time to write correct timestamps into Journal where it uses UTC
  
  Public Sub XX_BypassReasonRecorder(sModule As String, ByVal iIlkNum As Integer)
  '*********************************************************************************************************
  '* When interlock bypass requested, record a reason and save in Event Chronicle.
  '* sModule = Passed module name with server, e.g. DVSYS.1000-PMP-123
  '* iIlkNum = Interlock number being bypassed
  '*********************************************************************************************************
  
  Dim Confirm As VbMsgBoxResult
  Dim Reason As String
  Dim sCndDesc As String
  Dim bCmdOK As Boolean
  Dim conn As ADODB.Connection
  Dim sSQLstatement As String
  Dim i As Integer
  
  dTime_To_UTC = -4 'XX is UTC minus 4 hours. Times in Journal are all UTC
  
  On Error GoTo ErrorHandler
  
  'Ask operator to enter reason for bypass in text entry dialog box
  Reason = InputBoxEX("Please enter a reason for the bypass", "PXXInterlock Bypass Recorder")
  If Reason <> "" Then 'If a reason has been entered ...
  Dim sFields(12, 1) As String 'Array for data to write into historian: Column 0 = Names, Column 1 = Values
  InitFieldNames 'Fill column 0 with field names for table 'Journal'
  For i = 0 To UBound(sFields, 1)
  sFields(i, 0) = sFieldnames(i + 1)
  Next i
  
  'Assemble data into array to make complete record entry for Journal
  sFields(0, 1) = CStr(Now() - (dTime_To_UTC / 24)) 'Convert local time to UTC for timestamp
  sFields(1, 1) = 0 'Fraction of second at 0
  sFields(2, 1) = "CHANGE" 'Event type is CHANGE
  sFields(3, 1) = "USER" 'Category is USER
  bCmdOK = frsDisassembleAnyPath(sModule, , , sFields(7, 1)) 'Get module name as sModule will start with "DVSYS."
  'Read area from *** files
  bCmdOK = XX_GetModArea(sFields(7, 1), sFields(4, 1)) 'Get module's area.
  
  sFields(6, 1) = frsReadValue(sModule & "/UNITNAME.A_CV") 'Unit name if there is one
  sFields(8, 1) = frsReadValue(sModule & "/DESC.A_CV") 'Module description
  sFields(9, 1) = "BYPASS_REASON" & iIlkNum & ".CV" 'Fake module attribute to like real logic event '
  sFields(10, 1) = frsReadValue("DVSYS.THISUSER/USERNAME.A_CV") 'User logged on to console where reason entered
  sFields(5, 1) = frsGetComputerName 'Name of node where this reason is being entered
  
  sCndDesc = frsReadValue(sModule & "/CND" & iIlkNum & "/DESC.A_CV") 'Read description of interlock from condition block
  sFields(11, 1) = "BYPASS of " & iIlkNum & "| " & sCndDesc & "| " & Reason 'Reason as concatenated field to be read by MySQL job on external database
  sFields(12, 1) = 0 'Default the IsArchived tag to 0
  
  **** Add your code here to actually bypass the interlock or suppress the alarm, etc once a valid reason has been entered. ****
  
  'Write record of interlock bypass reason to Event Chronicle
  sSQLstatement = "INSERT INTO Journal (" 'Use SQL command INSERT INTO to record interlock bypass event in the Chronicle
  For i = 0 To UBound(sFields, 1) - 1 'Assemble list of fields and then their values into SQL command
  sSQLstatement = sSQLstatement & sFields(i, 0) & ", " 'INSERT INTO Journal ( list of fields ) VALUES ( list of values )
  Next i
  sSQLstatement = sSQLstatement & sFields(UBound(sFields, 1), 0) & ") VALUES ('" & sFields(0, 1) & "', 0, "
  For i = 2 To UBound(sFields, 1) - 1
  sSQLstatement = sSQLstatement & "'" & sFields(i, 1) & "', "
  Next i
  sSQLstatement = sSQLstatement & "'" & sFields(UBound(sFields, 1), 1) & "')"
  
  'Connect to Event Journal database on the ProPlus
  Set conn = New ADODB.Connection
  conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" & _
  "Data Source=PROPLUS\DELTAV_CHRONICLE;" & _
  "Initial Catalog = EJournal;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
  "Workstation ID=" & frsvariables.gs_wsName.CurrentValue & ";" & _
  "Use Encryption for Data=False;Tag with column collation when possible=False;QuotedID=No"
  
  conn.Open 'Open connection to Event Chronicle
  conn.Execute sSQLstatement 'Run SQL command
  conn.Close 'Close connection to Event Chronicle
  Set conn = Nothing
  
  Else
  frsMsgBox "You must enter a reason for the bypass.", vbOKOnly + vbCritical, "XX Interlock Bypass Recorder"
  End If
  
  Exit Sub
  
  ErrorHandler:
  conn.Close
  frsHandleError
  
  End Sub
  
  Private Sub InitFieldNames()
  'Set Journal table field names to be populated by interlock bypass reason
  'We use a collection so the array can be populated by a loop
  sFieldnames.Add "Date_Time"
  sFieldnames.Add "FracSec"
  sFieldnames.Add "Event_Type"
  sFieldnames.Add "Category"
  sFieldnames.Add "Area"
  sFieldnames.Add "Node"
  sFieldnames.Add "Unit"
  sFieldnames.Add "Module"
  sFieldnames.Add "Module_Description"
  sFieldnames.Add "Attribute"
  sFieldnames.Add "Desc1"
  sFieldnames.Add "Desc2"
  sFieldnames.Add "IsArchived"
  End Sub

  • Cancel
• István Orbán

  

  • 21 Aug 2018 12:38 PM

  I'd try to use Electronic Signatures - a built-in DeltaV feature.

  • Cancel
• Pat Grider

  

  • 22 Aug 2018 10:21 AM

  In reply to RobPerry:

  Nothing wrong with the concept, except for using an unsanitized user input with the SQL query. This web reference may help to explain the problem : stackoverflow.com/.../how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work

  • Cancel
• Tyler Anderson

  

  • 22 Aug 2018 1:32 PM

  In reply to Pat Grider:

  Good ole Bobby Tables ; --
  
  This is a great approach, however I will just advise that doing significant VBA in the background on an operator station has the potential to lock things up. Just for future/others, an example of things to look out for is DO/While loops with 100's of iterations.
  
  What Pat was mentioning and referenced is a SQL injection vector that the above code contains. To help you (or anyone using the code above) would want to do some research into using prepared statements and parameterized queries. In addition to Pat's link, this other stack overflow question has some examples of how to fix the mysql code to use parameterized queries. (stackoverflow.com/.../how-do-i-prevent-mysql-database-injection-attacks-using-vb-net) ((Yes, it's VB.net, but the syntax is super close to vba, you wouldn't have to do much to make it work with the above...))
  
  Great example and thanks for sharing!!

  • Cancel