Classic judgment database connection disconnection problem, many times there is no answer.

Classic judgment database connection disconnection problem, many times there is no answer. Delphi/Windows SDK/API
Http://www.delphi2007.net/DelphiDB/html/delphi_20061215202145283.html
When the program is connected normally, the database connection may be abnormally disconnected due to reasons such as server restart or network disconnection. In this case, both ADOConnection1.Connected and ADOConnection1.State display the connection status (True and stOpen respectively ), but in fact, the connection has been disconnected. After the server and network are normal, the only way is to close and re-open the program, which is obviously unfriendly.

It is obviously unrealistic to reconnect to the database once every time the data is queried and the connection status is not determined.

How can I determine whether a connection is lost in AdoConnection? So that we can reconnect to the database:
ADOConnection1.Connected: = False;
ADOConnection1.Connected: = True;

Try
ADOConnection1.Connected: = False;
ADOConnection1.Connected: = True;
Except
Problematic
End;

It is obviously unrealistic to reconnect to the database once every time the data is queried and the connection status is not determined.

Upstairs, doesn't it mean you reconnect to the database every time.

The key point is how to "judge" ADOConnection1.Connected = True, but AdoConnection actually has lost the connection?

Use timer to brush,

Dynamically create TADOConection, set ConnectionString, and then Connected: = True;

You can judge the exception!

Score !!!

Function ConnectAppServer: boolean; // determines whether the application server is connected.
Begin
Result: = True;
ADOConn. Connected: = false;
Try // test the connection
ADOConn. Connected: = true;
Failed t // not connected
Result: = False;
End;
End;

Call time:
If ConnectAppServer then
Begin
Processing functions;
End
Else
Begin
Showmessage ('Sorry! Database lost connection ');
Failed to process;
End;

Use
Try
Except
Reconnection
End;

The numbers above are determined only after the database is re-connected each time. This is not ideal, at least the response speed is slow.

I was afraid that you would answer this question before I made a statement in advance. Maybe you are anxious to miss the question? :(

If this problem can be solved, I will add another 200 points.

Connect when needed, usually disconnected

It takes a lot of network resources to determine whether the network is disconnected or not in real time.

ADOConnection1BeforeDisconnect event
Or
ADOConnection1AfterDisconnect event
Or
Starts from the ADOConnection1Disconnect event.

The upstairs is creative and I have learned

How do I feel that the event is useless !?

You can determine whether the database port is open. If false is returned, the communication is interrupted...

You can perform the operation first. If an operation exception occurs, check whether the database connection fails and re-execute the operation you want.

There is no value to discuss about such issues.

1. If the network is disconnected, you have to close the program no matter what the program determines.
2. If it is not disconnected, the system will automatically try to connect

If you judge each time, it will cause a great burden on the system.

Generally, if an error occurs when you select a "officer", you will find that the network is disconnected only when an error type is queried.

Of course, some clients have been used for a long time, but the connection status needs to be displayed. It is also necessary to make a heartbeat query to regularly detect connections, but it takes a little longer...

Thank you (Xingxing farm)
----
There is no value to discuss about such issues.

1. If the network is disconnected, you have to close the program no matter what the program determines.
2. If it is not disconnected, the system will automatically try to connect

If you judge each time, it will cause a great burden on the system.
----

There are still some cutting-edge questions in the answers from Hank (Xingxing farm), but I am not careful about the questions.

1. The network has been completely disconnected and you have to ask why, for example, you cannot.
I wrote "The same as when the server and network are normal". ADOConnection1 shows the connection status, but the connection has actually been disconnected.

2. The system will automatically try to connect. How can the system automatically try?

3. If you judge each time, it will cause a great burden on the system. This is indeed the case.

Please read the question carefully.

In my opinion, the concept of "thank you" is worth your reference.
Determining whether the network connection is normal depends on whether the actual application needs it, and it is very resource-consuming to use timer to disconnect the connection.
I have an idea. Suppose we think the connection is normal, and every time we query from the database system table:
In sqlserver, select sysdate,
In oracle, select sysdate from dual
If no error occurs, we think it is normal. If an error occurs, we close the connection and join again. What do you think?

Note: I have carefully read the questions and know what you mean.

1. The system is running well and the server is restarted. The client does not prompt at this time. However, when the client performs database-related operations, the system will prompt disconnection, it doesn't make sense to give the customer a prompt at this time, because in the actual process, if you re-initiate the request, either the Administrator notifies you in advance or calls you to ask.

2. If you perform system data operations after restarting, the system will try to connect itself. Isn't this automatic connection?

3. If you really want to judge, it's too long to create an ADOConnection to try to connect to the database.

4. If your application is the kind of gameplay that if the server cannot connect to the data and temporarily save it locally, it is best to do it through transaction processing.

Such requirements also appeared in the system a few days ago.
I tried some methods:
...............
1. Use your connection to perform a simple query operation. For example, select getdate () to check whether the returned value exists.
2. The multi-thread method is used. A ADOConnection is used to determine the network and a global variable is provided to record the network status. During the operation, only the variable status value is determined.
3. in Indy mode, the client and server are placed in one. after each client sends a message, if the server receives the message, a value is immediately returned. if the client sends a "1 + 1" message, the server immediately returns "2 ". of course, you need to add some agreed format strings. Otherwise, the client will be confused. if the returned value is not received within the specified time, the network has failed.

Basically all systems face this problem. My solution is:

Define a Timer and perform a simple query operation on a regular basis. For example, Select SYSDATE from DUAL to determine whether the query is disconnected Based on the result, and then ReConnect

I think it is wrong for you to know that you have to determine whether to connect and not allow others to refresh the detection.
I guess you are afraid that the refresh detection time is too long, so you can set an adoConnection separately, set its timeout to a shorter value, and then use a dataset to query for example: select 1 as testnum
If an error occurs, the connection may be faulty.

If you want to determine whether the database connection is normal by checking whether the network is connected, that is not acceptable.
You can try it out: Unplug the network and immediately plug it in again. It can be done in one second, but the database is disconnected.

I feel that there is no good way.

This is because the network is disconnected. as mentioned above. windows cannot connect to the network either. you also need to determine the time, just like the local connection. if the network cable is switched off, it is not immediately displayed. A prompt "network connection failed" will be displayed at that time"

Please pay attention to the following points:

How can I determine whether a connection is lost or not through a query or not.

I don't know what to say about one of my ideas?
Generally, we use
Try
Except
End
To capture exceptions.
A custom exception is displayed, indicating that the database cannot be connected ???
When this exception occurs, the database can be automatically reconnected ??
In this way, you don't need to refresh your judgment ?? The database will be reconnected only when the connection fails.

GoldShield (Li baizen) () reputation: 106 Blog 13:34:48 score: 0



Such requirements also appeared in the system a few days ago.
I tried some methods:
...............
1. Use your connection to perform a simple query operation. For example, select getdate () to check whether the returned value exists.
2. The multi-thread method is used. A ADOConnection is used to determine the network and a global variable is provided to record the network status. During the operation, only the variable status value is determined.
3. in Indy mode, the client and server are placed in one. after each client sends a message, if the server receives the message, a value is immediately returned. if the client sends a "1 + 1" message, the server immediately returns "2 ". of course, you need to add some agreed format strings. Otherwise, the client will be confused. if the returned value is not received within the specified time, the network has failed.



Supported

If you want to quickly use IDIcmpClient, you can use Adoconnection to determine the response time, which is longer.

Function conip_only: Boolean;
Begin
Result: = false;
IdIcmpClient1.Host: = '192. 168.0.2 ';
Try
IdIcmpClient1.Ping;
Result: = true;
Except
On e: exception do
Showmessage (e. Message + 'network communication interrupted .');
End;
End;

Procedure TdtmConnect. tiKeepConnectTimer (Sender: TObject );
Begin
Try
ConMain. Execute ('select @ CONNECTIONS ');
// MsgBox ('server connection is normal ...');
Except
TiKeepConnect. Enabled: = False; // stop clock movement
ErrBox ('network disconnected, and the connection to the server has been interrupted. ');

If not Assigned (frmConnect) then
FrmConnect: = TfrmConnect. Create (self );
Try
FrmConnect. ShowModal;
Finally
FreeAndNil (frmConnect );
End;
End;
End;

Under what circumstances?
In general, if the network is disconnected, the system will fail. In this case, you need to check the network.

The TAdoConnect component has multiple events for judgment.

I encapsulate all the functions used in the program to query, execute SQL, and execute the stored procedure into several functions, and then call the following functions when these functions fail to be executed:

// Join again
Function TDataModuleForm. ReConnect (Connection: TADOConnection): boolean;
Var I, j: integer; FBreak, FErr: boolean;
Label lbl_try;
Begin
Result: = false;
FBreak: = false;
FErr: = false;
For I: = 0 to connection. Errors. Count-1 do
Begin
If FBreak then Break;
If connection. Errors [I]. Number =-2147467259 then
Begin
FBreak: = true;
Connection. Errors. Clear;
If messagedlg ('the system detects that the database connection is disconnected. Do you need the system to try again to connect to the database? ', Mtinformation, [mbOk, mbCancel], 0) = 1 then
Begin
Lbl_try:
Connection. Close;
FErr: = false;
J: = connection. ConnectionTimeout;
Connection. ConnectionTimeout: = 5;
Screen. Cursor: =-11;
Application. ProcessMessages;
Try
Try
Connection. Connected: = true;
Except
FErr: = True;
End;
Finally
Connection. ConnectionTimeout: = j;
Screen. Cursor: = 0;
Connection. Errors. Clear;
End;
If FErr and (Messagedlg ('Connection failed, try again? ', Mtinformation, [mbOk, mbCancel], 0) = 1) then goto lbl_try;
End;
End;
End;
Result: = not FErr;
End;

Try

Except
Determines whether the connection is disconnected or not.
End;

The answers provided by kaper () and zczb (zczb) are constructive. Thank you.

Hi, there is no need to make it so complicated that the landlord will consume more resources.

In this case, timer1 is used.

Add more adodataset1,

Write in timer1.ontimer:
Try
Adodataset1.close;
Adodataset1.commandtext: = 'select getdate ()';
Adodataset1.open;
Secondary CT
Showmessage ('disconnected ');
End;

I do not like automatic connection. For example, the log is full. Or for other reasons. Your action will cause the system to be in the connection of the database for a long time, but in fact it is temporarily unable to connect, and it will lead to an endless loop! I think the intention of the landlord is to find that the data disconnection should also be detected in order to be able to automatically connect to the database. In a back step, when the use of the software fails to connect to the database. There are only a few reasons. 1. Network 2, database server, 3, database software. 4. Program itself. 5. Other reasons will force you to stop and adjust. There is no need to detect and reconnect! Personal ignorance,

The idea is complicated.

Try
......
.....
.....
Except
Adoconnection. close; // prepare for the next connection.
End

I still don't understand what I mean.

The solution to this problem is to open the connection and query every time we use the database, at the same time, a panel is left on the interface to indicate whether the network is disconnected or the database is disconnected (the following two functions are used to determine whether the network is disconnected or the database is disconnected ):
Function CheckNetConnection (IP: string): Boolean;
Var
Echo: TIdEcho;
Begin
Echo: = TIdEcho. Create (nil );
With Echo do
Begin
Try
Host: = IP;
Connect (1, 1000 );
Result: = True;
Except
Result: = False;
End;
End;
Echo. Free;
End;

Function CheckDBConnection (IP: string): Boolean;
Var
Telnet: TIdTelnet;
Begin
Telnet: = TIdTelnet. Create (nil );
Try
With Telnet do
Begin
If Connected then Disconnect;
Host: = IP;
Port: = 1433; // Port number used by the Database Service (this Port number is used by SQL server)
Try
Connect (1, 1000 );
Result: = True;
Else t // otherwise reconnect
Result: = False;
End;
End;
Finally
Telnet. Disconnect;
Telnet. Free;
End;
End;

The above two functions perform periodic checks in Timer. Our timer time interval is 10 seconds. The display is slightly later, but the customer can tolerate it. In addition, when the network is disconnected, the Retrieval Database is occasionally stuck, however, you don't need to restart the software. You only need to process the network and database.

Every time someone asks me a classic question, I answer the same question ..
But almost every time I ask a question, people think I'm joking.

What should I do when the database is disconnected? Are you trying to reconnect? Constantly check the connection status?
NO! Do not do this!
Why is the database disconnected? Most of the network problems or the database is on or restarted. In fact, at this time, the program is trying to automatically reconnect,
Meaningless! In this case, there will be no chance of recovery for a heavily burdened network.

What should we do at this time? Yes. Release the connection object. Many components that encapsulate the underlying connection will temporarily Save the connection status,
When the underlying connection fails, the state of the encapsulated object is normal. At this time, the connection cannot be restored.
How to make the encapsulated object consistent with the underlying connection status? It's easy to make the connection object CLOSE!

How do I know the connection failed? During database operations! For VCL, the component will throw an exception of the database type. The only thing to do is to catch it. For the data-aware control, it can also be obtained in the Application. OnException event.
When handling such an exception, as we just said, "CLOSE the connection object "!

You do not need to worry about connecting objects in CLOSE state. when VCL is used to encapsulate Ado, when the connection object AdoConnection is closed, any database operation will automatically connect. this is the default property design.

Agree to the upstairs. Maybe our opinions on the upstairs can solve the only one problem of our software. It would be better to prompt \ reconnect \ and it is not enough.

TDataForm = class (TDataModule)
//...
Procedure DataModuleCreate (Sender: TObject );
Private
FOldApplicationOnException: TExceptionEvent;
Procedure HandleException (Sender: TObject; E: Exception );
...
End.

Procedure TDataForm. DataModuleCreate (Sender: TObject );
Begin
...
FOldApplicationOnException: = Application. OnException;
Application. OnException: = HandleException;
End;

Procedure TDataForm. HandleException (Sender: TObject; E: Exception );
Begin
If E. ClassNameIs ('eoleexception') then
If ShowMsg (Screen. ActiveForm, format (
'The database connection was accidentally aborted. Do you want to reconnect? '#10 #13 +
'Error cause: % s ',
[E. Message]) = IDYES then
Begin
MainConn. Connected: = False;
MainConn. Connected: = True;
Exit;
End;
If Assigned (FOldApplicationOnException) then
FOldApplicationOnException (Sender, E)
End;

Mark

I also encountered a similar problem. You have time to take a look.
Http://community.csdn.net/Expert/topic/5460/5460676.xml? Temp = 7.497805E-02