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

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

I need to do this using stored procedures, I can not access the SQL tables directly. And using BDE is mandatory, too.

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

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

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

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

I'll post it today :-)

Regards Jacco

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

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

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

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

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

We use it constantly for files aprox 500KB in size.

Regards Jacco

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

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