Page 1 of 1

Connection: keep alive

PostPosted: Wed Oct 01, 2014 12:55 pm
by stu
I been working for a bit on a way to keep the connection to the database srv alive or at least make the application self aware of its state so in case connection is lost it will try to reconnect on a sort of loop that will stop once app gains connection again.

Currently I have a timer that goes every 60 secs and refreshes the tables and helps good, even for over a week, but sometimes server restarts or something happens and its just goes off, thus the need to do something a little more complex.

Thanks

Re: Connection: keep alive

PostPosted: Thu Oct 02, 2014 10:03 am
by Neosoft Support
The timer is a good idea. Instead of refreshing the table maybe you could use dbpExecSQL and have the server execute some innocuous command. Then check the [dbpError] variable and see if you get an error. I'm not sure this will work, but it's worth experimenting with.

Re: Connection: keep alive

PostPosted: Fri Oct 03, 2014 11:17 am
by stu
I'm not getting good results, well, actually its not that bad, but at some point on trying to reconnect it keeps showing online when in fact its offline.

This is on my timer, executing itself every 60 secs...

Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
ClearVariables "[dbpError]"
dbpExecSQL "DBS" "SELECT COUNT(ID) FROM table1;" ""

If "[dbpError]" "=" ""
   dbpRefresh "DBS" "table1"
   dbpRefresh "DBS" "table2"
   dbpRefresh "DBS" "table3"
   setvar "[CONNECTION]" "Connected"
else
    setvar "[CONNECTION]" "Disconnected"
    GoSub "RECOVER_CONN"
endif
Parsed in 0.006 seconds, using GeSHi 1.0.8.10


this is the subroutine...

Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
:RECOVER_CONN

setvar "[xx]" "1"
While "[xx]" "<=" "6"
      FileRead "!C:/data.dat" "[xx]" "[xx[xx]]"
      Math "[xx]+1" "0" "[xx]"
endwhile

ClearVariables "[dbpError],DBS.$Status]"

dbpShowErrors "No"

dbpOpenDatabase "DBS" "Provider=[xx5];Driver={[xx6]};Server=[xx1];Database=[xx2];User=[xx3];Password=[xx4];Option=3"

DeleteArray "[xx]" "All"

If "[DBS.$Status]" "<>" "Connected"

   dbpOpenTable "DBS" "table1" ""
   dbpopentable "DBS" "table2" ""
   dbpopentable "DBS" "table3" ""
 
   SetVar "[CONNECTION]" "Connected"

else
   
   setvar "[CONNECTION]" "Disconnected"

endif

Return
Parsed in 0.007 seconds, using GeSHi 1.0.8.10


Any sugestions?

Cheers!

Re: Connection: keep alive

PostPosted: Sat Oct 04, 2014 2:40 pm
by Neosoft Support
Maybe instead of checking the error variable you could look at the data returned or not returned by the server.

Also, this link may have some useful ideas:

http://www.mssqltips.com/sqlservertip/1477/methods-to-determine-the-status-of-a-sql-server-database/

Re: Connection: keep alive

PostPosted: Tue Nov 18, 2014 1:12 pm
by stu
Ahh!, took a while but finally I manage to come up with this:

The start:

Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
dbpShowErrors "No"
GoSub "INIT"
Parsed in 0.005 seconds, using GeSHi 1.0.8.10


The sub routine:

Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
:INIT
     dbpOpenDatabase "TESTAPP" ""
     dbpOpenTable "TESTAPP" "table1" ""
Return
Parsed in 0.005 seconds, using GeSHi 1.0.8.10


A timer:

Syntax: [ Download ] [ Hide ]
Using Neobook Syntax Highlighting
ClearVariables "[dbpError]"
dbpExecSQL "TESTAPP" "SELECT COUNT(ID) FROM table1;" ""
if "[dbpError]" "=" ""
   setvar "[STATUS]" "ONLINE"
else
    dbpCloseTable "TESTAPP" "table1"
    dbpCloseDatabase "TESTAPP"
    GoSub "INIT"
    Clearvariables "[dbpError]"
    dbpExecSQL "TESTAPP" "SELECT COUNT(ID) FROM table1;" ""
    if "[dbpError]" "=" ""
        setvar "[STATUS]" "ONLINE"
    else
        setvar "[STATUS]" "OFFLINE"
    endif
endif
Parsed in 0.006 seconds, using GeSHi 1.0.8.10


I think it looks weird and I'm pretty sure there's a better way of getting the same result but I gotta say, it works perfect for my needs, it will wait until server is back and connect again...

Re: Connection: keep alive

PostPosted: Tue Nov 18, 2014 1:21 pm
by Neosoft Support
I think it's a good solution. Thank you for sharing it with us.

Re: Connection: keep alive

PostPosted: Wed Nov 19, 2014 5:31 pm
by stu
No probs! I'm very happy about the result so far... =)