Step 6: Defining a Simple HTML Batch Job That Extracts Data from One Table

Previous Next

See Also

Let's create an HTML report that lists all scheduled tours. You need to define a batch job that extracts the data from the database and formats it in the report style you require.

The following illustration shows how USoft Batch handles export tasks.

USoft Batch creates temporary tables called external sets when exporting data. The structure of these external sets depends on SQL statements defined to extract the data from the database.

A second processing step embeds the data in HTML files in the required format.

To define an external set that extracts data from one table:

1. Double-click the Definer item in the Binder.
2. Choose Define, Sets, External Sets from the menu, and then type "STEP6_ESET" in the Set Name field.
3. Click the SQL Definer button next to the SQL Statement field.

The SQL Definer (a specialized graphical editor for SQL statements) appears.

4. On the Define Type And Name Of Statement To Build dialog, click OK to accept the default statement type (SELECT statement).
5. On the Repository tab of the catalog, expand the Scheduled Tours node.

Nodes appear for each of the columns of table SCHEDTOUR as well as for all relationships this table is involved in.

6. Drag-and-drop the Destination, Type, Start Date, Return Date, and Capacity columns from the catalog tab onto the SQL tab page displayed at the bottom of the SQL Definer window.

The statement on the SQL tab should now look like the code example below.


, "Scheduled Tour".TOUR_TYPE

, "Scheduled Tour".START_DATE

, "Scheduled Tour".RETURN_DATE

, "Scheduled Tour".MAX_PART

FROM SCHEDTOUR "Scheduled Tour"

7. Click the Results tab page to get a quick overview of the data the SQL statement retrieves from the SCHEDTOUR table.
8. Choose Statement, Save to have the statement copied to the calling USoft Batch window.
9. Close the SQL Definer window, then save your work by clicking the Save button on ribbon bar, or by choosing File, Save All from the menu bar.
10. In the External Sets window, accept the File Format and Record Separator defaults, check the HTML Set box, then click the HTML Set button.

The HTML Set window appears.

11. Choose Layout Style Form from the dropdown list, and then save your work.
12. Make the External Sets definition window the active window.

A Special menu option appears.

13. Choose Special, Compute Elements from the menu bar.

USoft Batch now deduces the structure of the external set on the basis of the columns that are part of the SQL statement.

14. Save your work.

To define an export task:

1. Choose Define, Tasks, Export Tasks from the menu.
2. Type "STEP6_ETASK" in the Export Name field.
3. Select the STEP6_ESET external set in the lookup window that you open by clicking the External Set lookup button, and then click OK.
4. Type "ScheduledTourStep6.htm" in the Filename field, and then save your work.

To define the batch job:

1. Choose Define, Jobs from the menu.
2. Type "STEP6_JOB" in the Job Name field.
3. Type "STEP6_TASK" in the Task Name field.
4. Type "1" in the Sequence field.
5. Accept the Task, and Abort On Error default values, and type "STEP6_ETASK" in the Export field.

You have now defined a job with the export task as its single task.

6. Save your work.
7. Click the Validate button in the top right corner of the Jobs window.

Next Step

Step 7: Running the Job and Viewing the Result