Recipe storage in SQL database

ASF · Aug 16, 2015

Hi all,

I have a customer who wants a recipe management system integrated on one of their production lines, and they would like it set up in their existing SQL databases. SQL is a fairly new beast to me, but thanks to their very helpful IT guys I'm learning as much as I can

Just wondering what people's thoughts are on the best way to get the data back and forth? Their setup is currently:

- ControlLogix PLC's as central line control PLC's, with ENBT for communication
- CompactLogix PLC's on individual machines
- FTView SE Network Distributed SCADA system
- FTView SE Historian

I don't need to be logging and timestamping values, basically all I need to do is set up a recipe structure to have things like:

Recipe Name (string)
Product 1...n weight
Conveyor 1...n speed
Temperature SP
Weight SP
etc.

They'll need to be able to view, create and modify recipe data using the FTView SE system. Then I just need to be able to call for "recipe ABC" and have all the recipe parameters be loaded from the SQL database into the CLX to run the day's production. A couple of the parameters may need to be loaded into individual machine PLC's, but worst case I can just send setpoint information via comms from the central Control Logix.

Is it possible to do this using the existing FTView SE setup? Can FTView SE natively go to and from an SQL database, or do I need to use something like Transaction Manager?

Any suggestions or experiences are appreciated. As they've gone to great effort to keep everything as AB so far, they're going to want to keep it that way here. But to be honest I'll probably be able to sell a different solution to them as long as I can promise it's bullet proof.

Thanks!

rdrast · Aug 17, 2015

FTView and VBA work for storing, editing, and retrieving recipes from a SQL database.

I usually try to do as much with SQL Stored procedures as possible; it makes the VBA cleaner.

Paullys50 · Aug 17, 2015

Any number of routes you could go, VBA as previously mentioned can be hit-or-miss given your experience with SQL and whatever your VBA experience is. Not to mention the quirks of VBA in FTView SE.

If they love their AB then Transaction Manager would work too, you could also get a hardware solution like the OLDi eATM module.

Probably a bigger factor is time and $$$. What is the end-user's budget for new hardware and/or software? Why type of test rig do you have available to work out VBA items?

Personally, this is where I would use the Ignition SQL bridge module and start the brain-washing.

curlyandshemp · Aug 17, 2015

Paully's5.0 said:
Any number of routes you could go, VBA as previously mentioned can be hit-or-miss given your experience with SQL and whatever your VBA experience is. Not to mention the quirks of VBA in FTView SE.

If they love their AB then Transaction Manager would work too, you could also get a hardware solution like the OLDi eATM module.

Probably a bigger factor is time and $$$. What is the end-user's budget for new hardware and/or software? Why type of test rig do you have available to work out VBA items?

Personally, this is where I would use the Ignition SQL bridge module and start the brain-washing.

2nd using Inductive Automation Ignition SQL bridge module. Set up is simple and easy. Not the nightmare hell in setting up Transaction Manager.
You can purchase just the SQL bridge module with unlimited tags for about the same cost of FTTM.

ASF · Aug 17, 2015

I'll have a look at the Ignition solution, it seems to have a lot of fans

As far as budget, at the moment they're just after an indicative cost so they can decide for sure that they want to do it.

rdrast, how much is there involved in using VBA to do this? As mentioned, my SQL knowledge is not all that, but I have some very helpful IT guys onsite to help me out who know it forwards, backwards and upside down, so I should be able to muddle through that part of it OK. I've used VBA reasonably extensively in the existing FTView SE application - I'm no expert but if I can get my hands on a "how to" document/some example code/an SQL to VBA manual I should be able to work that out too. I have remote access to the site and all of it's servers so I can set up some test displays and play around with things without upsetting anybody.

The advantage I can see about doing it with VBA if it's possible is that it I've got redundant FTView SE servers set up, and that would keep the redundancy should the primary one fall over. Could I do the same thing with ignition somehow?

Paullys50 · Aug 18, 2015

ASF said:
The advantage I can see about doing it with VBA if it's possible is that it I've got redundant FTView SE servers set up, and that would keep the redundancy should the primary one fall over. Could I do the same thing with ignition somehow?

Ignition a redundancy option, but I'm guessing your SQL server isn't redundant. If not, I'd just run the SQL Bridge module on the SQL Server box. Ignition or FTTM are nice because they provide handshakes that tell you if the read/write was a success or not. With VBA you don't know if it all downloaded properly unless you read all the PLC registers where the information is downloaded and compare with with what you're expecting. With the IT team and your experience I'm sure you can get it done without much trouble. If it's only a few data points, probably not worth purchasing Ignition SQL Bridge.

HOWEVER, the Ignition SQL Bridge gives you all sorts of ability to collect other data was well, so if data collection is a future value add this would be an excellent addition.

ASF · Aug 18, 2015

Thanks Paul, that's an excellent point about the SQL server not being redundant.

The factory has the FTView SE and Historian already set up, so all of the data logging is taken care of in that regard - or does the SQL bridge log different data?

I had a look at the Ignition pricing and almost fell off my chair until I realised I only needed one (or two) bridge modules, not the $32,000 ultimate SCADA package

it's certainly true that if I can get it all going with a $2600 bridge module and a little bit of tinkering, it may not be worth spending days nutting out the VBA.

Just for my curiosity though, does anyone know of a VBA-SQL manual/how-to document that I can poke through to see how involved it is at that end?

Thanks!

Manglemender · Aug 19, 2015

I've only played with this a little but the code below was written in excel VBA to run a stored procedure that returns a recordset from SQL server; It is based on an example that I found on the internet somewhere. It might give you a flavour of what is involved.

Nick

Code:

Sub Fetch_TimeSeries_By_LocalIDV2(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

' 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


Dim iRows, iCols As Integer

iRows = 8

While Not rs.EOF
    For iCols = 0 To rs.Fields.Count - 1
        WSP1.Cells(iRows, iCols + 2).Value = rs.Fields(iCols).Value
    Next iCols
    rs.MoveNext
    iRows = iRows + 1
Wend

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_LocalIDV2() error " & Err.Number & Err.Description
  Err.Clear

End Sub

Paullys50 · Aug 19, 2015

ASF said:
The factory has the FTView SE and Historian already set up, so all of the data logging is taken care of in that regard - or does the SQL bridge log different data?

It could, historians typically will log time-series data, and its not bi-directional. There are times where event driven data-collection will produce a better picture of a process especially if production summary reports are required, or something like a batch application where you want to collect target amount, actual amount, ingredient information, time to add the ingredient...etc.

So a product like SQL Bridge (or FTTM) can do both the time-series data and easily be triggered to capture data based on events, plus its bi-directional. Which makes it great for recipe management as well as data collection. The more complex your needs, the more complex VBA is required to do the same thing.

ASF said:
I had a look at the Ignition pricing and almost fell off my chair until I realised I only needed one (or two) bridge modules, not the $32,000 ultimate SCADA package ...

How much has the FTView SE soluiton cost the customer??

ASF said:
Just for my curiosity though, does anyone know of a VBA-SQL manual/how-to document that I can poke through to see how involved it is at that end?

In addition to Manglemender's code, you probably want to search for VBA in RSView32 applications both on the forum and in Rockwell manuals. VBA in RSView32 was used heavily for this type of thing as RSView32 provided better VBA support than FTViewSE does. Albiet, not necessarily very efficient. I've seen recipe downloads in RSView32 on a batching application take more than 3 minutes to process (we went back and rewrote the vba to get it to 30 seconds). That was 7 years ago so my memory is hazy on the VBA code we used.

Manglemender · Aug 19, 2015

Paully's5.0 said:
VBA in RSView32 was used heavily for this type of thing as RSView32 provided better VBA support than FTViewSE does.

I wrote my own file based recipe systems for RSview32 in VBA that worked better than the built in recipe system.

One thing to be aware of in RSview32 is that it is single threaded so if one script is running then it wont do anything else.

Nick

Phil Buchanan · Aug 19, 2015

I have used almost every solution suggested above and I will tell you stay away from Rockwell Transaction manager unless you are looking to increase your stress level quite a bit.

You could roll your own with FT View and VBA and that would depend on how large and complex the recipe DB will be.

The best solution I have found hands down is the OLDi module. It's very slick and easy to setup for recipe's, Data Logging, Etc.

The module is around the 5K mark but it can be used in a Data concentrator and service many process lines for recipe usage and data collection. On the data collection it can parse about 500 tags per second.

ASF · Aug 23, 2015

Paully's5.0 said:
How much has the FTView SE soluiton cost the customer??

It'd probably be around the same amount, but I don't think I'd be able to sell it to them twice

I think I'm leaning toward the ignition solution here - I get the feeling that once I implement it they're going to want to start doing more and more with it, and I think having it separated from the SCADA VBA would be beneficial to my blood pressure

PBuchanan, is this the OLDi module you're referring to?

Phil Buchanan · Aug 23, 2015

ASF said:
PBuchanan, is this the OLDi module you're referring to?

Yes it is.

ASF · Aug 25, 2015

Thanks for all the advice everyone. I've spoken to my local rep about the OLDi module and it also looks like quite a neat solution.

I think I'm going to propose both the OLDi and the ignition options to the customer (once I get all the pricing info) and see whether they'd prefer the hardware or software solution. It looks like either of them would do the job nicely.

Phil Buchanan · Aug 25, 2015

ASF said:
Thanks for all the advice everyone. I've spoken to my local rep about the OLDi module and it also looks like quite a neat solution.

I think I'm going to propose both the OLDi and the ignition options to the customer (once I get all the pricing info) and see whether they'd prefer the hardware or software solution. It looks like either of them would do the job nicely.

I agree it's hard to go wrong with either of those choices but since it's late I will muddy the water a little.

A couple more points to consider is how important is the data and what impact will a loss of data have? If its very important data that can't be lost for even a couple minutes a hardware solution like the OLDI module may be best as it has onboard memory and can buffer quite a bit of data on loss of connection and push it up to the server once connection is restored.

Another point to consider on the Ignition side though is of the customer may want to make Ignition it's HMI solution going forward and move away from FT View there may be some licensing considerations if you plan to deploy it further in the near future.

Just a few more things to throw on the table and hash out.

Recipe storage in SQL database

ASF

Lifetime Supporting Member

rdrast

Lifetime Supporting Member

Paullys50

Lifetime Supporting Member

curlyandshemp

Lifetime Supporting Member

ASF

Lifetime Supporting Member

Paullys50

Lifetime Supporting Member

ASF

Lifetime Supporting Member

Manglemender

Member

Paullys50

Lifetime Supporting Member

Manglemender

Member

Phil Buchanan

Lifetime Supporting Member

ASF

Lifetime Supporting Member

Phil Buchanan

Lifetime Supporting Member

ASF

Lifetime Supporting Member

Phil Buchanan

Lifetime Supporting Member

Similar Topics