SQL Assistant Java Edition is an information discovery tool that retrieves data from Teradata Database systems and allows the data to be manipulated and stored on the desktop. It is built on top of the Eclipse Rich Client Platform (RCP). This allows the product to take advantage of the RCP framework for building and deploying native GUI applications to a variety of desktop operating systems. SQL Assistant Java Edition can be run on Windows, Linux, and Mac OSX operating systemts. The Eclipse platform is designed as an open tools platform but architected so that its components (or a subset of its components) can be used to build RCP applications. This allows SQL Assistant Java Edition to benefit from the many high quality Eclipse features available while focusing on value-add for Teradata.

SQL Assistant Java Edition includes the following key features:

  • Modular display that allows you to move panels around, creating a custom look and feel.
  • Data Source Explorer to view database objects, such as schemas , tables, macros, stored procedures, user-defined functions, user-defined types, views, and triggers. (NOTE: SQL Assistant Java Edition uses the term schema to refer to a database.)
  • Data Source Explorer menu options to run Stored Procedures, Macros, and User-defined Functions.
  • Connection Profile Wizard to create connection profiles using the Teradata JDBC Driver.
  • SQL Editor to create, modify, and execute individual or multiple SQL Statements, including parsing the SQL statement for syntax errors and formatting the SQL according to selected formatting options.
  • Result Set Viewer that creates a historical record of the submitted SQL queries with time stamps, status information, and result set data.
  • Ability to export result set data as plain text, XML, HTML, CSV, as well as Excel format.
  • SQL Query Builder to visually build and edit SQL queries.
  • Data wizard to extract data from the Teradata Database to a file on the desktop.
  • Data wizard to load data from the desktop file system to the Teradata Database.

Modular Display

The SQL Assistant Java Edition display includes the SQL Assistant Toolbar along with five view panels: the Data Source Explorer View, the Project Explorer View, the SQL Editor (SQL Scratchpad), the SQL Results View, and the Teradata View.

SQL Assistant Java Edition contains a toolbar that provides buttons to allow quick access to functions that are used while creating and modifying SQL files.

A view can also contain its own toolbar and drop down menu. These toolbar buttons and menu items provide actions on the data managed by the view. Included in the view toolbar are buttons to minimize, maximize, and restore the view. Maximizing the view will cause the view to take over the entire SQL Assistant Java Edition window display. Click the Restore button to return the view back to its previous display.

You can customize the look and feel of SQL Assistant Java Edition by moving view panels within the main display window. A view can be moved by selecting the view and dragging it to a new location. As you move the view over the display, the mouse pointer will change depending on where you want to drop or dock the view. You can also drag the view outside the display area. The view will then become a "detached" view. You can also remove a view panel by clicking the "X" on the view tab. A view can be re-displayed by selecting the view from the Windows>Show View option in the main menu.

Data Source Explorer View

The Data Source Explorer View panel contains the database object tree display. It is here that you can create connection profiles to your Teradata Database systems and interact with the database objects.

Data Source Explorer

Creating Connections

You can create connections to your database using the Connection Profile Wizard. The wizard is invoked from the Data Source Explorer by right clicking on the Database Connections folder.

The Connection Profile Wizard is based on a driver template that references an embedded Teradata JDBC driver. Enter the database server name, user name, password, and default database name into the wizard dialog screen. Optionally, you can choose a different Teradata JDBC driver than the embedded Teradata JDBC driver, as well as add properties for the database connection.

Creating a SQL Statement (SQL Scratchpad)

When SQL Assistant Java Edition is launched, a SQL Scratchpad area is provided for you to enter your SQL statements. Once you have a connection to your Teradata database, you can begin to enter SQL statements in the SQL Scratchpad window.  Click the Save button to save the SQL Scratchpad to a file.

Another option to create a SQL file is through the Data Source Explorer. Within the Data Source Explorer, select your database connection and click the SQL Scrapbook action from the Data Source Exporer toolbar. This will create a SQL file in the SQL Editor with the name 'SQL Scrapbook [n]' and the connection profile set to your database connection. You are now ready to enter your SQL statement.  Refer to the section below entitled 'SQL Editor' for information on executing your SQL statement. Click the Save icon to save your SQL file within your SQL Project.

Edit, Extract, and Load Data

The Data Source Explorer also provides an option to edit, extract, and load data into a given table. Right click on the table and select the option from the drop down context menu.

The Edit option will present the current rows of data from the table in a table grid display within the SQL Editor. You can click on a cell in the table grid to modify the cell contents or add a new row by clicking on the <new row> entry. Depending on the edit option, a SQL UPDATE, INSERT, or DELETE statement is created and run. The results are displayed in the SQL Results View.

The Load option will present a dialog to choose the file to load and define the necessary file format needed to identify columns when loading data into the table. File format options include the column delimiter (comma, semicolon, space, tab, or vertical bar “|”) along with the character string delimiter (double quote, single quote, or none). It will create an INSERT statement for each row of data in the file.

The Extract data option will present a similar dialog for you to enter information about the file created from the extracted data, such as the output file and output file format (column delimiter and character string delimiter options).

The Sample Contents option will perform a “SELECT * FROM” SQL operation on the table and display the results in the SQL Results view.

Project Explorer View

The Project Explorer View panel provides a hierarchical view of the Projects and their resource files. It is here that you can select SQL files for editing or create new SQL files. SQL files can also be imported from or linked to your file system. When Teradata SQL Assistant is first installed, an initial project called 'SQL' is created for you. You can create additional projects using the New>Project... menu option.

Within the Project Explorer, you can also choose an option to compare two files. The Compare Option displays the two files side by side with buttons that allow you to jump from one difference to another or move changes from one file to another. This is very handy when comparing SQL files or result set data.

SQL Editor

The SQL Editor view is displayed when a SQL file is opened. Double clicking on a file in the Project Explorer will cause the file to be opened in the SQL Editor.

The SQL Editor provides a scratch pad for you to enter your SQL statements. As you type, SQL content assist will pop up annotations showing a list of expected keywords. If a database (or schema) name is entered followed by a dot, the content assist will display the list of tables from your schema for you to select from. (Note: You must be connected to a Teradata Database in order for SQL content assist to work.) The SQL Editor also provides syntax coloring for your SQL statements. You can change the colors of comments, keywords, identifiers, types, or other syntax items via the SQL Editor Preferences page.

When right clicking in the SQL Editor window, a SQL Editor Context menu appears with options to execute the SQL statement(s) in your SQL file.

 The following options are presented:

  • Execute all of the SQL statements
  • Execute the selected SQL statement
  • Execute the SQL statements as individual statements (with a commit occurring between statements)
  • Execute the selected statement as one statement
  • Execute the current statement where the cursor is located
  • Visual Query Builder

SQL Assistant Java Edition provides a graphical interface for creating SQL statements, referred to as the SQL Query Builder. You can edit an existing statement using the SQL Query Builder by selecting the statement within the SQL Editor and choosing the ‘Edit in SQL Query Builder…’ menu option. (Refer to figure above).

You can add additional tables and table joins by right clicking in the SQL diagram area and select Add Table… or Create Join…. When adding a table, a list of tables and views is presented for you to choose from. Additionally, from the SQL text window, you can choose an option to run the SQL statement. A SQL Results tab is provided for you to see the result set data.

SQL Results View

The SQL Results View displays the result data from executing SQL statements and running database routines, such as stored procedures, macros, user-defined functions, and triggers.

You can select a result entry from the history list and re-execute it or re-open it in the SQL Editor. This allows users to further fine tune their SQL statements.

The SQL Results View also provides an option to query the result history for particular SQL operations. You can enter your query in the space 'Type query expression here'. This will use the Apache Lucene query engine to search the SQL results history.

Exporting Result Data

SQL Results can also be saved to a file within your Project or exported to an external file in the file system. This can be invoked by right clicking in the result data area and choosing the Save or Export option.

If the Export option is chosen, the Export Result dialog is displayed. Select a file name, format, and export options. Then click the finish button to create the exported file. The Save option presents a similar dialog.

 

The following screen capture shows the result data with totals on integer columns as exported to an Excel file.

 

Teradata View

The Teradata View contains more detailed information about the database objects. When an object is selected in the Data Source Explorer, it is automatically displayed in the Teradata View. You can click on the tabs to display additional information on the database object.

Help

Help is provided by clicking on the Help>Help Contents in the main toolbar.

Conclusion

While SQL Assistant Java Edition is not necessarily intended to replace the current Teradata SQL Assistant product it does allows us to offer an alternative for customers whose client platform includes systems other than Windows. Supporting multiple platforms (Windows 32/64, Linux 32/64 and Mac OS X) in this way allows customers to be flexible when choosing a client operating system to connect to their Teradata Database system.

To get up and running with Teradata SQL Assistant Java Edition use the Download links above or go to the Tools Download area, pick your OS and take a look at the README.