New features in version 9.0

Many new features and updates to existing features have been added in Version 9.0. These changes are listed below Following is a list of new features in WinSQL version 9.0

• Named queries
• Cross-db queries
• Code Templates
• Enhanced Intelli-tips
• Latest ODBC drivers

Named Queries

Named query is a convenient way of executing a SQL query even if you are not connected to a database. Additionally, you can embed a named query within another SQL statement.

Prerequisites for using Named Queries

• You must be using WinSQL Professional
• Version must be 9.0 or higher
• Only one query can be used. If more than one query is found, WinSQL will select just the first one.
• It is recommended you only use SELECT statements in a named query. Running a named query is very easy and creating a Named Query for a DML statement can unintentionally modify data on the back-end.

Creating a new Named Query

Following steps demonstrate how to create a Name Query

• Connect to your desired database
• Either write a new SQL statement manually, or open an existing script
• Highlight the desired query within the script.
• Either select Save Named Query under the File menu, or select it from the pop-up menu by clicking the right mouse button. Following window appears
  
  
  
  
• Assign a name and click Ok
• The newly saved named query appears on the right hand side of the screen.
  
  
  
  

Benefits of using Named Queries

Named queries store connection information besides the SQL for queries. Therefore, if you need access to the results of a query very fast, consider saving that query as a Named Query.

Following statements apply to Named Queries.

• Double clicking any existing Named Query opens the script in a new MDI window. It automatically establishes connection to the back-end database.
• Double clicking does not execute the query. It just opens it up in a new window
• Click the right mouse button on an existing Named Query and select Run Query to Clipboard to execute the query and copy data to the clipboard. Data on the clipboard is tab delimited.
• Named Queries can be used in Cross Database Queries.

Cross database queries

Starting from version 9.0, WinSQL supports cross database queries, which is accomplished by running an embedded Named Query.

Consider the following example:

• You have two types of database systems in your company: Billing database runs on Oracle and Order Processing runs on IBM DB2.
• You want to find out the orders that have not been billed yet.
• Your goal is to find records in the Orders table (in Oracle), that do not exist in the Billing table (in DB2)

Steps to accomplish this task

• Create a Named Query against your Oracle table that returns records in billing that have been paid. The query can look something like:
  
  -- Query against an Oracle Table
  select custId
  from Billing
  where PaidStatus = 'Y'
  
  
• Let's name this query Paid Customers. Once the query is saved you will see the Named query in the right hand side panel.
• Establish connection to your DB2 database
• Write a query like following.
  
  -- Query against a DB2 table
  select *
  from Orders
  where custId not in ()
  
  Notice the two parenthesis in after the NOT IN clause. If you can somehow paste the customer IDs from the table in Oracle, you will get your final result.
• Now drag the Paid Customer query that appears on the right hand side and drop it between the two parenthesis. This will create a special function holder in WinSQL that accepts a Named Query
• The final query will look something like:
  
  -- Query against a DB2 table
  select *
  from Orders
  where custId not in ( wnsFunc_RunNQ("Paid Customer") )
  
  
• When you run this query, WinSQL will first run the embedded query against Oracle and replace the function with the actual results.
• Finally, the query is run against the DB2 table to retrieve the desired output.

Caveats

The following points should keep in mind when running cross database queries.

• Only the first column is used in the Named Query. If the query returns more than one columns every column but the first one will be ignored.
• WinSQL builds the outer query at run-time by pasting the results of the embedded query. If the data type needs quotes, WinSQL will automatically create a quoted string. For example:
  
  -- Query against a DB2 table
  select *
  from Orders
  where Region in ( wnsFunc_RunNQ("New Regions") )
  
  If the data type of the result column is VARCHAR, the final query will look like:
  
  -- Query against a DB2 table
  select *
  from Orders
  where Region in ( 'North', 'Pacific' )
• WinSQL builds the final query in memory and submits it to the database. Therefore, the number of rows returned by the embedded query is limited to the memory as well as the capabilities of the target database.

Code Templates

WinSQL includes the ability to add custom code templates that can improve speed and consistency for writing SQL scripts. To use these templates write the starting characters of the template and then press CTRL+J.

Consider the following examples:



The objective of this query is to find all students who joined in this year but are not active. One way to accomplish this goal is to prompt the user for the actual date.

Using code templates you can just enter boy, which stands for Beginning of year, and hit CTRL+J to let WinSQL automatically insert the value for the January 1 of the current year.

When you click type CTRL+J, WinSQL will replace boy with the actual date and the final query will look like:

Adding new custom templates

To add new templates, click Code Templates under the Tools menu, which brings up the following screen.

This screen lists existing templates. WinSQL automatically adds a handful of templates that you can use, which you see on the screen.

Click the add button to add another template.

Enhanced IntelliTips

Intelli-Tips have been enhanced to filter data as you type object names. Previous versions of WinSQL used to display every object making it difficult to find the desired object easily. Now it filters the data as you type a partial name

Latest ODBC Drivers

Version 9.0 of WinSQL includes the latest ODBC drivers from Data Direct Technologies.