13 авг. 2012 г.

Прямые запросы к MS SQL серверу

На днях была реализована следующая задача: создание таблицы/загрузка данных в базу на 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" вверху формы настроек объекта, мои скрипты получены именно таким образом. Пишем код процедуры:
 СтрокаПодключения = 
 "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С "Внешние источники данных", будет весело :)

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

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

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

      Удалить