Creating and dropping CONNECT Tables
MariaDB starting with 10.0
The CONNECT handler was introduced in MariaDB 10.0.
Create Table statements for “CONNECT” tables are standard MariaDB 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 external table type: DOS, FIX, BIN, CSV, FMT, XML, INI, DBF, VEC, ODBC, MYSQL, TBL, DIR, WMI, MAC, and OEM. Defaults to DOS, MYSQL, or PROXY depending on what options are used. |
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 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, PROXY, or catalog tables; or the top node name for XML tables. |
TABLE_LIST | String | The comma separated list of TBL table sub-tables. |
DBNAME | String | The target database for ODBC, MYSQL, catalog, and PROXY based tables. The database concept is sometimes known as a schema. |
DATA_CHARSET | String | The character set used in the external file or data source. |
SEP_CHAR | String | Specifies the field separator character of CSV tables. |
QCHAR | String | Specifies the character used for quoting some fields of a CSV table or the identifiers of an ODBC table. |
SRCDEF | String | The source definition of a table retrieved via ODBC or the MySQL API. |
COLIST | String | The column list of an OCCUR table. |
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 of the 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 a FIX, BIN, DBF, or VEC table 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. Defaults to 1 for Unix/Linux and 2 for Windows. |
Because CONNECT handles many table types; many table type specific options are
not in the above list and must be entered using the OPTION_LIST option. The
syntax to use is:
... option_list='opname1=opvalue1,opname2=opvalue2...'
Be aware 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', respectively. 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 the creation of 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
tablename.tabletype 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.
