Hi,

How to load an image to a TImage through a stored procedure?
Regards,
Abdulaziz Jasser

Abdulaziz,

> How to load an image to a TImage through a stored procedure?

It's not clear what you mean. Do you mean, how to load a stored image
(Blob) from a table into a TImage component?

Uli

Uli,

<<Do you mean, how to load a stored image
(Blob) from a table into a TImage component?>>

Yes...

Basically I am trying to load a blob field that contains an image to TImage component.  Currently I am doing that through a query component but it's slow over slow connection.  Therefore, I want to do that through a stored procedure.
Regards,
Abdulaziz Jasser

Abdulaziz

I'm assuming that you're using c/s.

My bet is the majority of the time taken is the transfer of the image over the network to the TImage component not the time taken to run the query.

How big is the image?


Roy Lambert [Team Elevate]

Roy,

<<I'm assuming that you're using c/s.>>

Of course...



<<My bet is the majority of the time taken is the transfer of the image over the network to the TImage component not the time taken to run the query.>>

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.

Any logical explanation?
Regards,
Abdulaziz Jasser

Below is the same procedure with two different flavors (one using a query and the other one using a stored procedure).  At the bottom is the stored procedure.  REMEBER: both procedures give the same result.


procedure LoadUsersRights_1(iUserSysNo : Integer);
var
         i,c : Integer;
         oSL : TStringlist;
begin
         dmAccount.EDBStoredProc1.StoredProcName := 'LoadUsersRights';
         dmAccount.EDBStoredProc1.Prepare;
         dmAccount.EDBStoredProc1.ParamByName('iUserSysNo').AsInteger := iUserSysNo;
         dmAccount.EDBStoredProc1.ExecProc;

         oSL := TStringlist.Create;
         oSL.Text := dmAccount.EDBStoredProc1.ParamByName('Result').AsString;

         SetLength(aUserRights,oSL.Count);

         c := 0;
         i := 0;
         while i < oSL.Count-1 do begin
               aUserRights[c].iRightNo    := StrToInt (oSL[i]);
               Inc(i);
               aUserRights[c].bRightValue := StrToBool(oSL[i]);
               Inc(i);

               Inc(c);
         end;

         oSL.Free;
end;


procedure LoadUsersRights_2UserSysNo : Integer);
var
         i,c : Integer;

         sSQL    : String;
         qryData : TEDBQuery;

         oSL : TStringlist;
begin
         qryData := Nil;
         qryData := TQuery.Create(Application);
         qryData.SessionName  := dmAccount.EDBSession1.SessionName;
         qryData.DatabaseName := dmAccount.EDBDatabase1.DatabaseName;

         sSQL := sSQL + 'SELECT LIST(CAST(RightNo AS VARCHAR)+#13+#10+CAST(RightValue AS VARCHAR) USING #13+#10) AS RightsList';
         sSQL := sSQL + ' FROM  TB_UsersRights1 WHERE UserSysNo = %d';
         sSQL := Format(sSQL,[iUserSysNo]);

         GetRecord(qryData,sSQL);

         oSL := TStringlist.Create;
         oSL.Text := qryData['RightsList'];

         qryData.Close;
         qryData.Free;

         SetLength(aUserRights,oSL.Count);

         c := 0;
         i := 0;
         while i < oSL.Count-1 do begin
               aUserRights[c].iRightNo    := StrToInt (oSL[i]);
               Inc(i);
               aUserRights[c].bRightValue := StrToBool(oSL[i]);
               Inc(i);

               Inc(c);
         end;

         oSL.Free;
end;

{***********************************************************************}

//The stored procedure.

BEGIN

DECLARE Result      VARCHAR DEFAULT '';
DECLARE sRightsList VARCHAR DEFAULT '';
DECLARE qryData CURSOR FOR sSQL;

PREPARE sSQL FROM 'SELECT LIST(CAST(RightNo AS VARCHAR)+#13+#10+CAST(RightValue AS VARCHAR) USING #13+#10) AS RightsList
                  FROM  TB_UsersRights1                     
                  WHERE UserSysNo = ?';

OPEN qryData USING iUserSysNo;

FETCH FIRST FROM qryData('RightsList') INTO sRightsList;

CLOSE qryData;

SET Result = sRightsList;

RETURN Result;

END
Regards,
Abdulaziz Jasser

Abdulaziz


I may be being a bit thick but what does that have to do with loading an image into a TImage?

I can't see where or how you're doing your timings so I have no idea how relevant they are.

Are your timings a single point for each or the average of a series of runs? Its very difficult to meaningful timings on a Windows network when the differences are fairly small. Buffers can really mess things up.

I'm also surprised that its taking as long as it is either way. How many records and how many meet the criteria?

Roy Lambert [Team Elevate]

Roy,

<<I may be being a bit thick but what does that have to do with loading an image into a TImage?>>

Nothing, just want to see if a stored procedure can speed-up the loading just like in my previous example (which in theory should be the same).



<<I can't see where or how you're doing your timings so I have no idea how relevant they are.>>

I am mesuring the time before and after calling my procedure "SetMenuAccess".  Example:

StartTiming;
SetMenuAccess(1);
ShowTiming;



<<Are your timings a single point for each or the average of a series of runs?>>

I tried everything, many calls verses a fresh start of my application.  I even restarted Windows to get rid of the cashed buffers.

<<I'm also surprised that its taking as long as it is either way.>>

Me too..!!!


<< How many records and how many meet the criteria?>>

250 records out out of 2000 records.
Regards,
Abdulaziz Jasser

Abdulaziz

><<I may be being a bit thick but what does that have to do with loading an image into a TImage?>>
>
>Nothing, just want to see if a stored procedure can speed-up the loading just like in my previous example (which in theory should be the same).

Fair enough

><< How many records and how many meet the criteria?>>
>
>250 records out out of 2000 records.

I've tried simulating things here and there's no way, even using fileserver over my LAN with a table with 25k+ records and c1k hits I can get it to take even 1 second.

What's the spec on your server? Are there lots of other things running on it?

Roy Lambert [Team Elevate]

Roy,


<<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.

Regards,
Abdulaziz Jasser