CA Live API Creator 4.1

5.4 5.3 5.2 5.1 5.0 4.1 4.0 3.2 3.1.1 3.1
English

Manage Stored Procedures

Manage Stored Procedures
calac41
You can leverage the business logic that is already developed in your database using stored procedures. If the API database supports stored procedures, API Creator discovers the stored procedures in your database and makes them available as RESTful resource endpoints by reading the schema. You can supply arguments and read results in JSON. You can also protect these resources using application security.
For more information about how to protect stored procedure resources, see Security.
In this article:
View your Stored Procedures
You can view your stored procedures in API Creator.
For more information about viewing your schema, see Database Connectivity.
  1. With your API open, in the Create section, click
    Schema
    .
    The tables are listed by default.
  2. Click 
    Procedures
    .
    The stored procedures display.
    For example, in the Demo API, the 
    get_employee
     stored procedure includes the following arguments:
    given_employee_id
    plus_one
    For reference, the following code snippet defines the 
    get_employee
     stored procedure:
    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
    $$
Invoke Stored Procedures
You can invoke stored procedures using the following methods:
Call Stored Procedures using the SysUtility.getProcedure Method
You can call a stored procedures from rules, libraries, and functions, using the
SysUtility.getProcedure
 method.
Example:
var parms = {arg1: 1, arg2: 2};
var procedureResponse = 
SysUtility.getProcedure("myStoredProcedure", args)
;
For more information about the other methods that are available with the
SysUtility
 object, see The SysUtility Object.
Call Stored Procedures using the GET Method
You can call a stored procedure using the GET method, as follows:
Prefix argument definitions with 
arg
.
Example:
http://serer.acme.com/rest/abl/demo/v1/
get_employee
?arg.given_employee_id=1&arg.plus_one=1
This GET call defined the 
get_employee
 stored procedure.
  • If the stored procedure updates the database, any update counts arising from the SQL are returned.
  • 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. You can add multiple 
    REF CURSOR OUT
     parameters in the same stored procedure.
The following image shows the URL and the GET response in the REST Lab:
Screen Shot 2015-10-20 at 8.39.13 AM.png
POST to Stored Procedures
You can POST to a stored procedure. Contain the arguments in the body. You can include an array of separate arguments to multiple invocations to the same stored procedure. API Creator performs invocations in a single transaction and returns results in a 
procresults
 array in the resulting JSON.
The following example updates its first parameter 
(plus_one
). The parameter is returned in the JSON result (the 
arg{}
 object). The 
input
, 
output
, and 
input-output
args are supported.
Example:
The following POST to the 
demo:get_employee
 stored procedure:
[ { "given_employee_id": 1, "plus_one": 5 }, { "given_employee_id" : 2} ]
Expected Results
The following result is expected:
{  "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": []
    }
  ]
}