I am reading data from a database which I want to average and make available to other parts of the system. I thought the best way to do this would be to read the data and then write the averages out to a temporary SQL table. The table has over 80 columns.
Is there an easy way of doing this or am I going to have to build the SQL output statement column by column?
Al,
If you want this data available to all clients without recalculating it for each one, you may want to do this in FactorySQL. If not, there are a couple things you can do in FactoryPMI. How are you getting the averages in the first place? Databases are very good at handling this kind of request, and if you are using a “SELECT AVG(col_x) FROM table…” with 80 columns in it, then you can just store that in a dynamic property and re-use it. If you are using a “SELECT * FROM table…” and using python to calculate the averages, then you can just build an SQL update query to populate the temporary table in the same script.
I need more information on exactly what you are doing to be able to choose the best approach, but this should get you pointed in the right direction.
Hi ,
I’m using a request like “SELECT AVG(col_x) FROM table…” and am currently storing it in a dynamic property in a Client tag. (This job is purely providing an interface to a SQL database and so I’m trying to do it without FactorySQL and SQLTags.)
My only issue with doing things this way was that I want to get at different subsets of this data (in this case to draw a number of charts). I want to refer to the fields by name rather than number in case more fields are added in the future (potentially changing the order of the data in the DataSet). As I understand it, I will have to convert the DataSet into a PyDataSet before accessing individual items of data by name.
I was just about to say that I couldn’t preview data in a DataSet when it is stored in a Client Tag, but I found the button on the Tag Properties page - excellent!
My remaining task seems to be taking a subset of data from the (one row) in the first DataSet and storing it in a form accessible to a chart - how would you recommend I approach this?
Al
Can you give us a bit more concrete example of what the dataset looks like? I’m lost - is it wide or tall? How are you going to graph 1 row of data, a graph needs an x and a y for each point…
Very true! 
I am going to chart the data using an XY plot by generating the matching X value (starting at one and incrementing) to match each Y value extracted from the original DataSet. I imagine it will be easier to produce a second DataSet with 2 columns, the first holding the generated values, the second holding the actual data values.
Ah, the old ‘turn a table on its side’ problem. The only ways I know to do this are in a stored procedure or a python script. Doing it in python, you’d create a dataset dynamic property, and then respond to its component’s propertyChange event, generating a new database for your chart every time the source dataset changed.
To get you started:
if event.propertyName=="RawDataset":
ds = fpmi.db.toPyDataSet(event.newValue)
values = []
x=0
for yVal in ds[0]:
values.append([x, yVal])
x += 1
chart = event.source.getComponent("Chart")
chart.data = fpmi.db.toDataSet(["X","Y"], values)
I’ve got this working with the following method. When the user selects a record, I read the data from the SQL database into a Client (DataSet) tag.
When the user wants to view a chart, I run the following code:
[code]#Read dataset containing complete data.
data = fpmi.tag.getTagValue("[Client]selectedData")
#Write results into new PyDataSets.
results = []
prediction = []
first = 0
#First data point.
if data.getValueAt(0,“Y1_1_R”) is not None:
results.append([4,data.getValueAt(0,“Y1_1_R”)])
if first == 0:
#Prediction line starts at the first valid point.
prediction.append([4,data.getValueAt(0,“Y1_1_R”)])
prediction.append([9,data.getValueAt(0,“Y1_1_R”) + 5])
first = 1
.
.
.
#Last data point.
if data.getValueAt(0,“Y2_3_R”) is not None:
results.append([9,data.getValueAt(0,“Y2_3_R”)])
if first == 0:
prediction.append([9,data.getValueAt(0,“Y2_3_R”)])
#Write these DataSets to the Chart.
chart = event.source.parent.getComponent(“Chart”)
chart.results = fpmi.db.toDataSet([“Term”,“Results”], results)
chart.prediction = fpmi.db.toDataSet([“Term”,“Prediction”], prediction)
[/code]
This all seems to work very well . I read fields directly from the original DataSet by name, which guards against problems if other fields are added in the future. The data only needs to be extracted just before viewing the Chart (which is then static), so I don’t need to respond to change events.