0. YPermitin 3331 14.03.19 09:20 Сейчас в теме

Трюки с внешними источниками данных

Некоторые трюки для преодоления ограничений внешних источников данных.

Перейти к публикации

Infector; +1 Ответить
Комментарии
Избранное Подписка Сортировка: Древо
1. Infector 131 14.03.19 12:34 Сейчас в теме
Лучший трюк с внешним источником, который довелось провернуть - имитация движений документа с записью в необъектную таблицу. Как способ интеграции хорошо заходит.

А чаще действительно пользуем ADO, как автор в конце описал. Тем более несредственное подключение и выполнение запроса, возвращающего таблицу значений, унифицируется до одной функции.
YPermitin; +1 Ответить
2. YPermitin 3331 14.03.19 12:42 Сейчас в теме
3. genayo 14.03.19 12:45 Сейчас в теме
(1) Ждём от вас статью про ADO :))
4. YPermitin 3331 14.03.19 12:47 Сейчас в теме
(3) не вижу интересных тем по нему. Или они есть?)
5. genayo 14.03.19 13:00 Сейчас в теме
(4) Информации много не бывает. В (1) вот упоминается, что работа с ADO унифицируется до одной функции - многим было бы полезно, думаю.
KEV8383; YPermitin; +2 Ответить
6. YPermitin 3331 14.03.19 13:09 Сейчас в теме
(5) ну так то конечно может быть:)
7. Infector 131 14.03.19 13:25 Сейчас в теме
(5)
Функция СоединитьСерверSQL() Экспорт
	
	Настройки   =	вичи_ПовтИспСеанс.ВернутьНастройкиСвязиOMS();
	
	Server		=   Настройки.СерверSQL;
	Base		=   Настройки.CATALOG;
	User		=   Настройки.ЛогинSQL;
	Pass		=	Настройки.ПарольSQL; 
	булWinLogin = 	Ложь;
		
	con = Новый  COMОбъект("ADODB.Connection");
	con.ConnectionTimeout	= 5;
	con.CommandTimeout		= 0;
	con.CursorLocation		= 3;
	con.Provider			= "MSDASQL";
	Если (не ПустаяСтрока(User))и(не булWinLogin) тогда
		con.ConnectionString	= "driver={SQL Server};server="+Server+";uid="+User+";pwd="+Pass+";Database="+Base+";";
	иначеЕсли не ПустаяСтрока(User) тогда
		con.ConnectionString	= "Driver={SQL Server Native Client 11.0};Server="+Server+";Database="+Base+";Trusted_Connection=yes;uid="+User+";pwd="+Pass+";";
	иначе
		con.ConnectionString	= "Driver={SQL Server Native Client 11.0};Server="+Server+";Database="+Base+";Trusted_Connection=yes;";
	КонецЕсли;
	Попытка
		con.Open();
	Исключение
		Сообщить(ОписаниеОшибки());
	КонецПопытки;
	
	Возврат con;
	
КонецФункции
Показать


Функция ПолучитьТаблицуДанныхSQL(ТекстЗапроса, con)  Экспорт
	Таблица = Новый ТаблицаЗначений;
	cmd	= Новый COMОбъект("ADODB.Command");
	cmd.CommandTimeout = 0;
	cmd.ActiveConnection = con;	
	cmd.CommandText	= ТекстЗапроса;
	rs = cmd.Execute();
	Для НомерСтолбца = 0 По Rs.Fields.Count-1 Цикл
		ИмяНовойКолонки = Rs.Fields(НомерСтолбца).Name;
		Таблица.Колонки.Добавить(ИмяНовойКолонки);
	КонецЦикла;
	Если Не Rs.eof Тогда
		rs.MoveFirst();
	КонецЕсли;
	Пока Не Rs.eof Цикл	
		НоваяСтрока = Таблица.Добавить();
		Для каждого Колонка из Таблица.Колонки Цикл
			ИмяКолонки = Колонка.Имя;
			Значение = Rs.Fields.Item(ИмяКолонки).Value;
			Если значение <> Null Тогда
				НоваяСтрока[ИмяКолонки] = Значение;
			КонецЕсли;		
		КонецЦикла;
		rs.MoveNext();
	КонецЦикла;
	Возврат Таблица;
КонецФункции

Показать


Соединение = СоединитьСерверSQL();
Т1 = ПолучитьТаблицуДанныхSQL(ТекстЗапроса1, Соединение);
Т2 = ПолучитьТаблицуДанныхSQL(ТекстЗапроса2, Соединение);

Попытка
   Соединение.Close();
Исключение
КонецПопытки;

Показать

Основная забота - слепить запросы и обработать то, что они вернут. Но пользоваться возможностями SQL и сводить на уровне 1С запросы к примитивнейшим тоже не запрещено. Полезно, когда есть возможность привлечь к труду ненавидящих 1С по религиозным мотивам или не разбирающихся в ней совершенно, но знающих SQL и написавших на нем свою маленькую базу.
Текст = "exec [VRUS_DW].GTD.getgtdLinesbyDocNum '%НомерГТД%'";	
Текст = СтрЗаменить(Текст, "'%НомерГТД%'", "'10101010/180219/0001010'");
 Т = ПолучитьТаблицуДанныхSQL(Текст , Соединение);
kote; CnupT; jif; DAAbramov; genayo; YPermitin; +6 Ответить
8. YPermitin 3331 14.03.19 13:27 Сейчас в теме
9. w.r. 363 14.03.19 13:42 Сейчас в теме
Был тут один "трюк" с внешими источниками в платформе 8.3.12, из-за которого мне пришлось переписывать обмены с внешней СУБД MS SQL с внешних источников на ADODB. После обновления с 8.3.10 на 8.3.12 перестала работать запись во внешние источники:

Невосстановимая ошибка
Ошибка при выполнении запроса POST к ресурсу /e1cib/logForm:
по причине:
Ошибка SDBL:
В схеме базы данных нет таблицы с именем EDBT33382


Ошибка зафиксирована была мной 8.10.2018

И ответ от 1С

Ошибка платформы 10197269.
Исправлена в 8.3.14, но должна быть перенесена на 8.3.12.
Ошибка исправлена в 8.3.14.907
Сроков переноса исправлений в 8.3.12 пока сообщить не можем.


И, конечно, ничего не перенесли. А не обновляться на 8.3.12 я не мог, так как обновления требовала конфигурация на новой БСП. 8.3.14.907 - это на тот момент была какая-то внутренняя альфа или пре-бета версия, недоступная для скачивания. Тогда я еще раз проклял про себя 1С и начал переписывать обмены
YPermitin; +1 Ответить
10. YPermitin 3331 14.03.19 13:46 Сейчас в теме
(9) возможно, это будет еще один сигнал всем, кто планирует сейчас использовать внешние источники.
11. w.r. 363 14.03.19 13:48 Сейчас в теме
(10) самое интересное, что еще с 8.3.5, когда я только написал обмены на внешних источниках, и по 8.3.10 все отлично работало - это 3 года, а потом в 8.3.12 сломали
16. Mortum 15.03.19 15:50 Сейчас в теме
(9) надо было немножко подождать, потому что на 30.11.2018 баг был оперативно исправлен после баг-репорта на testplatform@1c.ru.
19. w.r. 363 15.03.19 16:35 Сейчас в теме
(16) мы не могли ждать почти 2 месяца (оперативность однако) пока исправят баг
YPermitin; +1 Ответить
12. geka-geka 1 14.03.19 14:23 Сейчас в теме
Есть ли какой-нибудь трюк, чтобы в запросе соединить несколько таблиц внешних источников?
13. YPermitin 3331 14.03.19 14:26 Сейчас в теме
(12) разве что сделать говую таблицу, в которой сделать произвольный запрос. В этом запросе на уровне SQL и объединить.

Ну или в СКД использовать несколько наборов данных. И с помощью средств СКД как-раз и объединить.
17. Mortum 15.03.19 15:57 Сейчас в теме
(12) Есть. ВнешнийИсточникДанных.ИмяВнешнегоИсточника.Таблица.ИмяТаблицы1 КАК ИмяТаблицы1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВнешнийИсточникДанных.ИмяВнешнегоИсточника.Таблица.ИмяТаблицы2 КАК ИмяТаблицы2
Даже временные таблицы можно: ПОМЕСТИТЬ ВнешнийИсточникДанных.ИмяВнешнегоИсточника.ВременнаяТаблица.ИмяВТ
YPermitin; +1 Ответить
21. YPermitin 3331 15.03.19 16:52 Сейчас в теме
(17) то есть теперь работает соединение таблиц из разных источников или таблиц одного источника?
22. Mortum 15.03.19 22:30 Сейчас в теме
(21) из одного да. Из нескольких никак, но тут платформа даже при сильном желании ничего не сможет.
acanta; YPermitin; +2 Ответить
14. Aletar 15.03.19 06:02 Сейчас в теме
А скажите, пожалуйста, вот если я делаю запись через внешние источники данных, т.е.

НаборЗаписей = ВнешниеИсточникиДанных.<...>.СоздатьНаборЗаписей();
....
НаборЗаписей.Записать();


Как задать время ожидания на блокировке? Как установить LOCK_TIMEOUT для запроса операции записи?
YPermitin; +1 Ответить
15. YPermitin 3331 15.03.19 07:17 Сейчас в теме
(14) к сожалению, нет.

Тут надо либо использовать ADO, либо обходные пути из публикации. Тогда будет возможность установить таймаут на ожидании блокировки.
18. Mortum 15.03.19 16:03 Сейчас в теме
(14) возможно получится в строке соединения к ВИД указать таймаут, но это уже от драйвера внешней базы зависит.
YPermitin; +1 Ответить
20. YPermitin 3331 15.03.19 16:51 Сейчас в теме
(18) тут про параметр LOCK_TIMEOUT для SQL Server. К сожалению его только в скрипте можно.
23. Darklight 19 18.03.19 12:53 Сейчас в теме
ADO - это, конечно, хорошо, но доступно только через СOM со всеми вытекающими. И без COM придётся извращаться с внешними источниками.
YPermitin; +1 Ответить
24. YPermitin 3331 18.03.19 12:57 Сейчас в теме
(23) не спорю, но иногда все же лучше, чем внешний источник.

Но иногда и нет :)
25. acanta 60 18.03.19 13:02 Сейчас в теме
(23) а внешние источники Com не используют точно? А что тогда?
26. Darklight 19 18.03.19 13:10 Сейчас в теме
(25)Используются драйвер-внешняя-библиотека, указываемый при создании строки подключения (аналогично работает со своей СУБД и сама платформа). Работа с ним не подразумевает обязательного применения COM, но, если будет использован драйвер ODBC или OLE -то он, вроде как сам использует COM технологию.
YPermitin; +1 Ответить
27. DonAlPatino 53 19.03.19 10:32 Сейчас в теме
Я правильно понимаю, что ADO - это исключительно Windows вариант? Если сервер под Linux, то только внешние источники и odbc?
YPermitin; +1 Ответить
28. YPermitin 3331 19.03.19 17:56 Сейчас в теме
(27) да, ADO - это технология для Windows-платформы. Если сервер 1С под Linux, то вариантов связать 1С с внешним источником:

1. Внешние источники данных со всеми ограничениями + ODBC. Кстати, есть и ODBC-драйвер для Linux
(https://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/) Но такую связку я не тестировал.
Подробнее хорошо описано здесь: https://infostart.ru/public/522751/

2. Делать прослойку в виде веб-сервиса для базы и уже через нее взаимодействовать из 1С. Веб-сервис можно сделать на .NET, Java, GO. Вообщем, к чему душа ближе. На MSDN для .NET все хорошо расписано.

3. Есть менее популярные варианты в виде внешних компонент, использования сторонних утилит и др. Но сопровождать это все будет потом не просто.
29. DonAlPatino 53 20.03.19 15:04 Сейчас в теме
(28) Мы из ЗУПа во внешнюю базу данные по 20 тыс сотрудников и гпхашникам льём. Причем в другой датацентр. Были проблемы с каналом (пропадал 20ый-30ый пакет) - так выгрузка через ODBC стала идти 8 часов вместо одного. Вот надеялся, что переход на ADO поможет. А это получается "vendor-lock". Пункты 2 и 3 как-то не выглядят рабочими при таких вводных вообще...
YPermitin; +1 Ответить
30. YPermitin 3331 20.03.19 15:17 Сейчас в теме
(29) все верно, но вы уже в "vendor-lock" режиме, когда сели на 1С :)

Если серьезно, то при необходимости выгрузки очень больших объемов данных во внешнюю базу я бы использовал BULK-операции, в том числе если канал связи не очень хороший. Посмотрите в сторону BCP для SQL Server или COPY для PG. Это выгрузка и загрузка.

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

Если скорость выгрузки не является узким местом, то можно и стандартными средствами 1С выгружать в файл.

Если экосистема одна, например оба сервера SQL Server, то их можно залинковать и передавать данные собственными средствами (реаликация и т.д.)

В комментарии подробно сложно все это описать, но как вводная пойдет :)
31. DonAlPatino 53 20.03.19 16:14 Сейчас в теме
(30) BULK сейчас смотрим. А вот насчет передачи данных репликацией между SQL серверами... это будет лучше-быстрее чем те же ODBC или ADO?
YPermitin; +1 Ответить
32. YPermitin 3331 21.03.19 15:55 Сейчас в теме
(31) это будет надежней на мой взгляд, т.к. передачу на себя возьмет сам SQL Server. При настройке репликации есть возможность указать качество мвязи и др. параметры. В том числе и конкретные таблицы или условия передачи.

На MSDN есть отличные материалы по этой теме.
33. akpaevj 21.03.19 22:11 Сейчас в теме
(31) Можно поэкспериментировать с "EXEC sp_addlinkedserver" и "EXEC AT". Удобно для работы с удаленными серверами. Для быстрой загрузки можно попробовать порционно вставлять инструкциями
INS ERT IN TO ..... VALUES (.....), (.....), .....
главное длину запроса не превысить
YPermitin; +1 Ответить
34. YPermitin 3331 22.03.19 07:02 Сейчас в теме
(33) линковка серверов очень удобна. Сам использую :)
36. DonAlPatino 53 22.03.19 10:23 Сейчас в теме
(34)Я правильно понимаю что при линковке мы вроде как кидаем данные на ближайший сервер MS SQL, но по факту они сразу переправляются на удаленный? А внутри это ближе к репликации данных между серверами или все тому же ODBC/ADO?
37. akpaevj 22.03.19 13:53 Сейчас в теме
(36)
Я правильно понимаю что при линковке мы вроде как кидаем данные на ближайший сервер MS SQL, но по факту они сразу переправляются на удаленный? А внутри это ближе к репликации данных между серверами или все тому же ODBC/ADO?

При линковке мы формируем Dynamic statement и отправляем его для исполнения на удаленном сервере.
Если проводить параллели, то это ближе к ADO. Репликация подразумевает под собой автоматический перелив данных/транзакций на slave экземпляр. В отличие от BULK инструкций при линковке не требуется дополнительный шаг загрузки/выгрузки файла, но требуется валидация данных для исключения SQL Injections. Требования к каналу связи +- одинаковы, те же данные переливать. В любом случае, все это обмен средствами СУБД, т.е. консистентность данных на удаленном хосте на вашей совести.
YPermitin; +1 Ответить
39. akpaevj 22.03.19 14:36 Сейчас в теме
(36) И как в статье сказано, лучше избегать установки в запросы константных значений что-бы избежать ad-hoc workload и соответственно роста объема кэша планов и деградации производительности при выполнении таких запросов. Если конечно не включен параметр Optimize for Ad-hoc Workload
YPermitin; +1 Ответить
35. DonAlPatino 53 22.03.19 10:21 Сейчас в теме
(30) Про BULK - я правильно понимаю что из 1С мы выгружаем данные в файл, потом перекидываем файл в другой датацентр и там грузим BULK INSERT? Или есть способ напрямую из 1С данные неким здоровым пакетом бросить?
38. akpaevj 22.03.19 13:56 Сейчас в теме
(35) Транспорт данных на удаленный хост - на вкус и цвет=) Можно веб-сервис состряпать, голубями отправлять, курьером на флешке, ftp и еще куча разных задорных способов)
44. DonAlPatino 53 01.04.19 11:58 Сейчас в теме
(38) Мда... переделали с внешних источников на csv и bulk вставку. Непосредственно загрузка данных стало где-то минута вместо 40 минут... В общем "внешние источники" для точечных изменений...
YPermitin; +1 Ответить
45. YPermitin 3331 01.04.19 12:04 Сейчас в теме
(44) bulk тоже не универсальный способ. Например если для внешнего контингента данные нужно предоставлять, то через SQL это не совсем правильно и безопасно. Ниже в комментариях уже говорили.

А так рад, что у вас все получилось!
40. newbigrator 26.03.19 22:21 Сейчас в теме
Вопрос оффтоп, но рядом с темой.
Допустим надо каждую ночь писать большой обьем данных из 1С во внешнюю таблицу СГЛ. Насколько правильно и успешно такое можно будет сделать через механизм Вненших Источников Данных?
YPermitin; +1 Ответить
41. YPermitin 3331 27.03.19 14:44 Сейчас в теме
(40) если стандартными возможностями через наборы записей внешнего источника данных, то это не самый быстрый путь.

Эффективные способы:
1. Выгрузка в CSV или внутренний формат BCP и его загрузка в приемник. То есть использование Bulk Insert, подробнее смотрите в предыдущей статье.
2. Можно сделать выгрузку только измененных данных, если задача позволяет. Тогда и штатные механизмы могут помочь.
42. rukalico 31.03.19 15:29 Сейчас в теме
(41) А не правильнее ли в таких ситуациях вообще использовать веб сервисы. Поднимаем веб сервис согласно спецификации, в том числе получаем возможность раздавать xml схему. А дальше кому нужно тянет данные и главное АПИ описан и стандартизирован.
А так через СГЛ писать куда то самим это брать на себя обязательства по выгрузке и фактор АПИ теряется.
YPermitin; +1 Ответить
43. YPermitin 3331 31.03.19 21:36 Сейчас в теме
(42) никто не спорит. Все что сказано в статье это не замена всех возможных способов интеграции, а уж тем более веб-сервисов.

Все таки цели разные. Bulk Insert - это больше для внутреннего пользования, внутрикорпоративного, где нужно оптимизировать определенные операции на уровне SQL Server / PostgreSQL.

Веб-сервисы (SOAP) / HTTP-сервисы для другого - это создание интеграций, в т.ч. и с документированным API, который можно использовать и для внутренних целей, так и для внешних (для клиентов и др.).

То есть все зависит от задачи.
46. v3rter 20.06.19 12:49 Сейчас в теме
Вопрос: будут ли эти трюки легальны для лицензии SQL Runtime? С одной стороны, на SQL-сервере мы выходим за пределы функционала 1С, но с другой стороны, все полученные данные используются исключительно из 1С. Вопрос не самый простой и не праздный, считаю.

Цитирую с сайта одного из партнеров (для тех, кто не в курсе):
"1С" предлагает совместный продукт: лицензии Microsoft SQL Server, предназначенные для использования совместно с системой "1С:Предприятие 8". Эти лицензии бывают 2-х видов: Runtime и Full-use.
Лицензии Runtime можно использовать только с "1С:Предприятие 8".
Лицензии Full-use – без ограничения на право использования. Пользователь может использовать Microsoft SQL Server, приобретенный вместе или для "1С:Предприятие 8", вместе с другими приложениями и в любых целях до тех пор, пока продолжает пользоваться продуктом "1С:Предприятие 8".
Лицензии на сервер и клиентский доступ должны быть в одной категории – или Runtime, или Full-use.
YPermitin; +1 Ответить
47. YPermitin 3331 20.06.19 13:58 Сейчас в теме
(46) к сожалению, у меня нет ответа на этот вопрос.

Я могу исходить лишь из здравого смысла. В части лицензирования он не всегда работает :)
48. AlexBober78 21.06.19 18:42 Сейчас в теме
Очень интересно.
Главное, такую функцию по выполнению всего-чего-угодно в базе лучше хорошенько прятать как можно дальше...
YPermitin; +1 Ответить
49. YPermitin 3331 22.06.19 12:31 Сейчас в теме
(48)
прятать


Да :)
Как и много чего еще.
52. sparhh 27.06.19 12:57 Сейчас в теме
(49) Но ведь эта функция при правильной настройке прав получит доступ только к требуемой базе и по идее не такая уж и дыра.
Или чем такой подход отличается от выполнения такого же произвольного кода через ADO?
50. ADirks 181 26.06.19 09:28 Сейчас в теме
Кстати, про сложные запросы. Можно же использовать view'шки, и в 1С обращаться именно к ним.
Например

CREATE VIEW ActiveSessions as
SELECT
s.session_id,
r.status,
r.cpu_time,
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
r.command,
s.login_name,
s.host_name,
s.program_name
FROM
sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

И в 1С уже будет
SELECT ... FROM ActiveSessions

Даже какое-то количество SP'шек можно переделать на view'шки. И безопасность меньше пострадает.
51. sparhh 27.06.19 10:17 Сейчас в теме
Если требуется писать во внешние источники, то что данный механизм может предложить?

Потребовалось писать данные по справочнику во внешнюю таблицу, ID - PrimaryKey.
Запись\обновление строки таблицы сгл сделал так -

_ID = Строка(СсылкаНаЭлемент.УникальныйИдентификатор());

ОбъектСГЛ = ТаблицаСГЛ.НайтиПоПолю("_ID", _ID);
Если ЗначениеЗаполнено(ОбъектСГЛ) Тогда 
	ОбъектСГЛ = ОбъектСГЛ.ПолучитьОбъект();	
Иначе
	ОбъектСГЛ = ТаблицаСГЛ.СоздатьОбъект();
	ОбъектСГЛ._ID = Строка(СсылкаНаЭлемент.УникальныйИдентификатор());
КонецЕсли;
Показать


В таком случае выполняется 3! запроса для случая изменения строки + это вообще построчный insert.
Есть ли возможность типовыми средствами сделать оптимальную запись?

--
В СКЛ есть команда MERGE для случая вставки+обновления, можно ли через 1С ее вызвать в объектном варианте?
--
Или же как автор показал в одном из "трюков" остается только вариант добавлять хранимую процедуру для произвольного выполнения команд и через нее мерджить.
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Автор новостных обзоров на тему 1С и бухучета
Санкт-Петербург
По совместительству

Программист 1С
Москва
зарплата от 120 000 руб.
Полный день

Руководитель проекта, аналитик, консультант
Санкт-Петербург
По совместительству

Программист 1С
Самара
зарплата от 100 000 руб. до 150 000 руб.
Полный день

Бизнес-аналитик 1С
Москва
зарплата от 140 000 руб. до 200 000 руб.
Полный день