I'm using a niche / experimental JDBC driver (ClickHouse.) The Schema popup in SQL Commander tabs is populated with the list of databases in the server (although the database popup would have been more appropriate), but that selection is not used as the default schema/database for the query being executed.
Can you tell me what JDBC API calls do you use to set the default database and/or default schema for the query being executed? So that I can open an issue with the JDBC driver people to get it fixed, or fix it myself.
Otherwise, if setting the current database and/or schema is not a feature covered by the standard JDBC API, how does it work for Oracle, MySQL, and the other driver where it works? Can I configure it from the connection properties?
Oops, I had missed that setSchema() was added in JDBC 4. I have opened a ticket for this so we will discuss it internally. You will be notified if we release a version that supports this.
DbVisualizer uses the DatabaseMetaData#getCatalogs() to get databases/catalog and DatabaseMetaData#getSchemas() to get the schemas. It sounds like the driver returns databases/catalogs from the getSchemas() method.
DbVisualizer calls the Connection#setCatalog() to set the default connection for an execution based on the selection in the Databases list. There is no corresponding JDBC method for the default schema so DbVisualizer uses database-specific SQL for some databases, e.g. ALTER SESSION SET current_schema for Oracle.
So if the driver vendor fixes the issue with getCatalogs()/getSchemas(), you should get the default database/catalog set. If the database also supports schemas, the default schema will not be set based on the selection unless we add specific support for this database.
Having the vendor switch to using catalogs is indeed the correct solution in this case.
But I looked a bit into it and it seems that JDBC 4 (introduced 10 years ago, now part of Java 7) has a corresponding Connection#setSchema() and Connection#getSchema(), so maybe DbVis could use those methods, assuming the drivers support them?
Just to let you know we just started the BETA testing of the upcoming 10.0 version.
It includes a fix so that Connection.setSchema()/getSchema() is called in the JDBC driver.