Basics

Contents[Hide]

1. Parameters binding

Input parameters received from the client are represented by “p” variable.

1.1. Simple binding

Parameters binding is done with “:<variableName>/<expression>” An <expression> may only contain numbers, letters, “_” , “.”, “[”, “]”, “(”, ”)”, ”-” caracters. If it’s not enough, use scriptlets to introduce a local variable.

Select * from countries where continent = :p.continent

or

Select c.* from clients c, adress a where c.adress_id=a.id and a.country= :p.adress.country

JavaScript API : 

MyApp.getCountries({ address:{country:”USA”} },function(res){console.log(res.resultSet);})

 

1.2. Bind casting

The default input binding type is String. In order to explicitly indicate a different type of binding, use bind casting.

This is useful if a number or a date input parameter is passed as a string. This is a common situation as all HTML form elements values are always strings on the browser side.

Build-in casings are:

  • (number) – to perform a BigDecimal parsing
  • (date) – see later for types.setCustomDatePattern()method allowing date format customization
  • (blob) – BLOB represented by a byte array. Rarely used because of its verbosity, blob.base64 is more efficient.
  • (blob.base64) – BLOB represented as base64 encoded String
  • (blob. UTF-8) – BLOB represented as UTF-8 encoded String
  • (array) - Native DB Array type. Uses generic JDBC API to suport DB Array types. Example : PostgreSQL's array types
  • (json) –  JSON data type. Mapped to a native JSON type if supported by RDBMS (PostgreSQL, Oracle,.. ) otherwise can be mapped to a BLOB, TEXT or VARCHAR. 
  • (oArr)             – Oracle ARRAY of primitives, VARCHAR2 ou NUMBER
  • (oStruct)         – Oracle STRUCT
  • (oStructArr)   – Oracle ARRAY of STRUCT

 

1.2.1. Example:

INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
    VALUES(100, :(date)p.from , :(date)p.to , :(number)p.jobId, :(number)p.deptId)

 

1.3. Output casting

Output casing is rarely needed, but it allows adapting a value in resultSet or SP output parameters to a desired form.

Numbers and Dates types are handled automatically, no casting is needed. Most frequently you’ll need  to cast output Blobs :

SELECT blobField as '(blob.base64)blobField'FROM blobclob

or

SELECT blobField as '(blob.UTF-8)asString', clobField FROM blobclob

 

1.4. Stored procedures casting

It allows to indicate the IN/OUT/INOUT parameters :

CALL TEST_SIMPLE_SP(
    :(inout)(date)p.date ,
    :(inout)(number)p.number ,
    :p.name , -- default IN parameter of String type
    :(out)p.helloString
)

Multiple casings can be applied as a series of independent casting :(cast1)(cast2)variable

Output parameters are returned by the names of binding variables. If the binding starts with “p.”, those 2 characters are removed. So if we consider the previous example, its resulting object will be {helloString:” Hello LightLink”,number:123}

 

2. Scriptlets

There are 2 ways to define scriptletswithing SQL/JavaScript template:

  • Single line scriptlet
  • Block scriptlet

2.1. Single line

In single line scriptlet, each line starts with “--%”  and the remaining code is considered as JavaScript

A great advantage of single line scriptlets is that any SQL client software will see it as a comment and ignore such lines. It allows more flexibility during development.

WEB-INF/classes/Myapp/findCountriesBySubstring.js.sql

--% var likeExpr = ‘%’+p.query+’%’

Select * from countries where continent like :likeExpr

JavaScript API : 

MyApp.getCountries({query:”Cana”},function(res){console.log(res.resultSet);})

See: Querying data for more SP calls example (passinsg Oracle complex types)

2.2. Block scriptlet

Similarly to JSP, block scriptlets are delimited by <% and %>

Example :

<%
    for (vari=0;i<p.dates.length;i++){
%>
  INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
    VALUES(100, :(date)p.dates[i].from , :(date)p.dates[i].to , :(number)p.dates[i].jobId, :(number)p.dates[i].deptId)
<%
        sql.addBatch();
    }
sql.query();
%>

Methods sql.addBatch(); and sql.query(); commands will be covered in further chapters. They use JDBC Batch feature, to send a (big) number of statements as a single network call. Extremely usefull as a fast way to insert multiple (hundreds/thowsands of) lines.

2.3. Inline scriptlet

An expression can also be used as a part of SQL statement without binding:

Example:

--% if (! p.columns .match(/^[a-z0-9_,\s]*$/i))
--%        throw "Illegal 'columns' value"+p.columns;
SELECT <%=p.columns%> FROM MY_TABLE

 

Inline scriptlets warning: Without a proper check of allowed character set inlins scriptlets can leat to SQL Injection vulnerability. Use with caution. If values come from user input, always restrict allowed caracter.

 

2.4. Binding consistency

Any binding’s value is caught at a moment of bind execution.  It’s safe to change the variable that was bind after the execution. 

Example  :

<%
    function insertValue(no){
%>
                INSERT INTO NUMBERS (VALUE) values( :no );
<%   
        sql.addBatch();
    }
    for (var i=0;i<10;i++){
          insertValue(i);
    }

    sql.query();
%>