JSDataSet

Property Summary

TypeNameSummary

Get or set the record index of the dataset..

Methods Summary

TypeNameSummary

adds a column with the specified name to the dataset..

adds a column with the specified name to the dataset..

adds a column with the specified name to the dataset..

void

Add an HTML property to an HTML tag produced in getAsHTML()..

void

Add a row to the dataset..

void

Add a row to the dataset..

Create a datasource from the data set with specified name and using specified types..

Create a datasource from the data set with specified name and using specified types..

Create a datasource from the data set with specified name and using specified types..

Get the dataset as an html table, do not escape values or spaces, no multi_line_markup, do not add indentation, add column names..

Get the dataset as an html table, do not escape spaces, no multi_line_markup, do not add indentation, add column names..

Get the dataset as an html table, no multi_line_markup, do not add indentation, add column names..

Get the dataset as an html table, do not add indentation, add column names..

Get the dataset as an html table, add column names..

Get the dataset as an html table..

Get the dataset as formatted text..

Get the column data of a dataset as an Array..

Get a column name based on index..

Get the column names of a dataset..

Get a column type based on index..

Get the database exception if an error occurred..

Get the number of columns in the dataset..

Get the number of rows in the dataset..

Get the row data of a dataset as an Array..

Get the value specified by row and column position from the dataset..

Return true if there is more data in the resultset then specified by maxReturnedRows at query time..

Remove a column by index from the dataset..

void

Remove a row from the dataset..

void

Set a column name based on index..

void

Set the value specified by row and column position from the dataset..

void

Sort the dataset on the given column (1-based) in ascending or descending..

void

Sort the dataset using the function as comparator..

Properties Details

rowIndex

Get or set the record index of the dataset.

Returns Number

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 Details

addColumn(name)

adds a column with the specified name to the dataset.

Parameters String name column name.

Returns Boolean true if succeeded, else false.

Sample

//assuming the variable dataset contains a dataset
var success = dataset.addColumn('columnName',1);

addColumn(name, index)

adds a column with the specified name to the dataset.

Parameters String name column name. Number index column index number between 1 and getMaxColumnIndex().

Returns Boolean true if succeeded, else false.

Sample

//assuming the variable dataset contains a dataset
var success = dataset.addColumn('columnName',1);

addColumn(name, index, type)

adds a column with the specified name to the dataset.

Parameters String name column name. Number index column index number between 1 and getMaxColumnIndex(). Number type the type of column, see JSColumn constants.

Returns Boolean true if succeeded, else false.

Sample

//assuming the variable dataset contains a dataset
var success = dataset.addColumn('columnName',1);

addHTMLProperty(row, col, name, value)

Add an HTML property to an HTML tag produced in getAsHTML().

For row and col parameters use: 1 = applies to the container 0 = applies to all

0 = applies to specific cell

Parameters Number row row number Number col column number String name String property name String value String property value

Returns void

Sample

//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>';

addRow(index, array)

Add a row to the dataset.

Parameters Number index index to add row (1-based) Array array row data

Returns void

Sample

//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.

Parameters Array array row data

Returns void

Sample

//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()

Parameters String name datasource name

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.

Parameters String name datasource name Object types array of types as defined in JSColumn

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()

Parameters String name datasource name Object types array of types as defined in JSColumn, when null types are inferred from the query result Array pkNames 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.

Returns String String html.

Sample

//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 Boolean escape_values if true, replaces illegal HTML characters with corresponding valid escape sequences.

Returns String String html.

Sample

//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 Boolean escape_values if true, replaces illegal HTML characters with corresponding valid escape sequences. Boolean escape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags.

Returns String String html.

Sample

//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, multi_line_markup)

Get the dataset as an html table, do not add indentation, add column names.

Parameters Boolean escape_values if true, replaces illegal HTML characters with corresponding valid escape sequences. Boolean escape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags. Boolean multi_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

Returns String String html.

Sample

//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, multi_line_markup, pretty_indent)

Get the dataset as an html table, add column names.

Parameters Boolean escape_values if true, replaces illegal HTML characters with corresponding valid escape sequences. Boolean escape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags. Boolean multi_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

Boolean pretty_indent if true, adds indentation for more readable HTML code.

Returns String String html.

Sample

//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, multi_line_markup, pretty_indent, add_column_names)

Get the dataset as an html table.

Parameters Boolean escape_values if true, replaces illegal HTML characters with corresponding valid escape sequences. Boolean escape_spaces if true, replaces text spaces with non-breaking space tags ( ) and tabs by four non-breaking space tags. Boolean multi_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

Boolean pretty_indent if true, adds indentation for more readable HTML code. Boolean add_column_names if false, column headers will not be added to the table.

Returns String String html.

Sample

//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>'.

getAsText(column_separator, row_separator, value_delimiter, add_column_names)

Get the dataset as formatted text.

Parameters String column_separator any specified column separator; examples: tab '\t'; comma ','; semicolon ';'; space ' ' . String row_separator the specified row separator; examples: new line '\n'. String value_delimiter the specified value delimiter; null means empty string; example: double quote '"'. Boolean add_column_names if true column names will be added as a first row.

Returns String String formatted text.

Sample

//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)

getColumnAsArray(index)

Get the column data of a dataset as an Array.

Parameters Number index index of column (1-based).

Returns Array Object array of data.

Sample

//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

getColumnName(index)

Get a column name based on index.

Parameters Number index index of column (1-based).

Returns String String col