Профессия — 1С » Запросы к базе Oracle через ADODB.Connection

Профессия — 1С

Рукопашный бой Карташ

Категории

-->

Запросы к базе Oracle через ADODB.Connection

рубрики: ADO | Дата: 24 марта, 2017

Недавно столкнулся с необходимостью делать из прямые запросы к базе данных 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

В процессе отладки даже простейших запросов к базе данных 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();

Теперь хочу сделать несколько пояснений.

  • Если кто-то заметил, то псевдонимы полей в тексте запроса взяты в кавычки. Это особенность языка PL/SQL. Ни в языке запросов 1С ни в Transact-SQL эти кавычки не нужны.Поэтому, если будете использовать псевдонимы, помните про этот момент, иначе запрос вывалится с ошибкой.
  • В строке запроса

    
    
    
    |	, TO_CHAR(PERIOD, ‘YYYYMMDDHH24MISS’) AS ""Date""
    

    мы преобразуем значение с типом Дата в строку вида «20170901000000».

  • А теперь предположим, что нам нужно выбрать только первую строку результата запроса. В Transact-SQL это решается так «SELECT TOP 1«. Но для PL/SQL это не работает. Чтобы реализовать это, добавим в конец текста запроса еще одну строчку:

    
    
    
    ТекстЗапроса = "
    |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 стоит точка с запятой. Также точка с запятой стоит в конце предпоследней строки запроса

    
    
    |	, " + СтрокаТаблицы.Цена + ");
    
  • 3 комментария на «“Запросы к базе Oracle через ADODB.Connection”»

    1. Ненавижу1С:

      Спасибо, очень полезно!
      И ещё, вроде бы, в INSERT INTO запросе вылетит ошибка, т.к. отсутствует открывающая скобка для VALUES (очепятка) %)

    2. Роман:

      Очень серьезный момент упустили:
      Пока Выборка.EOF = 0 Цикл
      ……………

      Выборка.MoveNext();

      КонецЦикла;

    3. Роман:

      Пока Выборка.EOF = 0 Цикл

      Выборка.MoveNext();
      КонецЦикла;

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *

       

2014 - 2024г. Профессия — 1С. Обмен опытом по программированию в 1С