How long the life of a database connection ID?

[Simon Knight]

Hi,

I have been reading material about using databases with Livecode and wonder what the best practice is with regards to the database connection ID. My final application will be running on a number of client computers all connected to the same local network. The application will read and write to a postgeSQL database located on the server that is running the LAN.

My internet reading has revealed two schools of thought or so I believe. The first creates/opens a database connection and uses it for the whole period the application is running i.e. many hours. The connection is only dropped when the application is quit. The second creates a connection for a specific use e.g. CREATE table and once the task is complete the connection is closed, obviously this is more complex but does it offer any advantages? Also does it effect how database cursors may be used?

Simon K

[bangkok]

I put myself into the second category, thanks to my experience : good practice = open the connexion, query, close the connexion.

It's safer.

With the first method... all kind of problems can occur (and do occur on any networks) : time out, bad network leading to lost connexions etc.

And it's not more complicated. On the contrary.

Create 2 commands with short name like : dbstart, dbstop

they will open, and close the connexion, handle error message etc.

Between them, in your script, use the commands to send/execute SQL queries.

[Dixie]

Like Bangkok.. I'm in the second' category too...

[Simon Knight]

Thanks, I was leaning towards option 2 and your comments have been helpful.

Simon K

[Klaus]

Hi guys,

thanks for you insights, I will rethink my db strategies now



Best

Klaus

[phaworth]

I'll be the contrarian and go with option 1. Not doubting that communication errors can occur but errors are returned when that happens and you can script accordingly.

Some of this depends on your application. If all you are doing is querying or perhaps occasional db access during the life of the program, then maybe option 2 is OK although continually opening a db closing a db connection is time consuming. I've never quite figured out how you can sensibly deal with database cursors/recordsets and db locks in this scenario since those things disappear when you close the connection.

To me, continually opening/closing a db connection is somewhat similar to wrapping every instruction in a script in a try block just in case something goes wrong.

Pete