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


Advertisements

Virtual tables

Q:

We want to use sql server to act as our database. From what I have read, it sounds like vTables should be able to do this.

Lianja connects to the DSN I have setup without problem. Then I created a new database and imported the tables from the DSN and created vtables for all. Just to test things out, we created a grid using the vtable vt_client. The data seems to display without any issue.

Problem: 
The data displays fine and editing the data or adding to it seems to work as well. We can make changes and add new entries and it saves it successfully. BUT the data changes doesn’t communicate through to sql server. How do we accomplish this?

A:

Make sure that you have specified a primarykey for the Virtual Table. This is the KEYFIELDLIST clause in the CREATE VIRTUALTABLE command or ‘4 Key field list’ in the MODIFY A VIRTUAL TABLE dialog. This can also be used to exclude fields from the internal WHERE clause used for updates and deletes, e.g. ‘*,-id,-last_update’ would use all fields except the id and last_update fields.

Also use the updatefieldlist PROPERTY to exclude any fields that should not be updated, e.g. ‘updatefieldlist=*,-id,-last_update’.

The dbtype property can also be specified: ‘dbtype=mssql’.

A2:

You need to make sure that you have a primary key on your SQL Server table and that you specify it as the primary key in you Virtual table definition.


dynamic connectstring.

Code:
use mydb.dbo.mytable connstr "driver={SQL Server};server=myserver;Trusted_Connection=Yes"

//Also:

use mydb.dbo.mytable connstr "driver={SQL server};server=myserver;Trusted_Connection=Yes" alias mytemp.

select mytemp
browse

 


When creating virtual tables that use one of the newer versions of SQL Server, be sure to use the latest available ODBC drivers.

Code:
create virtualtable vt_mytable connstr 'driver={={ODBC Driver 13 for SQL Server};server=.;uid=xxxx;pwd=xxxx' keyfield 'PK1' properties 'keepalive=1;dbtype=mssql' as select * from mytable.

The performance improvement is very noticeable.


Q:

I’ve set up a grid with a virtual table (using VFP ODBC.) I set up a calculated field (balance = totalbudget-totalexpenses.) It works fine and calculate accurately in development view, but not so in app view, web view, tablet view, etc. There it gives me a zero.

Name:  lianja grid problems.jpg Views: 31 Size:  76.7 KB

A:

Look at the example web apps and verify that the server is running and they are working.

You need to make sure you have deployed your database containing your virtual table definitions.

Calculated columns are not yet available in the web/mobile grids.

Are you hiding the section header? [ I’m hiding a few columns, not just headers (as far as I can tell.) ] If you look at the examples you will note that this works as expected so sonething else is causing this.


Q:

My app works fine in development and web app view, but when I use Preview to see it running in the browser, I get a Server connection lost -message. Demo apps run fine, as do test apps using Lianja SQL server. I made sure that the virtual table has a primary key. Is ODBC problematic with apps running in the browser, or am I maybe missing something?

I will try the demo that use ODBC/VT’s once I have installed MySQL on my dev machine to see if I missed something in the setup.

Name:  Lianja Server Failed to Respond.jpg Views: 76 Size:  26.9 KB

A:

No, ODBC is fine in web and mobile apps.

Have you deployed the database containing your virtual table? Web app view uses your development data whereas preview uses deployed data so the database needs to be deployed.


Q:

Are there any restrictions with regards to accessing virtual tables using a SQL SELECT statement?

I’m finding that when working in the Console work space:

with the southwind database open…

select * from vt_orders –> expected 822 records.
select * from southwind!vt_orders –> 0 records with no error.

with the southwind database closed…

select * from vt_orders –> file does not exist error.
select * from southwind!vt_orders –> file does not exist error.

 

A:

You need to create vt_orders2 as select * from southwind!orders and it works with and without the database open.

with the southwind database open…
select * from vt_orders2 –> expected 822 records.
select * from southwind!vt_orders2 –> expected 822 records. 

with the southwind database closed…
select * from vt_orders2 –> file does not exist error as this virtualtable is in a database not a free table
select * from southwind!vt_orders2 –> expected 822 records. 

Name:  Screen Shot 2017-04-08 at 3.03.06 PM.jpg Views: 25 Size:  135.4 KB

Name:  Screen Shot 2017-04-08 at 3.09.39 PM.jpg Views: 25 Size:  139.4 KB

The basis behind using virtual tables with OData is that you create the VT with the join(s) you require.

You can however create VTs which are stored procedure calls.

Currently these stored procedures are executed on the ODBC target backend but there are plans to add and recognize “localcall” which will provide the ability to call local Lianja/VFP stored procedures to fulfill a request. The way this handles parameters has not yet been finalized but its likely to be with a $params argument extension to OData.

This also means that if you want the “any data” back end capabilities that virtual tables provide, and you plan on accessing them via the OData interface, you won’t be able to use any complex joins unless you perform the join on the client or in a server side function.”

Thats not strictly speaking true.

If you want “any backend” you create your VT’s in separate database containers and assign these to users in their tenancy.

I think “localcall” is the best way forward for joining disparate data sources. I will look into it.




You can access remote databases readonly without the need to setup a VirtualTable definition.

Code:
use sales.customers connstr "lianja_mssqltest"
list first 25

also…

Code:
use sales.customers connstr "lianja_mssqltest" where condition
list first 25

and…

Code:
use sales.customers connstr "lianja_mssqltest" as select * from sales.customers where condition order by column
list first 25

Once you have these “local cursors” you can join disparate databases as Lianja will build the required join indexes dynamically for you.

So, you can dynamically fetch data from remote databases with very little effort. If you need to update data you will need to setup a VirtualTable definition that describes the primary key and other pertinent information such as updatefldlist etc.

Here is an example.

Name:  Screen Shot 2017-04-08 at 3.40.26 PM.jpg Views: 66 Size:  140.9 KB


Q2:

It also works when specifying a dynamic connectstring. Like so

use mydb.dbo.mytable connstr “driver={SQL server};server=myserver;Trusted_Connection=Yes”

2 questions.
1. once I call the command, how can I reference the cursor? I can list the data, but I can’t seem to browse it.
2. When I call the command a second time, it is telling me the database is already open. Is there a connection that I need to close?

A:

Give it an alias name then SELECT aliasname

LIST Status to see what the default was.

One you have performed the query against MSSQL you can get a reference to the CursorAdaptor() and perform requery() on it.

use mydb.dbo.mytable connstr “driver={SQL server};server=myserver;Trusted_Connection=Yes” alias mytemp.

select mytemp
browse


Q:

I setup an ODBC connection to my sql server, and am now working with vTables.

So on the table Clients, my name field type in sql server is nvarchar(Max). I am trying to make a Lianja table that displays all my clients, but in the name field I only get the word Memo, that looks like a hyperlink.

I have seen in a form I can get it to display correctly if I use a textbox. But how do I manage this in a table?

A:

A varchar maps into a memo field as it can be an indeterminate length. I’m puzzled as to why a name field would be a varchar as it is problematic to work with as both a primary key and in IDEs.

Nevertheless you could use data mapping to convert the varchar to a fixed length and pad it out if nescessary.


Q:

The data i.e. *.dbf files (VFP free tables) are in one drive (D, different from the application drive (C. These tables are updated daily by the data entry staff.

Besides using virtual tables, is there another way to develop a Web App to query/update the records ?

A:

why you do not want to use VirtualTable?

it’s simple and fast..


 VT can actually run a stored procedure or make a web service call or odata etc., and the return the resultset.