Hi Tim,

I have been trying to work out how to load an image from a BLOB field into a TMemoryStream using a stored procedure, as well as saving an image to the same BLOB field from a TMemoryStream using a stored procedure.  

The idea is to save an image from an image control to the database, and load an image from the database to an image control.

I have tried all sorts of connotations using TBlobField and TEDBBlobStream but can't seem to work out how to do this correctly.

Thanks.

Steve

Steve


Do you really mean "stored procedure"? If so how are you passing the data in. As far as I know stored procedures don't have memorystreams of any type.

If you're trying to pass the image in and out as parameters using Delphi can you post your code. If not please explain a bit more.

Roy Lambert [Team Elevate]

Hi Roy,

<< Do you really mean "stored procedure"? If so how are you passing the data in. As far as I know stored procedures don't have memorystreams of any type.

If you're trying to pass the image in and out as parameters using Delphi can you post your code. If not please explain a bit more.>>

Yes, I use stored procedures (and sometimes functions) for just about all database interaction.  Here's some example Delphi code:


dmData.spGetAddressBookEntry.ParamByName('PAddressID').AsInteger := FAddressID;
dmData.spGetAddressBookEntry.Open;

try         
  if dmData.spGetAddressBookEntry.RecordCount > 0 then
  begin
     edtFirstname.Text := dmData.spGetAddressBookEntry.FieldByName('Firstname').AsString;
     edtLastname.Text := dmData.spGetAddressBookEntry.FieldByName('Lastname').AsString;
  end
  else
  begin
     // Blah, blah, blah
  end;
finally
  dmData.spGetAddressBookEntry.Close
end;
.
.
.
.
.
.
.

try
  dmData.spUpdateAddressBookEntry.ParamByName('PFirstname').AsString := edtFirstname.Text;
  dmData.spUpdateAddressBookEntry.ParamByName('PLastname').AsString := edtLastname.Text;
  dmData.spUpdateAddressBookEntry.ExecProc;
except
  on E: Exception do
  begin
     // Blah, blah, blah
  end;
end;

Steve

I don't use Procedures in my code, but I don't use delphi data components either, so I have to write custom code to do my INSERT, POST & UPDATES.

I can do all of this with Params apart from BLOB / picture files, where I have to use custom code. I use a dedicated function (in Delphi) which calls a specially created "PhotoQ" Query object.

This Query can be opened against a table and specific record by passing in "TableName" & "ID" properties

PhotoQ.TableName:= aTableName;
PhotoQ.ID:= aInt;

Then to actually "upload" a photo from a users file-system I call:

PhotoQ.Edit;
(PhotoQ.FieldByName('Image') AS TBlobField).LoadFromFile(aFileName);
PhotoQ.Post;
PhotoQ.Flushbuffers;
PhotoQ.Close;
PhotoQ.Unprepare;

And to show a photo on screen I call:

PhotoQ.TableName:= aTableName;
PhotoQ.ID:= aInt;
PhotoQ.RefreshData; //this just calls "SELECT Image FROM aTableName WHERE ID = %d '
FJPG.assign(PhotoQ.FieldByName(DataField)); //FJPG is a local JPEG Variable.
MainImage.Picture.Assign(FJPG);
PhotoQ.Close;
PhotoQ.Unprepare;

--

I'm not sure whether the above really helps you (!)  ... it won't allow you to maintain your rigorous "procedures only" model, but it is simple & seems robust for my users.

Adam

Hi Adam,

<< I'm not sure whether the above really helps you (!)  ... it won't allow you to maintain your rigorous "procedures only" model, but it is simple & seems robust for my users. >>

I don't use queries at all so I can't really use it, but thanks anyway.  I find the stored procedure only model is very effective and it ensures most processing is done on the server end rather than the client.  It also helps keep a lot of the business rules separate from the application.  

I have been helping maintain a large financial system (it processes over 1 billion dollars per year) over the last couple of years or so that has a Microsoft SQL Server back end.  All database interaction is done via stored procedures only.  It makes it easy to test database functionality and track down bugs.

Steve

Steve


Adam's approach is interesting, but not to dissimilar to using a table which is what I do.

Tim answered a similar question which might get you going in the right direction. Search for the following thread

NG:         elevatedb.sql
Poster:    David Loving
Date:       07/03/2011
Subject:   Blobs
Message-ID: <394C9FB3-7DA0-4140-BC1F-F9556769E7EB@news.elevatesoft.com>



Roy Lambert [Team Elevate]

Steve,

<< I have been trying to work out how to load an image from a BLOB field
into a TMemoryStream using a stored procedure, as well as saving an image to
the same BLOB field from a TMemoryStream using a stored procedure. >>

What version of Delphi are you using ?  It makes a difference since there's
been some changes to the way that BLOB params are handled.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

<< What version of Delphi are you using ?  It makes a difference since there's
been some changes to the way that BLOB params are handled. >>

Ahh, interesting.  I'm using XE2.

Steve

Steve,

<< Ahh, interesting.  I'm using XE2. >>

Okay, sorry about the delay.  Here's the code that you need:

procedure TForm37.Button1Click(Sender: TObject);
var
  TempMemoryStream: TMemoryStream;
  TempValue: Integer;
begin
  TempMemoryStream:=TMemoryStream.Create;
  try
     TempValue:=100;
     TempMemoryStream.Write(TempValue,SizeOf(Integer));
     with EDBStoredProc1 do
        begin
        Prepare;
        ParamByName('Image').LoadFromStream(TempMemoryStream,ftBlob);
        ExecProc;
        end;
  finally
     FreeAndNil(TempMemoryStream);
  end;
end;

And there's the stored procedure that I'm using:

CREATE PROCEDURE "InsertBLOB" (IN "Image" BLOB)
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO biolife ("Species No",Graphic) VALUES
(2000,?)' USING Image;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim, I finally solved it with the following code (abbreviated):

// Save Image to Database
JpgImage.Assign(imgPhoto.Picture.Graphic);  //TJpegImage
TBlobField(dmData.spUpdateAddressBookEntry.ParamByName('PPhoto')).Assign(JpgImage);
dmData.spUpdateAddressBookEntry.ExecProc;

// Load Image from Database
JpgImage.Assign(TBlobField(dmData.spGetAddressBookEntry.FieldByName('Photo')));
imgPhoto.Picture.Graphic := JpgImage;