Get database name (or schema) from a db connection

Ignition
1

is there a simple way by scripting to get database name (or schema) from a db connection

perhaps getDatabaseProductName() from the JDBC underlying connection ?

2

I assume other databases's have corollaries to this but in mysql you could run the query SELECT DATABASE() FROM DUAL and it will spit out what DB you are running queries against.

3

That's fine for MySQL,
for my use case: I'm looking for MySQL, PostreSQL and SQLServer

4

Presumably you won't know at run time what your running against? SQL Server has SELECT DB_NAME() and Postgres SELECT current_database().

But if you don't know what one you're running against and just trying to figure it out blindly in some script, then yea you probably need something from the JBDC connection.

5

Maybe this?
system.db.getConnectionInfo - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

1 Like
6

Don't see that giving you the db name directly but I think you could either 1) put the db name in the description of the db connection and just grab that property or 2) use that to get the driver like mysql, sqlserver etc and use that to figure out what query to run.

7

If it's done in a script, maybe try all of them in try/except blocks ?

8

That was my first thought but with the link @dkhayes117 provided there doesn't seem to be a reason to have to guess and try/except if you examine the DBType in the dataset, you should be able to figure out the query based on that (or at least narrow it down and then maybe you need a try/except if for some reason the syntax changed between versions of the same db).

9

:+1:
Yes that's perfect.
In my use case, I need it to enumerates tables in the db from one connection.
For MySQL and PostgreSQL, I need the the schema name.

I will get rid of the "schema" param

def listTable(database,schema,prefix=""):
	"""
		database : connexion
		schema : base (NB : non utilisé pour sqlserver)
	"""
	global strLib
	strFct="listTable"
	try:
		db_driver = shared.commun.constantes.getDbDriver(database)
		tables = []
		if db_driver == "mysql":
			sql="SELECT table_name"
			sql+=" FROM information_schema.TABLES"
			sql+=" WHERE table_name like ?"
			sql+=" and table_schema = ?"
			results = system.db.runPrepQuery(sql,["%s%%" % prefix,schema],database=database)
			for row in results:
				tables.append(row["table_name"])
		elif db_driver == "postgres":
			# /!\ table_schema = public et table_catalog = "schema"
			sql="SELECT table_name"
			sql+=" FROM information_schema.tables"
			sql+=" WHERE table_name like ?"
			sql+=" and table_schema = 'public' and table_catalog = ?"			
			results = system.db.runPrepQuery(sql,["%s%%" % prefix,schema],database=database)
			for row in results:
				tables.append(row["table_name"])
		elif db_driver == "sqlserver":
			# Le schéma n'est pas utilisé, la base est fournie dans la connection => Extra Connection Properties = databaseName=bdref
			sql="SELECT t.Name AS table_name FROM sys.tables t"
			sql+=" WHERE t.Name LIKE ?"
			results = system.db.runPrepQuery(sql,["%s%%" % prefix],database=database)
			for row in results:
				tables.append(row["table_name"])			
	except:
		shared.commun.logger.logERROR(strFct=strFct,strLib=strLib)
	return tables
1 Like
10

Just in case you didn't know, you can use auto-concatenation for strings, using this syntax:

some_string = (
    "here's a line "
    "that will be concatenated to this one "
    "and this one "
    "but no newlines are added"
    "so this results in just one line"
)

or use the triple quotes:

some_string = """
    this keeps whitespaces
    so this will an initial newline
    then 4 lines
    starting with 4 spaces
"""
3 Likes
11

If you've already got a GatewayContext, you can go to the DatasourceManager, get the DatasourceMetaProvider, and ask it.

This relies on the JDBC driver under the hood, so it's SQL-flavor-agnostic. Same functionality we use in the DB query browser and elsewhere in the designer.

1 Like