gifts2017

Функции для хранения рисунков в отдельной базе MSSQL в varbinary с помощью ADODB

Опубликовал Ivon (Ivon) в раздел Программирование - Практика программирования

В статье приведен набор функций для хранения файлов в отдельной базе MSSQL.

В одной из своих статей я рассказывал, как можно хранить рисунки в отдельной базе данных MSSQL с помощью ADO.Net и Base64Строка (ссылка в профиле). Предыдущий способ имел как плюс (для работы с полученным из базы файлом не требовалось предварительно сохранять данные во временный файл), так и минус (объем данных в базе = объем данных файла * 3).

Из-за увеличенного в 3 раза объема данных страдала и скорость чтения/записи. Поэтому я продолжил свою работу над данным вопросом и через некоторое время набор функций был переработан в сторону хранения данных в поле varbinary. Плюсы: объем данных в базе = объему данных в файле, данные из varbinary можно считывать и записывать и другими программами. Минус - без временного файла тут никак.

В статье я не буду прикладывать обработки и скрипты, все тексты будут представлены явно.

Для начала нам необходима правильная таблица в БД. Вот скрипт:

 

CREATE TABLE [dbo].[FileStore](
	[GUID] [nvarchar](120) NOT NULL,
	[FileName] [nvarchar](256) NOT NULL,
	[Data] [varbinary](max) NOT NULL,
	[UserName] [nvarchar](100) NOT NULL,
	[DateTime] [datetime] NOT NULL
) ON [PRIMARY]

 

Назначение колонок:

GUID - ЗначениеВСтрокуВнутр объекта, к которому привязан файл;

FileName - имя файла;

Data - данные файла;

UserName - текстовое имя пользователя, положившего файл;

DateTime - дата и время, когда файл положили в базу.

Все функции в 1С я положил в модуль РаботаСSQL и вызываю их соответственно из модуля.

 

Функция ПодключитьсяКБазеSQL(АдресСервера = "192.168.0.1", ИмяБД = "AdvancedStore", Логин = "sa", Пароль = "*****", ИмяТаблицы = "FileStore") Экспорт
Con = Новый COMОбъект("ADODB.Connection");
СтрокаПодключения = "Provider=SQLOLEDB; Data Source=" + АдресСервера + ";Initial Catalog=" + ИмяБД + ";Persist Security Info=True;User ID=" + Логин + ";Password=" + Пароль;
Попытка
Con.Open(СтрокаПодключения);
//Сообщить("Подключение прошло успешно");
Исключение
Сообщить(ОписаниеОшибки());
Con = Неопределено;
КонецПопытки;
Возврат Новый
Структура("Подключение, Таблица", Con, ИмяТаблицы);
КонецФункции

Параметры для функции:

АдресСервера - адрес сервера MSSQL;

ИмяБД - имя базы данных в MSSQL;

Логин  - учетная запись для доступа к таблице, где будут храниться файлы;

Пароль - пароль для данной учетной записи;

ИмяТаблицы - имя таблицы в БД, куда будут складываться файлы.

Функция возвращает:

структуру ("Подключение, Таблица"), если подключение прошло успешно;

Неопределено, если подключиться к серверу не удалось.

 

Функция ПоложитьФайлВБазуSQL(Подключение, Ссылка, ИмяФайла, Замещать = Истина) Экспорт
Если Подключение = Неопределено Тогда
Предупреждение("Вначале необходимо подключиться к серверу SQL!");
Возврат Ложь;
КонецЕсли;
ИмяТаблицы = Подключение.Таблица;
ИДСсылки = ЗначениеВСтрокуВнутр(Ссылка);
Stream = Новый COMОбъект("ADODB.Stream");
Stream.Type = 1;
Stream.Open();
Stream.LoadFromFile(ИмяФайла);
RecordSet = Новый COMОбъект("ADODB.RecordSet");
RecordSet.CursorLocation = 3;
RecordSet.LockType = 2;
Имяр = РазобратьСтроку(ИмяФайла, "\");
Имяр = Имяр[Имяр.Количество() - 1].Значение;
Запрос = "select [GUID], [Data], [FileName], [UserName], [DateTime] from [" + ИмяТаблицы + "] where [GUID]='" + ИДСсылки + "' AND [FileName]='" + Имяр + "'";
Попытка
RecordSet.Open(Запрос, Подключение.Подключение);
Исключение
Сообщить(ОписаниеОшибки());
Возврат Ложь;
КонецПопытки;
Если
RecordSet.RecordCount > 0 И Замещать = Ложь Тогда
RecordSet.AddNew();
Имяр = Формат(ТекущаяДата(), "ДФ='dd.MM.yyyy hh-mm-ss'") + " " + Имяр;
ИначеЕсли
RecordSet.RecordCount = 0 Тогда
RecordSet.AddNew();
Иначе
RecordSet.MoveFirst();
КонецЕсли;
RecordSet.Fields("Data").Value = Stream.Read(-1);
RecordSet.Fields("GUID").Value = ИДСсылки;
RecordSet.Fields("FileName").Value = Имяр;
RecordSet.Fields("UserName").Value = СокрЛП(Строка(глТекущийПользователь));
RecordSet.Fields("DateTime").Value = ТекущаяДата();
RecordSet.Update();
Stream.Close();
RecordSet.Close();
Возврат Истина;
КонецФункции

Данная функция принимает следующие параметры:

Подключение - структура подключения, полученная из функции ПодключитьсяКБазеSQL;

Ссылка - ссылка на объект 1С, к которому прикрепляется файл;

ИмяФайла - полное имя файла с путем, который заливается в базу;

Замещать - Истина или Ложь. Сравнивается имя файла и GUID. Если такая запись уже есть, то:

при Истина - данные файла замещаются; при Ложь - создается новый файл, при этом перед именем файла вставляется текущие дата и время.

Функция возвращает:

Истина - файл успешно добавлен в базу;

Ложь - файл не удалось добавить в БД.

 

Функция ЗаменитьОбъектФайлаВБазеSQL(Подключение, ИмяФайла, Ссылка, Ссылка2) Экспорт
Если Подключение = Неопределено Тогда
Предупреждение("Вначале необходимо подключиться к серверу SQL!");
Возврат Ложь;
КонецЕсли;
ИДСсылки = ЗначениеВСтрокуВнутр(Ссылка);
ИДСсылки2 = ЗначениеВСтрокуВнутр(Ссылка2);
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Подключение.Подключение;
Command.CommandType = 1;
Command.CommandText = "UPDATE [" + Подключение.Таблица + "] SET GUID = '" + ИДСсылки2 + "' WHERE GUID = '" + ИДСсылки + "' AND FileName = '" + ИмяФайла + "'";
Попытка
Command.Execute();
Исключение
Сообщить(ОписаниеОшибки());
Возврат Ложь;
КонецПопытки;
Возврат Истина;
КонецФункции

Данная функция нужна для перепривязки файла с обного объекта 1С к другому. просто заменяет идентификаторы в базе.

 

Параметры:

Подключение - структура подключения, полученная из функции ПодключитьсяКБазеSQL;

ИмяФайла - имя файла в формате имя.расширение;

Ссылка - ссылка на объект 1С, от которого открепляется файл;

Ссылка2 - ссылка на объект 1С, к которому прикрепляется файл.

Функция возвращает Истина при успехе операции и ложь при ошибке перепривязки.

 

 Функция ПолучитьФайлИзБазыSQL(Подключение, Ссылка, ИмяФайла) Экспорт
Если Подключение = Неопределено Тогда
Предупреждение("Вначале необходимо подключиться к серверу SQL!");
Возврат Неопределено;
КонецЕсли;
Файл = Неопределено;
ИДСсылки = ЗначениеВСтрокуВнутр(Ссылка);
Stream = Новый COMОбъект("ADODB.Stream");
Stream.Type = 1;
Stream.Open();
RecordSet = Новый COMОбъект("ADODB.RecordSet");
RecordSet.CursorLocation = 3;
RecordSet.LockType = 2;
Запрос = "SELECT Data FROM [" + Подключение.Таблица + "] WHERE GUID = '" + ИДСсылки + "' AND FileName = '" + ИмяФайла + "'";
RecordSet.Open(Запрос, Подключение.Подключение);
RecordSet.MoveFirst();
Stream.Write(RecordSet.Fields("Data").Value);
фрис = КаталогВременныхФайлов() + ИмяФайла;
Stream.SaveToFile(фрис);
Stream.Close();
RecordSet.Close();
Файл = Новый ДвоичныеДанные(фрис);
УдалитьФайлы(фрис);
Возврат Файл;
КонецФункции

Функция получает файл из БД и возвращает объект 1С типа Файл.

 

Параметры:

Подключение - структура подключения, полученная из функции ПодключитьсяКБазеSQL;

Ссылка - ссылка на объект 1С, к которому привязан файл;

ИмяФайла - имя файла в формате имя.расширение.

 

Функция ПолучитьСписокФайловИзБазыSQL(Подключение, Ссылка) Экспорт
Табл = Новый ТаблицаЗначений;
Табл.Колонки.Добавить("Наименование", , , 256);
Если Подключение = Неопределено Тогда
Предупреждение("Вначале необходимо подключиться к серверу SQL!");
Возврат Табл;
КонецЕсли;
Файл = Неопределено;
ИДСсылки = ЗначениеВСтрокуВнутр(Ссылка);
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Подключение.Подключение;
Command.CommandType = 1;
Command.CommandText = "SELECT FileName FROM [" + Подключение.Таблица + "] WHERE GUID = '" + ИДСсылки + "'";
RecordSet = Новый COMОбъект("ADODB.RecordSet");
Попытка
RecordSet = Command.Execute();
Исключение

Сообщить(ОписаниеОшибки());
Возврат
Табл;
КонецПопытки;
Пока RecordSet.EOF() = 0 Цикл
Строка = Табл.Добавить();
Строка.Наименование = СокрЛП(Строка(RecordSet.Fields(0).Value));
RecordSet.MoveNext();
КонецЦикла;
RecordSet.Close();
Возврат Табл;
КонецФункции

Функция получает список файлов для объекта по ссылке и возвращает таблицу с колонкой "Наименование", где хранятся имена файлов для объекта.

 

Параметры:

Подключение - структура подключения, полученная из функции ПодключитьсяКБазеSQL;

Ссылка - ссылка на объект 1С.

Возвращает таблицу значений. В случае ошибки возвращает пустую таблицу.

 

Функция УдалитьФайлИзБазыSQL(Подключение, Ссылка, ИмяФайла) Экспорт
Если Подключение = Неопределено Тогда
Предупреждение("Вначале необходимо подключиться к серверу SQL!");
Возврат Ложь;
КонецЕсли;
Файл = Неопределено;
ИДСсылки = ЗначениеВСтрокуВнутр(Ссылка);
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Подключение.Подключение;
Command.CommandType = 1;
Command.CommandText = "DELETE FROM [" + Подключение.Таблица + "] WHERE GUID = '" + ИДСсылки + "' AND FileName = '" + ИмяФайла + "'";
Попытка
Command.Execute();
Исключение
Сообщить(ОписаниеОшибки());
Возврат Ложь;
КонецПопытки;
Возврат Истина;
КонецФункции

Функция удаляет из БД файл. Возвращает истину при успехе и ложь при неудаче.

Параметры:

Подключение - структура подключения, полученная из функции ПодключитьсяКБазеSQL;

Ссылка - ссылка на объект 1С, к которому привязан файл;

ИмяФайла - имя файла в формате имя.расширение.

 

Функция РазобратьСтроку используется для быстрого получения имени файла из строки с полным именем файла, включающим путь.

 

Функция РазобратьСтроку(_Строка, _Разделитель, _УдалятьПустые = Истина) Экспорт
Результат = Новый СписокЗначений;
= СтрЗаменить(_Строка, _Разделитель, Символы.ПС);
Для Строк = 1 по СтрЧислоСтрок() Цикл
Если СтрДлина(СтрПолучитьСтроку(, Строк)) > 0 ИЛИ не _УдалятьПустые Тогда
Результат.Добавить(СтрПолучитьСтроку(, Строк));
КонецЕсли;
КонецЦикла;

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

 

См. также

PowerTools от 1 000
Подписаться Добавить вознаграждение
Комментарии
1. Дмитрий Дрейцер (MadDAD) 26.10.10 04:30
Решал подобную задачу с помощью 1С++ объекта BinaryData. Было реализовано хранилище обработок с раздачей прав в зависимости от должности и подразделения.
3. Сергей Кучеров (СергейКа) 26.10.10 13:10
Интересно, логотип Linux в заголовке, а речь о MSSQL...
4. Ivon (Ivon) 26.10.10 13:38
(3). Просто прикольный рисунок. Ведь статья о хранении рисунков...
5. Александр Зубцов (iov) 26.10.10 15:29
6. Алексей Соловьев (Silenser) 27.10.10 16:31
Реализовывал некоторое время назад подобный проект. До сих пор используем и не жалуемся.
http://infostart.ru/public/74821/
ПС: Насколько я помню, то T-SQL с курсорами работает не очень шустро, так что быстрее не отбирать и изменять, а удалять и записывать новую запись. Хотя могу и ошибаться.
7. Дмитрий Дрейцер (MadDAD) 28.10.10 09:56
(5) Да, хранение во внешней базе SQL. + история версий с возможностью перехода на предыдущие версии.
8. Александр Зубцов (iov) 28.10.10 12:54
(7) коммерческий продукт? ...
Я это к чему вопрос просто больно интересный с тех пор как увидел систему которая сразу с 3 мя внешними базами очень хотелось бы посмотреть. Есть возможность поделится своим примером?
P.S. интересуют способы которыми решаются задачи
9. Слава (DeepDiver) 28.10.10 13:58
10. Дмитрий Дрейцер (MadDAD) 29.10.10 03:06
(8) Продукт некоммерческий, сложность в отвязывании от существующей системы. Соберусь на днях - выложу в разработках автономный вариант.
11. Дмитрий Дрейцер (MadDAD) 29.10.10 05:55
12. Равиль Бикбаев (BRT) 29.10.10 09:23
Хотелось бы уточнить. Хранение только графических файлов или и любых.
13. Ivon (Ivon) 29.10.10 09:58
(12). Вообще-то любых до 2 гиг (вроде бы ограничение varbinary).
14. Влад Костянецкий (bytecoded) 01.12.11 13:31
Спасибо!

Я бы добавил проверку на количество записей в наборе — если он пуст, MoveFirst() приведёт к ошибке.
	RecordSet.Open(Запрос, Подключение.Подключение);
	RecordSet.MoveFirst();

Например, через метод RecordCount():
		Если RecordSet.RecordCount = 0 Тогда
			Возврат Неопределено;
		КонецЕсли;


Он, правда, работает не для всех типов курсора, но в данном случае вполне годится.
15. selesta (selesta) 19.06.12 14:02
(1) MadDAD, +1, переделал Фабрику событий + Хранение сканов документов в СКЛ базе с 1с++ - все отлично
16. KV1s (KroVladS) 07.08.12 12:03
Пытаюсь адаптировать для PostgreSQL.
В табличке столбцу "Data" присвоил тип bytea.

при попытке обратиться через Recordset к полю "Data" вываливаеться с ошибкой
Произошла исключительная ситуация (Provider): Недостаточно памяти для завершения операции.

Куда копать подскажите?