Docs‎ > ‎Logic Designer‎ > ‎REST Resources‎ > ‎

Stored Procedure Resources

Your Stored Procedures are provided as RESTful Resource End Points, including support for supplying arguments and reading results a JSON.

Automatic Discovery

Espresso reads the Schema to discover the Stored Procedures in your database and make them available as Resource End Points.  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

Invoke Stored Procedures using GET as shown below, using this URL:

http://houston.d.espressologic.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 End Points.

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

Espresso also supports several Oracle-specific Procedure 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": []
    }
  ]
}

ċ
Sproc.json
(6k)
Val Huber,
Feb 15, 2014, 9:39 PM