Driver loading issue when connecting to PostgreSQL after connecting to Oracle

[MJFShark]

It appears that if I connect to Oracle, then attempt a connection to PostgreSQL in the same application, I get an error when LoadLibrary for the postgresql driver is called.

System Error. Code: 126. The specified module could not be found.

However if I connect to PostgreSQL first, then connect to Oracle, everything is fine. Here's my code to cause the issue, but it's possible this is something that is specific to my environment. I'd be interested if others have an issue doing the same. Any thoughts or suggestions appreciated.

Code: Select all

    ZConn := TZConnection.Create(nil);
    ZConn.Protocol := 'oracle';
    ZConn.User := 'scott';
    ZConn.Password := 'tiger';
    ZConn.Database := '//192.168.11.112/orcl.mfm2';
    ZConn.LibraryLocation := 'C:\Oracle\19\32\oci.dll';
    ZConn.AutoCommit := False;
    ZConn.TransactIsolationLevel := tiReadCommitted;
    ZConn.Connect;

    ZConn2 := TZConnection.Create(nil);
    ZConn2.Protocol := 'postgresql';
    ZConn2.User := 'mark';
    ZConn2.Password := 'fish';
    ZConn2.Database := 'testdb';
    ZConn2.HostName := 'mfms1';
    ZConn2.LibraryLocation := 'C:\U\DLLS\Postgres\32bit\libpq.dll';
    ZConn2.AutoCommit := False;
    ZConn2.TransactIsolationLevel := tiReadCommitted;
    ZConn2.Connect;  // This will fail for me.

Thanks
-Mark

[aehimself]

I had the same, but with Oracle and MSSQL (FreeTDS). Symptom was the same - Oracle first, module could not be found. MSSQL first, all fine.
Afaik the problem is solved now... which version of Oracle driver and which version of Zeos are you using?

[MJFShark]

Thanks! The versions are:

Zeoslib Version: 8.0.0-beta
Oracle Client Version: 19.3.0
PostgreSQL Client Version: 10.0.13

Could it relate to Oracle and PostgreSQL drivers both using some other dlls but different versions? This is a total guess on my part, but my thoughts went to different versions of the Visual C redistributable (or some such.)

Alternately, is there a way to free a driver dll programmatically? It looks like FreeLibrary is never called until program exit (or at least I couldn't find a way to cause it.)

If I force load the pg dll at the beginning of my program then it completely solves the issue:

Code: Select all

  Location := 'C:\U\DLLS\Postgres\32bit\libpq.dll';
  newpath := ExtractFilePath(Location);
  if newpath <> '' then begin
    temp := GetCurrentDir;
    SetCurrentDir(newpath);
  end;
  FHandle := LoadLibrary(PChar(Location));

-Mark

[MJFShark]

After going down a rabbit hole of research, I modified TZNativeLibraryLoader.ZLoadLibrary and changed the SetCurrentDir to SetDllDirectory instead and it seems to solve the problem for me!

Code: Select all

    if newpath <> '' then 
    begin
      SetDllDirectory(PChar(newpath));
      { Original code:
      temp := GetCurrentDir;
      SetCurrentDir(newpath);}
    end;

[MJFShark]

Another option is to just use:

Code: Select all

  FHandle := LoadLibraryEx(PChar(Location), 0, LOAD_WITH_ALTERED_SEARCH_PATH);

This seems to do the same thing but without needing either SetCurrentDir or SetDllDirectory.

-Mark

[aehimself]

I'm not so sure LOAD_WITH_ALTERED_SEARCH_PATH is a good solution, at least not the way libraries are loaded at the moment.

According to MSDN:

If this value is used and lpFileName specifies an absolute path, the system uses the alternate file search strategy discussed in the Remarks section to find associated executable modules that the specified module causes to be loaded. If this value is used and lpFileName specifies a relative path, the behavior is undefined.
If this value is not used, or if lpFileName does not specify a path, the system uses the standard search strategy discussed in the Remarks section to find associated executable modules that the specified module causes to be loaded.

This value cannot be combined with any LOAD_LIBRARY_SEARCH flag.

The benefit of changing the directory and calling the basic functions are:
- Should work fine on Unix and WIndows
- Should work fine on FPC and older versions of Delphi too (I don't know when LoadLibraryEx was added to Windows.pas, so we do not overdefine)
- Should work with relative paths as well

I doubt it's because of different VC runtime, they should be in the search path and client DLLs should find them automatically. If you ask me it's an Oracle specialty. Try with 19.8.0; this issue disappeared for me when I upgraded both Oracle and the FreeTDS driver to the latest (also, Zeos to 8.0.0-9a2564ea from GIT). Oracle as the common point fills me with confidence, however if the update does not solve your problem we should investigate further on different platforms too (Linux, FPC) if they are affected.

Alternately, is there a way to free a driver dll programmatically? It looks like FreeLibrary is never called until program exit (or at least I couldn't find a way to cause it.)

Even if you could, don't do it. If LoadLibrary really can not find the correct module, this issue has to be fixed on Zeos level: forcing it to unload is a really "hacky" workaround. Plus it can cause other connections to reset.
On the other hand keeping the libraries opened until the end of times is NOT a good solution, I don't like this in Zeos either. It's really annoying if I want to update the client libs and I have to close my program to be able to do so. I'd expect it to be released when the (last) connection is disconnected which is using this library.

[MJFShark]

I just tested with Oracle 19.8 and I still have the same issue. I also tried the 11 and 12 Oracle clients and they all had the problem. I'll probably use one of these solutions (either setdlldirectory or LWASP) in my own version just to solve my local problem. It might be something to keep in mind if others have the same issue down the road. It could still just be something about my local system after all. Thanks for your help and insights!

-Mark

[aehimself]

Question... would you mind sharing the locations you set for the two libraries?
Things which I am mostly interested in are if they are in the same folder and if you are using absolute / relative paths.
I know you are developing on Windows, but are you using Delphi or Lazarus? 32 or 64 bit?

This information would make creating a test case easier.

Edit: I found the locations in the first post. Sorry about that!

[aehimself]

I attempted to reproduce the issue by connecting to Oracle and MSSQL on 32 bit and 64 bit, using absolute or relative paths defined in LibraryLocation.
All my attempts were successful:

Oracle connected by using C:\Users\user\Documents\a\oci.dll
MSSQL connected by using C:\Users\user\Documents\b\sybdb.dll

Worths mentioning that I don't have any connection libraries installed (I'm using "barebone" Oracle: 3 .DLLs copied from the .ZIP) or put in the system path.

If you are using the latest snapshot of Zeos, this should be a Delphi vs Lazarus, or an Oracle-PostgreSQL driver interference. Unfortunately I don't have PostgreSQL at home to test.

As it turns out, the usage of LoadLibraryEx was already requested in ticket 218. OP even describes the issue quite well:
The problem is windows can't find all needed dll's dependences for dll.

[aehimself]

I created a patch and issued a pull request on GitHub.

There are a couple of things which worries me though, but the truth will be revealed once the patch is accepted.
I don't know when IncludeTrailingPathDelimiter, LoadLibraryEx and LOAD_WITH_ALTERED_SEARCH_PATH was introduced. It's possible that we manually have to define these on older versions of Delphi / Lazarus.

[MJFShark]

Very cool! It seems you have things in hand, but to answer some questions:

I only use absolute paths.
All database client dlls are in separate directories and generally not in the system path.
I've only seen the issue going from Oracle to PostgreSQL (I do use SQLServer but only though OleDB)
I'm using Delphi 10.4

Patch looks good! I know I've used SysUtils.ExpandFileName to do relative to absolute path conversions in the past (which appears to be cross platform but there may be backwards compatibility reasons of course.)

Thanks! -Mark

[aehimself]

I only use absolute paths.
All database client dlls are in separate directories and generally not in the system path.
I've only seen the issue going from Oracle to PostgreSQL (I do use SQLServer but only though OleDB)
I'm using Delphi 10.4

So your setup is the same as mine, the only difference being I'm using 10.4.1 and FreeTDS instead of PostgreSQL. That is really strange to be honest. Unfortunately there's no way to debug it from Zeos as the library will simply fail to load if dependencies couldn't be found. At least I don't have anything in my mind to track that down.

I know I've used SysUtils.ExpandFileName to do relative to absolute path conversions in the past (which appears to be cross platform but there may be backwards compatibility reasons of course.)

Holy API, Batman. I did not know both Delphi and FPC already has a method to do this conversion for us! Even better, it seems it's a WinAPI call in the background and the OS knows things better than a simple string manipulation. Should be slightly faster too.
I changed the logic to use ExpandFileName and it seems to work correctly so I replaced that in the pull request. It even makes things easier as if older versions of Delphi / FPC do not support it we need to reintroduce only one method instead of 3.
Nice catch, thank you for that!

Edit: this method is even better, because I just realized I forgot to initialize the dlllocation variable if Location is an absolute path. So most probably absolute paths are ignored, simply checking the default search path in that version. ExpandFileName makes the local variable obsolete so it solved that bug too.
The first version is already accepted in the SF source, let's hope the next one follows soon as well :)

[EgonHugeist]

Thank you both for looking into that issue. I think i was running into same trap long time ago, IIRC.

Hope we did not break anything on the non Windows-targets?!
Thank you both for yours help!

[aehimself]

@Egonhugeist,

I was thinking about that and (since I can not test it) later I re-implemented the "change directory" logic because of this exact reason.
ExpandFileName had some features I did not count with so at the moment it should be broken for Windows too if location is NOT speified. There was a third commit in my pull request which did not yet get moved to SVN which is fixing it for good.

Edit: I resynced my repo and posted one more pull request with the missing commit. I wish it would be easier... I just saw that GitHub allows .diff and .patch views for pull request. If I send something like this instead of a pull request would it be easier?

Original pull request: https://github.com/marsupilami79/zeoslib/pull/21
Patch: https://github.com/marsupilami79/zeoslib/pull/21.patch
Diff: https://github.com/marsupilami79/zeoslib/pull/21.diff

[aehimself]

As far as I can see the new commit got accepted to SVN and 7.3 builds went back from failing miserably to their usual state. So I think we are all good but I'll wait for Jan to confirm.

In the meanwile, @MJFShark can you pull the latest snapshot of 8.0 and see if it works for you this way?