Insert Blob SQL

stians · Post by **stians** » Thu 21 Sep 2006 17:06

Hello

Is it posible to get a example with inserting BLOB with SQL.
Not only refering to "Please see MyDAC/Demos/Pictures project"

THis is the code i am using. and it works some times....
var
m:TmemoryStream
begin
...
MyQuery1.Close;
MyQuery1.sql.Text:= Trim('Update vRapport set rapport=:BB where id=9');
MyQuery1.ParamByName('BB').DataType:=ftBlob;
MyQuery1.ParamByName('BB').ParamType := ptInputOutput;
MyQuery1.ParamByName('BB').SetBlobData(m.Memory, m.Size);
MyQuery1.Execute;

Anybody that hase any code that works?

Stians

Antaeus · Post by **Antaeus** » Fri 22 Sep 2006 12:24

We have tested this code it works fine. Please specify what do you mean under "works some times".

stians · Post by **stians** » Fri 22 Sep 2006 16:02

This is the real code i use:

Function Tzdb.SaveBlobToDB(SQL:String; m:TmemoryStream):Integer;
begin
Result:=-1;

Self.MyConnection1.StartTransaction;

MyQuery1.Close;
MyQuery1.sql.Text:= Trim(SQL);
MyQuery1.ParamByName('BB').DataType:=ftBlob;
MyQuery1.ParamByName('BB').ParamType := ptInputOutput;
MyQuery1.ParamByName('BB').SetBlobData(m.Memory, m.Size);

try
MyQuery1.Execute;

zdb.MyConnection1.Commit;
Result:=1;
Except
zdb.MyConnection1.Rollback;
end;
end;

The problem is on line: myQuery1.ParamByName('BB').SetBlobData(m.Memory, m.Size);

I get a exception every time. If i remove the try except i somthimes can insert blobs. and sometimes i i get a accesviolation.

S.

stians · Post by **stians** » Fri 22 Sep 2006 16:15

I made this
MyQuery1.ParamByName('BB').SetBlobData(m.Memory, m.Size);
To
MyQuery1.ParamByName('BB').SetBlobData(m.Memory);

And now it looks like it is working.

S.

Antaeus · Post by **Antaeus** » Mon 25 Sep 2006 11:15

Please specify exact version of Delphi or Kylix you use and exact version of MyDAC, you can see it in About sheet of TMyConnection Editor.
Possibly there is something wrong with the MemoryStream object. We can not draw a conclusion about the reason of the problem if we do not have an examle that demonstrates the problem. If the problem appears in a big application try to curtail this application to minimum extent.

ca_cruiser · Post by **ca_cruiser** » Tue 16 Dec 2008 17:55

MyQuery.Params.ParamByName(fnBlob).SetBlobData(BlobData,Bytes - BytesWritten)

does not work when the data has \0 values.

How can I accomplish this ? i.e. how to escape the binary data?

I currently have to do the following:

ms := TMemoryStream.Create;
ms.WriteBuffer(BlobData^, Bytes - BytesWritten);
ms.Position := 0;
MyQuery.Params.ParamByName(fnBlob).LoadFromStream(ms,ftBlob);
ms.Free;

Dimon · Post by **Dimon** » Fri 19 Dec 2008 14:02

Please specify the exact version of MyDAC that you use.

ca_cruiser · Post by **ca_cruiser** » Fri 19 Dec 2008 14:19

MyDAC v 5.55.0.39 for Delphi-6

Dimon · Post by **Dimon** » Mon 29 Dec 2008 13:09

I could not reproduce the problem.
Please specify the way you check if all data was saved. Also try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

vga · Post by **vga** » Thu 19 Feb 2009 07:36

procedure TForm1.Button1Click(Sender: TObject);
var
stream: TMemorystream;
begin
if not OpenImageEnDialog1.Execute then exit;
stream := TMemorystream.Create;
stream.LoadFromFile(OpenImageEnDialog1.FileName);
stream.Position := 0;

MyQuery1.SQL.Text :=
'insert into `test` (pic_id, pic_name) values (null, :picname, :Image)';
MyQuery1.ParamByName('picname').AsString := OpenImageEnDialog1.FileName;

MyQuery1.ParamByName('Image').LoadFromStream(stream, ftBlob);
// MyQuery1.ParamByName('Image').LoadFromFile(OpenImageEnDialog1.FileName, ftBlob);
MyQuery1.Execute;

stream.Free;
end;

Dimon · Post by **Dimon** » Thu 19 Feb 2009 12:07

Please describe in more details what is the problem.

vga · Post by **vga** » Sat 21 Feb 2009 03:21

thank you.

when execute, delphi will display error.

I'v sent my program and error message to you.

vga · Post by **vga** » Mon 23 Feb 2009 05:38

Dear Dimon, have you received my program?

vga · Post by **vga** » Mon 02 Mar 2009 08:03

"We have investigate this problem and fixed it. This fix will be included in the next MyDAC build."

---- http://devart.com/forums/viewtopic.php?t=14254

thanks