В данной статье приведены экземпляры кода на языке 1С, которые позволяют подключаться к базе данных на SQL-сервере, получать из нее данные, а также записывать новые и обновлять существующие записи в таблицах этой базы данных. Код протестирован на платформе 8.3.5, а также на версиях SQL Server 2008 R2 и SQL Server 2012. Операционная система Windows Server 2008 и Windows Server 2012. Есть предположение, что указанный код будет работать и на других версиях 1С, SQL и Windows.
0. Оглавление
- Постановка задачи
- Подключение к SQL-серверу
- Добавление записей в таблицу
- Обновление записей в таблице
- Просмотр выборки из таблицы
- Удаление записей из таблицы
- Закрытие соединения
- Общий текст процедуры
1. Постановка задачи
Итак, имеется SQL-сервер с адресом WIN2012\TEST_SQL. Для подключения используется имя входа SQL-сервера UserDB с паролем Pass123456.
На сервере размещена база данных с именем TestDB. В базе имеется таблица с именем Table_1, в которой 3 столбца:
- C_DateTime, с типом datetime
- C_Char, с типом char(50)
- C_Numeric, с типом numeric(18,0)
Изначально таблица пустая:
У пользователя UserDB есть права на чтение и запись в базу данных TestDB. В данном случае пользователь является владельцем указанной базы данных.
Требуется научиться записывать в базу данных и читать из базы данных из 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С:Предприятии:
Теперь, в общем то, можно выполнять любой запрос SQL через данный COM-объект. Посмотрим как это делать на самых распространенных примерах.
3. Добавление записей в таблицу
Теперь добавим новую строку в нашу пока еще пустую таблицу. Для этого необходимо выполнить код:
///////////////////////////////////////// //Добавляем запись в таблицу ТекДата = ТекущаяДата(); ТекСтрока = "Запись строки"; ТекЧисло = 123456; //Приобразуем данные Если НЕ ЗначениеЗаполнено(ТекДата) Тогда //Для пустой даты необходимо отправлять 01.01.1753 ТекДата = Дата(1753,1,1); КонецЕсли; ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы ТекстТекущейИнструкции = "INSERT INTO "+ТаблицаSQL+" |([C_DateTime], [C_Char], [C_Numeric]) |VALUES ('"+ТекДата+"', N'"+ТекСтрока+"', '"+ТекЧисло+"')"; Попытка Соединение.Execute(ТекстТекущейИнструкции,,128); Сообщить("Запись добавлена!"); Исключение Сообщить(ОписаниеОшибки()); КонецПопытки;
Смотрим результат и видим, что строка появилась в таблице:
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); Сообщить("Запись обновлена!"); Исключение Сообщить(ОписаниеОшибки()); КонецПопытки;
Проверяем, что запись обновилась:
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(); КонецЦикла; КонецЕсли; Исключение Сообщить(ОписаниеОшибки()); КонецПопытки;
Результат в предприятии:
6. Удаление записей из таблицы
Для удаления всех записей в таблице со значением C_Numeric=123456 выполняем код:
///////////////////////////////////////// //Удаляем записи в таблице с C_Numeric = 123456 ТекЧисло = 123456; ТекЧисло = Формат(ТекЧисло,"ЧГ="); //Удаляем пробелы ТекстТекущейИнструкции = "DELETE FROM "+ТаблицаSQL+" |WHERE [C_Numeric]='"+ТекЧисло+"'"; Попытка Соединение.Execute(ТекстТекущейИнструкции,,128); Сообщить("Запись удалена!"); Исключение Сообщить(ОписаниеОшибки()); КонецПопытки;
Проверяем, что записей в таблице не осталось:
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С:Предприятии:
Дуже корисно. Дякую.
Спасибо, очень помогли!
Отличная интересная статья о том как можно подключить свою страницу в интернете к 1с предприятию — очень удобно управлять торговлей.
Utf-8 не понимает как исправить?
записивает как ??????
как можно таблицу в SQL загрузить сразу в массив или в тз? Вижу только выборку
Спасибо тебе добрый человек
Очень полезная инфа. Огромное спасибо
отличная статья
Добрый день. А как подключиться к SQL, если он на другом компе в локальной сети.
Спасибо.
Здравствуйте.
Все то же самое, только в свойствах подключения (ИмяСервераSQL) указываете адрес компьютера с SQL Server.
Большое спсб
Спасибо
Приветствую!
Подскажите пожалуйста насчет строки «driver={SQL Server};»
Я так понимаю нужно указать правильный драйвер для подключения к серверу….
Как узнать что за драйвер там стоит? Или этот драйвер надо установить и на компе где 1С установлен? SQL Server у меня на другом компе…
Да, драйвер должен быть на компьютере, откуда вы подключаетесь. Если код выполняется на сервере — то на сервере с кластером серверов 1С, если на клиенте, то, соответственно, на клиенте.
В системе Windows для SQL Server уже есть все необходимые драйвера. Специально ничего ставить не надо.
Посмотреть версию драйвера можно в программе «Источники данных ODBC», поищите поиском в меню «Пуск».
С подключением и драйвером разобрался… Все ок.
Подскажите пожалуйста, как добавить в таблицу SQL сразу все записи из выборки ?
Т.е сейчас у меня так:
РезЗапроса = Запрос.Выполнить().Выбрать();
Пока РезЗапроса.Следующий() Цикл
ТекстТекущейИнструкции =
«INSERT INTO «+ТаблицаSQL+»
|([StockAdministration], [ItemCode])
|VALUES (N'»+РезЗапроса.Код+»‘, ‘»+РезЗапроса.Сум+»‘, )»;
Попытка
Соединение.Execute(ТекстТекущейИнструкции,,128);
//Сообщить(«Запись добавлена!»);
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
КонецЦикла;
Возможно ли оптимизировать?
Да, конечно, можно сделать так:
Добрый день, Виталий! Большое спасибо за статью, выручала раньше, но тут случилась проруха — а именно база MySQL на Unix серевере((( Какого провайдера нужно указывать в таком случае и как посмотреть на Unix серевере, какие драйвера установлены и используются?
Здравствуйте. Для MySQL используется вот этот драйвер (нужной версии).
Пример кода:
Аккуратно написанный хелп. Инструкция /памятка.Большое спасибо.
Спасибо !
Здравствуйте, а как вот добавить записи из таблицы sql в 1с, в справочник допустим?
Даже не так, загрузить уже имеющиеся данные из таблицы sql в 1с
Вместо строчки
Необходимо описать логику добавления данных в 1С. Здесь уже все зависит от ваших требований и квалификации.
Приветствую. А можно ли подключиться в локальной сети без Com вообще?
Com только для винды. А там все на Линуксах. И без web сервисов всяких, по локалке, знаешь имя сервера имя базы, логин и пароль
Насколько я знаю, Microsoft® ODBC Driver есть и для Linux-систем. Вот, например: https://www.microsoft.com/en-us/download/details.aspx?id=36437
Скрипт:
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
Так сработало. Спасибо)
Спасибо, что поделились решением.
Может кому пригодится.
Кроме параметра-Соединение.CommandTimeout
Есть еще параметр-Команда.CommandTimeout
Большое спасибо за информацию
Использовать Формат() в данном случае – порочная практика. Гораздо лучше и правильнее использовать XMLСтрока().
Наверное, вы правы, спасибо. Только не могу придумать, чем все же плохо использование Формат() ?
Здравствуйте! А как удалить таблицу при помощи TRUNCATE? Никак не получается!
Покажите ваш итоговый запрос.
Надо помнить что SQL поддерживает примитивные данные строка число дата и тд. Поэтому при связки с 1С придётся всегда применять что то типа арбуз=Справочник.Номенклатур.НайтиПоНаименованию(«арбуз») . А затем только создавать документ продаж с этой номенклатурой., так как в ске ключевые данные справочников ., документов это ссылки.
Здравствуйте. Спасибо за полезную статью. Но никак не могу разобраться с кодировкой. При чтении из базы SQL символы на кириллице отображаются как ?????, Что нужно сделать?
Добавте пример — закинут в таблицу картинку или файл, чтобы передаваемые значения были не в строке а через параметры.
Хочу отметить одну не очень важную вещь. Когда делаете манипуляции с базой и через параметры пример ниже то не забываем очищать параметры иначе не чего не получится.
ПРИМЕР как можно еще делать Только часть условия:
//Инициализация глабальных переменных
ИмяСервераSQL = «Имя сервера»;
ПользовательSQL = «пользователь базы с правами»;
ПарольSQL = «Пароль»;
БазаДанныхSQL = «Имя базы данных»;
ТаблицаSQL = «Склад»;
Код = СокрЛП(Код); //Удаляем пробелы
/////////////////////////////////////////
// Подключение к SQL-серверу
Попытка
Соединение = Новый COMОбъект(«ADODB.Connection»);
Команда = Новый COMОбъект(«ADODB.Command»);
Выборка = Новый COMОбъект(«ADODB.RecordSet»);
//Соединение = Новый COMОбъект(«ADODB.Connection»);
Соединение.ConnectionString =
«driver={SQL Server};» +
«server=» + ИмяСервераSQL + «;» +
«uid=» + ПользовательSQL + «;» +
«pwd=» + ПарольSQL + «;» +
«database=» + БазаДанныхSQL + «;»;
Соединение.ConnectionTimeout = 30;
Соединение.CommandTimeout = 600;
Соединение.Open();
Команда.ActiveConnection = Соединение;
Исключение
Сообщить(«Ошибка подключения к базе данных » + БазаДанныхSQL + «: » + ОписаниеОшибки());
Возврат;
КонецПопытки;
Попытка
// Проверяем существование строки
ТекстИнструкции = «SELECT COUNT(*) FROM » + ТаблицаSQL + » WHERE [Код] = ?»;
Команда.CommandText = ТекстИнструкции;
Команда.CommandType = 1; // adCmdText
// Удаление параметров, если они уже существуют
Пока Команда.Parameters.Count > 0 Цикл
Команда.Parameters.Delete(0);
КонецЦикла;
// Передача параметра запроса
Команда.Parameters.Append(Команда.CreateParameter(«Код», 202, 1, 9, Код)); // adVarWChar
// Выполняем запрос для проверки существования строки
Выборка = Команда.Execute();
Если Выборка.Fields(0).Value > 0 Тогда
// тут часть кода когда нашли уже существующию строку и будем обновлять.
Иначе
// Строка не существует, выполняем добавление
ТекстИнструкции = «INSERT INTO » + ТаблицаSQL + » ([Код], [НаименованиеСклада], [Адрес]) VALUES (?,?,?)»;
Команда.CommandText = ТекстИнструкции;
// Передача параметров запроса
Команда.CommandType = 1; // adCmdText
// Удаление параметров, если они уже существуют иначе подтянутся старые
//параметры
Пока Команда.Parameters.Count > 0 Цикл
Команда.Parameters.Delete(0);
КонецЦикла;
// Передача параметра запроса
Команда.Parameters.Append(Команда.CreateParameter(«Код», 202, 1, 9, Код)); // adVarWChar
Команда.Parameters.Append(Команда.CreateParameter(«НаименованиеСклада», 202, 1, 25, Наименование)); // adVarWChar
Команда.Parameters.Append(Команда.CreateParameter(«Адрес», 202, 1, 25, Адрес)); // adVarWChar
// Выполнение команды
Команда.Execute();
// Закрытие соединения
Соединение.Close();
КонецЕсли;
Исключение
Сообщить(«Ошибка: » + ОписаниеОшибки());
Если Соединение Неопределено Тогда
Соединение.Close();
КонецЕсли;
КонецПопытки;
КонецПроцедуры
Народ, подскажите! Дано 1с 7.7 на SQL, через менеджер sql могу войти во все базы. но 1с пишет ошибку, и предлагает настроить подключение заново. Админа этого сервера давно нету — как восстановить настройки?! Спасибо!!!