Difference between revisions of "CREATE VIRTUALTABLE"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
Line 43: | Line 43: | ||
<code lang="recital"> | <code lang="recital"> | ||
open database advworks | open database advworks | ||
− | create virtualtable scurr | + | create virtualtable scurr connstr "awhr" as select * from sales.currency |
</code> | </code> | ||
Revision as of 06:37, 4 April 2013
Contents
Purpose
Creates a virtual table
Syntax
CREATE VIRTUALTABLE [<database>!]<virtualtable>
[IF NOT EXISTS]
CONNSTR <odbc_dsn_string>
[PRIMARYKEY <column>]
[PROPERTIES <properties_string>]
AS <select statement>
See Also
ADD TABLE, ALTER INDEX, ALTER TABLE, ALTER VIRTUALTABLE, COPY DATABASE, CONSTRAINTS, CREATE DATABASE, CREATE TABLE, CursorAdapter, CURSORADAPTER(), DATA TYPES, DROP DATABASE, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, SELECT, SET XMLFORMAT, 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 is only created if it does not already exist. An error occurs if the virtual table already exists and the IF NOT EXISTS clause is not specified. |
CONNSTR <odbc_dsn_string> | The ODBC connection string. |
PRIMARYKEY <column> | The PRIMARYKEY <column>, if specified, will be used to update data e.g. WHERE <column> = value |
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 connstr "awhr" as select * from sales.currency