Database Procedure - Create & Execute

Steps

1. Switch to the Database Perspective

2. Execute the following SQL script:

   CREATE TABLE CUSTOMERS (ID INTEGER PRIMARY KEY, NAME VARCHAR(50), COUNTRY VARCHAR(50));
   
   INSERT INTO CUSTOMERS VALUES (1, 'Google', 'USA');
   INSERT INTO CUSTOMERS VALUES (2, 'SAP', 'Germany');
   INSERT INTO CUSTOMERS VALUES (3, 'DigitalLights', 'Bulgaria');
   INSERT INTO CUSTOMERS VALUES (4, 'Quanterall', 'Bulgaria');
   INSERT INTO CUSTOMERS VALUES (5, 'SyMetric', 'India');
   

3. Create a project database-procedure.

4. Then create a JavaScript service named database-procedure-create.js.

5. Within the service code, enter the following content:

   var response = require("http/v4/response");
   var procedure = require("db/v4/procedure");
   
   let sql =
     " \
   CREATE PROCEDURE CUSTOMERS_BY_COUNTRY_AND_ALL_CUSTOMERS (in country varchar(50), out customersByCountry CUSTOMERS, out allCustomers CUSTOMERS) \
   AS \
     BEGIN \
       customersByCountry = SELECT * FROM CUSTOMERS WHERE COUNTRY = :country; \
       allCustomers = SELECT * FROM CUSTOMERS; \
     END; \
   ";
   
   procedure.create(sql);
   
   response.println("Procedure created");
   response.flush();
   response.close();
   

6. Create a JavaScript service named database-procedure-execute.js.

7. Within the service code, enter the following content:

   var response = require("http/v4/response");
   var procedure = require("db/v4/procedure");
   
   let sql =
     "CALL CUSTOMERS_BY_COUNTRY_AND_ALL_CUSTOMERS(COUNTRY => ?, customersByCountry => ?, allCustomers => ?)";
   let result = procedure.execute(sql, ["Bulgaria"]);
   
   response.println(JSON.stringify(result));
   response.flush();
   response.close();
   

---

For more information, see the API documentation.