Difference between revisions of "CREATE VIRTUALTABLE"
From Lianjapedia
(Created page with '==Purpose== Creates a virtual table ==Syntax== CREATE VIRTUALTABLE [<database>!]<virtualtable> [IF NOT EXISTS] [ALIAS <name>] CONNSTR <odbc_dsn_string> AS <select …') |
Yvonne.milne (Talk | contribs) (→Description) |
||
| (39 intermediate revisions by 5 users not shown) | |||
| Line 7: | Line 7: | ||
[IF NOT EXISTS] | [IF NOT EXISTS] | ||
| − | [ | + | [CONNSTR <odbc_dsn_string>] |
| − | + | [ALIAS <aliasname>] | |
| + | |||
| + | [TABLE <basetable>] | ||
| + | |||
| + | [KEYFIELDLIST <column-list>] | ||
| + | |||
| + | [PROPERTIES <properties_string>] | ||
AS <select statement> | AS <select statement> | ||
==See Also== | ==See Also== | ||
| − | [[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[ALTER VIRTUALTABLE]], [[COPY DATABASE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE DATABASE]], [[CREATE TABLE]], [[SQL Data Types|DATA TYPES]], [[DROP DATABASE]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN DATABASE]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]], [[USE]] | + | [[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[ALTER VIRTUALTABLE]], [[COPY DATABASE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE DATABASE]], [[CREATE TABLE]], [[CREATE VIEW]], [[CursorAdapter]], [[CURSORADAPTER()]], [[SQL Data Types|DATA TYPES]], [[DROP DATABASE]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN DATABASE]], [[SQL SELECT|SELECT]], [[SET XMLFORMAT]], [[TABLEINFO()]], [[USE]], [[Virtual Table Properties]], [[Virtual Tables]], [[VTINFO()]] |
==Description== | ==Description== | ||
| − | The CREATE VIRTUALTABLE command creates a new virtual table in the current database. Virtual tables exist in the database and are handled by | + | 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. |
| − | {| class="wikitable" | + | {| class="wikitable" width="100%" |
| − | !Keywords||Description | + | !width="30%"|Keywords||width="70%"|Description |
|- | |- | ||
| − | | | + | |valign="top"|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. | + | |<virtualtable>||The name of the virtual table to be created. |
|- | |- | ||
| − | | | + | |valign="top"|IF NOT EXISTS||The virtual table is only created if it does not already exist. An error occurs if a virtual table or table of the same name already exists and the IF NOT EXISTS clause is not specified. |
|- | |- | ||
| − | | | + | |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. | + | |valign=top|CONNSTR <odbc_dsn_string>||The ODBC connection string. Specify 'local' or 'localhost' for a local Lianja database. At runtime, 'local' will be replaced by the connection string in the 'Runtime connection' App Setting; 'localhost' will not be replaced and will continue to use the local Lianja database. Note: if you are specifying a DSN that does not or cannot store the username and password details, the account information should be included here, e.g. 'mydsn;uid=myusername;pwd=mypassword'.<br>If the CONNSTR is omitted, the current [[SET CONNSTR]] value will be applied (from v4.1, previously was required). |
| + | |- | ||
| + | |valign=top|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. For Lianja Cloud Server virtual table use, the virtual table must have a KEYFIELDLIST specified to provide a unique key for access and updates. | ||
| + | |- | ||
| + | |valign="top"|PROPERTIES <properties_string>||The <properties_string> is a ; separated list of name=value pairs. See [[Virtual Table Properties]]. | ||
| + | |- | ||
| + | |valign="top"|TABLE <basetable>||The <basetable> is the name of the remote table. This is auto-detected from the AS <select statement>. If Lianja is unable to auto-detect the primary table from a complex SQL join statement (basetable error reported), the TABLE <basetable> clause should be included in the CREATE VIRTUALTABLE command or the ALTER VIRTUALTABLE <virtualtable> TABLE <basetable> command run to modify an existing virtual table. | ||
|- | |- | ||
|AS <select statement>||The SQL SELECT statement. | |AS <select statement>||The SQL SELECT statement. | ||
| Line 39: | Line 51: | ||
<code lang="recital"> | <code lang="recital"> | ||
open database advworks | open database advworks | ||
| − | create virtualtable scurr connstr "awhr" as select * from sales.currency | + | create virtualtable scurr ; |
| + | alias "scurr" ; | ||
| + | connstr "awhr" ; | ||
| + | as select * from sales.currency | ||
| + | |||
| + | // Example including account details | ||
| + | open database orahr | ||
| + | create virtualtable vt_locactions ; | ||
| + | alias "vt_locactions" ; | ||
| + | connstr "oracle_1;uid=hr;pwd=hr" ; | ||
| + | as select * from locations | ||
| + | |||
| + | // Example including KEYFIELDLIST and PROPERTIES (updatefieldlist) | ||
| + | open database sak | ||
| + | create virtualtable vt_actor ; | ||
| + | alias "actor" ; | ||
| + | connstr "sakila" ; | ||
| + | keyfieldlist "actor_id" ; | ||
| + | properties "updatefieldlist=*,-actor_id" ; | ||
| + | as select * from actor | ||
</code> | </code> | ||
| Line 46: | Line 77: | ||
[[Category:Commands]] | [[Category:Commands]] | ||
[[Category:Databases]] | [[Category:Databases]] | ||
| − | [[Category: | + | [[Category:Lianja VFP Extensions]] |
| + | [[Category:VFP Command Extensions]] | ||
| + | [[Category:Third Party Database Connectivity]] | ||
| + | [[Category:Virtual Tables]] | ||
| + | [[Category:Lianja SQL]] | ||
Latest revision as of 05:28, 29 June 2018
Contents
Purpose
Creates a virtual table
Syntax
CREATE VIRTUALTABLE [<database>!]<virtualtable>
[IF NOT EXISTS]
[CONNSTR <odbc_dsn_string>]
[ALIAS <aliasname>]
[TABLE <basetable>]
[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, Virtual Table Properties, Virtual Tables, VTINFO()
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 a virtual table or table of the same name already exists and the IF NOT EXISTS clause is not specified. |
| 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. Specify 'local' or 'localhost' for a local Lianja database. At runtime, 'local' will be replaced by the connection string in the 'Runtime connection' App Setting; 'localhost' will not be replaced and will continue to use the local Lianja database. Note: if you are specifying a DSN that does not or cannot store the username and password details, the account information should be included here, e.g. 'mydsn;uid=myusername;pwd=mypassword'. If the CONNSTR is omitted, the current SET CONNSTR value will be applied (from v4.1, previously was required). |
| 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. For Lianja Cloud Server virtual table use, the virtual table must have a KEYFIELDLIST specified to provide a unique key for access and updates. |
| PROPERTIES <properties_string> | The <properties_string> is a ; separated list of name=value pairs. See Virtual Table Properties. |
| TABLE <basetable> | The <basetable> is the name of the remote table. This is auto-detected from the AS <select statement>. If Lianja is unable to auto-detect the primary table from a complex SQL join statement (basetable error reported), the TABLE <basetable> clause should be included in the CREATE VIRTUALTABLE command or the ALTER VIRTUALTABLE <virtualtable> TABLE <basetable> command run to modify an existing virtual table. |
| AS <select statement> | The SQL SELECT statement. |
Example
open database advworks create virtualtable scurr ; alias "scurr" ; connstr "awhr" ; as select * from sales.currency // Example including account details open database orahr create virtualtable vt_locactions ; alias "vt_locactions" ; connstr "oracle_1;uid=hr;pwd=hr" ; as select * from locations // Example including KEYFIELDLIST and PROPERTIES (updatefieldlist) open database sak create virtualtable vt_actor ; alias "actor" ; connstr "sakila" ; keyfieldlist "actor_id" ; properties "updatefieldlist=*,-actor_id" ; as select * from actor
