ZConnection problems connecting to PostgreSQL

[JD]

Hi there everyone,

I'm having a rather curious problem. I use Zeos 7.2 RC along with Lazarus 1.6/FPC 3.0 Win32. I also have PostgreSQL 9.5.1 Win32 installed. I wanted to connect to a PostgreSQL database in the Lazarus IDE using TZTable & TZQuery and browse the results in a DBGrid.

Anytime I try to set TZConnection's Connected property to True, I get the error in the attached image below saying libpq.dll was found BUT could not be loaded. Check compile target & library compatibility.

Problem loading PostgreSQL library error.png

It can't be a 32bit/64bit issue because everything I use for development is 32bit. In addition, the same test program works without problems when I replace Zeos components with SQLdb components shipped with Lazarus.

Thanks for your assistance with resolving this issue.

JD

[tsr8]

Hi

I think it could be 32/64b problem anyway.
Try to use 32bit libraries and your application target too.
Try to copy all libraries to your application directory. Leave "LibraryLocation" property blank.

I already have working application ZEOS 7.2 + PostgreSQL.

What system do you use?

[EgonHugeist]

Hi JD,

sorry for my absence. The issue is known to me.
Two cases to reproduce:

1. Compile-Target is different to library-target.
2. LibraryLocation points to a directory which isn't in the global environment variables. so Libpg.dll fails to load the additional libs they provide.

Long times ago i've been trying to resolve this issue with the patch of AB from synopse.info. Just see ZPlainLoader.pas

Code: Select all

function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: String; // AB modif
begin
  if FLoaded then
    Self.FreeNativeLibrary;
  temp := ''; //init for FPC
  FLoaded := False;
  Result := False;
  newpath := ExtractFilePath(Location);
  // AB modif BEGIN
  try
   if newpath <> '' then begin
     temp := GetCurrentDir;
     SetCurrentDir(newpath);
   end;
  // AB modif END

{$IFDEF UNIX}
  {$IFDEF FPC}
    FHandle := LoadLibrary(PAnsiChar(Location));
  {$ELSE}
    FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
  {$ENDIF}
{$ELSE}
  FHandle := LoadLibrary(PChar(Location));
{$ENDIF}

  // AB modif BEGIN
  finally
   if temp<>'' then
     SetCurrentDir(temp);
  end;
  // AB modif END
  if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
  begin
    FLoaded := True;
    FCurrentLocation := Location;
    Result := True;
  end;
end;

so switching the current directory while trying to load libpg.dll seems to fail or it simply doesn't help. Any suggestions to solve the issue is welcome..

[JD]

Thanks a lot for your reply EgonHugeist. I posted the same question on the Lazarus forums & miab3 posted responses saying copying the folowing files to the application's directory will make it work

zlib1.dll
libeay32.dll
libiconv-2.dll
libintl-8.dll
libpq.dll
libxml2.dll
libxslt.dll
ssleay32.dll

I did that & though I was able to connect at design time, browsing a table using ZTable at runtime was impossible. I kept getting the dreaded SIGSEGV exception.

I changed to ZQuery & it was impossible to set ZQuery.Active := True at designtime.

Here are the relevant threads on the Lazarus forums

http://forum.lazarus.freepascal.org/ind ... n.html#new
http://forum.lazarus.freepascal.org/ind ... #msg207766

Thanks,

JD

[JD]

Hi there everyone,

I just wanted to know if this problem has been fixed maybe in the trunk version.

Thanks a lot.

JD

[miab3]

And have you tried to upload client library files to the system directory SysWOW64(or System32)?

Michal

[JD]

And have you tried to upload client library files to the system directory SysWOW64(or System32)?

Michal

Hi there Michal & thanks for replying. Are you refering to these files

zlib1.dll
libeay32.dll
libiconv-2.dll
libintl-8.dll
libpq.dll
libxml2.dll
libxslt.dll
ssleay32.dll

that you listed in your Lazarus forum post that I referred to earlier?

JD

[miab3]

Best, all the dll files from bin directory of the PodtgreSQL distribution that you are using.

Michal

[JD]

I've been away for a while and now I'm back to this project. Unfortunately, it still doesn't work for me.

I'm using Lazarus 1.6.2/FPC 3.0 on Windows, I've installed PostgreSQL 9.5.6 and Zeos 7.1.2 RC. I've copied all the relevant DLLs from my PostgreSQL \bin directory to the sample Lazarus project directory. When I try to connect at designtime, I get the error in the screencap below

Lazarus + Zeos + PostgreSQL 32 bit error.png

I just don't understand it. Everything is 32 bit so why the compatibility problem. To further complicate things, the same code works when I use Lazarus' SQLdb.

What am I doing wrong with the Zeos connection?

Thanks,

JD

[JD]

The code for the test project is as shown below. It tries to connect to the sample PostgreSQL Sakila DVD Rental database.

Code: Select all

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, FileUtil, Forms, Controls, Graphics, Dialogs, DBGrids,
  StdCtrls, ZConnection, ZDataset;

type
  { TForm1 }
  TForm1 = class(TForm)
    btnActivateQuery: TButton;
    cboQueries: TComboBox;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZTable1: TZTable;
    procedure btnActivateQueryClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormShow(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.btnActivateQueryClick(Sender: TObject);
begin
  try
    ZConnection1.Connect;
    ZQuery1.Active := True;
    ZQuery1.SQL.Text := cboQueries.Text;
    ZQuery1.Open;
  except
    On E: Exception do
      MessageDlg('The query is not active', mtWarning, [mbOK], 0);
  end;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  if ZQuery1.Active then
    ZQuery1.Close;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  cboQueries.Items.Add('select * from public.actor');
  cboQueries.Items.Add('select * from public.address');
  cboQueries.Items.Add('select * from public.category');
  cboQueries.Items.Add('select * from public.city');
  cboQueries.Items.Add('select * from public.country');
  cboQueries.Items.Add('select * from public.customer');
  cboQueries.Items.Add('select * from public.film');
  cboQueries.Items.Add('select * from public.film_actor');
  cboQueries.Items.Add('select * from public.film_category');
  cboQueries.Items.Add('select * from public.inventory');
  cboQueries.Items.Add('select * from public.language');
  cboQueries.Items.Add('select * from public.payment');
  cboQueries.Items.Add('select * from public.rental');
  cboQueries.Items.Add('select * from public.staff');
  cboQueries.Items.Add('select * from public.store');
end;

end.

I really want to make it work with Zeos because that is what I use for my projects.
JD

[marsupilami]

Hello jd,

I suggest you use the latest Zeos 7.2 from SVN. Even Zeos 7.1.2 RC isn't the current version of Zeos 7.1 anymore. I think, I had the same problem with Zeos 7.1 and fixed it with code like this:

Code: Select all

ChDir(ExtractFilePath(ZConnection.LibraryLocation));
ZConnection.Connect;
Chdir(ExtractFilePath(Application.ExeName));

With best regards,

Jan

[JD]

Hi there Jan,

Thanks for your reply. I did as you suggested and downloaded the latest version 7.2.1 RC (svn 3980). I installed it an tried again and the result is the same. However I tried also to connect at design time and I got the following error

DLL Error.png

The message says the ordinal 4540 was not found in the libeay32.dll

I copied the dll files from the PostgreSQL 9.5.6 bin directory.

What do I do now? Thanks for your assistance.

JD

[JD]

I tried to debug the code and I saw that it just stops on the line ZQuery1.Open in the image below (line 48). It just stops responding.

Debug connection.png

JD

[JD]

Well I modified the same example for use with mORMot's SynDBZeos and my Zeos 7.2.1-rc (svn 3980) installation. And to my surprise, it worked.

All connection and browsing is done at runtime, since mORMot does not use design time components.

The code in my mORMot example is shown below:

Code: Select all

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, DBGrids,
  StdCtrls,
  // Synopse units
  SynDB,    // for the dPostgreSQL of the TSQLDBDefinition type
  SynDBZeos
  ;

type
  { TForm1 }
  TForm1 = class(TForm)
    btnActivateQuery: TButton;
    cboQueries: TComboBox;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    procedure btnActivateQueryClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.btnActivateQueryClick(Sender: TObject);
var
  fConnection: TSQLDBZEOSConnectionProperties;
  iRows: ISQLDBRows;
  Row: Variant;
  intRowCount: integer;
begin
  // Usual connection method
  //fConnection := TSQLDBZEOSConnectionProperties.Create('postgres', 'sakila dvdrental', 'postgres', 'password');
  // URI method
  fConnection := TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dPostgreSQL, 'localhost:5432'),
                  'sakila dvdrental', 'postgres', 'password');
  //
  try
    //
    intRowCount := 0;
    //
    iRows := fConnection.Execute(cboQueries.Text, [], @Row);
    //
    while iRows.Step do
    begin
      Inc(intRowCount);
      //
      ShowMessage('ID : ' + IntToStr(Row.Actor_id) + LineEnding +
                  'FirstName : ' + Row.First_Name + LineEnding +
                  'LastName : ' + Row.Last_Name + LineEnding +
                  'LastUpdate : ' + DateTimeToStr(Row.Last_Update));
      //
      if intRowCount = 5 then break;
    end;
  finally
    fConnection.Free;
  end;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  cboQueries.Items.Add('select * from public.actor');
end;

end.

Why does it work with Lazarus/mORMot/Zeos and fail with plain Lazarus + Zeos? That is the mystery!

JD

[marsupilami]

Hello JD,

hmm - I have a problem here because I think it is something on your computer. For me everything works es expected. My Environment:
- Windows 10, 64 Bits
- FPC 3.0.2, 32 Bits
- Lazarus 1.6.4, compiled from Source
- PostgreSQL 9.6.2, 32 Bits, installed from EnterpriseDB installer

My properties in ZConnection:
ClientCodepage = UTF8
Database = postgres
HostName = localhost
LibraryLocation = C:\Program Files (x86)\PostgreSQL\9.6\bin\libpq.dll
Protocol = postgresql-9
User = postgres

I have a simple Button that just calls ZConnection.Connect

Maybe you have some old files somewhere, that libpq tried to load and where it fails?

With best regards,

Jan