FILESTREAM ERROR

[markdelphi]

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]

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]

Hello

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

[AndreyZ]

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]

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]

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]

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]

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]

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]

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.