SDAC

FILESTREAM Data

FILESTREAM is a feature of SQL Server 2008, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

This topic demonstrates how to work with FILESTREAM data with the help of SDAC.

To work with FILESTREAM data, you should have an appropriate table on a server. SQL Server requires a table to have a column of the UNIQUEIDENTIFIER data type that has the ROWGUIDCOL attribute to be an appropriate one for working with FILESTREAM data. This column must not allow NULL values and must have either a UNIQUE or PRIMARY KEY single-column constraint. A FILESTREAM column must be defined as a VARBINARY(MAX) column that has the FILESTREAM attribute.

Here is an example of a script to create a correct table:


CREATE TABLE TESTFS(
  ID INT PRIMARY KEY NOT NULL,
  FS VARBINARY(MAX) FILESTREAM NULL,
  GD UNIQUEIDENTIFIER UNIQUE ROWGUIDCOL NOT NULL DEFAULT NEWID()
)

The FILESTREAM data is represented by a file on a computer where SQL Server is installed. In order to start working with it, you should insert any value into your FILESTREAM column. This will create a new file on a server and it will be possible to work with it. Here is an example that demonstrates it:

Delphi:


MSQuery.SQL.Text := 'SELECT * FROM TESTFS';
MSQuery.Open;
MSQuery.Append;
MSQuery.FieldByName('ID').AsInteger := 1;
MSQuery.FieldByName('FS').AsString := 'TEST';
MSQuery.Post;

C++Builder:


MSQuery->SQL->Text = "SELECT * FROM TESTFS";
MSQuery->Open();
MSQuery->Append();
MSQuery->FieldByName("ID")->AsInteger = 1;
MSQuery->FieldByName("FS")->AsString = "TEST";
MSQuery->Post();

After the steps above have been performed, it is possible to work with FILESTREAM data. Here is an example that demonstrates it:

Delphi:


procedure TMainForm.BitBtnRunClick(Sender: TObject);
var
  con: TMSConnection;
  qr: TMSQuery;
  fs: TMSFileStream;
  ts: AnsiString;
begin
  con := TMSConnection.Create(nil);
  qr := TMSQuery.Create(nil);
  try
    con.Authentication := auWindows; // FILESTREAM requirement
    con.Server := 'server';
    con.Database := 'database';
    qr.Connection := con;
    qr.SQL.Text := 'SELECT * FROM TESTFS';
    qr.Open;

    //writing data
    con.StartTransaction; // FILESTREAM requirement
    fs := qr.GetFileStreamForField('FS', daWrite);
    ts := 'TEST FILESTREAM';
    fs.WriteBuffer(ts[1], Length(ts));
    fs.Flush;
    fs.Close; // it's necessary to call this method before the transaction commits or rolls back FILESTREAM data
    con.Commit;

    //reading data
    con.StartTransaction; // FILESTREAM requirement
    fs := qr.GetFileStreamForField('FS', daRead);
    SetLength(ts, fs.Size);
    fs.ReadBuffer(ts[1], fs.Size);
    ShowMessage(ts);
    fs.Close; // it's necessary to call this method before the transaction commits or rolls back FILESTREAM data
    con.Commit;
  finally
    qr.Free;
    con.Free;
  end;
end;

C++Builder:


void __fastcall TMainForm::BitBtnRunClick(TObject *Sender)
{
  TMSConnection* con = new TMSConnection(NULL);
  TMSQuery* qr = new TMSQuery(NULL);
  try
  {
    con->Authentication = auWindows; // FILESTREAM requirement
    con->Server = "server";
    con->Database = "database";
    qr->Connection = con;
    qr->SQL->Text = "SELECT * FROM TESTFS";
    qr->Open();

    //writing data
    con->StartTransaction(); // FILESTREAM requirement
    TMSFileStream* fs = qr->GetFileStreamForField("FS", daWrite);
    char* ts = "TEST FILESTREAM";
    fs->WriteBuffer(ts, strlen(ts));
    fs->Flush();
    fs->Close(); // it's necessary to call this method before the transaction commits or rolls back FILESTREAM data
    con->Commit();

    //reading data
    con->StartTransaction(); // FILESTREAM requirement
    fs = qr->GetFileStreamForField("FS", daRead);
    ts = new char[fs->Size];
    fs->ReadBuffer(ts, fs->Size);
    ShowMessage(ts);
    fs->Close(); // it's necessary to call this method before the transaction commits or rolls back FILESTREAM data
    con->Commit();
  }
  __finally
  {
    qr->Free();
    con->Free();
  }
}

As you can see from these examples, you don't need to free TMSFileStream manually. SDAC takes care of freeing all assigned TMSFileStream objects.

Note: You can find more information about working with FILESTREAM data in MSDN at http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx

See also

© 1997-2019 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback