[Answers] Virtual table

Printable View

2015-10-20, 03:06
josipradnik

[Answers] Virtual table

Virtual tables use the ALTER VIRTUALTABLE command and the corresponding CREATE VIRTUALTABLE command. From the Data Workspace, you can also use 'Modify virtual table...' from the 'Additional commands' under Tables.
If you want to modify the columns, you need to change the 'SQL command' for the virtual table. In the CREATE/ALTER VIRTUALTABLE commands, this is the 'AS <select statement>' clause.

Q:
May I know the difference between Virtual Tables and Cursor Adaptors?
A:
When you open a virtual table, you can access its underlying Cursor Adaptor and then the Cursor Adaptor properties and methods.

Code:

open database southwind use vcust ca = cursoradaptor() // specify the alias if not currently selected ? ca.selectcmd

In your virtualtable definition, add the following Properties:

Code:

basetable=srv_tabledef;translatename=false

and set the basetable and translatename=false for other tables with an underscore in the real table name.

The sql attribute is used to populate the virtualtable, so yes it refers to the 'real' table, not the virtualtable. Here's a MS SQL Server example on Sales.Customer from AdventureWorks.
I've created the vt from the Data Workspace then amended the select statement on the vt to 'where 1=0' so I start with no records. My section.ready sets the sql (for the vt) so that I get all rows with a territoryid of 8:

Code:

//////////////////////////////////////////////////////////////// // Event delegate for 'ready' event proc page1_section1_ready() Lianja.get("page1.section1").sql = "select * from Sales.Customer where territoryid = 8" endproc

And that's what I get:

http://www.lianja.com/community/atta...7&d=1398866142

Note that in my example I am referencing Sales.Customer (the MS SQL Server Schema.Table). You should be able to use the ODBC tab in the Console, connect to your ODBC DSN and successfully run the SQL statement you are specifying in the Section.sql attribute, in the same way you should be able to run the 'SQL command' in your original vt definition.

Looks like your Page and your Section are both called abicab - is that correct? If so, lianja.get("AbiCab") will reference the Page. You will need lianja.get("Abicab.AbiCab").sql for the section sql attribute and I would recommend using a different naming convention.

Property is QuoteString as in cursoradapter.QuoteString.

If you are specifying a DSN that does not or cannot store the username and password details, the account information should be included in the CONNSTR, e.g. 'mydsn;uid=myusername;pwd=mypassword'. So, if your connection is working correctly from the ODBC Console when you specify the username and password, include these in the CONNSTR clause of create virtualtable or the 'ODBC Connection' when you use the create dialogs in the Data Workspace.

Once you have the DSN configured or the connection string established, you can test the ODBC connection in the Lianja App Builder ODBC Console and build Lianja Virtual Tables in the same way regardless of your Lianja App Builder or MySQL Server platform.

Before you create the table, from the Lianja/VFP Command Window in the Console Workspace, issue:
set debug on
Carry on and try to create your virtual table. If it still fails, exit the App Builder and check your Lianja\debug\ folder for debug text files.
For ODBC Connections requiring a username and password, where the account details are not or cannot be stored in the DSN, these need to be specified on the ODBC Connection string for the virtual table, e.g.

Code:

mydsn;uid=username;pwd=password

Q:
create virtual table from a database other than local use ..
A:
Yes, you should just be able to include the database-name!table-name in the Select statement.

you need to specify a Database, not a table.
The database needs to be deployed / included in the deployment package.
Have you deployed the database? The Lianja SQL Server accesses the data in the end user / runtime directory structure, just like the App Center
To create a virtual table in the App Builder, you can use local as the connection string to base it on a table in the local Lianja database.
To access Lianja databases and tables via the Lianja SQL Server from an ODBC Client application, they need to be deployed to the Lianja SQL Server data directory.

You probably need to exclude autoinc fields in the VT definition.

To test the connection select the "ODBC" tab in the "Console" workspace. You may not have the DSN configured correctly.

http://www.lianja.com/community/atta...5&d=1372290147

Please also note that for the CREATE VIRTUALTABLE syntax, the odbcdsn connection string is in quotes and the select_statement isn't.
CREATE|ALTER VIRTUALTABLE virtual_table_name
[IF NOT EXISTS]
[ALIAS name]
CONNSTR "odbcdsn"
AS select_statement
create virtualtable salescurr connstr "awhr" as select * from sales.currency

full support for Virtual Tables in the Web/Mobile clients.

Without a primary key it is not possible to handle any updates but also it is not possible to handle any pagination which will not scale and would be unusable in a web / mobile app scenario. The primarykey should be specified in the VT properties.
When no SQL SELECT is specified Lianja makes use of the basetable property for updates.
It does however need a primarykey as it generates the backend SQL SELECT statement dynamically when fetching a row or a page of rows to display in the Web Client. It is impractical to read millions of rows into a web browser or a mobile device. Lianja therefore reads on demand and the client fetches x rows starting at a specific offset.

It is better to alter the virtualtable through the GUI.
Alternatively you can use alter virtualtable NOT alter table.
You cannot drop a column from a virtualtable, you must specify the column list in the sql select or exclude autoinc columns.

Q:
I have a page with a grid section. I want to display data retrieved from several tables and display on the grid for read only
Therefore, data source for the grid is from a SQL rather from a single table.
May I know how I add a column from my SQL result set onto the grid?
A:
You have two ways to achieve this when using SQL.
1. Specify the columns by name and column position in the SQL SELECT statement I.e Select name,name from ...
or
2. Create a virtual table.
Just set the ODBC DSN to "local" (no quotes) and the virtual table will work against the embedded Lianja database engine as opposed to an out of process sql database server.

In V1.3 you can use ALTER VIRTUALTABLE to change each clause e.g. just the CONNSTR.

I now have Virtual Tables working in the Cloud Server. There is still some tuning, QA and testing to go through before release but they are behaving as expected.
In the process of implementing these I have also added a "DateTime Picker" for DateTime fields as I know this is an important requirement for you. I will localize this before release.
This is all working with VTs as well as native Lianja tables.

http://www.lianja.com/community/atta...5&d=1416391422

http://www.lianja.com/community/atta...6&d=1416391450

All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352
2015-10-20, 03:09
josipradnik

As I have progressed with VTs in the cloud server I am keeping the ODBC connections alive when the request completes. These are in fact pooled now and the connections are released after a TTL has expired.
I have added the TTL as a property on the VT e.g properties="keepalive=1;ttl=30". The default is 30 which causes them to keep alive for 30 minutes *if* you have specified keepalive=1.
This causes quiescent connections to be automatically closed. This is exactly what happens with cloud server connections currently. When there is no activity on a keep-alive connection for a period of time they automatically clean up and exit.
So to recap, when the HTTP server has a keep-alive and there is a permanent connection between the client and the server, the ODBC connections are pooled based on their connection string.
This results in better performance as there is no need to re-connect to the ODBC datasource e.g. MSSQL or MySQL if their is an existing connection.
This provides a seamless connection between the web client and the database server that is proxied through the Cloud Server.
Just to clarify, this also works the same in the desktop client.
TIP
You can also specify the packet size used to transfer data between the database server and the client (desktop or cloud server) using:
ALTER VIRTUALTABLE vt_name properties "SQL_ATTR_PACKET_SIZE=1048576"
In the above example the packet size is set to 1MB. This results in less round trips between the client and the server at the expense of more memory usage.
If you have configured your system for a large number of users and the database server is running on a different machine connected on a LAN (10Gbe+ recommended) then be sure to configure your TCPIP settings to specify a larger buffer size also.

I have now implemented VTs in the cloud server as well as full support for "CursorAdaptors", "Networkrequest" and COM/ActiveX.
This is the small script I have used for testing a server side .rsp page running with the IIS extension and all seems to behaving as expected. I am currently working on integrating VTs in with the Web/Mobile client.

Code:

<%@ Language=VFP %> <html> <body> <% //////////////////////////////////////////////////////////////////////////////////////// private obj private querytime private ca ? "<h3>createObject('cursoradaptor')</h3>" obj = createObject("cursoradaptor") ? "<pre>" ? obj ? "</pre>" ? "<hr>" //////////////////////////////////////////////////////////////////////////////////////// ? "<h3>createObject('networkrequest')</h3>" obj = createObject("networkrequest") ? "<pre>" ? obj ? ? "Data returned below" ? obj.getData("http://www.lianja.com/testdata.txt") ? "</pre>" ? "<hr>" //////////////////////////////////////////////////////////////////////////////////////// open database barry timeofday(0) //////////////////////////////////////////////////////////////////////////////////////// ? "<h3>Open Virtual Table to MySQL</h3>" ? "<pre>" text raw use vt_actors where last_name='GABLE' list html off endtext ? "</pre>" use vt_actors where last_name='GABLE' querytime = "<p>" + etos(reccount()) + " record(s) selected in " + timeofday(7) + "</p>" list html off ? querytime ? "<pre>" text raw ca = cursoradaptor() ca.requery("last_name='PALTROW'") list html off endtext ? "</pre>" ca = cursoradaptor() ca.requery("last_name='PALTROW'") querytime = "<p>" + etos(reccount()) + " record(s) selected in " + timeofday(7) + "</p>" list html off ? querytime ? "<hr>" //////////////////////////////////////////////////////////////////////////////////////// ? "<h3>Open Virtual Table to MSSQL</h3>" ? "<pre>" text raw use vt_customers where Accountnumber='AW00000006' list html off endtext ? "</pre>" use vt_customers where Accountnumber='AW00000006' querytime = "<p>" + etos(reccount()) + " record(s) selected in " + timeofday(7) + "</p>" list html off ? querytime ? "<pre>" text raw ca = cursoradaptor() ca.requery("Accountnumber='AW00000008'") list html off endtext ? "</pre>" ca = cursoradaptor() ca.requery("Accountnumber='AW00000008'") querytime = "<p>" + etos(reccount()) + " record(s) selected in " + timeofday(7) + "</p>" list html off ? querytime ? "<pre>" text raw ca.requery("Accountnumber='AW00000009'") list html off endtext ? "</pre>" ca.requery("Accountnumber='AW00000009'") querytime = "<p>" + etos(reccount()) + " record(s) selected in " + timeofday(7) + "</p>" list html off ? querytime %> </body> </html>

Notice how I am using USE ... WHERE and requery() extensively as this keeps the connection to the database server alive while I am processing the data.
This is the output from running this.

http://www.lianja.com/community/atta...2&d=1415680602

Q:
if we aren't using IIS but rather just hitting the CloudSever, is the connection to the database kept alive without frequent requeries?
A:
Yes to your first question. The server honors HTTP keep alives standalone or with IIS but ODBC connections are not kept alive they need to be made on each request. Once you have made one connection though the others are quicker as everything is in memory (driver and DLLs etc)

Q:
if the database connection is not kept alive, will the cursoradapter object will be alive and reinstitute the database connection?
A:
No to your second question as the web is stateless. You can however use the _session[] associative array to maintain your own state. This associative array (object in fact) can have key values updated and these persist across requests based on the session id.

Q:
a) is there a known TTL? Is there a way we can know in advance of trying to use the CA whether the ODBC connection is still alive?
b) I assume you will show us how to use the associative array when the time comes.
c) If the VT instance had an ID tied to the associative array, couldn't this reconnection be handled seamlessly by the cloudserver? Or is that part of how VTs in the web client will work, in fact?
A:
the connection between the web client and the server is determined by http keep alives. The server connection will not keep the ODBC connection open as each request needs to reconnect.
_session[] operates in the same way as PHP $_session[]. The array contents persist between requests.
_session["name"] = "hank"

I have also added "dbtype" as a property on cursor adaptors so you can see what type of database connection you have and adjust any SQL that you use in the requery() method.

There is in fact an undocumented function that will return information about a Virtual Table.
The string returned is a | separated list.
virtualtable|alias|connstr|primarykey|properties|s qlselect
You can then split this up and substitute what you want.

http://www.lianja.com/community/atta...3&d=1412139177

Q:
you can enter the same table, in two separate sections, in the same page, but not on the same record?
eg:
section1
warehouse.id
warehouse.descrption
warehouse.refilling
section2
warehouse_2.id
warehouse_2.descrption
where is the referred to warehouse_2.id warehouse.refilling
in VFP can enter the Data Environment the same table with two different aliases .. on Lianja?
A:
No you cannot have two sections on the same page against the same table on different records.
The data binding connects the UI View with the underlying data source. If two sections bind to the same underlying data source they have an "Active record" and thats what they display.
You can however (as you already know) call delegates that SAVE and RESTORE DATASESSION to lookup data without affecting the active record.
If you want to achieve that use virtual tables.

Q:
I can not create VT over Lianja's tables ... without ODBC?
Something like local view on VFP...
A:
A VT can be created against a local table. Just specify "local" as the ODBC Connection and enter a SQL statement.
Create a virtual table with a "local" DSN. That is proxied directly into the embedded database engine. It does not use ODBC nor talk to a remote server.

All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352
2016-03-14, 04:16
josipradnik

Q:
Are virtual tables now supported in grid section for Web/Mobile?
A:
Yes, there is VT support in the 1.3 RC 10 release.

Q:
using the VT, but, I think, the only way is to use the ODBC right?
So I always have to use Lianja Server ..
I can not create VT over Lianja's tables ... without ODBC?
Something like local view on VFP...
A:
A VT can be created against a local table. Just specify "local" as the ODBC Connection and enter a SQL statement.

Q:
How do I alter a virtual table ?
alter table vt_test drop column id does not work. (also on other fields)
If I try to alter the table in the table section I get: table cannot reload from the backup
A:
Virtual tables use the ALTER VIRTUALTABLE command and the corresponding CREATE VIRTUALTABLE command. From the Data Workspace, you can also use 'Modify virtual table...' from the 'Additional commands' under Tables.
If you want to modify the columns, you need to change the 'SQL command' for the virtual table. In the CREATE/ALTER VIRTUALTABLE commands, this is the 'AS <select statement>' clause.

Q:
everytime 'use vtTable where ... ' is used, it can be very time consuming to refresh the virtual table.

Code:

if inuse("vtTable") = .T. SELECT vtTable else select 0 endif use vtTable where ABC = '&ABC' and SOMEID = 123

Here is something similar to what I was thinking of:

Code:

if vtTable.where = ("ABC = '&ABC' and SOMEID = 123") use vtTable else use vtTable where ABC = '&ABC' and SOMEID = 123 endif

Is there a way I can determine what the where clause contains so that if it is the same as what I need I don't need to refresh the table?
A:
If you create a cursoradaptor reference to the table, you can query the SelectCmd property:

Code:

// select the workarea first or specify the alias in quotes in the () ca=cursoradaptor() ? ca.SelectCmd

You can also use

Code:

ca.requery("ABC = '&ABC' and SOMEID = 456")

to change the condition.

Q:
I have created a table and set the various DD attributes for the fields
When I create the VT from the physical table, is there any way to copy the attributes, field caption, validation attributes, etc to the VT fields?
A:
In the console, you can copy a dictionary to another using the COPY FILE command.
When a VT is opened and a dictionary with the same table name exists it will be attached to the cursor for that table.

Q:
when I change my WHERE clause, that is done through the Requery parameter.
Does that mean that when I reset the WHERE clause, I am unable to prepare the SQL statement?
A:
This appears to be desktop related only.
Web / Mobile uses OData calls for native and virtual tables so manipulating virtual tables using requery() Is not relevant with these unless you are working with custom actions.

Virtual tables provide the equivalent of sqlconnect() etc and abstract above the data source. Thats the whole point of them so you can use them in web/mobile too against third party (non embedded Lianja database).

There are more to VTs that manually using sqlconnect() and sqlexec(). For example they handle optimistic locking with before and after buffers and recognize update conflicts.

The sqlxxx() functions are desktop oriented.

If you cannot just access your data with a Virtual Table which has a SQL SELECT statement you can access the data using a stored procedure and specify that as a CALL/EXEC in the Virtual Table definition.

You don't need to use JSON unless you are writing your whole App manually as the web/mobile client will perform the query by sending it to the Lianja Cloud Server as an OData query and the data will be returned in a format that the Lianja Web Client understands and be loaded into the associated section of the page e.g. a form, canvas or grid section.

All this talk of manually making calls to the server and sending back JSON is not nescessary IMO unless you want to waste time replacing existing functionality that is built in.

Q:
physical tables can be used in more than 1 virtual table?
A:
Yes. A VT is like a Connection and a remote view but it also handles transparent CRUD (Create Read Update and Delete) operations irrespective of the SQL dialect used by the remote data source.

Q:
it is possible to have virtual tables that contain columns from more than one table?
A:
Yes. Just remember to exclude the columns from updates and inserts. This ability is built-in as is the basetable property so the database engine can insert and update data in the remote data source even though you may have fetched data that is a complex join that contains columns from multiple tables.
2016-03-14, 04:50
josipradnik

Q:
Is the FetchAsNeeded property of a virtual table, comparable to VFP's NoDataOnload? Meaning that virtual tables are only populated after a Requery?
A:
No, FetchAsNeeded is being implemented so that the records in the local cursor that is created from the SQL SELECT will be fetched from the remote data source when referenced rather than when the virtual table is opened. This is to provide better performance and to be able to see remote data in real time without it having to be requeried. The best way is for you to create the Virtual Tables WHERE 1=0 then use requery( condition ) on the CursorAdapter() for the Virtual Table. This will populate the local cursor only with the data that matches the condition of the requery() and performance will be optimized.

Note that In Lianja the ODBC connections are pooled (and reference counted) so one ODBC connection will handle multiple Virtual Tables to optimize performance.

Q:
Sometimes however users demand a different backend e.g. Pervasive, not to mention a certain SQL server.
we named local views with a "lv_" prefix, while remote views were given an "rv_" prefix. All views were referenced dropping the first letter of the prefix and a global setting instructed the data layer to either use the local or global views.
How does one solve this predicament in Lianja?
Can CONNSTR properties be modified on the fly?
A:
Thats the whole point of Virtual Tables, they are data source independent. They are all contained within a database.

You can also have a different database containing Virtual table definitions for each database type that you want to support.

You can issue:

Code:

USE vt_name CONNSTR "..."

or alter the VT definition.

Code:

ALTER VIRTUALTABLE vt_name CONNSTR "..."

Also, COPY DATABASE will copy over one set of VT definitions to another database.

Yes, ALIAS is a keyword clause when you create a VT and thats what it will be seen as internally after it is opened. You cannot obviously have two VTs open with the same ALIAS.

(?A):
Reasons to use virtual tables:

1) the ability to switch backends (to mssql, mysql, oracle, Amazon's Aurora, etc.)

2) the ability to scale the database (using a common, clustered backend, with multiple LSQL instances). (Note: this requires high-speed connections, database tuning, etc.)

3) the ability to select a sub-set of records for the user to work with, based on their search criteria.

A VT (Virtual Table) definition can contain properties to configure it. This is all explained in the documentation wiki.

http://www.lianja.com/doc/index.php/...roubleshooting

For example if you have a VT with an auto increment field called id and another called occurs specify an updatefieldlist to exclude them. (Notice how * is used in the list to include all columns and then following that exclude individual ones by prefixing them with a '-').

Code:

properties "updatefieldlist=*,-id,-occurs"

All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352
2017-02-14, 05:54
josipradnik

Q:
I am using the Lianja SQL Server DSN Configuration utility to setup an ODBC connection to my app's database. The utility came pre-loaded with a connection to southwind and this works just fine when I click 'Test' but when I try the same thing to any of the databases I have created I get the error: "Database 'x' - The system cannot fine the file specified." I am using '?' for user and password because I don't know what else to put in there.
A:
Have you deployed the databases you want to access?

Q:
Im'm try to access FoxPro 2.6 data.

From console, if I write:

Code:

use E:\cogema\dati\articoli

the table are opened correctly, I can use this way?
I've also try to configure ODBC to access the data through ODBC, then I've created a VT.
The first time, I've opened the table...
then, close and open Lianja, the table is showed as "Encrypted"
Now crash every time I try to open the vt.
using the ODBC?
I can not import the DB... I've a ERP solution working on this data... I can not convert all the ERP.
FoxPro data stored on E:\cogema\dati
table name = Articoli

I've create ODBC named Cogema and whit other application, work fine.

I've created DB on Lianja named Cogema, then a VT: ODBC = Cogema, SQL= select * from articoli

Dir command:

Quote:

Tables in Database: cogema
Name Path Type
------------------- ------------------------------------------------------------ --------------------
+ vtarticoli C:\Lianja\data\cogema\vtarticoli.dbf VirtualTable
| connstr 'Cogema'
| alias 'vtArticoli'
| as select * from articoli

If I try to open the table, Lianja crash; if I open the database, is empty..

I need to read write on the original table.. For that I think to use odbc..
The ODBC driver is the 32 bit "Microsoft dBase Driver"..
A:
'encrypted' will be displayed if there is a problem with the VT and the data cannot be accessed.

Read/Write to FoxPro tables is not supported.
You can only import foxpro data. You can't update any indexes.
Open you database and issue a DIR in the console then paste a screenshot.

If you have that table open exclusively somewhere else ODBC will not be able to open it.

Try closing the database and deleting the VT files in the C:\Lianja\data\cogema\ folder, then rebuild the VT. If that does not help, can you post the articoli structure? Were autoninc integers available in FoxPro 2.6 ?

you can just drag and drop Foxpro files onto the database panel and they will automatically be converted. You don't need to use ODBC to do this.

This crash has now been fixed in the next 2.0.2 beta build. It was due to an incorrect VT (permission denied to folder or table does not exist) with connection pooling.

The "(encrypted)" message has now been changed to "(Access denied)" which is less confusing.

If you double click on a VT in the data workspace any error is displayed in a notification message at the top of the window.

the problem is the MEMO field... now I try to reinsert the general.

Q2:
I've found a new "Visual Fox Pro" ODBC and I can access to "Fox Pro" table, with Memo and General field.
if I can try to update a record... apparently nothing happens, and the record is not saved.
A2:
If nothing is being updated the WHERE condition is failing.

You can either cut and paste that into VFP and see if you can SELECT using it or preferably set a primarykey on the virtual table definition.

Perhaps the ODBC driver you are using is not handling proper ODBC dates.

Found!
in the property of the virtual table, just set the "Key field list"... and now work fine.
You have to setup your virtual table correctly with primarykey and/or updatefieldlist or keyfieldlist

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

Q:
For external data VT's are the only way to go but what about sql Views to the local database? Perhaps I'm under the wrong impression but I'd expect a View to be treated almost as tables that can be dragged on to forms etc. Anyway here are some observations I made when looking into this, listed here for discussion:

1. Virtual tables to your own database are tricky because they use odbc (and hence connect to the deployed db). So in the app builder in dev mode, forms are using your local data/database but anything referencing a VT is getting data from the deployed db - very difficult to work with.

2. There is also no way to see views in the app builder, obviously they can be queried in the console but to get a list of views available and see their definitions is very cumbersome - should this be a tab in the database editor?
A:
Search for "local" VTs.
Virtual Tables are displayed along with tables in the tables tab of the database. You can click on one to see it's definition. With the database open, you can issue DIR from the console (vfp window) to see tables and views, and the views will contain the SQL for each view.

Q:
Have app with single form section populated by a VT with a dsn to VFP ODBC. Works as expected in Builder, so I wanted to test on the Cloudserver with an outside browser.

I Deployed the app and the database and VT tables to the Cloudserver because I understand that the deploy button in the Web App View only updates the app.

When I run Preview, it takes some time for the data to appear (it does take some time for the VT to load as I am doing a Select * for about 15,000 records), and occasionally a "server disconnected" appears, but then the first record's data loads. Looks fine, I still have some work to do on formatting. Anyway, pressing the next icon (on the Action Bar?) does nothing. None of the buttons seem to be functioning.
A:
What you have discovered is that VFP does not support the SQL LIMIT clause which Lianja uses if it is available. It determines this heuristically when the VT is opened.

Lianja SQL, MySQL, and PostgreSQL all support the LIMIT clause.

MSSQL does not but we get round it using some other strange MSSQL syntax that we generate internally.

So, in other words, when selecting one record in a web form Lianja has to read the whole table and extract that one record.

This is a limitation of VFP SQL which does not exist in Lianja SQL.
If you can it would be better to convert the VFP database into a native Lianja database.

Q:
How to work with virtual table programmatically.
I have in section1 a field (field1) which is filled by the login process according to the user role. I have to select records in a grid (section2) populated by a virtual table created with "where 1=0" at design time.
here is the recommandation from yvonne, but the grid shows nothing even if i remove the "where" condition to filter all the data.

If the query condition is to be set on loading the App - as opposed to when another section is navigated, put it in the ready of the grid. I can only use :

Code:

//////////////////////////////////////////////////////////////// // Event delegate for 'ready' event proc page1_section2_ready() Lianja.get("page1.section2").requery([select * from <table> where userid='{Lianja.get("page1.section1.field1").text} '] ) endproc

If you were navigating a section, I would use the datachanged event delegate in the section being navigated to requery the grid section
A:
You have a ] at the end of the string.
The ready event only fires when the app is loaded. You should requery after a successfull authentication and before you switch pages.
The requery replaces the where condition. It is a requery of the existing select statement. It can contain everything after a where including an order by.
You can try all this interactively in the console.

Code:

oSection = Lianja.get('page1.section1'') oSection.requery('value > 100')

console workspace. You can test your requery() calls from there. Alternatively use the app insoector console and you should see the data changing in the section.
You create a virtual table where 1=0 then you can drag that virtual range to create a form or grid section.

Once you have that virtual table bound to a section you use the requery() method to change the selected records against the select clause that you have specified in the virtual table definition.
if the virtual table is bound to a child section you need to include the foreign key or no records will match and nothing will be displayed.

major performance improvement when fetching VT data into a local cursor, which with very large tables is multiples of that. You can specify the "fetchsize" in the VT properties (default 100). This removes any delays when initially opening a virtual table.

The "CursorAdaptor" class in Lianja v2.1 has an interesting new method in it which I use for QA and testing.

The "SqlCommandBuilder()" method returns the database dependent SQL generated for a given SQL statement. It is used internally (by OData) to generate a database independent SQL SELECT statement that handles "pagination" of the result set.

The SQL LIMIT clause is common place now but... MSSQL does not support it. MSSQL 2012 and greater support an OFFSET ... FETCH clause. Versions prior to 2012 we need to use the OVER clause. Unfortunately the OVER clause does not seem to function as advertised consistently so we use the OVER clause for all MSSQL versions.

So let's see what a SQL SELECT looks like to select a page of records from a resultset in MSSQL.

Code:

open database cloudlibtest // create the VT create virtualtable vt_cust; connstr 'lianja_mssqltest'; primarykey 'customerid'; as select sales.customer.customerid, sales.customer.territoryid, sales.salesterritory.name from sales.customer; inner join sales.salesterritory on sales.customer.territoryid = sales.salesterritory.territoryid // open the VT. use vt_cust // obtain a reference to the cursorAdaptor ca = cursoradaptor() // generate the SQL for paginating rows sqlstmt = ca.SqlCommandBuilder(; "MSSQL", // dbType MSSQL, MYSQL, LIANJA, POSTGRESQL, ORACLE, OTHER "SELECT", // commandType "sales.customer", // baseTable "customerid", // primaryKey "", // columnNames "", // columnValues "", // columnTypes "", // filter "11,10", // limit "", // other "select sales.customer.customerid, sales.customer.territoryid, sales.salesterritory.name from sales.customer; inner join sales.salesterritory on sales.customer.territoryid = sales.salesterritory.territoryid") // SQLSelectCommand ? sqlstmt

// The output produced is:

Code:

select * from (select row_number() over (order by customerid) as __rowid,sales.customer.customerid, sales.customer.territoryid, sales.salesterritory.name from sales.customer inner join sales.salesterritory on sales.customer.territoryid = sales.salesterritory.territoryid ) a where __rowid > 0 and __rowid <= 10

Tip: You can connect to your ODBC DSN in the console|ODBC Tab and paste the SQL statement in to see what results it retrieves

Q:
It would be handy to have some way of telling the sql generator engine to not add the paging stuff, where one knows that, e.g., only 1 record (if any) will be returned. I was referring (ambiguously) to the paging code. It would be useful to have a switch that said "don't insert the paging SQL changes as they aren't needed here."
A:
various new VT PROPERTIES that can be specified including "pagination=0".

Q:
I'm using the app builder in a windows environment and the data is stored on a Linux machine and accessed via ODBC.

1. How do I verify what indexes are created on a table?
- Using the ODBC console tab doesn't seem to help since it appears to just be creating a temporary table and LIST STAT doesn't show indexes (a .DBX file may or may not exist).

2. How do I create an index for a table?
- I don't know if INDEX on fieldname tag tagname is working (see point number 1).

3. How do I set and use an index when virtual tables are used?
A:
If you are doing it all remotely from the ODBC Console in the App Builder, with the DSN open, you can:

1. Use the SQL CREATE INDEX command to create indexes.

2. Query the SYSINDEXINFO system table to determine which indexes exist, their keys etc. Just use a WHERE clause (the structure is listed on that wiki page) to restrict to the table and/or key you are interested in.
I notice that at least some commands (e.g. list tables) work directory from the odbc console. Based on the time delay, it appears that the command is wrapped in a sqlexec call, and the return is marshalled back to the console screen.

I'm thinking this will be a neat way to make experimental (in a test setting) or crises (n an emergency) data schema changes on an ad hoc basis.

Q:
Preparing SQL Queries is a good way to defeat injection.
But when I change my WHERE clause, that is done through the Requery parameter.

Does that mean that when I reset the WHERE clause, I am unable to prepare the SQL statement?
A:
This appears to be desktop related only.

Web / Mobile uses OData calls for native and virtual tables so manipulating virtual tables using requery() Is not relevant with these unless you are working with custom actions.

If you need to work with multiple queries on the backend, relating them, applying business rules, etc. you would create a function on the backend and call the function from the client with the needed parameters. When you've got the cursor you want, you would then move it to JSON using the SELECT....INTO JSON against the dbf() of the cursor (unless it's a database VT, in which you can select directly against it -- I think) and return that to the JS client.

Reasons to use virtual tables:

1) the ability to switch backends (to mssql, mysql, oracle, Amazon's Aurora, etc.)

2) the ability to scale the database (using a common, clustered backend, with multiple LSQL instances). (Note: this requires high-speed connections, database tuning, etc.)

3) the ability to select a sub-set of records for the user to work with, based on their search criteria.

...am in the process of removing loads of indices or is it indexes, from my tables. Many existed to optimize view performance and for reporting purposes.

If you are going to do reporting the old-fashioned way (SET RELATION), you will need the same indexes to make this happen.

Can you write a web app that works against tables? Yes: been there, done that.. However, as soon as you run into a table with lots of records, you will want to select from among them for displaying to the user: you're right back to virtual tables.

Will it allow you the flexibility you need to scale, or meet customers' needs for a particular backend database? No.

Will it complicate reporting? Yes.

views are a throwback: they were in the product before VT's were created. Lianja rapidly evolves in response to real-world developer needs. That was a big part of my attraction to Lianja, and still is. If views were removed from the product, only the confusion they create in new users learning process would be lost.

Q:
I created 2 vts with "where 1=0" at design time so that the VT are empty in teh DB until the program is running.
So in the section Ready event, I use the lianja("page.section").sql="sql statement without where clause".
But when I run the form, the grid is not filled. When i switched back to design envireonment and re-type the sql statement; the grid is filled with the data.
If I create teh VT without "where 1=0" option, everything is fine, but I would like to keep the VT empty and populate it only at runtime when needed. I dont know if it can be the DB issue, because when I tested the same form using the southwind db I got the same issue.
I'm using single section grid at this time not a parent-child structure.
A:

lianja("page.section").sql="sql.,.."
try

Code:

Lianja.get("page.section").where = "1=1"

If in JavaScript, put a ; at the end of the line.

Works for me (tested in JavaScript -- I did have to put 1=0 in the WHERE of the section, as it appears the empty expression was over-writing the WHERE of the VT, which I had set to 1=0).

Note that the WHERE section attribute is not observed in browser/mobile. I seem to remember this as a known discrepancy, Perhaps someone with a better memory than I can chime in on this.

This is how I create my virtual tables against SQL Server.

create virtualtable <vtname> connstr 'driver={SQL server};server=<servername>;Trusted_Connection=Yes ;database=<dbname>' as select * from <tableName>

Recall that VirtualTables are select statements. You can verify this by tracing (or profiling) your backend.

Your changing of the size of the field will be reflected only in your local cursor.

I decided to trace this to prove it out.

I created a table on my backend with a char(10) field called name and an int field called num.
I created the virtual table as a select * from vtest.

I then locally modified the column to be 15 characters in the App Builder.

I browsed the data and manually modified the column with 'abcdefghijklmn'

The update statement that Lianja sent, ignores (rightly so) the modified field length and uses the length that the base table has.

Code:

update vtest set NAME='abcdefghij' where isnull(NAME,'')='herb' and isnull(NUM,0)=1

I too did need to change the size of a field earlier, so I created a view on my backend with the correct size.

I could also have created the definition at the time I created the virtualtable

create virtualtable.... select left(name,2),num from vtest

Different ways to skin a cat.

create the definition you want in the select statement or in your backend.

Create virtualtable... as select field1 as 'NewFieldName', field2 as 'anothername'

Without a primary key it is not possible to handle any updates but also it is not possible to handle any pagination which will not scale and would be unusable in a web / mobile app scenario. The primarykey should be specified in the VT properties.

This is a core problem if stored procedure calls are used as the VT SQL statement.

When no SQL SELECT is specified Lianja makes use of the basetable property for updates.

It does however need a primarykey as it generates the backend SQL SELECT statement dynamically when fetching a row or a page of rows to display in the Web Client. It is impractical to read millions of rows into a web browser or a mobile device. Lianja therefore reads on demand and the client fetches x rows starting at a specific offset.

This is all done independent of the backend database server. So it handles MSSQL, MySQL, PostgreSQL, LianjaSQL and other SQL syntax.

I create my VT's from straight tables, updateable views or sometimes I create the VT as the result of a stored procedure.

Q:
In my DB I've a VT, based on a ODBC connection to Fox table: VT_Item (itemid, ...., catmercgam)
then I've a local table Item (itemid,......,foto)

I've create a new VT: VT_Item_Item

Quote:

ODBC connection = local
Key field = ItemID
Properties = updatefieldlist=*,-catmercgam;quotestring='';basetable=item;translate name=false
SQL command: = select item.*, vt_item.catmercgam from item, vt_item where item.itemid = vt_item.itemid

In the section, I need to update "Item" table..

But if I do:

Code:

select vt_item_item replace foto with "aaaa"

I get error:

Quote:

mar mar 15 16:39:23 2016
**** Lianja error ****
replace foto with "aaaa"

To work, I need to do:

Code:

select item seek vt_item_item.itemid replace foto with "aaaa" ca = cursorAdaptor("vt_item_item") ca.requery("item.itemid = vt_item.itemid")

but but it seems a road a bit twisted ..
A:
I haven't created VT with VFP tables, but have with SQL Server.
How about a regular update statement? does that work for you?

something like

update vt_item_item set foto = "aaaa" where itemid = <value>

My thought being this is probable what the ODBC is looking for.

All topics in [Answers] alphabetically:http://www.lianja.com/community/showthread.php?2717-Answers
2018-01-30, 02:41
josipradnik

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.

https://www.lianja.com/community/att...1&d=1484015125
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.

https://www.lianja.com/community/att...5&d=1484187270

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

Quote:

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

with the southwind database closed...

Quote:

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

Quote:

select * from vt_orders2 --> expected 822 records.
select * from southwind!vt_orders2 --> expected 822 records.

with the southwind database closed...

Quote:

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.

https://www.lianja.com/community/att...0&d=1491638869
https://www.lianja.com/community/att...1&d=1491639006

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.

https://www.lianja.com/community/att...3&d=1491640859

Q2:
It also works when specifying a dynamic connectstring. Like so

Code:

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.

Code:

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.

All topics in [Answers] alphabetically: https://www.lianja.com/community/sho...ll=1#post13748

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...irtual-tables/