Prepared Statament

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ThiagoRibeiro
Posts: 3
Joined: Mon 12 Dec 2016 22:45

Prepared Statament

Post by ThiagoRibeiro » Mon 12 Dec 2016 22:57

Hi,

We're starting to work with PGBouncer in your transaction mode, that is causing various problems of prepared statament, how can I disable this feature?

I have the following implementation:
procedure TspdADOQuery.Open(const aStatement: string; aDataSet: TDataset);
begin
inherited;
if FQuery.Connection = nil then
raise EspdConnectionError.Create('Conexão não definida');
FQuery.SQL.Clear;
FQuery.SQL.Add(aStatement);
FQuery.Prepare;
FQuery.Open;

if aDataSet <> nil then
CopyRecords(aDataSet);
end;

Where FQuery is the type TPgQuery and Connection have the following configurations:
FConn.ConnectString := Format(CONNECTSTR,[Self.Password, Self.User, Self.Database, Self.Server,
Self.Port, Self.User, Self.Password]);
FConn.Pooling := True;
FConn.PoolingOptions.MinPoolSize := 10;
FConn.PoolingOptions.MaxPoolSize := 100;
FConn.PoolingOptions.ConnectionLifetime := 30000;
FConn.PoolingOptions.Validate := False;
FConn.ConnectionTimeout := 30;

Error message:
prepared statement "PRSTMTST509186724" does not exist

Thanks!

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Prepared Statament

Post by azyk » Thu 15 Dec 2016 09:33

Maybe the error cause is not PgDAC, but the following PGBouncer specificity : https://pgbouncer.github.io/faq.html#ho ... on-pooling

To solve the problem try the PostgreSQL protocol without using prepared queries - protocol 2.0. For this before establishing a connection set the pv20 value to the TPgConnection.ProtocolVersion property. To use the pv20 constant, add the PgClasses unit to the uses section.

ThiagoRibeiro
Posts: 3
Joined: Mon 12 Dec 2016 22:45

Re: Prepared Statament

Post by ThiagoRibeiro » Thu 15 Dec 2016 16:38

I tried the following:

Code: Select all

procedure TspdADOQuery.Open(const aStatement: string; aDataSet: TDataset);
begin
  inherited;
  if FQuery.Connection = nil then
    raise EspdConnectionError.Create('Conexão não definida');
  FQuery.Options.AutoPrepare := false;
  FQuery.UnPrepare;
  FQuery.SQL.Clear;
  FQuery.SQL.Add(aStatement);
  //FQuery.Prepare;
  FQuery.Open;

Code: Select all

procedure TspdADOConnection.SetupConnection;
begin
  inherited;
  //passwd, userid, database, server, port (5432), userid, pswd
  FConn.ConnectString := Format(CONNECTSTR,[Self.Password, Self.User, Self.Database, Self.Server,
    Self.Port, Self.User, Self.Password]);
  FConn.Pooling := False;
//  FConn.PoolingOptions.MinPoolSize := 10;
//  FConn.PoolingOptions.MaxPoolSize := 100;
//  FConn.PoolingOptions.ConnectionLifetime := 30000;
//  FConn.PoolingOptions.Validate := False;
  FConn.ConnectionTimeout := 30;

ThiagoRibeiro
Posts: 3
Joined: Mon 12 Dec 2016 22:45

Re: Prepared Statament

Post by ThiagoRibeiro » Fri 16 Dec 2016 02:51

No changes to the code was needed, it was just a matter of checking whether the ProtocolVersion was indeed coming as pv20.

At some point of our code the PV30 is being forced, I did a test by forcing directly in code of pgdac Prasanthidandamudi and worked perfectly!

Thanks!

Post Reply