Query Designer




Query Designer


From anywhere within the application you can press F4 and open the query designer window.


You can use this tool to explore, design, and edit queries and connection string for a wide variety of database engines and files.  Most database products typically have a standard management and/or development tool or tool suite for interacting with and writing queries for that product and those tools are usually quite full featured.  The problem is that those tools usually only work with a single product.  Further, each tool usually has a completely different layout and learning curve associated with it.  The query designer is a single tool that can be used to access almost any product for which there is an OLEDB provider or ODBC driver and allows you to work with all of them from a single, central, and consistent user interface.  The query designer will likely not incorporate all the capabilities of the vendor-provided tools and tool suites but it should provide more than enough capability to allow you to build and test connection strings and queries alike.


Registered Connections


The Registered Connection tool window allows you to build and test connection strings.  In has native support for SQL Server, OLEDB providers, and ODBC drivers.  You will need to have the appropriate providers and/or drivers installed on the same machine as pasUnity in order to create and test connection strings.  Further, the providers and drivers you install will need to match the architecture of pasUnity in order to use them.  In other words, if you are running the x86 (32-bit) edition of pasUnity you will need the x86 edition of the relevant providers and drivers as well.


To create a new connection right click on the Registered Connections tree node and choose the Add Folder... option to create a folder to contain your connections.  Then you can right click any Folder node and click Add Connection... to open the user interface for designing new connections.


Manage connection and folders by right-clicking them and choosing the appropriate action from the context menu that appears.


When the application exits all of your changes to all registered connections and the folder hierarchy will be saved to your roaming profile location.


Connection Editor


The Connection Editor allows you to build and test data provider specific connection strings.  Begin by choosing the Connection Type which will load custom user interfaces for designing and testing connection strings that expose provider-specific and driver-specific options.  A list of all common properties for a given provider or driver are listed on the left side of the designer.  Depending on the connection selected a different UI and toolbar will appear to guide you through configuring your custom connection string.  A preview on the connection string appear on the lower right of the designer and allows you to directly edit the connection string.  When finished click Save to save your changes or Cancel to abandon them.


Schema Explorer


The Schema Explorer tool window allows you to connect to a data source and browse the meta data for the data repository as well as script queries and generally interact with the database.


To open a connection in the Schema Explorer locate it first in the Registered Connections tool window and double-click it.


While each database product is different a consistent hierarchy is displayed in the schema explorer whereby each connection can be expanded to view the Information and Catalogs root nodes.  Information is used to describe the features of the drivers and database products and help you to explore and understand the design and capabilities of the database product.  The items under the Information node may be viewed as data tables.  The Catalogs root nodes is used to enumerate the various database or database analogs (if any) within a product.  Not all database products have the catalog concept built in but this tool does so if there are no catalogs you will see a {DEFAULT} catalog that serves as the root container.  Within the catalogs you can expand to see tables at a minimum and depending on the product views, stored procedures, and maybe more.  Pretty much every database engine is going to allow you to explore the tables collection and view columns in those tables.  Some products will even display data type and other attributes.


Interact with the nodes in the Schema Explorer by right-clicking on them to open context-sensitive menus that list the actions you can take against them.  This includes everything from opening up new script tabs against a connection or catalog to scripting out SQL statements from tables and view.  Explore a bit because each provider or driver offers different features in these menus.


Query Tab


You can open new query tabs from the right-click menus in both the Schema Explorer and the Registered Connections tool windows.  The editor windows that appear allow you to write and execute queries using provider-specific dialects of the Structured Query Language (SQL). 


A syntax editor will offer color coding for basic SQL language.  It is only reflective of the basic rules of the ANSI SQL language and does not offer precise syntax highlighting for all SQL dialects.  It does not prevent you from using the full power of whatever SQL dialect you are writing in.


When you execute a query a connection is opened, the query is executed, and the connection is closed.  If a fatal transport exception is raised that terminates the connection execution will stop.  Prior to executing the query code provided the query is parsed into batches.  Batches are separated by the word GO on its own line (case matters).  Because the connection stays opened until all batches are executed any temporary object created in one batch can be accessed by subsequent batches in the query command text.


During execution a separate tab will appear in the results pane for each set of tabular output received and to summarize the informational and exception messages received for each batch.  Tab naming is reflective of the batch and tabular result positional information.


Shortcut keys in the query code window are F5 to execute the query and CTRL+R to toggle the results window.


Copyright © 2023 pasUNITY, Inc.


Send comments on this topic.