наверх
Свободные IT публикации
быстро и доступно
логин:
пароль:

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

  • Как получить ссылку 1С по типу, виду и строковому представлению внутреннего ID?
  • Как получить строковоу представление внутреннего ID ссылки 1С?
  • Как выгрузить ADO RecordSet в таблицу значений 1С?
  • Как выполнить запрос SQL?
  • Как выполнить SQL-запрос и получить результат в виде таблицы значений 1С?
  • Как преобразовать дату в формат SQL?
  • Как выполнить произвольную команду SQL?
  • Как сформировать строку фильтра для запроса SQL из списка значений 1С?
  • Как получить ссылку 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;
    		КонецЕсли;
    	КонецЦикла;
    
    	Если Фильтр = "" И ПустоеЗначение <> Неопределено Тогда
    		Фильтр = ПустоеЗначение;
    	КонецЕсли; 
    
    	Возврат Фильтр;
    КонецФункции