How to use TMSConnection in the background without blocking?

pete-R · Post by **pete-R** » Sun 14 Feb 2010 20:21

Hi. Currently testing this component in trial mode. So, I'm generally unfamilar with this component set, but have looked at the help files and searched the Forum, but can't find anything detailed enough to answer my query. I have made an App to Connect to SQL however, and I've used the Table and Query components to extract data OK.

I need to make MSConnect1.Connect Non-Blocking. Ideally I wish this component had this option, so I could just Poll it to see if the connection had been made, but it makes me wait for an answer, and in simulating SQL failure tests, the reconnection after a forced disconnect causes a pause of up to 3 seconds to my Main App which is running some critical timings to 1/25th of a second - which still need to continue even in the event of a SQL failure.

Now, if I put the Connection logic in a separate thread then surely the 'synronisation' method of safely accessing the VCL components back on the Main Thread would still pause it? Unless I have misunderstood the documentation I've read about how this works.

What are people's thoughts on how I can establish connections in the background which don't affect the Main Thread containing my VCL components, but those data-aware components on the Main thread still need to access the Connection to SQL made by the other thread?

Dimon · Post by **Dimon** » Mon 15 Feb 2010 09:04

SDAC doesn't support non-blocking establishing of a connection. You can do it using a separate thread. However in this case you can access to this connection in the main thread (for opening table e.g.) only after connection will be established in other thread.

pete-R · Post by **pete-R** » Mon 15 Feb 2010 17:32

Thanks Dimon. I think that's what I was asking. As long as the Main thread doesn't use the MSConnect Component, and leaves a separate thread to manage that, then it is totally non-blocking to the Main thread even if you use a Table or Query component in the Main thread which is linked to the "Connection" made in the sub-thread. With the proviso, as you say, that you have a bit of logic making sure the sub-thread has made the connection before you try and use it.

I'll do some experiments with that.

Out of interest, is there a fundamental technical reason why a Connection request has to wait? Rather than, say, carrying on and just waiting for an "On Event" which flags the connection as being valid. I'm sure there must be a good reason why it works the way it does!

Dimon · Post by **Dimon** » Tue 16 Feb 2010 11:14

If I've understood you right, MSConnection blocks the main thread because process of connecting interacts with SQL Server and we can't influence on this.

pete-R · Post by **pete-R** » Tue 16 Feb 2010 19:23

Hi Dimon,

With also some ideas from the thread Bruce created concerning Threading for Queries, I implemented the Connect procedure successfully, and it now does not block the main thread! I think this is alot neater. On a general level, the main problem with connections to SQL are not the few seconds it may take to make a Connection, but the Timeout if the SQL Server is off the network for some reason. This causes the longest apparent 'freeze' to the User.

I often think when launching an App it looks so unclean when it appears to be doing nothing for 10-15 seconds just waiting for a Network timeout. In my exerience Users tend to think it has crashed and do silly things such as force the App closed. I think maintaining an up to date Visual update of the Connection status which has not frozen is good Front End displine.

So, I tested the thread procedure both for SQL login errors, and also physically pulling the cable out. In all instances, the Application maintained a non-blocking stance while attempting the Connection, and was able to give up to date information on the Status to the User without freezing the rest of the front end.

Here is what I have done. So MSConnection1 is your VCL component dropped into the Main Design Form.

ConnectThread = class(TThread)
public
FMSConnection1: TMSConnection;
procedure Execute; override;
Constructor Create(MainConnection : TMSConnection); overload;
end;

constructor ConnectThread.Create(MainConnection : TMSConnection);
begin
inherited Create(True);
FMSConnection1 := MainConnection;
FreeOnTerminate := TRUE;
Resume;
end;

procedure ConnectThread.Execute;
begin
CoInitialize(Nil);
IF FMSConnection1.Connected = FALSE THEN FMSConnection1.Connect;
CoUninitialize;
end;

Back in the Main thread. On checking the status of the VCL component MSConnection1 and finding Connect is FALSE is simply spawns:

ConnectThread.Create(MSConnection1);

For the AfterConnect OnEvent in the Main Form Thread of MSConnection1 :

PrintStatus(0,'Connection made with Database'); //Info
AdvSmoothStatusIndicator1.Appearance.Fill.Color:=clGreen; //Set Status to Green
MSTable1.Active := TRUE;
etc...
etc....
This reconnects all the Data Aware components

For the AfterDisconnect OnEvent in the Main Form Thread of MSConnection1:

PrintStatus(20,'Connection lost to Database'); //Serious
AdvSmoothStatusIndicator1.Appearance.Fill.Color:=clRed; //Set Status to Red

The Data Aware components seem to de-active themselves so nothing else required.

Thanks for your prompt help, and I'll continue to test out the component suite for my needs. I'm sure I might have more questions over the next few weeks!

Dimon · Post by **Dimon** » Wed 17 Feb 2010 08:27

I am glad to help you.