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;