Подключение к SQL-серверу из 1С:Предприятие 8 (пример кода)

Logo_1c_8_RazradotkaВ данной статье приведены экземпляры кода на языке 1С, которые позволяют подключаться к базе данных на SQL-сервере, получать из нее данные, а также записывать новые и обновлять существующие записи в таблицах этой базы данных. Код протестирован на платформе 8.3.5, а также на версиях SQL Server 2008 R2 и SQL Server 2012. Операционная система Windows Server 2008 и Windows Server 2012. Есть предположение, что указанный код будет работать и на других версиях 1С, SQL и Windows.

 

0. Оглавление

  1. Постановка задачи
  2. Подключение к SQL-серверу
  3. Добавление записей в таблицу
  4. Обновление записей в таблице
  5. Просмотр выборки из таблицы
  6. Удаление записей из таблицы
  7. Закрытие соединения
  8. Общий текст процедуры

1. Постановка задачи

Итак, имеется SQL-сервер с адресом WIN2012\TEST_SQL. Для подключения используется имя входа SQL-сервера UserDB с паролем Pass123456.

podkly-k-sql-iz-1s_004

На сервере размещена база данных с именем TestDB. В базе имеется таблица с именем Table_1, в которой 3 столбца:

  • C_DateTime, с типом datetime
  • C_Char, с типом char(50)
  • C_Numeric, с типом numeric(18,0)

podkly-k-sql-iz-1s_001

Изначально таблица пустая:

podkly-k-sql-iz-1s_002

У пользователя UserDB есть права на чтение и запись в базу данных TestDB. В данном случае пользователь является владельцем указанной базы данных.

podkly-k-sql-iz-1s_003

Требуется научиться записывать в базу данных и читать из базы данных из 1С при помощи прямых запросов к SQL-серверу.

2. Подключение к SQL-серверу

Весь приведенный ниже код может работать как на клиенте, так и на сервере. Главное чтобы был доступен COM-объект «ADODB» (подробнее о данной библиотеке можно прочитать, например, здесь). Также с клиента (или с сервера, смотря где выполняется код) должен быть доступен указанный SQL-сервер (настроено правило в Брандмауере для возможности внешнего подключения). Код приводится совсем простой, для понимания с чего вообще начинать разработчику, который раньше с этим не сталкивался.

Итак, для подключения необходимо создать COM-объект «ADODB». Для этого выполняется код 1С:

&НаКлиенте
Процедура ВыполнитьОбработку(Команда)

    //Инициализация переменных
    ИмяСервераSQL = "WIN2012\TEST_SQL";
    ПользовательSQL = "UserDB";
    ПарольSQL = "Pass123456";
    БазаДанныхSQL = "TestDB";
    ТаблицаSQL = "Table_1";


    /////////////////////////////////////////
    //Подключение к SQL-серверу
    Попытка
        Соединение  = Новый COMОбъект("ADODB.Connection");
        Команда     = Новый COMОбъект("ADODB.Command");
        Выборка     = Новый COMОбъект("ADODB.RecordSet");
        Соединение.ConnectionString =
            "driver={SQL Server};" +
            "server="+ИмяСервераSQL+";"+
            "uid="+ПользовательSQL+";"+
            "pwd="+ПарольSQL+";"+
            "database="+БазаДанныхSQL+";";
        Соединение.ConnectionTimeout = 30;
        Соединение.CommandTimeout = 600;
        //Открытие соединение
        Соединение.Open();
        Команда.ActiveConnection   = Соединение;
        Сообщить("Успешное подключение!");
    Исключение
        Сообщить(ОписаниеОшибки());
        Возврат;
    КонецПопытки;

КонецПроцедуры

Результат работы в 1С:Предприятии:

podkly-k-sql-iz-1s_005

Теперь, в общем то, можно выполнять любой запрос SQL через данный COM-объект. Посмотрим как это делать на самых распространенных примерах.

3. Добавление записей в таблицу

Теперь добавим новую строку в нашу пока еще пустую таблицу. Для этого необходимо выполнить код:

    /////////////////////////////////////////
    //Добавляем запись в таблицу
    ТекДата = ТекущаяДата();
    ТекСтрока = "Запись строки";
    ТекЧисло = 123456;

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДата) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДата = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "INSERT INTO "+ТаблицаSQL+"
        |([C_DateTime], [C_Char], [C_Numeric])
        |VALUES ('"+ТекДата+"', N'"+ТекСтрока+"', '"+ТекЧисло+"')";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись добавлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Смотрим результат и видим, что строка появилась в таблице:

podkly-k-sql-iz-1s_006

4. Обновление записей в таблице

Теперь попробуем обновить все записи в таблице со значением C_Numeric=123456. Для этого выполняем код:

    /////////////////////////////////////////
    //Обновляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекДатаНовая = Дата(1,1,1);
    ТекСтрокаНовая = "Обновленная запись строки";

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДатаНовая) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДатаНовая = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "UPDATE "+ТаблицаSQL+"
        |SET
        |   [C_Char] = N'"+ТекСтрокаНовая+"',
        |   [C_DateTime] = '"+ТекДатаНовая+"'
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись обновлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Проверяем, что запись обновилась:

podkly-k-sql-iz-1s_007

5. Просмотр выборки из таблицы

Писать и обновлять записи в таблице мы научились. Теперь посмотрим как просматривать выборку из запроса с SQL-сервера:

    /////////////////////////////////////////
    //Читаем записи
    ТекстИнструкции =
        "SELECT
        |   [C_DateTime], [C_Char], [C_Numeric]
        |FROM "+ТаблицаSQL;
    Попытка
        Команда.CommandText = ТекстИнструкции;
        Выборка = Команда.Execute();
        Если Выборка.BOF = Ложь Тогда
            Выборка.MoveFirst();
            Пока Выборка.EOF = Ложь Цикл
                Сообщить("[C_DateTime]="+Дата(Выборка.Fields("C_DateTime").Value)
                    +", [C_Char]="+СокрЛП(Выборка.Fields("C_Char").Value)
                    +", [C_Numeric]="+Число(Выборка.Fields("C_Numeric").Value));
                Выборка.MoveNext();
            КонецЦикла;
        КонецЕсли;
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Результат в предприятии:

podkly-k-sql-iz-1s_008

6. Удаление записей из таблицы

Для удаления всех записей в таблице со значением C_Numeric=123456 выполняем код:

    /////////////////////////////////////////
    //Удаляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "DELETE FROM "+ТаблицаSQL+"
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись удалена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

Проверяем, что записей в таблице не осталось:

podkly-k-sql-iz-1s_009

7. Закрытие соединения

Ну и после выполнения всех необходимых действий необходимо закрыть соединение с SQL-сервером. Для этого достаточно вызвать метод Close() созданного COM-объекта:

    /////////////////////////////////////////
    //Закрытия соединения
    Попытка
        Соединение.Close();
        Сообщить("Соединение закрыто!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

8. Общий текст процедуры

Общий текст всей процедуры получился следующий:

&НаКлиенте
Процедура ВыполнитьОбработку(Команда)

    //Инициализация переменных
    ИмяСервераSQL = "WIN2012\TEST_SQL";
    ПользовательSQL = "UserDB";
    ПарольSQL = "Pass123456";
    БазаДанныхSQL = "TestDB";
    ТаблицаSQL = "Table_1";


    /////////////////////////////////////////
    //Подключение к SQL-серверу
    Попытка
        Соединение  = Новый COMОбъект("ADODB.Connection");
        Команда     = Новый COMОбъект("ADODB.Command");
        Выборка     = Новый COMОбъект("ADODB.RecordSet");
        Соединение.ConnectionString =
            "driver={SQL Server};" +
            "server="+ИмяСервераSQL+";"+
            "uid="+ПользовательSQL+";"+
            "pwd="+ПарольSQL+";"+
            "database="+БазаДанныхSQL+";";
        Соединение.ConnectionTimeout = 30;
        Соединение.CommandTimeout = 600;
        //Открытие соединение
        Соединение.Open();
        Команда.ActiveConnection   = Соединение;
        Сообщить("Успешное подключение!");
    Исключение
        Сообщить(ОписаниеОшибки());
        Возврат;
    КонецПопытки;


    /////////////////////////////////////////
    //Добавляем запись в таблицу
    ТекДата = ТекущаяДата();
    ТекСтрока = "Запись строки";
    ТекЧисло = 123456;

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДата) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДата = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "INSERT INTO "+ТаблицаSQL+"
        |([C_DateTime], [C_Char], [C_Numeric])
        |VALUES ('"+ТекДата+"', N'"+ТекСтрока+"', '"+ТекЧисло+"')";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись добавлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;


    /////////////////////////////////////////
    //Обновляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекДатаНовая = Дата(1,1,1);
    ТекСтрокаНовая = "Обновленная запись строки";

    //Приобразуем данные
    Если НЕ ЗначениеЗаполнено(ТекДатаНовая) Тогда
        //Для пустой даты необходимо отправлять 01.01.1753
        ТекДатаНовая = Дата(1753,1,1);
    КонецЕсли;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "UPDATE "+ТаблицаSQL+"
        |SET
        |   [C_Char] = N'"+ТекСтрокаНовая+"',
        |   [C_DateTime] = '"+ТекДатаНовая+"'
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись обновлена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

    /////////////////////////////////////////
    //Читаем записи
    ТекстИнструкции =
        "SELECT
        |   [C_DateTime], [C_Char], [C_Numeric]
        |FROM "+ТаблицаSQL;
    Попытка
        Команда.CommandText = ТекстИнструкции;
        Выборка = Команда.Execute();
        Если Выборка.BOF = Ложь Тогда
            Выборка.MoveFirst();
            Пока Выборка.EOF = Ложь Цикл
                Сообщить("[C_DateTime]="+Дата(Выборка.Fields("C_DateTime").Value)
                    +", [C_Char]="+СокрЛП(Выборка.Fields("C_Char").Value)
                    +", [C_Numeric]="+Число(Выборка.Fields("C_Numeric").Value));
                Выборка.MoveNext();
            КонецЦикла;
        КонецЕсли;
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

    /////////////////////////////////////////
    //Удаляем записи в таблице с C_Numeric = 123456
    ТекЧисло = 123456;
    ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы

    ТекстТекущейИнструкции =
        "DELETE FROM "+ТаблицаSQL+"
        |WHERE [C_Numeric]='"+ТекЧисло+"'";
    Попытка
        Соединение.Execute(ТекстТекущейИнструкции,,128);
        Сообщить("Запись удалена!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;


    /////////////////////////////////////////
    //Закрытия соединения
    Попытка
        Соединение.Close();
        Сообщить("Соединение закрыто!");
    Исключение
        Сообщить(ОписаниеОшибки());
    КонецПопытки;

КонецПроцедуры

Результат работы в 1С:Предприятии:

podkly-k-sql-iz-1s_010

Помогла ли вам данная статья?
Да, спасибо, все получилось.
Немного помогла.
Совсем не помогла.
Не то, что я искал(а).
Смотреть результаты
Запись опубликована в рубрике Разработка в 1С с метками , , . Добавьте в закладки постоянную ссылку.


39 Responses to Подключение к SQL-серверу из 1С:Предприятие 8 (пример кода)

  1. myr4ik07 говорит:

    Дуже корисно. Дякую.

  2. Олег говорит:

    Спасибо, очень помогли!

  3. Алексей говорит:

    Отличная интересная статья о том как можно подключить свою страницу в интернете к 1с предприятию — очень удобно управлять торговлей.

  4. Аноним говорит:

    Utf-8 не понимает как исправить?
    записивает как ??????

  5. Аноним говорит:

    как можно таблицу в SQL загрузить сразу в массив или в тз? Вижу только выборку

  6. Александр говорит:

    Спасибо тебе добрый человек

  7. Анон говорит:

    Очень полезная инфа. Огромное спасибо

  8. Аноним говорит:

    отличная статья

  9. David говорит:

    Добрый день. А как подключиться к SQL, если он на другом компе в локальной сети.
    Спасибо.

    • Виталий Онянов говорит:

      Здравствуйте.

      Все то же самое, только в свойствах подключения (ИмяСервераSQL) указываете адрес компьютера с SQL Server.

  10. Аноним говорит:

    Спасибо

  11. wmon говорит:

    Приветствую!
    Подскажите пожалуйста насчет строки «driver={SQL Server};»
    Я так понимаю нужно указать правильный драйвер для подключения к серверу….
    Как узнать что за драйвер там стоит? Или этот драйвер надо установить и на компе где 1С установлен? SQL Server у меня на другом компе…

    • Виталий Онянов говорит:

      Да, драйвер должен быть на компьютере, откуда вы подключаетесь. Если код выполняется на сервере — то на сервере с кластером серверов 1С, если на клиенте, то, соответственно, на клиенте.

      В системе Windows для SQL Server уже есть все необходимые драйвера. Специально ничего ставить не надо.

      Посмотреть версию драйвера можно в программе «Источники данных ODBC», поищите поиском в меню «Пуск».

  12. wmon говорит:

    С подключением и драйвером разобрался… Все ок.
    Подскажите пожалуйста, как добавить в таблицу SQL сразу все записи из выборки ?
    Т.е сейчас у меня так:
    РезЗапроса = Запрос.Выполнить().Выбрать();
    Пока РезЗапроса.Следующий() Цикл

    ТекстТекущейИнструкции =

    «INSERT INTO «+ТаблицаSQL+»
    |([StockAdministration], [ItemCode])
    |VALUES (N'»+РезЗапроса.Код+»‘, ‘»+РезЗапроса.Сум+»‘, )»;

    Попытка
    Соединение.Execute(ТекстТекущейИнструкции,,128);
    //Сообщить(«Запись добавлена!»);
    Исключение
    Сообщить(ОписаниеОшибки());
    КонецПопытки;
    КонецЦикла;

    Возможно ли оптимизировать?

    • Виталий Онянов говорит:

      Да, конечно, можно сделать так:

      РезЗапроса = Запрос.Выполнить().Выбрать();
      ТекстТекущейИнструкции = "";
      Пока РезЗапроса.Следующий() Цикл
      
      	ТекстТекущейИнструкции = ТекстТекущейИнструкции + Символы.ПС + 
      
      	"INSERT INTO "+ТаблицаSQL+"
      	|([StockAdministration], [ItemCode])
      	|VALUES (N'»+РезЗапроса.Код+»‘, ‘»+РезЗапроса.Сум+»‘, )";
      	
      КонецЦикла;
      
      Попытка
      	Соединение.Execute(ТекстТекущейИнструкции,,128);
      Исключение
      	Сообщить(ОписаниеОшибки());
      КонецПопытки;
      
      
  13. Armastus говорит:

    Добрый день, Виталий! Большое спасибо за статью, выручала раньше, но тут случилась проруха — а именно база MySQL на Unix серевере((( Какого провайдера нужно указывать в таком случае и как посмотреть на Unix серевере, какие драйвера установлены и используются?

    • Виталий Онянов говорит:

      Здравствуйте. Для MySQL используется вот этот драйвер (нужной версии).

      Пример кода:

      	Попытка
      		Соединение  = Новый COMОбъект("ADODB.Connection");
      		Команда     = Новый COMОбъект("ADODB.Command");
      		Соединение.ConnectionString ="driver={MySQL ODBC 5.3 ANSI Driver}; PORT=" + Формат(НастройкиИнтеграции.Порт,"ЧГ=") + ";" +
      			"SERVER="+НастройкиИнтеграции.Адрес+";"+
      			"uid="+НастройкиИнтеграции.ИмяПользователя+";"+
      			"pwd="+НастройкиИнтеграции.ПарольПользователя+";"+
      			"database="+НастройкиИнтеграции.ИмяБазыДанных+";";
      
      		//Открытие соединение
      		Соединение.Open();
      		Команда.ActiveConnection = Соединение;
      	Исключение
      		ДобавитьТекстОшибки(ИнформацияОбОшибке());
      		Возврат;
      	КонецПопытки;
      
      
  14. ищущий правду. говорит:

    Аккуратно написанный хелп. Инструкция /памятка.Большое спасибо.

  15. Аноним говорит:

    Спасибо !

  16. Никита говорит:

    Здравствуйте, а как вот добавить записи из таблицы sql в 1с, в справочник допустим?

    • Никита говорит:

      Даже не так, загрузить уже имеющиеся данные из таблицы sql в 1с

    • Виталий Онянов говорит:

      Вместо строчки

      Сообщить("[C_DateTime]="+Дата(Выборка.Fields("C_DateTime").Value)
      +", [C_Char]="+СокрЛП(Выборка.Fields("C_Char").Value)
      +", [C_Numeric]="+Число(Выборка.Fields("C_Numeric").Value));
      

      Необходимо описать логику добавления данных в 1С. Здесь уже все зависит от ваших требований и квалификации.

  17. Роман говорит:

    Приветствую. А можно ли подключиться в локальной сети без Com вообще?
    Com только для винды. А там все на Линуксах. И без web сервисов всяких, по локалке, знаешь имя сервера имя базы, логин и пароль

  18. Андрей говорит:

    Скрипт:
    USE [master]
    GO
    ALTER DATABASE [UH_Test_DB] SET OFFLINE
    GO

    Возвращает ошибку:
    {Справочник.ИнформационныеБазы.Форма.ФормаСписка.Форма(80)}: Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft OLE DB Provider for ODBC Drivers): [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘GO’.

    При этом коннект успешно
    Может ли ODBC выполнять подобные скрипты ?

    • Виталий Онянов говорит:

      Разницы не должно быть.
      Проверьте, выполняется ли этот скрипт в SQL Management Studio?

      • Аноним говорит:

        Да в SQL MS скрипт выполняется.
        Кстати и скрит на выборку (в контексте 1С, через ODBC драйвер) тоже выполняется успешно.

        • Аноним говорит:

          USE [master]
          ALTER DATABASE [UH_Test_DB] SET OFFLINE

          Так сработало. Спасибо)

          • Виталий Онянов говорит:

            Спасибо, что поделились решением.

  19. Аноним говорит:

    Может кому пригодится.
    Кроме параметра-Соединение.CommandTimeout
    Есть еще параметр-Команда.CommandTimeout

  20. EkimovDA говорит:

    Большое спасибо за информацию

  21. Аноним говорит:

    Использовать Формат() в данном случае – порочная практика. Гораздо лучше и правильнее использовать XMLСтрока().

    • Виталий Онянов говорит:

      Наверное, вы правы, спасибо. Только не могу придумать, чем все же плохо использование Формат() ?

  22. Крис говорит:

    Здравствуйте! А как удалить таблицу при помощи TRUNCATE? Никак не получается!

  23. Sergey Seredkin (SPS) говорит:

    Надо помнить что SQL поддерживает примитивные данные строка число дата и тд. Поэтому при связки с 1С придётся всегда применять что то типа арбуз=Справочник.Номенклатур.НайтиПоНаименованию(«арбуз») . А затем только создавать документ продаж с этой номенклатурой., так как в ске ключевые данные справочников ., документов это ссылки.

  24. Ирина говорит:

    Здравствуйте. Спасибо за полезную статью. Но никак не могу разобраться с кодировкой. При чтении из базы SQL символы на кириллице отображаются как ?????, Что нужно сделать?

  25. Аноним говорит:

    Добавте пример — закинут в таблицу картинку или файл, чтобы передаваемые значения были не в строке а через параметры.

Добавить комментарий для Аноним Отменить ответ

Ваш адрес email не будет опубликован. Обязательные поля помечены *