Start a new topic

SQL Commander default schema

[This topic is migrated from our old forums. The original author name has been removed]

Is there a way you can set a default schema for a database connection such that in the SQL Commander, when switching database connections, it is automatically selected? Most of the time I connect to a database I'm connecting as my own user but querying against tables in another schema I have select grants on. Every time I switch between database connections in the SQL Commander I have to always re-select the schema as it defaults to my schema. It would be nice if I could, at the database connection setup, define the schema to "default" to for things like SQL Commander.

[This reply is migrated from our old forums. The original author name has been removed]

Re: SQL Commander default schema
I have had the same issue - but managed to solve it just after reading through this - The problem is, that the DB Default schema I have for my user account is my "personal" schema, but the data I normally need to work with is in a different schema - and apparently, it's "wanted" that way (internal policy)... So, normally what I had to do is basically log in and then select the correct schema from the combo-box in the SQLCommander tab -- new tabs then "keep" that same setting; but switch to another DB and you're back to your default schema. I managed to "fix" this by going double clicking on a DB Connection in the Databases tab, and then in the databases view switch to properties and go to Connection Hooks - in the connection hooks -> Run SQL at Connect add the SQL to change the schema for the current session (for Oracle: ALTER SESSION SET CURRENT_SCHEMA = LIVEDATA; ) and hit Apply. In your current DbVisualizer instance, you may then still need to right-click a Db Connection and hit Reconnect All (to make it run that step for all open Dbs). After that, each Db connection will automatically use your desired default schema.
[This reply is migrated from our old forums. The original author name has been removed]

Re: SQL Commander default schema
Hi-- the active tab functionality doesn't really help as I'm not frequently opening tabs. For Postgresql -- then yes, default database (set via jdbc URL) + schema would be preferable. Edited by: Jason on Jul 11, 2014 7:48 AM -- was tired
[This reply is migrated from our old forums.]

Re: SQL Commander default schema
Hi Jason, Of the database you listed, only Oracle allows the default schema to be changed (we are looking into supporting this also for PostgreSQL). For all the other databases you need to qualify the table names with a schema regardless of the the selection made in the Schema list in the SQL Commander. If you have an SQL Commander tab as the "active tab" and open a new SQL Commander tab, the Schema selection for the new tab is set to the same value as in the "active tab". Is that sufficient for your needs? Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: SQL Commander default schema
Hi Hans, Just noticed you were looking for input here. Postgresql/Redshit, Oracle, MySql, Hsqldb are the primary databases I use. I don't use auto-complete.
[This reply is migrated from our old forums.]

Re: SQL Commander default schema
Hi Jason, Be aware that for most databases, the schema selected in the Schema list is used only to limit the tables the auto completion feature shows in the completion pop-up; it does not define a default schema for tables referenced in the SQL, because most databases do not allow the default schema to be changed during a session. For the databases that do allow the default schema to be changed, however, the selected schema is also used as the default schema, i.e., the schema used for unqualified table names in the SQL. Currently, the databases that support setting a default schema are DB2 LUW, DB2 z/OS, HP Neoview, H2, JavaDB/Derby and Oracle. So, which database do you use and do you set the schema for auto-completion or to actually change the default schema for the session? Best Regards, Hans