Virtual table [examples]

Creates a virtual table
CREATE_VIRTUALTABLE
open database advworks
create virtualtable scurr ;
    alias "scurr" ;
    connstr "awhr" ;
    as select * from sales.currency
 
// Example including account details
open database orahr
create virtualtable vt_locactions ;
    alias "vt_locactions" ;
    connstr "oracle_1;uid=hr;pwd=hr" ;
    as select * from locations
 
// Example including KEYFIELDLIST and PROPERTIES (updatefieldlist)
open database sak
create virtualtable vt_actor ;
    alias "actor" ;
    connstr "sakila" ;
    keyfieldlist "actor_id" ;
    properties "updatefieldlist=*,-actor_id" ;
    as select * from actor

Modifies a virtual table
ALTER_VIRTUALTABLE
open database advworks
create virtualtable scurr connstr "awhr" as select * from sales.currency
alter virtualtable scurr connstr "awhr" as select name from sales.currency
 
// Modifying properties example
open datables sakdata
create virtualtable vt_actor connstr "sakila" keyfieldlist "actor_id" as select * from actor
alter virtualtable vt_actor addproperty "updatefieldlist=first_name"
alter virtualtable vt_actor modifyproperty "updatefieldlist=first_name, last_name"
alter virtualtable vt_actor removeproperty "updatefieldlist"
alter virtualtable vt_actor properties "updatefieldlist=last_name;keepalive=1"
alter virtualtable vt_actor properties ""

https://www.lianja.com/doc/index.php/ALTER_VIRTUALTABLE


Information about a virtual table
VTINFO
open database southwind
use vt_orders
? vtinfo(vt_orders,"basetable")
use vt_employees in 3
? vtinfo(3,"connstr")

Specify a default ‘connstr’ for Virtual Tables
SET_CONNSTR

 

set connstr to 'local'
? set('connstr')
local

https://www.lianja.com/doc/index.php/SET_CONNSTR


//
// Assume we have a grid section which is bound to a VT called vcustomers
// and the vcustomers VT was created with
//
// create virtualtable vcustomers 
//     connstr “local” 
//     properties “pkwhere=1 eq 0“
//     as select * from southwind!customers {pkwhere}
//
mysection = Lianja.get(“page1.section1”)
mysection.setNamedParameter("pkwhere", " where customerid like 'A%'")
// The special named parameter defaultnamedparameters when set to “0” causes
// default named parameters in the VT properties to be ignored
mysection.setNamedParameter("defaultnamedparameters", "0")
mysection.reOpenTable()

https://www.lianja.com/doc/index.php/Using_Parameters_Views


You can use “local” stored procedures for Virtual Tables.

Virtual Tables provide transparent access to third party SQL backends.

Occasionally you want to fetch data from several backend servers and join the cursors or filter the records before presenting to the user.

You can create a Virtual Table with a “local” connection string and then specify the SQL command as a call to a stored procedure in the database.

create virtualtable vtorders connstr "local" as call sp_getorders()

Here is the stored procedure which is executed locally:

// This is an example of a "local" stored procedure
select * from southwind!orders into cursor localorders
return setResultSet("localorders")

The stored procedure can be quite sophisticated, including joins and filters, and the key to using these “local” stored procedures is to always return back the resultset using the setResultSet(“alias”) function as shown in the example above.

If your virtual table is to be used from the web/mobile clients, the basetable must be set using the TABLE clause of CREATE VIRTUALTABLE or ALTER VIRTUALTABLE:

create virtualtable vtorders connstr "local" table "orders" as call sp_getorders()

or

create virtualtable vtorders connstr "local" as call sp_getorders()
alter virtualtable vtorders table "orders"

Parameter substitution occurs when {arg} or ?arg is specified:

create virtualtable vtorders connstr "local" table "orders" ;
           as call sp_getorders("{m_arg1}", "{m_arg2}")

or

create virtualtable vtorders connstr "local" table "orders" ;
          as call sp_getorders(?m_arg1,?m_arg2)
// This is an example of a "local" stored procedure using parameters
select * from southwind!orders where customerid = ?m_arg1 ;
            and employeeid = ?m_arg2 into cursor localorders
return setResultSet("localorders")

In desktop apps, the parameters must be declared as public variables. In OData calls from the Web/Mobile clients, these are specified using the $args argument.

The OData $args argument is set to a comma-separated list of parameters. These are parsed and created as public variables containing the specified values. The first value is assigned to a public variable named m_arg1, the second to m_arg2 and so on.

Here, the public variable m_arg1 will be created and will contain the value “ALFKI” and the public variable m_arg2 will be created and will contain the value 4.

http://localhost:8001/odata/southwind/vtorders?$args=ALFKI,4

So, if your stored procedure is to be used from both desktop and the web/mobile clients, the m_argN naming convention should be used.

If parameters are optional, their existence can be checked in the stored procedure itself, e.g.

create virtualtable vtorders connstr "local" table "orders" as call sp_getorders()
// This is an example of a "local" stored procedure using optional parameters
if isset(m_arg1) and isset(m_arg2)
	select * from southwind!orders where customerid = ?m_arg1 ;
                     and employeeid = ?m_arg2 into cursor localorders
else
	select * from southwind!orders into cursor localorders
endif
return setResultSet("localorders")

https://www.lianja.com/doc/index.php/Virtual_Tables_-_Local_Stored_Procedures


// create virtual table and specify primary key
create virtualtable vt_actors connstr "lianja_mysqltest" alias "vt_actors" primarykey "actor_id";
       properties "keepalive=1;rowid=1" as select * from actor 
// or alter an existing virtual table
alter virtualtable vt_actors primarykey "actor_id"
// keyfieldlist and primarykey are synonymous
alter virtualtable vt_actors keyfieldlist "actor_id"

https://www.lianja.com/doc/index.php/Virtual_Tables_-_Lianja_Cloud_Server


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.