Could someone veryify this -- memory leakage with TStoredProc (D3, Interbase)

Could someone veryify this -- memory leakage with TStoredProc (D3, Interbase)

Post by The Waxen Mot » Sun, 14 Sep 1997 04:00:00



    I'm having problems with memory leakage using D3 and Interbase. It seems
fairly easy to duplicate... here are the steps (complete with source for the
lazy-folk) :

Steps for reproducing db memory leakage

1.  Create a database with WISQL - c:/temp/testdb.gdb (SYSDBA, "masterkey")
2.  Run SQL script through WISQL :

CONNECT "c:\temp\testdb.gdb" USER "SYSDBA" PASSWORD "masterkey";

CREATE TABLE Table1 (
 BearNo  INTEGER,
 BearName CHAR(16)
);

SET TERM !! ;
CREATE PROCEDURE AddBear (BearNo INTEGER, BearName CHAR(16))

AS BEGIN
   INSERT INTO TABLE1 (BearNo, BearName)
               VALUES (:BearNo, :BearName);
   EXIT;
END !!

CREATE PROCEDURE KillBear (BearNo INTEGER)

AS BEGIN
   DELETE FROM TABLE1 WHERE BearNo = :BearNo;
   EXIT;
END !!

3.  Create a new BDE Interbase Alias : TempDb.  Only field changed in alias
config is "SERVER NAME" to "c:\temp\testdb.gdb"
4.  Create a new Delphi 3.0 application. Two stored procedures, connected
through a database component.  Both procedures called by number, all else is
default (other than database name or stored procedure name).
5.  Add Two buttons to the main form. Here is the body of the main form from
the "var" line down :

var
  Form1: TForm1;
  Bears : array[1..10] of string =
      ('Smokey', 'Yogi', 'Care', 'Teddy', 'Paddington',
       'Hooligan', 'Burglar', 'Crook', 'Deadbeat', 'Derelect');
  Done : Boolean;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
var i : Integer;
begin
   Done := False;
   while not Done do begin
      for i := 1 to 10 do begin
         spAdd.ParamByName('BearNo').AsInteger := i;
         spAdd.ParamByName('BearName').AsString := Bears[i];
         spAdd.ExecProc;
      end;
      for i := 1 to 10 do begin
         spKill.ParamByName('BearNo').AsInteger := i;
         spKill.ExecProc;
      end;
      Application.ProcessMessages;
   end;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
   Done := True;
end;

6.  Application compiles are runs with warnings/errors.  After pressing
Button1, application will show significant memory gain almost immediately.
Running queries through SQL Explorer will verify that the application is
successfully posting records to the table.
7.  Stop program.  Comment out the sp*.ExecProc lines and re-run the
program.  The application will show no memory gain after Button1 is pressed.

    Could someone verify this?  It would seem to be a rather large problem
for database applications that run for extended periods of time...

    -D

 
 
 

Could someone veryify this -- memory leakage with TStoredProc (D3, Interbase)

Post by Mark William » Wed, 17 Sep 1997 04:00:00




Quote:

>    Could someone verify this?  It would seem to be a rather large problem
>for database applications that run for extended periods of time...

When using parameterized queries/stored procedures it's my understanding
that you should always explicitly prepare and unprepare them (as shown
below):

Quote:>procedure TForm1.Button1Click(Sender: TObject);
>var i : Integer;
>begin
>   Done := False;

    spAdd.Prepare;
    spKill.Prepare;    
Quote:>   while not Done do begin
>      for i := 1 to 10 do begin
>         spAdd.ParamByName('BearNo').AsInteger := i;
>         spAdd.ParamByName('BearName').AsString := Bears[i];
>         spAdd.ExecProc;
>      end;
>      for i := 1 to 10 do begin
>         spKill.ParamByName('BearNo').AsInteger := i;
>         spKill.ExecProc;
>      end;
>      Application.ProcessMessages;
>   end;

    spKill.Unprepare;
    spAdd.Unprepare;

Quote:>end;

Does this make any difference?

--

Polyhedron Software Ltd.        
Programs for Programmers - QA, Compilers, Graphics

************ Visit our Web site on http://www.polyhedron.co.uk/ ************

 
 
 

1. D3/NT Memory Leakage

We have two IBM Netfinity Servers with 'Vinca' installed as the 'Hot
'Swap' option.
Both servers are 128 Ram.
We have a Memory Leakage problem which equates to approx 2meg
per day running 45 terminals with constant input.
Has anyone encountered the leakage.
When Pick is shutdown and rebooted the memory is returned.
Interested in any input.

Thanks

Tim

2. RAID5 and Oracle 7.3.4

3. Direсеrtly sending E-mail From D3 (D3/NT ior D3/Linux)

4. Multilingual Full-Text support

5. D3 7.0 to D3 7.1 Incompatibility problem with c functions on D3/Linux

6. OLEDB Providers and DBPROP_MULTIPLESTORAGEOBJECTS

7. SQL Server Memory Leakage??

8. update gram not working

9. Oracle 8.0.5/NT4 SP3 Memory Leakage Problem

10. Paradox 4.5 with Windows NT - Memory Leakage

11. Help Memory Leakage Problems

12. Memory leakage

13. sql server memory leakage.