Database Manager

(databaseManager)

Return 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,

Property Summary

TypeNameSummary

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

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

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 Summary

TypeNameSummary

Request lock(s) for a foundset, can be a normal or related foundset..

Request lock(s) for a foundset, can be a normal or related foundset..

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

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

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

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

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

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

void

Add tracking info used in the log table..

Returns true if a transaction is committed; rollback if commit fails..

Returns true if a transaction is committed; rollback if commit fails..

Returns true if a transaction is committed; rollback if commit fails..

Creates a foundset that combines all the records of the specified one-to-many relation seen from the given parent/primary foundset..

Creates a foundset that combines all the records of the specified one-to-many relation seen from the given parent/primary foundset..

Converts the argument to a JSDataSet, possible use in controller..

Converts the argument to a JSDataSet, possible use in controller..

Converts the argument to a JSDataSet, possible use in controller..

Converts the argument to a JSDataSet, possible use in controller..

Converts the argument to a JSDataSet, possible use in controller..

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

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

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

Performs a query and saves the result in a datasource..

Performs a query and saves the result in a datasource..

Performs a query and saves the result in a datasource..

Performs a query and saves the result in a datasource..

Performs a sql query on the specified server, saves the the result in a datasource..

Performs a sql query on the specified server, saves the the result in a datasource..

Performs a sql query on the specified server, saves the the result in a datasource..

Returns an empty dataset object..

Returns an empty dataset object..

Returns an empty dataset object..

Create a QueryBuilder object for a datasource..

Create a QueryBuilder object for a datasource with given table alias..

Create a table filter that can be applied to all the foundsets based on a table..

Create a table filter that can be applied to all the foundsets based on a table..

Create a table filter that can be applied to all the foundsets based on a table..

Check wether a data source exists..

void

This method differences for recalculate() that it only works on a datasource rows/records that are loaded in memory..

void

This method differences for recalculate() that it only works on a datasource rows/records that are loaded in memory..

Returns true or false if autosave is enabled or disabled..

Retrieves a list with names of all database servers that have property DataModelCloneFrom equal to the server name parameter..

Performs a sql query with a query builder object..

Performs a sql query with a query builder object..

Performs a sql query on the specified server, returns the result in a dataset..

Returns the datasource corresponding to the given server/table..

Returns the server name from the datasource, or null if not a database datasource..

Returns the table name from the datasource, or null if not a database datasource..

Returns the database product name as supplied by the driver for a server..

Returns an array of edited records with outstanding (unsaved) data..

Returns an array of edited records with outstanding (unsaved) data..

Returns an array of edited records with outstanding (unsaved) data..

Returns an array of edited records with outstanding (unsaved) data for a datasource with a filter..

Returns an array of records that fail after a save..

Returns an array of records that fail after a save..

Returns a foundset object for a specified pk base query..

Returns a foundset object for a specified datasource or server and tablename..

Returns a foundset object for a specified datasource or server and tablename..

Returns the total number of records in a foundset..

Returns a JSFoundsetUpdater object that can be used to update all or a specific number of rows in the specified foundset..

Gets the next sequence for a column which has a sequence defined in its column dataprovider properties..

Returns an array with all the server names used in the solution..

Returns the JSTable object from which more info can be obtained (like columns)..

Returns the JSTable object from which more info can be obtained (like columns)..

Returns the JSTable object from which more info can be obtained (like columns)..

Returns the JSTable object from which more info can be obtained (like columns)..

Returns the total number of records(rows) in a table..

Returns a two dimensional array object containing the table filter information currently applied to the servers tables..

Returns a two dimensional array object containing the table filter information currently applied to the servers tables..

Returns an array of all table names for a specified server..

Returns a ViewFoundSet that was created by getViewFoundSet(name,query,register) with the registerd boolean "true"..

Returns a foundset object for a specified query..

Returns a foundset object for a specified query..

Returns an array of all view names for a specified server..

Returns true if the current client has any or the specified lock(s) acquired..

Returns true if the current client has any or the specified lock(s) acquired..

Returns true if the argument (foundSet / record) has at least one row that was not yet saved in the database..

Returns true if the argument (foundSet / record) has at least one row that was not yet saved in the database..

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

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

Returns true if the (related)foundset exists and has records..

Returns true if the (related)foundset exists and has records..

Returns true if there is an transaction active for this client..

Merge records from the same foundset, updates entire datamodel (via foreign type on columns) with destination record pk, deletes source record..

Merge records from the same foundset, updates entire datamodel (via foreign type on columns) with destination record pk, deletes source record..

void

Can be used to recalculate a specified record or all rows in the specified foundset..

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

Release all current locks the client has (optionally limited to named locks)..

Release all current locks the client has (optionally limited to named locks)..

Free resources allocated for a previously created data source..

Removes a previously defined table filter..

void

Reverts outstanding (not saved) in memory changes from edited records..

void

Reverts outstanding (not saved) in memory changes from edited records..

void

Rollback a transaction started by databaseManager..

void

Rollback a transaction started by databaseManager..

void

Rollback a transaction started by databaseManager..

Saves all outstanding (unsaved) data and exits the current record..

Saves all outstanding (unsaved) data and exits the current record..

Saves all outstanding (unsaved) data and exits the current record..

Saves all outstanding (unsaved) data and exits the current record..

Set autosave, if false then no saves will happen by the ui (not including deletes!)..

void

Turnoff the initial form foundset record loading, set this in the solution open method..

Apply multiple table filters to all the foundsets that are affected by the filters..

void

Start a database transaction..

Switches a named server to another named server with the same datamodel (recommended to be used in an onOpen method for a solution)..

Validates the given record, it runs first the method that is attached to the entity event "onValidate"..

Validates the given record, it runs first the method that is attached to the entity event "onValidate"..

Properties Details

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.

Returns 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

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

Returns Boolean

Sample

databaseManager.nullColumnValidatorEnabled = false;//disable

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

Methods Details

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
var query = datasources.db.admin.messages.createSelect()
query.where.add(query.columns.messagesid.gt(10))
var success = databaseManager.addTableFilterParam(query, '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])
// use "sql:in" in stead of "in" to allow the value to be interpreted as a custom query
var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'sql: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)

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. 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 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
var query = datasources.db.admin.messages.createSelect()
query.where.add(query.columns.messagesid.gt(10))
var success = databaseManager.addTableFilterParam(query, '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])
// use "sql:in" in stead of "in" to allow the value to be interpreted as a custom query
var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'sql: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)

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