USE

From Lianjapedia
Revision as of 09:02, 4 March 2019 by Yvonne.milne (Talk | contribs)

Jump to: navigation, search

Purpose

Open a table

Syntax

USE [<filename> | (<expC1>)

[AGAIN]

[ALIAS <name>]

[AUTOMEM]

[CONNHANDLE <expN>]

[CONNSTR <expC2> [FIELDS <"sqlfieldlist">] [WHERE <sqlcondition>] [ORDER BY <sqlorder>]]

[ENCRYPTION <expC3>]

[EXCLUSIVE]

[IN <cursor>]

[INDEX <index filelist> | (<expC4>)]

[NODBX]

[NOUPDATE]

[ORDER <.ndx file>|[TAG] <.dbx tag> [OF <.dbx filename>]]]


USE <virtual-table> WHERE <sqlpassthru> | AS <sqlpassthru>


USE <DSN>::<table> [ALIAS <name>] [IN <cursor>]

See Also

ALTER VIRTUALTABLE, AUSED(), CREATE VIEW, CREATE VIRTUALTABLE, CursorAdapter, CURSORADAPTER(), DECRYPT, ENCRYPT, INUSE(), OPEN DATABASE, SELECT, SELECT(), SET DATADIR, SET ENCRYPTION, SET EXCLUSIVE, SET INDEX, SET ORDER, SET SHARE, SQLCONNECT(), SQLDISCONNECT(), USED()

Description

The USE command opens an existing table in the currently selected cursor (also known as workarea) or in the cursor specified in the IN <cursor> option. The <filename> can be substituted with a <expC1> enclosed in round brackets that returns a valid file name. The <filename> can include the encryption key for encrypted database tables. The three part comma-separated key should be enclosed in angled brackets and appended to the filename, e.g. mytable<key_1,key_2,key_3>.

AGAIN

The AGAIN keyword is used to open an already open table in another cursor.

ALIAS <name>

An optional alias name can be specified for the workarea with the ALIAS keyword. Once a table is opened in a workarea, the workarea can be identified using any of the following 'aliases': the workarea letter (A-Z) excluding M; the specified ALIAS name, or if none is specified, the first 32 characters of the file name.

Note: for the use of UI Element Alias Names, see UI Presentation Rules:Using UI Element Alias Names. For user-defined commands, see the ALIAS command.

AUTOMEM

An empty memory variable of corresponding name, data type and size will be created for each field from the table.

CONNHANDLE <expN>

Provides support for pooled ODBC connections. The <expN> is an active connection handle established by SQLCONNECT(). This will share multiple local cursors using one ODBC connection. The connection is reference counted and will only be disconnected when all local cursors are closed or you issue a SQLDISCONNECT(0).

CONNSTR <expC2>

Transparently use ODBC data sources by specifying an ODBC data source connection string in <expC2>. Data is then made available as a local cursor. SQL update/delete commands are handled transparently and the cursor appears as a local one to the scripting code. You can also include a FIELDS clause with a comma-separated field list in quotes, a WHERE SQL condition clause and an ORDER BY SQL ordering clause.

ENCRYPTION <expC3>

The ENCRYPTION <expC3> clause is used to specify the encryption key for encrypted tables. The <expC3> is a string containing a three part comma-separated key, e.g. "key_1,key_2,key_3". The key may optionally be enclosed in angled brackets, e.g. "<key_1,key_2,key_3>". ". The SET ENCRYPTION command allows a default encryption key to be defined. If the ENCRYPTION <expC3> clause is not specified and the key is not included in the <filename>, this default key will be used. If the default key is not the correct key for the table, an error will be given.

EXCLUSIVE

If the EXCLUSIVE keyword is specified, then the table is opened for private use, disallowing access by other users. To open a table for shared use, issue the SET EXCLUSIVE OFF command before the USE command.

IN <cursor>

If the IN keyword is specified then the table is opened in the workarea specified by alias. Alias can be either: the workarea letter (A-Z) excluding M or the workarea number. Specifying 0 as the workarea number causes the table to be opened in the lowest free workarea.

INDEX <index filelist>

The INDEX <index filelist> clause specifies a list of index files which should be opened and associated with the table. The <index filelist> may contain both single (.ndx), and multiple (.dbx) files. The first index in the list is known as the master index file, and is used to search for key values with the SEEK and FIND commands. The SET ORDER TO command can be used to reselect a master index from the list of open index files. Index files are created using the INDEX ON command.

NODBX

If the NODBX keyword is specified then the table is opened without its associated production index file. All index definitions in the table header are released.

NOUPDATE

When NOUPDATE is specified, the table and related index file are opened with read only access. When used with an RMS bridge on OpenVMS, the RMS file is opened with 'shared read' access instead of 'shared read-write' access.

ORDER <.ndx file> | [TAG] <.dbx tag> [OF <.dbx filename>]

The ORDER qualifier can be used to specify the master index. The order can be specified by using the name of an .ndx file or by specifying the name of a tag. The OF <.dbx filename> may be used to explicitly specify the .dbx file to use.

USE <virtual-table> WHERE <sqlpassthru> | AS <sqlpassthru> | NODATA

This format of the USE command is only available for virtual tables. The WHERE or AS clauses should be followed by SQL statements in the syntax of the back end database. The <sqlpassthru> statement will be passed to the back end database for processing. Specifying NODATA specifies that only the structure is downloaded, not the data (from Lianja v3.2).

USE <DSN>::<table> [ALIAS <name>] [IN <cursor>]

This format of the USE command can be used to open a table from a remote ODBC data source. The <DSN> is the name of a 32-bit ODBC System DSN; the <table> is the name of the table. From v5.0.


All open files associated with the currently selected workarea are closed before any new files are opened. The USE command on its own closes all open files associated with the currently selected workarea. After a table has been opened, the record pointer is positioned to record number 1 if no index files have been specified, or to the first record in the master index if the table has been opened with associated index files.

Please see the SQL USE command for information on the MySQL compatible USE <database> usage.

Example

use accounts index address.dbx;
  order tag last of address;
  in 3
 
// Connection handle example
m_handle = sqlconnect("odbc_dsn")
use customers connhandle m_handle
select 0
use orders connhandle m_handle
sqldisconnect(0)
 
// Connection string example
use customers connstr "northwind" where customer_name!='';
  and order_amount>1000 order by customer_name
/* Once the table is in use you can get a reference to the internal
CursorAdapter using the cursorAdapter() function */
ca = cursoradapter("customers")
ca.cursorRefresh()
 
// Another example
use sales.customer connstr "awhr";
 fields "customerid, accountnumber, customertype, territoryid";
 where territoryid < 3 order by territoryid
 
// Using the actor table from the MySQL ODBC DSN lianja_mysqltest
use lianja_mysqltest::actor