Querying data

Contents[Hide]

1. Default query

After the .js.sql file is executed an implicit query is initiated, sending to the database everything that was present in memory buffer.

If the query returned a result set, its name in the final JSON response will be “resultSet

If the query retuned multiple result sets, their default names will be “resultSet”,”resultSet2”,” resultSet3”,… etc

Select * from countries

 

2. Explicit query

Alternatively it’s possible to execute the buffer as SQL statement explicitly :

Select * from countries
--% sql.query();

Same as implicit query, the result sets go to “resultSet”, “resultSet2”, “resultSet3”, etc..

 

 

2.1. Update count

If the query updated one or many records, a script can find out the number of lines updated (actually the value returned depends on the database and its jdbc driver) for each statement like this:

DELETE * FROM TEMP_RECORDS;
--% response.writeObject("records",sql.getUpdateCount ());

for the first/unique update or

INSERT INTO CITIES(name, country) values (:p.name, :p.country);
DELETE * FROM TEMP_RECORDS;
--% response.writeObject("records",sql.getUpdateCount (1));

if multiple update statement were executed (with a stored procedure or multiple statements within one query if the DB allows this) where N is the number of the statement (starting with 0)

 

 

2.2. Explicit resut set name

In order to indicate the resultSet property name in the final JSON response, we can pass a parameter to sql.query()

Select * from countries

--% sql.query("countries");

Or, in case of multiple result sets returned, a comma separated list of resultset names.

Select * from clinets;
Select * from orders;
Select * from cities;

--% sql.query("clients,orders,cities");

 

 

3. Custom row mapper

 

In case there is a need to transform the returned resultset on  the fly, it’s possible to use custom row mappers:

Example :

 

SELECT * FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesHiddenPersonalData",function(row, index, rsName){
      row.EMAIL = row.EMAIL.substring(0,2)+row.EMAIL.substring(2).replace(/./g,"*");  // HIDE private information
      return row;
    });
%>

or

SELECT * FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesArray",function(row, index, rsName){
      return [ row.EMPLOYEE_ID, row.FIRST_NAME,
        row.LAST_NAME, row.EMAIL, row.PHONE_NUMBER,
        row.HIRE_DATE, row.JOB_ID, row.SALARY,
        row.COMMISSION_PCT ]; // return each row as an array instead of map
    });
%>

or

 

SELECT EMPLOYEE_ID FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesIds",function(row, index, rsName){
      return row.EMPLOYEE_ID // return only one numeric column per row
    });
%>

or

SELECT FIRST_NAME, LAST_NAME FROM employees ORDER BY EMPLOYEE_ID
<%
 sql.query("employeesNames",function(row, index, rsName){
      return row.FIRST_NAME+" "+row.LAST_NAME // performs a server-side operation, return only one string column per row
    });
%>

 

 

4. Return additional data.

To send some additional objects as a JSON reply to the client along with or instead of SQL streaming, use

response.writeObject("name", value);

For example,  calling a static method of a Java class 

response.writeObject("appServerIP", Java.type("java.net.Inet4Address").getLocalHost().getHostAddress());

 

 

5. Query to memory (buffering mode)

Instead of streaming the result set directly to the client, it’s possible to buffer it in memory for eventual additional handling. Example :

SELECT *
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID
<% 
  var employees  = sql.queryToBuffer();
  var salarySum = 0;
  for (var i=0;i<employees.length;i++)
    salarySum+=employees[i].SALARY;
  var avgSalary = salarySum/employees.length;
  response.writeObject("employees ",employees);
  response.writeObject("avgSalary", avgSalary);
%>

 

 

6. Static include

 

To statically include another file (usually a library) use  "--@include" command:

Example:

--@include library.js.sql
--@include library.js
<%
   selectEmployees();
   response.writeObject("testFunction",testFunction());
%>

 

Where library.js.sql :

--% function selectEmployees(){
SELECT *
FROM EMPLOYEES
--% }

And library.js:

function testFunction(){
    return "testFunction";
}

To include a file that is located in a different folder branch, use 

--@include ../../common/libs/library.js.sql

 

Static includes can also be used to include JavaScript files/functions from the webapp folder. This allows to be able to execute some code (if appropriate) between the server and the client. For example : risk calculation preview in-browser and the same calculation during update on the server-side within a transaction, in secure environment.

 

 

7. Stored Procedure Calls

 

7.1. MySQL Example : 

CALL TEST_SIMPLE_SP(

    :(inout)(date)p.date ,

    :(inout)(number)p.number ,

    :p.name ,

    :(out)p.helloString

)

7.2. Oracle Example with complex data types

/WEB-INF/lightlink/oracle/testTypes.js.sql

{

  call TEST_TYPES(

   :(oStructArr.PERSON_ARRAY)p.personsIn ,
   :(out)(oStructArr.PERSON_ARRAY)personsOut ,
   :(oArr.MY_INT_ARRAY)p.inArr ,
   :(out)(oArr.MY_INT_ARRAY)outArr
    )
}

 

JavaScript API : 

oracle.testTypes({

        personsIn:[

            { name: "John Smith", email: "john @ smith.com" },

            { name: "James Bond", email: "james @ bond.com" }

        ],

        inArr:[1,2,3,4,5]},
        function(res){

            console.log(res.personsOut[0].name, res.outArr.length)

        }

); 

Where PERSON_ARRAY and MY_INT_ARRAY are defined as following:

CREATE TYPE MY_INT_ARRAY IS VARRAY(100) OF INTEGER; 

 

CREATE TYPE PERSON AS OBJECT  (
    name    VARCHAR(30),
    email   VARCHAR(30)
); 
CREATE TYPE PERSON_ARRAY AS TABLE OF PERSON; 

And the procedure TEST_TYPES is :

CREATE OR REPLACE PROCEDURE "TEST_TYPES" (
  personsIn IN PERSON_ARRAY,
  personsOut OUT PERSON_ARRAY,
  intArrayIn IN MY_INT_ARRAY,
  intArrayOut IN MY_INT_ARRAY
)
 IS
BEGIN
  personsOut := personsIn ;
  intArrayOut := intArrayIn ;
END;