Stored Procedures And Functions

From DreamFactory
Jump to: navigation, search
DreamFactoryFeaturesDatabaseSQLStored 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 can not 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.

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: getProcs

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

Live API: getFuncs

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: getProcs

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

Live API: getFuncs

Calling a Stored Procedure or Function

Procedures and functions can be called in two ways,

  • using GET when passing no payload is required, any parameters can be sent by passing the values in order required inside parentheses, i.e. /api/v2/db/_proc/myproc(val1, val2, val3). In most cases, only the IN and INOUT parameters are to be passed.
  • using POST when parameters are required.

Note that without formatting, all data is returned as strings. 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. Optionally, you can add a URL parameter wrapper that will cause the returned data to be wrapped in the requested element.

Without Parameters or Formatting

Description: Call a stored procedure that doesn't require parameters or formatting, which would require posted data (see POST method below).

URI: 
GET http[s]://<server>/api/v2/<service>/_proc/<procedure_name>[?wrapper=<wrapper_name>]

Live API: callStoredProc

Request None

Response 

{
  "resource": [
    {
      "id": "4",
      "name": "Test the application."
    },
    {
      "id": "5",
      "name": "Demo stored procedures."
    }
  ]
}

Response with Multiple Data Sets 

{
    "resource": [
        [
            {
                "id": "4",
                "name": "Test the application."
            },
            {
                "id": "5",
                "name": "Demo stored procedures."
            }
        ],
        [
            {
                "id": "4",
                "name": "Test the application."
            },
            {
                "id": "5",
                "name": "Demo stored procedures."
            }
        ]
    ]
}

With Parameters and/or Formatting

Description: Call a stored procedure that does require parameters. Parameter settings and schema can be used to make the data more presentable to the client. Posted request can consist of the following elements:

  • 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.
  • 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.
URI: 
POST http[s]://<server>/api/v2/<service>/_proc/<procedure_name>[?wrapper=<wrapper_name>]

Live API: callStoredProcWithParams

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
        }
    ],
    "schema": {
        "id": "integer",
        "complete": "boolean"
    },
    "wrapper": "record"
}

Response 

{
  "record": [
    {
      "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
}
Retrieved from "https://wiki.dreamfactory.com/index.php?title=DreamFactory/Features/Database/SQL/Stored_Procedures_And_Functions&oldid=32490"