Функции для работы с SQL

Как получить ссылку 1С по типу, виду и строковому представлению внутреннего ID?

//Функция возвращает объект ссылочного типа 1С по типу, виду и строковому представлению внутреннего ID
//строковое представление внутреннего ID может быть получено с помощью SQL-Функции CONVERT:
//SELECT
//  convert(char(34), Справочники_Валюты._IDRRef,1) AS Ссылка,
//  Справочники_Валюты._Marked&1 AS ПометкаУдаления,
//  Справочники_Валюты._IsMetadata&1 AS Предопределенный,
//  Справочники_Валюты._Code AS Код,
//  Справочники_Валюты._Description AS Наименование,
//  Справочники_Валюты._Fld808 AS НаименованиеПолное,
//  Справочники_Валюты._Fld809 AS ПараметрыПрописиНаРусском
//FROM
//  ZuP.dbo._Reference19 Справочники_Валюты (nolock)

Функция ЗначИзSQL(Тип, Вид, СсылкаSQL) Экспорт
	Перем Ссылка_;
	Попытка
		Если Тип = "Документ" Тогда 
			Ссылка_ = Документы[Вид].ПустаяСсылка();
		ИначеЕсли	Тип = "Справочник" Тогда
			Ссылка_ = Справочники[Вид].ПустаяСсылка();
		ИначеЕсли	Тип = "Перечисление" Тогда 
			Ссылка_ = Перечисления[Вид].ПустаяСсылка();
		ИначеЕсли	Тип = "ПланОбмена" Тогда
			Ссылка_ = ПланыОбмена[Вид].ПустаяСсылка();
		Иначе	
			Возврат Неопределено;
		КонецЕсли;
		Если Ссылка_ <> Неопределено Тогда
			Строка_ = ЗначениеВСтрокуВнутр( Ссылка_ );
			Строка_ = СтрЗаменить(Строка_,"00000000000000000000000000000000",Прав(СокрЛП(СсылкаSQL),32));
			Ссылка_ = ЗначениеИзСтрокиВнутр(Строка_);
		КонецЕсли;
	Исключение
		Ссылка_ = Неопределено;
	КонецПопытки;
    Возврат	Ссылка_;	
КонецФункции

Как получить строковоу представление внутреннего ID ссылки 1С?

//Функция возвращает шестнадцатиричное представление внутреннего ID объекта1С по объекту ссылочного типа 1С
//(Использовать в SQL без кавычек)
Функция ЗначВSQL(Ссылка1с) Экспорт
	SQL_ID = "0x00000000000000000000000000000000";
	Если Ссылка1с <> Неопределено Тогда
		ВнСТР = ЗначениеВСтрокуВнутр(Ссылка1с);
		SQL_ID = "0x"+ВРЕГ(Сред(ВнСТР, Найти(ВнСТР, ":") + 1, 32));
	КонецЕсли;
    Возврат	SQL_ID;		
КонецФункции

Как выгрузить ADO RecordSet в таблицу значений 1С?

//Функция преобразовывает ADO RecordSet в таблицу значений 1С
//RS - набор данных(RecordSet)
//ТЗ - таблица значений, в которую необходимо выгрузить RS
//РасшифровкаСсылок - ТЗ вида {Имя,Тип,Вид},
// где Имя (стр) - имя поля, содержащего ссылку
//     Тип (стр) - тип объекта ("Справочник","Документ" и т.д.)
//     Вид (стр) - вид объекта ("Склад","РеализацияТиУ" и т.п.)
//ЭлементФормы - соответствующее табличное поле на форме
Процедура ВыгрузитьRS_В_ТЗ(RS, ТЗ, РасшифровкаСсылок=Неопределено, ЭлементФормы = Неопределено) Экспорт
	Перем СчКолонок, РасшифровкаКолонок ;
	
	Если  RS = Неопределено Тогда
		#ЕСЛИ КЛИЕНТ ТОГДА
			Сообщить("Набор данных пуст!");
		#КОНЕЦЕСЛИ	
		Возврат;
	КонецЕсли;
	
	Если ТипЗнч(ТЗ)<> Тип("ТаблицаЗначений") Тогда
		#ЕСЛИ КЛИЕНТ ТОГДА
			Сообщить("Не определена таблица результата!"); 
		#КОНЕЦЕСЛИ
		Возврат;
	КонецЕсли;
	Если  РасшифровкаСсылок <> Неопределено и ТипЗнч(РасшифровкаСсылок)<> Тип("ТаблицаЗначений")  Тогда
		РасшифровкаСсылок = Неопределено;
	Иначе 
		РасшифровкаКолонок = Новый ТаблицаЗначений;
		РасшифровкаКолонок.Колонки.Добавить("Номер");
		РасшифровкаКолонок.Колонки.Добавить("Тип");
		РасшифровкаКолонок.Колонки.Добавить("Вид");
	КонецЕсли;
	  
	СчКолонок = (RS.Fields.Count - 1);
		
	Если НЕ ТЗ.Колонки.Количество() Тогда
		НаборКолонок = ТЗ.Колонки;
		НаборКолонок.Очистить();
		Для Сч = 0 По СчКолонок Цикл
			ТекИмя = RS.Fields(Сч).Name;
			НаборКолонок.Добавить(ТекИмя, , ТекИмя, 15);
			Если  РасшифровкаСсылок <> Неопределено  Тогда
				ТекСоответствие = РасшифровкаСсылок.Найти(ТекИмя,"Имя");
				Если ТекСоответствие <> Неопределено Тогда 
					 СтрСоотв = РасшифровкаКолонок.Добавить();
					 СтрСоотв.Номер = Сч;
					 СтрСоотв.Тип = ТекСоответствие.Тип;
					 СтрСоотв.Вид = ТекСоответствие.Вид;
				КонецЕсли;	
			КонецЕсли;		
		КонецЦикла;
	Иначе
		Если  РасшифровкаСсылок <> Неопределено  Тогда
			Для Сч = 0 По СчКолонок Цикл
			    ТекИмя = RS.Fields(Сч).Name;
				ТекСоответствие = РасшифровкаСсылок.Найти(ТекИмя,"Имя");
				Если ТекСоответствие <> Неопределено Тогда 
					 СтрСоотв = РасшифровкаКолонок.Добавить();
					 СтрСоотв.Номер = Сч;
					 СтрСоотв.Тип = ТекСоответствие.Тип;
					 СтрСоотв.Вид = ТекСоответствие.Вид;
				КонецЕсли;	
			КонецЦикла;
        КонецЕсли;	
	КонецЕсли;
	
	Если (НЕ ((RS.EOF()) ИЛИ (RS.BOF()))) Тогда
		csaМассив = RS.GetRows();
		Для Сч = 0 По csaМассив.GetLength(0) - 1 Цикл
			СтрокаТЗ = ТЗ.Добавить();
			Для ТекСч = 0 По СчКолонок Цикл
				ТекЗначение = csaМассив.GetValue(Сч, ТекСч);
				
				Если  РасшифровкаСсылок <> Неопределено  Тогда
					СтрРасшифровки = РасшифровкаСсылок.Найти(ТекСч,"Номер");
					Если СтрРасшифровки<>Неопределено Тогда 
						ТекЗначение = ЗначИзSQL(СтрРасшифровки.Тип, СтрРасшифровки.Вид, ТекЗначение);
					КонецЕсли;	
				КонецЕсли;	
	
				СтрокаТЗ[ТекСч] = ТекЗначение;
			КонецЦикла;
		КонецЦикла;
	КонецЕсли;
	
	Если ЭлементФормы <> Неопределено Тогда
		ЭлементФормы.СоздатьКолонки();			
	КонецЕсли;	
КонецПроцедуры

Как выполнить запрос SQL?

//Функция выполняет SQL-запрос и возвращает результирующий набор данных в виде объекта ADO RecordSet
Функция ВернутьRecordSet(ТекстЗапроса, СтруктураПодключения, Ошибка = "") Экспорт
	Перем Connection,Command,RecordSet;

	RecordSet = Неопределено;
	СтрокаПодключения = "Provider=SQLOLEDB.1;
	|Password="+СтруктураПодключения.ПарольПользователяSQL+";
	|User ID=" + СтруктураПодключения.ПользовательSQL + ";
	|Persist Security Info=True;
	|Initial Catalog="+СтруктураПодключения.ИмяБазыSQL+";
	|Data Source="+СтруктураПодключения.СерверБазыSQL;
	Попытка
		Connection = Новый COMОбъект("ADODB.Connection");
		Connection.Open(СтрокаПодключения);
		
		RecordSet = Новый COMОбъект("ADODB.Recordset");
		RecordSet.Open(ТекстЗапроса, Connection);
		Пока (НЕ RecordSet.State) Цикл
			RecordSet = RecordSet.NextRecordset();
		КонецЦикла;
	Исключение
		Ошибка = ОписаниеОшибки();
		RecordSet = Неопределено;
	КонецПопытки;
	Возврат RecordSet;
КонецФункции

Как выполнить SQL-запрос и получить результат в виде таблицы значений 1С?

//Функция выполняет SQL-запрос и возвращает результат в виде таблицы значений 1С
//Колонки таблицы значений соответствуют обозначениям полей в запросе
Функция СоздатьЗаполнитьТаблицуРезультата(ТекстЗапроса, БД=Неопределено,СерверБазыSQL="",ИмяБазыSQL="",ИмяПользователяSQL="",ПарольПользователяSQL="")Экспорт
	Перем ТаблицаРезультата;
	ТаблицаРезультата = Новый ТаблицаЗначений;
	
	СтрокаПодключения = ВернутьСтрокуПодключения(БД,СерверБазыSQL,ИмяБазыSQL,ИмяПользователяSQL,ПарольПользователяSQL);
	
	Попытка
		Connection = Новый COMОбъект("ADODB.Connection");
		Connection.Open(СтрокаПодключения);
		
		ADOCommand = Новый COMОбъект("ADODB.Command");
		ADOCommand.ActiveConnection = Connection;
		ADOCommand.CommandTimeout = 300;
		ADOCommand.CommandText = ТекстЗапроса;
	Исключение
		Ошибка = ОписаниеОшибки();
		#ЕСЛИ КЛИЕНТ ТОГДА
			Сообщить("Не удалось подключить ADO: " + Ошибка);
		#КОНЕЦЕСЛИ
	КонецПопытки;
	
	Попытка
		RecordSet = ADOCommand.Execute();
	Исключение
		ФлЕстьОшибкиОбработки = Истина;
		Возврат ТаблицаРезультата;
	КонецПопытки;
	
	Пока (НЕ RecordSet.State) Цикл
		RecordSet = RecordSet.NextRecordset();
	КонецЦикла;
	
	Если (НЕ ((RecordSet.EOF()) ИЛИ (RecordSet.BOF()))) Тогда
		//Выгрузка результата запроса в COMSafeArray
		csaМассивРезультата = RecordSet.GetRows();
	Иначе 
		Возврат ТаблицаРезультата;
	КонецЕсли;	
	
	//Заполнение колонок таблицы рез. по полям результата запроса
	СчКолонок = (RecordSet.Fields.Count - 1);
	НаборКолонок = ТаблицаРезультата.Колонки;
	Для Сч = 0 По СчКолонок Цикл
		ТекИмя = RecordSet.Fields(Сч).Name;
		НаборКолонок.Добавить(ТекИмя);	
	КонецЦикла;
	
	//Выгрузка результата запроса в таблицу результата
	Для СчСтрок = 0 По csaМассивРезультата.GetLength(0) - 1 Цикл
		СтрокаДанных = ТаблицаРезультата.Добавить();
		Для СчПолей = 0 По СчКолонок Цикл
			СтрокаДанных[СчПолей] = csaМассивРезультата.GetValue(СчСтрок, СчПолей);	 
		КонецЦикла;
	КонецЦикла;	
	
	Попытка
		RecordSet.Close();
		RecordSet = "";
	Исключение
	КонецПопытки;
	
	Возврат ТаблицаРезультата;	
КонецФункции

Как преобразовать дату в формат SQL?

//Функция формирует строковое представление даты для использования в тексте SQL-запроса
Функция ДатаВSQL(Дата) Экспорт 	
	Возврат Формат(Дата,Формат("ДФ='yyyy-MM-dd HH:mm:ss'"));
КонецФункции

Как выполнить произвольную команду SQL?

//Функция выполнения произвольной команды SQL, включая запросы изменения данных
Функция ВыполнитьКомандуSQL(ТекстКоманды, ADOCommand) Экспорт
	ADOCommand.CommandText = ТекстКоманды;
	Попытка
		ADOCommand.Execute();
		Возврат Истина;
	Исключение
		Возврат Ложь;
	КонецПопытки;
КонецФункции

Как сформировать строку фильтра для запроса SQL из списка значений 1С?

//Функция формирует строковое представление списка значений отбора для использования в условии SQL-запроса вида:
// WHERE _IDRREF IN ("+СформироватьФильтрSQL(СписокСсылок)+")
Функция  СформироватьФильтрSQL(МассивЗначений,ФлПреобразовыватьВSQL=Истина,ФлТипСтрока=Ложь,ПустоеЗначение = Неопределено) Экспорт
	Перем Фильтр,Сч;
	Фильтр = "";
	Сч = 0;
	Для Каждого ТекЗначение из МассивЗначений Цикл 
		Сч = Сч + 1;
		ТекЭлементФильтра=?(ФлТипСтрока,"'"+ТекЗначение+"'",?(ФлПреобразовыватьВSQL,ЗначВSQL(ТекЗначение),СокрЛП(ТекЗначение)));
		Фильтр = Фильтр + ?(Фильтр = "","",",") + ТекЭлементФильтра;
		Если Сч = 10 Тогда 
			Фильтр = Фильтр +"
			|";
			Сч = 0;
		КонецЕсли;
	КонецЦикла;

	Если Фильтр = "" И ПустоеЗначение <> Неопределено Тогда
		Фильтр = ПустоеЗначение;
	КонецЕсли; 

	Возврат Фильтр;
КонецФункции