OBJECTTABLE

The OBJECTTABLE function processes an object input to produce tabular output. The function itself defines what columns it projects. The OBJECTTABLE function is implicitly a nested table and may be correlated to the preceding FROM clause entries.

Usage

OBJECTTABLE([LANGUAGE lang] rowScript [PASSING val AS name ...] COLUMNS colName colType colScript [ DEFAULT defaultExpr] ...) AS id

Parameters

Syntax Rules

• The column names must not contain duplicates;

• The CData Virtuality Server will place several special variables in the script execution context. The CommandContext is available as dv_context. Additionally, the colScripts may access dv_row and dv_row_number. dv_row is the current row object produced by the row script.  dv_row_number is the current 1-based row number;

• rowScript is evaluated to an Iterator. If the result is already an Iterator, it is used directly. If the evaluation result is an Iteratable, then an Iterator will be obtained. Any other object will be treated as an Iterator of a single item. In all cases, null row values will be skipped.

While there is no restriction on what can be used as a PASSING variable name, you should choose names that can be referenced as identifiers in the target language. 

Examples

1. Accessing special variables:

SELECT x.* FROM OBJECTTABLE( 'dv_context' COLUMNS "user" string 'dv_row.userName' , row_number integer 'dv_row_number' ) AS x

The result is a row with two columns containing the username and 1, respectively.

Due to their mostly unrestricted access to Java functionality, usage of languages other than dv_script is restricted by default.

Data roles are also secured with Data Roles using language permission.

The admin-role does not include the necessary permissions to use Python by default. To grant Python access for users with the admin-role, you need to add the L permission for the "python" resource via a custom role and assign this custom role to the intended users.

L permission for Python removed from the admin-role since v4.9

dv_script

dv_script is a simple scripting expression language that allows access to passing and special variables and non-void 0-argument methods on objects and indexed values on arrays/lists. A dv_script expression begins by referencing the passing or special variable. Then any number of '.' accessors may be chained to evaluate the expression to a different value. Methods may be accessed by their property names, for example, foo rather than getFoo. If the object is both a getFoo() and foo() method, the accessor for references foo() and getFoo() should be used to call the getter. An array or list index may be accessed using a 1-based positive integral value - using the same '.' accessor syntax. The same logic as the system function array_get is used, meaning that null will be returned rather than an exception if the index is out of bounds.

dv_script is effectively dynamically typed as typing is performed at runtime. If an accessor does not exist on the object or if the method is not accessible, an exception will be thrown. If at any point in the accessor chain an expression evaluates to a null value, then null will be returned.

Examples

1. Get the VDB description string:

dv_context.session.vdb.description

2. Get the first character of the VDB description string:

dv_context.session.vdb.description.toCharArray. 1

3. Sleep function:

• JavaScript example:

SELECT "x.result" 
FROM (OBJECTTABLE(     
	LANGUAGE 'javascript'     
	'java.lang.Thread.sleep(5000);     
	"success";'      
	COLUMNS "result" string 'dv_row') AS x);; 

• Python example (Linux-based server only; for more information on Python support, see Running Python from the Code Editor):

SELECT "x.result"
FROM (OBJECTTABLE(    
    LANGUAGE 'python'    
'import time
time.sleep(5000)    
"success"'     
    COLUMNS "result" string 'dv_row') AS x);;

This script executes a 5000 milliseconds long sleep function; it deliberately does nothing for 5 seconds. To have some output afterwards, the COLUMNS statement is used in combination with dv_row. The special iterator dv_row automatically gets the result of the last statement in the script. It is simply the string success in the example above. This will return the constant expression as the column result. If the result to return is a scalar value, the rowScript 'dv_row' is used.

4. How the output is made:

• JavaScript example:

SELECT x.* 
FROM ( OBJECTTABLE(    
	LANGUAGE 'javascript' 
	'var rows = [];        
	firstrow = { "col1": "foo", "col2": "bar" };        
	rows.push( firstrow );        
	secondrow = { "col2": "foo", "col3": "bar" };        
	rows.push( secondrow );        
	rows;'         
	COLUMNS         
	"col1" string 'dv_row.col1',        
	"col2" string 'dv_row.col2',        
	"col3" string 'dv_row.col3',        
	"col4" string 'dv_row.col4' ) AS x);;

• Python example (Linux-based server only; for more information on Python support, see Running Python from the Code Editor):

SELECT x.*
FROM ( OBJECTTABLE(   
    LANGUAGE 'python'
'rows = []       
for j in range (0,10):
    rows.insert(j,{"col" + str(i) : i+j for i in range(1,5)})
rows'        
    COLUMNS        
    "col1" string 'dv_row.col1',       
    "col2" string 'dv_row.col2',       
    "col3" string 'dv_row.col3',       
    "col4" string 'dv_row.col4' ) AS x);;

The script creates a collection of name rows and adds tuples onto it using the rows.push() (JavaScript)/rows.insert() (Python) function. Each tuple can have entirely different property names and values, but they must all be present in the COLUMNS statement if they shall be returned. It is even possible to return non-existent properties. These will all be NULL. Note the notation for accessing the different properties: dv_row.<propertyName> and also, remember that the collection rows must be the script's last statement.

5. Function applied to a table column:

• JavaScript example:

SELECT "k.Name","x.Reversed"  
FROM "SYS.DataTypes" AS "k", OBJECTTABLE(       
			LANGUAGE 'javascript' 
			'function reverse(s){ return s.split("").reverse().join(""); }                 
			reverse(tabname);'                  
			PASSING "k.Name" AS "tabname"                 
			COLUMNS  "Reversed" string 'dv_row' ) AS x;;

• Python example (Linux-based server only; for more information on Python support, see Running Python from the Code Editor):

SELECT "k.Name","x.Reversed" 
FROM "SYS.DataTypes" AS "k", OBJECTTABLE(      
            LANGUAGE 'python'
'def reverse(s):
    return s[::-1]                
reverse(tabname);'                 
            PASSING "k.Name" AS "tabname"                
            COLUMNS  "Reversed" string 'dv_row' ) AS x;;

A script can also be applied to values from tables and views. The above script creates a function that reverses a string. Due to the combination with the table SYS.DataTypes, the script is executed for each row in the table. The PASSING statement declares that the value from k.Name shall be redirected as value for the variable tabname in the script. Passing constant expression, variable names from SQL scripts, and table columns is possible.