Database Table Functions
A special table type is used to handle database table lookups with optional caching in memory. All of the following column references may be either numeric (column index, starting at 0) or string (column name).
The table lookup only supports int, string, and date types.
Note!
For MS SQL, the column type timestamp is not supported in tables accessed by MediationZone. Use column type datetime instead. Please refer to the Notes section in the Database Functions page for details on allowed database data types.
The following functions for the Database Table described here are:
sqlExec
The sqlExec function is used when updating and inserting data into tables. It returns an integer value stating how many rows were updated/inserted. SQL errors will cause runtime errors (the workflow aborts).
This function can use for execute create or drop table statement.
int sqlExec (
string dbProfile,
string sqlQuery )Parameter | Description |
|---|---|
| Name of the database where the table resides, including folder name. |
| SQL query to send to the database. Note that SQL statements must not end with ';'. Calls to stored procedures must be embedded in blocks. Examples - Valid for Oracle databases "<SQL query>"
"BEGIN <procedure>; END;" |
Returns | An integer equaling the number of rows updated or inserted. |
tableCreate
Returns a table that holds the result of a database query. SQL errors in the table lookup will cause runtime errors (workflow aborts).
table tableCreate (
string dbProfile,
string sqlQuery
)Parameter | Description |
|---|---|
| Name of the database where the table resides. |
| SQL query to send to the database. Note that SQL statements must not end with ';'. Only columns of type number, date and string are supported. |
(Removed from MediationZone since 9.2.2.0) | An optional parameter to disable the commit statement from being performed at the end of every SQL transaction for this particular function. Setting this parameter to false will result in the commit statement to be performed at the end of every SQL transaction for this particular function. By default, the system has the disableCommit set to true unless otherwise changed via this parameter. Note! It should be noted that on recent Oracle versions, the DBLink SQL transaction behaviour has changed, where every single SQL statement for remote database transaction requires a commit or rollback statement in order to close a connection. In addition, Redshift users should set this to false as every statement needs to be committed. |
Returns | A table |
Example - Using tableCreate
To avoid performance problems, the table must be read from the database as seldom as possible. For instance, once for each workflow invocation.
initialize {
table myTab = tableCreate("myFolder.myProfile", "select user from subscribers" );
}kustoTableCreate
The kustoTableCreate function looks up information in Kusto databases. The resulting table is of the same type as that resulting from tableCreate, meaning that it can be used together with functions such as tableLookup, tableGet etc.
The format of this function is the following:
table kustoTableCreate(string profileName, string, databaseName, string query)Parameter | Description |
|---|---|
profileName | The name of the profile used. This should be an Azure Profile of the type Azure Data Explorer. |
databaseName | The name of the database in Azure. The cluster name is specified in the Azure profile. |
query | The query to apply to the database in question. This is written in the Kusto Query Language, see example below. |
Example - Using kustoTableCreate
table result = kustoTableCreate("Default.AZDLookupDb", "lookupdb", "test | where isnotnull(BeginLocation) | project EpisodeNarrative, EventNarrative | limit 5000");The Kusto data types will be interpreted as the following types in the resulting table object:
Azure Type | APL Type |
|---|---|
bool | boolean |
datetime | date |
dynamic | string |
int | int |
long | long |
real | bigdec |
string | string |
decimal | bigdec |
timespan | string |
tableCreateIndex
Creates an index for one or several columns of a table. This will greatly increase the efficiency of subsequent tableLookup calls on these columns using the equality operator. If the column already has an index, this function has no effect.
Note!
An index will not be created unless there are at least five rows in the table.
void tableCreateIndex (
table tableValue,
int|string column1, ... int|string columnN )Parameter | Description |
|---|---|
| A table object |
| A column index (starting with 0 for the first column), or name for which to create an index. At least one column must be specified. |
Returns | Nothing |
Example - Using tableCreateIndex
initialize {
table myTab = tableCreate("myFolder.myProfile", "select id, user from anum" );
tableCreateIndex( myTab, "id" );
}tableGet
The tableGet function returns the value of a table entry. The value is returned as an any object, which means the returned value is of the same type as the value extracted.
any tableGet (
table tableValue,
int row,
int|string column)Parameter | Description |
|---|---|
| A table object |
| The row index. The first row is indexed 0 (zero). |
| Column index or name. The first column is indexed 0 (zero). |
Returns | Any depending on the column type |
tableLookup
The tableLookup function returns a table containing all the rows of the original table matching the specified column value(s). At least one pair of (column, operator, value) group must be specified.
table tableLookup
( table tableValue,
int|string column1,
string operator1,
string|date|int value1,
any value1a, //Optional (used only for "between" and "not between") ...
int|column columnN,
string operatorN,
any valueN,
any valueNa ) //Optional (used only for "between" and "not between")Parameter | Description |
|---|---|
| A table object |
| Column index or name. The first column is indexed 0 (zero). |
| Operator specifying the range of the requested values. Possible operators are:
|
| Value(s) matching |
Returns | A table matching the query |
Example - Using tableLookup
The following example looks for a specific entry in the in-memory table returned by the preceding tableCreate command.
table myTab;
// To avoid performance problems, the table is read
// from the database once for each time the
// workflow is activated.
initialize {
myTab = tableCreate( "myFolder.myProfile", "select user from subscribers" );
}
consume {
table user = tableLookup( myTab, "user", "=", input.anum );
if ( tableRowCount( user ) > 0) {
// At least one entry found.
} else {
// No entry found.
}
}Note!
When a column is of date type, the matching values must contain date, time, and timezone, as demonstrated in the example below.
Example - Using tableLookup when a column is of date type
table myTab;
initialize {
myTab = tableCreate("myFolder.myProfile","select date_field from all_dates");
}
consume {
date dateFilter;
//The matching value must contain date, time, and timezone.
strToDate(dateFilter,"01/05/2016 00:00:00","dd/MM/yyyy HH:mm:ss","CET");
table myLookup = tableLookup(myTab,"date_field","=",dateFilter);
if ( tableRowCount( myLookup ) > 0) {
// At least one entry found.
} else {
// No entry found.
}
}tableRowCount
The tableRowCount function returns the number of rows in a table.
int tableRowCount (table tableValue )Parameter | Description |
|---|---|
| A table object |
Returns | An integer stating the number of rows found. |
tableGetMetaInfo
The tableGetMetaInfo function returns the column name, column type, index status and table name.
list<ColumnMetaInfoUDR> tableGetMetaInfo(table tableValue);Parameter | Description |
|---|---|
| A table object |
Returns | A list where each element is of type |
Note!
For Oracle database, ‘tableName’ and ‘isIndex’ values are not supported.
Note!
The tableGetMetaInfo function does not support table objects returned from the Database Table Functions | tableGetMetaInfo function or from the Shared Table Profile | APL (tableCreateShared and tableRefreshShared).
Example - Using tableGetMetaInfo
table myTab = tableCreate("Default.MySql", "select id, name, remark from test" );
list<ColumnMetaInfoUDR> columnInfoList = tableGetMetaInfo(myTab);
// Get the number of columns.
int columnCount = listSize(columnInfoList);
// Get the meta info of the first column
ColumnMetaInfoUDR firstColInfo = listGet(columnInfoList, 0);
string colName = firstColInfo.columnName; // get column name
string colType = firstColInfo.columnType; // get column type
boolean isIdx = firstColInfo.isIndex; // get index status (boolean type)
string tblName = firstColInfo.tableName; // get table nameNote!
ColumnType format follows Data types format.