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
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
open database southwind use vt_orders ? vtinfo(vt_orders,"basetable") use vt_employees in 3 ? vtinfo(3,"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
, different from the application drive (C
Lianja developer 