Run Procedure

The Run Procedure action is a more powerful alternative to Set Value which can optionally return results to dbFront. Depending upon the database capabilities and configuration, you could: perform a specialized calculation, send an email, create a report document or even start a backup.

Procedure: The name of the database procedure,
Parameters: The list of fields to pass to the database procedure.
Result: The expected result type and how it should be processed.

Creating the Action Button

The following steps will create a Run Procedure action button in dbFront:

Open the table from which you will want to call the procedure,
Open the Table Preferences and create a new button in the "Action Buttons" tab and Save,
Click on the gear icon beside the new Action Button and complete the button setup including the Caption and Visibility,
Change the "Action Type" to "Run Procedure", (this will immediately add a new "Procedure" tab to the dialog)
Switch to the "Procedure" tab,
Enter the procedure name,
Select the fields that should be assigned to the procedure,
Set the result type to 'Automatic',
Save the new button.

Once saved, the button may appear disabled if no record is selected and if one or more table fields were selected as parameters.

Procedure Results

Stored procedures can optionally have a final Select statement return results.

In the Procedure tab of the Action Button preferences you can specify the Output Format:

(Discard Results): This will cause the procedure results to be discarded.
Automatic: This will cause dbFront to check the returned value and select HTML as the output format if a single column and row was returned, or TABLE if multiple rows and columns were returned.
Report / Export: Pass the returned records to the Quick Report / Export engine,
Server Directed (UPDATED): A list of actions as prepared by the stored procedure,
Simple Message: Return the value from the first column and row as a simple message.
Simple Html: Return the value from the first column and row as HTML that will be shown in a dialog.
Simple Table: Build and display an HTML Table using the result set.

Report / Export

The Report / Export output format causes the Stored Procedure output to be sent directly to the Quick Report / Export engine. This allows for the creation of Excel or CSV exports and template-based Quick Reports. Quick Reports can be viewed as Html, downloaded in PDF format, or sent directly to a Server attached printer.

The formatting of Quick Reports is determined by the selected template. For more details see: Creating Report Templates

Server Directed

The Server Directed output format is a way for stored procedures to dynamically send instructions to dbFront. The action list is returned as a multicolumn select with the first column named "action".

The currently supported actions are: OpenDialog, OpenTable, OpenUrl, and WebRequest.

OpenDialog (NEW): Open the specified message in a Dialog. The message is shown at the top of the dialogue. Any extra result sets are appended below and formatted as tables.
The column "ReloadDelay" instructs dbFront to refresh the dialogue results from a new call to the procedure after X seconds. The Handle is returned in the optional stored procedure parameter {%dbfsp_header%}.
Each run of the procedure has the option to change the output and request something different.
The procedure should return:
Action Message (Optional) ReloadDelay Handle

OpenDialog Message text or Html 2 UniqueValue
OpenTable: Open the table specified in the column "TableName". The column "Target" specifies where the table will be opened. Optional Target values are: [Redirect, Dialog or NewTab].
The procedure should return:

Action Table Target Message (Optional)

OpenTable Customers NewTab User Message
OpenUrl: Open the Url specified in the column "Url". The column "Target" specifies where the table will be opened. Optional Target values are: [Redirect, Dialog or NewTab].
The procedure should return:

Action Url Target Message (Optional)

OpenUrl Http://dbFront.com NewTab User Message

Action	Message (Optional)	ReloadDelay	Handle
OpenDialog	Message text or Html	2	UniqueValue

Action	Table	Target	Message (Optional)
OpenTable	Customers	NewTab	User Message

Action	Url	Target	Message (Optional)
OpenUrl	Http://dbFront.com	NewTab	User Message

WebRequest (BETA): Send a WebRequest call to the specified Url, with the specified Header and Body and return the results to the Stored Procedure.
The column "Handle" lets you specify a unique string which is returned to the procedure so that you can distinguish between different instances. The Handle is returned in the optional stored procedure parameter {%dbfsp_header%}, and the data is returned in the optional parameter {%dbfsp_data%}.
The procedure should return:

Action	Url	Header (Json)	Body (Json, Form-Data)	Method	Handle
WebRequest	https://github.com/login/	{ "Key1":"Val1", Key2":"Val2" }	Param1=Val1& Param2=Val2	Get or Post. Defaults to Get if no Body is provided.	UniqueValue

The following select statement at the bottom of a SQL Server stored procedure will cause dbFront to open the [Sales].[Product] table and record based on the specified primary key and then display a user message. For more details on formatting the URL see: Deep Linking

   SELECT
       'OpenURL' as Action,
       concat('default.aspx?k={%DatabaseKey%}&t=[Sales].[Product]#[',@ProductId,']') as Url,
       'Redirect' as Target,
       'Finished calculating Product Details' as Message;

NOTE: dbFront recognizes the value {%DatabaseKey%} and will replace that with the current Database Key.

Trouble Shooting

Can't load '%procedurename%' parameters. The stored procedure '%procedurename%' doesn't exist.
The three most likely sources for this error are:
1. Incorrect procedure name,
2. Missing schema name for procedures saved in non-default schemas,
3. Missing security. Run [Grant Execute]

Next Steps

For more help or details on creating Action Button see: Action Buttons