Stored Procedures And Functions

Stored procedure and function support via the REST API is currently just for discovery and calling the stored procedures and functions that you have already created on your database, not for managing the stored procedures and functions themselves. Stored procedures can be accessed on each database service by the '''_proc''' resource. Stored functions can be accessed by the '''_func''' resource. 

As with most database features, there are a lot of common things about stored procedures and functions across the various database vendors, with of course, some pretty big exceptions. DreamFactory's blended API defines the difference between stored procedures and functions and how they are used in the API as follows. 

Procedures can use input parameters ('IN') and output parameters ('OUT'), as well as, parameters that serve both as input and output ('INOUT'). They can, except in the Oracle case, also return data directly. Functions may only have input parameters and return data directly, typically a single scalar value. Procedures cannot be utilized in a SELECT, WHERE, or HAVING clauses of statements, but functions can be.

Database Vendor Exceptions:
* SQLite does not support procedures or functions.
* PostgreSQL calls procedures and functions the same thing (a function) in PostgreSQL. DreamFactory calls them procedures if they have OUT or INOUT parameters or don't have a designated return type, otherwise functions.
* SQL Server treats OUT parameters like INOUT parameters, and therefore require some value to be passed in.

== Listing Available Stored Procedures or Functions ==
Description:  List the available stored procedures or functions by name, based on role accesses allowed.

URI: 
<pre>GET http[s]://<server>/api/v2/<service>/_proc</pre>

URI: 
<pre>GET http[s]://<server>/api/v2/<service>/_func</pre>

== Getting Stored Procedure or Function Details ==
Description:  Use the 'ids' URL parameter, passing a comma-delimited list of resource names to retrieve details about each of the requested procedures or functions. The details include things like the functions return type, if any, and details on the parameters required.

<syntaxhighlight lang=javascript>
{
	"resource": [
		{
			"name": "search_todos_by_name",
			"returns": null,
			"params": [
				{
					"name": "search",
					"position": 1,
					"param_type": "IN",
					"type": "string",
					"dbType": "varchar",
					"length": 128,
					"precision": null,
					"scale": null,
					"default": null
				},
				{
					"name": "inc",
					"position": 2,
					"param_type": "INOUT",
					"type": "integer",
					"dbType": "int",
					"length": null,
					"precision": 10,
					"scale": 0,
					"default": null
				},
				{
					"name": "count",
					"position": 3,
					"param_type": "OUT",
					"type": "integer",
					"dbType": "int",
					"length": null,
					"precision": 10,
					"scale": 0,
					"default": null
				},
				{
					"name": "total",
					"position": 4,
					"param_type": "OUT",
					"type": "integer",
					"dbType": "int",
					"length": null,
					"precision": 10,
					"scale": 0,
					"default": null
				}
			],
			"access": 31
		}
	]
}
</syntaxhighlight>

URI: 
<pre>GET http[s]://<server>/api/v2/<service>/_proc?ids=proc1,pro2,proc3</pre>

URI: 
<pre>GET http[s]://<server>/api/v2/<service>/_func?ids=func1,func2,func3</pre>

== Calling a Stored Procedure or Function ==
Procedures and functions can be called in two ways, using either the GET verb or the POST verb

=== Using '''GET''' ===
When passing no payload is required, any IN or INOUT parameters can be sent by passing the values in order required inside parentheses
<pre>/api/v2/db/_proc/myproc(val1, val2, val3)</pre> or as URL parameters by parameter name <pre>/api/v2/db/_proc/myproc?param1=val1&param2=val2&param3=val3</pre> 

URI: <pre>GET http[s]://<server>/api/v2/<service>/_proc/<proc_name>[(<value>,<value>)]</pre>
URI: <pre>GET http[s]://<server>/api/v2/<service>/_func/<func_name>[(<value>,<value>)]</pre>
OR
URI: 
<pre>GET http[s]://<server>/api/v2/<service>/_proc/<proc_name>[?<parameter_name>=<value>]</pre>
URI: <pre>GET http[s]://<server>/api/v2/<service>/_func/<func_name>[?<parameter_name>=<value>]</pre>

=== Using '''POST''' ===
When a payload is required, i.e. passing values that are not URL compliant, or passing '''schema''' formatting data (see below), include the parameter values directly in order...
<syntaxhighlight lang=javascript>
{
    "params": ["val1", 123, 123.45, true]
}
</syntaxhighlight>

...or in any order using an object with name-value pairs...
<syntaxhighlight lang=javascript>
{
    "params": {
        "<param_name>": "<param_value>",
        "<param_name>": "<param_value>",
        "<param_name>": "<param_value>"
    }
}
</syntaxhighlight>

...or, for backwards compatibility, in any order using array of parameter objects...
<syntaxhighlight lang=javascript>
{
    "params": [{
                "name": "<param_name>",
                "value": "<param_value>"
        }, {
                "name": "<param_name>",
                "value": "<param_value>"
        }, {
    }]
}
</syntaxhighlight>

URI: <pre>POST http[s]://<server>/api/v2/<service>/_proc/<procedure_name></pre>

URI: <pre>POST http[s]://<server>/api/v2/<service>/_func/<function_name></pre>

== Formatting Results ==
For functions, the results can be returned to the client as is (i.e. formatted to the return data type designated by the function itself) or formatted using the '''returns''' URL parameter. This parameter can be set to any scalar type, including string, integer, double, boolean, etc.

For procedures that do not have INOUT or OUT parameters, the results can be returned as is, or formatted using the '''returns''' URL parameter if the value is a single scalar value, or the '''schema''' payload attribute for result sets. 

If INOUT or OUT parameters are involved, any procedure response is wrapped using the configured (a URL parameter '''wrapper''') or default wrapper name (typically "resource"), and then added to the output parameter listing. The output parameter values are formatted based on the procedure configuration data types.

Note that without formatting, all data is returned as strings, unless the driver (i.e. mysqlnd) supports otherwise. If the stored procedure returns multiple data sets, typically via multiple "SELECT" statements, then an array of datasets (i.e. array of records) is returned, otherwise a single array of records is returned. 

* '''''schema''''' - When a result set of records is returned from the call, the server will use any name-value pairs, consisting of "<field_name>": "<desired_type>", to format the data to the desired type before returning.
* '''''wrapper''''' - Just like the URL parameter, the wrapper designation can be passed in the posted data.

Request with formatting configuration...
<syntaxhighlight lang=javascript>
{
    "schema": {
        "id": "integer",
        "complete": "boolean"
    },
    "wrapper": "data"
}
</syntaxhighlight>

Response without formatting...
<syntaxhighlight lang=javascript>
{
  "resource": [
    {
      "id": "3",
      "name": "Write an app that calls the stored procedure.",
      "complete": 1
    },
    {
      "id": "4",
      "name": "Test the application.",
      "complete": 0
    }
  ],
  "inc": 6,
  "count": 2,
  "total": 5
}
</syntaxhighlight>


Response with formatting applied...
<syntaxhighlight lang=javascript>
{
  "data": [
    {
      "id": 3,
      "name": "Write an app that calls the stored procedure.",
      "complete": true
    },
    {
      "id": 4,
      "name": "Test the application.",
      "complete": false
    }
  ],
  "inc": 6,
  "count": 2,
  "total": 5
}
</syntaxhighlight>

URI: <pre>POST http[s]://<server>/api/v2/<service>/_proc/<procedure_name>[?wrapper=<wrapper_name>&returns=<data_type>]</pre>

URI: <pre>POST http[s]://<server>/api/v2/<service>/_func/<function_name>[?wrapper=<wrapper_name>&returns=<data_type>]</pre>

== Events ==
Stored Procedure and Function resources add additional events to the scriptable events already available for all database services. Here ''db'' is the database service name.  They are as follows...
* db._proc.get,
* db._proc.{proc_name}.get,
* db._proc.{proc_name}.post,
* db._func.get,
* db._func.{proc_name}.get,
* db._func.{proc_name}.post

== Pre-2.2.1 Usage ==

Prior to release 2.2.1, if parameters were involved, the '''POST''' verb must be used and the payload must require the '''params''' attribute. The following information was also required for each parameter sent.

* '''''params''''' - An array of parameters definitions and settings for each parameter required by the stored procedure. Each parameter may consist of the following elements:
** '''''name''''' - String. Required. Name of the parameter as defined in the stored procedure.
** '''''param_type''''' - String. Allowed values of "IN" (input only), "INOUT" (input and returned as output), "OUT" (output only).  Defaults to "IN".
** '''''value''''' - Mixed. Required for "IN" and "INOUT" types. The value to pass into the stored procedure for the parameter.
** '''''type''''' - String. The data type of the value to be returned, defaults to type of passed in data (for INOUT) or string. Other allowed values are...
*** '''''int''''' or '''''integer''''' - cast the string as an integer. Note: Non-numeric string will be cast as 0.
*** '''''float''''' or '''''double''''' - cast the string as a float. Note: Non-numeric string will be cast as 0.
*** '''''bool''''' or '''''boolean''''' - cast the string as a boolean, true or false.
*** '''''time''''', '''''date''''', '''''datetime''''' or '''''timestamp''''' - cast the string as a reformatted string, using the system config [formatting options](Database-Date-Time-Formats).
** '''''length''''' - Integer. The total length for the returned value.

Request
<syntaxhighlight lang=javascript>
{
    "params": [
        {
            "name": "search",
            "param_type": "IN",
            "value": "%app%"
        },
        {
            "name": "inc",
            "param_type": "INOUT",
            "value": 5
        },
        {
            "name": "count",
            "param_type": "OUT",
            "type": "integer"
        },
        {
            "name": "total",
            "param_type": "OUT",
            "type": "integer",
            "length": 4
        }
    ]
}
</syntaxhighlight>