Configure a Stored Procedure Task

A Workflow Stored Procedure Task can be used in a couple of different ways:

to run a SQL Script, using the at_wkf_executecmd (just wkf_executecmd from v6.0.0) supplied stored procedure. This is the way you can run simple SQL Statements on your iShare database such as trimming Ward Names etc.
to run any Stored Procedure with or without parameters. If you pick your iShare database then you will be able to select to run any of the Astun supplied functions which can be used to manipulate your data.

Running a SQL Script

In the following example we will show you how to run a SQL script, such as spatializing road accidents, using the at_wkf_executecmd (wkf_executecmd from v6.0.0) stored procedure.

When creating your Task select the Stored Procedure Type.

Name

Enter a unique name for your task, in this case Spatialize Road Accidents.

Connection Details

Select your iShare database, in this case DataShare, from the selection. Make sure you have the Workflow radio button selected (or from v6.0.0 the -Workflow- entry from the Function list). Then select at_wkf_executecmd (wkf_executecmd from v6.0.0) from the list of Functions.

From Version 5.6.2 you will also be able to select a Timeout value from the list provided. The default is 1 hour.

Now you need to enter your SQL statement into the Edit parameter 'cmdtoexecute' e.g.

Example SQL Statement

UPDATE transport.road_accidents SET wkb_geometry=st_setsrid(st_makepoint("location_easting_osgr"::int, "location_northing_osgr"::int),27700)

Running a Stored Procedure

Astun supply numerous stored procedures for data manipulation and these can be run using a Workflow Stored Procedure Task. When creating your Task select the Stored Procedure Type.

Name

Enter a unique name for your task. It is advisable to call this something relevant as this name will be used to populate list box selections when creating Jobs.

Connection Details

Select a database connection from the list. This list will be limited to Postgres database connections that are Read/Write.

Select the Function from the list of available options for the selected database.

Alter the Timeout if required - available from Version 5.6.2.

The three radio buttons may be used to control the list of stored procedures returned in the list. If you select Workflow then add stored procedures which are prefixed at_wk will be returned; if you select Astun then the selection is at_ and All will return all stored procedures.

From v6.0.0

The radio buttons have been replaced by a Function filter list from which to select the type of function you require. This restricts the functions returned to either Workflow (functions in the ishare schema that are prefixed wkf_) or iShare (functions in the ishare schema that are not prefixed wkf_), these options are indicated by a dash at each side. Then there is the option to restrict functions by schema (no dash in the dropdown) e.g.

.

Parameters

If your selected function requires specific parameters you may enter the information here. First select the parameter from the list and then you will be able to enter the value in the Edit parameter area.

Click Save to update Studio with your new Task. To run the Task either just click the Run button or right click on the Task in the tree and select Run Task Task Name where Task Name is the name of your Task.