SQL Commander schema popup

Hello

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?

Hi,

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.

Best Regards,

Hans

Thank you for your informative answer.

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?

I just fixed that open source JDBC driver to correctly implement getSchema / setSchema (since they decided to go with schemas instead of catalogs, which is a valid choice) but DBVis is not using those methods! As a result, the schema popup is still not working.

At this point, it's clearly a DBVis bug, since getSchema / setSchema are standard methods. Can you please file it as a bug report?

Hi Tobia,

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.

Best Regards,

Hans