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. With the latest version of dbFront, procedures can additionally ask dbFront to display a custom form, retrieve data from a WebRequest and much more.
- 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 an 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. Can also trigger the ProcedureAPI.
- Report / Export: Pass the returned records to the Quick Report / Export engine,
- Server Directed (ProcedureAPI): A custom action as specified 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 dialogue.
- Simple Table: Build and display one or more HTML Table(s) 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 (ProcedureAPI)
The Server Directed (ProcedureAPI) output format is a way for stored procedures to dynamically send instructions to dbFront. The action is returned as a multicolumn select with the first column named "action". Not only can a stored procedure make specific requests, but the data is optionally returned to the procedure for further processing. This means that the ProcedureAPI allows stored procedures to have a two-way conversation with dbFront. Some possibilities are:
- Continually refresh a stored procedure result until the processing is finished,
- Display an HTML-formatted message with custom buttons, (the button click is sent to the procedure for processing)
- Procedure-generated forms with the form data and button clicks processed by the procedure,
- Send/Process WebRequests,
- Send Email.
For more details and examples, see: ProcedureAPI
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