3

My Delphi code opens a TFDQuery (FireDAC), then opens the TClientDataSet connected to it via a TDataSetProvider:

ClientDataSetData.Close;
with QueryData do
begin
  Close;
  SQL.Clear;
  SQL.Add(ASelectSQL);
  Open;
end;    
ClientDataSetData.Open;

ASelectSQL contains this SQL:

SELECT TT_NIV_ID,
TT_NIV,
REPLACE(TT_NIV_NAME, '|', '!') as TT_NIV_NAME2
FROM TT_SYS_PRJ_NIV

The ClientDataSetData.Open gives an insufficient memory error on a dataset with 42200 records.

If I inspect the result data (in the Delphi code) I see that TT_NIV_NAME2 is a string of length 8000!
From the REPLACE() documentation:

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes

... so this seems to be happening; not just truncating but also setting the result type.

TT_NIV_NAME is a VARCHAR(50), so a quick fix is to change the SQL to

SELECT TT_NIV_ID,
TT_NIV,
CAST(REPLACE(TT_NIV_NAME, '|', '!') as VARCHAR(50))  as TT_NIV_NAME2
FROM TT_SYS_PRJ_NIV

but this is with user defined SQL that I have no control over. Next time (s)he may run a with a REPLACE on something else... I'd prefer a generic solution.

Is there anything I can do to the Delphi code (TClientDataset or TFDQuery or TFDConnection settings?) that prevents this high memory usage?

And frankly, why would REPLACE conclude that "string_expression is not of type varchar(max)", when TT_NIV_NAME is a VARCHAR(50)?

Notes:

  • Tested with several SQL server drivers - it's not a driver issue.
  • The TFDConnection has FetchOptions.Mode := fmAll and FetchOptions.Items := FetchOptions.Items - [fiMeta];
    fmAll must stay*, and not subtracting fiMeta makes no difference.
  • Related [1] but here string_expression is longer than 8000, or [2] where string_expression is not of type varchar(max) but both do not apply to my SQL data.
  • Delphi Tokyo 10.2.3 using FireDAC against SQL Server 2012, Win32 app running under Win7 or Win10

* .. to prevent the famous hstmt error in an environment where only very basic SQL Server drivers are installed; and not setting it makes no difference anyway in my test app.

Jan Doggen
  • 8,799
  • 13
  • 70
  • 144
  • The REPLACE() does make the result varchar(8000) which isn't the real problem. The problem is FireDAC using fixed storage for this and consuming excessive memory. – Brian May 28 '18 at 16:16
  • @Brian, but what else can FireDAC do? The driver describes the field as such. Anywya, what makes you feel that FireDAC allocates memory as such? – Victoria May 28 '18 at 16:55
  • @Victoria FireDAC could store a reference/pointer to a variable length string in the fixed length buffer. The poster is getting out of memory errors and past experience with wide varchar() fields and Delphi database connection components where this was the problem makes me think it may be the case here as well. – Brian May 28 '18 at 17:00
  • @Brian, FireDAC's storage is efficiently written. In this case the field is described and treated as BLOB (and as such allocates only as much as is fetched from DBMS). Blame MIDAS here (client dataset). My advice is, get rid of MIDAS. Forever. Besides, the OP says, _"The ClientDataSetData.Open gives an insufficient memory error"_. If it was FireDAC's issue, the exception would be raised at their query object opening. – Victoria May 28 '18 at 17:23
  • @Victoria I concede - my statement about it being FireDAC storing the varchar(8000) inefficiently is not correct. – Brian May 28 '18 at 17:27
  • @Brian, no worries :) Here it's MIDAS who eats that much. How to stop it is difficult to say for me. I would print MIDAS source code to paper and send it to the deep space (as a possible alien invasion prevention). The source code on all computers I'd delete. But I see that the OP is modifying some legacy application and that it might be difficult to lose it. In any case, FireDAC describes the field correctly here and uses memory only as much as is fetched. And preventing users from writing e.g. `SELECT CAST('' AS VARCHAR(8000));` is merely impossible. – Victoria May 28 '18 at 17:49
  • @nil I was assuming that varchar(max) means any max sized varchar, i.e. varchar(n), but [that is not the case](https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017) – Jan Doggen May 29 '18 at 06:31

1 Answers1

2

We have 'solved' this by applying a mapping rule specifically for this 8000 character ANSIstring. I know, it's dirty, but for the legacy app it works (especially since we were using SQLDirect before switching to FireDAC, and there varchars > 256 bytes were translated to memo already).

with FDConnection.FormatOptions.MapRules.Add do
begin
  SourceDataType := dtAnsiString;
  SizeMin := 8000;
  SizeMax := 8000;
  TargetDataType := dtMemo;
end;

This mapping is specific to SQL Server, because the REPLACE function in the other two database types we support (Oracle, FireBird) does not have the SQL Server behavior.

Jan Doggen
  • 8,799
  • 13
  • 70
  • 144