IDERA Rapid SQL Quick Start Guide

The Best IDE for Writing SQL Code on Relational Database

Version 18.0.x

Introducing Rapid SQL

Rapid SQL is an integrated development environment that enables developers to create, edit, version, tune, and deploy server-side objects residing on Microsoft SQL Server, Oracle, Sybase Adaptive Server, InterBase/Firebird, IBM DB2 for Linux, Unix, and Windows, and IBM DB2 for z/OS databases. Its unified database development environment provides extensive graphical facilities that simplify SQL scripting, object management, reverse engineering, database project management, version control, and schema deployment. With Rapid SQL, programmers can develop and maintain high- quality, high-performance client/server and web-based applications in less time, and with greater accuracy.

About Rapid SQL

Your new application provides tools that can be used by a number of functions within an organization using DBMS from multiple vendors, in testing, development, or production environments. Datasource management tools: Datasources must be registered. Datasources can be registered manually or semi-automatically. Storage can be registry-based or file-based, and a network storage option facilitates shared use of datasource catalogs. Object management tools: Your application supports a wide range of database objects and related elements for each DBMS. You can create new objects, edit existing objects, and use a range of object operations that support common, general or DBMS-specific actions. Coding Environments/Editors: Fully-featured environments dedicated to SQL, DDL and Active Script development are provided. Execution options are available as appropriate, and environment-specific related features, such as rollback/commit and query plan options, are provided. Related coding aids include on-the-fly semantic and syntactic validation, text substitution shortcuts, and visual query building tools. Related execution tools include script and file execution facilities. Project Management, Version Control, and Script Library: Rapid SQL database project management facilities help you organize, alter, and keep track of changes to database objects or SQL scripts. The project management facilities act as a repository to maintain all source code for a database project. Rapid SQL also incorporates version control functions and build management facilities to help you manage and build projects. Other Time-savers and Productivity Tools: Working within the Rapid SQL environment, you have access to database and file search facilities, use visual difference to compare files or database objects, and access the Microsoft Windows task scheduler. License-specific Add-ons: Depending on the licenses you purchased, you have access to the following tools:
  • The Rapid SQL Code Analyst helps you identify time-consuming lines of code. It lets you perform detailed response time analysis, benchmark the execution of one or more procedures or functions, save response time metrics, and perform intelligent comparisons against current execution times.
  • Debuggers, available for IBM DB2 for Linux, Unix, and Windows, Oracle, Sybase, and SQL Server, let you test functions and procedures. A Profiler is available for Oracle datasources.

Technical Requirements

Before using Rapid SQL, please verify that your environment meets the requirements listed below: Note: Users need full registry privileges during the installation and access to the keys under HKEY_CURRENT_USER in the registry after installation.

Browser Requirements

Rapid SQL requires Microsoft Internet Explorer 11 or later.

Hardware Requirements

IDERA recommends the following minimum hardware requirements:
  • 1 GHz or faster CPU
  • 3 GB of RAM
  • 1 GB of free disk space
  • 1024 x 768 display

Operating System Requirements

Rapid SQL supports the following operating systems:
  • Windows 7, 8, 8.1, and 10 (32-bit and 64-bit)
  • Windows Server 2008 SP1 and 2008 R2 (32-bit and 64-bit)
  • Windows Server 2012 SP1, 2012 R2, 2016, and 2019
Windows 7 Support Notes Windows 7 provides two user types: Standard users and Administrators. Rapid SQL can be installed or uninstalled by an administrator or by a standard user using an administrator token. Standard users can run Rapid SQL. For the purpose of running Rapid SQL, default standard user token privileges should not be modified. Modifying standard user token privileges can result in licensing issues that will prevent Rapid SQL from operating correctly. 32-bit versus 64-bit Application Considerations and Restrictions If you install the 64-bit version of Rapid SQL, and you are using custom drivers, you must be using 64-bit versions of those drivers when using the 64-bit version of Rapid SQL. Similarly, 32-bit versions of custom drivers must be used with the 32-bit Rapid SQL installation. For version control integration, if you install the 64-bit version of Rapid SQL, you can work with either a 32-bit or 64-bit MSSCCI provider. The feature is controlled from the Options Editor's Version Control tab (File > Options > General > Version Control).

DBMS Support and Connectivity Options

Dedicated Support Connectivity Options

Rapid SQL provides dedicated connectivity to a specific version range of IBM DB2 for Linux, Unix, and Windows, IBM DB2 for z/OS, Microsoft SQL Server, MySQL, PostgreSQL, Oracle, Sybase, and Teradata databases. The following connectivity options are provided:
  • Native IDERA drivers: Rapid SQL is packaged with a set of native drivers, each requiring a DBMS-specific client to be installed.
  • JDBC drivers: Rapid SQL can connect to a datasource more directly using one of the packaged, third-party JDBC drivers. No additional connectivity components need to be installed. One or more third-party drivers, tested against Rapid SQL, are installed with Rapid SQL.
The following table provides a summary of resources/requirements for connectivity to dedicated DBMS platforms. For each platform, it lists supported versions, the client software that must be installed if using native IDERA clients, and the third-party, Type 4 JDBC drivers packaged with Rapid SQL.

JDBC Driver for Sybase IQ

* About Technical Previews - Technical previews are intended to introduce a new DBMS platform only. The available functionality is typically minimal. For example, command Line startup, standard datasource registration and connection features, limited Datasource Navigator tree functionality, a minimal set of object actions, as well as SQL Editor execution and related, common SQL Editor functionality such as Query Options, Paste SQL Syntax, and Paste SQL Statement. Access may not be covered under SE or trial licenses.

IDERA Team Server Support

Rapid SQL can make use of datasource definitions stored on an IDERA Team Server.

Generic JDBC/ODBC Connectivity

Generic JDBC/ODBC connectivity to non-dedicated DBMS systems or non-database datasources is also provided. Rapid SQL can connect to a datasource using a customer- provided, third-party JDBC version 4.0 or ODBC version 3.0 driver. Minimal Rapid SQL functionality is provided, including a basic Explorer tree and SQL querying.

IBM DB2 for Z/OS Stored Procedure Requirements

When working against an IBM DB2 for z/OS data source, Rapid SQL relies on the following stored procedures, provided as an optional installation step in setting up the DB2 subsystem:
  • DSNWZP
  • DSNUTILS
  • ADMIN_COMMAND_DSN
  • ADMIN_COMMAND_DB2
Prior to using Rapid SQL against an IBM DB2 for z/OS data source, ensure that these components are installed on theserver. Specifically-tested JDBC/ODBC Connectivity Products: Apache Hive/Hadoop Rapid SQL has been successfully tested against Apache Hive/Hadoop datasources using the Hortonworks ODBC driver. Similarly, Cloudera Impala datasources have been tested using the Cloudera JDBC driver. In both cases, SQL querying and a Datasource Navigator tree are available. DBMS Versions no Longer Supported Rapid SQL is no longer being tested against Sybase ASE versions before 15.7.

Installing and Licensing Rapid SQL

Before you can register any IDERA application, you must meet the minimum technical requirements. If you meet all the minimum technical requirements, you can install Rapid SQL. After installation, you must license the application. The following topics walk you through this process:
  • Technical Requirements
  • DBMS Support and Connectivity Options
  • Installing Rapid SQL
  • Licensing Your Product

Installing Rapid SQL

Beginning with version 16.5 (2016+), the installers for DBArtisan and Rapid SQL are combined and by default, both products are installed. The installation wizard installs all corresponding files on your machine. The installer offers common options such as license agreement, file and folder selections, and shortcut options. An application-specific option lets you associate file suffixes of elements such as SQL scripts with DBArtisan. The panels presented by the wizard vary depending on whether you are upgrading or performing an initial installation. Caution: If you have not registered a license when you start installation, you will be prompted to register a license. For more information, see Licensing Your Product. After the installation is complete, we recommend that you reboot your machine.

Viewing the Installation Options

If you want to change the default installation location or adjust the icons installed for these products, click the Options button. The Setup Options screen of the installation wizard appears. The Setup Options screen allows you to select an installation location and what icons or menu offerings you want to appear on your desktop or in your Start menu, respectively. Click Cancel to disregard any of the selections you made or click OK to save your selections and return to the screen you were on when you clicked the Options button. Click Install to install the product using the selections you made in the Setup Options screen.

Licensing Your Product

Each IDERA client application requires one or more licenses to run. An IDERA product, such as Rapid SQL, has a baseline license that provides basic feature support for that product. Also, incremental licenses may be required to support specific DBMS platforms, product add-ons, or other functions. For more information, see the following topics:
  • Licensing Overview
  • Licensing Your Application
  • Selecting a License Category During Startup
  • Online/Offline Mode and Concurrent License Checkout

Licensing Overview

Simplified licensing now offers a single Rapid SQL license for single and multiplatform environments. Standard and Professional editions now are simply Rapid SQL. The following topics provide a high-level discussion of key licensing topics and direct you to sources of more detailed information.
  • Viewing Your License Type and Modules
  • Understanding Trial, Workstation, and Networked Licenses
  • Directing Queries Regarding Licenses

Viewing Your License Type and Modules

The About... dialog, available from the Help menu, displays your license type and each license module currently registered.

Understanding Trial, Workstation, and Networked Licenses

Three kinds of licenses are available: Trial, Workstation, and Networked. Trial licenses A license for a 14-day, full-featured trial version of the product. The trial license must be registered before you can use the product. Workstation licensing A license or set of licenses is tied to a particular workstation. The product can only be used on that workstation. Networked licensing Networked licenses are administered and distributed by a central License Server (IDERA License Center or Acresso FLEXnet Publisher). There are two types of networked licenses: Concurrent and Networked Named User. With Concurrent licensing, users on different machines take turns using licenses from a shared pool. With Networked Named User licensing, licenses are pre-assigned to specific users setup on the license server's user list. Those users are guaranteed to have licenses available any time. NOTE: Concurrent licenses can be borrowed for use without a network connection. For details, see Online/Offline Mode and Concurrent License Checkout.

Directing Queries Regarding Licenses

Questions regarding license availability, feature availability, and client or server licensing, should be directed as follows: If you work in an organization that uses networked licensing, direct any questions to your site’s Rapid SQL administrator. If you are using workstation licensing, direct licensing questions to your IDERA representative.

Licensing Your Application

See the following topics for details on registering your product:
  • Registering a Trial or Workstation License during Installation
  • Registering a Workstation License After Application Startup
  • Registering By Phone
  • Registering a Networked License

Registering a Trial or Workstation License during Installation

Shortly after initiating the download of a trial version of an IDERA product, you should receive an email with a serial number you must register during installation. Similarly, if you purchase an IDERA product while no trial version is active, you will receive a serial number that must be registered during installation.
  • Start the installation. An IDERA License Registration dialog appears.
  • Copy the serial number from the email and paste it in the Serial Number field.
  • Enter your EDN account credentials in the Login or Email and Password fields. If you have not previously created an EDN
  • account or have forgotten your password, click I need to create ... or I’ve lost my password.
  • Click Register.
Your activation file should be downloaded and installed automatically. If this does not happen, click the Trouble Connecting? Try Web Registration link and follow the prompts. If you still have problems, see Registering By Phone.

Registering a Workstation License after Application Startup

The following instructions assume that you have received a workstation license by email and that you currently have a valid trial license. If you did not install a trial version or the trial period has expired, follow the instructions in Registering a Trial or Workstation License during Installation instead.
    • On the Help menu, select About, and then on the dialog that opens, click Manage to open a license manager dialog.
    • On the Serial menu, select Add.
    • Copy the serial number from the email and paste it in the Add Serial Number dialog, and then click OK.
    • Right-click on the serial number you added, and then select Register from the context menu. A registration dialog opens.
1. Note: The Registration Code box shows a machine-specific identifier required with other registration methods.
  • Ensure that the Register using Online Registration radio box is selected.
  • Provide Developer Network credentials in the DN Login name or Email and DN Password boxes. If you have not previously
  • created an EDN account or have forgotten your password, click I need to create ... or I’ve lost my password.
  • Click Register.
  • If prompted to restart the application, click Yes.
Your activation file should be downloaded and installed automatically. If this does not happen, click the Trouble Connecting? Try Web Registration link and follow the prompts. If you still have problems, Registering By Phone.

Registering By Phone

If you have problems with either of the above procedures, you can register licenses by phone. You will have to provide:
  • Developer Network credentials
  • The registration code displayed in the IDERA License Registration dialog that appears when you start an unlicensed application
  • The product base license serial number
  • The license serial numbers for any additional features you have purchased.
For North America, Latin America, and Asia Pacific, call (415) 834-3131 option 2 and then follow the prompts. The hours are Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time. For Europe, Africa, and the Middle East, call +44 (0)1628-684 494. The hours are Monday through Friday, 9 A.M. to 5:30 P.M. U.K. time. Shortly after phoning in, you will receive an email containing an activation file. Then do the following:
  • Save the file to the desktop or a scratch directory such as c:\temp.
  • On the Help menu select About and then on the dialog that opens, click Register. A registration dialog opens.
  • Select the I have received an activation file (*.slip or reg*.txt) radio box.
  • Click the Browse button and use the Select License Activation File dialog to locate and select the activation file you installed.
  • Click the Import button to import the activation file and when complete, click the Finish button.
  • If prompted to restart the application, click Yes.

Registering a Networked License

If you work in an organization using Networked licensing, an administrator, department head, or someone performing a similar function will provide you with an activation file. Once you receive the file, save it to the license subfolder of your product’s main installation folder (typically C:\Program Files\IDERA\ \license\), then restart the application. No additional steps are necessary.

Selecting a License Category During Startup

During startup, if multiple concurrent license categories are available, you are prompted to select a category to use for this Rapid SQL session. Multiple license categories can be set up to provide differing feature access or access to different DBMS versions. Feature and DBMS version access are typically distributed across multiple license categories to optimize the use of a site’s purchased licenses. Note: This dialog also includes the option to remember your selection on subsequent startups. If you select that option, you can subsequently use the Select Licenses button on the About... dialog (Help > About) to select a different license. Contact your License Administrator for details on individual license categories or requests for additional feature or DBMS support.

Online/Offline Mode and Concurrent License Checkout

Concurrent licenses can be used in both online and offline modes. In online mode, you must have a continuous network connection to your License Center. Licenses are checked out on startup and checked back in on shutdown. You can also use a license in offline mode. When you explicitly check out a license for offline use, you can use the license without a connection to your License Center for a specified duration. This lets you work while travelling or commuting, work away from your primary work area, or use the license when a network connection is unavailable or not required. Note: Contact your site administrator for information on offline license availability, the maximum duration, offline license policy at your site, or any other issues arising from online license usage. To check out a license for offline use:
  • On the Help menu, select Checkout License. The Check Out Licenses For Offline Use dialog opens.
  • Select the check box associated with each individual license you want to check out.
  • In the Checkout Duration box, type the number of hours that you can use the offline license without a network connection to the License Center.
  • Click OK.
You can work offline for the specified duration. The duration period begins immediately. If you subsequently establish a network connection to the License Center before the license duration expires, you can indicate to the License Center that the offline license is no longer required. To indicate that an offline license is no longer required: 1.On the Help menu, select Checkin License. There is no interruption in Rapid SQL usage. The license is not actually checked in until you shut down Rapid SQL.

Rapid SQL Tutorial exercises

The exercises that follow walk you through the Rapid SQL’s major functional areas. After completing these exercises, you will have the foundation you need to explore the many features and benefits of Rapid SQL. You’ll have learned how to competently manage the major database administration and development tools provided. This guide is divided into a set of sessions:
  • Session 1: Getting Started
  • Session 2: Productivity Enhancers
  • Session 3: Scripting
  • Session 4: Working with Code Workbench
  • Session 5: Building a Database Project
  • Session 6: Visual Query Builder
  • Session 7: Live Data Editor
  • Session 8: Code Analyst
  • Session 9: SQL Debugging and Profiling
You can use this basic tutorial as a roadmap of product highlights, but also to help you find your path to explore Rapid SQL. Once you’ve started, you can select Help Topics from the Help menu to find many additional resources that complement and build on many of the activities shown in this tutorial. Note: The tutorial exercises make use of the sample SQL Server database, AdventureWorks. Since this is not part of a typical install, you can obtain the files from installation CDs or online sources to install and attach that database. Alternatively, you can perform the tasks targeted for the AdventureWorks database against another available database. No destructive actions are initiated in the exercises.

Session 1: Getting Started

Before anything else, you must perform the following tasks:
  • Starting the Rapid SQL Application
  • Registering Cross-Platform Datasources to Rapid SQL

Starting the Rapid SQL Application

How you start Rapid SQL depends on the type of application you are evaluating:
  • InstantOn version: start the application by double-clicking the file you downloaded.
  • Fully-installed version: The Start menu sequence for Rapid SQL is always in the form Programs > IDERA Rapid SQLversion identifier> Rapid
SQLversion identifier, where version identifier reflects the version you are running. To get started Run Rapid SQL. The first time Rapid SQL starts, a dialog opens, prompting you to set up datasources. In addition to letting you manually set up individual datasources, a number of more automated methods are available. If you have installed and used other IDERA tools, Rapid SQL can find any active datasources being used by those tools. Also, Rapid SQL provides a Discover Datasources feature that automatically searches the DBMS configuration files on your system for datasources that are not currently registered. Since other IDERA tools let you export datasource definitions to file, you also have the option of importing these definitions. For the purpose of this tutorial, click Cancel. You will be registering a datasource manually. The main Rapid SQL window opens.

Proceed to Registering Cross-Platform Datasources to Rapid SQL.

Registering Cross-Platform Datasources to Rapid SQL For now, you will register a datasource manually.
  • On the Datasource menu, select Register Datasource. A Datasource Registration Wizard opens.
  • Choose Microsoft SQL Server as the DBMS type and then click Next. The next panel opens.
  • Ensure that a Registration type of User defined is selected, specify the Host name of an SQL Server datasource on your network, override the Datasource Name with SAMPLE_DATASOURCE and then click Next.
  • Provide valid credentials in the User ID and Password boxes, and then select the Auto-Connect? checkbox to eliminate having to provide credentials each time you connect to this datasource.
  • In the left-hand pane, select Datasource Group, select the MS SQL Server folder, and then click Finish.
  • Note: The Datasource Group panel also lets you assign a category to a datasource. This provides a means to visually distinguish between different server purposes, development vs. production, for example, in your enterprise. Categorization is a customizable feature.
  • Select Yes when prompted to connect to the new datasource.
  • Rapid SQL offers the same easy-to-use Datasource Registration Wizard for all supported DBMS platform connections. The connection information only needs to be set up one time for each platform and can be saved locally or in a common datasource catalog for use by other IDERA products.
By default, Rapid SQL stores datasource definitions in the Windows Registry. There is also a local, file-based option. IDERA products supporting these methods can share datasource catalogs on the same machine. There is also a network-shared storage option. Lastly, datasource definitions can be stored centrally on an IDERA Team Server 2016 for use by Rapid SQL users. Rapid SQL also offers the ability to import and export datasource definitions. This lets you share definitions among users and across datasource storage methods. Proceed to Session 2: Productivity Enhancers.

Session 2: Productivity Enhancers

  • This session focuses on some commonly used time-saving features:
  • The Datasource Navigator Tree
  • Creating an Object Using the Object Creation Wizard
  • Working With an Existing Object Using the Object Editor
  • Object Documentation and Reporting
  • Working With Code, Files and Data
  • Setting Environment Options
  • Script Library
  • Working with Scripts and Files
  • Viewing Data
  • Retaining Datasource Navigator View Settings
  • Datasource Navigator Bookmarks
  • Setting Keyboard Shortcuts and Hotkeys
  • Referencing Most Recently Used Datasources

The Datasource Navigator Tree

Rapid SQL makes it easy and intuitive to navigate between datasources and to drill- down into atomic database objects within the Datasource Navigator tree. The Datasource Navigator displays all registered datasources and serves as the entry-point for much of Rapid SQL’s advanced functionality. 1.Click on the Navigator’s dropdown and ensure that Organize By Object Type is selected. 2.Select and expand the SAMPLE_DATASOURCE > Databases > AdventureWorks node to display the database object sub-nodes. Proceed to Creating an Object Using the Object Creation Wizard.

Creating an Object Using the Object Creation Wizard

  • From within the Navigator tree, you can create any database object using simple Object Creation Wizards. The following is an example of how to use the Table Object Creation Wizard. It is similar to the Object Creation Wizards available within Rapid SQL for all database objects and other supported elements.
  • Right-click on the Tables node and select Create. A Create Table Wizard opens.
  • Select a Schema and provide a Name of SAMPLE_TABLE. Leave the remaining default settings and click Next.
  • Add a column, using a Name of Sample_Column1 and select a Type of char. Experiment with the Add Column and Delete buttons, and with selecting a column and modifying its attributes.
  • Click Finish. The DDL View panel opens showing the DDL that will be used to create the new table.
  • Deselect the Launch Object Editor After Execute and then click Execute. Rapid SQL builds the platform-specific SQL code, syntactically-correct and ready to run the first time. There is no SQL coding required in any of the Rapid SQL Object Creation Wizards.
Proceed to Working With an Existing Object Using the Object Editor.

Working With an Existing Object Using the Object Editor

While the wizard offered you the option to automatically open an editor on creating the table, you can also manually open an editor. 1.In the Navigator, ensure that the Tables node is expanded and then right-click on your new table and select Open. Object Editor features are as follows:
  • All Object Editors provide standardized, multi-tabbed windows for each database object type.
  • All Object Editors provide fully-functional toolbars for easy object management.
  • Rapid SQL has full knowledge of the underlying DBMS system catalog, syntax and alteration rules, so the user can concentrate on what needs to be done, not on how to do it.
  • Drop-down boxes allow you to easily move between owners and objects.
  • The Rapid SQL Object Editors easily perform operations that would normally require painstaking and error-prone scripting, such as deleting or inserting columns in a table while preserving data, dependencies, and permissions. Rapid SQL analyzes the database catalog to determine its structure, and then automatically generates the SQL script required for the extended alteration. For instance, when a full table alteration is required, Rapid SQL automatically unloads and reloads the data, eliminating tedious work.
2.Close the Object Editor window. Proceed to Object Documentation and Reporting.

Object Documentation and Reporting

Rapid SQL provides rich, detailed HTML Reporting for all database objects. Building a browser-ready report for any object is only a few mouse-clicks away.
  • Expand the Tables node, right-click on any table and select Report from the menu. A Report dialog opens.
  • Enter a destination Report Home Page File Name. This can be a network web server directory.
  • Enter a Report Title and click Execute.
The HTML report will automatically be displayed in the Rapid SQL application workspace. For example: The HTML report can be saved to a new file or referenced in the file named above. Note: All HTML reports are browser-ready and suitable for posting directly to the web. Proceed to Working With Code, Files and Data.

Working With Code, Files and Data

Rapid SQL provides many features and powerful development tools for creating and executing SQL code and working with data. Note: For purposes of this exercise, we are only covering the high-level functionality of the major features and tools within Rapid SQL. Proceed to Setting Environment Options.

Setting Environment Options

The Options dialog allows you to set the Rapid SQL development environment to meet your needs. 1. Select File > Options from the menu. The Options dialog opens. The Options dialog has one page per option category. Select an option category in the left-hand pane and you can subsequently set options on that page. Options are applied when you click OK. 2. Close the Options dialog. Proceed to Script Library.

Script Library

The Script Library provides a drag-and-drop library interface of all supported DBMS syntax, SQL syntax, built-in functions, optimizer hints, and SQL-conditional syntax. Additionally, it provides the ability to create custom folders to store commonly-used code for quick and efficient access, as needed. To open the Script Library:
  • Select View > Script Library. The Script Library window opens.
  • Expand the Microsoft node and then expand the Schema sub-node.
  • Right-click the Procedures node and select Open. The selected code opens in the SQL Editor window and is ready for execution.
  • Right-click in the editor window and select Close from the context menu. To add a custom folder to the Script Library
  • Right-click the Script Library folder.
  • Select New Folder from the context menu. A new folder is added to the Script Library folder.
  • To close the Script Library window: Select View > Script Library.
  • Proceed to Working with Scripts and Files.

Working with Scripts and Files

Rapid SQL extends the auto-generation of SQL code by allowing you to run your scripts across multiple databases at the same time. File Execution Facility
    • Files containing SQL scripts can be added to the File Execution Facility and executed immediately or scheduled to run later.
    • On the Tools menu, select File Execution Facility. Rapid SQL opens the File Execution Facility dialog box.
    • To locate the file, you want to execute, click Add. Use the Add Files dialog box to locate and select a file.
    • On the Target tab, select the datasources to run the script against.
    • On the Output tab, select the desired output option. For the purposes of this example, select Graphical Output.
Note: To enable the scheduling function for the script, you must select the File Output option.
  • If you want to send notification that the script has executed, on the Notify tab, complete the target information.
  • Click Execute if you want Rapid SQL to run the script against the target datasources. Otherwise, close the dialog without executing.
Note: Separate script output windows are created for each selected datasource. Script Execution Facility The Tools menu also offers a Script Execution Facility. Similar to the File Execution facility, it lets you type or paste the script to be executed. Proceed to Viewing Data.

Viewing Data

Rapid SQL provides several options for browsing data. Also, it gives you the ability to construct even the most complex SQL statements with point-and-click ease. Select * Browsing
  • On the Datasource Navigator, expand the MS SQL Servers node.
  • Expand any database you know has table data, expand the Tables node, right- click a table, and then click SELECT * FROM. All columns and rows from the table are displayed in the active workspace.
  • Close the workspace window.
Proceed to Retaining Datasource Navigator View Settings. Retaining Datasource Navigator View Settings
  • Click on the dropdown at the top of the Datasource Navigator.
  • Select Retain View Settings. Next time, the Navigator will open just as you left it. All connections that were present when you closed Rapid SQL will be re-established. Proceed to Datasource Navigator Bookmarks.

Datasource Navigator Bookmarks

Rapid SQL allows you to set bookmarks for frequently visited database objects.
    • Right-click on any node in the Datasource Navigator.
    • Select Add Bookmark and use the Add Friendly Bookmark Name dialog to optionally provide a new name, and create the bookmark.
After Bookmarks are defined, you can use them to easily navigate to commonly used datasource resources. In this case, you would select the dropdown at the top of the Datasource Navigator, selecting Bookmarks > SQLServer and then selecting the bookmark you just created. The Bookmark Manager handles maintenance of Bookmarks.
  • Select the dropdown at the top of the Datasource Navigator and then select Bookmarks>Bookmark Manager.
  • Close the Bookmark Manager dialog. Proceed to Setting Keyboard Shortcuts and Hotkeys.

Setting Keyboard Shortcuts and Hotkeys

    • On the Tools menu, select Customize.
    • In the Customize dialog, go to the Keyboard tab.
The Keyboard tab can be used to set keyboard shortcut hotkeys for all areas of Rapid SQL functionality.
  • Close the Customize dialog.
Proceed to Referencing Most Recently Used Datasources.

Referencing Most Recently Used Datasources

1. Select File > Recent Datasources and then select a datasource. This will automatically place you on the datasource within the Navigator, ready to work with active connection. Proceed to Session 3: Scripting.

Session 3: Scripting

This session looks at Rapid SQL’s development environment:
  • Generating Code
  • Right-click feature
  • Automated error detection and coding assistance
  • Other coding aids

Generating Code

By providing several code generation and assistance options, Rapid SQL makes cross- platform development easy for developers of all experience levels. Note: The following examples build on the SAMPLE_DATASOURCE registered earlier in this Evaluation Guide. These examples can be applied to any registered datasource for any of the supported platforms. Code Generation Facility The Code Generation Facility can be used to create complete procedures, functions or packages revolving around views or tables.
  • From the menu, open Tools > Code Generation Facility.
  • Select the SAMPLE_DATASOURCE datasource, the AdventureWorks database, and the Purchasing schema from the dropdown list boxes.
  • Select the Vendor table, Name as the input column and all columns for output.
  • Select Select as the code option.
  • Select a file to save the generated script and check Open.
  • Click OK and the DDL to create the procedure will be generated and displayed in an editable window, called the DDL Editor. You can edit the name of the new procedure and any of the generated code at this time. Name the new procedure sample_select_vendors.
  • Click on the Execute button to submit the DDL and create the procedure.
The indicated file will be saved on the selected directory. Note: No SQL statement coding is required to generate complete stored procedures and packages. If applicable, Rapid SQL allows all generated code to be previewed and edited to fit any development need. Proceed to Right-click feature.

Right-click feature

Similar to the Code Generation Facility, the “right-click” code generation feature can be used to create complete procedures, functions or packages revolving around views or tables.
  • From the Datasource Navigator tree, expand the SAMPLE_DATASOURCE > AdventureWorks > Tables sub-node.
  • Right-click on the Vendors table.
  • Select Generate > Procedure > Select.
  • Select Name as the input column, and leave all output columns selected.
  • Click OK and the DDL to create the procedure will be generated and displayed in the DDL Editor. You can edit the name of the new procedure and any of the generated code. Name the new procedure sample_select_vendors2.
  • Click on the Execute or Step Execute button to submit the DDL and create the procedure.
Proceed to Automated error detection and coding assistance.

Automated error detection and coding assistance

Rapid SQL provides a range of features that detect or help you avoid errors and save keystrokes in developing your scripts. To enable these features:
  • On the File menu, select Options. The Options dialog opens.
  • In the left-hand pane, expand the ISQL node and then select Code Assist.
  • On the Code Assist panel:
    • Ensure that Enable Code Complete is selected.
    • Ensure that Severity levels for semantic validation problems has Warning selected.
    • Ensure that Enable Real-time syntax checking is selected.
  • Click OK.
To see these features in action:
  • On the File menu, click New, and then SQL. Rapid SQL opens the SQL Editor window. You can add SQL code via your method of choice (free-form typing, retrieve from a file, paste copied code, etc.).
  • Type SELECT * FROM and stop typing. Note the error condition. Rapid SQL can run a syntax check any time there is an interval of 1.5 seconds between keystrokes. You can also disable automatic syntax checking and only run a check when you manually initiate it. Syntax error annotation persists until you correct the problem.
  • This time, type a fragment that includes the name of a nonexistent object, SELECT * FROM NON.OBJECT, for example. For now, ignore any popups. The warning condition is a result of on-the-fly semantic validation. Rapid SQL notifies you when a script contains a reference to an object that Rapid SQL cannot resolve.
  • Type SELECT * FROM followed by a space and then stop typing. If no popup appears, press CTRL+SPACE. The Code Complete suggestion box lets you select from objects or object name components such as databases or schema. This feature saves keystrokes and minimizes typing errors. See the online Help for full descriptions of these features.
  • Close the current SQL Editor window. To restore Rapid SQL settings:
    • On the File menu, select Options. The Options dialog opens.
    • On the Code Assist panel, click the Restore defaults button.
    • Click OK.
Proceed to Other coding aids.

Other coding aids

Rapid SQL provides extensive, easy-to-use coding aids for all of the supported DBMS platforms, throughout the application. Aids are provided in the form of ready-to-use code templates and blocks of syntactically correct code. Paste SQL
  • From the Datasource Navigator tree, expand the SAMPLE_DATASOURCE > AdventureWorks sub-node.
  • Select File > New > SQL to open an SQL Editor window.
  • Select Edit > Paste SQL Statement to open the Paste SQL window.
  • On the Paste SQL dialog, select Sample_Datasource from the Datasource dropdown, AdventureWorks from Database, All Owners from Owner, and an Object Type of Tables.
  • In the Tables list, select Purchasing.Vendor.
  • Under the Columns list, click ALL.
  • Click the Select radio button.
  • Click Paste Statement to copy the generated code to the SQL Editor window.
You can use the statement as is, or modify the code as needed. Paste SQL Syntax
  • Select File > New > SQL to open a fresh SQL Editor window.
  • Select Edit > Paste SQL Syntax to open the SQL Syntax for SQL Server window.
  • Select a template and click Paste to copy the code template into the SQL Editor window.
  • Add your own code to complete the needed operation. Proceed to Session 4: Working with Code Workbench.

Session 4: Working with Code Workbench

The Code Workbench lets you configure resources for two SQL Editor features:
  • Code Templates
  • Auto Replace
Code templates are complete code blocks that can be easily added to open windows or scripts with a few keystrokes. When you type CTRL+SPACE, the Code Assist menu opens, letting you select a code template for insertion in the editor window. Auto Replace lets you define shortcuts consisting of a few characters that represent longer character strings. Instances of these Auto Replace expressions are automatically replaced by the replacement string on activation events such as typing SPACE, TAB, or RETURN. This feature is useful for creating shortcuts for one-line commands or SQL statement subsets, or even to detect and fix common typographical errors such as teh for the. For example, consider an Auto Replace definition with an expression of sel to represent Select * From: If the associated activation event includes a SPACE then on typing sel followed by pressing SPACE, the following replacement occurs: Rapid SQL loads a default set of Auto Replace and Code template definitions at startup, but you can also add, edit, and delete definitions. Also, you can save sets of definitions to file and subsequently load specific sets of definitions, allowing you to customize your templates to different platforms or development projects. To invoke Code Workbench settings:
  • Select Tools > Code Workbench. The Settings tab lets you enable the Auto Replace and Code Template features.
  • Inspect the Code Templates and Auto Replace tabs.
  • Click OK.
Proceed to Session 5: Building a Database Project.

Session 5: Building a Database Project

Rapid SQL provides an excellent team development environment by allowing you to reverse engineer live database objects into off-line SQL source code files. You can subsequently perform tasks such as distribute the files or add them to a Version Control System (VCS). This example will reverse engineer the table objects from the Microsoft SQL Server AdventureWorks database into a Rapid SQL project
  • Select File > New > Project to open the wizard.
  • Enter sample_project as the name, and accept the default target directory. Select From Database and click OK.
  • On the Connection page, select SAMPLE_DATASOURCE and click Next.
  • On the Catalogs page, select AdventureWorks and click Next.
  • Click Schemas to open the Schema Selection dialog. Select Human Resources, Purchasing, and Sales, and then click OK. The Object Selection page opens.
  • Under Object Types, select Tables. Then, under Objects, expand the Tables node and select the Purchasing.Vendor, Sales.SpecialOffer, and HumanResources.Employee tables. Click Next.
  • On the Options page, leave the default selections and click Finish.
  • When the Execute page shows that reverse engineering is complete, click Continue.
The Project tab displays the results of the reverse engineering. Before closing the Project tab, investigate the individual project elements. Also, check the File and Project menus for project and version control options. Proceed to Session 6: Visual Query Builder.

Session 6: Visual Query Builder

Rapid SQL gives you the ability to construct complex SQL statements with point-and- click ease using the Visual Query Builder.
    • From the Navigator tree, right-click on the HumanResources.Employee table and select Build Query.
    • The table is automatically added to the Query Builder workspace.
    • On the Tables/Views tab, right-click on the Department table and select Add.
    • Similarly, on the Tables/Views tab, right-click on the EmployeeDepartmentHistory table and select Add.
    • Rearrange the contents of the window to look like the following graphic.
    • Select the columns indicated in the graphic.
Note that the tables are automatically identified as being joined by any columns with the same name and datatype. Also, note that the query is being built in the lower pane.
  • Click on the DML tab to expose the visual query building clauses and options. You can right-click on any clause to easily add the code to the query.
  • Experiment with adding, deleting, and modifying clauses.
  • Click the Execute button to execute the query.
The results will display in the lower window. Before closing the Query Builder, experiment with additional options. Try selecting a different statement type, such as Insert or Update, from the dropdown at the top of the Query Builder window. Use the different clauses on the DML tab. Note: Any visual query builder session can easily be saved to a file for later use. Proceed to Session 7: Live Data Editor.

Session 7: Live Data Editor

From the Navigator, right-click on the Purchasing.Vendor table and select Edit Data. In the Data Editor Filter dialog, click Add All to add all columns to the editing session. At this point, you can add a WHERE clause that will filter for only the desired data. Note that Rapid SQL builds the SQL to retrieve the data to be edited in the Select Statement area. Click OK. A Data Editor opens. Note the dropdown at the left of the toolbar. The editing window has LIVE and BATCH modes:
  • LIVE mode commits your changes each time you move to a new row.
  • BATCH mode will allow you to move within the window and commit your changes when ready.
Changes made in BATCH mode can be canceled by selecting the Reload Data icon. At any time during the session, you can change the filter parameters by selecting the Filter Data icon. Proceed to Session 8: Code Analyst.

Session 8: Code Analyst

The Code Analyst allows you to capture run-time statistics on executable database objects, including stored procedures and functions. Not only can you capture statistics for single objects, but you can group more than one object. To get started
  • Select Tools > Code Analyst.
Note: For Code Analyst to run, 5 repository tables will be created in the database. Select the database you would like the tables to be installed on and press OK. Once the tables are installed, you are ready to start defining a session.
    • On the Code Analyst toolbar, click the Create New Collection button.
    • On the Code Analyst Object Selection dialog, provide a Session Name, locate and select the objects to be executed, and click Next.
    • Use the Code Analyst Object Initialization dialog to initiate providing input parameters as required, change the order of execution, and when ready, click Finish.
Once the session has been run, the total time for the execution is displayed in the Run Summary tab.
  • Select the other tabs to view the tabular and graphical representation of the execution details on your selected objects. For example:
    • The Run Detail tab shows a breakdown of the different objects that make up the session.
    • The Unit Detail tab contains the specific time measurements for individual SQL statements.
  • Close the Code Analyst window.
  • Proceed to Session 9: SQL Debugging and Profiling.

Session 9: SQL Debugging and Profiling

Rapid SQL offers the following facilities that help you test and optimize code:
  • SQL Debugging
  • SQL Profiling- Oracle Only

SQL Debugging

The SQL Debugger is another database productivity tool that lets you debug SQL Server, Oracle, Sybase ASE or DB2 stored procedures, as well as Oracle functions. SQL Debugger simplifies the task of finding coding errors.
  • In the Navigator, expand the Procedure or Function node.
  • Right-click the object and select Debug from the context menu.
  • If the procedure or function takes input parameters, the Procedure Execution window prompts you to enter values.
  • Enter input values and press Continue.
Tip: Rapid SQL allows the user to save the input variable values to a file for later use. This is very helpful for procedures/functions with many input variables that need to be run repeatedly. The application opens the SQL Debugger Interface. The Debugger features basic execution, line-by-line execution, breakpoint support, and other common debugging features. For details, refer to the relevant online Help topics. Proceed to SQL Profiling- Oracle Only.

SQL Profiling- Oracle Only

The SQL Profiler within Rapid SQL provides the ability to capture the metrics of various PL/SQL programmable objects as they are executed in the database. It quickly identifies performance bottlenecks by first calculating the overall runtimes of objects like Oracle packages, and then computing the amount of time each line of PL/SQL code spends executing. Information is presented in an easily viewed, drill-down format.
  • To start a profiling session, use the Tools menu option and select SQL Profiler > Start.
  • Enter a name for the profiling session or select an existing name from the dropdown. Press OK. The Profile session is now active.
  • Execute the programmable object (i.e. Stored Procedure) you wish to capture metrics on.
  • When finished, select Tools > SQL Profiler > Stop. The SQL Profiler – Stop dialog window prompts you to select an option.
  • Press Stop.
  • On the Navigator, expand the PL/SQL Code Profiling node.
  • Right-click on the profile session and select Run Summary. The Run Summary window opens.
  • Select a session and select Run Detail from the right-click menu. The Run Detail screen appears allowing you to view the metrics for this execution in both a graphical and text format.
  • To drill down further into the data, highlight a unit and select Unit Detail from the right-click menu. Scroll through the Source window to view the times for each statement.
  • To compare 2 cases, select the 2 cases you wish to compare (shift-click to select the second case) from the Run Summary screen and select Compare from the right-click menu. The SQL Profiler Run Comparison screen appears.
See the relevant online Help topics for more information on profiling.