Delphi 2010 + Zeos + PostgreSQL

[rblsystem]

Hello guys , okay ?
 
    I'm trying to display a DBGrid result is a select in PostgreSQL DB . For this, I am using Zeos .
 
    The connection to the database is being made through ZConnection component. I set all component properties and make the connection and usually works without any error .
 
    Furthermore , I can also connect to the database using pgAdmin . I can access the tables , and also to select etc with no problem .
 
    The problem happens with ZQuery component. Using this component , return the select is :
 
Error 1 :
 
With ZQuery1 Do Begin
Close ;
Sql.Clear ;
Sql.Add ( 'SELECT * ' ) ;
Sql.Add ( 'FROM clients ' ) ;
Open ;
end ;
 
SQL Error : ERROR : prepared statement " 13607504130690304 " does not exist .
 
    Or, the following error appears :
 
Error 2 :
 
With ZQuery1 Do Begin
Close ;
Sql.Clear ;
Sql.Add ( 'SELECT * ' ) ;
Sql.Add ( 'FROM clients ' ) ;
ExecSql ;
end ;
 
Can not open the Resultset .
 
    Note that the only difference is that in the select uitlizo 1st OPEN and 2nd EXECSQL use .
 
    What can I be doing wrong for these errors happen ?

    I need to make some more configuration setting in ZConnection , associate another component to ZQuery ?
 
Thank you ,
 
Rogério .

[marsupilami]

Hello Rogério,

which Version of Zeos and PostgreSQL do you use?
Best regards,

Jan

[rblsystem]

Jan,

Zeos version 7.0.0 alpha
PostgreSQL version 9.1.1

Thanks a lot.

[rblsystem]

Sorry,

The correct version of Zeos is 7.1.2 stable

PostgreSQL version is 9.1.1

Thanks a lot,

Rogério.

[marsupilami]

Hello Rogério,

this is strange because I use the current Zeos 7.1 with Postgresql too. But for me it is 7.1-patches from the SVN. PostgreSQl is Version 9.2 here and Delphi is Delphi 2007 and Delphi XE 2.
Which settings do you do in the ZConnection Object?

ZQuery.Execute is only for executing statements that have no result sets, like update statements. If you want to do a select always use open.

Best regards,

Jan

[rblsystem]

The settings are:

ControlsCodePAge = cCP_UTF16
LibraryLocation = ...\libpq73.dll
Port = 5432
Protocol = postgresql-9

If you still miss some information on the settings, let me know so I can inform correctly. Or, if you miss a particular property, ask me what I inform.

As for the version of Zeos, you think I'd better change the same that you are using?

If yes, where I Can download it?

Thank you,

Rogério.

[miab3]

@rblsystem,

LibraryLocation = ...\libpq73.dll

This is not a good library. (It is 10 years old).
Use of this included in the server 9.1.1 distribution.

Why do you not write about all this in the first post?

Michal

[EgonHugeist]

What i'm doing, i can't reproduce your reported issue.

Does Michal's advice help you? Is your iussue resolved?

[ricardoclaudine]

Friend, this problem occurs when using a recent Zeus version that works with Real Prepared Statement (and not with Emulated Prepared Statement as before) connecting to a PostgreSQL Server with active pgBouncer and configured in "Transaction Pooling" Mode. This Mode does not support Prepared Statements, but not figured out how to disable them in Zeus:
 
https://wiki.postgresql.org/wiki/PgBouncer
http://pgbouncer.projects.pgfoundry.org ... on_pooling
 
If anyone knows how to disable Prepared Statements in Zeus, help me please!
 
 
Thanks!

[EgonHugeist]

Lorbs...

Code: Select all

function TZPostgreSQLConnection.CreatePreparedStatement(
  const SQL: string; Info: TStrings): IZPreparedStatement;
begin
  if IsClosed then
     Open;

  {$IFDEF ZEOS_TEST_ONLY}
  Case GetTestMode of
    0:
  {$ENDIF}
      if GetServerMajorVersion >= 8 then
        Result := TZPostgreSQLCAPIPreparedStatement.Create(GetPlainDriver, Self, SQL, Info)
      else
        Result := TZPostgreSQLClassicPreparedStatement.Create(GetPlainDriver, Self, SQL, Info);
  {$IFDEF ZEOS_TEST_ONLY}
    1: Result := TZPostgreSQLClassicPreparedStatement.Create(GetPlainDriver, Self, SQL, Info);
  end;
  {$ENDIF}
end;

You could patch back to the TZPostgreSQLClassicPreparedStatement but this is'nt the solution at all. Have to think about a simple TZConnection.Properties[''] parameter..
I think i can fix this quickly...

Michael

[EgonHugeist]

Edit:

Patch done R3178 /testing-7.2 (SVN)

Add TZConneciton.Properties.Values['EMULATE_PREPARES'] := 'True'; and all is fine again

[EgonHugeist]

Triple post:

my first patch was wrong! Second fix is already commited. Our Prepared stmts are able to fall back to 100% emulation modes. Add the suggested param and it should work fine.