Abdulaziz

><<What's the spec on your server? Are there lots of other things running on it?>>
>
>I am using two desktop pc with i5 processors and 4 GB ram running Windows 7.

I would have thought that those would be faster than mine (Core 2 Duo T9300 Vista 3Mb RAM)

My only other thought is indices. If you have an index on iUserSysNo then I'm baffled.

Roy Lambert [Team Elevate]

Abdulaziz,

<< I am fully aware of this but I cannot believe in this anymore.  Why?  I
executed the same bellow SQL clause from a query and from a stored procedure
and getting the same result but in different executions time.

Using a query it takes more than 1.7 seconds.
Using a stored procedure less than 0.5 seconds. >>

The examples that you showed are not *exactly* equivalent.  The TEDBQuery is
being created and executed each time, whereas the stored procedure is being
re-used each time and is not being re-prepared each time, either.
Therefore, the stored procedure version is effectively only doing this after
the first execution:

1) Is there any changes to the tables involved in this query ?
2) No, then just return the data.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<The examples that you showed are not *exactly* equivalent.  The TEDBQuery is
being created and executed each time, whereas the stored procedure is being
re-used each time and is not being re-prepared each time, either.
Therefore, the stored procedure version is effectively only doing this after
the first execution:>>

You probably right.  However, is there any example of loading an image from a database into an TImage component using a stored procedure?

Regards,
Abdulaziz Jasser

Abdulaziz,

> You probably right.  However, is there any example of loading an image from a database into an TImage component using a stored procedure?
If I don't misunderstand you:

1. Create a procedure like this:

  DECLARE Result CURSOR WITH RETURN FOR Stmt;
  PREPARE Stmt FROM
     'SELECT MyImage from MyTable where UserID = ?';
  OPEN Result using AUserID;

2. Use code like this to display the image in a TImage component:

var
  MyStream: TMemoryStream;
begin
  if not MyProcedure.Prepared then
     MyProcedure.prepare;
  MyProcedure.ParamByName('AUserID').asInteger := CurrentUserID;
  MyProcedure.execProc;

  MyStream := TMemoryStream.create;
  try
     TBlobField(MyProcedure.FieldByName('MyImage')).saveToStream(MyStream);
     MyStream.position := 0;
     Image1.picture.loadFromStream(MyStream);
  finally
     MyStream.free;
  end;
end;

Untested!

Uli

Abdulaziz,

I've been following this thread and there is something I still can't understand... Why are you convinced that a stored procedure would give you better results than a regular query... a stored procedure returns a dataset, exactly like a query, the technique to load it into a TImage would be exactly the same in both cases... I believe you are not explaining it clearly or there is something we all haven't yet understood about what you are looking for.


--
Fernando Dias
[Team Elevate]

Abdulaziz,

In addition, here is the generic procedure I use load JPeg images from a blob column into a TImage component, in this example from MyImgDataset table (but it could be a stored procedure or query) into Image1 (a TImage component), using a stream of type TEDBBlobStream.
If you are using a stored procedure to return the dataset, Uli has already shown an example on how to do it in his previous post.
Here is the code:


Procedure LoadImage;
var
  Buf:TEDBMBlobStream;
  Pic: TJpegImage;
begin
  Buf:=TEDBBlobStream.Create(MyImgDataset, bmRead);
  try
    if Buf.Size>4 then
    begin
      Pic := TJpegImage.Create ;
      try
        Pic.LoadFromStream(Buf);
        Image1.Picture.Graphic:=Pic;
      finally
        Pic.Free ;
      end;
    end
    else
      Image1.Picture.Graphic:=nil;
  finally
    Buf.Free;
  end;
end;

--
Fernando Dias
[Team Elevate]

Abdulaziz,

<< You probably right.  However, is there any example of loading an image
from a database into an TImage component using a stored procedure? >>

Just to clarify: do you want to use a data-aware TDBImage, or just a
straight TImage ?


--
Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Tim

<<Just to clarify: do you want to use a data-aware TDBImage, or just a straight TImage ?>>

Just TImage. component.


Regards,
Abdulaziz Jasser

Abdulaziz,

<< Just TImage. component. >>

Okay, use this SQL to create the function:

CREATE FUNCTION LoadImage
RETURNS BLOB
BEGIN

DECLARE Result BLOB;
DECLARE qryData CURSOR FOR sSQL;

PREPARE sSQL FROM 'SELECT MyImageColumn FROM MyTable';

OPEN qryData;

FETCH FIRST FROM qryData('MyImageColumn') INTO Result;

CLOSE qryData;

RETURN Result;

END

However, how you load the data depends upon which version of Delphi you're
using.  Some of the older Delphi versions like 7 have serious issues with
binary (BLOB) parameters and can't handle them very well.

Tim Young
Elevate Software
www.elevatesoft.com