SQL Upload From a Dataset Tag

Ignition
1

I have a dataset tag and I want to upload it to SQL on a timer script in the Gateway event scripts.
My problem is with pulling the data from the dataset tag.
I am receiving errors about functions that I thought were right. "getRowCount and getValueAt

image
image1115×318 16.5 KB

Attached is a button script that works by looking at a power table on a window.
Also attached is the timer script I have been trying to do the same thing with.
(so I can run it on a schedule)

Button Script.txt (2.6 KB)
Gateway Event Script - Timer.txt (2.3 KB)

Any help would be greatly appreciated.

2

In future please paste your code directly into your post in preformatted text ( </> button) rather than attaching as a text file, it will make it much easier for us to read and troubleshoot.

In your button script, you have included HTML formatting in your column names, not sure if perhaps this is copy paste error, but this will prevent getValueAt from executing.

Re your gateway script, system.tag.read returns a qualified value. You need to use .value to access just the value of your dataset tag.

https://docs.inductiveautomation.com/display/DOC79/system.tag.read

3

Thank you. - The HTML was due to the table formatting on a window and the dataset tag column names did not have the formatting.

I've used the .value on system.tag.read but how can I do it on a system.tag.readAll ?

4

So this script below does work; however it is only about 30 individual tag reads and it lags.
This is 1 row, there are 41 rows, so I should be using "readAll". It will be over 1000 tags.

Here is the working code:
*The variable "TAGset" is where I am trying the "readAll" so it is commented out at the moment.


logger = system.util.getLogger("myLogger")
logger.info("Starting Script")


query1 = "DELETE FROM tblSystemReport"
system.db.runPrepUpdate(query1)


data = system.tag.read("[default]SQL Update")

headers = [
"Date",
"Well",
"GPU",
"Flow Rate",
"Total Gas TDY",
"Total Gas YDY",
"Tubing Press",
"Casing Press",
"9-5/8 Press",
"13-3/8 Press",
"Surface Casing Press",
"Sand Trap Press",
"Separator Press",
"WH Static Press",
"WH DP Press",
"Midstream Press",
"Delivery Static1 Press",
"Delivery Static2 Press",
"Filter Sep Press",
"Filter Sep DP Press",
"Dew Point",
"Choke",
"Brine Flow",
"Today Water",
"Yest Water",
"Tank 1 Lvl",
"Tank 2 Lvl",
"Tank 3 Lvl",
"Tank 4 Lvl"
]
 

data = []

#TAGset = [
#"gaswellsites/midas/wells/2M/pad_short",
#"gaswellsites/midas/wells/2m/GPU2100/gpuid",
#"GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Inst Volume Flow Rate",
#"GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Total Volume Today",
#"GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Total Volume Yesterday",
#"GasWellSites/Midas/Wells/2M/W1100/Tub_Press/processValue",
#"GasWellSites/Midas/Wells/2M/W1100/Prod_Cas_Press/processValue",
#"GasWellSites/Midas/Wells/2M/W1100/Inter2_Cas_Press/processValue",
#"GasWellSites/Midas/Wells/2M/W1100/Inter1_Cas_Press/processValue",
#"GasWellSites/Midas/Wells/2M/W1100/Surface_Cas_Press/processValue",
#"GasWellSites/Midas/Wells/2M/GPU2100/Status/V-2X00 Pressure (Sand Separator)",
#"GasWellSites/Midas/Wells/2M/GPU2100/PIT-2X50_Pressure/processValue",
#"GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Static Pressure",
#"GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Differential Pressure",
#"GasWellSites/LogansFerry/Run_1/Pressure",
#"GasWellSites/LogansFerry/Run_1/Pressure",
#"GasWellSites/LogansFerry/Run_2/Pressure",
#"GasWellSites/LogansFerry/MeterValues/Site_Inlet_Pressure/processValue",
#"GasWellSites/LogansFerry/MeterValues/FilterSep_DiffPressure/processValue",
#"GasWellSites/LogansFerry/DewPoint/DewPoint",
#"GasWellSites/Midas/Wells/2M/GPU2100/Status/FV-2X20 Position Feedback (Auto Choke)",
#"GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Inst Volume Flow",
#"GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Total Volume Today",
#"GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Total Volume Yesterday",
#"GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank1/Volume",
#"GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank2/Volume",
#"GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank3/Volume",
#"GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank4/Volume"
#].value
#TAGS = system.tag.readAll(TAGset)
#print TAGS

#test = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Inst Volume Flow Rate").value
#print test

#Populate Variables

MS2M_Date = system.date.now()
MS2M_Well = system.tag.read("gaswellsites/midas/wells/2M/pad_short").value
MS2M_GPU = system.tag.read("gaswellsites/midas/wells/2m/GPU2100/gpuid").value
MS2M_Flow_Rate = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Inst Volume Flow Rate").value
MS2M_Total_Gas_TDY = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Total Volume Today").value
MS2M_Total_Gas_YDY = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Total Volume Yesterday").value
MS2M_Tubing_Press = system.tag.read("GasWellSites/Midas/Wells/2M/W1100/Tub_Press/processValue").value
MS2M_Casing_Press = system.tag.read("GasWellSites/Midas/Wells/2M/W1100/Prod_Cas_Press/processValue").value
MS2M_9_Press = system.tag.read("GasWellSites/Midas/Wells/2M/W1100/Inter2_Cas_Press/processValue").value
MS2M_13_Press = system.tag.read("GasWellSites/Midas/Wells/2M/W1100/Inter1_Cas_Press/processValue").value
MS2M_Surface_Casing_Press = system.tag.read("GasWellSites/Midas/Wells/2M/W1100/Surface_Cas_Press/processValue").value
MS2M_Sand_Trap_Press = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/Status/V-2X00 Pressure (Sand Separator)").value
MS2M_Separator_Press = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/PIT-2X50_Pressure/processValue").value
MS2M_WH_Static_Press =system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Static Pressure").value
MS2M_WH_DP_Press = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Differential Pressure").value
MS2M_Midstream_Press = system.tag.read("GasWellSites/LogansFerry/Run_1/Pressure").value
MS2M_Delivery_Static1_Press = system.tag.read("GasWellSites/LogansFerry/Run_1/Pressure").value
MS2M_Delivery_Static2_Press = system.tag.read("GasWellSites/LogansFerry/Run_2/Pressure").value
MS2M_Filter_Sep_Press = system.tag.read("GasWellSites/LogansFerry/MeterValues/Site_Inlet_Pressure/processValue").value
MS2M_Filter_Sep_DP_Press = system.tag.read("GasWellSites/LogansFerry/MeterValues/FilterSep_DiffPressure/processValue").value
MS2M_Dew_Point=system.tag.read("GasWellSites/LogansFerry/DewPoint/DewPoint").value
MS2M_Choke = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/Status/FV-2X20 Position Feedback (Auto Choke)").value
MS2M_Brine_Flow = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Inst Volume Flow").value
MS2M_Today_Water =system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Total Volume Today").value
MS2M_Yest_Water = system.tag.read("GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Total Volume Yesterday").value
MS2M_Tank1LVL =system.tag.read("GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank1/Volume").value
MS2M_Tank2LVL = system.tag.read("GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank2/Volume").value
MS2M_Tank3LVL =system.tag.read("GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank3/Volume").value
MS2M_Tank4LVL = system.tag.read("GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank4/Volume").value

print MS2M_Choke

# Then add each row to the list. Note that each row is also a list object.
data.append([
MS2M_Date,
MS2M_Well,
MS2M_GPU,
MS2M_Flow_Rate,
MS2M_Total_Gas_TDY,
MS2M_Total_Gas_YDY,
MS2M_Tubing_Press,
MS2M_Casing_Press,
MS2M_9_Press,
MS2M_13_Press,
MS2M_Surface_Casing_Press,
MS2M_Sand_Trap_Press,
MS2M_Separator_Press,
MS2M_WH_Static_Press,
MS2M_WH_DP_Press,
MS2M_Midstream_Press,
MS2M_Delivery_Static1_Press,
MS2M_Delivery_Static2_Press,
MS2M_Filter_Sep_Press,
MS2M_Filter_Sep_DP_Press,
MS2M_Dew_Point,
MS2M_Choke,
MS2M_Brine_Flow,
MS2M_Today_Water,
MS2M_Yest_Water,
MS2M_Tank1LVL,
MS2M_Tank2LVL,
MS2M_Tank3LVL,
MS2M_Tank4LVL,
])

data = system.dataset.toDataSet(headers, data)
datawrite = system.tag.write("SQL Upload System", data)
#print data

for row in range(data.getRowCount()):
	time = data.getValueAt(row, "Date")
	well = data.getValueAt(row, "Well")
	gpu = data.getValueAt(row, "GPU")
	flowrate = data.getValueAt(row, "Flow Rate")
	totalgastdy = data.getValueAt(row, "Total Gas TDY")
	totalgasydy = data.getValueAt(row, "Total Gas YDY")
	tubingpress = data.getValueAt(row, "Tubing Press")
	casingpress = data.getValueAt(row, "Casing Press")
	press958 = data.getValueAt(row, "9-5/8 Press")
	press1338 = data.getValueAt(row, "13-3/8 Press")
	surfacecasing = data.getValueAt(row, "Surface Casing Press")
	sandtrap = data.getValueAt(row, "Sand Trap Press")
	separator = data.getValueAt(row, "Separator Press")
	whstatic = data.getValueAt(row, "WH Static Press")
	whdp = data.getValueAt(row, "WH DP Press")
	midstreampress = data.getValueAt(row, "Midstream Press")
	deliverypress1 = data.getValueAt(row, "Delivery Static1 Press")
	deliverypress2 = data.getValueAt(row, "Delivery Static2 Press")
	filtersep = data.getValueAt(row, "Filter Sep Press")
	filtersepdp = data.getValueAt(row, "Filter Sep DP Press")
	dewpoint = data.getValueAt(row, "Dew Point")
	choke = data.getValueAt(row, "Choke")
	brineflow = data.getValueAt(row, "Brine Flow")
	todaywater = data.getValueAt(row, "Today Water")
	yestwater = data.getValueAt(row, "Yest Water")
	tank1 = data.getValueAt(row, "Tank 1 Lvl")
	tank2 = data.getValueAt(row, "Tank 2 Lvl")
	tank3 = data.getValueAt(row, "Tank 3 Lvl")
	tank4 = data.getValueAt(row, "Tank 4 Lvl")
	
	logger.info("Variables Created")
	
	query2 = "INSERT INTO tblSystemReport VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
	
	logger.info("Query Created")
	
	system.db.runPrepUpdate(query2, [time, well, gpu, flowrate, totalgastdy, totalgasydy, tubingpress, casingpress, press958, press1338, surfacecasing, sandtrap, separator, whstatic, whdp, midstreampress, deliverypress1, deliverypress2, filtersep, filtersepdp, dewpoint, choke, brineflow, todaywater, yestwater, tank1, tank2, tank3, tank4], "idm_wellpad")
	logger.info("SQL Update executed")
logger.info("END")
5

system.tag.readAll is reading a list of tags, so it returns a list of qualified values. You will need to access each element in the list and then get the value key of the qualified value.
E.g.

tags = ["myTag1","myTag2"]
tagValues = system.tag.readAll(tags)
tagValue1 = tagValues[0].value
tagValue2 = tagValues[1].value
1 Like
6

Ok, I tried that but I must have missed something. I will try again. Thanks

7

As an aside, what is the use case for logging a big chunk of tag values into a database like this? Perhaps tag historian might suit your needs better?

8

In the comment editor, highlight/select all of the code that needs formatting. Then click the "Preformatted Text" button in the comment editor toolbar. The button icon looks like this: </>

{ You can go back and edit your comment, using the icon that looks like a pencil. }

9

I found it, thanks.

10

I have a power table on the screen and the data needs to put in SQL in the same tabular format so a third part company can extract it for reporting. (I dont think they even have access to the Ignition portion)

I have the button script which is much faster and cleaner, but it has to be executed by click on screen.
I need the update to be automated so I am stuck with a gateway script that can not retrieve the data from the power table component on the window.

So I have to rebuild it. -- If you have any suggestions of how to accomplish this in a better way, please let me know.

11

If you need to log a lot of PLC tags (I assume this is what is populating your table/dataset tag?) to SQL, you may want to look at the SQL bridge module.

https://docs.inductiveautomation.com/pages/viewpage.action?pageId=6047036

12

How is the table being populated now? That may go a long way towards optimizing.

13

You don't need to make your tag values into dataset, then iterate through the dataset.
After a bit of refactoring:

logger = system.util.getLogger("myLogger")
logger.info("Starting Script")

query1 = "DELETE FROM tblSystemReport"
system.db.runPrepUpdate(query1)

TAGset = [
            "gaswellsites/Midas/wells/2M/pad_short",
            "gaswellsites/midas/wells/2m/GPU2100/gpuid",
            "GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Inst Volume Flow Rate",
            "GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Total Volume Today",
            "GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Total Volume Yesterday",
            "GasWellSites/Midas/Wells/2M/W1100/Tub_Press/processValue",
            "GasWellSites/Midas/Wells/2M/W1100/Prod_Cas_Press/processValue",
            "GasWellSites/Midas/Wells/2M/W1100/Inter2_Cas_Press/processValue",
            "GasWellSites/Midas/Wells/2M/W1100/Inter1_Cas_Press/processValue",
            "GasWellSites/Midas/Wells/2M/W1100/Surface_Cas_Press/processValue",
            "GasWellSites/Midas/Wells/2M/GPU2100/Status/V-2X00 Pressure (Sand Separator)",
            "GasWellSites/Midas/Wells/2M/GPU2100/PIT-2X50_Pressure/processValue",
            "GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Static Pressure",
            "GasWellSites/Midas/Wells/2M/GPU2100/GasMeasurement/FIT-2X50 Differential Pressure",
            "GasWellSites/LogansFerry/Run_1/Pressure",
            "GasWellSites/LogansFerry/Run_1/Pressure",
            "GasWellSites/LogansFerry/Run_2/Pressure",
            "GasWellSites/LogansFerry/MeterValues/Site_Inlet_Pressure/processValue",
            "GasWellSites/LogansFerry/MeterValues/FilterSep_DiffPressure/processValue",
            "GasWellSites/LogansFerry/DewPoint/DewPoint",
            "GasWellSites/Midas/Wells/2M/GPU2100/Status/FV-2X20 Position Feedback (Auto Choke)",
            "GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Inst Volume Flow",
            "GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Total Volume Today",
            "GasWellSites/Midas/Wells/2M/GPU2100/ProducedWater/Total Volume Yesterday",
            "GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank1/Volume",
            "GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank2/Volume",
            "GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank3/Volume",
            "GasWellSites/Midas/Tank_Haul/Tank_Hauling/Tank4/Volume"
            ]

values = [system.date.now()] + [tag.value for system.tag.readAll(TAGset)]

logger.info("Variables Created")
	
query2 = "INSERT INTO tblSystemReport VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
	
logger.info("Query Created")
	
system.db.runPrepUpdate(query2, values, "idm_wellpad")
logger.info("SQL Update executed")
14

The table is being populated in a very poor way at the moment.

It has custom properties on a window for each row of the table, which uses tag history to pull the most recent historical record. *Basically a live value.
Then a shared script compiles all 41 rows (custom properties) together.
I can go into more detail if you'd like.

In this new script for entering the table into SQL, I am hoping to also write this data set to a dataset tag and bind the table to the dataset tag. - but then the script would be running constantly, but may be better than polling tag history for 1000+ tags every minute???

Also was thinking cell updates on the table, bound to the tag values.
** The goal of this table is to show live values of a bunch of tags.

image
image1228×491 127 KB

15

I would say to log your values to the db, then use a query to read the values from the table.
EDIT.: Ah I see you're pulling form tag history. A little more difficult but not impossible.

1 Like
16

As @amy.thompson mentioned above, correct?
The Transaction groups?

17

I do not need tag history, this is how it was setup before me, with only 3 rows, and it has grown with new well additions.
Live values is what I am interested in.

18

You don't have to use a transaction group necessarily, it can be done via scripting.

1 Like