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:
and set the basetable and translatename=false for other tables with an underscore in the real table name.Code:basetable=srv_tabledef;translatename=false
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:
And that's what I get:Code://////////////////////////////////////////////////////////////// // Event delegate for 'ready' event proc page1_section1_ready() Lianja.get("page1.section1").sql = "select * from Sales.Customer where territoryid = 8" endproc
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.
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.
All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352
Reply With Quote
Bookmarks