Connecting to adhoc SQLite database from Jython?

Ignition
1

Has anyone successfully connected to an adhoc SQLite database before in Jython? I would normally use the sqlite3 library but it’s C-based. I only want to connect to it briefly to pull out some config.

If you must know, I want to connect to the Ignition config.idb to export some tables to JSON format so that I can document configuration and also compare config between gateway, so I want to get in and out.

Otherwise, would creating a connection to it via the gateway config cause issues? I would keep it disabled until I needed it after which I would disabled it again.

2

You can’t make additional connections to the config.idb (open the file) while Ignition is running.

3

Well that sounds slightly prohibitive then for what I’m doing :slight_smile: Also I’m sure I knew that already… I’ll have to go to plan B and copy the db or use a gateway backup’s copy instead and I’ll connect to it via the gateway config method. Cheers

4

Use the SDK APIs. If you can get a GatewayContext, you can call GatewayContext::getLocalDBInterface and then run queries on it. Danger zone.

https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.11/com/inductiveautomation/ignition/gateway/localdb/DBInterface.html#runQuery(java.lang.String)

5

Is there any danger in running SELECT * queries on a few small tables (SYSPROPS/DEVICESETTINGS/ others, NOT any tags tables)?

6

Should be okay… :grimacing:

7

You can also poke around from http://localhost:8088/web/status/sys.internaldb

8

Cheers, I saw that on the forum a year or so ago but I never actually tried it out. I need to get the results into python though so will be easier to query in script. We have a test environment, so here goes!

9

Hmm, I’m getting an error trying to run runQuery:

SQLFeatureNotSupportedException: java.sql.SQLFeatureNotSupportedException

	from com.inductiveautomation.ignition.gateway import IgnitionGateway
	import traceback
	context = IgnitionGateway.get()
	dbi = context.getLocalDBInterface()
	
	try:
		rs = dbi.runQuery('SELECT * FROM SYSPROPS')
	except:
		err = traceback.format_exc()
		system.tag.writeBlocking('[.]test', err)

I’m running it for testing from a tag change script. Tag is a memory bool tag that I just toggle to execute it

10

Hmm. I don’t know why that doesn’t work… maybe I lead you down the wrong path :sweat:

11 
session = IgnitionGateway.get().getPersistenceInterface().getSession()

try:
    session.rawQueryMaps("SELECT * FROM SYSPROPS")
finally:
    session.close()

You could try this; see SSessionJdbc. Make very sure you close the session, or this will hose a running gateway until restarted.

12

Would a context manager work here to close the session for me?

with session.rawQueryMaps('...') as rs:
   #stuff
   pass

?

13

session is an AutoCloseable, which in Java means you can use try-with-resources for the same basic pattern as a context manager. Unfortunately, I think Jython predates try-with-resources, and there’s no ready-made context manager… although contextlib.closing may ‘just work’, since the method is just called close()?
https://docs.python.org/2.7/library/contextlib.html#contextlib.closing

So it would be with contextlib.closing(persistenceInterface.getSession()) as session:, then the rawQueryMaps call inside the block.

14

This worked, I need to provide the other 2 args though:

session = IgnitionGateway.get().getPersistenceInterface().getSession()

try:
    session.rawQueryMaps("SELECT * FROM SYSPROPS", True, []) # sql, flush, params
finally:
    session.close()

Not entirely sure what flush does…

15

Flush should be pretty much irrelevant to a select…