Using SQLPostgresMBS under Win10

I last used SQLPostgresMBS for Windows apps in Xojo 2019 where I could get by with using the InternalPostgresSQLLibrary, but “This item is deprecated and should no longer be used. You can use postgres driver libraries instead.”

So in a clean new Win10 64-bit install, clean Xojo 2021R2.1 install, and MBS 21.4 plugins, tried the MBS example “SQLDatabaseMBS PostgresSQL Connect”.

It appears to be aimed at macOS users. If I change the FindFile for libpg.5.1.dylib and switch to libpq.dll and add a CopyFiles build step to insert libpq.dll under debug the FindFile does indeed find my libpq.dll yet it fails with:

libpq.dll: The specified module could not be found

DBMS API Library loading fails
This library is part of DBMS client installation, not SQLAPI++
Make sure DBMS client is installed and
this required library is available for dynamic loading.

What kOptionLibraryPostgreSQL options need to be set for using Postgres under Win10 with SQLConnectionMBS class?

The MBS example project can be used to demonstrate the error, though you need to change the App Open event FindFile to libpq.dll, and a CopyFiles build step to make the file available. (Or change the FindFile() to the installed dll location.)

Postgres since version 11 is only available in 64 bit binaries for Windows. So my libpq.dll is from a 64 bit build of Postgres.

I’ve tried both 64-bit and 32-bit builds in Xojo, but assume I should use 64 bit if the Postgres installation is 64 bit.

Any pointers on getting SQLDatabaseMBS connections to work in Win10 with Xojo 2021R2?

First you could still use the internal library, but we prefer to use the external libraries now.

I was thinking I had tried that, and the InternalPostgreSQLLibraryMBS docs show that as not supported for Windows (red X under Windows column).

Just commenting out the SetFileOption in the example program yields:

libpq.dll: The specified module could not be found.

pq.dll: The specified module could not be found

DBMS API library loading fails …

Do you have an example to show how to do this under Windows?

So you put the files for libpq.dll in a folder, usually also ssl dll.
Use SQLGlobalsMBS.SetCurrentWorkingDirectory() to set the path for that folder.
Then point to the libpq.dll to load it.

dim file as FolderItem = GetFolderItem("c:\libpq.dll", folderitem.PathTypeShell)

// important to change folder to make sure the files are found.
dim folder as FolderItem = file.parent
Call SQLGlobalsMBS.SetCurrentWorkingDirectory(folder)
db.SetFileOption db.kOptionLibraryPostgreSQL, file

See this screenshot of all the dlls:
Bildschirmfoto 2021-09-17 um 17.46.12

Ahh so I was being misled by the message saying libpq.dll was not found, when it was actually the other support dll’s that were not found. I got the sample project to work by changing the top portion of the App > Open event to:

dim db as new SQLDatabaseMBS

// where is the library?
#if TargetMacOS
db.SetFileOption SQLConnectionMBS.kOptionLibraryPostgreSQL, FindFile(“libpq.5.1.dylib”)
#endif

#if TargetWindows
dim file as FolderItem = FindFile(“libpq.dll”)

// important to change folder to make sure the files are found.
dim folder as FolderItem = file.parent
Call SQLGlobalsMBS.SetCurrentWorkingDirectory(folder)
db.SetFileOption db.kOptionLibraryPostgreSQL, file
#endif

// connect to database

So now to apply similar logic to my project…

Thanks!

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.