Procedure

Simplified procedure functionality, accepts SQL script and query parameters and returns the result-set as a JSON object.

Overview

• Module: db/v4/procedure
• Alias: db/procedure
• Definition: https://github.com/eclipse/dirigible/issues/773
• Source: /db/v4/update.js
• Facade: none
• Status: stable

Basic Usage

Create Procedure:

var response = require("http/v4/response");
var procedure = require("db/v4/procedure");

let sql = " \
CREATE PROCEDURE GET_DIRIGIBLE_EXTENSIONS_BY_EXTENSIONPOINT_NAME (in extensionName varchar(255), out extensions DIRIGIBLE_EXTENSIONS, out extensionPoints DIRIGIBLE_EXTENSION_POINTS) \
AS \
  BEGIN \
    extensions = SELECT * FROM DIRIGIBLE_EXTENSIONS WHERE EXTENSION_EXTENSIONPOINT_NAME = :extensionName; \
    extensionPoints = SELECT * FROM DIRIGIBLE_EXTENSION_POINTS WHERE  EXTENSIONPOINT_NAME = :extensionName; \
  END; \
";

procedure.create(sql);

response.println("Procedure created");
response.flush();
response.close();

Call Procedure:

var response = require("http/v4/response");
var procedure = require("db/v4/procedure");

let sql = "CALL GET_DIRIGIBLE_EXTENSIONS_BY_EXTENSIONPOINT_NAME(extensionName => ?, extensions => ?, extensionPoints => ?)";
let result = procedure.execute(sql, ["api-modules"]);

response.println(JSON.stringify(result));
response.flush();
response.close();

Functions

---

Function	Description	Returns
create(sql, databaseType?, datasourceName?)	Creates a SQL Stored Procedure in the selected databaseType and datasourceName, throws Error, if issue occur	-
execute(sql, parameters?, databaseType?, datasourceName?)	Execute SQL Stored Procedure in the selected databaseType and datasourceName with the provided parameters and returns the result, if any	array of arrays

Sample Parameters Array:

let parameters = [1, 'John', 34.56];

or

let parameters = [
  {
    value: 1,
    type: "int"
  }, {
    value: 'John',
    type: "string"
  }, {
    value: 34.56
    type: "double"
  }
];