Procedure
Simplified procedure functionality, accepts SQL script and query parameters and returns the result-set as a JSON object.
- Module:
db/procedure
- Definition: https://github.com/eclipse/dirigible/issues/773
- Source: /db/procedure.js
- Status:
stable
- Group:
core
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"
}
];