Virtual Tables - Lianja Cloud Server
- Properties and Methods of the CursorAdapter Class
Primary Key Required
Virtual tables used with the Lianja Cloud Server must have a unique primary key specified in the virtual table definition.
See Virtual Tables for an introduction to virtual table creation. To add a primary key to an existing virtual table, open the database in the Data Workspace, select the virtual table name then right-click Modify virtual table ... to open the dialog and add the key.
// 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"
- Note: keyfieldlist and primarykey are synonymous.
- For virtual table bound Grid Sections, the Primary Key must be present in the Grid columns to allow update and delete operations.
- For virtual tables based on a join, the Primary Key must be the name of the key field in the joined resultset and the 'basetable' property is required.
Lianja Web Virtual Tables Demo
The Lianja App Builder distribution includes the sample App 'Lianja Web Virtual Tables Demo' (example_webvirtualtables).
This App uses virtual tables to access a MySQL table from the sakila database and a MS SQL Server table from the AdventureWorks database. To run the App, you will need ODBC access to a MySQL Server with the sakila sample database and to the AdventureWorks sample database on MS SQL Server.
The Lianja database for the App is called cloudlibtest and it has 2 virtual tables.
The first is vt_actors, a virtual table for the actor table in the MySQL sakila database.
To use this virtual table, you first need to create a 32-bit ODBC System DSN called lianja_mysqltest to connect to the MySQL sakila database (Configuring a Connector/ODBC DSN on Windows).
Note: If you want to use a DSN with a different name, you need to change the ODBC Connection value for the vt_actors virtual table.
The second is vt_customers, a virtual table for the Sales.Customer table in the MS SQL Server AdventureWorks database.
To use this virtual table, you first need to create a 32-bit ODBC System DSN called lianja_mssqltest to connect to the MS SQL Server AdventureWorks database (help information is displayed in the ODBC Data Source Administrator or can be found here).
Note: If you want to use a DSN with a different name, you need to change the ODBC Connection value for the vt_customers virtual table.
If you change any values in the cloudlibtest virtual tables, make sure you Deploy the database before running the example_webvirtualtables App in the Lianja Web Client in your browser.
And don't forget to open example_webvirtualtables in the Web App View and Deploy the App.
Having problems? See Troubleshooting example webvirtualtables.
More on Virtual Table Properties
Coming soon ...