1C/BAS

Управление базой MySQL из 1С:Предприятие 8.3

Leave a comment

MySQL в качестве системы управления базой данных доминирует в интернет-проектах. Приложение 1С:Предприятие может устанавливать соединения с серверами MySQL для обмена данными в обоих направлениях. Это может использоваться например:

  • для публикации сведений в интернет;
  • для получения сведений  из интернет;
  • для обмена данными с другими приложениями 1С через общее интернет-хранилище.

Серверный доступ

Для установки соединения с сервером MySQL и подключения к одной из DB такая возможность должна быть предусмотрена целым комплексом серверных настроек, которые осуществляет администратор сервера, и здесь они не будут рассматриваться. Тем не менее, в ряде случаев доступ к каждой отдельной DB должен быть настроен администратором хостинга соответствующими средствами.

DirectAdmin Web Control Panel

При использовании для управления хостингом DirectAdmin Web Control Panel следует выполнить следующую последовательность действий:

  • Открыть и авторизоваться в DirectAdmin Web Control Panel
    • В списке Domain выбрать необходимый домен
      • В разделе Your Account выбрать MySQL Management
        • В колонке Database выбрать нужную DB
          • В разделе Access Hosts просмотреть все разрешенные источники доступа
            • localhost - открывает доступ к DB на самом сервере
            • % - открывает доступ к DB для любых внешних подключений
            • некоторый IP открывает доступ к DB из определенного ip-адреса
          • Если доступ % (или иное) не разрешен, то его следует добавить с помощью ввода и кнопки Add Host

Драйвер клиентского подключения

Для возможности установки соединения с сервером MySQL из приложения 1С:Предприятие в серверной системе 1С (в клиентской по отношению к серверу MySQL) должен быть установлен соответствующий ODBC драйвер. Актуальный драйвер ODBC Driver for MySQL (Connector/ODBC) можно загрузить в разделе коннекторов официального сайта //www.mysql.com/products/connector/.

Проверить наличие драйвера в системе можно в Панель управления -> Источники данных ODBC -> Администратор источника данных ODBC, драйвер должен присутствовать в закладке Драйверы в списке установленных драйверов, в списке будет отражено имя и версия. Окно администратора источника данных ODBC можно вызвать командами:

  • x32 версия: %windir%\syswow64\odbcad32.exe
  • x64 версия: %windir%\system32\odbcad32.exe

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

Точное имя установленного драйвера будет необходимо для установки соединения в программном модуле 1С.

Соединение с сервером MySQL

Все операции с сервером MySQL в программе необходимо выполнять через установленное соединение, которое реализуется универсальным COM-объектом типа Connection в его методе .Open(<строка подключения>, [<логин>], [<пароль>], [<опции>]) использующем специально подготовленную строку подключения.

  • строка подключения - образуется из нескольких обязательных и необязательных частей определяющих установление и параметры работы соединения с сервером.
    • driver={<имя драйвера>} - имя установленного в системе драйвера (см.выше)
    • server=<домен/ip сервера>; - адрес хоста сервера, по которому будет установлено сетевое подключение
    • port=<порт>; - (необязательный) порт хоста сервера для подключения
    • uid=<логин>; - логин подключения, необязателен, если логин будет указан в специальном параметре метода .Open
    • pwd=<пароль>; - пароль логина, необязателен, если будет указан в специальном параметре метода .Open
    • database=<БД по умолчанию>; - (необязательный) устанавливает БД по умолчанию, эквивалентен лидирующему выражению "use <db-name>;"
    • MULTI_STATEMENTS=1; - опция, позволяющая выполнять запросы из нескольких выражений
    • MULTI_RESULTS=1; - опция, позволяющая получить несколько результирующих наборов данных из запроса с несколькими выражениями
    • STMT=SET CHARACTER SET utf8 - назначает кодировку символов UTF8
  • логин - логин подключения, необязателен, если логин был указан в строке подключения
  • пароль - пароль логина, необязателен, если пароль был указан в строке подключения
  • опции - (необязательный)

Пример установки соединения:

(https://dev.mysql.com/doc/dev/mysql-server/latest/namespacemysql__protocol_1_1Capabilities.html)

При успешной установке соединения свойство .State устанавливается в 1, а в случае ошибки остается в состоянии 0. Коллекция ошибок типа Errors доступна в объекте соединения, однако в нее попадают ошибки только уровня сервера, которые возникают после установки соединения с сервером. Если ошибка возникла до установки соединения по сетевой или иной причине, ошибка в коллекцию .Errors не попадет.

Для завершения работы с установленным соединением его желательно закрыть:

Выполнение выражений запросов

После успешного установления соединения сервером MySQL можно управлять посредством sql-выражений в пределах прав установленных для пользователя, логин которого был использован при подключении. Существует два способа выполнения sql-выражений: непосредственно через объект соединения; через дополнительный объект команды.

Запросы Соединения

Успешно открытый объект соединения типа Connection может передать на сервер MySQL sql-выражение для выполнения используя метод .Execute(<выражение>[, <обработано>[, <опции>]]), где:

  • выражение - строковое sql-выражение запроса или команды;
  • обработано - числовое, возвращаемое, количество записей обработанных выражением;
  • опции
    • ExecuteOptionEnum
      • adAsyncExecute 0x10/16 Указывает, что будет выполняться асинхронно.
        - Это значение не может объединяться с CommandTypeEnum значение adCmdTableDirect.
      • adAsyncFetch 0x20/32 Указывает, что оставшиеся строки после начальной количество указанных в CacheSize асинхронно нужно извлечь свойство.
      • adAsyncFetchNonBlocking 0x40/64 Указывает, что основной поток не блокируется при извлечении. Если Запрошенная строка не были получены, текущая строка автоматически перемещается в конец файла.
        - При открытии записей из Stream содержащий постоянно хранимых записей, adAsyncFetchNonBlocking не будет эффекта. Данная операция будет синхронной и блокировки.
        - adAsynchFetchNonBlocking не действует, если adCmdTableDirect параметр используется для открытия записей.
      • adExecuteNoRecords 0x80/128 Указывает, что текст команды команду или хранимую процедуру, которая не возвращает строки (например, команды, только вставляет данные). - Если извлекаются все строки, они удаляются и не возвращается.
        - adExecuteNoRecords могут передаваться только как необязательный параметр для команда или выполнить соединение метод.
      • adExecuteStream 0x400/ Указывает, что результаты выполнения команды должно возвращаться в виде потока.
        - adExecuteStream могут передаваться только как необязательный параметр для Command Execute метод.
        - adExecuteRecord Указывает, что CommandText команду или хранимую процедуру, которая возвращает одну строку, в которой будут возвращены в качестве записи объекта.
    • CommandTypeEnum
      • adCmdText (1) Результатом является CommandText как текстовое определение команда или хранимая процедура вызывать.
      • adCmdTable (2) Результатом является CommandText как на имя таблицы, столбцы которых возвращаются, созданного внутреннего запроса SQL.
      • adCmdStoredProc (4) Результатом является CommandText как имя хранимой процедуры.
      • adCmdUnknown (8) По умолчанию. Указывает, что тип команды в CommandText свойство неизвестно.
        Если тип команды неизвестен, ADO сделает несколько попыток, чтобы интерпретировать CommandText.
        - CommandText интерпретируется как текстовое определение для вызова команды или хранимых процедур. Это аналогично adCmdText.
        - CommandText имя хранимой процедуры. Это аналогично adCmdStoredProc.
        - CommandText интерпретируется как имя таблицы. Созданная внутри структура SQL-запрос возвращает все столбцы. Это аналогично adCmdTable.
      • adCmdFile (256) Результатом является CommandText как имя файла постоянно хранимых записей. Используется с набор записей. Откройте или Requery только.
      • adCmdTableDirect (512) Результатом является CommandText как на имя таблицы, столбцы которого возвращаются все. Используется с Recordset.Open или Requery только. Чтобы использовать Seek метод, записей должен открываться с adCmdTableDirect.
        Это значение не может объединяться с ExecuteOptionEnum значение adAsyncExecute.

Результатом успешного выполнения sql-выражения будет новый COM объект Набор записей типа Recordset. В случае ошибки метод .Open(...) вернет пустое значение Неопределено, а перечисление ошибок будет размещено в свойстве .Errors объекта Соединение.

///

Команды и Запросы

В программном коде можно создать специальный COM объект Команды типа Command и связать его с открытым соединением, иногда это может оказаться удобным. Объект Команды использует свойство .CommandText для sql-выражения, которое передается серверу MySQL для выполнения собственным метод .Execute([<обработано>[, <опции>]]), которое отличается от метода .Execute(...) Соединения только отсутствием параметра выражения.

 

Обработка набора записей

Любой успешный запрос к серверу методами .Execute(...) предполагает, что сервер вернет контейнер объекта  Набор записей типа Recordset, однако следует учитывать следующие обстоятельства:

  • Контейнер набора Recordset возвращается методом Execute() в качестве результата только в случае успешного выполнения выражения и если не была указана опция adExecuteNoRecords.
    • В случае ошибки в выражении или его выполнении вызывается исключение и значение не возвращается (не изменяется или остается Неопределено)
    • В случае применения опции adExecuteNoRecords значение не возвращается (не изменяется или остается Неопределено);
  • Оценить наличие набора записей в контейнере можно через свойство контейнера .State, которое устанавливается в 1 при наличии набора (пусть даже пустого) или в 0 при его отсутствии;
  • При отсутствии набора в контейнере любой метод относящийся к набору вызовет исключение;
  • Методы .MoveFirst() или .MoveLast() применимы только к непустому набору, в пустом наборе они вызывают исключение;
  • В пустом наборе методы .Bof() и .Eof() возвращают Истина одновременно;
  • Структуру полей полученного набора можно получить из коллекции .Fields(<индекс поля>).Name даже в пустом наборе;
  • Значение поля в записи можно получить через коллекцию .Fields(<индекс поля>).Value только если курсор установлен на одной из записей набора, в противном случае вызывается исключение.

Простейший способ просмотра набора записей:

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

Набор записей выражений не возвращающих данные

Большое число sql-выражений не предполагает получение ответных данных с сервера в форме набора записей, в частности выражения модификации данных INSERT, UPDATE и DELETE. Их выполнение передается серверу тем же методом .Execute(), отличие состоит только в том, что у результирующего контейнера типа Recordset свойство .State установлено в 0.

Последовательность Набора записей

Успешное выполнение методом .Execute(...) запроса, содержащего несколько sql-выражение возвратит последовательность контейнеров типа Recordset в том порядке, в каком они следуют в запросе. Для получения следующего контейнера Набора записей типа Recordset используется метод объекта Набора записей .NextRecordset().

  • последовательность содержит контейнеры Набора записей всех выражений запроса, и тех которые вернули данные .State=1, и тех которые данные не возвращают .State=0
  • последовательность контейнеров Набора записей следует в том порядке, в каком соответствующие sql-выражения следуют в запросе
  • метод .Execute(...) возвращает первый Набор записей первого выражения запроса
  • метод .NextRecordset() последнего Набора записей возвратит пустое значение Неопределено.
  • возможность вернуться к предыдущему контейнеру Набора записей не предусмотрена
  • контейнер Набора записей уничтожается немедленно, после того как в программном коде соответствующая переменная освобождается или получает значение другого контейнера.

 

Ошибки при использовании Соединения

Ошибки возникающие при обработке и выполнении выражений запросов доступны в коллекции .Errors объекта соединения, которая содержит элементы типа Error, обладающие свойствами:

  • Description - описание ошибки. Обычно наиболее важная информация содержится именно в описании.
  • Number - номер ошибки. По номеру удобно производить поиск в базе знаний и в Интернет.
  • Source - источник ошибки. Эта информация полезна только в том случае, если в коллекции Errors могут оказаться ошибки из разных источников.
  • SQLState и NativeError - информация о возникшей ошибке, которая пришла из SQL-совместимого источника данных.

Хранимые процедуры

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

Создание процедуры осуществляется командой CREATE PROCEDURE:

Вызов созданной процедуры выполняется командой CALL:

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

Если в состав выражений процедуры входит запрос выборки данных, то результатом вызова процедуры аналогично простому запросу будет набор записей представленный объектом Recordset, который можно обработать уже описанным выше способом, средствами объекта. Если в состав пакета процедуры входит несколько запросов выборки, то наборы записей образуют последовательность, в которой следующий набор записей можно получить методом .NextRecordset(), как это показано на упрощенном примере перебора последовательности объектов Recordset и перебор записей, входящих в каждый набор:

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

Баги и фичи

Двукратное выполнение хранимой процедуры

По неустановленной причине хранимая процедура вызванная средствами ADO будет выполнена дважды, если процедура выполняет хотя бы один возврат записей выражением SELECT. Единственным способом избежать двойное выполнение является отказ от выражения SELECT в процедуре в пользу получения необходимых записей дополнительным серверным вызовом в рамках установленного сеанса соединения (в пределах сеанса временные таблицы остаются доступными для всей последовательности вызовов). В консоли Navicat описанная проблема на проявлялась.

Разрушение данных в поле набора записей

По неустановленной причине в полученном запросом наборе записей при повторном получении значения строкового/текстового поля большого объема там может оказаться значение Null вместо первоначально полученного, как это показано в примере:

///

Прочие замечания

Если установка соединения с сервером MySQL должна выполняться автоматически, то для автоматической авторизации придется где-то хранить реквизиты логина и пароля. Распространенная традиция хранить логин и пароль в реквизитах ИБ рискованна по многим причинам, поэтому будет гораздо безопасней сохранить их в системной или в пользовательской ветке реестра. Технология работы с реестром в приложении 1С:Предприятие описана в публикации Программная работа с реестром Windows.

Источники

Leave a Reply