I am knocking out my first Xojo app to familiarize myself with it. This desktop app will access a Postgres DB on localhost.
It looks like Xojo does not have connection pooling, at least not built-in. My impulse is to create a public DBConnection property in the app, that will be initialized with the connection info in the App.Opening event, opened, and then just be accessed from all the other code. Coming as I do from a .NET background this seems very retrograde and while I expect I can get away with it in a desktop app, this all seems very dicey for a web app.
Can anyone share best practices / experiences both as to DB connection management and also insuring the security of the connection info (user name, password, etc)?
Why not create the connection and kill it when done with the job at hand?
No reason to keep it alive for the life of the app
Same for a web app, why do you want to keep a life scope connection to the DB?
For a web app I would make the connection a property of session so each session would be independent.
2 Likes
There’s a tradeoff between the overhead of a static open connection and constantly closing and reopening it, if there is no automatic connection pooling going on. Before connection pooling (which, in .NET, is automatic for at least Sql Server) one tended to hold connections open at least within a particular method that makes more than one query. I’ve been used to opening and closing connections with abandon for years now and in fact the IDisposable pattern in .NET encourages that. I am just not seeing anything comparable in Xojo. If there were, the basic pattern would be something like
’ db = already-defined PostgreSQLDatabase object
db.Open() ’ Returns a connection from the connection pool if available, else creates a new connection
Using db
’ Do stuff
End Using
’ Exiting the using block calls db.Close()
The scope of the open connection, absent a pool, would simplistically be the Opening() event of the control where you’d fetch the data to populate it, and maybe that works well enough. It’s been probably a good 15 years since I experienced latency around opening un-pooled connections; maybe that just isn’t a practical issue anymore. Also my past experience is mostly Sql Server and I’m using Postgres for this project.
Any thoughts on connection info? Right now I don’t care as this is a prototype / personal use app, but in the .NET world we used to have connection strings in config files and that fell out of favor as a security risk and it’s usually abstracted away behind some kind of factory object these days. ISTR that it’s possible to distribute an encrypted Xojo app so that no one can retrieve the userid/password info, is that the security best practice within Xojo?
There is not a recommended way to manage database credentials in Xojo. Each developer has their own method. Generally, it is bad practice to store credentials in code and Xojo does not create encrypted apps anyway.
Years ago we had a developer create a connection pool in Xojo. It worked well but we stopped using it because it wasn’t needed. In Xojo, you sometimes have to create something that is built-in in other languages. But, the good thing is that you end up with exactly what is needed, assuming you have the skills.
You open a document, you do not connect to document.
Did you connect Xojo to your project ? Word, inDesign ? Firefox ?
So, using the correct terminolog-y allows you to find what you want in the (poor) documentation.
Also, looking at this forum (I mean search) may allow you to reach examples… I shared some last week (July 23rd or 24th) that you can use…
Search in the Code sharing Channel.
I have another to share…
Look there for Code Sharing:
Yes I am finding that to be true. It is a little weird to me for example that there is no data binding or auto wireup mechanism when you are bringing DB data in or out of the app but I am quickly building up a library of reusable methods to do that stuff and you are right, it’s not that much code if you know what you’re doing and then you can make it do exactly what you want it to do. And to be honest the data binding mechanism in MSFT Windows Forms or WPF are not very straightforward and sometimes over-engineered for typical use cases anyway.
Another example is the Xojo RowSet; it is kind of like a .NET DataTable, stripped of all the crufty functionality that I hardly ever use. (There doesn’t seem to be a firehose cursor (e.g., .NET DataReader) for best performance and minimal memory footprint where you just need to iterate through the data once – but perhaps I just have not found it yet).
So far overall I am quite impressed. I wish you could use the same project to target desktop or web rather than have to copy-pasta between similar projects, but I also know that is a heavy lift and comes with its own tradeoffs, and not everyone needs to support both anyway. So far the “differentness” from the C# / VB.NET world is a small learning curve relative to all the constantly churning frameworks abroad in the world. And the RealBASIC language itself has memory blocks, structures, all sorts of primitives so I feel that if I need to drop to a lower level at times, it’s all there if needed.
I’ve been kicking the tires on Lianja for example (I was very active back in the FoxPro heyday) and I just think the abstraction level is too high, and too oriented to the low / no-code space. It’s nice that you have a choice of several languages but it’s over-optimized for CRUD apps. Xojo seems to hit more of a sweet spot for me.
Looking forward to delving deeper!