Documentation

Server side JS action: executing a query

In order to execute a SQL query from within a server side JS action, the following snippet can be used:

var query = "SELECT * from TABLE where site_id = ? and company_id = ?";
 
var rows = utils.executeQuery(
  query,
  null, /* additional datastore ID, null for main Database */
  false, /* do it on a separated transaction */
  true, /* fire an Execution in case of error */
  
  siteId,
  companyId /* Parameters list separated by commas */
);
rows = JSON.parse(rows);
NOTE:  The second parameter can be number representing the additional datastore ID or null to execute the query on the main DB.
Remember to use camelCase to access fields data, so the field called COMPANY_ID in the database can be accessed as rows.companyID after the query execution.
 
There are some default variables available in JS Server actions:
  • COMPANY_ID: company id usata dall’applicazione e scelta in fase di login
  • SITE_ID: site id scelta in fase di login
  • USERNAME: user name dell’utente loggato
Also remember: To execute SELECT queries there’s the executeQuery method, but if UPDATE or DELETE must be performed, then use executeSQL
 
var insertSQL="INSERT INTO TABELLA (CAMPO1,CAMPO2) VALUES (?, ?)";

/* Same parameters as above, just the function name is different */
rows = utils.executeSql(
  insertSQL,
  null,
  false,
  true,
  "1",
  "2"
);