# Writing back to a database

**URL:** <https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [May 9, 2022, 2:27pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359 "2022-05-09T14:27:57Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 9, 2022, 2:27pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/1 "2022-05-09T14:27:57Z")

</div>

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?

```auto
	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)

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 9, 2022, 2:32pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/2 "2022-05-09T14:32:09Z")

</div>

> [@mjohnson0238](#):
>
> `#	ID = self.getSibling("Dropdown_Status").props.value`

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 9, 2022, 2:33pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/3 "2022-05-09T14:33:16Z")

</div>

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

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

```

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [May 9, 2022, 2:36pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/4 "2022-05-09T14:36:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 12:56pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/5 "2022-05-10T12:56:22Z")

</div>

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

````auto
        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.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 10, 2022, 1:44pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/6 "2022-05-10T13:44:54Z")

</div>

> [@mjohnson0238](#):
>
> 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.

```auto
#	ID = self.getSibling("Dropdown_Status").props.value (This line is commented out)

```

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

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 2:15pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/7 "2022-05-10T14:15:21Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 10, 2022, 2:42pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/8 "2022-05-10T14:42:55Z")

</div>

> [@mjohnson0238](#):
>
> 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](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.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 3:30pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/9 "2022-05-10T15:30:08Z")

</div>

So, in this case:

```auto
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]

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 10, 2022, 3:54pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/10 "2022-05-10T15:54:31Z")

</div>

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

```python
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.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 6:11pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/11 "2022-05-10T18:11:43Z")

</div>

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

```auto
	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?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 10, 2022, 6:14pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/12 "2022-05-10T18:14:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 6:20pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/13 "2022-05-10T18:20:18Z")

</div>

I found what you mean:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/2/62959a1dff93e24865c6e1a483b3fc0b66b4fd28.png)

heres the error

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 10, 2022, 6:33pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/14 "2022-05-10T18:33:42Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 10, 2022, 6:39pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/15 "2022-05-10T18:39:54Z")

</div>

> [@mjohnson0238](#):
>
> Can anyone tell me where Im going wrong on this?

```python
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

```python
id = self.session.custom.ID

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 6:44pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/16 "2022-05-10T18:44:11Z")

</div>

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 = ?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 10, 2022, 6:47pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/17 "2022-05-10T18:47:20Z")

</div>

> [@mjohnson0238](#):
>
> To unhardcode my parameter, would i simply remove the hardcode and replace them like this:  
> assignedto = ?

You would do this:

```python
	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')

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 7:59pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/18 "2022-05-10T19:59:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 8:00pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/19 "2022-05-10T20:00:44Z")

</div>

here is what Ive tried so far,

```auto
	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')

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [May 10, 2022, 8:29pm UTC](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359/20 "2022-05-10T20:29:09Z")

</div>

Ive figured a solution:

I run a script on the dropdown like this:

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

```

[Next page](https://forum.inductiveautomation.com/t/writing-back-to-a-database/59359.md?page=2)