save file to SQLSERVER with SP

Please discuss general Delphi programming topics here.

save file to SQLSERVER with SP

Postby babak8690 » May 31st, 2011, 9:01 pm

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
babak8690
Active Member
Active Member
 
Posts: 7
Joined: May 31st, 2011, 8:38 pm
Location: Iran

Re: save file to SQLSERVER with SP

Postby Kambiz » June 2nd, 2011, 4:40 pm

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;
Kambiz
User avatar
Kambiz
Administrator
Administrator
 
Posts: 2429
Joined: March 7th, 2003, 7:10 pm

Re: save file to SQLSERVER with SP

Postby babak8690 » June 11th, 2011, 5:50 pm

hi
thank you dear kambiz,but i have this error for this code.
please help me
error.JPG
error description pic
error.JPG (23.46 KiB) Viewed 5095 times

tnx
babak8690
Active Member
Active Member
 
Posts: 7
Joined: May 31st, 2011, 8:38 pm
Location: Iran

Re: save file to SQLSERVER with SP

Postby babak8690 » June 11th, 2011, 7:46 pm

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
Active Member
Active Member
 
Posts: 7
Joined: May 31st, 2011, 8:38 pm
Location: Iran

Re: save file to SQLSERVER with SP

Postby babak8690 » June 11th, 2011, 8:38 pm

ه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;
babak8690
Active Member
Active Member
 
Posts: 7
Joined: May 31st, 2011, 8:38 pm
Location: Iran

Re: save file to SQLSERVER with SP

Postby Kambiz » June 12th, 2011, 7:49 am

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.
Kambiz
User avatar
Kambiz
Administrator
Administrator
 
Posts: 2429
Joined: March 7th, 2003, 7:10 pm

Re: save file to SQLSERVER with SP

Postby babak8690 » June 12th, 2011, 8:04 am

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
Active Member
Active Member
 
Posts: 7
Joined: May 31st, 2011, 8:38 pm
Location: Iran

Re: save file to SQLSERVER with SP

Postby babak8690 » June 12th, 2011, 8:26 am

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 151 times
babak8690
Active Member
Active Member
 
Posts: 7
Joined: May 31st, 2011, 8:38 pm
Location: Iran

Re: save file to SQLSERVER with SP

Postby Kambiz » June 13th, 2011, 2:34 pm

Sorry Babak, but I don't have MSSQL on my machine.
Kambiz
User avatar
Kambiz
Administrator
Administrator
 
Posts: 2429
Joined: March 7th, 2003, 7:10 pm

Re: save file to SQLSERVER with SP

Postby babak8690 » June 14th, 2011, 2:39 am

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
babak8690
Active Member
Active Member
 
Posts: 7
Joined: May 31st, 2011, 8:38 pm
Location: Iran


Return to Delphi Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron