На днях была реализована следующая задача: создание таблицы/загрузка данных в базу на 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" вверху формы настроек объекта, мои скрипты получены именно таким образом. Пишем код процедуры:
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" вверху формы настроек объекта, мои скрипты получены именно таким образом. Пишем код процедуры:
СтрокаПодключения = "Provider=SQLOLEDB.1; |User ID=com1c; |Pwd=********; |Data Source=Имя_сервера_SQL; |Initial Catalog= testDB";//наша database Connection = Новый COMОбъект("ADODB.Connection"); Command = Новый COMОбъект("ADODB.Command"); RecordSet = Новый COMОбъект("ADODB.RecordSet"); Попытка Connection.Open(СокрЛП(СтрокаПодключения)); Command.ActiveConnection = Connection; //Проверка на наличие нашей database Command.CommandText = "select * from sys.databases where name = 'testDB'"; RecordSet = Command.Execute(); Если RecordSet.EOF() И RecordSet.BOF() Тогда //нет записей - нет такой database, надо создавать; Возврат; КонецЕсли; RecordSet.MoveFirst(); Исключение Сообщить(ОписаниеОшибки()); КонецПопытки; //создание/удаление таблицы. в файле загрузки несколько миллионов записей, поэтому было решено пересоздавать таблицу заново, нежели удалять записи. Command.CommandText = "USE testDB |IF exists(select name from sys.objects Where name = 'test_table' And type = 'U') drop table [dbo].[test_table]"; Command.Execute(); Command.CommandText = "USE testDB |Create table test_table ( |POLE_01 VARCHAR (4), |POLE_02 DATE, |POLE_03 Numeric(9,0), |)"; Command.Execute(); //Создаем индексы, несколько миллионов записей после этого будут обрабатываться быстрее Command.CommandText = "USE testDB |CREATE NONCLUSTERED INDEX [NumSer] ON [dbo].[test_table] |( |[POLE_01] ASC |) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"; Command.Execute(); //заполенение происходит из файла dbf ФайлДБФ = Новый XBase(ФайлДанных); ФайлДБФ.Кодировка=КодировкаXBase.OEM; КолЗаписей = ФайлДБФ.КоличествоЗаписей(); ФайлДБФ.Первая(); Для сч = 1 По КолЗаписей Цикл Command.CommandText = "USE testDB |INSERT INTO test_table VALUES( |'"+ДБФ.POLE_01+"', |'"+ДБФ.POLE_02 +"', |'"+ДБФ.POLE_03 +"', |)"; Command.Execute(); ФайлДБФ.Следующая(); КонецЦикла; ФайлДБФ.ЗакрытьФайл(); Command = Неопределено; Connection = Неопределено; RecordSet = Неопределено;
3. Поиск через 1С.
Подключение к базе приводить не буду, можно взять из кода выше, рассмотрим создание/обработку запроса.
Command.CommandText = "SELECT * FROM [testDB].[dbo].[test_table] WHERE POLE_01= '"+Поле_01+"' AND POLE_02 = '"+POLE_02+"'" ; //сначала пробовал этот запрос, в консоли SQL он так же работает, но в 1С после выполнения соединение закрывается, вылетает с ошибкой Operation is not allowed when object is closed //|USE "+ testDB +"; //|SELECT * FROM test_table WHERE POLE_01 = '"+Поле_01+"' AND POLE_02 = '"+Поле_02+"';"; RecordSet = Новый COMОбъект("ADODB.RecordSet"); Попытка RecordSet = Command.Execute(); Если RecordSet.EOF() И RecordSet.BOF() Тогда Сообщить("По заданным условиям ничего не найдено."); Возврат Неопределено; КонецЕсли; мСтруктура = Новый Структура; мПоля = RecordSet.Fields.Count - 1; //в моем случае запрос возвращает либо одну запись, либо ничего. для обхода записей можно использовать Пока НЕ RecordSet.EOF() Цикл RecordSet.MoveFirst(); Для сч = 0 По мПоля Цикл мСтруктура.Вставить(RecordSet.Fields(сч).Name, RecordSet.Fields(сч).Value); КонецЦикла; //из SQL дата возвращается строкой ГГГГ-ММ-ДД, преобразуем в дату мСтруктура. POLE_02 = Дата(СтрЗаменить(мСтруктура. POLE_02, "-", "")); Возврат мСтруктура; Исключение Сообщить(ОписаниеОшибки()); Возврат Неопределено; КонецПопытки;На этом все, в дальнейшем думаю реализовать что-нибудь подобное с помощью объекта 1С "Внешние источники данных", будет весело :)
Добрый день!
ОтветитьУдалитьУдалось ли Вам реализовать подобное через "Внешние источники данных"?
Да, взаимодействие с MSSQL через внешние источники работает как на чтение, так и на запись.
УдалитьДень добрый.
УдалитьА что быстрее ??
Прямые запросы, или через внешний источник ??
Без разницы, механизмы те же.
Удалитьhttp://v8.1c.ru/overview/Term_000000795.htm
Здесь больше вопрос удобства использования.