# Saving user configuration property object to SQL nvarchar and retrieving it again

**URL:** <https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444>\
**Category:** Ignition\
**Tags:** database, perspective\
**Created:** [March 11, 2026, 2:43pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444 "2026-03-11T14:43:41Z")\
**Posts on this page:** 17\
**Page:** 1

<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:** [March 11, 2026, 2:43pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/1 "2026-03-11T14:43:41Z")

</div>

I'm suffering from Engineer's Block. (Well, if writer's can suffer their version then why can't we. Try it on your boss.)

My application has some user configuration checkboxes linked to a `view.custom.mainTableShowColumns' and (the abbreviated version) looks like

```python
{
  "process": true,
  "notes": true,
  "meta": false
}

```

1. How do I script to convert that to a string to store in an MSSQL nvarchar column?
2. How do I get it back to load the user's preferences back into `view.custom.mainTableShowColumns'?

My attempts so far either have `<ObjectWrapper>` and Unicode 'u' designators attached, or else store the qualified values of each of the object members.

Many thanks.

---

<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:** [March 11, 2026, 2:47pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/2 "2026-03-11T14:47:54Z")

</div>

With MSSQL I used a NVARCHAR(MAX) column and `system.util.jsonEncode` to save to the db and `system.util.jsonDecode` when retrieving it out and it works well for me so far. That’s for me saving a python dictionary - I dont’ know if there are any special gotchas in your situation here, I know perspective objects are all wrapped up in stuff but if you’re able to get it into a dictionary and go from dictionary to the perspective object - I think the above should be fine.

If it is static ie always just three column process/notes/meta - I would opt for a column per field though. Always nice to ensure data integritywhen possible. This would also make probably make retrieval simpler.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 11, 2026, 3:34pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/3 "2026-03-11T15:34:23Z")

</div>

What Brian said.  
You may need to un-qualify the Perspective prop to a jython dict to feed to `.jsonEncode()`.

---

<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:** [March 11, 2026, 3:41pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/4 "2026-03-11T15:41:06Z")

</div>

Thanks, Phil. Unfortunately your Integration Toolkit is not available on this customer's gateway. What's the long-hand approach to achieve the same result?

---

<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:** [March 11, 2026, 3:42pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/5 "2026-03-11T15:42:29Z")

</div>

> [@bkarabinchak.psi](#):
>
> I would opt for a column per field though.

I understand, but in this case I'm trying to make it a generic user preference storage so there could be all sorts of stuff in there.

Thanks.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 11, 2026, 3:46pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/6 "2026-03-11T15:46:24Z")

</div>

Recursive function, similar to this:

```python
from java.util import Map
from com.inductiveautomation.ignition.common.model.values import QualifiedValue
try:
	pHook = system.util.toolkitCtx().getModule('com.inductiveautomation.perspective')
	objWrapper = pHook.class.classLoader.loadClass("com.inductiveautomation.perspective.gateway.script.PropertyTreeScriptWrapper$ObjectWrapper")
	mappings = (dict, Map, objWrapper)
except:
	mappings = (dict, Map)

# Strip qualified values out of hierarchical entities.
def deep(x):
	if x is None:
		return None
	if isinstance(x, QualifiedValue):
		return deep(x.value)
	if isinstance(x, basestring):
		return x
	if isinstance(x, mappings):
		x = dict(x)
		retv = dict()
		for k, v in x.items():
			retv[k] = deep(v)
		return retv
	try:
		return [deep(v) for v in x]
	except:
		return x

```

You'll need to use another method to get the gateway context.

(I wrote the above for use in the tag report utility in the Exchange, before I added the scripting and expression [unQualify()](https://www.automation-pros.com/toolkit/doc/scripting.html#unqualify) functions.)

---

<div class="post-metadata">

**Author:** ![Nol](https://avatars.discourse-cdn.com/v4/letter/n/8e8cbc/32.png) [@Nol](https://forum.inductiveautomation.com/u/Nol)\
**Post date:** [March 11, 2026, 3:46pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/7 "2026-03-11T15:46:47Z")

</div>

I'm doing something similar to that for storing some trend related properties per user. You're welcome to try and decipher and see if this helps you. This packages the config into a message handler, sends it to a remote gateway, and executes a namedquery to save/load it on the other gateway's database.

Fair warning - AI supported coding, so may not be best practices but it currently works well for me.

```python
def savequickpoints(UserName, Data):
	"""
	Saves the quickpoints dataset to the database as a JSON string.
	"""
	# 1. Build the list from the dataset
	points_list = []
	
	if Data is not None:
		py_data = system.dataset.toPyDataSet(Data)
		
		for row in py_data:
			points_list.append({
				"Pad": row["Pad"],
				"Pen Name": row["Pen Name"],
				"Description": row["Description"],
				"Path": row["Path"]
			})

	# 2. Encode to JSON
	# THIS IS THE CRITICAL STEP. 
	# We must convert the list of objects into a text string.
	json_string = system.util.jsonEncode(points_list)

	# DEBUG: Print this to the console to confirm it looks like text, not "Dataset [...]"
	print "Saving the following string to SQL:", json_string

	# 3. Call the remote data handler
	return get_remote_data(
		query_path="custom_trend/savequickpoints",
		database="-redacted-",
		UserName = UserName,
		Data = json_string 
	)	

def loadquickpoints(UserName):
	"""
	Fetches the JSON string from SQL and converts it to a Dataset.
	"""
	# 1. Fetch the data
	result = get_remote_data(
		query_path="custom_trend/loadquickpoints",
		database="-redacted-",
		UserName = UserName
	)

	# 2. Sanitize the Result (Handle Dataset vs String)
	json_string = ""
	
	if result is None:
		return None
	
	# Check if result is an Ignition Dataset (common if Query Type is 'Select')
	if hasattr(result, 'getValueAt'):
		if result.getRowCount() > 0:
			json_string = result.getValueAt(0, 0) 
		else:
			return None
	else:
		# Assume it is already the scalar string
		json_string = result

	# 3. Decode JSON
	if not json_string:
		return None

	try:
		data_structure = system.util.jsonDecode(json_string)
	except:
		# Log the error if needed, or just return None
		return None

	# 4. Normalize to List
	# This prevents the 'unicode' error by ensuring we are looping over a list of objects,
	# not iterating over the keys of a single dictionary.
	if isinstance(data_structure, dict):
		data_list = [data_structure]
	elif isinstance(data_structure, list):
		data_list = data_structure
	else:
		return None # Unknown format

	# 5. Convert to Dataset
	headers = ["Pad", "Pen Name", "Description", "Path"]
	rows = []
	
	for item in data_list:
		# Critical Check: ensure the item is a dictionary before calling .get()
		if isinstance(item, dict):
			rows.append([
				item.get("Pad"),
				item.get("Pen Name"),
				item.get("Description"),
				item.get("Path")
			])
			
	return system.dataset.toDataSet(headers, rows)

```

The named queries are just:

```python
loadquickpoints:
SELECT Data
FROM custom_quickpoints
WHERE UserName=:UserName

```

```python
savequickpoints:
IF EXISTS (SELECT 1 FROM custom_quickpoints WHERE UserName = :UserName)
BEGIN
    -- User exists: Update their existing record
    UPDATE custom_quickpoints
    SET Data = :Data
    WHERE UserName = :UserName
END
ELSE
BEGIN
    -- New User: Insert a new record
    INSERT INTO custom_quickpoints (UserName, Data)
    VALUES (:UserName, :Data)
END

```

Edit: The on change script that is attached to session.custom.quickpoints:

```python
def valueChanged(self, previousValue, currentValue, origin, missedEvents):
	UserName = self.props.auth.user.userName
	Data = self.custom.quickpoints
	if UserName:
	    remote_utils.savequickpoints(UserName, Data)

```

---

<div class="post-metadata">

**Author:** ![djs](https://avatars.discourse-cdn.com/v4/letter/d/e47774/32.png) [@djs](https://forum.inductiveautomation.com/u/djs)\
**Post date:** [March 11, 2026, 5:01pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/8 "2026-03-11T17:01:23Z")

</div>

nvarchar(max) will work perfectly fine. Since you're using MSSQL however, you can consider using its [JSON datatype](https://learn.microsoft.com/en-us/sql/t-sql/data-types/json-data-type?view=sql-server-ver17) which is optimized in different ways.

---

<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:** [March 12, 2026, 2:10pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/9 "2026-03-12T14:10:01Z")

</div>

When you do a insert with a named query do you use a String param type? With a prep query is it still just doing system.util.jsonEncode? Also retrieval do you still have to decode? Just curious how this plays with Ignition

---

<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:** [March 12, 2026, 3:13pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/10 "2026-03-12T15:13:24Z")

</div>

> [@bkarabinchak.psi](#):
>
> When you do a insert with a named query do you use a String param type?

That was my plan.

I'm rather discouraged with the complexity of what I thought would be a simple task - to convert a single layer deep dictionary into a string - only to find that the values are all qualified. At this stage I'm thinking that something like,

```python
concat(
	'{',
	'"process": ', {value}['process'], ', ',
	'"notes": ', {value}['notes'], ', ',
	'"meta": ', {value}['meta'],
	'}'
)

```

would be much simpler but at this stage I'm wondering why bother saving it as a JSON string at all if it's so complicated to pass it in and out of the database table. I might as well just do

```python
concat(
	'process: ', {value}['process'], '; ',
	'notes: ', {value}['notes'], '; ',
	'meta: ', {value}['meta'], ';'
)

```

to return a string of the form,  
`process: true; notes: true; meta: false;`  
and store that instead. It would be easy to split on retrieval.

---

<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:** [March 12, 2026, 3:56pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/11 "2026-03-12T15:56:31Z")

</div>

I do it with a

```python
system.db.runPrepUpdate(“INSERT INTO table (pythonDict) VALUES (?)”, [system.util.jsonEncode(myDict)])

```

but you would still need to handle converting it to a regular dict first without the qualified values and then getting it out convert back to one with qualified values presumably to make it work right.

My question was actually towards @djs about the JSON type I didn’t know how nicley the JBDC worked with that and if it that had extra considerations though this would have little to do with your current problem of nested qualified values.

I’d opt for @pturmel’s script above, use it to convert programatically to a dict and then you can use a query like I just wrote. Going back the otherway though 🤷 Go back through your dictionary casting everything as a `QualifiedValue` I guess?

---

<div class="post-metadata">

**Author:** ![djs](https://avatars.discourse-cdn.com/v4/letter/d/e47774/32.png) [@djs](https://forum.inductiveautomation.com/u/djs)\
**Post date:** [March 12, 2026, 4:12pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/12 "2026-03-12T16:12:23Z")

</div>

Yeah, you'd just use a String param type. The database would handle parsing the JSON and validating it, but in terms of IO it's a varchar. Here's the basic flow:

1. Encode your dict with system.util.jsonEncode() into a json string.
2. Pass to a named query as a string or to a prep query to store it in the DB.
3. Retrieve the json string from the DB (do modifications on it in SQL if you want).
4. Use system.util.jsonDecode() to decode the retrieved json string back into a dict.

---

<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:** [March 12, 2026, 4:23pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/13 "2026-03-12T16:23:17Z")

</div>

Well that was easy! It looks like I don't need to un-qualify the Perspective prop.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/0/b0a99cc7565ff4853faebb0c236ea159c9a49404.png)  
_Figure 1. JSON encoding of the dictionary._

And jsonDecode() seems to work fine on the way back out too.

Thanks, guys.

---

<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:** [March 12, 2026, 4:29pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/14 "2026-03-12T16:29:03Z")

</div>

So it works just like the nvarchar(max) from the ignition side its just the db optimizes on its end, nice! Good tidbit.

---

<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:** [March 17, 2026, 8:03pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/15 "2026-03-17T20:03:03Z")

</div>

Next problem:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/7/67470301bf9a24b3be92166416460012cab8b504.png)  
_Figure 1. View in Designer_  
(1) is a query binding to what's stored in the database.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/7/47ca38ee7ee408f7656f94516eab7c1d30f234b6.png)  
_Figure 2. View in the browser._

Can anyone think of a reason the data is returned as an escaped string in the browser?

It's the same on Chrome and Edge browsers.

---

<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:** [March 19, 2026, 1:18pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/16 "2026-03-19T13:18:07Z")

</div>

I don’t know that you can do a query binding since you will need to do a `system.util.jsonDecode` on the column

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 19, 2026, 1:22pm UTC](https://forum.inductiveautomation.com/t/saving-user-configuration-property-object-to-sql-nvarchar-and-retrieving-it-again/114444/17 "2026-03-19T13:22:07Z")

</div>

> [@Transistor](#):
>
> Can anyone think of a reason the data is returned as an escaped string in the browser?

Is your query binding returning JSON?