Skip to content

Open

See Also: Close, Declare Datafile, Relate, Opening a Table Without Using an Intermediate File

Purpose

To open a table using the specified table name or table number.

Syntax

Open [{rootName} As] {table-name} [Mode {table-mode}] [{index}]

table-mode: The mode to open the table in: either DF_SHARE or DF_EXCLUSIVE. If omitted, DF_SHARE is used (the table is opened non-exclusively).

Driver-specific open syntax: When using a database other than the embedded database, a driver may support a special form of the Open As command, where rootName contains information to locate tables. See Driver Special Open for syntax and further information.

What It Does

The Open command opens the specified table, including all associated index files, and makes its fields and records available for processing by the program.

The command opens the table set identified in the filelist for table unless you use the As option. The Open As command allows you to specify the full path (with no extension) of the table set to open, regardless of what is contained in the filelist. This path may contain a driver name (e.g., odbc_drv:person would open the table person in an ODBC data source). If the As option is used, and table contains 0, the table's number (from the FILELIST.CFG) will be retrieved and placed into table.

If you are using a database other than the embedded database, consult the documentation for the driver you are using for more information about opening tables in that database. Specifically, see Driver Special Open for using "Open As" with client/server tables.

Example

Integer hTable
Move 0 to hTable
Open "employee" as hTable

In this example, if employee is Number 7 in the filelist, then hTable will contain 7. If employee is not in your current filelist, you will get an error (see the note later in this article to open a table that is not in your filelist).

This method can be employed when you would like to use a string variable to open a table, and you need to know its table number. Commands which take a table number as a parameter (e.g., Get_Attribute) can then be called for this table.

The Index at the end of the command causes the index of that number to be buffered in memory. This is useful if your program produces a report of record data from table in the order of an index.

Open customer

This command opens the customer table.

Open vendor 3

This example opens the database table vendor and buffers part of its third index in memory, thereby making sequential access to its records faster.

Open "account\delinqs" as vendor

This example opens the table set delinqs.* in the folder \account. The table is referred to throughout the program as vendor, even though that is not its folder root name in the filelist.

The As option is useful when you would like to choose a physical table set to process at runtime. In this case, the value of rootName may be input at runtime through a variable before As.

To do this, table should be in your filelist, and its fields must have the same names, be of the same types (String, Number, Date, or Text), and in the same order as they are in rootName.

Another use for the As option is for opening a table with a generic table definition. For example, if you have three vendor tables: vendor1, vendor2, and vendor3, and they all have the same physical structure (field names, types, and lengths), then you could have one .FD file for all three: vendor.fd.

Open "vendor1" as vendor

This example looks for the file vendor.fd, and if it is found, opens vendor1 using the table definition in vendor.fd.

Open Customer Mode DF_EXCLUSIVE

This example opens the customer data table in exclusive mode.

Notes

  • Index buffering increases the speed of processing only when access to records is sequential by the specified index, as in a report. When access is random, as in data entry to non-sequential records, index buffering won't increase speed and will only consume memory unnecessarily. Note that this behavior is specific to the DataFlex Driver—other drivers may behave differently.

  • When the As option is not used, the folder root name used is that specified in FILELIST.CFG for table, unless that name specifies no path, and a value is in force for the DFPATH System Variable. In such a case, the physical table set is located by the pathname given in DFPATH. Folder root names in FILELIST.CFG of DataFlex database tables can be set using the DF_FILE_ROOT_NAME attribute or in the Administration Tool.

  • In applications which may run on various operating systems, literal rootNames should be avoided in favor of variable rootNames. This is because the syntax of folders and table names varies from environment to environment, and variable rootNames can be specified in a separate installation program specifically for the target environment.

  • The predefined indicator found is set to true (and finderr set to false) if an open command was successful. Found is set to false (and finderr set to true) if an open command was not successful.

  • With the As option, it is possible to open a table that is not in your FILELIST.CFG, but doing so is not advised because relates to, and attaches from, table will not work. On the other hand, security reasons might incline you to keep table out of FILELIST.CFG.

  • When compiling a program using a table not in FILELIST.CFG, the table number used may be any number that does not conflict with the number of another table used in the same program.

Open "test" as 11

This example opens test, regardless of whether it is in your filelist, and regardless of whether or not test.fd exists.

  • When a table is opened whose table definition specifies that its Maximum Records is one (1), the table buffer is automatically filled with the data of the first record (by record number) of table. Such tables (with Maximum Records of 1) are referred to as "system tables," and customarily do not support record finding and have no indexes.

  • If table is taken from a database field, you should pass it through the Trim function before opening it with the Open As command. If you don't use Trim, the string containing the field name will include a trailing space, which confuses the command. For example, if the tablename field of the monument table contains the names of tables, you could open one of them with the following statement:

Open (Trim(monument.tablename)) as rushmore

  • This command should not be used within a transaction; it will cause an error. A transaction starts with a Begin Transaction or Lock command and ends with End Transaction or Unlock.

  • Whenever a program attempts to open a data table in exclusive mode, there is the possibility that another program already has the same data table open. If a program attempts to open a table exclusively and cannot obtain exclusive access, error 4177 will be triggered.