Skip to content

Procedure

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

Basic Usage

!!! To use procedures you need to add database that supports them(default DB is H2 that does not support procedures) 1. Open Database perspective and click on Databases at the bottom 2. Click New and add your database information 3. Use you newly added database in most methods as databaseType

Create Procedure:

import { procedure } from "@dirigible/db";
import { response } from "@dirigible/http";

const sql = " \
CREATE PROCEDURE CUSTOMERS_BY_COUNTRY_AND_ALL_CUSTOMERS(c_id integer, c_name text, c_country text) \
LANGUAGE SQL \
AS $$ \
  INSERT INTO CUSTOMERS(id, name, country) values (c_id, c_name, c_country); \
$$; \
"

procedure.create(sql, "psql");

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

const response = require("http/response");
const procedure = require("db/procedure");

const sql = " \
CREATE PROCEDURE CUSTOMERS_BY_COUNTRY_AND_ALL_CUSTOMERS(c_id integer, c_name text, c_country text) \
LANGUAGE SQL \
AS $$ \
  INSERT INTO CUSTOMERS(id, name, country) values (c_id, c_name, c_country); \
$$; \
"

procedure.create(sql, "psql");

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

Call Procedure:

import { query, procedure } from "@dirigible/db";
import { response } from "@dirigible/http";

const sql = "CALL CUSTOMERS_BY_COUNTRY_AND_ALL_CUSTOMERS(c_id => ?, c_name => ?, c_country => ?)";

try {
    procedure.execute(sql, [6, "IBM", "USA"], "psql");
} finally {
    let result = query.execute("SELECT * FROM CUSTOMERS", [], "psql");

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

const response = require("http/response");
const procedure = require("db/procedure");
const query = require("db/query");

const sql = "CALL CUSTOMERS_BY_COUNTRY_AND_ALL_CUSTOMERS(c_id => ?, c_name => ?, c_country => ?)";
try {
    procedure.execute(sql, [6, "IBM", "USA"], "psql");
} finally {
    let result = query.execute("SELECT * FROM CUSTOMERS", [], "psql");

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

Functions

Function Description Returns
create(sql, datasourceName?) Creates a SQL Stored Procedure in the selected datasourceName, throws Error, if issue occur -
execute(sql, parameters?, datasourceName?) Execute SQL Stored Procedure in the selected 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"
  }
];