Versions Compared

Old Version 3

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Former user

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).

...

Note
titleNote!

For MS SQL, the column type timestamp is not supported in tables accessed by  . Use column type datetime instead. 

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).

...

ParameterDescription

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
titleExamples - 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).

...

Info
titleExample - 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.

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

...

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

boolboolean
datetimedate
dynamicstring
intint
longlong
realbigdec
stringstring
decimalbigdec
timespanstring

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.

...

Info
titleExample - 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.

...

ParameterDescription

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

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.

...

Info
titleExample - Using tableLookup when a column is of date type


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

...