What sql method does Database Explorer's Schema and Database drop-down execute

[Kuljit Jagpal]

Hi,

SQLWorkbench/J details

version - build 121 (2016-07-31 23:50)

OS - Mac osx el capitan

DB = Snowflake (www.snowflake.net)

When I go to DB explorer tab my schema drop down list shows all schemas irrespective of database selection.

I'd expect the schema to conform to the DB selected in the database dropdown and therefore, my question about how it is implemented in the client.

Thanks.

[Thomas Kellerer]

I'm using DatabaseMetaData.getSchemas(String, String)

http://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getSchemas-java.lang.String-java.lang.String-

but I'm passing nothing for the catalog as the only database I know that supports catalogs *and* schemas (SQL Server) doesn't support getting the schemas for a database other then the current one.

What I am doing is to switch the current catalog through Connection.setCatalog() for that.

You can enable this catalog switching trough

workbench.db.[dbid].dbexplorer.switchcatalog=true


It probably makes sense that I call getSchemas() with the selected catalog for other DBMS.

Thomas

[Kuljit Jagpal]

Hi Thomas,

Thanks for the reply.  I don't think this is what I am looking for.

Snowflake supports having the ability to switch database and schema for a user.  What I am seeing is that the Schema dropdown list all the available schemas in the DB regardless of my database selection (in the database dropdown).  I'd like for the schemas to only show what is available for a selected database.

I think your last statement about calling getSchemas(catalogName, schema pattern) is appropriate. 

Are there any properties I can set to enable this? 

I appreciate your response.  Thanks.

[Thomas Kellerer]

You are right, there was no filtering applied.

I have committed a fix for that, if you want to build from source, you can try that.

Thomas

Kuljit Jagpal schrieb am 25.10.2016 um 08:08:
> Hi Thomas,
>
> Thanks for the reply. I don't think this is what I am looking for.
> Snowflake supports having the ability to switch database and schema
> for a user. What I am seeing is that the Schema dropdown list all the
> available schemas in the DB regardless of my database selection (in
> the database dropdown). I'd like for the schemas to only show what is
> available for a selected database. I think your last statement about
> calling getSchemas(catalogName, schema pattern) is appropriate. Are
> there any properties I can set to enable this?
>
> I appreciate your response. Thanks.
>
>
> On Monday, October 24, 2016 at 2:53:07 PM UTC-7, Thomas Kellerer wrote:
>
> I'm using DatabaseMetaData.getSchemas(String, String)
>

> http://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getSchemas-java.lang.String-java.lang.String- <http://www.google.com/url?q=http%3A%2F%2Fdocs.oracle.com%2Fjavase%2F8%2Fdocs%2Fapi%2Fjava%2Fsql%2FDatabaseMetaData.html%23getSchemas-java.lang.String-java.lang.String-&sa=D&sntz=1&usg=AFQjCNE42AdkDKJkv5pQLx4r_baL0_r0bg>

>
> but I'm passing nothing for the catalog as the only database I know that supports catalogs *and* schemas (SQL Server) doesn't support getting the schemas for a database other then the current one.
>
> What I am doing is to switch the current catalog through Connection.setCatalog() for that.
>
> You can enable this catalog switching trough
>
> workbench.db.[dbid].dbexplorer.switchcatalog=true
>
>
> It probably makes sense that I call getSchemas() with the selected catalog for other DBMS.
>
> Thomas
>
> Kuljit Jagpal schrieb am 24.10.2016 um 22:22:
> > Hi,
> >
> > SQLWorkbench/J details
> > version - build 121 (2016-07-31 23:50)
> > OS - Mac osx el capitan

> > DB = Snowflake (www.snowflake.net <http://www.snowflake.net>)

> >
> > When I go to DB explorer tab my schema drop down list shows all schemas irrespective of database selection.
> > I'd expect the schema to conform to the DB selected in the database dropdown and therefore, my question about how it is implemented in the client.
> > Thanks.
>

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Kuljit Jagpal]

HI Thomas,

Thanks for the quick turnaround on this.  I had built it from source but still encounter the issue.  

I'd appreciate if you can help me determine what the issue is.

Here's the trace if it helps:

2016-10-28 18:49:50 DEBUG DbMetadata.getSchemas() Retrieving schemas using getSchemas() took: 460ms

2016-10-28 18:49:50 DEBUG DbMetadata.getCatalogInformation() Retrieving catalogs using getCatalogs() took: 282ms

2016-10-28 18:49:51 ERROR TableListPanel.retrieve() Error retrieving table list SQL compilation error:

Object does not exist, or operation cannot be performed. [SQL State=02000, DB Errorcode=2043] ^M

com.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

Object does not exist, or operation cannot be performed.

        at com.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:88)

        at com.snowflake.client.core.StmtUtil.execute(StmtUtil.java:384)

        at com.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:402)

        at com.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:240)

        at com.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:180)

        at com.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:153)

        at com.snowflake.client.core.SFStatement.execute(SFStatement.java:618)

        at com.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:187)

        at com.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:85)

        at com.snowflake.client.jdbc.SnowflakeDatabaseMetaData.getTables(SnowflakeDatabaseMetaData.java:1331)

        at workbench.db.DbMetadata.getObjects(DbMetadata.java:1629)

        at workbench.db.DbMetadata.getObjects(DbMetadata.java:1466)

        at workbench.gui.dbobjects.TableListPanel.retrieve(TableListPanel.java:1208)

        at workbench.gui.dbobjects.TableListPanel.setCatalogAndSchema(TableListPanel.java:1086)

        at workbench.gui.dbobjects.DbExplorerPanel$3.run(DbExplorerPanel.java:914)

2016-10-28 18:50:49 DEBUG DbMetadata.getSchemas() DbMetadata.getSchemas() called with catalog parameter, but current connection is not configured to support that Backtrace^M

java.lang.Exception: Backtrace

        at workbench.db.DbMetadata.getSchemas(DbMetadata.java:2720)

        at workbench.gui.dbobjects.DbExplorerPanel.retrieveAndShowSchemas(DbExplorerPanel.java:434)

        at workbench.gui.dbobjects.DbExplorerPanel.access$000(DbExplorerPanel.java:104)

        at workbench.gui.dbobjects.DbExplorerPanel$3.run(DbExplorerPanel.java:908)

2016-10-28 18:50:50 DEBUG DbMetadata.getSchemas() Retrieving schemas using getSchemas() took: 467ms

2016-10-28 18:50:50 DEBUG DbMetadata.getCatalogInformation() Retrieving catalogs using getCatalogs() took: 298ms

2016-10-28 18:50:50 DEBUG DbMetadata.getObjects() Retrieving table list took: 198ms

2016-10-28 18:50:50 DEBUG DbObjectCacheFactory.getCache() Creating new cache for: kj@jdbc:snowflake://sfcsupport.snowflakecomputing.com:443

2016-10-28 18:50:50 DEBUG ObjectCache.addTableList() Added 0 objects

Thanks!

Kuljit

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbench+unsub...@googlegroups.com>.

[Thomas Kellerer]

You need to set:

workbench.db.[dbid].getschemas.per.catalog.supported=true

replace [dbid] with the one generated for Snowflake:

http://www.sql-workbench.net/manual/settings.html#dbid

Thomas

[Kuljit Jagpal]

Hi Thomas,

Thanks so much for the help with this.  After setting the property I can see it functioning as expected.  The only caveat being on first launch the user will see all schemas and DBs and only after they switch the DB will the feature then come into play.

Best Regards,

Kuljit

[Thomas Kellerer]

> The only caveat being on first launch the user will see all schemas
> and DBs and only after they switch the DB will the feature then come
> into play.

It should select the current database (=catalog) and then retrieve the schemas for just that catalog.

Does your driver properly implement Connection.getCatalog()?

The selection of database and schema is also only remembered if "Remember DbExplorer Schema" is enabled in the connection profile (the selection is then stored in the workspace associated with the connection profile).

Thomas