If the Query Builder is launched without right-clicking or selecting an object in the Schema Browser, no databases or Data Sources are listed in the Database Pane when it opens. To connect to one, left-click the [Connect to Server...] text inside the Database Pane to create a new connection. The [Choose Server or Database] Dialog appears which allows you to expand a registered server and connect a database.

Once you pick a database and click [OK], it is displayed in the Database Pane. If you hover your cursor over the Database you get a tooltip indicating the Database Name followed by the database login within parentheses for this connection.

Add / remove database

To add multiple databases from the same registered server to your Query Builder Worksheet, click the triangle at the top of the Data Pane and select [Add Database...] For servers that only allow connection to a single database, but do allow multiple schemas, this menu will display [Add Schema...].

When the [Add Database] Dialog appears you can control-click and multi-select to include as many databases from your current Data Source as you like and click [OK].

Any database that is not set as [Primary] can be removed if needed by right-clicking it and selecting [Remove].

To see what Schema your current connection is using, view the bottom left corner of the Query Builder.

You can add additional databases (depending on the server vendor) from a single connected server by clicking the triangle in the Database Pane header and selecting [Add Database]. You cannot add databases from different registered servers in the same Data Pane. You can, however, add multiple databases from the same connection when the server supports cross-database queries. See Multi-Database Query for more.

Primary 

The databases will appear in the Database Pane of the Data Pane. If you do not choose a database when launching the Query Builder, whatever database is the default database for your registered server appears as [Primary] by default, which is indicated with a checkmark. If you launch the Query Builder by right-clicking directly on a database, that database will appear as Primary. 

To set one of the other databases as Primary, right-click it and choose [Set Primary]. 

The database you chose now displays the checkmark to indicate it is now used as the Primary.

See Multi-Database Query for how to write queries that use data from two different databases.

Note that any tables, views, or columns from the database you remove will be marked as invalid in the decks and Diagram Pane. See the Invalid Objects, Logic Filters, or Joins section for more.

Data Pane Menu

Clicking the triangle at the top right corner of the Data Pane triggers a menu containing:

  • Add Database... - Allows adding an additional database from the same server connection you are using. See Adding Multiple Databases to the Data Pane for more.
  • Group By Schema - Enables/disables grouping tables in the Table Pane by Schema (for those databases which support it). See Grouping Tables by Schema below for more.
  • Show System Tables and Views - Hides/Shows System Tables in the Table Pane.
  • Reconnect - Reconnects the current connection. See Reconnecting for more details.
  • Refresh - Refreshes the contents of the Data Pane by retrieving the tables and views from the currently connected server. See Refresh the Query Builder for more details.
  • Close - Closes the current connection, disconnects from the current server, and clears all Panes of content. You will receive a confirmation dialog that warns you closing clears the Query Builder Window of all content.
  • Edit Connection... - Edit the Server Registration properties for the current connection
  • Properties - Displays connection properties for the current server. 

Grouping Tables By Schema in the Table Pane

By default, the Query Builder displays tables in the Table Pane for a Data Source in alphabetical order. When servers support Schemas as database objects, you can configure the Data Pane to display your tables grouped by the Schema they belong to.  Selecting [Connection > Group By Schema] in the Menu, or clicking the triangle at the top of the Database Pane and choosing [Group By Schema], will display them this way. When grouping by schema, expand a Schema node in the Table Pane and its contained tables appear as subnodes in the tree.

Reconnecting

Aqua Data Studio does not sever idle connections but many database server installations are configured to drop connections that have been idle for a set amount of time. Also, many firewalls and network security devices, and OS settings can be configured to drop idle connections. Aqua Data Studio has no way of knowing when this occurs because it relies on a server response to inform it that a connection is closed. If the Query Builder reports that there was an error extracting meta information when you modify items, or if it reports [Invalid state, the Connection object is closed], select [Connection > Reconnect] from the Menu and try again.

If [Connection > Reconnect] does not resolve this problem you may want to contact your network administrator or DBA to find out what needs to be done to fix your connection issues.

Refresh

While in the process of using the Query Builder it is possible that you or someone else drops or adds a database object from the database server you connected to or need to connect to. To incorporate schema, database, and table changes that have occurred since you began working in a Query Builder worksheet, select [Connection > Refresh].

Your Tables and View Panes will pull in the latest schema objects from your Data Source. Red lines and red background colors indicate which items you have in the Diagram Pane and other decks that are no longer valid, informing you that you should take action to correct them. The most likely cause for an invalid server object or join is that a table, column or view was dropped from the server.

See Invalid Objects, Filter Logic or Joins for more information.



  • No labels