A question about Sql Server?

I want to Save picture(*.jpg or *.bmp) to Sql Server
How do I define the table field and How to realize it?
kousunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sfockCommented:
I'd use image
0
kousunAuthor Commented:
How to use the INSERT statement ,Would you please give me an example for it
Thanks you!
0
kousunAuthor Commented:
The follow is my program ,But It fail,Why?

var BitMap1 : TBitMap;
begin
   BitMap1 := TBitMap.Create;
   try
        BitMap1.LoadFromFile('c:\ball.bmp');
        Table1.FieldByName('Picture').Assign (Bitmap1);
        Table1.Post ;
   Finally
        BitMap1.Free;
   end;
end;
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

mocartsCommented:
if fails because you try to assign TBitmap to TField (returned by FieldByName).
you can use MSSQL Image column type.

Working with TTable (and TGraphicField):

procedure TForm1.Button1Click(Sender: TObject);
begin
  Table1.Active := True;
  Table1.Append;
  try
    TGraphicField(Table1.FieldByName('IMG')).LoadFromFile('D:\MrSQL.ICO');
    Table1.Post;
  except
    Table1.Cancel;
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  s: TMemoryStream;
begin
  s := TMemoryStream.Create;
  try
    TGraphicField(Table1.FieldByName('IMG')).SaveToStream(s);
    s.position := 0;
    Image1.Picture.Icon.LoadFromStream(s);
  finally
    s.free;
  end;
end;


working with TQuery:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query1.SQL.Text := 'INSERT INTO htest (img) values (:img)';
  Query1.ParamByName('img').LoadFromFile('D:\MrSQL.ICO', ftGraphic);
  Query1.ExecSQL;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  s: TMemoryStream;
begin
  s := TMemoryStream.Create;
  try
    Query1.SQL.Text := 'SELECT img FROM htest';
    Query1.Open;
    Query1.Last;
    TGraphicField(Query1.FieldByName('IMG')).SaveToStream(s);
    s.position := 0;
    Image1.picture.Icon.loadfromstream(s);
  finally
    s.free;
  end;
end;

wbr, mo.
0
mocartsCommented:
there is the way to skip this intermediate TMemoryStream when loading from Query/Table

var
  s: TStream;
begin
  Query1.SQL.Text := "..."
  Query1.Open;
  s := Query1.CreateBlobStream(Query1.FieldByName('img'), bmRead);
  try
    Image1.Picture.Icon.LoadFromStream(s);
  finally
    s.Free;  // must free stream
  end;
end;

same for TTable as CreateBlobStream is introduced in TBDEDataSet.
wbr, mo.
0
kousunAuthor Commented:
Thanks you mocarts!
I successfully use your example to save the ico file into my Sql Server2000, But When I try to save the jpg or bmp file into my database ,The application error is 'Invalid blob length'
 Note:My Table field's type is "Image" How Can I do it?
0
mocartsCommented:
if this error occurs when you laod your image from db then you must modify laoding code to be more general by using: Image1.Picture.Graphic.LoadFromStream(s);
(I had to write this way first time :)

if this error occurs when you save image in db then you can try not to use Image but some binary data field (BLOB probably) - TBLOBField in Delphi (don't exactly know at moment what type in MSSQL):

Query1.ParamByName('img').LoadFromFile('D:\MyJPG.jpg', ftBlob);

wbr, mo.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kousunAuthor Commented:
I had loaded the jpg and bmp file into my sql server2000 successfully,
But How can I show the 'Image' field?
This statement "Image1.Picture.Graphic.LoadFromStream(s);"
will occur error
Thanks you so much
0
kousunAuthor Commented:
Thanks you very much
I hope you can give me the anwer about my last question
0
mocartsCommented:
yes, there is a problem to determine which type of graphic to use. in example below I use header signatures to determine this, but it is better to use some type description field in database.

procedure TForm1.Button2Click(Sender: TObject);
var
 s: TStream;
 buf: word;
 gf: TGraphic;
begin
 Query1.SQL.Text := 'SELECT img FROM htest';
 Query1.Open;
 Query1.First;
 s := Query1.CreateBlobStream(Query1.FieldByName('img'), bmRead);
 try
   if s.read(buf, sizeof(buf)) <> sizeOf(buf) then exit;
   s.Position := 0;
   if buf = $D8FF then
     gf := TJPEGImage.Create
   else if buf = $4D42 then
     gf := TBitmap.Create
   else if buf = $CDD7 then
     gf := TMetafile.Create
   else if buf = 0 then  
     gf := TIcon.Create;
   try
     gf.LoadFromStream(s);
     Image1.Picture.Graphic := gf;
   finally
     gf.Free;
   end;
 finally
   s.Free;  // must free stream
 end;
end;

wbr, mo.
0
kousunAuthor Commented:
procedure TForm1.Button4Click(Sender: TObject);
begin
If OpenDialog1.Execute then    //Load *.Jpg or *.bmp file into sql server2000
begin
        Query1.SQL.Clear ;
        Query1.SQL.Add ('INSERT INTO Mypicture (picid,Picture) values (1,:Picture)');
        Query1.ParamByName('picture').LoadFromFile(OpenDialog1.FileName , ftBlob);
        Query1.ExecSQL;
        Query1.Active :=False;
        ShowMessage('Save Jpg or Bmp successfully');
        Query1.Free;
end;
end;



procedure TForm1.Button5Click(Sender: TObject);
var   s: TStream;
      buf: word;
      gf: TGraphic;
begin
        Query1.Close;
        Query1.SQL.Clear;
        Query1.SQL.Add('SELECT picture FROM mypicture');
        Query1.Open;
        Query1.Last ;
        s := Query1.CreateBlobStream(Query1.FieldByName('picture'), bmRead);
        try
                if s.read(buf, sizeof(buf)) <> sizeOf(buf) then exit;
                s.Position := 0;
                if buf = $D8FF then
                        gf := TJPEGImage.Create
                else  if buf = $4D42  then
                        gf := TBitmap.Create
                else  if buf = $CDD7  then
                        gf := TMetafile.Create
                else  if buf = 0  then
                        gf := TIcon.Create;
                try
                        gf.LoadFromStream(s);
                        Image1.Picture.Graphic := gf;
                finally
                        gf.Free;
                end;
        finally
                s.Free;  // must free stream
        end;
end;


There are two questions
first: when you set Image1's property Scretch:=True; The Image is no clear;
Second:when the blob field is save the bmp file ,the Image is black, why?
0
mocartsCommented:
Stretch uses fast but not good quality resizing algorythm, thats why quality of image after stretch is poor.
Bitmap is black or empty?
Did you debuged image loading procedure? maybe for this bitmap is different header bytes (usually BM)? that's why I recommended to save in separate field image type.
mo.
0
kousunAuthor Commented:
Hi Mocarts
   I find  monstrosity in the program ,that is
  If I save and show the ico file that is sucess
  If I save and show the jpg file which less than 30kb that can success
Can you explain this ? Is this is a bug ?
Would you please tell me why ?
0
mocartsCommented:
you should cahnge BDE configuration option BLOB SIZE for your db alias to some greater value (let's say 4096 for 4MB size)

wbr, mo.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.