SQL Server - Reconnect

[Former Member]

Hi all,

In the old version of our application which used the MSS native driver for the SQL Server, when the connection to the server was lost (eg the network card was configured to have a tight power management) after the eror message we had code to reconnect to the SQL Server.

After we migrated to newer versions of PB that they were not using the MSS (we are using the SNC now and SQL Server 2005/2008R2/2012) we cannot find a way to reconnect without having to restart our application.

I wrote this code but it does not fire

If SQLCA.SQLDBCode = 10005 OR SQLCA.SQLDBCode = 10025 Then

  MessageBox("Info","Click on OK To Reconnect!")

  DISCONNECT;

  CONNECT;

End If

I hope my description is not very vague.

I found the same issue asked by a guy here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103765

but no answer was given.

Thanx in advance!

EDIT: I think that the problem lies in the fact that SQLCA.SQLDBCode instead of giving me 10005 or 10025, it returns -1

Why doesn't it return the SQL Server error code?

[Former Member]

SQL Server 2005

SNC

PowerBuilder 11.5

+ I added some new findings in the first post.

Thanx

[Former Member]

Using current versions of sql server (2012) and  PB (12.5) with SNC, my application receives the error 10054 when the connection is killed from the database engine.  You should verify that your error logic is not accidentally covering up the actual error information.  And it seems that error codes change from interface to interface (and one should assume from version to version), so you should anticipate that in your application.  Perhaps one of the other transaction object attributes can be used to identify a disconnect - in my test the message had something that might be usable.

And BTW - the link you posted did have an answer - an OS update and a driver update.  That isn't particularly useful for your circumstances, but it is worth mentioning for future searchers.

[Former Member]

I did a little test. I loged into my app.

Then I disconnected physically from the LAN.

I clicked to start an operation which fired a function that called a stored procedure

I will put some code here in case anyone spots something fishy

The function:

DECLARE C PROCEDURE FOR pr_check_phase

@id = :ad_id;

EXECUTE C; //here it tries to execute but there is no connection to the server so the f_db_error below fires

IF f_db_error() = -1 Then

  CLOSE C;

  Return FALSE;

End If

The code of uf_db_error

If SQLCA.SQLDBCode = 10005 OR SQLCA.SQLDBCode = 10025 Then

  MessageBox("Info","Click on OK To Reconnect!")

  DISCONNECT;

  CONNECT;

  If f_db_error() = -1 Then

       MessageBox("Error","Reconnect failed! Please restart the application!",StopSign!)

  Else

       MessageBox("Info","Reconnection succeded!")

  End If

End If

It never gets inside the first IF clause because the SQLCA.SQLDBCode is -1

[Former Member]

Hi Panos.

I know this isn't the answer, but have you checked what is the value of sqlca.DBHandle? Normaly in your case it should be <> 0 and not null...

Also in you function you could execute a SELECT GETDATE() command. If this returns sqlca,sqlcode <> 0 then the connection should be broken...

Something like this:

If SQLCA.SQLDBCode = 10005 OR SQLCA.SQLDBCode = 10025 Then

  MessageBox("Info","Click on OK To Reconnect!")

  DISCONNECT;

  CONNECT;

  If f_db_error() = -1 Then

       MessageBox("Error","Reconnect failed! Please restart the application!",StopSign!)

  Else

       MessageBox("Info","Reconnection succeded!")

  End If

elseif SQLCA.SQLDBCode = -1 then

     execute immediate "SELECT GETDATE()";

     if sqlca.sqlcode <> 0 then

          CONNECT;

     end if

End If

Andreas.