Check SQL Connection Status 1

[mejiaks]

I have one form open 24/7 and a global connection hanlder open from the main program (lnConn)

from time to time the SQL Service become unavailable (please do not focus on this problem cause i am not trying to solve this) due to this, the lnConn becomes useless

the form tries to read and write data to back end from time to time using SQL Passthrough like

lnSQLResult = SQLEXEC(lnConn,"select * from table")

i know if lnSQLResult is -1, this means something went wrong
I know aerror retrieves the error from the SQLEXEC
I Know element 1,1 is a Error Code
I know element 5 is a error code too

my question to you guys is the foloowing: what are the codes i have to test in order to know that there is a broken link between the computer and the SQL Server??

best regards

 

[Mike Lewis]

My understanding is that, if the connection gets broken, SQLEXEC() won't return anything. It will trigger an error (error 1466). So, it is not a question of testing the value returned by SQLEXEC(), but rather of trapping the error, either in your normal error-handler or by means of a TRY / CATCH / ENDTRY construct.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mejiaks]

I understood what you said.

Let me point to the statement I made:

AERROR gives you an array containing the error even if the link is broken

My question would be what are the error numbers that tell me the link was broken?

 

[Mike Lewis]

I've just tested this, and I'm seeing the error number is 1466 (as I said, above). That's the value in the first element of the error array. The message (in the second element) is "Connection handle is invalid".

Note that this is a native VFP error, in contrast to an error that is actually returned by ODBC. In that case, the VFP error number would be 1526.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mejiaks]

Indeed odbc error no is 1526

I am trying to find out if there are some other

 

[Olaf Doschke]

I see, you want to ensure, you don't slip any other error related to the same reason of a broken connection.

1488: If you try REQUERY() or TABLEUPDATE() on a cursor while it's connection is broken or already disconnected. Other errors are about DBC CONNECTION objects, but don't pay a role for connection handles retrieved by SQLCONNECT() or SQLSTRINGCONNECT().

You could check ASQLHANDLES(), but it doesn't pay to do so before each and any SQLEXEC(). I can tell you more tomorrow, back in the office.

Bye, Olaf.

 

[Olaf Doschke]

I looked into a databasemanager class and it turns out it actually does a "sqlvalidateconnect" before each Cursor operation within the Cursoradapter. And this validation method makes use of ASQLHANDLES() and checks, whether the connection handle it wants to use is still among the handles reported by ASQLHANDLES().

That's one of the nice features of Cursoradapters, it has events as BeforCursorFill(), BeforeCursorRefresh() etc.

This way it doesn't handle an error happening because of an invalid connection, but makes a check befoe using it. In case the connection handle is missing from the ASQLHANDLES() result array, the databasemanager reconnects with the database.

Bye, Olaf.

 

[Mike Lewis]

Olaf, that sounds like a good plan. Of course, you don't need to use cursor adapters to take advantage of it.

I have a generic "SQL pass-through" class (not a very good name, but it gives the general idea). It has a Send method that executes whatever command(s) you give it. I could easily add some logic to that method along the lines you suggested.

It would add a small overhead to every call to SQLEXEC(), but I can't see that being very big (more or less just calling ASQLHANDLES() and then ASCAN()).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Hi Mike,

yes, even though I said "but it doesn't pay to do so before each and any SQLEXEC()" yesterday, I found out the databasemanager does and it also does not hurt the applications performance.

And yes, that can also be applied in conjunction with sqlexec(). I still don't make that call to sqlvalidateconnect() before any TABLEUPDATE() of CA cursors, but of course that also is somewhat redundant.

Another idea is to store the conneciton handle in some property of your Applicationobject or of _screen or anyhting globally available. Then have a property_access method, which does the validation each time the handle property is read accessed in any place of the application.

And you can still also handle errors 1526 and 1466 specifically for broken connections.

Bye, Olaf.