What is the prefered way to "handle" conection handles 1

[jrumbaug]

I am using VFP8
I am writing an application with MySQL 2005 as the backend.
I am using some SQL PASS THROUGH functions.

My question has 2 parts.

First,
To use the functions I need a connection handle. Is the preferred way to create a connection handle at the beginning and use it throughout the application? Or is preferred to get a new handle each time you start another round of queries?

Second
What is the purpose/advantage of a shared vs. "not shared" connection?

Jim Rumbaugh

 

[Mike Lewis]

Jim,

The short answer (to both questions) is that it's a trade off.

There is a certain amount of overhead in creating a connection handle, so you don't want to do it too often. For example, you wouldn't go to the extreme of creating a handle each time you call SQLEXEC(), and dropping it immediately afterwards.

On the other hand, some back ends place a restriction on the number of connection handles that can be open at one time, and others suffer a performance loss if too many handles are open, so some developers prefer not to keep handles open longer than necessary.

Rightly or wrongly, my own approach is to create a connection at the start of the application and leave it open the whole time (but perhaps with a time-out so that it gets dropped after so-many minutes of inactivity). I have some code which manages this; you're welcome to have a copy of it (see:

http://www.ml-consult.demon.co.uk/foxst-25).

This has worked well for me, but I can't say for sure that it will be the best option in your case.

As far as shared connections are concerned, this is mainly an issue for remote views (with pass-through, there is generally only one connection). If your RVs share a connection, you avoid the issue of having too many connections open at one time. But you have to worry about asynchronicity -- one view might make a request for data while another one is still busy. You need to write code to handle that possibility.

My approach (again, rightly or wrongly) is not to share a connection between RVs unless I've got a special reason for doing so.

Hope this helps.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)