How To Create Virtual Table In Sql

This is an introduction to the use of Virtual Tables. It explains what virtual tables are, their advantages and how to get started using them. Further documentation will give more detailed information on modifying virtual tables, defining their keys, update properties, use of the underlying CursorAdapter object, 'local' virtual tables and more.

What are Virtual Tables?

Virtual tables allow you to access external data via an ODBC connection, yet they look like regular tables in a Lianja database. You can drag 'n' drop virtual tables in the Page Builder and reference them in the same way as standard tables and all the time the underlying connection, SQL statements and cursor adapter are being handled for you.

What's so special about Virtual Tables?

Pro Tip

All virtual table connections using the same connection string or DSN are pooled to maximize performance.

Virtual tables are an all-in-one combination of a connection, SQL commands and a CursorAdapter object, so the connection and the logic to perform a query is self-contained inside the virtual table definition. This gives a clear separation between the UI presentation layer and the underlying data source.
Virtual tables are fully updatable with transparent inserts, updates and deletes and they are both SQL and NoSQL compatible.
All virtual table connections using the same connection string or DSN are pooled to maximize performance.
Virtual tables allow you to join disparate data sources. For example, you can use virtual tables accessing Microsoft SQL Server alongside native Lianja tables, alongside other virtual tables accessing MySQL and build relationships between the data.

Creating a Virtual Table

It's always a good idea to test out any ODBC connection you want to use in Lianja in the ODBC Console, so that you can see that the connection is being made and that you can access the tables you expect.

Using the ODBC Console

I've set up a system DSN called awhr in the Windows 32 bit ODBC Administrator (on 64 bit Windows, this is %windir% \SysWOW64\odbcad32.exe) to connect to the Microsoft SQL Server AdventureWorks sample database on my local machine.

In the Lianja App Builder Console Workspace, I enter awhr as the Connstring and once the connection is reported as successful, I list the tables then open the Sales.Currency table to check its structure and data.

So, I'm happy that I can connect to the awhr DSN - a DSNless connection string can also be specified in the Connstring field - and that Sales.Currency is the table that I am interested in. Clicking the x disconnects the ODBC connection.
I've now switched to the Data Workspace and I open a database - this one is empty and called vtables.

The Tables tab is now automatically displayed and I can select New virtual table... from the Additional Commands.

New Virtual Table

I'm then prompted to give my virtual table a name. Table names should be unique within the database, start with a letter and be followed by a combination of letters, digits and underscores, without spaces and up to a total of 32 characters.

Virtual Table Name

The Create a Virtual Table dialog is now displayed. I'm keeping it simple at the moment, specifying my DSN - awhr, as before - and the SQL SELECT command as select * from sales.currency, the table I looked at earlier.

If your data source requires a username and password and these are not or cannot be stored in the DSN, specify the account information in the ODBC Connection along with the DSN, e.g. awhr;uid=yvonne;pwd=mypass.

Virtual Table Definition

Once created, my virtual table is displayed in the Tables list in the SideBar like any normal Lianja table and I can double-click on the table name to open the table and load its data into the Data Editor.

View Virtual Table data in Data Editor

I can change the virtual table definition using Modify virtual table ... from Additional commands.

Modify Virtual Table

I can modify the SQL SELECT command to specify just the columns I want to use.

Note that from Lianja v5.3 the SQL command along with the ODBC Connection, Key field list and Properties all have a [...] button that opens an editor in a dialog panel.

Modify Virtual Table Definition

Then close and reopen the virtual table to load my selected columns into the Data Editor.

View modified Virtual Table data

Using Virtual Tables

App with Virtual Table

I've closed the database in the Data Workspace and created a new App called myvtapp.

This takes me into the Pages Workspace, where I can open the vtables database and drag my vt_currency virtual table onto a Page to create a bound Form Section, just like I would with a standard Lianja table.