DB work orders to PLC

Manglemender said:

If it helps, The VBA code below is taken from Excel but it connects to a Database via ODBC and executes a stored procedure that returns a recordset.

Code:

Sub Fetch_TimeSeries_By_LocalID(myLocalID As Long)
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Dim mySheet, myFileName, strTemp As String

On Error GoTo ErrHandler

If myConfig.DSN = "" Then
    ConfigOK = Read_Config
End If


myFileName = ActiveWorkbook.Name
mySheet = ActiveSheet.Name

Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."

Set WSP1 = Worksheets("Time Series Data")
WSP1.Activate
' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(7, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents

' Log into our SQL Server, and run the Stored Procedure

con.Open myConfig.DSN, myConfig.UName, myConfig.PWord
cmd.ActiveConnection = con

Dim prmLocalID As ADODB.Parameter
'Dim myLocalID As Integer

'myLocalID = CInt(Range("C2").Value)

' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)

cmd.Parameters.Append cmd.CreateParameter("myLocalID", adBigInt, adParamInput, 10, myLocalID)
Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "Return_TimeData_By_LocalID"
Set rs = cmd.Execute(, , adCmdStoredProc)

' Copy the results to cell B7 on the first Worksheet

If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set cmd = Nothing

con.Close
Set con = Nothing

Set WSP1 = Nothing
Workbooks(myFileName).Worksheets(mySheet).Activate

Application.StatusBar = "Data successfully updated."
Exit Sub
ErrHandler:

'rs.Close
Set rs = Nothing
Set cmd = Nothing

'con.Close
Set con = Nothing
  Workbooks(myFileName).Worksheets(mySheet).Activate
  Application.StatusBar = "VBSub:Fetch_TimeSeries_By_LocalID() error " & Err.Number & Err.Description
  Err.Clear

End Sub

Click to expand...

Manglemender said:

I think you will need WinCCAdvanced for this project or another SCADA (Ignition?) to make the connection to the database via scripting and extract data from it. I've never done it directly in the SCADA so I'm unsure whether WinCC Advanced can do it but I'd be surprised if it can't. Many people suggest that it would be easier in Ignition.

The other option is to get the database to export the data as a text file in a defined format but you will still need to use scripting to read the file and extract the data.

It sounds to me like you need two stored procedures in the database: 1) List available orders 2) return data based on order number. Your program must first query the database to get a list of the available orders and then, using the order number, select the relevant data. You might also need a third stored procedure to mark a job number as complete.

Good luck,

Nick

Click to expand...

LoganB said:

Seems like no one likes Factory Talk Transaction Manager

Click to expand...

Manglemender said:

I have used FTTM but it isn't for the feint hearted. I actually think it's a great product, just difficult to set up.

Nick

Click to expand...

sigmadelta said:

I have done database reporting and recipes with WinCC Advanced and MS SQL Server 2014. Also data management with other SCADA software.

Basically, WinCC has to be looking for a new work order. If every minute is fast enough, use the Scheduler. If not a PLC timer is needed to execute your script.

Something like... SELECT TOP 1 <columns> ... FROM <columns> WHERE job_complete = 0 ORDER BY date ASC

Then when you finish: UPDATE <table> SET <other process variables>, job_complete = 1 WHERE order_id = <current work order id>

Click to expand...

have you done something like that?

Click to expand...

sigmadelta said:

Yes I have.

Click to expand...