# Get database name (or schema) from a db connection

**URL:** <https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [April 27, 2023, 2:08pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966 "2023-04-27T14:08:35Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 27, 2023, 2:08pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/1 "2023-04-27T14:08:35Z")

</div>

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

perhaps **getDatabaseProductName()** from the JDBC underlying connection ?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 27, 2023, 2:11pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/2 "2023-04-27T14:11:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 27, 2023, 2:14pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/3 "2023-04-27T14:14:15Z")

</div>

> [@bkarabinchak.psi](#):
>
> SELECT DATABASE() FROM DUAL

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 27, 2023, 2:16pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/4 "2023-04-27T14:16:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [April 27, 2023, 2:18pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/5 "2023-04-27T14:18:37Z")

</div>

Maybe this?  
[system.db.getConnectionInfo - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/system.db.getConnectionInfo)

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 27, 2023, 2:20pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/6 "2023-04-27T14:20:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [April 27, 2023, 2:23pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/7 "2023-04-27T14:23:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 27, 2023, 2:25pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/8 "2023-04-27T14:25:49Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [April 27, 2023, 2:27pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/9 "2023-04-27T14:27:42Z")

</div>

👍  
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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [April 27, 2023, 2:48pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/10 "2023-04-27T14:48:18Z")

</div>

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

```python
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:

```python
some_string = """
    this keeps whitespaces
    so this will an initial newline
    then 4 lines
    starting with 4 spaces
"""
```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [April 27, 2023, 3:40pm UTC](https://forum.inductiveautomation.com/t/get-database-name-or-schema-from-a-db-connection/72966/11 "2023-04-27T15:40:36Z")

</div>

If you've already got a GatewayContext, you can go to the DatasourceManager, [get the `DatasourceMetaProvider`](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.27/com/inductiveautomation/ignition/gateway/datasource/DatasourceManager.html#getMetaProvider()), and [ask it](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.27/com/inductiveautomation/ignition/common/db/schema/DatasourceMetaProvider.html).

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.