FILESTREAM ERROR

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
markdelphi
Posts: 16
Joined: Sat 08 Sep 2012 19:43

FILESTREAM ERROR

Post by markdelphi » Sun 18 Nov 2012 01:39

Hello:

I'm testing the type of SQL Server FileStream. The help is an example called "FILESTREAM Data" I'm doing it but when you run the line:

fs: = qr.GetFileStreamForField ('FS', daRead);

Make the following error:

"Required provider is not installed"

What happens?

Using SQL Server 2012 Express
SDAC Trial 6.5.10
Delphi 2010

AndreyZ

Re: FILESTREAM ERROR

Post by AndreyZ » Mon 19 Nov 2012 13:04

Hello,

To work with FILESTREAM data, you should use SQL Native Client provider. If there is no SQL Native Client provider installed on a computer, the "Required provider is not installed" error is generated. To solve the problem, you should install SQL Native Client (in your case it is SQL Native Client 11 that is shipped with SQL Server 2012) on computers where you encounter the "Required provider is not installed" error.

markdelphi
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: FILESTREAM ERROR

Post by markdelphi » Mon 19 Nov 2012 19:51

Hello

I have installed the SQL Native Client 11.0.2100.60. Now reinstall and the problem persists.

AndreyZ

Re: FILESTREAM ERROR

Post by AndreyZ » Tue 20 Nov 2012 11:28

Please set the TMSConnection.Options.Provider property to prNativeClient and the TMSConnection.Options.NativeClientVersion property to nc2012, and check if the problem persists. Note that to set these options in run-time, you should add the OLEDBAccess unit to the USES clause of your unit.

markdelphi
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: FILESTREAM ERROR

Post by markdelphi » Tue 20 Nov 2012 12:22

I did what I said, but the problem persists. I'm desperate and I acquired SDAC components to work on a project that requires storing images in the database and I can not continue. Here the sample code:

Code: Select all

procedure TForm12.Button1Click(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 := '127.0.0.1';
    con.Database := 'SADI3';
    con.Options.Provider := prNativeClient;
    con.Options.NativeClientVersion := nc2012;
    qr.Connection := con;
    qr.SQL.Text := 'SELECT * FROM TESTFS';
    qr.Open;

    con.StartTransaction; // FILESTREAM requirement
    fs := qr.GetFileStreamForField('FS', daRead);

  finally
    qr.Free;
    con.Free;
 end;

AndreyZ

Re: FILESTREAM ERROR

Post by AndreyZ » Tue 20 Nov 2012 16:44

Thank you for the information. I have reproduced this problem and fixed it. This fix will be included in the next SDAC build.
As a workaround, you can install SQL Native Client 10. You can download it from the Microsoft site.

markdelphi
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: FILESTREAM ERROR

Post by markdelphi » Tue 20 Nov 2012 22:39

We tried it with version 10 and it works, can you give me an example of how to load an image?

Another question, does the Authentication has to be necessarily auWindows?

And I also want to know: When will the version with the bug fixes?

AndreyZ

Re: FILESTREAM ERROR

Post by AndreyZ » Wed 21 Nov 2012 12:06

can you give me an example of how to load an image?
Here is a code example that demonstrates loading an image:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  con: TMSConnection;
  qr: TMSQuery;
  fs: TMSFileStream;
  fs1: TFileStream;
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;

    fs1 := TFileStream.Create('C:\test.jpg', fmOpenRead);
    try
      //writing data
      con.StartTransaction; // FILESTREAM requirement
      fs := qr.GetFileStreamForField('FS', daWrite);
      fs.CopyFrom(fs1, 0); // copying the entire contents of fs1 into fs
      fs.Flush;
      fs.Close; // it's necessary to call this method before the transaction commits or rolls back FILESTREAM data
      con.Commit;
      fs1.Free;

      //reading data
      fs1 := TFileStream.Create('C:\test1.jpg', fmCreate);
      con.StartTransaction; // FILESTREAM requirement
      fs := qr.GetFileStreamForField('FS', daRead);
      fs1.CopyFrom(fs, 0); // copying the entire contents of fs into fs1
      fs.Close; // it's necessary to call this method before the transaction commits or rolls back FILESTREAM data
      con.Commit;
    finally
      fs1.Free;
    end;
  finally
    qr.Free;
    con.Free;
  end;
end;
You can use this approach to load any files to the FILESTREAM storage on the server.
does the Authentication has to be necessarily auWindows?
Yes, it is a FILESTREAM requirement. You can find the note about it at http://msdn.microsoft.com/en-us/library/gg471497.aspx
More detailed answer is at http://blogs.msdn.com/b/psssql/archive/ ... ation.aspx
When will the version with the bug fixes?
We plan to release all DAC products at the beginning of December.

bobbell
Posts: 1
Joined: Tue 03 Mar 2015 09:06

Re: FILESTREAM ERROR

Post by bobbell » Tue 03 Mar 2015 09:12

I am a designer of qrcode of asp.net and i am new to sql server.I also met some problems about SQL Server FileStream.I have my machine and a database server on the same Windows domain. I also have a staging machine that is not on the domain.

From my machine, I am able to perform any and all operations using FILESTREAM without trouble. From the staging machine, my application is able to use TSQL as it pleases, but the moment it tries to open a SqlFileStream, I receive 'access is denied.`

The staging machine, the database server, and my machine all have a local user set up with an identical name and password so that the application can use integrated security to connect to SQL Server. Since FILESTREAM is the only thing not working for the non-domain machine, I am wondering if I am missing something in the FILESTREAM documentation or if the documentation fails to specify that only Active Directory will suffice for integrated security, or of course if there is something I can do to make my situation work.

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: FILESTREAM ERROR

Post by azyk » Fri 06 Mar 2015 06:32

This question is not related to SDAC functionality. You can find an answer to this question in the MS SQL Server documentation concerning Active Directory configuration.

Post Reply