Exploring the new options for stored procedures

The ability to discover and execute stored procedures and functions has been available in DreamFactory's REST API for a while now. With the 2.2.1 release, we have enhanced the usability of these resources quite a bit, adding the ability to retrieve their parameter information, as well as, simplifying the way you call them.

Parameter details and return type now available

You have always been able to list the stored procedures by name using the GET verb on the _proc resource of the database service, and likewise for functions with the _func resource. Now using the same API with an additional URL parameter ("ids"), you can get additional details on the desired procedures or functions. Here is the format of the API call...

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

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

The ids URL parameter takes a comma-delimited list of resource (procedure/function) names to retrieve. The details returned include things like the function's return type, if any, and details on each of the parameters required. So a GET call to https://example.com/api/v2/db/_proc/?ids=search_todos would retrieve the following...

{

    "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": "total",

                    "position": 4,

                    "param_type": "OUT",

                    "type": "integer",

                    "dbType": "int",

                    "length": null,

                    "precision": 10,

                    "scale": 0,

                    "default": null

                }

            ]

        }

    ]

}

Calling a stored procedure or function made easier

Procedures and functions can be called in two ways, using either the GET verb or the POST verb as before, but now you do not need to pass in every detail about the parameters themselves. All that is required are the values for IN and INOUT parameters, unless there are defaults defined (not commonly supported). The INOUT and OUT parameter values are formatted to the correct types upon return.

Passing parameter values inline with GET

When passing no payload is required, any IN or INOUT parameter values can be sent by passing the values in the order required inside parentheses...

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

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

For example,

https://example.com/api/v2/db/_proc/search_todos(test, 0)

Passing parameters and values as URL parameters with GET

You can also send each parameter as a URL name-value pair the way you would typically for HTTP calls...

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

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

For example,

https://example.com/api/v2/db/_proc/search_todos?search=test&inc=0

Passing parameters as a payload with POST

When a payload is required, i.e. passing values that are not URL compliant, etc., or passing schema formatting data, use the API format below...

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

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

and include the parameter values directly in order as an array in the params element...

{

    "params": ["test%", 0]

}

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

{

    "params": {

        "search": "test%",

        "inc": 0

    }

}

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

{

    "params": [

        {

            "name": "search",

            "value": "test%"

        },

        {

            "name": "inc",

            "value": 0

        }

    ]

}

We hope that makes using your stored procedures and functions easier through the DreamFactory REST API. For more information on this topic, check out the wiki entry.