Connection has been marked dead error.

[Former Member]

Hi All,

Our PB application users some times work from home using VPN and connectivity dips (network disconnect and reconnect)are expected.

If such thing happens, our PB application throws the DB error : ct_cmd_alloc():user api layer: external error: The connection has been marked dead.

And the application has to be restarted to be able to work normally again.

At the same time, the webpages in a brower are reloading successfully when refreshed after a network dip.

I understand that our PB application is unable to resume DB connection like a browser.

Is there way to handle this situation in PB applications? I mean the application should be able to reconnect to the database after a network disconnect and reconnect.

Best Regards,

Kiran

[Former Member · Accepted solution]

Hi Kiran;

  Jacob's suggestion works quite well in my experience as well.

  Also, for PB version 12 or higher the Transaction object (ie: SQLCA) has new methods and events. One of the events is the SQLPreview. The SQLPreview for example is fired no matter whether the PB application's DML is in-line, DWO or SP related. Another interesting event is DBNotofication -  when a PowerBuilder database driver receives a notification from the database server.

  You might want to look into these new SQLCA features to trap and restart a DBMS connection if the current one fails.

Regards ... Chris

[Former Member]

Hi Kiran,

You could code a "liveliness" function on your transaction object to check and see if the connection is alive. It would simply execute a select getdate() from the db and return the sqlca.sqlcode. If sqlcode < 0 you could reconnect your transaction and continue. With a little thought you could put the liveliness check in an ancestor (with a flag to turn it on or off of course) and let it check all db calls prior to execution.

One other idea is to route all communications through a web service but that would more than likely involve a good bit of refactoring.

hth,

Mark

[Former Member]

In SQL Server 2008 you can use mirroring along with the FailOverServer database connection parameter and it will handle some disconnects automatically.

If you want your system to reconnect automatically all the time you would have to design it that way from the start (using Jacob's example is a good start).  Even so, if you have an uncommitted transaction when a failure occurs, you will have a hard time recovering.

Matt

[CobyKako]

Hello Kiran,

I can share my experience if your PB app connects to a SQL Anywhere database (using the sa_conn_info system stored procedure).

I did test it a long time ago , so please bear with me

To check if a SQLA connection is active:

1) Define a Timing object (say nvo_timer) in your PB app.

2) Code in the Constructor event of nvo_timer:

// ds_datastore is an instance variable of nvo_timer

// d_sa_conn_info is a DataWindow objects based on sa_conn_info

ds_datastore = CREATE datastore

ds_datastore.dataobject = "d_sa_conn_info"

3) Code in the Destructor event of nvo_timer:

If IsValid(ds_datastore) Then Destroy ds_datastore

4) Code in the Timer event of nvo_timer:

long ll_SA_CONN_INFO, ll_rows

SetNull(ll_SA_CONN_INFO)

ds_datastore.SetTransObject(SQLCA)

ll_rows= ds_datastore.Retrieve(ll_SA_CONN_INFO)

5) Define a global variable in the Application object

nvo_timer MyTimer

6) Code in the Open event of Application object:

// Processing each 4 seconds

MyTimer = CREATE nvo_timer

MyTimer.Start(4)

7) Code in the Close event of Application object:

If IsValid(MyTimer) Then Destroy(MyTimer)

P.S.: You can follow same approach with other RDBMS

Jacob

[Former Member]

Hi Jacob,

Thanks for your reply.

I understand that your approach tests every 4 seconds if the connection is active.

Can you suggest some approach for the application to reconnect automatically incase if the connection becomes inactive.

Regards,

Kiran

[CobyKako]

I'm afraid there is no good way to verify for connection disruption such as connection timeouts.

Check this thread which is talking about that: http://scn.sap.com/thread/3411329

Jacob

[Former Member]

If the connection is dropped, perform a DISCONNECT and then a CONNECT to re-establish a good connection.