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
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.
2 Likes
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)
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.
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:
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)
No, you cannot use .query() without the ORM classes. You must use one of the .rawQuery*() methods.
1 Like
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.
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)
I don't see you closing the session, either. Danger!
Does the line query.close the session? I have it there.
Ah, yes. You called it query instead of session.
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?
You didn't capture the return value from .rawQueryMaps(). And it isn't a dataset. It's a list of maps (dictionaries).
1 Like
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.
1 Like
