Virtual Tables - Lianja Cloud Server

See Also

• Introduction to Virtual Tables

• Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands

• Virtual Table Properties

• Virtual Tables - Further Configuration and Troubleshooting

• Virtual Tables - Local Development, Remote Deployment

• Properties and Methods of the CursorAdapter Class

Primary Key Required

Modify Virtual Table: Primary Key

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.

Primary Key

The primary key can also be specified in the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE commands.

// 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"

Notes:

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

Lianja Web Virtual Tables Demo App in browser

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.

Virtual Table vt_actors

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.

Virtual Table vt_customers

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.

Deploy cloudlibtest database

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.

Deploy cloudlibtest example_webvirtualtables App

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