WebFOCUS Newsletter April 2005 - SQL Support In WebFOCUS 7.1: The Icing on the Cake

Information Builders has incorporated user-friendly SQL support in the form of the SQL Editor and the SQL Wizard into Release 7.1 of WebFOCUS Developer Studio.

The SQL Editor is a simple text editor that provides color-coding for SQL commands. The menu has some dropdown boxes to make connections to the RDBMS and a checkbox that specifies whether to create a SQLOUT file.

The SQL editor lets you code any SQL instructions necessary, including table creation and insertion of records. These instructions are passed to the Relational Database Management System (RDBMS) as is. You can create a SQLOUT file that may be used just like any hold file by following the steps of the procedure as illustrated in Screen 1.

Screen 1

The SQL Report Wizard provides step-by-step assistance for creating a report or a graph using the dataset resulting from a SQL request. On the first screen you are offered the following options:

Execute a stored procedure: This option is used to execute a stored procedure in a RDBMS database. A stored procedure, which cannot be modified, is stored in a database by the administrator to be used by the user community.
Include an external file containing SQL statements (file must have ".sql" extension): This option will INCLUDE a procedure that has a .sql extension. Use this option with code that is common to many procedures. The maintenance of the code can be easily done if it exists only in one file.
Type your own SQL statements: This option is for typing ad hoc SQL code into an editor window.
Import an external file containing SQL statements and edit the statements (file must have a .sql extension): This option will import the content of a SQL file into the editor window to allow you to make changes (Screen 2).

Screen 2

Then, you are prompted for connection and database access information. The next screens depend on the option you have chosen.

If you choose to execute a stored procedure, just type it in, and remember that SQL is case sensitive. For the "import" and "include" options, you can browse for a .sql file.

When you type your own statement, or if you import a file, you then see the editor screen that allows you to make modifications. And always, you have the option of testing the SQL on a limited number of records.

The final screen in the SQL wizard is a summary of what you have chosen to do. Our new SQL support is available in all the different environments – local project, data servers, and MRE. With these features, users can leverage a number of SQL programs and functions without leaving the WebFOCUS Developer Studio interface.