See Also: Close, Declare_Datafile, Relate, Opening a Table Without Using an Intermediate File
To open a table using the specified table name or table number.
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.
Open opens table, including all associated index files and makes its fields and records available for processing by the program.
Open opens the table set identified in the filelist for table unless you use the as option. Open as 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 the database you are using. Specifically, see Driver Special Open for using "Open As" with client/server tables.
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.
Index at the end of the command causes the index of that number to be buffered in memory. This is of use 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 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 table set delinqs.* in folder \account. The table is referred to throughout the program as vendor, even though that is not its folder Rootname 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, and 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, you have three vendor tables: vendor1, vendor2 and vendor3. If 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.
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. It only consumes 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 Rootname 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 Rootnames 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 tablenames 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 true (and finderr set false) if an open command was successful. Found is set false (and finderr set 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 tablenumber 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.