SQL Server 2008 support for filestream feature

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Mojoman
Posts: 5
Joined: Tue 05 May 2009 01:17

SQL Server 2008 support for filestream feature

Post by Mojoman » Tue 05 May 2009 01:21

Hi,

Do you have specific support for the above?

Thanks in advance

-Mohamed

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 05 May 2009 09:04

SDAC does not support specific filestream feature.

Mojoman
Posts: 5
Joined: Tue 05 May 2009 01:17

Post by Mojoman » Tue 05 May 2009 12:08

Are there any plans to add this in the future?

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 06 May 2009 08:23

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
Posts: 5
Joined: Tue 05 May 2009 01:17

Post by Mojoman » Wed 06 May 2009 09:28

Thank you!

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Wed 19 May 2010 07:56

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 20 May 2010 11:59

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

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 04 May 2011 08:12

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
Posts: 19
Joined: Fri 18 Sep 2009 20:04

Post by Dido » Sun 28 Aug 2011 08:53

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

Post by AndreyZ » Thu 01 Sep 2011 12:40

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

Post Reply