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:

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

Live API: getMysqlStoredProcedures

URI:

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

Live API: getMysqlStoredFunctions

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.

{
	"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
		}
	]
}

URI:

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

Live API: getMysqlStoredProcedures

URI:

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

Live API: getMysqlStoredFunctions

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

/api/v2/db/_proc/myproc(val1, val2, val3)

or as URL parameters by parameter name

/api/v2/db/_proc/myproc?param1=val1&param2=val2&param3=val3

URI:

GET http[s]://<server>/api/v2/<service>/_proc/<proc_name>[(<value>,<value>)]

URI:

GET http[s]://<server>/api/v2/<service>/_func/<func_name>[(<value>,<value>)]

OR URI:

GET http[s]://<server>/api/v2/<service>/_proc/<proc_name>[?<parameter_name>=<value>]

URI:

GET http[s]://<server>/api/v2/<service>/_func/<func_name>[?<parameter_name>=<value>]

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

{
    "params": ["val1", 123, 123.45, true]
}

...or in any order using an object with name-value pairs...

{
    "params": {
        "<param_name>": "<param_value>",
        "<param_name>": "<param_value>",
        "<param_name>": "<param_value>"
    }
}

...or, for backwards compatibility, in any order using array of parameter objects...

{
    "params": [{
                "name": "<param_name>",
                "value": "<param_value>"
        }, {
                "name": "<param_name>",
                "value": "<param_value>"
        }, {
    }]
}

URI:

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

URI:

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

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

{
    "schema": {
        "id": "integer",
        "complete": "boolean"
    },
    "wrapper": "data"
}

Response without formatting...

{
  "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
}

Response with formatting applied...

{
  "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
}

URI:

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

URI:

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

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

{
    "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
        }
    ]
}