Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Reading data form MSSQL FileStream


This question is answered.


Permlink Replies: 9 - Last Post: Sep 18, 2017 9:27 PM Last Post By: Dmitry Arefiev
Davide Rubbiani

Posts: 70
Registered: 8/15/03
Reading data form MSSQL FileStream  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 7, 2017 3:11 AM
Hello,

Using Delphi Tokio and MSSQL 2014 (10.0.5203) I'm developing an application that use FileStream feature to store some pdf and xlsx documents.
I'm using the following procedure to read one record from a FILESTREAM enabled table but I get an "Access violation" exception:

function TAttachmentsDataModule.LoadAttachment(const AAttachmentId: Integer): TAttachment;
var
QryFS: TFDQuery;
Buffer: TArray<System.Byte>;
begin
QryFS := TFDQuery.Create(nil);
QryFS.Connection := MainDataModule.Connection;
try
QryFS.Connection.StartTransaction;
try
QryFS.SQL.Text := 'select :st = DATA.PathName(), :fn = AttachmentFileName from Attachments where AttachmentId = :id';
QryFS.Params.ParamByName('st').DataType := ftStream;
QryFS.Params.ParamByName('st').FDDataType := dtHBFile;
QryFS.Params.ParamByName('st').ParamType := ptOutput;
QryFS.Params.ParamByName('st').StreamMode := smOpenRead;
QryFS.Params.ParamByName('fn').DataType := ftString;
QryFS.Params.ParamByName('fn').ParamType := ptOutput;
QryFS.Params.ParamByName('id').AsInteger := AAttachmentId;
QryFS.OpenOrExecute;

Result := nil;
if (QryFs.Params.ParamByName('fn').AsString > '') then
begin

// TFDParam.AsStream returns reference to internal low-level stream
QryFS.Params.ParamByName('st').AsStream.Read(Buffer, Length(Buffer)); <------ Here I get an Access violation

Result := TAttachment.Create(AAttachmentId, QryFS.Params.ParamByName('fn').AsString, QryFS.Params.ParamByName('st').AsStream);
end;
QryFS.Connection.Commit;
except
QryFS.Connection.Rollback;
raise;
end;
finally
QryFS.Free;
end;
end;

All the database configurations were made and the data exists in the storage on disk.
When I run the SQL statement on SSMS I get one record with the right path and FileName data.
The exception is raised on the execution of this kline : QryFS.Params.ParamByName('st').AsStream.Read(Buffer, Length(Buffer));
Debugging this line I get the Buffer value = 0.

What I'm doing wrong?

Thank you,
Davide
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Reading data form MSSQL FileStream  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 7, 2017 7:02 AM   in response to: Davide Rubbiani in response to: Davide Rubbiani
Davide Rubbiani wrote:

// TFDParam.AsStream returns reference to internal low-level stream
QryFS.Params.ParamByName('st').AsStream.Read(Buffer,
Length(Buffer)); <------ Here I get an Access violation

From your code you're not initializing the buffer:

SetLength(buffer, QryFS.Params.ParamByName('st').asStream.SIZE);

prior to:

QryFS.Params.ParamByName('st').AsStream.Read(Buffer, Length(Buffer));
Davide Rubbiani

Posts: 70
Registered: 8/15/03
Re: Reading data form MSSQL FileStream  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 8, 2017 2:09 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:
Davide Rubbiani wrote:

// TFDParam.AsStream returns reference to internal low-level stream
QryFS.Params.ParamByName('st').AsStream.Read(Buffer,
Length(Buffer)); <------ Here I get an Access violation

From your code you're not initializing the buffer:

SetLength(buffer, QryFS.Params.ParamByName('st').asStream.SIZE);

prior to:

QryFS.Params.ParamByName('st').AsStream.Read(Buffer, Length(Buffer));

Hello Lajos,

thanks for your response.
Unfortunately your indication did not solve the problem.
Now I get the access violation exception on the line that sets the buffer size.
It seems the problem is on the "st" params which seems not assigned.

I try with a fresh new database but i got the same exception.
Is there a way to check if the "st" params is correctly assigned?

Thank you,
Davide
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Reading data form MSSQL FileStream [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 9, 2017 7:09 AM   in response to: Davide Rubbiani in response to: Davide Rubbiani
Davide Rubbiani wrote:

Lajos Juhasz wrote:
Davide Rubbiani wrote:

// TFDParam.AsStream returns reference to internal low-level
stream QryFS.Params.ParamByName('st').AsStream.Read(Buffer,
Length(Buffer)); <------ Here I get an Access violation

From your code you're not initializing the buffer:

SetLength(buffer, QryFS.Params.ParamByName('st').asStream.SIZE);

prior to:

QryFS.Params.ParamByName('st').AsStream.Read(Buffer,
Length(Buffer));

Hello Lajos,

thanks for your response.
Unfortunately your indication did not solve the problem.
Now I get the access violation exception on the line that sets the
buffer size. It seems the problem is on the "st" params which seems
not assigned.

I try with a fresh new database but i got the same exception.
Is there a way to check if the "st" params is correctly assigned?

Thank you,
Davide

Try to debug using debug dcu. This code should work.
Davide Rubbiani

Posts: 70
Registered: 8/15/03
Re: Reading data form MSSQL FileStream [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 11, 2017 9:18 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:
Davide Rubbiani wrote:

Lajos Juhasz wrote:
Davide Rubbiani wrote:

// TFDParam.AsStream returns reference to internal low-level
stream QryFS.Params.ParamByName('st').AsStream.Read(Buffer,
Length(Buffer)); <------ Here I get an Access violation

From your code you're not initializing the buffer:

SetLength(buffer, QryFS.Params.ParamByName('st').asStream.SIZE);

prior to:

QryFS.Params.ParamByName('st').AsStream.Read(Buffer,
Length(Buffer));

Hello Lajos,

thanks for your response.
Unfortunately your indication did not solve the problem.
Now I get the access violation exception on the line that sets the
buffer size. It seems the problem is on the "st" params which seems
not assigned.

I try with a fresh new database but i got the same exception.
Is there a way to check if the "st" params is correctly assigned?

Thank you,
Davide

Try to debug using debug dcu. This code should work.

Hello,

after a lot of test I can't still read any filestream field form my MS SQL 2014 database.
The error is Always an "Access violation" on the line that sets the buffer size.
In fact the Params[0] seems to be nil.

Following the code I'm using to write and read single record to my filestream table :

function TDataModule2.LoadAttachment(const ASerialNo: Integer): TStream;
var
QryFS: TFDQuery;
Buffer: TArray<System.Byte>;
begin
QryFS := TFDQuery.Create(nil);
QryFS.Connection := Connection;
try
Connection.StartTransaction;
try
QryFS.SQL.Text := 'select :st = Chart.PathName() from Records where SerialNumber = :id';
QryFS.Params[0].DataType := ftStream;
QryFS.Params[0].FDDataType := dtHBFile;
QryFS.Params[0].ParamType := ptOutput;
QryFS.Params[0].StreamMode := smOpenRead;
QryFS.Params[1].AsInteger := ASerialNo;
QryFS.OpenOrExecute;
// TFDParam.AsStream returns reference to internal low-level stream
SetLength(Buffer, QryFS.Params[0].AsStream.Size);
QryFS.Params[0].AsStream.Read(Buffer, Length(Buffer));
Result := QryFS.Params[0].AsStream;
Connection.Commit;
except
Connection.Rollback;
raise;
end;
finally
QryFS.Free;
end;
end;

procedure TDataModule2.StoreAttachment(const ASerialNo: Integer; const AFileName: string);
var
oStr: TFileStream;
FName: string;
QryFS: TFDQuery;
begin
QryFS := TFDQuery.Create(nil);
QryFS.Connection := Connection;
try
Connection.StartTransaction;
try
QryFS.SQL.Text := 'INSERT INTO [dbo].[Records] ([SerialNumber], [Chart]) VALUES (:SerialNumber, :Chart)';
QryFS.Params[0].Value := ASerialNo;
QryFS.Params[1].DataType := ftStream;
QryFS.Params[1].StreamMode := smOpenWrite;
QryFS.ExecSQL;
oStr := TFileStream.Create(AFileName, fmOpenRead);
try
// FireDAC receives a copy of the stream
QryFS.Params[1].AsStream.CopyFrom(oStr, -1);
finally
// The user is responsible for freeing the original stream
oStr.Free;
end;
QryFS.CloseStreams;
Connection.Commit;
except
Connection.Rollback;
raise;
end;
finally
QryFS.Free
end;
end;

Selecting rows from smss query results in a correct number and valued records (comprise the .PathName() function) so the writing procedure seems to work fine.

Unfortunately FILESTREAM seems the right choice to respond to my application requisites.
Have you got any advice about make the above code functioning? (or a functioning sample I can run on my side)

Thank you,
Davide

Edited by: Davide Rubbiani on Sep 11, 2017 9:19 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Reading data form MSSQL FileStream [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 11, 2017 6:08 PM   in response to: Davide Rubbiani in response to: Davide Rubbiani
If you will provide a test application, including SQL script to create DB objects, then it will be possible to test / review it.

--
With best regards,
Dmitry
Davide Rubbiani

Posts: 70
Registered: 8/15/03
Re: Reading data form MSSQL FileStream [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 12, 2017 2:53 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
If you will provide a test application, including SQL script to create DB objects, then it will be possible to test / review it.

--
With best regards,
Dmitry

Hi Dmitry,

I prepared a test application and database scripts.
How can I provide it to you?

Thank you,
Davide
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Reading data form MSSQL FileStream [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 12, 2017 3:23 AM   in response to: Davide Rubbiani in response to: Davide Rubbiani
Davide Rubbiani wrote:

Dmitry Arefiev wrote:
If you will provide a test application, including SQL script to
create DB objects, then it will be possible to test / review it.

--
With best regards,
Dmitry

Hi Dmitry,

I prepared a test application and database scripts.
How can I provide it to you?

The best way is to open a ticket at https://quality.embarcadero.com/.

If you would like just to upload to the forum you can do that in the
Attachment forum. Using the web interface:
https://forums.embarcadero.com/forum.jspa?forumID=2 and reply here with
the subject of the message in that forum.
Davide Rubbiani

Posts: 70
Registered: 8/15/03
Re: Reading data form MSSQL FileStream [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 12, 2017 10:29 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:
Davide Rubbiani wrote:

Dmitry Arefiev wrote:
If you will provide a test application, including SQL script to
create DB objects, then it will be possible to test / review it.

--
With best regards,
Dmitry

Hi Dmitry,

I prepared a test application and database scripts.
How can I provide it to you?

The best way is to open a ticket at https://quality.embarcadero.com/.

If you would like just to upload to the forum you can do that in the
Attachment forum. Using the web interface:
https://forums.embarcadero.com/forum.jspa?forumID=2 and reply here with
the subject of the message in that forum.

Hello,

I created a new issue in quality central as you suggested.
Issue no. : RS-85407

Thank you,
Davide
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Reading data form MSSQL FileStream [Edit]
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 18, 2017 9:26 PM   in response to: Davide Rubbiani in response to: Davide Rubbiani
It is a bug in FireDAC. To resolve it (workaround) do:
* open FireDAC.Phys.MSSQL.pas
* locate there line:
    if (oExtStr = nil) and (AParam.DataType = ftStream) then begin

* replace it with:
    if not lExtStream and (AParam.DataType = ftStream) then begin

* save unit
* add path to FireDAC sources to your IDE Library Path.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02