Remote Postgres connection with MBS Xojo SQL Plugin

Add-Ons
1

Trying to initiate a remote connection with MBS Xojo (Real Studio) SQL Plugin commands below:

[code]dim db as new SQLDatabaseMBS

db.DatabaseName = “PostgreSQL:123.456.789.001@test”
db.UserName = “username”
db.Password = “password”

If db.Connect then
MsgBox “We are connected!”
MsgBox "Server Version: "+db.Connection.ServerVersionString
else
MsgBox db.ErrorMessage
End if
[/code]

The following error is generated:

dlopen(libpq.dylib, 1): no suitable image found. Did find:
/usr/lib/libpq.dylib: mach-o, but wrong architecture
dlopen(libpq.5.dylib, 1): no suitable image found. Did find:
/usr/lib/libpq.5.dylib: mach-o, but wrong architecture

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

Is this library on the remote server?

2

you miss a line like this:

// where is the library?
db.SetFileOption SQLConnectionMBS.kOptionLibraryPostgreSQL, getfolderitem(“libpq.5.1.dylib”)

and you need to have the client library for postgres somewhere next or inside your app.

3

if you need, I have some libs here:

http://www.macsw.de/plugin/Libs/

but normally you can simply get it from the postgres installation.

The libraries you have there in /usr/lib are probably only 64-bit ones.

4

Would this be connecting to a server on the internet ?
Or a Lan ?

Either way I’d make sure you can / do use an connection that runs over SSL / TLS
Something slightly more secure than an unencrypted connection.

5

OK. The library files helped.

Now getting error:

could not translate host name “123.456.789.001:5432” to address: nodename nor servname provided, or not known

(I replaced the real ip address with 123.456.789.001)

How do you initiate an SSL connection with the MBS plugin?

6

Well, first connection string is like this:

A string describes PostgreSQL connection optinons, The fromat is “[<server_name>]@][database_name][;]”:

“” or “@” - empty string or ‘@’ character, connects to a local server.
“<database_name>” or “@ <database_name>” - connects to a database with the specified name on local server.
“<server_name>@” - connects to the specified server.
“<server_name>@<database_name>” - connects to a database with the specified name on the specified server.
<server_name> can be a cobination of hostname/address and port number (<server_name> = [,])) or unix_socket path.

So you may want to try “123.456.789.001,5432@”

7

Did you enable SSL for postgres Server?

8

As far as I know you just add ssl mode parameter to connection string.
So a “;sslmode=require” should be added to force SSL.

9

This worked and I was able to establish a connection.

Added sslmode=require to the connection string and the result was:

FATAL: invalid command-line arguments for server process
HINT: Try “postgres --help” for more information.

10

Is SSL required for you?
Also does your server has SSL enabled?
We may check that in the next days.

11

Yes, the application is a medical data application were a secure connection is required. We are currently using SSL via Xojo.

We are having an issue with the Xojo having trouble handling blob data (crashing) in the Windows OS only and I was testing the MBS plugin to see if it handled it better. Upon reading the info on the MBS plugin, I was hoping there might be better performance as well.