|Index Search Add FAQ Ask Question|
Index > Other 3rd Party Tools > Software Tools > SQL-Programmer 2001
SQL-Programmer 2001 by BMC Software
Author: Rene Butcher and Suren Yegiyants
SQL-Programmer is a very robust development environment for writing and maintaining stored procedures in various multi-user database platforms including: Oracle, Sybase, and Microsoft's SQL Server. It enables users to revise, maintain and test server-side SQL code. The product is useful to both developers and DBAs. This review considers application of the tool to Oracle's PL/SQL. While there are many tools available for developing PL/SQL applications, SQL-Programmer is particularly useful because it provides a more complete development environment than many competitors. It gives the developer the ability to establish multiple connections to Oracle instances simultaneously. Once the connections are established, it is easy to compare stored programs, copy and paste code fragments from one environment to another. The tool can significantly enhance the productivity of 95% of the work carried out by an Oracle developer.
HistorySQL-Programmer has been around for several years. It was developed by Sylvain Faust Inc., based in Canada, who were acquired by BMC Software based in Texas in October 2000.
This review is based on the latest 2001 release which shipped in October 2000.
Supported Operating Systems and VersionsSQL-Programmer runs on all 32-bit Windows platforms, including Windows 95, 98, 2000, NT and ME. You need Oracle 32-bit SQL*Net and Oracle version 7.x or later. It is also compatible with MICROSOFT SQL Server: 4.2 to 7.0, and SQL Server 2000, SYBASE SQL Server: 4.8 to System 11, and SYBASE EnterpriseConnect Gateways: all versions.
InstallationThe Main Menu for the Installer includes company contact information, the company web site, and a very informative "Quick Tour" of the product. Installation of SQL-Programmer was simple and straight forward. After agreeing to the licensing agreement and viewing the README file, a local or network install option is presented. I chose the local install and was able to choose my drive and directory path for installation. The installation proceeded quickly, overlaying the previous version of SQL-Programmer already installed on my system and only complaining about overwriting the 11 files marked read-only from the previous version. An upgrade to the Help system is included as part of the install, and on one of the test systems, this required a reboot. All 3 systems tested were NT 4.0 with Oracle 8.1.6 client
What it doesThe developer or DBA can use this tool to accomplish the following:
Problems SolvedDramatically speeds up software development cycle, makes it easy to correct compile errors, generate scripts and documentation on various stored objects. Also handy for troubleshooting difficult problems using the powerful debugger.
Who should use it?All Oracle developers and most Oracle DBAs
Competitive productsTOAD from Quest software, which used to be a freeware product but now costs $670 plus an extra $520 for the debugger. It has some features similar to SQL-Programmer, see the review of TOAD on the Oracle Underground FAQ. There is also SQL Navigator by Quest Software.
Detailed reviewSQL-Programmer provides a complete end to end development environment, and is great value for money for the functionality you get. It supports multiple connections to Oracle, Sybase, and/or Microsoft databases.
SQL-Programmer 2001 comes with SQL-Explorer, Access Manager, Object Editor, interfaces for version control with SourceSafe, MKS, StarTeam, and PVCS, and interfaces to RevealNet Knowledge Base and Instant Messaging. It also includes PL/SQL reformatting and code metrics for Oracle databases and a Transact-SQL profiler for the SQL Server databases. Optional modules include separate debuggers for PL/SQL and Transact-SQL, SQL-Scripter and SQL-Documenter.
From the SQL-Explorer window, a SQL-Programmer Development Window (SPDW) can be opened for each database object. The SPDW is context sensitive, with the tool bar operations changing with object type. Information available about stored procedures, for example, includes dependencies, auditing, privileges, synonyms, and a list of arguments. Reporting on a stored procedure provides this information along with environmental information such as SID, Oracle and OS versions, owner, create and revision dates. These reports are production quality and would add real value to those development shops keeping project notebooks as part of their software engineering methodology.
The SQL-Explorer provides an Object Editor for creating and changing objects that can assist DBA and developer alike. It provides analyze and update statistics, drop and truncate table, and copy and paste an object between schemas.
The Edit window color codes reserved words, literals, and comments, permitting easier reading of PL/SQL code. It was a little confused when I edited a PSP script; it highlighted some of the HTML as reserved works, even though it was enclosed as a literal.
At one point I also had a problem with editing a trigger. The trigger would compile when unchanged after retrieval from the database, but would fail to compile when any change, even cosmetic, was made. Since my NT box had been up for several weeks, I rebooted and that solved the problem. I did report the problem to BMC support. BMC recently acquired SFI. Since I work at several sites, I prefer to get support via email rather than telephone. The support hours for this product are 8am to 5pm Central Time Monday through Friday. These are the same hours I'm usually in meetings and unavailable for working support problems. Despite my requests to use email, I was called 5 times. For most developers these hours should not be a problem and they will appreciate the persistence of the BMC support staff.
The PL/SQL Debugger is an extremely valuable tool, allowing the examination and setting of variables, setting of break points, and module call stack display. This option is a must have for Oracle shops.
The PL/SQL Reformatter is configurable and quickly reorganized stored procedures to my preferences. The code metrics tab provides breakdowns of module, SQL content, built-ins utilization, procedural content, and PL/SQL version dependencies. Other tabs reveal user and role privileges, auditing options, dependencies, and session activity logging.
The SQL-Programmer comes with FreeSql, which is used to write queries and execute them. The FreeSql tool seems to be quite limited, particularly the display of table contents has proven to be a disappointment. Not only must the width of columns be manually resized, but the software does not support selecting of Large Objects.
The software comes with a very powerful database search feature, allowing to search for patterns within stored object for a given schema. This feature can be quite handy for troubleshooting or just browsing through someone else's code. (e.g. Oracle ERP applications)
Batch objects are introduced with this product and can be stored in Oracle. This feature enables developer's to save their SQLplus like scripts in the database rather than in operating system files. Before using this feature though, you need to enable batch objects in a particular schema. Once you click on the "Enable Batch Objects" button in the Access Manager, it guides you on how to proceed. Once enabled, the batch objects can be loaded into a FreeSql Window and executed. The programmer has the ability to modify the scripts and execute them without saving the changes in the database. One word of caution, you can only use SQL commands in the batch objects, it will not recognize SQLplus commands, such as (spool, column..). The output from a FreeSql window can be saved into a CSV file and loaded into Excel or be used as a datafile for SQL Loader.
The Query Builder, introduced with the 2001 version is a welcome addition. It complements the FreeSql product, providing with a graphical interface to building SQL queries. However, it does not integrate at all with the FreeSql and is quite limited with this version. Hopefully it will improve in future releases.
The Personal Command Post is also a new addition, it comes with a mini explorer which is quite similar to the SQL Explorer. In addition, it provides additional tabs that may improve programmer productivity. Now one can store favorite code fragments, favorite objects and a to do list. With this version the data stored in the tabs mentioned above is stored externally in dynamically generated XML files.
ShortcomingsThe display of table contents has proven to be a disappointment. Not only must the width of columns be manually resized, but the software does not support selecting of Large Objects (e.g. clobs)
Cost and where to buyDownload SQL-Programmer for free here
Purchase directly from BMC's on-line store here
Can you write reviews and articles like this one? Why not become an editor or reviewer for the Oracle FAQ? Find out how you can make money, get noticed and advance your career click here