CONNECT Table Types - Catalog Tables

A catalog table is one that returns information about another table, or data source. It is similar to what MariaDB commands such as DESCRIBE or SHOW do. Applied to local tables, this just duplicates what these commands do, with the noticeable difference that they are tables and can be used inside queries as joined tables or inside sub-selects.

But their main interest is to enable querying the structure of external tables that cannot be directly queried with description commands. Let's see an example:

Suppose we want to access the tables from a Microsoft Access database as an ODBC type table. The first information we must obtain is the list of tables existing in this data source. To get it, we will create a catalog table that will return it extracted from the result set of the SQLTables ODBC function:

create table tabinfo (
  table_name varchar(128) not null,
  table_type varchar(16) not null)
engine=connect table_type=ODBC catfunc=tables
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

The SQLTables function returns a result set having the following columns:

FieldData TypeNullInfo TypeFlag Value
Table_Catchar(128)NOFLD_CAT17
Table_Namechar(128)NOFLD_SCHEM18
Table_Namechar(128)NOFLD_NAME1
Table_Typechar(16)NOFLD_TYPE2
Remarkchar(128)NOFLD_REM5

Note: The Info Type and Flag Value are CONNECT interpretations of this result.

Here we could have omitted the column definitions of the catalog table or, as in the above example, chose the columns returning the name and type of the tables. If specified, the columns must have the exact name of the corresponding SQLTables result set, or be given a different name with the matching flag value specification.

(The Table_Type can be TABLE, SYSTEM TABLE, VIEW, etc.)

For instance, to get the tables we want to use we can ask:

select table_name from tabinfo where table_type = 'TABLE';

This will return:

table_name
Categories
Customers
Employees
Products
Shippers
Suppliers

Now we want to create the table to access the CUSTOMERS table. Because CONNECT can retrieve the column description of ODBC tables, it not necessary to specify them in the create table statement:

create table Customers engine=connect table_type=ODBC
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

However, if we prefer to specify them (to eventually modify them) we must know what the column definitions of that table are. We can get this information with a catalog table. This is how to do it:

create table custinfo engine=connect table_type=ODBC
tabname=customers catfunc=columns
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

Alternatively it is possible to specify what columns of the catalog table we want:

create table custinfo (
  column_name char(128) not null,
  type_name char(20) not null,
  length int(10) not null flag=7,
  prec smallint(6) not null flag=9)
  nullable smallint(6) not null)
engine=connect table_type=ODBC tabname=customers
catfunc=columns
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

To get the column info:

select * from custinfo;

which results in this table:

column_nametype_namelengthprecnullable
CustomerIDVARCHAR501
CompanyNameVARCHAR4001
ContactNameVARCHAR3001
ContactTitleVARCHAR3001
AddressVARCHAR6001
CityVARCHAR1501
RegionVARCHAR1501
PostalCodeVARCHAR1001
CountryVARCHAR1501
PhoneVARCHAR2401
FaxVARCHAR2401

Now you can create the CUSTOMERS table as:

create table Customers (
  CustomerID varchar(5),
  CompanyName varchar(40),
  ContactName varchar(30),
  ContactTitle varchar(30),
  Address varchar(60),
  City varchar(15),
  Region varchar(15),
  PostalCode varchar(10),
  Country varchar(15),
  Phone varchar(24),
  Fax varchar(24))
engine=connect table_type=ODBC block_size=10
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

Let us explain what we did here: First of all, the creation of the catalog table. This table returns the result set of an ODBC SQLColumns function sent to the ODBC data source. Columns functions always return a data set having some of the following columns, depending on the table type:

FieldData TypeNullInfo TypeFlag ValueReturned by
Table_Cat*char(128)NOFLD_CAT17ODBC, JDBC
Table_Schema*char(128)NOFLD_SCEM18ODBC, JDBC
Table_Namechar(128)NOFLD_TABNAME19ODBC, JDBC
Column_Namechar(128)NOFLD_NAME1ALL
Data_Typesmallint(6)NOFLD_TYPE2ALL
Type_Namechar(30)NOFLD_TYPENAME3ALL
Column_Size*int(10)NOFLD_PREC4ALL
Buffer_Length*int(10)NOFLD_LENGTH5ALL
Decimal_Digits*smallint(6)NOFLD_SCALE6ALL
Radixsmallint(6)NOFLD_RADIX7ODBC, JDBC, MYSQL
Nullablesmallint(6)NOFLD_NULL8ODBC, JDBC, MYSQL
Remarkschar(255)NOFLD_REM9ODBC, JDBC, MYSQL
Collationchar(32)NOFLD_CHARSET10MYSQL
Keychar(4)NOFLD_KEY11MYSQL
Default_valueN.A.FLD_DEFAULT12
PrivilegeN.A.FLD_PRIV13
Date_fmtchar(32)NOFLD_DATEFMT15MYSQL
Xpath/JpathVarchar(256)NOFLD_FORMAT16XML/JSON

'*': These names have changed since earlier versions of CONNECT.

Note: ALL includes the ODBC, JDBC, MYSQL, DBF, CSV, PROXY, TBL, XML, JSON, XCOL, and WMI table types. More could be added later.

We chose among these columns the ones that were useful for our create statement, using the flag value when we gave them a different name (case insensitive).

The options used in this definition are the same as the one used later for the actual CUSTOMERS data tables except that:

  1. The TABNAME option is mandatory here to specify what the queried table name is.
  2. The CATFUNC option was added both to indicate that this is a catalog table, and to specify that we want column information.

Note: If the TABNAME option had not been specified, this table would have returned the columns of all the tables defined in the connected data source.

Currently the available CATFUNC are:

FunctionSpecified as:Applies to table types:
FNC_TABtablesODBC, JDBC, MYSQL
FNC_COLcolumnsODBC, JDBC, MYSQL, DBF, CSV, PROXY, XCOL, TBL, WMI
FNC_DSNdatasources
dsn
sqldatasources
ODBC
FNC_DRIVERdrivers
sqldrivers
ODBC, JDBC

Note: Only the bold part of the function name specification is required.

The DATASOURCE and DRIVERS functions respectively return the list of available data sources and ODBC drivers available on the system.

The SQLDataSources function returns a result set having the following columns:

FieldData TypeNullInfo TypeFlag value
Namevarchar(256)NOFLD_NAME1
Descriptionvarchar(256)NOFLD_REM9

To get the data source, you can do for instance:

create table datasources (
engine=CONNECT table_type=ODBC catfunc=DSN;

The SQLDrivers function returns a result set having the following columns:

FieldTypeNullInfo TypeFlag value
Descriptionvarchar(128)YESFLD_NAME1
Attributesvarchar(256)YESFLD_REM9

You can get the driver list with:

create table drivers
engine=CONNECT table_type=ODBC catfunc=drivers;

Another example, WMI table

To create a catalog table returning the attribute names of a WMI class, use the same table options as the ones used with the normal WMI table plus the additional option ‘catfunc=columns’. If specified, the columns of such a catalog table can be chosen among the following:

NameTypeFlagDescription
Column_NameCHAR1The name of the property
Data_TypeINT2The SQL data type
Type_NameCHAR3The SQL type name
Column_SizeINT4The field length in characters
Buffer_LengthINT5Depends on the coding
ScaleINT6Depends on the type

If you wish to use a different name for a column, set the Flag column option.

For example, before creating the "csprod" table, you could have created the info table:

create table CSPRODCOL (
  Column_name char(64) not null,
  Data_Type int(3) not null,
  Type_name char(16) not null,
  Length int(6) not null,
  Prec int(2) not null flag=6)
engine=CONNECT table_type='WMI' catfunc=col;

Now the query:

select * from csprodcol;

will display the result:

Column_nameData_TypeType_nameLengthPrec
Caption1CHAR2551
Description1CHAR2551
IdentifyingNumber1CHAR2551
Name1CHAR2551
SKUNumber1CHAR2551
UUID1CHAR2551
Vendor1CHAR2551
Version1CHAR2551

This can help to define the columns of the matching normal table.

Note 1: The column length, for the Info table as well as for the normal table, can be chosen arbitrarily, it just must be enough to contain the returned information.

Note 2: The Scale column returns 1 for text columns (meaning case insensitive); 2 for float and double columns; and 0 for other numeric columns.

Catalog Table result size limit

Because catalog tables are processed like the information retrieved by “Discovery” when table columns are not specified in a Create Table statement, their result set is entirely retrieved and memory allocated.

By default, this allocation is done for a maximum return line number of:

CatfuncMax lines
Drivers256
Data Sources512
Columns20,000
Tables10,000

When the number of lines retrieved for a table is more than this maximum, a warning is issued by CONNECT. This is mainly prone to occur with columns (and also tables) with some data sources having many tables when the table name is not specified.

If this happens, it is possible to increase the default limit using the MAXRES option, for instance:

create table allcols engine=connect table_type=odbc
connection='DSN=ORACLE_TEST;UID=system;PWD=manager'
option_list='Maxres=110000' catfunc=columns;

Indeed, because the entire table result is memorized before the query is executed; the returned value would be limited even on a query such as:

select count(*) from allcols;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.