Skip to main content
Version: 2025.0

db

Methods​

array​

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

Executes a SQL.

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: [sql, [value1, value2, valuen]]. 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​

var data = db.array(db.COLUMN, "select RELATIONID from ORG join RELATION on ORGID = ORG_ID where PERS_ID is not null and ORG_ID = '" + id + "'", "AO_DATEN", db.DB_EOF, 10 * datetime.ONE_MINUTE);
question.showMessage("Count: " + data.length);

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: [sql, [value1, value2, valuen]]. 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​

var data = db.arrayPage(db.COLUMN, "select RELATIONID from ORG join RELATION on ORGID = ORG_ID where PERS_ID is not null and ORG_ID = '" + id + "'", "AO_DATEN", 0 , 20, 10 * datetime.ONE_MINUTE);

cell​

cell(pSQL, pAlias?): string

Executes a SQL. 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: [sql, [value1, value2, valuen]]. 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​

var relationid = vars.get("$global.user").relationid;
var employeeid = db.cell( "select EMPLOYEEID from EMPLOYEE where RELATION_ID = '" + relationid + "'", "AO_DATEN");
question.showMessage(employeeid);

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​

var 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​

// delete process of a documentView
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 ADITO 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 = '" + vars.get("$local.idvalue") + "'", "AO_DATEN", 10 * datetime.ONE_MINUTE);
// In the delete process of an editable table

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​

var toDel = new Array();
toDel.push(new Array("QUESTION", "QUESTIONNAIRE_ID = '" + id + "'"));
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​

var statements = [];
//Insert
statements[0] = new Array("ORG", new Array("ORGID", "DATE_NEW", "USER_NEW", "ORGNAME"), db.getColumnTypes("ORG", new Array("ORGID", "DATE_NEW", "USER_NEW", "ORGNAME")), new Array(util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH"));

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

// Delete
statements[2] = new Array("ORG", "ORGID = '1'");
db.execute(statements, db.getCurrentAlias(), 10 * datetime.ONE_SECOND);

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​

var 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, "AO_DATEN", 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​

var sql = "select * from relation";
db.fastExportData("ContactExport", "c:/contactExport.txt", sql, ";", "'", "\n", true, util.EXPORT_OVERWRITE, "AO_DATEN", 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​

* // get the product picture for the report
var currAlias = db.getCurrentAlias();

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

// get the binary content as base64 String
var prodPicture = 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​

var data = db.getBinaryContents(['5ea59359-5d8e-4a03-bf48-dd5433405578', '75459359-5e8d-4c03-ad48-bb5435605578'], db.getCurrentAlias());
for(var i = 0; i < data.length; i++)
{
// Further processing
}

getBinaryCount​

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

Liefert die Anzahl der BlobEinträge zu den Suchparametern.

Parameters​

pAssignmentTable
string | number | boolean

die Tabelle des LOB-Assignments

pAssignmentName
string | number | boolean

der Name des Assignments

pAssignmentRowId
string | number | boolean

die Id des Datensatzes, dessen Assignments benötigt werden

pAlias
string | number | boolean

der Alias, in dem sich die Binärdaten befinden, null für aktuellen Alias

pKeyword?
string | number | boolean

das Keyword nach dem gefiltert werden soll

Returns​

number

die Anzahl der Medataten

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​

var id = vars.getString("$comp.historyid");

var toDel = new Array();
toDel.push( new Array("TABLEACCESS", "TATYPE = 'R' and FRAME_ID = " + vars.get("$image.Frame").Id + " and ROW_ID = '" + id + "'"));
toDel.push( new Array("HISTORYLINK", "HISTORY_ID = '" + id + "'"));
toDel.push( new Array("HISTORY_THEME", "HISTORY_ID = '" + id + "'"));
toDel.push( new Array("ATTRLINK", "OBJECT_ID = " + vars.get("$image.Frame").Id + " and ROW_ID = '" + id + "'"));
var binMetadata;

//Immediately after creating the follow-up history, the delete button remains active, therefore it must be checked here
if(db.cell("select count(HISTORYID) from HISTORY where HISTORY_ID = '" + id + "' and HISTORYID != '" + id + "'") &gt; 0)
{
question.showMessage(translate.text("The record contains subsequent histories and cannot be deleted until you remove them."));
result.string("false");
swing.refresh(); //To make the Delete button inactive and "Open subsequent histories" active
}
else
{
db.deletes(toDel);
binMetadata = db.getBinaryMetadata("$!GENERIC!$", "DOCUMENT", id, false, null);
// for each document, execute the delete
for( var i = 0; i &lt; binMetadata.length; i++)
{
db.deleteBinary(binMetaData[i].id, null);
}
}

getBinaryMetadataForIds​

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

Liefert die Metainformationen zu einem oder mehreren BlobEinträgen.

Parameters​

pIds
string[]

IDs der Binärdaten.

pIncludePreview
boolean

true liefert die Preview-Daten mit

pAlias
string | number | boolean

der Alias, in dem sich die Binärdaten befinden, null für aktuellen Alias

Returns​

Map<any, any>[]

die Medataten (alle), oder eine leere ScriptableMap, wenn keine Ids übergeben wurden

Throws​

May throw an exception.

Example​

var 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.show(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​

var cols = db.getColumns("ORG", "AO_DATEN");
question.showMessage(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​

var status = vars.get("$field.STATUS");
if ( status == 2 )//inaktiv
{
var cols = ["STATUS", "DATE_EDIT", "USER_EDIT"];
var types = db.getColumnTypes("RELATION", cols, db.getCurrentAlias());
db.updateData("RELATION", cols, types, [status, vars.getString("$sys.date"), vars.getString("$sys.user")], "ORG_ID = '" + vars.get("$field.ORGID") + "' and PERS_ID is not null");
}

getCurrentAlias​

getCurrentAlias(): string

Returns the current alias. invalid input: 'var alias = db.getCurrentAlias(); // Usage: var ticketid = vars.getString("$comp.SUPPORTTICKET_ID"); var aktion = ""; var user_aktuell = vars.getString("$sys.user"); var user_zustaendig = db.cell("select LOGIN from EMPLOYEE join SUPPORTTICKET on (EMPLOYEE.RELATION_ID = SUPPORTTICKET.EMPRELATION_ID) " + " where SUPPORTTICKETID = '" + ticketid + "'"); //the status of the ticket will be updated while saving var status_neu = vars.getString("$comp.STATUS"); var type = db.getColumnTypes("SUPPORTTICKET", ["STATUS"], db.getCurrentAlias()); if (vars.getString("$sys.superframe") == "SUPPORTTICKET") swing.setValueOn("$comp.STATUS", status_neu, vars.getString("$sys.superwindowid"), vars.getString("$sys.superimageid")); db.updateData("SUPPORTTICKET", ["STATUS"], type, [status_neu], "SUPPORTTICKETID = '" + ticketid + "'"); @return The current alias; 'null' if no alias exists' @function

Returns​

string

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​

var dbtype = db.getDatabaseType("AO_DATEN");

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​

var id = db.getNewID("ORG", "ORGID", db.getCurrentAlias());
question.showMessage(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.

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​

var cols = new Array("ORGNAME", "ORGID");
var types = db.getColumnTypes("ORG", cols, "AO_DATEN");
var values = new Array("ADITO", "12345");
var count = db.getRowCount("ORG", cols, types, values, "AO_DATEN", 10 * datetime.ONE_SECOND);
question.showMessage(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​

//data to fill a combobox
var tablenames = db.getTables(vars.getString("$sys.dbalias"));
var list = [];

for ( var i = 0; i &lt; tablenames.length; i++)
list.push([tablenames[i]])

result.object( list );

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​

// insert process for the documentView
// getting the row to insert
var rowdata = vars.get("$local.rowdata");

// parse the JSON String into an object
rowdata = JSON.parse(rowdata);

var tablename = vars.getString("$param.p_assignmentTable");
var container = vars.getString("$param.p_assigntmentContainer");
var rowid = vars.getString("$param.p_rowId");

// inster binary, with it's according assignment, data and filename
db.insertBinary(tablename, container, rowid, null, rowdata.BINDATA, rowdata.FILENAME, "", "", null, id);

insertData​

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

Inserts data 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 type of the columns where you want to insert the contents ("SQLTYPES.*").

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​

var cols = new Array("KNOWLEDGE_NOTIFICATIONID", "DATE_NEW", "USER_NEW");
var types = db.getColumnTypes("KNOWLEDGE_NOTIFICATION", cols);
var vals = [util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user")];
db.insertData("KNOWLEDGE_NOTIFICATION", cols, types, 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​

var cols = new Array("ORGID", "DATE_NEW", "USER_NEW", "ORGNAME");
var types = db.getColumnTypes("ORG", cols);
var vals1 = [util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH"];
var vals2 = [util.getNewUUID(), vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH Geisenhausen"];

var inserts = [];
inserts[0] = new Array("ORG", cols, types, vals1);
inserts[1] = new Array("ORG", cols, types, vals2);

var count = db.inserts(inserts, "AO_DATEN", 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​

var name = vars.getString("$comp.edt_name");
var columnType = db.getColumnTypes("ORG", new Array("ORGNAME"), "AO_DATEN");

if(SQLTYPES.isTextType(columnType[0]))
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​

var count = db.runStatement("update RELATION set STATUS = 1", "AO_DATEN");
logging.show(count);

table​

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

Executes a SQL.

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: [sql, [value1, value2, valuen]]. 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​

var data = db.table("select * from org", vars.getString("$sys.dbalias"), db.DB_EOF, 10 * date.ONE_MINUTE);
question.showMessage("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: [sql, [value1, value2, valuen]]. 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​

var data = db.tablePage(new Array("select * from org"), vars.getString("$sys.dbalias"), 0, 20, 10 * datetime.ONE_SECOND);
question.showMessage("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​

var sqlstr = "select distinct orgid from org join relation on org_id = orgid join address on address_id = addressid " + " where org.orgname like ? and address.city like ? and pers_id is null";

var q1 = ["%" + vars.getString("$comp.edt_orgname") + "%", SQLTYPES.VARCHAR];
var q2 = ["%" + vars.getString("$comp.edt_city") + "%", SQLTYPES.VARCHAR];

var statement = [sqlstr, [q1, q2]];
var sttrans = db.translateCondition(statement, "AO_DATEN");
question.showMessage(sttrans);

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​

var sqlstr = "select distinct orgid from org " +"join relation on org_id = orgid join address on address_id = addressid " + " where org.orgname like ? and relation.city " +"like ? and pers_id is null";

var q1 = ["%" + vars.getString("$comp.edt_orgname") + "%", SQLTYPES.VARCHAR];
var q2 = ["%" + vars.getString("$comp.edt_city") + "%", SQLTYPES.VARCHAR];

var statement = [sqlstr, [q1, q2]];
var sttrans = db.translateStatement(statement, "AO_DATEN");
question.showMessage(sttrans);

var cdtrans = db.translateCondition(statement);
question.showMessage(cdtrans);

truncate​

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

Deletes the data from a certain SQL 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​

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

truncates​

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

Deletes the data 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​

var tables = [];
tables[0] = "PERSRELATION";
tables[1] = "ORGRELATION";

var deleted = db.truncates(tables, "AO_DATEN", 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
var rowdata = vars.get("$local.rowdata");
rowdata = JSON.parse(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 ADITO 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 columns (SQLTYPES.*) you want to update.

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: [sql, [value1, value2, valuen]].

pAlias?
string | number | boolean

The database alias.

pTimeout?
number

Returns​

number

The number of records that were updated.

Throws​

May throw an exception.

Example​

var cols = new Array("DATE_EDIT", "USER_EDIT", "ORGNAME");
var types = db.getColumnTypes("ORG", cols);
var vals = [vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO"];
var cond = "ORGID = '1'";

db.updateData("ORG", cols, types, 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 ADITO 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​

var cols = new Array("DATE_EDIT", "USER_EDIT", "ORGNAME");
var types = db.getColumnTypes("ORG", cols);

var vals1 = [vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO"];
var vals2 = [vars.getString("$sys.date"), vars.getString("$sys.user"), "ADITO Software GmbH"];

var cond1 = "ORGID = '1'";
var cond2 = "ORGID = '5'";
var updateStatements = [];
updateStatements[0] = new Array("ORG", cols, types, vals1, cond1);
updateStatements[1] = new Array("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​

var result = db.validateDBColumn("Value to be entered", "ORG", "ORGNAME", "AO_DATEN");
question.showMessage(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​

var result = db.validateType("Value to be entered", SQLTYPES.INTEGER);
question.showMessage(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