Difference between revisions of "ALTER VIRTUALTABLE"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
| Line 30: | Line 30: | ||
|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'. |
|- | |- | ||
|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|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. | ||
Revision as of 05:55, 26 February 2014
Contents
Purpose
Modifies a virtual table
Syntax
ALTER VIRTUALTABLE [<database>!]<virtualtable>
CONNSTR <odbc_dsn_string>
[ALIAS <aliasname>]
[KEYFIELDLIST <column-list>]
[PROPERTIES <properties_string>]
AS <select statement>
See Also
ADD TABLE, ALTER INDEX, ALTER TABLE, COPY DATABASE, CREATE VIRTUALTABLE, CONSTRAINTS, CREATE DATABASE, CREATE TABLE, CursorAdapter, CURSORADAPTER(), DATA TYPES, DROP DATABASE, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, SELECT, SET XMLFORMAT, TABLEINFO(), USE
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.
| 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. |
| 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'. |
| 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 connstr "awhr" as select * from sales.currency alter virtualtable scurr connstr "awhr" as select name from sales.currency
