Skip to Content
avatar image
Former Member

Handling Connection Time Out

When Connecting to SQL Anywhere via ODBC a client application can get disconnected from the server either by the LiveNess Timeout (where a regular check run by the connection determined that communication has dropped) or by the Idle Timeout where a certain amount of time has passed with no activity between the client and the server.  As I understand it, parameters which control the default Idle or Liveness test periods can be configured with Server Parameters -ti (idle) and -tl (liveness).  These can also be overridden by the ODBC Client connection parameters.

One could try to ensure that disconnection doesn't always happen on idle or liveness timeout - not always a best practice

The question is how to best determine if an idle application has lost its connection so the user does not try to run a SQL or Datawindow Query and have the application error.

Some years ago I saw samples where a SQL query was run on an idle timer - then checking the DB Return Code and then use Disconnect and Connect to reestablish the connection. 

Is this still the best practice or have their been enhancements in the PB environment (like connection error events) that can simplify this handling.

Can anyone post some good sample code?

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Aug 24, 2013 at 04:05 PM

    That's still the best practice, the PowerBuilder connection won't know that it's been cut off until you try to use it.

    Alternatively, you can add code to a timer to send a 'keep alive' request to the database at a frequency shorter than the timeout.  That way the database connection doens't time out and you don't have to deal with re-establishing the connection.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 26, 2013 at 03:55 AM

    Hi Glenn;

       You can perform the following checks ...

    1) SQLCA.DBHandle ( ) > 0

    2) Issue a "Select 1 from DUMMY;" in SS/ASE/SA or "Select 1 from DUAL" in Oracle and check the return code.

    3) In PB 12 and higher, the Transaction Object has a new "DBNotification" & "DBError" events where you can trap timeout issues.

    HTH

    Regards ... Chris

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Chris,

      According to the doc, DBHandle() is not able to determine if the database connection does not exist or has been lost, so I'm not sure what it's useful for.

      DbError event will trap only request errors and I think DbNotification is not supported with ODBC.

      I would stick with SELECT 1 FROM DUMMY (SELECT 1 in SqlServer) in a timer or idle event.

      Regards

      Guillaume

  • avatar image
    Former Member
    Aug 26, 2013 at 12:23 AM

    idle event is definitely a good place to evaluate, not only whether the database is still available, but whether the end user is still around.  The user might be half way through keying something in on a screen and been called away.  There may be unsaved data. 

    I would free up the connection and prompt for a new one:

    disconnect using SQLCA;

    idle(0)

    open ( w_relogin ) // response window with password and reconnect.

    if message.doubleparm = -1 then halt close // or something less brutal

    idle ( 60 * 60 ) // what ever is a reasonable time

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 27, 2013 at 06:06 PM

    Glenn,

    I don't know what LiveNess timeout is but I assume it's some method of managing idle database connections in order to free up resources. In Oracle we define IDLE_TIME on the user's profile to do this.

    First if your SA or DBA has an idle timeout policy in place and client programmers are defeating that policy with a "keep alive" function in their code I would personally have a big problem with that. That's a terrible idea to circumvent a policy. Perhaps they put this policy in place for security and performance reasons.

    Second the Powerbuilder idle event to detect an idle connection is a bad idea. You can not sync a database timer with a client side timer.  I would disagree that is proper support of a server policy.

    Just let the DBMS tell you when a connection has been closed. There is no need to issue phantom SQL to ping the database to see if the connection is alive.  All should have an error code for idle timeout. For example in Oracle it is

    ORA-02396

    exceeded maximum idle time, please connect again

    You can check for the code in the dberror event. I do this on my transaction object but you could also in your datawindow and datastore base classes.

    It's as simple as

    if sqldbcode = 2396 then

        MessageBox( "Exceeded maximum idle time", "You have been disconnected from the server due to " + string( i_l_idle_time ) + " minutes of inactivity.~r~n~r~nPlease logon again to continue." )

        Disconnect using sqlca;

       

        // call the logon process you used to connect the first time

    end if

    Once you reconnect to the database you may need to re-issue all your SetTransObject( ) calls. In my environment they become orphaned when the connection is idled out.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 16, 2014 at 03:47 AM

    Yes, that's still the best practice (issuing a SQL statement).  The database will look open to PowerBuilder until an actual SQL statement is attempted.

    Add comment
    10|10000 characters needed characters exceeded