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!
Prepared Statament
Re: Prepared Statament
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.
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.
-
- Posts: 3
- Joined: Mon 12 Dec 2016 22:45
Re: Prepared Statament
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;
-
- Posts: 3
- Joined: Mon 12 Dec 2016 22:45
Re: Prepared Statament
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!
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!