How To Determine if A Connection is Already Open

[SGLong]

I'm running into a problem where I need to know if I already have a connection to SQL established so I don't waste time opening another connection.

I have several procedure files that are issuing a command like

nHandle = SQLCONNECT('MyDatabase')

There are times when the main program also has established a connection to that database, like

nDBHandle = SQLCONNECT('MyDatabase')

The underlying problem is an inconsistent use of the variable to store the handle, but that can't be addressed at this point. We've had several developers in this project over the years and there are probably 20 different variable names referring to multiple databases of differing names.

What I would like to know is if a connection to 'MyDatabase' already exists and how I can tap into it's handle in the various procedure and function files.

Is this possible?

Steve

 

[DSummZZZ]

Well, since the handle is nothing more than a numeric value, there isn't really a way to trace it back to a particular connection. I'm afraid it's going to have to be up to you to somehow keep track of connections.



-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

Steve,

As Dave points out, there's no obvious way of testing for an open connection within VFP. Whatever you want to do with a connection, you have to start with the connection handle.

But I wonder if there would be any way of achieving your goal on the server side. You don't say which back end you are using, but it could be that, whichever one you are using, it has a way of reporting on open connections.

If so, it would very much depend on the type of database; this sort of thing isn't built into SQL. But it might be worth checking the documentation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

Based on the variety of developers and the resultant jumble of handle names, you might be best to always close all possibly open handle-referenced connections and open the one that you want.

Using =SQLDisconnect(0) will close ALL open connections regardless of their handles.

Then you can open just what you want and use it.

As to your not wanting to "waste time opening another connection."... You are talking about milliseconds.

Alternatively you can run a text search through ALL of your program source code files and look for the text string SQLCONNECT( and then edit the code in the various places to restore things to some level of uniformity.

Good Luck,
JRB-Bldr

 

[SGLong]

Mike,

We are using MSSQL-2005 and MSSQL-2008.

JRB,

Some of the procedures we call can be called several times per record, let alone per table. Opehing a connection multiple times on a 3000 to 4000 record set is taking a lot of processing time unnecessarily. As much as I dread the task, your suggestion of cleaning up our code looks like the only alternative.

Thanks guys,

Steve

 

[JRB-Bldr]

"I dread the task"

What do you mean? It sounds like FUN to me (NOT!!!).

But you might want to consider writing yourself a small PRG which would do the work of changing the Source Code files for you.

For the PRG's it would not be too difficult to have your small PRG to open each of the PRG files as a Text file and make the conversions for you and then close the file, saving the changes.

For the Forms it would be a little more difficult since you would have to open each of the Form' SCX file as a table and make the changes in the Methods field (a memo field) of the various records.

Once those changes had been made, you would just need to Build the Project again.

DO NOT FORGET TO MAKE A FULL BACKUP BEFORE DOING ANY OF THESE CHANGES!!!!

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Steve,

Haivng thought some more about this problem, I think that the best thing for you to do would be to bite the bullet and get rid of all the multiple connection handles. They could cause all kinds of problems, not least when the variable holding an active connection goes out of scope.

Also, the fact that your back end is SQL Server (or, at least, I assume that's what you mean by "MSSQL-2005 and MSSQL-2008") is a factor. That's because SQL Server imposes a finite limit on the number of open connections. If the app has many users, that could be an issue.

If I was you, I would seriously consider re-factoring the application to use a single connection handle that is available throughout the session. In particular, consider using a connection manager class that handles the whole thing for you.

I published an example of such a class in my article, Simplifying SQL Pass-Through. You are welcome to use if you like, but it's only a simple example to give the idea of what's possible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

There's ASQLHandles(laHandles) giving you an array of currently open handles, you just need to find out, to which remote database a handle connects you, which you find out via SQLGetProp(handle,"ConnectString") for example.

Bye, Olaf.