Difference between revisions of "ALTER VIRTUALTABLE"
From Lianjapedia
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) (→Description) |
||
| (16 intermediate revisions by 2 users not shown) | |||
| Line 5: | Line 5: | ||
ALTER VIRTUALTABLE [<database>!]<virtualtable> | ALTER VIRTUALTABLE [<database>!]<virtualtable> | ||
| − | CONNSTR <odbc_dsn_string> | + | [CONNSTR <odbc_dsn_string>] |
[ALIAS <aliasname>] | [ALIAS <aliasname>] | ||
| + | |||
| + | [TABLE <basetable>] | ||
[KEYFIELDLIST <column-list>] | [KEYFIELDLIST <column-list>] | ||
| Line 13: | Line 15: | ||
[PROPERTIES <properties_string>] | [PROPERTIES <properties_string>] | ||
| − | AS <select statement> | + | [AS <select statement>] |
| + | |||
| + | |||
| + | ALTER VIRTUALTABLE [<database>!]<virtualtable> ADDPROPERTY | MODIFYPROPERTY <properties_string> | ||
| + | |||
| + | |||
| + | ALTER VIRTUALTABLE [<database>!]<virtualtable> REMOVEPROPERTY <property> | ||
==See Also== | ==See Also== | ||
| − | [[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[ | + | [[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[SQL Constraints|CONSTRAINTS]], [[COPY DATABASE]], [[CREATE DATABASE]], [[CREATE TABLE]], [[CREATE VIRTUALTABLE]], [[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]] |
==Description== | ==Description== | ||
| − | The ALTER VIRTUALTABLE command modifies a 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. | + | The ALTER VIRTUALTABLE command modifies a 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. |
| + | |||
| + | ALTER VIRTUALTABLE can also be used as a synonym of CREATE VIRTUALTABLE, in which case the CONNSTR and AS clauses must be included. | ||
{| class="wikitable" width="100%" | {| class="wikitable" width="100%" | ||
| − | !width=" | + | !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. | |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. | ||
|- | |- | ||
|valign="top"|<virtualtable>||The name of the virtual table to be modified. | |valign="top"|<virtualtable>||The name of the virtual table to be modified. | ||
| + | |- | ||
| + | |} | ||
| + | |||
| + | A combination of one or more of the following can be specified: | ||
| + | |||
| + | {| class="wikitable" width="100%" | ||
| + | !width="30%"|Keywords||width="70%"|Description | ||
|- | |- | ||
|valign="top"|ALIAS <name>||The optional [[ALIAS()|alias]] name for the virtual table. | |valign="top"|ALIAS <name>||The optional [[ALIAS()|alias]] name for the virtual table. | ||
|- | |- | ||
| − | |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. | + | |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 virtual table was created without specifying a CONNSTR, 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. | + | |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 ALTER VIRTUALTABLE <virtualtable> TABLE <basetable> command should be run to specify this information for the virtual table. |
| + | |- | ||
| + | |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 corresponding to cursoradaptor properties. This therefore allows you to switch databases in the connection string. | |valign="top"|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. | ||
| Line 39: | Line 58: | ||
|- | |- | ||
|} | |} | ||
| + | |||
| + | Individual properties for the virtual table can be added using the ADDPROPERTY clause, modified using the MODIFYPROPERTY clause or removed using the REMOVEPROPERTY clause. See [[Virtual Table Properties]]. | ||
==Example== | ==Example== | ||
| Line 45: | Line 66: | ||
create virtualtable scurr connstr "awhr" as select * from sales.currency | create virtualtable scurr connstr "awhr" as select * from sales.currency | ||
alter virtualtable scurr connstr "awhr" as select name from sales.currency | alter virtualtable scurr connstr "awhr" as select name from sales.currency | ||
| + | |||
| + | // Modifying properties example | ||
| + | open datables sakdata | ||
| + | create virtualtable vt_actor connstr "sakila" keyfieldlist "actor_id" as select * from actor | ||
| + | alter virtualtable vt_actor addproperty "updatefieldlist=first_name" | ||
| + | alter virtualtable vt_actor modifyproperty "updatefieldlist=first_name, last_name" | ||
| + | alter virtualtable vt_actor removeproperty "updatefieldlist" | ||
| + | alter virtualtable vt_actor properties "updatefieldlist=last_name;keepalive=1" | ||
| + | alter virtualtable vt_actor properties "" | ||
</code> | </code> | ||
| Line 53: | Line 83: | ||
[[Category:Lianja VFP Extensions]] | [[Category:Lianja VFP Extensions]] | ||
[[Category:VFP Command Extensions]] | [[Category:VFP Command Extensions]] | ||
| + | [[Category:Third Party Database Connectivity]] | ||
| + | [[Category:Virtual Tables]] | ||
| + | [[Category:Lianja SQL]] | ||
Latest revision as of 10:58, 7 February 2018
Contents
Purpose
Modifies a virtual table
Syntax
ALTER VIRTUALTABLE [<database>!]<virtualtable>
[CONNSTR <odbc_dsn_string>]
[ALIAS <aliasname>]
[TABLE <basetable>]
[KEYFIELDLIST <column-list>]
[PROPERTIES <properties_string>]
[AS <select statement>]
ALTER VIRTUALTABLE [<database>!]<virtualtable> ADDPROPERTY | MODIFYPROPERTY <properties_string>
ALTER VIRTUALTABLE [<database>!]<virtualtable> REMOVEPROPERTY <property>
See Also
ADD TABLE, ALTER INDEX, ALTER TABLE, CONSTRAINTS, COPY DATABASE, CREATE DATABASE, CREATE TABLE, CREATE VIRTUALTABLE, CursorAdapter, CURSORADAPTER(), DATA TYPES, DROP DATABASE, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, SELECT, SET XMLFORMAT, TABLEINFO(), USE, Virtual Table Properties, Virtual Tables
Description
The ALTER VIRTUALTABLE command modifies a 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.
ALTER VIRTUALTABLE can also be used as a synonym of CREATE VIRTUALTABLE, in which case the CONNSTR and AS clauses must be included.
| 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 modified. |
A combination of one or more of the following can be specified:
| Keywords | Description |
|---|---|
| ALIAS <name> | The optional alias name for the virtual table. |
| 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 virtual table was created without specifying a CONNSTR, the current SET CONNSTR value will be applied (from v4.1, previously was required). |
| 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 ALTER VIRTUALTABLE <virtualtable> TABLE <basetable> command should be run to specify this information for the virtual table. |
| 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 corresponding to cursoradaptor properties. This therefore allows you to switch databases in the connection string. |
| AS <select statement> | The SQL SELECT statement. |
Individual properties for the virtual table can be added using the ADDPROPERTY clause, modified using the MODIFYPROPERTY clause or removed using the REMOVEPROPERTY clause. See Virtual Table Properties.
Example
open database advworks create virtualtable scurr connstr "awhr" as select * from sales.currency alter virtualtable scurr connstr "awhr" as select name from sales.currency // Modifying properties example open datables sakdata create virtualtable vt_actor connstr "sakila" keyfieldlist "actor_id" as select * from actor alter virtualtable vt_actor addproperty "updatefieldlist=first_name" alter virtualtable vt_actor modifyproperty "updatefieldlist=first_name, last_name" alter virtualtable vt_actor removeproperty "updatefieldlist" alter virtualtable vt_actor properties "updatefieldlist=last_name;keepalive=1" alter virtualtable vt_actor properties ""
