Jython Script to read Internal Database using javadocs

[CJ_Johnson]

My team currently has an automation setup to quickly add devices to gateways but we need a way to query the list of configured devices and their connection paths and other parameters.

As of now, I can access the the parameters I need by manually running SQL queries via the web UI @ main/web/status/sys.internaldb?10

Example of the queries
DEVICESETTINGS.NAME as name,
LOGIXDRIVERSETTINGS.HOSTNAME as logix_hostname,
COMPACTLOGIXDRIVERSETTINGS.HOSTNAME as compactlogix_hostname,
CONTROLLOGIXDRIVERSETTINGS.HOSTNAME as controllogix_hostname,
S71500DRIVERSETTINGS.HOSTNAME as siemens_hostname,
LOGIXDRIVERSETTINGS.SLOTNUMBER as logix_slot,

I have many gateways to go through and automating this effort would be great.

Is there a way to read tables off the internal database and use the result in a Jython script using the javadocs?

For example for the devicesettings table, I would want to iterate over each device on the gateway and record the following fields (description, enable, general,id,name,type) described here. ignition 7.9.5 API
com.inductiveautomation.xopc.driver.api.configuration.DeviceSettingsRecord

[paul-griffith]

There's no supported way to access the internal database at runtime, but there's some methods floating around the forum. Start from the PersistenceInterface class.

[CJ_Johnson]

I'm aware that my ask is not officially supported.
I can't quite find a post that mirrors what I'm attempting. Most examples I've seen are java solutions while I'm looking for Python/Jython.

From what I've read, I came up with this

from com.inductiveautomation.ignition.gateway import SRContext
from com.inductiveautomation.xopc.driver.api.configuration import DeviceSettingsRecord
import simpleorm.dataset.SQuery as SQuery
import simpleorm.dataset.SRecordMeta as SRecordMeta

def run():
	query = SQuery(DeviceSettingsRecord.META).ascending(DeviceSettingsRecord.Name)
	results = SRContext.get().getPersistenceInterface().query(query)
	test = []
	for result in results:
		test.append(result.getName())
	logger.info("%s" % (test))
	return test

I'm trying to run this from the gw but keep getting errors trying to import DeviceSettingsRecord.

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 9, in ImportError: Error loading module getNames: Traceback (most recent call last): File "", line 2, in ImportError: No module named xopc
at org.python.core.Py.ImportError(Py.java:304)

[pturmel]

Most PI record implementations are module-specific, and can only be obtained when in that module's classloader environment. So, the OPC UA server module for DeviceSettingsRecord itself, and each driver module for their driver-specific extension records.

[paul-griffith]

On the gateway, modules are each in separate, segmented classloaders, so you won't easily be able to retrieve instances of module specific classes. You're probably better off with a 'raw' query - you'll have to open a session in your code (make sure you close it!) and then call one of the methods that returns a dictionary or list of dictionaries:

SSessionJdbc

[CJ_Johnson]

Would you be able to assist with the proper syntax for the raw query? I'm not sure how to proceed.

I would have to import

import simpleorm.dataset.SSessionI as SSession

than something like this?

query = SSession.query(SELECTDEVICESETTINGS.NAME as name)

[pturmel]

No, you cannot use .query() without the ORM classes. You must use one of the .rawQuery*() methods.

[paul-griffith]

PersistenceInterface has a method on it to open a session, which will be an instance of the session class you call the raw query method on.

[CJ_Johnson]

I've now tried the following

from com.inductiveautomation.ignition.gateway import SRContext
from simpleorm.dataset import SSessionI

def run():
	query = SRContext.get().getPersistenceInterface().getSession()
	try:
		query.rawQueryMaps("SELECT DEVICESETTINGS.NAME FROM from DEVICESETTINGS", True, [])
	finally:
		    query.close()
	test = []
	for quer in query:
		test.append(quer.getName())
	logger.info("%s" % (test))
	return test

Which returns :

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 9, in File "", line 7, in run at simpleorm.sessionjdbc.SSessionJdbc.rawQueryInner(SSessionJdbc.java:797) at simpleorm.sessionjdbc.SSessionJdbc.rawQueryMaps(SSessionJdbc.java:709) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) simpleorm.utils.SException$Jdbc: simpleorm.utils.SException$Jdbc: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "from": syntax error) 

[Kevin.Herron]

@CJ_Johnson

FROM from

Typo in your query?

[pturmel]

I don't see you closing the session, either. Danger!

[CJ_Johnson]

Does the line query.close the session? I have it there.

[pturmel]

Ah, yes. You called it query instead of session.

[CJ_Johnson]

I now have the following.

def run():
	logger = LoggerFactory.getLogger(LOGGER_PREFIX + "run")
	query = SRContext.get().getPersistenceInterface().getSession()
	try:
		query.rawQueryMaps("SELECT DEVICESETTINGS.NAME FROM DEVICESETTINGS", True, [])
	finally:
		    query.close()
	logger.info("%s" % (query))

This does not error but the output is unexpected.

Would "query" hold the dataset created by rawQueryMaps?

[pturmel]

You didn't capture the return value from .rawQueryMaps(). And it isn't a dataset. It's a list of maps (dictionaries).

[CJ_Johnson]

I finally have an output containing the values I expected. I can scale this up gather the other parameters I need. Thank you to all who chimed in. I truly appreciate the help I've received today.

[tyler.bennett]

Thanks to all who contributed in this thread.
The clues herein led me to develop a solution (unsupported as it may be) for reading all properties for existing devices (Ignition v8.1). Posted here:

How to read properties of existing device connection - Example Code

I had often been disappointed that not all device connection properties for existing devices were easily available through supported scripting (system.device.listDevices()) . I recently had cause to develop a methodology to create/update/remove devices, UDTs (definitions/instances), tags and tag alarms based on an Excel workbook. Without scripted access to device connection properties it was difficult to determine whether a device required an update so I dug in and finally found a way to retriev…