Difference between revisions of "Virtual Tables - Lianja Cloud Server"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
Line 70: | Line 70: | ||
The first is '''vt_actors''', a virtual table for the actor table in the MySQL sakila database. | 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 DSN called '''lianja_mysqltest''' to connect to the MySQL sakila database ([http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-windows-5-2.html Configuring a Connector/ODBC DSN on Windows]). | + | 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 ([http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-windows-5-2.html 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. | 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. | ||
Line 82: | Line 82: | ||
The second is '''vt_customers''', a virtual table for the Sales.Customer table in the MS SQL Server AdventureWorks database. | 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 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 [http://msdn.microsoft.com/en-us/library/windows/desktop/dn170503(v=vs.85).aspx here]). | + | 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 [http://msdn.microsoft.com/en-us/library/windows/desktop/dn170503(v=vs.85).aspx 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. | 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. |
Revision as of 05:21, 6 July 2015
Contents
See Also
- Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands
- Properties and Methods of the CursorAdapter Class
Primary Key Required
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.
The primary key can also be specified in the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE commands.
Note: keyfieldlist and primarykey are synonymous.
// 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"
Lianja Web Virtual Tables Demo
The Lianja App Builder distribution includes the sample App 'Lianja Web Virtual Tables Demo' (example_webvirtualtables).
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.
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.
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.
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.
And don't forget to open example_webvirtualtables in the Web App View and Deploy the App.
More on Virtual Table Properties
Coming soon ...