A JSDataSet is a runtime object in Servoy designed to work with data organized in rows and columns. Unlike foundsets, it operates independently of persistent datasources, allowing developers to create and manipulate temporary datasets dynamically. This flexibility makes it ideal for handling transient data at runtime without requiring direct database connectivity.
Core Functionality
The JSDataSet supports a range of operations for managing data. Developers can add, remove, and retrieve rows and columns or set specific values within the dataset. For enhanced usability, the object provides methods to sort rows by column values or through custom comparator functions, enabling advanced data handling logic. Additionally, the dataset can be exported into formats such as HTML tables or delimited text, supporting customized data representation and integration with other Servoy features.
Temporary datasources can also be created from datasets, allowing them to be reused in forms, scripts, or even foundsets. This capability ensures the flow between runtime data generation and persistent application components. The JSDataSet is particularly effective for scenarios requiring transient data manipulation, such as on-the-fly calculations or data processing pipelines.
Sort the dataset using the function as comparator.
Properties Detailed
rowIndex
Get or set the record index of the dataset.
TypeNumber The current row index, starting from 1, or -1 if no row is selected.
Sample
//assuming the variable dataset contains a dataset
//to set the rowIndex:
dataset.rowIndex = 1 //sets the rowIndex to the first row (dataset is 1-based)
//to retrieve the rowIndex of the currently selected row
var currRow = dataset.rowIndex
Methods Detailed
addColumn(name)
adds a column with the specified name to the dataset.
//adds a container property (to TABLE tag)
dataset.addHTMLProperty(-1,-1,'cellspacing','3');
dataset.addHTMLProperty(-1,-1,'style','border-collapse:collapse;'); //to have a single line border
//adds a row property to all rows (to TR tag)
dataset.addHTMLProperty(0,0,'class','text');
//adds a row property to second row (to TR tag)
dataset.addHTMLProperty(2,0,'class','text');
//adds a column property to all 3rd columns (to TD tag)
dataset.addHTMLProperty(0,3,'class','redcolumn') ;
//adds a specific cell property (to TD tag)
dataset.addHTMLProperty(2,4,'color','blue');
scopes.globals.html_field = '<html>'+dataset.getAsHTML()+'</html>';
//assuming the variable dataset contains a dataset
dataset.addRow(new Array(1,2,3,4,5,6,7,7)); //adds a row with 8 columns
dataset.addRow(2, new Array(1,2,3,4,5,6,7,7)); //adds a row with 8 columns at row 2
addRow(array)
Add a row to the dataset. The row will be added as the last row.
//assuming the variable dataset contains a dataset
dataset.addRow(new Array(1,2,3,4,5,6,7,7)); //adds a row with 8 columns
dataset.addRow(2, new Array(1,2,3,4,5,6,7,7)); //adds a row with 8 columns at row 2
createDataSource(name)
Create a datasource from the data set with specified name and using specified types.
The types are inferred from the data if possible.
A temporary datasource cannot be removed because once created there may always be forms or relations that refer to it.
When the client exits, all datasources used by that client are removed automatically.
Most resources used by the datasource can be released by deleting all records:
dataset.removeRow(-1) or databaseManager.getFoundSet(datasource).deleteAllRecords()
Returns:String String uri reference to the created datasource.
Sample
ds.addColumn('my_id'); // note: use regular javascript identifiers so they can be used in scripting
ds.addColumn('my_label');
var uri = ds.createDataSource('mydata', [JSColumn.INTEGER, JSColumn.TEXT]);
var jsform = solutionModel.newForm(fname, uri, null, true, 300, 300);
var query = 'select customerid, address, city, country from customers';
var ds2 = databaseManager.getDataSetByQuery('example_data', query, null, 999);
var uri2 = ds2.createDataSource('mydata2'); // types are inferred from query result
createDataSource(name, types)
Create a datasource from the data set with specified name and using specified types.
A temporary datasource cannot be removed because once created there may always be forms or relations that refer to it.
When the client exits, all datasources used by that client are removed automatically.
Most resources used by the datasource can be released by deleting all records:
dataset.removeRow(-1) or databaseManager.getFoundSet(datasource).deleteAllRecords()
A datasource can be reused if the data has the same signature (column names and types).
A new createDataSource() call will clear the datasource contents from a previous call and insert the current data.
Returns:String String uri reference to the created datasource.
Sample
ds.addColumn('my_id'); // note: use regular javascript identifiers so they can be used in scripting
ds.addColumn('my_label');
var uri = ds.createDataSource('mydata', [JSColumn.INTEGER, JSColumn.TEXT]);
var jsform = solutionModel.newForm(fname, uri, null, true, 300, 300);
var query = 'select customerid, address, city, country from customers';
var ds2 = databaseManager.getDataSetByQuery('example_data', query, null, 999);
var uri2 = ds2.createDataSource('mydata2'); // types are inferred from query result
createDataSource(name, types, pkNames)
Create a datasource from the data set with specified name and using specified types.
A temporary datasource cannot be removed because once created there may always be forms or relations that refer to it.
When the client exits, all datasources used by that client are removed automatically.
Most resources used by the datasource can be released by deleting all records:
dataset.removeRow(-1) or databaseManager.getFoundSet(datasource).deleteAllRecords()
Objecttypes array of types as defined in JSColumn, when null types are inferred from the query result
ArraypkNames array of pk names, when null a hidden pk-column will be added
Returns:String String uri reference to the created datasource.
Sample
ds.addColumn('my_id'); // note: use regular javascript identifiers so they can be used in scripting
ds.addColumn('my_label');
var uri = ds.createDataSource('mydata', [JSColumn.INTEGER, JSColumn.TEXT], ['my_id']);
var jsform = solutionModel.newForm(fname, uri, null, true, 300, 300);
var query = 'select customerid, address, city, country from customers';
var ds2 = databaseManager.getDataSetByQuery('example_data', query, null, 999);
var uri2 = ds2.createDataSource('mydata2', null, ['customerid']); // types are inferred from query result, use customerid as pk
getAsHTML()
Get the dataset as an html table, do not escape values or spaces, no multi_line_markup, do not add indentation, add column names.
//gets a dataset based on a query
//useful to limit the number of rows
var maxReturnedRows = 10;
var query = 'select c1,c2,c3 from test_table where start_date = ?';
//to access data by name, do not use '.' or special characters in names or aliases
var args = new Array();
args[0] = order_date //or new Date();
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,args,maxReturnedRows);
// gets a dataset with escape values; escape spaces (lines will not wrap); no multi-line markup; with pretty indentation; shows column names
var htmlTable = dataset.getAsHTML(true, true, false, true, true);
//assigns the dataset to a field and sets the display type to HTML_AREA
//assuming the html_field is a global text variable
scopes.globals.html_field = '<html>'+dataset.getAsHTML()+'</html>';
//Note: To display an HTML_AREA field as an HTML page, add HTML tags at the beginning '<html>' and at the end '</html>'.
getAsHTML(escape_values)
Get the dataset as an html table, do not escape spaces, no multi_line_markup, do not add indentation, add column names.
Parameters
Booleanescape_values if true, replaces illegal HTML characters with corresponding valid escape sequences.
//gets a dataset based on a query
//useful to limit the number of rows
var maxReturnedRows = 10;
var query = 'select c1,c2,c3 from test_table where start_date = ?';
//to access data by name, do not use '.' or special characters in names or aliases
var args = new Array();
args[0] = order_date //or new Date();
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,args,maxReturnedRows);
// gets a dataset with escape values; escape spaces (lines will not wrap); no multi-line markup; with pretty indentation; shows column names
var htmlTable = dataset.getAsHTML(true, true, false, true, true);
//assigns the dataset to a field and sets the display type to HTML_AREA
//assuming the html_field is a global text variable
scopes.globals.html_field = '<html>'+dataset.getAsHTML()+'</html>';
//Note: To display an HTML_AREA field as an HTML page, add HTML tags at the beginning '<html>' and at the end '</html>'.
getAsHTML(escape_values, escape_spaces)
Get the dataset as an html table, no multi_line_markup, do not add indentation, add column names.
Parameters
Booleanescape_values if true, replaces illegal HTML characters with corresponding valid escape sequences.
Booleanescape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags.
//gets a dataset based on a query
//useful to limit the number of rows
var maxReturnedRows = 10;
var query = 'select c1,c2,c3 from test_table where start_date = ?';
//to access data by name, do not use '.' or special characters in names or aliases
var args = new Array();
args[0] = order_date //or new Date();
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,args,maxReturnedRows);
// gets a dataset with escape values; escape spaces (lines will not wrap); no multi-line markup; with pretty indentation; shows column names
var htmlTable = dataset.getAsHTML(true, true, false, true, true);
//assigns the dataset to a field and sets the display type to HTML_AREA
//assuming the html_field is a global text variable
scopes.globals.html_field = '<html>'+dataset.getAsHTML()+'</html>';
//Note: To display an HTML_AREA field as an HTML page, add HTML tags at the beginning '<html>' and at the end '</html>'.
Get the dataset as an html table, do not add indentation, add column names.
Parameters
Booleanescape_values if true, replaces illegal HTML characters with corresponding valid escape sequences.
Booleanescape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags.
Booleanmulti_line_markup if true, multiLineMarkup will enforce new lines that are in the text; single new lines will be replaced by
, multiple new lines will be replaced by
//gets a dataset based on a query
//useful to limit the number of rows
var maxReturnedRows = 10;
var query = 'select c1,c2,c3 from test_table where start_date = ?';
//to access data by name, do not use '.' or special characters in names or aliases
var args = new Array();
args[0] = order_date //or new Date();
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,args,maxReturnedRows);
// gets a dataset with escape values; escape spaces (lines will not wrap); no multi-line markup; with pretty indentation; shows column names
var htmlTable = dataset.getAsHTML(true, true, false, true, true);
//assigns the dataset to a field and sets the display type to HTML_AREA
//assuming the html_field is a global text variable
scopes.globals.html_field = '<html>'+dataset.getAsHTML()+'</html>';
//Note: To display an HTML_AREA field as an HTML page, add HTML tags at the beginning '<html>' and at the end '</html>'.
Get the dataset as an html table, add column names.
Parameters
Booleanescape_values if true, replaces illegal HTML characters with corresponding valid escape sequences.
Booleanescape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags.
Booleanmulti_line_markup if true, multiLineMarkup will enforce new lines that are in the text; single new lines will be replaced by
, multiple new lines will be replaced by
Booleanpretty_indent if true, adds indentation for more readable HTML code.
//gets a dataset based on a query
//useful to limit the number of rows
var maxReturnedRows = 10;
var query = 'select c1,c2,c3 from test_table where start_date = ?';
//to access data by name, do not use '.' or special characters in names or aliases
var args = new Array();
args[0] = order_date //or new Date();
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,args,maxReturnedRows);
// gets a dataset with escape values; escape spaces (lines will not wrap); no multi-line markup; with pretty indentation; shows column names
var htmlTable = dataset.getAsHTML(true, true, false, true, true);
//assigns the dataset to a field and sets the display type to HTML_AREA
//assuming the html_field is a global text variable
scopes.globals.html_field = '<html>'+dataset.getAsHTML()+'</html>';
//Note: To display an HTML_AREA field as an HTML page, add HTML tags at the beginning '<html>' and at the end '</html>'.
Booleanescape_values if true, replaces illegal HTML characters with corresponding valid escape sequences.
Booleanescape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags.
Booleanmulti_line_markup if true, multiLineMarkup will enforce new lines that are in the text; single new lines will be replaced by
, multiple new lines will be replaced by
Booleanpretty_indent if true, adds indentation for more readable HTML code.
Booleanadd_column_names if false, column headers will not be added to the table.
//gets a dataset based on a query
//useful to limit the number of rows
var maxReturnedRows = 10;
var query = 'select c1,c2,c3 from test_table where start_date = ?';
//to access data by name, do not use '.' or special characters in names or aliases
var args = new Array();
args[0] = order_date //or new Date();
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,args,maxReturnedRows);
// gets a dataset with escape values; escape spaces (lines will not wrap); no multi-line markup; with pretty indentation; shows column names
var htmlTable = dataset.getAsHTML(true, true, false, true, true);
//assigns the dataset to a field and sets the display type to HTML_AREA
//assuming the html_field is a global text variable
scopes.globals.html_field = '<html>'+dataset.getAsHTML()+'</html>';
//Note: To display an HTML_AREA field as an HTML page, add HTML tags at the beginning '<html>' and at the end '</html>'.
//assuming the variable dataset contains a dataset
//you can create csv or tab delimited results
var csv = dataset.getAsText(',','\n','"',true)
var tab = dataset.getAsText('\t','\n','"',true)
//assuming the variable dataset contains a dataset
var dataArray = dataset.getColumnAsArray(1); //puts the contents from the first column of the dataset into an array
//once you have it as an array you can loop through it or feed it to a custom valuelist for example
//assuming the variable dataset contains a dataset
var firstColumName = dataset.getColumnName(1) //retrieves the first columnname into the variable firstColumName
//using a loop you can get all columnames in an array:
var query = 'select * from customers';
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), query, null, 100);
var colArray = new Array()
for (var i = 1; i <= dataset.getMaxColumnIndex(); i++)
{
colArray[i-1] = dataset.getColumnName(i)
//note the -1, because an array is zero based and dataset is 1 based.
}
var query = 'select * from customers';
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), query, null, 100);
var columnNames = dataset.getColumnNames();
Returns:Number Number the column type (JSColumn constant)
Sample
//assuming the variable dataset contains a dataset
var firstColumnType = dataset.getColumnType(1) //retrieves the first column's type into the variable firstColumnType
if (firstColumnType == JSColumn.NUMBER) { }
getException()
Get the database exception if an error occurred.
Returns:ServoyException ServoyException exception or null when not available.
Sample
//assuming the variable dataset contains a dataset
var dbException = dataset.getException();
//assuming the variable dataset contains a dataset
for (var i = 1; i <= dataset.getMaxColumnIndex(); i++)
{
colArray[i-1] = dataset.getColumnName(i)
//have to subtract 1, because an array is zero based and a dataset is 1 based.
}
//assuming the variable dataset contains a dataset
var dataArray = dataset.getRowAsArray(1); //puts the contents from the first row of the dataset into an array
//once you have it as an array you can loop through it
getRowObject(index)
Get the row data of a dataset as an Array that can also retrieve data by column name.
//assuming the variable dataset contains a dataset
var dataArray = dataset.getRowObject(1); //puts the contents from the first row of the dataset into an array
//use dataArray[index] or dataArray.columnName
getValue(row, col)
Get the value specified by row and column position from the dataset.
var ds = databaseManager.getDataSetByQuery('example_data', 'select order_id from orders', null, 10000)
if (ds.hadMoreData())
{
// handle large result
}
var query = 'select customerid, customername from customers';
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), query, null, -1);
dataset.setColumnName(2, 'name_of_customer') // change the column name for second column.
setValue(row, col, obj)
Set the value specified by row and column position from the dataset.
Use row = -1, to set columnnames.
Booleansort_direction ascending (true) or descending (false)
Returns: void
Sample
// sort using column number
//assuming the variable dataset contains a dataset
dataset.sort(1, false)
sort(comparator)
Sort the dataset using the function as comparator.
The comparator function is called to compare two rows, that are passed as arguments, and
it will return -1/0/1 if the first row is less/equal/greater then the second row.
NOTE: starting with 7.2 release, when called on datasource(foundset) dataset, this function doesn't save the data anymore
