Creating and dropping CONNECT Tables
Create Table statements for “CONNECT” tables are standard MySQL create statements specifying “engine=CONNECT”. There are a few additional table and column options specific to CONNECT.
Table options:
(More options will likely be added to this list in future versions of the handler.)
| Table Option | Type | Description |
|---|---|---|
| TABLE_TYPE | String | The type of the external table: DOS, FIX, BIN, CSV, FMT, XML, INI, DBF, VEC, ODBC, MYSQL, TBL, DIR, WMI, MAC. PROXY, XCOL, OCCUR, PIVOT and EOM. Defaults to DOS. |
| FILE_NAME | String | The file (path) name for all table types based on files. Can be absolute or relative to the current data directory. |
| XFILE_NAME | String | The file (path) base name for a table index files. Can be absolute or relative to the data directory. Defaults to the file name. |
| TABNAME | String | The target table or node for ODBC, MYSQL, XML or catalog tables. |
| TABLE_LIST | String | The comma separated list of a TBL table sub-tables. |
| DBNAME | String | The target database for ODBC, MYSQL or catalog tables. |
| DATA_CHARSET | String | The character set used in the external file or data source. |
| SEP_CHAR | String | Specifies the field separator character of a CSV tables. |
| QCHAR | String | Specifies the character used for quoting some fields of a CSV table or the identifiers of an ODBC tables. |
| MODULE | String | The (path) name of the DLL or shared lib implementing the access of a non-standard (OEM) table type. |
| SUBTYPE | String | The subtype of an OEM table type. |
| CATFUNC | String | The catalog function used by a catalog table. |
| OPTION_LIST | String | Used to specify all other options not yet directly defined. |
| MAPPED | Boolean | Specifies whether “file mapping” is used to handle the table file. |
| HUGE | Boolean | To specify that a table file can be larger than 2GB. |
| COMPRESS | Boolean | True if the data file is compressed. Defaults to NO. |
| SPLIT | Boolean | True for a VEC table when each columns are in separate files. |
| READONLY | Boolean | True if the data file must not be modified or erased. |
| SEPINDEX | Boolean | When true, indexes are saved in separate files. |
| LRECL | Integer | The file record size (often calculated by default). |
| BLOCK_SIZE | Integer | The number of rows each block of FIX, BIN, DBF OR VEC tables contains. For an ODBC table this is the RowSet size option. |
| MULTIPLE | Integer | Used to specify multiple file tables. |
| HEADER | Integer | Applies to CSV, VEC and HTML files. Its meaning depends on the table type. |
| QUOTED | Integer | The level of quoting used in CSV table files. |
| ENDING | Integer | End of line length. Default to 1 for Unix/Linux and 2 for Windows. |
Because CONNECT handles many table types; many table type specific options are not into the above list and must be entered using the OPTION_LIST option. The syntax to use is:
... option_list='opname1=opvalue1,opname2=opvalue2...'
Beware that no blanks should be inserted before or after the ‘=’ and ‘,’ characters. The option name is all that is between the start of the string or the last ‘,’ character and the next ‘=’ character, and the option value is all that is between this ‘=’ character and the next ‘,’ or end of string. For instance:
option_list='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow';
This defines four options, ‘name’, ‘coltype’, ‘attribute’, and ‘headattr’ with values ‘TABLE’, ‘HTML’, ‘border=1;cellpadding=5’, and ‘bgcolor=yellow’. The only restriction is that values cannot contain commas, but they can contain equal signs.
Column options:
| Column Option | Type | Description |
|---|---|---|
| FLAG | Integer | An integer value whose meaning depends on the table type. |
| FIELD_LENGTH | Integer | Set the internal field length for DATE columns. |
| DATE_FORMAT | String | The format indicating how a date is stored in the file. |
| FIELD_FORMAT | String | The column format for some table types. |
| SPECIAL | String | The name of the SPECIAL column that set this column value. |
Note 1: Creating a CONNECT table based on file does not erase or create the file if the file name is specified in the CREATE TABLE statement (“outward” table). If the file does not exist, it will be populated by subsequent INSERT commands or by the “AS select statement” of the CREATE TABLE command. Unlike the CSV engine, CONNECT easily permits to create tables based on already existing files, for instance files made by other applications. However, if the file name is not specified, a file with a name defaulting to tabname.tabtype will be created in the data directory (“inward” table).
Note 2: Dropping a CONNECT table is done with a standard DROP statement. For outward tables, this drops only the CONNECT table definition but does not erase the corresponding data file and index files. Use DELETE or TRUNCATE to do so. This is contrary to data and index files of inward tables are erased on DROP like for other MariaDB engines.
