Cloud Software Group, Inc. EBX®
Documentation > Developer Guide > EBX® Script > API
Navigation mode Documentation > Developer Guide > EBX® Script > API

# Unit core.data

Script unit that provides database operations (insert, modify, update, lookup).

Methods
procedure addDataset(sql: queryType, alias: string, dataset: datasetType) Adds a dataset to a query.
function createQuery(sql: string, datasets: string...): Creates a query.
procedure deleteRecord(transaction: transactionType, record: recordType) Deletes a read only record.
procedure deleteRecordByPrimaryKey(transaction: transactionType, table: tableType, primaryKey: primaryKeyType) Deletes a record specified by its primary key.
function executeQuery(query: query): list of tupleType. Executes the query.
function explain(query: query): string. Returns a meaningful string representation of the query plan.
function fetchFirst(query: query): tuple. Executes the query and returns the first tuple.
function fetchOne(query: query): tuple. Executes the query and returns a single tuple or null if the number of tuple is not exactly one.
function field<fieldType>(tuple: tupleType, alias: string): fieldType. Returns the value of the field by specified alias.
function fieldByIndex<fieldType>(tuple: tupleType, index: int): fieldType. Returns the value of the field at specified index position.
function fieldNames(result: query_result): list<string> Returns the names of the fields in the result of the query.
function findDataset(dataspace: dataspaceType, name: string): datasetType Returns the dataset specified by its name.
function findDataspace(name: string): dataspaceType Returns the dataspace specified by its name.
function findSnapshot(name: string): snapshotType Returns the snapshot specified by its name.
function findTable<tableType>(dataset: datasetType): tableType Returns a table of the specified dataset.
function lookupDataset(dataspace: dataspaceType, name: string): datasetType Returns the dataset specified by its name.
function lookupDatasetField(dataset: datasetType): fieldType Returns a read only field specified by the dataset.
function lookupDatasetFieldForUpdate(transaction: transactionType, dataset: datasetType): updatableFieldType Returns an updatable field of the specified dataset.
function lookupDataspace(name: string): dataspaceType Returns the dataspace specified by its name.
function lookupRecord(table: tableType, primaryKey: PrimaryType): recordType Returns a read only record specified by its primary key.
function lookupRecordByFullXPath(table: tableType, xPath: string): recordType Returns a read only record specified by an XPath.
function lookupRecordByFullXPathForUpdate(transaction:transactionType,table: tableType, xPath: string): recordType Returns an updatable record specified by an XPath.
function lookupRecordForUpdate(transaction: transactionType, table: tableType, tableRef: string): updatableRecordType Returns an updatable record specified by its primary key.
function lookupRecordForUpdate(transaction: transactionType, table: tableType, primaryKey: PrimaryKeyType): updatableRecordType Returns an updatable record specified by its primary key.
function lookupSnapshot(name: string): snapshotType Returns the snapshot specified by its name.
function record<recordType>(tuple: tupleType, alias: string): recordType. Returns a read-only record by specified alias.
function recordByIndex<recordType>(tuple: tupleType, index: int): recordType. Returns a read-only record at index position.
function recordOf(transaction: transactionType, table: tableType): recordType Create a new record for a table.
procedure saveField(field: updatableFieldType) Saves the dataset field.
procedure saveRecord(record: updatableRecordType) Saves the record.
procedure setFetchSize(query: query, number: int) Provides a hint to the underlying data with the number of records to fetch at a time from the result.
procedure setParameter(query: query, position: int, value: fieldType) Sets a value to a positional parameter in the query.

## addDataset

procedure addDataset(sql: queryType, alias: string, dataset: datasetType)

Adds a dataset to a query. This method must be called before executeQuery or explain.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var sql :='select t.calA, t.colB from d1.tableA t where t.id=xx';
      var query :=d.createQuery(sql);
      var dataset1 := d.findDataset(_ebx.dataspace, 'dataset-name1');
      d.addDataset(query, 'd1', dataset1);
    end

Parameters :

sql: the sql query.

alias: the alias of the dataset to be added to the query.

dataset: the dataset to be added to the query.

Can be used in: Script tasks, Table triggers

## createQuery

function createQuery(sql: string, datasets: string...):

Creates a query. Raises an error if the sql string is not a valid select; for example, it contains syntax errors, it references missing tables, missing columns, etc.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var sql := 'select t.calA, t.colB from tableA t where t.id=xx';
      var query := d.createQuery(sql);
    end

Parameters :

sql: the string representation of the sql query.

Return :

the query.

Can be used in: Script tasks, Table triggers

## deleteRecord

procedure deleteRecord(transaction: transactionType, record: recordType)

Deletes a read only record. Raises an error if the deletion fails.

Example:

    uses core.data as d;
    export procedure onAfterDelete()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);
      var record := d.lookupRecord(tableA,_ebx.record.foreignKey);
      // deletes the record
      d.deleteRecord(_ebx.transaction,record);
    end

Parameters :

transaction: the transaction.

record: the record.

Can be used in: Script tasks, Table triggers

## deleteRecordByPrimaryKey

procedure deleteRecordByPrimaryKey(transaction: transactionType, table: tableType, primaryKey: primaryKeyType)

Deletes a record specified by its primary key. Raises an error if the deletion fails. Deleting a record that does not exist is not an error.

. Example 1:

    uses core.data as d;
    uses core.complex as c;
    export procedure onAfterDelete()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);

      // Creates the primary key.
      var pk := c.primaryKeyOf<.tableName>();
      pk.field1 := ...;
      pk.field2 := ...;

      // Deletes the record using the primary key.
      d.deleteRecordByPrimaryKey(_ebx.transaction, table, pk);
    end

Example 2:

    uses core.data as d;
    export procedure onAfterDelete()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);

      // Deletes the record referenced by a foreign key. A foreign key is also a primary key.
      d.deleteRecordByPrimaryKey(_ebx.transaction,table,_ebx.record.foreignKey);
    end

Parameters :

transaction: the transaction.

table: the table.

primaryKey: the primary key.

Can be used in: Script tasks, Table triggers

## executeQuery

function executeQuery(query: query): list of tupleType.

Executes the query. Raises an error if nodes used in the query are forbidden.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var query := d.createQuery('select t.colA, t.colB" from s1.tableA t where t.colC=?');
      d.addDataset(query, 's1', d.findDataset(_ebx.dataspace, 'dataset-name'));
      a.setParameter(query, 0, 'A value');

      var queryResult := d.executeQuery(query);
      for tuple in queryResult do
      begin
        // Do something with tuple.
        ...
      end
      d.close(queryResult)
    end

Parameters :

query: the sql query.

Return :

the result for the query.

Can be used in: Script tasks, Table triggers

## explain

function explain(query: query): string.

Returns a meaningful string representation of the query plan.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var query := d.createQuery('select t."$pk" as id from table t');
      var queryPlan := d.explain(query);
      ...
    end

Parameters :

query: the sql query.

Return :

the representation of the query plan.

Can be used in: Script tasks, Table triggers

## fetchFirst

function fetchFirst(query: query): tuple.

Executes the query and returns the first tuple. Raises an error if nodes used in the query are forbidden.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var query := d.createQuery('select t.colA, t.colB" from s1.tableA t where t.colC=?');
      d.addDataset(query, 's1', d.findDataset(_ebx.dataspace, 'dataset-name'));
      a.setParameter(query, 0, 'A value');

      var tuple := d.fetchFirst(query);
      // Do something with tuple.
      ...
    end

Parameters :

query: the sql query.

Return :

the first tuple for the query on null if query returns no result.

Can be used in: Script tasks, Table triggers

## fetchOne

function fetchOne(query: query): tuple.

Executes the query and returns a single tuple or null if the number of tuple is not exactly one. Raises an error if nodes used in the query are forbidden.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var query := d.createQuery('select t.colA, t.colB" from s1.tableA t where t.colC=?');
      d.addDataset(query, 's1', d.findDataset(_ebx.dataspace, 'dataset-name'));
      a.setParameter(query, 0, 'A value');

      var tuple := d.fetchOne(query);
      // Do something with tuple.
      ...
    end

Parameters :

query: the sql query.

Return :

the first tuple for the query on null if query returns no result.

Can be used in: Script tasks, Table triggers

## field

function field<fieldType>(tuple: tupleType, alias: string): fieldType.

Returns the value of the field by specified alias.

Example:

    uses core.data as d;
    export procedure onBeforeDelete()
    begin
      var query := d.createQuery('select a.col1 as c1, a.col2 as c2 from tableA a');
      var queryResult := d.executeQuery(query);
      for tuple in queryResult do
      begin
        var field1 := d.field<string>(tuple,'c1');
        var field2 := d.field<decimal>(tuple,'c2');
        // Do something with field values
        ...
      end;
      ...
    end

Function Types :

fieldType: the field type. Is mandatory.

Parameters :

tuple: the tuple of the query result.

alias: the alias of the field.

Can be used in: Script tasks, Table triggers

## fieldByIndex

function fieldByIndex<fieldType>(tuple: tupleType, index: int): fieldType.

Returns the value of the field at specified index position.

Example:

    uses core.data as d;
    export procedure onBeforeModify()
    begin
      var query := d.createQuery('select a.col1, a.col2 from tableA a');
      var queryResult := d.executeQuery(query);
      for tuple in queryResult do
      begin
        var field1 := d.fieldByIndex<string>(tuple,0);
        var field2 := d.fieldByIndex<date>(tuple,1);
        // Do something with field values.
        ...
      end;
      ...
    end

Function Types :

fieldType: the field type. Is mandatory.

Parameters :

tuple: the tuple of the query result.

index: the position of the field. First position is 0.

Can be used in: Script tasks, Table triggers

## fieldNames

function fieldNames(result: query_result): list<string>

Returns the names of the fields in the result of the query.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var query := d.createQuery('select t.* from table t');
      var names := d.fieldNames(query);
      for name in names do
      begin
        // Do something with name.
        ...
      end;
    end

Parameters :

result: the sql query.

Return :

the names of the fields in the query result.

Can be used in: Script tasks, Table triggers

## findDataset

function findDataset(dataspace: dataspaceType, name: string): datasetType

Returns the dataset specified by its name. Raises an error if the dataset is not found.

    uses core.data as d;

    procedure doSomething()
    begin
      var dataspace := d.findDataspace('dataspace-name');
      var dataset := d.findDataset(dataspace, 'dataset-name');
      ...
    end

Parameters :

dataspace: the dataspace that owns the dataset.

name: the name of the dataset.

Return :

the dataset specified by its name.

Can be used in: Script tasks, Table triggers

## findDataspace

function findDataspace(name: string): dataspaceType

Returns the dataspace specified by its name. Raises an error if the dataspace is not found.

Example:

    uses core.data as d;

    begin
      var dataspace := d.findDataspace('dataspace-name');
      ...
    end

Parameters :

name: the name of the dataspace.

Return :

the dataspace.

Can be used in: Script tasks, Table triggers

## findSnapshot

function findSnapshot(name: string): snapshotType

Returns the snapshot specified by its name. Raises an error if the snapshot is not found.

Example:

    uses core.data as d;

    procedure doSomething()
    begin
      var snapshot := d.findSnapshot('snapshot-name');
      ...
    end

Parameters :

name: the name of the snapshot.

Return :

the snapshot.

Can be used in: Script tasks, Table triggers

## findTable

function findTable<tableType>(dataset: datasetType): tableType

Returns a table of the specified dataset. Raises an error if the table is not found.

Example:

    uses core.data as d;

    procedure doSomething()
    begin
      var customerTable := db.findTable<.Customer>(_ebx.dataset);
      ...
    end

Function Types :

complexType: the table type.

Parameters :

dataset: the dataset that owns the table.

Return :

the table.

Can be used in: Script tasks, Table triggers

## lookupDataset

function lookupDataset(dataspace: dataspaceType, name: string): datasetType

Returns the dataset specified by its name. Returns null if the dataset is not found.

    uses core.data as d;

    procedure doSomething()
    begin
      var dataspace := d.lookupDataspace('dataspace-name');
      var dataset := d.lookupDataset(dataspace, 'dataset-name');
      ...
    end

Parameters :

dataspace: the dataspace that owns the dataset.

name: the name of the dataset.

Return :

the dataset specified by its name.

Can be used in: Script tasks, Table triggers

## lookupDatasetField

function lookupDatasetField(dataset: datasetType): fieldType

Returns a read only field specified by the dataset.

Example:

    uses core.data as d;
    references model "myModel" as m;

    export procedure onAfterModify()
    begin

      var dataset := d.findDataset(_ebx.dataspace,'dataset-name');
      var address := d.lookupDatasetField<m.address>(dataset);
      var city:=address.city ;
      var country:=address.country ;
      ...
    end

Note: Currently, it's not possible to access simple dataset fields that are directly under the root.

Parameters :

dataset: The dataset.

Return :

the read only field.

Can be used in: Script tasks, Table triggers

## lookupDatasetFieldForUpdate

function lookupDatasetFieldForUpdate(transaction: transactionType, dataset: datasetType): updatableFieldType

Returns an updatable field of the specified dataset. Raises an error if the dataset is not owned by the dataspace used to create the transaction.

Example:

    uses core.data as d;
    references model "myModel" as m;

    export procedure onAfterModify()
    begin

      var dataset := d.findDataset(_ebx.dataspace,'dataset-name');
      var address := d.lookupDatasetFieldForUpdate<m.address>(_ebx.transaction,dataset);
      address.city := 'Paris';
      address.country := 'France';
      ...
      d.saveField(address);
    end

Note: Currently, it's not possible to access simple dataset fields that are directly under the root.

Parameters :

transaction: the transaction.

dataset: The dataset must be owned by the dataspace used to create the transaction.

Return :

the updatable field.

Can be used in: Script tasks, Table triggers

## lookupDataspace

function lookupDataspace(name: string): dataspaceType

Returns the dataspace specified by its name. Returns null if the dataspace is not found.

Example:

    uses core.data as d;

    procedure doSomething()
    begin
      var dataspace := d.lookupDataspace('dataspace-name');
      ...
    end

Parameters :

name: the name of the dataspace.

Return :

the dataspace.

Can be used in: Script tasks, Table triggers

## lookupRecord

function lookupRecord(table: tableType, primaryKey: PrimaryType): recordType

Returns a read only record specified by its primary key. Returns null if the record does not exist.

Example:

    uses core.data as d;

    export procedure onAfterCreate()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);
      // Fetch the record referenced by the foreign key.
      var record := d.lookupRecord(table, _ebx.record.foreignKey);
      // Do something with record.
      ..
    end

Parameters :

table: the table.

primaryKey: the primary key.

Return :

the record specified by its primary key or null if not found.

Can be used in: Script tasks, Table triggers

## lookupRecordByFullXPath

function lookupRecordByFullXPath(table: tableType, xPath: string): recordType

Returns a read only record specified by an XPath. Returns null if the record does not exist.

The XPath must specify the value of each primary key field.

An example of XPath is:

    /root/table[./pk-field1='a' and ./pk-field2='b']

Example:

    uses core.data as d;

    export procedure onAfterCreate()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);
      var xpath:='/root/table[./pk-field1='a' and ./pk-field2='b']';
      // Fetch the record specified by an XPath.
      var record := d.lookupRecordByFullXPath(table, xpath);
      // Do something with record.
      ..
    end

Parameters :

table: the table.

xPath: the xPath expression.

Return :

the record specified by the xPath or null if not found.

Can be used in: Script tasks, Table triggers

## lookupRecordByFullXPathForUpdate

function lookupRecordByFullXPathForUpdate(transaction:transactionType,table: tableType, xPath: string): recordType

Returns an updatable record specified by an XPath. Returns null if the record does not exist.

The XPath must specify the value of each primary key field.

An example of XPath is:

    /root/table[./pk-field1='a' and ./pk-field2='b']

Example:

    uses core.data as d;

    export procedure onAfterCreate()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);
      var xpath='/root/table[./pk-field1='a' and ./pk-field2='b']';
      // Fetch the record specified by an XPath.
      var record := d.lookupRecordByFullXPathForUpdate(_ebx.transaction,table, xpath);
      // Update the record.
      record.field3:='XX';
      record.field4:='YY';
      ..
      d.saveRecord(record);
    end

Parameters :

transaction: the transaction.

table: the table.

xPath: the xPath expression.

Return :

the record specified by the xPath or null if not found.

Can be used in: Script tasks, Table triggers

## lookupRecordByRefForUpdate

function lookupRecordForUpdate(transaction: transactionType, table: tableType, tableRef: string): updatableRecordType

Returns an updatable record specified by its primary key. Returns null if the record does not exist or tableRef does not have a valid format

Parameters :

transaction: the transaction.

table: the table.

tableRef: the table ref.

Return :

the updatable record specified by its tableRef or null if not found or tableRef does not have a valid format.

Can be used in: Script tasks, Table triggers

## lookupRecordForUpdate

function lookupRecordForUpdate(transaction: transactionType, table: tableType, primaryKey: PrimaryKeyType): updatableRecordType

Returns an updatable record specified by its primary key. Returns null if the record does not exist.

Example:

    uses core.data as d;

    export procedure onAfterCreate()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);
      // Fetch the record referenced by the foreign key.
      var record := d.lookupRecordForUpdate(_ebx.transaction,table, _ebx.record.foreignKey);
      // Update the record.
      record.fieldA:='A';
      record.fieldB:='B';
      ..
      d.saveRecord(record);
    end

Parameters :

transaction: the transaction.

table: the table.

primaryKey: the primary key.

Return :

the updatable record specified by its primary key or null if not found.

Can be used in: Script tasks, Table triggers

## lookupSnapshot

function lookupSnapshot(name: string): snapshotType

Returns the snapshot specified by its name. Returns null if the specified snapshot is not found.

Example:

    uses core.data as d;

    procedure doSomething()
    begin
      var snapshot := d.lookupSnapshot('snapshot-name');
      ...
    end

Parameters :

name: the name of the snapshot.

Return :

the snapshot.

Can be used in: Script tasks, Table triggers

## record

function record<recordType>(tuple: tupleType, alias: string): recordType.

Returns a read-only record by specified alias.

Example:

    uses core.data as d;
    export procedure onAfterModify()
    begin
      var query := d.createQuery('select a."$adaptation" as rd from tableA a');
      var queryResult := d.executeQuery(query);
      for tuple in queryResult do
      begin
        var record := d.record<.tableA>(tuple,'rd');
        // Do something with the record.
        ...
      end;
      ...
    end

Function Types :

recordType: the record type. Is mandatory.

Parameters :

tuple: the tuple of the query result.

alias: the alias of the field.

Can be used in: Script tasks, Table triggers

## recordByIndex

function recordByIndex<recordType>(tuple: tupleType, index: int): recordType.

Returns a read-only record at index position.

Example:

    uses core.data as d;
    export procedure onBeforeModify()
    begin
      var query := d.createQuery('select a."$adaptation" from s1.tableA a', 'Bdataspace1\dataset1 s1');
      var queryResult := d.executeQuery(query);
      for tuple in queryResult do
      begin
        var record := d.record<.tableA>(tuple,0);
        // Do something with the record.
        ...
      end;
      ...
    end

Function Types :

complexType: the record type. Is mandatory.

Parameters :

tuple: the tuple of the query result.

index: the position of the field.

Can be used in: Script tasks, Table triggers

## recordOf

function recordOf(transaction: transactionType, table: tableType): recordType

Create a new record for a table. Default values are set as specified in the schema.

Example:

    uses core.data as d;

    export procedure onAfterCreate()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);
      var record := d.recordOf(_ebx.transaction, table);
      // Update the record.
      record.pk:='IDA';
      record.field1:='A1';
      record.field2:='A2';
      ...
      d.saveRecord(record);
    end

Parameters :

transaction: the transaction.

table: the table.

Return :

the new record.

Can be used in: Script tasks, Table triggers

## saveField

procedure saveField(field: updatableFieldType)

Saves the dataset field. Raises an error if the operation fails.

references model "myModel" as m;

Example:

    uses core.data as d;

    export procedure onAfterCreate()
    begin
      var dataset := d.findDataset(_ebx.dataspace,'dataset-name');
      var address := d.lookupDatasetFieldForUpdate<m.address>(_ebx.transaction,dataset);
      address.city := 'Paris';
      address.country := 'France';
      ...
      d.saveField(address);
    end

Parameters :

field: the dataset field to save.

Can be used in: Script tasks, Table triggers

## saveRecord

procedure saveRecord(record: updatableRecordType)

Saves the record. Raises an error if the operation fails.

The record is updated with new values. Example:

    uses core.data as d;

    export procedure onAfterCreate()
    begin
      var table := d.findTable<.tableName>(_ebx.dataset);
      var record := d.recordOf(_ebx.transaction, table);
      // Update the record.
      ...
      d.saveRecord(record);
    end

Parameters :

record: the record to save.

Can be used in: Script tasks, Table triggers

## setFetchSize

procedure setFetchSize(query: query, number: int)

Provides a hint to the underlying data with the number of records to fetch at a time from the result.

Example:

    uses core.data as d;
    export procedure onAfterCreate()
    begin
      var sql := 'select * from tableA where id=xx';
      var query := d.createQuery(sql);
      d.setFetchSize(query,50);
      ...
    end

Parameters :

query: the query.

number: the number of record to fetch.

Can be used in: Script tasks, Table triggers

## setParameter

procedure setParameter(query: query, position: int, value: fieldType)

Sets a value to a positional parameter in the query. Example:

    uses core.data as d;
    uses core.locale as locale;
    export procedure onAfterModify()
    begin
      var query := d.createQuery('select * from table tableA t where t.col1=? and t.col2=?');
      d.setParameter(query, 0, 'Paris');
      d.setParameter(query, 1, d(1956-06-17));
      var queryResult := d.executeQuery(query);
      ...
    end

Parameters :

query: the query.

position: the position of the parameter in the query.

value: value of the parameter.

Can be used in: Script tasks, Table triggers

Documentation > Developer Guide > EBX® Script > API