Versions Compared

Old Version 3

changes.mady.by.user Chee Hove Teng

Saved on

compared with

New Version Current

changes.mady.by.user Jenni Wallin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 auto commit after the function has executed.

...

Note

Note!

For MS SQL, the column type timestamp is not supported in tables accessed by Image RemovedMediationZone. 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

dbProfile

Name of the database where the table resides, including folder name.

sqlQuery

SQL query to send to the database. Note that SQL statements must not end with ';'. Calls to stored procedures must be embedded in blocks.

Info

Examples - Valid for Oracle databases

 "<SQL query>"
"BEGIN <procedure>; END;"

Returns

An integer equaling the number of rows updated or inserted.

...

Code Block
table tableCreate ( 
 string dbProfile, 
 string sqlQuery 
 boolean disableCommit)

Parameter

Description

dbProfile

Name of the database where the table resides.

sqlQuery

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.

disableCommit

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.

Info

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

...

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

Code Block
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

...

Code Block
void tableCreateIndex ( 
 table tableValue, 
 int|string column1, ... int|string columnN )

Parameter

Description

tableValue

A table object

columnN

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

Code Block
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.

Code Block
any tableGet ( 
 table tableValue, 
 int row, 
 int|string column)

Parameter

Description

tableValue

A table object

row

The row index. The first row is indexed 0 (zero).

column

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

tableValue

A table object

columnN

Column index or name. The first column is indexed 0 (zero).

operatorN

Operator specifying the range of the requested values. Possible operators are:

  • =

  • !=

  • >

  • <

  • <=

  • >=

  • between - Requires two operands, (valueN, valueNa).

  • not between - Requires two operands, (valueN, valueNa).

  • starts with - The operator can only be applied to string columns.

valueN/ValueNa

Value(s) matching columnN and operatorN

Returns

A table matching the query

...

Code Block
int tableRowCount (table tableValue )

Parameter

Description

tableValue

A table object

Returns

An integer stating the number of rows found.

...

Code Block
tableGetMetaInfo(table tableValue);

Parameter

Description

tableValue

A table object

Returns

A list where each element is of type ColumnMetaInfoUDR.

...

Note!

For Oracle db, ‘tableName’ and ‘isIndex’ functions are not supported.

note
Info

Example - Using tableGetMetaInfo

Code Block
table myTab = tableCreate("Default.MySql", "select id, name, remark from test" );
list<ColumnMetaInfoUDR> columnInfoList = tableGetMetaInfo(myTab);

// Display the number of columns.
int columnCount = listSize(columnInfoList);
// Get the meta info of the first column
ColumnMetaInfoUDR firstColInfo = listGet(columnInfoList, 0);
firstColInfo.columnName  // get column name
firstColInfo.columnType  // get column type
firstColInfo.isIndex  // get index status (boolean type)
firstColInfo.tableName  // get table name
Note!
ColumnType format follows Data types format.