Типы таблиц CONNECT - Каталог таблиц

Таблица каталога — это таблица, которая возвращает информацию о другой таблице или источнике данных. Она аналогична командам MariaDB, таким как DESCRIBE или SHOW. При применении к локальным таблицам, она просто дублирует функции этих команд, с заметным отличием, что это таблицы, которые могут использоваться в запросах как таблицы соединения или внутри подзапросов.

Но их основная задача — возможность запроса структуры внешних таблиц, которые нельзя напрямую запросить с помощью команд описания. Давайте рассмотрим пример:

Предположим, мы хотим получить доступ к таблицам из базы данных Microsoft Access как к таблице типа ODBC. Первая информация, которую мы должны получить, — это список таблиц, существующих в этом источнике данных. Для этого мы создадим таблицу каталога, которая вернёт её, извлечённую из набора результатов функции SQLTables ODBC:

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;';

Функция SQLTables возвращает набор результатов, содержащий следующие столбцы:

Примечание: Тип инф. и Значение флага — это интерпретации CONNECT для этого результата.

Здесь мы могли бы опустить определения столбцов таблицы каталога или, как в приведённом примере, выбрать столбцы, возвращающие имя и тип таблиц. Если указаны, столбцы должны иметь точное имя соответствующего набора результатов SQLTables или получить другое имя со спецификацией соответствующего значения флага.

(Тип Table_Type может быть TABLE, SYSTEM TABLE, VIEW и т. д.)

Например, чтобы получить таблицы, которые мы хотим использовать, мы можем спросить:

select table_name from tabinfo where table_type = 'TABLE';

Это вернёт:

Теперь мы хотим создать таблицу для доступа к таблице CUSTOMERS. Поскольку CONNECT может извлекать описание столбцов таблиц ODBC, нет необходимости указывать их в операторе создания таблицы:

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

Однако, если мы хотим их указать (чтобы впоследствии их изменить), мы должны знать, каковы определения столбцов этой таблицы. Мы можем получить эту информацию с помощью таблицы каталога. Вот как это сделать:

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;';

В качестве альтернативы можно указать, какие столбцы таблицы каталога мы хотим:

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;';

Чтобы получить информацию о столбцах:

select * from custinfo;

что приводит к этой таблице:

Теперь вы можете создать таблицу CUSTOMERS как:

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;';

Давайте объясним, что мы сделали: Прежде всего, создание таблицы каталога. Эта таблица возвращает набор результатов функции ODBC SQLColumns, отправленной в источник данных ODBC. Функции столбцов всегда возвращают набор данных, содержащий некоторые из следующих столбцов, в зависимости от типа таблицы:

'*': Эти имена изменились с предыдущих версий CONNECT.

Примечание: ВСЕ включает типы таблиц ODBC, JDBC, MYSQL, DBF, CSV, PROXY, TBL, XML, JSON, XCOL и WMI. В будущем могут быть добавлены и другие.

Мы выбрали среди этих столбцов те, которые были полезны для нашего оператора создания, используя значение флага, когда мы дали им другое имя (регистронезависимое).

Параметры, используемые в этом определении, такие же, как и те, которые используются позже для фактических таблиц данных CUSTOMERS, за исключением того, что:

1. Параметр TABNAME здесь обязателен для указания имени таблицы, которая запрашивается.
2. Параметр CATFUNC был добавлен, чтобы указать, что это таблица каталога, и для указания, что мы хотим информацию о столбцах.

Примечание: Если бы параметр TABNAME не был указан, эта таблица возвращала бы столбцы всех таблиц, определённых в подключённом источнике данных.

В настоящее время доступные CATFUNC:

Примечание: Требуется только выделенная жирным шрифтом часть спецификации имени функции.

Функции DATASOURCE и DRIVERS соответственно возвращают список доступных источников данных и драйверов ODBC, доступных в системе.

Функция SQLDataSources возвращает набор результатов, содержащий следующие столбцы:

Для получения источника данных вы можете сделать, например:

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

Функция SQLDrivers возвращает набор результатов, содержащий следующие столбцы:

Для получения списка драйверов вы можете сделать:

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

Другой пример, таблица WMI

Для создания таблицы каталога, возвращающей имена атрибутов класса WMI, используйте те же параметры таблицы, что и с обычной таблицей WMI, плюс дополнительный параметр «catfunc=columns». Если указаны, столбцы такой таблицы каталога могут быть выбраны среди следующих:

Если вы хотите использовать другое имя для столбца, установите опцию столбца Флаг.

Например, перед созданием таблицы "csprod" вы могли бы создать таблицу информации:

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;

Теперь запрос:

select * from csprodcol;

отобразит результат:

Это может помочь определить столбцы соответствующей обычной таблицы.

Примечание 1: Длина столбца для таблицы Info, а также для обычной таблицы может быть выбрана произвольно, она просто должна быть достаточно большой для хранения возвращаемой информации.

Примечание 2: Столбец Scale возвращает 1 для текстовых столбцов (означает регистронезависимость); 2 для столбцов float и double; и 0 для других числовых столбцов.

Предельный размер результата таблицы каталога

Поскольку таблицы каталога обрабатываются как информация, получаемая при «поиске», когда столбцы таблиц не указаны в операторе Create Table, их набор результатов полностью извлекается и выделяется память.

По умолчанию это выделение памяти выполняется для максимального количества строк возврата:

Когда количество извлеченных строк для таблицы превышает это максимальное значение, CONNECT выдает предупреждение. Это чаще всего происходит со столбцами (и также с таблицами) с некоторыми источниками данных, имеющими много таблиц, когда имя таблицы не указано.

В случае возникновения этой проблемы можно увеличить предельное значение по умолчанию, используя опцию MAXRES, например:

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

Действительно, поскольку весь результат таблицы запоминается перед выполнением запроса; возвращаемое значение будет ограничено даже в запросе, таком как:

select count(*) from allcols;