Типы таблиц ODBC подключения: Доступ к таблицам из другой СУБД

ODBC (Open Database Connectivity) — это стандартный API для доступа к системам управления базами данных (DBMS). CONNECT использует этот API для доступа к данным, содержащимся в других DBMS, без необходимости реализации отдельного приложения для каждой из них. Исключением является доступ к MySQL, который должен выполняться с использованием типа таблицы MYSQL.

Эти таблицы имеют тип ODBC. Например, если таблица «Клиенты» содержится в базе данных Access™, вы можете определить её с помощью команды:

create table Customer (
  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
tabname='Customers'
Connection='DSN=MS Access Database;DBQ=C:/Program
Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

Параметр Tabname по умолчанию принимает значение имени таблицы. Он требуется, если имя исходной таблицы отличается от имени таблицы CONNECT. Обратите также внимание, что для некоторых источников данных это имя чувствительно к регистру.

Часто, поскольку CONNECT может извлекать описание таблицы, используя функции каталога ODBC, определения столбцов можно не указывать. Например, эту таблицу можно просто создать как:

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

Спецификация BLOCK_SIZE будет использоваться позже для установки значения RowsetSize при извлечении строк из таблицы ODBC. Достаточно большое значение RowsetSize может значительно ускорить процесс извлечения.

Если вы указываете описание столбца, имена столбцов вашей таблицы должны существовать в таблице источника данных. Однако вы не обязаны определять все столбцы источника данных и можете изменить порядок столбцов. Также может быть выполнено некоторое преобразование типов, если это необходимо. Например, для доступа к образцовой таблице FireBird EMPLOYEE можно определить вашу таблицу как:

create table empodbc (
  EMP_NO smallint(5) not null,
  FULL_NAME varchar(37) not null),
  PHONE_EXT varchar(4) not null,
  HIRE_DATE date,
  DEPT_NO smallint(3) not null,
  JOB_COUNTRY varchar(15),
  SALARY double(12,2) not null)
engine=CONNECT table_type=ODBC tabname='EMPLOYEE'
connection='DSN=firebird';

Это определение игнорирует столбцы FIRST_NAME, LAST_NAME, JOB_CODE и JOB_GRADE. Оно помещает столбец FULL_NAME исходной таблицы на второе место. Тип столбца HIRE_DATE был изменён с timestamp на date, а тип столбца DEPT_NO — с char на integer.

В настоящее время для таблиц ODBC применяются некоторые ограничения:

1. Тип курсора — только вперёд (последовательное чтение).
2. Индексация таблиц ODBC недоступна (не указывайте столбцы в качестве ключа). Однако, поскольку CONNECT часто может добавить в запрос, отправленный в источник данных, предложение where, индексация будет использоваться источником данных, если он её поддерживает. (Удаленная индексация доступна с версией 1.04, выпущенной с MariaDB 10.1.6)
3. CONNECT ODBC поддерживает SELECT и INSERT. UPDATE и DELETE также поддерживаются в несколько ограниченном виде (см. ниже). Для других операций используйте таблицу ODBC с опцией EXECSRC (см. ниже), чтобы напрямую отправлять соответствующие команды в источник данных.

Случайный доступ к таблицам ODBC

В версии CONNECT 1.03 (до MariaDB 10.1.5) таблицы ODBC не индексируемы. Версия 1.04 (с MariaDB 10.1.6) добавляет возможность удалённой индексации к типу таблицы ODBC.

Однако некоторые запросы требуют случайного доступа к таблице ODBC, например, когда она объединяется с другой таблицей или используется в запросах с ORDER BY, применённых к длинному столбцу или большим таблицам.

Существует несколько способов включения случайного (позиционного) доступа к таблице CONNECT ODBC. Они зависят от следующих параметров таблицы:

* — для указания в списке параметров.

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

Другой способ хранения набора результатов в памяти — использование параметра memory. Этот параметр может принимать следующие значения:

0. Память не используется (по умолчанию). Лучше всего, когда таблица читается последовательно, как в операторах SELECT с условными предложениями WHERE.
1. Размер памяти вычисляется при первом последовательном чтении таблицы. Выделенная память заполняется при втором последовательном чтении. Затем строки таблицы извлекаются из памяти. Это следует использовать, когда к таблице будет несколько раз обращаться случайным образом, например, в подзапросах или в качестве целевой таблицы в объединении.
2. Сначала выполняется запрос для получения размера набора результатов, и выделяется необходимая память. Она заполняется при первом последовательном чтении. Затем случайный доступ к таблице возможен. Это может быть использовано в случае операторов ORDER BY, когда MariaDB использует чтение по позиции.

Обратите внимание, что лучший способ обработки операторов ORDER BY — установить переменную max_length_for_sort_data в большее значение (её значение по умолчанию — 1024, что довольно мало). Действительно, это требует меньшего использования памяти, особенно когда предложение WHERE ограничивает набор извлекаемых данных. Это связано с тем, что в случае запроса с ORDER BY MariaDB сначала последовательно извлекает набор результатов и позицию каждой записи. Часто сортировка может выполняться из набора результатов, если он не слишком велик. Но если он слишком велик или если он подразумевает некоторые «длинные» столбцы, то сортируются только позиции, и MariaDB извлекает окончательный результат из таблицы, прочитанной в случайном порядке. Если установка переменной max_length_for_sort_data невозможна или не работает, чтобы иметь возможность извлекать данные таблицы из памяти после первого последовательного чтения, параметр памяти должен быть установлен в 2.

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

С версией CONNECT 1.04 (с MariaDB 10.1.6) существует ещё один способ обеспечить случайный доступ — указать столбцы для индексирования. Это следует делать только в том случае, если соответствующий столбец таблицы-источника также индексирован. Это следует использовать для таблиц, слишком больших для хранения в памяти, и аналогично удалённой индексации, используемой типом таблицы MYSQL и FEDERATED движком.

Остаётся возможность извлечения данных из внешней таблицы и построения другой таблицы любого формата из источника данных. Например, чтобы создать таблицу с фиксированным форматом DOS, содержащую данные таблицы CUSTOMER, создайте таблицу как

create table Custfix engine=connect File_name='customer.txt'
  table_type=fix block_size=20 as select * from customer;

Теперь вы можете использовать custfix для быстрых операций с базой данных на скопированных данных таблицы customer.

Извлечение данных из электронных таблиц

ODBC также можно использовать для создания таблиц на основе табличных данных, принадлежащих электронным таблицам Excel:

create table XLCONT
engine=CONNECT table_type=ODBC tabname='CONTACT'
Connection='DSN=Excel Files;DBQ=D:/Ber/Doc/Contact_BP.xls;';

Это предполагает, что табличная область листа, включая заголовки столбцов, определена как таблица с именем CONTACT или с использованием «именной ссылки». Обратитесь к документации Excel, чтобы узнать, как указать таблицы внутри листов. После этого вы можете запросить:

select * from xlcont;

Это извлечёт данные из Excel и отобразит:

Опять же, описание столбцов было оставлено CONNECT при создании таблицы.

Несколько таблиц ODBC

Концепция нескольких таблиц может быть расширена до таблиц ODBC, если они физически представлены файлами, например, таблицами Excel или Access. Условием является то, что строка подключения к таблице должна содержать поле DBQ=имя файла, в котором могут быть включены символы подстановки, как и для таблиц multiple=1 в их имени файла. Например, таблица, содержащаяся в нескольких файлах Excel CA200401.xls, CA200402.xls, ...CA200412.xls, может быть создана с помощью команды:

create table ca04mul (Date char(19), Operation varchar(64),
  Debit double(15,2), Credit double(15,2))
engine=CONNECT table_type=ODBC multiple=1
qchar= '"' tabname='bank account'
connection='DSN=Excel Files;DBQ=D:/Ber/CA/CA2004*.xls;';

При условии, что в каждом файле информация для применения устанавливается внутри Excel как таблица с именем «счёт банка». Это расширение для ODBC не поддерживает multiple=2. Параметр qchar был указан, чтобы сделать идентификаторы заключёнными в кавычки в операторе select, отправленном в ODBC, в частности, когда имя таблицы или столбца содержит пробелы, чтобы избежать ошибок синтаксиса SQL.

Рассмотрение производительности

Чтобы избежать извлечения целых таблиц из источника ODBC, что может занять много времени, CONNECT извлекает совместимую часть предложений WHERE запроса и добавляет её к запросу ODBC. Совместимость означает, что она должна пониматься источником данных. В частности, предложения, включающие скалярные функции, не сохраняются, потому что у источника данных могут быть другие функции, чем у MariaDB, или использоваться другой синтаксис. Конечно, предложения, включающие подзапросы, также пропускаются. Это перенесёт возможную индексацию в источник данных.

Использование таблиц ODBC внутри коррелированных подзапросов

В отличие от некоррелированных подзапросов, которые выполняются только один раз, коррелированные подзапросы выполняются многократно. Именно это ODBC называет «перевыполнением». CONNECT может использовать несколько методов для обработки этого в зависимости от настроек параметров MEMORY или SCROLLABLE:

Примечание: опции MEMORY и SCROLLABLE должны быть указаны в списке OPTION.

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

Если набор результатов имеет разумный размер, также можно указать опцию block_size, равную или немного большую, чем размер набора результатов. Весь набор результатов будет прочитан при первом извлечении и может быть доступен многократно без дополнительных действий.

Ещё одним хорошим способом решения проблемы является замена в коррелированном подзапросе таблицы ODBC на локальную копию, так как MariaDB часто может оптимизировать запрос и обеспечить очень быстрое выполнение.

Доступ к указанным представлениям

Вместо указания имени таблицы-источника через опцию TABNAME можно получить данные из «представления», определение которого задано в новой опции SRCDEF. Например:

CREATE TABLE custnum (
  country varchar(15) NOT NULL,
  customers int(6) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=ODBC BLOCK_SIZE=10
CONNECTION='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;'
SRCDEF='select country, count(*) as customers from customers group by country';

Или просто, потому что CONNECT может получить определение возвращаемых столбцов:

CREATE TABLE custnum ENGINE=CONNECT TABLE_TYPE=ODBC BLOCK_SIZE=10
CONNECTION='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;'
SRCDEF='select country, count(*) as customers from customers group by country';

Затем, при выполнении, например:

select * from custnum where customers > 3;

Обработка группировки выполняется источником данных, который возвращает только сгенерированный набор результатов, к которому локально применяется только условие WHERE. Результат:

Это позволяет источнику данных выполнять сложные операции, такие как объединение нескольких таблиц или выполнение процедур, возвращающих набор результатов. Это сводит к минимуму передачу данных через ODBC.

Операции изменения данных

Единственными операциями изменения данных являются команды INSERT, UPDATE и DELETE. Они могут быть выполнены успешно только в том случае, если база данных или таблицы источника данных не являются только для чтения.

Команда INSERT

При вставке значений в таблицу ODBC используются локальные значения, которые отправляются в таблицу ODBC. Это не имеет значения, когда значения являются константами, но в запросе, таком как:

insert into t1 select * from t2;

Где t1 — таблица ODBC, t2 — локально определённая таблица, которая должна существовать на локальном сервере. Кроме того, это хороший способ создать удалённую таблицу ODBC из локальных данных.

CONNECT напрямую не поддерживает команды INSERT, такие как:

insert into t1 values(2,'Deux') on duplicate key update msg = 'Two';

Конечно, часть «on duplicate key update» игнорируется и приведёт к ошибке, если значение ключа дублируется.

Команды UPDATE и DELETE

В отличие от команды INSERT, команды UPDATE и DELETE поддерживаются в упрощенном виде. Поддерживаются только простые команды для таблиц; CONNECT не поддерживает многотабличные команды, команды, отправленные из процедуры, или выпущенные через триггер. Эти команды просто перефразируются, чтобы соответствовать синтаксису источника данных, и отправляются в источник данных для выполнения. Предположим, что мы создали таблицу:

create table tolite (
  id int(9) not null,
  nom varchar(12) not null,
  nais date default null,
  rem varchar(32) default null)
ENGINE=CONNECT TABLE_TYPE=ODBC tabname='lite'
CONNECTION='DSN=SQLite3 Datasource;Database=test.sqlite3'
CHARSET=utf8 DATA_CHARSET=utf8;

Мы можем заполнить её, используя:

insert into tolite values(1,'Toto',now(),'First'),
(2,'Foo','2012-07-14','Second'),(4,'Machin','1968-05-30','Third');

Функция now() будет выполнена MariaDB, и возвращённое значение будет отправлено в таблицу ODBC.

Давайте посмотрим, что произойдёт при обновлении таблицы. Если мы используем запрос:

update tolite set nom = 'Gillespie' where id = 10;

CONNECT перефразирует команду как:

update lite set nom = 'Gillespie' where id = 10;

Всё, что было сделано, это замена имени локальной таблицы на имя удалённой таблицы и изменение всех обратных кавычек на пробелы или на символы кавычек для идентификаторов источника данных, если указано QUOTED. Затем эта команда будет отправлена источнику данных для выполнения им.

Это проще и может быть быстрее, чем выполнение позиционного обновления с использованием курсора и команд, таких как «select ... for update of ...», которые не поддерживаются всеми источниками данных. Однако есть некоторые ограничения, которые необходимо понимать из-за того, как это обрабатывается MariaDB.

1. MariaDB ничего не знает о вышеперечисленном. Команда будет обработана так, как будто она должна выполняться локально. Поэтому она должна соответствовать синтаксису MariaDB.
2. Так как выполнение происходит на стороне источника данных, перефразированная команда должна соответствовать синтаксису этого источника данных.
3. Все данные, на которые ссылается оператор SET и WHERE, принадлежат источнику данных.

Это возможно, потому что как MariaDB, так и источник данных используют язык SQL. Но вы должны использовать только базовые функции, которые являются частью основного языка SQL. Например, ключевые слова, такие как IGNORE или LOW_PRIORITY, приведут к синтаксической ошибке во многих источниках данных.

Имена скалярных функций также могут быть разными, что сильно ограничивает их использование. Например:

update tolite set nais = now() where id = 2;

Это не сработает с SQLite3, источник данных вернёт сообщение об ошибке «неизвестная скалярная функция». Обратите внимание, что в этом конкретном случае вы можете перефразировать его следующим образом:

update tolite set nais = date('now') where id = 2;

Это понимается обоими анализаторами, и даже если эта функция вернёт NULL при выполнении в MariaDB, она вернёт текущую дату при выполнении в SQLite3. Но это начинает становиться слишком сложным, поэтому чтобы преодолеть все эти ограничения и позволить выполнять все типы команд в источнике данных, CONNECT предоставляет особый подтип таблиц ODBC, о котором будет сказано далее.

Отправка команд в источник данных

Это можно сделать, используя специальный подтип таблиц ODBC. Давайте рассмотрим это на примере:

create table crlite (
  command varchar(128) not null,
  number int(5) not null flag=1,
  message varchar(255) flag=2)
engine=connect table_type=odbc
connection='Driver=SQLite3 ODBC Driver;Database=test.sqlite3;NoWCHAR=yes'
option_list='Execsrc=1';

Ключевыми моментами в этом операторе CREATE являются опция EXECSRC и определение столбцов.

Опция EXECSRC указывает, что эта таблица будет использоваться для отправки команды в источник данных. Большинство отправленных команд не возвращают набор результатов. Поэтому столбцы таблицы используются для указания команды для выполнения и получения результата выполнения. Название этих столбцов можно выбрать произвольно, их функция определяется значением флага:

Как использовать эту таблицу и указать команду для отправки? Выполнив команду, такую как:

select * from crlite where command = 'a command';

Это отправит команду, указанную в предложении WHERE, в источник данных и вернёт результат её выполнения. Синтаксис предложения WHERE должен быть точно таким же, как показано выше. Например:

select * from crlite where command =
'CREATE TABLE lite (
ID integer primary key autoincrement,
name char(12) not null,
birth date,
rem varchar(32))';

Эта команда возвращает:

Теперь мы можем создать стандартную таблицу ODBC на новой созданной таблице:

CREATE TABLE tlite
ENGINE=CONNECT TABLE_TYPE=ODBC tabname='lite'
CONNECTION='Driver=SQLite3 ODBC Driver;Database=test.sqlite3;NoWCHAR=yes'
CHARSET=utf8 DATA_CHARSET=utf8;

Мы можем заполнить её напрямую, используя поддерживаемую команду INSERT:

insert into tlite(name,birth) values('Toto','2005-06-12');
insert into tlite(name,birth,rem) values('Foo',NULL,'No ID');
insert into tlite(name,birth) values('Truc','1998-10-27');
insert into tlite(name,birth,rem) values('John','1968-05-30','Last');

И увидеть результат:

select * from tlite;

Любая команда, например UPDATE, может быть выполнена из таблицы crlite:

select * from crlite where command =
'update lite set birth = ''2012-07-14'' where ID = 2';

Эта команда возвращает:

Давайте проверим это:

select * from tlite where ID = 2;

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

create procedure send_cmd(cmd varchar(255))
MODIFIES SQL DATA
select * from crlite where command = cmd;

Теперь вы можете отправлять команды, такие как:

call send_cmd('drop tlite');

Это возможно только при отправке одной единственной команды.

Отправка нескольких команд вместе

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

select * from crlite where command in (
  'update lite set birth = ''2012-07-14'' where ID = 2',
  'update lite set birth = ''2009-08-10'' where ID = 3');

Когда отправляются несколько команд, выполнение прекращается в конце команд или после команды, содержащей ошибку. Чтобы продолжить после n ошибок, установите опцию maxerr=n (по умолчанию 0) в списке опций.

Примечание 1: Можно указать опцию SRCDEF при создании таблицы EXECSRC. Это будет команда, отправляемая по умолчанию, когда предложение WHERE не указано.

Примечание 2: Большинство источников данных не позволяют отправлять несколько команд, разделённых точкой с запятой.

Примечание 3: Кавычки внутри команд должны быть экранированы. Это можно избежать, используя другой символ кавычек, чем тот, который используется в команде.

Примечание 4: Отправленная команда должна подчиняться синтаксису источника данных.

Примечание 5: Отправленные команды применяются в указанной базе данных. Однако они могут обращаться к любой таблице в этой базе данных или к другой базе данных, используя синтаксис имён схема.имя_таблицы.

Подключение к источнику данных

Существует два способа установить соединение с источником данных:

1. Использование SQLDriverConnect и строки подключения
2. Использование SQLConnect и имени источника данных (DSN)

Первый способ использует строку подключения, компоненты которой описывают то, что необходимо для установления соединения. Это наиболее полный способ сделать это, и по умолчанию CONNECT его использует.

Второй способ — упрощенный, в котором ODBC получает только имя DSN, которое должно быть определено для ODBC или UnixOdbc и содержит необходимую информацию для установления соединения. Из спецификации DSN могут быть указаны только имя пользователя и пароль.

Определение строки подключения

Используя первый способ, необходимо указать строку подключения. Иногда это самая сложная задача при создании таблиц ODBC, так как в зависимости от операционной системы и источника данных эта строка может значительно отличаться.

Формат строки подключения ODBC:

connection-string::= empty-string[;] | attribute[;] | attribute; connection-string
empty-string ::=
attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]
attribute-keyword ::= DSN | UID | PWD | driver-defined-attribute-keyword
attribute-value ::= character-string
driver-defined-attribute-keyword = identifier

где строка-символов имеет ноль или более символов; идентификатор имеет один или более символов; атрибут-ключ нечувствителен к регистру; атрибут-значение может быть чувствителен к регистру; и значение ключа DSN не состоит только из пробелов. Из-за грамматики строки подключения следует избегать ключевых слов и значений атрибутов, содержащих символы []{}(),;?*=!@. Значение ключа DSN не может состоять только из пробелов и не должно содержать начальных пробелов. В силу грамматики информации системы ключевые слова и имена источников данных не могут содержать обратную косую черту (\). Приложения не обязаны добавлять фигурные скобки вокруг значения атрибута после ключевого слова DRIVER, если атрибут не содержит точку с запятой (;), в этом случае фигурные скобки необходимы. Если значение атрибута, полученное драйвером, включает фигурные скобки, драйвер не должен удалять их, но они должны быть частью возвращаемой строки подключения.

Определяемые ODBC атрибуты подключения

Определяемые ODBC атрибуты:

• DSN — имя источника данных для подключения. Его необходимо создать перед ссылкой на него. Новые DSN создаются через администратор ODBC (Windows), ODBCAdmin (графический менеджер unixODBC) или в файле odbc.ini.
• DRIVER — имя драйвера для подключения. Его можно использовать в соединениях без DSN.
• FILEDSN — имя файла, содержащего атрибуты подключения.
• UID/PWD — имя пользователя и пароль, необходимые базе данных для аутентификации.
• SAVEFILE — запрос на сохранение атрибутов DSN в указанном файле.

Другие атрибуты зависят от DSN. Строка подключения может содержать имя драйвера в поле DRIVER или имя источника данных в поле DSN (обратите внимание на правописание и регистр), а также другие поля, зависящие от источника данных. При указании файла поле DBQ должно содержать полный путь и имя файла, содержащего таблицу. Обратитесь к документации конкретного драйвера ODBC для точной синтаксической структуры строки подключения.

Использование предопределенного DSN

Это делается путем указания в списке параметров булевого параметра «UseDSN» со значением «да» или «1». Дополнительно в список параметров можно необязательно включить строковые параметры «пользователь» и «пароль».

В этом случае строка подключения содержит только имя предопределенного источника данных. Например:

CREATE TABLE tlite ENGINE=CONNECT TABLE_TYPE=ODBC tabname='lite'
CONNECTION='SQLite3 Datasource' 
OPTION_LIST='UseDSN=Yes,User=me,Password=mypass';

Примечание: имя источника данных подключения (ограничено 32 символами) не должно предваряться «DSN=».

Таблицы ODBC в Linux/Unix

Для использования таблиц ODBC необходимо установить unixODBC. Кроме того, потребуется драйвер ODBC для протокола внешнего сервера. Например, для MS SQL Server или Sybase необходимо установить FreeTDS.

Убедитесь, что пользователь, запускающий mysqld (обычно пользователь mysql), имеет разрешения на конфигурацию источника данных ODBC и драйверы ODBC. Если при использовании TABLE_TYPE=ODBC на Linux/Unix возникает ошибка:

Error Code: 1105 [unixODBC][Driver Manager]Can't open lib
'/usr/cachesys/bin/libcacheodbc.so' : file not found

Убедитесь, что пользователь, запускающий mysqld (обычно «mysql»), имеет достаточные разрешения для загрузки библиотеки драйвера ODBC. Возможно, у файла драйвера недостаточно прав на чтение (используйте chmod для исправления), или загрузка блокируется настройками SELinux (см. ниже).

Попробуйте выполнить эту команду в оболочке, чтобы проверить, достаточно ли у драйвера прав:

sudo -u mysql ldd /usr/cachesys/bin/libcacheodbc.so

SELinux

SELinux может вызывать различные проблемы. Если вы подозреваете, что проблемы связаны с SELinux, проверьте системный журнал (например, /var/log/messages) или журнал аудита (например, /var/log/audit/audit.log).

mysqld не может загрузить некоторый исполняемый код, поэтому не может использовать драйвер ODBC.

Пример ошибки:

Error Code: 1105 [unixODBC][Driver Manager]Can't open lib
'/usr/cachesys/bin/libcacheodbc.so' : file not found

Журнал аудита:

type=AVC msg=audit(1384890085.406:76): avc: denied { execute }
for pid=1433 comm="mysqld"
path="/usr/cachesys/bin/libcacheodbc.so" dev=dm-0 ino=3279212
scontext=unconfined_u:system_r:mysqld_t:s0
tcontext=unconfined_u:object_r:usr_t:s0 tclass=file

mysqld не может открыть TCP-сокеты на некоторых портах, поэтому не может подключиться к внешнему серверу.

Пример ошибки:

ERROR 1296 (HY000): Got error 174 '[unixODBC][FreeTDS][SQL Server]Unable to connect to data source' from CONNECT

Журнал аудита:

type=AVC msg=audit(1423094175.109:433): avc:  denied  { name_connect } for  pid=3193 comm="mysqld" dest=1433 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:mssql_port_t:s0 tclass=tcp_socket

Информация каталога ODBC

В зависимости от версии используемого драйвера ODBC, может быть доступна дополнительная информация о таблицах, например, QUALIFIER или OWNER для старых версий, теперь называемые CATALOG или SCHEMA с версии 3.

CATALOG, по-видимому, редко используется большинством источников данных, но SCHEMA (ранее OWNER) используется и соответствует данным DATABASE MySQL.

Проблема в том, что если имя схемы не указано, некоторые источники данных возвращают информацию обо всех схемах, а другие — только информацию о «по умолчанию». Кроме того, используемая «схема» или «база данных» иногда подразумевается строкой подключения, а иногда нет. Иногда она также может быть включена в определение источника данных.

CONNECT предоставляет два способа указать эту информацию:

1. Если указан параметр DBNAME в создании таблицы, он рассматривается ODBC-таблицами как имя SCHEMA.
2. Имена таблиц могут быть указаны как «cat.sch.tab», позволяя задавать информацию о каталоге и схеме.

Если оба параметра используются, квалифицированное имя таблицы имеет приоритет над DBNAME. Например:

При создании стандартной ODBC-таблицы следует убедиться, что указан только один исходный столбец. Указание более одного исходного столбца допустимо только для CONNECT-каталожных таблиц (с CATFUNC=tables или columns).

В частности, если определение столбцов оставлено функции обнаружения, при наличии таблиц с одинаковым именем в нескольких схемах и имени схемы не указано, будут созданы несколько столбцов с одинаковым именем. Это приведет к ошибке создания с не очень ясным сообщением об ошибке.

Примечание. У некоторых драйверов ODBC параметр DBNAME или квалифицированное имя таблицы бесполезны, поскольку схема, подразумеваемая строкой подключения или определением источника данных, имеет приоритет над указанным DBNAME.

Регистр имени таблицы

Еще одна проблема при работе с ODBC-таблицами — обработка имён таблиц и столбцов с учётом регистра.

Например, Oracle следует SQL-стандарту. Он преобразует необрамлённые идентификаторы в верхний регистр. Это верно и ожидаемо. PostgreSQL не стандартен. Он преобразует идентификаторы в нижний регистр. MySQL/MariaDB не стандартен. Они сохраняют идентификаторы в Linux и преобразуют в нижний регистр в Windows.

Подумайте об этом, если вы не видите таблицу или столбец в источнике данных ODBC.

Наборы символов, отличные от ASCII, с Oracle

При подключении через ODBC сервер MariaDB работает как клиент к внешней системе управления базами данных. В связи с этим необходимо настроить MariaDB так же, как вы настраивали бы родные клиенты для данного сервера базы данных.

В случае подключения к Oracle, при использовании наборов символов, отличных от ASCII, необходимо правильно установить переменную среды NLS_LANG перед запуском сервера MariaDB.

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

CREATE TABLE t1 (letter VARCHAR(4000));

INSERT INTO t1 VALUES
   (UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('C4'))),
   (UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('C5'))),
   (UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('C6')));

SELECT letter, RAWTOHEX(letter) FROM t1;

letter | RAWTOHEX(letter)
-------|-----------------
Ä     | C4
Å     | C5
Æ     | C6

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

CREATE TABLE t1 (
   letter VARCHAR(4000))
ENGINE=CONNECT
DEFAULT CHARSET=utf8mb4
CONNECTION='DSN=YOUR_DSN'
TABLE_TYPE = 'ODBC'
DATA_CHARSET = latin1
TABNAME = 'YOUR_SCHEMA.T1';

SELECT letter, HEX(letter) FROM t1;

+--------+-------------+
| letter | HEX(letter) |
+--------+-------------+
| A      | 	    41 |
| ?      | 	    3F |
| ?      | 	    3F |
+--------+-------------+

Хотя набор символов определён так, что это удовлетворяет MariaDB, он не определён для Oracle (т.е. переменная среды NLS_LANG не задана). В результате Oracle не предоставляет MariaDB и Connect нужные символы. Конкретный метод установки переменной NLS_LANG может отличаться в зависимости от вашей операционной системы или дистрибутива. Если у вас возникла эта проблема, обратитесь к документации вашей ОС для получения более подробной информации о том, как правильно устанавливать переменные среды.

Использование systemd

В дистрибутивах Linux, использующих systemd, необходимо установить переменную среды в файле службы, (systemd не читает файл /etc/environment).

Это делается путем задания переменной среды в блоке [Service] единицы. Например,

# systemctl edit mariadb.service

[Service]
Environment=NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1

Затем перезапустите MariaDB,

# systemctl restart mariadb.service

Теперь вы можете получить соответствующие символы из таблиц Oracle:

SELECT letter, HEX(letter) FROM t1;

+--------+-------------+
| letter | HEX(letter) |
+--------+-------------+
| Ä      | C384        |
| Å      | C385        |
| Æ      | C386        |
+--------+-------------+

Использование Windows

Microsoft Windows не игнорирует переменные среды так, как systemd в Linux, но требует установки переменной среды NLS_LANG в вашей системе. Для этого необходимо открыть командную строку с повышенными правами (т. е. Cmd.exe с правами администратора).

Отсюда можно использовать команду Setx для установки переменной. Например,

Setx NLS_LANG GERMAN_GERMANY.WE8ISO8859P1 /m

Примечание. Более подробную информацию об этом см. на MDEV-17501.

OPTION_LIST Значения, поддерживаемые таблицами ODBC

Следующие параметры можно задать как строку, разделенную запятыми, значению OPTION_LIST в операторе CREATE TABLE.