Memory Data sample

About the sample mentioned in the title, I was inspecting the code on the server side so I could implement something similar that’s required for my project. I assume the two TDAMemDataTable components, “MemoryData.ComboDataset” and “MemoryData.DirectoryData”, are not thread safe and are used just to display how to achieve required result. In real life scenarios with many users, I guess, I must create TDAMemDataTable on runtime in the “DataAbstractServiceBeforeGetDatasetData”. In such case where do I destroy the TDAMemDataTable instance I create, in the same event? Does this mean data are already on the wire transmitting? Probably not, right? What am I missing here? Should I use an object pool of these TDAMemDataTable instances?

Thank you!

you can just drop TDAMemDataTable component to your service.
if default class factory is used, it will be thread safe

Got it. A new instance of the TDatamodule is created at every request, so this makes it thread safe.
Since we are on this topic with DataAbstractServiceBeforeGetDatasetData, is it possible to override the aDataset’s sql and execute the new one instead?

yes, it is possible

On the Dataset’s SQL overriding topic, I’m doing this:

var
aTempDataset : IDADataset;
begin
if CompareText(aDataset.Name, ‘MyTable’) = 0 then
begin
aTempDataset := XRPackages.NewDataset( Connection, ‘MyTable2’, [‘myparam’], [aDataset.ParamByName(‘myparam’).Value]);
aDataset.SQL := GetSQLFromProcessedData(aTempDataset.Dataset);
end;
end;

The “GetSQLFromProcessedData” processes the records from aTempDataset and generates a new SQL statement that returns recordset. When the overridden dataset is opened I get an exception with description “CommandText does not return a result set.”
What am I missing or doing wrong here?

can you show your SQL for which you receive this error?
you can attach it here or send directly to support@

aSQLtext.txt (15.6 KB)

Here’s the SQL I use. The insert statements are different in number depending on the parameter.

your SQL looks valid.
Can you debug your server and check what SQL is used?
breakpoint can be set at TDAEBaseDataset.DoSetActive

Can’t locate TDAEBaseDataset, which unit is it in?

Did you mean TDAEDataset.DoSetActive in “uDAEngine.pas”?

it is uDAEBaseDataset.pas

Note: If you are using outdated version of DataAbstract, use TDAEDataset.DoSetActive

Already did use TDAEDataset (using version v7 ) and the exception is raised at line 1702

// Writes the parameter values
if (fParams.Count > 0) then SetParamValues(fParams) else ClearParams;

startTick := ROGetTickCount;
if Assigned(fOnBeforeOpen) then fOnBeforeOpen(Self);
// Opens the dataset
fAutoFields := (fFields.Count = 0);
try
  **FNativeDatabaseAccess.Active := True;**   <-- HERE
  FixKnownIssues;
except
  on E:Exception do begin
    if Assigned(fOnOpenError) then fOnOpenError(Self, DoGetSQL, E);
    raise;
  end;
end;

can you evaluate self.GetSQL at this line? it should be your SQL

It is my SQL but not all of it. I’m not sure if this is an issue of the debugging tool, not being able to show the whole string because it’s too big or in fact the self.GetSQL does not contain the whole SQL string.

What else should I look for?

is your generated SQL opened correctly in DASM?
I suppose, that you are using only SELECT statement here and not INSERT ones.


can you create a simple testcase, that reproduces this case, pls?
you can attach it here or send directly to support@

It doesn’t look valid to me - maybe the syntax is different than that what I am used to (Microsoft TSQL), but I see the following problems with the inserts: The first field is an identity field, so you cannot insert without a fieldlist (or without using Identity insert on)

The SQL is valid and runs correctly as expected. I have also used it in the DASchema as the SQL statement of a test data table. No issues at all. And actually you can insert without the field list.

Am I correctly understand, that GetSQLFromProcessedData returns only SELECT statement?
aDataset.SQL in this event cannot contain another statements like INSERT or DECLARE

Ok, than it works differently than I am used to.