Get SQL of TCriteria

Is there an easy way to get the SQL or maybe the corresponding TSelectCommand out of TCriteria without executing SQL or setting global Config SimulateStatements?

Is there any problem in using SimulateStatements?

Another workaround would be using the OnSqlExecuting event. After grabbing the SQL, you can call Abort or just raise a specific exception ESqlGrabbingException and ignore such exception at the caller level.

SimuleStatements is global, I think.

So setting this to true, doing some stuff, finally setting this false would work.

But I think it behaves a little bit different using threads, and there is just one that needs SimuleStatements, while others should at least execute their statements...

Correct me, if I'm wrong.

Yes, if multithread, then SimulateStatements cannot be used. But you have my other suggestion.

I could do this without raising and fetching an exception.
But you would have to change small things:

  • TSelecter.FCurrentCursor
  • TSelectCursor.FCommand

has to be protected for this.

I just gave it a try by doing this:

function TMySelecter.GetSQLFromCriteria(aCriteria: TCriteria): string;
  hParams: TObjectList<TDBParam>;
  hParams := nil;
  FCurrentCursor := TMySelectCursor.Create;
    FCurrentCursor.Traverser := TTableTraverser.Create(TMySelectCursor(FCurrentCursor).FCommand, Self, aCriteria, nil, False);
    hParams := TObjectList<TDBParam>.Create;
    BuildCommand(TMySelectCursor(FCurrentCursor).FCommand, hParams);
    Result := SQLGenerator.GenerateSelect(TMySelectCursor(FCurrentCursor).FCommand);

or maybe you could provide such a function in TSelecter?
or you could make your own derivation TSelecterSimulator or something like this?

I wait in suspense for your response, if this could be another (maybe a better) solution.

I don't know, I'm not sure if this is the best approach.

What about other SQL statements, like DELETE, UPDATE?

What about batch operations?

What about extra SQL statements that Aurelius executes, like to get a SEQUENCE value, or to retrieve the autogenerated id value?

What about SQL executed to retrieve associations and lazy-loaded lists?

Why you need to get such statements?

We got some lookups using the same ObjectManager, for setting Associations.
These lookups are using an AureliusDataset providing content by TCriteria (using paging for max. performance)

An entity got different Associations to same target entity.
Example: Article is associated with an entity called MeasurementUnit, while MeasurementUnit is part of a SingleTable Inheritance of an Entity Unit.
Article needs MeasurementUnit for gross and net measurements, so list of MeasurementUnits is fetched 2 times.

This could be done in a simple way, by checking entitytype and sharing TCriteriaResultList with both controls.

But there are other associations, that behave quite similar, using additional Criterion.
So that's why I'd like to take a look at sql-level, so that I could decide, if this Criteria should be executed by an ObjectManager, or if it's already executed and result is already in cache.

As far as I can see, TObjectManager is only taking care for it's cache, by using Find-Methods including ID as parameter.

Looks like a very specific case. Why don't you map the data to the criteria parameters, instead of SQL?

because criteria itself will be cloned, so that using criteria-pointers would not work...

But I'm referring to serializing the parameter names and their values, and then map that to the result.

that's what I wanted to do first, but by doing this I realized, that with same criterion different projections could appear...

Save the projections as well.