Procedure API

Procedure Results

Stored procedures can return one or more result sets.  If the Stored Procedure output style is set to "Server Directed" or "Automatic", then dbFront will examine the first result-set and look for a first column named Action.  The value of that column determines what dbFront will do with the remaining output of the stored procedure.

dbFront currently supports the following action types:

• Close: Exit with an optional message and optionally refresh the screen,
• OpenDialog: Create a dialogue with optional: HTML Content, Form Fields, Custom Buttons and Table Results.  The updated Form Fields and Custom Button press are passed back to the procedure,
• OpenReport: Open the procedure output as a Report or Export,
• OpenTable: Redirect the user to a specific table, 
• OpenUrl: Redirect the user to a URL,
• SendEmail: Have dbFront send an EMail and return to the procedure for further processing,
• WebRequest: Have dbFront send a WebRequest, returning the results to the procedure.

Procedure API Fields

When configuring a stored procedure, there are two optional expressions that you can set as procedure parameters.

• {%dbfsp_header%} : This expression returns a JSON structure that details the current state.
• {%dbfsp_data%} : This expression returns the result of the last action: OpenDialog, or WebRequest

Various actions allow you to specify a Handle, which is returned in parameter {%dbfsp_header%}.  The purpose of the handle is to allow dbFront and the procedure to manage state.

Each run of the procedure has the option to return with a completely different request or exit with nothing.

Examples

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: The value {%DatabaseKey%} is replaced with the current Database Key.

The following example shows how you can create an advanced Search Dialog that also allows online search and local creation of new entries. Custom Local & Online Search (ProcedureAPI)

Close (exit)

Close and optionally display a message or trigger a screen refresh.  Any extra result sets are ignored.

Action	Message (Optional)	Refresh
Close	Message text or Html	Screen, Table or Row

OpenDialog (return / exit)

Open the specified message in a dialogue.   The message is shown at the top of the dialogue.  Any extra result sets are appended below and formatted as tables.

Example: Validate Inputs and Prompt if Missing Example

Action	Message (Optional)	Fields	Buttons	ReloadDelay	Handle
OpenDialog	Message text or Html	[  "Field Caption 1",  "Field Caption 2",  "Field Caption 3" ] {  "Field Caption 1":"varchar(100)",  "Field Caption 2":"int",  "Field Caption 3":"bit" } Next	[  "Button Caption 1",  "Button Caption 2", ] {  "Button Caption 1":null,  "Button Caption 2":null }	2	UniqueValue

If the Fields, Buttons, and ReloadDelay columns are all empty, then the dialogue will have a close button that simply exits with no further calls to the procedure.

The ReloadDelay column instructs dbFront to refresh the dialogue based on a new call to the procedure after the specified number of seconds.  The procedure can respond with any action, including a new dialogue or simply exiting.

The Buttons column instructs dbFront to add the specified buttons to the bottom of the dialogue.  The name of the clicked button is returned to the procedure via the {%dbfsp_header%} and {%dbfsp_data%} parameters.

The Fields column instructs dbFront to generate a form with the specified fields and to return the form data to the procedure at the next button click or ReloadDelay-triggered refresh.  The form data is returned as a JSON structure via the {%dbfsp_data%} parameter.

If the value of the Fields column is set to "Next" then the very next result set is used and expected to contain the following structure with one row for each field and at minimum the FieldCaption and FieldType columns.

FieldName	FieldCaption (Required)	FieldType (Required)	FieldOptions (Default: None, Allow Multiple)	SelectOptions	FieldValue (Default:null)
FieldName1	Field Caption 1	varchar(size) bit bool date datetime int numeric(size,precision)	ReadOnly | Required | Hidden | Password	[  "Red",  "Green",  "Blue" ] {  1: "Red",  2: "Green",  3: "Blue" }	2

If the SelectOptions column is null, then a simple field of the specified type and size is drawn.  If the SelectOptions column is populated with a JSON array or dictionary, then a dropdown list will be created.

OpenReport (exit) 

Open the Stored Procedure output as a Report or Export.  The Template would either be a predefined Quick Print Template or an optional Excel Template.

This allows a procedure to dynamically generate the Report Name and dynamically choose the Report/Export format based on the data or the user-provided input.

Example: Select OpenReport Example

Action	ReportName	OutputType	Template (Print or Excel)	HeaderRow (Print)	ExcelRange (Excel, Multiline)	PrinterName (Print)	PageHeight (Print)	PageWidth (Print)
OpenReport	Customers List	HtmlPrint, ServerPrint, PDF, Csv, Excel, Zip	CustomerList.xlsx	True if a single header detail row is present.	$'Listing'!A1:H3 $'Data'	EPSON ET-3850

OpenTable (exit)

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].

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

OpenUrl (exit)

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].

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

SendEmail (return)

Send an email to the specified email addresses with the specified message body.  Any extra result sets are appended below the main message and formatted as tables. 

Action	To	Subject	Message	Handle
SendEmail	[email protected] ; [email protected]	Subject Line Text	Message text or Html	UniqueValue

Once the email is sent, then dbFront will call the same procedure with an updated {%dbfsp_header%}.  The procedure can then exit or proceed with the next operation or request.

WebRequest (return)

Send a WebRequest to the specified Url, with the specified Header and Body and return the results to the Stored Procedure.

Example: Advanced Find or Create

Action	Url	Header (Json)	Body (Json or Form-Data)	Method	Path	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.	JsonPath XPath(Xml) XPath(Html)	UniqueValue

Once the WebRequest returns, then dbFront will call the same procedure with an updated {%dbfsp_header%} and with the data returned to the optional {%dbfsp_data%} parameter.  The procedure can then proceed with the next operation or request.

If no Path is specified, then the raw WebRequest data is sent to the procedure.  If a Path is specified, dbFront will use either JsonPath or XPath to extract the requested data before sending it to the procedure.

Debugging

On the main screen of the Action Button, or when setting up a Request Handler, you can enable a Debug flag that will cause dbFront to display extra debug information when possible.

You can also examine the Log File for messages or error details.

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]