рубрики: ADO | Дата: 24 марта, 2017
Недавно столкнулся с необходимостью делать из 1С прямые запросы к базе данных Oracle, используя ADODB.Connection. В этой статье хочу поделиться опытом и рассказать про некоторые особенности с которыми пришлось столкнуться.
Первое, что необходимо для использования ADODB.Connection это наличие в системе провайдера (драйвера), который позволяет подключаться к источнику данных. Для баз данных Oracle существует два драйвера.
Один от компании Microsoft — MSDAORA и второй от Oracle — OraOLEDB. Возможно есть и другие, но мне попадались описания только этих двух. Сразу возникает вопрос как можно проверить установлен ли хоть один из этих драйверов на компьютере. Чтобы понять это можно воспользоваться файлом c расширением *.udl. Если открыть этот файл и перейти на первую закладку — Поставщик данных, то мы увидим перечень всех провайдеров. Как видно на картинке у меня установлен провайдер от Microsoft Microsoft OLE DB Provider for Oracle.
Но я при подключении использовала драйвер от Oracle. Просто из тех соображений, что родной драйвер должен работать лучше. При необходимости можно скачать его с сайта Oracle. Выбираем закладку Downloads, в разделе Enterprise Management пункт See All…
Здесь в разделе Drivers нас интересует пакет Oracle Data Access Components for Windows
Дальше разберетесь сами. Нужно только выбрать правильную разрядность. Не надо только качать пакет где в названии есть Xcopy. В нем используется какая-то полуавтоматическая установка через .bat файлы.
В процессе установки можно выбрать только то, что нас интересует Oracle Provider For OLE DB
Также в процессе установки желательно указать параметры для подключения к серверу с оракловыми базами данных. Или это можно сделать позднее, но для этого придется самостоятельно отредактировать файл tnsnames.ora
После установки откроем наш файл *.udl и теперь мы видим, что на закладке Поставщик данных появилась еще одна строчка с провайдером Oracle Provider for OLE DB
Теперь немного порассуждаем на каком компьютере должен быть установлен провайдер. Это может отразиться и на выборе разрядности драйвера, о чем говорилось в предыдущем пункте. Раз уж вы читаете эту статью вряд ли вы используете файловый вариант базы 1С, т.к. базы данных Oracle используются как правило очень крупными организациями. Скорее речь идет о клиент-серверном варианте базы 1С. Таким образом мы имеем два варианта.
Либо устанавливать драйвер на каждой клиентской машине, либо один раз на сервере, где физически установлен сервер 1С:Предприятия. Понятно, что ставить драйвер на каждую клиентскую машину это не вариант, тем более, что у разных пользователей могут быть установлены операционные системы с разной разрядностью. Да и само количество пользовательских станций может быть большим. Поэтому устанавливать драйвер будем на сервере 1С:Предприятия. Напомню, что код на сервере 1С:Предприятия выполняется от имени служебного пользователя USR1CV8. Соответственно у этого пользователя должны быть права на использование провайдера.
Так как драйвер мы установили на сервере, то и установка соединения должна выполняться на сервере, для этого процедуру подключения к оракловой базе разместим в серверном общем модуле ОбщегоНазначенияСервер.
Функция УстановитьСоединение() Экспорт
Сервер = "SRV";
Логин = "Vasya";
Пароль = "123";
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=" + Пароль + ";User ID=" + Логин + ";Data Source=" + Сервер;
//либо для драйвера от Microsoft
//Соединение.ConnectionString = "Provider=MSDAORA.1;Password=" + Пароль + ";User ID=" + Логин + ";Data Source=" + Сервер;
Попытка
Соединение.Open();
Исключение
Соединение = Неопределено;
КонецПопытки;
Возврат Соединение;
КонецФункции
Строки подключения самые примитивные. При необходимости можно добавить нужные параметры.
В процессе отладки даже простейших запросов к базе данных Oracle пришлось неоднократно столкнуться с ошибкой вот такого типа ORA-00933: SQL command not properly ended. По сути она говорит о том, что некорректно написан текст запроса. Поскольку ранее я не сталкивался с языком PL/SQL, то пытался делать запросы по аналогии с Transact-SQL. Но как выяснилось есть много тонкостей, которые отличают один язык от другого.
Рассмотрим примеры выполнения запросов к базе данных оракл и возможные ошибки в синтаксисе.
Пусть у нас есть оракловая база данных по имени TEST, в которой есть таблица SAMPLE вот такого вида
PERIOD | DESCR | PRICE |
---|---|---|
2017-09-01 | Яблоки | 100.00 |
… | … | … |
То есть тип полей в нашей таблице: дата, строка и число. Сделаем простейший запрос, который выберет все из таблицы, а на этапе обхода выборки поместим данные в таблицу значений.
ТаблицаЦен = Новый ТаблицаЗначений;
ТаблицаЦен.Колонки.Добавить("Период");
ТаблицаЦен.Колонки.Добавить("Товар");
ТаблицаЦен.Колонки.Добавить("Цена");
//Устанавливаем соединение с использованием ADODB.Connection
Соединение = ОбщегоНазначенияСервер.УстановитьСоединение();
ТекстЗапроса = "
|SELECT
| , TO_CHAR(PERIOD, ‘YYYYMMDDHH24MISS’) AS ""Date""
| , DESCR AS ""Name""
| , PRICE AS ""Sum""
|FROM TEST.SAMPLE";
Выборка = Соединение.Execute(ТекстЗапроса);
Пока Выборка.EOF = 0 Цикл
НоваяСтрока = ТаблицаЦен.Добавить();
НоваяСтрока.Период = Дата(Выборка.Fields("Date").Value);
НоваяСтрока.Товар = СокрЛП(Выборка.Fields("Name").Value);
НоваяСтрока.Цена = Выборка.Fields("Sum").Value;
КонецЦикла;
//Не забываем разрывать соединение
Соединение.Close();
Теперь хочу сделать несколько пояснений.
| , TO_CHAR(PERIOD, ‘YYYYMMDDHH24MISS’) AS ""Date""
мы преобразуем значение с типом Дата в строку вида «20170901000000».
ТекстЗапроса = "
|SELECT
| , TO_CHAR(PERIOD, 'YYYYMMDDHH24MISS') AS ""Date""
| , DESCR AS ""Name""
| , PRICE AS ""Sum""
|FROM TEST.SAMPLE
|FETCH FIRST 1 ROWS ONLY";
Теперь проделаем обратную операцию. Вставим записи из таблицы значений в таблицу базы данных Oracle.
Для каждого СтрокаТаблицы Из ТаблицаЦен Цикл
ТекстЗапроса = "
|begin
|INSERT INTO TEST.SAMPLE
| (PERIOD
| , DESCR
| , PRICE)
|VALUES
| TO_DATE('" + Формат(СтрокаТаблицы.Период, "ДФ=yyyyMMddHHmmss") + "', 'YYYYMMDDHH24MISS')
| , '" + СтрокаТаблицы.Товар + "'
| , " + СтрокаТаблицы.Цена + ");
|end;";
Соединение.Execute(ТекстЗапроса);
КонецЦикла;
И опять несколько замечаний
"| TO_DATE('" + Формат(СтрокаТаблицы.Период, "ДФ=yyyyMMddHHmmss") + "', 'YYYYMMDDHH24MISS')"
мы с помощью языка PL/SQL преобразуем строковое значение даты, например вот такое «20170901235959» в значение с типом Дата.
|begin
|...................................
| , " + СтрокаТаблицы.Цена + ");
|end;";
Без заключения текста запроса внутрь блока «begin……end;», запрос выполнялся с ошибкой, причем это только при вставке в таблицу. При выборке (как в предыдущем примере) этот блок не нужен. Обратите внимание, что после end стоит точка с запятой. Также точка с запятой стоит в конце предпоследней строки запроса
| , " + СтрокаТаблицы.Цена + ");
Спасибо, очень полезно!
И ещё, вроде бы, в INSERT INTO запросе вылетит ошибка, т.к. отсутствует открывающая скобка для VALUES (очепятка) %)
Очень серьезный момент упустили:
Пока Выборка.EOF = 0 Цикл
……………
Выборка.MoveNext();
КонецЦикла;
Пока Выборка.EOF = 0 Цикл
Выборка.MoveNext();
КонецЦикла;