Stored Procedure Component

The Stored Procedure component (SP component) allows executing Stored Procedure for your database application.

Connection

In the Connection section, select a database connection. If you don't have a connection, then you need to create a Database Connection.

Stored Procedure Settings

In the Stored Procedure Settings section, select the needed Stored Procedure and provide mapping for Stored Procedure parameters. (If you don't have a ready to use Stored Procedure yet, then you can pick one from our Examples section below.)

Each parameter has a name and type, which you can also map into a variable from the service flow. This means when the service flow is executed, the variables you map will be automatically passed into this component.

Response Body

The last section of the SP component is the Response body section. This is where you define the output of this component. You can manually enter the response but we recommend to click the Generate button to test the component and set its response this way.

When you click the Generate button, a popup will open and there you can test the Stored Procedure. You will need to enter any parameter values (if any). Once you have tested the Stored Procedure, click Import response button to set the response (template) for this component.

Parameter Types Mapping

API Express works with Relation Database via JDBC driver version 4.0.
Every database has its own set of data types and all of them have to be mapped into limited set of types:

NUMBER
STRING
DATE
TIME
TIMESTAMP
BOOLEAN
CLOB
BLOB
BINARY
BYTE
SHORT
INTEGER
LONG
FLOAT
DOUBLE

❗️
In majority cases, API Express defines types of parameters of Stored Procedure automatically but in some cases it is impossible and in this case type of parameter is set as OTHER and it is user's responsibility to manually provide a correct type.
Even in the case of automatic type detection, it is highly recommended to double check parameter types and change them to correct ones if the detection was wrong.

🚧
Pay attention that the DATE type contains information only about the year, month and date only. If it's needed to get/pass information about hours, minutes, seconds then the TIMESTAMP type should be used.

❗️
Some databases provide the ability to create an alias for a data types (for example Boolean type can be an alias on number type) in this case the original type should be specified.

Examples section

MySQL Examples

MySQL the example of Stored Procedure which returns passed value

create procedure echo(IN message varchar(255))
  BEGIN
    SELECT message;
  END;

MySQL example of Stored Procedure which calculates sqrt of passed parameter

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$
CREATE PROCEDURE my_sqrt(input_number INT, OUT out_number FLOAT)
  BEGIN
    SET out_number=SQRT(input_number);
  END$$

DELIMITER ;

Example of working with tables in Stored Procedure

create table books
(
  id int auto_increment primary key,
  name char(255) not null
)
  engine=InnoDB
;

Insert initial data into books table

insert into books (name) values ('In Search of Lost Time')  ;
insert into books (name) values ('Don Quixote')  ;
insert into books (name) values ('Ulysses')  ;

DELIMITER //
CREATE PROCEDURE GetBooksByName(IN pname VARCHAR(255))
  BEGIN
    SELECT *
    FROM books
    WHERE name = pname;
  END //
DELIMITER ;

Procedure INSERT

DELIMITER //
CREATE PROCEDURE insertBook(IN name VARCHAR(255))
  BEGIN
	insert into books (name) values (name);
  END //
DELIMITER ;

API Express response

{
    "data": {
        "updateCount1": 1
    },
    "status": "success"
}

Procedure UPDATE

CREATE PROCEDURE updateBookByName(IN pname VARCHAR(255), IN newName VARCHAR(255))
  BEGIN
  UPDATE books SET name = newName WHERE name = pname;
  END

API Express response

{
    "data": {
        "updateCount1": 1
    },
    "status": "success"
}

Procedure DELETE

CREATE PROCEDURE deleteBookByName(IN pname VARCHAR(255))
  BEGIN
  DELETE from books WHERE name = pname;
  END

API Express response

{
    "data": {
        "updateCount1": 1
    },
    "status": "success"
}

MSSQL Examples

MSSQL example of Stored Procedure which returns passed value

CREATE PROCEDURE echo(
  @message VARCHAR(255)                   
)
AS
  BEGIN
    SELECT @message as message
  END

Invoked Stored Procedure with a value of parameter equal hello the response will be:

{
    "data": {
        "#result-set-1": [
            {
                "message": "hello"
            }
        ]
    },
    "status": "success"
}

Create books table

create table books
(
  id int identity primary key,
  name varchar(255)
)

Insert initial data

insert into books (name) values ('In Search of Lost Time')  ;
insert into books (name) values ('Don Quixote')  ;
insert into books (name) values ('Ulysses')  ;

Stored Procedure which returns book info by id

CREATE PROCEDURE GetBookById(
  @id INT
)
AS
  BEGIN
    SELECT * FROM books WHERE id=@id
  END

For parameter 1 Stored Procedure component returns

{
    "data": {
        "#result-set-1": [{
            "id": 1,
            "name": "In Search of Lost Time"
        }]
    },
    "status": "success"
}

Create Procedure getBookNameById
    (
      @id as int,
      @name as nvarchar(20) out
    )
  as
    begin
      select @name=name from books where id=@id
    end

{
    "data": {
        "@name": "In Search of Lost Ti"
    },
    "status": "success"
}

Procedure INSERT

CREATE PROCEDURE insertBook(
  @name as nvarchar(20)
)
AS
  BEGIN
    insert into books (name) values (@name);
  END

API Express response

{
    "data": {
        "updateCount1": 1
    },
    "status": "success"
}

Procedure UPDATE

CREATE PROCEDURE updateBookByName(
  @pname as nvarchar(20),
  @newName as nvarchar(20)
)
AS
  BEGIN
    UPDATE books SET name = @newName WHERE name = @pname;
  END

API Express response

{
    "data": {
        "updateCount1": 1
    },
    "status": "success"
}

Procedure DELETE

CREATE PROCEDURE deleteBookByName(@pname as nvarchar(20))
AS
  BEGIN
  DELETE from books WHERE name = @pname;
  END

API Express response

{
    "data": {
        "updateCount1": 1
    },
    "status": "success"
}

PostgreSQL Examples

CREATE OR REPLACE FUNCTION echo(message varchar(255)) RETURNS varchar(255) AS $$
BEGIN
  RETURN message;
END;
$$ LANGUAGE plpgsql;

{
    "data": {
        "#result-set-1": [{
            "result": "hello"
        }]
    },
    "status": "success"
}

create table books
(
  id integer not null constraint books_pkey primary key,
  name varchar(255)
)
;

insert into books (id, name) values (1, 'In Search of Lost Time')  ;
insert into books (id, name) values (2, 'Don Quixote')  ;
insert into books (id, name) values (3, 'Ulysses')  ;

CREATE OR REPLACE FUNCTION AddBook(id INT, name varchar(255))
  RETURNS void AS $$
BEGIN
  INSERT INTO books VALUES (id, name);
END;
$$ LANGUAGE plpgsql;

{
    "data": {
        "#result-set-1": [
            {
                "result": {
                    "type": "void",
                    "value": ""
                }
            }
        ]
    },
    "status": "success"
}

Oracle Examples

create table "books"
(
  "id" NUMBER not null primary key,
  "name" VARCHAR2(255)
)

Procedure SELECT

CREATE OR REPLACE PROCEDURE selectBookNameById (id IN number, bookName OUT VARCHAR2)
IS
  BEGIN
    SELECT "name" INTO bookName from "books"  WHERE "id" = id;
  END selectBookNameById;
/

API Express response

{
    "data": {
        "BOOKNAME": "Test"
    },
    "status": "success"
}

Procedure INSERT

CREATE OR REPLACE PROCEDURE insertBook (id IN number, pname IN VARCHAR)
as
  BEGIN
    INSERT INTO "books" ("id", "name") VALUES (id, pname);
  END insertBook;
/

API Express response

{
    "data": {},
    "status": "success"
}

Procedure UPDATE

CREATE OR REPLACE PROCEDURE updateBook (id IN number, pname IN VARCHAR)
as
  BEGIN
    UPDATE "books" SET "name" = pname WHERE "id" = id;
  END updateBook;
/

API Express response

{
    "data": {},
    "status": "success"
}

Procedure DELETE

CREATE OR REPLACE PROCEDURE deleteBook (id IN number)
as
  BEGIN
    DELETE from "books" WHERE "id" = id;
  END deleteBook;
/

API Express response

{
    "data": {},
    "status": "success"
}