[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Storing and retrieveing images using SQL Server 2000,  a Delphi7 stored procedure and Borland BDE

Posted on 2006-05-11
16
Medium Priority
?
475 Views
Last Modified: 2013-11-23
I have a table with a image field and some other fields. I have a stored procedure with a image parameter and some other parameters, in order to include rows into the table. I need to store .bmp and .gif images, and retrieve then, tried several ways, and no use.  
0
Comment
Question by:JoelCruz
  • 8
  • 7
16 Comments
 
LVL 3

Expert Comment

by:KyleyHarris
ID: 16658196
it really depends on what controls you are using in delphi.

I use CRLabs controls and with them you can basically access them as a string field, or a TBlobField. after opening the table you need to get the correct field, cast it as a TBlobField and use loadfromstream, or savetostream.

I am not sure if Stored Procedures will support image type correctly, but a normal select, insert and update certainly do
0
 

Author Comment

by:JoelCruz
ID: 16658663
I need to do this using stored procedures, I can not access the SQL tables directly. And using BDE is mandatory, too.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 16685971
Hi there,

The BDE/MSSQL does not support image output parameters in stored procedures.

For getting an image I use a solution with a stored procedure that returns a rowset of 1 record.

For putting an image in the database you can use a stored procedure with a image parameter.

There are some BDE connection setting you have to set correctly to work with larger blobs.

Regards Jacco
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:JoelCruz
ID: 16689381
Hi Jacco,

I have tried several solutions to put and get an image to and from a MSSL database, but no use. Would you please elaborate with your solution?

TIA

Joel
0
 
LVL 10

Expert Comment

by:Jacco
ID: 16689703
To store an image using a stored procedure you first need a stored procedure:

CREATE  procedure dbo.prcSaveImage(
  @intID      int,
  @binImage       image)
as
begin
  update dbo.tblScan
  set    binImage = @binImage
  where  intID = @intID
end

This procedure assumes the record has already been inserted in the DB you could replace this with an insert statement if necessary.

On a datamodule you add the stored procedure and make sure the dfm text looks like this (you need to set the ParamType manually)

  object spInternalSaveImage: TStoredProc
    DatabaseName = 'SCAN'
    StoredProcName = 'dbo.prcSaveImage'
    Left = 144
    Top = 112
    ParamData = <
      item
        DataType = ftInteger
        Name = 'Result'
        ParamType = ptResult
      end
      item
        DataType = ftInteger
        Name = '@intID'
        ParamType = ptInput
      end
      item
        DataType = ftBlob
        Name = '@binImage'
        ParamType = ptInput
      end>
  end

To call the stored procedure use the following code:

procedure TDataModule1.SaveImage(const aID: Integer; aFileName: string);
var
  lMS  : TMemoryStream;
begin
    lMS := TMemoryStream.Create;
    try
      lMS.LoadFromFile(aFileName);
      lMS.Position := 0;
      with spInternalSaveImage do
      begin
        ParamByName('@intID')     .AsInteger := aID;
        ParamByName('@binImage' )     .LoadFromStream(lMS, ftGraphic);
        ExecProc;
      end;
    finally
      lMS.Free;
    end;
end;

This should do the job of saving a file to an MSSQL database using a stored procedure with the BDE.

Regards Jacco
0
 

Author Comment

by:JoelCruz
ID: 16695322
Hi Jacco,

Cute, man. Gimme the second part, the way of retrieving it using another procedure, and the 500 points are all yours.

Regards,

Joel
0
 
LVL 10

Expert Comment

by:Jacco
ID: 16696140
I'll post it today :-)

Regards Jacco
0
 
LVL 10

Expert Comment

by:Jacco
ID: 16699409
To retrieve a image we use the following stored procedure

CREATE procedure dbo.prcLoadImage(@intID int) as
begin
    select binImage
    from   dbo.tblScan
    where  autScanID = @intID
end

To call this stored procedure from Delphi use the following code:

function TDataModule1.LoadImage(const aID: Integer; const aStream: TStream): Boolean;
begin
  spLoadImage.ParamByName('@intID').AsInteger := aID;
  spLoadImage.Open;
  try
    if not spLoadImage.EOF then
    begin
      (spLoadImage.FieldByName('binImage') as TBlobField).SaveToStream(aStream);
      Result := True;
      Result := aStream.Size > 0;
    end
    else
      Result := False;
  finally
    spLoadImage.Close;
  end;
end;

Make sure the storedprocedure is configured like this (you have to set the ParamType manually!):

  object spLoadImage: TStoredProc
    DatabaseName = 'SCAN'
    StoredProcName = 'dbo.prcLoadImage'
    Left = 144
    Top = 160
    ParamData = <
      item
        DataType = ftInteger
        Name = 'Result'
        ParamType = ptResult
      end
      item
        DataType = ftInteger
        Name = '@intID'
        ParamType = ptInput
      end>
  end

To use the LoadImage procedure you have to create a memory stream yourself:

          lMemoryStream := TMemoryStream.Create;
          try
            if DataModule1.LoadImage(liID, lMemoryStream) then
            begin
              lMemoryStream.Position := 0;
              Image.Picture.Bitmap.LoadFromStream(lMemoryStream);
            end;
          finally
            lMemoryStream.Free;
          end;

Regards Jacco
0
 
LVL 10

Expert Comment

by:Jacco
ID: 16699435
One thing to remember when working with large blob is to set the "BLOB SIZE" connection property of the BDE to a value that is high enough!

Pay attention though the BDE reserves this amount of KB for every possibly available blob in result sets. Make sure you do not open queries that return a lot of records including a blob field since the BDE will reserve space for it and you'll run out of memory very soon.

Regards Jacco
0
 

Author Comment

by:JoelCruz
ID: 16705338
Hi Jacco!


I have some images into a database, I know how to put them in and how to get them out, reading the table directly. Your first procedure did the putting job fine. For the second, I remade your sugestion, in order to put it inside a sole procedure. My test program has  a Timage, a speedbutton and a edit, and the code is:

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
  MyStream : TMemoryStream;
begin
    MyStream := TMemoryStream.Create;
    with prImageGet do         // the real procedure, the table has two keys
    begin
        Active := false;
        ParamByName('@prNumber').AsInteger := StrtoInt(Edit1.Text);
        ParamByName('@prType').AsInteger := 1;
        Active := true;
        if EOF then
        begin
          ShowMessage('No record');
          exit;
        end;
        (FieldByName('Image') as TBlobField).SaveToStream(MyStream);
        Active := false;
    end;
    try
      MyStream.Position := 0;
      Image1.Picture.Bitmap.LoadFromStream(MyStream);
    finally
      MyStream.Free
    end;
end;

When I run it, write the number in the edit.text and click the button, the image turns black.
I tried writing to a file : MyStream.SaveToFile('c:\Imagens\'+ Edit1.Text + '.bmp'); It writes, but XP refuses drawing the image.

I reckon something is missing.

Joel
0
 
LVL 10

Expert Comment

by:Jacco
ID: 16706047
I see that you have named the field image? Image is a reserved word in MSSQL maybe this causes the error?

You didn't by any chance put a jpg or gif in the DB?

When you do a "select DataLength('Image') from tblYourTableName" does it return the correct sizes?

After (FieldByName('Image') as TBlobField).SaveToStream(MyStream); what is the Size of MyStream. (What is the size of the file that writes to disk)?

Try it with a very small image first (smaller than 32 KB) that is the default value of BLOB SIZE. You might have to make it bigger.

Regards Jacco
0
 

Author Comment

by:JoelCruz
ID: 16711496
Hi Jacco!

I translated all the real names from portuguese to english, and forgot about reserved words. Actually, the name is "imagem", so there is no problem with MSSQL.

The image is  a . bmp. I can retrieve it directly from the table, without stored procedures. I have images who go from 16 kb to 4.32 mb. I can retrieve all of them, putting them into a image component or writing to a file.

I supose that if the image size was too large for BDE, the direct retrieving from the table would not be successful. I don't  remember if I tried your procedure with a small image, but I tried several approaches, including one almost equal yours, without success. I am in the office now, tonight at home I will try increasing the value of BLOB SIZE. How do I do it from the delphi program?

If you dont mind my asking, your procedure is tested?

Regards,

Joel
0
 
LVL 10

Expert Comment

by:Jacco
ID: 16712942
We use it constantly for files aprox 500KB in size.

Regards Jacco
0
 

Author Comment

by:JoelCruz
ID: 16714554
Hi Jacco,

No way. It refuses images with 1.2 kb and images with 4.2 mb, all the same. I changed BLOB SIZE from 32 to 4000, nothing changed. As I said, I can retrieve all images reading directly from the table. And the images where put on the database with your first procedure. So, BLOB SIZE is not the issue. But you said you use your procedure for real, so perhaps there are some other diferences in BDE or in Delphi.

Regards

Joel
0
 
LVL 10

Accepted Solution

by:
Jacco earned 2000 total points
ID: 16717610
Can you try this with jpg's:

var
  lMemoryStream: TMemoryStream;
  lJPG : TJPEGImage;
begin
  lMemoryStream := TMemoryStream.Create;
  try
    if LoadImage(aID, lMemorystream) then
    begin
      lMemoryStream.Position := 0;
      lJPG := TJPEGImage.Create;
      try
        lJPG.LoadFromStream(lMemoryStream);
        Image.Picture.Bitmap.Assign(lJPG);
        Image.Repaint;
      finally
        lJPG.Free
      end;
    end else
    begin
      ShowMessage('Not found');
    end;
  finally
    lMemoryStream.Free;
  end;

This is the exact production code we use. If it works you can try replacing the TJPEGImage with a temporary TBitmap.

Regards Jacco
0
 

Author Comment

by:JoelCruz
ID: 16741981
Hi Jacco,

I had a hard time this weekend, only today I was able to try you code. Well done, it works! And I will have the pleasure of giving you your 500 points, as soon as I discover how to do it. Well, the solution is not complete, as I can put jpgs and bitmaps, but can recover only jpgs, but you surely earned the points.

Regards,

Joel
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview
Suggested Courses

826 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