Essentially what I need to do is take the data from a table in an SQL database and then update a set of points within CIMPLICITY.

As in the following example I would want to take the data in Column 1 and load that data into a set of points with the values of 1, 2 and 3 respectively.

Column 1​

1​

2​

3​

I am not great a script creation so any examples would be useful.

 

You could adapt this alarm history script to read the table that you are logging the data into. Key aspects are the SQLOpen, SQLExecQuery, SQLBind and SQLRetrieve commands.

Sub Main()

' This routine outputs an alarm history list as a dialogue box
' In this version, all alarm data is output with most recent alarms at the top

Dim b() As Variant
Dim c() As Variant

' Open connection to CIMPLICITY Alarm Log and perform SQL query

Connid& = SQLOpen("dsn=CIMPLICITY Alarm Logging",,4)
Qry& = SQLExecQuery(Connid&,"Select * From ALARM_LOG")

' Define SQL query results to be binded
' (comment out unrequired fields)

I% = SQLBind(Connid&,b,1) ' Date and Time
' I% = SQLBind(Connid&,b,2) ' Sequence Number
I% = SQLBind(Connid&,b,3) ' ID
I% = SQLBind(Connid&,b,4) ' Alarm Class
I% = SQLBind(Connid&,b,5) ' Resource
' I% = SQLBind(Connid&,b,6) ' Logging Process
' I% = SQLBind(Connid&,b,7) ' Reference
' I% = SQLBind(Connid&,b,8) ' Previous State
' I% = SQLBind(Connid&,b,9) ' Log Action
I% = SQLBind(Connid&,b,10) ' Final State
I% = SQLBind(Connid&,b,11) ' Alarm Message
' I% = SQLBind(Connid&,b,12) ' Generation Time

' Retrieve SQL Query results into an array

l& = SQLRetrieve(Connid&,c)

' Define string array for output of alarm list

Dim Data(Ubound(c)) As String

' Populate string array (in reverse order so most recent is at top)
' (additional handling is included to expand the final state)

z = Ubound(c)
For row = 0 To Ubound(c)
s$ = ""
For column = 0 To Ubound(b)
t$ = c(row, column)
If column = 4 Then ' expand final state
u$ = Left$(t$, 1)
If u$ = "G" Then
t$ = "Generated"
ElseIf u$ = "D" Then
t$ = "Deleted"
ElseIf u$ = "A" Then
t$ = "Acknowledged"
ElseIf u$ = "R" Then
t$ = "Reset"
End If
End If
s$ = s$ & t$ &", "
Next column
Data(z-row) = s$
Next Row

' Define dialogue box for output of alarm list
' (only action is to close the dialogue box)

Begin Dialog AlarmList ,,500,260,"Alarm History"
ListBox 8,8,484,216,Data$,.ListBox1
PushButton 440,236,52,16,"Close",.ClosePushButton
End Dialog

' Display alarm list dialogue box

Dim dummy As AlarmList
rc% = Dialog(dummy)

' Close connection to database

Connid& = SQLClose(Connid&)

End Sub