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, starts at 0) or string (column name).
All functions without the parameter disableCommit=false
will not be commit after the function has executed.
The table lookup only supports int, string and date types.
Note |
---|
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 type. |
...
Code Block |
---|
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.
| ||
Returns | An integer equaling the number of rows updated or inserted. |
...
Code Block |
---|
table tableCreate ( string dbProfile, string sqlQuery boolean disableCommit) |
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. | ||
| 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.
| ||
Returns | A table |
...
Code Block |
---|
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. |
Info | ||
---|---|---|
kustoTableCreate Example
|
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 |
...
Code Block |
---|
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 |
Info | ||
---|---|---|
Example - Using tableCreateIndex
|
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.
Code Block |
---|
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 |
...
Code Block |
---|
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 |
...
Code Block |
---|
int tableRowCount (table tableValue ) |
Parameter | Description |
---|---|
| A table object |
Returns | An integer stating the number of rows found. |
...
Code Block |
---|
tableGetMetaInfo(table tableValue); |
Parameter | Description |
---|---|
| A table object |
Returns | A list where each element is of type |
...
Note!
For Oracle db, ‘tableName’ and ‘isIndex’ functions are not supported.
Info | ||
---|---|---|
Example - Using tableGetMetaInfo
| ||
Note!
ColumnType format follows Data types format.