save file to SQLSERVER with SP

[babak8690]

Hi dear Kambiz
I want to save and load a text file(.xt) into my table (Fied name :BodyFile as Binary) with Storeprocedure from Delphi.
I Have a code for save and load image and it works as well but i cant change this code for a file.
My code is :

Code: Select all

var st1 : TStream;
St1 := DTM.my_SP.CreateBlobStream(DTM.my_SP.FieldByName('mypic'), bmRead);
Pic_img.Picture.Bitmap.LoadFromStream(St1);

//and for save ...
 FileStrm : TStream;
    FileStrm := TMemoryStream.Create;
    Pic_img.Picture.Bitmap.SaveToStream(FileStrm);
    DTM.my_SP.Parameters.ParamByName('@mypic').LoadFromStream(FileStrm , ftGraphic);
    FileStrm.Free;


I changed it like below but it doesnt work and give Access violation error :

Code: Select all

//--save text file to database
var S : TMemoryStream;
      S := TMemoryStream.Create;
      try
        S.LoadFromFile(FileName_lbl.Caption);
        S.Position := 0;
        TBlobField(DTM.my_SP.Parameters.ParamByName('@BodyFile')).LoadFromStream(s);
      finally
        S.Free;
      end;

      DTM.my_SP.Parameters.ParamByName('@Sample_param').Value := 'Insert';
      DTM.my_SP.ExecProc;

//--for read content of file
var MyStream: TStream;
try
MyStream := DTM.my_SP.CreateBlobStream(DTM.my_SP.FieldByName('BodyFile'), bmRead);
Memo1.Lines.LoadFromStream(MyStream);
finally
MyStream.Free;
end;


please help me for save and load files in SQL Server database with SP in Delphi
Kind Regards
Babak a

[Kambiz]

Try this:

Code: Select all

//--save text file to database
DTM.my_SP.Parameters.ParamByName('@BodyFile').LoadFromFile(FileName_lbl.Caption, ftBlob);
DTM.my_SP.Parameters.ParamByName('@Sample_param').Value := 'Insert';
DTM.my_SP.ExecProc;

//--for read content of file
Memo1.Lines.Text := DTM.my_SP.FieldByName('BodyFile').Value;

[babak8690]

hi
thank you dear kambiz,but i have this error for this code.
please help me

error description pic

error.JPG (23.46 KiB) Viewed 5163 times

tnx

[babak8690]

I used this code but still i have errors :

Code: Select all

var  FileStrm: TStream;
begin
if (OpenDialog1.Execute) then
    Begin
    Memo1.Lines.LoadFromFile(OpenDialog1.FileName);

    my_SP.Active := False;
    my_SP.Parameters.ParamByName('@Sample_param').Value := 'Insert';
    my_SP.Parameters.ParamByName('@Emtiaz').Value := '444';

    FileStrm := TStream.Create;
    Memo1.Lines.SaveToStream(FileStrm);
    my_SP.Parameters.ParamByName('@BodyFile').LoadFromStream(FileStrm , ftBlob); //or ftBlob
    my_SP.ExecProc;

    FileStrm.Free;
    end;

[babak8690]

هI used another code.read from memo but it doesnt work too :

Code: Select all

var  FileStrm: TMemoryStream;
begin
if (OpenDialog1.Execute) then
    Begin
    Memo1.Lines.LoadFromFile(OpenDialog1.FileName);

    my_SP.Active := False;
    my_SP.Parameters.ParamByName('@Sample_param').Value := 'Insert';
    my_SP.Parameters.ParamByName('@Emtiaz').Value := '444';

    FileStrm := TMemoryStream.Create;
    Memo1.Lines.SaveToStream(FileStrm);
    my_SP.Parameters.ParamByName('@BodyFile').LoadFromStream(FileStrm , ftMemo); //or ftblob
    FileStrm.Free;

    my_SP.ExecProc;
    end;

[Kambiz]

Seems the problem is because of something else.

I wonder why you insist to load data from file into an intermediate stream and then save that stream into the blob field. You can directly load a blob from a file or save it into a file.

[babak8690]

Dear Kambiz
can you help me this way?you means that I can't use Storedprocedure for read and write data?or i should use read/write method directly like same as you say?
This is my first beggest problem in my project.please help me with code.
kind regards
babak

[babak8690]

I have attached a tiny sample for this topic.If possible please see it.
Thank you so much.

stream.rar

tiny sample

(144.15 KiB) Downloaded 188 times

[Kambiz]

Sorry Babak, but I don't have MSSQL on my machine.

[babak8690]

Dear Kambiz,
I solved the problem by change the code like this :

Code: Select all

procedure TForm1.Button4Click(Sender: TObject);
var  FileStrm: TMemoryStream;
begin
if (OpenDialog1.Execute) then
    Begin
    Memo1.Lines.LoadFromFile(OpenDialog1.FileName);

    my_SP.Active := False;
    my_SP.Parameters.ParamByName('@Sample_param').Value := 'Insert';
    my_SP.Parameters.ParamByName('@Emtiaz').Value := '7777';

    try
    FileStrm := TMemoryStream.Create;//(OpenDialog1.FileName);
    FileStrm.LoadFromFile(OpenDialog1.FileName);
    with my_SP.Parameters.ParamByName('@BodyFile') do
    begin
    LoadFromStream(FileStrm, DataType);
    end;
    finally
    FileStrm.Free;
    end;
    my_SP.ExecProc;
    end;
end;

But i have a problem yet.
When i save a text file in database,no error occured and i cane save data successfully but when i want to read same data from database,i see just 2 line of its data in a memo.Does this method has any limitaion of file size?
How can i solve this problem?
Warm regards
Babak