Hi Darren,
We have the exact same problem with VPN connections for our client server application. It was particularly an issue for one window that uses a timer and needs to write to the database when the timer stops, but if there was a disconnection between the timer starting and stopping the database update failed.
I created a user object called n_cst_db_connection_utility that checks for a database disconnect and reconnects before that window writes to the database. This is the of_TestConnection function from that object:
integer li_return = 0
execute immediate 'select 1' using sqlca;
if sqlca.SQLCode <> 0 then
sqlca.of_Disconnect()
if sqlca.of_Connect() = 0 then
li_return = 1
else
is_error = 'There was a database disconnection and the following error occurred trying to reconnect~r~n' + sqlca.sqlerrtext + ' [' + string(sqlca.sqldbcode) + ']'
li_return = -1
end if
end if
return li_return
This is the of_CheckConnection Function
integer li_return
li_return = of_TestConnection()
if li_return = 1 then
if not isNull(apo_control) then
of_ReconnectDataWindows({apo_control})
end if
// Reconnect the main datawindows too
of_ReconnectDataWindows(gnv_app.of_GetMain().Control)
elseif li_return = -1 then
MessageBox('Database Disconnection', is_error)
end if
return li_return
In the ue_save event of the window it calls the of_CheckConnection function of our user object before continuing on with it's save logic (not shown)
n_cst_db_connection_utility lo_db_check
integer li_check
li_check = lo_db_check.of_CheckConnection(this)
if li_check < 0 then
return -1
end if
The important part of n_cst_db_connection_utility.of_CheckConnection is of_ReconnectDatawindow which just loops through all the datawindows on the passed in control and calls SetTransObject on them. SetTransObject needs to be called after reconnecting sqlca because the sqlca that was originally used is invalid after reconnecting.
This is the code for of_ReconnectDatawindows. apo_control is an array of PowerObject
Integer li_max
Integer li_i
PowerObject lpo_tocheck
powerobject lpo_SubControls[]
Datawindow ldw_control
Datastore lds_control
Object lo_type
// Loop thru all the objects
li_max = UpperBound (apo_control)
For li_i = 1 to li_max
lpo_tocheck = apo_control[li_i]
IF IsNull (lpo_tocheck) OR NOT IsValid(lpo_tocheck) THEN CONTINUE
IF of_CheckForControls (lpo_ToCheck, lpo_SubControls) THEN
IF UpperBound (lpo_SubControls) > 0 THEN
THIS.of_ReconnectDataWindows (lpo_SubControls)
END IF
Continue // Jump out of code and go back to looping through apo_Control
END IF
lo_type = TypeOf(lpo_tocheck)
choose case lo_type
case DataWindow!, DataStore!
choose case lo_type
case DataWindow!
ldw_control = lpo_tocheck
if Left(ldw_control.DataObject, 2) = 'd_' then
ldw_control.SetTransObject(sqlca)
end if
case DataStore!
lds_control = lpo_tocheck
if Left(lds_control.DataObject, 2) = 'd_' then
lds_control.SetTransObject(sqlca)
end if
end choose
end choose
Next
of_ReconnectDataWindows is recursive because for each control passed in, it checks to see if it has any subcontrols (e.g. a window with a userobject or tab with child datawindows). Here is the code for of_CheckForControls (lpo_SubControls is an array of powerobject passed by reference
// returns false if this is not a Window, Tab or UserObject or if it is a SUO
PowerObject lpo_empty[]
UserObject luo_control
Tab ltab_control
Window lw_control
Object lo_type
apo_controls = lpo_empty
lo_type = apo_Object.TypeOf ()
Choose Case lo_type
CASE Window!, Tab!, UserObject!
CHOOSE CASE lo_type
Case Window!
lw_control = apo_object
apo_controls = lw_control.control
Case Tab!
ltab_control = apo_object
apo_controls = ltab_control.control
Case UserObject!
luo_control = apo_object
apo_controls = luo_control.control
END CHOOSE
RETURN TRUE
END CHOOSE
return false
The code isn't ideal, but it saved us from losing work due to database connections for one specific window. Our of_CheckConnection also checks whether a couple of other important windows are also open and reconnects the datawindows on those two (that code isn't shown). I also show a message after calling this function if a database disconnection/reconnection has occurred letting the user know and suggesting that they restart to application for stability. Sorry for such a long post, but hopefully some of this may be helpful.
Works great for the immediate window and controls. But having trouble w/ the background windows. Was wondering if I could get a copy of your gnv_app.of_GetMain() ?
// Reconnect the main datawindows too
of_ReconnectDataWindows(gnv_app.of_GetMain().Control)