Database type in [System]Gateway tags

Ignition
, ,
1

I'm trying to get the database or connection type from the Gateway/Database tags but I'm not seeing where that's an included tag in the individual database tag folder.

Looking for ideas on how I can fetch this information.

What I'm trying to do is get some stats from the databases but some, like Oracle connections, I'd like to exclude from the query.

Thanks!

2

I would try using scripting to get this info:

1 Like
3

Sweet, that's what I was looking for!

Follow-up question:

Is there a way to get the databaseName from the extra connection properties?

4

I'm not quite sure what you're asking for here.

If you're asking for the name of the datasource that you'd find in your Ignition database connections page, then Name is the column you're looking for.

If you're asking for the schema that datasource is connected to, then I think you can query for it. But, it is database-specific, so you'd have to look up how to find it in each database type you're using. You'd need to use the DBType column to choose which query to run on that connection to get the schema name.

1 Like
5

What I'm referring to is when you set up a database connection in the gateway you can add extra connection properties to point to a specific database.

So let's say in the connect url field I have jdbc:sqlserver://192.168.1.1 which connects to the sql server, then add the extra connection property: databaseName=my_database

This way, in our projects we don't have to call out the specific database in every query. This allows us to disguise dev databases as prod ones so we can send the project to prod without having to change anything.

So what I was asking was if there was a way to tease that extra connection property out of the gateway.

Sorry for the misunderstanding.

6

@Brandon_Peterson nerd-sniped me.

queryDict = {'POSTGRES' : 'SELECT current_database()',
             'MSSQL'    : 'SELECT DB_NAME()',
            }
dataIn = system.dataset.toPyDataSet(system.db.getConnections())

for row in dataIn:
	if row['Status'] == 'Valid':
		dbConn = row['Name']
		query = queryDict[row['DBType']]
		
		db = system.db.runScalarQuery(query, dbConn)
		print dbConn, ':', db

Ran against my own gateway (you can see I'm usually lazy in my naming.) :wink: :

Alarms : alarms
Audit : audit
Bypass : bypass
Downtime : downtime
DS_Door_New : ds_door
DT : dt_door
HCH_SILOMON01 : silo
Issues : issues
MachineLearning : machine_learning
Nedd : nedd
Plex : plex
Production : production
Scheduling : scheduling
Takt : takt
Test : test
Tickets : ticketsdb
Visitor : digital_signage
WeeklyProductivityData : weekly_data
WorkOrders : work_orders
3 Likes
7

Great stuff, thanks!

I got my project working as I wanted it.

I added some error control.

def getDatabaseInfo():
	
	# Define the query dictionary and excluded types
	queryDict = {
		'POSTGRES': 'SELECT current_database()',
		'MSSQL': 'SELECT DB_NAME()',
	}
	excludedTypes = ['ORACLE']

	# Get the database connections
	dataIn = system.dataset.toPyDataSet(system.db.getConnections())

	# Initialize lists for dataset
	headers = ['ConnectionName', 'DatabaseName']
	data = []

	# Iterate through the connections
	for row in dataIn:
		if row['DBType'] in excludedTypes:
			print row['Name'] + ' excluded.'
			continue
			
		try:
			if row['Status'] == 'Valid':
				dbConn = row['Name']
				query = queryDict.get(row['DBType'])
				if query:
					db = system.db.runScalarQuery(query, dbConn)
					print dbConn + ': ' + db
					data.append([dbConn, db])
		except:
			print row['Name'] + ' exception.'
			

	# Convert the data to a BasicDataset
	dataset = system.dataset.toDataSet(headers, data)
	return dataset

In case anyone's interested, I call this function through an expression: runScript('dbScripts.getDatabaseInfo') then use the result to loop through the connections and get storage information from each db.

	ds = system.dataset.toPyDataSet(value)
	headers = ['ConnectionName', 'Database', 'file_id', 'LogicalName', 'PhysicalName', 'TotalSizeMB', 'AvailableSpaceMB']
	data = []

	query = '''
		SELECT 
		    file_id,
		    name AS LogicalName,
		    physical_name AS PhysicalName,
		    size * 8 / 1024 AS TotalSizeMB,
		    size * 8 / 1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8 / 1024 AS AvailableSpaceMB
		FROM 
		    sys.master_files
		WHERE 
		    database_id = DB_ID(N'{dbName}');
	'''	

	resultData = []

	for row in ds:
		connectionName = row['ConnectionName']
		databaseName = row['DatabaseName']	
		try:
			# format the query with the database name
			sql = query.format(dbName=databaseName)
			# execute the formatted query
			results = system.db.runPrepQuery(sql, [], connectionName)
			
			# check if any results are returned.
			if len(results) == 0:
				# Add a blank row to data if there are no results
				data.append([dbName, None, None, None, None, None])
				
			# process the results
			for resultRow in results:
				data.append([
					connectionName,
					databaseName,
					resultRow['file_id'],
					resultRow['LogicalName'],
					resultRow['PhysicalName'],
					resultRow['TotalSizeMB'],
					resultRow['AvailableSpaceMB']
					])
		except:
			# Add a blank row to resultData if there's an exception
			data.append([connectionName, databaseName, None, None, None, None, None])

	dataset = system.dataset.toDataSet(headers, data)

	return dataset

I'm sure there's a cleaner way to do it, but I'm not a coder.

Thanks again for the help!