SQL Server Parameter Memory Leak

[Volker.Maier]

Hello,

environment : Delphi 2010, SQLServer 2012, Windows 7

we are experiencing a problem with memory leaks regarding "unused/uninitialized" parameters.

So for example i have:

Code: Select all

var
  LUniQuery: TCustomDADataSet;
[....]
LUniQuery.ParamCheck := True;
LUniQuery.SQL.Add('select * from mytable where field1 = :param1 and field2 = :param2');
LUniQuery.Params[0].Value := 'Hello';

If i execute that often enough, i see a memory leak, which is sadly enough, not detected by Eurekalog or anything else. Run it 3000 or more times and its clearly visible in taskmanager though.

Adding this :

Code: Select all

LUniQuery.Params[1].Value := '';

Or putting any value to the second parameter will fix it. No leak, no problem. So its only a problem when one parameter is null/unassigned and therefore is of type ftUnknown inside of unidac.

I did run the same code against oracle and there is no problem.
No parameters other than shown above where used.

Could you please try to reproduce the issue.. and as i said, its not shown in Eurekalog. Make it a loop, 5000 times, 10000 times and it will show up.

edit : Tested with Unidac 6.3.13

[azyk]

We have composed an example according to your description(10000 times), but specified issue was reproduced neither on Unidac 6.3.13, nor on the latest version.

Please check whether the specified issue is reproduced if:
- run the application on another PC
- use credentials of another user
- use another server/version of SQL Server

[Volker.Maier]

So i have been investigating further, and here are my findings so far:

We are working exclusively in virtual development machines using vmware with windows 7.

If i compile and run my test application with Delphi Seattle in our new seattle VM, there is no leak.
If i run the compiled executable in our Delphi 2010 VM, there is seems to be a leak once again. But much less than if compiled with Delphi 2010.

But.. if i compile with Delphi 2010, the application will leak in both VMs. thats a very inconsistent behaviour which is really confusing me.

Maybe its tied to what kind of drivers are being used to connect to MSSQL?
How can i maybe see or change the driver being used?

The next confusing thing is.. If i leave 2 parameter empty/null, then i still see the same behaviour as described. But once i run it out of my VM, inside my hostsystem (also Windows 7), i am getting "invalid input parameters. Check the status values for details".
But if i fill all parameters or leave one null (tried for each, doesnt matter which), it runs fine.

And finally here is the testprogram i used. .but as i said, its stripped down to most simple calls. Change the table/fields as you see fit. It does not matter if the field type is numeric, date, varchar etc.

Code: Select all

program SQLServerLeak;

{$APPTYPE CONSOLE}

uses

  DBAccess,
  Uni,
  SQLServerUniProvider;

procedure TestIt;
var
  LUniQuery: TCustomDADataSet;
  LUniConnection : TUniConnection;
  lidx : integer;
begin

  LUniConnection := TUniConnection.Create(nil);
  LUniConnection.ProviderName := 'Sql Server';

  LUniConnection.Username    := 'username';
  LUniConnection.Password    := 'password';
  LUniConnection.Server      := 'SQL2010SRV\MSSQLPRD';
  LUniConnection.LoginPrompt := False;

  LUniConnection.Connect;

  LUniQuery := TUniQuery.Create(nil);
  LUniQuery.Connection := LUniConnection;

  for lidx  := 0 to 20002 do
  begin
    if (lidx mod 50) = 0 then
      Writeln(lidx);

    LUniQuery.ParamCheck := True;

    LUniQuery.SQL.Clear;
    LUniQuery.SQL.Add('Select * from tblwms_lu_tara where wlut_guid = :parawlut_guid and wlut_lu=:parawlut_lu and wlut_weight_tara = :parawlut_weight_tara and wlut_description= :parawlut_description');

    LUniQuery.Params[0].Value := 'Hallo2';
    LUniQuery.Params[1].Value := 'Hallo2';
    LUniQuery.Params[2].Value := 3;
// Uncomment the following line and the leak will disappear
//   LUniQuery.Params[3].Value := '';

    LUniQuery.Execute;
  end;

  FreeAndNil(LUniQuery);
  FreeAndNil(LUniConnection);

end;

begin
  try
    CoInitializeEx(nil, COINIT_MULTITHREADED);
    TestIt;
    CoUninitialize;
  except
    on E: Exception do
    begin
      Writeln(E.ClassName, ': ', E.Message);
      Readln;
    end;
  end;

end.

[Volker.Maier]

I got a Eurekalog Leak now after adding all the sourcefiles of unidac to the project.
Eurekalog 7.3.2.0, Delphi 2010

This is the part that repeats for each loop i have in the testprogram:

Code: Select all

0003 Memory Leak: Type=BSTR (Memory): $00ED53D4 - SysAllocStringLen($00ED2C8C, $00000006); Total size=1; 
00407FFB	00007FFB	00000000	SQLServerLeak.exe	00400000	System		_WStrFromPWCharLen			
0044C5C2	0004C5C2	00000000	SQLServerLeak.exe	00400000	Variants		_VarFromWStr			
007AA148	003AA148	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBCommand	RequestAndFreeParamAccs	4279	20	
007AA450	003AA450	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBCommand	RequestParamsIfPossible	4328	17	
007AFB07	003AFB07	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBRecordSet	RequestParamsIfPossible	5904	2	
007AE81C	003AE81C	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBRecordSet	ReleaseRecordSetInterfaces	5559	6	
007B3B1C	003B3B1C	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBRecordSet	ProcessNoResult	7112	7	
007B41CA	003B41CA	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBRecordSet	ReadNextRows	7311	113	
007B3E51	003B3E51	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBRecordSet	FetchingAccessible	7191	64	
006A9457	002A9457	00000000	SQLServerLeak.exe	00400000	CRAccess	TCRRecordSet	InternalFetch	4739	6	
006A97CA	002A97CA	00000000	SQLServerLeak.exe	00400000	CRAccess	TCRRecordSet	Fetch	4851	9	
007989C4	003989C4	00000000	SQLServerLeak.exe	00400000	MSClassesUni	TMSSQLRecordSet	DoFetchAll	2634	4	
0079899B	0039899B	00000000	SQLServerLeak.exe	00400000	MSClassesUni	TMSSQLRecordSet	FetchAll	2625	17	
006A9340	002A9340	00000000	SQLServerLeak.exe	00400000	CRAccess	TCRRecordSet	ExecFetch	4665	10	
006A6C7C	002A6C7C	00000000	SQLServerLeak.exe	00400000	CRAccess	TCRRecordSet	InternalOpen	3496	27	
007AED3B	003AED3B	00000000	SQLServerLeak.exe	00400000	OLEDBAccessUni	TOLEDBRecordSet	InternalOpen	5658	13	
005F74D4	001F74D4	00000000	SQLServerLeak.exe	00400000	MemData	TData	Open	1791	4	
00685726	00285726	00000000	SQLServerLeak.exe	00400000	MemDS	TMemDataSet	InternalOpen	1087	2	
006D617B	002D617B	00000000	SQLServerLeak.exe	00400000	DBAccess	TCustomDADataSet	InternalOpen	10476	12	
0067EB9D	0027EB9D	00000000	SQLServerLeak.exe	00400000	DB	TDataSet	DoInternalOpen			
006D0080	002D0080	00000000	SQLServerLeak.exe	00400000	DBAccess	TCustomDADataSet	OpenCursor	8000	45	
006FECBB	002FECBB	00000000	SQLServerLeak.exe	00400000	Uni	TCustomUniDataSet	OpenCursor	3157	3	
0067EB11	0027EB11	00000000	SQLServerLeak.exe	00400000	DB	TDataSet	SetActive			
006CFE70	002CFE70	00000000	SQLServerLeak.exe	00400000	DBAccess	TCustomDADataSet	SetActive	7940	4	
0067E958	0027E958	00000000	SQLServerLeak.exe	00400000	DB	TDataSet	Open			
006D03F5	002D03F5	00000000	SQLServerLeak.exe	00400000	DBAccess	TCustomDADataSet	Execute	8108	1	
007D29EA	003D29EA	00000000	SQLServerLeak.exe	00400000	SQLServerLeak		TestIt	151	31	
007E1559	003E1559	00000000	SQLServerLeak.exe	00400000	SQLServerLeak		Initialization	162	3	
75383378	00013378	00000000	kernel32.dll	75370000	kernel32		BaseThreadInitThunk			
779492E0	000392E0	00000000	ntdll.dll	77910000	ntdll		(possible RtlInitializeExceptionChain+97)			
779492B0	000392B0	00000000	ntdll.dll	77910000	ntdll		(possible RtlInitializeExceptionChain+49)			

[azyk]

We tested the provided example, however memory leak wasn't reproduced. We used:
- Windows 7 VMware virtal maсhine
- RAD Studio 2010 Version 14.0.3615.26342, Update 4
- Eurekalog 7.3.2.0
- standard OLEDB provider, SQL Native Client provider and the Direct mode to connect to SQL Server

In order for UniDAC to use standard OLEDB provider to connect to SQL Server, before establishing connection set 'prSQL' value to the TUniConnecyion.SpecificOptions.Values['SQL Server.Provider'] property, for SQL Native Client provider - 'prNativeClient' value and for the Direct mode - 'prDirect' value.

Please inform if the issue is reproduced when using the mentioned providers

[Volker.Maier]

Thank you very much, we are making progress

prSQL : Complains if 2 or more Params are null. works with 1 null parameter. does NOT leak memory
prNativeClient : Memoryleaks, like mentioned before. Looks like that was used before
prDirect : Works with 1 or 2 null parameters, NO memory leak..

So the problem ONLY occurs with the native client. So this should be ok for us now to find a workaround. But still i am wondering why it happens with the nativeclient on different machines.
And as a sidenote: prDirect is more than double the speed compared to the others, its blazing fast...

[Volker.Maier]

directmode seems to have troubles too.. no memory leak, but the RowsAffected is always 0 after an update, even the data has been changed..

and we have problems with conversions, when we use null parameters. there i am getting the error that implicit conversion from sql_variant to nvarchar is not allowed. use "convert"...
That was working fine with the other driver.

So we are still stuck on the problem

[azyk]

To avoid memory leak for prNativeClient, try to reinstall SQL Native Client provider on a client machine.

If the memory leak still exists, provide the SQL Native Client provider version which reproduces the issue. For example, to use SQL Native Client 11 set the 'prNativeClient' value to the TUniConnection.SpecificOptions.Values['SQL Server.Provider'] property and the 'nc2012' value to the TUniConnection.SpecificOptions.Values['SQL Server.NativeClientVersion'] property; for SQL Native Client 10 to 'nc2008'; for SQL Native Client 9 to 'nc2005';

Also specify a full version of SQL Server and bitness of a client OS (32/64 bit).

Please send us a small sample reproducing the UPDATE query execution in the Direct mode, when the dataset property RowsAffected returns 0.

To set a null value to dataset parameter, you need to call the Clear method for the parameter and set a data type to the DataType property, e.g ftWideString for the nvarchar type. The code example can look like this:

Code: Select all

UniQuery.ParamByName(ParamName).Clear;
UniQuery.ParamByName(ParamName).DataType := ftWideString;
UniQuery.Execute;