Connect to Postgres in Xojo using MBS Plugins

Extras
1

Today we like to connect to Postgres Database server from Xojo using MBS Xojo SQL Plugin. For that we need the client libraries for Postgres, but where to get them?

macOS

For macOS you can download the Postgres App to get a local test server for Postgres on your Mac. You can directly point to the libpq.5.dylib within the application.

We adjusted the install names to make them work into the Frameworks folder of your application. The libpq library needs crypto and ssl libraries, so these must be in the same folder. You can find these with a test project here: PostgreSQL Libraries. You can add a these using a CopyFiles build step into the Frameworks folder of your application.

Var con As New SQLConnectionMBS
Var cmd As New SQLCommandMBS
			
Var ResourceFolder As FolderItem = SpecialFolder.Resources
Var ContentsFolder As FolderItem = ResourceFolder.Parent
Var FrameworksFolder As FolderItem = ContentsFolder.Child("Frameworks")
Var LibFile As FolderItem = FrameworksFolder.child("libpq.5.dylib")

If LibFile.Exists = False Then
	MessageBox "libpq not found?"
End If
			
con.SetFileOption con.kOptionLibraryPostgreSQL, LibFile

Linux

For Ubuntu we can just install the postgres client package and get libpq.so file installed. Once installed, the plugin should find it automatically. e.g. for Ubuntu 24 on ARM, we find the library in /usr/lib/aarch64-linux-gnu/libpq.so.5

You can look for libpq5 package in the package manager or just install it via Terminal:

apt-get install libpq5

Windows

For Windows we download the Windows installer for PostgreSQL Server. We look into the C:\Program Files\PostgreSQL\17\bin folder here and find all the DLLs we need.

The libpq.dll doesn’t do it alone. As you may guess from above, that it needs at least the ssl and crypto libraries, too.

Here are the dependency:

  • libpq.dll
    • libintl-9.dll
      • libiconv-2.dll
      • libwinpthread-1.dll
    • libssl-3-x64.dll
      • libcrypto-3-x64.dll
    • libcrypto-3-x64.dll

Plus of course DLLs like VCRuntime140.dll coming with Visual Studio 2015 runtime libraries.

You can find the download from us here with all six DLLs: PostgreSQL Libraries.

Connect

To connect, we then define a connection string with host, port and database name. Then we call Connect method (or function) on the SQLDatabaseMBS class or SQLConnectionMBS class.

// put in your IP or DNS name, port and the database name
Var databaseName As String = "cs"
Var host As String = "localhost"
Var port As Integer = 5432

Var ConnectionString As String = host + "," + port.ToString + "@" + databaseName

con.Connect(ConnectionString, "username","password", SQLConnectionMBS.kPostgreSQLClient)

Once connected, you can do whatever queries you like to do.

Let us know if you have questions

2

I would like to know what are the key advantages of the Postgres MBS plugin over the Xojo one ?
thanks.

1 Like
3

In general it’s an alternative. If you are unhappy with the built-in class in Xojo, you have a different one to use. For some people it’s about calling stored procedures, some prefer streaming BLOB values and others need bulk transfers. Depends on what you do.

Here a few points applying to PostgreSQL from our website:

  • Connect
    • Connect to SQL databases in multithreaded mode for better GUI responsibility.
    • Use your own copy of the native connector library.
  • Prepared Statements improved
    • Bind parameters to prepared statements by name instead of index.
    • Binding type is optional on prepared statements.
    • Bind using variants or dictionary with values.
    • Inspect bound values in debugger.
  • Improved performance
    • Depending on what you do with your database our plugins can be magnitudes faster!
    • Transfer rows in bulk mode to reduce network traffic and increase speed.
    • Perform SQL queries and SQL commands in multithreaded mode for better GUI responsibility.
    • Handle blob values with more than one GB of size by streaming in chunks.
  • Integrated for Xojo
    • Works with Xojo with iOS, Web, Desktop and Console.
    • Can provide RecordSets for use with Xojo reports engine.
  • Extra features
    • Supports moving forward/backword, first and last in result sets.
    • We ping the servers regularly to keep connection alive.
4 Likes