TPQConnection create a second access
lazarus / 16-Databases

----------------------------------------------------------------------
Michl · 2013-09-18 20:29

Hi,

this code works fine (Zeos).Code: [Select]    procedure
TForm1.Button1Click(Sender: TObject);
    begin
      if ZConnection1.Connected then begin
        ZQuery1.Active:=False;
        ZConnection1.Connected:=False;
      end else begin
        ZConnection1.Connected:=True;
        ZQuery1.Active:=True;
      end;
    end; first click on button -> 1 access is created
second click -> access is closed

the same procedure with TPQConnection and TSQLQuery create 2 hits
(first click)
second click 1 access is closed, 1 is active

I tried follow code to delete the second
Code: [Select]    procedure TForm1.Button1Click(Sender: TObject);
    begin
      if PQConnection1.Connected then begin
        SQLQuery1.Active:=False;
        SQLQuery1.Close;
        SQLQuery1.Destroy;
        SQLQuery1.Free;
        PQConnection1.CloseDataSets;
        PQConnection1.CloseTransactions;
        PQConnection1.Connected:=False;
        PQConnection1.Close;
        PQConnection1.Free;
      end else begin
        PQConnection1.Connected:=True;
        SQLQuery1.Active:=True;
      end;
    end; nothing works

Does anybody know, how to close the second access?

I add two projects, where you can see, what I mean (you have to enter
your databasename, username, password and query.sql)

Maybe it´s a bug, I use Lazarus 1.1 r42453 FPC 2.7.1
i386-win32-win32/win64

Here is a crosspost
http://www.lazarusforum.de/viewtopic.php?f=17&t=7218

----------------------------------------------------------------------
ludob · 2013-09-20 06:32

This was a design decision taken in sqldb. Since you can have only one
transaction per db connection in PostGres, a new connection is created
per transaction and disconnected after a COMMIT or ROLLBACK. When
opening a TPQConnection, a connection to the DB is established which
is not re-used.

This has been fixed in 2.7.1 (trunk). A connection pool is used and
idle connections (no active transaction) are returned to the pool.
This has also the advantage that the number of re-connections (and its
authorization overhead) is limited enormously when a lot of small
transactions are used.

----------------------------------------------------------------------
Michl · 2013-09-20 09:38

Ok, I use FPC 2.7.1.

My problem is, when I want to make a Backup, the simplest way is to
drop my database in PG and load the Backup (no double items in there
and wrong items are deleted). But to drop the database is not
possible, cause I can not delete the dead access. What can I do?

My linked project shows the dead access, if it helps?!

----------------------------------------------------------------------
ludob · 2013-09-21 06:21

Dit you update your trunk recently? 2 days ago a bug was solved that
caused connections to be "lost" in the pool?

----------------------------------------------------------------------
Michl · 2013-09-21 22:26

I would test it, but I´m failed.

I can build a Lazarus (42906) with the actual fpcbuild, but by
compiling a own project there a to many bugs, which I don not know to
handle

So, I will wait for a snapshot...

Thank you very much, for your attention and work!