Script unit that provides database operations (insert, modify, update, lookup).
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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; export procedure onAfterModify() begin var dataset := d.findDataset(_ebx.dataspace,'dataset-name'); var field := d.lookupDatasetFieldForUpdate<typeof _ebx.root>(_ebx.transaction,dataset); field.fieldA := 'AA'; field.fieldB := 'BB'; ... d.saveField(field); end
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
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.findDataspace('dataspace-name');
...
end
Parameters :
name: the name of the dataspace.
Return :
the dataspace.
Can be used in: Script tasks, Table triggers
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
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
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
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
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
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
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
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
procedure saveField(field: updatableFieldType)
Saves the dataset field. Raises an error if the operation fails.
Example:
uses core.data as d; export procedure onAfterCreate() begin var field := d.lookupDatasetFieldForUpdate<typeof _ebx.root>(_ebx.transaction,'dataset-name'); field.fieldA := 'AA'; field.fieldB := 'BB'; d.saveField(field); end
Parameters :
field: the dataset field to save.
Can be used in: Script tasks, Table triggers
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
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
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