Writing back to a database

Good day group! Im trying to write back to a database using the following code, it doesnt work and I cannot see why, any tips?

	AssignedTo = self.getSibling("Dropdown_Assigned").props.value
	Status = self.getSibling("Dropdown_Status").props.value
#	ID = self.getSibling("Dropdown_Status").props.value
#	Priority = 
	
	system.db.runPrepUpdate("UPDATE ShiftMaintWO SET AssignedTo = ?, Status = ?, Priority = ? WHERE ID = ?",[AssignedTo, Status, Priority, ID,])
	
	CompleteTime = system.date.now()
	CompleteShift = system.tag.read("Twisters/Current Shift").value
	CompleteUser = system.security.getUsername()
	
	if Status == 2:
		system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID,])	
	
	window = system.nav.openWindow('Main Window')
	system.nav.centerWindow(window)
	system.nav.closeParentWindow(event)

Is that line commented in your code ?
Is ID defined somewhere else ?

First, check your parameters for the queries and make sure they are what you expect.

print 'AssignedTo: %s, Status: %s, Priority: %s, ID: %s' % (AssignedTo,Status, Priority, ID)

Also, do you have the ID variable assignment commented out?

You also need to specify the database connection, as the code runs in the gateway.

2 Likes

Good day! I have additional info for this topic, this was originally a project my coworker was working on and now has been passed to me. I have an ‘assign’ button with the script

        AssignedTo = self.getSibling("Dropdown_Assigned").props.value
	Status = self.getSibling("Dropdown_Status").props.value
#	ID = self.getSibling("Dropdown_Status").props.value
#	Priority = 
	
	system.db.runPrepUpdate("UPDATE ShiftMaintWO SET AssignedTo = ?, Status = ?, Priority = ? WHERE ID = ?",[AssignedTo, Status, Priority, ID,])
	
	CompleteTime = system.date.now()
	CompleteShift = system.tag.read("Twisters/Current Shift").value
	CompleteUser = system.security.getUsername()
	
	if Status == 2:
		system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID,])	
	
	window = system.nav.openWindow('Main Window')
	system.nav.centerWindow(window)
	system.nav.closeParentWindow(event)```

on it. My co worker says the original idea was to have that populate this query table

SELECT
WorkOrder
,SubmitShift AS ‘Shift’
,SubmitTime
,Equipment
,Description
,AssignedTo
,CASE
WHEN Status = 1 THEN ‘Open’
WHEN Status = 2 THEN ‘Complete’
WHEN Status = 3 THEN ‘Hold’
When Status = 4 THEN ‘Active’
END AS ‘Status’
,SubmitUser
,Priority
,ID
FROM ShiftMaintWO
WHERE Status = 1
OR Status = 3
OR Status = 4
OR SubmitTime > dateadd(hour, -8, getdate())
ORDER BY CASE
WHEN Status = 1 THEN 2
WHEN Status = 2 THEN 4
WHEN Status = 3 THEN 1
When Status = 4 THEN 3
END ASC```

I hope that clears up any confusion.

Not really, you still have your ID variable commented out, you didn’t say anything about whether you tried to print out your parameters before running the prepUpdate to see if they are expected values, and you didn’t include the database connection in the prepUpdate as suggested…

The # symbol in the script causes the line to be commented out, meaning it is not run as part of the script.

#	ID = self.getSibling("Dropdown_Status").props.value  (This line is commented out)
database = 'nameOfMyDBConnection'
system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID,], database = database)

I have also found out that this script for the button is being used in a vision client:

AssignedTo = event.source.parent.getComponent('AssignSelect').selectedStringValue
Status = event.source.parent.getComponent('StatusSelect').selectedValue
ID = event.source.parent.ID
Priority = event.source.parent.getComponent('PriorityDropDown').selectedValue

system.db.runPrepUpdate("UPDATE ShiftMaintWO SET AssignedTo = ?, Status = ?, Priority = ? WHERE ID = ?",[AssignedTo, Status, Priority, ID,])

CompleteTime = system.date.now()
CompleteShift = system.tag.read("Twisters/Current Shift").value
CompleteUser = system.security.getUsername()

if Status == 2:
	system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID,])	

window = system.nav.openWindow('Main Window')
system.nav.centerWindow(window)
system.nav.closeParentWindow(event)

and works like it should. I suppose I am having a hard time understanding the script differences in vision vs perspective.

The biggest difference is that scripts in perspective run in a Gateway scope.

When you run some functions like system.db.runPrepUpdate in Gateway scope you will need to provide additional information. In this instance what database connection to run the query against.

When running in a vision client, if you don’t provide the database connection, then the default connection is assumed. Thus the script works in a vision client, but not in perspective.

See the syntax section on this page in the manual:
https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepUpdate

Notice how in the Gateway scope the database parameter is required and how in vision client scope the database parameter is optional (denoted by the [ ] surrounding it?

I will give you that it is a bit confusing that it also state that syntax will work in perspective session scope, in my experience it’s best to assume that code will be running in the Gateway.

Get in the habit of always providing the database and you wont run into this particular problem.

1 Like

So, in this case:

system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID])

what I have in the should be the table in trying to update, ShiftMaintWO or should it be what I have now, including ,[ShiftMaintWO]

No, it shold be the name of the database connection to the database that has the Table ShiftMaintWO.

system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID], `database connection name`)

To get the name of the database connection you can find that on the Gateway at Config->Databases->Connections The name in the “Name” column is what you want to use.

So I was thinking about trying to use a runNamedQuery. I have this so far:

	assignedto = self.getSibling("Dropdown_Assigned").props.value
	status = self.getSibling("Dropdown_Status").props.value
	id = self.getSibling(self.session.custom.ID).props.value
	priority = self.getSibling("Dropdown_Prority").props.value
	namedQuery = "WOupdate"
	parameters = {"assignedto":"bob", "status":1, "priority":3, "id":45022}
		
	system.db.runNamedQuery(namedQuery, parameters)
	
#	CompleteTime = system.date.now()
#	CompleteShift = system.tag.read("Twisters/Current Shift").value
#	CompleteUser = system.security.getUsername()
	
#	if Status == 2:
#		system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID,], database = database)
	
	system.perspective.navigate('/wo_assign')

If i comment out everything except the navigate portion the button does navigate, so I know I have a Scripting error in the system.db.runNamedQuery. Can anyone tell me where Im going wrong on this?

What error are you getting?

In google chrome Control+Shift+I shows the browser console (or console in your Designer). Or it might appear in the gateway server logs - not 100% sure but its going to be in one of those two.

I found what you mean:

heres the error

Without your full script it’s hard to tell what line 4 is, I am guessing it is this though

id = self.getSibling(self.session.custom.ID).props.value

as .getSibling is trying to coerce a value into a string but it can’t, and the rest of your lines have hardcoded strings so it is not them (you would get a different sort of error if you did something like self.getSibling('thisDoesntExists").props.value, I’d guess saying NoneType has no props.). Since you can see the line numbers, it’s on line 4.

self.getSibling(self.session.custom.ID).props.value

This is not how you would reference a session property. self.getSibling() is used to retrieve another component in the same container as this component. The correct syntax would be something like

id = self.session.custom.ID

OK, when I # out the line 4, the script runs and puts the hardcoded values into the table.

To unhardcode my parameter, would i simply remove the hardcode and replace them like this:
assignedto = ?

You would do this:

	assignedto = self.getSibling("Dropdown_Assigned").props.value
	status = self.getSibling("Dropdown_Status").props.value
	id = self.session.custom.ID #corrected to what I would expect the syntax to be
	priority = self.getSibling("Dropdown_Prority").props.value
	namedQuery = "WOupdate"
	parameters = {"assignedto":asignedto, "status":status, "priority":priority, "id":id}
		
	system.db.runNamedQuery(namedQuery, parameters)
	
#	CompleteTime = system.date.now()
#	CompleteShift = system.tag.read("Twisters/Current Shift").value
#	CompleteUser = system.security.getUsername()
	
#	if Status == 2:
#		system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID,], database = database)
	
	system.perspective.navigate('/wo_assign')
1 Like

lrose, Thanks. That writes. When I select a row in that table, it populates some dropdowns and if i do not reselect data for those dropdowns it writes nothing to that table. How can I make sure it keeps that prepopulate data in the dropdowns and rewrites to the table? like if i had to modify one of dropdowns, but not the others.

here is what Ive tried so far,

	assignedto = self.getSibling("Dropdown_Assigned").props.value
	
	if self.getSibling("Dropdown_Assigned").props.value is None:
		assignedto = self.session.custom.ASSIGNED
	
	status = self.getSibling("Dropdown_Status").props.value
	
	if self.getSibling("Dropdown_Status").props.value is None:
		status = self.session.custom.STATUS
	
	priority = self.getSibling("Dropdown_Prority").props.value
	
	if self.getSibling("Dropdown_Priority").props.value is None:
	priority = self.session.custom.PRIORITY
		
	id = self.session.custom.ID
	
	namedQuery = "WOupdate"
	parameters = {"assignedto":assignedto, "status":status, "priority":priority, "id":id}
		
	system.db.runNamedQuery(namedQuery, parameters)
	
#	CompleteTime = system.date.now()
#	CompleteShift = system.tag.read("Twisters/Current Shift").value
#	CompleteUser = system.security.getUsername()
	
#	if Status == 2:
#		system.db.runPrepUpdate("UPDATE ShiftMaintWO SET CompleteTime = ?, CompleteShift = ?, CompleteUser = ? WHERE ID = ?",[CompleteTime, CompleteShift, CompleteUser, ID,], database = database)
	
	system.perspective.navigate('/wo_assign')

Ive figured a solution:

I run a script on the dropdown like this:

self.props.value = self.session.custom.ASSIGNED