Altova DatabaseSpy 2007

It professionals and consultants often must work in heterogeneous database environments. The available database client tools range from command-line tools to GUIs. Understanding the nuances of all these client applications can be expensive and time consuming. Altova, maker of the popular XMLSpy XML editing tool, has released a new product called DatabaseSpy to address these problems. DatabaseSpy is a multi- database management and design tool that provides a common interface for professionals who routinely work with different database environments.

I installed DatabaseSpy on a Windows XP machine. (It's supported on Windows 2000, XP, and Windows NT). The installation was straightforward, with only one configuration option—the option to make DatabaseSpy my default SQL editor, which would increase efficiency if I want to use DatabaseSpy as my primary database client tool. After installation, I opened the shortcut on my desktop and was presented with the Quick Connect dialog box that displayed a number of built-in database connection options. I chose Microsoft SQL Server and proceeded through the connection wizard. After I selected my local database server and a database, the Project View window opened, displaying the connection I had just configured. I then clicked the Online Browser tab to view the database schemas and the objects within each schema.

Running queries from any table was easy using DatabaseSpy. The data is returned in a common Results window. I also was able to export table data into a number of different formats, including XML, comma-separated value (CSV), Microsoft Excel, and HTML. Unfortunately, DatabaseSpy offers only one format option for importing—CSV. I would have liked to see more options, such as importing from other databases or importing other formatted data, such as tab-delimited files. DatabaseSpy also lets you open a table directly in the SQL Editor window. From there, you can choose to generate SELECT, CREATE, INSERT, UPDATE, or DELETE statements. All SQL statements are auto-generated and appear in the SQL Editor window.

While working in the SQL Editor, I tested the auto-complete feature and was impressed. As I typed the SELECT statement into the SQL Editor, the auto-complete list appeared showing potential tables, functions, or procedures that I could query. The only drawback I found is that the auto-complete list can take some time to load, especially if the database contains a lot of objects such as tables and procedures.

I think the Design Editor, which Figure 1 shows, is the feature that provides the most benefit to IT professionals. I was able to add existing tables to the design view, create new tables, and add and edit columns with one click of a button. I could view relationships and save the design for additional modification. Each time I made a change to a table or added a new table, the SQL Editor window appeared at the bottom, generating SQL statements to execute when I was ready to commit these changes to the database.

I did run into a few problems while working in the Design Editor. I added a table and tried to change the table name. The table name wouldn't always change in the design view. I found the same behavior was true when trying to change column names in the Design Editor. A rename option doesn't exist in the Design Editor's file menu. As a workaround, I opened the SQL Editor window, changed the table and column names, and executed the script.

One feature I missed was the ability to create a column of data type XML when connected to a SQL Server 2005 database. One of the highlighted features of DatabaseSpy is its increased interoperability with XML. Adding native support for the XML data type seems like a logical addition.

Altova also could make this tool more robust by adding UI support for stored procedures and user-defined functions (UDFs) or user-defined data types (UDTs). Several other database client tools support not only tables and views, but also stored procedures, UDTs, and UDFs.

One crucial feature that's missing for DBAs is the ability to back up and restore an entire database, along with scheduling of these operations. If DatabaseSpy seeks to be a replacement for existing client tools, it must have this capability.

DatabaseSpy is a good option for IT professionals who need to perform database updates or schema changes, and the common interface makes it easy to access the different data sources. However, without better UI support for stored procedures, UDTs, and UDFs, assignment of data types, and database backup and restore scheduling, this product isn't robust enough for the DBA.