FetchAll=False & cancel fetching

[Quido]

Hello

UniDac trial, mssql provider, big query but we don't want all records:

1.FetchAll=True

FetchAll=True
query.Open -- slow, fetching all records
...
query.close -- quick


2.FetchAll=False

FetchAll=False
FetchRows=100
query.Open -- quick, fetch 100 records
...
query.close -- slow, fetching rest of records

Is it possible cancel fetching? We want only close cursor...


Thanks in advance for your reply

[AndreyZ]

Hello,

Most probably this problem is caused by OLEDB. Please try using Native Client and check if the problem persists. To use Native Client, you should set the TMSConnection.Options.Provider option to prNativeClient.

[Quido]

Hello

Current setting:

var
dbCon: uni.TUniConnection; // DB connection
...
begin
...
// common part 1. (Oracle and MS SQL)
dbCon:=TUniConnection.Create(Application);
dbCon.Pooling :=False;
dbCon.Options.DisconnectedMode:=False;
...
// in case of MS SQL:
dbCon.ProviderName:='SQL Server';
dbCon.SpecificOptions.Values['ConnectionTimeout'] :='1';
dbCon.SpecificOptions.Values['AutoTranslate'] :='True';
dbCon.SpecificOptions.Values['PersistSecurityInfo']:='False';
dbCon.SpecificOptions.Values['Encrypt'] :='False';
dbCon.SpecificOptions.Values['PacketSize'] :='4096';
dbCon.SpecificOptions.Values['MultipleActiveResultSets']:='True';
dbCon.SpecificOptions.Values['OLEDBProvider']:='prNativeClient';
dbCon.SpecificOptions.Values['FetchAll']:='False';
dbCon.SpecificOptions.Values['CursorType']:='ctDynamic';
dbCon.SpecificOptions.Values['CursorLocation']:='clUseClient';
...
// common part 2.
dbCon.LoginPrompt:= True;
dbCon.Server :=string(dbServer);
dbCon.Username :=string(dbusername);
dbCon.Password :=string(dbPsswd);

try
dbCon.Connect;
except
...

// setting for query after create query (common)
connection:=dbCon;
Options.LongStrings :=True;
Options.RequiredFields :=True;
Options.RemoveOnRefresh:=False;
Options.StrictUpdate :=False;
Options.TrimFixedChar :=False;
options.SetFieldsReadOnly:=False;
Options.detailDelay:=0;
FetchAll:=False;
FetchRows:=100;
Unidirectional:=False;
...


Is it correct? I suppose that TMSConnection.Options.Provider is in SDAC, not UniDac.

[AndreyZ]

I cannot reproduce the problem. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com, including a script to create and fill a table. Also please specify the following:
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor;
- the exact version of your IDE;
- the exact version of SQL Server server. You can learn it from the Info sheet of TUniConnection Editor.

[Quido]

Hello,

It's only testing environment:

appl. side:

MS WIN XP SP3
Delphi XE (Embarcadero® RAD Studio XE Version 15.0.3953.35171 )
UniDac Trial 3.70.0.19 for RAD Studio XE

MS SQL explicit instalation (info from ms sql man. studio):
Microsoft SQL Server Management Studio 10.50.1600.1
Microsoft Data Access Components (MDAC) 3.85.1132
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.5512
Microsoft .NET Framework 2.0.50727.3623
Operating System 5.1.2600

server side:

MS Win 7 64b
MS SQL Server 2005 Express Edition (file version: 2005.90.4035)
MS SQL Server 20008 Management Studio (10.50.1600.1)

...but I'm only testing UniDac, we are going to replace ADO with UniDac. If you say that this behavior is wrong, I'll be satisfied. Our production IDE is Delphi XE and customers have MS SQL server 2000/2005/2008

I can try it from another test environment on app. side (in few days):
MS WIN XP SP3
BDS2006 (version 10 Update 2)
UniDac Trial 3.70.0.19 for Delphi 2006
without explicit MS SQL Server sw instalation


Note: We not use visual components, so TUniconnecion Editor have no information about DB server. But I make test connection from it and there is info about server/client:

Server version: Microsoft SQL Server 09.00.4035
Client version: 10.50.1600.1

[Quido]

Hello

I tried another IDE:

MS WIN XP SP3
BDS2006 (version 10 Update 2)
UniDac Trial 3.70.0.19 for Delphi 2006
without explicit MS SQL Server sw instalation

but prNativeClient: Provider not installed

so I installed ms native client sql server 2005

-> OK but same wrong behavior:

open
FetchAll=True 5 sec (120 000 rec)
FetchAll=False <1 sec (100 rec)

close
FetchAll=True <1 sec
FetchAll=False 5 sec

[AndreyZ]

Unfortunately, we cannot reproduce the problem. We will be able to investigate this problem if you provide us access to your SQL Server database. If you want to help in the investigation of this problem, please contact us at andreyz*devart*com.

[Quido]

Thank You.

We are going to buy UniDac with source and we will see.

[gustavo]

I am not sure I should be posting this here, but I am experiencing the exact same behavior, but using MySQL instead. When I open a query with FetchAll set to False, the query opens quickly, but takes some seconds to close. When I open a query with FetchAll set to True, it takes a while to open (I know it is retrieving all the records) but closes instantly with no delay. How can I fix that? I need to open the query quickly (FetchAll=false) and close it quickly too. It seems to me all data is being fetched when I try to close the query object, and that should not happen since I am using FetchAll=false.

I am using Delphi XE2, UniDAC 4.1.4 and MySQL 5.5.

Thank you.

[gustavo]

After trying a lot of different things I found out the calling BreakExec before Close resolves my issue. The documentation states that BreakExec "Breaks execution of a SQL statement on the server". Is that the correct way to do that?

Thank you.

[AlexP]

hello,

Such behavior (fetching results when closing Query) is connected with specific character of MySQL server operation. The server returns all records received after query execution, and we, depending on the FetchAll option setting, when opening DataSet either get all records from socket (FetchAll = true), or get the number of records specified in the FetchRows property (FetchAll = false), but when DataSet is closed, if the FetchAll = false, we should get all returned data from the socket to ensure that further work will be correct. In some cases calling BreakExec can really solve the problem.