Connection Handle VS. Statement Handle

[jrumbaug]

I'm still trying to get a handle on remote "handles". From the VFP8 HELP I read:

To obtain a handle, you request a connection to the data source using the SQLCONNECT( ) or SQLSTRINGCONNECT( ) function. If the connection is successful, your application receives a connection handle for use in subsequent Visual FoxPro calls

Which makes me think SQLCONNECT( ) or SQLSTRINGCONNECT( ) returns a connection handle. But then I read:

Remarks
The SQLCONNECT( ) and SQLSTRINGCONNECT( ) functions return a numeric value as the statement handle rather than a connection handle. You cannot obtain a connection handle directly. You can still set and get connection properties using the SQLSETPROP( ) and SQLGETPROP( ) functions by passing the statement handle for that connection and the string, "Shared", as arguments. All other SQL functions use a statement handle instead of a connection handle.

Does any code I write need a connection handle? I'm starting to think I never need a "connection handle" in my code, but I do need a "Statement Handle" to use with my SQL Pass Through. Please help me figure out when I will use these 2 handles.

Jim Rumbaugh

 

[Mike Lewis]

Jim,

For VFP purposes, you can ignore the difference between connection handles and statement handles.

Just keep in mind that SQLCONNECT() and SQLSTRINGCONNECT() return an integer. You pass that integer to other SQL pass-through functions, notably SQLCONNECT(). For almost all remote accessing, that's all you need to know.

VFP 9.0 now refers to that integer as a statement handle rather than a connection handle, which is technically more correct, but it makes no practical difference to your code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[jrumbaug]

Mike

Thank you very much for the explanation and advice. I know what to do now.

Jim Rumbaugh

 

[TomLeMes]

Yes, thanks Mike - I was wondering what this 'statement handle' was, as I was used to referring to this as a 'connection handle'.

On a related point, is there a way to return a list (or array) of all current connection handles? I know that you can use SQLGetprop("ConnectHandle") to get the connection handle for a particular work area, but what I'd like to do is get a list of all the handles I've created in a session.

If I know the name that I gave the handle when I created it, querying it will return the number, but what would be handy would be a command to return all handles, and (even better) some information about them. Is there such a thing? If not I guess I could just record the information in a public array as I create the handles, so maybe I'm being lazy, but still...

 

[Mike Lewis]

Hi Tom,

I see what you mean. I guess you need an ACONNECTIONS() property, but unfortunatley that doesn't exist.

You're right that one solution would be for you to maintain an array of connections. Add an entry each time you open a connection, and delete it when you close the connection. The entries would be simple integers.

By the way, knowing the "name" of the connection isn't much help. The name is simply the name of the variable you use to initially store the connection. The name can go out of scope, but that doesn't affect the existence of the connection.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TomLeMes]

Ah well, it was worth a try! The reason I was looking for this was that I wanted to avoid creating connections to our (remote) offices more than once. Each office has a 3 letter code which is used to create a standard "name" for each connection location at the point of creation. Then whenever I need to make data selections from a particular office I use the connection handle name in the SQLEXEC command.

Querying the vartype of this name (preceded with an ampersand) seems to be a fairly reliable test of whether the connection has already been created (vartype returns N if the connection handle exists, U if it doesn't exist), but I wouldn't be massively surprised if this produces false-negative results on occasion. Thanks for the help,

Tom

 

[Mike Lewis]

Tom,

Querying the vartype of this name (preceded with an ampersand) seems to be a fairly reliable ... but I wouldn't be massively surprised if this produces false-negative results on occasion.

I can't see why it should produce false negatives. That said, I suppose there's always the possibility that the name (that is, the variable) exists, but it doesn't currently contain a handle. Also, given that the variables are public, there's the danger of them being accidently released before the connection is closed.

To do it properly, you could consider writing a connection manager class. This would have OpenConnection and CloseConnecion methods, and an array property that holds all the currently open connections.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TomLeMes]

That sounds like an excellent idea - definitely one for my 'To-do' list. Thanks Mike,

Tom

 

[mkrausnick]

Quick and Dirty, but seems to work. It returns an 2 dimensional array containing the handle number and DSN of each open SQL connection:

Function ACONNECTIONS(aConnectionList)
LOCAL nCount,i,lFound
nCount = 0
FOR i = 1 TO 255
	lFound = .T.
	TRY 
		cDataSourceName = SQLGETPROP(i,"DataSource")
	CATCH 
		lFound = .F.
	ENDTRY 
	IF lFound
		nCount = nCount+1
		DIMENSION aConnectionList(nCount,2)
		aConnectionList[nCount,1] = i
		aConnectionList[nCount,2] = cDataSourceName
	ENDIF 
NEXT i
RETURN aConnectionList

Sample calling program:

CLEAR 
DIMENSION aList(1)
aConnections(@aList)
? ALLTRIM(STR(ALEN(aList,1)))+" connections found"
FOR i = 1 TO ALEN(aList,1)
	? ALLTRIM(STR(aList[i,1])) + " " + aList[i,2]
NEXT i
RETURN

Mike Krausnick
Dublin, California