Wonderware and SQL display ideas

baref00t25

Member
Join Date
Feb 2006
Location
Central Wisconsin
Posts
176
We have an application where we are storing information about the product that this machine makes. The data we are storing are marks on the product so the operator can see where the marks are on the roll of material so they can cut that section out. Each "mark" has roughly 20 values associated with it (date,time,size,x position, y position, picture file path, etc.)

We are getting the data into SQL through Wonderware and we can retrieve the data for viewing, but only one at a time on the screen. We could potentially make 16 seperate connections to the database and display upto 16 data points, but would prefer to display 30 - 50 "marks" at once so that we could print it. So far the only way we can see doing this is to painfully load thesee manually one at a time into memory variables which could be up to 1000 variables if we do 50 "marks".

Are there any other ways that someone could think of? Maybe loading it into a .csv or .xls file and then displaying?
 
Could you describe the "one at a time"? It makes little sense in the context of an SQL query. One 'mark' or data point?

How dynamic are these values? I'm assuming that a single (or few) "mark(s)" is stored in the PLC at a time - which sounds like a classic "batching" operation.

baref00t25 said:
We have an application where we are storing information about the product that this machine makes. The data we are storing are marks on the product so the operator can see where the marks are on the roll of material so they can cut that section out. Each "mark" has roughly 20 values associated with it (date,time,size,x position, y position, picture file path, etc.)

We are getting the data into SQL through Wonderware and we can retrieve the data for viewing, but only one at a time on the screen. We could potentially make 16 seperate connections to the database and display upto 16 data points, but would prefer to display 30 - 50 "marks" at once so that we could print it. So far the only way we can see doing this is to painfully load thesee manually one at a time into memory variables which could be up to 1000 variables if we do 50 "marks".

Are there any other ways that someone could think of? Maybe loading it into a .csv or .xls file and then displaying?
 
'One at a time' is referring to one data set or 'mark'. Instead of saying data points, I sould have used data set.

The values are batched. When the roll is finished we read an array of 20 x (number of marks). Number of marks can be from 0 to 100. The PLC stores all of these 'marks' until InTouch has read them and stored into SQL, then the PLC will remove the data and get ready for the next roll.
 
This looks like an icky problem that will likely require custom coding/scripting/queries to work out well - regardless of the package you use.

I'll use the following terminology:
mark = fixed array of 20 values of various types.
roll = set of exactly 100 (or 50 or whatever) marks.
history = uniquely indexed set of recorded rolls.

How is the roll data determined? User input, based on process, based on a set of predefined values, or a combination. Important questions are if they tend to be unique and when/how they are generated.

A few observations -
1. You're probably going to want to stick to using a fixed roll size - 50, 100, whatever.
2. You almost certainly don't want to create 16+ database connections - that has bad written all over it.

What do you need to do with the history? Just pull up and print rolls or do any data mining/comparing/etc. About how many rolls do you have? This case may be simpler to write a script that outputs each roll to a separately named .CSV file (or does something clever in the database).

baref00t25 said:
'One at a time' is referring to one data set or 'mark'. Instead of saying data points, I sould have used data set.

The values are batched. When the roll is finished we read an array of 20 x (number of marks). Number of marks can be from 0 to 100. The PLC stores all of these 'marks' until InTouch has read them and stored into SQL, then the PLC will remove the data and get ready for the next roll.
 
Last edited:
The roll data is determined by the number of marks (or imperfections) in the material. So if we choose a roll size of 50 then a perfect roll would have 50 blank 'marks'.

The reason I mentioned 16 connections is that is the limit with InTouch for SQL connections.

As for history:
The SQL database is the history. The unique identifier is the Roll ID number that we create by concatenating the date and time together. 5/23/2008 12:10:10 = 5232008121010. Every 'mark' that we store to the SQL database has the Roll ID number so that we can print reports from past rolls.

We are currently playing with the idea of the csv file, but it displays poorly on the screen with the scroll bars and A B C column headers.
 
I see - well two options:
1. Use the relational database as you're describing. No need to "fill gaps" in the data.
It might make sense to have a "roll->mark" mapping table instead of a "roll" column on the mark table - depending on what you're trying to do. How much of this is canned and how much have you created?

2. The .CSV file would probably be easiest to work with if you flatten it out to the maximum size, padding appropriately. If you get it working well, you could go back and store it in the SQL database as a BLOB or create a fixed table structure for it.

I understand the 16 connections, but don't see how that matters. I take it you can't set up a connection pool...

baref00t25 said:
The roll data is determined by the number of marks (or imperfections) in the material. So if we choose a roll size of 50 then a perfect roll would have 50 blank 'marks'.

The reason I mentioned 16 connections is that is the limit with InTouch for SQL connections.

As for history:
The SQL database is the history. The unique identifier is the Roll ID number that we create by concatenating the date and time together. 5/23/2008 12:10:10 = 5232008121010. Every 'mark' that we store to the SQL database has the Roll ID number so that we can print reports from past rolls.

We are currently playing with the idea of the csv file, but it displays poorly on the screen with the scroll bars and A B C column headers.
 
On the .csv file, do you know how to flatten it out to the maximum size, and the padding? Right now I'm using the ActiveX of Microsoft Internet Explorer to display the csv file. The other issue I have is the columns aren't coming in the correct width and in order to print them I have to manually resize the columns.

We don't need to worry about storing the data, we have that portion working just fine. It the report that is causing the trouble.
 
Well, I could have helped you attack that problem with script - which turns out to not be relevant here.

Why not use Excel for formatting and printing? Does the process need to be automated?

Sorry, I can't address the intricacies of reporting within Wonderware or your ActiveX plugin. Can you get vendor support?

baref00t25 said:
On the .csv file, do you know how to flatten it out to the maximum size, and the padding? Right now I'm using the ActiveX of Microsoft Internet Explorer to display the csv file. The other issue I have is the columns aren't coming in the correct width and in order to print them I have to manually resize the columns.

We don't need to worry about storing the data, we have that portion working just fine. It the report that is causing the trouble.
 
You should be able to use Intouch SQL commands within scripts to read rows of data and store them into tags. Check out SQLFirst(), SQLNext(), etc. Alternatively, there's an Intouch ActiveX called SQLGrid that will display a table of data for you.
 
Currently we are using SQLFirst(), SQLNext(), etc. to get our data. This gives us our data that we need, but we need to display a large amount of data at once. The SQLGrid sounds like it might be what we are looking for. Where would I get that ActiveX install and some possible examples?
 
Intouch is pretty good with indirect addressing so you should be able to customize your data display anyway you want (display 50 rows at a time, page up, page down, etc.). I believe the SQLGrid is available from WW download section.
 
I've downloaded the SQLGrid and have tried using it. I can seem to get connected to the database. The help file is not very helpful and I have put in a call to Wonderware support, but haven't recieved a call back. I've also tried using MSFlexGrid and that I can change the rows and column sizes, but I can't set the individual cells to values.
 
We ended up using wwGenericSQLGrid available at www.wonderware.com/support. There is a PDF file that comes with the ActiveX install. The PDF file is a little cryptic with what is a “keyword”, “tags”, or “name”. We used the OBDC connection that we needed to use the SQL Access Manager.

We couldn’t just use the Properties tab on the SQLGrid because it required a username and password and our SQL database is not configured to use SQL Authentication but rather NT authentication. We used the ConnectWithString fuction as shown below:

#GridAdmin.ConnectWithString("Provider=MSDASQL.1;Persist Security Info=True;Data Source=The DSN Name Goes Here;Initial Catalog=The SQL Database Name Goes here");

From there we were able to set the SQLString in the SQLGrid Properties to: Select * from tbl_mark. “tbl_mark” is the name that we gave the table when we created it using SQLCreateTable.

This was a little frustrating, especially becasuse we did not get any assistance from Wonderware Tech Support.
 
I know this is an old thread, but I am new to wonderware and sql and have been learning to connect and dump values into microsoft access from wonderware, but I do not know how to retrieve the data from the database and display it on the HMI screen. I was hoping you could help me out here barefoot. Thanks!

Here is what I have so far:

WhereExpr = "Time_Year = '2012'";
OrderByExpr = "";
ResultCode = SQLSelect( ConnectionID, TableName, BindList, WhereExpr, OrderByExpr );

Which seems to work, but how do I get the values into tags?
 
hi drip,
Unfortunately I haven't used SQL with Wonderware since that application back in 2008. To get the data to a tag it would need to be run by a script triggered by something. There is no way that I found that a tag could be directly linked to an Access database. I hope that helps.
 

Similar Topics

I am new to Wonderware and am trying to display a few columns of information from an SQL database onto one of my HMI screens. The SQL database...
Replies
1
Views
3,345
1. Why Wonderware crash after SQL crashes. Can I get something official of AVEVA?
Replies
1
Views
1,038
I’m trying to get data from a column in sql server to post in wonderware. My select script is as follows...
Replies
8
Views
4,480
I have a wonderware standalone HMI collecting process data from our production line. I am writing those values to a database in MySQL, though I...
Replies
0
Views
2,711
This is a pretty general question since I'm just beginning on these fronts, but does anyone know of a good "Where to Start" type of tutorial? I...
Replies
1
Views
1,910
Back
Top Bottom