Stored Procedure Resources

Stored procedure resources enable you to leverage the business logic already developed in your database.

If the API Database supports stored procedures, these will automatically be created as REST endpoints in the Automatic Discovery. The stored procedures are provided as RESTful Resource endpoints, including support for supplying arguments and reading results a JSON. 

Automatic Discovery

API Creator reads the Schema to discover the Stored Procedures in your database and make them available as Resource endpoints.  You can use the Schema screen to see your procedures:

For reference, the procedure is coded as follows:
DELIMITER $$

PROCEDURE get_employee(
    IN given_employee_id BIGINT
   ,INOUT plus_one BIGINT
)
    COMMENT 'given an employee id and a number ''plus_one'', adds one to the number and returns the employee info as well as picture, voice and icon'
begin
 set plus_one = plus_one + 1;
 select e.employee_id
       ,plus_one
       ,e.login
       ,ep.icon
       ,ep.picture
       ,ep.voice
   from employee e
  right outer join employee_picture ep
     on e.employee_id = ep.employee_id
  where given_employee_id = e.employee_id;

 select *
   from purchaseorder
  where given_employee_id = salesrep_id
  order by order_number;
end

$$

GET

Use the GET method to Invoke a Stored Procedures as shown below (note that each argument is prefixed with arg. ) :

http://serer.acme.com/rest/abl/demo/v1/get_employee?arg.given_employee_id=1&arg.plus_one=1



For reference, the get_employee procedure was defined as shown below in MySQL.  Observe the argument definitions, referenced above with the prefix arg.
For fans of September 19th, pirate syntax is supported (e.g., arghhh.given_employee_id).

You can review the full JSON here.  Note:
  • If the stored procedure updates the database, we return any update counts arising from the SQL
  • MetaData for each result set is returned
  • Oracle Result sets are returned as REF CURSOR OUT parameters.  A reference to this value is place in the arg section.  The actual result data is placed in the "result" array.  Multiple REF CURSOR parameters in the same procedure is supported.  

Args supported: input, output, input-output

Observe this example updates its first parameter (plus_one)., and that it is returned in the JSON result (the arg{} object).

Security

You can control which users can access your procedure-based endpoints.

Default Security

To simplify administration, you can assign default access for your procedures for a given role, like this:



Specific Security

You can also control access on a procedure-by-procedure basis, like this:


Oracle-specific Extensions

API Creator also supports several Oracle Extensions.

Post support for Stored Procedures

You can perform a POST to a stored procedure.  Any arguments are contained in the body.  An array of separate arguments to multiple invocations to the same procedure is supported.  All invocations are performed in a single transaction.

Results are returned in a "procresults" array in the resulting JSON.

For example a POST of

[ { "given_employee_id": 1, "plus_one": 5 }, { "given_employee_id" : 2} ]

to the demo:get_employee procedure results in

{
  "statusCode": 200,
  "procresults": [
    {
      "arg": {
        "given_employee_id": 1,
        "plus_one": 6
      },
      "result": [
        {
          "truncated": false,
          "rowCount": 1,
          "filteredCount": 0,
          "columnMeta": [
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee",
              "columnName": "employee_id",
              "columnLabel": "employee_id",
              "columnType": "BIGINT"
            },
            {
              "catalog": "",
              "schema": "",
              "tableName": "",
              "columnName": "plus_one",
              "columnLabel": "plus_one",
              "columnType": "BIGINT"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee",
              "columnName": "login",
              "columnLabel": "login",
              "columnType": "VARCHAR"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee_picture",
              "columnName": "icon",
              "columnLabel": "icon",
              "columnType": "VARBINARY"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee_picture",
              "columnName": "picture",
              "columnLabel": "picture",
              "columnType": "LONGBLOB"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee_picture",
              "columnName": "voice",
              "columnLabel": "voice",
              "columnType": "MEDIUMBLOB"
            }
          ],
          "rows": [
            {
              "employee_id": 1,
              "plus_one": null,
              "login": "sam",
              "icon": {
                "type": "base64",
                "length": 1185,
                "value": "iVBORw0KGgoAAAANSUhEUgAAADIAAABKCAIAAAB2LJBKAAAEaElEQVRoBdWZi5bbIAxE657+/y+7AsEwCIlHnE3aPV2vDNLoMsbOo9d937/o57ro5Hvhb279jzAJ0h9gHTJNXO3sh/5RkLDeB6StlfgRnEgc1U9Miuw40i8iR1gvMIH1DG4f6wnTMVx3J6L6x4LdtX0Ya3e9n8faMuzzWGLYmuwrWEuy9CTduXXX69vdNU6eA/Attxw6Hvq/sRyfeXEPYl/5627JrnU27iaWU/nAobHU6m9i+VaP8u8YSf5tYtnVvKP9TGMTaybxE3ObWB+4iN0Fae/lny9a2Tv5A1GsPAWbbh30gvw2klTYok2srRaG3em2JZOS3oalECCzyw+B3MStBwR6hdplon5Cd1utinn+ft0tYagYWVG/NLhvLEICxNyT4oh/yy3SqSGAENSZ9Fdp+i83eF7jiElm125NFzx8Hr/qSC7r7WxgAjRhSnlSPs/wsbSG59KIXMHryiZ1mmDNYN1UHnEO88cp93WKZQh8KdVj8stWo3OssFp5hQngcxMoUyrmuanpgzuRdkDrCsy2ojJELE5SS8/RhH1RTG2Kphak8fJ7iZncgBTH6g6Mq3iCFHiYYlfYLePM3K8920ivC10sV7wr4046YYRUwk3L+eNMp//63upk6jZWmogJJQsobHnNy48c1YSCH0RJRadS+sUyere7sb5opRGJ01FuJn71yI/oqKNtoQR2tD4yVrPENUrArTr1lKnqtOcZRrogoq5Jb9tbKsjL4ri2K9eufznCZAs6LL6gLeUbUYclABOy9H4g/0Scoz3jCGrnhlksJpMNMOdAj0kwIZtUOVjIhiLDuauUTCSjfBm4UlrlYwmHTOvRqK/uIZPun+br4E/pqI8lcy5TqtHHn1bTMRhOGcbIlJl/qdqGPtbEXhFQYjSrwZaPOblkhisfHqeFuhTUN+Z2LRmNByOrRtJxhHUQR26lhGpDSRYLjYsmAaLPAx9LVx+arFciJxWyANAMy6kZiRbgY0XZdVdlcflAUfNSv8kVr2neX59zjYV2MK/C7S5daNBcanUB9eihRluec6PtLHckpx3FS2vXbkX9BBf+RTk8DsN0UGsjhQMsXE1uthOb3vl04fQBFnYILgGCGE4YEgHItCTeGEXpCOv4dovaL9eziwUhCbD02CSdMVeKTzl2ZLZ6gKl0600ws2ji0muyO4VCCZJb8708duWReS130liAlkwJK/cwN29TA0HWas4TTao1s3Wk6UgEKartEvgk/CIpF6Mll9hYmNDSztXzHkVk2wprSvc3xJKsidvYXYmpE9QT21XyhUz+obArqhLKK8fwTjT1ME/lYE/q1HVIJ6ZW52mRfUX+sklQlI2PWapPVi0cDVYeL43GKSKAwBDk/goxzDW3ogSUmITWevQGXqJ4DETOKHLObG9xnokNSmM0efGpKmSnxyT674K2RKyiBkHxKHc8InD9CtXE7jaymwsFQvyCH0eM2gLWVEOSRsMC0JH082Rg8d4SxJ82ZZe8Yf0rRJn8LyOOamREwQ06AAAAAElFTkSuQmCC"
              },
              "picture": {
                "type": "base64",
                "length": 138795,
                "procInlineLimitExeceeded": 2000
              },
              "voice": {
                "type": "base64",
                "length": 127187,
                "procInlineLimitExeceeded": 2000
              }
            }
          ]
        },
        {
          "truncated": false,
          "rowCount": 5,
          "filteredCount": 0,
          "columnMeta": [
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "order_number",
              "columnLabel": "order_number",
              "columnType": "BIGINT"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "amount_total",
              "columnLabel": "amount_total",
              "columnType": "DECIMAL"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "paid",
              "columnLabel": "paid",
              "columnType": "TINYINT"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "notes",
              "columnLabel": "notes",
              "columnType": "VARCHAR"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "customer_name",
              "columnLabel": "customer_name",
              "columnType": "VARCHAR"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "salesrep_id",
              "columnLabel": "salesrep_id",
              "columnType": "BIGINT"
            }
          ],
          "rows": [
            {
              "order_number": 7,
              "amount_total": 1860,
              "paid": false,
              "notes": "",
              "customer_name": "Echo Environmental Services",
              "salesrep_id": 1
            },
            {
              "order_number": 9,
              "amount_total": 735,
              "paid": false,
              "notes": "Deliver to Frank Jones",
              "customer_name": "Quebec Geologic Services",
              "salesrep_id": 1
            },
            {
              "order_number": 10,
              "amount_total": 2024,
              "paid": false,
              "notes": "",
              "customer_name": "Romeo Restaurant Design",
              "salesrep_id": 1
            },
            {
              "order_number": 25,
              "amount_total": 65,
              "paid": false,
              "notes": "",
              "customer_name": "Lima Citrus Supply",
              "salesrep_id": 1
            },
            {
              "order_number": 1038,
              "amount_total": 635,
              "paid": false,
              "notes": "",
              "customer_name": "Baja Software Ltd",
              "salesrep_id": 1
            }
          ]
        }
      ],
      "updateCount": []
    },
    {
      "arg": {
        "given_employee_id": 2,
        "plus_one": null
      },
      "result": [
        {
          "truncated": false,
          "rowCount": 0,
          "filteredCount": 0,
          "columnMeta": [
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee",
              "columnName": "employee_id",
              "columnLabel": "employee_id",
              "columnType": "BIGINT"
            },
            {
              "catalog": "",
              "schema": "",
              "tableName": "",
              "columnName": "plus_one",
              "columnLabel": "plus_one",
              "columnType": "BIGINT"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee",
              "columnName": "login",
              "columnLabel": "login",
              "columnType": "VARCHAR"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee_picture",
              "columnName": "icon",
              "columnLabel": "icon",
              "columnType": "VARBINARY"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee_picture",
              "columnName": "picture",
              "columnLabel": "picture",
              "columnType": "LONGBLOB"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "employee_picture",
              "columnName": "voice",
              "columnLabel": "voice",
              "columnType": "MEDIUMBLOB"
            }
          ],
          "rows": []
        },
        {
          "truncated": false,
          "rowCount": 4,
          "filteredCount": 0,
          "columnMeta": [
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "order_number",
              "columnLabel": "order_number",
              "columnType": "BIGINT"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "amount_total",
              "columnLabel": "amount_total",
              "columnType": "DECIMAL"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "paid",
              "columnLabel": "paid",
              "columnType": "TINYINT"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "notes",
              "columnLabel": "notes",
              "columnType": "VARCHAR"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "customer_name",
              "columnLabel": "customer_name",
              "columnType": "VARCHAR"
            },
            {
              "catalog": "dbdev_demo",
              "schema": "",
              "tableName": "PurchaseOrder",
              "columnName": "salesrep_id",
              "columnLabel": "salesrep_id",
              "columnType": "BIGINT"
            }
          ],
          "rows": [
            {
              "order_number": 1,
              "amount_total": 1079,
              "paid": false,
              "notes": "This is a small order",
              "customer_name": "Alpha and Sons",
              "salesrep_id": 2
            },
            {
              "order_number": 4,
              "amount_total": 720,
              "paid": false,
              "notes": "Deliver by overnight with signature required",
              "customer_name": "Charlie's Construction",
              "salesrep_id": 2
            },
            {
              "order_number": 11,
              "amount_total": 1279,
              "paid": false,
              "notes": "",
              "customer_name": "Juliet Dating Inc.",
              "salesrep_id": 2
            },
            {
              "order_number": 14,
              "amount_total": 84,
              "paid": false,
              "notes": "",
              "customer_name": "Charlie's Construction",
              "salesrep_id": 2
            }
          ]
        }
      ],
      "updateCount": []
    }
  ]
}

ċ
Proc Access.png
(157k)
Val Huber,
Feb 16, 2016, 2:35 PM
ċ
Proc Default Access.png
(190k)
Val Huber,
Feb 16, 2016, 2:35 PM
ċ
ProcDiscov.png
(164k)
Val Huber,
Feb 16, 2016, 2:35 PM
ċ
Screen Shot 2015-10-20 at 8.39.13 AM.png
(281k)
Val Huber,
Feb 16, 2016, 2:35 PM
ċ
Screen Shot 2015-10-21 at 9.28.24 AM.png
(294k)
Val Huber,
Feb 16, 2016, 2:35 PM
ċ
Screen Shot 2015-10-21 at 9.29.43 AM.png
(282k)
Val Huber,
Feb 16, 2016, 2:35 PM
ċ
call Sproc.png
(204k)
Val Huber,
Feb 16, 2016, 2:35 PM
Comments