SQL Server 2008 support for filestream feature

[Mojoman]

Hi,

Do you have specific support for the above?

Thanks in advance

-Mohamed

[Dimon]

SDAC does not support specific filestream feature.

[Mojoman]

Are there any plans to add this in the future?

[Dimon]

We will investigate the possibility of adding this functionality in the near future. As soon as we solve this question we will let you know.

[Mojoman]

Thank you!

[brace]

I am investigating using filestream, even if SDAC doesn't support specific features, SDAC supports writing a blob to a varbinary(max) field, so it should also support filestream... Could you confirm?

[Dimon]

Yes, SDAC supports writing values to filestream fields as a blob value.

[Dimon]

New build of SDAC version 5.10.0.6 is available for download now. Support of API interface for managing FILESTREAM data was added in this build.

[Dido]

Hello,
where can i find the examples and
documentation for FILESTREAM API functions
and does UniDAC support it, or only SDAC.

Thanks in advance!

[AndreyZ]

UniDAC doesn't support API interface for managing FILESTREAM data, it is supported only by SDAC. It's a specific feature of SQL Server, and because UniDAC is oriented to work with many database servers in a common way, we do not plan to add FILESTREAM support to UniDAC (for the time being). The SDAC documentation doesn't have examples of using FILESTREAM API, we will add some soon.
Here is the "Working with FILESTREAM data" article that we will include to the SDAC documentation:

To work with FILESTREAM data, you should have a correct table on a server. A FILESTREAM column must be defined as a VARBINARY(MAX) column that has the FILESTREAM attribute. The table must also have a column of the UNIQUEIDENTIFIER data type that has the ROWGUIDCOL attribute. This column must not allow NULL values and must have either a UNIQUE or PRIMARY KEY single-column constraint. Here is an example of a script to create a correct table:

Code: Select all

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:

Code: Select all

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:

Code: Select all

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 ... .100).aspx