Source language: Translate to:

Connection: keep alive

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Connection: keep alive

Postby stu » Wed Oct 01, 2014 12:55 pm

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
User avatar
stu
 
Posts: 320
Joined: Wed Aug 07, 2013 11:37 am

Re: Connection: keep alive

Postby Neosoft Support » Thu Oct 02, 2014 10:03 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Connection: keep alive

Postby stu » Fri Oct 03, 2014 11:17 am

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!
User avatar
stu
 
Posts: 320
Joined: Wed Aug 07, 2013 11:37 am

Re: Connection: keep alive

Postby Neosoft Support » Sat Oct 04, 2014 2:40 pm

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/
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Connection: keep alive

Postby stu » Tue Nov 18, 2014 1:12 pm

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...
User avatar
stu
 
Posts: 320
Joined: Wed Aug 07, 2013 11:37 am

Re: Connection: keep alive

Postby Neosoft Support » Tue Nov 18, 2014 1:21 pm

I think it's a good solution. Thank you for sharing it with us.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Connection: keep alive

Postby stu » Wed Nov 19, 2014 5:31 pm

No probs! I'm very happy about the result so far... =)
User avatar
stu
 
Posts: 320
Joined: Wed Aug 07, 2013 11:37 am


Return to NeoBookDBPro

Who is online

Users browsing this forum: Yahoo [Bot] and 0 guests