Skip to main content
Version: 2025.2

db

Methods​

array​

array(pType, pSQL, pAlias?, pMaxRows?, pTimeout?): string[]

Executes a SQL query.

Parameters​

pType
number

The return type of the SQL query. If you omit this parameter, the contents of the first column in the first row is returned as a string. If you specify db.ROW, the contents of the first row is returned as a one-dimensional array; db.COLUMN returns the contents of the first column as a one-dimensional array.

pSQL
any

If entered as a string: The SQL query as text. If entered as an object: A query in the following format: [sqlString, [[value1, sqlType1], [value2, sqlType2], ...]. Optionally as an array of queries ([ [sql, alias, etc.], [sql, alias, etc.]].

pAlias?
string | number | boolean

The alias under which the database query is to be run.

pMaxRows?
number

The maximum number of rows to be returned (ORDER BY must be defined in the SQL statement!). If you specify maxRows, this restriction might apply to all tables indicated in the JOIN part of the query. If you do not want to restrict this value, enter 0.

pTimeout?
number

Returns​

string[]

Depending on the type, a string or array containing the result set. The result can never be null. All values are strings, NULL is returning an empty string.

Throws​

May throw an exception.

Example​

let data = db.array(db.COLUMN, "select RELATIONID from ORG join RELATION on ORGID = ORG_ID where PERS_ID is not null and ORG_ID = '33'", "Data_alias", db.DB_EOF, 10 * datetime.ONE_MINUTE);
logging.log("ids: " + data.join(", "));

arrayPage​

arrayPage(pType, pSQL, pAlias, pStartIndex, pRowCount, pTimeout?): string[]

Runs an SQL query.

Parameters​

pType
number

The return type of the SQL query. If you omit this parameter, the contents of the first column in the first row is returned as a string. If you specify db.ROW, the contents of the first row is returned as a one-dimensional array; db.COLUMN returns the contents of the first column as a one-dimensional array.

pSQL
any

If entered as a string: The SQL query as text. If entered as an object: A query in the following format: [sqlString, [[value1, sqlType1], [value2, sqlType2], ...]. Optionally as an array of queries ([ [sql, alias, etc.], [sql, alias, etc.]].

pAlias
string | number | boolean

The alias under which the database query is to be run.

pStartIndex
number

The starting index of the result set to be returned.

pRowCount
number

Is required for pagination. Contains the number of rows to be included in the result set. If the number of results is less than rowCount, the system will return only the records that were found. It is also possible to set RowCount by using db.DB_EOF. This means that all records starting with StartIndex will be returned.

pTimeout?
number

Returns​

string[]

Depending on the type, a string or array containing the result set. The result can never be null. All values are strings, NULL is returning an empty string.

Throws​

May throw an exception.

Example​

let data = db.arrayPage(db.COLUMN, "select RELATIONID from ORG join RELATION on ORGID = ORG_ID where PERS_ID is not null and ORG_ID = '100'", "Data_alias", 0 , 20, 10 * datetime.ONE_MINUTE);
logging.log("ids: " + data.join(", "));

cell​

cell(pSQL, pAlias?): string

Executes a SQL query. Returns the value in the first column of the first row as String.

Parameters​

pSQL
any

The SQL query to be run: If entered as a string: The SQL query as text. If entered as an object: A query in the following format: [sqlString, [[value1, sqlType1], [value2, sqlType2], ...]. Optionally as an array of queries ([ [sql, alias, etc.], [sql, alias, etc.] ].

pAlias?
string | number | boolean

The alias under which the database query is to be run.

Returns​

string

The value in the first column of the first row. Never 'null', empty string if there is no result.

Throws​

May throw an exception.

Example​

let employeeCount = db.cell("select count(*) from EMPLOYEE where STATUS = '1'", "Data_alias");
logging.log(employeeCount);

createEmptyTable​

createEmptyTable(pColumnCount): any

Creates an empty table with the indicated number of columns (for a table component).

Parameters​

pColumnCount
number

The number of columns.

Returns​

any

The empty table.

Throws​

May throw an exception.

Example​

let table = db.createEmptyTable(6);

deleteBinary​

deleteBinary(pId, pAlias): void

Deletes an entry from the binary database.

Parameters​

pId
string | number | boolean

The ID of the entry in the binary database.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

void

Throws​

May throw an exception.

Example​

db.deleteBinary(vars.getString("$local.uid"), db.getCurrentAlias());

deleteData​

deleteData(pTableName, pCondition, pAlias?, pTimeout?): number

Deletes data from the database. This process will be logged by the audit layer.

Parameters​

pTableName
string | number | boolean

The table where the data will be deleted.

pCondition
any

The SQL condition used to determine the records to be deleted. It can be entered in the following format: [sql, [value1, value2, valuen] ].

pAlias?
string | number | boolean

The database alias.

pTimeout?
number

Returns​

number

The number of records that were deleted.

Throws​

May throw an exception.

Example​

db.deleteData("ATTR", " ATTRID = '404'", "Data_alias", 10 * datetime.ONE_MINUTE);

deletes​

deletes(pStatements, pAlias?, pTimeout?): number

Deletes data from the database.

Parameters​

pStatements
any[]

The statements to be executed: An array of arrays as elements. The "sub" arrays are structured as follows [0]: Tablename (String), [1]: Condition (String).

pAlias?
string | number | boolean

The database alias

pTimeout?
number

Returns​

number

The number of records that were deleted.

Throws​

May throw an exception.

Example​

let toDel = [];
toDel.push(["QUESTION", "QUESTIONNAIRE_ID = '565'"]);
db.deletes(toDel, db.getCurrentAlias(), 10 * datetime.ONE_SECOND);

execute​

execute(pStatements, pAlias?, pTimeout?): number

Executes SQL queries of various types in the database.

Parameters​

pStatements
any[]

The statements to be performed, of type INSERT, UPDATE or DELETE. An array of arrays as elements. The "sub" arrays are identically structured to the db.inserts(object), db.deletes(object), db.updates(object) methods. The type of SQL statement is determined from the length of the array used.

pAlias?
string | number | boolean

The database alias.

pTimeout?
number

Returns​

number

The number of actions that were performed.

Throws​

May throw an exception.

Example​

let statements = [];
//Insert
statements.push([
"ORG",
["ORGID", "DATE_NEW", "USER_NEW", "ORGNAME"],
null,
[util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH"]
]);

//Update
statements.push([
"ORG",
["DATE_EDIT", "USER_EDIT", "ORGNAME"],
null,
[vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH"],
"ORGID = '1'"
]);

// Delete
statements.push(["ORG", "ORGID = '1'"]);
db.execute(statements);

exportData​

exportData(pTitle, pFile, pStatement, pFieldDivider, pFieldLimiter, pSentenceDivider, pWithFieldIdentifier, pHandleOldFile, pAlias, pWaitFor): boolean

This method can be used to export data from a database to a file on the client.

Parameters​

pTitle
string | number | boolean

The title of the export (only used for internal purposes; required for error handling).

pFile
string | number | boolean

The file to be exported (on the client).

pStatement
any

The SQL statement that selects the records to be exported.

pFieldDivider
string | number | boolean

The field separator.

pFieldLimiter
string | number | boolean

The field delimiter.

pSentenceDivider
string | number | boolean

The record separator.

pWithFieldIdentifier
boolean

Is 'true' if a field separator was specified.

pHandleOldFile
number

Constant that describes how to handle an existing file. For the possible values, please refer to util.EXPORT_*.

pAlias
string | number | boolean

The alias of the database where the statement is to be executed. If 'null' is passed, the internal alias of the environment (frame, process) will be used.

pWaitFor
boolean

'true' if other actions should wait until the export has been performed; otherwise 'false'.

Returns​

boolean

'true' if the export was successful; otherwise 'false'.

Example​

let statement = "select RELATIONID, FIRSTNAME, LASTNAME, ORGNAME " + "from RELATION join ORG on ORG_ID = ORGID join PERS on PERS_ID = PERSID";
db.exportData("ContactExport", "C:\\temp\\ContactExport.csv", statement, ";", "'", "\n", true, util.EXPORT_OVERWRITE, "Data_alias", false);

fastExportData​

fastExportData(pTitle, pFile, pStatement, pFieldDivider, pFieldLimiter, pSentenceDivider, pWithFieldIdentifier, pHandleOldFile, pAlias, pWaitFor, pCharset, pLocale, pEscapeSymbol, pEscapeStrategy, pOutputFormat): boolean

This method can be used to export data from a database to a file on the server.

The time zone for the export is the time zone of the client executing the process; for server processes, it is UTC.

Parameters​

pTitle
string | number | boolean

The title of the export (only used for internal purposes; required for error handling).

pFile
string | number | boolean

The file to be exported (on the server).

pStatement
any

The SQL statement that selects the records to be exported.

pFieldDivider
string | number | boolean

The field separator.

pFieldLimiter
string | number | boolean

The field delimiter.

pSentenceDivider
string | number | boolean

The record separator.

pWithFieldIdentifier
boolean

Is 'true' if a field separator was specified.

pHandleOldFile
number

Constant that describes how to handle an existing file. For the possible values, please refer to util.EXPORT_*.

pAlias
string | number | boolean

The alias of the database where the statement is to be executed. If 'null' is passed, the internal alias of the environment (frame, process) will be used.

pWaitFor
boolean

'true' if other actions should wait until the export has been performed; otherwise 'false'.

pCharset
string | number | boolean

This charset is used for writing data to the hard disk; if 'null', the default charset of the system will be used ("UTF-8").

pLocale
string | number | boolean

Locale to be used for number formatting, by default, US formatting is used. Example: "en_GB").

pEscapeSymbol
string | number | boolean

Symbol to be used for escaping field delimiters in strings with some escape strategies (pEscapeStrategy).

pEscapeStrategy
number

Specifies the escape strategy to be used, see util.ESCAPEMODE_*

pOutputFormat
string[]

Here, you can define a date / number format for each column. Otherwise, the default format will be used.

Returns​

boolean

'true' if the export was successful; otherwise 'false'.

Example​

let sql = "select * from RELATION";
db.fastExportData("ContactExport", "c:/contactExport.txt", sql, ";", "'", "\n", true, util.EXPORT_OVERWRITE, "Data_alias", true, "UTF-8", "de_DE", "", util.ESCAPEMODE_NOTHING, null);

getBinaryContent​

getBinaryContent(pId, pAlias): string

Returns the contents of a binary file.

Parameters​

pId
string | number | boolean

The ID of the entry in the binary database.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

string

The binary data, Base64-encoded.

Throws​

May throw an exception.

Example​

let currentAlias = db.getCurrentAlias();

// get the metadata of the file of the DB-Picture Container
let metadata = db.getBinaryMetadata("PRODUCT", "Picture", pProductID, false, currentAlias);

// get the binary content as base64 String
let productPicture = db.getBinaryContent(metadata[0].id, currAlias);

getBinaryContents​

getBinaryContents(pIds, pAlias): string[]

Returns the contents of multiple binaries.

Parameters​

pIds
string[]

The IDs of the entries in the binary database.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

string[]

The binary data, Base64-encoded, in a String array.

Throws​

May throw an exception.

Example​

let data = db.getBinaryContents(['5ea59359-5d8e-4a03-bf48-dd5433405578', '75459359-5e8d-4c03-ad48-bb5435605578'], db.getCurrentAlias());
for(let binaryContent of data)
{
// Further processing
}

getBinaryCount​

getBinaryCount(pAssignmentTable, pAssignmentName, pAssignmentRowId, pAlias, pKeyword?): number

Returns the amount of blob entries that match the given search parameters.

Parameters​

pAssignmentTable
string | number | boolean

The table of the LOB assignment

pAssignmentName
string | number | boolean

The name of the assignment

pAssignmentRowId
string | number | boolean

The IDs of the records whose assignments are requested

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

pKeyword?
string | number | boolean

The keyword that is used to filter the content

Returns​

number

The number of binaries

Throws​

May throw an exception.

getBinaryHistory​

getBinaryHistory(pId, pAlias): Map<any, any>[]

Returns history information related to a BLOB entry. Each history element exists of a number of elements: All metadata elements, excluding a preview. db.BINARY_HISTORYID the ID of the history entry db.BINARY_HOSTORY_DATE the ID of creaton of the history entry

Parameters​

pId
string | number | boolean

The ID of the entry in the binary database.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

Map<any, any>[]

an array of history data

Throws​

May throw an exception.

getBinaryMetadata​

getBinaryMetadata(pAssignmentTable, pAssignmentName, pAssignmentRowId, pIncludePreview, pAlias, pKeyword?): Map<any, any>[]

Returns the meta data related to a BLOB entry.

Parameters​

pAssignmentTable
string | number | boolean

The table of the LOB assignment (tablename).

pAssignmentName
string | number | boolean

The name of the assignment (containername).

pAssignmentRowId
string | number | boolean

The ID of the record whose assignments are required and will be retrieved (row_id).

pIncludePreview
boolean

If 'true', a preview will also be retrieved.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

pKeyword?
string | number | boolean

The keyword, which is used to filter

Returns​

Map<any, any>[]

Object, which contains the meta information as a map

Throws​

May throw an exception.

Example​

let metadata = db.getBinaryMetadata("ORG", "DOCUMENT", vars.get("$field.ORGID"), false, null, "picture");
for(metadataRow of metadata)
{
db.deleteBinary(metadataRow.id, null);
}

getBinaryMetadataForIds​

getBinaryMetadataForIds(pIds, pIncludePreview, pAlias): Map<any, any>[]

Returns the meta data related to multiple BLOB entries.

Parameters​

pIds
string[]

IDs of the binary entries.

pIncludePreview
boolean

Set to true to include preview data

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

Map<any, any>[]

array of metadata entries

Throws​

May throw an exception.

Example​

let binaryData = db.getBinaryMetadataForIds(['bca96d2e-bbfd-495a-be0d-af61ec9168d1', 'b996440e-143a-4c6a-a9e8-923c1a0b4021'], false, db.getCurrentAlias());

getColumnInfos​

getColumnInfos(pTable, pColumnNames, pAlias?): Map<any, any>[]

Returns the meta data of the columns you passed.

Parameters​

pTable
string | number | boolean

The table for which you want to determine the column types.

pColumnNames
string[]

The column names as a string array.

pAlias?
string | number | boolean

The alias that contains the table.

Returns​

Map<any, any>[]

The desired meta data. See the constants db.COLUMN_*.

Throws​

May throw an exception.

Example​

logging.log(db.getColumnInfos("ORG", ["ORGNAME"], db.getCurrentAlias()).join("\n"));
// returns the following result
//{columnType=12,
//nullable=true,
//indexName=null,
//precision=250,
//sortSequence=null,
//isUnique=false,
//scale=0,
//readOnly=true,
//columnTypeName=VARCHAR,
//label=ORGNAME,
//searchable=true,
//columnName=ORGNAME}

getColumns​

getColumns(pTable, pAlias?): string[]

Returns the column names of a table in a database.

Parameters​

pTable
string | number | boolean

The table for which you want to retrieve the column names.

pAlias?
string | number | boolean

The alias that contains the table.

Returns​

string[]

The column names as a one-dimensional string array or an empty array if the table has no columns.

Throws​

May throw an exception.

Example​

let cols = db.getColumns("ORG", "Data_alias");
logging.log(cols.join("\n"));

getColumnTypes​

getColumnTypes(pTable, pColumnNames, pAlias?): number[]

Returns the data types of the columns you passed. The information retrieved here will be cached and can thus also be used for prepared statements. This means that, in case a column type is changed, a server restart will be required.

Parameters​

pTable
string | number | boolean

The table for which you want to determine the column types.

pColumnNames
string[]

The column names as a string array.

pAlias?
string | number | boolean

The alias that contains the table.

Returns​

number[]

The column types.

Throws​

May throw an exception.

Example​

let cols = ["STATUS", "DATE_EDIT", "USER_EDIT"];
let types = db.getColumnTypes("RELATION", cols, db.getCurrentAlias());

getCurrentAlias​

getCurrentAlias(): string

Returns the current alias.

Returns​

string

The current alias; 'null' if no alias exists

Example​

let alias = db.getCurrentAlias();

getDatabaseType​

getDatabaseType(pAlias): number

Returns the database type of the alias you passed.

Parameters​

pAlias
string | number | boolean

The alias whose type will be determined. The result will be an integer, its value corresponds to a database type constant. For details on the alias types, please refer to the Database Type Constants (db.DBTYPE_*)

Returns​

number

The database type.

Throws​

May throw an exception.

Example​

let dbType = db.getDatabaseType("Data_alias");

getNewID​

getNewID(pTable, pIDColumn, pAlias?): string

Generates a new ID based on the information stored in the ASYS_SEQUENCES system table.

Parameters​

pTable
string | number | boolean

The table for which you want to generate the new ID.

pIDColumn
string | number | boolean

ID column of the table

pAlias?
string | number | boolean

The alias that contains the table.

Returns​

string

The created ID.

Throws​

AditoException

Example​

let id = db.getNewID("ORG", "ORGID", db.getCurrentAlias());
logging.log(id);

getRowCount​

getRowCount(pTableName, pColumns, pColumnTypes, pValues, pAlias?, pTimeout?): number

This method determines the number of rows in the table that have the properties you passed here.

Parameters​

pTableName
string | number | boolean

The table to be checked.

pColumns
string[]

The columns to be checked.

pColumnTypes
number[]

The types of the columns to be checked. This is no longer necessary, set it to null.

pValues
string[]

The values the columns should have.

pAlias?
string | number | boolean

The database alias

pTimeout?
number

Returns​

number

The number of records.

Throws​

May throw an exception.

Example​

let cols = ["ORGNAME", "ORGID"];
let values = ["ADITO", "12345"];
let count = db.getRowCount("ORG", cols, null, values, "Data_alias", 10 * datetime.ONE_SECOND);
logging.log(count);

getTables​

getTables(pAlias): string[]

Returns the names of all tables in a database.

Parameters​

pAlias
string | number | boolean

The alias under which to search for tables.

Returns​

string[]

The table names as a one-dimensional string array or an empty array if the alias has no tables.

Throws​

May throw an exception.

Example​

let tableNames = db.getTables("Data_alias");
logging.log("tables: " + tableNames.join(", "));

insertBinary​

insertBinary(pAssignmentTable, pAssignmentName, pAssignmentRowId, pParentId, pBase64, pFileName, pDescription, pKeyword, pAlias, pId?): string

Adds binary data.

Parameters​

pAssignmentTable
string | number | boolean

The table of the LOB assignment.

pAssignmentName
string | number | boolean

The name of the assignment.

pAssignmentRowId
string | number | boolean

The ID of the record whose assignments are required and will be retrieved.

pParentId
string | number | boolean

The parent ID of the record; 'null' for none.

pBase64
string | number | boolean

The data, Base64-encoded.

pFileName
string | number | boolean

The file name associated with the record.

pDescription
string | number | boolean

The description.

pKeyword
string | number | boolean

The keyword.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

pId?
string | number | boolean

A new ID for the entry in the binary store. May be changed by the store.

Returns​

string

The ID of the binary record

Throws​

May throw an exception.

Example​

let rowdata = vars.get("$local.rowdata");
let tableName = vars.getString("$param.AssignmentTable_param");
let container = vars.getString("$param.AssignmentContainer_param");
let rowId = vars.getString("$param.RowId_param");
let binaryId = util.getNewUUID();

// insert binary, with its according assignment, data and filename
db.insertBinary(tableName, container, rowId, null, rowdata.BINDATA, rowdata.FILENAME, "", "", null, binaryId);

insertData​

insertData(pTableName, pColumns, pColumnTypes, pValues, pAlias?, pTimeout?): number

Inserts a new row into a database table.

Parameters​

pTableName
string | number | boolean

The name of the table where you want to insert the contents.

pColumns
string[]

The columns where you want to insert the contents.

pColumnTypes
number[]

The types of the specified columns ("SQLTYPES.*"). This is no longer necessary, set it to null.

pValues
string[]

The values to be inserted.

pAlias?
string | number | boolean

The database alias.

pTimeout?
number

Returns​

number

The number of records inserted.

Throws​

May throw an exception.

Example​

let cols = ["KNOWLEDGE_NOTIFICATIONID", "DATE_NEW", "USER_NEW"];
let vals = [util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user")];
db.insertData("KNOWLEDGE_NOTIFICATION", cols, null, vals, db.getCurrentAlias(), 10 * datetime.ONE_SECOND);

inserts​

inserts(pStatements, pAlias?, pTimeout?): number

Inserts data into the database.

Parameters​

pStatements
any[]

The statements to be executed: An array of arrays as elements. The "sub" arrays are structured as follows [0]: Tablename (String), [1]: Columns: (String[]), [2]: ColumnTypes: (int[]), [3]: Values (String[])

pAlias?
string | number | boolean

The database alias

pTimeout?
number

Returns​

number

The number of records inserted.

Throws​

May throw an exception.

Example​

let cols = ["ORGID", "DATE_NEW", "USER_NEW", "ORGNAME"];
let vals1 = [util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH"];
let vals2 = [util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH Geisenhausen"];

let inserts = [];
inserts.push(["ORG", cols, null, vals1]);
inserts.push(["ORG", cols, null, vals2]);

let count = db.inserts(inserts, "Data_alias", 10 * datetime.ONE_MINUTE);

quote​

quote(pValue, pAlias?): string

Quotes a value entered by the user so that it can be used directly in a statement.

Parameters​

pValue
string | number | boolean

The string to be quoted.

pAlias?
string | number | boolean

The alias for which the quoted value will be used.

Returns​

string

The quoted value which makes SQL injection more difficult.

Throws​

May throw an exception.

Example​

let name = vars.getString("$field.ORGNAME");
name = db.quote(name, db.getCurrentAlias());

runStatement​

runStatement(pSQL, pAlias?): number

Runs an SQL statement.

Parameters​

pSQL
any

Runs an SQL statement.

pAlias?
string | number | boolean

The alias for which the quoted value will be used.

Returns​

number

The number of columns that were updated; -1 for unknown.

Throws​

May throw an exception.

Example​

let count = db.runStatement("update RELATION set STATUS = 1 where STATUS = 0", "Data_alias");
logging.log(count);

table​

table(pSQL, pAlias?, pMaxRows?, pTimeout?): string[][]

Executes a SQL query.

Parameters​

pSQL
any

The query to be run. If entered as a string: The SQL query as text. If entered as an object: A query in the following format: [sqlString, [[value1, sqlType1], [value2, sqlType2], ...]. Optionally as an array of queries ([sql, alias, etc.] [sql, alias, etc.]].

pAlias?
string | number | boolean

The alias under which the database query is to be run.

pMaxRows?
number

The maximum number of rows to be returned (ORDER BY must be defined in the SQL statement!). If you specify maxRows, this restriction might apply to all tables indicated in the JOIN part of the query. If you do not want to restrict this value, enter 0.

pTimeout?
number

Specifies the period, in milliseconds, after which the query will be aborted. This timeout value overwrites DBSyncTimeout for the SQL command to be executed (this value was predefined in the server defaults). If you omit this parameter, DBSyncTimeout will be used.

Returns​

string[][]

The result as a two-dimensional string array. The first dimension indicates the row, the second one the column. The result can never be null.

Throws​

May throw an exception.

Example​

let data = db.table("select * from ORG", "Data_alias", db.DB_EOF, 10 * date.ONE_MINUTE);
logging.log("Count: " + data.length);

tablePage​

tablePage(pSQL, pAlias, pStartIndex, pRowCount, pTimeout?): string[][]

Runs a Select query.

Parameters​

pSQL
any

The query to be run. If entered as a string: The SQL query as text. If entered as an object: A query in the following format: [sqlString, [[value1, sqlType1], [value2, sqlType2], ...]. Optionally as an array of queries ([sql, alias, etc.] [sql, alias, etc.]].

pAlias
string | number | boolean

The alias of where to execute the SQL query.

pStartIndex
number

The starting index of the result set.

pRowCount
number

The maximum number of rows to be returned (ORDER BY must be defined in the SQL statement!). If you specify maxRows, this restriction might apply to all tables indicated in the JOIN part of the query. If you do not want to restrict this value, enter 0.

pTimeout?
number

Returns​

string[][]

The result of the query as a two-dimensional string array. The result can never be null.

Throws​

May throw an exception.

Example​

let data = db.tablePage(["select * from ORG"], "Data_alias", 0, 20, 10 * datetime.ONE_SECOND);
logging.log("Count: " + data.length);

toFilterCondition​

toFilterCondition(pFilterJson, pEntityName): string

Creates a condition from the given filter

Parameters​

pFilterJson
string | number | boolean

The filter as json

pEntityName
string | number | boolean

The name of the entity, for which this condition is created, NULL für current entity. Only entities with db as primary recordcontainer are supported yet.

Returns​

string

The SQL-Condition für the given Alias

Throws​

May throw an exception.

translateCondition​

translateCondition(pCondition, pAlias?): string

Creates readable SQL code for a condition based on a prepared-statement object. This makes it possible to output a prepared statement, for example through logging.log().

Parameters​

pCondition
any

The statement in the following format: [sql, [value1, value2, valuen] ].

pAlias?
string | number | boolean

The alias of the database for which the condition is to be translated.

Returns​

string

The readable SQL text.

Throws​

May throw an exception.

Example​

let sqlStr = "ORG.ORGNAME like ? and ADDRESS.CITY like ? and PERS_ID is null";

let orgNameValue = ["%" + vars.getString("$field.ORGNAME") + "%", SQLTYPES.VARCHAR];
let cityValue = ["%" + vars.getString("$field.CITY") + "%", SQLTYPES.VARCHAR];

let condition = [sqlStr, [orgNameValue, cityValue]];
let translatedCondition = db.translateCondition(condition, "Data_alias");
logging.log(translatedCondition);

translateStatement​

translateStatement(pStatement, pAlias?): string

Creates readable SQL code based on a prepared-statement object. This makes it possible to output a prepared statement, for example through logging.log().

Parameters​

pStatement
any

The statement in the following format: [sql, [value1, value2, valuen] ].

pAlias?
string | number | boolean

The alias of the database for which the code is to be translated.

Returns​

string

The readable SQL text.

Throws​

May throw an exception.

Example​

let sqlStr = "select distinct ORGID from ORG join RELATION on ORGID = ORG_ID join ADDRESS on ADDRESS_ID = ADDRESSID " + " where ORG.ORGNAME like ? and ADDRESS.CITY like ? and PERS_ID is null";

let orgNameValue = ["%" + vars.getString("$field.ORGNAME") + "%", SQLTYPES.VARCHAR];
let cityValue = ["%" + vars.getString("$field.CITY") + "%", SQLTYPES.VARCHAR];

let statement = [sqlStr, [orgNameValue, cityValue]];
let translatedStatement = db.translateStatement(statement, "Data_alias");
logging.log(translatedStatement);

truncate​

truncate(pTableName, pAlias?, pTimeout?): number

Deletes all rows from the specified database table.

Parameters​

pTableName
string | number | boolean

The table to be emptied.

pAlias?
string | number | boolean

The alias whose tables are to be emptied.

pTimeout?
number

The database query timeout in ms.

Returns​

number

The number of records that were removed.

Throws​

May throw an exception.

Example​

let deleted = db.truncate("NOTRELATION", db.getCurrentAlias(), 10 * datetime.ONE_SECONDD);

truncates​

truncates(pTableNames, pAlias?, pTimeout?): number

Deletes all rows from multiple tables.

Parameters​

pTableNames
string[]

The tables to be emptied.

pAlias?
string | number | boolean

The alias whose tables are to be emptied.

pTimeout?
number

The database query timeout in ms.

Returns​

number

The number of records that were removed.

Throws​

May throw an exception.

Example​

let tables = ["PERSRELATION", "ORGRELATION"];
let deleted = db.truncates(tables, "Data_alias", 10 * datetime.ONE_SECOND);

updateBinary​

updateBinary(pId, pParentId, pBase64, pFileName, pDescription, pKeyword, pAlias): void

Updates binary data.

Parameters​

pId
string | number | boolean

The ID of the entry in the binary database.

pParentId
string | number | boolean

New parent ID of the record; 'null' for none.

pBase64
string | number | boolean

The new data, Base64-encoded.

pFileName
string | number | boolean

The new file name.

pDescription
string | number | boolean

The new description.

pKeyword
string | number | boolean

The new keyword.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

void

Throws​

May throw an exception.

Example​

// update process of a documentView
let rowdata = vars.get("$local.rowdata");
db.updateBinary(rowdata.UID, null, rowdata.BINDATA, rowdata.FILENAME, "", "", null);

updateBinaryAssignment​

updateBinaryAssignment(pId, pAssignmentTable, pAssignmentName, pAssignmentRowId, pAlias): string

Updates the assignment of the binary file.

Parameters​

pId
string | number | boolean

The ID of the entry in the binary store. May change after update

pAssignmentTable
string | number | boolean

the table of the assignments

pAssignmentName
string | number | boolean

the name of the assignments

pAssignmentRowId
string | number | boolean

the ID of the data record to which the binary data record is to be assigned

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

string

The ID of the entry after into the binary store after this operation

Throws​

May throw an exception.

updateBinaryMetadata​

updateBinaryMetadata(pId, pParentId, pFileName, pDescription, pKeyword, pAlias): void

Updates the metadata of a binary file. The metadata is always stored (and updated) in the database only.

Parameters​

pId
string | number | boolean

The ID of the entry in the binary database.

pParentId
string | number | boolean

New parent ID of the record; 'null' for none.

pFileName
string | number | boolean

The new file name.

pDescription
string | number | boolean

The new description.

pKeyword
string | number | boolean

The new keyword.

pAlias
string | number | boolean

The alias that contains the binary data; 'null' for the current alias.

Returns​

void

Throws​

May throw an exception.

updateData​

updateData(pTableName, pColumns, pColumnTypes, pValues, pCondition, pAlias?, pTimeout?): number

Updates data in a database table. This process can be logged through the audit layer.

Parameters​

pTableName
string | number | boolean

The name of the table where you want to update.

pColumns
string[]

The columns where you want to update.

pColumnTypes
number[]

The types of the specified columns ("SQLTYPES.*"). This is no longer necessary, set it to null.

pValues
string[]

The values to be updated.

pCondition
any

The condition that specifies the records to be updated. The condition can be specified in the following format: [sqlString, [[value1, sqlType1], [value2, sqlType2], ...].

pAlias?
string | number | boolean

The database alias.

pTimeout?
number

Returns​

number

The number of records that were updated.

Throws​

May throw an exception.

Example​

let cols = ["DATE_EDIT", "USER_EDIT", "ORGNAME"];
let vals = [vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO"];
let cond = "ORGID = '1'";

db.updateData("ORG", cols, null, vals, cond, db.getCurrentAlias, 10 * datetime.ONE_SECOND);

updates​

updates(pStatements, pAlias?, pTimeout?): number

Updates data in a database table. This process can be logged through the audit layer.

Parameters​

pStatements
any[]

The statements to be executed: An array of arrays as elements. The "sub" arrays are structured as follows [0]: Tablename (String), [1]: Columns: (String[]), [2]: ColumnTypes: (int[]), [3]: Values (String[]), [4]: Condition (String)

pAlias?
string | number | boolean

The database alias

pTimeout?
number

Returns​

number

The number of records that were changed.

Throws​

May throw an exception.

Example​

let cols = ["DATE_EDIT", "USER_EDIT", "ORGNAME"];
let vals1 = [vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO"];
let vals2 = [vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH"];

let cond1 = "ORGID = '1'";
let cond2 = "ORGID = '5'";
let updateStatements = [];
updateStatements.push(["ORG", cols, types, vals1, cond1]);
updateStatements.push(["ORG", cols, types, vals2, cond2]);

db.updates(updateStatements, db.getCurrentAlias, 10 * datetime.ONE_SECOND);

validateDBColumn​

validateDBColumn(pValue, pTablename, pColumnName, pAlias?): boolean

Validates a value entered by the user and checks whether it can be valid for the column indicated by the user.

Parameters​

pValue
string | number | boolean

The value to be validated.

pTablename
string | number | boolean

The table that contains the column.

pColumnName
string | number | boolean

The name of the column for which the value is to be validated.

pAlias?
string | number | boolean

The alias that contains the database table.

Returns​

boolean

'true' if the value is valid; otherwise 'false'.

Throws​

May throw an exception.

Example​

let result = db.validateDBColumn("Value to be entered", "ORG", "ORGNAME", "Data_alias");
logging.log(result);

validateType​

validateType(pValue, pSQLType): boolean

Validates a value entered by the user and checks whether it can be valid for an SQL type indicated by the user.

Parameters​

pValue
string | number | boolean

The value to be validated.

pSQLType
number

The SQL type for which the system will check whether the value is valid (SQLTYPES.*).

Returns​

boolean

'true' if the value is valid; otherwise 'false'.

Throws​

May throw an exception.

Example​

let result = db.validateType("Value to be entered", SQLTYPES.INTEGER);
logging.log(result);

Properties​

ASCENDING​

boolean

Sort by: Ascending

BINARY_DATE_NEW​

string

Constant for accessing metadata for binary data: Date of creation

BINARY_DESCRIPTION​

string

Constant for accessing metadata for binary data: description

BINARY_EDIT​

string

Constant for accessing metadata for binary data: Date of the last modification

BINARY_FILENAME​

string

Constant for accessing metadata for binary data: filename

BINARY_HISTORYDATE​

string

Constant for accessing history for binary data: Date of history item

BINARY_HISTORYID​

string

Constant for accessing history for binary data: Id of history item

BINARY_ID​

string

Constant for accessing metadata for binary data: ID

BINARY_KEYWORD​

string

Constant for accessing metadata for binary data: keywords

BINARY_MIMETYPE​

string

Constant for accessing metadata for binary data: mimetype

BINARY_PARENTID​

string

Constant for accessing metadata for binary data: Parent ID of the directory

BINARY_PREVIEW​

string

Constant for accessing metadata for binary data: preview

BINARY_SIZE​

string

Constant for accessing metadata for binary data: size in bytes

BINARY_USER_EDIT​

string

Constant for accessing metadata for binary data: User who last modified the data

BINARY_USER_NEW​

string

Constant for accessing metadata for binary data: User who created the data

COLUMN​

number

SQL mode – first column

COLUMN_ISUNIQUE​

string

Constant for the results of db.getColumnInfos: Returns 'true' if the column is unique.

COLUMN_LABEL​

string

Constant for the results of db.getColumnInfos: Returns the column label.

COLUMN_NAME​

string

Constant for the results of db.getColumnInfos: Returns the column name.

COLUMN_NULLABLE​

string

Constant for the results of db.getColumnInfos: Returns whether the column may contain the value NULL. 'Null' if unknown.

COLUMN_PRECISION​

string

Constant for the results of db.getColumnInfos: For decimal fields, returns the total length including sign.

COLUMN_READONLY​

string

Constant for the results of db.getColumnInfos: Returns whether the column is read-only. 'Null' if unknown.

COLUMN_SCALE​

string

Constant for the results of db.getColumnInfos: For decimal fields, returns the decimals part without the decimal separator.

COLUMN_SEARCHABLE​

string

Constant for the results of db.getColumnInfos: Returns whether the column is searchable. 'Null' if unknown.

COLUMN_SORT_SEQUENCE​

string

Constant for the results of db.getColumnInfos: Returns a string for display that defines the sorting order.

COLUMN_TYPE​

string

Constant for the results of db.getColumnInfos: Returns the value of the SQLTYPES constant that corresponds to the column type.

COLUMN_TYPE_NAME​

string

Constant for the results of db.getColumnInfos: Returns the database-specific name of the column type.

DB_EOF​

number

DB request – return all rows (EOF)

DBTYPE_MARIADB10​

number

Constant for database types: MariaDB 10

DBTYPE_MYSQL4​

number

Constant for database types: MYSQL 4

DBTYPE_ORACLE10_CLUSTER​

number

Constant for database types: Oracle 8-11 cluster

DBTYPE_ORACLE10_OCI​

number

Constant for database types: Oracle 8-10 oci

DBTYPE_ORACLE10_THIN​

number

Constant for database types: Oracle 8-10 thin

DBTYPE_POSTGRESQL8​

number

Constant for database types: Postgresql 8

DBTYPE_SQLSERVER2000​

number

Constant for database types: MS SQLServer 2000

DESCENDING​

boolean

Sort by: Descending

EMPTY_TABLE_SQL​

string

This string is not listed as SQL on the DB, but is representative of an empty table.

ROW​

number

SQL mode – first row