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.
You can join data from disparate SQL databases using Local Stored Procedures
- Watch the introduction to the Data Workspace video:
- Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands
- Properties and Methods of the CursorAdapter Class
- SET CONNSTR Command (default 'ODBC Connection')
What are Virtual Tables?
Virtual tables allow you to access external data via an ODBC connection (or an OLEDB connection on Windows), 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?
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.
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 opened 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.
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.
The Create a Virtual Table dialog is now displayed. I'm keeping it simple at the moment, specifying my DSN - awhr as before, the PrimaryKey - the unique currencycode, the SQL SELECT command as select * from sales.currency and the Basetable as sales.currency, the table I looked at earlier.
Note: Basetable is included in the dialog from Lianja v7.2. In earlier versions, it can be specified in the Properties as, e.g. basetable=sales.currency.
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.
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.
I can change the virtual table definition using Modify virtual table ... from Additional commands.
I can modify the SQL SELECT command to specify just the columns I want to use.
Click the [...] button to open an editor in a dialog panel.
Then close and reopen the virtual table to load my selected columns into the Data Editor.
Using Virtual Tables
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.
Here's another way I can use my vt_currency virtual table.
I've added a new standard Lianja table called transaction to the vtables database and created a new App with a Form Section bound to the new table.
I've then specified Autosuggestions based on the columns from the vt_currency virtual table.
As I type in the transaction Currency Code formitem, the Autosuggestions choicelist based on the vt_currency virtual table is displayed.