?
Solved

A question about Sql Server?

Posted on 2003-02-27
14
Medium Priority
?
344 Views
Last Modified: 2010-04-04
I want to Save picture(*.jpg or *.bmp) to Sql Server
How do I define the table field and How to realize it?
0
Comment
Question by:kousun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 3

Expert Comment

by:sfock
ID: 8039736
I'd use image
0
 

Author Comment

by:kousun
ID: 8039983
How to use the INSERT statement ,Would you please give me an example for it
Thanks you!
0
 

Author Comment

by:kousun
ID: 8040107
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:mocarts
ID: 8040618
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8040690
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
 

Author Comment

by:kousun
ID: 8050868
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
 
LVL 9

Accepted Solution

by:
mocarts earned 200 total points
ID: 8052173
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
 

Author Comment

by:kousun
ID: 8070487
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
 

Author Comment

by:kousun
ID: 8070508
Thanks you very much
I hope you can give me the anwer about my last question
0
 
LVL 9

Expert Comment

by:mocarts
ID: 8070697
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
 

Author Comment

by:kousun
ID: 8093276
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8093889
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
 

Author Comment

by:kousun
ID: 8136041
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8136170
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question