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"
  }
];