Validate a datasource connection is working (SQL Server/jdbc)

Module Development
1

I have been writing a custom ignition module that accesses an SQL Server data source and have been pulling my hair out trying to figure out why the following function I have running periodically isn’t working:

    public boolean validateConnection() throws CustomException {
        try {
            // connection is an SRConnection
            boolean isValid = connection.isValid(3); 
            boolean isClosed = connection.isClosed();
            return isValid && !isClosed;
        } catch (SQLException e) {
            throw new CustomException("Error grabbing DB Connection status");
        }
    }

This method is in a custom wrapper for an SRConnection

As a bit of context, I just want to verify that my SQL Server connection is “Valid” i.e. I can make sql calls to it and expect the data to be returned back, but I can’t find any way to verify that

The issue is .isValid() and .isClosed() always respond with true and false respectively, even when I make the data source say “Faulted” in the Gateway

Any ideas on how to verify a db connection is up would be greatly appreciated

2

I don't think you're meant to be caching or holding onto instances of SRConnection.

You should always be getting the datasource and then getting a connection from it when you need to use one.

1 Like
3

Correct. That interferes with the JDBC connection pooling layer in Ignition.