SoftTree Technologies SQL Assistant (SA) 2.5
promises to enhance the productivity and effectiveness
of database developers working with Microsoft
SQL Server 2005 and 2000; MySQL 5; IBM
DB2; UDB 9, 8, and 7, or Oracle 10g, 9i, and 8i. SA
runs in the background, and you access its productivity
features via hotkeys or a right-click menu from
within your chosen development environment. Soft-Tree might have an update to SA available shortly
after you read this, offering support for advanced
dynamically generated code snippets; temporary
table references and table variables; multiple code
formatting styles; and more preconfigured SQL
editors and IDEs.
SA connects to the target database system to
gain access to the object data - for example, table
and view entries - that it needs. When possible, it
will use an existing connection, sharing SQL Query
Analyzer's ODBC connection or SQL Server Management
Studio's SMO connection for access to
catalog tables and views. For access to user-defined
objects, SA will open - and remember - a second
connection by using credentials that you provide.
To get a quick start in some familiar environments,
I installed SA to a system with a full installation
of SQL Server 2000 and another with SQL
Server 2005. SA installed quickly, placing itself in
the Startup group and running with a system tray
icon. SA is preconfigured for use with 12 editing
programs, including Notepad, Query Analyzer,
SQL Server Enterprise Manager, and SQL Server
Management Studio.
SA's functions are accessible in two ways from
within supported text editors: direct access to specific
functions through the use of hot keys, and via integration
with the editing program's right-click menus.
To get a sense for SA's native capabilities, I chose
to start with Notepad. I started on the SQL Server
2000 system by configuring Notepad for SQL Server
2000 - a simple process of selection from three dropdown
boxes on the Targets tab in SA's Options menu.
Opening a saved query in Notepad, I worked through
SA's functions. A right-click in Notepad presented
SQL Assistant as an option that displayed a submenu.Figure 1, page 46, shows an example from SQL Server
Management Studio. Selecting SQL Reference or
Code Structure opens a pane along the left side of the
window with Reference and Structure tabs.
The Reference section lets you browse through SQL
topics, showing a concise summary of statement
structure and syntax, and allowing you to insert
statement structures at the current cursor position
in your code. With the cursor on a particular
statement, Reference takes you directly to relevant
information. When several statement formats are
available - for example, Declare lets you declare a
variable or a cursor - SA displays the alternatives.
Clicking one of these alternatives inserts that statement
structure at the cursor position.
The Structure tab displays the overall structure of
the statements in your code. Plus (+) and minus (-)
characters let you expand and contract the display.
Clicking a structure element takes you to that point
in your code for quick navigation.
In my tests, context-sensitive code completion was
a real time saver. As I typed in a statement, potentially
relevant object names from the server I was connected
to popped up in list format. In my testing, this feature
worked relatively quickly, exhibiting only a second
or two delay as the code is being entered. I could
use the mouse or keyboard to scroll through the list,
then either left-click or press the Enter key to insert the selected item. For example, when completing
a SELECT statement, keying the space following
the SELECT keyword displayed a list that included
system tables, views, functions, and databases. Keying
the first letter of the
desired object name narrowed
the list. Using the
arrow keys or typing on
the keyboard took me to
the desired object, and
pressing Enter inserted
the name. When entering
a fully qualified field
name, keying the period
(.) character displayed
the names of the next
level of objects, allowing
the automatic statement completion to continue. At
the completion of a field, keying the comma (,) character
displayed the original object list again to let me
select another field name.
Another useful component is the syntax checker,
which you access via the right-click menu or (by
default) the Control + F9 hotkey combination. Error
messages appear in a pane at the bottom of the
window. I noticed that error detection is sometimes
progressive: Correcting one error will reveal others.
Other functions performed by SA include code formatting and rapid insertion of named code snippets.
A code reference to a stored procedure turns
into a hotlink when you place the cursor on the
name and press the Control key. Clicking the link
displays the underlying procedure definition.
SA can be a very useful tool, speeding up SQL
coding and improving your accuracy. That said, I
do see some room for improvement. For example,
when building a Select statement the ability to select
and insert multiple field names in a single operation
would be helpful. I would also like to be able
to create a complete skeleton statement in a single
operation in a way that would allow the contextsensitive
auto-completion tool to help complete the
various clauses of a complete statement. Nevertheless,
I believe that SA is well worth the price for those
who spend their days coding and for those who have
a regular need to construct SQL code - but perhaps
not enough to maintain top skill levels.
End of Article
