Connection issues from Xojo to PostgreSQL

Hello all.

First, I have been using PostgreSQL for some time now. Lately, I have been trying to figure out a new issue. Short queries, such select single commands, work fine. As soon as I try to read a larger number of records, say 50 rows each with 5 columns, I get an error that the server disconnected. The typical reason would be some network issue, according to a quick research on the web. I am working on that separately.

I have a doubt however that it is a network issue, since I can run the same queries on the same workstation and the same database with Navicat. In fact, even the largest queries that I can do on the database work just fine using Navicat. It could be a Xojo issue. But given my recent workstation issues (I changed workstations since), I am not ready to call it a Xojo issue just yet. My new workstation is a Core I5 13gen, 64GB RAM, Windows 11. My db server is Debian 12, PostgreSQL 16 running in a Proxmox VE VM (8 virtual cores, 16GB). Oh. I turned off both local firewalls and antivirus for testing, without any effect. Turned them back on. I confirmed the issue on 2023 R4 and newer releases.

Next step: connect using something other than the Xojo PostgreSQL plugin. MBS has a SQL plugin. So, I am trying to test the database connection with it. I am running into an issue of missing libraries. From previous posts, I understand that several libraries may be needed. I downloaded psqlodbc_16_00_0000-x64.zip from the Postgresql web site (I am using PostgreSQL 16 at the moment, but I also have a PostgreSQL 14 server should it be needed). I extracted all the libraries and placed them alongside the MBS example projects in my testing folder.


I am getting a missing lib error:

Questions:
1- does anyone have any suggestion about what else might be the root cause of the connection issue using the Xojo plugin?
2- what am I doing wrong trying to test with the MBS plugin?

Thanks! LD

We use xojo on windows with postgresql 14 without issues. We’ve never tried 16. If you can, I would see if the issue occurs with 14. Maybe it is an issue specifically with 16.

Brandon;

thank you. Indeed, it is on the list. I cloned the Deb 11.5 PG14 VM and I am upgrading Debian to 12. It was 11.5. I am keeping the original 11.5 VM intact for further testing.

I have no trouble connecting to my PG 16 instance with the Xojo plugin. Can you show us where exactly the error occurs in your code? Remember, the connection needs to stay alive as long as the rowset. Don’t close it before you are finished looping over the rowset.
Also a deep look into the PG logfiles might help with connections issues. Turn log_connections = on and log_disconnections = on in your postgresql.conf and issue pg_ctl reload to track that. It might just give you an idea where exactly the disconnect happens.

1 Like

Maximilian,

It is good to know that it should work. I expected that much, because it had been working fine for a few years already, through several upgrades ( Xojo and/or PostgreSQL)

The method is a very simple little function that retrieves texts for the next page menu: If I comment this one out, the next select method fails for the same reason. A smaller select (select single) was successfully processed prior to this point. I added a call to a function that ensures the connection to the database is OK. It is not there usually, since the connection is a property of the session and the coonnection was verified just a bit priot to calling this method in the code. So, here is the code:


…and the DatabaseException:

It does bother me somewhat that the connection is closed prematurely when I query with Xojo, but the same query works fine when run with Navicat. (of course, instead of variable Langue, I write ‘FRA’ in Navicat.)

I am starting to wonder whether I am perhaps missing a dll or perhaps one dll is the wrong version. This is a brand new installation if Windows 11 and of Xojo (and a long list of other software).

Thanks!

Using the MBS SQL plugin (and the libraries contained in psqlodbc_13_02_0000-x64.zip instead of psqlodbc_16_00_0000-x64.zip), I am able to process the failing query. There is something funky with my Xojo postgresql plugin it would seem…I tested on PostgreSQL 14, 15 and 16. The database itself seems to be fine, and also the network connection. I ran much larger queries and everything works perfectly, same as with Navicat.

You are connecting to your Postgres using ODBC?
Are you using Xojo connection using ODBC?
Can’t you connect to your Postgres without ODBC?
Sorry for my questions, we use Postgres with Linux (web app) and we don’t use the ODBC plugin, nor download any psqlodbc for it. Maybe is some requirement for Windows?

I usually connect to PostgreSQL using the Xojo plugin. Navicat uses ODBC. Everything else stems from the fact that connecting with the Xojo plugin fails for me (evidently works for others, so the root cause remains to be found) I used libpq and others to perform a test with the MBS plugin.

I am open to hear about better ways.

Ah, I see. To be honest I only connect from Mac OS or Ubuntu, so I can’t really comment on your connection issues on Windows. It could also be a configuration issue in your postgresql.conf file. Head over to https://pgtune.leopard.in.ua/ and check if your settings are okay. Check your values for tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count. If everything looks okay and the log files don’t reveal anything (did you track & check?) I’d set up a little demo app demonstrating the problem and simply report it (along with the postgresql.conf file).

I notice that in your SELECT statement that texte is lowercase but then you use it in the Field calls as uppercase. Postgres is case-sensitive by default.

You also have the same issue with TxtId within the select statement itself.

Thank you Greg,

You bring a good point…However, it never was an issue before. Why now? That said, I made a quick test. In the database, everything is always created in lowercase. I made sure it was the case first with this specific table, then adjusted the code to use lowercadse fields and table name. The result is the same.

I thought that perhaps my database server VM might be sick, so I created a new one and cloned the databases onto the new one. Same result. I am now building a physical server on one of my test machines - new hadr disk, fresh Debian install, fresh PostgreSQL install. Perhaps I have a networking issue of some kind on my VM server. This test should shed some light on the hypothesis.

Ha! it is a networking issue of some kind. Before running a test with a new physical server, I changed the bridge assignment on my database server on the VM server. - My network has multiple subnets: LAN, WAN, DB, and a couple special purpose others. So, reassigning the db server to the lan bridge, all works normally. It is down to networking. Now, I can find the failure point.

The physical server test will determine whether the issue is with the VM server/cable, or with the firewal itself or perhaps the subnet switch. The firewall is an industrial pc with multiple nics, running OPNSense I did change the switch before, so it is unlikely to be the switch.

The issue is at the firewall level. I connected the physical server directly to the db subnet port on the firewall with a new cable. The issue is present. It is puzzling. Everything else works on that subnet, including connecting to the database with ODBC.

The firewall is an iundustrial PC with several network adapters, running OPNSense. I will now go through the logs to try figure out what is wrong.

Clearly, this is not a Xojo issue.

It is most likely a software issue. I ran several permutation and direct connection tests. Any LAN to other subnet fails with the Xojo plugin. But works with the MBS ODBC plugin. I added explicit LAN to DB subnet allow rules to the firewall (TCP/UDP, any to 5432). The connection still fails when I use the Xojo plugin. Works with the MBS ODBC plugin.

I am at a loss to explain what is wrong. As a reminder, the Xojo plugin works fine if I connect the db server to the LAN subnet.The MBS plugin works in every case.

No, not really (it would violate the SQL standard if it were). The SQL standard specifies that identifiers, including table names, should be treated in a case-insensitive manner unless quoted. This means that in the absence of quotes, the standard suggests that identifiers like table names should not depend on case. Postgres adheres to this perfectly.
So this works:

Create table SomeTable (someField integer);
Select SOMEfIELD from sOMEtABLE;

(exeutes without error)

Some people believe it is case sensitive because they create their tables using PG Admin’s table creation tool which issues this SQL to the server: Create table “SomeTable” (some other tools do this as well). The quotes tell the server that you are dead serious about the capitalization (as the SQL standard requires it to). From now on you will always have to refer to you table as “SomeTable” (with the quotes). The above query will now fail.

I have now come to believe that it may be a Xojo issue. I have been trying to identify the root cause of another issue. There may be a connection. OK, step by step:

1- from the Postgresql logs:

2024-03-05 07:42:47.925 EST [3477] [inconnu]@[inconnu] LOG:  connexion reçue : hôte=172.16.99.104 port=58601
2024-03-05 07:42:47.961 EST [3477] postgres@GEOFF2_DEV LOG:  connexion autorisée : utilisateur=postgres base de données GEOFF2_DEV application_name=DebugGeoff.exe
2024-03-05 07:42:47.972 EST [3477] postgres@GEOFF2_DEV ERREUR:  erreur de syntaxe sur ou près de « CONNECTION_CHECK » au caractère 1
2024-03-05 07:42:47.972 EST [3477] postgres@GEOFF2_DEV INSTRUCTION :  CONNECTION_CHECK
2024-03-05 07:42:48.001 EST [3477] postgres@GEOFF2_DEV LOG:  déconnexion : durée de la session : 0:00:00.076

The connection happens from my workstation, but then something fails. I don’t think that it is my doing. More later

From the Xojo logs:

SyntaxError: Unexpected token '.'
    at tryToEval (http://127.0.0.1:58183/framework/Xojo-xa59cf02ecb5cb05a.js:6:27116)
    at parseResponse (http://127.0.0.1:58183/framework/Xojo-xa59cf02ecb5cb05a.js:6:26754)
    at Object.success (http://127.0.0.1:58183/framework/Xojo-xa59cf02ecb5cb05a.js:6:25515)
    at c (http://127.0.0.1:58183/framework/jquery-3-x27f3b64dee964807.5.1.min.js:2:28294)
    at Object.fireWith [as resolveWith] (http://127.0.0.1:58183/framework/jquery-3-x27f3b64dee964807.5.1.min.js:2:29039)
    at l (http://127.0.0.1:58183/framework/jquery-3-x27f3b64dee964807.5.1.min.js:2:79800)
    at XMLHttpRequest.<anonymous> (http://127.0.0.1:58183/framework/jquery-3-x27f3b64dee964807.5.1.min.js:2:82254)
         :  startup: connected to sqlite db
         : GEOFF2_DEV
         : ***************  not showing password
         : postgres
         : True
         : startup: connection to PG database OK
         : starting GoNext method
         : reached checkpoint 1
         : reached checkpoint 2
         : reached checkpoint 3a
         : Startup: killed existing connection
         :  startup: connected to sqlite db
         : GEOFF2_DEV
         : ***************  not showing password
         : postgres
         : True
         : startup: connection to PG database OK
         : Startup: killed existing connection
         :  startup: connected to sqlite db
         : GEOFF2_DEV
         : ***************  not showing password
         : postgres
         : True
07:58:24 : startup: connection to PG database OK

The token error is something I have been trying to resolve for a while. I can’t figure it out. I thought that perhaps it had to do with my CSS file being incorrect, but this log is from a run without the CSS file. It comes from something else. Every new page triggers this error. Here, the login page was shown.

In the logs, the “GoNext” method is called when the user enters UID and password, and the calculated hash code is successfully compared with the stored hash. Accessing the data in the database works. So, GoNext is called. It calls a succession of methods to load general session data. The first method called from GoNext loads menu texts (I converted everything else to localized constants, except menu texts. It is on the to do). The method fails. I added a call to the connection method, just to be 100% sure that the connection is still alive. From the logs, I know that it was. The method kills it and starts a new one.

I am pretty sure that this error in the Postgresql logs is not my doing. I could be wrong, though. 2024-03-05 07:42:47.972 EST [3477] postgres@GEOFF2_DEV ERREUR: erreur de syntaxe sur ou près de « CONNECTION_CHECK » au caractère 1

I am getting close to creating a new issue with this, but perhaps it is my error. I am looking for insights before I proceed.

For reference, here is my connection method:

Dim DBName As String
Dim zz As String
Dim yy As String
Dim SVRStr As String
Dim IniPath As String
Dim mDb As SQLitedatabase

Dim SQL As String
Dim RS1 As RowSet

'' Kill the existing connection if it exists 
If Me.IsConnected = True then
  Me.Con.Close
  Me.Isconnected = False
  
  System.debuglog "Startup: killed existing connection"
End If

' Open parameter file     
Inipath = App.ExePath + "GeoffParam.sqlite"
Me.IniPath = IniPath

Dim dbFile As New FolderItem(Inipath, FolderItem.pathModes.Native,False)

Try
  mDb = New SQLiteDatabase
  mDb.DatabaseFile = dbFile
  mdb.CreateDatabase
  
  System.debuglog " startup: connected to sqlite db"
Catch e As runtimeexception
  MessageBox e.Message
End Try

' Get PostgreSQL parameters
SQL = "Select * from ConnParam where Environnement = '" + ZKey + "';"
RS1 = mDB.SelectSQL(SQL)
DBName = RS1.Column("dbname").StringValue
SVRStr = RS1.Column("serveur").stringvalue
zz = modutil.strzz(RS1.Column("Pass").StringValue)
zz = zz.Left(8)
yy = RS1.Column("uid").StringValue

System.Debuglog DBName
System.Debuglog zz
System.Debuglog yy


Me.Con = New PostgreSQLdatabase
Me.Con.Host = SVRSTR
Me.Con.UserName = yy
Me.Con.Password = zz
Me.Con.AppName = App.ExecutableFile.Name
Me.Con.DatabaseName = DBName

system.debuglog con.connect.ToString

Try
  Me.Con.Connect
  Self.IsConnected = True
  System.debuglog "startup: connection to PG database OK"
  Return True
  
Catch Error As DatabaseException
  MessageBox("Error: " + error.Message)
  System.debuglog "startup: connection to PG database Failed"
  Return False
  
End Try

And the first failing method (it does the menu text retrieval)

'method called from checkpoint 3a

Dim B as Boolean
Dim RS1 As RowSet

'Make sure we are connected to the selected environment
B = Session.SetSessionConn(Session.ZENV)

'prepare to read the 50 records in the menu text table
Dim psGetMenuTxt As PostgreSQLPreparedStatement = Me.con.Prepare("Select txtid,texte FROM geoff.stxt WHERE langue = $1 ORDER BY txtid asc")

'Go.
psGetMenuTxt.bind(0, langue)
RS1 = psGetMenuTxt.SelectSQL

If rs1.RowCount >0 Then
  Redim Me.MenuText(-1)
  Me.Menutext.Add(langue)
  While Not RS1.AfterLastRow
    Me.MenuText.Add(DefineEncoding(rs1.Column("texte").value, encodings.UTF8))
    rs1.MoveToNextRow
  Wend
end if

rs1 = Nil

Bottom line: Please let me know if I do something to trigger the database error. Otherwise, it looks like a case for a new issue.

Thanks.

LD

I wouldn’t use PostgreSQLPreparedStatement these days. You should be able to of course, but maybe using me.con.SelectSQL(sql,values) (where values is a paramarray or an array of variants) does make a difference?

Thank you Maximilian. Definitely worth a try. This is old code updated to work with recent Xojo versions, but not necessarily updated to current standards. The original is live on Xojo 2019 R3.1. So, a good cleaning job should probably be added to the to-do list.

I will try later today with the whole startup and initial data loading to see if it makes a difference.Will post findings here.

I had to try. The updated method is now:

'method called from checkpoint 3a

Dim B as Boolean
Dim RS1 As RowSet

'Make sure we are connected to the selected environment
'B = Session.SetSessionConn(Session.ZENV)

'prepare to read the 50 records in the menu text table
'Dim psGetMenuTxt As PostgreSQLPreparedStatement = Me.con.Prepare("Select txtid,texte FROM geoff.stxt WHERE langue = $1 ORDER BY txtid asc")

'Go.
'psGetMenuTxt.bind(0, langue)
'RS1 = psGetMenuTxt.SelectSQL

RS1 = Session.con.SelectSQL ("Select txtid,texte FROM geoff.stxt WHERE langue = $1 ORDER BY txtid asc;", langue )

If rs1.RowCount >0 Then
  Redim Me.MenuText(-1)
  Me.Menutext.Add(langue)
  While Not RS1.AfterLastRow
    Me.MenuText.Add(DefineEncoding(rs1.Column("texte").value, encodings.UTF8))
    rs1.MoveToNextRow
  Wend
end if

rs1 = Nil

The end result is the same.