May 06, 2024 03:27 Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients SampleSupported Clients
SmartClient
WebClient
NGClient
MobileClient
Property Summary
Boolean
nullColumnValidatorEnabled
Enable/disable the default null validator for non null columns, makes it possible todo the checks later on when saving, when for example autosave is disabled.
Methods Summary
Boolean
acquireLock(foundset, recordIndex)
Request lock(s) for a foundset, can be a normal or related foundset.
Boolean
acquireLock(foundset, recordIndex, lockName)
Request lock(s) for a foundset, can be a normal or related foundset.
Boolean
addTableFilterParam(datasource, dataprovider, operator, value)
Adds a filter to all the foundsets based on a table.
Boolean
addTableFilterParam(datasource, dataprovider, operator, value, filterName)
Adds a filter to all the foundsets based on a table.
Boolean
addTableFilterParam(serverName, tableName, dataprovider, operator, value)
Adds a filter to all the foundsets based on a table.
Boolean
addTableFilterParam(serverName, tableName, dataprovider, operator, value, filterName)
Adds a filter to all the foundsets based on a table.
void
addTrackingInfo(columnName, value)
Add tracking info used in the log table.
Boolean
commitTransaction()
Returns true if a transaction is committed; rollback if commit fails.
Boolean
commitTransaction(saveFirst)
Returns true if a transaction is committed; rollback if commit fails.
Boolean
commitTransaction(saveFirst, revertSavedRecords)
Returns true if a transaction is committed; rollback if commit fails.
JSFoundSet
convertFoundSet(foundset, related)
Creates a foundset that combines all the records of the specified one-to-many relation seen from the given parent/primary foundset.
JSFoundSet
convertFoundSet(foundset, related)
Creates a foundset that combines all the records of the specified one-to-many relation seen from the given parent/primary foundset.
JSDataSet
convertToDataSet(foundset)
Converts the argument to a JSDataSet, possible use in controller.
JSDataSet
convertToDataSet(foundset, dataproviderNames)
Converts the argument to a JSDataSet, possible use in controller.
JSDataSet
convertToDataSet(values)
Converts the argument to a JSDataSet, possible use in controller.
JSDataSet
convertToDataSet(values, dataproviderNames)
Converts the argument to a JSDataSet, possible use in controller.
JSDataSet
convertToDataSet(ids)
Converts the argument to a JSDataSet, possible use in controller.
Boolean
copyMatchingFields(source, destination)
Copies all matching non empty columns (if overwrite boolean is given all columns except pk/ident, if array then all columns except pk and array names).
Boolean
copyMatchingFields(source, destination, overwrite)
Copies all matching non empty columns (if overwrite boolean is given all columns except pk/ident, if array then all columns except pk and array names).
Boolean
copyMatchingFields(source, destination, names)
Copies all matching non empty columns (if overwrite boolean is given all columns except pk/ident, if array then all columns except pk and array names).
String
createDataSourceByQuery(name, query, useTableFilters, max_returned_rows, types, pkNames)
Performs a query and saves the result in a datasource.
String
createDataSourceByQuery(name, query, max_returned_rows)
Performs a query and saves the result in a datasource.
String
createDataSourceByQuery(name, query, max_returned_rows, types)
Performs a query and saves the result in a datasource.
String
createDataSourceByQuery(name, query, max_returned_rows, types, pkNames)
Performs a query and saves the result in a datasource.
String
createDataSourceByQuery(name, server_name, sql_query, arguments, max_returned_rows)
Performs a sql query on the specified server, saves the the result in a datasource.
String
createDataSourceByQuery(name, server_name, sql_query, arguments, max_returned_rows, types)
Performs a sql query on the specified server, saves the the result in a datasource.
String
createDataSourceByQuery(name, server_name, sql_query, arguments, max_returned_rows, types, pkNames)
Performs a sql query on the specified server, saves the the result in a datasource.
JSDataSet
createEmptyDataSet()
Returns an empty dataset object.
JSDataSet
createEmptyDataSet(rowCount, columnCount)
Returns an empty dataset object.
JSDataSet
createEmptyDataSet(rowCount, columnNames)
Returns an empty dataset object.
QBSelect
createSelect(dataSource)
Create a QueryBuilder object for a datasource.
QBSelect
createSelect(dataSource, tableAlias)
Create a QueryBuilder object for a datasource with given table alias.
Boolean
dataSourceExists(datasource)
Check wether a data source exists.
Boolean
getAutoSave()
Returns true or false if autosave is enabled or disabled.
Array
getDataModelClonesFrom(serverName)
Retrieves a list with names of all database servers that have property DataModelCloneFrom equal to the server name parameter.
JSDataSet
getDataSetByQuery(query, useTableFilters, max_returned_rows)
Performs a sql query with a query builder object.
JSDataSet
getDataSetByQuery(query, max_returned_rows)
Performs a sql query with a query builder object.
JSDataSet
getDataSetByQuery(server_name, sql_query, arguments, max_returned_rows)
Performs a sql query on the specified server, returns the result in a dataset.
String
getDataSource(serverName, tableName)
Returns the datasource corresponding to the given server/table.
String
getDataSourceServerName(dataSource)
Returns the server name from the datasource, or null if not a database datasource.
String
getDataSourceTableName(dataSource)
Returns the table name from the datasource, or null if not a database datasource.
String
getDatabaseProductName(serverName)
Returns the database product name as supplied by the driver for a server.
Array
getEditedRecords()
Returns an array of edited records with outstanding (unsaved) data.
Array
getEditedRecords(foundset)
Returns an array of edited records with outstanding (unsaved) data.
Array
getFailedRecords()
Returns an array of records that fail after a save.
Array
getFailedRecords(foundset)
Returns an array of records that fail after a save.
JSFoundSet
getFoundSet(query)
Returns a foundset object for a specified pk query.
JSFoundSet
getFoundSet(dataSource)
Returns a foundset object for a specified datasource or server and tablename.
JSFoundSet
getFoundSet(serverName, tableName)
Returns a foundset object for a specified datasource or server and tablename.
Number
getFoundSetCount(foundset)
Returns the total number of records in a foundset.
JSFoundSetUpdater
getFoundSetUpdater(foundset)
Returns a JSFoundsetUpdater object that can be used to update all or a specific number of rows in the specified foundset.
Object
getNextSequence(dataSource, columnName)
Gets the next sequence for a column which has a sequence defined in its column dataprovider properties.
String
getSQL(foundsetOrQBSelect)
Returns the internal SQL which defines the specified (related)foundset.
String
getSQL(foundsetOrQBSelect, includeFilters)
Returns the internal SQL which defines the specified (related)foundset.
Array
getSQLParameters(foundsetOrQBSelect)
Returns the internal SQL parameters, as an array, that are used to define the specified (related)foundset.
Array
getSQLParameters(foundsetOrQBSelect, includeFilters)
Returns the internal SQL parameters, as an array, that are used to define the specified (related)foundset.
Array
getServerNames()
Returns an array with all the server names used in the solution.
JSTable
getTable(foundset)
Returns the JSTable object from which more info can be obtained (like columns).
JSTable
getTable(record)
Returns the JSTable object from which more info can be obtained (like columns).
JSTable
getTable(dataSource)
Returns the JSTable object from which more info can be obtained (like columns).
JSTable
getTable(serverName, tableName)
Returns the JSTable object from which more info can be obtained (like columns).
Number
getTableCount(dataSource)
Returns the total number of records(rows) in a table.
Array
getTableFilterParams(serverName)
Returns a two dimensional array object containing the table filter information currently applied to the servers tables.
Array
getTableFilterParams(serverName, filterName)
Returns a two dimensional array object containing the table filter information currently applied to the servers tables.
Array
getTableNames(serverName)
Returns an array of all table names for a specified server.
Array
getViewNames(serverName)
Returns an array of all view names for a specified server.
Boolean
hasLocks()
Returns true if the current client has any or the specified lock(s) acquired.
Boolean
hasLocks(lockName)
Returns true if the current client has any or the specified lock(s) acquired.
Boolean
hasNewRecords(foundset)
Returns true if the argument (foundSet / record) has at least one row that was not yet saved in the database.
Boolean
hasNewRecords(foundset, index)
Returns true if the argument (foundSet / record) has at least one row that was not yet saved in the database.
Boolean
hasRecordChanges(foundset)
Returns true if the specified foundset, on a specific index or in any of its records, or the specified record has changes.
Boolean
hasRecordChanges(foundset, index)
Returns true if the specified foundset, on a specific index or in any of its records, or the specified record has changes.
Boolean
hasRecords(foundset)
Returns true if the (related)foundset exists and has records.
Boolean
hasRecords(record, relationString)
Returns true if the (related)foundset exists and has records.
Boolean
hasTransaction()
Returns true if there is an transaction active for this client.
Boolean
mergeRecords(sourceRecord, combinedDestinationRecord)
Merge records from the same foundset, updates entire datamodel (via foreign type on columns) with destination
record pk, deletes source record.
Boolean
mergeRecords(sourceRecord, combinedDestinationRecord, columnNames)
Merge records from the same foundset, updates entire datamodel (via foreign type on columns) with destination
record pk, deletes source record.
void
recalculate(foundsetOrRecord)
Can be used to recalculate a specified record or all rows in the specified foundset.
Boolean
refreshRecordFromDatabase(foundset, index)
Flushes the client data cache and requeries the data for a record (based on the record index) in a foundset or all records in the foundset.
Boolean
releaseAllLocks()
Release all current locks the client has (optionally limited to named locks).
Boolean
releaseAllLocks(lockName)
Release all current locks the client has (optionally limited to named locks).
Boolean
removeTableFilterParam(serverName, filterName)
Removes a previously defined table filter.
void
revertEditedRecords()
Reverts outstanding (not saved) in memory changes from edited records.
void
revertEditedRecords(foundset)
Reverts outstanding (not saved) in memory changes from edited records.
void
rollbackTransaction()
Rollback a transaction started by databaseManager.
void
rollbackTransaction(rollbackEdited)
Rollback a transaction started by databaseManager.
void
rollbackTransaction(rollbackEdited, revertSavedRecords)
Rollback a transaction started by databaseManager.
Boolean
saveData()
Saves all outstanding (unsaved) data and exits the current record.
Boolean
saveData(foundset)
Saves all outstanding (unsaved) data and exits the current record.
Boolean
saveData(record)
Saves all outstanding (unsaved) data and exits the current record.
Boolean
setAutoSave(autoSave)
Set autosave, if false then no saves will happen by the ui (not including deletes!).
void
setCreateEmptyFormFoundsets()
Turnoff the initial form foundset record loading, set this in the solution open method.
void
startTransaction()
Start a database transaction.
Boolean
switchServer(sourceName, destinationName)
Switches a named server to another named server with the same datamodel (recommended to be used in an onOpen method for a solution).
Property Details
nullColumnValidatorEnabled
databaseManager.nullColumnValidatorEnabled = false;//disable
//test if enabled
if(databaseManager.nullColumnValidatorEnabled) application.output('null validation enabled')
Methods Details
acquireLock(foundset, recordIndex)
JSFoundSet
foundset
The JSFoundset to get the lock for
Number
recordIndex
The record index which should be locked.
//locks the complete foundset
databaseManager.acquireLock(foundset,-1);
//locks the current row
databaseManager.acquireLock(foundset,0);
//locks all related orders for the current Customer
var success = databaseManager.acquireLock(Cust_to_Orders,-1);
if(!success)
{
plugins.dialogs.showWarningDialog('Alert','Failed to get a lock','OK');
}
acquireLock(foundset, recordIndex, lockName)
JSFoundSet
foundset
The JSFoundset to get the lock for
Number
recordIndex
The record index which should be locked.
String
lockName
The name of the lock.
//locks the complete foundset
databaseManager.acquireLock(foundset,-1);
//locks the current row
databaseManager.acquireLock(foundset,0);
//locks all related orders for the current Customer
var success = databaseManager.acquireLock(Cust_to_Orders,-1);
if(!success)
{
plugins.dialogs.showWarningDialog('Alert','Failed to get a lock','OK');
}
addTableFilterParam(datasource, dataprovider, operator, value)
String
datasource
The datasource
String
dataprovider
A specified dataprovider column name.
String
operator
One of "=, <, >, >=, <=, !=, LIKE, or IN" optionally augmented with modifiers "#" (ignore case) or "^||" (or-is-null).
Object
value
The specified filter value.
// Best way to call this in a global solution startup method, but filters may be added/removed at any time.
// Note that multiple filters can be added to the same dataprovider, they will all be applied.
// filter on messages table where messagesid>10, the filter has a name so it can be removed using databaseManager.removeTableFilterParam()
var success = databaseManager.addTableFilterParam('admin', 'messages', 'messagesid', '>', 10, 'higNumberedMessagesRule')
// all tables that have the companyid column should be filtered
var success = databaseManager.addTableFilterParam('crm', null, 'companyidid', '=', currentcompanyid)
// some filters with in-conditions
var success = databaseManager.addTableFilterParam('crm', 'products', 'productcode', 'in', [120, 144, 200])
var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'in', 'select country code from countries where region = "Europe"')
// you can use modifiers in the operator as well, filter on companies where companyname is null or equals-ignore-case 'servoy'
var success = databaseManager.addTableFilterParam('crm', 'companies', 'companyname', '#^||=', 'servoy')
// the value may be null, this will result in 'column is null' sql condition.
var success = databaseManager.addTableFilterParam('crm', 'companies', 'verified', '=', null)
//if you want to add a filter for a column (created by you) in the i18n table
databaseManager.addTableFilterParam('database', 'your_i18n_table', 'message_variant', 'in', [1, 2])
addTableFilterParam(datasource, dataprovider, operator, value, filterName)
String
datasource
The datasource
String
dataprovider
A specified dataprovider column name.
String
operator
One of "=, <, >, >=, <=, !=, LIKE, or IN" optionally augmented with modifiers "#" (ignore case) or "^||" (or-is-null).
Object
value
The specified filter value.
String
filterName
The specified name of the database table filter.
// Best way to call this in a global solution startup method, but filters may be added/removed at any time.
// Note that multiple filters can be added to the same dataprovider, they will all be applied.
// filter on messages table where messagesid>10, the filter has a name so it can be removed using databaseManager.removeTableFilterParam()
var success = databaseManager.addTableFilterParam('admin', 'messages', 'messagesid', '>', 10, 'higNumberedMessagesRule')
// all tables that have the companyid column should be filtered
var success = databaseManager.addTableFilterParam('crm', null, 'companyidid', '=', currentcompanyid)
// some filters with in-conditions
var success = databaseManager.addTableFilterParam('crm', 'products', 'productcode', 'in', [120, 144, 200])
var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'in', 'select country code from countries where region = "Europe"')
// you can use modifiers in the operator as well, filter on companies where companyname is null or equals-ignore-case 'servoy'
var success = databaseManager.addTableFilterParam('crm', 'companies', 'companyname', '#^||=', 'servoy')
// the value may be null, this will result in 'column is null' sql condition.
var success = databaseManager.addTableFilterParam('crm', 'companies', 'verified', '=', null)
//if you want to add a filter for a column (created by you) in the i18n table
databaseManager.addTableFilterParam('database', 'your_i18n_table', 'message_variant', 'in', [1, 2])
addTableFilterParam(serverName, tableName, dataprovider, operator, value)
String
serverName
The name of the database server connection for the specified table name.
String
tableName
The name of the specified table.
String
dataprovider
A specified dataprovider column name.
String
operator
One of "=, <, >, >=, <=, !=, LIKE, or IN" optionally augmented with modifiers "#" (ignore case) or "^||" (or-is-null).
Object
value
The specified filter value.
// Best way to call this in a global solution startup method, but filters may be added/removed at any time.
// Note that multiple filters can be added to the same dataprovider, they will all be applied.
// filter on messages table where messagesid>10, the filter has a name so it can be removed using databaseManager.removeTableFilterParam()
var success = databaseManager.addTableFilterParam('admin', 'messages', 'messagesid', '>', 10, 'higNumberedMessagesRule')
// all tables that have the companyid column should be filtered
var success = databaseManager.addTableFilterParam('crm', null, 'companyidid', '=', currentcompanyid)
// some filters with in-conditions
var success = databaseManager.addTableFilterParam('crm', 'products', 'productcode', 'in', [120, 144, 200])
var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'in', 'select country code from countries where region = "Europe"')
// you can use modifiers in the operator as well, filter on companies where companyname is null or equals-ignore-case 'servoy'
var success = databaseManager.addTableFilterParam('crm', 'companies', 'companyname', '#^||=', 'servoy')
// the value may be null, this will result in 'column is null' sql condition.
var success = databaseManager.addTableFilterParam('crm', 'companies', 'verified', '=', null)
//if you want to add a filter for a column (created by you) in the i18n table
databaseManager.addTableFilterParam('database', 'your_i18n_table', 'message_variant', 'in', [1, 2])
addTableFilterParam(serverName, tableName, dataprovider, operator, value, filterName)
String
serverName
The name of the database server connection for the specified table name.
String
tableName
The name of the specified table.
String
dataprovider
A specified dataprovider column name.
String
operator
One of "=, <, >, >=, <=, !=, LIKE, or IN" optionally augmented with modifiers "#" (ignore case) or "^||" (or-is-null).
Object
value
The specified filter value.
String
filterName
The specified name of the database table filter.
// Best way to call this in a global solution startup method, but filters may be added/removed at any time.
// Note that multiple filters can be added to the same dataprovider, they will all be applied.
// filter on messages table where messagesid>10, the filter has a name so it can be removed using databaseManager.removeTableFilterParam()
var success = databaseManager.addTableFilterParam('admin', 'messages', 'messagesid', '>', 10, 'higNumberedMessagesRule')
// all tables that have the companyid column should be filtered
var success = databaseManager.addTableFilterParam('crm', null, 'companyidid', '=', currentcompanyid)
// some filters with in-conditions
var success = databaseManager.addTableFilterParam('crm', 'products', 'productcode', 'in', [120, 144, 200])
var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'in', 'select country code from countries where region = "Europe"')
// you can use modifiers in the operator as well, filter on companies where companyname is null or equals-ignore-case 'servoy'
var success = databaseManager.addTableFilterParam('crm', 'companies', 'companyname', '#^||=', 'servoy')
// the value may be null, this will result in 'column is null' sql condition.
var success = databaseManager.addTableFilterParam('crm', 'companies', 'verified', '=', null)
//if you want to add a filter for a column (created by you) in the i18n table
databaseManager.addTableFilterParam('database', 'your_i18n_table', 'message_variant', 'in', [1, 2])
addTrackingInfo(columnName, value)
String
columnName
The name of the column in the log table, used for tracking info
Object
value
The value to be set when inserting a new row in the log table, for the 'columnName' column
databaseManager.addTrackingInfo('log_column_name', 'trackingInfo')
commitTransaction()
// starts a database transaction
databaseManager.startTransaction()
//Now let users input data
//when data has been entered do a commit or rollback if the data entry is canceld or the the commit did fail.
if (cancel || !databaseManager.commitTransaction())
{
databaseManager.rollbackTransaction();
}
commitTransaction(saveFirst)
Boolean
saveFirst
save edited records to the database first (default true)
// starts a database transaction
databaseManager.startTransaction()
//Now let users input data
//when data has been entered do a commit or rollback if the data entry is canceld or the the commit did fail.
if (cancel || !databaseManager.commitTransaction())
{
databaseManager.rollbackTransaction();
}
commitTransaction(saveFirst, revertSavedRecords)
Boolean
saveFirst
save edited records to the database first (default true)
Boolean
revertSavedRecords
if a commit fails and a rollback is done, the when given false the records are not reverted to the database state (and are in edited records again)
// starts a database transaction
databaseManager.startTransaction()
//Now let users input data
//when data has been entered do a commit or rollback if the data entry is canceld or the the commit did fail.
if (cancel || !databaseManager.commitTransaction())
{
databaseManager.rollbackTransaction();
}
convertFoundSet(foundset, related)
JSFoundSet
foundset
The JSFoundset to convert.
JSFoundSet
related
can be a one-to-many relation object or the name of a one-to-many relation
// Convert in the order form a orders foundset into a orderdetails foundset,
// that has all the orderdetails from all the orders in the foundset.
var convertedFoundSet = databaseManager.convertFoundSet(foundset,order_to_orderdetails);
// or var convertedFoundSet = databaseManager.convertFoundSet(foundset,"order_to_orderdetails");
forms.orderdetails.controller.showRecords(convertedFoundSet);
convertFoundSet(foundset, related)
JSFoundSet
foundset
The JSFoundset to convert.
String
related
the name of a one-to-many relation
// Convert in the order form a orders foundset into a orderdetails foundset,
// that has all the orderdetails from all the orders in the foundset.
var convertedFoundSet = databaseManager.convertFoundSet(foundset,order_to_orderdetails);
// or var convertedFoundSet = databaseManager.convertFoundSet(foundset,"order_to_orderdetails");
forms.orderdetails.controller.showRecords(convertedFoundSet);
convertToDataSet(foundset)
JSFoundSet
foundset
The foundset to be converted.
// converts a foundset pks to a dataset
var dataset = databaseManager.convertToDataSet(foundset);
// converts a foundset to a dataset
//var dataset = databaseManager.convertToDataSet(foundset,['product_id','product_name']);
// converts an object array to a dataset
//var dataset = databaseManager.convertToDataSet(files,['name','path']);
// converts an array to a dataset
//var dataset = databaseManager.convertToDataSet(new Array(1,2,3,4,5,6));
// converts an string list to a dataset
//var dataset = databaseManager.convertToDataSet('4,5,6');
convertToDataSet(foundset, dataproviderNames)
JSFoundSet
foundset
The foundset to be converted.
Array
dataproviderNames
Array with column names.
// converts a foundset pks to a dataset
var dataset = databaseManager.convertToDataSet(foundset);
// converts a foundset to a dataset
//var dataset = databaseManager.convertToDataSet(foundset,['product_id','product_name']);
// converts an object array to a dataset
//var dataset = databaseManager.convertToDataSet(files,['name','path']);
// converts an array to a dataset
//var dataset = databaseManager.convertToDataSet(new Array(1,2,3,4,5,6));
// converts an string list to a dataset
//var dataset = databaseManager.convertToDataSet('4,5,6');
convertToDataSet(values)
Array
values
The values array.
// converts a foundset pks to a dataset
var dataset = databaseManager.convertToDataSet(foundset);
// converts a foundset to a dataset
//var dataset = databaseManager.convertToDataSet(foundset,['product_id','product_name']);
// converts an object array to a dataset
//var dataset = databaseManager.convertToDataSet(files,['name','path']);
// converts an array to a dataset
//var dataset = databaseManager.convertToDataSet(new Array(1,2,3,4,5,6));
// converts an string list to a dataset
//var dataset = databaseManager.convertToDataSet('4,5,6');
convertToDataSet(values, dataproviderNames)
Array
values
The values array.
Array
dataproviderNames
The property names array.
// converts a foundset pks to a dataset
var dataset = databaseManager.convertToDataSet(foundset);
// converts a foundset to a dataset
//var dataset = databaseManager.convertToDataSet(foundset,['product_id','product_name']);
// converts an object array to a dataset
//var dataset = databaseManager.convertToDataSet(files,['name','path']);
// converts an array to a dataset
//var dataset = databaseManager.convertToDataSet(new Array(1,2,3,4,5,6));
// converts an string list to a dataset
//var dataset = databaseManager.convertToDataSet('4,5,6');
convertToDataSet(ids)
String
ids
Concatenated values to be put into dataset.
// converts a foundset pks to a dataset
var dataset = databaseManager.convertToDataSet(foundset);
// converts a foundset to a dataset
//var dataset = databaseManager.convertToDataSet(foundset,['product_id','product_name']);
// converts an object array to a dataset
//var dataset = databaseManager.convertToDataSet(files,['name','path']);
// converts an array to a dataset
//var dataset = databaseManager.convertToDataSet(new Array(1,2,3,4,5,6));
// converts an string list to a dataset
//var dataset = databaseManager.convertToDataSet('4,5,6');
copyMatchingFields(source, destination)
Object
source
The source record or (java/javascript)object to be copied.
JSRecord
destination
The destination record to copy to.
otherfoundset.loadAllRecords();
for( var i = 1 ; i <= foundset.getSize() ; i++ )
{
var srcRecord = foundset.getRecord(i);
var destRecord = otherfoundset.getRecord(i);
if (srcRecord == null || destRecord == null) break;
databaseManager.copyMatchingFields(srcRecord,destRecord,true)
}
//saves any outstanding changes to the dest foundset
databaseManager.saveData();
//copying from a MailMessage JavaScript object
//var _msg = plugins.mail.receiveMail(login, password, true, 0, null, properties);
//if (_msg != null)
//{
// controller.newRecord();
// var srcObject = _msg[0];
// var destRecord = foundset.getSelectedRecord();
// databaseManager.copyMatchingFields(srcObject, destRecord, true);
// databaseManager.saveData();
//}
copyMatchingFields(source, destination, overwrite)
Object
source
The source record or (java/javascript)object to be copied.
JSRecord
destination
The destination record to copy to.
Boolean
overwrite
Boolean values to overwrite all values. If overwrite is false/not provided, then the non empty values are not overwritten in the destination record.
otherfoundset.loadAllRecords();
for( var i = 1 ; i <= foundset.getSize() ; i++ )
{
var srcRecord = foundset.getRecord(i);
var destRecord = otherfoundset.getRecord(i);
if (srcRecord == null || destRecord == null) break;
databaseManager.copyMatchingFields(srcRecord,destRecord,true)
}
//saves any outstanding changes to the dest foundset
databaseManager.saveData();
//copying from a MailMessage JavaScript object
//var _msg = plugins.mail.receiveMail(login, password, true, 0, null, properties);
//if (_msg != null)
//{
// controller.newRecord();
// var srcObject = _msg[0];
// var destRecord = foundset.getSelectedRecord();
// databaseManager.copyMatchingFields(srcObject, destRecord, true);
// databaseManager.saveData();
//}
copyMatchingFields(source, destination, names)
Object
source
The source record or (java/javascript)object to be copied.
JSRecord
destination
The destination record to copy to.
Array
names
The property names that shouldn't be overriden.
otherfoundset.loadAllRecords();
for( var i = 1 ; i <= foundset.getSize() ; i++ )
{
var srcRecord = foundset.getRecord(i);
var destRecord = otherfoundset.getRecord(i);
if (srcRecord == null || destRecord == null) break;
databaseManager.copyMatchingFields(srcRecord,destRecord,true)
}
//saves any outstanding changes to the dest foundset
databaseManager.saveData();
//copying from a MailMessage JavaScript object
//var _msg = plugins.mail.receiveMail(login, password, true, 0, null, properties);
//if (_msg != null)
//{
// controller.newRecord();
// var srcObject = _msg[0];
// var destRecord = foundset.getSelectedRecord();
// databaseManager.copyMatchingFields(srcObject, destRecord, true);
// databaseManager.saveData();
//}
createDataSourceByQuery(name, query, useTableFilters, max_returned_rows, types, pkNames)
String
name
Data source name
QBSelect
query
The query builder to be executed.
Boolean
useTableFilters
use table filters (default true).
Number
max_returned_rows
The maximum number of rows returned by the query.
Array
types
The column types, when null the types are inferred from the query.
Array
pkNames
array of pk names, when null a hidden pk-column will be added
// select customer data for order 1234
var q = datasources.db.example_data.customers.createSelect()
q.result.add(q.columns.customer_id).add(q.columns.city).add(q.columns.country);
q.where.add(q.joins.customers_to_orders.columns.orderid.eq(1234));
var uri = databaseManager.createDataSourceByQuery('mydata', q, true, 999, null, ['customer_id']);
//var uri = databaseManager.createDataSourceByQuery('mydata', q, true, 999, [JSColumn.TEXT, JSColumn.TEXT, JSColumn.TEXT], ['customer_id']);
// the uri can be used to create a form using solution model
var myForm = solutionModel.newForm('newForm', uri, 'myStyleName', false, 800, 600);
myForm.newTextField('city', 140, 20, 140,20);
// the uri can be used to acces a foundset directly
var fs = databaseManager.getFoundSet(uri);
fs.loadAllRecords();
createDataSourceByQuery(name, query, max_returned_rows)
String
name
data source name
QBSelect
query
The query builder to be executed.
Number
max_returned_rows
The maximum number of rows returned by the query.
// select customer data for order 1234
var q = datasources.db.example_data.customers.createSelect()
q.result.add(q.columns.customer_id).add(q.columns.city).add(q.columns.country);
q.where.add(q.joins.customers_to_orders.columns.orderid.eq(1234));
var uri = databaseManager.createDataSourceByQuery('mydata', q, true, 999, null, ['customer_id']);
//var uri = databaseManager.createDataSourceByQuery('mydata', q, true, 999, [JSColumn.TEXT, JSColumn.TEXT, JSColumn.TEXT], ['customer_id']);
// the uri can be used to create a form using solution model
var myForm = solutionModel.newForm('newForm', uri, 'myStyleName', false, 800, 600);
myForm.newTextField('city', 140, 20, 140,20);
// the uri can be used to acces a foundset directly
var fs = databaseManager.getFoundSet(uri);
fs.loadAllRecords();
createDataSourceByQuery(name, query, max_returned_rows, types)
String
name
Data source name
QBSelect
query
The query builder to be executed.
Number
max_returned_rows
The maximum number of rows returned by the query.
Array
types
The column types
// select customer data for order 1234
var q = datasources.db.example_data.customers.createSelect();
q.result.add(q.columns.address).add(q.columns.city).add(q.columns.country);
q.where.add(q.joins.customers_to_orders.columns.orderid.eq(1234));
var uri = databaseManager.createDataSourceByQuery('mydata', q, 999);
//var uri = databaseManager.createDataSourceByQuery('mydata', q, 999, [JSColumn.TEXT, JSColumn.TEXT, JSColumn.TEXT]);
// the uri can be used to create a form using solution model
var myForm = solutionModel.newForm('newForm', uri, 'myStyleName', false, 800, 600);
myForm.newTextField('city', 140, 20, 140,20);
// the uri can be used to acces a foundset directly
var fs = databaseManager.getFoundSet(uri);
fs.loadAllRecords();
createDataSourceByQuery(name, query, max_returned_rows, types, pkNames)
String
name
Data source name
QBSelect
query
The query builder to be executed.
Number
max_returned_rows
The maximum number of rows returned by the query.
Array
types
The column types
Array
pkNames
array of pk names, when null a hidden pk-column will be added
// select customer data for order 1234
var q = datasources.db.example_data.customers.createSelect();
q.result.add(q.columns.customer_id).add(q.columns.city).add(q.columns.country);
q.where.add(q.joins.customers_to_orders.columns.orderid.eq(1234));
var uri = databaseManager.createDataSourceByQuery('mydata', q, 999, null, ['customer_id']);
//var uri = databaseManager.createDataSourceByQuery('mydata', q, 999, [JSColumn.TEXT, JSColumn.TEXT, JSColumn.TEXT], ['customer_id']);
// the uri can be used to create a form using solution model
var myForm = solutionModel.newForm('newForm', uri, 'myStyleName', false, 800, 600);
myForm.newTextField('city', 140, 20, 140,20);
// the uri can be used to acces a foundset directly
var fs = databaseManager.getFoundSet(uri);
fs.loadAllRecords();
createDataSourceByQuery(name, server_name, sql_query, arguments, max_returned_rows)
String
name
data source name
String
server_name
The name of the server where the query should be executed.
String
sql_query
The custom sql.
Array
arguments
Specified arguments or null if there are no arguments.
Number
max_returned_rows
The maximum number of rows returned by the query.
var query = 'select address, city, country from customers';
var uri = databaseManager.createDataSourceByQuery('mydata', 'example_data', query, null, 999);
//var uri = databaseManager.createDataSourceByQuery('mydata', 'example_data', query, null, 999, [JSColumn.TEXT, JSColumn.TEXT, JSColumn.TEXT]);
// the uri can be used to create a form using solution model
var myForm = solutionModel.newForm('newForm', uri, 'myStyleName', false, 800, 600)
myForm.newTextField('city', 140, 20, 140,20)
// the uri can be used to acces a foundset directly
var fs = databaseManager.getFoundSet(uri)
fs.loadAllRecords();
createDataSourceByQuery(name, server_name, sql_query, arguments, max_returned_rows, types)
String
name
data source name
String
server_name
The name of the server where the query should be executed.
String
sql_query
The custom sql.
Array
arguments
Specified arguments or null if there are no arguments.
Number
max_returned_rows
The maximum number of rows returned by the query.
Array
types
The column types
var query = 'select address, city, country from customers';
var uri = databaseManager.createDataSourceByQuery('mydata', 'example_data', query, null, 999);
//var uri = databaseManager.createDataSourceByQuery('mydata', 'example_data', query, null, 999, [JSColumn.TEXT, JSColumn.TEXT, JSColumn.TEXT]);
// the uri can be used to create a form using solution model
var myForm = solutionModel.newForm('newForm', uri, 'myStyleName', false, 800, 600)
myForm.newTextField('city', 140, 20, 140,20)
// the uri can be used to acces a foundset directly
var fs = databaseManager.getFoundSet(uri)
fs.loadAllRecords();
createDataSourceByQuery(name, server_name, sql_query, arguments, max_returned_rows, types, pkNames)
String
name
data source name
String
server_name
The name of the server where the query should be executed.
String
sql_query
The custom sql.
Array
arguments
Specified arguments or null if there are no arguments.
Number
max_returned_rows
The maximum number of rows returned by the query.
Array
types
The column types
Array
pkNames
array of pk names, when null a hidden pk-column will be added
var query = 'select customer_id, address, city, country from customers';
var uri = databaseManager.createDataSourceByQuery('mydata', 'example_data', query, null, 999);
//var uri = databaseManager.createDataSourceByQuery('mydata', 'example_data', query, null, 999, [JSColumn.TEXT, JSColumn.TEXT, JSColumn.TEXT], ['customer_id']);
// the uri can be used to create a form using solution model
var myForm = solutionModel.newForm('newForm', uri, 'myStyleName', false, 800, 600)
myForm.newTextField('city', 140, 20, 140,20)
// the uri can be used to acces a foundset directly
var fs = databaseManager.getFoundSet(uri)
fs.loadAllRecords();
createEmptyDataSet()
// gets an empty dataset with a specifed row and column count
var dataset = databaseManager.createEmptyDataSet(10,10)
// gets an empty dataset with a specifed row count and column array
var dataset2 = databaseManager.createEmptyDataSet(10,new Array ('a','b','c','d'))
createEmptyDataSet(rowCount, columnCount)
Number
rowCount
The number of rows in the DataSet object.
Number
columnCount
Number of columns.
// gets an empty dataset with a specifed row and column count
var dataset = databaseManager.createEmptyDataSet(10,10)
// gets an empty dataset with a specifed row count and column array
var dataset2 = databaseManager.createEmptyDataSet(10,new Array ('a','b','c','d'))
createEmptyDataSet(rowCount, columnNames)
Number
rowCount
Array
columnNames
// gets an empty dataset with a specifed row and column count
var dataset = databaseManager.createEmptyDataSet(10,10)
// gets an empty dataset with a specifed row count and column array
var dataset2 = databaseManager.createEmptyDataSet(10,new Array ('a','b','c','d'))
createSelect(dataSource)
String
dataSource
The data source to build a query for.
/** @type {QBSelect<db:/example_data/book_nodes>} */
var q = databaseManager.createSelect('db:/example_data/book_nodes');
q.result.addPk()
q.where.add(q.columns.label_text.not.isin(null))
datasources.db.example_data.book_nodes.getFoundSet().loadRecords(q)
createSelect(dataSource, tableAlias)
String
dataSource
The data source to build a query for.
String
tableAlias
The alias for the main table.
/** @type {QBSelect<db:/example_data/book_nodes>} */
var q = databaseManager.createSelect('db:/example_data/book_nodes', 'b');
q.result.addPk()
q.where.add(q.columns.label_text.isin('select comment_text from book_text t where t.note_text = ? and t.node_id = b.node_id', ['test']))
datasources.db.example_data.book_nodes.getFoundSet().loadRecords(q)
dataSourceExists(datasource)
datasource
if (!databaseManager.dataSourceExists(dataSource))
{
// does not exist
}
getAutoSave()
//Set autosave, if false then no saves will happen by the ui (not including deletes!). Until you call saveData or setAutoSave(true)
//Rollbacks in mem the records that were edited and not yet saved. Best used in combination with autosave false.
databaseManager.setAutoSave(false)
//Now let users input data
//On save or cancel, when data has been entered:
if (cancel) databaseManager.rollbackEditedRecords()
databaseManager.setAutoSave(true)
getDataModelClonesFrom(serverName)
String
serverName
var serverNames = databaseManager.getDataModelClonesFrom('myServerName');
getDataSetByQuery(query, useTableFilters, max_returned_rows)
QBSelect
query
QBSelect query.
Boolean
useTableFilters
use table filters (default true).
Number
max_returned_rows
The maximum number of rows returned by the query.
// use the query froma foundset and add a condition
/** @type {QBSelect<db:/example_data/orders>} */
var q = foundset.getQuery()
q.where.add(q.joins.orders_to_order_details.columns.discount.eq(2))
var maxReturnedRows = 10;//useful to limit number of rows
var ds = databaseManager.getDataSetByQuery(q, true, maxReturnedRows);
// query: select PK from example.book_nodes where parent = 111 and(note_date is null or note_date > now)
var query = datasources.db.example_data.book_nodes.createSelect().result.addPk().root
query.where.add(query.columns.parent_id.eq(111))
.add(query.or
.add(query.columns.note_date.isNull)
.add(query.columns.note_date.gt(new Date())))
databaseManager.getDataSetByQuery(q, true, max_returned_rows)
getDataSetByQuery(query, max_returned_rows)
QBSelect
query
QBSelect query.
Number
max_returned_rows
The maximum number of rows returned by the query.
// use the query froma foundset and add a condition
/** @type {QBSelect<db:/example_data/orders>} */
var q = foundset.getQuery()
q.where.add(q.joins.orders_to_order_details.columns.discount.eq(2))
var maxReturnedRows = 10;//useful to limit number of rows
var ds = databaseManager.getDataSetByQuery(q, maxReturnedRows);
// query: select PK from example.book_nodes where parent = 111 and(note_date is null or note_date > now)
var query = datasources.db.example_data.book_nodes.createSelect().result.addPk().root
query.where.add(query.columns.parent_id.eq(111))
.add(query.or
.add(query.columns.note_date.isNull)
.add(query.columns.note_date.gt(new Date())))
databaseManager.getDataSetByQuery(q, max_returned_rows)
getDataSetByQuery(server_name, sql_query, arguments, max_returned_rows)
String
server_name
The name of the server where the query should be executed.
String
sql_query
The custom sql.
Array
arguments
Specified arguments or null if there are no arguments.
Number
max_returned_rows
The maximum number of rows returned by the query.
//finds duplicate records in a specified foundset
var vQuery =" SELECT companiesid from companies where company_name IN (SELECT company_name from companies group bycompany_name having count(company_name)>1 )";
var vDataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), vQuery, null, 1000);
controller.loadRecords(vDataset);
var maxReturnedRows = 10;//useful to limit number of rows
var query = 'select c1,c2,c3 from test_table where start_date = ?';//do not use '.' or special chars in names or aliases if you want to access data by name
var args = new Array();
args[0] = order_date //or new Date()
var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), query, args, maxReturnedRows);
// place in label:
// elements.myLabel.text = '<html>'+dataset.getAsHTML()+'</html>';
//example to calc a strange total
global_total = 0;
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
global_total = global_total + dataset.c1 + dataset.getValue(i,3);
}
//example to assign to dataprovider
//employee_salary = dataset.getValue(row,column)
getDataSource(serverName, tableName)
String
serverName
The name of the table's server.
String
tableName
The table's name.
var datasource = databaseManager.getDataSource('example_data', 'categories');
getDataSourceServerName(dataSource)
String
dataSource
The datasource string to get the server name from.
var servername = databaseManager.getDataSourceServerName(datasource);
getDataSourceTableName(dataSource)
String
dataSource
The datasource string to get the tablename from.
var tablename = databaseManager.getDataSourceTableName(datasource);
getDatabaseProductName(serverName)
String
serverName
The specified name of the database server connection.
var databaseProductName = databaseManager.getDatabaseProductName(servername)
getEditedRecords()
//This method can be used to loop through all outstanding changes,
//the application.output line contains all the changed data, their tablename and primary key
var editr = databaseManager.getEditedRecords()
for (x=0;x<editr.length;x++)
{
var ds = editr[x].getChangedData();
var jstable = databaseManager.getTable(editr[x]);
var tableSQLName = jstable.getSQLName();
var pkrec = jstable.getRowIdentifierColumnNames().join(',');
var pkvals = new Array();
for (var j = 0; j < jstable.getRowIdentifierColumnNames().length; j++)
{
pkvals[j] = editr[x][jstable.getRowIdentifierColumnNames()[j]];
}
application.output('Table: '+tableSQLName +', PKs: '+ pkvals.join(',') +' ('+pkrec +')');
// Get a dataset with outstanding changes on a record
for( var i = 1 ; i <= ds.getMaxRowIndex() ; i++ )
{
application.output('Column: '+ ds.getValue(i,1) +', oldValue: '+ ds.getValue(i,2) +', newValue: '+ ds.getValue(i,3));
}
}
//in most cases you will want to set autoSave back on now
databaseManager.setAutoSave(true);
getEditedRecords(foundset)
JSFoundSet
foundset
return edited records in the foundset only.
//This method can be used to loop through all outstanding changes in a foundset,
//the application.output line contains all the changed data, their tablename and primary key
var editr = databaseManager.getEditedRecords(foundset)
for (x=0;x<editr.length;x++)
{
var ds = editr[x].getChangedData();
var jstable = databaseManager.getTable(editr[x]);
var tableSQLName = jstable.getSQLName();
var pkrec = jstable.getRowIdentifierColumnNames().join(',');
var pkvals = new Array();
for (var j = 0; j < jstable.getRowIdentifierColumnNames().length; j++)
{
pkvals[j] = editr[x][jstable.getRowIdentifierColumnNames()[j]];
}
application.output('Table: '+tableSQLName +', PKs: '+ pkvals.join(',') +' ('+pkrec +')');
// Get a dataset with outstanding changes on a record
for( var i = 1 ; i <= ds.getMaxRowIndex() ; i++ )
{
application.output('Column: '+ ds.getValue(i,1) +', oldValue: '+ ds.getValue(i,2) +', newValue: '+ ds.getValue(i,3));
}
}
databaseManager.saveData(foundset);//save all records from foundset
getFailedRecords()
var array = databaseManager.getFailedRecords()
for( var i = 0 ; i < array.length ; i++ )
{
var record = array[i];
application.output(record.exception);
if (record.exception.getErrorCode() == ServoyException.RECORD_VALIDATION_FAILED)
{
// exception thrown in pre-insert/update/delete event method
var thrown = record.exception.getValue()
application.output("Record validation failed: "+thrown)
}
// find out the table of the record (similar to getEditedRecords)
var jstable = databaseManager.getTable(record);
var tableSQLName = jstable.getSQLName();
application.output('Table:'+tableSQLName+' in server:'+jstable.getServerName()+' failed to save.')
}
getFailedRecords(foundset)
JSFoundSet
foundset
return failed records in the foundset only.
var array = databaseManager.getFailedRecords(foundset)
for( var i = 0 ; i < array.length ; i++ )
{
var record = array[i];
application.output(record.exception);
if (record.exception.getErrorCode() == ServoyException.RECORD_VALIDATION_FAILED)
{
// exception thrown in pre-insert/update/delete event method
var thrown = record.exception.getValue()
application.output("Record validation failed: "+thrown)
}
// find out the table of the record (similar to getEditedRecords)
var jstable = databaseManager.getTable(record);
var tableSQLName = jstable.getSQLName();
application.output('Table:'+tableSQLName+' in server:'+jstable.getServerName()+' failed to save.')
}
getFoundSet(query)
QBSelect
query
The query to get the JSFoundset for.
// type the foundset returned from the call with JSDoc, fill in the right server/tablename
/** @type {JSFoundset<db:/servername/tablename>} */
var fs = databaseManager.getFoundSet(controller.getDataSource())
// same as datasources.db.example_data.orders.getFoundSet() or datasources.mem['myds'].getFoundSet()
var ridx = fs.newRecord()
var record = fs.getRecord(ridx)
record.emp_name = 'John'
databaseManager.saveData()
getFoundSet(dataSource)
String
dataSource
The datasource to get a JSFoundset for.
// type the foundset returned from the call with JSDoc, fill in the right server/tablename
/** @type {JSFoundset<db:/servername/tablename>} */
var fs = databaseManager.getFoundSet(controller.getDataSource())
// same as datasources.db.example_data.orders.getFoundSet() or datasources.mem['myds'].getFoundSet()
var ridx = fs.newRecord()
var record = fs.getRecord(ridx)
record.emp_name = 'John'
databaseManager.saveData()
getFoundSet(serverName, tableName)
String
serverName
The servername to get a JSFoundset for.
String
tableName
The tablename for that server
// type the foundset returned from the call with JSDoc, fill in the right server/tablename
/** @type {JSFoundset<db:/servername/tablename>} */
var fs = databaseManager.getFoundSet(controller.getDataSource())
// same as datasources.db.example_data.orders.getFoundSet() or datasources.mem['myds'].getFoundSet()
var ridx = fs.newRecord()
var record = fs.getRecord(ridx)
record.emp_name = 'John'
databaseManager.saveData()
getFoundSetCount(foundset)
JSFoundSet
foundset
The JSFoundset to get the count for.
//return the total number of records in a foundset.
databaseManager.getFoundSetCount(foundset);
getFoundSetUpdater(foundset)
JSFoundSet
foundset
The foundset to update.
//1) update entire foundset
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn('customer_type',1)
fsUpdater.setColumn('my_flag',0)
fsUpdater.performUpdate()
//2) update part of foundset, for example the first 4 row (starts with selected row)
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
fsUpdater.setColumn('customer_type',new Array(1,2,3,4))
fsUpdater.setColumn('my_flag',new Array(1,0,1,0))
fsUpdater.performUpdate()
//3) safely loop through foundset (starts with selected row)
controller.setSelectedIndex(1)
var count = 0
var fsUpdater = databaseManager.getFoundSetUpdater(foundset)
while(fsUpdater.next())
{
fsUpdater.setColumn('my_flag',count++)
}
getNextSequence(dataSource, columnName)
String
dataSource
The datasource that points to the table which has the column with the sequence,
or the name of the server where the table can be found. If the name of the server
is specified, then a second optional parameter specifying the name of the table
must be used. If the datasource is specified, then the name of the table is not needed
as the second argument.
String
columnName
The name of the column that has a sequence defined in its properties.
var seqDataSource = forms.seq_table.controller.getDataSource();
var nextValue = databaseManager.getNextSequence(seqDataSource, 'seq_table_value');
application.output(nextValue);
nextValue = databaseManager.getNextSequence(databaseManager.getDataSourceServerName(seqDataSource), databaseManager.getDataSourceTableName(seqDataSource), 'seq_table_value')
application.output(nextValue);
getSQL(foundsetOrQBSelect)
Object
foundsetOrQBSelect
The JSFoundset or QBSelect to get the sql for.
var sql = databaseManager.getSQL(foundset)
getSQL(foundsetOrQBSelect, includeFilters)
Object
foundsetOrQBSelect
The JSFoundset or QBSelect to get the sql for.
Boolean
includeFilters
include the foundset and table filters.
var sql = databaseManager.getSQL(foundset)
getSQLParameters(foundsetOrQBSelect)
Object
foundsetOrQBSelect
The JSFoundset or QBSelect to get the sql parameters for.
var sqlParameterArray = databaseManager.getSQLParameters(foundset,false)
getSQLParameters(foundsetOrQBSelect, includeFilters)
Object
foundsetOrQBSelect
The JSFoundset or QBSelect to get the sql parameters for.
Boolean
includeFilters
include the parameters for the filters.
var sqlParameterArray = databaseManager.getSQLParameters(foundset,false)
getServerNames()
var array = databaseManager.getServerNames()
getTable(foundset)
JSFoundSet
foundset
The foundset where the JSTable can be get from.
var jstable = databaseManager.getTable(controller.getDataSource());
//var jstable = databaseManager.getTable(foundset);
//var jstable = databaseManager.getTable(record);
//var jstable = databaseManager.getTable(datasource);
var tableSQLName = jstable.getSQLName();
var columnNamesArray = jstable.getColumnNames();
var firstColumnName = columnNamesArray[0];
var jscolumn = jstable.getColumn(firstColumnName);
var columnLength = jscolumn.getLength();
var columnType = jscolumn.getTypeAsString();
var columnSQLName = jscolumn.getSQLName();
var isPrimaryKey = jscolumn.isRowIdentifier();
getTable(record)
JSRecord
record
The record where the table can be get from.
var jstable = databaseManager.getTable(controller.getDataSource());
//var jstable = databaseManager.getTable(foundset);
//var jstable = databaseManager.getTable(record);
//var jstable = databaseManager.getTable(datasource);
var tableSQLName = jstable.getSQLName();
var columnNamesArray = jstable.getColumnNames();
var firstColumnName = columnNamesArray[0];
var jscolumn = jstable.getColumn(firstColumnName);
var columnLength = jscolumn.getLength();
var columnType = jscolumn.getTypeAsString();
var columnSQLName = jscolumn.getSQLName();
var isPrimaryKey = jscolumn.isRowIdentifier();
getTable(dataSource)
String
dataSource
The datasource where the table can be get from.
var jstable = databaseManager.getTable(controller.getDataSource());
//var jstable = databaseManager.getTable(foundset);
//var jstable = databaseManager.getTable(record);
//var jstable = databaseManager.getTable(datasource);
var tableSQLName = jstable.getSQLName();
var columnNamesArray = jstable.getColumnNames();
var firstColumnName = columnNamesArray[0];
var jscolumn = jstable.getColumn(firstColumnName);
var columnLength = jscolumn.getLength();
var columnType = jscolumn.getTypeAsString();
var columnSQLName = jscolumn.getSQLName();
var isPrimaryKey = jscolumn.isRowIdentifier();
getTable(serverName, tableName)
String
serverName
Server name.
String
tableName
Table name.
var jstable = databaseManager.getTable(controller.getDataSource());
//var jstable = databaseManager.getTable(foundset);
//var jstable = databaseManager.getTable(record);
//var jstable = databaseManager.getTable(datasource);
var tableSQLName = jstable.getSQLName();
var columnNamesArray = jstable.getColumnNames();
var firstColumnName = columnNamesArray[0];
var jscolumn = jstable.getColumn(firstColumnName);
var columnLength = jscolumn.getLength();
var columnType = jscolumn.getTypeAsString();
var columnSQLName = jscolumn.getSQLName();
var isPrimaryKey = jscolumn.isRowIdentifier();
getTableCount(dataSource)
Object
dataSource
Data where a server table can be get from. Can be a foundset, a datasource name or a JSTable.
//return the total number of rows in a table.
var count = databaseManager.getTableCount(foundset);
getTableFilterParams(serverName)
String
serverName
The name of the database server connection.
var params = databaseManager.getTableFilterParams(databaseManager.getDataSourceServerName(controller.getDataSource()))
for (var i = 0; params != null && i < params.length; i++)
{
application.output('Table filter on table ' + params[i][0]+ ': '+ params[i][1]+ ' '+params[i][2]+ ' '+params[i][3] +(params[i][4] == null ? ' [no name]' : ' ['+params[i][4]+']'))
}
getTableFilterParams(serverName, filterName)
String
serverName
The name of the database server connection.
String
filterName
The filter name for which to get the array.
var params = databaseManager.getTableFilterParams(databaseManager.getDataSourceServerName(controller.getDataSource()))
for (var i = 0; params != null && i < params.length; i++)
{
application.output('Table filter on table ' + params[i][0]+ ': '+ params[i][1]+ ' '+params[i][2]+ ' '+params[i][3] +(params[i][4] == null ? ' [no name]' : ' ['+params[i][4]+']'))
}
getTableNames(serverName)
String
serverName
The server name to get the table names from.
//return all the table names as array
var tableNamesArray = databaseManager.getTableNames('user_data');
var firstTableName = tableNamesArray[0];
getViewNames(serverName)
String
serverName
The server name to get the view names from.
//return all the view names as array
var viewNamesArray = databaseManager.getViewNames('user_data');
var firstViewName = viewNamesArray[0];
hasLocks()
var hasLocks = databaseManager.hasLocks('mylock')
hasLocks(lockName)
String
lockName
The lock name to check.
var hasLocks = databaseManager.hasLocks('mylock')
hasNewRecords(foundset)
JSFoundSet
foundset
The JSFoundset to test.
var fs = databaseManager.getFoundSet(databaseManager.getDataSourceServerName(controller.getDataSource()),'employees');
databaseManager.startTransaction();
var ridx = fs.newRecord();
var record = fs.getRecord(ridx);
record.emp_name = 'John';
if (databaseManager.hasNewRecords(fs)) {
application.output("new records");
} else {
application.output("no new records");
}
databaseManager.saveData();
databaseManager.commitTransaction();
hasNewRecords(foundset, index)
JSFoundSet
foundset
The JSFoundset to test.
Number
index
The record index in the foundset to test (not specified means has the foundset any new records)
var fs = databaseManager.getFoundSet(databaseManager.getDataSourceServerName(controller.getDataSource()),'employees');
databaseManager.startTransaction();
var ridx = fs.newRecord();
var record = fs.getRecord(ridx);
record.emp_name = 'John';
if (databaseManager.hasNewRecords(fs)) {
application.output("new records");
} else {
application.output("no new records");
}
databaseManager.saveData();
databaseManager.commitTransaction();
hasRecordChanges(foundset)
JSFoundSet
foundset
The JSFoundset to test if it has changes.
if (databaseManager.hasRecordChanges(foundset,2))
{
//do save or something else
}
hasRecordChanges(foundset, index)
JSFoundSet
foundset
The JSFoundset to test if it has changes.
Number
index
The record index in the foundset to test (not specified means has the foundset any changed records)
if (databaseManager.hasRecordChanges(foundset,2))
{
//do save or something else
}
hasRecords(foundset)
JSFoundSet
foundset
A JSFoundset to test.
if (%%elementName%%.hasRecords(orders_to_orderitems))
{
//do work on relatedFoundSet
}
//if (%%elementName%%.hasRecords(foundset.getSelectedRecord(),'orders_to_orderitems.orderitems_to_products'))
//{
// //do work on deeper relatedFoundSet
//}
hasRecords(record, relationString)
JSRecord
record
A JSRecord to test.
String
relationString
The relation name.
if (%%elementName%%.hasRecords(orders_to_orderitems))
{
//do work on relatedFoundSet
}
//if (%%elementName%%.hasRecords(foundset.getSelectedRecord(),'orders_to_orderitems.orderitems_to_products'))
//{
// //do work on deeper relatedFoundSet
//}
hasTransaction()
var hasTransaction = databaseManager.hasTransaction()
mergeRecords(sourceRecord, combinedDestinationRecord)
JSRecord
sourceRecord
The source JSRecord to copy from.
JSRecord
combinedDestinationRecord
The target/destination JSRecord to copy into.
databaseManager.mergeRecords(foundset.getRecord(1),foundset.getRecord(2));
mergeRecords(sourceRecord, combinedDestinationRecord, columnNames)
JSRecord
sourceRecord
The source JSRecord to copy from.
JSRecord
combinedDestinationRecord
The target/destination JSRecord to copy into.
Array
columnNames
The column names array that should be copied.
databaseManager.mergeRecords(foundset.getRecord(1),foundset.getRecord(2));
recalculate(foundsetOrRecord)
Object
foundsetOrRecord
JSFoundset or JSRecord to recalculate.
// recalculate one record from a foundset.
databaseManager.recalculate(foundset.getRecord(1));
// recalculate all records from the foundset.
// please use with care, this can be expensive!
//databaseManager.recalculate(foundset);
refreshRecordFromDatabase(foundset, index)
Object
foundset
The JSFoundset to refresh
Number
index
The index of the JSRecord that must be refreshed (or -1 for all).
//refresh the second record from the foundset.
databaseManager.refreshRecordFromDatabase(foundset,2)
//flushes all records in the related foundset (-1 is or can be an expensive operation)
databaseManager.refreshRecordFromDatabase(order_to_orderdetails,-1);
releaseAllLocks()
databaseManager.releaseAllLocks('mylock')
releaseAllLocks(lockName)
String
lockName
The lock name to release.
databaseManager.releaseAllLocks('mylock')
removeTableFilterParam(serverName, filterName)
String
serverName
The name of the database server connection.
String
filterName
The name of the filter that should be removed.
var success = databaseManager.removeTableFilterParam('admin', 'higNumberedMessagesRule')
revertEditedRecords()
//Set autosave, if false then no saves will happen by the ui (not including deletes!). Until you call saveData or setAutoSave(true)
//reverts in mem the records that were edited and not yet saved. Best used in combination with autosave false.
databaseManager.setAutoSave(false)
//Now let users input data
//On save or cancel, when data has been entered:
if (cancel) databaseManager.revertEditedRecords()
//databaseManager.revertEditedRecords(foundset); // rollback all records from foundset
//databaseManager.revertEditedRecords(foundset.getSelectedRecord()); // rollback only one record
databaseManager.setAutoSave(true)
revertEditedRecords(foundset)
JSFoundSet
foundset
A JSFoundset to revert.
//Set autosave, if false then no saves will happen by the ui (not including deletes!). Until you call saveData or setAutoSave(true)
//reverts in mem the records that were edited and not yet saved. Best used in combination with autosave false.
databaseManager.setAutoSave(false)
//Now let users input data
//On save or cancel, when data has been entered:
if (cancel) databaseManager.revertEditedRecords()
//databaseManager.revertEditedRecords(foundset); // rollback all records from foundset
//databaseManager.revertEditedRecords(foundset.getSelectedRecord()); // rollback only one record
databaseManager.setAutoSave(true)
rollbackTransaction()
// starts a database transaction
databaseManager.startTransaction()
//Now let users input data
//when data has been entered do a commit or rollback if the data entry is canceld or the the commit did fail.
if (cancel || !databaseManager.commitTransaction())
{
databaseManager.rollbackTransaction();
}
rollbackTransaction(rollbackEdited)
Boolean
rollbackEdited
call rollbackEditedRecords() before rolling back the transaction
// starts a database transaction
databaseManager.startTransaction()
//Now let users input data
//when data has been entered do a commit or rollback if the data entry is canceld or the the commit did fail.
if (cancel || !databaseManager.commitTransaction())
{
databaseManager.rollbackTransaction();
}
rollbackTransaction(rollbackEdited, revertSavedRecords)
Boolean
rollbackEdited
call rollbackEditedRecords() before rolling back the transaction
Boolean
revertSavedRecords
if false then all records in the transaction do keep the user input and are back in the edited records list.
Note that if the pks of such a record are no longer used by it's foundset (find/search or load by query or ...) it will just be rolled-back as
it can't be put in editing records list.
// starts a database transaction
databaseManager.startTransaction()
//Now let users input data
//when data has been entered do a commit or rollback if the data entry is canceld or the the commit did fail.
if (cancel || !databaseManager.commitTransaction())
{
databaseManager.rollbackTransaction();
}
saveData()
databaseManager.saveData();
//databaseManager.saveData(foundset.getRecord(1));//save specific record
//databaseManager.saveData(foundset);//save all records from foundset
// when creating many records in a loop do a batch save on an interval as every 10 records (to save on memory and roundtrips)
// for (var recordIndex = 1; recordIndex <= 5000; recordIndex++)
// {
// foundset.newRecord();
// someColumn = recordIndex;
// anotherColumn = "Index is: " + recordIndex;
// if (recordIndex % 10 == 0) databaseManager.saveData();
// }
saveData(foundset)
JSFoundSet
foundset
The JSFoundset to save.
databaseManager.saveData();
//databaseManager.saveData(foundset.getRecord(1));//save specific record
//databaseManager.saveData(foundset);//save all records from foundset
// when creating many records in a loop do a batch save on an interval as every 10 records (to save on memory and roundtrips)
// for (var recordIndex = 1; recordIndex <= 5000; recordIndex++)
// {
// foundset.newRecord();
// someColumn = recordIndex;
// anotherColumn = "Index is: " + recordIndex;
// if (recordIndex % 10 == 0) databaseManager.saveData();
// }
saveData(record)
JSRecord
record
The JSRecord to save.
databaseManager.saveData();
//databaseManager.saveData(foundset.getRecord(1));//save specific record
//databaseManager.saveData(foundset);//save all records from foundset
// when creating many records in a loop do a batch save on an interval as every 10 records (to save on memory and roundtrips)
// for (var recordIndex = 1; recordIndex <= 5000; recordIndex++)
// {
// foundset.newRecord();
// someColumn = recordIndex;
// anotherColumn = "Index is: " + recordIndex;
// if (recordIndex % 10 == 0) databaseManager.saveData();
// }
setAutoSave(autoSave)
Boolean
autoSave
Boolean to enable or disable autosave.
//Rollbacks in mem the records that were edited and not yet saved. Best used in combination with autosave false.
databaseManager.setAutoSave(false)
//Now let users input data
//On save or cancel, when data has been entered:
if (cancel) databaseManager.rollbackEditedRecords()
databaseManager.setAutoSave(true)
setCreateEmptyFormFoundsets()
//this has to be called in the solution open method
databaseManager.setCreateEmptyFormFoundsets()
startTransaction()
// starts a database transaction
databaseManager.startTransaction()
//Now let users input data
//when data has been entered do a commit or rollback if the data entry is canceld or the the commit did fail.
if (cancel || !databaseManager.commitTransaction())
{
databaseManager.rollbackTransaction();
}
switchServer(sourceName, destinationName)
String
sourceName
The name of the source database server connection
String
destinationName
The name of the destination database server connection.
//dynamically changes a server for the entire solution, destination database server must contain the same tables/columns!
//will fail if there is a lock, transaction , if repository_server is used or if destination server is invalid
//in the solution keep using the sourceName every where to reference the server!
var success = databaseManager.switchServer('crm', 'crm1')