Difference between revisions of "Virtual Tables - Local Development, Remote Deployment"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
(3 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
+ | ''To be updated to include latest functionality and screenshots'' | ||
+ | |||
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. | 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. | ||
Line 15: | Line 17: | ||
* [[Virtual Tables - Local Stored Procedures]] | * [[Virtual Tables - Local Stored Procedures]] | ||
+ | |||
+ | * [[SET CONNSTR]] Command (default 'ODBC Connection') | ||
+ | |||
+ | * [[Using_Parameters_Views|Using Parameterized Views]] | ||
==Introduction== | ==Introduction== | ||
Line 66: | Line 72: | ||
− | + | 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. | '''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. |
Latest revision as of 08:14, 14 September 2023
To be updated to include latest functionality and screenshots
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.
Contents
See Also
- Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands
- Properties and Methods of the CursorAdapter Class
- SET CONNSTR Command (default 'ODBC Connection')
Introduction
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
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.
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
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
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.
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.
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 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
Once deployed, the App's virtual tables will use the Runtime connection.