Все об 1С SQL версии

Статьи

Подключение к базе данных 1С и выполнение SQL запросов на примере работы со справочниками. Часть 1.

Приступим к более интересным вещам, чем простое созерцание созданных 1С таблиц. А именно попробуем поработать со справочниками, используя прямой доступ к базе. Безусловно сначало этот доступ надо получить.

Рассмотрим два наиболее распространенных способа:

доступ с использованием библиотеки "Rainbow";
доступ с использованием технологии ADO.

Преимуществам и недостаткам этих способов будет уделено внимание в дальнейшем, а сейчас в принципе...

Доступ с использованием библиотеки "Rainbow".

Библиотека распространяется свободно и название красивое, но в данный момент не поддерживается, что грустно. Возможности у Радуги большие, для нашей задачи даже избыточные. Приведем цитату из краткого описания библиотеки: "Программный продукт RAINBOW ADDIN 2000 является внешней компонентой для системы 1С-предприятие версии 7.7.9. Программный продукт представляет доступ к таким возможностям системы, как создание динамических классов, получение дополнительной информации об объектах метаданных 1С, исполнение SQL-запросов в соответствующей версии платформы и библиотеку математических функций". Мы будем использовать Rainbow для следующих целей:

получение дополнительной информации об объектах метаданных 1С (объект MetaDataWork);
получение дополнительной информации об объектах 1С (объект RainbowService);
исполнение SQL-запросов в соответствующей версии платформы (объект ODBCQuery).

Небольшое замечание: во всех примерах предполагается, что библиотека (файл Rainbow.dll) у Вас есть и размещена в кталоге 1С. Т.к. загрузка внешней компоненты процесс не самый быстрый, а, войдя во вкус, Вы будете использовать ее все чаще, стоит создать глобальные переменные

Перем objRainbowService;
Перем objODBCQuery;
Перем objMetaDataWork;

и инициализировать их в контексте глобального модуля:

Попытка
  ЗагрузитьВнешнююКомпоненту("rainbow.dll");
  objRainbowService = CreateObject("RainbowService");
  objODBCQuery      = CreateObject("ODBCQuery");
  objMetaDataWork   = CreateObject("MetaDataWork");
Исключение
  // Если Вы увидели нижеследующее сообщение, 
  //не стоит пытаться выполнять остальные примеры.
  Сообщить("Невозможно загрузить Rainbow!", "!");
КонецПропытки;

Для того чтобы сделать что-нибудь полезное (например удалить все записи :-) со справочником Контрагенты, надо сначал узнать в какой таблице SQL 1С его хранит. То, что имя таблицы начинается с "SC" мы уже знаем, а числовой постфикс это идентификатор мета-объекта "Справочник.Контрагенты". Следующая функция позволяет получить его, а также идентификатор любого другого мета-объекта:

Функция глПолучитьМетаIDОбъекта(метаОбъект) Экспорт
   IDОбъекта = Число(objMetaDataWork.GetMetaDataID(метаОбъект));
   Возврат Строка(IDОбъекта);
КонецФункции

Параметры функции:
метаОбъект - собственной персоной. Для тех, кто не знает (к Вам лично это, разумеется, не относится) привожу строчку кода:

метаОбъект = Метаданные.Справочник("Контрагенты");
// не останавливаемся на достигнутом и получаем имя SQL таблицы, 
//содержащей справочник контрагентов
ИмяТаблицы = "SC" + глПолучитьМетаIDОбъекта(метаОбъект);

Однако редко требуется выполнять действия над всеми записями таблицы, значит нам надо уметь получать идентификатор агрегатного объекта 1С, что и делает нижеследующая функция.

Функция глПолучитьIDОбъекта(Объект, ИспользоватьСкобки = 1) Экспорт
Перем Результат, ID, стрСкобка;
   Если Объект = "" Тогда
      Результат = "Null";
   Иначе
      Результат = "";
      Если ПустоеЗначение(Объект) > 0 Тогда
         ID = "     0   ";
      Иначе
         //Функция ValueToDBString возвращает строку,
         //используемую для хранения ссылки на агрегатный элемент в БД.
         ID = objRainbowService.ValueToDBString(Объект);
      КонецЕсли;
      стрСкобка = ?(ИспользоватьСкобки = 1, "'", "");
      Результат = стрСкобка + ID + стрСкобка;
   КонецЕсли;
   Возврат Результат;
КонецФункции

Функция принимает два параметра:
Объект - агрегатный объект чей идентификатор нас интересует
ИспользоватьСкобки - если равен 1, то слева и справа к идентификатору добавляется одинарная кавычка, для того чтобы полученный идентификатор можно сразу было использовать в SQL запросе.
Возвращается строка - идентификатор объекта в тридцатишестиричной системе счисления.

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

Функция глВыполнитьЗапрос(MyObjODBCQuery = "",ТекстЗапроса, 
   НазваниеЗапроса = "", ЗакрытьОбъект = 0) Экспорт
   Попытка
      Если НазваниеЗапроса <> "" Тогда
         Состояние("Подготовка SQL запроса '" + НазваниеЗапроса + "'");
      КонецЕсли;
      Если ПустоеЗначение(MyObjODBCQuery) = 1 Тогда
         MyObjODBCQuery = CreateObject("ODBCQuery");
      КонецЕсли;
      //Reset - Сбрасывает состояние объекта.
      MyObjODBCQuery.Reset();
      //Prepare - Подготавливает SQL-выражение к исполнению.
      MyObjODBCQuery.Prepare(ТекстЗапроса, 0, 0);
   Исключение
      Сообщить(ОписаниеОшибки(),"!!!");
      Сообщить(">> Не удается подготовить SQL запрос: " + НазваниеЗапроса,"I");
      Возврат 0;
   КонецПопытки;
   
   Попытка
      Если НазваниеЗапроса <> "" Тогда
         Состояние("Выполнение SQL запроса '" + НазваниеЗапроса + "'");
      КонецЕсли;
      //Open - Исполняет команду. Возвращает 1, если успешно.
      Если MyObjODBCQuery.Open() = 0 Тогда
         Сообщить("Ошибка выполнения SQL запроса: " + НазваниеЗапроса);
         Возврат 0;
      КонецЕсли;
   Исключение
      Сообщить(ОписаниеОшибки(), "!");
      Сообщить(">> Не удается выполнить SQL запрос: " + НазваниеЗапроса, "I");
      Возврат 0;
   КонецПопытки;

   Если ЗакрытьОбъект = 1 Тогда
      MyObjODBCQuery.Close();
   КонецЕсли;

   Если НазваниеЗапроса <> "" Тогда
      Состояние("...");
   КонецЕсли;

   Возврат 1;
КонецФункции

Параметры функции вполне понятны (если нет, то читайте исходник) пояснения требует только первый параметр - MyObjODBCQuery. Если вам надо выполнить SQL запрос, который возращает результат, то он должен содержать объект типа ODBCQuery, в противном случае его можно оставить пустым. Так Вы говорите удалить все записи...

ВНИМАНИЕ ОПАСНО!!! БЕЗ СТРАХОВКИ НЕ ПОВТОРЯТЬ!!!

метаОбъект = Метаданные.Справочник("Контрагенты");
ИмяТаблицы = "SC" + глПолучитьМетаIDОбъекта(метаОбъект);
ТекстЗапроса = "TRUNCATE TABLE " + ИмяТаблицы;
НазваниеЗапроса = "Начинаем новую жизнь!";
глВыполнитьЗапрос(,ТекстЗапроса, НазваниеЗапроса, 1);

Обратите внимание, как быстро! Надеюсь у Вас была резервная копия.

Попробуем что-нибудь более полезное. Предположим надо сменить размер предоставляемого кредита всем контрагентам, в определенной группе справочника.

Процедура СменитьКредитГруппеКонтрагентов(ВыбраннаяГруппаКонтрагентов, НовыйКредит)
   метаОбъект = Метаданные.Справочник("Контрагенты");
   ИмяТаблицы = "SC" + глПолучитьМетаIDОбъекта(метаОбъект);
   метаОбъект = Метаданные.Справочник("Контрагенты").Реквизит("Кредит");
   ИмяКолонкиКредит = "SP" + глПолучитьМетаIDОбъекта(метаОбъект);
   IDГруппы = глПолучитьIDОбъекта(Объект);
   НазваниеЗапроса = "Пусть радуются!";
   ТекстЗапроса = "UPDATE " + ИмяТаблицы + " SET " + ИмяКолонкиКредит + " = " + 
      НовыйКредит + " WHERE ParentID = " + IDГруппы;
   Если глВыполнитьЗапрос(,ТекстЗапроса, НазваниеЗапроса, 1) = 0 Тогда
      Сообщить("Ошибка при выполнении SQL запроса в функции СменитьКредитГруппеКонтрагентов","!");
   КонецЕсли;
КонецПроцедуры

Вот и все на сегодня.