Writing data from SQL query result to a dataset tag

Ignition
, , , ,
1

Hello All,

I am getting dataset ([1R ⅹ 514C]) from a named query. I need to loop through this named query dataset and write this data to a dataset tag in selected columns only (Setpoint ,HiHiLimit , HiLimit , LoLimit , LoLoLimit).
Below is the tag structure.

Dataset Tag Structure
Dataset Tag Structure807×224 23.4 KB

Thanks in advance.

2

You need all the rows but only a few of the columns?

system.dataset.filterColumns should be able to do that: system.dataset.filterColumns - Ignition User Manual 8.1 - Ignition Documentation

1 Like
3

I am reading 514 recipe parameters from SQL table and creating one dictionary. Below is the code for the same.

dsResult 		= system.db.runNamedQuery('Recipe/selectRecipe', parameters)
if dsResult.rowCount > 0:
	dictParameter 	= {}
	for row in range(dsResult.rowCount):
		for col in system.dataset.getColumnHeaders(dsResult):
			key 	= str(col)
			value 	= dsResult.getValueAt(row , col)
			if type(value) == float:
				dictParameter[key] = str(round(value , 2))
			else:
				dictParameter[key] = str(value)

So based on this dictionary I want to update each row of dataset tag with respective values from the database. Can you give me psuedo code for it.

4

So you are reading in a SQL query via a named query, which is a dataset, unpacking that into a dict and then want to repack that dict into a dataset? Why not just take the original dataset from the query and manipulate that into the final dataset. Or better yet just use a query tag to get the data directly from the SQL database into a dataset tag?

5

How is the data coming in from the named query? This is beginning to look like a pivot.

1 Like
6

Well I get your point, but on this dataset tag, my UI components also depend. Above code which I pasted in earlier reply is part of large recipe function script. Query tag to dataset tag is not suitable.

7

UI components shouldn't be a part of the considerations when designing data handling.
Nothing prevent your UI from using the tag as its data source.

You showed us the required output, but what does the input looks like ?

4 Likes
8

OK... so let's backup. And a part of @pascal.fragnoud comment...

Can you describe what the entire system needs to do? And how it is being done currently? That would help us help you.

Sometimes the best way to handle things is to backup and re-evaluate the problem and solution.

1 Like
9

I have been working around this topic and finally closed this. From the dictionary I have accessed individual parameters and stored them into variable and the using system.dataset.updateRow function I have written those values to dataset tag.

Still I wanted to optimize this process of individually reading each parameter from dictionary and updating them into dataset tag (May be a for loop code or something).