Difference between revisions of "Virtual Tables - Lianja Cloud Server"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
Line 61: | Line 61: | ||
− | This App uses virtual tables to access a MySQL table from the | + | 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 [http://dev.mysql.com/doc/sakila/en/ sakila] sample database and to the [http://sqlserversamples.codeplex.com/ AdventureWorks] sample database on MS SQL Server. |
<br clear=all> | <br clear=all> | ||
Line 75: | Line 75: | ||
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. | ||
+ | |||
+ | <br clear=all> | ||
+ | |||
+ | [[{{ns:file}}:vt4_mssqldsn.png|376px|thumb|left|link={{filepath:vt4_mssqldsn.png}}|Virtual Table vt_customers]] | ||
+ | |||
+ | |||
+ | |||
+ | 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]). | ||
+ | |||
+ | 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. | ||
<br clear=all> | <br clear=all> |
Revision as of 10:46, 28 January 2015
Under Construction
See Also
- Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands
- Properties and Methods of the CursorAdapter Class
Primary Key
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 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 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.