Hi,

David Peace set it before, in Oracle we can set a setting on the server to keep connections alive.

You are on ASE, is your connection a CT-Lib connection?

Have your tried setting this value in dbparm: CS_CON_KEEP_ALIVE=1 ?

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc00042_0250/html/dwbparm/BFCDIGBF.htm

Can't hurt to see if it has any positve effect, I'd say.

Regards,

HIH

Just a few more notes.  I have turned off everything that could go to sleep and still getting disconnect.  I think the suspect is the Verizon card.  I did notice that for some reason between 30 AND 45 minutes within the hour (i.e 1:30 to 1:45... 2:30 to 2:5) something is happening within the network where I do lose my DB connection.  I am currently trying to create a reconnect service.. one question is there a way to get the transaction that was used by the datawindow/datastore?  (We use Settransobject for connection so GetTrans won't work).

Hi David,

We experienced much the same thing with our apps, and got around it by putting a bunch of reconnection logic into an overloaded Error object.  We were already using the Error object as a place to store and display SQL errors, so in our SQL error setter function in which we save SqlDbCode and SqlErrText, we check the SqlDbCode to see if we suffered a disconnection.  In SQL Server using OLE DB, that would be 11 or 999, but for your DataBase it may well be different.

In the event of a disconnect error,

1. reconnect SQLCA.  For us, we found the most reliable way was to create a brand new Transaction object, connect, and store it as SQLCA.
2. Starting with our frame window (virtually all our apps are MDI) and cycling through any open sheets, call a recursive function passing the window as an argument to reset the transaction object for any data windows.  The code for this is not too bad.   Call the TypeOf() method on the passed in object to see what it is.  If a DataWindow, call SetTransObject, if a Tab, UserObject, of Window, iterate through the object's Control array and call the recursive reset function for each object in the Control array.
3. To handle the situation of DataStores that are not stored in the Control array, we added a user event to our base objects called ueSetTransObjects, and called this event dynamically for the object in the recursive reset function.  Being a dynamic event call, it would not hurt anything if called on an object for which the event had not been declared. 

To make things a bit easier to code, we added a function to our overloaded Transaction object, ufWakeSQLCA() which consisted of some embedded SQL to call GETDATE.  In the event of an error, we save the error information in the Error object using our SQLError setter function, and if the error was caused by a disconnect, it would trigger the reconnection process described above.

So with the Error and Transaction objects thus enhanced, we went through the applications to enable the reconnection functionality.  It was a little bit of work, but pretty much rather mindless assembly line type of work.  And Windows, Tabs, or UserObjects that had instance level DataStores, we would code the ueSetTransObjects event to call the SetTransObject for each DataStore.  And prior to any SQL operation, we would call the ufWakeSQLCA(). 

The end result has proven extremely robust.  Even when intentionally trying to foul things up while testing by explicitly killing a user's connection at the most inopportune time does not impact the application, as it simply reconnects prior to the next save or retrieve.

Andy

Tips that worked me in certain situations. Your setup may differ and render these tips useless.

1. Automated app shutdown for security purposes
   Shutdown app for security - when possible select time threshold just short of Power Sleep limit.
   

   // APP.Open
      ...
      li_sleep = gnv_WinAPI.of_GetPowerSleepThreshold( )
      Idle((li_sleep * 60) - 30)
   // APP.Idle
      Close(w_frame)
      HALT CLOSE​

2. Surround every "active transaction" with new DB connection
   This only when extra cost is acceptable = "large" time period between connections.
   

   // PUBLIC FUNCTION n_OurWindowsService.of_Run( )
   CONNECT USING itr_SQLCA;
   // do-work ...
   DISCONNECT USING itr_SQLCA;​

3. Extend "Begin Transaction" to include connection check.
   Easy add-in when some kind of of_BeginTran already exists.
   

   // PUBLIC FUNCTION tr_base.of_BeginTransaction( )
   SELECT a INTO :var FROM (select 1 a) dummy USING this;
   if this.SQLCode <> 0 then
      DISCONNECT USING this;
      CONNECT USING this;
   end if
   this.AutoCommit = false​

Hi David

Your suggestion of "Power Saving" on network cards is quite likely. Having had these sorts of problems in the past which were either cause by network latency and lost packets or as you say hardware issues. PC Configuration for Power Saving (Laptops especially) need to be sorted.

I don't know enough about ASE but  Oracle SQL Net can auto reconnect at that layer which solved our problem. Chris' suggestion of a "Keep Alive" function that pings the DB would aslo keep the hardware alive by poking the network on a regular tick.

The problem with trying to code around a lost connection is that by the time you realise you have lost the connection it's usually too late to do much about it. Certainly the transaction you were working on will be lost and rolled back. You can reconnect of course and the system will continue to work from the start of th next new transaction.

Personally I would go for Chris' ping on a timer as a dirt kludge, and hit the IT to get the hardware power saving options to be properly configured or networking issues fixed. Tryingto code round crappy network and hardware configiration is a waste of effort.

Cheers

David Peace

Hi Dave, PB runtime doesn't have "objects in memory" list for your app to inspect and process. You would have to write such add-on for relevant classes. I know adding generic fault tolerance to existing apps; especially large apps ; can be nightmarish depending on current code structure.

I've used technique like below to have PB app run in "occasionally disconnected" environment. Connection counter on transaction object and data containers (datawindow + datastore) helps system recover and self-heal when lost connection can be restablished.

You could evolve to attempt several re-connects over certain time period before finally aborting.

REMEMBER: Also check for DB connection before initiating embedded SQL!

// CLASS = tr_base -- inherits transaction -- -- -- -- -- -- -- -- -- -- -- --
// Instance variables
   PUBLIC:
   PrivateWrite long il_currentConnection
// PUBLIC FUNCTION of_Connect
   CONNECT USING this;
   if this.SQLCode = 0 then
      this.il_currentConnection ++
      return 1
   else
      return -1
   end if
// PUBLIC INT FUNCTION of_AssertConnected
   long value
   SELECT 1 INTO :value FROM (SELECT 1 a) dummy USING this;
   if this.SQLCode = 0 then
      return 1
   else
      this.of_Disconnect()
      return this.of_Connect()
   end if


// CLASS = dw_base -- inherits datawindow -- -- -- -- -- -- -- -- -- -- -- --
// Instance variables
   PRIVATE:
   tr_base itr_sqlca
   long il_currentConnection
// PUBLIC FUNCTION of_SetTransObject(tr_base atr_sqlca)
   if this.SetTransObject(atr_sqlca) = 1
      this.itr_sqlca = atr_sqlca
      this.il_currenctConnection = atr_sqlca.il_currentConnection
   end if
// EVENT RetrieveStart
   if this.itr_sqlca.of_AssertConnected() < 1 then
      // DB connection lost for good => Give up!
      return 0
   end if
   if this.itr_sqlca.il_currentConnection > this.il_currentConnection then
      // Re-connect to restablished connection
      this.of_SetTransObject(this.itr_sqlca)
   end if


// CLASS = ds_base -- inherits datastore -- -- -- -- -- -- -- -- -- -- -- --
// SAME added variables and logic as for dw_base


// Code anywhere -- using embedded SQL -- -- -- -- -- -- -- -- -- -- -- --
// standard embedded SQL rhythm
   if SQLCA.of_AssertConnected( ) < 0 then return -1
   SELECT COUNT(*) FROM ... USING SQLCA;
   if SQLCA.SQLCode < 0 then return -1
   SQLCA.of_MyStoredProc(...)
   if SQLCA.SQLCode < 0 then return -1
   ...

HTH /Michael

Hi David;

  FWIW: I have encountered this situation in the past with various DBMS where the DBA has set a low "inactivity" timeout setting that automatically disconnects the App(s) when the user minimizes the app for long periods of time or just locks the PC and walks away for a meeting, lunch, etc.

  I tried to first resolve this situation like you and thought about how to re-establish the DB Connection again and then reset all the DWO's SetTransObject() pointers again. However, once you think about that aspect in more detail, you quickly realize that this is not a trivial programming task. Especially, when you have update business transactions in progress.  :-(

  My final solution was to use a "preventative" design like many Application Servers do where, the DBMS connection(s) are kept alive via pinging the DBMS on a regular basis in order to not let the "inactivity" DBMS timer expire. Of course, we also tried convincing the DBA's to turn this "inactivity" setting off (infinite wait) but in most shops, that was met with a resounding NO or bold laughter.  ;-)

  The "DB Ping" feature of App Servers is also commonly known as a "Heart Beat" feature. So what I did was implement this in a PB App and low and behold, the DBMS disconnects completely disappeared! Once that worked, I went one step further and implemented this as a "Heart beat" DB feature in my framework. You can see it at work here in my Demo App's log, as follows:

  The HB feature is controlled by the App's Transaction Object's properties, as follows:

  My framework extends the properties of PB's Transaction object class to allow the PB developer to set the heartbeat criteria. Thus, each TO that connects to the same and/or different DBMS / DB combination can have it's own HB specific settings.

  When the framework sees that the "ib_heartbeat_required" Boolean flag is ON, it automatically starts a "Timing" object for the period as defined in the "il_heartbeat_time" property. When the Timing Object reaches the timer limitation, it sends an "oe_heart_beat" event to the TO. The TO responds by performing an "Execute Immediate" using the SQL in the "is_heartbeat_sql" property.

   Note that the TO does not perform this HB action if between HB's, any SQL is sent out by the App. This DML activity resets the HB timer for each SQL sent. Also, if the HB fires and the App is waiting for a long result set, the HB feature will also not fire.

  If you like this approach and want to look deeper into this implementation. Please check out the "Heart Beat" feature used by OrderEntry Demo App built from the STD Integrated framework.  FYI:  https://sourceforge.net/projects/stdfndclass/files/Applications/PowerBuilder/OrderEntry

HTH

Regards ...Chris