# Error when attempting to edit a boolean value linked to a MySQL database

**URL:** <https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977>\
**Category:** Ignition\
**Tags:** perspective, scripting\
**Created:** [June 13, 2023, 7:33pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977 "2023-06-13T19:33:58Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cameron\_Nickle](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@Cameron\_Nickle](https://forum.inductiveautomation.com/u/Cameron_Nickle)\
**Post date:** [June 13, 2023, 7:33pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/1 "2023-06-13T19:33:58Z")

</div>

Hi all. I am still new to the ignition software and its scripting functionality. I'm attempting edit a Boolean value through the perspective table component and have it write back to the database.

Im using a script under the "event: onEditCellCommit" that looks like this:

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

I created columns in my table that are set to editable  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/1/31bdb7c89ecdaca325c0229e0a6260182d584244.png)

and i also was sure to activate the "enableRowSelection" and "enableColumnSelection" attributes.

The database connection is working as I've been able to add rows and query them, however when I try to edit the isActive columns bool value in my table (which looks like this):

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/5/15efd94425b029bea2ada7a429d0329bf5fe106b.png)

I'm presented with this error.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/b/0be48dd6c3bc230322a5f479ceae2f8724484c57.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/3/833eccd975f3870216cbfb676aa3b7b539d7b5aa.png)

How can I go about fixing this issue and if possible could someone explain my oversight to me so I can more fully understand what it is I'm trying to do vs what I'm actually doing.

Thank you,

Cam

---

<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:** [June 13, 2023, 7:38pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/2 "2023-06-13T19:38:15Z")

</div>

~~First off, event.source.data is a dataset, not a pyDataset. You have to convert to a pyDataset to use indexes using `system.dataset.toPyDataSet()`. That doesn't solve your problem though. You have to actually run a named query, or a prep update to modify the database to keep the change.~~

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 13, 2023, 7:55pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/3 "2023-06-13T19:55:37Z")

</div>

> [@dkhayes117](#):
>
> You have to convert to a pyDataset to use indexes using `system.dataset.toPyDataSet()`.

I don't think that's correct. This code works for me in Designer and prints the correct value out to Output Console.

```python
def runAction(self, event):
	self.props.data[event.row][event.column] = event.value
	system.perspective.print(self.props.data[event.row][event.column])

```

Result:

```python
20:53:34.328 [Browser Thread: 51737] INFO Perspective.Designer.Workspace - true
20:53:34.880 [Browser Thread: 51737] INFO Perspective.Designer.Workspace - false

```

---

<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:** [June 13, 2023, 7:56pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/4 "2023-06-13T19:56:47Z")

</div>

oof, I flashed back to Vision. Gotta reboot...

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 13, 2023, 7:58pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/5 "2023-06-13T19:58:35Z")

</div>

> [@Cameron\_Nickle](#):
>
> I'm presented with this error.

Is there any more on the Details tab?  
Tip post the errors as text formatted with the \</\> button rather than post as a screengrab. The important bit is probably at the end of the first line.

---

<div class="post-metadata">

**Author:** ![Cameron\_Nickle](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@Cameron\_Nickle](https://forum.inductiveautomation.com/u/Cameron_Nickle)\
**Post date:** [June 13, 2023, 8:13pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/6 "2023-06-13T20:13:43Z")

</div>

com.inductiveautomation.ignition.common.script.JythonExecException  
Traceback (most recent call last):  
File "function:runAction", line 2, in runAction  
TypeError: 'com.inductiveautomation.ignition.gateway.datasource.BasicStreamingDataset' object is unsubscriptable

```
at org.python.core.Py.TypeError(Py.java:236)
at org.python.core.PyObject. __finditem__ (PyObject.java:653)
at org.python.core.PyObjectDerived. __finditem__ (PyObjectDerived.java:920)
at org.python.core.PyObject. __getitem__ (PyObject.java:717)
at org.python.core.PyObjectDerived. __getitem__ (PyObjectDerived.java:960)
at org.python.pycode._pyx13.runAction$1(<function:runAction>:3)
at org.python.pycode._pyx13.call_function(<function:runAction>)
at org.python.core.PyTableCode.call(PyTableCode.java:173)
at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
at org.python.core.PyFunction.function ___call__ (PyFunction.java:474)
at org.python.core.PyFunction. __call__ (PyFunction.java:469)
at org.python.core.PyFunction. __call__ (PyFunction.java:464)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:846)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:828)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:832)
at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1009)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:897)
at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:158)
at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:97)
at com.inductiveautomation.perspective.gateway.action.ScriptAction.runAction(ScriptAction.java:74)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.lambda$call$0(ActionCollection.java:263)
at com.inductiveautomation.perspective.gateway.api.LoggingContext.mdc(LoggingContext.java:54)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:252)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:221)
at com.inductiveautomation.perspective.gateway.threading.BlockingTaskQueue$TaskWrapper.run(BlockingTaskQueue.java:154)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)
at java.base/java.lang.Thread.run(Unknown Source)

```

Caused by: org.python.core.PyException  
Traceback (most recent call last):  
File "function:runAction", line 2, in runAction  
TypeError: 'com.inductiveautomation.ignition.gateway.datasource.BasicStreamingDataset' object is unsubscriptable

```
... 31 more

```

Ignition v8.1.27 (b2023042509)  
Java: Azul Systems, Inc. 11.0.18\

I think this is what youre asking for. If not could you give me more specific steps so I can get exactly what you need?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 13, 2023, 8:25pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/7 "2023-06-13T20:25:52Z")

</div>

Datasets aren't directly mutable.  
You need to change your script to something like:  
`self.props.data = system.dataset.setValue(self.props.data, event.row, event.column, event.value)`  
Or change your query return type to JSON so that the table data is "exploded" into a JSON like format this is directly mutable.

Note that doing _either_ of those things will still only update _the local data_. If you want to update the actual table in your database, you need a more involved script; you should run an UPDATE query from `onEditCellCommit` and then `refresh("props.data")` once you've issue the update.

---

<div class="post-metadata">

**Author:** ![Cameron\_Nickle](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@Cameron\_Nickle](https://forum.inductiveautomation.com/u/Cameron_Nickle)\
**Post date:** [June 13, 2023, 8:52pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/8 "2023-06-13T20:52:25Z")

</div>

Thank you very much! The first part of this was very clear, the second part I'm a little unclear on. How would I go about writing the update query in the event scripting section? Also what do you mean by refresh("props.data"), and how do I do that in the script?

---

<div class="post-metadata">

**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\
**Post date:** [June 13, 2023, 8:59pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/9 "2023-06-13T20:59:58Z")

</div>

I like to use named queries with [system.db.runNamedQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery).

You can refresh the table like this:

```auto
self.getSibling("Table").refreshBinding("props.data")

```

---

<div class="post-metadata">

**Author:** ![Cameron\_Nickle](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@Cameron\_Nickle](https://forum.inductiveautomation.com/u/Cameron_Nickle)\
**Post date:** [June 13, 2023, 9:32pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/10 "2023-06-13T21:32:02Z")

</div>

Thank you very much! That answers all my questions 😀

---

<div class="post-metadata">

**Author:** ![Cameron\_Nickle](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@Cameron\_Nickle](https://forum.inductiveautomation.com/u/Cameron_Nickle)\
**Post date:** [June 13, 2023, 9:35pm UTC](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/11 "2023-06-13T21:35:20Z")

</div>

> [@Error when attempting to edit a boolean value linked to a MySQL database](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/7):
>
> Datasets aren't directly mutable. You need to change your script to something like: self.props.data = system.dataset.setValue(self.props.data, event.row, event.column, event.value) Or change your query return type to JSON so that the table data is "exploded" into a JSON like format this is directly mutable. Note that doing either of those things will still only update the local data. If you want to update the actual table in your database, you need a more involved script; you should run an U…

> [@Error when attempting to edit a boolean value linked to a MySQL database](https://forum.inductiveautomation.com/t/error-when-attempting-to-edit-a-boolean-value-linked-to-a-mysql-database/74977/9):
>
> I like to use named queries with [system.db.runNamedQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery). You can refresh the table like this: self.getSibling("Table").refreshBinding("props.data")