How to cancel the db access operation?

visli · Post by **visli** » Mon 27 Jun 2011 08:13

I use TUniConnection and TUniQuery components in subthread. I need to terminate the thread as soon as possible when the user closes the program.
However, in some cases, it would be more time-consuming on database operations. For example, connect to DB Server or execute SQL. The thread will be blocked in these methods if those methods take more time-consuming. Is there any way we can quickly cancel or terminate those operations?

AndreyZ · Post by **AndreyZ** » Mon 27 Jun 2011 09:24

Hello,

Depending on the database server you are using, you can set the ConnectionTimeout option to decrease the time for connection establishment in the following way:

Code: Select all

UniConnection.SpecificOptions.Values['ConnectionTimeout'] := '5';

You will find more information about server-specific options in the "Provider-Specific Notes" section of the UniDAC documentation.
You can break query execution by calling the TUniQuery.BreakExec method. You should call this method from another thread. For more information, please refer to the UniDAC documentation.

visli · Post by **visli** » Fri 01 Jul 2011 06:28

AndreyZ,

I do a test, use TUniConnection connect to a non-existent database:

Code: Select all

    FConnection.SpecificOptions.Values['ConnectionTimeout'] := '5';
    FConnection.SpecificOptions.Values['SQL Server.ConnectionTimeout'] := '5';
    st := GetTickCount;
    try
      FConnection.Connect;
      st := GetTickCount - st;
    except
      st := GetTickCount - st;  // Line 8
    end;

In line 8, "st" equal to 56364 ms, more bigger than 5000 ms.

AndreyZ · Post by **AndreyZ** » Fri 01 Jul 2011 08:23

This behaviour is connected with the specificity of the OLEDB provider work. OLEDB tries to find the specified server and it takes much time if it doesn't exist. You can check this OLEDB behaviour with ADO components and Microsoft SQL Server Management Studio. We cannot influence OLEDB provider work.