1С прямой запрос в sql

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

Подключение к SQL в общем то широко описано:
Код 1C v 8.х

Сервер и БД можно получить и программно, но муторно. Эти данные можно взять из свойств БД на сервере 1С.

Название таблицы и полей в SQL можно получить с помощью команды 1С:
Цитата

Дальше нам нужно новый элемент справочника все ж таки записать, ну например :
Код 1C v 8.х

Автор статьи — Ситников Анатолий.

Данная статья предполагает наличие базовых знаний по TSQL у читателя.

Глава 1: Мой первый запрос

Для начала нужно научиться выполнять элементарные запросы к базе 1С непосредственно из самой программы.

Рассмотрим такой пример: выбрать Код, Наименование из справочника “Номенклатура”.

Как известно, имена таблиц и полей не совпадают с теми идентификаторами, которые мы задаем в конфигураторе (соответствия можно посмотреть в файле 1Cv7.DDS):

Запрос на TSQL будет выглядеть следующим образом:

Его можно запустить в QA и увидеть полученный результат.

Теперь попробуем получить результат из 1С. Для этого в 1С++ есть встроенный тип “ODBCRecordset”. Полный перечень методов и свойств можно увидеть в документации и в синтаксис-помощнике. Пока остановимся на методе ВыполнитьИнструкцию(Текст, ТЗ = "", ОчищатьТЗ = ""), который возвращает результат работы запроса, переданного в параметре Текст в таблицу значений.

Вот и выполнился наш первый запрос. Это конечно хорошо, но справочников в конфигурации много, смотреть каждый раз в DDS ой как не хочется, да и читать такие запросы совсем неудобно. Если использовать ADO, а не 1С++, то так и придется делать. Но скажем спасибо разработчикам 1С++, что в ней есть метапарсер имен, который сам переведет все идентификаторы объектов в их реальные имена. А делается это так:

Заменяются следующие имена:

• $Справочник.ХХХ – справочник
• $Документ.ХХХ – документ
• $ДокументСтроки.ХХХ – табличная часть документа
• а также регистры, журналы расчетов (об этом поговорим позднее)

Немножко усложним пример. Выберем дополнительно реквизит “ТипНоменклатуры”:

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

Как вы уже заметили, мы не стали искать соответствий имен в DDS, а переложили эту задачу на метапарсер. Для того чтобы парсер понял, что это реквизит, который нужно перевести на язык SQL, ставим знак $ перед именем таблицы: $Спр.ТипНоменклатуры.

Правда, не все имена метапарсер преобразовывает в поля таблиц. Есть исключения – это предопределенные имена.

ИД (Ссылка) — > Для документов:

ИД (Ссылка) — > Пример: выберем непомеченные элементы справочника “Номенклатура”, которые не являются группами.

Для поля IsMark: 1 – Помечен на удаление, 0 – Нет.

Для IsFolder: 2 – Элемент, 1 – Группа. Это сделано для того, чтобы упорядочивание по этому полю сначала выдавало группы, а затем элементы.

Все конечно работает, но вместо типа номенклатуры получаются какие-то буковки вида ‘ C3A ‘. Это внутренние идентификаторы объектов 1С, как они хранятся в базе. Существует несколько способов получения объектов по их внутренним идам, например с помощью функции ЗначениеВСтрокуВнутр(). Но у нас в руках такая мощная вещь – она практически все умеет делать сама:

В результате мы получим ТЗ, в которой будет 3 колонки: Код, Наименование и ТипНоменклатуры. В последней колонке уже будут знакомые нам названия: Товар, Услуга и др.

Общий принцип таков: имя колонки пишется в квадратных скобочках [] и состоит из 2х частей: собственно наименования и типа значения, разделенных пробелом. Типы бывают следующие:

• $Справочник – справочник неопределенного вида
• $Справочник.ХХХ – конкретный справочник
• $Документ – документ неопределенного вида
• $Документ.ХХХ – конкретный документ
• $Перечисление.ХХХ
• $Счет.ХХХ – счет, где ХХХ – имя плана счетов
• $Субконто – специальный тип для бухгалтерской подсистемы
• $Неопределенный
• также есть типы $Число, $Строка, $Дата – но их можно не указывать

Замечание: приводить нужно не к тому типу, который мы хотим получить, а к тому, который задан в конфигураторе.

Глава 2: Условия в запросах

Основным преимуществом прямых запросов является то, что мы не будем выбирать ненужные нам данные и их отбор предоставим MSSQL, который делает это очень хорошо и главное на сервере, в отличие от 1С.

Как всегда рассмотрим примерчик: выбрать все элементы спр. Номенклатура, у которых реквизит ТипНоменклатуры = Перечисление.ТипыНоменклатуры.Товар.

Что бы посмотреть, какой же запрос в итоге уйдет на сервер, у объекта ODBCRecordset существует метод Отладка(Вкл).

В окно сообщений выведется текст:

Такие запросы удобно отлаживать в QA. Выделяем, копируем (не забывая перед этим включить русскую раскладку) и выполняем. В отличие от 1С++, QA показывает номер строки с ошибкой, к которой можно перейти двойным кликом.

Приведение параметров к типу колонки

Пример: документ реализация, в котором Реквизит контрагент задан как тип справочник: Контрагенты или Сотрудники. Тогда значения этого реквизита в базе будут выглядеть примерно так: ‘ 1В C3A ‘. Естественно, если мы установим параметр ‘ C3A ‘, то выборка получится пустой. Для этого существует понятие модификаторов.

Существует 2 вида модификаторов:

• Справочник.ХХХ > Справочник, аналогично для документов
• Какой-то тип > Неопределенный

Первый записывается так :ИмяПараметра

, второй :ИмяПараметра* или :ИмяПараметра

Добьем наш пример:

Отбор по пустому значению

Для этой цели существуют специальные переменные $ПустойИД = ‘ 0 ‘ и $ПустойИД13 = ‘ 0 0 ‘. В запросе это выглядит следующим образом.

Пример: пусть в документе реализация есть реквизит “Склад” типа “Справочник.Склады”. Выберем все документы, у которых пустой склад или контрагент.

Отбор по списку или группе

Для таких случаев у объекта ODBCRecordset есть методы УложитьСписокОбъектов(Список, Таблица, ВидСправочника) и УложитьСписокОбъектов13(Список, Таблица).

Список – это список или группа. После выполнения этого метода появляется таблица с именем Таблица и колонками Val, IsFolder.

Пример: выберем все документы реализации, у которых склад входит в выбранную группу.

Замечание: рекомендуется использовать локальные временные таблицы – префикс #. В этом случае не нужно заботиться об уникальности имени таблицы для разных пользователей, а также об их последующем удалении.

Тот же пример, но по списку контрагентов:

Обычно при написании отчетов нужно делать переменные условия: по всем, по элементу, по группе или по списку. Отрабатывать все варианты в каждом отчете – проще застрелиться. И для того, чтобы так не делать, мною был разработан класс-обертка "ЗапросSQL" (можно найти на acsent.nm.ru). Суть это класса состоит в макроподстановках.

В зависимости от значения ВыбСклад в запрос встанет условие:

• 0 = 0 (Пустое значение, по всем)
• $Док.Склад = :Склад (Выбран элемент)
• $Док.Склад IN (SELECT Val FROM #__Склад) (Выбрана группа или список)

Глава 3: Работа с документами

Все документы 1С хранятся в таблице _1SJourn. Конечно, это не очень хорошо, особенно когда блокируется вся таблица при проведении 1 документа, но мы не можем изменять структуры БД, поэтому будем довольствоваться тем, что есть. Структура таблицы:

> Пример: получить документы “Реализация” за период с НачДата по КонДата.

Здесь стоит обратить внимание на 2 вещи:

1. Для типизации документа по полю IDDoc необходимо, чтобы в выборке присутствовало поле, содержащее IDDocDef с именем _вид, в нашем случае это будет Док_вид. Для полей, которые содержат реквизит типа “Документ”, вспомогательного поля не требуется.
2. Модификатор параметра КонДата. Он необходим для того, чтобы в выборку попали документы за последний день. Поле Date_Time_ > Посмотрим, как в запросе преобразовать поле Date_Time_IDDoc в дату документа.

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

Дополним пример выбором контрагента.

В этом примере можно было и не делать отбор по виду документа, т.к. соединение с таблицей документов “Реализация” автоматически выполняет эту задачу, но для попадания в индекс условие все-таки осталось.

Более сложный пример: пусть наш документ “Реализация” имеет табличную часть с колонками Товар – “Справочник.Номенклатура” и Количество.

Выберем все товары с количеством из всех проведенных документов за период по выбранному складу.

Совет: накладывайте условия на соединяемые таблицы в месте их присоединения. Это повышает читабельность кода, если это конечно не меняет суть запроса (проходит только для INNER JOIN).

Общие реквизиты

Если для общего реквизита стоит отбор, то этот реквизит будет находиться в таблице _1SJourn, иначе в таблице документа. Доступ к этому реквизиту осуществляется через мета-имя $ОбщийРеквизит.ХХХ.

Пример: с отбором.

Без отбора, по 2-м видам документов.

Использование граф отбора

Графы отбора и подчиненные документы лежат в таблице _1SCRDOC. Вид графы — в поле MDID, значение отбора или документа владельца — в поле ParentVal.

Пример: выберем документы по графе отбора Контрагент.

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

Получение представлений в запросе

Это первый принцип оптимизации: получать в запросе всю необходимую (или как можно больше) информацию.

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

Пример: получим Номер, дату и наименование склада в запросе.

Поля Док, Склад пойдут в расшифровку, а остальные поля выведутся на экран.

А что делать, если заранее не известно кокой справочник хранится в поле? В общем случае ничего нельзя сделать. Но если мы заранее можем ограничить тип этого поля, тогда решение можно найти. Например, в нашем случае поле “Контрагент” может быть только Контрагентом или Сотрудником.

Вот как будет выглядеть запрос:

Глава 4: Работа с регистрами

Как известно, регистр остатков состоит из 2-х таблиц: Итоги и Движения. В таблице итогов хранятся остатки на ТА и конец каждого месяца (или другой период, как установлено в Операции > Управление оперативными итогами > Периодичность сохранения остатков; для больших регистров не рекомендуется уменьшать это значение). В таблице движений хранятся соответственно движения за весь период.

Для работы с этими таблицами в 1С++ для них есть свои имена:

• $Регистр.ХХХ – таблица движений регистра ХХХ
• $РегистрИтоги.ХХХ – таблица итогов регистра ХХХ

Пример: получим движения по регистру ОстаткиТоваров у документа Реализация.

Получение документа из регистра

В зависимости от наличия флага БыстаяОбработкаДвижений (значение флага смотрите в разделе Оптимизация регистров) получается 2 способа.

Способ 1: при наличии флага.

Способ 2: если флаг не стоит.

Как всегда, при типизации документа по полю IDDoc не забываем включать в выборку поле IDDocDef.

Виртуальные таблицы

Чтобы получить остаток на некоторую дату, допустим на середину месяца, нужно объединить два запроса: Итоги на конец предыдущего месяца и Обороты с начала месяца по выбранную дату. Чтобы облегчить нам работу, были придуманы так называемые виртуальные таблицы (не путать с представлениями VIEW), которые являются простыми макроподстановками (хотя на самом деле не такими уж и простыми; с большой вероятностью, если вы сами будете их разворачивать, то у вас получится хуже, т.к. лучше уже просто некуда).

Существует несколько видов виртуальных таблиц: Остатки, ОстаткиОбороты, Обороты. Первые 2 только для регистров остатков, 2 – для оборотного регистра.

Пример: получим остатки по складу в разрезе товаров на дату.

В этом примере мы получим остатки на начало ВыбДата. Если мы хотим на конец, то нужно указывать модификатор :ВыбДата

. Если вообще опустить параметр ВыбДата, то получатся остатки на ТА.

В модуле документа обычно необходимо получить остатки на документ. Делается это так:

Замечание: все фильтры нужно накладывать внутри ВТ. Нельзя накладывать фильтр по реквизитам регистра. Это также касается таблицы ОстаткиИОбороты, а для таблицы Обороты можно.

А что, если нужно получить остатки, отфильтрованные по типу номенклатуры? А для этого нужно использовать 2-й параметр ВТ, который называется Соединение.

Таблица ОстаткиИОбороты похожа на таблицу Остатки, только выбирается начальная и конечная даты, и периодичность.

Без периодичности (за период):

С периодичностью: период может быть День, Неделя, Месяц, Квартал, Год.

В данном случае в поле Период будет начало каждого месяца (периода). Как всегда, при указании периода первую дату ставим без модификатора, вторую с модификатором.

Аналогично работает ВТ Обороты, только поля называются Оборот.

Оптимизация регистров

Существует всего 3 способа.

Установка флага БыстраяОбработкаДвижений. Очень полезен при частых расчетах регистра задним числом, а также при снятии отчета за не полный период. При установке этого флага в таблицу движений регистра добавляется поле Date_Time_ > 1 и 3 способы приводят к заметному увеличению индекса, поэтому нужно помнить о балансе записи и чтения.

Глава 5: Вывод остатков в форме списка

Для этой цели больше всего подходят параметризированные запросы.

Выглядит такой запрос так:

Суть состоит в том, что запрос компилируется только 1 раз, а в остальные разы меняются только параметры, помеченные знаком “?”. За счет этого достигается некоторый выигрыш в скорости.

Для того чтобы воспользоваться этой хорошей штукой, поступим следующим образом. В модуле формы заведем переменную RS. В процедуре ПриОткрытии() проинициализируем ее:

В качестве периода в запросе будем использовать начало текущего месяца. Добавим текстовую колонку, в которой пропишем формулу:

Здесь нужно помнить, что запрос нужно писать такой, который вернет только одну позицию. Это можно всегда сделать с помощью функции SUM(). Опять же, для того чтобы это быстро бегало, необходимо наиболее точно попасть в индекс, т.е указать все поля участвующие в индексе. В данном случае это все измерения.

В этом примере мы использовали метод ВыполнитьСкалярный(), который возвращает не таблицу, а единственное значение или структуру, если выбирается несколько колонок.

Вы конечно спросите: а почему не использовали ВТ Остатки? Да, это единственное место, где ее применять не очень хорошо, т.к. там присутствует метод GROUP BY, что несколько снижает скорость работы.

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

Глава 6: Некоторые методы ODBCRecordset

С этим методом запросы выполняются с помощью вызова удаленных процедур (RPC). Например, запрос

превратится в запрос

Что практически равносильно использованию параметризированных запросов. Вывод: нужно всегда использовать РежимRPC(1), кроме тех случаев, когда вы вручную создаете и заполняете временные таблицы (баг MSSQL, приводящий к замедлению таких операций; кстати, сама 1С так делает всегда, поэтому при долгом проведении оно продвигается все медленнее и медленнее). К методу УложитьСписокОбъектов() это не относится.

При расчете регистра на дату, близкую к ТА, удобнее делать Остаток = НачОст + Оборот, а Остаток = ОстатокНаТА – Оборот, за что собственно и отвечает эта процедура.

Глава 7: Контроль остатков и партионный учет

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

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

Пример: тот же пример, но склад в табличной части. Значение склада в ТЧ может быть пустым, тогда берем его из шапки, т.е. просто передаем параметром.

Глава 8: Периодические реквизиты

Периодические реквизиты хранятся в файле _1SConst. Для получения их значений служит виртуальное значение $ПоследнееЗначение. | Константа. ( , [, [, ]]), которое является коррелированным подзапросом (вложенный запрос, в котором используется значения основного).

Пример: Справочник.Номенклатура, подчиненный справочник Цены с периодической ценой.

Глава 9: Получение итогов в запросе

Итоги по группировкам можно получать прямо в запросе, используя конструкцию:

Пример: запрос по остаткам, с итогами по складам, которые будут выводиться перед списком товаров (как в обычных запросах 1С).

В результате получаем таблицу:

Выведем эту таблицу на печать:

Как всегда, поля Склад, Товар — в Расшифровку, Наименования на печать.

Пример: Получить итоги с разворотом по документам. Воспользуемся ВТ ОстаткиОбороты с периодичностью Документ. Для упрощения примера представления получать не будем.

Обратите внимание: MAX(Рег.ВидДокумента). Для типизации $Документ, в выборке должно присутствовать поле Док_вид. Группировать по нему нельзя, т.к. используется конструкция ROLLUP (иначе мы получили бы дополнительные итоги по виду документа, которые нам совсем не нужны). Поэтому мы и делаем таким образом.

Глава 10: Операции UPDATE и DELETE

Пример: в регистр Остатки добавили реквизит Фирма. Необходимо заполнить это поле по общему реквизиту документа (возможно, только если по реквизиту установлен отбор; хотя конечно возможно и без установки отбора, но запрос получается гораздо больше и сложнее).

Обратите внимание на предложение FROM. Только так можно указывать алиасы в предложении UPDATE.

После выполнения запроса необходимо пересчитать регистр. Это можно сделать с помощью ТиИ, но лучше воспользоваться обработкой: УстановкаТА, автор DmitrO. Брать здесь: http://metaprog.km.ru/secrprog1c/sql/apsetup_2_2.zip.

А теперь удалим что-нибудь:

Глава 11: Эмуляция OLAP кубов. Или о том, как на T-SQL реализовать опции и

На основе статьи с сайта 1csql.ru: http://1csql.ru/materials/articles/sql/001.html. Вкратце: суть метода состоит в умножении таблиц со значениями группировок и присоединении таблицы со значениями функций.

Пример: Регистр ОстаткиТоваров, Измерения: Фирма, Склад, Товар. Необходимо получить остатки по товарам в разрезе складов по выбранной Фирме.

Глава 12: Разные примеры использования прямых запросов

Удаление дублирующихся значений в истории для справочника Номенклатура.

Готовая обработка лежит здесь: http://itland.ru/forum/index.php?showtopic=13810.

Поиск дублирующихся элементов. Выберем все элементы справочника Контрагенты, у которых совпадают ИНН.

Уменьшение размера журнала транзакций *.ldf.

Для начала нужно перевести Recovery model в режим Simple. ЕМ (Enterprise Manager) > Свойства базы > Options > Recovery model. Потом запустить скрипт в QA.

Восстановление БД из дампа в ручном режиме. Естественно, имена файлов и БД нужно заменить на свои.

На днях была реализована следующая задача: создание таблицы/загрузка данных в базу на MS SQL из 1С, считывание данных из MS SQL в 1С. Реализация сделана с использованием COM-соединения. Обновиться до 14й платформы и организовать все с помощью внешних источников данных по ряду причин было невозможно. А теперь обо всем по порядку.

1. Настройки для MS SQL.
Создаем новую DataBase (пусть будет testDB ) через консоль SQL, дополнительные настройки настройки по желанию. Я не стал создавать из 1С через скрипт, потому что:
а) процедура разовая;
б) для этого пришлось бы хранить логин/пароль администратора SQL в 1С.
Создаем отдельного пользователя SQL с полными правами в нашей DataBase (допустим, имя пользователя com1c ). Для этого в форме настроек пользователя на вкладке "Server Roles" оставляем все как есть, по умолчанию доступна роль "public" — только просмотр. На вкладке "User Mapping" находим нашу DataBase, добавляем роль db_owner. Пароль пользователя зависит от настроек политики безопасности сервера.

2. Создание/загрузка через 1С.
Для начала нам нужно подключиться к нашей DataBase и создать в ней таблицу ( test_table в моем примере). Для этого нам нужна строка подключения для создания COM-соединения в 1С. Я нашел примеры различных строк на http://www.connectionstrings.com/. Необходимые скрипты SQL можно получить из консоли, если при создании нового объекта нажать кнопку "Script" вверху формы настроек объекта, мои скрипты получены именно таким образом. Пишем код процедуры:

4 комментария:

Добрый день!
Удалось ли Вам реализовать подобное через "Внешние источники данных"?

Да, взаимодействие с MSSQL через внешние источники работает как на чтение, так и на запись.

День добрый.
А что быстрее ??
Прямые запросы, или через внешний источник ??