Write data from database table to UDT tags

General Discussion
1

Hi Team,
I have a data in database table. I am fetching data from database and displaying in table on clicking on button. Database table column names and UDT tags names are same.

can you please guide how to write this database table data to UDT. I am working in Perspective mode.

image
image1516×813 93.5 KB

2

try this approach:
When ever you click on load button, write the query result dataset to a memory dataset [Name: Mem Dataset] tag,
and make other tags which are same as columns as expression tag that refers to their column name like,

{[.]Mem Dataset}[0, "MACHINE_ID"]
3

Thank you but sorry I didn't understood . I am not experienced. can you please guide in detail.

def runAction(self, event):

	sapordernumber = self.getSibling("TextArea").props.text
	query = "select top 1 * from KBETable where SAP_OPRDER_NUMBER = '"+sapordernumber+"'"
	
	# here's the command to run the query
	returnedData = system.db.runQuery(query)
		
		
	self.getSibling("Table").props.data = returnedData
	system.perspective.print(returnedData)
	
	# Convert to a PyDataset
	pyData = system.dataset.toPyDataSet(returnedData)
		 
		# The for loop pulls out the whole row, so typically the variable row is used.
	for row in pyData:
		    # Now that we have a single row, we can loop through the columns just like a list.
		for value in row:
		    system.perspective.print(value)
			    
	system.tag.writeBlocking("[default]Raptor_KBE_WebServices/KBE_UDT_Tags", value)

this is how I am trying

4

modify as
system.tag.writeBlocking("[default]Raptor_KBE_WebServices/MemoryDatasetTag", [returnedData])

5

I tried this still not working

6

system.tag.writeBlocking() requires a list of tagPaths and values. It would have to be something like:

system.tag.writeBlocking(["[default]Raptor_KBE_WebServices/MemoryDatasetTag"], [returnedData])
7 
MemoryDatasetTag"

this mean, do I need to create other tag then what data should I put. sorry

8

You will have to write to each of the tags within the UDT. You can do it in one call by create a list of tagPaths and values. In the example below I am assuming that you are successfully getting returned data. We create a list of tag paths and a list of values and then write those.

def runAction(self, event):

	sapordernumber = self.getSibling("TextArea").props.text
	query = "select top 1 * from KBETable where SAP_OPRDER_NUMBER = '"+sapordernumber+"'"
	
	# here's the command to run the query
	returnedData = system.db.runQuery(query)
		
		
	self.getSibling("Table").props.data = returnedData
	system.perspective.print(returnedData)
	
	# Convert to a PyDataset
	pyData = system.dataset.toPyDataSet(returnedData)
		 
		# The for loop pulls out the whole row, so typically the variable row is used.
	for row in pyData:
		    # Now that we have a single row, we can loop through the columns just like a list.
		for value in row:
		    system.perspective.print(value)
			    

	tagPaths = [
	"[default]Raptor_KBE_WebServices/SAP_OPRDER_NUMBER"
	]
	
	values = [
	returnedData[0]["SAP_OPRDER_NUMBER"]
	]

	system.tag.writeBlocking(tagPaths, values)

What exactly are you trying to accomplish here? There may be a better way to do it. What is the purpose of having to hit the button to push data to the UDT?

9

we are trying to fetch data from DB table and write in UDT tags.
DB table contains live Machine data. same live data we want to send to HMI screen.

first step we are trying this.
In our organization we have server called KBE(Knowledge base Engineering) which contains SAP machine data.
From KBE file we will send to DB-UDT-HMI.

10

I'm assuming you have multiple machines with different MACHINE_ID's. When will the update of the UDT be triggered?It seems like you're manually entering an order number and then using that to update the UDT. Will the data in the table change for a given order number once the UDT has been updated?

11

yes, I am entering SAP order number as filter, yes data will change/update in DB.
i need the method to write one row of data to UDT

12

Makes sense. I would recommend using a query tag. Instead of updating all the data to the UDT using system.tag.writeBlocking(), just update the SAP_OPRDER_NUMBER with the user entry. Then create a query tag that gets the row data for that order number. Finally turn all the existing tags into expression tags and have each on grab its value from the query tag.

13

thanks . I will try.
its an urgent requirement I need to try since its new for me

14

You can use tag bindings in the query tag query. Something like
select top 1 * from KBETable where SAP_OPRDER_NUMBER = '{[.]SAP_OPRDER_NUMBER}'