Multi-tier database applications - BLOB field read and write

資料庫 BLOB 欄位被用來儲存二進位串流資料，像是影像、聲音檔、html文件、文字檔等等。透過DataSnap的方法/函式可以用來傳送這些資料，假設你的資料庫有2個欄位：

Film_id Integer 
Picture BLOB

中間層傳回一整個資料表：

function TMoviesMethods.GetPictures: TDataSet; 
begin 
  with dsQuery do 
    begin 
      Close; 
      CommandText: = 'select * from poster'; 
      Open; 
    end; 
  Result: = dsQuery; 
end;

中間層依據給定的film_ID回傳對應的圖案串流：

function TMoviesMethods.GetPictureById (film_id: Integer): TStream; 
begin 
  Result: = nil; 
  with dsQuery do 
  begin 
    Close; 
    CommandText: = 'select * from poster where film_id =: film_id'; 
    ParamByName ('film_id'). AsInteger: = film_id; 
    Open; 
    if not eof then 
      Result: = CreateBlobStream (FieldByName ('picture'), bmRead); 
  end; 
end;

客戶端使用一個TClientDataSet控制來接收資料， 一個TEdit顯示目前的記錄ID，一個TImage控制顯示目前記錄ID的圖像。當When TClientDataSet.AfterScroll事件發生，TEdit和TImage控制將顯示資料及圖像，其中，TMoviesMethodsClient方法自動產生客戶端的中間層代理類別：

procedure TForm1.ClientDataSet1AfterScroll (DataSet: TDataSet); 
var 
  ResultStream: TStream; 
  ServiceProxy: TMoviesMethodsClient; 
begin 
  Edit1.Text: = IntToStr (ClientDataSet1.FieldByName ('film_id').AsInteger); 
  ServiceProxy: = TMoviesMethodsClient.Create(SQLConnection1.DBXConnection); 
  try 
    ResultStream: = ServiceProxy.GetPictureById(ClientDataSet1.FieldByName ('film_id'). AsInteger); 
    if ResultStream <> nil then 
    begin 
      Image1.Picture.Bitmap.LoadFromStream (ResultStream); 
    end; 
    finally 
    ServiceProxy.Free; 
  end; 
end;

下一步，本示範客戶端建立一個新的記錄，增加一個圖像資料到Blob欄位，並且將結果寫入資料庫。

procedure TForm1.Button1Click (Sender: TObject); 
var 
  AStream: TMemoryStream; 
begin 
  with ClientDataSet1 do 
  begin 
    AStream: = TMemoryStream.Create; 
    try 
      Image1.Picture.Graphic.SaveToStream (AStream); 
      Append; 
      FieldByName ('film_id'). AsInteger: = StrToInt (Edit1.Text); 
      TBlobField (FieldByName ('picture')). LoadFromStream (AStream); 
     Post; 
    finally 
      AStream.Free; 
    end; 
  end; 
end;

送出更新資料到中間層，其中SavePictures方法被用獲取中間層的一個方法：

procedure TForm1.Button2Click (Sender: TObject); 
begin 
  with SqlServerMethod1 do 
  begin 
    ServerMethodName: = 'TMoviesMethods.SavePictures'; 
    ParamByName ('PosterDelta'). Value: = ClientDataSet1.Delta; 
    ExecuteMethod; 
  end; 
end;

The middle layer save the Delta packet. The process, to use a TDataSetProvider control dspUpdate, use it to resolve the Delta package generates SQL statements submitted to the database to perform; UpdatePosterDelta process truly manually update the Delta package:

function TMoviesMethods.SavePictures (PosterDelta: OleVariant):Boolean; var 
  ErrorCount: Integer; 
begin 
  Result: = False;
  if not VarIsError (PosterDelta) then 
  begin 
    try 
      FOnDeltaRecordUpdate: = UpdatePosterDelta; 
      dspUpdate.ApplyUpdates (PosterDelta, 0, ErrorCount); 
      Result: = (ErrorCount = 0); 
    finally 
      FOnDeltaRecordUpdate: = nil; 
    end; 
  end; 
end;

When the the the dspUpdate BeforeUpdateRecord event is triggered, call UpdatePosterDelta method, in order to achieve the purpose of manually update the Delta package:

procedure TMoviesMethods.dspUpdateBeforeUpdateRecord (Sender: TObject; SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;var Applied: Boolean); 
begin 
  If Assigned (FOnDeltaRecordUpdate,) then 
    FOnDeltaRecordUpdate (Sender, SourceDS, DeltaDS, UpdateKind, Applied); 
end;

UpdatePosterDelta. This uses a TSQLDataSet control DSQUERY use it to submit SQL statements:

procedure TMoviesMethods.UpdatePosterDelta (Sender: TObject; SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind; var Applied: Boolean); 
begin 
  Applied: = False;

  case UpdateKind of 
    ukModify: 
    begin 
      with dsQuery do 
      begin
        Close; 
        CommandText: = 'update poster set picture =: picture' + 'Where film_id =: film_id'; 
        ParamByName ('film_id'). AsInteger: = DeltaDS.FieldByName('film_id'). OldValue; 
        ParamByName ('picture'). AsBlob: = DeltaDS.FieldByName ('picture').AsBytes; 
        if the ExecSQL () = 0 then Abort; 
      end; 
    end; 
    ukInsert: 
    begin 
      with dsQuery do 
      begin 
        Close; 
        CommandText: = 'insert into poster (film_id, picture)' + 'Values ??(: film_id: picture)'; 
        ParamByName ('film_id'). AsInteger: = DeltaDS.FieldByName ('film_id'). AsInteger; 
        ParamByName ('picture'). AsBlob: = DeltaDS.FieldByName ('picture'). AsBytes; 
        if the ExecSQL () = 0 then Abort; 
      end; 
    end; 
    ukDelete: 
    begin 
      with dsQuery do 
      begin 
        Close; 
        CommandText: = 'delete poster' + 'Where film_id =: film_id'; 
        ParamByName ('film_id'). AsInteger: = DeltaDS.FieldByName('film_id'). OldValue; 
        if the ExecSQL () = 0 then Abort; 
      end; 
    end; 
  end;
  Applied: = True; 
end;