Sources to learn?

I would like to find resources to learning best practices when working with Xojo and databases. I wrote DB apps in VBasic in the late nines. Kinda old experience, but I’m not new to database apps.

And references to resources/books on the topic to learn best practices? (Presently I’m just using forums, blogs, and the example projects).

Thanks in advance…

link text

will get you going

Thx!

Here’s more background on my I’m asking. I created a database object in the navigator and drove forward with my application code. After a few days of development, I started getting the ‘server gone away’ error after the web app is running for a while (I think when one of my threads completes it’s tasks.) I read around, and it sounds like it’s better to create the db objects and connect in code without creating the object in the navigator. And then something about not using a single object across the application. Not sure that makes any sense, so yeah… I’ll check out the link.

Thx again.

Use one database connection per Session. Connect on Open, disconnect on Close. Each Session basically runs in its own thread, and Database plugins generally aren’t thread safe. Design your transactions to be tight so locks don’t block. Those are the basics for WE.

Good to know. I’m implementing those ideas now. fingers-crossed the error goes away. :wink:

It seems a bit odd to me then that Xojo allows you to create the DB object in your project if it is such a poor way to do it - considering they Xojo (RealStudio) has/is used for DB apps so often.

There are a handful of features in RS/Xojo that go back many years that are useful for learning and/or hacking around, but not terribly useful for production software development. Unfortunately, it’s usually a blurry line where hacking and learning end and production begins.

@Brad Hutchings: Being an expert on the web front, what do you think about having a connection pool instead of 1 connection per session? That is the way I developed a recent program deployed for a client portal at my work. The code was rather simple… In my App class, I added a property DatabasePool() As Database, and then two methods:

[code]Function GetDatabase As Database
If DatabasePool.UBound = -1 Then
// Create a new database connection and return it
End If

Return DatabasePool.Pop
End Function

Sub ReturnDatabase(d As Database)
If DatabasePool.UBound > MaxDatabaseConnections Then
d.Close
Else
DatabasePool.Append d
End If
End Sub
[/code]

My users spend a lot of time idle and tying up a database connection per session seemed like a waste, but maybe I am wrong. When I handle a request, I will simply do something like:

[code]Dim db As Database = App.GetDatabase

// Do stuff

App.ReturnDatabase db[/code]

Expert is pretty charitable… I’m not a fan of premature optimization, especially when trying to scale something, so if I didn’t observe a problem with one open connection per session, I’d stick with its simplicity. For a SQLite database, I have not observed nor been given any reason to expect much overhead for an idle connection. For any of the database servers, there would be an open socket, and systems will have limits on how many you can have, so perhaps a managed pool makes more sense there.

On managing your pool in your App class… Remember that calling those from session/page events are calling from other Threads, so you might want to protect with a CriticalSection or the like. You have to ensure that there is no chance that two different users could call into those database objects at the same time, have simultaneous pending queries, etc.

Good news is I improved performance considerable with this change. Bad news is I’m still getting the ‘Server is away 2006’ message. back to digging for reasons

Is there a way to get more information about why the error is happening? More debug information about those pesky DB errors?

[quote=22600:@Brad Hutchings]Expert is pretty charitable… I’m not a fan of premature optimization, especially when trying to scale something, so if I didn’t observe a problem with one open connection per session, I’d stick with its simplicity. For a SQLite database, I have not observed nor been given any reason to expect much overhead for an idle connection. For any of the database servers, there would be an open socket, and systems will have limits on how many you can have, so perhaps a managed pool makes more sense there.

On managing your pool in your App class… Remember that calling those from session/page events are calling from other Threads, so you might want to protect with a CriticalSection or the like. You have to ensure that there is no chance that two different users could call into those database objects at the same time, have simultaneous pending queries, etc.[/quote]

Thanks. This app is talking to a PostgreSQL database with hundreds of connections already opened, each connection takes resources in the form of sockets and memory. I have not noticed a problem thus far, but all the other tools connecting to it uses connection pooling as well.