Sql

Generic SQL execution (NO SQL queries)

Syntax

var rows = utils.executeSql(sql, dataSourceId, separatedTransaction, interruptExecution, params)

Details

An :XXX variable can be replaced by vo or params values

Example

var updatedRowsNr = utils.executeSql(
"UPDATE PRM01_USERS SET USER_CODE_ID=:USER_CODE_ID, DESCRIPTION=:USER_CODE_ID WHERE COMPANY_ID= :COMPANY_ID AND STATUS=? AND LOCKED=?",
null, // no additional datastore!
false, // do it on a separated transaction
true, // fire an Exception in case of error
["E", "N"]
);

Note: every SQL instruction will be logged.

Generic SQL execution (NO SQL queries) without logging it

Syntax

var rows = utils.executeSqlNoLog(
sql,
dataSourceId,
separatedTransaction,
interruptExecution,
params
)

Details

An :XXX variable can be replaced by vo or params values

Example

var updatedRowsNr = utils.executeSql(
"UPDATE PRM01_USERS SET USER_CODE_ID=:USER_CODE_ID, DESCRIPTION=:USER_CODE_ID WHERE COMPANY_ID= :COMPANY_ID AND STATUS=? AND LOCKED=?",
null, // no additional datastore!
false, // do it on a separated transaction
true, // fire an Execution in case of error
"E",
"N"
);

Note: the SQL operation will not be logged. This method csan be useful with bulk operations, whose execution could slow down if a log message were produced for each execution.

SQL query execution

Syntax

var jsonString = utils.executeQuery(
  sql,
  dataSourceId,
  separatedTransaction,
  interruptExecution,
  params
)

Details

An :XXX variable can be replaced by vo or params values

Example

var jsonString = utils.executeQuery(
  "SELECT USER_CODE_ID,DESCRIPTION FROM PRM01_USERS WHERE COMPANY_ID=:COMPANY_ID AND STATUS=? AND LOCKED=?",
  null, // no additional datastore!
  false, // do it on a separated transaction
  true, // fire an Execution in case of error
  "E",
  "N"
);
var rows = JSON.parse(jsonString);
for(var i=0;i<rows.length;i++) {  
  var username = rows[i].userCodeId;
  ...
}

SQL query execution with very long result sets

From 5.4.0 version

In case of a SQL query returning a very long result set, it is NOT recommended to use the previous method, since it gives back the whole result set at once, which is dangerous because it can consume a large amount of memory on the server.

A better solution is represented by the following method, where the result set is read row by row: for each row, a callback function is invoked, in order to process it.

Consequently, this method cannot be coupled to the user interface, since it would mean that all data would be read in the end. This approach is good when the row processing does not involve the UI, but some other operation on the server side, like writing a text file, starting from the result set.

Syntax

utils.executeQueryWithCallback(
  callbackFunName,
  sql,
  dataStoreId,
  separatedTransaction,
  interruptExecution,
  params
)

Details

An :XXX variable can be replaced by vo or params values

Example

var processRow = function(record) {
  utils.log(record.userCodeId+" "+record.description,"INFO");
}

utils.executeQueryWithCallback(
"processRow",
"SELECT USER_CODE_ID,DESCRIPTION FROM PRM01_USERS WHERE COMPANY_ID=:COMPANY_ID AND STATUS=? AND LOCKED=?",
null, // no additional datastore!
false, // do it on a separated transaction
true, // fire an Execution in case of error
"E",
"N"
);

A more complex example is the one reported as follows, where the SQL query result set is coupled with the export of records to a CSV file on the server file system. The CSV file is built by writing row by row, using an optimized approach based on 3 steps: opening the output stream, writing multiple lines, closing the output stream:

Example of an optimized approach for reading data from a SQL query + writing data on a CSV file

// open the CSV file, in order to write into it
var fileId = utils.openCSVFile(
    "ab.csv",
    true,
    9, // data source id
    ",",
    true,
    ["userCodeId","password","rowVersion","dateExpirationPassword"], // attributes coming from a row, to manage and export
    [null,null,null,null] // optional data converters (es. from number to text: "0.00")
);

// declare a callback function, invoked by a SQL query, used to write a single line into the CSV file
var processRow = function(jsonRow) {
    utils.writeToCSVFile(fileId,jsonRow);
}

// execute the SQL query, whose result set will be read row by row, by invoking each time the specified callback
utils.executeQueryWithCallback(
    "processRow", // callback function, invoked for each record coming from the query, whose argument will receive a JSON object representing the record
    "SELECT U.USER_CODE_ID,U.DESCRIPTION,U.PASSWORD,U.DATE_EXPIRATION_PASSWORD,U.ROW_VERSION FROM PRM01_USERS U",
    null,
    false,
    true,
    []
);

// close the CSV file, after reading all records from the query
utils.closeCSVFile(fileId);

Execute the specified SQL query and enrich it by adding filtering/sorting and pagination settings

These settings can be defined through ListCommand object automatically created when this method is invoked through a "Server JS business component" connected to a grid panel.

Syntax

var json = utils.getPartialResult(sql, dataStoreId, separatedTransaction, interruptExecution, pars);

Details

 Returns a list of records, expressed in JSON format, which also includes "valueObjectList", "moreRows" and "resultSetLength" attributes.

In case of a complex SQL query including aliases, it could be needed to decode the field (to filter/sort) from the grid panel into an alias. You can do it through a specific utility method:setDecodeField(fieldToDecode, decodedField).

In this way, you can control exactly which fields to apply in case of filtering or sorting conditions.

Example

utils.setDecodeField("ROW_VERSION*ROW_VERSION AS RV","RV");
var json = utils.getPartialResult(
    "SELECT USER_CODE_ID,DESCRIPTION,ROW_VERSION*ROW_VERSION AS RV "+
    "FROM PRM01_USERS "+
    "WHERE STATUS='E'",
    null,
    false,
    true,
    []
);

utils.setReturnValue(json);

Stored SQL function execution

Syntax

var json = utils.executeStoredFunction( sql, dataSourceId, separatedTransaction, interruptExecution, params)

Details

 use with the sql statement

Example

var returnedValue = utils.executeStoredFunction("usercheck1(?, ?)",....

Progressive

Syntax

var value = utils.getProgressive(String tableName, String columnName,Boolean separatedTransaction, Boolean interruptExecution)

Details

Counter

Syntax

var value = utils.getCount(String tableName, String valueColumnName, String incrementValue, String where, Boolean separatedTransaction, Boolean interruptExecution, Long dataSourceId)

Details

Get the current date

helpful when executing a SQL query and a bind variable should be filled out by a dynamic value which is the current date.

Syntax

var currentDate = utils.getCurrentDate();

Execute synchronously a server-side JS action

A new SQL transaction is created for this action.

Syntax

var json = utils.executeAction(Long actionId,Map vo,Map params,Map headers);

Details

Execute synchronously a server-side JS action on the same transaction

The same SQL transaction is reused for this action. This method is recommended in case of database locks when executing the action: a lock usually means that the logic executed here works on the same data managed by the calling action and both are trying to write the same records. In such cases, it is not correct to execute a second action on another transaction, so this second method is the right one to use.

Bear in mind that errors (javascript exceptions) fired during the execution of this action will be affect the main action too, since it is the same transaction. If you do not want that data written by the main action will not be rollbacked, use try-catch keywords to surround the executeActionSameTransaction instruction.

Syntax

var json = utils.executeActionSameTransaction(Long actionId,Map vo,Map params,Map headers);

Details

How to get the field names and types for every fields in the select clause of a SQL query to execute

This can be helpful when creating programmatically a grid and there is the need to know how to format data.

Syntax

var jsonList = utils.getQueryColumns(sql, dataStoreId, separatedTransaction, interruptExecution, pars);

Details

Execute a SQL insert instruction, starting from a javascript object, instead of defining explicitelly the SQL script.

Syntax

var ok = utils.insertObject(obj, tableName, dataSourceId, separatedTransaction, interruptExecution);

Details

Execute a SQL update instruction, starting from a javascript object, instead of defining explicitelly the SQL script

Syntax

var ok = utils.updateObject(obj, emptyAsNull, forceAttributesToNull, tableName, dataSourceId, separatedTransaction, interruptExecution);

Details

Execute a SQL delete instruction, starting from a javascript object, instead of defining explicitelly the SQL script

Syntax

var ok = utils.deleteObject(obj, tableName, dataSourceId, separatedTransaction, interruptExecution);

Details

How to extract a binary object from a BLOB field

In case you need to extract the binary content from a BLOB data field of a relational database, you can use the following server-side javascript function, which allows you to get its content and convert it to a Base64 String.

This String value can be easily passed forward to the web UI or to a Jasper Report template, for example as a JSON attribute and later refer it within the report and map it to an Image.

Syntax:

var base64String = utils.getBlobAsBase64(
   datastoreId,
   sql,
   params
);

This method returns a String whose content is the Base64 representation of the BLOB field content.

Call a business component from a server-side js business component

It is available a js function to invoke from within a server-side js b.c. (not from an action) to call another business component.

Basically, it can be used to pass forward all request parameters received in input from the starting b.c and get a response to use as the final result to pass back.

Syntax

callBusinessComponent(
  Long compId,
  Map additionalReqParams,
  Map decodedReqParams,
  Map decodedFilterNames
)

Example of a server-side js b.c. invoking a b.c. for grids

var json = utils.callBusinessComponent(109,{},{},{
     codice: "uid", // replace "codice" input req param with "uid"
     descrizione: "companyTitle"
});
var res = JSON.parse(json);
// scroll all results returned by the called b.c. and create a new 
// response, containing the data needed
var list = [];
for(var i=0;i<res.valueObjectList.length;i++) {
    list.push({
        codice: res.valueObjectList[i].uid,
        descrizione: res.valueObjectList[i].companyTitle
    });
}

var _res = {
    valueObjectList: list,
    moreRows: res.moreRows
};
utils.setReturnValue(JSON.stringify(_res));

Example of a server-side js b.c. invoking a b.c. for a form

var params = new Object();
params["cliente.uid"] = "00109801-de92-4e5e-85a4-108aa6b63387"; // for example
var json = utils.callBusinessComponent(119,params,{},{}); // here the required "pk" for the b.c. for a form is passed forward through "params"
...

Execute bulk insert/update from a SQL query

Use this method when you need to copy data from one table to another, having the same structure (in terms of field types and mandatory constraints), for example from one SQL schema to another.

This is the fastest method to execute inserts or updates (or both), it can save up to 300.000 records in 30 seconds.

Syntax

var processedRows = utils.executeSqlBatch(Map settings);

Details

The javascript object "settings" contains a few attributes:

selectSql: a mandatory String type attribute, containing the SQL query to execute in the source schema; this SQL query can contain aliases and bind variables
selectParameters: an optional java.util.ArrayList containing the values for the bind variables specified in the SQL query
srcDataStoreId: an optional numeric type attribute, identifying the source database schema by its datasource id
insertSql: this String type attribute defines the INSERT statement to execute, expressed with biding variables and in-line values (e.g. NOW(), 'INSERT_USER', 1, etc.); for each field reported in the SQL query, there must be a corresponding bind variable for this INSERT statement and exactly in the same position; you are free to include additional fields in the INSERT clause, whose values do not coming from the SQL query, but they must be filled with in-line values
updateSql: this String type attribute defines the UPDATE statement to execute, expressed with biding variables and in-line values (e.g. NOW(), 'UPDATE_USER', etc.); for each field reported in the SQL query, there must be a corresponding bind variable for this UPDATE statement and exactly in the same position; you are free to include additional fields in the INSERT clause, whose values do not coming from the SQL query, but they must be filled with in-line values. The UPDATE statement ends with the WHERE clause which must be defined with bind variables and must always refer a single record to update. When defining the "updateSql" attribute, you have also to specify the "pk" attribute
pk: this java.util.ArrayList type attribute must be specified as long as you have defined the "updateSql" attribute and it reports the name of the fields in the SELECT clause which are used to fill in the binding variables of the WHERE clause for the UPDATE statement
blockSize: optional numeric type attribute; it defines the amount of records to enqueue before executing all of them and commit such block; if not specified, the default value is 10.000 records
destDataStoreId: an optional numeric type attribute, identifying the destination database schema by its datasource id

Important note: when appending writing operations, these are collected through the JDBC driver in use; it is up to the driver to either pass appended data to the database server or accumulate it on the client side; in the latter case, the memory consumption can increase significantly on the application server and a memory heap error could happen because of that. In order to prevent such a critical error, it is a good practice to limit the amount of collected data to a lower value, so it is better to set the blockSize property to a relatively lower value, like 100 o 500 and pay attention to the memory consumption: do not increase it if you note a potentially critical amount of memory consumed (e.g. hundreds of MB).

Executes once the specified SQL query and for each fetched record:

if updateSql has been specified:
- execute massively N updates, where N is 10k (or blockSize if specified)
- execute a commit
- if there are 0 records updated, only for those ones, execute the insertSql (if specified)
- continue until the end of the result set
otherwise, the insertSql must be specified, therefore:
- execute massively N inserts, where N is 10k (or blockSize if specified)
- execute a commit
- continue until the end of the result set

Insert only example

var selectParameters = new java.util.ArrayList();
// selectParameters.add(...);

utils.executeSqlBatch({
    selectSql: "SELECT ID_CLIENTE, ETA, SESSO, PROV, TOTALE, CHECK_ACQ, PREDIZIONE, USER_CODE_ID, LAST_UPDATE FROM CLIENTI_BOGGI",
    selectParameters: selectParameters,
    srcDataStoreId: null,
    destDataStoreId: 319,
    insertSql: "INSERT INTO CLIENTI(ID_CLIENTE, ETA, SESSO, PROV, TOTAL, CHECK_ACQ, PREDIZIONE, USER_CODE_ID, LAST_UPDATE) VALUES(?,?,?,?,?,?,?,?,?)"
});

Note that the select fields MUST be in the same order of ? variables in the insertSql statement

Update only example

var selectParameters = new java.util.ArrayList();
// selectParameters.add(...);

var pk = new java.util.ArrayList();
pk.add("ID_CLIENTE");

utils.executeSqlBatch({
    selectSql: "SELECT ID_CLIENTE, ETA, SESSO, PROV, TOTALE, CHECK_ACQ, PREDIZIONE, USER_CODE_ID, LAST_UPDATE FROM CLIENTI_BOGGI",
    selectParameters: selectParameters,
    srcDataStoreId: null,
    destDataStoreId: 319,
    updateSql: "UPDATE CLIENTI set ID_CLIENTE=?, ETA=?, SESSO=?, PROV=?, TOTAL=?, CHECK_ACQ=?, PREDIZIONE=?, USER_CODE_ID=?, LAST_UPDATE=? WHERE ID_CLIENTE=?",
    pk: pk
});

Note that select fields MUST be in the same order of ? variables in the updateSql statement (there must be as many SET clauses as the number of fields in the select clause)

Note that the "pk" attribute is mandatory in case of "updateSql" and it must contains the field names (or alias) of the select clause field.

Update + Insert example

var selectParameters = new java.util.ArrayList();
// selectParameters.add(...);

var pk = new java.util.ArrayList();
pk.add("ID_CLIENTE");

utils.executeSqlBatch({
    selectSql: "SELECT ID_CLIENTE, ETA, SESSO, PROV, TOTALE, CHECK_ACQ, PREDIZIONE, USER_CODE_ID, LAST_UPDATE FROM CLIENTI_BOGGI",
    selectParameters: selectParameters,
    srcDataStoreId: null,
    destDataStoreId: 319,
    insertSql: "INSERT INTO CLIENTI(ID_CLIENTE, ETA, SESSO, PROV, TOTAL, CHECK_ACQ, PREDIZIONE, USER_CODE_ID, LAST_UPDATE) VALUES(?,?,?,?,?,?,?,?,?)",
    updateSql: "UPDATE CLIENTI set ID_CLIENTE=?, ETA=?, SESSO=?, PROV=?, TOTAL=?, CHECK_ACQ=?, PREDIZIONE=?, USER_CODE_ID=?, LAST_UPDATE=? WHERE ID_CLIENTE=?",
    pk: pk
});

In this example, a block of 10.000 records are updated; for those records not updated (because they do not exist yet), a bulk insert is performed; in any case, the commit is execute at the end of this block. Then process the next 10.000 records and so on.

Cached progressive calculation

In case you have a progressives table used to calculate a progressive and it is invoked a very high amount of times concurrently, it is likely to have locks or a low performance.

This method can be used to increase the progressives table a lower number of times, since the update is performed on the table rarely, since the writing operation would increase the current value not by 1, but by an "increment" value.

Every time this method is invoked, a new increment is returned without any update operation, since the current value is cached and incremented programatically in the cache, until the max "increment" value is reach: only at that point the update operation is called again.

The operation of Insert a new record in progressives table is also supported.

Example:

progressives table already contains the record with current value 1
the method is invoked for the first time with increment value = 1000
the method updates the record with 1+1000 = 1001 and returns 2; cached incremented value is now 2
the method is invoked again and the cached current value is incremented to 3; 3 is returned

...

the method is invoked for the 1000th time: cached current value is 1001: no more increments can be done;
the record is updated to 1001+1000 = 2001 and returns 1002; cached incremented value is now 1002

Arguments: settings - a javascript object, containing a series of attributes, all mandatory

Example:

     var settings = {
            datasourceId: null,
            tableName: "INI21_GLOBAL_SEQUENCES",
            pkFields: ["TABLE_NAME","FIELD_NAME"],
            pkValues: ["AAAA","PROG_ID"],
            updateFields: ["USER_ID_UPDATE","LAST_UPDATE"],
            updateValues: [userInfo.username,utils.getCurrentDateAndTime()],
            currentValueField: "CURRENT_VALUE",
            incrementValue: 2,
            initialValue: utils.getInitialValue(),
            insertFields: ["INITIAL_VALUE","INCREMENT_VALUE","IS_CYCLE","MAX_NUMBER","USER_ID_CREATE","CREATE_DATE","ROW_VERSION","STATUS"],
            insertValues: [utils.getInitialValue(),2,"F",null,userInfo.username,utils.getCurrentDateAndTime(),0,"E"]
      };
      var p = utils.getCachedProgressive(settings);

Reading filter parameters coming from the browser

In case you are defining a business component filling a grid and the grid is sending filter conditions to the b.c., these are automatically applied to the "base query" defined within the b.c. through the utils.getPartialResultXXX method.

However, there are cases where you want to apply these conditions in a specific part of the SQL query.

Example:

SELECT ALIAS.A,ALIAS.B,ALIAS.C,ALIAS.D FROM (
  SELECT TMP.A,TMP.B,TMP.C,TMP.D FROM MYTABLE TMP WHERE TMP.C=?
) AS ALIAS 
WHERE
ALIAS.D = ?

In such a case, it is likely to have a mapping between filter conditions (e.g. applied on attributes a or b) and the field names defined for the ALIAS table name:

utils.setDecodedField("A","ALIAS.A");
utils.setDecodedField("B","ALIAS.B");
utils.setDecodedField("C","ALIAS.C");
utils.setDecodedField("D","ALIAS.D");

Now suppose you want to apply these conditions to the internal table. You can use the utility functions:

var subFilters = utils.getAllFilterWhere(table);
var pars = getAllFilterParams();

The first function would analyze all request parameters and create a "subfilters" String containing the filtering conditions in SQL format, whereas the second function would fill in the "pars" array with the corresponding values.

Note: only filter conditions defined using setDecodedField will be taken into account.

Example:

http://...?filterNames=a,b&filterOps=like,=&filterValues=AAA,BBB&filterCaseSensitives=true,false

would lead to fill in subFilters and pars with:

subFilters = utils.getAllFilterWhere("TMP"); // " UPPER(TMP.A) like ? AND TMP.B = ?
pars = getAllFilterParams(); // ["AAA","BBB"]

At this point, it would be easy to inject the dynamic subfilter conditions to the main SQL query:

var sql = 
"SELECT ALIAS.A,ALIAS.B,ALIAS.C,ALIAS.D FROM ( "+
"  SELECT TMP.A,TMP.B,TMP.C,TMP.D FROM MYTABLE TMP WHERE TMP.C=? ";

var params = [ valueForC ];  

var subFilters = utils.getAllFilterWhere("TMP"); // " UPPER(TMP.A) like ? AND TMP.B = ?
var pars = getAllFilterParams(); // ["AAA","BBB"] 

if (pars.length>0) {
  sql += " AND "+subFilters;
  for(var i=0;i<pars.length;i++) {
    params.push(pars[i]);
  }
}

sql += 
") AS ALIAS "+
"WHERE "+
"ALIAS.D = ? ";

params.push( valueForD );

var json = utils.getPartialResultXXX(
  sql,
  ...
  params
);

PreviousReporting NextXML

Last updated 1 year ago