CREATE VIRTUALTABLE

Purpose

Creates a virtual table

Syntax

CREATE VIRTUALTABLE [<database>!]<virtualtable>

[IF NOT EXISTS]

CONNSTR <odbc_dsn_string>

[ALIAS <aliasname>]

[KEYFIELDLIST <column-list>]

[PROPERTIES <properties_string>]

AS <select statement>

See Also

ADD TABLE, ALTER INDEX, ALTER TABLE, ALTER VIRTUALTABLE, COPY DATABASE, CONSTRAINTS, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CursorAdapter, CURSORADAPTER(), DATA TYPES, DROP DATABASE, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, SELECT, SET XMLFORMAT, TABLEINFO(), USE

Description

The CREATE VIRTUALTABLE command creates a new virtual table in the current database. Virtual tables exist in the database and are handled by CursorAdapters allowing remote/external ODBC data based on SQL SELECT statements to be treated as regular Lianja tables. Any code that references these tables e.g. USE <virtualtable> connects via the CursorAdapter and retrieves the data. Updates and deletes are handled transparently via the CursorAdapter. All CursorAdapters automatically use ODBC connection pooling against the same ODBC DSN to maximize performance.

Keywords	Description
[database]	The name of the database in which the virtual table should be created. The '!' character must be included between the database name and the virtual table name.
<virtualtable>	The name of the virtual table to be created.
[IF NOT EXISTS]	The virtual table overwrites a table with the same name unless the IF NOT EXISTS clause is specified, in which case it will only create the virtual table if a table with the same name does not already exist.
ALIAS <aliasname>	The optional alias name for the virtual table. Defaults to the same as the virtual table basename.
CONNSTR <odbc_dsn_string>	The ODBC connection string or 'local' for the current local Lianja database.
KEYFIELDLIST <column-list>	The optional KEYFIELDLIST <column-list> specifies a comma separated list of columns e.g. "id,name" which are used to generate the WHERE clause for UPDATE and DELETE commands.
PROPERTIES <properties_string>	The <properties_string> is a ; separated list of name=value pairs corresponding to cursoradaptor properties. This therefore allows you to switch databases in the connection string.
AS <select statement>	The SQL SELECT statement.

Example

open database advworks
create virtualtable scurr alias "scurr" connstr "awhr" as select * from sales.currency