Lab Reports: SQL-Programmer IX

SQL-Programmer is a consistent interface for cross-platform development

Sylvain Faust's SQL-Programmer, a third-party, general-purpose SQL Server utility development tool, provides cross-platform capability for any combination of SQL Server, Sybase Adaptive Server, and Oracle. Sylvain Faust's latest release, SQL-Programmer IX, and its use with SQL Server 7.0 are the focus of this review.

SQL-Programmer consists of five basic modules: SQL-Explorer for browsing databases, Access Manager for quick access to database objects, SQL-Documenter Report Interface for documenting objects, SQL Scripter for scripting objects through a script interface, and Free SQL! for querying. You can invoke all modules from SQL-Programmer's button bar.

SQL-Explorer lets you explore a tree view of databases and their objects, similar to the view in SQL Server's Enterprise Manager. Unlike Enterprise Manager, SQL-Explorer's tree view is developer-oriented and it doesn't include as many administrative nodes (such as SQL Agent); it adds nodes only for Transact SQL (T-SQL) developers. You can find nodes for triggers and indexes more easily in SQL-Explorer than in Enterprise Manager. SQL-Explorer's Show Code mode lets you view the T-SQL code for each object in the right-hand pane of the Explorer interface, with color-syntax highlighting. You don't have to open an object or view its properties to see the code. The Show Code feature also extends to logins; you can select a login and see the login's script immediately. Another useful feature is the ability to drag and drop objects, including tables, from one database to another. (As with Enterprise Manager, SQL-Programmer can occasionally go into a Not responding mode.)

Access Manager is an alternative way to select database objects more quickly than through SQL- Explorer. The Access Manager interface lets you choose an object by selecting the database and the object type. You can click a button on the lower button bar to perform any of the same features you can invoke somewhat more laboriously in SQL-Explorer. You can also use standard Windows keystrokes to copy and paste objects from one database to another. And, you can click the Edit button to view the contents of a table. The Access Manager is an example of less is more—a simple design that is more efficient than the flashier drill-down Explorer design.

The SQL-Documenter utility is a valuable feature for creating reports about your database. This kind of utility doesn't come with the default SQL Server toolset. You can use SQL-Documenter to document programmable objects in a database. The report format is appropriate for printing, binding, formatting information, and keeping source code in a readable fashion.

SQL Scripter lets you extract object scripts for backup. SQL Scripter doesn't have as many options as Enterprise Manager's Generate SQL Scripts dialog box; however, it can produce table scripts without square brackets to make it easier to spot T-SQL keywords used as column names. Also, SQL Scripter doesn't script ordinary column defaults with constraint names as Enterprise Manager's scripting utility does (an annoying aspect of Enterprise Manager).

Free SQL! is a query utility that contains some of the SQL Server Query Analyzer's features. Free SQL! is a quick way to send code to the server and edit batch objects.

Search Utility

SQL-Programmer and SQL-Explorer can access a search utility, which you can invoke from each application's Edit and Find menu, as Screen 1, page 52, shows. You can search for text strings across all objects in a database. The return dialog box lets you select and edit an object. However, this search utility doesn't include a companion replace function.

Source Control and Batch Objects

SQL-Programmer comes with two additional Access Manager features. One is a proprietary source-code control system, which keeps its data in the SQL_Programmer_In_Out table. The source-code control system lets you check objects in and out and record their histories in the database. In addition, SQL-Programmer can indirectly interface with other source-code control systems, including Visual SourceSafe.

The other Access Manager feature lets you create and save T-SQL code as batch objects in your database. The Sylvain Faust batch object can contain more than one batch of T-SQL code. (A T-SQL batch can contain many T-SQL statements, and a Sylvain Faust batch object can store many T-SQL batches.)

Stored-Procedure Editor

You can edit a stored procedure from either SQL-Explorer or Access Manager. To edit a stored procedure in SQL-Explorer, right-click the stored procedure and choose Edit. Screen 2 shows the result. In the Virtual SQL Editor, you can enable the debugger toolbar. The stored-procedure editor has many features besides editing and debugging. In addition to editing a stored procedure, you can set up a test environment by pre-appending code in the Prologue tab and appending code to the Epilogue tab. The editor also has tabs for arguments, results, privileges, and dependencies. It has an activity log, which records activity about the object and stores it in the SQL_Programmer_History table of the database. Then, if you save your work as a SQL-Programmer Development Window (SPDW) file, you can save and restore work in progress, along with the arguments, prologue, and epilogue for a given stored procedure. The Virtual SQL Editor also lets you save specified test cases on disk.

You can also retrieve statistics and showplan data from a Virtual SQL Editor's dialog box. But the Virtual SQL Editor's graphical display doesn't show all the information you can get from the SQL Server Query Analyzer's estimated execution plan.

Stored-Procedure Debugger

The SQL-Programmer debugger, which is in the toolbar above the stored-procedure editor, has the usual debugger features (Step Into, Over, and Breakpoints) and lets you open multiple debugging sessions at once. The current version doesn't work with stored procedures that contain dynamic SQL and it occasionally produces Dr. Watson exception error messages, which I hope Sylvain Faust will fix in a future service pack.

Installation and Configuration

Because SQL-Programmer is a cross-platform tool, when you install it, you're asked to enter a key that defines which back-end products to connect to. With the demo CD-ROM, you can get this key from the Sylvain Faust Web site.

You'll need several files to install the Sylvain Faust debugger, including sqldbg.dll and sqldbreg.exe on the client and msdi98.dll on the server, which come with the SQL Server and Visual Studio CD-ROM. If you've already installed the Visual Studio stored-procedure debugging utility, these files are in place. However, you still need to configure the Distributed Component Object Model (DCOM) on your server. Assistance on installs is available at the Sylvain Faust Support Page for Frequently Asked Questions (http://www.sfi-software.com/support.htm).

SQL-Programmer for SQL Server resides somewhere between the Enterprise Manager and Visual Studio; it combines some of these applications' features and adds some of its own. To SQL-Programmer's credit, it has more developer features than Enterprise Manager and addresses more database objects than Visual Studio, including object security. SQL-Programmer also has many useful features, such as a documenter, a search utility, and object copy and paste.

Although SQL-Programmer has many innovations that the SQL Server and Visual Studio database tools lack, its look is different from Microsoft's GUI (for example, it has older-looking icons) and for this reason you might consider it to have a less modern interface. Also, SQL-Programmer's use of multiple-document interface (MDI) windows isn't like Microsoft's GUI and takes some getting used to.

If you aren't satisfied with the amount of effort you invest when you use Microsoft tools to develop stored procedures and other T-SQL objects, or if you don't like switching among Enterprise Manager, Query Analyzer, and Visual Studio, consider SQL-Programmer. You won't need to remember all the database management system (DBMS) vendors' tool features if you use SQL-Programmer's consistent interface for cross-platform development.