Virtual Tables - Local Development, Remote Deployment

From Lianjapedia
Revision as of 22:39, 9 February 2020 by Barrymavin (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Lianja allows you to develop using virtual tables connected to local data, then - by setting the Runtime connection attribute for the App - to deploy your App to use an alternative connection available on the client.

See Also


In the example here we do the following:

  • Use the Import ODBC database utility in Lianja to connect to an Oracle server via the Oracle ODBC Driver and import the 'hr' tables required by my App. The utility also automatically creates virtual tables for these local copies.
  • Develop the App using the virtual tables, without needing to be connected to the Oracle server.
  • Set the Runtime connection on the App to the target client's Oracle DSN and set the App's Published flag to true.
  • Deploy the App

Create 'local' virtual tables

Import ODBC database

Firstly, we need local Lianja copies of the target data (Oracle 'hr' sample here). We can use the Import ODBC database utility for this via the Oracle ODBC Driver (32 bit). In the Lianja App Builder, select the Data Workspace and create a new database or open the database you want to use. Then select Import ODBC database from Additional commands.

Create Virtual Tables

In the Import ODBC Database dialog:

  • Specify the Oracle ODBC DSN and its associated Username and Password.
  • Check the Create Virtual Tables box to automatically create 'local' virtual tables for the imported tables.
  • Click the Tables button to display the available tables.
  • Check the required tables.
  • Click Import

Imported Tables and Local Virtual Tables

The Console Workspace will be selected automatically and the import progress displayed. Once the import has completed, issuing the dir command in the Lianja/VFP command window displays the tables and virtual tables that have been created. Note that the virtual tables have a connstr of 'local'.

At this stage, the connection to the Oracle server is no longer required.

Create 'local' virtual tables for an existing database

Create local virtual tables for an existing database

From Lianja App Builder v1.3.1 the additional commands available in the database context menu (right-click the database name in the Data Workspace - the database must be open) include options to create local virtual tables for an existing database.

Create Local Virtual Tables ... creates the virtual tables with a select * from tablename; the Create Local Virtual Tables where 1=0 ... a select * from tablename where 1=0. The where 1=0 means no data is read when the virtual table is first opened, only when a requery is issued with a where clause that has matching records.

You will be prompted to confirm that the virtual tables should be created.

Create local virtual tables for an existing database

The virtual tables are named with a 'vt_' prefix followed by the name of the source table.

If a virtual table with that name already exists, it is not overwritten.

If a table has a field with the name tablename+id, e.g. customer.customerid, customers.customerid or customers.customersid, this field is set as the primarykey for the virtual table.

Create local virtual tables for an existing database

These options can also be used to create local virtual tables for databases containing native Lianja tables.

For example, here local virtual tables have been created for the tables in the sample southwind database.

Develop locally

Develop using Local Virtual Tables

Develop the App using the virtual tables.

Remember to check the box next to the Published attribute for the App in Settings so that it will appear in the Lianja App Center and also to complete the settings for the App's App Center tile.

Finally, set the Runtime connection attribute to be the DSN or connection string the target client will use, including the authentication information if required.


Create an Installer or other method of deploying the App. The screenshot shows the files to be included for a local deployment.

Deploy remotely

Deployed App uses Remote Data

Once deployed, the App's virtual tables will use the Runtime connection.