1. Darren Longenecker
  2. PowerBuilder
  3. Friday, 3 June 2022 20:57 PM UTC

When the DB connection fails in our app, there *can* be many SQLSTATE = 08S01 errors that annoy the users. 

What they'd like is for the app to reconnect if disconnected. 

I'm trying to, firstly, circumvent all the exceptions, but having trouble.

I've tried messing w/ the error, dberror, and systemerror events but I'm not getting it.

I tried this (adding my own message and returning 1) but it just adds my own messagebox in addition to the error popups: https://docs.appeon.com/pb2019/datawindow_reference/ch08s12.html 

I use SQLCA. My testing involves logging into app, then killing the connection within SQL Server, then trying to retrieve a dw in the app, thereby forcing the 08S01 error.

I'll get this error first:

Then (n) number of these:

 

Ideally, I'd like to pick out the error code behind the scenes, if it's 08S01 then alert user that I'll try to reconnect. Then if cant connect, alert user and exit.

 

 

Accepted Answer
Daryl Foster Accepted Answer Pending Moderation
  1. Tuesday, 7 June 2022 02:39 AM UTC
  2. PowerBuilder
  3. # Permalink

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.

 

 

 

 

 

 

 

 

 

 

Comment
  1. Darren Longenecker
  2. Tuesday, 7 June 2022 15:38 PM UTC
This looks terrific! thank you so much for sharing this. I'll give this a shot, looks like it'll work very well w/ what we have.
  1. Helpful
  1. Darren Longenecker
  2. Tuesday, 7 June 2022 21:40 PM UTC
Daryl,

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)
  1. Helpful
  1. Daryl Foster
  2. Wednesday, 8 June 2022 00:24 AM UTC
Hi Darren, see my reply above with some more details
  1. Helpful
There are no comments made yet.
Daryl Foster Accepted Answer Pending Moderation
  1. Wednesday, 8 June 2022 00:23 AM UTC
  2. PowerBuilder
  3. # 1

Hi Darren,

The function

gnv_app.of_GetMain().Control

just returns the Control array from my main window.  gnv_app is just a NVO application manager similar to that used in the PFC.  of_GetMain() will just return the main window that we have setup as the main window for the application. If your main window is w_main you could just do:

w_main.Control

That will only connect the datawindows on the main window though. To get it to reconnect all the datawindows on all the windows you need to call of_ReconnectDataWindows on all the open windows.  I don't think there is a built in way to get all of the open windows in an application. We use a window manager that tracks most of our application's windows when they open and close.  It's build into our ancestor windows to register with the window manager when they open and de-register when they close.  The other bit of of_CheckConnection that I didn't show uses that window manager to reconnect datawindows in the open windows tracked by our window manager.

	of_ReconnectDataWindows(gnv_WindowManager.iw_handler)

in the above code gnv_WindowManager.iw_hander is an array of open windows in the application. It is pretty straightforward to do, but does depend on all the windows registering themselves, which is easy if you have a common ancestor window.  The code for my window manager is pretty specific for our application so I can't share the complete source, but this discussion has a good outline of what is involved.  Chris' STD framework implements something similar. https://community.appeon.com/index.php/qna/q-a/get-all-open-windows

The other shortfall of my code is that it doesn't reconnect dropdown datawindows in any datawindows that you reconnect to, but for me that was a limitation I was happy to live with.

If you need any help implementing a window manager just let us know.

Comment
There are no comments made yet.
Darren Longenecker Accepted Answer Pending Moderation
  1. Monday, 6 June 2022 19:01 PM UTC
  2. PowerBuilder
  3. # 2

PB12 5530 and PB19 2082 

SQL Server 2017

SQLCA.DBMS = OLE DB; Provider = MSOLEDBSQL

DBParm = DBTextLimit = '64000', AppName = 'myapp', Host - 'mypc', PROVIDER = 'MSOLEDBSQL', DATASOURCE = 'myserver', ProviderString = 'database = mydatabase'

 

To add, the problem isn't w/ powerbuilder's db connection, --it' the VPN used to connect users from abroad to the centralized SQL Server.

Their VPN connection will drop in/out, meanwhile when they try to open a window w/ multiple datawindows/retrieves, my app will show them a 'communication link' db error for every attempted/failed retrieve. Depending on the window there may be up to 20+ different retrieves on open, causing 20+ error popups. 

So this understandably annoys the users and they want me to do something about it. Development-wise, I'd like to show one error, notify of trying to reconnect, and if so, close the window/process to nix any db changes since disconnect. If can't reconnect, then show error and close app.

I don't have control over the VPN, is it what it is, unfortunately. 

The best I've been able to do so far is putting code into the dberror event of the transaction object to reconnect SQLCA. It works for some dw's. On others though, it hard crashes my pb IDE. It appears that PB doesn't like the reconnect code for dw's that have code in their retrieveend event. When stepping thru app, the hard crash happens when moving from the dberror event straight to the retrieveend event.

 

Transaction Object-->dberror event:

if code <> -1 then 

	IF sqlca.of_IsConnected() = TRUE then
		sqlca.of_Disconnect()
	END IF

	/* Attempt to connect to DB */
	IF sqlca.of_Connect() <> 0 THEN
		[messagebox w/ error message]
	END IF

end if
return 1

 

Comment
  1. Roland Smith
  2. Monday, 6 June 2022 19:56 PM UTC
The best way to check if the database connection is live is to perform a simple SQL statement and check sqlca.SQLCode. If it fails, do a DISCONNECT and then a CONNECT.



A simple SQL statement could be: SELECT TOP 1 getdate() INTO :ldt_current FROM tablename;



For tablename, use a table that has only a few rows. Some database servers have a table DUAL for this purpose.
  1. Helpful 1
  1. John Fauss
  2. Tuesday, 7 June 2022 04:19 AM UTC
That's a good idea, Roland. We also use a trivial query in SQL Server: SELECT * FROM (SELECT dummy = 1) AS Temp;

Utilizing a simple sub-select has the advantage that no tables are touched.
  1. Helpful 2
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 6 June 2022 17:59 PM UTC
  2. PowerBuilder
  3. # 3

Darren, I feel your pain!

We are migrating from Oracle to PowerBuilder and between securing and the PG databases themseves, they tend to drop the connections if the app is left open but no activity is occurring.

Like you I've tried a variety of things to keep the connection alive but I cannot seem to get it to work. Apparently inactivity is NOT something you can fake with a timer.

My best attempt:   30 minute timer set in the IDLE event of the app, if it fires then drop the connection and reconnct for every object in every open window. It does not work and I don't know why: the connection still drops at the 1 hr of inactivity mark.

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Saturday, 4 June 2022 21:15 PM UTC
  2. PowerBuilder
  3. # 4

Hi, Darren -

Why are these disconnects occurring? Does this happen to all users, one, or only a few? What commonalities (if any) do they share?

Perhaps it might be more fruitful to try and address the root cause of the problem rather than trying to mask & recover from the behavior.

And, FYI, it is extremely helpful when asking database-related questions for everyone to know some key details of your environment, such as:

  • RDBMS vendor and version.
  • PB version, release & build.
  • Which PB database client you are using (SQLCA.DBMS=???).
  • If you are using ODBC, the ODBC driver & version.
  • SQLCA.DBParm settings (mask any sensitive setting values, of course).
  • If the problem has started only recently, what factor(s) changed?

I did a search on "SQL Server 08S01" and got numerous hits. A quick look at several (dating back a decade or more) seems to indicate that this could be related to network or transport layer security (TLS) issues. I suggest you do a similar search and see what you can learn about this issue. This issue does not appear to be caused by the application.

Regards, John

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 3 June 2022 23:02 PM UTC
  2. PowerBuilder
  3. # 5

Hi,

The problem of having "many" messages, is that any SQL should check for errors. If the code just keeps running, you'll get many indeed.

The problem I see with reconnecting are transactions. What if your being disconnected in the middle of several DB operations? Will a reconnect get you back at the point where you were? Should you automatically close all windows and rollback?

Maybe there's better ways, but I think the best approach is to avoid being disconnected and IF you are, shut down the application (and optionally use the Restart() command).

regards,

MiguelL

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.