Temporarily enable DB connection

Ignition
1

Hello community,

Recently a DBA asked me about a “live” connection to a database, and this was a connection I had set up on one of our gateways for testing purposes. Since I was not going to use it, I decided to disable it, which gave me an idea: I could enable the connection when needed, and disable it when I’m done; that way I don’t keep the connection alive for more than needed.

So I came up with the following code:

class Connection(object):
    def __init__(self, db):
        self.db = db
        self.enabled = False

    def __enter__(self):
        import time
        self.enabled = True
        system.db.setDatasourceEnabled(self.db, self.enabled)
        # Wait for the connection to be Valid.
        while self.status != "Valid":
            time.sleep(1)

    def __exit__(self, type, value, traceback):
        self.enabled = False
        system.db.setDatasourceEnabled(self.db, self.enabled)

    @property
    def status(self):
        ci = system.db.getConnectionInfo(self.db)
        return ci.getValueAt(0, "Status")


db = "test"
conn = Connection(db)

with conn:
    data = system.db.runQuery(query="SELECT 1", database=db)
    print data.getValueAt(0, 0)

Using the with statement I don’t have to worry about enabling and disabling the connection every time I run a query against that connection.

It is a very rough approach, as I am sure there is room for improvement, but I wanted to share and ask if someone else has done something similar.

2

Other than the potential for clashes on that too-generic classname, looks pretty good. Consider limiting the number of loops in enter waiting for “Valid”–there might be reasons it cannot be achieved.

1 Like
3

Also, consider maintaining a map of concurrent users in the gateway, keyed to client IDs perhaps, to prevent problems when one user exits just after a different user enters. You’d need a gateway message handler to support client-scoped usage.

1 Like
4

Thanks for the suggestions, @pturmel .

5

:loudspeaker: Announcing incendium v2023.4.0 which addresses this concern using system.util.globals introduced in Ignition 8.1.0 (see docs).

It took me a while to add that feature (Add mechanism for handling concurrent DisposableConnection connections · Issue #12 · thecesrom/incendium · GitHub), but I'm pleased to announce it's been done.

To learn more about incendium's DisposableConnection, head over to the wiki:

:rotating_light: :warning: Just consider that this will only work in Ignition 8.1. :warning::rotating_light:

You may download incendium 2023.4.0 from:

Happy coding!

1 Like