[Openroad-users] App Server - Timeout Interval

[David Tondreau]

A very interesting subject, Kim.

A long time ago, we wrote an OpenROAD application that was used in over
5000 locations across the country all dialing in to a central server in
Washington, DC.  Dropped phone lines which resulted in lost networking
resulting in lost database connectivity was a big problem.  We perfected
the art of SQL retry, building a wrapper that managed database sessions
which could automatically reconnect if the session was lost much like
the suggestions already posted.  

However, to solve the "lost" SQL statements when a database session goes
away problem, our wrapper has two methods:  "PrepareSession()" and
"CheckSession()".  Then, we place all SQL in a while loop like this:

    CurMethod.DbSession = CurObject.PrepareSession(
        IsMST=IsMST, RequiredRows=RequiredRows, Status=Status);

    while (CurObject.CheckSession(Status=Status) = ER_CONTINUE) do

        //SQL

    endwhile;

    return Status.GetStatus();


The PrepareSession() simply sets an internal counter that says a SQL
statement is about to be executed and returns the managed database
session.  The CheckSession() method actually checks the state of the
database session, reconnecting if necessary.  On the first call, it
knows to simply return an ER_CONTINUE and increment the call counter.
On subsequent calls, it checks the error status of the statement and the
state of the session.  If the statement executes successfully, it simply
returns ER_OK and puts a success code in the status object.  If the
database session fails, it can reconnect and simply return an
ER_CONTINUE which will cause the statement to be retried.  It can also
handle other things.  For instance, if the number of "required" rows
(programmer defined) does not match, it can throw an error.  If the MST
flag is false, it commits.  If you want to test for deadlock errors or
any other specific database errors and handle them generically, it
provides a place to do that.  Etc., etc.

In the next week or so I'll be publishing my Data Access Object
Generator utility on the community site.  It generates OpenROAD user
classes that wrap tables in the database to provide a simple
object-relational infrastructure for building OpenROAD client and server
applications.  The default template for the code generation in the tool
uses this construct and has all the wrapper classes as well.


Regards, 

David 

David Tondreau
Architect, Ingres Corp.
http://community.ingres.com/