publish
plugins.it2be_data info


Servoy Properties Summary
String build
Date buildDate
String component
String licensee
String typeLicensed
Number version
String versionLicensed


Servoy Methods Summary
IDCsv createCsv( )
IDDbf createDbf( )
IDExcel createExcel( )
IDCalendar createICalendar( )
IDQbContact createQbContact( )
IDTxt createTxt( )
IDXml createXml( )
void filterDataSet( JSDataSet, Number, Object, Boolean )
void filterDataSet( JSDataSet, Number, Object )
Array<Object> getFilteredColumnAsArray( JSDataSet, Number )
JSDataSet getFilteredDataSet( JSDataSet, Number, Object, Boolean )
JSDataSet getFilteredDataSet( JSDataSet, Number, Object )
Array<JSDataSet> getFilteredDataSets( JSDataSet, Number )
Boolean query( String, String, Array<Object> )
Boolean query( String, String )
Boolean register( String )


Servoy Properties Details


build
String  build
Returns the current version (major, minor, build)
Returns
String  build String

Supported Clients
SmartClient, WebClient, NGClient


buildDate
Date  buildDate
Returns date release date
Returns
Date  date Date

Supported Clients
SmartClient, WebClient, NGClient


component
String  component
Returns the component name
Returns
String  name String

Supported Clients
SmartClient, WebClient, NGClient


licensee
String  licensee
Returns the licensee
Returns
String  licensee String

Supported Clients
SmartClient, WebClient, NGClient


typeLicensed
String  typeLicensed
Returns the type that is valid for this license
Returns
String  type String

Supported Clients
SmartClient, WebClient, NGClient


version
Number  version
Returns the current version (major, minor)
Returns
Number  version Double

Supported Clients
SmartClient, WebClient, NGClient


versionLicensed
String  versionLicensed
Returns the version that is valid for this license
Returns
String  license String

Supported Clients
SmartClient, WebClient, NGClient

Servoy Methods Details


createCsv
IDCsv  createCsv( )
Create a CSV Object
Returns
IDCsv  csv IDCsv

Supported Clients
SmartClient, WebClient, NGClient


createDbf
IDDbf  createDbf( )
Create a DBF Object
Returns
IDDbf  dbf IDDbf

Supported Clients
SmartClient, WebClient, NGClient


createExcel
IDExcel  createExcel( )
Create an Excel Object
Returns
IDExcel  excel IDExcel

Supported Clients
SmartClient, WebClient, NGClient


createICalendar
IDCalendar  createICalendar( )
Create an iCalendar Object
Returns
IDCalendar  calendar IDCalendar

Supported Clients
SmartClient, WebClient, NGClient


createQbContact
IDQbContact  createQbContact( )
Create a QB Contact Object
Returns
IDQbContact  contact IDQbContact

Supported Clients
SmartClient, WebClient, NGClient


createTxt
IDTxt  createTxt( )
Create a TXT Object
Returns
IDTxt  txt IDTxt

Supported Clients
SmartClient, WebClient, NGClient


createXml
IDXml  createXml( )
Create a XML Object
Returns
IDXml  xml IDXml

Supported Clients
SmartClient, WebClient, NGClient


filterDataSet
void  filterDataSet( JSDataSet, Number, Object, Boolean )
Exclude or include rows at the given column and value of the CURRENT BEDataSet.
Parameters
JSDataSet  dataset  the dataset
Number  column  the column
Object  filter  the filter
Boolean  include  the include

Returns
void  

Supported Clients
SmartClient, WebClient, NGClient

Sample
// Exclude or include rows at the given column and value of the JSDataSet.
// set the name of the file
var filename = plugins.file.showFileOpenDialog();

// check that we really selected a file
// if not ignore the rest of the code
if (filename) {
//create a dataset from the file
//the showHeader parameter is set to false by default
//REMARK: a header will always be constructed from the first row
var set = plugins.it2be_data.csv().getDataSet(filename);

//filter the CURRENT set at column # 2 (we don't create a new dataset here)
//a boolean value true will filter out all values other than the given value
//a boolean value false will filter out the given value
plugins.it2be_data.filterDataSet(set, 2,"Harrison", true);

//when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";

//output to the debugger to show there is a result
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());

//because the set is a 'standard' JSDataSet all the functions and properties
//we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();

//limit the number of columns we show to 5 when columncount > 5
//5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;

//loop through the columns
for (var i = 1 ; i <= columncount ; i++) {
//retrieve the names
message += set.getColumnName(i) + "::";
}
message += "\n";

var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();

//loop through the rows of the JSDataSet
//REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1 ; i <= rowcount ; i++) {
//loop through the columns
for (var ii = 1 ; ii <= columncount ; ii++) {
//and retrieve the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}

//show the result
if (message) {
plugins.dialogs.showInfoDialog("JSDataSet", message);
}
} else {
//output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
}


filterDataSet
void  filterDataSet( JSDataSet, Number, Object )
Exclude or include rows at the given column and value of the JSDataSet.
Parameters
JSDataSet  dataset  the dataset
Number  column  the column
Object  filter  the filter

Returns
void  

Supported Clients
SmartClient, WebClient, NGClient

Sample
// Exclude or include rows at the given column and value of the JSDataSet.
// set the name of the file
var filename = plugins.file.showFileOpenDialog();

// check that we really selected a file
// if not ignore the rest of the code
if (filename) {
//create a dataset from the file
//the showHeader parameter is set to false by default
//REMARK: a header will always be constructed from the first row
var set = plugins.it2be_data.csv().getDataSet(filename);

//filter the CURRENT set at column # 2 (we don't create a new dataset here)
//a boolean value true will filter out all values other than the given value
//a boolean value false will filter out the given value
plugins.it2be_data.filterDataSet(set, 2,"Harrison", true);

//when the dataset is NOT empty
if (set.getMaxRowIndex()) {
var message = "";

//output to the debugger to show there is a result
application.output("set.getMaxRowIndex() = " + set.getMaxRowIndex());

//because the set is a 'standard' JSDataSet all the functions and properties
//we can use the Database Manager to retrieve the column count and column names
var columncount = set.getMaxColumnIndex();

//limit the number of columns we show to 5 when columncount > 5
//5 is an arbitrary value to limit the width of the dialog
columncount = (columncount > 5) ? 5 : columncount;

//loop through the columns
for (var i = 1 ; i <= columncount ; i++) {
//retrieve the names
message += set.getColumnName(i) + "::";
}
message += "\n";

var rowcount = (set.getMaxRowIndex() > 5) ? 5 : set.getMaxRowIndex();

//loop through the rows of the JSDataSet
//REMARK: the JSDataSet starts counting at 1 instead of 0
for (var i = 1 ; i <= rowcount ; i++) {
//loop through the columns
for (var ii = 1 ; ii <= columncount ; ii++) {
//and retrieve the column values
message += set.getValue(i, ii) + "::";
}
message += "\n";
}

//show the result
if (message) {
plugins.dialogs.showInfoDialog("JSDataSet", message);
}
} else {
//output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
}


getFilteredColumnAsArray
Array<Object>  getFilteredColumnAsArray( JSDataSet, Number )
Retrieve all distinct values in the given column.
Parameters
JSDataSet  dataset  the dataset
Number  column  the column

Returns
Array<Object>  columns Object[]

Supported Clients
SmartClient, WebClient, NGClient


getFilteredDataSet
JSDataSet  getFilteredDataSet( JSDataSet, Number, Object, Boolean )
Exclude or include rows at the given column and value and return a NEW JSDataSet.
Parameters
JSDataSet  dataset  the dataset
Number  column  the column
Object  filter  the filter
Boolean  include  the include

Returns
JSDataSet  dataset JSDataSet

Supported Clients
SmartClient, WebClient, NGClient

Sample
// Exclude or include rows at the given column and value and return a NEW JSDataSet.
// set the name of the file
var filename = plugins.file.showFileOpenDialog();

// check that we really selected a file
// if not ignore the rest of the code
if (filename) {
//create a dataset from the file
//the showHeader parameter is set to false by default
//REMARK: a header will always be constructed from the first row
var set = plugins.it2be_data.csv().getDataSet(filename);

//filter the current set at column # 2
//and return the column with all distinct values from that column
var values = plugins.it2be_data.getFilteredColumnAsArray(set, 2);

//filter the current set at column # 2
//and create an array of DataSets per distinct value found
var sets = plugins.it2be_data.getFilteredDataSets(set, 2);

//get the separator for the os
var osname = application.getOSName();
var separator = "/";

if (osname.indexOf("Windows") > -1) {
separator = "\";
}

//get the path to the folder where the selected file is located
var path = filename.getParentFile().getAbsolutePath() + separator;

//when the array of DataSets is NOT empty (length is not 0)
if (sets.length) {
//loop throught the DataSets
for (var i = 0 ; i < sets.length ; i++) {
//write a new .txt file with the value
//found in column 2 into the directory where we found the original file
plugins.it2be_data.txt().write(path + values[i], sets[i]);
}
} else {
//output to the debugger to show there is NO result
application.output("JSDataSet = null");
}
}


getFilteredDataSet
JSDataSet  getFilteredDataSet( JSDataSet, Number, Object )
Exclude or include rows at the given column and value and return a NEW JSDataSet.
Parameters
JSDataSet  dataset  the dataset
Number  column  the column
Object  filter  the filter

Returns
JSDataSet  dataset JSDataSet

Supported Clients
SmartClient, WebClient, NGClient


getFilteredDataSets
Array<JSDataSet>  getFilteredDataSets( JSDataSet, Number )
Returns an array of DataSets based on the given column and distinct values.
Parameters
JSDataSet  dataset  the dataset
Number  column  the column

Returns
Array<JSDataSet>  datasets JSDataSet[]

Supported Clients
SmartClient, WebClient, NGClient


query
Boolean  query( String, String, Array<Object> )
Create and execute RAW SQL code.
Parameters
String  server  the server
String  query  the query
Array<Object>  input  the input

Returns
Boolean  result boolean

Supported Clients
SmartClient, WebClient, NGClient

Sample
// create a query, this is plain sql but needs to be database specific
var query = "CREATE TABLE sql_test(sql_test_id INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,creation DATETIME,modification DATETIME);"

// execute the query and retrieve the (boolean) result
var result = plugins.it2be_data.query("crm", query);

// create a message based on the result
if (result) {
var message = "The table is alive and kicking!\nHave a look in your database management application";
} else {
var message = "Creation of the table failed!";
}

// and show the message in an info dialog
plugins.dialogs.showInfoDialog("DBTable creation", message);

// now let's assume you are in production and run the query against a database that is
// used by the solution
// plugins.it2be_data.flushClientsCache("crm", "sql_test");

// REMARK: it is possible to perform ANY query you want as long as there is a database server
// however I would only use this method to perform actions on databases not connected to
// your production solution to avoid critical mistakes.

// now let's create a record and use the input parameter
// to execute uncomment the below 5 lines of code
// var query = "INSERT INTO sql_test (creation, modification) VALUES (?, ?)";
// var input = [new Date(), new Date()];
// var result = plugins.it2be_data.query("crm", query, input);

// application.output(result);
// plugins.it2be_data.flushClientsCache("crm", "sql_test");

// it is also possible to use the query to drop a table (below) and perform any
// other action you need to take
// to execute uncomment the below 3 lines of code
// var query = "DROP TABLE sql_test";
// var result = plugins.it2be_data.query("crm", query);

// application.output(result);


query
Boolean  query( String, String )
Create and execute RAW SQL code.
Parameters
String  connection  the connection
String  query  the query

Returns
Boolean  result boolean

Supported Clients
SmartClient, WebClient, NGClient

Sample
// create a query, this is plain sql but needs to be database specific
var query = "CREATE TABLE sql_test(sql_test_id INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,creation DATETIME,modification DATETIME);"

// execute the query and retrieve the (boolean) result
var result = plugins.it2be_data.query("crm", query);

// create a message based on the result
if (result) {
var message = "The table is alive and kicking!\nHave a look in your database management application";
} else {
var message = "Creation of the table failed!";
}

// and show the message in an info dialog
plugins.dialogs.showInfoDialog("DBTable creation", message);

// now let's assume you are in production and run the query against a database that is
// used by the solution
// plugins.it2be_data.flushClientsCache("crm", "sql_test");

// REMARK: it is possible to perform ANY query you want as long as there is a database server
// however I would only use this method to perform actions on databases not connected to
// your production solution to avoid critical mistakes.

// now let's create a record and use the input parameter
// to execute uncomment the below 5 lines of code
// var query = "INSERT INTO sql_test (creation, modification) VALUES (?, ?)";
// var input = [new Date(), new Date()];
// var result = plugins.it2be_data.query("crm", query, input);

// application.output(result);
// plugins.it2be_data.flushClientsCache("crm", "sql_test");

// it is also possible to use the query to drop a table (below) and perform any
// other action you need to take
// to execute uncomment the below 3 lines of code
// var query = "DROP TABLE sql_test";
// var result = plugins.it2be_data.query("crm", query);

// application.output(result);


register
Boolean  register( String )
Register your runtime license (use the Components Manager to request your license string)
Parameters
String  license  the license

Returns
Boolean  result boolean

Supported Clients
SmartClient, WebClient, NGClient

Sample
// You have to register your plug-in(s) and beans before you start using them
// One way to do so is create an 'onOpenSolution' method and add the 'register(.)' method(s) there
plugins.it2be_data.register("Insert the license string(s) from your '*_runtime_license.txt' file here!");