Using a simple templating language, you can apply data from GET query string parameters or POST payloads to queries.

SQL templates leverage Mustache templating language. This template language support iterators, basic conditionals, and more.

All templates are saved on the file system in the "extras/files/environments/default/queries" folder. The name of the template file also represents an API endpoint name.

Retrieving Data via JDBC
Endpoint
GET /applicants?middlename=micheal

SQL Template in Dashboard 

select * 
from applicants 
where "MiddleName" = '{{row.middlename}}' order by id

The default distribution of the Lingk API Plugin for Apache Nifi contains JDBC drivers for Postgres, Oracle, and Microsoft SQL Server. 

Writing data AND getting a response via JDBC
Endpoint
POST /applicants

SQL Template in Dashboard

INSERT INTO erpdata (id, payloadId, ordertype, orderid)
VALUES (
          floor(random() * 100000 + 1)::int,
          '{{row.[u1.1:cxml-request].cXML.payloadID}}',
        '{{row.[u1.1:cxml-request].cXML.Request.OrderRequest.OrderRequestHeader.orderType}}',
        '{{row.[u1.1:cxml-request].cXML.Request.OrderRequest.OrderRequestHeader.orderID}}'
        )
 RETURNING *        

Writing data via a stored procedure
Endpoint
POST /applicants?jdbc=jdbcStoredProc

SQL Template in Dashboard

sp.InsertPerson('{{row.middlename}}')

Retrieving Data via Java Jar file
Endpoint
GET /applicants?exec=java

JSON Template in Dashboard

{ "middleName","{{row.middlename}}" }

Writing Data or Retrieving via Oracle SQLcl
Endpoint
POST /applicants?exec=sqlcl
{ "firstName", "Test" }

SQL Script in Dashboard
Complex Query Example (for PL/SQL) that returns JSON
Oracle SQLcl is recommended because it can output JSON directly.

set serveroutput on size 30000;
SET SQLFORMAT JSON
SET TERM OFF

variable usrData VARCHAR2;


DECLARE
 dbUserCursor SYS_REFCURSOR;
 usrXmlType XMLTYPE;


BEGIN
 
 procCursorExample('{{row.firstname}}',dbUserCursor);
 usrXmlType := XMLTYPE(dbUserCursor);
 :usrData := usrXmlType.getClobVal();


--  dbms_output.put_line('--------------');
--  dbms_output.put_line(usrXmlType.getClobVal());
--  dbms_output.put_line('--------------');


END;

/

-- print usrData
-- SPOOL /home/test.json


SELECT i.*
from xmltable(
    '/ROWSET/ROW'
    passing xmltype( :usrData )
    columns
      personId number(4) path 'PERSONID',
      firstName varchar2(50) path 'FIRSTNAME'
   ) i;


-- SPOOL OFF
SET TERM ON
/
exit

Writing or Retrieving Data via Microsoft SQL CMD
Endpoint
POST /applicants?exec=sqlcmd
{ "firstName", "Test" }

SQL Script in Dashboard
Microsoft SQL Server SQL script

JSON templates for Colleague integration using NifiConnect are also supported.