cancel
Showing results for 
Search instead for 
Did you mean: 

Handling Connection Time Out

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (5)

Answers (5)

former_member190719
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

former_member190719
Active Contributor
0 Kudos

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.