Manage Stored Procedures

Manage Stored Procedures
lac42
You can leverage the business logic that is already developed in your database using stored procedures. If your data source supports stored procedures, 
CA Live API Creator
 exposes the stored procedures in your database and makes them available as RESTful resource endpoints. You can supply 
input
 , 
output
 , or 
input-output
 arguments and you can read results in the JSON response. You can also protect these resources.
For more information about how to protect these resources, see Authorization and Role-Based Endpoint Access.
In this article:
 
 
View your Stored Procedures
  1. With your API open, in the Create section, click 
    Schema
    .
    The tables are listed by default.
  2. Click the 
    Procedures
     tab.
    API Creator displays your stored procedures as a list of names of each stored procedure.
  3. Click a stored procedure from the list.
    The 
    input
    output
    input-output
     arguments, with their data types, display.
Example Stored Procedure
The Demo API sample includes the 
get_employee
 stored procedure. This stored procedure includes the given_employee_id and 
plus_one
 parameters. The 
plus_one
 argument is an input-output argument and the 
given_employee_id
 argument is an input argument.
The following code snippet shows the code for 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:
 
CA Live API Creator
 returns the response of the stored procedure in the following format: 
  • For each result set, 
    CA Live API Creator
     returns the column metadata in the 
    columnMeta
     section. 
  • If the stored procedure returns data, 
    CA Live API Creator
     returns the rows in the 
    row
     section.
  • If the stored procedure updates data, 
    CA Live API Creator
     returns the update counts that arise from the SQL in the 
    updateCount
     section.
  • For Oracle stored procedures, 
    CA Live API Creator
     returns Oracle result sets as 
    REF CURSOR OUT
     parameters. It places a reference to this value in the 
    arg
     section. It places the actual result data in the 
    result
     array. You can add multiple 
    REF CURSOR OUT
     parameters in the same stored procedure.
Call Stored Procedures using the SysUtility.getProcedure() Method
You can call stored procedures from rules, libraries, and functions using the 
SysUtility.getProcedure()
 method.
Example:
 
The following example demonstrates calling a stored procedure using the 
SysUtility.getProcedure()
 method:
var parms = {arg1: 1, arg2: 2};
var procedureResponse =
SysUtility.getProcedure(procedureName, argsList)
;
For more information about this method, see The SysUtility Object.
Call Stored Procedures using a GET Call
You can call a stored procedure endpoint using a GET call.
Prefix argument definitions with 
arg
 . 
Example:
 
The following URL demonstrates calling the 
get_employee
 stored procedure using a GET call:
http://localhost/rest/default/demo/v1/
demo:get_employee
?arg.GIVEN_EMPLOYEE_ID=1&arg.PLUS_ONE=1
The following response is expected:
{
"arg": {
"GIVEN_EMPLOYEE_ID": 1,
"PLUS_ONE": 2
},
"result": [
{
"truncated": false,
"rowCount": 1,
"filteredCount": 0,
"
columnMeta
": [
{
"catalog": "",
"schema": "DEMO",
"tableName": "employee",
"columnName": "employee_id",
"columnLabel": "employee_id",
"columnType": "BIGINT"
},
{
"catalog": "",
"schema": "",
"tableName": "",
"columnName": "plus_one",
"columnLabel": "plus_one",
"columnType": "INTEGER"
},
{
"catalog": "",
"schema": "DEMO",
"tableName": "employee",
"columnName": "login",
"columnLabel": "login",
"columnType": "VARCHAR"
},
...
Call Stored Procedures using a POST Call
You can POST to a stored procedure by passing in the arguments in the body. You can include an array of separate arguments for multiple invocations to the same stored procedure. 
CA Live API Creator
 invokes in a single transaction and returns results in a 
procresults
 array in the resulting JSON.
In the 
Demo
 API sample, you can call the 
get_employee
 stored procedure by calling a POST.  
Example:
 
The following example shows the arguments to pass in the request body:
{ "GIVEN_EMPLOYEE_ID": 1, "PLUS_ONE": 5 }
Example:
 
The following example shows an array of arguments for multiple invocations of the stored procedure with different arguments to pass in the request body:
[ { "GIVEN_EMPLOYEE_ID": 1, "PLUS_ONE": 5 }, { "GIVEN_EMPLOYEE_ID" : 2} ]
The following result is expected:
 
Click to expand...
{ "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": []
}
]
}