Script unit that provides database operations (insert, modify, update, lookup).
procedure addDataset(sql: queryType, alias: string, dataset: datasetType)
Adds a dataset to a SQL 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 :
query: 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:
function createQuery(sql: string, datasets: string...):
Creates a SQL 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. Is never null.
Can be used in:
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 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:
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:
function executeQuery(query: query): list of tupleType.
Executes the SQL 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. Is never null.
Can be used in:
function explain(query: query): string.
Returns a meaningful string representation of the SQL 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:
function fetchFirst(query: query): tuple.
Executes the SQL 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 or null if query returns no result.
Can be used in:
function fetchOne(query: query): tuple.
Executes the SQL 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 or null if query returns no result.
Can be used in:
function field<fieldType>(tuple: tupleType, alias: string): fieldType.
Returns the field of the SQL tuple for the specified alias.
Note: This method is not recommended for tuples from embedded SQL queries because compiler cannot check the return type.
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:
function fieldByIndex<fieldType>(tuple: tupleType, index: int): fieldType.
Returns the field of the SQL tuple for the specified position.
Note: This method is not recommended for tuples from embedded SQL queries because compiler cannot check the return type.
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:
function fieldNames(result: query_result): list<string>
Returns the names of the fields in the result of the SQL 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. Is never null.
Can be used in:
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. Is never null.
Can be used in:
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. Is never null.
Can be used in:
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. Is never null.
Can be used in:
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. Is never null.
Can be used in:
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 or null if not found.
Can be used in:
function lookupDatasetField(dataset: datasetType): fieldType
This method is deprecated and replaced by lookupDatasetRoot.
Returns a read only field specified by the dataset.
Parameters :
dataset: The dataset.
Return :
the read only field.
Can be used in:
function lookupDatasetFieldForUpdate(transaction: transactionType, dataset: datasetType): updatableFieldType
This method is deprecated and replaced by lookupDatasetRootForUpdate.
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.
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:
procedure lookupDatasetRoot<modelAlias>(dataset: datasetType)
Returns dataset root. Raises an error if the operation fails.
Example:
references model "sample-model" as data;
uses core.data as db;
export procedure executeScriptTask()
begin
const dataspace: dataspace := db.findDataspace('test-dataspace');
const dataset: dataset := db.findDataset(dataspace, 'test-dataset');
var root := db.lookupDatasetRoot<data>(dataset);
var name := root.name;
var city := root.address.city;
....
end
Parameters :
dataset: the dataset.
Return :
the dataset's root. Is never null.
Can be used in:
procedure lookupDatasetRootForUpdate<modelAlias>(transaction: transactionType, dataset : datasetType) updatableDatasetRootType
Returns an updatable dataset root. Raises an error if the operation fails.
Example:
references model "sample-model" as data;
uses core.data as db;
export procedure executeScriptTask()
begin
const dataspace: dataspace := db.findDataspace('test');
execute transaction tr on dataspace
begin
var dataset := db.findDataset(dataspace, 'test-dataset');
var root := db.lookupDatasetRootForUpdate<data>(tr, dataset);
// Modify a dataset field.
root.address.city := 'Paris';
// Persist the changes.
db.saveDatasetRoot(root);
end
end
Parameters :
transaction: the transaction.
dataset: the dataset.
Return :
the updatable dataset's root. Is never null.
Can be used in:
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 or null if not found.
Can be used in:
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:
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:
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:
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:
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:
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 or null if not found.
Can be used in:
function record<recordType>(tuple: tupleType, alias: string): record.
This method is deprecated and replaced by field.
Returns a read-only record by specified alias.
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:
function recordByIndex<recordType>(tuple: tupleType, index: int): recordType.
This method is deprecated and replaced by fieldByIndex.
Returns a read-only record at index position.
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:
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. Is never null.
Can be used in:
procedure saveDatasetRoot(root: updatableDatasetRootType)
Saves the all dataset fields. Raises an error if the operation fails.
Example:
references model "sample-model" as data;
uses core.data as db;
export procedure executeScriptTask()
begin
const dataspace: dataspace := db.findDataspace('test');
execute transaction tr on dataspace
begin
var dataset := db.findDataset(dataspace, 'test-dataset');
var root := db.lookupDatasetRootForUpdate<data>(tr, dataset);
// Modify a dataset field.
root.address.city := 'Paris';
// Persist the changes.
db.saveDatasetRoot(root);
end
end
Parameters :
root: the root of the dataset to save.
Can be used in:
procedure saveField(field: updatableFieldType)
This method is deprecated and replaced by saveDatasetRoot.
Saves the dataset field. Raises an error if the operation fails.
references model "myModel" as m;
Parameters :
field: the dataset field to save.
Can be used in:
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:
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 SQL query.
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:
procedure setLocale(query: query, locale: locale)
Sets the locale for the SQL query. Should be called before method executeQuery.
Example:
uses core.data as d;
uses core.locale as locale;
export procedure onAfterModify()
begin
var query := d.createQuery('select * from table tableA');
d.setLocale(query,locale.of('fr','FR'));
var queryResult := d.executeQuery(query);
...
end
Parameters :
query: the query.
locale: the locale.
Can be used in:
procedure setParameter(query: query, position: int, value: fieldType)
Sets a value to a positional parameter in the SQL 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: