Error When Specifying Connection String for PostgreSQLDatabase

[Aaron_Schacht]

I get this error: “There is more than one method with this name but this does not match any of the available signatures.”

It doesn’t matter what I specify as the connection string…I always get that error. I’m trying to specify an SSL certificate in the connection string, otherwise it defaults to /Users/Aaron/.postgresql/postgresql.key

Xojo documentation points to PostgreSQL documents from 10 years ago and there are no examples that I can find for using the connection string. db.Connect(…)

Here’s my code:

Var certFile As New FolderItem(“certs/ccs-client-cert.pem”)
Var db As New PostgreSQLDatabase
db.Host = “127.0.0.1”
db.SSLMode = PostgreSQLDatabase.SSLRequire
db.SSLCertificate = certFile
db.Port = 5432
db.DatabaseName = “ccs”
db.UserName = “ccs”
db.Password = “MishMash98123”
Try
//db.Connect()
Var connString As String = “sslcert=” + certFile.ShellPath
db.Connect(connString)
MessageBox(“Connection Succeeded!”)
Catch error As DatabaseException
MessageBox("Connection Failed: " + error.Message)
End Try

Self.cv1Dashboard.Invalidate()

Thank you!

[Jeannot_Muller]

@Aaron_Schacht

Var certFile As New FolderItem(“certs/ccs-client-cert.pem”)

https://documentation.xojo.com/api/deprecated/deprecated_class_members/database.connect.html

Database connect doesn’t have any parameters. It will use all the “parameters” you specified in db.XY, like db.Host etc.

db.connect()

will work. This might not yet solve you main issue (I am not at my desk), but you get the current error from the compiler as you are using db.connect() in the wrong way.

[Aaron_Schacht]

Thank you for responding!

Is the Xojo documentation wrong or misleading here? PostgreSQLDatabase.Connect

It implies you can connect with an optional connection string. I am trying to use this string because I have multiple PostgreSQL instances running on Google Cloud and the SSL cert apparently needs to be passed in the connection string.

I just don’t know enough about how PostgreSQL works to figure out to resolve this issue with connecting to multiple Google Cloud instances with their uniquely generated client certificates. I can download the client certificates, no problem, and I can specify them in db.SSLCertificate when I connect to the database, but the PostgreSQL plugin (or some other mechanism) looks to this file for the private key: /Users/Aaron/.postgresql/postgresql.key no matter what certificate I specify. The only way I could see to get around this based on some research is by passing a connection string to Postgres with the “sslcert” keyword.

[Jeannot_Muller]

@Aaron_Schacht

Is the Xojo documentation wrong or misleading here? PostgreSQLDatabase.Connect

oops, I’m using the SQL plugin from MBS, as it by far faster … … so I really don’t know. From the timestamp the documentation seems to be up2date.

Perhaps this will help: MySQLCommunityServer — Xojo documentation

It is written for mySQLServer, but I just tried for postgres and you have the same parameters:

And perhaps this thread is useful as well: MariaDB 10.4 SSL connection error: protocol version mismatch

[Jeannot_Muller]

@Aaron_Schacht

Ahh, long week, my brain is not working any longer.

Try to return db.connect into a boolean, that will at least compile:

var myDB as new PostgreSQLDatabase

var myParameters as string
var checkSuccess as boolean

checkSuccess = myDB.connect( myParameters )

I guess this will help you moving forward, good luck!

Disclaimer: this code is of course not working. You need to fill in all your db. parameters, as you originally did. It is useless for me to copy that, as without a cert I can’t make it work for you

[Aaron_Schacht]

You are easily one of my favourite people in the Xojo community! Thank you! Yes, this might just work and I’ll experiment with passing parameters as a string. Hoping Xojo improves or expands their documentation.

Will you be at XDC in London next year?

[Jeannot_Muller]

Too much honor. My experience with the Xojo Community is that many, many people are helping.

I don’t think the documentation is “bad”, but yes I remember that I was struggling with it at the beginning as well, as not always every bit is explained, but over time you are getting more and more used to it, and to read the missing parts between the lines. Xojo is just a very powerful “beast”. For instance there are many examples using the database with a statement:

if db.connect then
...
end if

This If statement implicitly shows you that connect is returning a value. Same for the description in the documentation. The parentheses are an indicator that it is a method and methods usually return a value. And this is even described in the middle (not ideal) of the documentation:

This said, what you are trying to do is understandable but it is already the icing on the cake … a “normal” approach would probably be to start “easy” with a simple local database, than one on a server, and then only the connection via SSH. But I’m impatient as well, I fully understand where you are coming from. The good news: once you are making it to work you will never have to care with this challenge again.

As for the conference: well, we will see, as a physician, I’m unfortunately not too optimistic :-(, as a businessman let’s see how much money will be left by then, as a European, let’s see what a possible hard Brexit will bring to us ;-).

[Aaron_Schacht]

@Jeannot_Muller Yes, this dessert first approach is not always so healthy! I have worked with local database connections, built my own PostgreSQL/MySQL/CubeSQL servers on a repurposed laptop and made connections that way, but then wanted to dive into Google Cloud (GC) because I have an upcoming project that will require a cloud-based SQL solution (might also entertain Xojo Cloud®).

Here’s the good news - with your help and some guidance in various forums, leveraging PostgreSQL documentation, and experimentation, I have figured out a solution that seems to work well using connection strings. I can now connect to my disparate GC instances with their unique SSL certs without incident.

Since I’m on a Mac (Unix), I first needed to disallow world/group access on the certificate files that I downloaded from GC:

chmod 0600 client-cert.pem
chmod 0600 client-key.pem

Obviously everything else had to be in place as well - making sure my IP is allowed in GC Connections (Authorized networks), and obvious stuff like having permissions (un/pw) set up properly.

Here’s my code:

// IMPORTANT NOTES:
// Before any of this will work, we need to make sure the right permissions are set on the certificate files
// chmod 0600

Var dbSuccess As Boolean
Var certFile As New FolderItem(“ccs/certs/ccs-client-cert.pem”)
Var certKey As New FolderItem(“ccs/certs/ccs-client-key.pem”)
Var db As New PostgreSQLDatabase

Var strConn As String = “host=34.121.1.1 sslmode=prefer sslcert=” + certFile.ShellPath + " sslkey=" + certKey.ShellPath + " port=5432 user=un password=pwd dbname=db"
dbSuccess = db.Connect(strConn)

If dbSuccess Then
MessageBox(“Great success!”)
Else
MessageBox(“No dice…”)
End If

@Jeannot_Muller

As for the conference: well, we will see, as a physician, I’m unfortunately not too optimistic :-(, as a businessman let’s see how much money will be left by then, as a European, let’s see what a possible hard Brexit will bring to us ;-).

So much uncertainty and the potential for chaos (Brexit). I want to show up this year, particularly because it’s in London and I want to meet more European developers. Frankly, I’m thinking about spending as much time in Europe as possible next year and beyond. Considering what has been going on here in the past 4 years, I’m sure you can understand why I’d want to get the hell out of here. My partner/girlfriend lived in Hungary for several months a few years back and spent time in numerous European countries (including Germany). I have not yet had my first trip to Europe…please don’t judge me! We’re trying to learn German, as we’d love to live there three months every year if we can somehow make that happen. We have no interest in bringing our American values to other counties - we’ve done enough damage already. So, we will do our best to fit in and respect/honour our host country, however long they will take us

[Jeannot_Muller]

Great success. Champagne!

I’m following a slightly different approach. Basically my main databases are on my own servers (in the cloud) but they are not open to the outside. I am SSH-ing from Xojo into that server whilst debugging, when the final code is deployed to the server there is obviously no need for SSH any longer. If I have to access databases at a later stage, I’m connecting to those via SSH from my linux servers but for Xojo I am always using the same way SSH-ing from code in Xojo to MY servers and from there to other stuff.

As for the traveling plans: I hope that starting next Tuesday I will be able again (in theory at least) to travel to the US. For 4 years now I fear that if my Facebook Account is somehow linked to the NSA (and it surely is) homeland security will not let me in for not having been nice to agent orange ;-). I love the US, not so much the food, but the country side. Hungary is unfortunately getting be bit crazy too these days, but give me a shout when you are planning to be in Munich.

[Aaron_Schacht]

I like this development->deployment methodology you’re using for connecting to your databases. Maybe I’ll get there. Are you using MBS plugins for SSH connections?

Look, I don’t think the American intelligence community/apparatus is a fan of “agent orange”, and you’re lost in a sea of millions (including me) who share your distaste for him. Most of the polls show a comfortable lead for Biden, but I don’t have much faith in our broken electoral system. Where do you visit when you come to the US? What do you mean by the “country side”? More rural areas and vast open spaces? Curious to know what parts of this country you are enjoying. I can’t wait to visit Munich and I’ll definitely give you a shout when our travel plans take us there.

So, I’m popping the cork off my champagne bottle only to realize that formulating this connection string wasn’t necessary after all! Specifying the SSL key file in PostgreSQLDatabase.SSLKey allowed me to make the connection I needed without the need for a connection string. I guess it’s still a good exercise to go through, figuring out how to use connection strings as this may come in handy some other time. Anyway, thank you for holding my hand through all of this and giving me key insights and strategies during my foray into database connections with Xojo.

This is the code that worked flawlessly for me:

// SSL certificates downloaded from GC
Var certFile As New FolderItem(“ccs/certs/ccs-client-cert.pem”)
Var certKey As New FolderItem(“ccs/certs/ccs-client-key.pem”)
Var db As New PostgreSQLDatabase

db.Host = “34.121.1.1”
db.SSLMode = PostgreSQLDatabase.SSLRequire
db.SSLCertificate = certFile
db.SSLKey = certKey
db.Port = 5432
db.DatabaseName = “dbname”
db.UserName = “un”
db.Password = “pwd”

Try
db.Connect()
MessageBox(“Great success!”)
Catch error As DatabaseException
MessageBox("Connection to database failed: " + error.Message)
End Try

[Jeannot_Muller]

that’s why I posted the screenshot of all options ;-).

I like many parts in the US, but particularly the west coast. But I drove once from Washington D.C. to Harrisburg. That’s what I call “country side” … in Europe it is pretty hard to drive as long without seeing big towns. Same while driving through Arizona, New Mexico or Utah ;-).

[Jeannot_Muller]

BTW @Aaron_Schacht, if your are marking your code and you then click on the </> button, your code will be formatted - makes it easier to read and to copy&paste

[Aaron_Schacht]

Yeah. Plain as day! I need to be a little more methodical in my approach sometimes and breathe. I can get quite anxious and hyper so I sometimes skim information too quickly!

Maybe I need to take another road trip through Utah, Arizona, and Utah. My girlfriend is from Utah…I’ll admit there are some spectacular expanses of land there.

[Aaron_Schacht]

I’ll do this in the future to make my code more readable and easy to copy/paste…thank you