Skip to Content
author's profile photo Former Member
Former Member

Connection has been marked dead error.

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2014 at 01:07 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 09, 2014 at 11:07 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2014 at 01:12 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2014 at 12:37 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.