Azure SQL Server getCatalogs doesn't support USE statement when not using master database

[JordanGreissman]

Expected behavior

Expected behavior: Calling Meta.getCatalogs() on an Azure SQL Server database connection should return all of the catalogs for the database

Actual behavior

Actual behavior: The call fails with USE statement is not supported to switch between databases. Use a new connection to connect to a different database. The SQL Server JDBC driver does not support returning the entire set of catalogs for an Azure DB. As is, this would never work unless you're connecting to the master database

https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-to-an-azure-sql-database?view=sql-server-ver16#details

Steps to reproduce the problem

• Instantiate a connection to any user db in Azure DB using the SQL Server JDBC driver
• call DSLContext.dsl().meta().getCatalogs() or any of the other getters exposed in Meta

jOOQ Version

JOOQ Enterprise 3.18.6

Database product and version

Azure SQL Server

Java Version

openjdk version "20.0.2" 2023-07-18

OS Version

Mac OS Ventura 13.0

JDBC driver name and version (include name if unofficial driver)

com.microsoft.sqlserver:mssql-jdbc:12.2.0.jre11-preview

[lukaseder]

Thanks for your report. That rings a bell, I recall having seen something similar recently. Can you post a stack trace, perhaps?

[lukaseder]

I can't reproduce this in our integration tests. I'm using com.microsoft.sqlserver:mssql-jdbc:12.4.2.jre11, but I can also not reproduce it with 12.2.0.jre11:

System.out.println(ctx.meta().getCatalogs());
System.out.println(ctx.fetchValue(currentCatalog()));

Prints:

["master", "test"]
test

I.e. I'm connected to the test database, not master

[lukaseder]

There's this fix from jOOQ 3.16.0, which seems not to work in your case:

• Reference to database and/or server name in 'master.sys.schemas' is not supported in this version of SQL Server. #12291

I suspect, I'll need a more complete reproducer, e.g. based on our template here: https://github.com/jOOQ/jOOQ-mcve