Database Manager

(databaseManager)

Overview

The database manager object. Available as "databaseManager." in scripting. It helps working with the database, foundsets and datasets.

Returned Types

SQL_ACTION_TYPES,JSColumn,JSDataSet,JSFoundSetUpdater,JSRecordMarker,JSRecordMarkers,JSRecord,JSFoundSet,JSTable,QBSelect,QBColumn,QBCase,QBCaseWhen,QBColumn,QBColumns,QBCondition,QBColumn,QBGroupBy,QBJoin,QBJoins,QBLogicalCondition,QBLogicalCondition,QBResult,QBColumn,QBSort,QBSorts,QBTableClause,QBPart,QBParameter,QBParameters,QBFunctions,QBAggregates,QUERY_COLUMN_TYPES,JSFoundSet,JSRecord,JSTableFilter,

Properties Summarized

Type	Name	Summary
Boolean	alwaysFollowPkSelection	Enable/disable the foundset behaviour to keep selection to the first row always, even if updates from other clients are received that add new records before the current first record.
Boolean	disableRelatedSiblingsPrefetch	Enable/disable the automatic prefetching of related foundsets for sibling records.
Boolean	nullColumnValidatorEnabled	Enable/disable the default null validator for non null columns, makes it possible to do the checks later on when saving, when for example autosave is disabled.

Methods Summarized

Type	Name	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(query)	Adds a filter based on a query to all the foundsets based on a table.
Boolean	addTableFilterParam(query, filterName)	Adds a filter based on a query to all the foundsets based on a table.
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, columnTypes, 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.
JSTableFilter	createTableFilterParam(query)	Create a table filter that can be applied to all the foundsets based on a table.
JSTableFilter	createTableFilterParam(datasource, dataprovider, operator, value)	Create a table filter that can be applied to all the foundsets based on a table.
JSTableFilter	createTableFilterParam(serverName, tableName, dataprovider, operator, value)	Create a table filter that can be applied to all the foundsets based on a table.
Boolean	dataSourceExists(dataSource)	Check wether a data source exists.
void	flushCalculations(datasource, onlyUnstored)	This method differences for recalculate() that it only works on a datasource rows/records that are loaded in memory.
void	flushCalculations(datasource, onlyUnstored, calcnames)	This method differences for recalculate() that it only works on a datasource rows/records that are loaded in memory.
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	getEditedRecords(datasource)	Returns an array of edited records with outstanding (unsaved) data.
Array	getEditedRecords(datasource, filter)	Returns an array of edited records with outstanding (unsaved) data for a datasource with a filter.
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 base 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.
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.
JSFoundSet	getViewFoundSet(name)	Returns a ViewFoundSet that was created by getViewFoundSet(name,query,register) with the registerd boolean "true".
JSFoundSet	getViewFoundSet(name, query)	Returns a foundset object for a specified query.
JSFoundSet	getViewFoundSet(name, query, register)	Returns a foundset object for a specified query.
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 or is new unsaved record.
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 or is new unsaved record.
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	removeDataSource(uri)	Free resources allocated for a previously created data source.
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	saveData(records)	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.
Boolean	setTableFilters(filterName, tableFilters)	Apply multiple table filters to all the foundsets that are affected by the filters.
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).
JSRecordMarkers	validate(record)	Validates the given record, it runs first the method that is attached to the entity event "onValidate".
JSRecordMarkers	validate(record, customObject)	Validates the given record, it runs first the method that is attached to the entity event "onValidate".

Properties Detailed

alwaysFollowPkSelection

Enable/disable the foundset behaviour to keep selection to the first row always, even if updates from other clients are received that add new records before the current first record.

If set to false [default], a foundset with selection on first record will keep the selected index to 1, but may change the selected record when a new record is received from another client. If set to true, the selected index may change but the selected record will be kept if possible.

Type Boolean

Sample

databaseManager.alwaysFollowPkSelection = true; // enable

// test if enabled
if(databaseManager.alwaysFollowPkSelection) application.output('alwaysFollowPkSelection enabled')

disableRelatedSiblingsPrefetch

Enable/disable the automatic prefetching of related foundsets for sibling records.

For example, when orders from a record in a customer foundset are retrieved, already the orders of a few sibling records are also prefetched. By default this prefetch is enabled for SmartClient but is disabled for all serverbased clients like NGClient and HeadlessClient. Because server based client are close enough to the database that they can fetch the siblings themselfs

Type Boolean

Sample

databaseManager.disableRelatedSiblingsPrefetch = false; // enable the siblings prefetch

// test if enabled
if(databaseManager.disableRelatedSiblingsPrefetch) application.output('prefetching of sibling related foundsets is enabled')

nullColumnValidatorEnabled

Enable/disable the default null validator for non null columns, makes it possible to do the checks later on when saving, when for example autosave is disabled.

Type Boolean

Sample

databaseManager.nullColumnValidatorEnabled = false;//disable

//test if enabled
if(databaseManager.nullColumnValidatorEnabled) application.output('null validation enabled')

Methods Detailed

acquireLock(foundset, recordIndex)

Request lock(s) for a foundset, can be a normal or related foundset.
The record_index can be -1 to lock all rows, 0 to lock the current row, or a specific row of > 0
Optionally name the lock(s) so that it can be referenced it in releaseAllLocks()

By default this call doesn't try to lock records in the database itself. But the locks are tracked  in the Servoy Server itself.
If you need database locking because of others applications that can also read the table or you use the Broadcaster plugin for more then 1 servoy server on the same database,
you need to set the property 'servoy.record.lock.lockInDB' in the servoy.properties file to true. This will try to do a 'select for update no wait' on databases that supports this.
This can only be used together with a transaction, so before you aquire the lock a transaction must be started so the database lock is held on to the transaction connection.

Do not change the record data before that, because aquirelock will make sure with a select from the database that it really has the latest data.
If there are changes to columns that you changed before calling aquireLock these changes will be reverted, so you don't change something again that you didn't see really the value of first.

returns true if the lock could be acquired.

Parameters

• JSFoundSet foundset The JSFoundset to get the lock for

• Number recordIndex The record index which should be locked.

Returns: Boolean true if the lock could be acquired.

Sample

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

Request lock(s) for a foundset, can be a normal or related foundset.
The record_index can be -1 to lock all rows, 0 to lock the current row, or a specific row of > 0
Optionally name the lock(s) so that it can be referenced it in releaseAllLocks()

By default this call doesn't try to lock records in the database itself. But the locks are tracked  in the Servoy Server itself.
If you need database locking because of others applications that can also read the table or you use the Broadcaster plugin for more then 1 servoy server on the same database,
you need to set the property 'servoy.record.lock.lockInDB' in the servoy.properties file to true. This will try to do a 'select for update no wait' on databases that supports this.
This can only be used together with a transaction, so before you aquire the lock a transaction must be started so the database lock is held on to the transaction connection.

Do not change the record data before that, because aquirelock will make sure with a select from the database that it really has the latest data.
If there are changes to columns that you changed before calling aquireLock these changes will be reverted, so you don't change something again that you didn't see really the value of first.

returns true if the lock could be acquired.

Parameters

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

Returns: Boolean true if the lock could be acquired.

Sample

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

Adds a filter based on a query to all the foundsets based on a table.

Filters on tables touched in the query will not be applied to the query filter. For example, when a table filter exists on the order_details table, a query filter with a join from orders to order_details will be applied to queries on the orders table, but the filter condition on the orders_details table will not be included.

returns true if the table filter could be applied.

Parameters

• QBSelect query condition to filter on.

Returns: Boolean true if the table filter could be applied.

Sample

// Best way to call this in a global solution startup method, but filters may be added/removed at any time.
// Note that

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
   query.or.add(
            query.columns.shipcity.eq('Amersfoort'))
   .add(    query.columns.shipcity.eq('Amsterdam')));

var success = databaseManager.addTableFilterParam(query, 'cityFilter')

addTableFilterParam(query, filterName)

Adds a filter based on a query to all the foundsets based on a table.

Filters on tables touched in the query will not be applied to the query filter. For example, when a table filter exists on the order_details table, a query filter with a join from orders to order_details will be applied to queries on the orders table, but the filter condition on the orders_details table will not be included.

returns true if the table filter could be applied.

Parameters

• QBSelect query condition to filter on.

• String filterName The specified name of the database table filter.

Returns: Boolean true if the table filter could be applied.

Sample

// Best way to call this in a global solution startup method, but filters may be added/removed at any time.
// Note that

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
   query.or.add(
            query.columns.shipcity.eq('Amersfoort'))
   .add(    query.columns.shipcity.eq('Amsterdam')));

var success = databaseManager.addTableFilterParam(query, 'cityFilter')

addTableFilterParam(datasource, dataprovider, operator, value)

Adds a filter to all the foundsets based on a table. Note: if null is provided as the tablename the filter will be applied on all tables with the dataprovider name. A dataprovider can have multiple filters defined, they will all be applied. returns true if the table filter could be applied.

Parameters

• 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), prefix with "sql:" to allow the value to be interpreted as a custom query.

• Object value The specified filter value.

Returns: Boolean true if the table filter could be applied.

Sample

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

// a filter can be created based on a query