Checking for an open SPT connection

[Mike Lewis]

A client has just asked me a question which I couldn't answer. I'm sure some of you folk can help.

He uses SQL Pass-Through (SPT) against a back end. He obtains a connection handle at the start of the session, and keeps it active until close down. This works fine.

He now wants to set the connection's IdleTimout property, so that the connection will close after so-many minutes of inactivity. His question is: How does he detect that the timeout has happened?

In other words, every time he calls an SPT function, he needs to check to see if the connection is still active, and to re-connect if it is not. How does he make that check?

I'm sure there is a simple way of doing this, but I can't think what it is.

Thanks in advance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[AndyKr]

Hi Mike

The only way I know of to do this reliably is to call some function that you know will work on the server and check for the error.

e.g.

lnRes = SQLEXEC( nHandle, [SELECT Getdate() AS test] )
IF lnRes < 1
  *** NOT connected!
ELSE
  *** Connected!
ENDIF

----
Andy Kramek
Visual FoxPro MVP

 

[Mike Lewis]

Thanks, Andy. That was roughly what I assumed he would have to do. I just wondered if there was a slicker way of doing it.

In fact, I'll probably suggest that, after every call to SQLEXEC(), he checks for a reply of -2. If that happens, he would then do the SQLCONNECT() again. It that succeeds, he is back in business.

In fact, I might add that feature to my base SPT class (which is based on -- but not as good as -- your own similar class).

Thanks again for your help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[DougHennig]

Hi Mike.

Perhaps he could use the SQLIDLEDISCONNECT() function. I haven't used this function myself, but according to help, VFP will automatically reconnect as soon as the connection is needed (ie. SQLEXEC), so no need to manually reconnect.

Doug

 

[Mike Lewis]

Hi Doug,

That looks interesting. I've never noticed that function before.

It's not exactly what he wanted, because it means he would have to manage his own timeouts. But it looks like it can achieve the same goal.

I'll pass it on, and let you know how it works out.

Thanks.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[AndyKr]

>>That looks interesting. I've never noticed that function before.

SQLIDLEDISCONNECT() is also limited to automatic transaction mode - if you have manual transactions enabled (as you should!!!) it is of no use whatever!

It was introduced in VFP 9.0 to mimic the behavior of .NET - which does exactly the same thing by default.


----
Andy Kramek
Visual FoxPro MVP

 

[Mike Lewis]

Andy,

SQLIDLEDISCONNECT() is also limited to automatic transaction mode - if you have manual transactions enabled (as you should!!!) it is of no use whatever!

Ah, good point. He does have manual transactions enabled (but only after I spent a long time explaining the advantages). I don't want him to go back to automatic transaction mode.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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