Customizable SQL Script Options
Original Publication Date: 2004-Jan-13
Included in the Prior Art Database: 2004-Jan-13
Abstract: Disclosed is a method for specifying SQL statement execution options using embedded XML for database scripts. These options may be used to specify how these SQL statements are to be handled by the database server or how the results may be displayed.
Customizable SQL Script Options
Many Integrated Development Environments (IDEs), such as Microsoft Visual Studio .NET and IBM WebSphere Studio, support development of SQL scripts as part of a database project. These scripts typically contain, among other things, data definition language (DDL) or data manipulation language (DML) SQL statements. These scripts can be used to create one or more SQL stored procedures, SQL user-defined functions, tables, views, indexes, triggers, types, and so on.
The IDEs that supports the development and execution of these scripts typically support script level options and settings that are shown as part of the properties or configurations settings for the script file itself or the project. These may include such things as data source name, user id and password used for executing the script.
There is a need to have a more granular set of options that would control the execution of one or more statements within the script.
Design and Implementation of Script Options
Any database script compiler can be extended to recognize a special XML tag in script comment lines. Supported options may be parsed out of these comment lines using an XML document and XPATH queries. These options can then be used and applied to any SQL statements that follows this option line.
The IBM DB2 Development Add-In for Visual Studio .NET, a component of the DB2 Application Development Client, supports such an XML tag in its DB2 database project script files. This special XML tag is <ScriptOptions> and is preceded by the standard DB2 comment delimiter, namely, a double dash. Using the standard DB2 comment delimiter allows other script execution engines that do not support these script options to safely ignore them.
Some of the supported script options include:
a. Ignoring errors option
When an error is encountered during a script file execution, the compilation step is halted. This may not be desirable in certain cases where the error should really be treated as a warning allowing for the script execution to continue. One example would be a drop specific procedure statement that precedes a create procedure statement. The drop is there to ensure that the script can be built multiple times and that the older version of the procedure is deleted. This drop will fail the first time the script is executed as the procedure has not been created yet.
To treat errors as warnings, set the value of the errors script options property to "off". To revert back to the default behavior of treating errors as failures, set the property value to "on".
-- <ScriptOptions errors="off"/> @
-- Drop the stored procedure if one already exists DROP SPECIFIC PROCEDURE MySchema.MySpecificSP @
-- <ScriptOptions errors="on"/>
b. Data grid output option
For any database operation performed during a script file execution that results in a data set, the data set is displayed in an output data grid as well as in the IBM DB2 Development Add-I...