Delphi programming blog
Источник: http://teran.karelia.pro/articles/item_4496.html
 

Бинарное поле в MS SQL Server через ADO

Опубликовано 08.11.2011 г. 20:51

Идет третий год моего программирования в Delphi, и первый раз сегодня пришлось столкнуться с вопросом записи бинарного поля в БД. Предыстория была весьма проста: программа при работе использует некоторую не маленькую структуру данных, и сохраняет ее в файл (в бинарном виде). Потребовалось реализовать многопользовательский доступ к расчетным файлам результатов, и я решил записывать их в БД в том же бинарном виде.

Первое время я долго не мог понять, почему у меня возникает проблема после загрузки результатов из БД, и пытался долго найти ошибку в своем коде. В итоге оказалось что код был верен, просто я не то что надо было записывал в БД (: Этот пост из ряда "записок", чтобы можно было при необходимости быстро найти. Тем не менее, в поисках возможной ошибки я немного пользовался гуглом. И это была одной из причин, которые побудили меня написать этот пост. Если вы попробуете поискать материал о записи бинарных данных в поле таблицы БД, то заметите две вещи: основная часть всех статей и заметок достаточно старые, и имеют чуть ли не 10-летнюю историю (тут, правда, мало что изменилось). Второе, что для меня показалось странным, что везде описывается один и тот же метод вставки данных:
  • Набор данных (dataset) переводится в режим редактирования
  • Определяется поле, которое содержит бинарные данные
  • Для поля создается поток TADOBlobStream с помощью вызова TDataSet.CreateBlobStream
  • В поток записываются необходимые данные
  • С вызовом команды Post для датасета данные передаются на сервер
Возможно, в изложении логики я немного не прав, но общая суть всех примеров, которые я нашел, была примерно такова. То есть ситуация такая, что мы всегда имеем набор данных, и поле которое редактируем. Но вот загвоздка именно в том, что это поле данных мы имеем только в случае, когда датасет действительно содержит какой то набор значений, вернее когда он представляет команду SELECT. В моем же случае запрос представлял инструкцию INSERT либо UPDATE. Например, такой:
update myData set data = :data where id = 5
В таком случае, мы не можем обратиться к полям, ибо их нет, запрос находится в закрытом (active = false) состоянии. Очевидно работа как и предполагается проводится через параметр. Логика весьма проста: создается поток, в него записываются нужные данные, выбирается нужный параметр, значение которого записывается из этого потока. Ничего сложного в этом нет, но как ни странно, таких вариантов на первых страницах в поиске гугла просто нет (или мб я что то не то искал? (: ). Тестовая запись имела следующий простой вид:
  TTest = packed record
      bv : byte;
      bv2 : byte;
      intv : integer;
      intv2 : integer;
  end;
Перед передачей данных на сервер значения заполнялись следующим образом:
var r : TTest;
begin
    r.bv := $F0;
    r.bv2 := $AA;
    r.intv := 1;
    r.intv2 := $abcd;
Значения указаны в 16-ричной форме, чтобы было легко сопоставить результаты выборки select в MS SQL Server Managment Studio, которая показывает данные в hex-форме. Здесь же вы можете наглядно сравнить как при сохранении будет различаться использование record и packed record. А теперь небольшой т.н. code-snippet демонстрирующий отправку бинарных данных на сервер:
var dp : TParameter;
    ms : TMemoryStream;
begin
    query.SQL.Text := 'update myData set data = :data where id = 5';
    dp := query.Parameters.ParamByName('data');

    ms := TMemoryStream.Create();
    try
        ms.Write(r, sizeof(r));
        ms.Position := 0;
        dp.LoadFromStream(ms, ftBlob);
    finally
         ms.Free();
    end;
    query.ExecSQL();
end;
Предполагается что query это экземпляр TADOQuery. Данный код, конечно же, может вызывать исключения, например при при вызове execSql, поэтому необходимо помнить об их обработке. При использовании update-запроса ситуация полностью аналогична. И обратный пример, загрузки данных обратно в структуру из таблицы:
var s : TStream;
begin
    query.SQL.Text := 'select data from myData where id = 5';
    query.Open();
    s := query.CreateBlobStream(query.FieldByName('data'), bmRead);
    try
        s.Read(r, sizeof(r));
    finally
        s.Free();
    end;
end;
Здесь же нам, вообще говоря, необходимо убедиться, что размер полученных данных соответствует структуре, в которую мы хотим их записать, поэтому нужны соответствующие проверки.
Метки:  ADO 

Комментарии

Саид
09.11.2011 в 21:48
Очень полезно! Спасибо. И я тоже часто не нахожу решений в гугле и изобретаю их сам, а потом публикую в своём блоге.
ter
11.11.2011 в 10:59
да не за что, ибо тут в принципе ничего сложного не было написано, и необычного.
leechdraw
13.11.2011 в 18:18
Вообще говоря, если в лоб решать эту задачу, то гугл оч легко выдает подсказки. :)
Ну, это так к слову. Первый вариант аналогичного кода у меня был такой (прошу не кидать тапками, тогда я только начинал программировать)
with qry do
 begin
  close;
  sql.clear;
  sql.add('select id, some_data from some_table');
  open;
  first; // это из разряда паранойи - сам удивлен наличию этого вызова, но из песни слов...
  edit;
  TBlobField(field[1]).loadFromStream(some_stream);
  post;
 end;


Поскольку этот код просто писал в базу небольшие картинки - то есть размер был строго меньше, ну скажем, 50 кб, но был вариабельным, а картинок было много поле в MS SQL было объявлено как varbinary(max).
Загрузка данных из базы делается аналогично, только edit и post вызывать не надо.
Тут, кстати, есть еще один очень приятный момент - мы можем повесить обработчики на onEdit, onPost или как их там и пользоваться в свое удовольствие.
А вообще, Ваш код более правильный с точки зрения логики ;) сейчас большая часть записей в базу у меня идет именно так.
Только на будущее (хотя может Вы и так в курсе) хочу предупредить, что типы данных в той же D7 - увы такие проекты встречаются и ничего с этим не поделаешь :( - не всегда удовлетворяют MS SQL в плане корректности, то есть иногда приходится приводить ручками например вот так
 sql.add('insert into table_name(some_data)'); 
 sql.add('values(cast(:some_data as nvarchar(max)))');
 parameters.ParamByName('some_data'):=mmo1.lines.text;

Я, конечно, понимаю, что не изящно, но когда поддерживаешь чужой код особо выбирать не приходится. ;)
Успехов.
ter
14.11.2011 в 22:58
спасибо за коммент (:

зы: никогда не понимал смысла записи в виде .sql.add()... sql.add если можно написать :)
sql.text := ' .....'#13+
                 '.......';
skydweller
15.11.2011 в 14:45
Это сделано для любителей fluent api ;)
Денис Пантюшев
07.11.2012 в 15:48
Такой код визуально грязный, решетки всякие, плюсы. Легко допускают ошибку - пропускают перевод каретки и пробел, из за чего возникают труднопонимаемые ошибки. Сложно организовать отступы в sql-запросе.
Такое оформление нисколько не проще, не быстрее набивать, но сложнее сопровождать и понимать.
Крайне не рекомендую.
пишите
with sql do begin 
  Add('SELECT');
  Add('    a1');
  Add('    , b1');
  Add('  FROM C');
end;
[code]
teran
13.11.2012 в 20:14
поверьте, по удобству понимая и сопровождения - дело привычки. Я в своей работе трудностей с этим не испытываю совершенно никаких.
Оформление сложнее тоже не становится. Набивать может и не быстрее но уж точно не дольше.
В вашем варианте вместо решеток и плюсов будут скобки и точки с запятой.
Лично я не люблю использование Add по нескольким причинам. Одной из них является то, что это вызов метода.

Не важно с каким образом код объединяется, важно чтобы он был изначально грамотно струтурирован по строкам, а не полный sql одной строкой без переносов и отсутступов.
Денис Пантюшев
25.01.2013 в 12:39
Совершенно с Вами согласен. Вызов метода - это недостаток. Тем более, как я позже убедился, ADO при каждом Add вызывает запрос к базе данных с целью обновить информацию о полях запроса. Поэтому в последнее время я делаю так:
...
with QuQL do begin
Clear;
Add('SELECT');
...
end;
qu.SQL.AddStrings(aSQL);
...
где QuQL - глобальная переменная TStringList
- Имя
- e-mail*
- Сайт
вы можете использовать теги [i],[b],[code],[quote]
Дополнительно