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

25.10.10

База данных - Инструменты администратора БД

В статье приведен набор функций для хранения файлов в отдельной базе 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 ИЛИ не _УдалятьПустые Тогда
Результат.Добавить(СтрПолучитьСтроку(, Строк));
КонецЕсли;
КонецЦикла;

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

 

См. также

Автоподбор ролей для профилей и групп доступа в любых типовых базах 1С УТ 11, КА 2, ERP2, Розница 2/3, УНФ 16/3, БП 3, ЗУП 3 и подобных (УФ, Платформа 8.3.14+)

Инструменты администратора БД Роли и права 8.3.14 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:Документооборот 1С:Зарплата и кадры государственного учреждения 3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Платные (руб)

Роли… Вы тратите много времени и сил на подбор ролей среди около 2400 в ERP или 1500 в Рознице 2, пытаясь понять какими правами они обладают? Вы все время смотрите права в конфигураторе или отчетах чтоб создать нормальные профили доступа? Вы хотите наглядно видеть какие права дает профиль и редактировать все в простом виде? А может хотите просто указать подсистему и дать права на просмотр и добавление на объекты и не лезть в дебри прав и чтоб обработка сама подобрала нужные роли? Все это теперь стало возможно! Обновление от 15.12.2023, версия 1.1.

12000 руб.

06.12.2023    2973    13    1    

34

SALE! 20%

Infostart УДиФ: Управление данными и формами

Инструменты администратора БД Инструментарий разработчика Роли и права Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

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

10000 8000 руб.

10.11.2023    3524    11    1    

34

SALE! 30%

PowerTools

Инструментарий разработчика Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Россия Платные (руб)

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

3600 2520 руб.

14.01.2013    177737    1073    0    

849

Ускоренное проведение документов (x4), устранение ошибок 60/62 счетов и зачет авансов (Бухгалтерия 3.0)

Закрытие периода Инструменты администратора БД Корректировка данных Бухгалтерский учет 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Платные (руб)

Расширение «Оперативное проведение» в 4 раза уменьшает время проведения документов и закрытия месяца. Является комплексным решением проблем 62 и 60 счетов. Оптимизирует проведение при включенной функциональной опции «Раздельный учет НДС». Используется в более 10 организациях уже 2 года. Совместимо с конфигурацией Бухгалтерия 3.0 (+КОРП).

14400 руб.

29.04.2020    27375    79    146    

59

Система хранения присоединенных файлов в томах на диске

Инструменты администратора БД Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Управление производственным предприятием Платные (руб)

Конфигурация Комплексная автоматизация 1.1 (и УПП 1.3 тоже) хранит файлы и изображения в справочнике Хранилище дополнительной информации в реквизите Хранилище типа ХранилищеЗначений. Та же история с ВложениямиЭлектроннойПочты. Но при этом присоединенные файлы в Электронном документообороте хранит в томах на диске. Эта доработка позволяет использовать стандартный механизм хранения файлов, изображений и вложений электронных писем в томах на диске. При этом можно разделить тома хранения по объектам конфигурации.

4200 руб.

10.11.2015    61314    88    59    

73

"Менеджер потоков 2.1": УПП: "Восстановление партий"

Инструменты администратора БД Платформа 1С v8.3 1С:Управление производственным предприятием Россия Бухгалтерский учет Управленческий учет Платные (руб)

Как оптимизировать то, что, считалось, не поддается оптимизации? Как повысить доступность базы данных? Как проводить самую «времяемкую» операцию не по паре раз в неделю, а по несколько раз в день*? Ответ есть!

20000 руб.

12.09.2019    11746    5    9    

7

Брандмауэр для сервера 1С Предприятие 8 - внешнее управление сеансами

Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Управление возможностью начала и возобновления сеансов пользователей по различным условиям, ограничение общего числа возможных сеансов для работы с информационной базой, резервирование возможности работы с информационной базой определенных польззователей, запрет запуска нескольких сеансов для пользователя, журнализация событий начала (возобновления) и завершения (гибернации) сеансов, ведение списка активных сеансов для информационных баз кластера серверов

3600 руб.

06.02.2017    31110    31    18    

47

Хранилище файлов на SQL

Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Управленческий учет Платные (руб)

Привязка файлов / сканов к объектам 1С с сохранением их на SQL-сервере

12000 руб.

09.10.2019    10983    5    8    

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

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

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


Он, правда, работает не для всех типов курсора, но в данном случае вполне годится.
16. KroVladS 34 07.08.12 12:03 Сейчас в теме
Пытаюсь адаптировать для PostgreSQL.
В табличке столбцу "Data" присвоил тип bytea.

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

Куда копать подскажите?
Оставьте свое сообщение